def executeSQL(DB_CONF_FILE, sql_script, cod_rule, return_value=False): conn = getDatabaseConnection(DB_CONF_FILE) cur = conn.cursor() try: cur.execute(sql_script) except Exception as e: conn.rollback() scai.processEnd(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY, 3, cod_rule) # SCAI print(e) print(e.pgerror) sys.exit( "The process aborted with error when attempting to execute task calc." ) else: conn.commit() if return_value: result = cur.fetchone() cur.close() conn.close() return result[0] else: cur.close() conn.close()
def copyFromDatabaseToS3(source_conf, target_conf, resources, schema, last_updates_dict, aux_path, scai_last_execution_status=1): print('Connecting to Chandra...') conn = getDatabaseConnection(source_conf) cur = conn.cursor() credentials = getS3Keys(source_conf) sc_conf = json.load(open(source_conf)) #UNLOAD resources data print('Unloading from Chandra...') for resource in resources: print('\t' + resource + ": " + last_updates_dict[resource]) tg_table = 'stg_' + COUNTRY + '_' + resource[4:] # Target table name has the country in the middle of the source table name (for example, stg_d_base_contacts -> stg_pt_d_base_contacts) scai_process_name = scai.getProcessShortDescription(target_conf, tg_table) # SCAI if(scai_last_execution_status==3): scai_process_status = scai.processCheck(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY,scai_last_execution_status) # SCAI # Is normal execution or re-execution starting from the step that was in error if (scai_last_execution_status == 1 or (scai_last_execution_status == 3 and scai_process_status == 3)): scai.processStart(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI try: cur.execute( "UNLOAD ('SELECT * from %(schema)s.%(resource)s "\ " WHERE meta_event_time >= \\\'%(last_update_date)s\\\' "\ " AND base_account_country = \\\'%(BASE_ACCOUNT_COUNTRY)s\\\'') "\ "TO 's3://%(aux_path)s/%(schema)s_%(resource)s/data_' "\ "CREDENTIALS '%(credentials)s' "\ "ESCAPE "\ "manifest;" % { 'schema':schema, 'resource':resource, 'last_update_date':last_updates_dict[resource], 'credentials':credentials, 'aux_path':aux_path, 'BASE_ACCOUNT_COUNTRY':BASE_ACCOUNT_COUNTRY } ) except Exception as e: conn.rollback() scai.processEnd(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'meta_event_time',3) # SCAI scai.integrationEnd(target_conf, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print (e) print (e.pgerror) sys.exit("The process aborted with error.") else: conn.commit() #Enable execution of following processes scai_last_execution_status = 1 #Close connection cur.close() conn.close()
def copyFromS3ToDatabase(target_conf, resources, sc_schema, tg_schema, aux_path, scai_last_execution_status=1): #LOAD to target redshift print('Connecting to Yamato...') conn_target = getDatabaseConnection(target_conf) cur_target = conn_target.cursor() credentials = getS3Keys(target_conf) print('Loading to Yamato...') for resource in resources: tg_table = 'stg_' + COUNTRY + '_' + resource[4:] # Target table name has the country in the middle of the source table name (for example, stg_d_base_contacts -> stg_pt_d_base_contacts) print('Loading %(tg_schema)s.%(tg_table)s...' % {'tg_schema':tg_schema, 'tg_table':tg_table }) scai_process_name = scai.getProcessShortDescription(target_conf, tg_table) # SCAI if(scai_last_execution_status==3): scai_process_status = scai.processCheck(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY,scai_last_execution_status) # SCAI # Is normal execution or re-execution starting from the step that was in error if (scai_last_execution_status == 1 or (scai_last_execution_status == 3 and scai_process_status == 3)): try: cur_target.execute( "TRUNCATE TABLE %(tg_schema)s.%(tg_table)s; "\ "COPY %(tg_schema)s.%(tg_table)s "\ "FROM 's3://%(aux_path)s/%(sc_schema)s_%(resource)s/data_manifest' "\ "CREDENTIALS '%(credentials)s' "\ "REGION 'us-west-2' "\ "ESCAPE "\ "manifest; "\ "ANALYZE %(tg_schema)s.%(tg_table)s;" % { 'tg_schema':tg_schema, 'tg_table':tg_table, 'resource':resource, 'credentials':credentials, 'aux_path':aux_path, 'sc_schema':sc_schema } ) except Exception as e: conn_target.rollback() scai.processEnd(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'meta_event_time',3) # SCAI scai.integrationEnd(target_conf, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print (e) print (e.pgerror) sys.exit("The process aborted with error.") else: conn_target.commit() scai.processEnd(target_conf, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'meta_event_time',1) # SCAI #Enable execution of following processes scai_last_execution_status = 1 cur_target.close() conn_target.close()
def updateFacAutoTask(cod_auto_task, cod_status, message, opr_task=0): try: cur.execute( " update "\ " crm_integration_anlt.t_fac_auto_task "\ " set cod_status = " + cod_status + ", "\ " dat_task_sync = cast(to_char(sysdate,'YYYYMMDD') as integer), "\ " opr_task = case when '" + str(opr_task) + "' = 0 then opr_task else '" + str(opr_task) + "' end "\ " where cod_auto_task = " + str(cod_auto_task) + " ") except Exception as e: scai.processEnd(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) conn.rollback() sys.exit(message) conn.commit()
cur = conn.cursor() #Begin scai execution scai.integrationStart(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY) # SCAI scai.processStart(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY) # SCAI try: cur.execute( " select token "\ " from crm_integration_anlt.t_lkp_token "\ " where 1 = 1 "\ " and application = '" + BASE_APPLICATON + "' ") except Exception as e: scai.processEnd(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) sys.exit("The process aborted with error when attempting to get token.") BASE_TOKEN = cur.fetchone()[0] #print (BASE_TOKEN) try: cur.execute( " select "\ " task.cod_auto_task, "\ " task.content, "\ " task.resource_type, "\ " task.due_date, "\
print('End Query: ' + time.strftime("%H:%M:%S")) print('Starting Updating: ' + time.strftime("%H:%M:%S")) # Threading implementation number_active_threads = 0 number_deals = len(result_list) deals_per_thread = - (-number_deals // MAX_ACTIVE_THREADS) # Ceiling of integer division thread_list = [] i = 0 j = deals_per_thread for n in range(0, MAX_ACTIVE_THREADS): t = threading.Thread(target=updateDealsInBase, args=(client, result_list[i:j])) thread_list.append(t) t.start() print('Spawned thread #' + str(n+1)) i = i + deals_per_thread j = j + deals_per_thread if j > number_deals: j = number_deals for t in thread_list: t.join() print('End of Updating: ' + time.strftime("%H:%M:%S")) scai.processEnd(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, '', '',1) # SCAI scai.integrationEnd(conf_file, COD_INTEGRATION, COD_COUNTRY, 1) # SCAI cur.close() conn.close()
def createDealsInBase(client, result_list, conf_file): number_of_errors = 0 for result in result_list: keep_trying = True while keep_trying: try: if (number_of_errors > MAX_ERRORS_SKIPPED): scai.processEnd(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI sys.exit("The process aborted for exceeding " + str(MAX_ERRORS_SKIPPED) + " errors.") deal = client.deals.create(name=result[0], contact_id=result[1], owner_id=result[2], source_id=result[3], stage_id=result[4], value=result[5], custom_fields={ 'L2 Categoria': result[6], 'L1 Categoria': result[7], 'Payment Date': result[8], 'Transaction ID': result[9], 'Deal created by': result[10] }) break except basecrm.errors.ResourceError as err: scai.logError(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.ResourceError", str(err)) print("Error: basecrm.errors.ResourceError: " + str(err) + "\nSkipping creation of deal for contact with ID " + str(result[1])) number_of_errors = number_of_errors + 1 keep_trying = False except basecrm.errors.ServerError as err: scai.logError(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.ServerError", str(err)) print("Error: basecrm.errors.ServerError. Trying again...") number_of_errors = number_of_errors + 1 except basecrm.errors.RateLimitError as err: scai.logError(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.RateLimitError", str(err)) print( "Error: basecrm.errors.RateLimitError. Trying again in 1 second..." ) number_of_errors = number_of_errors + 1 time.sleep(1) except requests.exceptions.ConnectionError as err: scai.logError(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, "requests.exceptions.ConnectionError", str(err)) print( "Error: requests.exceptions.ConnectionError. Reconnecting and trying again..." ) number_of_errors = number_of_errors + 1 except Exception as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "Exception with general handling", str(err)) print("Error\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1)
def main(conf_file): print('Starting Process... ' + time.strftime("%H:%M:%S")) global BASE_API_TOKEN BASE_API_TOKEN = json.load(open(conf_file))['base_api_token_olxpt'] client = getBaseConnection() # Create Redshift Connection conn = getDatabaseConnection(conf_file) cur = conn.cursor() scai_process_status = scai.processCheck(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, 1) # SCAI #First time ever execution if (not scai_process_status): scai_process_status = 1 #if (scai_process_status != 1): # sys.exit("The integration is already running or there was an error with the last execution that has to be fixed manually.") scai.integrationStart(conf_file, COD_INTEGRATION, COD_COUNTRY) # SCAI scai.processStart(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI print('Starting Data Query... ' + time.strftime("%H:%M:%S")) cur.execute( "select "\ "fac.name, "\ "cast(lkp_contact.opr_contact as integer), "\ "lkp_base_user.opr_base_user, "\ "4616871 as opr_base_source, "\ "case "\ "when "\ "v_lkp_pi.cod_index_type = 1 /* VAS */ "\ "and to_date(fac.paidads_valid_to,'yyyy-mm-dd') /* active package */ < to_date(sysdate,'yyyy-mm-dd') "\ "and last_call < to_date(sysdate,'yyyy-mm-dd') -15 "\ "then 7344246 /* organico */ "\ "else 2950782 /* sales pipeline */ "\ "end as opr_stage, "\ "(fac.price * (-1))/1.23 as val_price, "\ "lkp_category.name_pt sub_category, "\ "lkp_category_parent.name_pt main_category, "\ "to_char(to_date(fac.date,'yyyy/mm/dd'),'dd/mm/yyyy') as dat_payment, "\ "fac.id_transaction, "\ "'Automation' as deal_created_by "\ "from "\ "db_atlas.olxpt_paidads_user_payments fac, "\ "db_atlas.olxpt_users lkp_atlas_user, "\ "db_atlas.olxpt_ads lkp_ad, "\ "db_atlas.olxpt_categories lkp_category, "\ "( "\ "select id opr_category, name_pt, parent_level1, parent_level2, parent_id from db_atlas.olxpt_categories lkp_category "\ "where "\ "parent_level2 is null "\ "and parent_level1 is null "\ ") lkp_category_parent, "\ "crm_integration_anlt.t_lkp_contact lkp_contact, "\ "crm_integration_anlt.t_lkp_base_user lkp_base_user, "\ "crm_integration_anlt.t_lkp_paidad_index lkp_pi, "\ "crm_integration_anlt.t_lkp_paidad_index_type lkp_pit, "\ "crm_integration_anlt.v_lkp_paidad_index v_lkp_pi, "\ "(select cod_contact, to_date(max(updated_at),'yyyy-mm-dd') last_call from crm_integration_anlt.t_fac_call where cod_source_system = 16 group by cod_contact) fac_call "\ "where "\ "fac.id_user = lkp_atlas_user.id "\ "and lkp_contact.cod_source_system = 16 "\ "and lower(lkp_atlas_user.email) = lower(lkp_contact.email) "\ "and fac.id_index = lkp_pi.opr_paidad_index "\ "and lkp_pi.cod_paidad_index_type = lkp_pit.cod_paidad_index_type "\ "and lkp_pit.valid_to = 20991231 "\ "and lkp_pit.cod_source_system = 8 "\ "and lkp_pit.opr_paidad_index_type in ('ad_homepage','highlight','bundle','nnl','pushup','logo','topads','topupaccount','paid_subscription','paid_limits_single','paid_for_post') "\ "and lkp_pi.cod_source_system = 8 "\ "and fac.id_ad = lkp_ad.id "\ "and lkp_ad.category_id = lkp_category.id "\ "and lkp_contact.cod_base_user_owner = lkp_base_user.cod_base_user "\ "and lkp_base_user.cod_source_system = 16 "\ "and lkp_base_user.valid_to = 20991231 "\ "and v_lkp_pi.cod_paidad_index = lkp_pi.cod_paidad_index "\ "and isnull(lkp_category.parent_level1,-2) = lkp_category_parent.opr_category "\ "and fac.price < 0 "\ "and trunc(fac.date) > (select last_processing_datetime from crm_integration_anlt.aut_deals_insert_to_base_date where source_system = 'pthorizontal') "\ "and lkp_contact.valid_to = 20991231 "\ "and lkp_pi.valid_to = 20991231 "\ "and lkp_contact.cod_contact = fac_call.cod_contact (+); ") result_list = cur.fetchall() print('Ending Data Query... ' + time.strftime("%H:%M:%S")) print('Starting Delete Dates Query... ' + time.strftime("%H:%M:%S")) cur.execute( "delete from crm_integration_anlt.aut_deals_insert_to_base_date where source_system = 'pthorizontal'; " ) print('Ending Delete Dates Query... ' + time.strftime("%H:%M:%S")) print('Starting Dates Query... ' + time.strftime("%H:%M:%S")) cur.execute( "insert into crm_integration_anlt.aut_deals_insert_to_base_date "\ "select "\ "'pthorizontal' as source_system, "\ "max(fac.date) "\ "from "\ "db_atlas.olxpt_paidads_user_payments fac, "\ "db_atlas.olxpt_users lkp_atlas_user, "\ "db_atlas.olxpt_ads lkp_ad, "\ "db_atlas.olxpt_categories lkp_category, "\ "( "\ "select id opr_category, name_pt, parent_level1, parent_level2, parent_id from db_atlas.olxpt_categories lkp_category "\ "where "\ "parent_level2 is null "\ "and parent_level1 is null "\ ") lkp_category_parent, "\ "crm_integration_anlt.t_lkp_contact lkp_contact, "\ "crm_integration_anlt.t_lkp_base_user lkp_base_user, "\ "crm_integration_anlt.t_lkp_paidad_index lkp_pi, "\ "crm_integration_anlt.t_lkp_paidad_index_type lkp_pit, "\ "crm_integration_anlt.v_lkp_paidad_index v_lkp_pi "\ "where "\ "fac.id_user = lkp_atlas_user.id "\ "and lkp_contact.cod_source_system = 16 "\ "and lower(lkp_atlas_user.email) = lower(lkp_contact.email) "\ "and fac.id_index = lkp_pi.opr_paidad_index "\ "and lkp_pi.cod_paidad_index_type = lkp_pit.cod_paidad_index_type "\ "and lkp_pit.valid_to = 20991231 "\ "and lkp_pit.cod_source_system = 8 "\ "and lkp_pit.opr_paidad_index_type in ('ad_homepage','highlight','bundle','nnl','pushup','logo','topads','topupaccount','paid_subscription','paid_limits_single','paid_for_post') "\ "and lkp_pi.cod_source_system = 8 "\ "and fac.id_ad = lkp_ad.id "\ "and lkp_ad.category_id = lkp_category.id "\ "and lkp_contact.cod_base_user_owner = lkp_base_user.cod_base_user "\ "and lkp_base_user.cod_source_system = 16 "\ "and lkp_base_user.valid_to = 20991231 "\ "and v_lkp_pi.cod_paidad_index = lkp_pi.cod_paidad_index "\ "and isnull(lkp_category.parent_level1,-2) = lkp_category_parent.opr_category "\ "and fac.price < 0 "\ "and lkp_contact.valid_to = 20991231 "\ "and lkp_pi.valid_to = 20991231; ") conn.commit() print('Ending Dates Query... ' + time.strftime("%H:%M:%S")) print('Starting Deals Creations in Base... ' + time.strftime("%H:%M:%S")) # Threading implementation number_active_threads = 0 number_deals = len(result_list) deals_per_thread = -(-number_deals // MAX_ACTIVE_THREADS ) # Ceiling of integer division thread_list = [] i = 0 j = deals_per_thread for n in range(0, MAX_ACTIVE_THREADS): t = threading.Thread(target=createDealsInBase, args=(client, result_list[i:j], conf_file)) thread_list.append(t) t.start() print('Spawned thread #' + str(n + 1)) i = i + deals_per_thread j = j + deals_per_thread if j > number_deals: j = number_deals for t in thread_list: t.join() print('Ending Deals Creations in Base... ' + time.strftime("%H:%M:%S")) scai.processEnd(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, '', '', 1) # SCAI scai.integrationEnd(conf_file, COD_INTEGRATION, COD_COUNTRY, 1) # SCAI cur.close() conn.close() print('Ending Process... ' + time.strftime("%H:%M:%S"))
def copyHydraTable(db_conf_file, sc_schema, tg_schema, resource, last_update_date, horizontal_name, scai_last_execution_status=1): print('Connecting to Yamato...') conn = getDatabaseConnection(db_conf_file) cur = conn.cursor() tg_table = 'stg_%(COUNTRY)s_%(sc_schema)s_%(resource)s' % { 'resource': resource, 'sc_schema': sc_schema, 'COUNTRY': COUNTRY } scai_process_name = scai.getProcessShortDescription( db_conf_file, tg_table) # SCAI if (scai_last_execution_status == 3): scai_process_status = scai.processCheck( db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, scai_last_execution_status) # SCAI # Is normal execution or re-execution starting from the step that was in error if (scai_last_execution_status == 1 or (scai_last_execution_status == 3 and scai_process_status == 3)): scai.processStart(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI print( 'Loading %(tg_schema)s.%(tg_table)s from %(last_update)s...' % { 'tg_schema': tg_schema, 'tg_table': tg_table, 'last_update': last_update_date }) try: cur.execute( "TRUNCATE TABLE %(tg_schema)s.%(tg_table)s; "\ "INSERT INTO %(tg_schema)s.%(tg_table)s "\ "SELECT "\ " server_date_day, "\ " ad_id, "\ " action_type, "\ " %(horizontal_name)s source, "\ " count(*) occurrences, "\ " count(distinct session_long) distinct_occurrences "\ "FROM hydra.web "\ "WHERE upper(country_code) = '%(HYDRA_COUNTRY_CODE)s' "\ "AND ad_id is not null "\ "AND server_date_day >= '%(last_update_date)s' "\ "GROUP BY server_date_day, ad_id, action_type; "\ "ANALYZE %(tg_schema)s.%(tg_table)s;" % { 'tg_table':tg_table, 'tg_schema':tg_schema, 'horizontal_name':horizontal_name, 'HYDRA_COUNTRY_CODE':HYDRA_COUNTRY_CODE, 'last_update_date':last_update_date } ) except Exception as e: conn.rollback() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'server_date_day', 3) # SCAI scai.integrationEnd(db_conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) sys.exit("The process aborted with error.") else: conn.commit() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'server_date_day', 1) #Enable execution of following processes scai_last_execution_status = 1 # SCAI cur.close() cur.close() # If error was solved here, return new status to use in subsequent processes return scai_last_execution_status
def copyHydraVerticalsTable(db_conf_file, sc_schema, tg_schema, resource, last_update_date, hydra_verticals_names, anlt_verticals_names, scai_last_execution_status=1): print('Connecting to Yamato...') conn = getDatabaseConnection(db_conf_file) cur = conn.cursor() tg_table = 'stg_%(COUNTRY)s_%(sc_schema)s_%(resource)s' % { 'resource': resource, 'sc_schema': sc_schema, 'COUNTRY': COUNTRY } scai_process_name = scai.getProcessShortDescription( db_conf_file, tg_table) # SCAI if (scai_last_execution_status == 3): scai_process_status = scai.processCheck( db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, scai_last_execution_status) # SCAI # Is normal execution or re-execution starting from the step that was in error if (scai_last_execution_status == 1 or (scai_last_execution_status == 3 and scai_process_status == 3)): scai.processStart(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI print( 'Loading %(tg_schema)s.%(tg_table)s from %(last_update)s...' % { 'tg_schema': tg_schema, 'tg_table': tg_table, 'last_update': last_update_date }) # Dynamically build CASE statement according to number of verticals case_statement = "CASE" for i in range(len(anlt_verticals_names)): case_statement += " WHEN lower(host) LIKE '%%" + hydra_verticals_names[ i] + "%%' THEN " + anlt_verticals_names[i] case_statement += " ELSE 'other' END" try: cur.execute( "TRUNCATE TABLE %(tg_schema)s.%(tg_table)s; "\ "INSERT INTO %(tg_schema)s.%(tg_table)s "\ "SELECT "\ " server_date_day, "\ " ad_id, "\ " trackname, "\ " %(case_statement)s source, "\ " count(*) occurrences, "\ " count(distinct session_long) distinct_occurrences "\ "FROM hydra_verticals.web "\ "WHERE upper(country_code) = '%(HYDRA_COUNTRY_CODE)s' "\ "AND ad_id is not null "\ "AND server_date_day >= '%(last_update_date)s' "\ "GROUP BY server_date_day, ad_id, trackname, "\ " %(case_statement)s; "\ "ANALYZE %(tg_schema)s.%(tg_table)s;" % { 'tg_table':tg_table, 'tg_schema':tg_schema, 'HYDRA_COUNTRY_CODE':HYDRA_COUNTRY_CODE, 'last_update_date':last_update_date, 'case_statement':case_statement } ) except Exception as e: conn.rollback() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'server_date_day', 3) # SCAI scai.integrationEnd(db_conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) sys.exit("The process aborted with error.") else: conn.commit() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'server_date_day', 1) # SCAI #Enable execution of following processes scai_last_execution_status = 1 cur.close() cur.close() # If error was solved here, return new status to use in subsequent processes return scai_last_execution_status
def updateContactsInBase(client, contact_list, conf_file, return_queue): number_of_updates = 0 number_of_errors = 0 for contact in contact_list: keep_trying = True while keep_trying: try: if (number_of_errors > MAX_ERRORS_SKIPPED): scai.processEnd(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI sys.exit("The process aborted for exceeding " + str(MAX_ERRORS_SKIPPED) + " errors.") client.contacts.update(contact.id, contact) break except basecrm.errors.ResourceError as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.ResourceError", str(err)) print("Error: basecrm.errors.ResourceError\nDescription: " + str(err) + "\nSkipping update of contact with ID " + str(contact.id)) number_of_errors = number_of_errors + 1 keep_trying = False except basecrm.errors.RequestError as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.RequestError", str(err)) print("Error: basecrm.errors.RequestError\nDescription: " + str(err) + "\nSkipping update of contact with ID " + str(contact.id)) number_of_errors = number_of_errors + 1 keep_trying = False except basecrm.errors.ServerError as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.ServerError", str(err)) print("Error: basecrm.errors.ServerError\nDescription: " + str(err) + "\nTrying again...") number_of_errors = number_of_errors + 1 except basecrm.errors.RateLimitError as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.RateLimitError", str(err)) print("Error: basecrm.errors.RateLimitError\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) except requests.exceptions.ConnectionError as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "requests.exceptions.ConnectionError", str(err)) print( "Error: requests.exceptions.ConnectionError\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) except Exception as err: scai.logError(conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "Exception with general handling", str(err.__class__.__name__) + ": " + str(err)) print("Error\nDescription: " + str(err.__class__.__name__) + ": " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) number_of_updates = number_of_updates + 1 print('Thread done sending contacts to Base!') return_queue.put(number_of_updates)
def main(db_conf_file, conf_file): print(datetime.now().time()) data = json.load(open(conf_file)) cod_source_system = data['cod_source_system'] base_api_token = data['base_api_token'] global DSC_PROCESS DSC_PROCESS = data['dsc_process'] global COD_COUNTRY COD_COUNTRY = int(data['cod_country']) # Create Redshift Connection print('Connecting to Database...') conn = getDatabaseConnection(db_conf_file) cur = conn.cursor() scai.processStart(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY) # SCAI # Obtain the list of custom fields and contacts to update in Base; This is a list of tuples (opr_contact, dsc_custom_field, custom_field_value) print( 'Querying for contacts with custom fields to update to Base with cod_source_system ' + cod_source_system + '...') # TODO: Confirm dsc_process_short name try: cur.execute( "SELECT cast(contact.opr_contact as integer), "\ " custom_field.dsc_custom_field, "\ " fac.custom_field_value "\ "FROM crm_integration_anlt.t_fac_base_integration_snap fac, "\ "crm_integration_anlt.t_lkp_contact contact, "\ "crm_integration_anlt.t_lkp_custom_field custom_field, "\ "crm_integration_anlt.t_rel_scai_integration_process rel, "\ "crm_integration_anlt.t_lkp_scai_process process "\ "WHERE fac.cod_custom_field = custom_field.cod_custom_field "\ "AND fac.cod_contact = contact.cod_contact "\ "AND fac.dat_snap = rel.dat_processing "\ "AND rel.cod_process = process.cod_process "\ "AND process.dsc_process_short = '" + DSC_PROCESS + "' "\ "AND fac.cod_source_system = " + cod_source_system + " "\ "AND contact.valid_to = 20991231;") except Exception as e: scai.processEnd(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(db_conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) sys.exit("The process aborted with error.") print('Extracting query results...') result_list = cur.fetchall() #print('Results:') #print(result_list) print('Closing Database connection...') cur.close() conn.close() print(datetime.now().time()) # Create Base Connection print('Connecting to ' + DSC_PROCESS + '...') client = basecrm.Client(access_token=base_api_token) # Put all query results in a dictionary with key as opr_contact, and value as a list of the tuples the query returned (one for each custom field of that contact) result_dictionary = dict() for result in result_list: if result[CONTACT_ID_IDX] in result_dictionary: result_dictionary[result[CONTACT_ID_IDX]].append(result) else: result_dictionary[result[CONTACT_ID_IDX]] = [result] # Get contacts and iterate through them contact_dictionary = dict() page_nbr = 1 contacts_data = '1' number_of_updates = 0 number_of_errors = 0 while len(contacts_data) > 0: while True: try: if (number_of_errors > MAX_ERRORS_SKIPPED): scai.processEnd(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 3) # SCAI scai.integrationEnd(db_conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI sys.exit("The process aborted for exceeding " + str(MAX_ERRORS_SKIPPED) + " errors.") print('Page #' + str(page_nbr)) contacts_data = client.contacts.list(page=page_nbr, per_page=100) break except basecrm.errors.ServerError as err: scai.logError(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.ServerError", str(err)) print("Error: basecrm.errors.ServerError\nDescription: " + str(err) + "\nTrying again...") number_of_errors = number_of_errors + 1 except basecrm.errors.RateLimitError as err: scai.logError(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "basecrm.errors.RateLimitError", str(err)) print("Error: basecrm.errors.RateLimitError\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) except requests.exceptions.ConnectionError as err: scai.logError(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "requests.exceptions.ConnectionError", str(err)) print( "Error: requests.exceptions.ConnectionError\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) except Exception as err: scai.logError(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, "Exception with general handling", str(err)) print("Error\nDescription: " + str(err) + "\nTrying again in 1 second...") number_of_errors = number_of_errors + 1 time.sleep(1) # Code could be further improved if all contacts are acquired from Base first, put in a dictionary, and then do updates later (will use much more memory, however) # Alternatively, a dictionary with 100 contacts could be created for every iteration here, instead of putting all contacts in a single dictionary; there could still be some time gains compared to current implementation for contact in contacts_data: if contact.id in result_dictionary: for result in result_dictionary[contact.id]: contact.custom_fields[ result[CUSTOM_FIELD_NAME_IDX]] = result[ CUSTOM_FIELD_VALUE_IDX] number_of_updates = number_of_updates + 1 contact_dictionary[contact.id] = contact page_nbr = page_nbr + 1 print(datetime.now().time()) print('Number of updates done in code: ' + str(number_of_updates)) # Update contacts in Base print('Updating #' + str(len(contact_dictionary)) + ' contacts in Base') #input('Ready to send contacts to Base. Proceed?') # Threading implementation number_active_threads = 0 contact_list = list(contact_dictionary.values()) number_contacts = len(contact_list) contacts_per_thread = -(-number_contacts // MAX_ACTIVE_THREADS ) # Ceiling of integer division thread_list = [] thread_return_values_queue = queue.Queue() i = 0 j = contacts_per_thread for n in range(0, MAX_ACTIVE_THREADS): t = threading.Thread(target=updateContactsInBase, args=(client, contact_list[i:j], db_conf_file, thread_return_values_queue)) thread_list.append(t) t.start() print('Spawned thread #' + str(n + 1)) i = i + contacts_per_thread j = j + contacts_per_thread if j > number_contacts: j = number_contacts for t in thread_list: t.join() number_of_updates = 0 while not thread_return_values_queue.empty(): number_of_updates = number_of_updates + thread_return_values_queue.get( ) print('Number of updates done in ' + DSC_PROCESS + ': ' + str(number_of_updates)) scai.processEnd(db_conf_file, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY, '', '', 1) # SCAI print(datetime.now().time()) print('Done\n')
def copyBaseTables(db_conf_file, sc_schema, tg_schema, resources, last_updates_dict, verticals_names='', scai_last_execution_status=1): print('Connecting to Yamato...') conn_target = getDatabaseConnection(db_conf_file) cur_target = conn_target.cursor() for resource in resources: tg_table = 'stg_' + COUNTRY + '_' + resource[ 4:] # Target table name has the country in the middle of the source table name (for example, stg_d_base_contacts -> stg_pt_d_base_contacts) scai_process_name = scai.getProcessShortDescription( db_conf_file, tg_table) # SCAI if (scai_last_execution_status == 3): scai_process_status = scai.processCheck( db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, scai_last_execution_status) # SCAI # Is normal execution or re-execution starting from the step that was in error if (scai_last_execution_status == 1 or (scai_last_execution_status == 3 and scai_process_status == 3)): scai.processStart(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI print( 'Loading %(tg_schema)s.%(tg_table)s from %(last_update)s...' % { 'tg_schema': tg_schema, 'tg_table': tg_table, 'last_update': last_updates_dict[resource] }) try: cur_target.execute( "TRUNCATE TABLE %(tg_schema)s.%(tg_table)s; "\ "INSERT INTO %(tg_schema)s.%(tg_table)s "\ "SELECT * FROM %(sc_schema)s.%(resource)s "\ "WHERE meta_event_time >= '%(last_update_date)s' "\ "AND base_account_country = '%(BASE_ACCOUNT_COUNTRY)s'; "\ "ANALYZE %(tg_schema)s.%(tg_table)s;" % { 'tg_table':tg_table, 'tg_schema':tg_schema, 'sc_schema':sc_schema, 'resource':resource, 'last_update_date':last_updates_dict[resource], 'BASE_ACCOUNT_COUNTRY':BASE_ACCOUNT_COUNTRY } ) except Exception as e: conn_target.rollback() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'meta_event_time', 3) # SCAI scai.integrationEnd(db_conf_file, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI print(e) print(e.pgerror) sys.exit("The process aborted with error.") else: conn_target.commit() scai.processEnd(db_conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, tg_table, 'meta_event_time', 1) # SCAI #Enable execution of following processes scai_last_execution_status = 1 cur_target.close() conn_target.close() # If error was solved here, return new status to use in subsequent processes return scai_last_execution_status