def test_snowflake_operator(self, mock_get_hook):
sql = """
CREATE TABLE IF NOT EXISTS test_airflow (
dummy VARCHAR(50)
);
"""
operator = SnowflakeOperator(task_id='basic_snowflake', sql=sql, dag=self.dag)
operator.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE, ignore_ti_state=True)
}
dag = DAG(
'example_snowflake',
default_args=default_args,
start_date=days_ago(2),
tags=['example'],
)
# [START howto_operator_snowflake]
snowflake_op_sql_str = SnowflakeOperator(
task_id='snowflake_op_sql_str',
dag=dag,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
sql=CREATE_TABLE_SQL_STRING,
warehouse=SNOWFLAKE_WAREHOUSE,
database=SNOWFLAKE_DATABASE,
schema=SNOWFLAKE_SCHEMA,
role=SNOWFLAKE_ROLE,
)
snowflake_op_with_params = SnowflakeOperator(
task_id='snowflake_op_with_params',
dag=dag,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
sql=SQL_INSERT_STATEMENT,
parameters={"id": 56},
warehouse=SNOWFLAKE_WAREHOUSE,
database=SNOWFLAKE_DATABASE,
schema=SNOWFLAKE_SCHEMA,
role=SNOWFLAKE_ROLE,
SNOWFLAKE_CREATE_TABLE_SQL = f"CREATE TRANSIENT TABLE IF NOT EXISTS {SNOWFLAKE_LOAD_TABLE}(data VARIANT);"
default_args = {
'owner': 'airflow',
}
dag = DAG(
'example_snowflake',
default_args=default_args,
start_date=days_ago(2),
tags=['example'],
)
select = SnowflakeOperator(
task_id='select',
snowflake_conn_id=SNOWFLAKE_CONN_ID,
sql=SNOWFLAKE_SELECT_SQL,
dag=dag,
)
slack_report = SnowflakeToSlackOperator(task_id="slack_report",
sql=SNOWFLAKE_SLACK_SQL,
slack_message=SNOWFLAKE_SLACK_MESSAGE,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
slack_conn_id=SLACK_CONN_ID,
dag=dag)
create_table = SnowflakeOperator(
task_id='create_table',
snowflake_conn_id=SNOWFLAKE_CONN_ID,
sql=SNOWFLAKE_CREATE_TABLE_SQL,
schema=SNOWFLAKE_SCHEMA,
) as dag:
# This example shows a SnowflakeOperator followed by a lineage emission. However, the
# same DatahubEmitterOperator can be used to emit lineage in any context.
sql = """CREATE OR REPLACE TABLE `mydb.schema.tableC` AS
WITH some_table AS (
SELECT * FROM `mydb.schema.tableA`
),
some_other_table AS (
SELECT id, some_column FROM `mydb.schema.tableB`
)
SELECT * FROM some_table
LEFT JOIN some_other_table ON some_table.unique_id=some_other_table.id"""
transformation_task = SnowflakeOperator(
task_id="snowflake_transformation",
dag=dag,
snowflake_conn_id="snowflake_default",
sql=sql,
)
emit_lineage_task = DatahubEmitterOperator(
task_id="emit_lineage",
datahub_conn_id="datahub_rest_default",
mces=[
builder.make_lineage_mce(
upstream_urns=[
builder.make_dataset_urn("snowflake", "mydb.schema.tableA"),
builder.make_dataset_urn("snowflake", "mydb.schema.tableB"),
],
downstream_urn=builder.make_dataset_urn(
"snowflake", "mydb.schema.tableC"
),
endpoints = ['ca', 'co', 'wa', 'or']
date = '{{ yesterday_ds_nodash }}'
with DAG('covid_data_s3_to_snowflake',
start_date=datetime(2020, 6, 1),
max_active_runs=1,
schedule_interval='@daily',
default_args=default_args,
catchup=False) as dag:
t0 = DummyOperator(task_id='start')
pivot_data = SnowflakeOperator(task_id='call_pivot_sproc',
snowflake_conn_id='snowflake',
sql='call pivot_state_data();',
role='KENTEND',
schema='SANDBOX_KENTEND')
for endpoint in endpoints:
generate_files = PythonOperator(
task_id='generate_file_{0}'.format(endpoint),
python_callable=upload_to_s3,
op_kwargs={
'endpoint': endpoint,
'date': date
})
snowflake = S3ToSnowflakeOperator(
task_id='upload_{0}_snowflake'.format(endpoint),
s3_keys=['{0}_{1}.csv'.format(endpoint, date)],
from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from datetime import datetime, timedelta
default_args = {
'owner': 'airflow',
'depends_on_past': False,
'email_on_failure': False,
'email_on_retry': False,
'retries': 1,
'retry_delay': timedelta(minutes=1)
}
with DAG('paramaterized_query',
start_date=datetime(2020, 6, 1),
max_active_runs=3,
schedule_interval='@daily',
default_args=default_args,
template_searchpath='/usr/local/airflow/include',
catchup=False) as dag:
opr_param_query = SnowflakeOperator(task_id='param_query',
snowflake_conn_id='snowflake',
sql='param-query.sql')
default_args = {
'owner': 'airflow',
'start_date': days_ago(2),
}
dag = DAG(
'example_snowflake',
default_args=default_args,
tags=['example'],
)
select = SnowflakeOperator(
task_id='select',
snowflake_conn_id=SNOWFLAKE_CONN_ID,
sql="""
SELECT *
FROM {0}
LIMIT 100;
""".format(SNOWFLAKE_SAMPLE_TABLE),
dag=dag,
)
create_table = SnowflakeOperator(
task_id='create_table',
snowflake_conn_id='snowflake_conn_id',
sql="""
CREATE TRANSIENT TABLE IF NOT EXISTS {0} (
data VARIANT
);
""".format(SNOWFLAKE_LOAD_TABLE),
schema=SNOWFLAKE_SCHEMA,
dag=dag,
