1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
|
import xlrd
from tkinter import *
MenuRequestTmp="test"
def aster_env_gen(type_env,hostname,dbname,user,passwd):
aster_sh_gen_text="##############Environnement input\n\n"+ type_env +"_host_name="+ hostname +"\n"+ type_env +"_user="+ user +"\n"+ type_env +"_password="+passwd+"\n"+ type_env +"_dbname="+dbname+"\n\n#Superuser credential\n"+ type_env +"_sp=db_superuser\n"+ type_env +"_sp_pw=db_superuser\n"
return aster_sh_gen_text
def aster_sh_gen(sql_input,type_script,env_directory,type_env,env_filename,sql_directory):
aster_sh_gen_text=". "+ env_directory + env_filename +"\n \n echo \"-----------------Execution start : 00_DDL_CREATE_STG.sh--------------------Time : \" $(date +'%r')\n echo \"----------------------"+type_script+" table creation---------------------------- Time : \" $(date +'%r')\n act -h ${"+type_env+"_host_name} --username ${"+type_env+"_user} --password ${"+type_env+"_password} --dbname ${"+type_env+"_dbname} -f "+ sql_directory + sql_input +'.sql'
return aster_sh_gen_text
def aster_stg_sql_statment(worksheet, tmp_nrows):
global sql_core_statement
distribute_2=""
sql_core_statement = ''
tmp = 1
distribute = worksheet.cell(tmp, 3).value
while tmp < tmp_nrows - 1:
if tmp == 1 :
sql_start_statement = "\echo *--------------------------Staging table creation : " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value + " -----------------------\nDROP TABLE IF EXISTS " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value + ";\n" + "CREATE TABLE " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp, 2).value + "(\nsource text\n," + worksheet.cell(tmp,3).value + " " + worksheet.cell(tmp, 6).value + worksheet.cell(tmp, 7).value + "\n"
tmp += 1
# current distribute key
if worksheet.cell(tmp + 1, 8).value == "x" and tmp > 1:
distribute_2 = worksheet.cell(tmp + 1, 3).value
# current end statement
if worksheet.cell(tmp, 0).value != worksheet.cell(tmp + 1, 0).value:
sql_core_statement = sql_core_statement + ')\nDISTRIBUTE BY HASH(' + distribute + ') \nCOMPRESS HIGH;\n\n\echo *--------------------------Staging table creation : ' + worksheet.cell(tmp + 1, 1).value + "." + worksheet.cell(tmp + 1,2).value + ' -----------------------\nDROP TABLE IF EXISTS ' + worksheet.cell(tmp + 1, 1).value + "." + worksheet.cell(tmp + 1, 2).value + ";\n" + "CREATE TABLE " + worksheet.cell(tmp + 1, 1).value + "." + worksheet.cell(tmp + 1, 2).value + "(\nsource text,\n" + worksheet.cell(tmp + 1,3).value + " "+ worksheet.cell(tmp + 1, 6).value + worksheet.cell(tmp + 1, 7).value +"\n"
distribute = distribute_2
tmp += 1
# current core statement
if worksheet.cell(tmp, 0).value == worksheet.cell(tmp + 1, 0).value and tmp != 1:
sql_core_statement = sql_core_statement + "," + worksheet.cell(tmp + 1, 3).value + " " + worksheet.cell(tmp + 1, 6).value + worksheet.cell(tmp + 1, 7).value +"\n"
tmp += 1
# build all the statment
sql_statement = sql_start_statement + sql_core_statement + ")\nDISTRIBUTE BY HASH(" + distribute_2 + ")\nCOMPRESS HIGH;"
return sql_statement
def aster_prep_sql_statment(worksheet, tmp_nrows):
global sql_core_statement
distribute_2 = ""
sql_core_statement = ''
tmp = 1
sql_start_statement = "\echo *--------------------------Preparation table creation : " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value + " -----------------------\nDROP TABLE IF EXISTS " + worksheet.cell(1, 1).value + "." + worksheet.cell(1,2).value + ";\n" + "CREATE TABLE " + worksheet.cell(1, 1).value + "." + worksheet.cell(1, 2).value + "(\n" + worksheet.cell(1, 3).value + " "+ worksheet.cell(1, 4).value + worksheet.cell(1, 5).value + "\n"
distribute = worksheet.cell(tmp, 3).value
while tmp < tmp_nrows - 1:
# current distribute key
if worksheet.cell(tmp + 1, 8).value == "x" and tmp > 1:
distribute_2 = worksheet.cell(tmp + 1, 3).value
# current end statement
if worksheet.cell(tmp, 0).value != worksheet.cell(tmp + 1, 0).value:
sql_core_statement = sql_core_statement + ')\nDISTRIBUTE BY HASH(' + distribute + ') \nCOMPRESS HIGH;\n\n\echo *--------------------------Preparation table creation : ' + worksheet.cell(tmp, 1).value + '.' + worksheet.cell(tmp,2).value + ' -----------------------\nDROP TABLE IF EXISTS ' + worksheet.cell(
tmp + 1, 1).value + "." + worksheet.cell(tmp + 1, 2).value + ";\n" + "CREATE TABLE " + worksheet.cell(
tmp + 1, 1).value + "." + worksheet.cell(tmp + 1, 2).value + "(\n" + worksheet.cell(tmp + 1,3).value + " " + worksheet.cell(tmp + 1, 4).value + worksheet.cell(tmp + 1, 5).value + "\n"
distribute = distribute_2
tmp += 1
# current core statement
if worksheet.cell(tmp, 0).value == worksheet.cell(tmp + 1, 0).value:
sql_core_statement = sql_core_statement + "," + worksheet.cell(tmp + 1, 3).value + " "+ worksheet.cell(tmp + 1, 4).value + worksheet.cell(tmp + 1, 5).value + "\n"
tmp += 1
# build all the statment
sql_statement = sql_start_statement + sql_core_statement + ")\nDISTRIBUTE BY HASH(" + distribute_2 + ")\nCOMPRESS HIGH;"
return sql_statement
def aster_insert_prep_sql_statment(worksheet, tmp_nrows):
global sql_core_statement
distribute_2 = ""
sql_core_statement = ''
tmp = 1
while tmp < tmp_nrows - 1:
if tmp == 1 :
sql_start_statement = "\echo *--------------------------Insert into : "+ worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value +" -----------------------\nINSERT INTO " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value + "\n" + "SELECT " + cast_sql_statment(worksheet.cell(tmp,3).value, worksheet.cell(tmp,4).value, worksheet.cell(tmp,6).value) + "\n"
tmp += 1
# current end statement
if worksheet.cell(tmp, 0).value != worksheet.cell(tmp + 1, 0).value and tmp != 1 :
sql_core_statement = sql_core_statement + 'FROM '+ worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value +";\n\n\echo *--------------------------Insert into : "+ worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value +" -----------------------\nINSERT INTO " + worksheet.cell(tmp, 1).value + "." + worksheet.cell(tmp,2).value + "\nSELECT " + cast_sql_statment(worksheet.cell(tmp,3).value, worksheet.cell(tmp,4).value, worksheet.cell(tmp,6).value) + "\n"
tmp += 1
# current core statement
if worksheet.cell(tmp, 0).value == worksheet.cell(tmp + 1, 0).value and tmp!=1:
sql_core_statement = sql_core_statement + "," + cast_sql_statment(worksheet.cell(tmp,3).value, worksheet.cell(tmp,4).value, worksheet.cell(tmp,6).value) + "\n"
tmp += 1
# build all the statment
sql_statement = sql_start_statement + sql_core_statement + " FROM " + worksheet.cell(tmp_nrows-1, 1).value + "." + worksheet.cell(tmp_nrows-1,2).value
return sql_statement
def cast_sql_statment(field, type_origin, type_dest):
cast=''
if type_origin == type_dest:
cast = field
if type_origin != type_dest:
cast='cast(' + str(field) + ' as ' + str(type_dest) + ') as ' + str(field)
return cast
def loader_param_gen(worksheet,tmp_param_nrows,hostname,dbname,user,passwd):
loader_gen_temp=''
tmp_param=1
loader_gen_temp_head ="\n########################################################\n##General parameter\n########################################################\nHostname="+ hostname +" #host de la queen\nUsername="+user+" #Utilisateur de la database\nPassword="+ passwd +" #mots de passe\nDbname="+ dbname +" #Nom de la base de données\nSource= #Chemin du dossier contenant l'arborescence des données \nNb_Param=1 #Number of paramter\nVerbose= #Active le paramètre verbose en entrant --verbose\n\n"
while tmp_param < tmp_param_nrows:
loader_gen_temp = loader_gen_temp + "\n########################################################\n##Parameters to load : "+worksheet.cell(tmp_param, 2).value+" \n########################################################\nSkip_rows["+str(tmp_param)+"]="+ str(int(worksheet.cell(tmp_param, 1).value)) +"\nTableName["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 2).value+"\nFolder["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 3).value+"\nDelimiter["+str(tmp_param)+"]=\""+ worksheet.cell(tmp_param, 4).value+"\"\nCSV["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 5).value+"\nDateFormat["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 6).value+"\nFilePattern["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 7).value+"\nNullValue["+str(tmp_param)+"]="+ worksheet.cell(tmp_param, 8).value
tmp_param+=1
loader_gen = loader_gen_temp_head + loader_gen_temp
return loader_gen
def loader_dataloader_gen(worksheet,tmp_param_nrows):
loader_gen_temp=''
tmp_param=1
loader_gen_temp_head ="\n########################################################\n##DataLoader Version 1.0\n########################################################\n. function\n"
while tmp_param < tmp_param_nrows:
loader_gen_temp = loader_gen_temp + "\nLoad_fact 'd' '" + str(int(worksheet.cell(tmp_param, 0).value)) + "' ##Load : "+ worksheet.cell(tmp_param, 2).value
tmp_param+=1
loader_gen = loader_gen_temp_head + loader_gen_temp
return loader_gen
def var_states() :
print(
"ENV: %d,ENV: %s,ENV: %s,ENV: %s,ENV: %s,\nSQL_STG: %d,\nSQL_PREP: %d,\nSQL_PREP_INSERT: %d,\nBASH: %d,\nPARAM: %d,\nDATALOADER: %d" % (
var1.get(), var8.get(), var9.get(), var10.get(), var11.get(), var2.get(), var3.get(), var4.get(), var5.get(),
var6.get(), var7.get()))
#Action du menu
def MenuRequest(page,menu_items):
global MenuRequestTmp
print(MenuRequestTmp)
if menu_items == "Env":
MenuRequestTmp = "Env"
Label(page, text="\nEnv Variable Generator:", font="bold").grid(row=1, sticky=EW)
var8 = StringVar()
var8.set("------Hostname IP------")
Entry(page, textvariable=var8).grid(row=2, sticky=W, pady=10)
var10 = StringVar()
var10.set("beehive")
Entry(page, textvariable=var10).grid(row=2, sticky=E, pady=10)
var9 = StringVar()
var9.set("es186015")
Entry(page, textvariable=var9).grid(row=3, sticky=W, pady=10)
var11 = StringVar()
var11.set("es186015")
Entry(page, textvariable=var11).grid(row=3, sticky=E, pady=10)
Button(page, text='Execute', command=var_states).grid(row=19, sticky=EW, pady=4)
if menu_items == "About":
Label(page, text="\nThis product was designed by Etienne SIGWALD\n who is a part from Teradata company ", font="bold").grid(row=2, sticky=EW) |
Partager