COLUMN_FIELD_PATHS view
The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
view contains one row for each column nested
within a RECORD
(or STRUCT
) column.
Required permissions
To query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
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
Query results contain one row for each column nested
within a RECORD
(or STRUCT
) column.
When you query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
view, the query
results contain one row for each column nested
within a RECORD
(or STRUCT
) column.
The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
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 |
FIELD_PATH
|
STRING
|
The path to a column nested within a `RECORD` or `STRUCT` column |
DATA_TYPE
|
STRING
|
The column's GoogleSQL data type |
DESCRIPTION
|
STRING
|
The column's description |
COLLATION_NAME
|
STRING
|
The name of the collation specification
if it exists; otherwise, NULL
If a STRING
, ARRAY<STRING>
, or STRING
field in a STRUCT
is passed in, the
collation specification is returned if it exists; otherwise, NULL
is returned |
ROUNDING_MODE
|
STRING
|
The mode of rounding that's used when applying precision and scale to
parameterized NUMERIC
or BIGNUMERIC
values;
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.COLUMN_FIELD_PATHS
|
Project level | REGION
|
[ PROJECT_ID
.] DATASET_ID
.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
|
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.COLUMN_FIELD_PATHS
view for the commits
table in the github_repos
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`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
.
The commits
table contains the following nested and nested and repeated
columns:
-
author: nestedRECORDcolumn -
committer: nestedRECORDcolumn -
trailer: nested and repeatedRECORDcolumn -
difference: nested and repeatedRECORDcolumn
To view metadata about the author
and difference
columns, run the following query.
SELECT * FROM `bigquery-public-data` . github_repos . INFORMATION_SCHEMA . COLUMN_FIELD_PATHS WHERE table_name = 'commits' AND ( column_name = 'author' OR column_name = 'difference' );
The result is similar to the following. For readability, some columns are excluded from the result.
+------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+ | table_name | column_name | field_path | data_type | description | policy_tags | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+ | commits | author | author | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP> | NULL | 0 rows | | commits | author | author.name | STRING | NULL | 0 rows | | commits | author | author.email | STRING | NULL | 0 rows | | commits | author | author.time_sec | INT64 | NULL | 0 rows | | commits | author | author.tz_offset | INT64 | NULL | 0 rows | | commits | author | author.date | TIMESTAMP | NULL | 0 rows | | commits | difference | difference | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL | 0 rows | | commits | difference | difference.old_mode | INT64 | NULL | 0 rows | | commits | difference | difference.new_mode | INT64 | NULL | 0 rows | | commits | difference | difference.old_path | STRING | NULL | 0 rows | | commits | difference | difference.new_path | STRING | NULL | 0 rows | | commits | difference | difference.old_sha1 | STRING | NULL | 0 rows | | commits | difference | difference.new_sha1 | STRING | NULL | 0 rows | | commits | difference | difference.old_repo | STRING | NULL | 0 rows | | commits | difference | difference.new_repo | STRING | NULL | 0 rows | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
