Hive Study

发表于   |   分类于

本文用来记录自己在使用Hive Sql方面的一些经验。

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建了一个带有两个分区的表，这个表按照partition_date和hour进行分区
CREATE EXTERNAL TABLE `user.user_action`(
     `action` string COMMENT '{"chs_name":"", "description":"","etl":"","value":"","remark":""}',
     `num` double comment '{"chs_name":"", "description":"","etl":"","value":"","remark":""}'
  )
PARTITIONED BY ( `partition_date` string COMMENT '分区日期',  `hour` string COMMENT '小时')
ROW FORMAT DELIMITED
    --TODO: 导入MYSQL的表建议'\t'分隔
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY '\002'
    MAP KEYS TERMINATED BY '\003'
    LINES TERMINATED BY '\n'
STORED as textfile;

查询数据并将数据写入到表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT OVERWRITE TABLE user.user_action
partition(partition_date = '20180602', hour='0')
select action,
       sum(num) as n
  from (
        select action,
               num
          from (
               select momo_id, 
                      event_num_map 
                 from db.event_summary 
                where partition_date = '20180602'
                  and size(event_num_map)>0
          )a
          LATERAL VIEW EXPLODE(event_num_map)t AS action, num
    
  )b
  group by action;

该表从db.event_summary中查询数据然后吸入到user.user_action表中。需要注意db.event_summay中的event_num_map字段是一个map，map的key是action，value是action的数量。这里使用了一个函数LATERAL VIEW EXPLODE，用来map展开。

一些常用函数

ROW_NUMBER() OVER()函数

ROW_NUMBER() OVER()函数用来为每条记录返回一个行号，可以用来对记录进行排序并返回该序号，需要从1开始排序。
OVER()是一个聚合函数，可以对记录进行分组和排序。ROW_NUMBER()不能单独使用，必须搭配OVER()才能使用，否则会报错。

1
select *, row_number() over() as r from mytable;

配合partition by/order by
按照某个字段排序后返回行号

1
select *, row_number() over(partition by aaaaab order by num desc) r from mytable;

按照aaaaab分组后，并根据aaaaaab进行倒序排列。

SQL中的类型转换

需要使用cast()函数进行类型转换。

cast(str_column as int)

一些经验的总结

一个表中分时段记录内容的统一查询

需求

遇到的情况是这样的，有一个表A，表A中有24个字段（event_0_map … event_24_map）用来记录对应小时内每个用户各自发生的一些事情的数量。表结构如下：

1
2
3
4
5
id string = 1000010
event_0_map = {'event0':200, 'event2':100}
...
event_24_map = {'event0':500, 'event2':800}
partition = '20180101'

现在有一个需求：需要统计每个小时发生事件最多的前100个事件

分析

因为是每个小时执行的任务，而且每个小时的数据是存放在不同的字段里面，而字段名在SQL中是不可以拼接的，如：event_24_map，无法来拼接，因此有两种方案。

方案一

生成24个任务，每个任务的SQL都一样，只是查询的字段不一样

1
2
3
4
5
6
7
8
9
10
select action,
       num
  from (
       select id, 
              event_0_map 
         from online.tableA 
        where partition = '${partition_date}'
          and size(event_0_map)>0
  )a0
  LATERAL VIEW EXPLODE(event_0_map)t AS action, num

方案二(推荐)

将所有的字段同时解析，生成一个大表，再对大表进行过滤查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select action,
       num
  from (
		select action,
		       num,
		       '00' as hour
		  from (
		       select id, 
		              event_0_map 
		         from online.tableA 
		        where partition = '${partition_date}'
		          and size(event_0_map)>0
		  )a0
		  LATERAL VIEW EXPLODE(event_0_map)t AS action, num
		union all
		select action,
		       num,
		       '01' as hour
		  from (
		       select id, 
		              event_2_map 
		         from online.tableA 
		        where partition = '${partition_date}'
		          and size(event_2_map)>0
		  )a1
		  LATERAL VIEW EXPLODE(event_2_map)t AS action, num
	) data
 where hour = '${partition_hour}'

表的删除和恢复

在使用Hive的表的过程中，难免会有对表进行删除的情况，其实把表删除后，数据文件还是存在的，那么如何将数据按照新表的结构恢复一下呢？可以如下操作，但是需要注意的是，对于新增的字段，值是NULL。

1
2
3
4
5
drop table db.table_test;
...
create table xxx...
...
MSCK REPAIR TABLE db.table_test;