CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Pierre56
Pierre56

Reputation: 567

BigQuery - copy a query into a new table

I wrote a query for one of my Big Query table called historical and I would like to copy the result of this query into a new Big Query table called historical_recent. I have difficulties to figure out how to do this operation with Python. Right now, I am able to execute my query and get the expected result:

    SELECT * FROM gcp-sandbox.dailydev.historical WHERE (date BETWEEN '2015-11-05 00:00:00' AND '
2015-11-07 23:00:00')

I am also able to copy a my Big Query table without making any changes with this script:

from google.cloud import bigquery
client = bigquery.Client()
job = client.copy_table(
    'gcp-sandbox.dailydev.historical',
    'gcp-sandbox.dailydev.historical_copy')

How can I combine both using Python?

Upvotes: 0

Views: 211

Answers (2)

Cloudkollektiv
Cloudkollektiv

Reputation: 14699

Using Python to save your query result.

from google.cloud import bigquery

client = bigquery.Client()

# Target table to save results
table_id = "gcp-sandbox.dailydev.historical_recent"

job_config = bigquery.QueryJobConfig(
    allow_large_results=True,
    destination=table_id,
    use_legacy_sql=True
)

sql = """
   SELECT * FROM gcp-sandbox.dailydev.historical 
   WHERE (date BETWEEN '2015-11-05 00:00:00' AND '2015-11-07 23:00:00')
"""

query = client.query(sql, job_config=job_config)
query.result()

print("Query results loaded to the table {}".format(table_id))

This example is based on the Google documentation.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

You can use INSERT statement as in below example

INSERT `gcp-sandbox.dailydev.historical_recent`
SELECT * 
FROM `gcp-sandbox.dailydev.historical` 
WHERE date BETWEEN '2015-11-05 00:00:00' AND '2015-11-07 23:00:00'

Upvotes: 2

Related Questions