CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerydatabase-schema
Fariya
Fariya

Reputation: 284

Merging two queries in bigquery

I need to get table_schemna, table_name, creation_time and last modified time columns

using this query i am not getting table_name.

SELECT * FROM `region`.INFORMATION_SCHEMA.SCHEMATA;

using this query i am not getting Last modified time.

SELECT * FROM `region`.INFORMATION_SCHEMA.TABLES;

Is this possible to merge the above queries to get the required four columns or is there any other way to get the last_modified time ,creation time, table from multiple datasets

Upvotes: 0

Views: 426

Answers (2)

Prajna Rai T
Prajna Rai T

Reputation: 1820

You can consider the below approach to get the table_id, creation time and last_modified time from datasets.

select table_id, TIMESTAMP_MILLIS(creation_time) AS creation_time,
TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
dataset_id
FROM `project.dataset.__TABLES__`

Upvotes: 0

Nestor
Nestor

Reputation: 1377

I did a simple join query with your requirements.

Query:

SELECT table_schema,table_name,a.creation_time,last_modified_time FROM <projectID>.`region-us`.INFORMATION_SCHEMA.TABLES a
INNER JOIN region-us.INFORMATION_SCHEMA.SCHEMATA on schema_name = table_schema ;

Just apply the project ID and and proper region where your BQ dataset is situated. (Also apply proper role to access the tables schema)

Sample Output: enter image description here

Upvotes: 1

Related Questions