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.