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.

In what way does this "not work"? Do you get some kind of error message? How or where are you trying to run this? — lowtex
– lowtex, Commented Feb 1, 2020 at 0:13
What version of Python library do you use? Older versions default to Legacy SQL dialect, so you might need to upgrade or specify Standard SQL in client properties. — Michael Entin
– Michael Entin, Commented Feb 1, 2020 at 5:40

rmesteves · Accepted Answer · 2020-02-03 10:59:31Z

8

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.

answered Feb 3, 2020 at 10:59

rmesteves

4,1359 silver badges26 bronze badges

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

1 Comment

Iraj Hedayati Over a year ago

"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." could you provide your working code?

Collectives™ on Stack Overflow

CREATE OR REPLACE TABLE using the Google BigQuery Python library

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related