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_delete_insert_apply(cf, source_output_path,
secondary_output_path_HIST, smx_table):
hist_load_types = funcs.get_history_load_types(smx_table)
folder_name = 'Apply_History_Delete_Insert'
apply_folder_path = path.join(source_output_path, folder_name)
makedirs(apply_folder_path)
template_path = cf.templates_path + "/" + pm.default_history_deleteInsert_apply_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_history_deleteInsert_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_DELETE_INSERT")
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)
# 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:
f = funcs.WriteFile(secondary_output_path_HIST, BTEQ_file_name,
"bteq")
possible_special_handling_comments = ""
if special_handling_flag.upper() == "Y":
possible_special_handling_comments = "/* Please refer to the SMX for this record id to check the comments in the Rule column*/"
tbl_columns = funcs.get_sama_table_columns_comma_separated(
history_df, table_name, None, record_id)
bteq_script = template_string.format(
source_system=source_name,
table_name=table_name,
record_id=record_id,
currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_Schema_name=LD_SCHEMA_NAME,
model_schema_name=MODEL_SCHEMA_NAME,
table_columns=tbl_columns,
possible_special_handling_comments=
possible_special_handling_comments)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' '))
f.close()
def TFN_insertion(cf, source_output_path, secondary_output_path_TFN,
SMX_SHEET):
template_path = cf.templates_path + "/" + pm.default_TFN_template_file_name
template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.default_TFN_template_file_name
concat_template_path = cf.templates_path + "/" + pm.tfn_concat_template_name
concat_template_smx_path = cf.smx_path + "/" + "Templates" + "/" + pm.tfn_concat_template_name
bteq_run_file = cf.bteq_run_file
ld_prefix = cf.ld_prefix
STG_prefix = cf.stg_prefix
current_date = funcs.get_current_date()
# Source_name = cf.sgk_source
# if Source_name != 'ALL':
# SMX_SHEET = SMX_SHEET[SMX_SHEET['Ssource'] == Source_name]
SMX_SHEET = funcs.get_apply_processes(SMX_SHEET, "TFN")
# print(SMX_SHEET['Column'])
template_string = ""
template_head = ""
concat_template_string = ""
concat_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
try:
concat_template_file = open(concat_template_path, "r")
except:
concat_template_file = open(concat_template_smx_path, "r")
concat_template_start = 0
concat_template_head_line = 0
for i in concat_template_file.readlines():
if i != "":
if i[0] == '#' and concat_template_head_line >= concat_template_start:
concat_template_head = concat_template_head + i
concat_template_head_line = concat_template_head_line + 1
else:
concat_template_string = concat_template_string + i
concat_template_start = concat_template_head_line + 1
tfn_concat_file_name = 'TFN_CONCAT'
f_c = funcs.WriteFile(source_output_path, tfn_concat_file_name, "bteq")
f_c.write(template_head)
record_ids_list = SMX_SHEET['Record_ID'].unique()
bteq_script = ""
for record_id in record_ids_list:
TFN_record_id_df = funcs.get_sama_fsdm_record_id(SMX_SHEET, record_id)
# print(TFN_record_id_df['Column'])
Record_id = record_id
Source_name = TFN_record_id_df['Stg_Schema'].unique()[0]
from_Source_name = Source_name
fsdm_table_name = TFN_record_id_df['Entity'].unique()[0]
ld_table_name = fsdm_table_name + "_R" + str(Record_id)
BTEQ_file_name = "{}_{}_R{}".format(Source_name, fsdm_table_name,
Record_id)
special_handline_flag = TFN_record_id_df[
'SPECIAL_HANDLING_FLAG'].unique()[0]
if special_handline_flag.upper() == "N":
f = funcs.WriteFile(source_output_path, BTEQ_file_name, "bteq")
f.write(template_head)
else:
f = funcs.WriteFile(secondary_output_path_TFN, BTEQ_file_name,
"bteq")
f.write(template_head)
ld_tbl_columns = funcs.get_fsdm_tbl_columns(TFN_record_id_df,
alias_name=None)
col_mapping = funcs.get_TFN_column_mapping(TFN_record_id_df)
# src_table = funcs.get_Rid_Source_Table(TFN_record_id_df)
src_table = TFN_record_id_df['From_Rule'].unique()[0]
STG_tbl_alias = src_table
# join_clause = TFN_record_id_df['Join_Rule'].unique()[0]
try:
join_clause = funcs.enject_alias_in_TFN_join(SMX_SHEET, Record_id)
except:
join_clause = TFN_record_id_df['Join_Rule'].unique()[0]
where_clause = TFN_record_id_df['Filter_Rule'].unique()[0]
where_clause = where_clause.upper()
# if where_clause.split(' ', 2)[0].upper() == 'GROUP' and where_clause.split(' ', 2)[1].upper() == 'BY':
# where_clause_comment = '-- smx menationed : ' + where_clause + ' so it was repalced by distinct grouping'
# columns_Count = len(TFN_record_id_df.index)
# where_clause = 'GROUP BY '
# for i in range(columns_Count):
# j = i+1
# where_clause = where_clause + str(j) + ', '
# where_clause = where_clause[0:len(where_clause)-2]
# where_clause = where_clause + where_clause_comment
if where_clause.split(' ', 1)[0].upper() not in [
'QUALIFY', 'GROUP', 'WHERE'
] and where_clause != "" and 'GROUP BY ' not in where_clause.upper():
where_clause = 'WHERE' + ' ' + where_clause
if 'GROUP BY ' in where_clause:
#print('ll', where_clause.split('GROUP BY', 1))
group_by_columns = where_clause.split('GROUP BY', 1)[1]
STG_db_prefix = '(Select {} from '.format(
group_by_columns) + STG_prefix
STG_tbl_alias = where_clause + ')' + STG_tbl_alias
where_clause = ' '
else:
STG_db_prefix = STG_prefix
left_joins = funcs.rule_col_analysis_sgk(TFN_record_id_df)
left_joins = "\n" + left_joins if left_joins != "" else left_joins
bteq_script = template_string.format(currentdate=current_date,
bteq_run_file=bteq_run_file,
ld_prefix=ld_prefix,
FSDM_tbl_Name=fsdm_table_name,
Source_name=Source_name,
from_Source_name=from_Source_name,
Record_Id=Record_id,
ld_tbl_name=ld_table_name,
ld_tbl_cols=ld_tbl_columns,
Column_Mapping=col_mapping,
STG_prefix=STG_db_prefix,
STG_tbl=src_table,
STG_tbl_alias=STG_tbl_alias,
possible_left_joins=join_clause,
possible_filters=where_clause)
concat_bteq_script = concat_template_string.format(
ld_prefix=ld_prefix,
FSDM_tbl_Name=fsdm_table_name,
Source_name=Source_name,
Record_Id=Record_id,
ld_tbl_name=ld_table_name,
ld_tbl_cols=ld_tbl_columns,
Column_Mapping=col_mapping,
STG_prefix=STG_db_prefix,
STG_tbl=src_table,
possible_left_joins=left_joins)
bteq_script = bteq_script.upper()
f.write(bteq_script.replace('Â', ' '))
f_c.write(concat_bteq_script.replace('Â', ' ') + "\n")
f.close()
f_c.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, 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()