[jira] [Updated] (AIRFLOW-1012) Add run_as_script option so jinja templating can be used for sql parameter

["Aizhamal Nurmamat kyzy (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/AIRFLOW-1012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Aizhamal Nurmamat kyzy updated AIRFLOW-1012:
--------------------------------------------
    Component/s:     (was: db)
                     (was: core)
                 database

> Add run_as_script option so jinja templating can be used for sql parameter
> --------------------------------------------------------------------------
>
>                 Key: AIRFLOW-1012
>                 URL: https://issues.apache.org/jira/browse/AIRFLOW-1012
>             Project: Apache Airflow
>          Issue Type: Improvement
>          Components: database
>    Affects Versions: 1.8.0
>            Reporter: Ruslan Dautkhanov
>            Assignee: zgl
>            Priority: Major
>              Labels: database, improvement, operators, sql
>
> It would be great to extend jinja templating to sql parameter for SQL Operators.
> With this improvement, it's possible to have extended Jinja template like below that generates multiple SQL statements that can be passed as a single 'sql' parameter, separated by ';' separator:
> {noformat}
>     )
>     >> OracleOperator( task_id='give_owner_grants', oracle_conn_id=ora_conn1, run_as_script=True,
>         sql='''
>           {% for role in ['CONNECT', 'RESOURCE'] %}
>           GRANT {{ role }} TO {{ schema }};
>           {% endfor %}
>           {% for create_grant in ['PROCEDURE', 'SEQUENCE', 'SESSION', 'TABLE', 'VIEW'] %}
>           GRANT CREATE {{ create_grant }} TO {{ schema }};
>           {% endfor %}
>           {% for tbsp in ['DISCOVER_MART_IDX01', 'DISCOVER_MART_TBS01', 'STAGING_NOLOG'] %}
>           ALTER USER {{ schema }} QUOTA UNLIMITED ON {{ tbsp }};
>           {% endfor %}
>           GRANT SELECT ANY TABLE TO {{ schema }};
>           GRANT EXECUTE ON SYS.DBMS_SESSION TO {{ schema }};
>         '''
>     )
>     >> DummyOperator(task_id='stop')
> {noformat}
> Notice there are three Jinja 'for' loops that generate multiple SQL DDL statements. 
> Without this change, sql has to be passed as an Python array, and Jinja templating can't be used.
> I've tested this change with OracleOperator and works as expected. 
> Notice `run_as_script=True` parameter. run_as_script defaults to False so this is a backward-compatible change.
> Most of the change is in airflow/hooks/dbapi_hook.py (very straightforward as run() already supports running an array of statements) and a light change of airflow/operators/oracle_operator.py - so this change can be easily applied to other sql operators.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)