TABLE_OPTIONS view

The INFORMATION_SCHEMA.TABLE_OPTIONS view contains one row for each option, for each table or view in a dataset. The TABLES and TABLE_OPTIONS views also contain high-level information about views. For detailed information, query the INFORMATION_SCHEMA.VIEWS view.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_OPTIONS view, you need the following Identity and Access Management (IAM) permissions:

• bigquery.tables.get
• bigquery.tables.list
• bigquery.routines.get
• bigquery.routines.list

Each of the following predefined IAM roles includes the preceding permissions:

• roles/bigquery.admin
• roles/bigquery.dataViewer
• roles/bigquery.metadataViewer

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.TABLE_OPTIONS view, the query results contain one row for each option, for each table or view in a dataset. For detailed information about views, query the INFORMATION_SCHEMA.VIEWS view instead.

The INFORMATION_SCHEMA.TABLE_OPTIONS 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 or view also referred to as the datasetId
TABLE_NAME	STRING	The name of the table or view also referred to as the tableId
OPTION_NAME	STRING	One of the name values in the options table
OPTION_TYPE	STRING	One of the data type values in the options table
OPTION_VALUE	STRING	One of the value options in the options table

Options table

OPTION_NAME	OPTION_TYPE	OPTION_VALUE
partition_expiration_days	FLOAT64	The default lifetime, in days, of all partitions in a partitioned table
expiration_timestamp	TIMESTAMP	The time when this table expires
kms_key_name	STRING	The name of the Cloud KMS key used to encrypt the table
friendly_name	STRING	The table's descriptive name
description	STRING	A description of the table
labels	ARRAY<STRUCT<STRING, STRING>>	An array of STRUCT's that represent the labels on the table
require_partition_filter	BOOL	Whether queries over the table require a partition filter
enable_refresh	BOOL	Whether automatic refresh is enabled for a materialized view
refresh_interval_minutes	FLOAT64	How frequently a materialized view is refreshed

For external tables, the following options are possible:

