How to pass use_legacy_sql=False to the SqlSensor in an Airflow DAG with a BigQuery connection?

Question

I am using my own custom SqlSensor since the release of Apache Airflow 1.9, as I was unable to use the one included with Standard SQL statements operating on Google BigQuery, because the default is to use legacy SQL.

I've checked the recent 1.10.3 release and it seems this is still the case. Is there any other way to make this work besides using my own SQL sensor as a plugin?

Answer 1

You can also pass it in as hook_param as long as your connection begins with gcpbigquery:// (maps to BigQueryHook) and not google-cloud-platform:// (maps to GoogleBaseHook which is too general)

    wait = SqlSensor(
        task_id="wait_task",
        conn_id="bigquery",
        sql="SELECT 1",
        fail_on_empty=False,
        poke_interval=60,  # 1 minute
        mode="reschedule",
        timeout=60 * 117,
        hook_params={
            "use_legacy_sql": False,
            "location": "us",
            "api_resource_configs": {"query": {"useQueryCache": False}},
        },
    )

https://github.com/apache/airflow/blob/2.9.1/airflow/providers/google/cloud/hooks/bigquery.py#L111