四、Hive SQL
1.外部表和内部表
区别在于删除的时候只删除元数据,而内部表删除的时候连数据都给删除了
2.Hive DDL
定义:Hive的数据定义语言 (LanguageManual DDL)
1)建表语句
· 创建普通的表: create table abc( id int ) row format delimited fields terminated by ',' stored as textFile
· 创建带有分区的表:create table abc( id int ) partitioned by (dt String) row format delimited fields terminated by ','
· 创建外表:create external table abc( id int ) row format delimited fields terminated by ',' location'/home/hive/text.text';
3.Hive DML
定义:Hive数据操作语言(LanguageManual DML)
1)操作语句
HDFS上导入数据:load data inpath 'filePath' into table table_name;
· 从别的表中导入:insert into table table_name1 [patition(dt '.....', value)] select id,name from table_name2
· 多重数据插入:from table_name1 t1,table_name2 t2 insert overwrite table table_name3 [patition(col1=val1,col2=val2)] select t1.id, t2.id, .....................;
五、Hive优化
1.优化一:本地模式
· 开启本地模式 hive> set hive.exec.mode.local.auto=true
· 需要注意的是:hive.exec.mode.local.auto.inputbytes.max 这个参数默认 是128M,这个值表示了当加载文件的值大于这个值的时候,该配置仍 会以集 群来运行;默认就是集群运行的;当项目上线的时候开启; 使 用本地模式 的话,小数据小表可以避免提交时间的延迟
2.优化二:并行计算
· 开启并行计算 hive> set hive.exec.parallel=true
· 相关参数 hive.exec.parallel.thread.number(一次sql计算中允许并执行的 job 数量)
· 需要注意的是,并行计算会加大集群的压力
3.优化三:严格模式
· 开启严格模式 hive> set hive.mapred.mode = strict
· 主要是防止一群sql查询将集群压力大大增加
· 同时它也有一些限制:1、对于分区表,必须添加where对于分区字段的 条件过滤 1、orderby语句必须包含limit输出限制 3、限制执行笛卡尔积 查询
4.优化四:排序
· order by 对于查询结果做全排序,只允许有一个reduce处理
· 需要注意的是:当数据过大的时候谨慎使用,在严选模式下需要结合limit 来使用
· sort by 是对单个reduce的数据进行排序
· 只会在每个reducer 中对数据进行排序,也就是执行局部排序过程,只 能保证每个reducer的输出数据都是有序的(但并非全局有序)
· distribut by 是分区排序经常结合sort by一起使用
· cluster by 相当于distribut br + sort by
· cluster by 默认是倒序排序,不能用asc和desc来指定排序规则;可以通 过distribute by clumn sort by clumn asc|desc方式来指定排序方式
5.优化五:JOIN
· join时将小表放在join的左边
· mapjoin:在map端进行join(可以省略shuffle和reduce提高性能)
1)实现方式1:mapJoin标记
sql方式,在sql语句中添加mapjoin标记(mapjoin hint) select /*+mapjoin(smalltable)*/smalltable.key ,bigTable.value from smallTable join bigTable on smallTable.key=bigTable.key
2)实现方式2:开启自动的mapjoin
参数配置: 自动对小表进行mapjoin
hive> set hive.auto.convert.join=true
相关参数
· hive.mapjoin.smalltable.filesize这个值是大表和小表的判定阀值,小于这 个值就会被放入内存
· hive.ignore.mapjoin.hint 默认为true,是够忽略mapjoin hint
· hive.aotu.caonvert.join.noconditionaltask 默认为true,将普通的join转 换为mapjoin的时候是否将多个mapjoin转为一个mapjoin
· hive.aotu.caonvert.join.noconditionaltask.size 将多个mapjoin转为一个 mapjoin的最大值
6.优化六:聚合
开启map聚合 hive> set hive.map.aggr=true
相关参数
· hive.groupby.mapaggr.checkinterval: map端group by执行聚合时处理的多少行数据(默认:100000)
· hive.map.aggr.hash.min.reduction: 进行聚合的最小比例(预先对100000条数据做聚合,若聚合之后的数据量 /100000的值大于该配置0.5,则不会聚合)
· hive.map.aggr.hash.percentmemory:map端聚合使用的内存的最大值
· hive.map.aggr.hash.force.flush.memory.threshold: map端做聚合操作是hash表的最大可用内容,大于该值则会触发flush
· hive.groupby.skewindata :是否对GroupBy产生的数据倾斜做优化,默认为false
7.优化七:控制Hive中map和reduce的数量
Map数量相关的参数
· mapred.max.split.size:一个split的最大值,即每个map处理文件的最大值
· mapred.min.split.size.per.node:一个节点上split的最小值
· mapred.min.split.size.per.rack:一个机架上split的最小值
Reduce数量相关的参数
· mapred.reduce.tasks: 强制指定reduce任务的数量
· hive.exec.reducers.bytes.per.reducer 每个reduce任务处理的数据量
· hive.exec.reducers.max 每个任务最大的reduce数 [Map数量 >= Reduce数量 ]
8.优化八:JVM的重用
适用场景: 1、小文件个数过多 2、task个数过多
· 通过 set mapred.job.reuse.jvm.num.tasks=n; 来设置(n为task插槽个数)
缺点:设置开启之后,task插槽会一直占用资源,不论是否有task运行,直 到所有的task即整个job全部执行完成时,才会释放所有的task插槽资源!