COLUMNS view
The INFORMATION_SCHEMA.COLUMNS
view contains one row for each column
(field) in a table.
Required permissions
To query the INFORMATION_SCHEMA.COLUMNS
view, you need the following
Identity and Access Management (IAM) permissions:
-
bigquery.tables.get -
bigquery.tables.list
Each of the following predefined IAM roles includes the preceding permissions:
-
roles/bigquery.admin -
roles/bigquery.dataViewer -
roles/bigquery.dataEditor -
roles/bigquery.metadataViewer
For more information about BigQuery permissions, see Access control with IAM .
Schema
When you query the INFORMATION_SCHEMA.COLUMNS
view, the query results contain
one row for each column (field) in a table.
The INFORMATION_SCHEMA.COLUMNS
view has the following schema:
| Column name | Data type | Value |
|---|---|---|
TABLE_CATALOG
|
STRING
|
The project ID of the project that contains the dataset |
TABLE_SCHEMA
|
STRING
|
The name of the dataset that contains the table also referred to as
the datasetId
|
TABLE_NAME
|
STRING
|
The name of the table or view also referred to as the tableId
|
COLUMN_NAME
|
STRING
|
The name of the column |
ORDINAL_POSITION
|
INT64
|
The 1-indexed offset of the column within the table; if it's a pseudo
column such as _PARTITIONTIME or _PARTITIONDATE, the value is NULL
|
IS_NULLABLE
|
STRING
|
YES
or NO
depending on whether the column's
mode allows NULL
values |
DATA_TYPE
|
STRING
|
The column's GoogleSQL data type |
IS_GENERATED
|
STRING
|
The value is always NEVER
|
GENERATION_EXPRESSION
|
STRING
|
The value is always NULL
|
IS_STORED
|
STRING
|
The value is always NULL
|
IS_HIDDEN
|
STRING
|
YES
or NO
depending on whether the column is
a pseudo column such as _PARTITIONTIME or _PARTITIONDATE |
IS_UPDATABLE
|
STRING
|
The value is always NULL
|
IS_SYSTEM_DEFINED
|
STRING
|
YES
or NO
depending on whether the column is
a pseudo column such as _PARTITIONTIME or _PARTITIONDATE |
IS_PARTITIONING_COLUMN
|
STRING
|
YES
or NO
depending on whether the column is
a partitioning column
|
CLUSTERING_ORDINAL_POSITION
|
INT64
|
The 1-indexed offset of the column within the table's
clustering columns; the value is NULL
if the table is not a
clustered table |
COLLATION_NAME
|
STRING
|
The name of the collation specification
if it exists; otherwise, NULL
If a STRING
or ARRAY<STRING>
is passed
in, the collation specification is returned if it exists; otherwise NULL
is returned |
COLUMN_DEFAULT
|
STRING
|
The default value
of the
column if it exists; otherwise, the value is NULL
|
ROUNDING_MODE
|
STRING
|
The mode of rounding that's used for values written to the field if its
type is a parameterized NUMERIC
or BIGNUMERIC
;
otherwise, the value is NULL
|
POLICY_TAGS
|
ARRAY<STRING>
|
The list of policy tags that are attached to the column |
Scope and syntax
Queries against this view must include a dataset or a region qualifier. For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project. For more information see Syntax . The following table explains the region and resource scopes for this view:
| View name | Resource scope | Region scope |
|---|---|---|
[ PROJECT_ID
.]`region- REGION
`.INFORMATION_SCHEMA.COLUMNS
|
Project level | REGION
|
[ PROJECT_ID
.] DATASET_ID
.INFORMATION_SCHEMA.COLUMNS
|
Dataset level | Dataset location |
- Optional:
PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used. -
REGION: any dataset region name . For example,`region-us`. -
DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier .
Example
The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMNS
view for the population_by_zip_2010
table in the census_bureau_usa
dataset. This dataset is part of the BigQuery public dataset program
.
Because the table you're querying is in another project, the bigquery-public-data
project, you add the project ID to the dataset in the
following format: ` project_id
`. dataset
.INFORMATION_SCHEMA. view
;
for example, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
.
The following columns are excluded from the query results because they are currently reserved for future use:
-
IS_GENERATED -
GENERATION_EXPRESSION -
IS_STORED -
IS_UPDATABLE
SELECT * EXCEPT ( is_generated , generation_expression , is_stored , is_updatable ) FROM `bigquery-public-data` . census_bureau_usa . INFORMATION_SCHEMA . COLUMNS WHERE table_name = 'population_by_zip_2010' ;
The result is similar to the following. For readability, some columns are excluded from the result.
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+ | table_name | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | policy_tags | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+ | population_by_zip_2010 | zipcode | 1 | NO | STRING | NO | NO | NO | NULL | 0 rows | | population_by_zip_2010 | geo_id | 2 | YES | STRING | NO | NO | NO | NULL | 0 rows | | population_by_zip_2010 | minimum_age | 3 | YES | INT64 | NO | NO | NO | NULL | 0 rows | | population_by_zip_2010 | maximum_age | 4 | YES | INT64 | NO | NO | NO | NULL | 0 rows | | population_by_zip_2010 | gender | 5 | YES | STRING | NO | NO | NO | NULL | 0 rows | | population_by_zip_2010 | population | 6 | YES | INT64 | NO | NO | NO | NULL | 0 rows | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+
