CODEWITHSUNDEEP
google-bigquery
Newb
Newb

Reputation: 3

Is there a metadata table that shows information other than the column name/id/data type for a partitioned table

big query table metadata as seen on the UI console

Is there an info schema table or equivalent that shows the following metadata information about a partitioned table :

Partition range end, Partition range interval, Partition range start, Partitioned by (eg day, hour, month, year)

I can see them in the console UI when clicking on the details tab on the table, but cannot find which if any info schema tables contain them. I've looked in all the listed ones from the google site but cannot se them in there: https://cloud.google.com/bigquery/docs/information-schema-intro

Is there a table in BQ this is contained that's accessible?

At the moment the only way I can find to determine whether a table that's been date partitioned for example by month/day is to look at the length of the partition_id for each partition and determine it that way. It would be more useful/reliable if I could see the information as it's displayed on the console, where it shows partitioned by DAY or MONTH or YEAR etc.

Upvotes: 0

Views: 330

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173106

Use below

select *
from `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` tbl
where regexp_contains(lower(ddl), 'partition by')

Below are fields available to choose from

enter image description here

Upvotes: 0

Related Questions