def test_get_hook(self, conn, hook_class, get_connection): """ Operator should use odbc hook if conn type is ``odbc`` and pymssql-based hook otherwise. """ get_connection.return_value = conn op = MsSqlOperator(task_id='test', sql='', mssql_conn_id=conn.conn_id) hook = op.get_hook() assert hook.__class__ == hook_class
def test_get_hook(self, get_connection): """ Operator should use odbc hook if conn type is ``odbc`` and pymssql-based hook otherwise. """ for conn, hook_class in [(ODBC_CONN, OdbcHook), (PYMSSQL_CONN, MsSqlHook)]: get_connection.return_value = conn op = MsSqlOperator(task_id='test', sql='', mssql_conn_id=conn.conn_id) hook = op.get_hook() assert hook.__class__ == hook_class
def test_get_hook_default(self, get_connection): """ If :meth:`airflow.models.Connection.get_hook` does not return a hook (e.g. because of an invalid conn type), then :class:`~.MsSqlHook` should be used. """ get_connection.return_value.get_hook.side_effect = Mock( side_effect=AirflowException()) op = MsSqlOperator(task_id='test', sql='') assert op.get_hook().__class__.__name__ == 'MsSqlHook'
def test_get_hook_from_conn(self, get_connection): """ :class:`~.MsSqlOperator` should use the hook returned by :meth:`airflow.models.Connection.get_hook` if one is returned. This behavior is necessary in order to support usage of :class:`~.OdbcHook` with this operator. Specifically we verify here that :meth:`~.MsSqlOperator.get_hook` returns the hook returned from a call of ``get_hook`` on the object returned from :meth:`~.BaseHook.get_connection`. """ mock_hook = MagicMock() get_connection.return_value.get_hook.return_value = mock_hook op = MsSqlOperator(task_id='test', sql='') assert op.get_hook() == mock_hook
dag = DAG('run_call_light_etl', default_args=default_args, catchup=False, schedule_interval='40 5 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' # deps = ExternalTaskSensor( # external_dag_id='run_daily_census', # external_task_id='refresh_daily_census', # task_id='wait_for_daily_census', # dag=dag # ) CLT = MsSqlOperator( sql='EXEC EBI_CallLight_tmp_Logic;', task_id='call_light_tmp', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag ) CLC = MsSqlOperator( sql='EXEC EBI_CallLightUntC_Logic;', task_id='call_light_unt_c', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag ) CLCT = MsSqlOperator( sql='EXEC EBI_CallLightUntC_tmp_Logic;',
1, 'retry_delay': timedelta(minutes=2) } dag = DAG('refresh_scm', default_args=default_args, catchup=False, schedule_interval='0 16 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' si = MsSqlOperator(sql='EXEC EBI_SCM_Items_Logic;', task_id='load_scm_items', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) sv = MsSqlOperator(sql='EXEC EBI_SCM_Vendors_Logic;', task_id='load_scm_vendors', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) vch = MsSqlOperator(sql='EXEC EBI_SCM_Vouchers_Logic;', task_id='load_scm_vouchers', autocommit=True, mssql_conn_id=conn_id, pool=pool_id,
return [pd.read_json('/var/nfsshare/etl_deps/dev_{}.json'.format(name)) for name in names] conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' to_read = ['unique_procs', 'no_dep_procs', 'proc_map', 'unique_dep_procs', 'unique_ds', 'ds_map', 'unique_ds_procs'] unique_procs, no_dep_procs, proc_map, unique_dep_procs, unique_ds, ds_map, unique_ds_procs = read_json_files(to_read) # create a sql operator for each procedure sql_ops = {} for p in unique_procs.procs: o = MsSqlOperator( sql='exec {};'.format(p), task_id='exec_{}'.format(p), autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag ) sql_ops[p] = o # create a python operator for each tableau datasource python_ops = {} for ds in unique_ds.ds_name: ds_id = unique_ds.loc[unique_ds.ds_name == ds, 'id'].values[0] if ds == 'pb_tdl_transactions': weight = 0 elif 'appointment' in ds: weight = 2 else: weight = 1
schedule_interval='@daily', start_date=datetime(2021, 10, 1), tags=['example'], catchup=False, ) # [START howto_operator_mssql] # Example of creating a task to create a table in MsSql create_table_mssql_task = MsSqlOperator( task_id='create_country_table', mssql_conn_id='airflow_mssql', sql=r""" CREATE TABLE Country ( country_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name TEXT, continent TEXT ); """, dag=dag, ) # [END howto_operator_mssql] # [START mssql_hook_howto_guide_insert_mssql_hook] @dag.task(task_id="insert_mssql_task") def insert_mssql_hook(): mssql_hook = MsSqlHook(mssql_conn_id='airflow_mssql', schema='airflow')
conn_id = 'qrrm_datamart' ebi = get_json_secret('ebi_db_conn')['db_connections']['fi_dm_ebi'] auth = TSC.TableauAuth(ebi['user'].split(sep='\\')[1], ebi['password']) server = TSC.Server('https://ebi.coh.org', use_server_version=True) def refresh_workbook_data(tableau_server, tableau_authentication, workbook_luid): with server.auth.sign_in(tableau_authentication): server.workbooks.refresh(workbook_luid) cp = MsSqlOperator(sql='EXEC Clarity_COVID19_SP;', task_id='covid_proc', autocommit=True, mssql_conn_id=conn_id, dag=dag) ce = PythonOperator(task_id='covid_extract', python_callable=refresh_workbook_data, op_kwargs={ 'tableau_server': server, 'tableau_authentication': auth, 'workbook_luid': '2fd3f851-37b9-45d1-bedc-0d7cdacdf888' }, dag=dag) cp >> ce
'retry_delay': timedelta(minutes=2) } dag = DAG('refresh_kpis', default_args=default_args, catchup=False, concurrency=2, schedule_interval='0 19 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' awt = MsSqlOperator(sql='EXEC sp_EBI_KPI_AGG_Adjusted_Wait_Time;', task_id='adjusted_wait_time', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) ca = MsSqlOperator(sql='EXEC sp_EBI_KPI_AGG_Call_Abandonment;', task_id='calls_abandoned', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) cfd = MsSqlOperator(sql='EXEC sp_EBI_KPI_AGG_Cancel_14;', task_id='cancel_14_days', autocommit=True, mssql_conn_id=conn_id, pool=pool_id,
#print(list(df)) engineorigem = sqlalchemy.create_engine('mssql+pyodbc://sa:[email protected]/bi_integracao?driver=ODBC+Driver+17+for+SQL+Server') #df.to_sql(name=users, con=engineorigem, if_exists='replace') with DAG('user_processing', schedule_interval='@daily', default_args=default_args, catchup=False) as dag: # define tasks / operators creating_table = MsSqlOperator( task_id='creating_table', mssql_conn_id ='sql_server', database = 'bi_integracao', sql = ''' CREATE TABLE users ( firstname varchar(100) NOT NULL, lastname varchar(100) NOT NULL, country varchar(100) NOT NULL, username varchar(100) NOT NULL, password varchar(30) NOT NULL, email varchar(100) NOT NULL PRIMARY KEY ) ''' ) is_api_available = HttpSensor( task_id = 'is_api_available', http_conn_id='user_api', endpoint='api/' ) extract_user = SimpleHttpOperator (
1, 'retry_delay': timedelta(minutes=2) } dag = DAG('refresh_total_charges', default_args=default_args, catchup=False, schedule_interval='0 17 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' tc = MsSqlOperator(sql='EXEC EBI_Total_Charges_Clarity_Logic;', task_id='load_total_charges_clarity', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) new = PythonOperator( task_id='refresh_new_em_provider', python_callable=refresh_tableau_extract, op_kwargs={'datasource_id': 'ea9a44b2-3f27-4560-9a00-4e056ede95bd'}, dag=dag) trj = PythonOperator( task_id='refresh_patient_trajectory', python_callable=refresh_tableau_extract, op_kwargs={'datasource_id': '951a76e5-e7a7-466a-a0aa-6b592bd9a370'}, dag=dag)
dag = DAG('refresh_patient_satisfaction', default_args=default_args, catchup=False, schedule_interval='0 18 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' results_bash = 'cd C:\\Anaconda\\ETL\\patient_sat && python patient_sat_results.py' ranks_bash = 'cd C:\\Anaconda\\ETL\\patient_sat && python patient_sat_percentile.py' results_bash_new = 'cd C:\\Anaconda\\ETL\\patient_sat && python ResponseToDB1.py' ranks_bash_new = 'cd C:\\Anaconda\\ETL\\patient_sat && python RanksToDB1.py' red = MsSqlOperator(sql='EXEC EBI_PressGaney_Results_Clarity_Logic;', task_id='refresh_edw_data', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) crd = SSHOperator(ssh_conn_id='tableau_server', task_id='copy_results_to_db1', command=results_bash, dag=dag) crk = SSHOperator(ssh_conn_id='tableau_server', task_id='copy_ranks_to_db1', command=ranks_bash, dag=dag) crdn = SSHOperator(ssh_conn_id='tableau_server', task_id='copy_results_to_db1_new',
'email_on_retry': False, 'retries': 1, 'retry_delay': timedelta(minutes=2) } dag = DAG('run_edw_qa', default_args=default_args, catchup=False, schedule_interval='30 6 * * *') conn_id = 'ebi_datamart' pool_id = 'ebi_etl_pool' scp = MsSqlOperator(sql='EXEC EBI_SEM_Census_QA_Logic;', task_id='sem_census_qa', autocommit=True, mssql_conn_id=conn_id, pool=pool_id, dag=dag) sce = PythonOperator( task_id='refresh_scm_census_qa_extract', python_callable=refresh_tableau_extract, op_kwargs={'datasource_id': 'df1da0b7-7b06-4e4e-bd8e-0dfd5c3a42a1'}, dag=dag) scp >> sce