airflow: templated argument with BigQueryCheckOperator

Question

I would like to use the BigQueryCheckOperator in airflow where the query needs to have the execution date as parameter. I have written the following operator that check the current date, but I do not understand how to use the execution_date of the dag.

BigQueryCheckOperator(
        task_id='mytask',
        retry_delay=10,
        retries=2,
        sql="""
           SELECT
            COUNT(*)
           FROM
            mydataset.mytable AS tb
           WHERE
            DATE(tb.date) = '{check_date}'
        """.format(
            check_date=(datetime.now()).strftime("%Y-%m-%d")
        ),
        bigquery_conn_id='my_google_cloud_conn',
        dag=dag)

Could you help me?

do you want check_date to be execution_date ? – ozs Jun 24 '22 at 12:20 — ozs, Jun 24 '22 at 12:20
yes, it's exactly what I want to do – Galuoises Jun 24 '22 at 12:54 — Galuoises, Jun 24 '22 at 12:54

ozs · Accepted Answer · 2022-06-24T15:29:20.783

3

You can use Jinja template and variable = {{ ds }}

BigQueryCheckOperator(
        task_id='mytask',
        retry_delay=10,
        retries=2,
        sql="""
           SELECT
            COUNT(*)
           FROM
            mydataset.mytable AS tb
           WHERE
            DATE(tb.date) = DATE('{{ ds }}')""",
        bigquery_conn_id='my_google_cloud_conn',
        dag=dag)

edited Jun 24 '22 at 15:29

answered Jun 24 '22 at 13:02

ozs

3,051
1
10
19

Thanks a lot! I Had to modify it a bit to `DATE('{{ ds }}')` otherwise it wasn't recognized as a date – Galuoises Jun 24 '22 at 15:05

airflow: templated argument with BigQueryCheckOperator

1 Answers1