Options
allow_jagged_rows	BOOL If true, allow rows that are missing trailing optional columns. Applies to CSV data.
allow_quoted_newlines	BOOL If true, allow quoted data sections that contain newline characters in the file. Applies to CSV data.
bigtable_options	STRING Only required when creating a Bigtable external table. Specifies the schema of the Bigtable external table in JSON format. For a list of Bigtable table definition options, see BigtableOptions in the REST API reference.
compression	STRING The compression type of the data source. Supported values include: GZIP. If not specified, the data source is uncompressed. Applies to CSV and JSON data.
decimal_target_types	ARRAY<STRING> Determines how to convert a Decimal type. Equivalent to ExternalDataConfiguration.decimal_target_types Example: ["NUMERIC", "BIGNUMERIC"].
description	STRING A description of this table.
enable_list_inference	BOOL If true, use schema inference specifically for Parquet LIST logical type. Applies to Parquet data.
enable_logical_types	BOOL If true, convert Avro logical types into their corresponding SQL types. For more information, see Logical types. Applies to Avro data.
encoding	STRING The character encoding of the data. Supported values include: UTF8 (or UTF-8), ISO_8859_1 (or ISO-8859-1). Applies to CSV data.
enum_as_string	BOOL If true, infer Parquet ENUM logical type as STRING instead of BYTES by default. Applies to Parquet data.
expiration_timestamp	TIMESTAMP The time when this table expires. If not specified, the table does not expire. Example: "2025-01-01 00:00:00 UTC".
field_delimiter	STRING The separator for fields in a CSV file. Applies to CSV data.
format	STRING The format of the external data. Supported values for CREATE EXTERNAL TABLE include: AVRO, CLOUD_BIGTABLE, CSV, DATASTORE_BACKUP, DELTA_LAKE (preview), GOOGLE_SHEETS, NEWLINE_DELIMITED_JSON (or JSON), ORC, PARQUET. Supported values for LOAD DATA include: AVRO, CSV, DELTA_LAKE (preview) NEWLINE_DELIMITED_JSON (or JSON), ORC, PARQUET. The value JSON is equivalent to NEWLINE_DELIMITED_JSON.
hive_partition_uri_prefix	STRING A common prefix for all source URIs before the partition key encoding begins. Applies only to hive-partitioned external tables. Applies to Avro, CSV, JSON, Parquet, and ORC data. Example: "gs://bucket/path".
file_set_spec_type	STRING Specifies how to interpret source URIs for load jobs and external tables. In preview. Supported values include: FILE_SYSTEM_MATCH. Expands source URIs by listing files from the object store. This is the default behavior if FileSetSpecType is not set. NEW_LINE_DELIMITED_MANIFEST. Indicates that the provided URIs are newline-delimited manifest files, with one URI per line. Wildcard URIs are not supported in the manifest files. For example, if you have a source URI of "gs://bucket/path/file" and the file_set_spec_type is FILE_SYSTEM_MATCH, then the file is used directly as a data file. If the file_set_spec_type is NEW_LINE_DELIMITED_MANIFEST, then each line in the file is interpreted as a URI that points to a data file.
ignore_unknown_values	BOOL If true, ignore extra values that are not represented in the table schema, without returning an error. Applies to CSV and JSON data.
json_extension	STRING For JSON data, indicates a particular JSON interchange format. If not specified, BigQuery reads the data as generic JSON records. Supported values include: GEOJSON. Newline-delimited GeoJSON data. For more information, see Creating an external table from a newline-delimited GeoJSON file.
max_bad_records	INT64 The maximum number of bad records to ignore when reading the data. Applies to: CSV, JSON, and Google Sheets data.
max_staleness	INTERVAL Applicable for BigLake tables and object tables. Specifies whether cached metadata is used by operations against the table, and how fresh the cached metadata must be in order for the operation to use it. To disable metadata caching, specify 0. This is the default. To enable metadata caching, specify an interval literal value between 30 minutes and 7 days. For example, specify INTERVAL 4 HOUR for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the cached metadata is older than that, the operation falls back to retrieving metadata from Cloud Storage instead.
metadata_cache_mode	STRING Applicable for BigLake tables and object tables. Specifies whether the metadata cache for the table is refreshed automatically or manually. Set to AUTOMATIC for the metadata cache to be refreshed at a system-defined interval, usually somewhere between 30 and 60 minutes. Set to MANUAL if you want to refresh the metadata cache on a schedule you determine. In this case, you can call the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the cache. You must set metadata_cache_mode if max_staleness is set to a value greater than 0.
null_marker	STRING The string that represents NULL values in a CSV file. Applies to CSV data.
object_metadata	STRING Only required when creating an object table. Set the value of this option to SIMPLE when creating an object table.
preserve_ascii_control_characters	BOOL If true, then the embedded ASCII control characters which are the first 32 characters in the ASCII table, ranging from '\x00' to '\x1F', are preserved. Applies to CSV data.
projection_fields	STRING A list of entity properties to load. Applies to Datastore data.
quote	STRING The string used to quote data sections in a CSV file. If your data contains quoted newline characters, also set the allow_quoted_newlines property to true. Applies to CSV data.
reference_file_schema_uri	STRING User provided reference file with the table schema. Applies to Parquet/ORC/AVRO data. Example: "gs://bucket/path/reference_schema_file.parquet".
require_hive_partition_filter	BOOL If true, all queries over this table require a partition filter that can be used to eliminate partitions when reading data. Applies only to hive-partitioned external tables. Applies to Avro, CSV, JSON, Parquet, and ORC data.
sheet_range	STRING Range of a Google Sheets spreadsheet to query from. Applies to Google Sheets data. Example: “sheet1!A1:B20”,
skip_leading_rows	INT64 The number of rows at the top of a file to skip when reading the data. Applies to CSV and Google Sheets data.
uris	For external tables, including object tables, that aren't Bigtable tables: ARRAY<STRING> An array of fully qualified URIs for the external data locations. Each URI can contain one asterisk (*) wildcard character, which must come after the bucket name. When you specify uris values that target multiple files, all of those files must share a compatible schema. The following examples show valid uris values: ['gs://bucket/path1/myfile.csv'] ['gs://bucket/path1/*.csv'] ['gs://bucket/path1/*', 'gs://bucket/path2/file00*'] For Bigtable tables: STRING The URI identifying the Bigtable table to use as a data source. You can only specify one Bigtable URI. Example: https://googleapis.com/bigtable/projects/project_id/instances/instance_id[/appProfiles/app_profile]/tables/table_name For more information on constructing a Bigtable URI, see Retrieving the Bigtable URI.

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.TABLE_OPTIONS	Project level	REGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.TABLE_OPTIONS	Dataset level	Dataset location

Replace the following:

• 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.

Note: You must specify the location to query the INFORMATION_SCHEMA views and the location of the query execution must match the region of the INFORMATION_SCHEMA view.

Example

Example 1:

The following example retrieves the default table expiration times for all tables in mydataset in your default project (myproject) by querying the INFORMATION_SCHEMA.TABLE_OPTIONS view.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `project_id`.dataset.INFORMATION_SCHEMA.view; for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

Note: INFORMATION_SCHEMA view names are case-sensitive.

  SELECT
    *
  FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
  WHERE
    option_name = 'expiration_timestamp';

The result is similar to the following:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Note: Tables without an expiration time are excluded from the query results.

Example 2:

The following example retrieves metadata about all tables in mydataset that contain test data. The query uses the values in the description option to find tables that contain "test" anywhere in the description. mydataset is in your default project — myproject.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `project_id`.dataset.INFORMATION_SCHEMA.view; for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

  SELECT
    *
  FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
  WHERE
    option_name = 'description'
    AND option_value LIKE '%test%';

The result is similar to the following:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+