Introduction to INFORMATION_SCHEMA
The BigQuery INFORMATION_SCHEMA
views are read-only, system-defined
views that provide metadata information about your BigQuery
objects. The following table lists all INFORMATION_SCHEMA
views that you can
query to retrieve metadata information:
† For *BY_PROJECT
views, the BY_PROJECT
suffix is optional. For
example, querying INFORMATION_SCHEMA.JOBS_BY_PROJECT
and INFORMATION_SCHEMA.JOBS
return the same results.
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 capacity-based pricing, queries against INFORMATION_SCHEMA
views and tables consume your purchased BigQuery slots. For more
information, see capacity-based pricing.
Because INFORMATION_SCHEMA
queries are not cached, you are charged each time
that 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 needs 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
MATERIALIZED_VIEWS
PARAMETERS
PARTITIONS
ROUTINES
ROUTINE_OPTIONS
TABLES
TABLE_OPTIONS
VIEWS
Region qualifier
Region qualifiers are represented using a
region-REGION
syntax.
Any dataset location 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
location. For example, the following query returns metadata for all datasets in a
project in the US
multi-region:
-- Returns metadata for all datasets in a region. SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;
The following INFORMATION_SCHEMA
views don't support region qualifiers:
INFORMATION_SCHEMA.PARTITIONS
INFORMATION_SCHEMA.SEARCH_INDEXES
INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS
INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONS
If neither a region qualifier nor a dataset qualifier is specified, you will receive an error.
Queries against a region-qualified INFORMATION_SCHEMA
view run in the region
that you specify, which means that you can't write a single query to join data
from views in different regions. To combine INFORMATION_SCHEMA
views from multiple regions, write your query results to tables and then
copy your tables
or move your datasets to
a common region.
Project qualifier
When present, a project qualifier restricts results to the specified project. For example:
-- Returns metadata for the specified project and region.
SELECT * FROM myProject.`region-us`.INFORMATION_SCHEMA.TABLES;
-- Returns metadata for the specified project and dataset.
SELECT * FROM myProject.myDataset.INFORMATION_SCHEMA.TABLES;
All INFORMATION_SCHEMA
views support project qualifiers. If a project
qualifier is not specified, the view will default to the
project in which the query is executing.
Specifying a project qualifier for organization-level views
(e.g. STREAMING_TIMELINE_BY_ORGANIZATION
)
has no impact on the results.
Limitations
- BigQuery
INFORMATION_SCHEMA
queries must be in GoogleSQL syntax.INFORMATION_SCHEMA
does not support legacy SQL. INFORMATION_SCHEMA
query results are not cached.INFORMATION_SCHEMA
views cannot be used in DDL statements.INFORMATION_SCHEMA
views don't contain information about hidden datasets.INFORMATION_SCHEMA
queries with region qualifiers might include metadata from resources in that region from deleted datasets that are within your time travel window.- When you list resources from an
INFORMATION_SCHEMA
view, the permissions are checked only at the parent level, not at an individual row level. Therefore, any deny policy (preview) that conditionally targets an individual row using tags is ignored.