create a table and query the value of this table using Python API in Bigquery

Question

I want to create a new table and assign the value of this table by writing a query.

from google.cloud import bigquery

bigquery_client = bigquery.Client(project="myproject")
dataset = bigquery_client.dataset("mydataset")
table_ref = dataset.table('New table')
table = bigquery.Table(table_ref)
table = client.create_table(table)

So, I created an empty table

And now I wish to have some values for this table from another table that's called "Old Table":

query = "SELECT * FROM `{Old table}`"

How can I make sure that my table is linked to this query?

I tried

table.view_query = "SELECT * FROM `{Old table}`"

but it didn't work

Thanks,

Vasily Bronsky · Accepted Answer · 2017-12-18 09:29:20Z

2

I think you should use smth like this:

bigquery_client = bigquery.Client(project="myproject")
dataset = bigquery_client.dataset("mydataset")
table_ref = dataset.table('New_table')

sql_query = "SELECT * FROM `{project}.{dataset}.{table}`"

job_config = bigquery.QueryJobConfig()

# Set configuration.query.destinationTable
job_config.destination = table_ref

# Set configuration.query.createDisposition
job_config.create_disposition = 'CREATE_IF_NEEDED'

# Set configuration.query.writeDisposition
job_config.write_disposition = 'WRITE_APPEND'

# Start the query
job = bigquery_client.query(sql_query, job_config=job_config)

# Wait for the query to finish
job.result()

edited Dec 18, 2017 at 9:29

answered Dec 18, 2017 at 7:27

Vasily Bronsky

4332 silver badges12 bronze badges

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

4 Comments

MFR Over a year ago

thanks but when I ran job = bigquery_client.query(sql_query, job_config=job_config) it gives me a bunch of errors including Invalid table ID "New table"

Vasily Bronsky Over a year ago

Try "New_table" except "New table". Thats because IDs may only contain letters, numbers, and underscores.

MFR Over a year ago

Thanks, sorry, that was may bad. Still when I run job.result(). I got some errors such as in result raise self._exception and in result super(QueryJob, self).result(timeout=timeout). Do you have any idea, why?

Vasily Bronsky Over a year ago

Hm, i don't know whats an error. This code works perfectly for me. Try to update your google-cloud-bigquery with 'sudo pip install --upgrade google-cloud-bigquery'

Collectives™ on Stack Overflow

create a table and query the value of this table using Python API in Bigquery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related