[GitHub] [airflow] leahecole commented on issue #23129: BigQueryInsertJobOperator fails when there are templated variables in nested dictionaries

[GitBox <gi...@apache.org>]

leahecole commented on issue #23129:
URL: https://github.com/apache/airflow/issues/23129#issuecomment-1108934284

   Hi, yep! Here's a complete DAG
   ```python
   
   import datetime
   
   from airflow import models
   from airflow.providers.google.cloud.operators.bigquery import BigQueryInsertJobOperator
   
   
   
   PROJECT_NAME = '{{var.value.gcp_project}}'
   
   BQ_DATASET_NAME="bigquery-public-data.ghcn_d.ghcnd_2021"
   BQ_DESTINATION_DATASET_NAME="holiday_weather"
   BQ_DESTINATION_TABLE_NAME="holidays_weather_joined"
   
   
   WEATHER_HOLIDAYS_JOIN_QUERY = f"""
   SELECT Holidays.Date, Holiday, id, element, value
   FROM `{PROJECT_NAME}.holiday_weather.holidays` AS Holidays
   JOIN (SELECT id, date, element, value FROM {BQ_DATASET_NAME} AS Table WHERE Table.element="TMAX" AND Table.id LIKE "US%") AS Weather
   ON Holidays.Date = Weather.Date;
   """
   
   yesterday = datetime.datetime.combine(
       datetime.datetime.today() - datetime.timedelta(1),
       datetime.datetime.min.time())
   
   default_dag_args = {
       'start_date': yesterday,
       'email_on_failure': False,
       'email_on_retry': False,
       'retries': 1,
       'retry_delay': datetime.timedelta(minutes=5),
       'project_id': PROJECT_NAME,
       'region': '{{ var.value.gce_region }}',
   
   }
   
   with models.DAG(
           'example_bug',
           # Continue to run DAG once per day
           schedule_interval=datetime.timedelta(days=1),
           default_args=default_dag_args) as dag:
   
       bq_join_holidays_weather_data = BigQueryInsertJobOperator(
           task_id="bq_join_holidays_weather_data",
           configuration={
               "query": {
                   "query": WEATHER_HOLIDAYS_JOIN_QUERY,
                   "useLegacySql": False,
                   "destinationTable": {
                           "projectId": PROJECT_NAME,
                           "datasetId": BQ_DESTINATION_DATASET_NAME,
                           "tableId": BQ_DESTINATION_TABLE_NAME
                       }
               }
           },
           location="US", 
       )
       bq_join_holidays_weather_data`
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org