Executing parameterized BigQuery SQL inside the Python function

Question

I am trying to pass some parameters through a Python function. Inside the function, I am trying to execute BigQuery SQL and update an existing table(creating and replacing tables). I keep getting

BadRequest: 400 1.2 - 1.118: Unrecognized token CREATE.
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

(job ID: 7417d5d6-fdcd-420e-b7ac-4aaa8bb3347c)

                                              -----Query Job SQL Follows-----                                               

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1: CREATE OR REPLACE TABLE `analytics-mkt-cleanroom.MKT_DS.PXV2DWY_HS_MODEL_INTRMDT_TAB_01` AS SELECT '2021-07-01' AS DT 
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

error.

Here's my complete Jupyter Notebook code:

# Creating and initializing a random table:

    %%bigquery
    CREATE OR REPLACE TABLE `analytics-mkt-cleanroom.MKT_DS.Home_Services_PXV2DWY_HS_MODEL_INTRMDT_TABLE_01` AS 
    SELECT CURRENT_DATE AS DT

# Checking what's the current date:

    %%bigquery
    SELECT * FROM `analytics-mkt-cleanroom.MKT_DS.Home_Services_PXV2DWY_HS_MODEL_INTRMDT_TABLE_01`


# Initializing random str date variable:

    from_date = f"'2021-07-01'"
    to_date = f"'2022-06-30'"


# Creating a Python function to update the existing table using a parameter:

    from google.cloud import bigquery
    def my_func(from_date):
        client = bigquery.Client(project='analytics-mkt-cleanroom')
        job_config = bigquery.QueryJobConfig()
        job_config.use_legacy_sql = True   
    
        destination_table_id = f'`analytics-mkt-cleanroom.MKT_DS.PXV2DWY_HS_MODEL_INTRMDT_TAB_01`'
        
        sql = """ CREATE OR REPLACE TABLE """ + destination_table_id + """ AS SELECT {0} AS DT """.format(from_date)
        query = client.query(sql, job_config=job_config)
        query.result()
        return

# Checking what's the SQL that is getting generated inside:

    destination_table_id = f'`analytics-mkt-cleanroom.MKT_DS.PXV2DWY_HS_MODEL_INTRMDT_TAB_01`'
    sql = """ CREATE OR REPLACE TABLE """ + destination_table_id + """ AS SELECT {0} AS DT """.format(from_date)
    sql


my_func(from_date)

This will be just a small part of larger project where I have to create data pipelines using Python and BigQuery.

Would you try it again with job_config.use_legacy_sql = False ? — Jaytiger
– Jaytiger, Commented Jan 4, 2023 at 1:16

fmsedrez · Accepted Answer · 2023-01-04 01:26:26Z

1

Remove the query job configuration param job_config.use_legacy_sql = True, in this case you don't need to use legacy sql. In fact is not needed to pass job configuration for the client, it has his on default.

Other point that may help you is use F-String for a better code legibility, like this: sql = f"CREATE OR REPLACE TABLE {destination_table_id} AS SELECT {from_date} AS DT"

answered Jan 4, 2023 at 1:26

fmsedrez

662 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing parameterized BigQuery SQL inside the Python function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related