def pg_to_mssql(**kwargs):
pg_hook = PostgresHook('etl_postgres')
mssql_hook = MsSqlHook('mssql_wtproperty')
# The fields we want to work with
target_fields = [
'master_account_num', 'master_account_name', 'sub_account_num',
'sub_account_name', 'short_name', 'account_status', 'group_num',
'item_num', 'original_balance', 'fed_withholding_tax_this_period',
'ytd_fed_withholding_tax', 'int_paid_this_period', 'ytd_int_paid',
'int_split_this_period', 'escrow_balance'
]
# pull records from postgres
recs = pg_hook.get_records(
f"select {', '.join(target_fields)} from escrow.escrow")
# insert records to mssql
mssql_hook.insert_rows('property_data_escrowbalance', recs, target_fields)
def execute(self, context):
source_hook = WasbHook(wasb_conn_id=self.azure_blob_conn_id)
# Assumption 1: there is sufficient disk space to download the blob in question
# Assumption 2: The file is a correctly formatted csv file
with NamedTemporaryFile(mode='a+', delete=True) as f:
source_hook.get_file(file_path=f.name,
container_name=self.src_blob_container,
blob_name=self.src_blob)
f.flush()
self.log.info("Saving file to %s", f.name)
csv_reader = reader(f)
list_of_tuples = list(map(tuple, csv_reader))
self.log.info(list_of_tuples)
self.log.info(f"Inserting into {self.dest_table}")
hook = MsSqlHook(mssql_conn_id=self.azure_sql_conn_id,
schema=self.database)
hook.insert_rows(self.dest_table, list_of_tuples)
self.log.info(f"Data inserted into {self.database}.{self.dest_table}")