INFORMATION_SCHEMA: explore a dataset
Every dataset can describe itself. Before you query a table you do not know, ask BigQuery with plain SQL what tables exist and what their columns are.
How it works
INFORMATION_SCHEMA is a set of read-only views that describe your data: one row for each table, for each column, for each field. You query them like tables. Name the dataset before the view: write learn.mot in backticks, a dot, and then INFORMATION_SCHEMA.COLUMNS.
TABLEShas one row for each table, withtable_name,table_typeandddl, the statement that would create the table again.COLUMNShas one row for each top-level column of each table, withcolumn_nameanddata_type. Search it to find which tables hold a column.COLUMN_FIELD_PATHSgoes inside structs. It has one row for each field, with afield_pathsuch asodometer.value_normalized_km.- A view about a whole project takes a region in place of a dataset: write
region-euin backticks, a dot, and thenINFORMATION_SCHEMA.JOBS. JOBSis the job history of that region, with who ran what and what it cost.TABLE_STORAGEalso takes a region, and it shows the size of each table.
Common mistake
The view names are case sensitive. Write INFORMATION_SCHEMA in capitals and the view as BigQuery spells it. Any other spelling fails.
Worked example
SELECT column_name, data_type
FROM `learn.mot`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'dim_vehicle'
ORDER BY ordinal_position
LIMIT 4The query lists the first four columns of dim_vehicle with their types.
Nothing is read from dim_vehicle itself, only its description.