常用HiveQL总结

最近在用Hive做多维数据分析,总结一些常用HiveQL命令。

1. 建表

以纯文本数据建表:

create table `dmp.dim_con_adx_id_name` (
	`adx_id` string comment 'ADX ID'
	, `adx_name` string comment 'ADX名称'
	, `update_dt` string comment '更新时间(天粒度)'
)
comment 'ADX的ID与名称映射表'
row format delimited 
fields terminated by ','
stored as textfile
;

若未指定为外部表(external table),则默认为托管表(managed table)。二者的区别在于load与drop操作:托管表用load data inpath加载数据(路径可为本地目录,也可是HDFS目录),该操作会将该文件放在HDFS目录:/user/hive/warehouse/ 下;而外部表的数据是在location中指定,一般配合partition描述数据的生成信息;drop托管表时会将元数据与/user/hive/warehouse/下的数据一起删掉,而drop外部表时只会删除元数据。将本地文件加载到托管表:

load data local inpath 'adx.csv' overwrite into table dmp.dim_con_adx_id_name;

以orc file数据建外部表表:

create external table `dmp.dwd_evt_ad_user_action_di` (
    `uid` string comment '用户ID'
    , `adx_name` string comment 'ADX名称'
    , `media_name` string comment '媒体名称'
    , `is_exposure` string comment '是否曝光'
    , `is_click` string comment '是否点击'
)
comment '广告用户点击天表'
partitioned by (dt string comment '天分区')
stored as orc
location '/<hdfs path>'
;

2. Partition

增加partition并指定location:

alter table dmp.dwd_evt_ad_user_action_di add if not exists partition (dt='20160520') location '20160520';

重新设置partition的location:

-- must be an absolute path
alter table dmp.dwd_evt_ad_user_action_di partition (dt='20160520') set location '<hdfs path>';  

删除partition

alter table dmp.dwd_evt_ad_user_action_di drop if exists partition (dt='20160520') ignore protection;

查看所有的paritition,以及查看某一partition的详细信息:

show partitions dwd_evt_ad_user_action_di;

describe formatted dwd_evt_ad_user_action_di partition (dt='20160520');

3. UDF

Hive的UDF非常丰富,基本能满足大部分的需求。

正则匹配获取相应字符串:

regexp_extract(dvc_model, '(.*)_(.*)', 2) as imei

复杂数据类型map、struct、指定schema的struct、array、union的构造如下:

map(key1, value1, key2, value2, ...)
struct(val1, val2, val3, ...)
named_struct(name1, val1, name2, val2, ...)
array(val1, val2, ...)
create_union(tag, val1, val2, ...)

获取复杂数据类型的某列值:

array: A[n]
map: M[key]
struct: S.x

条件判断case when,比如,在left join中指定默认值:

select uid
	, media
	, case 
		when b.tags is NULL then array(named_struct('tag','EMPTY', 'label','EMPTY')) 		else b.tags
	end as tags
from (
	select uid
    from dwd_evt_ad_user_action_di
    where dt = '{biz_dt}'
    	and is_exposure = '1'
) a
left outer join dwb_par_multi_user_tags_dd b 
on a.uid = b.uid;

4. UDTF

UDTF主要用来对复杂数据类型进行平铺操作,比如,explode平铺array与map,inline平铺array<struct>;这种内置的UDTF要与lateral view配合使用:

select myCol1, col2 FROM baseTable
lateral view explode(col1) myTable1 AS myCol1;

select uid
	, tag
	, label
from dwb_par_multi_user_tags_dd
lateral view inline(tags) tag_tb;
-- tags: array<struct<tag:string,label:string>>

5. 多维分析

Hive 提供grouping set、rollup、cube关键字进行多维数据分析,可以解决自定义的维度组合、上钻维度(\(n+1\)种)组合、所有的维度组合(\(2^n\)种)的需求。比如:

SELECT a, b, SUM( c ) 
FROM tab1 
GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )

-- equivalent aggregate query with group by
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
UNION
SELECT null, null, SUM( c ) FROM tab1


GROUP BY a, b, c, WITH ROLLUP 
-- is equivalent to 
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))


GROUP BY a, b, c WITH CUBE 
-- is equivalent to 
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))

此外,Hive还提供了GROUPING__ID函数对每一组合的维度进行编号,以区分该统计属于哪一维度组合,比如:

select adx_name, media_name, grouping__id, count(*) as pv
from dwd_evt_ad_user_action_di
group by adx_name, media_name with rollup;

以指定分隔符保存结果到本地目录:

explain
INSERT OVERWRITE LOCAL DIRECTORY '/home/<path>/<to>' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
select media_name, count(distinct uid) as uv
from dwd_evt_ad_user_action_di 
where day_time = '2016-05-20' 
	and is_exposure = '1'
group by media_name;
posted @ 2016-05-20 18:43  Treant  阅读(1973)  评论(0编辑  收藏  举报