def test_postgres_operator_test_multi(self): sql = [ "CREATE TABLE IF NOT EXISTS test_airflow (dummy VARCHAR(50))", "TRUNCATE TABLE test_airflow", "INSERT INTO test_airflow VALUES ('X')", ] from airflow.providers.postgres.operators.postgres import PostgresOperator op = PostgresOperator( task_id='postgres_operator_test_multi', sql=sql, dag=self.dag) op.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True)
def test_vacuum(self): """ Verifies the VACUUM operation runs well with the PostgresOperator """ sql = "VACUUM ANALYZE;" op = PostgresOperator(task_id='postgres_operator_test_vacuum', sql=sql, dag=self.dag, autocommit=True) op.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True)
def delete_data(symbol, symbol_id, start_ts, end_ts, granularity): """ Deletes rows from table specified by a time range - (start_ts, end_ts) before writing to the table. Args - symbol (str): coinpair which Binance API recognises. - start_ts (int): time range beginning, in epoch timestamp - end_ts (int): time range end, in epoch timestamp - granularity (str): Time period of granularity. ex: 1m, 5m, 15m. See, Binance's API documentation for further options. Returns - Returns JSON object, containing Pandas Dataframe. """ table = "ticker_data_{:s}".format(granularity) CMD = """ DELETE FROM {table} WHERE close_time BETWEEN {start_ts} AND {end_ts} AND symbol_id = {symbol_id} """.format(table=table, start_ts=start_ts, end_ts=end_ts, symbol_id=symbol_id) PostgresOperator( task_id="delete_data_{:s}_{:s}".format(symbol, table), postgres_conn_id=POSTGRES_CONN_ID, sql=CMD, ) return True
def test_overwrite_schema(self): """ Verifies option to overwrite connection schema """ sql = "SELECT 1;" op = PostgresOperator( task_id='postgres_operator_test_schema_overwrite', sql=sql, dag=self.dag, autocommit=True, database="foobar", ) from psycopg2 import OperationalError try: op.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True) except OperationalError as e: assert 'database "foobar" does not exist' in str(e)
def test_postgres_operator_test(self): sql = """ CREATE TABLE IF NOT EXISTS test_airflow ( dummy VARCHAR(50) ); """ op = PostgresOperator(task_id='basic_postgres', sql=sql, dag=self.dag) op.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True) autocommit_task = PostgresOperator( task_id='basic_postgres_with_autocommit', sql=sql, dag=self.dag, autocommit=True) autocommit_task.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True)
def covid_per_popgroup_subdag(parent_dag_id, child_dag_id, args): with DAG( dag_id=f'{parent_dag_id}.{child_dag_id}', default_args=args, #start_date= datetime(2021,5,2), #days_ago(2), #datetime.datetime.now(), #days_ago(2), #schedule_interval = '@once', #tags=['covid'], ) as dag: last_date_popgroup_task = get_last_date_popgroup( task_id="last_date_popgroup_task") current_dir = os.path.dirname(os.path.abspath(__file__)) root_dir = os.path.dirname(current_dir) download_recent_cdc_task = SparkSubmitOperator( task_id="download_recent_cdc_task", conn_id="spark_default", application=os.path.join(root_dir, "python", "stage_recent_cdc.py"), application_args=[ "--apptoken", Variable.get("socrata_apptoken"), "--last_date", "{{ti.xcom_pull( task_ids = 'last_date_popgroup_task', key = 'last_cdc_date')}}", ], ) insert_covid_pergroup_task = PostgresOperator( task_id="insert_covid_pergroup_task", postgres_conn_id="postgres_default", sql=""" INSERT INTO covid_per_popgroup(cdc_case_earliest_dt, sex_id, age_group_id, race_ethnicity_id, count) SELECT cdc_case_earliest_dt, sex_id, age_group_id, race_ethnicity_id, count FROM recent_cdc AS n JOIN dim_age_group AS a ON a.age_group = n.age_group JOIN dim_sex AS s ON s.sex = n.sex JOIN dim_race_ethnicity AS e ON e.race = n.race_ethnicity_combined ; """) last_date_popgroup_task >> download_recent_cdc_task >> insert_covid_pergroup_task return dag
default_args = { 'email_on_retry': False, 'retries': 3, 'retry_delay': timedelta(minutes=5) } with DAG('avocado_dag', default_args=default_args, description='Forecasting avocado prices', schedule_interval='*/10 * * * *', start_date=datetime(2020, 1, 1), catchup=False) as dag: creating_table = PostgresOperator(task_id='creating_table', sql='sql/CREATE_TABLE_ACCURACIES.sql', postgres_conn_id='postgres') downloading_data = PythonOperator(task_id='downloading_data', python_callable=download_dataset) sanity_check = PythonOperator(task_id="sanity_check", python_callable=check_dataset) waiting_for_data = FileSensor(task_id='waiting_for_data', fs_conn_id='fs_default', filepath='avocado.csv', poke_interval=15) n_estimators = [100, 150] max_features = ['auto', 'sqrt']
'password': user['login']['password'], 'email': user['email'] }) processed_user.to_csv('/tmp/processed_user.csv', index=None, header=False) with DAG('user_processing_postgres', schedule_interval='@daily', start_date=datetime(2021, 1, 1), catchup=True) as dag: create_table = PostgresOperator(task_id='create_table', postgres_conn_id='db_postgres', sql=''' CREATE TABLE IF NOT EXISTS test.users ( firstname TEXT NOT NULL, lastname TEXT NOT NULL, country TEXT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT NOT NULL PRIMARY KEY ); ''') is_api_available = HttpSensor(task_id='is_api_available', http_conn_id='user_api', endpoint='api/') extracting_usr = SimpleHttpOperator( task_id='extracting_user', http_conn_id='user_api', endpoint='api/', method='GET',
import datetime from airflow import DAG from airflow.providers.postgres.operators.postgres import PostgresOperator from airflow.utils.dates import days_ago default_args = {'owner': 'Dylan Bragdon'} with DAG( dag_id='create_postgres_tables', start_date=days_ago(2), schedule_interval=None, default_args=default_args, catchup=False, ) as dag: create_post_table = PostgresOperator(task_id='create_tables', postgres_conn_id='hackernews_connect', sql="sql/post_schema.sql")
def covid_subdag(parent_dag_id, child_dag_id, args): with DAG( dag_id=f'{parent_dag_id}.{child_dag_id}', default_args=args, #start_date= datetime(2021,5,2), #days_ago(2), #datetime.datetime.now(), #days_ago(2), #schedule_interval = '@once', #tags=['covid'], ) as dag: download_nyt_task = download_fromweb(task_id="download_nyt") create_recent_per_county_table = PostgresOperator( task_id="create_recent_per_county_table", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_create_recent_per_county_table, ) load_recent_per_county_table = PostgresOperator( task_id="load_recent_per_county_table", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_load_recent_per_county_table, ) # add location_id key to new data add_location_id = PostgresOperator( task_id="add_location_id", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_add_location_id) # to compute daily data from cumulated data, it is necessary to have the previous cumulated data # for some counties with very few cases / deaths, previous data does not appear in the 1-month window # we must thus retrieve it from past data and add it to recent data # this is done in 2 phases : # - First, get min(date) in recent data for each location_id (table min_recent) # - Then, compare with max(date) for past_data : # - if min(recent) > max(past) : add max(past) for location_id to recent table retrieve_past_data = PostgresOperator( task_id="retrieve_past_data", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_retrieve_past_data) # compute daily deaths and cases from cumulated values : # - partition by location_id and order by date # - compute previous cases and deaths by "shifting" the case and deaths column # - compute daily data : cumulated_current - cumulated_prev compute_daily_stats = PostgresOperator( task_id="compute_daily_stats", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_compute_daily_stats) filter_date = PostgresOperator(task_id="filter_date", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_filter_date) update_last_date = PostgresOperator( task_id="update_last_date", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_update_last_date) append_full_per_county = PostgresOperator( task_id="append_full_per_county", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_append_full_per_county) drop_recent_per_county_table = PostgresOperator( task_id="drop_recent_per_county_table", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_drop_recent_per_county_table) drop_recent_per_county_table_final = PostgresOperator( task_id="drop_recent_per_county_table_final", postgres_conn_id="postgres_default", sql=sql_covid_counties.q_drop_recent_per_county_table) download_nyt_task >> drop_recent_per_county_table >> create_recent_per_county_table >> load_recent_per_county_table >> add_location_id >> retrieve_past_data >> compute_daily_stats >> filter_date >> append_full_per_county >> update_last_date >> drop_recent_per_county_table_final return dag
with DAG( 'orchestration_good_practices', start_date=datetime(2021, 1, 1), schedule_interval='@daily', catchup=False, default_args={ 'owner': 'airflow', 'email_on_failure': False, 'retries': 1, 'retry_delay': timedelta(minutes=1) }, ) as dag: opr_refresh_mat_view = PostgresOperator( task_id='refresh_mat_view', postgres_conn_id='postgres_default', sql='REFRESH MATERIALIZED VIEW example_view;', ) opr_submit_run = DatabricksSubmitRunOperator( task_id='submit_run', databricks_conn_id='databricks', new_cluster=new_cluster, notebook_task=notebook_task) opr_run_now = DatabricksRunNowOperator(task_id='run_now', databricks_conn_id='databricks', job_id=5, notebook_params=notebook_params) opr_refresh_mat_view >> opr_submit_run >> opr_run_now
dag_id='dag_psql', default_args=default_args, schedule_interval='@monthly', start_date=datetime( 2021, 4, 27), #days_ago(2), #datetime.datetime.now(), #days_ago(2), tags=["covid", "psql"], ) as dag: create_recent_per_county_table = PostgresOperator( task_id="create_recent_per_county_table", postgres_conn_id="postgres_default", sql=""" CREATE TABLE IF NOT EXISTS recent_per_county ( date date, county text, state text, fips text, cases int, deaths int ); """, ) load_recent_per_county_table = PostgresOperator( task_id="load_recent_per_county_table", postgres_conn_id="postgres_default", sql=""" COPY recent_per_county FROM '/tmp/us-counties-recent.csv' WITH CSV HEADER ; """) # add location_id key to new data add_location_id = PostgresOperator(task_id="add_location_id",
# create_pet_table, populate_pet_table, get_all_pets, and get_birth_date are examples of tasks created by # instantiating the Postgres Operator with DAG( dag_id="postgres_operator_dag", start_date=datetime.datetime(2020, 2, 2), schedule_interval="@once", catchup=False, ) as dag: # [START postgres_operator_howto_guide_create_pet_table] create_pet_table = PostgresOperator( task_id="create_pet_table", sql=""" CREATE TABLE IF NOT EXISTS pet ( pet_id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, pet_type VARCHAR NOT NULL, birth_date DATE NOT NULL, OWNER VARCHAR NOT NULL); """, ) # [END postgres_operator_howto_guide_create_pet_table] # [START postgres_operator_howto_guide_populate_pet_table] populate_pet_table = PostgresOperator( task_id="populate_pet_table", sql=""" INSERT INTO pet (name, pet_type, birth_date, OWNER) VALUES ( 'Max', 'Dog', '2018-07-05', 'Jane'); INSERT INTO pet (name, pet_type, birth_date, OWNER) VALUES ( 'Susie', 'Cat', '2019-05-01', 'Phil'); INSERT INTO pet (name, pet_type, birth_date, OWNER)
'retry_delay': timedelta(minutes=2), 'catchup': False } append_data = Variable.get('load_mode') == 'append-only' dag = DAG('udac_example_dag', default_args=default_args, description='Load and transform data in Redshift with Airflow', schedule_interval='0 * * * *', max_active_runs=1) start_operator = DummyOperator(task_id='Begin_execution', dag=dag) create_tables = PostgresOperator(task_id='Create_tables', dag=dag, sql='create_tables.sql', postgres_conn_id='redshift') stage_events_to_redshift = StageToRedshiftOperator( task_id='Stage_events', dag=dag, table='staging_events', redshift_conn_id='redshift', aws_credentials_id='aws_credentials', s3_bucket='udacity-dend', s3_key='log_data/', aws_region='us-west-2', jsonpath='log_json_path.json') stage_songs_to_redshift = StageToRedshiftOperator( task_id='Stage_songs',
with open("/tmp/wikipageviews", "r") as f: for line in f: domain_code, page_title, view_counts, _ = line.split(" ") if domain_code == "en" and page_title in pagenames: result[page_title] = view_counts with open("/tmp/postgres_query.sql", "w") as f: for pagename, pageviewcount in result.items(): f.write("INSERT INTO pageview_counts VALUES (" f"'{pagename}', {pageviewcount}, '{execution_date}'" ");\n") fetch_pageviews = PythonOperator( task_id="fetch_pageviews", python_callable=_fetch_pageviews, op_kwargs={ "pagenames": {"Google", "Amazon", "Apple", "Microsoft", "Facebook"} }, dag=dag, ) write_to_postgres = PostgresOperator( task_id="write_to_postgres", postgres_conn_id="my_postgres", sql="postgres_query.sql", dag=dag, ) get_data >> extract_gz >> fetch_pageviews >> write_to_postgres
{% endfor %} """ t3 = BashOperator( task_id='templated', depends_on_past=False, bash_command=templated_command, params={'my_param': 'Parameter I passed in'}, dag=dag, ) conn_db_src = PostgresHook(postgres_conn_id='PostgresNDoD') sqlalchemy_engine = conn_db_src.get_sqlalchemy_engine() create_nDoD_tables = PostgresOperator(task_id="create_nDoD_tables", postgres_conn_id="PostgresNDoD", sql="./sql/nDoD_schema.sql") def load_utp_group(filename, conn_db, schema_db, **kwargs): """Load data into db""" df = pd.read_csv(filename, sep=';') logging.info('CSV in pandas \n %s', df.head().to_string()) conn = conn_db.connect() conn.execute("TRUNCATE TABLE ndod.utp_group CASCADE") df.to_sql('utp_group', con=conn_db, schema=schema_db,
# create_pet_table, populate_pet_table, get_all_pets, and get_birth_date are examples of tasks created by # instantiating the Postgres Operator with DAG( dag_id="postgres_operator_dag", start_date=datetime.datetime(2020, 2, 2), schedule_interval="@once", catchup=False, ) as dag: # [START postgres_operator_howto_guide_create_pet_table] create_pet_table = PostgresOperator( task_id="create_pet_table", sql=""" CREATE TABLE IF NOT EXISTS pet ( pet_id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, pet_type VARCHAR NOT NULL, birth_date DATE NOT NULL, OWNER VARCHAR NOT NULL); """, ) # [END postgres_operator_howto_guide_create_pet_table] # [START postgres_operator_howto_guide_populate_pet_table] populate_pet_table = PostgresOperator( task_id="populate_pet_table", sql=""" INSERT INTO pet (name, pet_type, birth_date, OWNER) VALUES ( 'Max', 'Dog', '2018-07-05', 'Jane'); INSERT INTO pet (name, pet_type, birth_date, OWNER) VALUES ( 'Susie', 'Cat', '2019-05-01', 'Phil'); INSERT INTO pet (name, pet_type, birth_date, OWNER)
def dbt_dags_factory_legacy( dwh_engine, dwh_conn_id, project_name, dbt_schema_name, airflow_conn_id, dag_base_name="DBT_run", analytics_reader=None, # list of users of DWH who are read-only schedule_interval=timedelta(hours=1), start_date=datetime(2019, 1, 1), default_args=None, folder=None, models=None, exclude=None, ): if analytics_reader: for statement in ( "insert", "update", "delete", "drop", "create", "select", ";", "grant", ): for reader in analytics_reader: if statement in reader.lower(): raise Exception("Error! The analytics reader {0} " + "is invalid.".format(reader)) # analytics_reader = analytics_reader.split(',') analytics_reader_sql = f'\nGRANT USAGE ON SCHEMA "{dbt_schema_name}"' analytics_reader_sql += ' TO "{0}";' analytics_reader_sql += (f''' \nGRANT SELECT ON ALL TABLES IN SCHEMA "{dbt_schema_name}"''' + ' TO "{0}";') analytics_reader_sql = "".join( [analytics_reader_sql.format(i) for i in analytics_reader]) if models and not (type(models) == str): models = " --models " + " ".join(models) else: models = "" if exclude and not (type(exclude) == str): exclude = " --exclude " + " ".join(exclude) else: exclude = "" flags = models + exclude dag = DAG( dag_base_name, catchup=False, max_active_runs=1, schedule_interval=schedule_interval, start_date=start_date, default_args=default_args, ) dag_full_refresh = DAG( dag_base_name + "_full_refresh", catchup=False, max_active_runs=1, schedule_interval=None, start_date=start_date, default_args=default_args, ) folder = folder or (os.environ.get("AIRFLOW_HOME") or conf.get("core", "airflow_home")).replace( "airflow_home/airflow", "dbt_home", ) bash_command = """ cd {1} source env/bin/activate cd {2} dbt {0} """.format( "{0}", folder, project_name, ) sensor_sql = """ SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END -- only run if exatly equal to 0 FROM public.dag_run WHERE dag_id IN ('{0}', '{1}') and state = 'running' and not (run_id = '{2}') """.format( dag._dag_id, dag_full_refresh._dag_id, "{{ run_id }}", ) # refactor?! not coupled to values in profiles.yml! if dwh_engine == EC.DWH_ENGINE_POSTGRES: conn = BaseHook.get_connection(dwh_conn_id) env = { "DBT_DWH_HOST": str(conn.host), "DBT_DWH_USER": str(conn.login), "DBT_DWH_PASS": str(conn.password), "DBT_DWH_PORT": str(conn.port), "DBT_DWH_DBNAME": str(conn.schema), "DBT_DWH_SCHEMA": dbt_schema_name, "DBT_PROFILES_DIR": folder, } elif dwh_engine == EC.DWH_ENGINE_SNOWFLAKE: analytics_conn = BaseHook.get_connection(dwh_conn_id) analytics_conn_extra = analytics_conn.extra_dejson env = { "DBT_ACCOUNT": analytics_conn_extra.get( "account", analytics_conn.host, ), "DBT_USER": analytics_conn.login, "DBT_PASS": analytics_conn.password, "DBT_ROLE": analytics_conn_extra.get("role"), "DBT_DB": analytics_conn_extra.get("database"), "DBT_WH": analytics_conn_extra.get("warehouse"), "DBT_SCHEMA": dbt_schema_name, "DBT_PROFILES_DIR": folder, } else: raise ValueError("DWH type not implemented!") # with dag: snsr = EWAHSqlSensor( task_id="sense_dbt_conflict_avoided", conn_id=airflow_conn_id, sql=sensor_sql, poke_interval=5 * 60, mode="reschedule", # don't block a worker and pool slot dag=dag, ) dbt_seed = BashOperator( task_id="run_dbt_seed", bash_command=bash_command.format("seed"), env=env, dag=dag, ) dbt_run = BashOperator( task_id="run_dbt", bash_command=bash_command.format("run" + flags), env=env, dag=dag, ) dbt_test = BashOperator( task_id="test_dbt", bash_command=bash_command.format("test" + flags), env=env, dag=dag, ) dbt_docs = BashOperator( task_id="create_dbt_docs", bash_command=bash_command.format("docs generate"), env=env, dag=dag, ) snsr >> dbt_seed >> dbt_run >> dbt_test if analytics_reader: # This should not occur when using Snowflake read_rights = PostgresOperator( task_id="grant_access_to_read_users", sql=analytics_reader_sql, postgres_conn_id=dwh_conn_id, dag=dag, ) dbt_test >> read_rights >> dbt_docs else: dbt_test >> dbt_docs # with dag_full_refresh: snsr = EWAHSqlSensor( task_id="sense_dbt_conflict_avoided", conn_id=airflow_conn_id, sql=sensor_sql, poke_interval=5 * 60, mode="reschedule", # don't block a worker and pool slot dag=dag_full_refresh, ) dbt_seed = BashOperator( task_id="run_dbt_seed", bash_command=bash_command.format("seed"), env=env, dag=dag_full_refresh, ) dbt_run = BashOperator( task_id="run_dbt", bash_command=bash_command.format("run --full-refresh" + flags), env=env, dag=dag_full_refresh, ) dbt_test = BashOperator( task_id="test_dbt", bash_command=bash_command.format("test" + flags), env=env, dag=dag_full_refresh, ) dbt_docs = BashOperator( task_id="create_dbt_docs", bash_command=bash_command.format("docs generate"), env=env, dag=dag_full_refresh, ) snsr >> dbt_seed >> dbt_run >> dbt_test if analytics_reader: read_rights = PostgresOperator( task_id="grant_access_to_read_users", sql=analytics_reader_sql, postgres_conn_id=dwh_conn_id, dag=dag_full_refresh, ) dbt_test >> read_rights >> dbt_docs else: dbt_test >> dbt_docs return (dag, dag_full_refresh)
def daily_weather_subdag(parent_dag_id, child_dag_id, args): with DAG( dag_id=f'{parent_dag_id}.{child_dag_id}', default_args=args, #schedule_interval= '@once', #'@monthly', # for testing # @daily', #start_date= datetime(2021,5,2), #days_ago(2), #datetime.datetime.now(), #days_ago(2), # end_date= datetime(2021,5,2), #days_ago(2), #datetime.datetime.now(), #days_ago(2), # max_active_runs = 1, #tags=['covid'], ) as dag: #download_diff_weather_task = download_diff_weather(task_id = "download_diff_weather") download_diff_weather_task = dummy_download(task_id = "download_diff_weather") # jinja + xcom + task parameters + sql string is uber relou stage_recent_insert = PostgresOperator( task_id = "stage_recent_insert", postgres_conn_id = "postgres_default", sql= sq.q_create_stage__weather_table, params = { "stage_table" : "recent_insert", "stage_file" : "insert.csv" } ) stage_recent_delete = PostgresOperator( task_id = "stage_recent_delete", postgres_conn_id = "postgres_default", sql = sq.q_create_stage__weather_table, params = { "stage_table" : "recent_delete", "stage_file" : "delete.csv" } ) stage_recent_update = PostgresOperator( task_id = "stage_recent_update", postgres_conn_id = "postgres_default", sql = sq.q_create_stage__weather_table, params = { "stage_table" : "recent_update", "stage_file" : "update.csv" } ) filter_insert = PostgresOperator( task_id = "filter_insert", postgres_conn_id = "postgres_default", sql = sq.q_filter_weather, params= {"full_table" : "recent_insert", "filtered_table" : "recent_insert_filtered", "selected_stations" : "weatherelem_station"} ) filter_delete = PostgresOperator( task_id = "filter_delete", postgres_conn_id = "postgres_default", sql = sq.q_filter_weather, params= {"full_table" : "recent_delete", "filtered_table" : "recent_delete_filtered", "selected_stations" : "weatherelem_station"} ) filter_update = PostgresOperator( task_id = "filter_update", postgres_conn_id = "postgres_default", sql = sq.q_filter_weather, params= {"full_table" : "recent_update", "filtered_table" : "recent_update_filtered", "selected_stations" : "weatherelem_station"} ) process_delete = PostgresOperator( task_id = "process_delete", postgres_conn_id = "postgres_default", sql = sq.q_process_delete_weather ) process_insert = PostgresOperator( task_id = "process_insert", postgres_conn_id = "postgres_default", sql = sq.q_process_insert_weather ) process_update = PostgresOperator( task_id = "process_update", postgres_conn_id = "postgres_default", sql = sq.q_process_update_weather ) #filter_quality_check = PostgresOperator( #task_id = "filter_quality_check", #postgres_conn_id = "postgres_default", #sql = """ #""" #) download_diff_weather_task >> [stage_recent_insert, stage_recent_update, stage_recent_delete] stage_recent_insert >> filter_insert stage_recent_delete >> filter_delete >> process_delete stage_recent_update >> filter_update >> process_update stage_recent_insert >> filter_insert >> process_insert process_delete >> process_update >> process_insert return dag
def _remove_sample_data_from_s3(): s3_hook = S3Hook() if s3_hook.check_for_key(f'{S3_KEY}/{REDSHIFT_TABLE}', S3_BUCKET): s3_hook.delete_objects(S3_BUCKET, f'{S3_KEY}/{REDSHIFT_TABLE}') with DAG(dag_id="example_s3_to_redshift", start_date=days_ago(1), schedule_interval=None, tags=['example']) as dag: setup__task_add_sample_data_to_s3 = PythonOperator( python_callable=_add_sample_data_to_s3, task_id='setup__add_sample_data_to_s3') setup__task_create_table = PostgresOperator( sql= f'CREATE TABLE IF NOT EXISTS {REDSHIFT_TABLE}(Id int, Name varchar)', postgres_conn_id='redshift_default', task_id='setup__create_table', ) # [START howto_operator_s3_to_redshift_task_1] task_transfer_s3_to_redshift = S3ToRedshiftOperator( s3_bucket=S3_BUCKET, s3_key=S3_KEY, schema="PUBLIC", table=REDSHIFT_TABLE, copy_options=['csv'], task_id='transfer_s3_to_redshift', ) # [END howto_operator_s3_to_redshift_task_1] teardown__task_drop_table = PostgresOperator( sql=f'DROP TABLE IF EXISTS {REDSHIFT_TABLE}', postgres_conn_id='redshift_default',
from datetime import datetime from airflow import DAG from airflow.operators.python import PythonOperator from airflow.providers.postgres.operators.postgres import PostgresOperator from etl_spotify import get_tracks, save_tracks_to_db with DAG(dag_id='etl_spotify', start_date=datetime(2021, 7, 1), schedule_interval='@daily') as dag: create_tracks_table = PostgresOperator( task_id='create_table', sql='create_table.sql', postgres_conn_id='postgres_default', ) getting_spotify_data = PythonOperator( task_id='getting_data', python_callable=get_tracks, provide_context=True, ) insert_data_to_db = PythonOperator(task_id='insert_data_to_db', python_callable=save_tracks_to_db, provide_context=True) create_tracks_table >> getting_spotify_data >> insert_data_to_db
default_args = { 'owner': 'airflow', 'depends_on_past': False, 'email_on_failure': False, 'email_on_retry': False, 'retries': 1, 'retry_delay': timedelta(minutes=5) } with DAG('metrics_dag', start_date=datetime(2021, 2, 15), schedule_interval='@once', default_args=default_args, template_searchpath='/usr/local/airflow/include', catchup=False ) as dag: run_first_task = PostgresOperator( task_id='task_2_1', postgres_conn_id='postgres', sql='task_2_1.sql', params={'k': '50', 'p_date': '2019-01'} ) run_second_task = PostgresOperator( task_id='task_2_2', postgres_conn_id='postgres', sql='task_2_2.sql' ) run_first_task >> run_second_task
os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', 'create_tables.sql')) as f: create_tables_sql = f.read() dag = DAG( 'udacity_sparkify_dag', default_args=default_args, description='Load and transform data in Redshift with Airflow', schedule_interval='@hourly', catchup=False, ) start_operator = DummyOperator(task_id='Begin_execution', dag=dag) create_tables = PostgresOperator(task_id="create_tables", dag=dag, postgres_conn_id="redshift", sql=create_tables_sql) stage_events_to_redshift = StageToRedshiftOperator( task_id='Stage_events', dag=dag, table="staging_events", redshift_conn_id="redshift", aws_credentials_id="aws_credentials", s3_bucket="udacity-dend", s3_key="log_data", json_format="'s3://udacity-dend/log_json_path.json'", ) stage_songs_to_redshift = StageToRedshiftOperator( task_id='Stage_songs',
job_flow_id=job_flow_id, step_id="{{ task_instance.xcom_pull(task_ids='" + 'add_step_{}'.format(key) + "', key='return_value')[0] }}", aws_conn_id='aws_default' ) entity_task_list.append(watch_step) # if file has sql quality steps, then create additional tasks in DAG if file['sql_step_args']: # must drop duplicates in table as spark jdbc does not support upsert # https://issues.apache.org/jira/browse/SPARK-19335 drop_table_duplicates = PostgresOperator( task_id='drop_table_duplicates_{table}'.format(table=file['sql_step_args']['db_table']), postgres_conn_id='postgres_default', sql=""" DELETE FROM {table} t1 USING {table} t2 WHERE t1.ctid < t2.ctid AND t1.{duplicate_key} = t2.{duplicate_key}; """.format(table=file['sql_step_args']['db_table'], duplicate_key=file['sql_step_args']['duplicate_key']), ) entity_task_list.append(drop_table_duplicates) check_table_has_no_duplicates = SQLValueCheckOperator( task_id='check_table_has_no_duplicates_{table}'.format(table=file['sql_step_args']['db_table']), conn_id='postgres_default', sql=""" SELECT COALESCE( ( SELECT COUNT({duplicate_key})
"email_on_retry": False, "retries": 1, "retry_delay": timedelta(minutes=1), }, catchup=False, template_searchpath="/usr/local/airflow/include", ) as dag: t0 = DummyOperator(task_id="start") # Define Task Group with Postgres Queries with TaskGroup("covid_table_queries") as covid_table_queries: for state in states: generate_files = PostgresOperator( task_id="covid_query_{0}".format(state), postgres_conn_id="gpdb", sql="covid_state_query.sql", params={"state": "'" + state + "'"}, ) # Define task to send email send_email = EmailOperator( task_id="send_email", to=email_to, subject="Covid Greenplum Queries DAG", html_content= "<p>The Covid queries were run on Greenplum successfully. <p>", ) # Define task dependencies t0 >> covid_table_queries >> send_email
default_args=default_args, catchup=False) as dag: get_slots = PythonOperator(task_id="get_slots", provide_context=True, python_callable=get_slots) choose_next_task = BranchPythonOperator(task_id='choose_next_task', python_callable=_choose_next_task, do_xcom_push=False) skipped_task = DummyOperator(task_id='skipped_task') email_task = PythonOperator( task_id='email_task', python_callable=notify_email, provide_context=True, dag=dag, ) delete_xcom_task = PostgresOperator( task_id='delete-xcom-task', postgres_conn_id='postgres_1', sql="delete from xcom where dag_id='" + dag.dag_id + "' and date(execution_date)=date('{{ ds }}')", trigger_rule='one_success', dag=dag) get_slots >> choose_next_task >> [skipped_task, email_task ] >> delete_xcom_task
import airflow.utils.dates from airflow import DAG from airflow.sensors.external_task_sensor import ExternalTaskSensor from airflow.providers.postgres.operators.postgres import PostgresOperator from datetime import datetime, timedelta default_args = {"owner": "airflow", "start_date": datetime(2020, 1, 1)} with DAG(dag_id="cleaning_dag", default_args=default_args, schedule_interval="*/10 * * * *", catchup=False) as dag: waiting_for_task = ExternalTaskSensor(task_id='waiting_for_task', external_dag_id='avocado_dag', external_task_id='publish_notebook', failed_states=['failed']) cleaning_xcoms = PostgresOperator(task_id='cleaning_xcoms', sql='sql/CLEANING_XCOMS.sql', postgres_conn_id='postgres') waiting_for_task >> cleaning_xcoms
from airflow import DAG from airflow.providers.postgres.operators.postgres import PostgresOperator from airflow.utils.dates import days_ago default_args = { 'owner': 'Anil', 'dag_id': 'PostgreOperatorTest_Dag', 'start_date': days_ago(1), 'schedule_interval': '@daily' } dag: DAG = DAG(dag_id='PostgreOperatorTest_Dag', default_args=default_args, template_searchpath='/opt/airflow/') task1 = PostgresOperator(task_id="create_table", postgres_conn_id='postgres_new', sql="sql/create_table.sql", params={'table_name': 'covid'}, database="userdata", dag=dag)
op_kwargs={ 'dest_table': 'stg_purchases', 'sql': 'select * from purchases where "purchase_date" = %s', 'params': [execution_date], }) load_full_products_data = PythonOperator( task_id='load_full_products', python_callable=transfer_oltp_olap, op_kwargs={ 'dest_table': 'stg_products', 'sql': 'select * from products', }) delete_products_sales_exec_date = PostgresOperator( task_id='delete_products_sales_exec_date', postgres_conn_id='olap', sql='delete_products_sales_exec_date.sql') join_purchases_with_products = PostgresOperator( task_id='join_purchases_products', postgres_conn_id='olap', sql='join_purchases_with_products.sql') union_incremental_products_sales = PostgresOperator( task_id='union_staging_to_products_sales', postgres_conn_id='olap', sql='union_staging_to_products_sales.sql') agg_sales_category = PostgresOperator(task_id='rebuild_agg_sales_category', postgres_conn_id='olap', sql='agg_sales_category.sql')
def create_incremental_refresh_dag( # fmt: off dag_id, schedule, symbol, granularity, symbol_id, default_args # fmt: on ): """ Function returns a dag instance which loads data from Binance's S3 bucket to load incremental data. S3 Bucket containing monthly/daily data can be used to load data containing different intervals. Args: dag_id (str): dag ID. schedule (str): schedule at which DAG should run. symbol (str): Stock/Crypto symbol which will be used to pull data. symbol_id (int): Symbol Id of the symbol which was shared. granularity (str): Time period for which candles are created. Ex: 1m, 5m etc. Returns: instance of Airflow DAG. """ def load_data(symbol, symbol_id, start_ts, end_ts, granularity): """ Used to pull incremental data specified by a time range in epoc timestamp - (start_ts, end_ts) Args - symbol (str): coinpair which Binance API recognises. - start_ts (int): time range beginning, in epoch timestamp - end_ts (int): time range end, in epoch timestamp - granularity (str): Time period of granularity. ex: 1m, 5m, 15m. See, Binance's API documentation for further options. Returns - Returns JSON object, containing Pandas Dataframe. """ api = BinanceAPI() def get_ts_shift(ts, i): shifted_date = ts + 86400 * i return round(shifted_date.timestamp()) # calculate total 24h batches required to backfill data time_diff_secs = (end_ts - start_ts).total_seconds() batches = round(time_diff_secs / 60 / 60 / 24) log_message = "Getting data: {:d} {:d} to {:d}" logger.info(log_message.format(symbol, start_ts, end_ts)) data = [ api.get_historical( symbol, start_ts=get_ts_shift(start_ts, i), end_ts=get_ts_shift(start_ts, i + 1) + 1, granularity=granularity, ) for i in range(batches) ] df = pd.concat(data) df.loc[:, "symbol_id"] = symbol_id pg_hook.write_pandas_df( df.to_json(), name="tickerdata_{:s}".format(granularity), if_exists="append", index=False, ) def delete_data(symbol, symbol_id, start_ts, end_ts, granularity): """ Deletes rows from table specified by a time range - (start_ts, end_ts) before writing to the table. Args - symbol (str): coinpair which Binance API recognises. - start_ts (int): time range beginning, in epoch timestamp - end_ts (int): time range end, in epoch timestamp - granularity (str): Time period of granularity. ex: 1m, 5m, 15m. See, Binance's API documentation for further options. Returns - Returns JSON object, containing Pandas Dataframe. """ table = "ticker_data_{:s}".format(granularity) CMD = """ DELETE FROM {table} WHERE close_time BETWEEN {start_ts} AND {end_ts} AND symbol_id = {symbol_id} """.format(table=table, start_ts=start_ts, end_ts=end_ts, symbol_id=symbol_id) PostgresOperator( task_id="delete_data_{:s}_{:s}".format(symbol, table), postgres_conn_id=POSTGRES_CONN_ID, sql=CMD, ) return True dag = DAG(dag_id, schedule_interval=schedule, default_args=default_args) with dag: # fmt: off t1 = PythonOperator(task_id="delete_data_{:s}".format(symbol), python_callable=delete_data) t2 = PythonOperator(task_id="load_data_{:s}".format(symbol), python_callable=load_data) # fmt: on SQL = """UPDATE symbols SET symbol_last_updated_at=timezone('utc', NOW()) WHERE symbol_id = {:d}""".format(symbol_id) t3 = PostgresOperator( task_id="Set_{:s}_last_updated_at".format(symbol), postgres_conn_id=POSTGRES_CONN_ID, sql=SQL, ) t1 >> t2 >> t3 return dag