11.2.2 Hive中创建表

Hive中创建结果表

hive中的表，执行HQL之后分析的结果保存该表，然后通过sqoop工具导出到mysql

xxxxxxxxxx
CREATE TABLE `stats_view_depth` (
  `platform_dimension_id` bigint ,
  `date_dimension_id` bigint , 
  `kpi_dimension_id` bigint , 
  `pv1` bigint , 
  `pv2` bigint , 
  `pv3` bigint , 
  `pv4` bigint , 
  `pv5_10` bigint , 
  `pv10_30` bigint , 
  `pv30_60` bigint , 
  `pv60_plus` bigint , 
  `created` string
) row format delimited fields terminated by '\t';

Hive中创建临时表

把hql分析之后的中间结果存放到当前的临时表。

xxxxxxxxxx
CREATE TABLE `stats_view_depth_tmp`(`pl` string, `day` string, `col` string, `ct` bigint);

pl平台、day日期、col列，值对应于mysql表中的列：pv1,pv2,pv3….

ct对应于每列的值

xxxxxxxxxx
from (select  u_ud,pl,from_unixtime(cast(s_time/1000 as bigint),'yyyy-MM-dd') day,
(case when count(p_url)==1 then 'pv1'
 when count(p_url)==2 then 'pv2'
 when count(p_url)==3 then 'pv3'
 when count(p_url)==4 then 'pv4'
 when count(p_url)>=5 and count(p_url)<10 then 'pv5_10'
 when count(p_url)>=10 and count(p_url)<30 then 'pv10_30'
 when count(p_url)>=30 and count(p_url)<60 then 'pv30_60'
 else 'pv60_plus' end) as pv
from event_logs
where en='e_pv'
and p_url is not null
and pl is not null
and s_time >= unix_timestamp('2022-01-20','yyyy-MM-dd')*1000
and s_time< unix_timestamp('2022-01-21','yyyy-MM-dd')*1000
group by pl,u_ud,from_unixtime(cast(s_time/1000 as bigint),'yyyy-MM-dd')) tmp
insert overwrite table stats_view_depth_tmp
select pl,day,pv,count(u_ud) ct
where u_ud is not null
group by pl,day,pv;

执行以上hql语句后，查询临时表：

xxxxxxxxxx
select pl,`day`,col,ct from stats_view_depth_tmp;

注意：如果列名是date等关键字避免出错加上了反引号。

如何将表stats_view_depth_tmp中的数据添加到stats_view_depth表，需要用到行转列。