[
https://issues.apache.org/jira/browse/AIRFLOW-1012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zgl reassigned AIRFLOW-1012:
----------------------------
Assignee: zgl (was: Ruslan Dautkhanov)
> 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: core, db
> Affects Versions: Airflow 1.8
> 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)
