Skip to content

Latest commit

 

History

History
40 lines (26 loc) · 2.68 KB

raw_sql.rst

File metadata and controls

40 lines (26 loc) · 2.68 KB
Raw

:pyrun_raw_sql operator <astro.sql.operators.raw_sql>

When to use the run_raw_sql operator

The run_raw_sql operator allows you to declare any SQL statement using the Astro SDK templating available in transform_operator. By default this operator returns None, but you can alternatively define the task output by using the handler argument. For example, you may wish to return a list with the row results of a query.

The run_raw_sql function also treats values in double brackets as Airflow jinja templates. You can find more details on templating at templating.

This example shows how you can create a Snowflake table using run_raw_sql without the handler. This task will return None.

../../../../example_dags/example_snowflake_partial_table_with_append.py

This example shows how you can run a select query in Bigquery and return rows using the handler argument. This task will return the results of the query.

../../../../example_dags/example_bigquery_dynamic_map_task.py

Parameters

  • handler - This parameter is used to pass a callback and this callback gets a cursor object from the database.

  • results_format - There are common scenarios where the kind of results you would expect from the handler function to return.

    1. List - If you expect a query return to be a list of rows. instead of passing handler to do cursor.fetchall(), we can pass results_format=='list'
    2. Pandas Dataframe - If you expect query result to be converted to Pandas Dataframe we can pass results_format=='pandas_dataframe'
  • fail_on_empty - Sometimes the handler function can raise an exception when the data is not returned by the database and we try to run fetchall(). We can make sure that the handler function doesn't raise an exception by passing fail_on_empty==False. The default value for this parameter is True.
  • query_modifier - The query_modifier parameter allows you to define statements to run before and after the run_raw_sql main statement. To associate a Snowflake query tag, for instance, it is possible to use query_modifier=QueryModifier(pre_queries=["ALTER SESSION SET QUERY_TAG=<my-query-tag>]).