Manage configuration settings

BigQuery administrators and project owners can manageconfiguration settings at the organization and project levels. You can setconfigurations to enforce security, control costs, and optimize queryperformance across your entire data infrastructure. By setting default values,you can ensure consistent compliance and operational efficiency, making iteasier to manage your BigQuery environment.

Specify configuration settings

The following sections describe how to specify default configurationsettings. Default settings are configured at an organization or project levelbut can be overridden at the session or job level. To enforce default behavior,you can configure default settings in combination with relatedorganizational policies.

Required roles

To get the permission that you need to specify a configuration setting, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the bigquery.config.update permission, which is required to specify a configuration setting.

You might also be able to get this permission withcustom roles or otherpredefined roles.

For more information on IAM roles and permissions inBigQuery, seePredefined roles and permissions.

Configure organization settings

You can configure settings at the organization level by using the followingGoogleSQL statements. When you specify the configuration, you mustspecify the region where it applies. You can only use one region for eachstatement.

To configure organization settings, use theALTER ORGANIZATION SET OPTIONS DDL statement.The following example specifies several default configurations, includingthe following:

• Time zone:America/Chicago
• Cloud KMS key: a user-defined key
• Query timeout: 30 minutes (1800000 milliseconds)
• Interactive query queue timeout: 10 minutes (600000 milliseconds)
• Batch query queue timeout: 20 minutes (1200000 milliseconds)

ALTERORGANIZATIONSETOPTIONS(`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`=BILLING_MODEL,`region-REGION.default_max_time_travel_hours`=72);

Replace the following:

• REGION: theregionassociated with your project or organization—for example,us oreurope-west6.
• KMS_KEY: a user-defined Cloud KMS key. Formore information, seeCustomer-managed Cloud KMS keys.
• BILLING_MODEL: the storage model for new datasets—forexample,PHYSICAL for physical bytes.

The following example clears all organization-level default settings:

ALTERORGANIZATIONSETOPTIONS(`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,`region-REGION.default_cloud_resource_connection_id`=NULL,`region-REGION.default_sql_dialect_option`=NULL,`region-REGION.enable_reservation_based_fairness`=NULL,`region-REGION.enable_global_queries_execution`=NULL,`region-REGION.enable_global_queries_data_access`=NULL);

Configure project settings

You can configure settings at the project level by using the followingGoogleSQL statements. When you specify the configuration, you mustspecify the region where it applies. You can only use one region for eachstatement.

To configure project settings, use theALTER PROJECT SET OPTIONS DDL statement.TheALTER PROJECT SET OPTIONS DDL statement optionally accepts thePROJECT_ID variable. If thePROJECT_ID is not specified, it defaults to thecurrent project where the query runs.The following example specifies several default configurations:

• Time zone:America/Los_Angeles
• Cloud KMS key: an example key
• Query timeout: 1 hour
• Interactive query queue timeout: 10 minutes
• Batch query queue timeout: 20 minutes
• Reservation-based fairness: enabled
• Global queries: enabled for running and for accessing data

ALTERPROJECTPROJECT_IDSETOPTIONS(`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`=BILLING_MODEL,`region-REGION.default_max_time_travel_hours`=72,`region-REGION.default_cloud_resource_connection_id`=CONNECTION_ID,`region-REGION.default_sql_dialect_option`='default_google_sql',`region-REGION.enable_reservation_based_fairness`=true,`region-REGION.enable_global_queries_execution`=true,`region-REGION.enable_global_queries_data_access`=true);

Replace the following:

• PROJECT_ID: the ID of the project.
• REGION: theregionassociated with your project or organization—for example,us oreurope-west6.
• KMS_KEY: a user-defined Cloud KMS key. Formore information, seeCustomer-managed Cloud KMS keys.
• BILLING_MODEL: the storage model for new datasets—forexample,PHYSICAL for physical bytes.
• CONNECTION_ID: the ID of the connection to use asthe default connection for tables and models.

The following example clears all project-level default settings. The defaultsettings use any organization-level default settings, if they exist. Otherwise,all default settings are set to the global default.

ALTERPROJECTPROJECT_IDSETOPTIONS(`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,`region-REGION.default_cloud_resource_connection_id`=NULL,`region-REGION.default_sql_dialect_option`=NULL,`region-REGION.enable_reservation_based_fairness`=NULL,`region-REGION.enable_global_queries_execution`=NULL,`region-REGION.enable_global_queries_data_access`=NULL);

Project-level configurations override organization-level configurations.Project-level configurations can in turn be overridden bysession-level configurations, which canbe overridden byjob-level configurations.

Retrieve configuration settings

You can view the configuration settings for an organization or projectby using the followingINFORMATION_SCHEMAviews:

• INFORMATION_SCHEMA.PROJECT_OPTIONS:the configurations applied to a project.
• INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS:the effective configurations applied to a project. Effective configurationsinclude all configurations set at the project level as well as all settingsinherited by the project from an organization.
• INFORMATION_SCHEMA.ORGANIZATION_OPTIONS:the configurations applied to an organization.

It may take a few minutes for new configurations to become effective andreflected within theINFORMATION_SCHEMA view.

Required roles

To get the permission that you need to retrieve configuration settings, ask your administrator to grant you theBigQuery Job User (roles/bigquery.jobUser) IAM role on the specified project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the bigquery.config.get permission, which is required to retrieve configuration settings.

You might also be able to get this permission withcustom roles or otherpredefined roles.

For more information on IAM roles and permissions inBigQuery, seePredefined roles and permissions.

