astro.sql.operators.raw_sql

Module Contents

Classes

RawSQLOperator

Given a SQL statement, (optional) tables and a (optional) function, execute the SQL statement

Functions

run_raw_sql([python_callable, conn_id, parameters, ...])

Given a python function that returns a SQL statement and (optional) tables, execute the SQL statement and output
class astro.sql.operators.raw_sql.RawSQLOperator(conn_id=None, parameters=None, handler=None, database=None, schema=None, response_limit=-1, response_size=-1, sql='', **kwargs)

Bases: astro.sql.operators.base_decorator.BaseSQLDecoratedOperator

Given a SQL statement, (optional) tables and a (optional) function, execute the SQL statement and apply the function to the results, returning the result of the function.

Disclaimer: this could potentially trash the XCom Database, depending on the XCom backend used and on the SQL statement/function declared by the user.

Parameters

  • conn_id (str | None) –

  • parameters (dict | None) –

  • handler (Function | None) –

  • database (str | None) –

  • schema (str | None) –

  • response_limit (int) –

  • response_size (int) –

  • sql (str) –

  • kwargs (Any) –

execute(context)

This is the main method to derive when creating an operator. Context is the same dictionary used as when rendering jinja templates.

Refer to get_template_context for more context.

Parameters

context (astro.utils.typing_compat.Context) –

Return type

Any

astro.sql.operators.raw_sql.run_raw_sql(python_callable=None, conn_id='', parameters=None, database=None, schema=None, handler=None, response_size=settings.RAW_SQL_MAX_RESPONSE_SIZE, **kwargs)

Given a python function that returns a SQL statement and (optional) tables, execute the SQL statement and output the result into a SQL table.

Use this function as a decorator like so:

@run_raw_sql
def my_sql_statement(table1: Table) -> Table:
    return "DROP TABLE {{table1}}"

In this example, by identifying parameters as Table objects, astro knows to automatically convert those objects into tables (if they are, for example, a dataframe). Any type besides table will lead astro to assume you do not want the parameter converted.

Please note that the run_raw_sql function will not create a temporary table. It will either return the result of a provided handler function or it will not return anything at all.

Parameters

  • python_callable (Callable | None) – This parameter is filled in automatically when you use the transform function as a decorator. This is where the python function gets passed to the wrapping function

  • conn_id (str) – Connection ID for the database you want to connect to. If you do not pass in a value for this object we can infer the connection ID from the first table passed into the python_callable function. (required if there are no table arguments)

  • parameters (Mapping | Iterable | None) – parameters to pass into the SQL query

  • database (str | None) – Database within the SQL instance you want to access. If left blank we will default to the table.metatadata.database in the first Table passed to the function (required if there are no table arguments)

  • schema (str | None) – Schema within the SQL instance you want to access. If left blank we will default to the table.metatadata.schema in the first Table passed to the function (required if there are no table arguments)

  • handler (Callable | None) – Handler function to process the result of the SQL query. For more information please consult https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Result

  • response_size (int) – Used to trim the responses returned to avoid trashing the Airflow DB. The default value is -1, which means the response is not changed. Otherwise, if the response is a list, returns up to the desired amount of items. If the response is a string, trims it to the desired size.

  • kwargs (Any) –

Returns

By default returns None unless there is a handler function, in which case returns the result of the handler

Return type

airflow.decorators.base.TaskDecorator