def move_clean_to_conform(**kwargs):
ti = kwargs['ti']
file = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='currently_processing')
lote_id = ti.xcom_pull(task_ids='generate_unique_lote_id', key='lote_id')
source = ti.xcom_pull(task_ids='move_csv_to_stagging_area', key='source')
file_size = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='file_size')
table = ti.xcom_pull(task_ids='set_base_table', key='table')
query = """SELECT * FROM {} WHERE loteId = '{}';""".format(table, lote_id)
src_conn = PostgresHook(postgres_conn_id='etl_stage_clean').get_conn()
dest_conn = PostgresHook(postgres_conn_id='etl_stage_conform').get_conn()
src_cursor = src_conn.cursor()
dest_cursor = dest_conn.cursor()
src_cursor.execute(query)
count = 0
set_lote_id_begin(dest_cursor, lote_id, source, file_size)
while True:
records = src_cursor.fetchmany(size=1)
if not records:
break
query_insert = """INSERT INTO {} VALUES %s""".format(table)
execute_values(dest_cursor, query_insert, records)
dest_conn.commit()
count += 1
set_lote_id_end(dest_cursor, lote_id, count, source, file_size)
dest_conn.commit()
src_cursor.close()
dest_cursor.close()
src_conn.close()
dest_conn.close()
return True
def load_transfer(**kwargs):
# Collect days using function that accesses postgres
days_open = average_days_open()
conn = PostgresHook(postgres_conn_id='my_local_db').get_conn()
cursor = conn.cursor()
# Find most recent pull
cursor.execute('SELECT MAX(last_update_date) FROM airflow.transfer_table')
start_date = cursor.fetchone()[0]
if not start_date:
cursor.execute(
'SELECT MIN(last_update_date) FROM airflow.austin_service_reports')
start_date = cursor.fetchone()[0] + timedelta(seconds=1)
end_date = datetime.strptime(kwargs['ds'],
'%Y-%m-%d').date() - timedelta(days=days_open)
# Remove previous data in transfer table
cursor.execute('DELETE FROM airflow.transfer_table')
with open(SQL_PATH + 'insert_into_transfer_table.sql') as f:
insert = f.read().format(start_date, end_date)
cursor.execute(insert)
conn.commit()
cursor.close()
conn.close()
def load_csv_extract_db(**kwargs):
ti = kwargs['ti']
file = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='currently_processing')
lote_id = ti.xcom_pull(task_ids='generate_unique_lote_id', key='lote_id')
source = ti.xcom_pull(task_ids='move_csv_to_stagging_area', key='source')
file_size = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='file_size')
table = ti.xcom_pull(task_ids='set_base_table', key='table')
dest_conn = PostgresHook(postgres_conn_id='etl_stage_extract').get_conn()
dest_cursor = dest_conn.cursor()
header = tuple(pd.read_csv(file, sep=';', nrows=1).columns)
header = header + ('loteid', )
strfy_header = '(' + ','.join(str(s) for s in header) + ')'
count = 0
set_lote_id_begin(dest_cursor, lote_id, source, file_size)
for df_row in pd.read_csv(file, sep=';', chunksize=1):
row = [tuple(x) for x in df_row.values]
row = row[0]
row = row + (lote_id, )
strfy_row = '(' + ','.join('\'' + str(s) + '\'' for s in row) + ')'
query = """INSERT INTO {} {} VALUES {};""".format(
table, strfy_header, strfy_row)
dest_cursor.execute(query)
count += 1
if count >= 100:
break
set_lote_id_end(dest_cursor, lote_id, count, source, file_size)
dest_conn.commit()
dest_cursor.close()
dest_conn.close()
return True
def etl(ds, **kwargs):
execution_date = kwargs['execution_date'].strftime('%Y-%m-%d')
query = """
SELECT *
FROM users
WHERE created_at::date = date '{}'
""".format(execution_date)
src_conn = PostgresHook(postgres_conn_id='source',
schema='source_schema').get_conn()
dest_conn = PostgresHook(postgres_conn_id='dest',
schema='dest_schema').get_conn()
# notice this time we are naming the cursor for the origin table
# that's going to force the library to create a server cursor
src_cursor = src_conn.cursor("serverCursor")
src_cursor.execute(query)
dest_cursor = dest_conn.cursor()
# now we need to iterate over the cursor to get the records in batches
while True:
records = src_cursor.fetchmany(size=2000)
if not records:
break
execute_values(dest_cursor, "INSERT INTO users VALUES %s", records)
dest_conn.commit()
src_cursor.close()
dest_cursor.close()
src_conn.close()
dest_conn.close()
def execute(self, context):
# self.log.info('LoadDimensionOperator not implemented yet')
try:
conn = PostgresHook(self.redshift_conn_id).get_conn()
logging.info(f"Ensuring Valid Records::{self.table_name}")
cursor = conn.cursor()
cursor.execute(f'select count(*) from {self.table_name}')
total_count = cursor.fetchone()
logging.info(f"Total Count::{total_count}")
if total_count == 0:
raise Exception(
f"There are no records for table::{self.table_name}")
conn.commit()
cursor.close()
conn.close()
except Exception as e:
raise e
def start_etl_totals(self, table_name):
"""Gets the data from postgreSQL(countries2020_2021),
transform data(world sum vaccined people), load to table(totals2020_2021)
Parameters:
table_name(string): The name of the table that will be used for loading data
Returns:
bool: True if everything successful
"""
# 1. extract/SELECT data from postgreSQL
path = __file__.split('.')[-2] + '.xml'
create_select_query = ET.parse(path).getroot().find(
'select_query').text.format(table_name=table_name)
df = pd.read_sql_query(
create_select_query,
con=PostgresHook(postgres_conn_id='covid_id').get_conn())
# 2. transform data with pandas for loading to postgreSQL
df = df.drop('country', 1)
dfs = df.sum(axis=0)
# 3. load to posgreSQL
connection = PostgresHook(postgres_conn_id='covid_id').get_conn()
cursor = connection.cursor()
cursor.execute(
"""INSERT INTO totals2020_2021
(fields, total_2020, total_2021)
VALUES ('vaccinated', %s, %s)""",
(int(dfs[0]), int(dfs[1])))
connection.commit()
return True
def csvToPostgres():
#Open Postgres Connection
pg_hook = PostgresHook(postgres_conn_id='airflow_db')
get_postgres_conn = PostgresHook(postgres_conn_id='airflow_db').get_conn()
curr = get_postgres_conn.cursor("cursor")
# CSV loading to table.
with open('/usr/local/airflow/dags/example.csv', 'r') as f:
next(f)
curr.copy_from(f, 'example_table', sep=',')
get_postgres_conn.commit()
def insert_from_copy_table_if_needed(target_db,
table_name=None,
task_instance=None,
run_fetch_task_id=None,
**kwargs):
"""
Inserts from copy table to target table when all tasks succeed, if target table
already exists. The rational behind is not doing any modification on target table
before we make sure fetching from source and insertation is successful.
"""
insert_from_copy_sql = """
DELETE FROM {table_name};
INSERT INTO {table_name}
SELECT * FROM {copy_table_name};
DROP TABLE {copy_table_name};
"""
table_exists = task_instance.xcom_pull(
task_ids='check-if-table-exists')[0][0]
fetcher_state = task_instance.xcom_pull(key='state',
task_ids=run_fetch_task_id)
inserter_state = True
for index in range(constants.INGEST_TASK_CONCURRENCY):
inserter_state = (inserter_state and task_instance.xcom_pull(
key='state',
task_ids=f'execute-insert-into-{index}',
))
if ((table_exists and table_exists != 'None') and fetcher_state is True
and inserter_state is True):
logging.info(f'Inserting from {table_name}_copy table to {table_name}')
try:
target_db_conn = PostgresHook(
postgres_conn_id=target_db).get_conn()
target_db_cursor = target_db_conn.cursor()
target_db_cursor.execute(
insert_from_copy_sql.format(
table_name=sql.Identifier(table_name).as_string(
target_db_conn),
copy_table_name=sql.Identifier(
f'{table_name}_copy').as_string(target_db_conn)))
target_db_conn.commit()
# TODO: Gotta Catch'm all
except Exception as e:
logging.error(f'Exception: {e}')
target_db_conn.rollback()
raise
finally:
if target_db_conn:
target_db_cursor.close()
target_db_conn.close()
else:
logging.info('Target table newly created. No need for copy table')
def execute(self, context):
conn = PostgresHook(self.conn_id).get_conn()
cur = conn.cursor()
for query, exp_result in self.queries_with_exp_result.items:
result = cur.execute(query)
if (exp_result == result[0]):
print(f'Tested {query} and Sucessfully completed')
else:
raise AirflowRescheduleException
self.log.info(
f'DataQualityOperator not for {query} was not successfull')
conn.commit()
def count_users():
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
cur = conn.cursor()
cur.execute(
'delete from dar_group.fm_count_users where date_ = current_date')
cur.execute('insert into dar_group.fm_count_users \
select brand,id_space,current_date as date_,count(distinct identifier_id) as users \
from ( select upper(brand) as brand,id_space,state,identifier_id,created::date \
from dar_group.dar_account where lower(state) like \'%active%\' and lower(brand) = \'forte_market\' \
and lower(id_space) like \'%mobile%\') as a group by brand, id_space'
)
conn.commit()
def execute(self, context):
try:
logging.info("Dimension Operator::Creating Dimension Tables")
conn = PostgresHook(self.redshift_conn_id).get_conn()
logging.info(
f"Dimension Operator::Executing Dimension Query::{self.sql_statement}"
)
cursor = conn.cursor()
cursor.execute(self.sql_statement)
conn.commit()
cursor.close()
conn.close()
except Exception as e:
raise e
def execute(self, context):
copy_query = f"""
copy {self.table}
from {self.s3_src_path}
iam_role {self.iam_role}
format as JSON '{self.json_type}'
region '{self.region}'
{self.extra_params};
"""
conn = PostgresHook(self.conn_id).get_conn()
cur = conn.cursor()
cur.execute(copy_query)
conn.commit()
self.log.info('StageToRedshiftOperator Ran Sucessfully')
def start_etl(self, table_name):
"""Download the data from S3 bucket, transform data, load to table(countries2020_2021)
Parameters:
table_name(string): The name of the table that will be used for loading data
Returns:
bool: True if everything successful
"""
# 1. extract data from s3 to dataframe
client = boto3.client('s3')
path = 's3://covid-exercise/covid-data.csv'
df = pd.read_csv(path)
# 2. transform data(dataframe) with pandas for loading to postgreSQL
q2021 = """SELECT location, SUM (new_vaccinations) FROM df
WHERE new_vaccinations IS NOT NULL AND date LIKE '%2021%' GROUP BY location"""
q2020 = """SELECT location, SUM (new_vaccinations) FROM df WHERE
new_vaccinations IS NOT NULL AND date LIKE '%2020%' GROUP BY location"""
df2021 = ps.sqldf(q2021, locals())
df2020 = ps.sqldf(q2020, locals())
df2021 = df2021.rename(columns={
'location': 'Country',
'SUM (new_vaccinations)': 'Total_2021'
},
inplace=False)
df2020 = df2020.rename(columns={
'location': 'Country',
'SUM (new_vaccinations)': 'Total_2020'
},
inplace=False)
dict1 = df2020.set_index('Country')['Total_2020'].to_dict()
df2021['Total_2020'] = df2021['Country'].map(dict1)
df2021['Total_2021'] = df2021['Total_2021'].fillna(0).astype(int)
df2021['Total_2020'] = df2021['Total_2020'].fillna(0).astype(int)
df2021 = df2021.reindex(
columns=['Country', 'Total_2020', 'Total_2021'])
# 3. load to posgreSQL
connection = PostgresHook(postgres_conn_id='covid_id').get_conn()
cursor = connection.cursor()
for index, row in df2021.iterrows():
cursor.execute(
"""INSERT INTO countries2020_2021
(Country, Total_2020, Total_2021)
VALUES (%s, %s, %s)""",
(row["Country"], row["Total_2020"], row["Total_2021"]))
connection.commit()
return True
def create_target_table(
target_db,
table_name=None,
field_mapping=None,
task_instance=None,
**kwargs,
):
"""Create target database table or delete all from it.
If target table exists, create a copy table as a back up to be used in case of failure.
If target table doesn't exits, create one.
"""
create_table_sql = """
CREATE TABLE {{ table_name }} (
{% for _, tt_field_name, tt_field_constraints in field_mapping %}
{{ tt_field_name }} {{ tt_field_constraints }}{{ "," if not loop.last }}
{% endfor %}
);
"""
table_exists = task_instance.xcom_pull(
task_ids='check-if-table-exists')[0][0]
try:
target_db_conn = PostgresHook(postgres_conn_id=target_db).get_conn()
target_db_cursor = target_db_conn.cursor()
# If table already exists in the target database, create a copy table to be used
# for rollback in case of failiure or create one.
# Until there will be possibility for incremental load
if table_exists and table_exists != 'None':
table_name = f'{table_name}_copy'
logging.info(f'Creating a target table {table_name}')
rendered_create_table_sql = Template(create_table_sql).render(
table_name=sql.Identifier(table_name).as_string(target_db_conn),
field_mapping=field_mapping,
)
target_db_cursor.execute(rendered_create_table_sql)
target_db_conn.commit()
# TODO: Gotta Catch'm all
except Exception as e:
logging.error(f'Exception: {e}')
target_db_conn.rollback()
raise
finally:
if target_db_conn:
target_db_cursor.close()
target_db_conn.close()
def clear_clean_db(**kwargs):
ti = kwargs['ti']
file = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='currently_processing')
lote_id = ti.xcom_pull(task_ids='generate_unique_lote_id', key='lote_id')
source = ti.xcom_pull(task_ids='move_csv_to_stagging_area', key='source')
file_size = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='file_size')
table = ti.xcom_pull(task_ids='set_base_table', key='table')
query = """DELETE FROM {};""".format(table)
dest_conn = PostgresHook(postgres_conn_id='etl_stage_clean').get_conn()
dest_cursor = dest_conn.cursor()
dest_cursor.execute(query)
dest_conn.commit()
dest_conn.close()
return True
def etl():
conn = PostgresHook(postgres_conn_id="postgres_apple").get_conn()
src_cur = conn.cursor()
dest_cur = conn.cursor()
query = """
SELECT *
FROM raw
"""
src_cur.execute(query)
while True:
records = src_cur.fetchone()
if not records:
break
row = []
url = records[2]
soup = bs(records[3], "html.parser")
row.append(
[
url,
apple.get_id_num(url),
apple.get_price(soup),
apple.get_date(soup),
apple.get_screen(soup),
apple.get_color(url),
]
)
try:
dest_cur.execute(
"""INSERT INTO ads (url, id_num, price, date, screen, color)
VALUES (%s, %s, %s, %s, %s, %s)""",
[i for i in row[0]],
)
conn.commit()
logging.info("New record inserted")
except BaseException:
pass
logging.info("Duplicate records")
src_cur.close()
dest_cur.close()
conn.close()
def load_table(**kwargs):
conn = PostgresHook(postgres_conn_id='my_local_db').get_conn()
cursor = conn.cursor()
client = storage.Client()
bucket = client.get_bucket('airy-media-254122.appspot.com')
blob = bucket.get_blob('bq_bucket/bq_dataset.csv')
# create a temporary file and store csv into that to read
tempf = NamedTemporaryFile()
blob.download_to_filename(tempf.name)
query = "COPY airflow.austin_service_reports FROM '"+tempf.name+"' WITH (FORMAT csv)"
cursor.execute(query)
tempf.close()
conn.commit()
cursor.close()
conn.close()
def load_table(**kwargs):
conn = PostgresHook(postgres_conn_id='my_local_db').get_conn()
cursor = conn.cursor()
client = storage.Client()
bucket = client.get_bucket('airy-media-254122.appspot.com')
blob = bucket.get_blob('bq_bucket/bq_dataset.txt')
# create a temporary file and store csv into that to read
tempf = NamedTemporaryFile()
blob.download_to_filename(tempf.name)
with open(SQL_PATH + 'load_postgres_table.sql') as f:
query = f.read().format(tempf.name, kwargs['ts'])
cursor.execute(query)
tempf.close()
conn.commit()
cursor.close()
conn.close()
def agg_count():
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
cur = conn.cursor()
cur.execute(
'delete from dar_group.agg_fm_count_act_product where date_ = current_date'
)
conn.commit()
word1 = '{darbazar}'
word2 = 'Архив'
s = 'insert into dar_group.agg_fm_count_act_product\
select current_date as date_, c.title as category,\
count(distinct f.id) as koltovara\
from dar_group.fm_showcase f\
join dar_group.darbazar_categories c on substring(f.default_category, 7, 36) = c.id\
where f.is_visible = \'true\' and f.scope_ = \'{}\' and c.title != \'{}\' \
group by c.title,f.merchant_id'.format(word1, word2)
cur.execute(s)
conn.commit()
cur.close()
conn.close()
def move_conform_to_final(**kwargs):
ti = kwargs['ti']
file = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='currently_processing')
lote_id = ti.xcom_pull(task_ids='generate_unique_lote_id', key='lote_id')
source = ti.xcom_pull(task_ids='move_csv_to_stagging_area', key='source')
file_size = ti.xcom_pull(task_ids='move_csv_to_stagging_area',
key='file_size')
table = ti.xcom_pull(task_ids='set_base_table', key='table')
query = """SELECT * FROM {} WHERE loteId = '{}';""".format(table, lote_id)
src_conn = PostgresHook(postgres_conn_id='etl_stage_conform').get_conn()
dest_conn = PostgresHook(postgres_conn_id='etl_stage_final').get_conn()
src_cursor = src_conn.cursor()
dest_cursor = dest_conn.cursor()
query_header = """ select column_name from information_schema.columns where table_name = '{}';""".format(
table)
src_cursor.execute(query_header)
headers_result = src_cursor.fetchmany(size=1000)
headers = [str(s[0]).lower() for s in headers_result]
src_cursor.execute(query)
count = 0
set_lote_id_begin(dest_cursor, lote_id, source, file_size)
while True:
records = src_cursor.fetchmany(size=1)
if not records:
break
insert_final_table(dest_cursor, table, records, headers)
count += 1
set_lote_id_end(dest_cursor, lote_id, count, source, file_size)
dest_conn.commit()
src_cursor.close()
dest_cursor.close()
src_conn.close()
dest_conn.close()
return True
def read_cassandra():
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
cur = conn.cursor()
cluster = Cluster(["10.103.5.51", "10.103.5.52", "10.103.5.53"], port=9042)
session = cluster.connect('darbiz', wait_for_all_pools=True)
session.default_timeout = 10000
rows = session.execute('select * from forte_market_digital_orders')
cur.execute('truncate table dar_group.digital_orders')
for row in rows:
d = json.loads(row.order_data)
order_id = d['uid']
cert_amount = d['price']
order_type = d['order_type']
is_gift_cert = d['is_gift_cert']
cert_title = d['cert_title'].strip()
gift_receiver = d['gift_receiver']['name'].strip()
gift_receiver_mail = d['gift_receiver']['email'].strip()
gift_receiver_mobile = d['gift_receiver']['mobile'].strip()
act_code = d['activation_code'].strip()
is_paid = d['paid']
pay_type = d['pay_type'].strip()
cert_name = d['pay_title'].strip()
created_on = d['created_on']
delivery_date = d['delivery_date']
expiry_date = d['expiring_date'] if 'expiring_date' in d else None
is_act = d['is_activated'] if 'is_activated' in d else None
cur.execute ("INSERT INTO dar_group.digital_orders(order_id,order_type,created_dtime,is_gift_cert,cert_title,cert_name,cert_amount,\
activation_code,is_paid,is_activated,pay_type,gift_receiver,gift_receiver_mail,gift_receiver_mobile,delivery_dtime,expiry_dtime) \
VALUES (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s,%s)" , (order_id,order_type,created_on,is_gift_cert,cert_title,cert_name,cert_amount,\
act_code,is_paid,is_act,pay_type,gift_receiver,gift_receiver_mail,gift_receiver_mobile,delivery_date,expiry_date))
conn.commit()
cur.close()
conn.close()
def execute(self, context):
# self.log.info('LoadDimensionOperator not implemented yet')
try:
conn = PostgresHook(self.redshift_conn_id).get_conn()
logging.info(
f"Ensuring Distinct Records::{self.table_name}, on::{self.distinct_column}"
)
cursor = conn.cursor()
cursor.execute(f'select count(*) from {self.table_name}')
total_count = cursor.fetchone()
logging.info(f"Total Count::{total_count}")
cursor.execute(
f'select count (distinct {self.distinct_column}) from {self.table_name}'
)
unique_count = cursor.fetchone()
logging.info(f"Unique Count::{unique_count}")
if total_count != unique_count:
raise Exception(
"Total count does not match unique count. Invalid duplicate records."
)
conn.commit()
cursor.close()
conn.close()
except Exception as e:
raise e
def start_etl_deaths(self, table_name):
"""Fetch covid data from API , transform data and loads to Postgres totals2020_2021
Parameters:
table_name(string): The name of the table that should be used for loading
Returns:
bool: True if everything successful
"""
#fetch API data
response = requests.get(
'https://corona-api.com/countries?include=timeline')
data = json.loads(response.text)
#transform data to totals per 2020 and 2021/today
def countries_deaths_yearly(date):
total_deaths = 0
for i in range(len(data['data'])):
for x in range(len(data['data'][i]['timeline'])):
if data['data'][i]['timeline'][x]['date'] == date:
total_deaths += data['data'][i]['timeline'][x][
'deaths']
return total_deaths
deaths_2020 = countries_deaths_yearly('2020-12-31')
deaths_2021 = countries_deaths_yearly(str(date.today())) - deaths_2020
# load to posgreSQL
connection = PostgresHook(postgres_conn_id='covid_id').get_conn()
cursor = connection.cursor()
cursor.execute(
"""INSERT INTO totals2020_2021
(fields, total_2020, total_2021)
VALUES ('deaths', %s, %s)""", (deaths_2020, deaths_2021))
connection.commit()
return True
def execute_insert_into(target_db,
table_name=None,
run_fetch_task_id=None,
field_mapping=None,
task_instance=None,
**kwargs):
"""Inserts each paginated response data into target database table.
Polls to find variable hasn't been processed, generates regarding sql statement to
insert data in, incrementally waits for new variables.
Success depends on fetcher task completion.
"""
insert_into_sql = """
INSERT INTO {{ table_name }} (
{% for _, tt_field_name, _ in field_mapping %}
{{ tt_field_name }}{{ "," if not loop.last }}
{% endfor %}
)
VALUES
{% for record in record_subset %}
(
{% for st_field_name, _, _ in field_mapping %}
{% if not record[st_field_name] or record[st_field_name] == 'None' %}
NULL
{% else %}
{{ record[st_field_name] }}
{% endif %}
{{ "," if not loop.last }}
{% endfor %}
{{ ")," if not loop.last }}
{% endfor %}
);
"""
# Give some initial time to fetch task to get a page and save it into variable
time.sleep(3)
# Used for providing incremental wait
sleep_time = 5
number_of_run = 1
redis_client = get_redis_client()
table_exists = task_instance.xcom_pull(
task_ids='check-if-table-exists')[0][0]
if table_exists and table_exists != 'None':
table_name = f'{table_name}_copy'
try:
target_db_conn = PostgresHook(postgres_conn_id=target_db).get_conn()
target_db_cursor = target_db_conn.cursor()
while True:
var_name = get_available_page_var(redis_client, run_fetch_task_id)
if var_name:
logging.info(f'Got the unprocessed var_name {var_name}')
sleep_time = 5
var_name = var_name.decode('utf-8')
try:
record_subset = json.loads(Variable.get(var_name))
except KeyError:
logging.info(
f'Var {var_name} no more exist! It is processed by another worker. Moving on.'
)
continue
escaped_record_subset = []
for record in record_subset:
escaped_record = {}
for key, value in record.items():
if value and value != 'None':
escaped_record[key] = sql.Literal(value).as_string(
target_db_conn)
else:
escaped_record[key] = sql.Literal(None).as_string(
target_db_conn)
escaped_record_subset.append(escaped_record)
exec_sql = Template(insert_into_sql).render(
table_name=sql.Identifier(table_name).as_string(
target_db_conn),
field_mapping=field_mapping,
record_subset=escaped_record_subset,
)
target_db_cursor.execute(exec_sql)
logging.info(f'Deleting the var_name {var_name}')
Variable.delete(var_name)
else:
# Check if fetch task completed successfully, if it's, break out of loop and commit
# the transaction because there is no more page to process. If it's failed raise Exception so that
# transaction will be rollbacked
state = task_instance.xcom_pull(key='state',
task_ids=run_fetch_task_id)
logging.info(f'Checking the state of fetcher task {state}')
if state is False:
raise Exception('Fetcher task failed!')
elif state is True:
logging.info(
'Fetcher task successfully completed and there is no more variable to process.'
)
break
else:
logging.info(
f'Sleeping for {sleep_time} fetcher task to catchup')
sleep_time = sleep_time * number_of_run
time.sleep(sleep_time)
number_of_run += 1
target_db_conn.commit()
task_instance.xcom_push(key='state', value=True)
# TODO: Gotta Catch'm all
except Exception as e:
logging.error(f'Exception: {e}')
target_db_conn.rollback()
task_instance.xcom_push(key='state', value=False)
raise
finally:
if target_db_conn:
target_db_cursor.close()
target_db_conn.close()
def execute(self, context):
conn = PostgresHook(self.conn_id).get_conn()
cur = conn.cursor()
cur.execute(self.query)
conn.commit()
self.log.info('LoadDimensionOperator Ran Sucessfully')
def load_bpm_report():
query = '''with comp_state as
(select th1.all_step as completed_state, th1.proc_def_id_ from dar_group.dim_bpm_business_task_history th1
RIGHT JOIN (select --th.all_step as completed_state,
max(th.date_end_t) as date_end_t, th.proc_def_id_
from dar_group.dim_bpm_business_task_history th where th.completed = 'true'
and th.proc_def_id_ is not null and th.all_step is not null and th.date_end_t is not null and application_num is not null
GROUP BY th.proc_def_id_
) th2 on th1.proc_def_id_ = th2.proc_def_id_ and th1.date_end_t = th2.date_end_t)
SELECT
tah.application_num,
tah.start_dtime,
tah.process_name,
tah.proc_inst_id_,
th.task_name,
th.due_dtime,
th.create_dtime,
tah.end_dtime,
emp_curr.employeenumber as curr_employeenumber,
th.user_state AS th_curr_fullname,
emp_curr.fullname as curr_fullname,
emp_curr.position_code as curr_position_code,
emp_curr.position_name as curr_position_name,
emp_curr.specialization_code as curr_specialization_code,
emp_curr.specialization_name as curr_specialization_name,
emp_curr.department_code as curr_department_code,
emp_curr.department_name as curr_department_name,
emp_init.employeenumber AS init_employeenumber,
tah.init_username,
emp_init.fullname AS init_fullname,
emp_init.position_code AS init_position_code,
emp_init.position_name AS init_position_name,
emp_init.specialization_code AS init_specialization_code,
emp_init.specialization_name AS init_specialization_name,
emp_init.department_code as init_department_code,
emp_init.department_name as init_department_name,
emp_init.branch_code as init_branch_code,
emp_init.branch_name as init_branch_name,
case when tah.state = 'true'
then 'COMPLETED'
when tah.state = 'false'
then 'ACTIVE'
else tah.state
end as state,
case when tah.state = 'true'
then th_ct.completed_state
end as completed_state,
inc.incident_msg_,
inc.error_dtime
from dar_group.dim_bpm_business_task_agg_hist tah
full JOIN ( select th.all_step as task_name, th.proc_def_id_, th.due_dtime, th.create_dtime, th.user_state, th.initiator, th.proc_start_date from dar_group.dim_bpm_business_task_history th where --th.date_end_t is null and th.proc_def_id_ is not null
th.completed = 'false' and th.application_num is not null
) AS th
ON tah.proc_inst_id_ = th.proc_def_id_
LEFT JOIN
( SELECT
emp.username,
emp.branch_name,
emp.fullname,
emp.position_name,
emp.specialization_name,
emp.department_name,
emp.position_code,
emp.employeenumber,
emp.department_code,
emp.specialization_code
FROM
dar_group.dim_bpm_employee_profile emp
) AS emp_curr ON th.user_state = emp_curr.username
LEFT JOIN
( SELECT
emp.username,
emp.branch_name,
emp.fullname,
emp.position_name,
emp.specialization_name,
emp.department_name,
emp.position_code,
emp.employeenumber,
emp.department_code,
emp.specialization_code,
emp.branch_code
FROM
dar_group.dim_bpm_employee_profile emp
) AS emp_init ON tah.init_username = emp_init.username
LEFT JOIN (
SELECT i.incident_msg_, e.root_proc_inst_id_, i.incident_timestamp_ as error_dtime
FROM dar_group.bpm_act_ru_incident i, dar_group.bpm_act_ru_execution e where i.proc_inst_id_ = e.id_ --AND e.root_proc_inst_id_ = tah.proc_inst_id_
) AS inc ON tah.proc_inst_id_ = inc.root_proc_inst_id_
LEFT JOIN comp_state as th_ct on tah.proc_inst_id_ = th_ct.proc_def_id_
GROUP BY
tah.application_num,
tah.start_dtime,
tah.process_name,
tah.proc_inst_id_,
th.task_name,
th.due_dtime,
th.create_dtime,
th_curr_fullname,
curr_employeenumber,
curr_fullname,
curr_position_code,
curr_position_name,
curr_specialization_code,
curr_specialization_name,
curr_department_code,
curr_department_name,
tah.init_username,
init_employeenumber,
init_fullname,
init_position_code,
init_position_name,
init_specialization_code,
init_specialization_name,
init_department_code,
init_department_name,
init_branch_code,
init_branch_name,
state,
tah.end_dtime,
completed_state,
inc.incident_msg_,
inc.error_dtime,
tah.state
order by start_dtime desc'''
src_conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
dest_conn = PostgresHook(postgres_conn_id='pgConn_bpm').get_conn()
dest_cursor = dest_conn.cursor()
cur = src_conn.cursor()
dest_cursor.execute('truncate table bpm.fct_process_flow_new')
cur.execute(query)
while True:
records = cur.fetchall()
if not records:
break
execute_values(dest_cursor,
"INSERT INTO bpm.fct_process_flow_new VALUES %s",
records)
dest_conn.commit()
dest_cursor.execute('select * from bpm.fct_process_flow_new')
rec1 = dest_cursor.fetchall()
wb = Workbook()
ws = wb.active
ws.append(['№ заявки','Дата старта процесса','Наименование процесса','Уникальный идентификатор заявки','Название шага, на котором находится заявка', \
'Срок исполнения заявки на шаге, на котором находится заявка','Дата и время поступления заявки на шаг, на котором находится заявка','Дата завершения', \
'ID сотрудника исполнителя','Логин пользователя, на шаге которого находится заявка','ФИО пользователя, на шаге которого находится заявка', \
'ID должности сотрудника, на шаге которого находится заявка','Звание пользователя, на шаге которого находится заявка', \
'ID специализации сотрудника, на шаге которого находится заявка','Направление пользователя, на шаге которого находится заявка', \
'ID подразделения сотрудника, на шаге которого находится заявка','Департамент пользователя, на шаге которого находится заявка','ID сотрудника инициатора', \
'Логин автора заявки','ФИО автора заявки','ID должности сотрудника инициатора','Звание автора заявки','ID специализации сотрудника инициатора','Направление автора заявки', \
'ID подразделения сотрудника инициатора','Департамент автора заявки','ID ГБ/Филиал','ГБ/Филиал','Статус','Последний шаг заявки','Текст ошибки (Message)','Дата и время ошибки'])
for row in rec1:
ws.append(row)
wb.save('/tmp/BPM_report_new.xlsx')
cur.close()
dest_cursor.close()
src_conn.close()
dest_conn.close()
def read_elastic():
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
conn1 = PostgresHook(postgres_conn_id='pgConn_merch').get_conn()
cur = conn.cursor()
cur1 = conn1.cursor()
cur.execute ("delete from dar_group.fk_nomenclature")
conn.commit()
cur1.execute ("delete from merchant.fk_nomenclature")
conn1.commit()
def read_page(page, table):
d = page['hits']['hits']
for ss in d:
source = ss['_source']
try:
temp_dict = {
'id': ss['_id'],
'uid': source['uid'],
'merchant_id': source['merchant_id'],
'name': source['name'] if 'name' in source else None,
#'categories_array': source['categories_array'] if 'categories_array' in source else None,
'amount': source['amount'] if 'amount' in source else None,
'price': source['price'] if 'price' in source else None,
'articul': source['articul'] if 'articul' in source else None,
'status': source['status'] if 'status' in source else None,
'bar_code': source['bar_code'][0] if 'bar_code' in source else None,
'created_on': source['created_on'] if 'created_on' in source else None,
'updated_on': source['updated_on'] if 'updated_on' in source else None,
'is_visible': source['is_visible'] if 'is_visible' in source else None,
'available': source['available'] if 'available' in source else None,
#'product_id': source['product_id'] if 'product_id' in source else None,
'scope': source['scope'] if 'scope' in source else None,
#'sale_channels': source['sale_channels'][0] if 'sale_channels' in source else None,
#'name_ebt': source['name_ebt'] if 'name_ebt' in source else None,
#'sku_id': source['sku_id'] if 'sku_id' in source else None,
'group_id': source['group_id'] if 'group_id' in source else None,
#'vendor': source['vendor'] if 'vendor' in source else None
}
table.append(temp_dict)
except:
pass
def read_catalog():
es = elasticsearch.client.Elasticsearch(["http://10.64.0.156:9200"])
pages = list()
page_size = 1000
page = es.search(
index='kassa_nomen_*',
doc_type='nomenclature',
scroll='3m',
body={
"from": 0,
"size": page_size,
"sort": "created_on"
}
)
read_page(page, pages)
sid = page['_scroll_id']
scroll_size = page['hits']['total']
chunks_count = round(scroll_size / page_size)
for i in range(0, int(chunks_count)):
page = es.scroll(scroll_id=sid, scroll='3m')
sid = page['_scroll_id']
read_page(page, pages)
for j in pages:
id1 = j['id']
id2 = j['uid']
id3 = j['merchant_id']
id4 = j['name']
#id5 = j['categories_array']
id6 = j['amount']
id7 = j['price']
id8 = j['articul']
id9 = j['status']
id10 = j['bar_code']
id11 = j['created_on']
id12 = j['updated_on']
id13 = j['is_visible']
id14 = j['available']
#id15 = j['product_id']
id16 = j['scope']
#id17 = j['sale_channels']
#id18 = j['name_ebt']
#id19 = j['sku_id']
id20 = str(j['group_id'])
try:
cur.execute ("INSERT INTO dar_group.fk_nomenclature(id, uid, merchant_id, name, amount, price, articul, status, bar_code, created_on, updated_on, is_visible, available, scope, group_id)VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",(id1,id2,id3,id4,id6,id7,id8,id9,id10,id11,id12,id13,id14,id16,id20))
cur1.execute ("INSERT INTO merchant.fk_nomenclature(id, uid, merchant_id, name, amount, price, articul, status, bar_code, created_on, updated_on, is_visible, available, scope, group_id)VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",(id1,id2,id3,id4,id6,id7,id8,id9,id10,id11,id12,id13,id14,id16,id20))
except:
pass
read_catalog()
conn.commit()
conn1.commit()
cur.close()
cur1.close()
conn.close()
conn1.close()
class PostgresConnection(Connection):
__instance = None
__postgres_conn_id: str = 'postgres_rds'
__schema: str = 'database_schema'
def __init__(self):
self.connection = PostgresHook(PostgresConnection.__postgres_conn_id,
PostgresConnection.__schema)\
.get_conn()
try:
self.__cursor = self.connection.cursor()
if PostgresConnection.__instance:
raise Exception("Error. Singleton Class here!")
PostgresConnection.__instance = self
super().__init__()
except Exception as e:
logger.warning(
'Exception while connecting to PostgresDB : {}'.format(e))
@staticmethod
def get_instance(self):
if not PostgresConnection.__instance:
PostgresConnection.__instance = PostgresConnection()
return PostgresConnection.__instance
def get_connection(self):
return self.connection
def execute_query(self, query=None):
if query is None:
raise ValueError("Query MUST be provided")
try:
self.__cursor.execute(query)
except Exception as e:
logger.warning("Exception executing query {}".format(e))
logger.info("query failed: {}".format(query))
def execute_queries(self, queries=List[str], batch_size=500) -> None:
if queries is None:
raise ValueError("Queries MUST be provided")
if batch_size > 1000:
raise ValueError("Maximum batch size is 1000")
try:
count = 0
for query in queries:
self.__cursor.execute_query(query)
count += 1
if count >= batch_size:
self.__commit_queries()
count = 0
self.__commit_queries()
except Exception as e:
logger.warning("Exception executing queries: {} ".format(e))
def fetch_many(self, query):
# naming the __cursor for the origin table to force the library to create a server __cursor
# __cursor = self.connection.__cursor("serverCursor")
# get the records in batches
queried_records = []
try:
self.__cursor.execute(query)
while True:
records = self.__cursor.fetchmany(size=500)
if not records:
break
queried_records.extend(records)
except Exception as e:
raise Exception("Postgres connection exception : %s " % e)
finally:
self.__cursor.close()
logger.info("{} records fetched.".format(len(queried_records)))
return queried_records
def __commit_queries(self):
self.connection.commit()
def close(self):
self.connection.close()
def get_aerospike_data():
try:
client = aerospike.client(config).connect()
print(datetime.now(), "Connected to aerospike host:", config['hosts'])
except:
print("Failed to connect to the cluster with", config['hosts'])
sys.exit(1)
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
cur = conn.cursor()
cur.execute('delete from dar_group.merch_com')
conn.commit()
query = client.query('merchants', 'contracts')
records = query.results()
client.close()
print('Starting insert...')
for x in records:
id1 = x[2]['merchant_id'] if 'merchant_id' in x[2] else None
if 'tariffs' in x[2]:
try:
s = json.loads(str(x[2]['tariffs']).replace("'", '"'))
if s == {}:
id4 = ''
id5 = ''
id6 = ''
id7 = ''
id8 = ''
id9 = ''
id10 = ''
id11 = ''
id12 = ''
id13 = ''
id14 = ''
for i in s:
id4 = i
id5 = s[i]['FORTE_EXPRESS_0_4'] if 'FORTE_EXPRESS_0_4' in s[
i] else None
id6 = s[i][
'FORTE_EXPRESS_0_12'] if 'FORTE_EXPRESS_0_12' in s[
i] else None
id7 = s[i][
'FORTE_EXPRESS_0_24'] if 'FORTE_EXPRESS_0_24' in s[
i] else None
id8 = s[i][
'FORTE_EXPRESS_18_6'] if 'FORTE_EXPRESS_18_6' in s[
i] else None
id9 = s[i][
'FORTE_EXPRESS_18_12'] if 'FORTE_EXPRESS_18_12' in s[
i] else None
id10 = s[i][
'FORTE_EXPRESS_18_24'] if 'FORTE_EXPRESS_18_24' in s[
i] else None
id11 = s[i]['CARD_0_0'] if 'CARD_0_0' in s[i] else None
id12 = s[i]['ACQUIRING_0_4'] if 'ACQUIRING_0_4' in s[
i] else None
id13 = s[i]['ACQUIRING_0_6'] if 'ACQUIRING_0_6' in s[
i] else None
id14 = s[i]['ACQUIRING_0_12'] if 'ACQUIRING_0_12' in s[
i] else None
cur.execute(
"INSERT INTO dar_group.merch_com(merchant_id,cat_id,FORTE_EXPRESS_0_4,FORTE_EXPRESS_0_12,FORTE_EXPRESS_0_24,FORTE_EXPRESS_18_6, \
FORTE_EXPRESS_18_12,FORTE_EXPRESS_18_24,CARD_0_0,ACQUIRING_0_4,ACQUIRING_0_6,ACQUIRING_0_12) \
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(id1, id4, id5, id6, id7, id8, id9, id10, id11, id12,
id13, id14))
conn.commit()
print('Written portion...')
except:
pass
cur.close()
conn.close()
def read_data():
cluster = cl(['10.103.5.51', '10.103.5.52', '10.103.5.53'])
session = cluster.connect('darbiz')
conn = PostgresHook(postgres_conn_id='pgConn_pg').get_conn()
cur = conn.cursor()
cur.execute("truncate table dar_group.fm_express_loan;")
rows = session.execute("SELECT * from darbiz.forte_express_loan_requests")
for user_row in rows:
d = json.loads(user_row.loan_request)
id0 = d['profile_id'] if 'profile_id' in d else None
id1 = user_row.order_id
id2 = user_row.created_on
id3 = d['person_info']['surname'] if 'surname' in d[
'person_info'] else None
id4 = d['person_info']['name'] if 'name' in d['person_info'] else None
id5 = d['person_info']['patronymic'] if 'patronymic' in d[
'person_info'] else None
id6 = d['person_info']['birthdate'] if 'birthdate' in d[
'person_info'] else None
id7 = d['person_info']['sex'] if 'sex' in d['person_info'] else None
id8 = d['person_info']['iin'] if 'iin' in d['person_info'] else None
id9 = d['person_info']['mobile_phone'] if 'mobile_phone' in d[
'person_info'] else None
id10 = user_row.status
id11 = d['status'] if 'status' in d else None
# wage = d['person_info']['wage'] if 'wage' in d['person_info'] else None
# chp = d['person_info']['childr
# pr = d['person_info']['financing_info']['product'] if 'product' in d['person_info']['financing_info'] else None
# psc = d['person_info']['financing_info']['product_sub_code'] if 'product_sub_code' in d['person_info']['financing_info'] else None
# ps = d['person_info']['financing_info']['sum'] if 'sum' in d['person_info']['financing_info'] else None
# pp = d['person_info']['financing_info']['period'] if 'period' in d['person_info']['financing_info'] else None
# lt = d['person_info']['financing_info']['loan_type'] if 'loan_type' in d['person_info']['financing_info'] else None
# fp = d['person_info']['financing_info']['fin_purpose'] if 'fin_purpose' in d['person_info']['financing_info'] else None
# pd = d['person_info']['product_info']['description'] if 'description' in d['person_info']['product_info'] else None
# ppr = d['person_info']['product_info']['price'] if 'price' in d['person_info']['product_info'] else None
# pc = d['person_info']['product_info']['category'] if 'category' in d['person_info']['product_info'] else None
# ms = d['person_info']['marital_status'] if 'marital_status' in d['person_info'] else None
# dty = d['person_info']['document_id'] if 'document_id' in d['person_info'] else None
# ib = d['person_info']['issued_by'] if 'issued_by' in d['person_info'] else None
# idt = d['person_info']['issued_date'] if 'issued_date' in d['person_info'] else None
# vdt = d['person_info']['validity_date'] if 'validity_date' in d['person_info'] else None
# pob = d['person_info']['place_of_birth_id'] if 'place_of_birth_id' in d['person_info'] else None
# pobn = d['person_info']['place_of_birth_name'] if 'place_of_birth_name' in d['person_info'] else None
# rpi = d['person_info']['reg_postal_index'] if 'reg_postal_index' in d['person_info'] else None
# rl = d['person_info']['reg_locality'] if 'reg_locality' in d['person_info'] else None
# rmd = d['person_info']['reg_microdistrict'] if 'reg_microdistrict' in d['person_info'] else None
# rs = d['person_info']['reg_street'] if 'reg_street' in d['person_info'] else None
# rhn = d['person_info']['reg_house_number'] if 'reg_house_number' in d['person_info'] else None
# rlw = d['person_info']['reg_live_with'] if 'reg_live_with' in d['person_info'] else None
try:
cur.execute ("INSERT INTO dar_group.fm_express_loan(profile_id,order_id,created_on,surname,name,patronymic,birthdate,sex,iin,mobile_phone,status1,status2) \
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" , \
(id0,id1,id2,id3,id4,id5,id6,id7,id8,id9,id10,id11))
conn.commit()
# cur.execute ("INSERT INTO dar_group.fm_express_loan(profile_id,order_id,created_dtime,last_name,name,mid_name,birth_date,\
# gender,iin,mobile,status) \
# VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", \
# (id0,id1,id2,id3,id4,id5,id6,id7,id8,id9,id10#, \
# pr,psc,pd,pc,ppr,pp,lt,ps,fp,\
# req_ip,psi,wage,chp,\
# ms,dty,ib,idt,vdt,pob,pobn,rpi,rl,rmd,rs,rhn,rlw
# ))
# product_code,product_sub_code,product_name,product_cat,product_price,loan_period,loan_type, \
# loan_sum,fin_purpose, \
# request_ip,profile_size,wage,has_children, \
# marital_status,document_id,issued_by,issued_date,valid_date,birth_place_id,birth_place, \
# post_index,locality,microdistrict,street,house_num,live_with
# ) \
# %s, %s, %s, %s, %s, %s, %s, %s, %s,
#, %s, %s, %s, %s, \
# %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
except Exception as e:
print(e)
cur.close()
conn.close()
