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.getbigquery.tables.list
Each of the following predefined IAM roles includes the preceding permissions:
roles/bigquery.adminroles/bigquery.dataViewerroles/bigquery.dataEditorroles/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, NULLIf 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
|
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: nestedRECORDcolumncommitter: nestedRECORDcolumntrailer: nested and repeatedRECORDcolumndifference: 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 | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | commits | author | author | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP> | NULL | | commits | author | author.name | STRING | NULL | | commits | author | author.email | STRING | NULL | | commits | author | author.time_sec | INT64 | NULL | | commits | author | author.tz_offset | INT64 | NULL | | commits | author | author.date | TIMESTAMP | NULL | | 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 | | commits | difference | difference.old_mode | INT64 | NULL | | commits | difference | difference.new_mode | INT64 | NULL | | commits | difference | difference.old_path | STRING | NULL | | commits | difference | difference.new_path | STRING | NULL | | commits | difference | difference.old_sha1 | STRING | NULL | | commits | difference | difference.new_sha1 | STRING | NULL | | commits | difference | difference.old_repo | STRING | NULL | | commits | difference | difference.new_repo | STRING | NULL | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+