Manage default configurations
This document describes how to create and retrieve default configuration settings at an organization or project level.
To help manage jobs, you can configure default settings within BigQuery. Settings that use this service are configured at an organization or project level but can be overridden at the session or job level. You can configure default settings in combination with related organizational policies to enforce default behavior.
Pricing
There is no additional charge to use the BigQuery configuration service. For more information, see pricing.
Default configurations
You can specify the following default configuration settings:
default_batch_query_queue_timeout_ms
: The default amount of time, in milliseconds, that a batch query is queued. If unset, the default is 24 hours. The minimum value is 1 millisecond. The maximum value is 48 hours. To turn off batch query queueing, set the value to -1.default_column_name_character_map
: The default scope and handling of characters in column names. If unset, load jobs that use unsupported characters in column names fail with an error message. Some older tables might be set to replace unsupported characters in column names. For more information, seeload_option_list
.default_interactive_query_queue_timeout_ms
: The default amount of time, in milliseconds, that an interactive query is queued. If unset, the default is six hours. The minimum value is 1 millisecond. The maximum value is 48 hours. To turn off interactive query queueing, set the value to -1.default_kms_key_name
: The default Cloud Key Management Service key for encrypting table data, including temporary or anonymous tables. For more information, see Customer-managed Cloud KMS keys.default_query_job_timeout_ms
: The default time after which a query job times out, including the time the job is queued and the time spent running. The timeout period must be between five minutes and six hours.default_query_optimizer_options
: The history-based query optimizations. This option can be one of the following:'adaptive=on'
: Use history-based query optimizations.'adaptive=off'
: Don't use history-based query optimizations.NULL
(default): Use the default history-based query optimizations setting, which is equivalent to'adaptive=off'
.
default_time_zone
: The default time zone to use in time zone-dependent SQL functions, when a time zone is not specified as an argument. This configuration does not apply to time-unit column partitioned tables (which use UTC as the time zone), the Storage Transfer Service schedule transfers, or loading data with the bq command-line tool. For more information, see time zones.default_storage_billing_model
: The default storage billing model for new datasets. Set the value to PHYSICAL to use physical bytes when calculating storage charges or to LOGICAL to use logical bytes. Note that changing the default storage billing model does not affect existing datasets. For more information, see Storage Billing Models.default_max_time_travel_hours
: The default time travel window in hours for new datasets. This duration must be within the range of 48 to 168, inclusive, and must be divisible by 24. Changing the default max time travel hours does not affect existing datasets. For more information, see Time Travel.
Create default configurations
This section describes how to create a configuration.
Required permissions
To create a configuration, you need the bigquery.config.update
Identity and Access Management (IAM) permission. The predefined IAM role
roles/bigquery.admin
includes the permissions that you need to create a
configuration.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Configure default settings
You can configure the default settings at the organization or project level by using the following SQL statements. When you create the configuration, you must specify the region where it applies. You can only use one region for each statement.
Configure organization settings
To configure organization settings, use the
ALTER ORGANIZATION SET OPTIONS
DDL statement.
The following example sets the default time zone to America/Chicago
, the
default Cloud KMS key to a user-defined key, the default query timeout
to 30 minutes, the default interactive query queue timeout to 10 minutes, and
the default batch query queue timeout to 20 minutes:
ALTER ORGANIZATION SET OPTIONS ( `region-REGION.default_time_zone`= 'America/Chicago', -- Ensure all service accounts under the organization have permission to KMS_KEY `region-REGION.default_kms_key_name` = KMS_KEY, `region-REGION.default_query_job_timeout_ms` = 1800000, `region-REGION.default_interactive_query_queue_timeout_ms` = 600000, `region-REGION.default_batch_query_queue_timeout_ms` = 1200000, `region-REGION.default_storage_billing_model`= PHYSICAL, `region-REGION.default_max_time_travel_hours` = 72);
Replace the following:
- REGION: the
region
associated with your project or organization. For example,
us
oreurope-west6
. - KMS_KEY: a user-defined Cloud KMS key. For more information about Cloud KMS keys, see Customer-managed Cloud KMS keys.
The following example clears all organization-level default settings:
ALTER ORGANIZATION SET OPTIONS ( `region-REGION.default_time_zone` = NULL, `region-REGION.default_kms_key_name` = NULL, `region-REGION.default_query_job_timeout_ms` = NULL, `region-REGION.default_interactive_query_queue_timeout_ms` = NULL, `region-REGION.default_batch_query_queue_timeout_ms` = NULL, `region-REGION.default_storage_billing_model`= NULL, `region-REGION.default_max_time_travel_hours` = NULL);
Configure project settings
To configure project settings, use the
ALTER PROJECT SET OPTIONS
DDL statement.
The ALTER PROJECT SET OPTIONS
DDL statement optionally accepts the
project_id
variable. If the project_id
is not specified, it defaults to the
current project where the query runs.
The following example sets the default time zone to America/Los_Angeles
,
the default Cloud KMS key to an example key, the default query timeout
to 1 hour, the default
interactive query queue timeout to 10 minutes, and the default batch query queue
timeout to 20 minutes:
ALTER PROJECT project_id SET OPTIONS ( `region-REGION.default_time_zone` = 'America/Los_Angeles', -- Ensure all service accounts under the project have permission to KMS_KEY `region-REGION.default_kms_key_name` = KMS_KEY, `region-REGION.default_query_job_timeout_ms` = 3600000, `region-REGION.default_interactive_query_queue_timeout_ms` = 600000, `region-REGION.default_batch_query_queue_timeout_ms` = 1200000, `region-REGION.default_storage_billing_model`= PHYSICAL, `region-REGION.default_max_time_travel_hours` = 72);
The following example clears all project-level default settings. The default settings use any organization-level default settings, if they exist. Otherwise, all default settings are set to the global default.
ALTER PROJECT project_id SET OPTIONS ( `region-REGION.default_time_zone` = NULL, `region-REGION.default_kms_key_name` = NULL, `region-REGION.default_query_job_timeout_ms` = NULL, `region-REGION.default_interactive_query_queue_timeout_ms` = NULL, `region-REGION.default_batch_query_queue_timeout_ms` = NULL, `region-REGION.default_storage_billing_model`= NULL, `region-REGION.default_max_time_travel_hours` = NULL);
Project-level configurations override organization-level configurations. Project-level configurations can in turn be overridden by session-level configurations, which can be overridden by job-level configurations.
Retrieve configuration values
You can view the default configuration settings for an organization or project by viewing the information schema. There are three different views to query:
INFORMATION_SCHEMA.PROJECT_OPTIONS
: the configurations applied to a projectINFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS
: the effective configurations applied to a project. Effective configurations include all configurations set at the project level as well as all settings inherited by the project from an organizationINFORMATION_SCHEMA.ORGANIZATION_OPTIONS
: the configurations applied to an organization
It may take a few minutes for new configurations to become effective and
reflected within the INFORMATION_SCHEMA
view.
Required permissions
To retrieve configuration values, you need the bigquery.config.get
IAM permission.
Each of the following predefined IAM roles includes the
permissions that you need to get the configuration from the INFORMATION_SCHEMA
view:
roles/bigquery.jobUser
roles/bigquery.dataEditor
roles/bigquery.dataUser
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Examples
To view the configurations under an organization in the us
region, run the
following query:
SELECT * FROM region-us.INFORMATION_SCHEMA.ORGANIZATION_OPTIONS;
To view the effective configurations under your default project in the us
region, run the following query:
SELECT * FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;
To view the configurations under your default project in the us
region,
run the following query:
SELECT * FROM region-us.INFORMATION_SCHEMA.PROJECT_OPTIONS;