Examples

To view the configurations under an organization in theus region, run thefollowing query:

SELECT*FROMregion-us.INFORMATION_SCHEMA.ORGANIZATION_OPTIONS;

To view the effective configurations under your default project in theusregion, run the following query:

SELECT*FROMregion-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;

To view the configurations under your default project in theus region,run the following query:

SELECT*FROMregion-us.INFORMATION_SCHEMA.PROJECT_OPTIONS;

Configuration settings

The following sections describe the configuration settings that you can specify.

Query and job execution settings

Use the following settings to control how queries are executed, timed, and queued.

• default_batch_query_queue_timeout_ms: The default amount of time, inmilliseconds, that a batch query isqueued. If unset, the default is 24hours. The minimum value is 1 millisecond. The maximum value is 48 hours.To turn off batch query queueing, set the value to-1.
• default_interactive_query_queue_timeout_ms: The default amount of time,in milliseconds, that an interactive query isqueued. If unset, the defaultis six hours. The minimum value is 1 millisecond. The maximum value is 48hours. To turn off interactive query queueing, set the value to-1.

• default_query_job_timeout_ms: The default time after which a query jobtimes out, including the time the job is queued and the time spent running.The timeout period must be between 5 minutes and 48 hours. This timeoutonly applies to individual query jobs and the child jobs of scripts. To seta timeout for script jobs, you should use thejobs.insertAPI method and set thejobTimeoutMs field.

  Note: Thedefault_query_job_timeout_ms setting also applies tocontinuous query jobs. Tooverride this project-level setting for an individual continuous query,assign ajob timeoutto the continuous query in question. Continuous queries still adhere tomaximum runtimes.

• enable_reservation_based_fairness: The option that determines how idle slotsare shared. The default value is false, which means idle slots areequally distributed across all query projects. If enabled, the idle slotsare shared equally across all reservations first, and then across projectswithin the reservation. For more information, seereservation-based fairness. This option isonly supported at the project level. You can't specify it at the organization or joblevel.

• default_time_zone: The default time zone to use in time zone-dependentGoogleSQL functions, when a time zone is not specified as anargument. This configuration does not apply totime-unit column partitioned tables(which use UTC as the time zone), theStorage Transfer Service schedule transfers,orloading data with the bq command-line tool.For more information, seetime zones.

  Note: If you want to set a default time zone, ensure any existing queriesthat useDATETIME literals aren't affected. This includes queries with theexplicitDATETIME keyword, implicitly converted string literals passed as aparameter to time functions likeDATETIME_DIFF('2022-10-01', ...), thePARSE_DATETIME() function, and more. For this reason, it is safer to onlyset thedefault_time_zone parameter on new projects.

• 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 optimizationssetting, which is equivalent to'adaptive=on'.

• default_sql_dialect_option: The default SQL query dialect for executingquery jobs using the bq command-line tool or BigQuery API. Changing this settingdoesn't affect the default dialect in the console. This option can be one of the following:

  • 'default_legacy_sql' (default): Use legacy SQL if the query dialect isn't specified at the job level.
  • 'default_google_sql': Use GoogleSQL if the query dialect isn't specified at the job level.
  • 'only_google_sql': Use GoogleSQL if the query dialect isn't specified at the job level. Reject jobs with query dialect set to legacy SQL.
  • NULL: Use the default query dialect setting, which is equivalent to'default_legacy_sql'.

• enable_global_queries_execution: The option that determines ifglobal queries can be run. The default value isFALSE, which means that global queries are not enabled.

• enable_global_queries_data_access: The option that determines ifglobal queries can access data stored in the region. The default value isFALSE, which means that global queries can't copy data from this region regardless of the project in which they run.

Data management settings

Use the following settings to define rules for data creation, security, and lifecycle.

• default_column_name_character_map: The default scope and handling ofcharacters in column names. If unset, load jobs that use unsupportedcharacters in column names fail with an error message. Some oldertables might be set to replace unsupported characters in column names.For more information, seeload_option_list.

• default_kms_key_name: The default Cloud Key Management Service key for encrypting tabledata, including temporary or anonymous tables. For more information, seeCustomer-managed Cloud KMS keys.

  Note: to set a default Cloud KMS key, you must grant theEncrypter/Decrypter role to all BigQuery service accounts thatare used within the project or organization. If a service account within theproject or organization doesn't have appropriate permissions, all queriesrun by the service account fail. For information about assigning theEncrypter/Decrypter role, seeAssign the Encrypter/Decrypter role.If you set a default Cloud KMS key without first assigning theappropriate roles, you can clear the default key by setting the value toNULL. For examples, seeConfigure organization settings andConfigure project settings.

• default_max_time_travel_hours: The default time travel window in hours for newdatasets. 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, seeTime Travel and data retention.

Cost and resource settings

Use the following settings to determine how resources are billed and connected.

• default_storage_billing_model: The default storage billing model for newdatasets. Set the value toPHYSICAL to use physical bytes when calculating storagecharges or toLOGICAL to use logical bytes.Note that changing the default storage billing model does not affect existing datasets.For more information, seeStorage billing models.
• default_cloud_resource_connection_id: The default connection to use whencreating tables and models. Only specify the connection's ID or name, andexclude the attached project ID and region prefixes. Usingdefault connections can cause the permissions granted to the connection'sservice account to be updated, depending on the type of table or model you'recreating. For more information, see theDefault connectionoverview.

Pricing

There is no additional charge to use the BigQuery configurationservice. For more information, seePricing.