CODEWITHSUNDEEP
pythongoogle-bigquery
Fauz
Fauz

Reputation: 89

Is there a way to get a list of datasets and the Tables and Views in Big Query?

I have a long list of datasets and in each dataset there are many tables and views. I would like the list of names of all the datasets, the tables and views contained in each. And I would also like to know for each table or view name whether its a table or view?

It would be good to get the results as a dataframe/csv, that I could put back into Big Query.

Getting something like this:

Dataset Name | Table Name/View Name | Table or View

Football | german_team | Table

Football | british_team | View

What are the ways this can be achieved?

Upvotes: 3

Views: 1387

Answers (2)

Fauz
Fauz

Reputation: 89

Here is what I ended up using, just to add a 3rd option (similar to option 2 though).

SELECT project_id, dataset_id, table_id,
    DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
    DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date,
    row_count,
    size_bytes,
    CASE
        WHEN type = 1 THEN 'table'
        WHEN type = 2 THEN 'view'
        WHEN type = 3 THEN 'external'
        ELSE '?'
    END AS type,
    TIMESTAMP_MILLIS(creation_time) AS creation_time,
    TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time
FROM `<project_id>.<dataset_name>.__TABLES__`

Upvotes: 0

Yun Zhang
Yun Zhang

Reputation: 5503

Since you are using python to query BigQuery, you can achieve the goal in 2 steps:

  1. Query information_schema to list all the datasets (documentation)
SELECT
 schema_name
FROM
 INFORMATION_SCHEMA.SCHEMATA
  1. For each dataset, form query like below (documentation):
SELECT
 table_schema AS dataset_name,
 table_name,
 table_type   -- "view" or "table"
FROM
 <dataset_name>.INFORMATION_SCHEMA.TABLES

Upvotes: 4

Related Questions