CREATE OR REPLACE TABLE using the Google BigQuery Python library

Question

My Python code is like so:

from google.cloud import bigquery

client = bigquery.Client(
                        project='my-project',
                        credentials=credentials,
                        )
sql = '''
        CREATE OR REPLACE TABLE `my-project.my_dataset.test` AS
            WITH some_table AS (
              SELECT * FROM `my-project.my_dataset.table_1` 
            ),
            some_other_table AS (
              SELECT id, some_column FROM my-project.my_dataset.table_2
            )
            SELECT * FROM some_table 
            LEFT JOIN some_other_table ON some_table.unique_id=some_other_table.id
        '''

query_job = client.query(sql)

query_job.result()

The query works in the Google BigQuery Console UI, but not when executed as above from Python.

I understand that by using CREATE OR REPLACE this is a "DDL" request, which I cannot figure out how to execute from the Python library. You can set the destination table in the job.config, which lets you CREATE a table, but then you don't get the CREATE OR REPLACE functionality.

Thanks for any assistance.

Answer 1

After carefully reviewing the documentation, I can say that the Python SDK for BigQuery don't specify a way to to perform DDL statements as a query. You can find the documented code for the query function you are using here. As you can see, the query parameter expects a SQL statement.

Despite that, I tried to reproduce your problem and it worked for me. I could create the table perfectly by using a DDL statement as you're trying to do. Hence we can conclude that the API consider DDL as a subset of SQL. I suggest that you comment the error you're receiving so I can provide you a better support.