CREATE OR REPLACE TABLE using the Google BigQuery Python library
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.
tw0000
Updated on June 13, 2022Comments
-
tw0000 almost 2 years
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.