def d210(cf, source_output_path, STG_tables, Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
INS_DTTM = ",CURRENT_TIMESTAMP AS INS_DTTM \n"
MODIFICATION_TYPE_found = 0
stg_tables_df = funcs.get_stg_tables(STG_tables, None)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
Table_name = stg_tables_df_row['Table name']
create_stg_view = "REPLACE VIEW " + cf.v_stg + "." + Table_name + " AS LOCK ROW FOR ACCESS \n"
create_stg_view = create_stg_view + "SELECT\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, Table_name)
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name = STG_table_columns_row['Column name']
if Column_name == "MODIFICATION_TYPE":
MODIFICATION_TYPE_found = 1
comma = ',' if STG_table_columns_index > 0 else ' '
comma_Column_name = comma + Column_name
create_stg_view = create_stg_view + comma_Column_name + "\n"
if MODIFICATION_TYPE_found == 0:
MODIFICATION_TYPE = ",MODIFICATION_TYPE\n"
else:
MODIFICATION_TYPE = ""
create_stg_view = create_stg_view + MODIFICATION_TYPE + INS_DTTM
create_stg_view = create_stg_view + "from " + cf.T_STG + "." + Table_name + ";\n\n"
f.write(create_stg_view)
f.close()
def d415(cf, source_output_path, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
stg_tables_df = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name']
del_script = "DEL FROM " + cf.GCFR_V + ".GCFR_Transform_KeyCol "
del_script = del_script + " WHERE OUT_DB_NAME = '" + cf.SI_VIEW + "' AND OUT_OBJECT_NAME = '" + stg_table_name + "';\n"
STG_table_columns = funcs.get_stg_table_columns(STG_tables, None, stg_table_name, True)
exe_ = "EXEC " + cf.MACRO_DB + ".GCFR_Register_Tfm_KeyCol('" + cf.SI_VIEW + "'"
_p = ",'" + stg_table_name + "'"
_p = _p + ",'SEQ_NO' );\n\n"
exe_p = exe_ + _p
exe_p_ = ""
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows():
if STG_table_columns_row['PK'].upper() == 'Y':
Column_name = STG_table_columns_row['Column name']
_p = ",'" + stg_table_name + "'"
_p = _p + ",'" + Column_name + "' );\n"
exe_p_ = exe_p_ + exe_ + _p
exe_p = exe_p_ + "\n" if exe_p_ != "" else exe_p
f.write(del_script)
f.write(exe_p)
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name, traceback.format_exc()).log_error()
f.close()
def stgCounts(cf, source_output_path, System, STG_tables, LOADING_TYPE, flag):
file_name = funcs.get_file_name(__file__) + '_' + flag
f = funcs.WriteFile(source_output_path, file_name, "sql")
if flag == 'Accepted':
template_path = cf.templates_path + "/" + pm.compareSTGacccounts_template_filename
file_name += '_' + flag
else:
template_path = cf.templates_path + "/" + pm.compareSTGcounts_template_filename
smx_path = cf.smx_path
template_string = ""
try:
REJ_TABLE_NAME = System['Rejection Table Name']
except:
REJ_TABLE_NAME = ''
try:
REJ_TABLE_RULE = System['Rejection Table Rules']
except:
REJ_TABLE_RULE = ''
try:
source_DB = System['Source DB']
except:
source_DB = ''
try:
template_file = open(template_path, "r")
except:
template_file = open(smx_path, "r")
if LOADING_TYPE == 'ONLINE':
LOADING_TYPE = 'STG_ONLINE'
else:
LOADING_TYPE = 'STG_LAYER'
for i in template_file.readlines():
if i != "":
template_string = template_string + i
stg_table_names = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_table_names[
(stg_table_names['Table name'] != REJ_TABLE_NAME)
& (stg_table_names['Table name'] != REJ_TABLE_RULE)].iterrows():
TABLE_NAME = stg_tables_df_row['Table name']
TBL_PKs = TDDL.get_trgt_pk(STG_tables, TABLE_NAME)
if flag == 'Accepted':
output_script = template_string.format(
TABLE_NAME=TABLE_NAME,
STG_DATABASE=cf.T_STG,
source_DB=source_DB,
LOADING_TYPE=LOADING_TYPE,
REJ_TABLE_NAME=REJ_TABLE_NAME,
REJ_TABLE_RULE=REJ_TABLE_RULE,
TBL_PKs=TBL_PKs)
else:
output_script = template_string.format(TABLE_NAME=TABLE_NAME,
STG_DATABASE=cf.T_STG,
WRK_DATABASE=cf.t_WRK,
source_DB=source_DB)
seperation_line = '--------------------------------------------------------------------------------------------------------------------------------------------------------------------'
output_script = output_script.upper(
) + '\n' + seperation_line + '\n' + seperation_line + '\n'
f.write(output_script.replace('Â', ' '))
f.close()
def d400(cf, source_output_path, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
stg_tables_df = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
Table_name = stg_tables_df_row['Table name']
Fallback = ', Fallback' if stg_tables_df_row['Fallback'].upper(
) == 'Y' else ''
create_stg_table = "create multiset table " + cf.SI_DB + "." + Table_name + Fallback + "\n" + "(\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, Table_name, True)
pi_columns = ""
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name = STG_table_columns_row['Column name']
comma = ',' if STG_table_columns_index > 0 else ' '
comma_Column_name = comma + Column_name
Data_type = str(STG_table_columns_row['Data type'])
character_set = " CHARACTER SET UNICODE NOT CASESPECIFIC " if "CHAR" in Data_type.upper(
) or "VARCHAR" in Data_type.upper() else ""
not_null = " not null " if STG_table_columns_row['Mandatory'].upper(
) == 'Y' or STG_table_columns_row['PK'].upper() == 'Y' else " "
create_stg_table = create_stg_table + comma_Column_name + " " + Data_type + character_set + not_null + "\n"
if STG_table_columns_row['PK'].upper() == 'Y':
pi_columns = pi_columns + ',' + Column_name if pi_columns != "" else Column_name
extra_columns = ",Start_Ts\tTIMESTAMP(6) WITH TIME ZONE \n" \
+ ",End_Ts\tTIMESTAMP(6) WITH TIME ZONE \n"\
+ ",Start_Date\tDATE FORMAT 'YYYY-MM-DD' \n" \
+ ",End_Date\tDATE FORMAT 'YYYY-MM-DD' \n" \
+ ",Record_Deleted_Flag\tBYTEINT\n" \
+ ",Ctl_Id\tSMALLINT COMPRESS(997)\n" \
+ ",File_Id\tSMALLINT\n" \
+ ",Process_Name\tVARCHAR(128)\n" \
+ ",Process_Id\tINTEGER\n" \
+ ",Update_Process_Name\tVARCHAR(128) \n" \
+ ",Update_Process_Id\tINTEGER\n"
if pi_columns == "":
pi_columns = "SEQ_NO"
seq_column = ",SEQ_NO DECIMAL(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY\n\t (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE)\n"
else:
seq_column = ""
Primary_Index = ")Primary Index (" + pi_columns + ")"
create_stg_table = create_stg_table + extra_columns + seq_column + Primary_Index
create_stg_table = create_stg_table + ";\n\n"
f.write(create_stg_table)
f.close()
def d215(cf, source_output_path, source_name, System, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
smx_path = cf.smx_path
template_path = cf.templates_path + "/" + pm.D215_template_filename
template_string = ""
try:
REJ_TABLE_NAME = System['Rejection Table Name']
except:
REJ_TABLE_NAME = ''
try:
REJ_TABLE_RULE = System['Rejection Table Rules']
except:
REJ_TABLE_RULE = ''
try:
source_DB = System['Source DB']
except:
source_DB = ''
try:
template_file = open(template_path, "r")
except:
template_file = open(smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
stg_table_names = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_table_names[(stg_table_names['Table name'] != REJ_TABLE_NAME) & (stg_table_names['Table name'] != REJ_TABLE_RULE)].iterrows():
TABLE_NAME = stg_tables_df_row['Table name']
TABLE_COLUMNS = funcs.get_stg_table_columns(STG_tables, source_name, TABLE_NAME)
TBL_PKs = TDDL.get_trgt_pk(STG_tables, TABLE_NAME)
STG_TABLE_COLUMNS = ""
WRK_TABLE_COLUMNS = ""
lengthh = len(TABLE_COLUMNS)
for stg_tbl_index, stg_tbl_row in TABLE_COLUMNS.iterrows():
align = '' if stg_tbl_index >= lengthh - 1 else '\n\t'
STG_TABLE_COLUMNS += 'STG_TBL.' + '"' + stg_tbl_row['Column name'] + '"' + ',' + align
WRK_TABLE_COLUMNS += 'WRK_TBL.' + '"' + stg_tbl_row['Column name'] + '"' + ',' + align
output_script = template_string.format(TABLE_NAME=TABLE_NAME,
STG_TABLE_COLUMNS=STG_TABLE_COLUMNS,
WRK_TABLE_COLUMNS=WRK_TABLE_COLUMNS,
STG_DATABASE=cf.T_STG,
WRK_DATABASE=cf.t_WRK,
STG_VDATABASE=cf.v_stg,
REJ_TABLE_NAME=REJ_TABLE_NAME,
REJ_TABLE_RULE=REJ_TABLE_RULE,
TBL_PKs=TBL_PKs,
source_DB=source_DB
)
output_script = output_script.upper() + '\n' + '\n' + '\n'
f.write(output_script.replace('Â', ' '))
f.close()
def d110(cf, source_output_path, stg_Table_mapping, STG_tables, Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
source_t = cf.online_source_t if Loading_Type == "ONLINE" else cf.offline_source_t
if cf.staging_view_db == '':
source_v = cf.online_source_v if Loading_Type == "ONLINE" else cf.offline_source_v
else:
source_v = cf.staging_view_db
stg_tables_df = funcs.get_stg_tables(STG_tables, None)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
Table_name = stg_tables_df_row['Table name']
try:
where_clause = "\nWhere "
where_clause = where_clause + str(stg_Table_mapping.loc[
(stg_Table_mapping['Mapping name']
== Table_name)]['Filter criterion'].values[0])
where_clause = where_clause.replace("#SRC#", source_t)
except:
where_clause = ""
for stg_Table_mapping_index, stg_Table_mapping_row in stg_Table_mapping.iterrows(
):
if stg_Table_mapping_row['Mapping name'] == Table_name:
if stg_Table_mapping_row['Source layer'] == 'MATCHING':
source_t = cf.db_prefix + 'V_ANALYTICS'
create_stg_view = "REPLACE VIEW " + source_v + "." + Table_name + " AS LOCK ROW FOR ACCESS \n"
create_stg_view = create_stg_view + "SELECT\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, Table_name)
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name_as_src = '"' + STG_table_columns_row[
'Column name in source'] + '"'
Column_name = '"' + STG_table_columns_row['Column name'] + '"'
column_transformation_rule = str(
STG_table_columns_row['Column Transformation Rule'])
# Column_name = (column_transformation_rule + " AS " + Column_name) if column_transformation_rule != "" else Column_name
if column_transformation_rule != "":
Column_name = (column_transformation_rule + " AS " +
Column_name)
else:
Column_name = (Column_name_as_src + " AS " + Column_name)
comma = ',' if STG_table_columns_index > 0 else ' '
comma_Column_name = comma + Column_name
create_stg_view = create_stg_view + comma_Column_name + "\n"
create_stg_view = create_stg_view + "from " + source_t + "." + Table_name + " t " + where_clause + ";\n\n"
f.write(create_stg_view)
f.close()
def d410(cf, source_output_path, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
stg_tables_df = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name']
script = "REPLACE VIEW " + cf.SI_VIEW + "." + stg_table_name + " AS\n"
script = script + "SELECT * FROM " + cf.SI_DB + "." + stg_table_name + ";\n\n"
f.write(script)
f.close()
def d001(cf, source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
f.write("delete from " + cf.GCFR_t + "." + cf.SOURCE_TABLES_LKP_table +
" where SOURCE_NAME = '" + source_name + "';\n\n")
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name=None)
for STG_tables_index, STG_tables_row in stg_tables_df.iterrows():
Table_name = STG_tables_row['Table name']
f.write("insert into " + cf.GCFR_t + "." + cf.SOURCE_TABLES_LKP_table +
"(SOURCE_NAME, TABLE_NAME)\n")
f.write("VALUES ('" + source_name + "', '" + Table_name + "')" + ";\n")
f.write("\n")
f.close()
def d410(cf, source_output_path, STG_tables):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
stg_tables_df = funcs.get_stg_tables(STG_tables)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name']
script = "REPLACE VIEW " + cf.SI_VIEW + "." + stg_table_name + " AS\n"
script = script + "SELECT * FROM " + cf.SI_DB + "." + stg_table_name + ";\n\n"
f.write(script)
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def dataValidation(cf, source_output_path, source_name, System, STG_tables,
LOADING_TYPE, flag):
file_name = funcs.get_file_name(__file__) + '_' + flag
f = funcs.WriteFile(source_output_path, file_name, "sql")
if flag == 'Accepted':
template_path = cf.templates_path + "/" + pm.dataValidation_template_filename
file_name += '_' + flag
else:
template_path = cf.templates_path + "/" + pm.dataValidationAll_template_filename
smx_path = cf.smx_path
template_string = ""
try:
REJ_TABLE_NAME = System['Rejection Table Name']
except:
REJ_TABLE_NAME = ''
try:
REJ_TABLE_RULE = System['Rejection Table Rules']
except:
REJ_TABLE_RULE = ''
try:
source_DB = System['Source DB']
except:
source_DB = ''
try:
template_file = open(template_path, "r")
except:
template_file = open(smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
stg_table_names = funcs.get_stg_tables(STG_tables)
if LOADING_TYPE == 'ONLINE':
LOADING_TYPE = 'STG_ONLINE'
else:
LOADING_TYPE = 'STG_LAYER'
for stg_tables_df_index, stg_tables_df_row in stg_table_names[
(stg_table_names['Table name'] != REJ_TABLE_NAME)
& (stg_table_names['Table name'] != REJ_TABLE_RULE)].iterrows():
TABLE_NAME = stg_tables_df_row['Table name']
TABLE_COLUMNS = funcs.get_stg_table_columns(STG_tables, source_name,
TABLE_NAME)
TBL_PKs = TDDL.get_trgt_pk(STG_tables, TABLE_NAME)
IBM_STG_TABLE_COLUMNS = ""
TERADATA_STG_TABLE_COLUMNS = ""
TERADATA_WRK_TABLE_COLUMNS = ""
COUNT_COLS = ''
lengthh = len(TABLE_COLUMNS)
for stg_tbl_index, stg_tbl_row in TABLE_COLUMNS.iterrows():
align = '' if stg_tbl_index >= lengthh - 1 else '\n\t\t'
STGalign = '' if stg_tbl_index >= lengthh - 1 else '\n\t\t\t'
IBM_STG_TABLE_COLUMNS += 'IBM_STG_TABLE.' + '"' + stg_tbl_row[
'Column name'] + '"' + ',' + align
TERADATA_STG_TABLE_COLUMNS += 'TERADATA_STG_TABLE.' + '"' + stg_tbl_row[
'Column name'] + '"' + ',' + STGalign
TERADATA_WRK_TABLE_COLUMNS += 'TERADATA_WRK_TABLE.' + '"' + stg_tbl_row[
'Column name'] + '"' + ',' + STGalign
COUNT_COLS += str(stg_tbl_index + 1) + ','
COUNT_COLS = COUNT_COLS[0:len(COUNT_COLS) - 1]
IBM_STG_TABLE_COLUMNS = IBM_STG_TABLE_COLUMNS[
0:len(IBM_STG_TABLE_COLUMNS) - 1]
TERADATA_STG_TABLE_COLUMNS = TERADATA_STG_TABLE_COLUMNS[
0:len(TERADATA_STG_TABLE_COLUMNS) - 1]
TERADATA_WRK_TABLE_COLUMNS = TERADATA_WRK_TABLE_COLUMNS[
0:len(TERADATA_WRK_TABLE_COLUMNS) - 1]
if flag == 'Accepted':
output_script = template_string.format(
TABLE_NAME=TABLE_NAME,
IBM_STG_TABLE_COLUMNS=IBM_STG_TABLE_COLUMNS,
TERADATA_STG_TABLE_COLUMNS=TERADATA_STG_TABLE_COLUMNS,
STG_DATABASE=cf.T_STG,
REJ_TABLE_NAME=REJ_TABLE_NAME,
REJ_TABLE_RULE=REJ_TABLE_RULE,
TBL_PKs=TBL_PKs,
source_DB=source_DB,
LOADING_TYPE=LOADING_TYPE,
COUNT_COLS=COUNT_COLS)
else:
output_script = template_string.format(
TABLE_NAME=TABLE_NAME,
IBM_STG_TABLE_COLUMNS=IBM_STG_TABLE_COLUMNS,
TERADATA_STG_TABLE_COLUMNS=TERADATA_STG_TABLE_COLUMNS,
STG_DATABASE=cf.T_STG,
REJ_TABLE_NAME=REJ_TABLE_NAME,
REJ_TABLE_RULE=REJ_TABLE_RULE,
TBL_PKs=TBL_PKs,
source_DB=source_DB,
LOADING_TYPE=LOADING_TYPE,
COUNT_COLS=COUNT_COLS,
WRK_DATABASE=cf.t_WRK,
TERADATA_WRK_TABLE_COLUMNS=TERADATA_WRK_TABLE_COLUMNS)
seperation_line = '--------------------------------------------------------------------------------------------------------------------------------------------------------------------'
output_script = output_script.upper(
) + '\n' + seperation_line + '\n' + seperation_line + '\n'
f.write(output_script.replace('Â', ' '))
f.close()
def d200(cf, source_output_path, STG_tables, Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
MODIFICATION_TYPE_found = 0
INS_DTTM = ",INS_DTTM TIMESTAMP(6) NOT NULL \n"
try:
stg_tables_df = funcs.get_stg_tables(STG_tables, None)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
Table_name = stg_tables_df_row['Table name']
Fallback = ', Fallback' if stg_tables_df_row['Fallback'].upper(
) == 'Y' else ''
create_stg_table = "create multiset table " + cf.T_STG + "." + Table_name + Fallback + "\n" + "(\n"
create_wrk_table = "create multiset table " + cf.t_WRK + "." + Table_name + Fallback + "\n" + "(\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, Table_name, False)
pi_columns = ""
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name = STG_table_columns_row['Column name']
if Column_name == "MODIFICATION_TYPE":
MODIFICATION_TYPE_found = 1
comma = ',' if STG_table_columns_index > 0 else ' '
comma_Column_name = comma + Column_name
Data_type = str(STG_table_columns_row['Data type'])
character_set = " CHARACTER SET UNICODE NOT CASESPECIFIC " if "CHAR" in Data_type.upper(
) or "VARCHAR" in Data_type.upper() else ""
not_null = " not null " if STG_table_columns_row[
'Mandatory'].upper() == 'Y' or STG_table_columns_row[
'PK'].upper() == 'Y' else " "
create_stg_table = create_stg_table + comma_Column_name + " " + Data_type + character_set + not_null + "\n"
create_wrk_table = create_wrk_table + comma_Column_name + " " + Data_type + character_set + "\n"
if STG_table_columns_row['PK'].upper() == 'Y':
pi_columns = pi_columns + ',' + Column_name if pi_columns != "" else Column_name
wrk_extra_columns = ",REJECTED INTEGER\n" + ",BATCH_LOADED INTEGER\n" + ",NEW_ROW INTEGER\n"
if pi_columns == "":
pi_columns = "SEQ_NO"
seq_column = ",SEQ_NO DECIMAL(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY\n\t (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE)\n"
else:
seq_column = ""
Primary_Index = ")Primary Index (" + pi_columns + ")"
if MODIFICATION_TYPE_found == 0 and Loading_Type == "OFFLINE_CDC":
MODIFICATION_TYPE = ",MODIFICATION_TYPE char(1) CHARACTER SET UNICODE NOT CASESPECIFIC not null\n"
else:
MODIFICATION_TYPE = ""
create_stg_table = create_stg_table + MODIFICATION_TYPE + INS_DTTM + seq_column + Primary_Index
create_wrk_table = create_wrk_table + MODIFICATION_TYPE + INS_DTTM + wrk_extra_columns + seq_column + Primary_Index
create_stg_table = create_stg_table + ";\n\n"
create_wrk_table = create_wrk_table + ";\n\n"
f.write(create_stg_table)
f.write(create_wrk_table)
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def d200(cf, source_output_path, STG_tables, Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
MODIFICATION_TYPE_found = 0
Data_mover_flag = cf.Data_mover_flag
INS_DTTM = ",INS_DTTM TIMESTAMP(6) NOT NULL \n"
stg_tables_df = funcs.get_stg_tables(STG_tables, None)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
Table_name = stg_tables_df_row['Table name']
Fallback = ', Fallback' if stg_tables_df_row['Fallback'].upper(
) == 'Y' else ''
create_stg_table = "create multiset table " + cf.T_STG + "." + Table_name + Fallback + "\n" + "(\n"
create_wrk_table = "create multiset table " + cf.t_WRK + "." + Table_name + Fallback + "\n" + "(\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, Table_name, False)
pi_columns = ""
MODIFICATION_TYPE_found = funcs.table_has_modification_type_column(
STG_tables, Table_name)
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name = STG_table_columns_row['Column name']
comma = ',' if STG_table_columns_index > 0 else ' '
comma_Column_name = comma + Column_name
Data_type = str(STG_table_columns_row['Data type'])
character_set = " CHARACTER SET UNICODE NOT CASESPECIFIC " if "CHAR" in Data_type.upper(
) or "VARCHAR" in Data_type.upper() else ""
not_null = " not null " if STG_table_columns_row['Mandatory'].upper(
) == 'Y' or STG_table_columns_row['PK'].upper() == 'Y' else " "
create_stg_table = create_stg_table + comma_Column_name + " " + Data_type + character_set + not_null + "\n"
create_wrk_table = create_wrk_table + comma_Column_name + " " + Data_type + character_set + "\n"
if STG_table_columns_row['PK'].upper() == 'Y':
pi_columns = pi_columns + ',' + Column_name if pi_columns != "" else Column_name
wrk_extra_columns = ",REJECTED INTEGER\n" + ",BATCH_LOADED INTEGER\n" + ",NEW_ROW INTEGER\n"
if pi_columns == "":
pi_columns = "SEQ_NO"
seq_column = ",SEQ_NO DECIMAL(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY\n\t (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE)\n"
else:
seq_column = ""
Primary_Index = ")Primary Index (" + pi_columns + ")\n"
if MODIFICATION_TYPE_found == 0:
MODIFICATION_TYPE = ",MODIFICATION_TYPE char(1) CHARACTER SET UNICODE NOT CASESPECIFIC not null\n"
else:
MODIFICATION_TYPE = ""
if Data_mover_flag == 1:
Run_date_column = ", RUN_DATE TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP\n"
partition_statement = "PARTITION BY RANGE_N(RUN_DATE BETWEEN TIMESTAMP '2020-03-03 00:00:00.000000+00:00' AND TIMESTAMP '2100-03-03 00:00:00.000000+00:00' EACH INTERVAL'1'DAY)\n"
else:
Run_date_column = ""
partition_statement = ""
create_stg_table = create_stg_table + MODIFICATION_TYPE + INS_DTTM + seq_column + Run_date_column + Primary_Index + partition_statement
create_wrk_table = create_wrk_table + MODIFICATION_TYPE + INS_DTTM + wrk_extra_columns + seq_column + Run_date_column + Primary_Index + partition_statement
create_stg_table = create_stg_table + ";\n\n"
create_wrk_table = create_wrk_table + ";\n\n"
f.write(create_stg_table)
f.write(create_wrk_table)
f.close()
def d420(cf, source_output_path, STG_tables, BKEY, BMAP, Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
separator = pm.stg_cols_separator
stg_tables_df = funcs.get_stg_tables(STG_tables)
bmap_physical_table = "BMAP_STANDARD_MAP"
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name'].upper()
stg_Natural_key_df = STG_tables.loc[
(STG_tables['Table name'].str.upper() == stg_table_name)
& (STG_tables['Natural key'] != "")]
Natural_key_list = []
for stg_Natural_key_df_index, stg_Natural_key_df_row in stg_Natural_key_df.iterrows(
):
Natural_key_split = str(
stg_Natural_key_df_row['Natural key']).split(separator)
for i in Natural_key_split:
Natural_key_list.append(i.upper())
# Natural_key_list_str = funcs.list_to_string(Natural_key_list, ',').upper()
stg_table_has_pk = True if len(STG_tables.loc[
(STG_tables['Table name'].str.upper() == stg_table_name)
& (STG_tables['PK'].str.upper() == 'Y')].index) > 0 else False
if not stg_table_has_pk:
seq_pk_col = " SEQ_NO\n,"
else:
seq_pk_col = " "
create_view = "REPLACE VIEW " + cf.SI_VIEW + "." + stg_table_name + " AS LOCK ROW FOR ACCESS\nSELECT \n"
from_clause = "FROM " + cf.v_stg + "." + stg_table_name + " t"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, None, stg_table_name, True)
bkeys_query = ""
bkeys_left_join_count = 0
bmap_query = ""
bmap_left_join_count = 0
normal_columns = ""
bkey_columns = ""
bmap_columns = ""
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
comma = ',' if STG_table_columns_index > 0 else seq_pk_col
Column_name = STG_table_columns_row['Column name'].upper()
Natural_key = str(STG_table_columns_row['Natural key']).upper()
alias = Column_name
Column_name = "t." + Column_name
for i in list(set(Natural_key_list)):
i = i.replace(" ", "")
if alias == i or "COALESCE(" + alias + ",'')" == i:
if "COALESCE" in i:
Column_name = "COALESCE(" + Column_name + ",'')"
Column_name = "TRIM(Trailing '.' from TRIM(" + Column_name + ")) " + alias
if Natural_key == "":
comma_Column_name = comma + Column_name
normal_columns = normal_columns + comma_Column_name + "\n"
else:
trim_Natural_key = []
split_Natural_key = Natural_key.replace(
" ", "").split(separator)
for i in split_Natural_key:
trim_Natural_key.append(
"TRIM(Trailing '.' from TRIM(" + i.strip() + "))")
trimed_Natural_key = funcs.list_to_string(
trim_Natural_key, separator)
Key_domain_name = STG_table_columns_row[
'Key domain name'].upper()
if Key_domain_name != "":
BKEY_row = BKEY.loc[(BKEY['Key domain name'].str.upper(
) == Key_domain_name)]
if len(BKEY_row.index) > 0:
bkey_physical_table = BKEY_row[
'Physical table'].values[0]
bkey_domain_id = str(
int(BKEY_row['Key domain ID'].values[0]))
bkeys_left_join_count = bkeys_left_join_count + 1
bk_alias = " bk" + str(bkeys_left_join_count)
bkeys_query = "( Select " + bk_alias + ".EDW_Key\n"
bkeys_query = bkeys_query + "\tFrom " + cf.UTLFW_v + "." + bkey_physical_table + bk_alias + "\n"
bkeys_query = bkeys_query + "\tWhere " + bk_alias + ".Source_Key = " + trimed_Natural_key + "\n"
bkeys_query = bkeys_query + "\tand " + bk_alias + ".Domain_ID = " + bkey_domain_id + ")"
comma_Column_name = comma + bkeys_query + " AS " + alias
bkey_columns = bkey_columns + comma_Column_name + "\n"
Code_domain_name = STG_table_columns_row[
"Code domain name"].upper()
if Code_domain_name != "":
BMAP_row = BMAP.loc[(BMAP["Code domain name"].str.
upper() == Code_domain_name)]
if len(BMAP_row.index) > 0:
Code_set_ID = str(
int(BMAP_row["Code set ID"].values[0]))
Code_domain_ID = str(
int(BMAP_row["Code domain ID"].values[0]))
bmap_left_join_count = bmap_left_join_count + 1
bmap_alias = " bm" + str(bmap_left_join_count)
bmap_query = "( Select " + bmap_alias + ".EDW_Code\n"
bmap_query = bmap_query + "\tFrom " + cf.UTLFW_v + "." + bmap_physical_table + bmap_alias + "\n"
bmap_query = bmap_query + "\tWhere " + bmap_alias + ".Source_Code = " + trimed_Natural_key + "\n"
bmap_query = bmap_query + "\tand " + bmap_alias + ".Code_Set_id = " + Code_set_ID + "\n"
bmap_query = bmap_query + "\tand " + bmap_alias + ".Domain_ID = " + Code_domain_ID + ")"
comma_Column_name = comma + bmap_query + " AS " + alias
bmap_columns = bmap_columns + comma_Column_name + "\n"
modification_type = ",t.modification_type\n" if Loading_Type == "OFFLINE_CDC" else ""
normal_columns = normal_columns + modification_type
create_view_script = create_view + normal_columns + bkey_columns + bmap_columns + from_clause + ";\n"
f.write(create_view_script + "\n")
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
