HIve面试题

1、行转列与列转行

1.1、行转列

select explode(split(‘aaa,bbb,ccc’,’,’))temp as x

–本身返回一张虚拟表，使用later view可直接关联两张表，保持原有映射关系。

静态：

select 
  t1.uid
  ,t2.key
  ,t2.value
from t1
later view explode(map(
	'c1',c1,
    'c2',c2,
    'c3',c3
)) t2 as key,value

select s.name,temp.x from s later view explode(split('score',',')) temp as x

1.2、列转行

• 将多行数据融为一行

select name,concat_ws(',',collect_set(score)) from score group by name

①、collect_set：将一列数据转化为一个集合；

②、concat_ws：将集合中所有元素以逗号分割链接为字符串。

str_to_map(concat_ws(','collect_set(':',key_clm,value_clm))))

select 
  uid
  ,kv['c1'] as c1
  ,kv['c2'] as c2
  ,kv['c3'] as c3
from(
  select 
    	uid    
    	,str_to_map(concat_ws(','collect_set(':',key_clm,value_clm)))) kv
  from vtable
  group by uid 
) temp

2、高级聚合函数

• 高级聚合函数：相当于group by加强

2.1、Grouping sets

–为自定义维度，根据需要分组即可

grouping sets((device_id),(os_id),(device_id,os_id),())<=>

group by device_id union all group by os_id union all  device_id,os_id

select 
  platform
  ,action
  ,count(guid)
  ,GROUPING__ID
  ,rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 20210303
group BY
  platform
  ,action
grouping sets((platform),(action),(platform,action),())

2.2、cube函数

• 分组组合最全，是各个维度值的笛卡尔（包含null）组合，

group by a,b,c with cube <=> grouping sets((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())

select 
  platform
  ,action
  ,count(guid)
  ,GROUPING_ID
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 20210303
group BY
  platform
  ,action
with cube

结果：

2.3、rollup函数

• 从右到做递减多级的统计

group by a,b,c with rollup <=> grouping sets((a,b,c),(a,b),(a),())

select 
  platform
  ,action
  ,count(guid)
  ,GROUPING_ID
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 20210303
group BY
  platform
  ,action
with rollup

3、拉链表实现

• 全量表回刷历史数据

  • 1）判断历史数据是否更新状态，如果更新则将end_date改为前一天时间，无更新不用变
  • 2）将增量数据union并把end_date写为「9999-12-31」表示为正在执行状态
  • 3）重写原拉链表

• 全量表数据：

SELECT 
	a.orderid,
    a.createtime,
	a.modifiedtime,
	a.status,
	a.dw_start_date,
	CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2016-08-21' THEN '2016-08-20' ELSE a.dw_end_date END AS** dw_end_date
FROM dw_orders_his a
left outer join (SELECT * FROM ods_orders_inc WHERE day = '2016-08-21') b
ON (a.orderid = b.orderid)

• 增量数据处理

SELECT orderid,
    createtime,
    modifiedtime,
    status,
	modifiedtime AS dw_start_date,
	'9999-12-31' AS dw_end_date
FROM ods_orders_inc
WHERE day = '2016-08-21'

4、hive和数据库的比较

hive和数据库除了拥有类似的查询语言外，再无类似之处；

4.1、数据存储位置

Hive存储在HDFS上;
数据库将数据保存在块设备或者本地文件中;

4.2、数据更新

Hive不建议对数据改写;
而数据库中的数据通常是要进行修改的

4.3、执行延迟

Hive执行延迟较高，数据库的执行延迟较低；

4.4、数据规模

Hive支持大规模的数据计算,数据库可以支持数据规模较小;

5、Hive字段的分隔符

• 问题：Hive里边字段的分隔符用的什么？为什么用\t？有遇到过字段里边有\t的情况吗，怎么处理的？

hive 默认的字段分隔符为ascii码的控制符\001（^A）,建表的时候用fields terminated by '\001'。
	注意:如果采用\t或者\001等为分隔符，需要要求前端埋点和javaEE后台传递过来的数据必须不能出现该分隔符，通过代码规范约束。一旦传输过来的数据含有分隔符，需要在前一级数据中转义或者替换（ETL）。
	否则会导致字段错位