CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-python-api
vortex
vortex

Reputation: 99

GCP BigQuery how to set expiration date to table by python api

I am using BigQuery Python API to create table, and would like to set an expiration date to the table, so the table would be automatically dropped after certain days.

Here is my code:

client = bq.Client()
job_config = bq.QueryJobConfig()
dataset_id = dataset
table_ref = client.dataset(dataset_id).table(filename)
job_config.destination = table_ref
job_config.write_disposition = 'WRITE_TRUNCATE'
dt = datetime.now() + timedelta(seconds=259200)
unixtime = (dt - datetime(1970,1,1)).total_seconds()
expiration_time = unixtime
job_config.expires = expiration_time
query_job = client.query(query, job_config=job_config)
query_job.result()

The problem is that the expiration parameter doesn't seem to work. When I am checking the table detail in the UI, the expiration date is still Never.

Upvotes: 4

Views: 10384

Answers (3)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

To update an existing table expiration time with Python:

import datetime
from google.cloud import bigquery

client = bigquery.Client()
table = client.get_table("project.dataset.table")
table.expires = datetime.datetime.now() + datetime.timedelta(days=1)
client.update_table(table, ['expires'])

Credits: /u/ApproximateIdentity

Upvotes: 6

Willian Fuks
Willian Fuks

Reputation: 11787

Looking at the docs for the query method we can see that it's not possible to set an expiration time in the query job config.

The proper way of doing so is setting at the Table resource, something like:

client = bq.Client()
job_config = bq.QueryJobConfig()
dataset_id = dataset
table_ref = client.dataset(dataset_id).table(filename)
table = bq.Table(table_ref)
dt = datetime.now() + timedelta(seconds=259200)
table.expires = dt
client.create_table(table)

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

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33745

To answer a slightly different question, instead of specifying the expiration as part of the request options, you can use a CREATE TABLE statement instead, where the relevant option is expiration_timestamp. For example:

CREATE OR REPLACE TABLE my_dataset.MyTable
(
  x INT64,
  y FLOAT64
)
OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
);

This creates a table with two columns that will expire three days from now. CREATE TABLE supports an optional AS SELECT clause, too, if you want to create the table from the result of a query (the documentation goes into more detail).

Upvotes: 6

Related Questions