IT工程師數位筆記本

HiveQL查詢變量(動態參數值)的使用，主要用hiveconf來進行變量值的獲取,格式如下：
變量賦值：
set pt_day='2016-12-31';
變量引用：
${hiveconf:pt_day} --> 用在Hql里

參考示例：

set pt_day='2016-12-31';drop table if exists xx_new_identifier;create table xx_new_identifier asselect a1.appsource,a1.appkey,a1.identifier,a1.pt_day from (select appsource,appkey,identifier,${hiveconf:pt_day} pt_day from (select appsource,appkey,identifier,row_number()over(partition by identifier order by appkey) rkfrom bi_all_access_logwhere pt_day = ${hiveconf:pt_day}) awhere rk=1) a1left join(select identifierfrom bi_all_access_logwhere pt_day < ${hiveconf:pt_day} ) a2 on a1.identifier=a2.identifierwhere a2.identifier is null;

set pt_week_his='2016#52';set pt_week_curr='2017#1';with tab_new_identifier_byWeek as (select appsource,appkey,identifier from xx_new_identifier where case when weekofyear(pt_day)>=52 and month(pt_day)=1 then concat(year(pt_day)-1,'#',weekofyear(pt_day)) else concat(year(pt_day),'#',weekofyear(pt_day)) end = ${hiveconf:pt_week_his}-- group by appsource,appkey,identifier),tab_access_log_byWeek as (select identifier from bi_all_access_logwhere case when weekofyear(pt_day)>=52 and month(pt_day)=1 then concat(year(pt_day)-1,'#',weekofyear(pt_day)) else concat(year(pt_day),'#',weekofyear(pt_day)) end = ${hiveconf:pt_week_curr}group by identifier)select a1.appsource,a1.appkey,count(a1.identifier) 留存設備數  from tab_new_identifier_byWeek a1inner join tab_access_log_byWeek a2 on a1.identifier=a2identifiergroup by a1.appsource,a1.appkey;

看文倉www.kanwencang.com網友整理上傳,為您提供最全的知識大全,期待您的分享，轉載請注明出處。
歡迎轉載：http://www.kanwencang.com/bangong/20170216/102230.html

文章列表

不含病毒。www.avast.com