How to get the DDL of an existing table/view in BigQuery?
Contents
INFORMATION_SCHEMA in BigQuery
The INFORMATION_SCHEMA views allows us to retrieve the metadata information about our BigQuery objects. It has list of views for each BigQuery resource. In this tutorial, we will check the DDL(Create Table/Create View) of the BigQuery tables and views using the below INFORMATION_SCHEMA views.
- INFORMATION_SCHEMA.TABLES
- INFORMATION_SCHEMA.VIEWS
Syntax
|
1 2 3 4 5 6 7 8 9 |
select * from <project_id>.<dataset>.INFORMATION_SCHEMA.<view_name>; (or) select * from <dataset>.INFORMATION_SCHEMA.<view_name>; (or) select * from <project_id>.<region_qualifier>.INFORMATION_SCHEMA.<view_name>; |
- project_id – It is our BigQuery project id.
- dataset – It is a BigQuery dataset name where the table is created.
- region_qualifier – It is a location name where the BigQuery table is created. It represented as region-<data_location>. Examples: region-us, region-us-central1
- view_name – It defines the INFORMATION_SCHEMA views such as TABLES, TABLE_OPTIONS,VIEWS and so on.
CREATE TABLE statement of an existing table
Consider that we have an existing BigQuery table customer_360 in a dataset rc_fin_test_tables. Let’s check the DDL of this table using INFORMATION_SCHEMA.
Since we want to check the metadata of the table, we can query from INFORMATION_SCHEMA.TABLES. As shown below, the Select query is written with the INFORMATION_SCHEMA. Along with that, we have provided our BigQuery project id (rc-bq-practice) and the dataset name(rc_fin_test_tables).
|
1 |
select * from rc-bq-practice.rc_fin_test_tables.INFORMATION_SCHEMA.TABLES; |
This query fetched the metadata of the BigQuery tables which are created under dataset rc_fin_test_tables and project rc-bq-practice. In the below screenshot, we could see the create table DDL with other details such as creation time, table type and so on.
Now we can filter the results based on table name customer_360.
|
1 2 |
select * from rc-bq-practice.rc_fin_test_tables.INFORMATION_SCHEMA.TABLES where table_name ='customer_360'; |
CREATE VIEW statement of an existing view
For the table customer_360, we have an existing BigQuery view with the same name in another dataset rc_fin_test_views. Let’s get the CRATE VIEW statement of customer_360.
Both CREATE TABLE and CREATE VIEW statement can be retrieved from same INFORMATION_SCHEMA.TABLES. Since the view is created in another dataset, we need to change that name in the Select query.
|
1 |
select * from rc-bq-practice.rc_fin_test_views.INFORMATION_SCHEMA.TABLES; |
As shown below, the query returned the CREATE VIEW statement for the existing view customer_360.
From INFORMATION_SCHEMA.VIEWS, we can get only the view definition of the existing BigQuery views, meaning that it returns the SELECT statement that provides the definition of the view.
|
1 |
select * from rc-bq-practice.rc_fin_test_views.INFORMATION_SCHEMA.VIEWS; |
Recommended Articles
- Create table as Select, Create table Copy and Create table Like in BigQuery
- How to create a view in BigQuery?
- How to create an external table in BigQuery?
References from GCP official documentation
Your Suggestions
