from airflow.operators.python_operator import PythonOperator from datacleaner import data_cleaner from airflow.operators.mysql_operator import MySqlOperator from airflow.operators.email_operator import EmailOperator default_args = { 'owner': 'Airflow', 'depends_on_past': False, 'start_date': datetime(2020, 2, 17), 'retries': 1, 'retry_delay': timedelta(seconds=5) } with DAG('store_dag12', default_args=default_args, schedule_interval='@daily', template_searchpath=['/usr/local/airflow/sql_files'], catchup=True) as dag: t1 = BashOperator( task_id='check_file_exists', bash_command='shasum ~/store_files_airflow/raw_store_transactions.csv', retries=2, retry_delay=timedelta(seconds=15)) t2 = PythonOperator(task_id='clean_raw_csv', python_callable=data_cleaner) t3 = MySqlOperator(task_id='create_mysql_table', mysql_conn_id="mysql_conn", sql="create_table.sql") t4 = MySqlOperator(task_id='insert_into_table', mysql_conn_id="mysql_conn", sql="insert_into_table.sql") t1 >> t2 >> t3 >> t4
default_args=default_args, schedule_interval='@daily', catchup=True) as dag: check_file = BashOperator(task_id="check_file", bash_command="shasum ~/ip_files/or.csv", retries=2, retry_delay=timedelta(seconds=15)) pre_process = PythonOperator(task_id="pre", python_callable=pre_process) groupbys = PythonOperator(task_id="aggre", python_callable=process_data) create_table = MySqlOperator( task_id='create_table', mysql_conn_id="mysql_db1", sql= "CREATE table IF NOT EXISTS aggre_res (stock_code varchar(100) NULL,descb varchar(100) NULL,country varchar(100) NULL,total_price varchar(100) NULL)" ) insert = MySqlOperator( task_id='insert_db', mysql_conn_id="mysql_db1", sql= "LOAD DATA INFILE '/var/lib/mysql-files/fin.csv' INTO TABLE aggre_res FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;" ) email = EmailOperator( task_id='send_email', to='*****@*****.**', subject='Daily report generated', html_content=
l_load = [ 'dummy_table', 'dummy_table2', 'dummy_table3', 'dummy_table4', ] dag = DAG("dag_etl_sp", schedule_interval="@once", default_args=default_args) conn_id_extract = "conn_test_source" conn_id_transform = "conn_test_transform" conn_id_load = "conn_test_load" o_transform_sp = MySqlOperator(task_id='transform_sp', sql='call test_transform.dummy_airflow_sp()', mysql_conn_id=conn_id_transform, dag=dag) o_truncate_transform = MySqlOperator( task_id='truncate_transform', sql='call test_transform.truncate_dummy_data()', mysql_conn_id=conn_id_transform, dag=dag) o_truncate_load = MySqlOperator( task_id='truncate_load', sql='call test_destination.truncate_dummy_data()', mysql_conn_id=conn_id_load, dag=dag) o_completed = DummyOperator(task_id="Completed", dag=dag)
hive_insert_overwrite_final_address_data = HiveOperator( task_id='hive_insert_overwrite_final_address_data', hql=insertoverwrite_final_address_data, hive_cli_conn_id='beeline', dag=dag) create_hive_table_final_address_data >> hive_insert_overwrite_final_address_data # -------------------------------------------------------------------------------- # create table remote # -------------------------------------------------------------------------------- create_table_remote = MySqlOperator( task_id='create_table_remote', sql="CREATE TABLE IF NOT EXISTS final_address_data(number varchar(255),street varchar(255),city varchar(255),postcode varchar(255),hash varchar(255),country varchar(255));", mysql_conn_id='mysql_default', database='db' ) hive_insert_overwrite_final_address_data >> create_table_remote # -------------------------------------------------------------------------------- # clear table remote # -------------------------------------------------------------------------------- delete_from_remote = MySqlOperator( task_id='delete_from_remote', sql="DELETE FROM final_address_data;", mysql_conn_id='mysql_default', database='db' )
dir_path = '/home/karan/Attempt_ApacheAirflow/' file_name = 't3.log' full_path = dir_path + file_name f = open(full_path, "a+") f.write("T3 Time start: {}\n".format(str(datetime.now()))) f.close() # Get sub dag sub_dag, t2_complete, t3_complete = load_subdag('sample_sub_dag', 'file_operation', default_args) sub_dag_tasks = SubDagOperator(task_id='file_operation', subdag=sub_dag, dag=dag) task_status_sql = """ insert into stage_status values (3, '{}', 'python_create_file', 'success'), (2, '{}', 'create_file', 'success'); """.format(str(t3_complete), str(t2_complete)) # Mysql is installed locally # Connection can be created using Airflow UI t4 = MySqlOperator(task_id='Update_status_table', sql=task_status_sql, mysql_conn_id='local_mysql', owner='airflow', dag=dag) # Dependancy t1 >> sub_dag_tasks >> t4
catchup=False) as dag: # task1: check if the source file exists in the input directory # note that the file in airflow container. t1 = BashOperator( task_id="check_file_exists", bash_command="shasum ~/store_files_airflow/raw_store_transactions.csv", retries=1, retry_delay=timedelta(seconds=15)) # task 2: clean data (remove special characters) t2 = PythonOperator(task_id="clean_raw_csv", python_callable=data_cleaner) # task 3: create table t3 = MySqlOperator(task_id="create_mysql_table", mysql_conn_id="mysql_conn", sql="create_table.sql") # task 4: insert cleaned data into table t4 = MySqlOperator(task_id="insert_into_table", mysql_conn_id="mysql_conn", sql="insert_into_table.sql", dag=dag) # task 5: calculate store-wise and location-wise profit (yesterday) and save results as csv t5 = MySqlOperator(task_id="select_from_table", mysql_conn_id="mysql_conn", sql="select_from_table.sql") yesterday_date = datetime.strftime(datetime.now() - timedelta(1), "%Y-%m-%d")
'email': ['*****@*****.**'], 'email_on_failure': False, 'email_on_retry': False, 'retries': 1, 'retry_delay': timedelta(minutes=5), 'catchup': False } dag = DAG('mysql2mysql', default_args=default_args, schedule_interval='@daily') qry_truncate_staging = """ truncate st_adventure.st_contact """ t1 = MySqlOperator(sql=qry_truncate_staging, mysql_conn_id='mysql_adventure', task_id='truncating_staging', dag=dag) qry_populate_staging = """ insert into st_adventure.st_contact (select ContactID , FirstName , MiddleName , LastName , EmailAddress , Phone from adventureworks.contact c) """ t2 = MySqlOperator(sql=qry_populate_staging,
'email_on_failure': False, 'email_on_retry': False, } dag = DAG('airflow_logs', default_args=default_args, schedule_interval='0/1 * * * *') task1 = BashOperator(task_id='print_date', bash_command='date', dag=dag) task2 = BashOperator(task_id='print_hello-world', bash_command='echo "hello world!!"', dag=dag) task3 = MySqlOperator(mysql_conn_id='airflow_db', task_id='basic_mysql', sql="SELECT * FROM `dag`", dag=dag) EMAIL_CONTENT = """ <ul> <li>Instatnce key: %s</li> <li>Owner: %s</li> <li>Host: %s</li> </ul> """ % ( "{{ task_instance_key_str }}", "{{ task.owner}}", "{{ ti.hostname }}", )
] ) class CustomMySqlOperator(MySqlOperator): def execute(self, context): self.log.info('Executing: %s', self.sql) hook = MySqlHook(mysql_conn_id=self.mysql_conn_id, schema=self.database) return hook.get_records(self.sql, parameters=self.parameters) update_flat_obs = MySqlOperator( task_id='update_flat_obs', sql='flat_obs_v1.3.sql', mysql_conn_id=MYSQL_CONN_ID, database='etl', dag=dag ) update_flat_orders = MySqlOperator( task_id='update_flat_orders', sql='flat_orders_v1.1.sql', mysql_conn_id=MYSQL_CONN_ID, database='etl', dag=dag ) update_flat_lab_obs = MySqlOperator(
import airflow from airflow.models import DAG from airflow.operators.mysql_operator import MySqlOperator from airflow.operators.bash_operator import BashOperator ### CONSTANTS: DO NOT EDIT ### ## MYSQL CONNECTION MYSQL_CONN_ID = 'amrs_slave_conn' ## DAG ID DAG_ID = 'hiv_monthly_summary_daily_10pm' ### END TRIGGER RULES ### nbo_timezone = timezone("Africa/Nairobi") #start_date = nbo_timezone.localize(datetime.strptime('2019-06-25 20:00', '%Y-%m-%d %H:%M')) start_date = datetime.strptime('2019-06-25', '%Y-%m-%d') # Dag is returned by a factory method dag = DAG(dag_id=DAG_ID, schedule_interval='30 22 * * *', start_date=start_date, catchup=False) update_hiv_monthly_report_dataset = MySqlOperator( task_id='update_hiv_monthly_report_dataset', sql= 'call generate_hiv_monthly_report_dataset_v1_4("sync",1,100000,100,"2013-01-01");', mysql_conn_id=MYSQL_CONN_ID, database='etl', dag=dag)
'owner': 'sergio', 'start_date': datetime(2021, 3, 1, tzinfo=local_tz) } with DAG('11-load-dimensions.py', default_args=default_args, schedule_interval=None, template_searchpath=cfg, catchup=False, is_paused_upon_creation=False) as dag: start = DummyOperator(task_id='start') truncate_tables = MySqlOperator(task_id='truncate_tables', mysql_conn_id='cool_car', sql='00-truncate_tables.sql', autocommit=True, dag=dag) load_branch = MySqlOperator(task_id='load_branch_office', mysql_conn_id='cool_car', sql='01-dim_branch_office.sql', autocommit=True, dag=dag) load_car = MySqlOperator(task_id='load_car', mysql_conn_id='cool_car', sql='02-dim_car.sql', autocommit=True, dag=dag)