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 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 get_analytics_stats_conn_cursors(): analytics_conn = PostgresHook(postgres_conn_id='ANALYTICS_DB').get_conn() stats_conn = PostgresHook(postgres_conn_id='STATS_DB').get_conn() analytics_server_cursor = analytics_conn.cursor("analytics_server_cursor") # providing an argument makes this a server cursor, which wouldn't hold # all records in the memory # https://www.psycopg.org/docs/usage.html#server-side-cursors stats_client_cursor = stats_conn.cursor() return (analytics_conn, analytics_server_cursor, stats_conn, stats_client_cursor)
def bq_to_gcs(**kwargs): date_stamp = kwargs['ts'] # get the last current date from Postgres conn = PostgresHook(postgres_conn_id='my_local_db').get_conn() cursor = conn.cursor() cursor.execute('SELECT MAX(last_update_date) FROM airflow.austin_service_reports;') recent_ds = cursor.fetchone()[0] if recent_ds is not None: recent_ds+=timedelta(seconds=1) last = recent_ds else: last = kwargs['start_date']-timedelta(days=1) cursor.close() conn.close() # open connection to BigQuery hook = BigQueryHook( bigquery_conn_id='my_gcp_connection', use_legacy_sql=False ) conn = hook.get_conn() cursor = conn.cursor() with open(SQL_PATH + 'query_bq_dataset.sql', 'r') as f: query = f.read() query = query.format(last, date_stamp) cursor.execute(query) # write to gcs bucket # Each returned row of the result gives: # result = [unique_key, complaint_type, complaint_description, owning_department, source, # status, created_date, last_update_date, close_date, city] with BUCKET.open('bq_bucket/bq_dataset.txt', 'w') as f: while True: result = cursor.fetchone() if result is None: break if result[8] is None: result[8] = '' else: result[8] = datetime.utcfromtimestamp(result[8]) if result[9] is None: result[9] = '' result[7] = datetime.utcfromtimestamp(result[7]) result[6] = datetime.utcfromtimestamp(result[6]) f.write('|'.join([str(val) for val in result]) + '\n') cursor.close() conn.close()
def bq_to_gcs(**kwargs): ds = kwargs['ds'] previous = datetime.strptime(kwargs['prev_ds'], '%Y-%m-%d').date() # get the last current date from Postgres conn = PostgresHook(postgres_conn_id='my_local_db').get_conn() cursor = conn.cursor() cursor.execute('SELECT MAX(CAST(created_date AS DATE)) FROM airflow.austin_service_reports;') recent_ds = cursor.fetchone()[0] if recent_ds is not None: recent_ds+=timedelta(days=1) if recent_ds < previous: prev_ds = datetime.strftime(recent_ds, '%Y-%m-%d') else: prev_ds = kwargs['prev_ds'] else: prev_ds = datetime.strftime(kwargs['start_date']-timedelta(days=1), '%Y-%m-%d') cursor.close() conn.close() # open connection to BigQuery hook = BigQueryHook( bigquery_conn_id='my_gcp_connection', use_legacy_sql=False ) conn = hook.get_conn() cursor = conn.cursor() with open(SQL_PATH + 'query_bq_dataset.sql', 'r') as f: query = f.read() query = query.format(prev_ds,ds) cursor.execute(query) # write to gcs bucket with BUCKET.open('bq_bucket/bq_dataset.csv', 'w') as f: while True: result = cursor.fetchone() if result is None: break if result[6] is None: result[6]= '' else: result[6] = datetime.utcfromtimestamp(result[6]) result[5] = datetime.utcfromtimestamp(result[5]) f.write(','.join([str(val) for val in result]) + '\n') cursor.close() conn.close()
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 set_base_table(**kwargs): import random ti = kwargs['ti'] file = ti.xcom_pull(task_ids='move_csv_to_stagging_area', key='currently_processing') header = tuple(pd.read_csv(file, sep=';', nrows=1).columns) header = header + ('loteid', ) strfy_header = '(' + ','.join(str(s) for s in header) + ')' strfy_header = strfy_header.lower() query_tables = """ SELECT table_name FROM information_schema.tables WHERE table_schema='public';""" dest_conn = PostgresHook(postgres_conn_id='etl_stage_extract').get_conn() dest_cursor = dest_conn.cursor() dest_cursor.execute(query_tables) tables = dest_cursor.fetchmany(size=100) for table_tuple in tables: table_name = table_tuple[0] query_header = """ select column_name from information_schema.columns where table_name = '{}';""".format( table_name) dest_cursor.execute(query_header) headers = dest_cursor.fetchmany(size=1000) hd = '(' + ','.join(str(s[0]).lower() for s in headers) + ')' if hd == strfy_header: ti.xcom_push(key='table', value=table_name) break return tables
def query_database(query: str, target_db: str, table_name, batch_size: int = 100000, **kwargs): s3 = S3Data(table_name, kwargs["ts_nodash"]) total_records = 0 next_batch = 1 connection = PostgresHook(postgres_conn_id=target_db).get_conn() try: # create connection with named cursor to fetch data in batches cursor = connection.cursor(name='query_database') cursor.execute(query) rows = cursor.fetchmany(batch_size) fields = [d[0] for d in cursor.description] while rows: records = [] for row in rows: record = {fields[col]: row[col] for col in range(len(row))} records.append(record) s3.write_key(f'{next_batch:010}.json', records) next_batch += 1 total_records += len(records) rows = cursor.fetchmany(batch_size) finally: if connection: cursor.close() connection.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 get_team_data(): conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn() conn.autocommit = True cur = conn.cursor() params = {"token": Variable.get("workboard_token")} base_url = "https://www.myworkboard.com/wb/apis" r = requests.get(base_url + "/team?include=org_teams", params=params) if r.status_code != 200: raise ValueError("Request not successful. Status code {}".format( r.status_code)) else: teams = r.json()["data"]["team"] logging.info("There are {} teams".format(len(teams))) for t in teams: try: cur.execute( """INSERT INTO teams_raw (team_id, team_data) VALUES (%s, %s) ON CONFLICT (team_id) DO UPDATE SET team_data = EXCLUDED.team_data """, [t["team_id"], json.dumps(t)], ) logging.info("Team id #{} inserted into the table".format( t["team_id"])) except BaseException: logging.warning("Record skipped") pass 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 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 filter_questions() -> str: """ Read all questions from the database and filter them. Returns a JSON string that looks like: [ { "title": "Question Title", "is_answered": false, "link": "https://stackoverflow.com/questions/0000001/...", "tags": ["tag_a","tag_b"], "question_id": 0000001 }, ] """ columns = ("title", "is_answered", "link", "tags", "question_id") filtering_query = """ SELECT title, is_answered, link, tags, question_id FROM public.questions WHERE score >= 1 AND owner_reputation > 1000; """ pg_hook = PostgresHook(postgres_conn_id="postgres_connection").get_conn() with pg_hook.cursor("serverCursor") as pg_cursor: pg_cursor.execute(filtering_query) rows = pg_cursor.fetchall() results = [dict(zip(columns, row)) for row in rows] return json.dumps(results, indent=2)
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 run_and_push(**kwargs): conn = PostgresHook('postgres_default').get_conn() PostgresHook.set_autocommit(PostgresHook(), conn, True) cur = conn.cursor() cur.execute(kwargs['templates_dict']['script']) result = cur.fetchall() row = result[0] kwargs['ti'].xcom_push(key=row[0], value=row[1])
def export_postgres_data_to_csv(filepath, query, **kwargs): connection = PostgresHook(postgres_conn_id='tutorial_general').get_conn() cursor = connection.cursor() cursor.execute(query) data = cursor.fetchall() with open(filepath, 'w') as csv_file: csvwriter = csv.writer(csv_file, delimiter=',') csvwriter.writerow(header[0] for header in cursor.description) for row in data: csvwriter.writerow(row)
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 average_days_open(): conn = PostgresHook(postgres_conn_id='my_local_db').get_conn() cursor = conn.cursor() cursor.execute( 'SELECT AVG(average_num_days_open) FROM airflow.metrics_by_day') days_open = cursor.fetchone() cursor.close() conn.close() return int(days_open[0])
def execute (self, context): # pylint: disable=unused-argument LOG.info ("Executing: %s", str (self.sql)) db = PostgresHook (postgres_conn_id = self.postgres_conn_id).get_conn () rc = [] with db.cursor (cursor_factory = psycopg2.extras.DictCursor) as cur: LOG.info ("Executing SQL: %s", self.sql) cur.execute (self.sql) for rec in cur: rc.append (dict (rec)) return rc
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 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 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 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 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 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 read_data(**kwargs): 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() rows = session.execute("SELECT * from darbiz.forte_express_loan_requests where created_on>='2020-09-20' allow filtering") cur.execute ("select distinct owner profile_id, uid order_id, pay_title from dar_group.bazar_orders1 where created_on>=now()-interval '24' hour") res = cur.fetchall() for user_row in rows: d = json.loads(user_row.loan_request) id0 = user_row.profile_id id1 = user_row.order_id id2 = user_row.created_on pp = d['person_info']['financing_info']['period'] if 'period' in d['person_info']['financing_info'] else None lh = datetime.now() - timedelta(hours = 12) if id2>=lh: for a,b,c in res: ll=c.split() if id1==b: if pp!=int(ll[2]): email = EmailOperator(\ task_id='send_email',\ to=['*****@*****.**','*****@*****.**'],\ subject='Ошибка в Fortemarket',\ html_content='Error in order_id: {} created at: {}, profile_id: {}, months in request: {}, months in orders: {}\n' \ .format(a, id2, b, pp, ll[2])\ ) email.execute(context=kwargs) t3 = SlackWebhookOperator( task_id='send_slack_notification', http_conn_id='slack_connection', message='Error in order_id: {} created at: {}, profile_id: {}, months in request: {}, months in orders: {}\n' \ .format(a, id2, b, pp, ll[2]),\ # files = '/tmp/BPM_report.xlsx', channel='#reports',\ dag=dag ) t3.execute(context=kwargs) else: continue else: continue # lt = d['person_info']['financing_info']['loan_type'] if 'loan_type' in d['person_info']['financing_info'] else None cur.close() conn.close()
def postgres(ds, **kwargs): #this should be a query to insert the data to the postgress query = """ SELECT *FROM marks """ conn = PostgresHook(postgres_conn_id='source', schema='marks').get_conn() # that's going to force the library to create a server cursor cursor = conn.cursor("serverCursor") cursor.execute(query) # now we need to close our connection cursor.close() return "Saved to the database"
def execute(self, context): postgres = PostgresHook( postgres_conn_id=self.postgres_conn_id).get_conn() es = ElasticsearchHook( elasticsearch_conn_id=self.elasticsearch_conn_id) self.log.info("Extracting data from PostgreSQL: %s", self.sql) with postgres.cursor(name="serverCursor", cursor_factory=RealDictCursor) as postgres_cursor: postgres_cursor.itersize = 2000 postgres_cursor.execute(self.sql) for row in postgres_cursor: doc = json.dumps(row, indent=2) es.add_doc(index=self.index, doc_type='external', doc=doc) postgres.close()