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}")