def d415(source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
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 " + pm.GCFR_V + ".GCFR_Transform_KeyCol "
del_script = del_script + " WHERE OUT_OBJECT_NAME = '" + stg_table_name + "';\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, source_name, stg_table_name, True)
exe_ = "EXEC " + pm.MACRO_DB + ".GCFR_Register_Tfm_KeyCol('" + pm.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)
f.close()
def d210(source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
INS_DTTM = ",CURRENT_TIMESTAMP AS INS_DTTM \n"
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
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 " + pm.gdev1v_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, source_name, 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
create_stg_view = create_stg_view + comma_Column_name + "\n"
create_stg_view = create_stg_view + INS_DTTM
create_stg_view = create_stg_view + "from " + pm.gdev1t_stg + "." + Table_name + ";\n\n"
f.write(create_stg_view)
f.close()
def d400(source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
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 " + pm.gdev1t_stg + "." + Table_name + Fallback + "\n" + "(\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, source_name, 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 d200(source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
INS_DTTM = ",INS_DTTM TIMESTAMP(6) NOT NULL \n"
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
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 " + pm.gdev1t_stg + "." + Table_name + Fallback + "\n" + "(\n"
create_wrk_table = "create multiset table " + pm.gdev1t_WRK + "." + Table_name + Fallback + "\n" + "(\n"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, source_name, Table_name)
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"
create_wrk_table = create_wrk_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
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 + ")"
create_stg_table = create_stg_table + INS_DTTM + seq_column + Primary_Index
create_wrk_table = create_wrk_table + 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)
f.close()
def d410(source_output_path, source_name, STG_tables):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
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 " + pm.SI_VIEW + "." + stg_table_name + " AS\n"
script = script + "SELECT * FROM " + pm.SI_DB + "." + stg_table_name + ";\n\n"
f.write(script)
f.close()
def d420(source_output_path, source_name, STG_tables, BKEY):
file_name = funcs.get_file_name(__file__)
f = open(source_output_path + "/" + file_name + ".sql", "w+")
separator = pm.separator
stg_tables_df = funcs.get_stg_tables(STG_tables, source_name)
for stg_tables_df_index, stg_tables_df_row in stg_tables_df.iterrows():
stg_table_name = stg_tables_df_row['Table name']
stg_Natural_key_df = STG_tables.loc[
(STG_tables['Source system name'] == source_name)
& (STG_tables['Table name'] == stg_table_name)
& ~(STG_tables['Natural key'].isnull())]
bkey_Natural_key_list = []
for stg_Natural_key_df_index, stg_Natural_key_df_row in stg_Natural_key_df.iterrows(
):
# bkey_Natural_key_list = stg_Natural_key_df_row['Natural key'].split(separator)
bkey_Natural_key_list.append(stg_Natural_key_df_row['Natural key'])
bkey_Natural_key_list_str = funcs.list_to_string(
bkey_Natural_key_list, ',').upper()
stg_table_has_pk = True if len(STG_tables.loc[
(STG_tables['Table name'] == 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_script = "REPLACE VIEW " + pm.SI_VIEW + "." + stg_table_name + " AS\nSELECT \n"
from_clause = "FROM " + pm.gdev1v_stg + "." + stg_table_name + " t"
STG_table_columns = funcs.get_stg_table_columns(
STG_tables, source_name, stg_table_name, True)
bkeys_left_join = ""
bkeys_left_join_count = 0
for STG_table_columns_index, STG_table_columns_row in STG_table_columns.iterrows(
):
Column_name = STG_table_columns_row['Column name'].upper()
alias = Column_name
if Column_name in bkey_Natural_key_list_str:
if "COALESCE" in bkey_Natural_key_list_str:
Column_name = "COALESCE( " + Column_name + ",'')"
Column_name = "TRIM(Trailing '.' from TRIM(" + Column_name + ")) " + alias
comma = ',' if STG_table_columns_index > 0 else seq_pk_col
comma_Column_name = comma + Column_name
create_view_script = create_view_script + comma_Column_name + "\n"
try:
Key_domain_name = STG_table_columns_row['Key domain name']
bkey_physical_table = BKEY.loc[(
BKEY['Key domain name'].str.upper() == Key_domain_name
)]['Physical table'].values[0]
bkeys_left_join_count = bkeys_left_join_count + 1
bk_alias = " bk" + str(bkeys_left_join_count)
bkeys_left_join = bkeys_left_join + "LEFT JOIN " + pm.G_BKEY_V + "." + bkey_physical_table + bk_alias + "\n"
Natural_key = STG_table_columns_row['Natural key']
split_Natural_key = Natural_key.replace(" ",
"").split(separator)
trim_Natural_key = []
for i in split_Natural_key:
trim_Natural_key.append("TRIM(Trailing '.' from TRIM(" +
i.strip() + "))")
Natural_key = funcs.list_to_string(trim_Natural_key, separator)
bkeys_left_join = bkeys_left_join + "\tON " + bk_alias + ".Source_Key = " + Natural_key + "\n"
except:
pass
create_view_script = create_view_script + from_clause + "\n" + bkeys_left_join
f.write(create_view_script + "\n")
# f.write(Natural_key_list_str + "\n")
f.close()