def apply_insert_upsert(cf, source_output_path, SMX_SHEET, script_flag):
current_date = funcs.get_current_date()
ld_prefix = cf.ld_prefix
FSDM_prefix = cf.modelDB_prefix
DupDB_prefix = cf.modelDup_prefix
bteq_run_file = cf.bteq_run_file
# SOURCENAME = cf.sgk_source
# if SOURCENAME != 'ALL':
# SMX_SHEET = SMX_SHEET[SMX_SHEET['Ssource'] == SOURCENAME]
if script_flag == 'Apply_Insert':
SMX_SHEET = funcs.get_apply_processes(SMX_SHEET, "Apply_Insert")
folder_name = 'Apply_Insert'
template_path = cf.templates_path + "/" + pm.default_bteq_apply_insert_template_file_name
template_smx_path = cf.smx_path + "/" + pm.default_bteq_apply_insert_template_file_name
elif script_flag == 'Apply_Upsert':
SMX_SHEET = funcs.get_apply_processes(SMX_SHEET, "Apply_Upsert")
folder_name = 'Apply_Upsert'
template_path = cf.templates_path + "/" + pm.default_bteq_apply_upsert_template_file_name
template_smx_path = cf.smx_path + "/" + pm.default_bteq_apply_upsert_template_file_name
else: # script_flag == 'Apply_Delete_Insert':
SMX_SHEET = funcs.get_apply_processes(SMX_SHEET, "Apply_Delete_Insert")
folder_name = 'Apply_Delete_Insert'
template_path = cf.templates_path + "/" + pm.default_bteq_apply_delete_insert_template_file_name
template_smx_path = cf.smx_path + "/" + pm.default_bteq_apply_delete_insert_template_file_name
apply_folder_path = path.join(source_output_path, folder_name)
makedirs(apply_folder_path)
template_string = ""
template_head = ""
try:
template_file = open(template_path, "r")
except:
template_file = open(template_smx_path, "r")
template_start = 0
template_head_line = 0
for i in template_file.readlines():
if i != "":
if i[0] == '#' and template_head_line >= template_start:
template_head = template_head + i
template_head_line = template_head_line + 1
else:
template_string = template_string + i
template_start = template_head_line + 1
# f.write(template_head)
record_ids_list = SMX_SHEET['Record_ID'].unique()
# print("****", script_flag,"........rec ids ......",record_ids_list )
for record_id in record_ids_list:
smx_record_id_df = funcs.get_sama_fsdm_record_id(SMX_SHEET, record_id)
# source_system = funcs.get_Rid_Source_System(smx_record_id_df)
# source_system = source_system.replace('Mobile Payments - ', '')
Record_id = record_id
schema_name = smx_record_id_df['Stg_Schema'].unique()[0]
# ld_DB = ld_prefix+schema_name
Table_name = smx_record_id_df['Entity'].unique()[0]
fsdm_tbl_alias = funcs.get_fsdm_tbl_alias(Table_name)
ld_tbl_alias = funcs.get_ld_tbl_alias(fsdm_tbl_alias, Record_id)
fsdm_tbl_alias = fsdm_tbl_alias + "_FSDM"
ld_table_name = Table_name + "_R" + str(Record_id)
BTEQ_file_name = "UDI_{}_{}".format(schema_name, ld_table_name)
f = funcs.WriteFile(apply_folder_path, BTEQ_file_name, "bteq")
f.write(template_head)
# ld_tbl_columns_aliased = funcs.get_fsdm_tbl_non_technical_columns(smx_record_id_df, ld_tbl_alias)
ld_pk_cols_aliased = funcs.get_sama_pk_columns_comma_separated(
smx_record_id_df,
Table_name,
alias=ld_tbl_alias,
record_id=Record_id)
fsdm_tbl_columns = funcs.get_fsdm_tbl_columns(smx_record_id_df,
alias_name=None)
on_clause = funcs.get_conditional_stamenet(smx_record_id_df,
Table_name, "pk", "=",
ld_tbl_alias,
fsdm_tbl_alias, Record_id)
where_clause = funcs.get_conditional_stamenet(smx_record_id_df,
Table_name, "pk", "=",
ld_tbl_alias, "FLAG_IND",
Record_id)
# from_clause = smx_record_id_df['From_Rule'].unique()[0]
# join_clause = smx_record_id_df['Join_Rule'].unique()[0]
# filter_Clause = smx_record_id_df['Filter_Rule'].unique()[0]
FSDM_tbl_pk = funcs.get_sama_pk_columns_comma_separated(
smx_record_id_df,
Table_name,
alias=fsdm_tbl_alias,
record_id=Record_id)
FSDM_first_tbl_pk = FSDM_tbl_pk.split(',')[0]
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM = funcs.get_comparison_columns(
smx_record_id_df, Table_name, "UPSERT", '=', ld_tbl_alias,
fsdm_tbl_alias, Record_id)
if COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM.strip() == "":
duplicate_when = ""
duplicate_then = ""
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM = ""
else:
duplicate_when = '\n WHEN'
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM = COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM + "\n"
duplicate_then = " THEN 'D'"
if script_flag == 'Apply_Insert':
# COALESCED_TABLE_PK_COLUMNS_LD_EQL_FSDM = funcs.get_comparison_columns(smx_record_id_df, Table_name, "INSERT"
# , '=', ld_tbl_alias, fsdm_tbl_alias,
# Record_id)
bteq_script = template_string.format( #filename=BTEQ_file_name,#versionnumber=pm.ver_no,
source_system=schema_name,
table_name=Table_name,
record_id=str(Record_id),
currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_prefix=ld_prefix,
schema_name=schema_name,
ld_table_name=ld_table_name,
tbl_pk_cols_aliased=ld_pk_cols_aliased,
ld_tbl_alias=ld_tbl_alias,
fsdm_tbl_alias=fsdm_tbl_alias,
table_columns=fsdm_tbl_columns,
FSDM_first_tbl_pk=FSDM_first_tbl_pk.strip(),
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM=
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM,
fsdm_prefix=FSDM_prefix,
fsdm_table_name=Table_name,
ld_equal_fsdm_pk=on_clause,
FLAG_IND_equal_fsdm_pk=where_clause,
dup_prefix=DupDB_prefix,
duplicate_when=duplicate_when,
duplicate_then=duplicate_then)
elif script_flag == 'Apply_Upsert':
ld_equal_fsdm_pk_update = funcs.get_conditional_stamenet(
smx_record_id_df, Table_name, "pk", "=", fsdm_tbl_alias,
ld_tbl_alias, Record_id)
non_pk_cols_eql_ld_cols = funcs.get_conditional_stamenet(
smx_record_id_df, Table_name, "non_pk_upsert_set", "=", None,
ld_tbl_alias, Record_id)
non_pk_cols_eql_ld_cols = non_pk_cols_eql_ld_cols.replace(
' and ', ',')
bteq_script = template_string.format( #filename=BTEQ_file_name, #versionnumber=pm.ver_no,
source_system=schema_name,
table_name=Table_name,
record_id=str(Record_id),
currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_prefix=ld_prefix,
schema_name=schema_name,
ld_table_name=ld_table_name,
tbl_pk_cols_aliased=ld_pk_cols_aliased,
ld_tbl_alias=ld_tbl_alias,
fsdm_tbl_alias=fsdm_tbl_alias,
table_columns=fsdm_tbl_columns,
FSDM_first_tbl_pk=FSDM_first_tbl_pk.strip(),
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM=
COALESCED_TABLE_nonPK_COLUMNS_LD_EQL_FSDM,
non_pk_cols_eql_ld_cols=non_pk_cols_eql_ld_cols,
fsdm_prefix=FSDM_prefix,
fsdm_table_name=Table_name,
ld_equal_fsdm_pk=on_clause,
FLAG_IND_equal_fsdm_pk=where_clause,
ld_equal_fsdm_pk_update=ld_equal_fsdm_pk_update,
dup_prefix=DupDB_prefix,
duplicate_when=duplicate_when,
duplicate_then=duplicate_then)
else:
bteq_script = template_string.format( #filename=BTEQ_file_name, # versionnumber=pm.ver_no,
source_system=schema_name,
table_name=Table_name,
#record_id=str(Record_id),
currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_prefix=ld_prefix,
schema_name=schema_name,
ld_table_name=ld_table_name,
table_columns=fsdm_tbl_columns,
fsdm_prefix=FSDM_prefix,
fsdm_table_name=Table_name,
record_id=Record_id)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' '))
f.close()
def history_legacy_apply(cf, source_output_path, secondary_output_path_HIST,
smx_table):
folder_name = 'Apply_History_Legacy/INITIAL_LOAD'
apply_folder_path = path.join(source_output_path, folder_name)
makedirs(apply_folder_path)
template_path = cf.templates_path + "/" + pm.default_history_legacy_apply_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_history_legacy_apply_template_file_name
ld_schema_name = cf.ld_prefix
model_Schema_name = cf.modelDB_prefix
bteq_run_file = cf.bteq_run_file
current_date = funcs.get_current_date()
template_string = ""
try:
template_file = open(template_path, "r")
except:
template_file = open(template_smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
history_handeled_df = funcs.get_apply_processes(smx_table,
"Apply_History_Legacy")
record_ids_list = history_handeled_df['Record_ID'].unique()
for r_id in record_ids_list:
history_df = funcs.get_sama_fsdm_record_id(history_handeled_df, r_id)
record_id = r_id
table_name = history_df['Entity'].unique()[0]
source_name = history_df['Stg_Schema'].unique()[0]
filename = table_name + '_R' + str(record_id)
BTEQ_file_name = "UDI_{}_{}".format(source_name, filename)
special_handling_flag = history_df['SPECIAL_HANDLING_FLAG'].unique()[0]
if special_handling_flag.upper() == "N":
f = funcs.WriteFile(apply_folder_path, BTEQ_file_name, "bteq")
else:
folder_name_Sapecial = 'Apply_History_Legacy/INITIAL_LOAD'
apply_folder_path_Sapecial = path.join(secondary_output_path_HIST,
folder_name_Sapecial)
if not path.exists(apply_folder_path_Sapecial):
makedirs(apply_folder_path_Sapecial)
f = funcs.WriteFile(apply_folder_path_Sapecial, BTEQ_file_name,
"bteq")
possible_special_handling_comments = ""
if special_handling_flag.upper() == "Y":
#possible_special_handling_comments = history_df[history_df['Historization_Column'].str.upper() == 'E']['Rule'].values
possible_special_handling_comments = "/* Please refer to the SMX for this record id to check the comments in the Rule column*/"
fsdm_tbl_alias = funcs.get_fsdm_tbl_alias(table_name)
ld_tbl_alias = funcs.get_ld_tbl_alias(fsdm_tbl_alias, record_id)
fsdm_tbl_alias = fsdm_tbl_alias + "_FSDM"
strt_date, end_date, hist_keys, hist_cols = funcs.get_history_variables(
history_df, record_id, table_name)
strt_date = strt_date[0]
end_date = end_date[0]
# hist_cols = hist_cols[0]
history_keys_list = funcs.get_list_values_comma_separated(
hist_keys, 'N')
history_keys_columns = funcs.get_list_values_comma_separated(
hist_keys, 'Y')
history_columns = funcs.get_list_values_comma_separated(hist_cols, 'Y')
#history_columns_list = funcs.get_list_values_comma_separated(hist_cols, 'N')
max_history_columns_clause, pre_hist_cols_null_clause, hh_tbl_pre_not_eql_hh_tbl_hist_col = \
funcs.get_hist_legacy_hist_cols_clauses(hist_cols, history_keys_list, strt_date, table_name)
TBL_COLUMNS = funcs.get_sama_table_columns_comma_separated(
history_df, table_name, None, record_id)
HH_alias_TBL_COLUMNS = \
funcs.get_sama_table_columns_comma_separated(history_df, table_name, 'HH_DATA', record_id)
LRD_alias_TBL_COLUMNS = \
funcs.get_sama_table_columns_comma_separated(history_df, table_name, ld_tbl_alias, record_id)
FSDM_alias_TBL_COLUMNS = \
funcs.get_sama_table_columns_comma_separated(history_df, table_name, fsdm_tbl_alias,
record_id)
ld_fsdm_history_key_and_strt_date_equality = funcs.get_conditional_stamenet(
history_df, table_name, 'hist_key_strt_date', '=', ld_tbl_alias,
fsdm_tbl_alias, record_id, None)
interval = funcs.get_hist_end_Date_interval(history_df, table_name,
record_id)
high_date, end_date_dtype = funcs.get_hist_high_date(
history_df, table_name, record_id)
end_dt_coalesce_stmnt = "COALESCE(MAX({start_date} ) OVER (PARTITION BY {history_keys_list} ORDER BY {start_date} ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), CAST('{high_date}' AS {end_date_dtype})) {end_date}".format(
start_date=strt_date,
history_keys_list=history_keys_list,
high_date=high_date,
end_date_dtype=end_date_dtype,
end_date=end_date)
TBL_COLS_EndDt_Coalesced = TBL_COLUMNS.replace(end_date,
end_dt_coalesce_stmnt)
bteq_script = template_string.format(
source_system=source_name,
versionnumber=pm.ver_no,
currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_schema_name=ld_schema_name,
table_name=table_name,
record_id=record_id,
table_columns=TBL_COLUMNS,
HH_aliased_table_columns=HH_alias_TBL_COLUMNS,
LRD_aliased_table_columns=LRD_alias_TBL_COLUMNS,
FSDM_aliased_table_columns=FSDM_alias_TBL_COLUMNS,
ld_alias=ld_tbl_alias,
model_schema_name=model_Schema_name,
fsdm_alias=fsdm_tbl_alias,
TBL_COLS_EndDt_Coalesced=TBL_COLS_EndDt_Coalesced,
ld_fsdm_history_key_and_strt_date_equality=
ld_fsdm_history_key_and_strt_date_equality,
history_keys_list=history_keys_list,
history_keys_columns=history_keys_columns,
max_history_columns_clause=max_history_columns_clause,
pre_hist_cols_null=pre_hist_cols_null_clause,
hh_tbl_pre_not_eql_hh_tbl_hist_col=
hh_tbl_pre_not_eql_hh_tbl_hist_col,
history_columns=history_columns,
start_date=strt_date,
end_date=end_date,
high_date=high_date,
end_date_dtype=end_date_dtype,
possible_special_handling_comments=
possible_special_handling_comments)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' ').replace('\t', ' '))
f.close()
def history_apply(cf, source_output_path, smx_table):
hist_load_types = funcs.get_history_load_types(smx_table)
folder_name = 'Apply_History'
apply_folder_path = path.join(source_output_path, folder_name)
makedirs(apply_folder_path)
template_path = cf.templates_path + "/" + pm.default_history_apply_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_history_apply_template_file_name
LD_SCHEMA_NAME = cf.ld_prefix
MODEL_SCHEMA_NAME = cf.modelDB_prefix
MODEL_DUP_SCHEMA_NAME = cf.modelDup_prefix
bteq_run_file = cf.bteq_run_file
current_date = funcs.get_current_date()
template_string = ""
try:
template_file = open(template_path, "r")
except:
template_file = open(template_smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
history_handeled_df = funcs.get_history_handled_processes(
smx_table, hist_load_types)
for history_df_index, history_df_row in history_handeled_df.iterrows():
record_id = history_df_row['Record_ID']
table_name = history_df_row['Entity']
SOURCE_SYSTEM = history_df_row['Source_System']
filename = table_name + '_' + str(record_id)
f = funcs.WriteFile(apply_folder_path, filename, "bteq")
filename = filename + '.bteq'
PK_TABLE_COLOUMNS_WITH_ALIAS_LD = funcs.get_sama_pk_columns_comma_separated(
history_handeled_df, table_name, 'LOAD_TABLE', record_id)
TABLE_PK = funcs.get_sama_pk_columns_comma_separated(
history_handeled_df, table_name, 'one_pk', record_id)
COALESCED_TABLE_COLUMNS_LD_EQL_DATAMODEL = funcs.get_comparison_columns(
history_handeled_df, table_name, "History", '=', 'LOAD_TABLE',
'MODEL_TABLE', record_id)
LOADTBL_PK_EQL_MODELTBL = funcs.get_conditional_stamenet(
history_handeled_df, table_name, 'pk', '=', 'LOAD_TABLE',
'MODEL_TABLE', record_id, 'histort')
LOADTBL_PK_EQL_FLAGIND = funcs.get_conditional_stamenet(
history_handeled_df, table_name, 'pk', '=', 'LOAD_TABLE',
'FLAG_IND', record_id, 'history')
TABLE_COLUMNS = funcs.get_sama_table_columns_comma_separated(
history_handeled_df, table_name, None, record_id)
NON_PK_COLS_EQL_LD = funcs.get_conditional_stamenet(
history_handeled_df, table_name, 'non_pk', '=', 'MODEL_TABLE',
'LOAD_TABLE', record_id)
bteq_script = template_string.format(
SOURCE_SYSTEM=SOURCE_SYSTEM,
versionnumber=pm.ver_no,
currentdate=current_date,
filename=filename,
bteq_run_file=bteq_run_file,
LD_SCHEMA_NAME=LD_SCHEMA_NAME,
MODEL_SCHEMA_NAME=MODEL_SCHEMA_NAME,
MODEL_DUP_SCHEMA_NAME=MODEL_DUP_SCHEMA_NAME,
TABLE_NAME=table_name,
RECORD_ID=record_id,
PK_TABLE_COLOUMNS_WITH_ALIAS_LD=PK_TABLE_COLOUMNS_WITH_ALIAS_LD,
TABLE_PK=TABLE_PK,
COALESCED_TABLE_COLUMNS_LD_EQL_DATAMODEL=
COALESCED_TABLE_COLUMNS_LD_EQL_DATAMODEL,
LOADTBL_PK_EQL_MODELTBL=LOADTBL_PK_EQL_MODELTBL,
LOADTBL_PK_EQL_FLAGIND=LOADTBL_PK_EQL_FLAGIND,
NON_PK_COLS_EQL_LD=NON_PK_COLS_EQL_LD,
TABLE_COLUMNS=TABLE_COLUMNS)
bteq_script = bteq_script.upper()
f.write(bteq_script)
f.close()
def bteq_temp_script(cf, source_output_path, STG_tables, script_flag):
if script_flag == 'from stg to datamart':
template_path = cf.templates_path + "/" + pm.default_bteq_stg_datamart_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_bteq_stg_datamart_template_file_name
else:
template_path = cf.templates_path + "/" + pm.default_bteq_oi_stg_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_bteq_oi_stg_template_file_name
template_path_all_pks = cf.templates_path + "/" + pm.default_bteq_stg_datamart_leftjoin_template_file_name
template_smx_path_all_pks = cf.smx_path + "/" + "Templates" + "/" + pm.default_bteq_stg_datamart_leftjoin_template_file_name
today = date.today()
today = today.strftime("%d/%m/%Y")
stg_prefix = cf.stg_prefix
oi_prefix = cf.oi_prefix
data_mart_prefix = cf.dm_prefix
bteq_run_file = cf.bteq_run_file
template_string = ""
template_string_all_pks = ""
try:
template_file = open(template_path, "r")
except:
template_file = open(template_smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
try:
template_file_all_pks = open(template_path_all_pks, "r")
except:
template_file_all_pks = open(template_smx_path_all_pks, "r")
for i in template_file_all_pks.readlines():
if i != "":
template_string_all_pks = template_string_all_pks + i
stg_tables_df = funcs.get_sama_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']
schema_name = stg_tables_df_row['SCHEMA_NAME']
filename = 'UDI_' + schema_name.upper() + '_' + Table_name.upper()
f = funcs.WriteFile(source_output_path, filename, "bteq")
stg_columns = funcs.get_sama_table_columns_comma_separated(
STG_tables, Table_name, 'STG')
table_columns = funcs.get_sama_table_columns_comma_separated(
STG_tables, Table_name)
stg_equal_datamart_pk = funcs.get_conditional_stamenet(
STG_tables, Table_name, 'pk', '=', 'stg', 'dm')
stg_equal_updt_cols = funcs.get_conditional_stamenet(
STG_tables, Table_name, 'stg', '=', None, 'stg')
if stg_equal_datamart_pk != '':
stg_equal_datamart_pk = "ON" + stg_equal_datamart_pk
use_leftjoin_dm_template = funcs.is_all_tbl_cols_pk(
STG_tables, Table_name)
dm_first_pk = funcs.get_stg_tbl_first_pk(STG_tables, Table_name)
# if use_leftjoin_dm_template and script_flag == 'from stg to datamart': #overwrite the template paths if tbl cols are all pk
# template_string = template_string_all_pks
if script_flag == 'from stg to datamart':
if use_leftjoin_dm_template is False:
bteq_script = template_string.format(
currentdate=today,
versionnumber=pm.ver_no,
filename=filename,
bteq_run_file=bteq_run_file,
stg_prefix=stg_prefix,
dm_prefix=data_mart_prefix,
schema_name=schema_name,
table_name=Table_name,
stg_columns=stg_columns,
stg_equal_datamart_pk=stg_equal_datamart_pk,
stg_equal_updt_cols=stg_equal_updt_cols,
table_columns=table_columns)
else:
bteq_script = template_string_all_pks.format(
currentdate=today,
versionnumber=pm.ver_no,
filename=filename,
bteq_run_file=bteq_run_file,
stg_prefix=stg_prefix,
dm_prefix=data_mart_prefix,
schema_name=schema_name,
table_name=Table_name,
stg_columns=stg_columns,
stg_equal_datamart_pk=stg_equal_datamart_pk,
stg_equal_updt_cols=stg_equal_updt_cols,
table_columns=table_columns,
dm_first_pk=dm_first_pk)
elif script_flag == 'from stg to oi':
bteq_script = template_string.format(currentdate=today,
versionnumber=pm.ver_no,
filename=filename,
bteq_run_file=bteq_run_file,
oi_prefix=oi_prefix,
stg_prefix=stg_prefix,
schema_name=schema_name,
table_name=Table_name,
stg_columns=table_columns)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' '))
f.close()
def history_apply(cf, source_output_path, secondary_output_path_HIST,
smx_table, pure_history_flag):
template_path = cf.templates_path + "/" + pm.default_history_apply_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_history_apply_template_file_name
LD_SCHEMA_NAME = cf.ld_prefix
MODEL_SCHEMA_NAME = cf.modelDB_prefix
MODEL_DUP_SCHEMA_NAME = cf.modelDup_prefix
bteq_run_file = cf.bteq_run_file
current_date = funcs.get_current_date()
template_string = ""
try:
template_file = open(template_path, "r")
except:
template_file = open(template_smx_path, "r")
for i in template_file.readlines():
if i != "":
template_string = template_string + i
if pure_history_flag is True:
folder_name = 'Apply_History'
apply_folder_path = path.join(source_output_path, folder_name)
makedirs(apply_folder_path)
history_handeled_df = funcs.get_apply_processes(
smx_table, "Apply_History")
else:
folder_name = 'Apply_History_Legacy'
subfolder_name = 'SUBSEQUENT_LOADS'
apply_folder_path = path.join(source_output_path, folder_name,
subfolder_name)
if not path.exists(apply_folder_path):
makedirs(apply_folder_path)
history_handeled_df = smx_table
record_ids_list = history_handeled_df['Record_ID'].unique()
for r_id in record_ids_list:
history_df = funcs.get_sama_fsdm_record_id(history_handeled_df, r_id)
record_id = r_id
table_name = history_df['Entity'].unique()[0]
SOURCENAME = history_df['Stg_Schema'].unique()[0]
filename = table_name + '_R' + str(record_id)
BTEQ_file_name = "UDI_{}_{}".format(SOURCENAME, filename)
# f = funcs.WriteFile(apply_folder_path, BTEQ_file_name, "bteq")
special_handling_flag = history_df['SPECIAL_HANDLING_FLAG'].unique()[0]
if special_handling_flag.upper() == "N":
f = funcs.WriteFile(apply_folder_path, BTEQ_file_name, "bteq")
else:
if pure_history_flag is False: #not pure history but history legacy subsequent loads
folder_name_Sapecial = 'Apply_History_Legacy/SUBSEQUENT_LOADS'
apply_folder_path_Sapecial = path.join(
secondary_output_path_HIST, folder_name_Sapecial)
if not path.exists(apply_folder_path_Sapecial):
makedirs(apply_folder_path_Sapecial)
f = funcs.WriteFile(apply_folder_path_Sapecial, BTEQ_file_name,
"bteq")
else: #pure history
folder_name_Sapecial = 'Apply_History'
apply_folder_path_Sapecial = path.join(
secondary_output_path_HIST, folder_name_Sapecial)
if not path.exists(apply_folder_path_Sapecial):
makedirs(apply_folder_path_Sapecial)
f = funcs.WriteFile(apply_folder_path_Sapecial, BTEQ_file_name,
"bteq")
filename = filename + '.bteq'
fsdm_tbl_alias = funcs.get_fsdm_tbl_alias(table_name)
ld_tbl_alias = funcs.get_ld_tbl_alias(fsdm_tbl_alias, record_id)
fsdm_tbl_alias = fsdm_tbl_alias + "_FSDM"
strt_date, end_date, hist_keys, hist_cols = funcs.get_history_variables(
history_df, record_id, table_name)
first_history_key = hist_keys[0]
strt_date = strt_date[0]
end_date = end_date[0]
hist_keys_aliased = funcs.get_aliased_columns(hist_keys, ld_tbl_alias)
COALESCED_history_col_LD_EQL_DATAMODEL = funcs.get_comparison_columns(
history_df, table_name, "HISTORY_COL", '=', ld_tbl_alias,
fsdm_tbl_alias, record_id)
# print("COALESCED_history_col_LD_EQL_DATAMODEL", COALESCED_history_col_LD_EQL_DATAMODEL)
if COALESCED_history_col_LD_EQL_DATAMODEL.strip() == "":
COALESCED_history_col_LD_EQL_DATAMODEL = "/* This is a special history case, please refer to the" \
" SMX's Rules column to deduce the needed History_columns " \
"that will be changing */"
ld_fsdm_history_key_and_end_date_equality = funcs.get_conditional_stamenet(
history_df, table_name, 'hist_key_end_date', '=', ld_tbl_alias,
fsdm_tbl_alias, record_id, None)
ld_fsdm_history_key_and_strt_date_equality = funcs.get_conditional_stamenet(
history_df, table_name, 'hist_key_strt_date', '=', ld_tbl_alias,
"FLAG_IND", record_id, None)
# end_date_updt = funcs.get_hist_end_dt_updtt(history_df, table_name, end_date, "=", None,ld_tbl_alias, record_id)
possible_special_handling_comments = ""
if special_handling_flag.upper() == "Y":
possible_special_handling_comments = history_df[
history_df['Historization_Column'].str.upper() ==
'E']['Rule'].values
possible_special_handling_comments = "/*" + str(
possible_special_handling_comments).replace("\n", " ") + "*/"
TBL_COLUMNS = funcs.get_sama_table_columns_comma_separated(
history_df, table_name, None, record_id)
interval = funcs.get_hist_end_Date_interval(history_df, table_name,
record_id)
bteq_script = template_string.format(
SOURCE_SYSTEM=SOURCENAME,
versionnumber=pm.ver_no,
currentdate=current_date,
filename=filename,
bteq_run_file=bteq_run_file,
LD_SCHEMA_NAME=LD_SCHEMA_NAME,
MODEL_SCHEMA_NAME=MODEL_SCHEMA_NAME,
TABLE_COLUMNS=TBL_COLUMNS,
MODEL_DUP_SCHEMA_NAME=MODEL_DUP_SCHEMA_NAME,
TABLE_NAME=table_name,
RECORD_ID=record_id,
ld_alias=ld_tbl_alias,
fsdm_alias=fsdm_tbl_alias,
history_key=hist_keys_aliased,
start_date=strt_date,
first_history_key=first_history_key,
COALESCED_history_col_LD_EQL_DATAMODEL=
COALESCED_history_col_LD_EQL_DATAMODEL,
ld_fsdm_history_key_and_end_date_equality=
ld_fsdm_history_key_and_end_date_equality,
ld_fsdm_history_key_and_strt_date_equality=
ld_fsdm_history_key_and_strt_date_equality,
# end_date_updt=end_date_updt,
end_date=end_date,
interval=interval,
possible_special_handling_comments=
possible_special_handling_comments)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' '))
f.close()