Hive使用技巧总结

1.在Hive中可以使用正则表达式

set hive.support.quoted.identifiers=None; 
select a.pin, `(pin)?+.+` from Table

2.输出表数据时，显示列名

set hive.cli.print.header=true;

3.排序优化

order by全局排序，一个reduce实现，不能并行故效率偏低；
sort by部分有序，配合distribute by使用；
cluster by col1 == distribute by col1 sort by col1，但不能指定排序规则；

4.join优化

多表join的key值统一则可以归为一个reduce；
先过滤后join；
小表在前读入内存，大表在后；
使用left semi join 代替in功能，效率更高；
小表join大表时数据倾斜优化：

select t1.a,t1.b from table t1 join table2 t2  on ( t1.a=t2.a)
select /*+ mapjoin(t1)*/ t1.a,t1.b from table t1 join table2 t2  on ( t1.a=t2.a)

5.分区插入

静态插入:需要指定插入的分区dt，name的值；

insert overwrite table test partition (dt='2018-10-17', name='a') 
select col1, col2 from data_table where dt='2018-10-17' and  name='a';

动态插入：可以自动从列中匹配分区，查询时分区放在最后；

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test partition (dt, name) 
select col1, col2, dt, name from data_table where dt='2018-10-17';

6.抽样

---   随机排序后取前10个
select * from table1 distribute by rand() sort by rand() limit 10
select * from table1 order by rand() limit 10 --不可分布式，效率低
---   抽样函数
select * from table1 tablesample(50 PERCENT)
select * from table1 tablesample(1M)
select * from table1 tablesample(10 ROWS)
---   创建分桶表
create table bucketed_user(id int ,name string) 
clustered by (id)  sorted by (name)  --指定分桶列和排序列
into 4 buckets 
row format delimited fields terminated by '\t' stored as textfile;
---   向分桶表中插入数据
set hive.enforce.bucketing=true;（hive2.0好像没有这个参数）
insert overwrite rable bucketed_users select id,name from users ;
select * from bucketed_users tablesample(bucket 1 out of 2 on id);
#从1号bucket起抽取  2/总bucket个数  个bucket的数据

7、时间戳格式

十三位时间戳格式转化
from_unixtime(cast(substr(time_stamp,1,10) as bigint), 'yyyy-MM-dd')

8、行列转换

行转列

--  某一列中由多个元素
use cf_tmp; 
create table cf_tmp.table1 as 
select  1 as id, array(1,2,3) as col1, 'aa\;bb\;cc' as col2 ;
--  只有它一列时，可以直接行转列
select explode(col1) from table1 ;
select explode(splite(col2, '\;') ) from table1 ;
--  但当有其他列需要保留时，类似聚合
select id, tag 
from table1 
lateral view explode(split(col2, '\;')) num as tag ;

列转行

use cf_tmp; 
create table cf_tmp.table2 as  
select 1 as id, 'a' as col1 
union all 
select 2 as id, 'b' as col1  ;
--  去重合并
select id, concat_ws(',',collect_set(col1)) as tag
from table2 
group by id ;
--  不去重合并
select id, concat_ws(',',collect_list(col1)) as tag
from table2 
group by id ;

998.与presto的差异

presto具有严格的数据类型，在做比较时两侧类型必须严格相同，手动转换。

cast(a as bigint)>b
cast(6 as double)/4
6*1.0/4

时间函数的差异性

dt=cast(current_date+interval '-1' day as varchar)#昨天
dt=date_format(current_timestamp-interval '1' month, '%Y-%m-01')#上个月1日
dt=date_format(cast(date_format(current_timestamp , '%Y-%m-01') as date) - INTERVAL '1' DAY, '%Y-%m-%d') #上个月底

999.数据拆分行