def transfer_oltp_olap(**kwargs):
"""Get records from OLTP and transfer to OLAP database"""
dest_table = kwargs.get('dest_table')
sql = kwargs.get('sql')
params = kwargs.get('params')
oltp_hook = PostgresHook(postgres_conn_id='oltp')
olap_hook = PostgresHook(postgres_conn_id='olap')
data_extracted = oltp_hook.get_records(sql=sql, parameters=params)
olap_hook.insert_rows(dest_table, data_extracted, commit_every=1000)
def execute(self, context):
"""Establish connections to both MySQL & PostgreSQL databases, open
cursor and begin processing query, loading chunks of rows into
PostgreSQL. Repeat loading chunks until all rows processed for query.
"""
source = MySqlHook(mysql_conn_id=self.mysql_conn_id)
target = PostgresHook(postgres_conn_id=self.postgres_conn_id)
with closing(source.get_conn()) as conn:
with closing(conn.cursor()) as cursor:
cursor.execute(self.sql, self.params)
target_fields = [x[0] for x in cursor.description]
row_count = 0
rows = cursor.fetchmany(self.rows_chunk)
while len(rows) > 0:
row_count += len(rows)
target.insert_rows(
self.postgres_table,
rows,
target_fields=target_fields,
commit_every=self.rows_chunk,
)
rows = cursor.fetchmany(self.rows_chunk)
self.log.info(
f"{row_count} row(s) inserted into {self.postgres_table}.")
