Mailing-List: contact commits-help@airflow.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@airflow.incubator.apache.org
Date: Sun, 19 Mar 2017 18:48:41 +0000 (UTC)
From: "Ruslan Dautkhanov (JIRA)" <jira@apache.org>
To: commits@airflow.incubator.apache.org
Message-ID: <JIRA.13057374.1489949281000.60643.1489949321516@Atlassian.JIRA>
In-Reply-To: <JIRA.13057374.1489949281000@Atlassian.JIRA>
References: <JIRA.13057374.1489949281000@Atlassian.JIRA> <JIRA.13057374.1489949281074@jira-lw-us.apache.org>
Subject: [jira] [Created] (AIRFLOW-1012) Add run_as_script option so jinja
 templating can be used for sql parameter
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Sun, 19 Mar 2017 18:48:47 -0000

Ruslan Dautkhanov created AIRFLOW-1012:
------------------------------------------

             Summary: 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: Ruslan Dautkhanov


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
(v6.3.15#6346)