CODEWITHSUNDEEP
gogoogle-cloud-platformgoogle-bigquery
EldadT
EldadT

Reputation: 932

How to get BigQuery table expiration date via query / cli / go?

I can't find a way to extract table expiration date that is not via the console (https://console.cloud.google.com/). We maintain thousands of tables in BQ and we want to enforce the use of table expiration date - so the only way is to collect the data automatically. is this possible via query/cli/go/python/perl/whatever?

Upvotes: 0

Views: 2801

Answers (3)

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can use BigQuery CLI:

bq show mydataset.mytable

Output:

Last modified         Schema         Total Rows   Total Bytes     Expiration      Time Partitioning   Clustered Fields   Labels
----------------- ------------------- ------------ ------------- ----------------- ------------------- ------------------ --------
 16 Aug 10:42:13   |- col_1: integer   7            106           21 Aug 10:42:13
                   |- col_2: string

Upvotes: 0

Gonzalo Pérez Fernández
Gonzalo Pérez Fernández

Reputation: 416

If you want to extract the expiration time from a number of tables in a dataset, you can refer to this documentation [1] (the same query provided by @EldadT), which returns the catalog of tables in a dataset with the expiration time option.

Therefore, if you want to create a script in Python to get the result of this query, you can also check a Client Bigquery Library [2] to run that query and get the expiration time for each table in a dataset.

[1] https://cloud.google.com/bigquery/docs/tables#example_1_2

[2] https://cloud.google.com/bigquery/docs/reference/libraries#client-libraries-usage-python

Upvotes: 0

EldadT
EldadT

Reputation: 932

this can be done via querying the INFORMATION_SCHEMA.TABLE_OPTIONS:

SELECT * FROM `my_project.my_dataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
where option_name='expiration_timestamp'

the value will be in the option_name column.

Upvotes: 3

Related Questions