Introduction to BigQuery INFORMATION_SCHEMA

INFORMATION_SCHEMA is a series of views that provide information about the following:

Resource type INFORMATION_SCHEMA View
Access control INFORMATION_SCHEMA.OBJECT_PRIVILEGES
Datasets INFORMATION_SCHEMA.SCHEMATA
Dataset options INFORMATION_SCHEMA.SCHEMATA_OPTIONS
Jobs INFORMATION_SCHEMA.JOBS_BY_*
Job timelines INFORMATION_SCHEMA.JOBS_TIMELINE_BY_*
Reservations INFORMATION_SCHEMA.RESERVATION*
Reservation assignments INFORMATION_SCHEMA.ASSIGNMENT*
Reservation capacity commitments INFORMATION_SCHEMA.CAPACITY_COMMITMENT*
Routines INFORMATION_SCHEMA.ROUTINES
Routine options INFORMATION_SCHEMA.ROUTINE_OPTIONS
Routine parameters INFORMATION_SCHEMA.PARAMETERS
Sessions INFORMATION_SCHEMA.SESSIONS_BY_*
Streaming data INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_*
Tables INFORMATION_SCHEMA.TABLE*
Table columns INFORMATION_SCHEMA.COLUMN*
Table partitions INFORMATION_SCHEMA.PARTITIONS
Table snapshots TABLE_SNAPSHOTS
Table storage usage (current) INFORMATION_SCHEMA.TABLE_STORAGE
Table storage usage over time INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_*
Views INFORMATION_SCHEMA.VIEWS

Pricing

For projects that use on-demand pricing, queries against INFORMATION_SCHEMA views incur a minimum of 10 MB of data processing charges, even if the bytes processed by the query are less than 10 MB. 10 MB is the minimum billing amount for on-demand queries. For more information, see On-demand pricing.

For projects that use flat-rate pricing, queries against INFORMATION_SCHEMA views and tables consume your purchased BigQuery slots. For more information, see Flat-rate pricing.

Because INFORMATION_SCHEMA queries are not cached, you are charged each time you run an INFORMATION_SCHEMA query, even if the query text is the same each time you run it.

You are not charged storage fees for the INFORMATION_SCHEMA views.

Syntax

An INFORMATION_SCHEMA view might need to be qualified with a dataset or region.

Dataset qualifier

When present, a dataset qualifier restricts results to the specified dataset. For example:

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;

The following INFORMATION_SCHEMA views support dataset qualifiers:

  • COLUMNS
  • COLUMN_FIELD_PATHS
  • PARAMETERS
  • ROUTINES
  • ROUTINE_OPTIONS
  • TABLES
  • TABLE_OPTIONS
  • VIEWS

Region qualifier

Region qualifiers are represented using a region-REGION syntax. Any dataset region name can be used for REGION. For example, the following region qualifiers are valid:

  • region-us
  • region-asia-east2
  • region-europe-north1

When present, a region qualifier restricts results to the specified region. For example, the following query returns metadata for all datasets in a project in the US multi-region:

-- Returns metadata for datasets in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;

The following INFORMATION_SCHEMA views support region qualifiers:

  • All views that support dataset qualifiers
  • ASSIGNMENT_CHANGES_BY_PROJECT
  • ASSIGNMENTS_BY_PROJECT
  • CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
  • CAPACITY_COMMITMENTS_BY_PROJECT
  • JOBS_BY_ORGANIZATION
  • JOBS_BY_FOLDER
  • JOBS_BY_PROJECT
  • JOBS_BY_USER
  • JOBS_TIMELINE_BY_ORGANIZATION
  • JOBS_TIMELINE_BY_FOLDER
  • JOBS_TIMELINE_BY_PROJECT
  • JOBS_TIMELINE_BY_USER
  • OBJECT_PRIVILEGES
  • RESERVATION_CHANGES_BY_PROJECT
  • RESERVATIONS_PROJECT
  • STREAMING_TIMELINE_BY_ORGANIZATION
  • STREAMING_TIMELINE_BY_FOLDER
  • STREAMING_TIMELINE_BY_PROJECT
  • SCHEMATA
  • SCHEMATA_OPTIONS
  • TABLE_STORAGE
  • TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
  • TABLE_STORAGE_TIMELINE_BY_PROJECT

Project qualifier

When present, a project qualifier restricts results to the specified project. For example:

-- Returns metadata for the specified project in a region.
SELECT * FROM myProject.`region-us`.INFORMATION_SCHEMA.TABLES;

-- Returns metadata for the specified project and dataset.
SELECT * FROM myProject.myDataset.INFORMATION_SCHEMA.TABLES;

The following INFORMATION_SCHEMA views support project ID qualifiers:

  • All views that support dataset qualifiers
  • All views that support region qualifiers

Limitations

BigQuery INFORMATION_SCHEMA is subject to the following limitations:

  • BigQuery INFORMATION_SCHEMA queries must be in standard SQL syntax. INFORMATION_SCHEMA does not support legacy SQL.
  • INFORMATION_SCHEMA query results are not cached.
  • Currently, INFORMATION_SCHEMA views cannot be used in DDL statements.