大数据--hive6--实战训练之获取数据并插入到表中

目录

一：获取数据并且插入到表中

二.命名空间

三.通过set设置一些参数以及负载均衡总结

四.分区和concat_ws用法

五.cast和coalesce和case when 的用法

六.使用explode（）和group by配合使用进行分组

---

一：获取数据并且插入到表中

use namespace hehe;
// 名称
set mapred.job.name=v_haha_heheyoutubehehe_ads_people_kehuduan_experience_data_{DATE};

-- 以下选填
set hive.map.aggr=true;  //开启Map 端聚合参数设置
// 有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata=true;

insert overwrite table udw_ns.default.temp_table
partition(event_day='{DATE}',dim_type='heheyoutubehehe_ads_people_kehuduan_experience_data')

select 
concat_ws(
  '\t',
  cast('{DATE}' as string),
  cast(coalesce(os, '-') as string),
  cast(coalesce(soft_version, '-') as string),
  cast(coalesce(is_app_new, '-') as string),
  cast(coalesce(new_cnt, 0) AS string),
  cast(coalesce(is_open_cnt, 0) AS string),
  cast(coalesce(no_open_cnt, 0) AS string),
  cast(coalesce(disp_pv, 0) AS string),
  cast(coalesce(disp_uv, 0) AS string),
  cast(coalesce(agree_clk_uv, 0) AS string),
  cast(coalesce(disagree_clk_uv, 0) AS string),
  cast(coalesce(second_disp_pv, 0) AS string),
  cast(coalesce(second_disp_uv, 0) AS string),
  cast(coalesce(second_agree_clk_uv, 0) AS string),
  cast(coalesce(second_think_clk_uv, 0) AS string),
  cast(coalesce(third_disp_pv, 0) AS string),
  cast(coalesce(third_disp_uv, 0) AS string),
  cast(coalesce(third_check_agreement_clk_uv, 0) AS string),
  cast(coalesce(third_exit_clk_uv, 0) AS string),
) as dim_value
from(
  select 
  C.os as os,
  C.soft_version as soft_version,
  C.is_app_new as is_app_new,
  count(case when k = 'display' and v = 'privacy_pop' then b.cuid end) as disp_pv, -- 弹窗首次引导展现pv(验证）
  count(distinct case when k = 'display' and v = 'privacy_pop' then b.cuid end) as disp_uv, -- 弹窗首次引导展现uv(验证）
  count(distinct case when k = 'agree_clk' and v = 'privacy_pop' then b.cuid end) as agree_clk_uv, -- 弹窗首次引导「同意并继续」点击uv
  count(distinct case when k = 'disagree_clk' and v = 'privacy_pop' then b.cuid end) as disagree_clk_uv, -- 弹窗首次引导「不同意」点击uv
  count(case when k = 'display' and v = 'privacy_pop_again' then b.cuid end) as second_disp_pv, -- 弹窗二次挽留展现pv
  count(distinct case when k = 'display' and v = 'privacy_pop_again' then b.cuid end) as second_disp_uv, -- 弹窗二次挽留展现uv
  count(distinct case when k = 'agree_clk' and v = 'privacy_pop_again' then b.cuid end) a