Demo entry 6093190

111

   

Submitted by 1111111 on Sep 27, 2016 at 13:21
Language: MySQL. Code size: 1.8 kB.

##Description##
##-- Accounting job running on Murex Data Mart

##TaskInfo##
creator = '1449331 - Wang, Steven Zhikuan'

source = {
    'db': META['MUREXFDR'], ##-- Murex Data Mart database connection meta name
}

stream = {
    'column1,column2,column3,column4', ##-- Target table columns in sequence
}

target = {
    'db': META['FMETAL-ODS'], ##-- Target database connection meta name
    'table': 'ACCOUNTING', ##-- Target table name
}

##Load##
#if $isRELOAD
set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.dynamic.partition=true;

set hive.exec.max.dynamic.partition=10000;
set hive.exec.max.dynamic.partition.pernode=1000;
set hive.exec.reducers.max=1000;

set hive.exec.max.created.files=10000;
set hive.merge.mapfiles=true;
#end if

INSERT OVERWRITE TABLE `$target.table`
#if $isRELOAD
    PARTITION(dt)
#else
    PARTITION(dt = '$now.datekey')
#end if
select
    column1, column2, column3, avg(column5) as column4
from
    MUREXFDR.ACCOUNTING
where
#if $isRELOAD
    1 = 1
#elif $isDELTARELOAD
    dt >= '$now.datekey' AND dt < '$current.datekey'
#else
    dt='$now.datekey'
#end if
    and column4 >= 0
    and column1 != 0
    and column2 is not null
GROUP BY column1, column2, column3, dt

#if $isRELOAD or $isDELTARELOAD
distribute by dt
#end if


##TargetDDL##
##-- Target table creation
CREATE TABLE IF NOT EXISTS `$target.table`
(
    `column1` int    comment 'Testing column 1',
    `column2` string comment 'Testing column 2',
    `column3` double comment 'Testing column 3',
    `column1` float  comment 'Testing column 4',
) comment 'FMETAL ODS for Murex Accounting data'
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as orc;

This snippet took 0.00 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).