Hive SQL高级操作指南-优快云博客

1.创建

//创建数据库
create database database_name

//创建表 列按‘,’分割
create table table_name(id int,name string) row format delimited fields terminated by ','

//创建分区表 列按‘,’分割 按bt和country分区
//create external table if not exists 判断表是否存在
create external table if not exists table_name(id int,name string)
partitioned by (dt String,country string)
row format delimited fields terminated by ','

//添加分区
alter table table_name add if not exists partition (dt='xxx',country 
='xxx')

2.查看

//查询所有数据库
show databases

//查询所有表
show tables

//查看表结构
desc formatted table_name

hbase映射表

3.加载数据

//local关键字 没有local关键字,则加载hdfs上的文件 有local关键字,则加载本地文件 
//overwrite关键字 覆盖数据(清空原数据)
//分区表，则加上partition分区
load data [local] inpath 'filepath' [overwrite] into table table_name [partition(part=val,part2=val2)]

//加载hdfs数据
load data inpath '/hdfsPath' into table table_name

//加载本地数据
load data load inpath '/path' into table table_name

4.查询

select * from table_name

//查询分区表 where 后面加分区条件，这样做是不会扫描全表 只会查询该分区
select * from table_name where dt='xxx'

5.删除，清空

//删除数据库
DROP DATABASE if EXISTS table_name

//删除表 
DROP TABLE if EXISTS table_name

//清空分区表 表结构还在，只清空数据
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2018-08',country='CH')

6.重命名

//重命名表名
ALTER TABLE table_name_old RENAME TO table_name_new

//重命名列名
ALTER TABLE table_name CHANGE col_old_name col_new_name STRING;

7.导出文件

//导出文件到hdfs上 按照‘,’分割 
//overwrite关键字 会覆盖目录下的原数据
INSERT OVERWRITE directory '/hdfsPath' row format delimited fields terminated by ','  SELECT * from table_name

8.无效ID在关联时的数据倾斜问题

解决方法：函数过滤 null

把空值的id变成一个字符串加上随机数，就能把倾斜的数据分到不同的Reduce上，从而解决数据倾斜问题。因为空值不参与关联，即使分到不同的 Reduce 上，也不会影响最终的结果。

SELECT * FROM tableName1 a LEFT OUTER 
JOIN tableName2 b ON 
CASE WHEN a.id IS NULL THEN CONCAT('hive_temp_id',RAND()) ELSE a.id END =b.user_id;

9.Hive不允许直接访问非group by字段

对于非group by字段，可以使用collect_set函数收集这些字段，返回一个数组；

SELECT a,b FROM tableName GROUP BY a	//Expression not in GROUP BY key 'b'

SELECT a,collect_set(b) FROM tableName GROUP BY a	//正确的使用方式

collect_set函数的其他用法：

使用数字下标，可以直接访问数组中的元素

10.控制map和reduce数量

控制map

set mapred.max.split.size=256000000;   //每个Map处理的最大输入文件大小(256MB)
set mapred.min.split.size.per.node=1;  //节点上split文件的最小值
set mapred.min.split.size.per.rack=1;  //机架上split文件的最小值

控制reduce

//方法一
set mapred.reduce.tasks=10;  //设置reduce的数量
//方法二
set hive.exec.reducers.bytes.per.reducer=1073741824 //每个reduce处理的数据量,默认1GB

10.hive join 优化的方式

map join

set hive.auto.convert.join=true //自动MAPJOIN转换操作（common join转换为map join

12.合并小文件

 hive.merge.mapfiles = true	//和并 Map 输出文件，默认为 True
 hive.merge.mapredfiles = false //合并 Reduce 输出文件，默认为 False
 hive.merge.size.per.task = 256*1000*1000 //合并文件的大小。

13.并行执行优化

set hive.exec.parallel=true; //开启任务并行执行
set hive.exec.parallel.thread.number=8; //同一个sql允许并行任务的最大线程数
set hive.groupby.skewindata=true; //设置数据负载均衡，防止数据倾斜
set hive.ignore.mapjoin.hint=false;	//不忽略MAPJOIN标记
set mapred.reduce.tasks=15;	//设置reduce的数量
set hive.exec.reducers.max=200;	//reduce最大数量(默认999)
set hive.auto.convert.join=false;	//自动MAPJOIN转换操作（common join转换为map join）
set hive.exec.compress.intermediate=true; //hive的压缩设置，中间结果压缩  
set hive.map.aggr = true;	//map中做部分聚集操作(默认true)
set hive.execution.engine=tez;	//更改hive底层执行的计算框架