def d630(cf, source_output_path, Table_mapping):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
for table_maping_index, table_maping_row in Table_mapping.iterrows(
): #& (source_name=='CRA')& (Table_mapping['Mapping name'] == 'L1_PRTY_RLTD_L0_CRA_COMPANY_PERSON')].iterrows():
process_type = table_maping_row['Historization algorithm']
layer = str(table_maping_row['Layer'])
table_maping_name = str(table_maping_row['Mapping name'])
tbl_name = table_maping_row['Target table name']
ctl_id = funcs.single_quotes(cf.gcfr_ctl_Id)
stream_key = funcs.single_quotes(cf.gcfr_stream_key)
process_name = "TXF_" + layer + "_" + table_maping_name
reg_exp = "EXEC " + cf.MACRO_DB + ".GCFR_Register_Process('" + process_name + "','',"
if process_type == "SNAPSHOT":
process_type_cd = cf.gcfr_snapshot_txf_process_type
else:
if process_type == 'INSERT':
process_type_cd = cf.gcfr_insert_txf_process_type
else:
process_type_cd = cf.gcfr_others_txf_process_type
process_type_cd = funcs.single_quotes(process_type_cd)
# print(process_type_cd)
reg_exp += process_type_cd + ',' + ctl_id + ',' + stream_key + ",'" + cf.INPUT_VIEW_DB + "','" + process_name + "_IN',"
reg_exp += "'" + cf.core_view + "','" + tbl_name + "','" + cf.core_table + "','" + tbl_name + "','" + cf.TMP_DB + "',,,,1,0,1,0);\n"
f.write(reg_exp)
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def get_select_clause(target_table, Core_tables, table_maping_name,
Column_mapping):
sel_clause = '\n'
for col_map_indx, col_map_row in Column_mapping[(
Column_mapping['Mapping name'] == table_maping_name)].iterrows():
sel_ready = ""
src_tbl = col_map_row['Mapped to table']
src_col = col_map_row['Mapped to column']
sql_const = str(col_map_row['Transformation rule'])
if sql_const.upper() == funcs.single_quotes("NULL"):
sql_const = "NULL"
trgt_col_data_type = get_column_data_type(Core_tables,
col_map_row['Column name'],
target_table)
src = ""
if src_tbl != "":
src = 'Cast (' + src_tbl + '.' + src_col + ' AS ' + trgt_col_data_type + ')'
if sql_const != "":
src = 'Cast (' + sql_const + ' AS ' + trgt_col_data_type + ')'
trgt_col = ' AS ' + col_map_row['Column name']
sel_ready = str(src) + str(trgt_col) + ',\n'
sel_clause = sel_clause + sel_ready
return sel_clause
def gcfr(cf, output_path):
file_name = "000_run_first_" + funcs.get_file_name(__file__).upper()
f = funcs.WriteFile(output_path, file_name, "sql")
try:
system_name = funcs.single_quotes(cf.gcfr_system_name)
stream_name = funcs.single_quotes(cf.gcfr_stream_name)
register_system = "exec " + cf.M_GCFR + ".GCFR_Register_System(" + str(
cf.gcfr_ctl_Id
) + ", " + system_name + ", '', " + system_name + ");"
register_stream = "call " + cf.P_UT + ".GCFR_UT_Register_Stream(" + str(
cf.gcfr_stream_key
) + ", 1, " + stream_name + ", cast('2019-01-01' as date));"
delete_parameters = "delete from " + str(
cf.GCFR_t) + ".PARAMETERS where PARAMETER_ID in (11, 7, 10);\n"
insert_into_parameters = "insert into " + str(
cf.GCFR_t) + ".PARAMETERS values "
insert_into_parameters = insert_into_parameters + "(11, 'LRD T DB', " + funcs.single_quotes(
cf.SI_DB) + ");\n"
insert_into_parameters = insert_into_parameters + "insert into " + str(
cf.GCFR_t) + ".PARAMETERS values "
insert_into_parameters = insert_into_parameters + "(7, 'INPUT V DB', " + funcs.single_quotes(
cf.INPUT_VIEW_DB) + ");\n"
insert_into_parameters = insert_into_parameters + "insert into " + str(
cf.GCFR_t) + ".PARAMETERS values "
insert_into_parameters = insert_into_parameters + "(10, 'BASE T DB', " + funcs.single_quotes(
cf.core_table) + ");\n"
# "11 LRD T DB GDEV1T_SRCI"
# "7 INPUT V DB GDEV1V_INP"
# "10 BASE T DB GDEV1T_BASE"
f.write(register_system + "\n")
f.write(register_stream + "\n\n")
f.write(delete_parameters + "\n")
f.write(insert_into_parameters + "\n")
except:
funcs.TemplateLogError(cf.output_path, output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def d320(cf, source_output_path, STG_tables, BKEY):
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 = STG_tables.loc[(STG_tables['Key domain name'] != "")
& (STG_tables['Natural key'] != "")]
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
key_domain_name = stg_tables_df_row['Key domain name']
stg_table_name = stg_tables_df_row['Table name']
stg_Column_name = stg_tables_df_row['Column name']
Bkey_filter = str(stg_tables_df_row['Bkey filter']).upper()
Bkey_filter = "WHERE " + Bkey_filter if Bkey_filter != "" and "JOIN" not in Bkey_filter else Bkey_filter
Bkey_filter = Bkey_filter + "\n" if Bkey_filter != "" else Bkey_filter
Natural_key_list = stg_tables_df_row['Natural key'].split(separator)
trim_Trailing_Natural_key_list = []
for i in Natural_key_list:
trim_Trailing_Natural_key_list.append("TRIM(Trailing '.' from TRIM(" + i.strip() + "))")
Source_Key = funcs.list_to_string(trim_Trailing_Natural_key_list, separator)
coalesce_count = Source_Key.upper().count("COALESCE")
separator_count = Source_Key.count(separator)
compare_string = funcs.single_quotes("_" * separator_count) if coalesce_count > separator_count else "''"
Source_Key_cond = "WHERE " if "WHERE" not in Bkey_filter else " AND "
Source_Key_cond = Source_Key_cond + "COALESCE(Source_Key,"+compare_string+") <> "+compare_string+" "
bkey_df = BKEY.loc[(BKEY['Key domain name'] == key_domain_name)]
Key_set_ID = str(int(bkey_df['Key set ID'].values[0]))
Key_domain_ID = str(int(bkey_df['Key domain ID'].values[0]))
script = "REPLACE VIEW " + cf.INPUT_VIEW_DB + ".BK_" + Key_set_ID + "_" + stg_table_name + "_" + stg_Column_name + "_" + Key_domain_ID + "_IN AS LOCK ROW FOR ACCESS\n"
script = script + "SELECT " + Source_Key + " AS Source_Key\n"
script = script + "FROM " + cf.v_stg + "." + stg_table_name + "\n"
script = script + Bkey_filter + Source_Key_cond + "\n"
script = script + "GROUP BY 1;" + "\n"
f.write(script)
f.write('\n')
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name, traceback.format_exc()).log_error()
f.close()
def d002(cf, source_output_path, Core_tables, Table_mapping):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
# Core_tables=TransformDDL.get_src_core_tbls(source_name, Core_tables, Table_mapping)
Table_mappings = Table_mapping
hist_key_insert_header = ""
history_tbl = cf.GCFR_t + "." + cf.history_tbl
hist_key_insert_header += "INSERT INTO " + history_tbl
hist_key_insert_header += "( TRF_TABLE_NAME,PROCESS_NAME,TABLE_NAME,RECORD_ID,START_DATE_COLUMN,END_DATE_COLUMN,HISTORY_COLUMN, HISTORY_KEY)\n"
hist_key_insert_header += "VALUES ('"
tbl_mapping_name = ""
process_name = ""
trgt_tbl = ""
start_date_column = ""
end_date_column = ""
history_key = ""
history_column = ""
for tbl_mapping_index, table_maping_row in Table_mappings[
Table_mappings['Historization algorithm'] ==
"HISTORY"].iterrows():
tbl_mapping_name = table_maping_row['Mapping name']
trgt_layer = table_maping_row['Layer']
process_name = "TXF_" + trgt_layer + "_" + tbl_mapping_name
trgt_tbl = table_maping_row['Target table name']
start_date_column = TransformDDL.get_core_tbl_sart_date_column(
Core_tables, trgt_tbl)
end_date_column = TransformDDL.get_core_tbl_end_date_column(
Core_tables, trgt_tbl)
# history_column_vals = table_maping_row ['Historization columns']
# history_column_list=pd.unique(list(history_column_vals)).split(',')
history_column_list = table_maping_row[
'Historization columns'].split(',')
history_column_list = [x.strip() for x in history_column_list]
history_key_list = TransformDDL.get_core_tbl_hist_keys_list(
Core_tables, trgt_tbl, history_column_list)
history_key_list = [x.strip() for x in history_key_list]
del_st = " DELETE FROM " + history_tbl + " WHERE PROCESS_NAME = '" + process_name + "';\n"
f.write(del_st)
f.write("--History_keys \n")
for hist_key in history_key_list:
hist_key_insert_st = process_name + "','" + process_name + "','" + trgt_tbl + "','" + tbl_mapping_name + "','" + start_date_column
hist_key_insert_st += "','" + end_date_column + "'," + "null,"
if hist_key != "undefined":
hist_key = funcs.single_quotes(hist_key)
hist_key_insert_st += hist_key + "); \n"
f.write(hist_key_insert_header)
f.write(hist_key_insert_st)
f.write("--History_columns \n")
# f.write(str(history_column_list))
# f.write(str(len(history_column_list)))
# f.write("\n")
for hist_col in history_column_list:
if hist_col == '':
hist_col = "undefined"
else:
hist_col = funcs.single_quotes(hist_col)
hist_col_insert_st = process_name + "','" + process_name + "','" + trgt_tbl + "','" + tbl_mapping_name + "','" + start_date_column
hist_col_insert_st += "','" + end_date_column + "'," + hist_col + "," + "null); \n"
f.write(hist_key_insert_header)
f.write(hist_col_insert_st)
f.write("\n \n")
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def source_testing_script(cf, source_output_path, source_name, Table_mapping, Column_mapping, STG_tables, BKEY):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
########################################
# TARGET_TABLE_NAME Table mapping done
# TABLE_MAPPING_NAME Table mapping done
# SOURCE_TABLE Table mapping done
# TARGET_COLUMN_NAME Column mapping done
# NATURAL_KEY STG tables done
# PHYSICAL_NAME BKEY done
# KEY_DOMAIN_ID_VALUE BKEY done
########################################
Table_mapping_df = Table_mapping[Table_mapping['Source'] == source_name][['Target table name', 'Mapping name', 'Main source']]
for Table_mapping_df_index, Table_mapping_df_row in Table_mapping_df.iterrows():
TARGET_TABLE_NAME = Table_mapping_df_row['Target table name']
TABLE_MAPPING_NAME = Table_mapping_df_row['Mapping name']
SOURCE_TABLE = Table_mapping_df_row['Main source']
Column_mapping_df = Column_mapping[(Column_mapping['Mapping name'] == TABLE_MAPPING_NAME)
& (Column_mapping['Mapped to table'] == SOURCE_TABLE)][['Column name', 'Mapped to column']]
STG_tables_df = STG_tables[(STG_tables['Source system name'] == source_name)
& (STG_tables['Table name'] == SOURCE_TABLE)
& (STG_tables['Key domain name'] != "")][['Natural key', 'Key domain name', 'Column name']]
merge_df = Column_mapping_df.merge(STG_tables_df,
left_on=['Mapped to column'],
right_on=['Column name'],
suffixes=('_clnM', '_stgT'),
how='inner')
for merge_df_index, merge_df_row in merge_df.iterrows():
TARGET_COLUMN_NAME = merge_df_row['Column name_clnM']
NATURAL_KEY = merge_df_row['Natural key']
key_domain_name = merge_df_row['Key domain name']
BKEY_df = BKEY[BKEY['Key domain name'] == key_domain_name]
for BKEY_df_index, BKEY_df_row in BKEY_df.iterrows():
PHYSICAL_NAME = BKEY_df_row['Physical table']
KEY_DOMAIN_ID_VALUE = str(BKEY_df_row['Key domain ID'])
select_script = "-- " + TABLE_MAPPING_NAME + " -- " + SOURCE_TABLE + " -- " + PHYSICAL_NAME + \
"\nSELECT\t" + funcs.single_quotes(TARGET_TABLE_NAME) + " AS CORE_TABLE," \
"\n\t\t"+ funcs.single_quotes(TARGET_COLUMN_NAME) + " AS CORE_COLUMN," \
"\n\t\t"+ funcs.single_quotes(TABLE_MAPPING_NAME) + " AS MAPPING_NAME," \
"\n\t\tCASE WHEN BKEY_CNT > 0 THEN 'BKEY_FAILED' ELSE 'BKEY_SUCCEEDED' END AS BKEY_STATUS," \
"\n\t\tCASE WHEN CORE_CNT > 0 THEN 'CORE_FAILED' ELSE 'CORE_SUCCEEDED' END AS CORE_STATUS" \
"\nFROM" \
"\n(" \
"\n\tSELECT COUNT(*) BKEY_CNT" \
"\n\tFROM " + cf.v_stg + "." + SOURCE_TABLE + " X" \
"\n\tLEFT JOIN " + cf.UTLFW_v + "." + PHYSICAL_NAME + " AS BK1" \
"\n\tON BK1.SOURCE_KEY = TRIM(" + NATURAL_KEY + ") AND BK1.DOMAIN_ID = " + KEY_DOMAIN_ID_VALUE + "" \
"\n\tWHERE EDW_KEY IS NULL" \
"\n)BK_CHECK," \
"\n(" \
"\n\tSELECT COUNT(*) CORE_CNT" \
"\n\tFROM (SELECT * FROM " + cf.UTLFW_v + "." + PHYSICAL_NAME + " WHERE DOMAIN_ID=" + KEY_DOMAIN_ID_VALUE + ")BK1" \
"\n\tINNER JOIN " + cf.v_stg + "." + SOURCE_TABLE + " SRC ON BK1.SOURCE_KEY = TRIM(" + NATURAL_KEY + ")" \
"\n\tLEFT JOIN " + cf.core_view + "." + TARGET_TABLE_NAME + " CORE ON EDW_KEY = " + TARGET_COLUMN_NAME + "" \
"\n\tWHERE " + TARGET_COLUMN_NAME + " IS NULL" \
"\n)CORE_CHECK;\n\n"
f.write(select_script)
f.close()
def d620(cf, source_output_path, Table_mapping, Column_mapping, Core_tables,
Loading_Type):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
try:
notes = list()
for table_maping_index, table_maping_row in Table_mapping.iterrows():
inp_view_from_clause = ''
process_type = 'TXF'
layer = str(table_maping_row['Layer'])
table_maping_name = str(table_maping_row['Mapping name'])
src_layer = str(table_maping_row['Source layer'])
process_name = process_type + "_" + layer + "_" + table_maping_name
inp_view_header = 'REPLACE VIEW ' + cf.INPUT_VIEW_DB + '.' + process_name + '_IN AS LOCK ROW FOR ACCESS'
target_table = str(table_maping_row['Target table name'])
apply_type = table_maping_row['Historization algorithm']
main_src = table_maping_row['Main source']
main_src_alias = table_maping_row['Main source alias']
if main_src == main_src_alias:
main_src = cf.SI_VIEW + '.' + main_src
# core_tables_list= pd.unique(list(Core_tables['Table name']))
core_tables_list = TransformDDL.get_core_tables_list(Core_tables)
if main_src is None:
msg = 'Missing Main Source for Table Mapping:{}'.format(
str(table_maping_row['Mapping name']))
notes += msg
continue
if target_table not in core_tables_list:
msg = 'TARGET TABLE NAME not found in Core Tables Sheet for Table Mapping:{}'.format(
str(table_maping_row['Mapping name']))
notes += msg
continue
sub = "/* Target table:\t" + target_table + "*/" + '\n'\
+ "/* Table mapping:\t" + table_maping_name + "*/" + '\n'\
+ "/* Mapping group:\t" + table_maping_row['Mapping group'] + "*/" + '\n' \
+ "/* Apply type:\t\t" + apply_type + "*/"
inp_view_select_clause = 'SELECT ' + '\n' + sub + TransformDDL.get_select_clause(
target_table, Core_tables, table_maping_name, Column_mapping)
map_grp = ' CAST(' + funcs.single_quotes(
table_maping_row['Mapping group']
) + ' AS VARCHAR(100)) AS MAP_GROUP ,'
start_date = '(SELECT Business_Date FROM ' + cf.GCFR_V + '.GCFR_Process_Id' + '\n' + ' WHERE Process_Name = ' + "'" + process_name + "'" + '\n' + ') AS Start_Date,'
end_date = 'DATE ' + "'9999-12-31'" + ' AS End_Date,'
if Loading_Type == 'OFFLINE':
modification_type = "'U' AS MODIFICATION_TYPE"
else:
modification_type = main_src_alias + '.MODIFICATION_TYPE'
inp_view_select_clause = inp_view_select_clause + '\n' + map_grp + '\n' + start_date + '\n' + end_date + '\n' + modification_type + '\n'
if table_maping_row['Join'] == "":
inp_view_from_clause = 'FROM ' + main_src + ' ' + main_src_alias
elif table_maping_row['Join'] != "":
if (table_maping_row['Join'].find(
"FROM".strip()) == -1): #no subquery in join clause
inp_view_from_clause = 'FROM ' + main_src + ' ' + main_src_alias
inp_view_from_clause = inp_view_from_clause + '\n' + table_maping_row[
'Join']
join = 'JOIN ' + cf.SI_VIEW + '.'
inp_view_from_clause = inp_view_from_clause.replace(
'JOIN ', join)
else:
sub_query_flag = 1
join_clause = table_maping_row['Join']
subquery_clause = TransformDDL.get_sub_query(
cf, join_clause, src_layer, main_src)
inp_view_from_clause = ' FROM \n' + subquery_clause
inp_view_where_clause = ';'
if table_maping_row['Filter criterion'] != "":
# if (sub_query_flag == 0):
inp_view_where_clause = 'Where ' + table_maping_row[
'Filter criterion'] + ';'
# else:
# inp_view_where_clause = 'Where '+table_maping_row['Filter criterion']+');'
f.write(inp_view_header)
f.write("\n")
f.write(inp_view_select_clause)
f.write("\n")
f.write(inp_view_from_clause)
f.write("\n")
f.write(inp_view_where_clause)
f.write("\n")
f.write("\n")
f.write("\n")
except:
funcs.TemplateLogError(cf.output_path, source_output_path, file_name,
traceback.format_exc()).log_error()
f.close()
def d620(cf, source_output_path, Table_mapping, Column_mapping, Core_tables,
Loading_Type, input_view_flag, stg_tables):
file_name = funcs.get_file_name(__file__)
if input_view_flag == 'TESTING':
file_name = 'testing_input_views'
f = funcs.WriteFile(source_output_path, file_name, "sql")
notes = list()
view_name_suffix = ''
for table_maping_index, table_maping_row in Table_mapping.iterrows():
inp_view_from_clause = ''
process_type = 'TXF'
layer = str(table_maping_row['Layer'])
table_maping_name = str(table_maping_row['Mapping name'])
src_layer = str(table_maping_row['Source layer'])
process_name = process_type + "_" + layer + "_" + table_maping_name
if input_view_flag == 'UDI':
view_name_suffix = ' '
elif input_view_flag == 'TESTING':
view_name_suffix = '_TESTING '
inp_view_header = 'REPLACE VIEW ' + cf.INPUT_VIEW_DB + '.' + process_name + '_IN' + view_name_suffix + 'AS LOCK ROW FOR ACCESS' + '\n'
target_table = str(table_maping_row['Target table name'])
apply_type = table_maping_row['Historization algorithm']
process_names_condition = str(table_maping_row['SubProcess Condition'])
process_names_case_when = process_names_condition.replace(
'#process_name#', process_name)
process_names_case_when_clause = '(' + process_names_case_when + ') AS PROCESS_NAME '
main_src = table_maping_row['Main source']
SRCI = cf.SI_DB + '.'
main_src = main_src.replace('#SRCI#', SRCI)
main_src_alias = table_maping_row['Main source alias'].upper()
main_src_alias_mt = main_src_alias.replace('_ONLINE', '')
modification_type_exists = funcs.table_has_modification_type_column(
stg_tables, main_src_alias_mt)
if modification_type_exists:
modification_type = main_src_alias + '.MODIFICATION_TYPE'
else:
modification_type = "'U' AS MODIFICATION_TYPE"
if main_src == main_src_alias:
main_src = cf.SI_DB + '.' + main_src
# core_tables_list= pd.unique(list(Core_tables['Table name']))
core_tables_list = TransformDDL.get_core_tables_list(Core_tables)
if main_src is None:
msg = 'Missing Main Source for Table Mapping:{}'.format(
str(table_maping_row['Mapping name']))
notes += msg
continue
if target_table not in core_tables_list:
msg = 'TARGET TABLE NAME not found in Core Tables Sheet for Table Mapping:{}'.format(
str(table_maping_row['Mapping name']))
notes += msg
continue
sub = "/* Target table:\t" + target_table + "*/" + '\n' \
+ "/* Table mapping:\t" + table_maping_name + "*/" + '\n' \
+ "/* Mapping group:\t" + table_maping_row['Mapping group'] + "*/" + '\n' \
+ "/* Apply type:\t\t" + apply_type + "*/"
inp_view_select_clause = 'SELECT ' + '\n' + sub + TransformDDL.get_select_clause(
target_table, Core_tables, table_maping_name, Column_mapping)
map_grp = ' CAST(' + funcs.single_quotes(
table_maping_row['Mapping group']
) + ' AS VARCHAR(100)) AS MAP_GROUP ,'
start_date = '(SELECT Business_Date FROM ' + cf.GCFR_V + '.GCFR_Process_Id' + '\n' + ' WHERE Process_Name = ' + "'" + process_name + "'" + '\n' + ') AS Start_Date,'
end_date = 'DATE ' + "'9999-12-31'" + ' AS End_Date,'
load_id = main_src_alias + '.LOAD_ID'
batch_id = main_src_alias + '.BATCH_ID'
ref_key = main_src_alias + '.REF_KEY'
if process_names_case_when != '':
inp_view_select_clause = inp_view_select_clause + '\n' + map_grp + '\n' + start_date + '\n' + end_date + '\n' + modification_type + ',' + '\n' + batch_id + ',' + '\n' + ref_key + ',' + process_names_case_when_clause + '\n'
else:
inp_view_select_clause = inp_view_select_clause + '\n' + map_grp + '\n' + start_date + '\n' + end_date + '\n' + modification_type + ',' + '\n' + batch_id + ',' + '\n' + ref_key + '\n'
if table_maping_row['Join'] == "":
inp_view_from_clause = 'FROM ' + main_src + ' ' + main_src_alias
elif table_maping_row['Join'] != "":
if table_maping_row['Join'].find(
"FROM".strip()) == -1: # no subquery in join clause
inp_view_from_clause = 'FROM ' + main_src + ' ' + main_src_alias
inp_view_from_clause = inp_view_from_clause + '\n' + table_maping_row[
'Join']
if '#CORE#' not in inp_view_from_clause:
join = 'JOIN ' + cf.SI_VIEW + '.'
else:
join = ' '
inp_view_from_clause = inp_view_from_clause.replace(
'JOIN ', join)
inp_view_from_clause = inp_view_from_clause.replace(
'#CORE#', cf.core_view + '.')
else:
sub_query_flag = 1
join_clause = table_maping_row['Join']
subquery_clause = TransformDDL.get_sub_query(
cf, join_clause, src_layer, main_src)
inp_view_from_clause = ' FROM \n' + subquery_clause
inp_view_where_clause = ''
if table_maping_row['Filter criterion'] != "":
inp_view_where_clause = '\n' + 'Where ' + table_maping_row[
'Filter criterion'] + ';'
elif table_maping_row['Aggregation filter criterion'] != "":
inp_view_where_clause = inp_view_where_clause + '\n' + table_maping_row[
'Aggregation filter criterion'] + ';'
else:
inp_view_where_clause = ';'
f.write(inp_view_header)
f.write(inp_view_select_clause)
f.write(inp_view_from_clause)
f.write(inp_view_where_clause)
f.write("\n")
f.write("\n")
f.write("\n")
f.close()
def d320(cf, source_output_path, STG_tables, BKEY):
file_name = funcs.get_file_name(__file__)
f = funcs.WriteFile(source_output_path, file_name, "sql")
separator = pm.stg_cols_separator
stg_tables_df = STG_tables.loc[(STG_tables['Key domain name'] != "")
& (STG_tables['Natural key'] != "")]
trimmed_Natural_key = []
normal_columns = ""
join_type = ""
join_statement = ""
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name'].upper()
Column_name = stg_tables_df_row['Column 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 = str(stg_tables_df_row['Natural key']).upper()
for i in list(set(Natural_key_list)):
i = i.replace(" ", "")
if "COALESCE" in i:
Column_name = "COALESCE(" + Column_name + ",'')"
Column_name = "TRIM(Trailing '.' from TRIM(" + Column_name + ")) "
if Natural_key == "":
comma_Column_name = ',' + Column_name
normal_columns = normal_columns + comma_Column_name + "\n"
else:
trim_Natural_key = []
split_Natural_key = Natural_key.split(separator)
for i in split_Natural_key:
trim_Natural_key.append("TRIM(Trailing '.' from TRIM(" +
i + "))")
trimmed_Natural_key = funcs.list_to_string(
trim_Natural_key, separator)
key_domain_name = stg_tables_df_row['Key domain name']
stg_table_name = stg_tables_df_row['Table name']
stg_Column_name = stg_tables_df_row['Column name']
generation_flag = stg_tables_df_row['Bkey generation flag']
Bkey_filter = str(stg_tables_df_row['Bkey filter']).upper()
Bkey_filter = "WHERE " + Bkey_filter if Bkey_filter != "" and "JOIN" not in Bkey_filter else Bkey_filter
Bkey_filter = Bkey_filter + "\n" if Bkey_filter != "" else Bkey_filter
Bkey_join = str(stg_tables_df_row['Bkey Join']).upper()
if Bkey_join != "":
Bkey_join_splitted = Bkey_join.split("JOIN ")
join_type = Bkey_join_splitted[0]
join_statement = "JOIN " + cf.v_stg + '.' + Bkey_join_splitted[1]
Natural_key_list = stg_tables_df_row['Natural key'].split(separator)
trim_Trailing_Natural_key_list = []
for i in Natural_key_list:
trim_Trailing_Natural_key_list.append(
"TRIM(Trailing '.' from TRIM(" + i.strip() + "))")
Source_Key = funcs.list_to_string(trim_Trailing_Natural_key_list,
separator)
coalesce_count = Source_Key.upper().count("COALESCE")
separator_count = Source_Key.count(separator)
compare_string = funcs.single_quotes(
"_" *
separator_count) if coalesce_count > separator_count else "''"
Source_Key_cond = "WHERE " if "WHERE" not in Bkey_filter else " AND "
Source_Key_cond = Source_Key_cond + "COALESCE(Source_Key," + compare_string + ") <> " + compare_string + " "
bkey_df = BKEY.loc[(BKEY['Key domain name'] == key_domain_name)]
Key_set_ID = str(int(bkey_df['Key set ID'].values[0]))
Key_domain_ID = str(int(bkey_df['Key domain ID'].values[0]))
if generation_flag != 0:
script = "REPLACE VIEW " + cf.INPUT_VIEW_DB + ".BK_" + Key_set_ID + "_" + stg_table_name + "_" + stg_Column_name + "_" + Key_domain_ID + "_IN AS LOCK ROW FOR ACCESS\n"
script = script + "SELECT " + trimmed_Natural_key + " AS Source_Key\n"
script = script + "FROM " + cf.v_stg + "." + stg_table_name + "\n"
if Bkey_join != "":
script = script + join_type + join_statement + "\n"
script = script + Bkey_filter + Source_Key_cond + "\n"
script = script + "GROUP BY 1;" + "\n"
f.write(script)
f.write('\n')
f.close()
