[FAQ] How to obtain the DDL of a table in BigQuery?

[elcapo]

Course

data-engineering-zoomcamp

Question

How to obtain the DDL of a table in BigQuery?

Answer

In BigQuery the DDL statement that would be needed to recreate a table can be obtained by querying the INFORMATION_SCHEMA.TABLES metadata table. Assuming that we are in the zoomcamp dataset and we want to know the DDL of the yellow_tripdata_parquet table, we could run:

SELECT table_name, ddl
FROM zoomcamp.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'yellow_tripdata_parquet';

The result would be the CREATE TABLE needed to recreate the table:

CREATE TABLE `zoomcamp-ingenieria-datos.zoomcamp.yellow_tripdata_parquet`
(
  VendorID INT64,
  tpep_pickup_datetime TIMESTAMP,
  tpep_dropoff_datetime TIMESTAMP,
  passenger_count INT64,
  trip_distance FLOAT64,
  RatecodeID INT64,
  store_and_fwd_flag STRING,
  PULocationID INT64,
  DOLocationID INT64,
  payment_type INT64,
  fare_amount FLOAT64,
  extra FLOAT64,
  mta_tax FLOAT64,
  tip_amount FLOAT64,
  tolls_amount FLOAT64,
  improvement_surcharge FLOAT64,
  total_amount FLOAT64,
  congestion_surcharge FLOAT64,
  Airport_fee FLOAT64
);

The technique also works for external tables. For instance:

SELECT table_name, ddl
FROM zoomcamp.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'yellow_tripdata_parquet_ext';

In this case, the result would contain the DDL including the path of the external files that store the data.

CREATE EXTERNAL TABLE `zoomcamp-ingenieria-datos.zoomcamp.yellow_tripdata_parquet_ext`
OPTIONS(
  format="PARQUET",
  uris=["gs://newyork-taxi/yellow_tripdata_*.parquet"]
);

There is more information in the official documentation of information schema tables.

Checklist

• I have searched existing FAQs and this question is not already answered
• The answer provides accurate, helpful information
• I have included any relevant code examples or links

[github-actions]

✅ FAQ NEW proposal created in PR #147

Please review and approve the changes.