如何获取BigQuery表与数据集的列信息，构建Data Studio数据字典报告

Great question! Let’s walk through exactly how to extract column details (and their descriptions) from BigQuery, leverage the right metadata tables, and turn that into a clean data dictionary report in Data Studio.

Step 1: Use BigQuery’s INFORMATION_SCHEMA for Metadata #

The old __TABLES__ and __TABLES_SUMMARY__ views are limited—they only give high-level table info, not column-level details. Instead, use the INFORMATION_SCHEMA.COLUMNS view, which is SQL-standard and built for this exact use case.

Query a Single Table’s Columns & Descriptions #

Run this query to get all columns, their data types, nullability, and descriptions for a specific table:

SELECT
  column_name,
  data_type,
  is_nullable,
  description
FROM
  `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'your-target-table'
ORDER BY
  ordinal_position;

• ordinal_position ensures columns are listed in the same order as they appear in the table.
• The description field pulls the custom descriptions you’ve added to columns in BigQuery (you can set these in the BigQuery UI by editing the table schema).

Query All Tables in a Dataset #

To get column details for every table in a dataset, remove the WHERE clause:

SELECT
  table_name,
  column_name,
  data_type,
  is_nullable,
  description
FROM
  `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
ORDER BY
  table_name, ordinal_position;

Bonus: Get Table-Level Metadata #

If you want to include table descriptions alongside column data, join with INFORMATION_SCHEMA.TABLES:

SELECT
  t.table_name,
  t.description AS table_description,
  c.column_name,
  c.data_type,
  c.is_nullable,
  c.description AS column_description
FROM
  `your-project.your-dataset.INFORMATION_SCHEMA.TABLES` t
JOIN
  `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS` c
ON
  t.table_name = c.table_name
ORDER BY
  t.table_name, c.ordinal_position;

Step 2: Build the Data Dictionary Report in Data Studio #

Once you have your metadata query ready, turn it into a shareable report:

1. Save your BigQuery query as a view: This makes it easy to connect to Data Studio without re-running the query every time.
2. Connect Data Studio to BigQuery: In Data Studio, create a new data source, select BigQuery, and pick your saved view.
3. Design your report:
   • Add a table widget to display table_name, column_name, data_type, column_description, and table_description.
   • Add a filter control (e.g., a text input) to let users search for specific tables or columns.
   • Include card widgets to show summary stats: total number of tables, total number of columns, or count of columns with descriptions.
   • Format the table to highlight empty descriptions (so you know where to add documentation!).

Pro Tips #

• If your column descriptions are empty, head back to BigQuery’s table editor to add them—this makes your data dictionary far more useful.
• To pull metadata across multiple datasets in a project, use a wildcard in your query:

  SELECT
    table_catalog AS project_id,
    table_schema AS dataset_name,
    table_name,
    column_name,
    data_type,
    description
  FROM
    `your-project.*.INFORMATION_SCHEMA.COLUMNS`
  

• For legacy projects still using __TABLES__, note that it only returns table names, creation time, and row counts—no column data, so it’s not useful for this task.

内容的提问来源于stack exchange，提问作者Kiran Kumar