· 6 years ago · Jul 17, 2019, 08:40 AM
1val strCre = """CREATE TABLE IF NOT EXISTS merge_me
2(
3ODS_EFFECTIVE_FROM_DT STRING,
4ODS_EFFECTIVE_TO_DT STRING,
5ODS_IS_ACTIVE_FLG STRING,
6ID BIGINT
7)
8stored as carbondata
9location 'hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me'
10TBLPROPERTIES(
11'carbon.load.sort.scope'='GLOBAL_SORT'
12,'load_min_size_inmb'='128'
13,'TABLE_BLOCKSIZE'='256'
14,'TABLE_BLOCKLET_SIZE'='64'
15,'MAJOR_COMPACTION_SIZE'='1048576'
16,'COMPACTION_PRESERVE_SEGMENTS'='0'
17,'COMPACTION_LEVEL_THRESHOLD'='2,2'
18,'ALLOWED_COMPACTION_DAYS'='0'
19,'AUTO_LOAD_MERGE'='FALSE'
20,'FLAT_FOLDER'='TRUE'
21,'LOCAL_DICTIONARY_ENABLE'='FALSE'
22,'LOCAL_DICTIONARY_EXCLUDE'='ODS_EFFECTIVE_FROM_DT,ODS_EFFECTIVE_TO_DT,ODS_IS_ACTIVE_FLG'
23,'carbon.horizontal.compaction.enable'='TRUE'
24,'carbon.horizontal.update.compaction.threshold'='1'
25,'carbon.horizontal.delete.compaction.threshold'='1'
26,'tableName'='merge_me'
27,'path'='hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me'
28,'tablePath'='hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me'
29,'mapreduce.input.carboninputformat.databaseName'='
30,'mapreduce.input.carboninputformat.tableName'='merge_me'
31,'SORT_COLUMNS'='ID, ODS_EFFECTIVE_FROM_DT'
32,'SORT_SCOPE'='GLOBAL_SORT'
33)"""
34carbon.sql(strCre).show
35
36carbon.sql("""insert into merge_me
37select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288618 as ID UNION ALL
38select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288619 as ID UNION ALL
39select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288620 as ID UNION ALL
40select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288621 as ID UNION ALL
41select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288622 as ID UNION ALL
42select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288623 as ID UNION ALL
43select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288624 as ID UNION ALL
44select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288625 as ID UNION ALL
45select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288626 as ID UNION ALL
46select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288627 as ID UNION ALL
47select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288628 as ID UNION ALL
48select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288629 as ID UNION ALL
49select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288630 as ID UNION ALL
50select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288631 as ID UNION ALL
51select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288632 as ID UNION ALL
52select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288633 as ID UNION ALL
53select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288634 as ID UNION ALL
54select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288635 as ID UNION ALL
55select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288636 as ID UNION ALL
56select '2017-01-06 14:09:48' as ods_effective_from_dt, '5999-12-31 00:00:00' as ods_effective_to_dt, '1' as ODS_IS_ACTIVE_FLG, 57288637 as ID
57""")
58
59val strCre2 = """CREATE TABLE IF NOT EXISTS merge_me_t
60(
61ODS_EFFECTIVE_FROM_DT STRING,
62ODS_EFFECTIVE_TO_DT STRING,
63ODS_IS_ACTIVE_FLG STRING,
64ID BIGINT
65)
66stored as carbondata
67location 'hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me_t'
68TBLPROPERTIES(
69'carbon.load.sort.scope'='GLOBAL_SORT'
70,'load_min_size_inmb'='128'
71,'TABLE_BLOCKSIZE'='256'
72,'TABLE_BLOCKLET_SIZE'='64'
73,'MAJOR_COMPACTION_SIZE'='1048576'
74,'COMPACTION_PRESERVE_SEGMENTS'='0'
75,'COMPACTION_LEVEL_THRESHOLD'='2,2'
76,'ALLOWED_COMPACTION_DAYS'='0'
77,'AUTO_LOAD_MERGE'='FALSE'
78,'FLAT_FOLDER'='TRUE'
79,'LOCAL_DICTIONARY_ENABLE'='FALSE'
80,'LOCAL_DICTIONARY_EXCLUDE'='ODS_EFFECTIVE_FROM_DT,ODS_EFFECTIVE_TO_DT,ODS_IS_ACTIVE_FLG'
81,'carbon.horizontal.compaction.enable'='TRUE'
82,'carbon.horizontal.update.compaction.threshold'='1'
83,'carbon.horizontal.delete.compaction.threshold'='1'
84,'tableName'='merge_me_t'
85,'path'='hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me_t'
86,'tablePath'='hdfs://bda1-cluster-ns/data/ods/prod_stg/merge_me_t'
87,'mapreduce.input.carboninputformat.databaseName'='
88,'mapreduce.input.carboninputformat.tableName'='merge_me_t'
89,'SORT_COLUMNS'='ID, ODS_EFFECTIVE_FROM_DT'
90,'SORT_SCOPE'='GLOBAL_SORT'
91)"""
92carbon.sql(strCre2).show
93
94carbon.sql("insert into merge_me_t select * from merge_me where id <=57288627")
95
96
97
98/////////////////////////////////////////////////////////////////////////////////////
99
100
101carbon.sql(""" update merge_me a
102 set (ODS_EFFECTIVE_TO_DT) =
103 (select dt
104 from (select ID, (timestamp(min(ODS_EFFECTIVE_FROM_DT)) + INTERVAL -1 SECONDS) dt
105 from merge_me_t
106 group by ID) t
107 where a.ID = t.ID
108 and a.ODS_EFFECTIVE_TO_DT > t.dt)""").show
109
110carbon.sql("insert into merge_me select * from merge_me_t")