Hive中用参数变量在HQL中执行

Hive配置属性存储于 hiveconf 命名空间中，该命名空间中的属性是可读写的。在查询语句中插入 '${hiveconf:变量名}'，就可以通过 hive -hiveconf来替换变量。例如，查询语句和执行方式如下：

[root]$cat test.sql    #查看该文件
SELECT * FROM ${hiveconf:tablename}
limit ${hiveconf:var_rows};
[root]$hive -hiveconf tablename='t1' -hiveconf var_rows=10 -f test.sql

需要注意的是：
如果有多个变量，每个变量前都要有参数 -hiveconf
变量赋值等号左右不能有空格(例如var_rows=10不能有空格）

一般查询使用示例：

set forecast_month='2019-01';
set curr_date='2019-01-24';
--set forecast_month=substr(add_months(date_sub(CURRENT_DATE,1),i),1,7);

drop table if exists xxyl0123_pay_basic;
create table xxyl0123_pay_basic as
select pt_month,pt_day,sum(amount) pay_amount
from oss_bi_all_pay
where pt_month between substr(add_months(${hiveconf:curr_date},-15),1,7) and substr(${hiveconf:curr_date},1,7)
group by pt_month,pt_day
order by pt_month,pt_day;

select
${hiveconf:curr_date} calc_date,
${hiveconf:forecast_month} forecast_month,
sum(case when pt_month=substr(${hiveconf:curr_date},1,7) then pay_amount else 0 end) CM,
sum(IF((substr(${hiveconf:curr_date},1,7)=${hiveconf:forecast_month}) and (pt_day between concat(${hiveconf:forecast_month},'-01') and ${hiveconf:curr_date}),pay_amount,0)) PYD,
sum(case when pt_month=substr(add_months(${hiveconf:curr_date},-1),1,7) then pay_amount else 0 end) BM,
sum(case when pt_month=substr(add_months(${hiveconf:curr_date},-13),1,7) then pay_amount else 0 end) BBM,
sum(case when pt_month=substr(add_months(concat(${hiveconf:forecast_month},'-01'),-13),1,7) then pay_amount else 0 end) BYM,
sum(case when pt_month in(substr(add_months(${hiveconf:curr_date},-3),1,7),substr(add_months(${hiveconf:curr_date},-2),1,7),substr(add_months(${hiveconf:curr_date},-1),1,7)) then pay_amount else 0 end) TBM,
sum(case when pt_month in(substr(add_months(${hiveconf:curr_date},-15),1,7),substr(add_months(${hiveconf:curr_date},-14),1,7),substr(add_months(${hiveconf:curr_date},-13),1,7)) then pay_amount else 0 end) TBBM,
sum(case when pt_month in(substr(add_months(concat(${hiveconf:forecast_month},'-01'),-15),1,7),substr(add_months(concat(${hiveconf:forecast_month},'-01'),-14),1,7),substr(add_months(concat(${hiveconf:forecast_month},'-01'),-13),1,7)) then pay_amount else 0 end) TBYM
from xxyl0123_pay_basic
;