阅读目录
一、Hive内部表和外部表
0、Hive是什么?
Hive是一个SQL解析引擎,将SQL语句转译成MR Job,然后再Hadoop平台上运行,达到快速开发的目的。
Hive中的表是纯逻辑表,就只是表的定义等,即表的元数据。本质就是Hadoop的目录/文件,达到了元数据与数据存储分离的目的
Hive本身不存储数据,它完全依赖HDFS和MapReduce.。
Hive的内容是读多写少,不支持对数据的改写和删除
Hive中没有定义专门的数据格式,由用户指定,需要指定三个属性:
-- 列分隔符
-- 行分隔符
-- 读取文件数据的方法
1、Hive的create创建表的时候,选择的创建方式:
- create table
- create external table
2、特点:
● 在导入数据到外部表,数据并没有移动到自己的数据仓库目录下,也就是说外部表中的数据并不是由它自己来管理的!而表则不一样;
● 在删除表的时候,Hive将会把属于表的元数据和数据全部删掉;而删除外部表的时候,Hive仅仅删除外部表的元数据,数据是不会删除的!
注意:
1、- create table 创建内部表,create external table 创建外部表
2、建议在工作中用外部表来创建
二、Hive中的Partition
● 在Hive中,表中的一个Partition对应于表下的一个目录,所有的Partition的数据都储存在对应的目录中
– 例如:pvs 表中包含 ds 和 city 两个 Partition,则 – 对应于 ds = 20090801, ctry = US 的 HDFS 子目录为:/wh/pvs/ds=20090801/ctry=US; – 对应于 ds = 20090801, ctry = CA 的 HDFS 子目录为;/wh/pvs/ds=20090801/ctry=CA
● Partition是辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理。
三、Hive中的 Bucket
• hive中table可以拆分成partition,table和partition可以通过‘CLUSTERED BY ’进一步分bucket,bucket中的数据可以通过‘SORT BY’排序。 • 'set hive.enforce.bucketing = true' 可以自动控制上一轮reduce的数量从而适 配bucket的个数,当然,用户也可以自主设置mapred.reduce.tasks去适配 bucket个数
• Bucket主要作用:
– 数据sampling,随机采样
– 提升某些查询操作效率,例如mapside join
• 查看sampling数据: – hive> select * from student tablesample(bucket 1 out of 2 on id); – tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y) – y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了64份,当y=32 时,抽取(64/32=)2个bucket的数据,当y=128时,抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽 取。例如,table总bucket数为32,tablesample(bucket 3 out of 16),表示总共抽取(32/16=)2个bucket的数据 ,分别为第3个bucket和第(3+16=)19个bucket的数据
四、Hive数据类型
1、原生类型
– 原生类型 • TINYINT • SMALLINT • INT • BIGINT • BOOLEAN • FLOAT • DOUBLE • STRING • BINARY(Hive 0.8.0以上才可用) • TIMESTAMP(Hive 0.8.0以上才可用)
2、复合类型
– 复合类型 • Arrays:ARRAY<data_type> • Maps:MAP<primitive_type, data_type> ##复合类型 • Structs:STRUCT<col_name: data_type[COMMENT col_comment],……> • Union:UNIONTYPE<data_type, data_type,……>
五、Hive SQL — — Join in MR
INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age
FROM page_view pv
JOIN user u
ON (pv.userid = u.userid);
SELECT pageid, age, count(1)
FROM pv_users
GROUP BY pageid, age;
六、Hive的优化
• Map的优化:
– 作业会通过input的目录产生一个或者多个map任务。set dfs.block.size
– Map越多越好吗?是不是保证每个map处理接近文件块的大小?
– 如何合并小文件,减少map数?
set mapred.max.split.size=100000000; #100M set mapred.min.split.size.per.node=100000000; set mapred.min.split.size.per.rack=100000000; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
– 如何适当的增加map数?
set mapred.map.tasks=10;
– Map端聚合 hive.map.aggr=true 。 Mr中的Combiners.
• Reduce的优化:
• Reduce的优化: – hive.exec.reducers.bytes.per.reducer;reduce任务处理的数据量
– 调整reduce的个数: • 设置reduce处理的数据量 • set mapred.reduce.tasks=10
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 写成 select count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04';
Set mapred.reduce.tasks = 100 Create table a_standby_table as select * from a distribute by XXX
• 分区裁剪优化(partition):
– Where中的分区条件,会提前生效,不必特意做子查询,直接Join和GroupBy
• 笛卡尔积:
– join的时候不加on条件或者无效的on条件,Hive只能使用1个reducer来完成笛卡尔积
• Map join:
– /*+ MAPJOIN(tablelist) */,必须是小表,不要超过1G,或者50万条记录
• Union all:
– 先做union all再做join或group by等操作可以有效减少MR过程,尽管是多个Select,最终只有一个
mr
Union:有去重操作,会消耗系统性能
Union all:没有去重操作,
• Multi-insert & multi-group by:
– 从一份基础表中按照不同的维度,一次组合出不同的数据 – FROM from_statement – INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1 – INSERT OVERWRITE TABLE tablename2 [PARTITION(partcol2=val2 )] select_statement2 group by key2
• Automatic merge:
– 当文件大小比阈值小时,hive会启动一个mr进行合并 – hive.merge.mapfiles = true 是否和并 Map 输出文件,默认为 True – hive.merge.mapredfiles = false 是否合并 Reduce 输出文件,默认为 False – hive.merge.size.per.task = 256*1000*1000 合并文件的大小
• Multi-Count Distinct:
– 必须设置参数:set hive.groupby.skewindata=true; – select dt, count(distinct uniq_id), count(distinct ip) – from ods_log where dt=20170301 group by dt
• Hive的Join优化:
• 一个MR job
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (a.key = c.key1)
• 生成多个MR job
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
• Hive的Join优化----表连接顺序
• 按照JOIN顺序中的最后一个表应该尽量是大表,因为JOIN前一阶段生成的数据会存在于
Reducer的buffer中,通过stream最后面的表,直接从Reducer的buffer中读取已经缓冲的中间
结果数据(这个中间结果数据可能是JOIN顺序中,前面表连接的结果的Key,数据量相对较小,
内存开销就小),这样,与后面的大表进行连接时,只需要从buffer中读取缓存的Key,与大表
中的指定Key进行连接,速度会更快,也可能避免内存缓冲区溢出。
• 使用hint的方式启发JOIN操作
SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1); a表被视为大表
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key; MAPJION会把小表全部读入内存中,在map阶段直接 拿另外一个表的数据和内存中表数据做匹配,由于在 map是进行了join操作,省去了reduce运行的效率也 会高很多.
• 左连接时,左表中出现的JOIN字段都保留,右表没有连接上的都为空
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')
• 执行顺序是,首先完成2表JOIN,然后再通过WHERE条件进行过滤,这样在JOIN过程中可能会
输出大量结果,再对这些结果进行过滤,比较耗时。可以进行优化,将WHERE条件放在ON后
,在JOIN的过程中,就对不满足条件的记录进行了预先过滤。
• Hive的Join优化----并行执行
• 并行实行: – 同步执行hive的多个阶段,hive在执行过程,将一个查询转化成一个或者多个阶段。某个特 定的job可能包含众多的阶段,而这些阶段可能并非完全相互依赖的,也就是说可以并行执行 的,这样可能使得整个job的执行时间缩短。hive执行开启:set hive.exec.parallel=true
• Hive的Join优化----数据倾斜
• 操作
• Join
• Group by
• Count Distinct
• 原因
• key分布不均导致的
• 人为的建表疏忽
• 业务数据特点
• 症状
• 任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。
• 查看未完成的子任务,可以看到本地读写数据量积累非常大,通常超过10GB可以认定为发生数据倾斜。
• 倾斜度
• 平均记录数超过50w且最大记录数是超过平均记录数的4倍。
• 最长时长比平均时长超过4分钟,且最大时长超过平均时长的2倍。
• 万能方法
• hive.groupby.skewindata=true
• Hive的Join优化----数据倾斜----大小表关联
• 原因
• Hive在进行join时,按照join的key进行分发,而在join左边的表的数据会首先读入内存,如果左边表的key相对
分散,读入内存的数据会比较小,join任务执行会比较快;而如果左边的表key比较集中,而这张表的数据量很大,
那么数据倾斜就会比较严重,而如果这张表是小表,则还是应该把这张表放在join左边。
• 思路
• 将key相对分散,并且数据量小的表放在join的左边,这样可以有效减少内存溢出错误发生的几率
• 使用map join让小的维度表先进内存。
• 方法
• Small_table join big_table
• Hive的Join优化----数据倾斜----大大表关联
• 原因 • 日志中有一部分的userid是空或者是0的情况,导致在用user_id进行hash分桶的时候,会将日志中userid为0或者 空的数据分到一起,导致了过大的斜率。 • 思路 • 把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null值关联不上,处理后并不 影响最终结果。 • 方法 • on case when (x.uid = '-' or x.uid = '0‘ or x.uid is null) then concat('dp_hive_search',rand()) else x.uid end = f.user_id
七、Hive的搭建
1、Mysql配置
• 默认情况下,Hive的元数据信息存储在内置的Derby数据中。
• Hive支持将元数据存储在MySQL中
• 元数据存储配置:
– 【本地配置1】:默认
– 【本地配置2】:本地搭建mysql,通过localhost:Port方式访问
– 【远程配置】:远程搭建mysql,通过IP:Port方式访问
• 第一步:安装MySQL服务器端和MySQL客户端,并启动MySQL服务 • 安装: – yum install mysql – yum install mysql-server • 启动: – /etc/init.d/mysqld start • 设置用户名和密码: – mysqladmin -u root password '111111‘ • 测试登录是否成功: – mysql -uroot -p111111
2、安装Hive
①下载apache-hive-0.13.0-bin.tgz,并解压:
②在conf目录下,创建hive-site.xml配置文件:
<configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>111111</value> </property> </configuration>
③ 修改profile,配置环境变量:
④将mysql-connector-java-5.1.41-bin.jar拷贝到hive home的lib目录下,以支
持hive对mysql的操作
注意:
测试hive的前提得打开hadoop集群,start-all.sh
⑤hive在创建表的过程中,报如下错误处理:
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: org.apache.hadoop.ipc.RemoteException org.apache.hadoop.hdfs.server.namenode.SafeModeException: Cannot create directory /user/hive/warehouse/w_a. Name node is in safe mode.
处理方法:
bin/hadoop dfsadmin -safemode leave
关闭Hadoop的安全模式
⑥测试hive
hive> create EXTERNAL TABLE w_a( usrid STRING, age STRING, sex STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LINES TERMINATED BY ' '; #创建表 OK Time taken: 0.309 seconds hive> show tables; OK w_a Time taken: 0.049 seconds, Fetched: 1 row(s) hive> drop table w_a; #删除表 OK 导入数据: [root@master badou]# hive -f create_ex_table.sql #-f指定sql文件导入到hive中 Logging initialized using configuration in jar:file:/usr/local/src/apache-hive-0.13.0-bin/lib/hive-common-0.13.0.jar!/hive-log4j.properties OK Time taken: 1.168 seconds OK Time taken: 0.077 seconds 导入文本到hive: hive> LOAD DATA LOCAL INPATH '/home/ba/a.txt' OVERWRITE INTO TABLE w_a; #导入a.txt 文本到hive中 Copying data from file:/home/badou/a.txt Copying file: file:/home/badou/a.txt Failed with exception java.io.IOException: File /tmp/hive-root/hive_2019-04-28_05-20-33_879_4807090646149011006-1/-ext-10000/a.txt could only be replicated to 0 nodes, instead of 1
⑦
⑧
⑨
⑩
⑪