How to get the DDL of an existing table/view in BigQuery?

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

  • 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).

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.

Create table DDL in INFORMATION_SCHEMA.TABLES
Create table DDL in INFORMATION_SCHEMA.TABLES

Now we can filter the results based on table name customer_360.

Create table DDL of BigQuery table
Create table DDL of BigQuery table

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.

As shown below, the query returned the CREATE VIEW statement for the existing view customer_360.

Create view DDL for existing BigQuery view
Create view DDL for existing BigQuery view

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.

View Definition of the existing BigQuery view
View Definition of the existing BigQuery view

Recommended Articles

References from GCP official documentation