airflow-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "zgl (JIRA)" <j...@apache.org>
Subject [jira] [Assigned] (AIRFLOW-1012) Add run_as_script option so jinja templating can be used for sql parameter
Date Thu, 01 Feb 2018 17:17:00 GMT

     [ 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)

Mime
View raw message