def execute(self, context: Context):
source_hook = BaseHook.get_hook(self.source_conn_id)
destination_hook = BaseHook.get_hook(self.destination_conn_id)
self.log.info("Extracting data from %s", self.source_conn_id)
self.log.info("Executing: \n %s", self.sql)
get_records = getattr(source_hook, 'get_records', None)
if not callable(get_records):
raise RuntimeError(
f"Hook for connection {self.source_conn_id!r} "
f"({type(source_hook).__name__}) has no `get_records` method")
else:
results = get_records(self.sql)
if self.preoperator:
run = getattr(destination_hook, 'run', None)
if not callable(run):
raise RuntimeError(
f"Hook for connection {self.destination_conn_id!r} "
f"({type(destination_hook).__name__}) has no `run` method")
self.log.info("Running preoperator")
self.log.info(self.preoperator)
run(self.preoperator)
insert_rows = getattr(destination_hook, 'insert_rows', None)
if not callable(insert_rows):
raise RuntimeError(
f"Hook for connection {self.destination_conn_id!r} "
f"({type(destination_hook).__name__}) has no `insert_rows` method"
)
self.log.info("Inserting rows into %s", self.destination_conn_id)
insert_rows(table=self.destination_table,
rows=results,
**self.insert_args)
def mean_fare_per_class(titanic_df_json_str: dict):
"""
# Mean_fare_per_class task
Takes the str of json data from XCOM,
converts it to Pandas dataframe and makes some df aggregations
Then dataframe is converted to the list of tuples and sent to external
local DB
"""
# преобразуем в pandas dataframe и изменяем группировками, агрегациями:
titanic_df = pd.read_json(titanic_df_json_str['titanic_df_json_str'])
df = titanic_df \
.groupby(['Pclass']) \
.agg({'Fare': 'mean'}) \
.reset_index()
# создаем кастом хук, коннектшн берем из предварительно созданного в UI:
pg_hook = BaseHook.get_hook('postgres_default')
# имя тааблицы в локальной БД предварительно задано в UI в Variables. Извлекаем:
pg_table_name = Variable.get('mean_fares_table_name')
# перемалываем датафрейм в список кортежей, приводим типы к стандартным (int и float):
pg_rows = list(df.to_records(index=False))
pg_rows_conv = [(int(t[0]), float(t[1])) for t in pg_rows]
# извлекаем названия полей(колонок) датафрейма:
pg_columns = list(df.columns)
# отправляем данные в локальную БД:
pg_hook.insert_rows(table=pg_table_name,
rows=pg_rows_conv,
target_fields=pg_columns,
commit_every=0,
replace=False)
def test_check_operators(self):
conn_id = "sqlite_default"
captain_hook = BaseHook.get_hook(conn_id=conn_id) # quite funny :D
captain_hook.run("CREATE TABLE operator_test_table (a, b)")
captain_hook.run("insert into operator_test_table values (1,2)")
self.dag.create_dagrun(run_type=DagRunType.MANUAL,
state=State.RUNNING,
execution_date=DEFAULT_DATE)
op = CheckOperator(task_id='check',
sql="select count(*) from operator_test_table",
conn_id=conn_id,
dag=self.dag)
op.run(start_date=DEFAULT_DATE,
end_date=DEFAULT_DATE,
ignore_ti_state=True)
op = ValueCheckOperator(
task_id='value_check',
pass_value=95,
tolerance=0.1,
conn_id=conn_id,
sql="SELECT 100",
dag=self.dag,
)
op.run(start_date=DEFAULT_DATE,
end_date=DEFAULT_DATE,
ignore_ti_state=True)
captain_hook.run("drop table operator_test_table")
def execute(self, context):
source_hook = BaseHook.get_hook(self.source_conn_id)
self.log.info("Extracting data from %s", self.source_conn_id)
self.log.info("Executing: \n %s", self.sql)
results = source_hook.get_records(self.sql)
destination_hook = BaseHook.get_hook(self.destination_conn_id)
if self.preoperator:
self.log.info("Running preoperator")
self.log.info(self.preoperator)
destination_hook.run(self.preoperator)
self.log.info("Inserting rows into %s", self.destination_conn_id)
destination_hook.insert_rows(table=self.destination_table,
rows=results)
def get_db_hook(self):
"""
Get the database hook for the connection.
:return: the database hook object.
:rtype: DbApiHook
"""
return BaseHook.get_hook(conn_id=self.conn_id)
def pivot_dataset(titanic_df_json_str: dict):
"""
# Pivot dataset task
Takes the str of json data from XCOM,
converts it to Pandas dataframe and makes some df aggregations
Then dataframe is converted to the list of tuples and sent to external
local DB
"""
# преобразуем в pandas dataframe и изменяем агрегацией:
titanic_df = pd.read_json(titanic_df_json_str['titanic_df_json_str'])
df = titanic_df.pivot_table(index=['Sex'],
columns=['Pclass'],
values='Name',
aggfunc='count').reset_index()
# создаем кастом хук, коннектшн берем из предварительно созданного в UI:
pg_hook = BaseHook.get_hook('postgres_default')
# имя таблицы в локальной БД предварительно задано в UI в Variables. Извлекаем:
pg_table_name = Variable.get('pivot_table_name')
# перемалываем датафрейм в список кортежей, приводим типы к стандартным (str и int):
pg_rows = list(df.to_records(index=False))
pg_rows_conv = [(t[0], int(t[1]), int(t[2]), int(t[3]))
for t in pg_rows]
# извлекаем названия полей(колонок) датафрейма и приводим их типы к строковому:
pg_columns = list(df.columns)
pg_columns_conv = [
pg_columns[0], '"' + str(pg_columns[1]) + '"',
'"' + str(pg_columns[2]) + '"', '"' + str(pg_columns[3]) + '"'
]
# отправляем данные в локальную БД:
pg_hook.insert_rows(table=pg_table_name,
rows=pg_rows_conv,
target_fields=pg_columns_conv,
commit_every=0,
replace=False)
def get_db_hook(self):
"""Returns DB hook"""
return BaseHook.get_hook(conn_id=self.conn_id)
def connection_operator(**context):
postg_hook = BaseHook.get_hook('airflow_dwh')
