--#####################################################################
--程序说明
--filename: test.sql
--purpose: 程序模板
--author: blt
--parameters:
--${v_proc_name}: 程序名
--${v_month}: 统计月份 (yyyymm)
--${v_fm_dt}: 统计日期 (yyyymmdd)
--description 式例程序
--history
--date author version modifications
--2017-11-15 blt v01.00.000 程序模板
--#####################################################################
--#####################################################################
--设置参数
set hive.fetch.task.conversion=more;
set hive.cli.print.header=true;
set hive.exec.reducers.max=300;
set hive.exec.compress.output=false;
set hive.exec.compress.intermediate=true;
set mapred.max.split.size=1000000000;
set mapred.min.split.size.per.node=1000000000;
set mapred.min.split.size.per.rack=1000000000;
set hive.auto.convert.join=true;
set hive.groupby.skewindata=true;
set mapred.job.name=003_itp_bsl_cn06_gath;
set mapred.queue.name=vcs;
set mapred.job.queue.name=vcs;
set hive.exec.dynamic.partition.mode=nonstrict;
--#####################################################################
use test;
--step1.步骤1
drop table if exists itp_bsl_tab_tmp01;
create table itp_bsl_tab_tmp01 as
select a.asu_dept_code op_dept_code,
a.abc_dept_type_code op_dept_type,
a.level5_code op_area_code
from ods_test.abc_dim_dept_ng a
where to_date(a.fm_tm) <= '${v_fm_dt}'
and to_date(a.to_tm) >= '${v_fm_dt}';
--step02.步骤2
drop table if exists itp_bsl_tab_tmp02;
create table itp_bsl_tab_tmp02 as
select '${v_month}' month_code,
a.waybill_no,
nvl(a.abc_dept_code, 'N') op_dept_code,
nvl(b.op_dept_type, 'N') op_dept_type,
nvl(b.op_area_code, 'N') op_area_code,
a.bar_scan_tm
from ods_test.itp_bsl_op a
left join itp_bsl_tab_tmp01 b
on a.abc_dept_code = b.op_dept_code
where a.hq_month_code = '${v_month}'
and a.op_code in ('30', '830', '31', '831');
--step03.步骤3
insert overwrite table itp_bsl_tab partition
(hq_month_code = '${v_month}')
select a.month_code,
a.op_dept_code,
a.op_area_code,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') load_tm
from itp_bsl_tab_tmp02 a;
--#####################################################################
--程序说明
--filename: test.sql
--purpose: 程序模板
--author: blt
--parameters:
--${v_proc_name}: 程序名
--${v_month}: 统计月份 (yyyymm)
--${v_fm_dt}: 统计日期 (yyyymmdd)
--description 式例程序
--history
--date author version modifications
--2017-11-15 blt v01.00.000 程序模板
--#####################################################################
--#####################################################################
--设置参数
set hive.fetch.task.conversion=more;
set hive.cli.print.header=true;
set hive.exec.reducers.max=300;
set hive.exec.compress.output=false;
set hive.exec.compress.intermediate=true;
set mapred.max.split.size=1000000000;
set mapred.min.split.size.per.node=1000000000;
set mapred.min.split.size.per.rack=1000000000;
set hive.auto.convert.join=true;
set hive.groupby.skewindata=true;
set mapred.job.name=003_itp_bsl_cn06_gath;
set mapred.queue.name=vcs;
set mapred.job.queue.name=vcs;
set hive.exec.dynamic.partition.mode=nonstrict;
--#####################################################################
use test;
--step1.步骤1
drop table if exists itp_bsl_tab_tmp01;
create table itp_bsl_tab_tmp01 as
select a.asu_dept_code op_dept_code,
a.abc_dept_type_code op_dept_type,
a.level5_code op_area_code
from ods_test.abc_dim_dept_ng a
where to_date(a.fm_tm) <= '${v_fm_dt}'
and to_date(a.to_tm) >= '${v_fm_dt}';
--step02.步骤2
drop table if exists itp_bsl_tab_tmp02;
create table itp_bsl_tab_tmp02 as
select '${v_month}' month_code,
a.waybill_no,
nvl(a.abc_dept_code, 'N') op_dept_code,
nvl(b.op_dept_type, 'N') op_dept_type,
nvl(b.op_area_code, 'N') op_area_code,
a.bar_scan_tm
from ods_test.itp_bsl_op a
left join itp_bsl_tab_tmp01 b
on a.abc_dept_code = b.op_dept_code
where a.hq_month_code = '${v_month}'
and a.op_code in ('30', '830', '31', '831');
--step03.步骤3
insert overwrite table itp_bsl_tab partition
(hq_month_code = '${v_month}')
select a.month_code,
a.op_dept_code,
a.op_area_code,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') load_tm
from itp_bsl_tab_tmp02 a;
--#####################################################################
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31535951/viewspace-2153995/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31535951/viewspace-2153995/