Runtime variable for Oracle DATE type in Airflow

Question

I'm trying to define task that requires a run-time parameter (let's call it 'batch_dt') in Apache Airflow.
I'm using OracleStoredProcedureOperator and the parameter of the procedure is of database type date.

procedure use_dates (
    i_date  in date,
    i_date2 in date,
    i_date3 in date
  );

However I'm having hard time defining this parameter as runtime variable. I could use an exactly formatted string for particular database but don't want to depend on current NLS setting in the database.

Airflow macros don't work {{ dag_run.conf['batch_dt'] }} or even
{{ macros.datetime.strptime(dag_run.conf['batch_dt'], '%Y-%m-%d') }} returns always a string resulting in

ORA-01861: literal does not match format string
Using to_date('{{ dag_run.conf['batch_dt'] }}', 'DD-MM-YYYY') results in

ORA-01858: a non-numeric character was found where a numeric was expected
When I define date.today() right in the task, it works fine, however I need to use run-time variable.

res_task = OracleStoredProcedureOperator(
    task_id = 'mytask',
    procedure = 'use_dates',
    parameters = 
        {"i_date": date.today(), #works but is not runtime
        "i_date2": "to_date('{{ dag_run.conf['batch_dt'] }}', 'DD-MM-YYYY')", 
        #returns a string "to_date('13-06-2022', 'DD-MM-YYYY')" which results in ORA-01858: a non-numeric character was found where a numeric was expected
        "i_date3": "{{ macros.datetime.strptime(dag_run.conf['batch_dt'], '%Y-%m-%d' ) }}"
        #returns a string '2022-06-13 00:00:00' which results in ORA-01861: literal does not match format string
        }
    )

I was thinking about macro that returns a datetime object at runtime, however it seems macros can return only strings. Any idea how this can be achieved?

user_4841399 · Answer

Jinja templating, used in Apache Airflow, renders template fields as strings by default. Therefore the result of your strptime method call is converted into string when rendered.

To override this behavior and be able to render native Python objects instead of plain strings, you have to set DAG's render_template_as_native_obj option to True (default option's value is False).

with DAG(
        'USE_DATES',
        description='TEST DATE INPUT RUNTIME DAG',
        ...
        render_template_as_native_obj=True
) as dag:
...

Your OracleStoredProcedureOperator call should now function as expected, passing Jinja rendered parameters as native Python objects (datetime in this case).

For more information please refer to official Airflow's documentation: https://airflow.apache.org/docs/apache-airflow/stable/concepts/operators.html#rendering-fields-as-native-python-objects

user_9623730 · Answer

call_procedure = OracleStoredProcedureOperator(
    task_id="call_procedure",
    do_xcom_push=False,        
    procedure="PROC_XYZ",
    parameters={"P_IN_WORK_DATE":date(2020, 5, 17)},
)

You can try 1) AIRFLOW__CORE__ENABLE_XCOM_PICKLING: true 2) Set do_xcom_push=False . Any of these should help you to pass date as input and also avoid error shown below. Reference:- https://github.com/apache/airflow/issues/16386#issuecomment-1060017364

ERROR - Could not serialize the XCom value into JSON. If you are using pickle instead of JSON for XCom, then you need to enable pickle support for XCom in your *** config.

Runtime variable for Oracle DATE type in Airflow

2 Answers