Introduction to BigQuery INFORMATION_SCHEMA

Beta

This feature is in a pre-release state and might change or have limited support. For more information, see the product launch stages.

INFORMATION_SCHEMA is a series of views that provide access to:

• dataset metadata
• job metadata

• reservation metadata

• routine metadata

• table metadata

• view metadata

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 require a specifier.

No specifier

The following INFORMATION_SCHEMA views must not have a specifier:

• INFORMATION_SCHEMA.SCHEMATA
• INFORMATION_SCHEMA.SCHEMATA_OPTIONS

For example:

SELECT * FROM `INFORMATION_SCHEMA.SCHEMATA`

To get metadata from a different project, prepend the project ID to the view.

SELECT * FROM `myOtherProject.INFORMATION_SCHEMA.SCHEMATA`;

Dataset specifier

The following INFORMATION_SCHEMA views must have a dataset specifier:

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

For example:

SELECT * FROM `myDataset.INFORMATION_SCHEMA.COLUMNS`

To get metadata from a different project, prepend the project ID to the view:

SELECT * FROM `myOtherProject.myDataset.INFORMATION_SCHEMA.TABLES`;

Regional specifier

The following INFORMATION_SCHEMA views must have a regional specifier.

• INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT
• INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
• INFORMATION_SCHEMA.JOBS_BY_PROJECT
• INFORMATION_SCHEMA.JOBS_BY_USER
• INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
• INFORMATION_SCHEMA.RESERVATION_TIMELINE_BY_PROJECT

For example:

SELECT * FROM `region-eu.INFORMATION_SCHEMA.JOBS_BY_USER`

To get metadata from a different project, prepend the project ID to the view:

SELECT * FROM `myOtherProject.region-eu.INFORMATION_SCHEMA.JOBS_BY_USER`;

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 cannot be used to retrieve metadata on partitions in partitioned tables.
• Currently, INFORMATION_SCHEMA views cannot be used in DDL statements.