Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/bigquery/docs/default-configuration below:

Manage configuration settings | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

This document describes how to specify and retrieve BigQuery configuration settings at an organization or project level.

For example, to help manage jobs, you can use this service to configure default job settings. Default settings 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.

You can specify the following 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, see load_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.

  Note: If you want to set a default Cloud KMS key, you must grant the Encrypter/Decrypter role to all BigQuery service accounts that are used within the project or organization. If a service account within the project or organization doesn't have appropriate permissions, all queries run by the service account fail. For information about assigning the Encrypter/Decrypter role, see Assign the Encrypter/Decrypter role. If you mistakenly set a default Cloud KMS key without first assigning the appropriate roles, you can clear the default key by setting the value to NULL. For examples, see Configure organization settings and Configure project settings.

• 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. This timeout only applies to individual query jobs, and the child jobs of scripts. To set a timeout for script jobs, you should use the jobs.insert API method and set the jobTimeoutMs field.

  Note: The default_query_job_timeout_ms setting also applies to continuous query jobs. To override this project-level setting for an individual continuous query, assign a job timeout to the continuous query in question. Continuous queries still adhere to maximum runtimes.

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

  Note: If you want to set a default time zone, ensure any existing queries that use DATETIME literals aren't affected. This includes queries with the explicit DATETIME keyword, implicitly converted string literals passed as a parameter to time functions like DATETIME_DIFF('2022-10-01', ...), the PARSE_DATETIME() function, and more. For this reason, it is safer to only set the default_time_zone parameter on new projects.

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

• default_cloud_resource_connection_id: The default connection to use when creating tables and models. Only specify the connection's ID or name, and exclude the attached project ID and region prefixes. Using default connections can cause the permissions granted to the connection's service account to be updated, depending on the type of table or model you're creating. For more information, see the Default connection overview.

• default_sql_dialect_option: The default sql query dialect for executing query jobs using the bq command-line tool or BigQuery API. Changing this setting doesn'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_reservation_based_fairness: The option that determines how idle slots are shared. The default value is false, which means idle slots are equally distributed across all query projects. If enabled, the idle slots are shared equally across all reservations first, and then across projects within the reservation. For more information, see reservation-based fairness. This option is only supported at the project level. You can't specify it at the organization or job level.

This section describes how to specify a configuration setting.

To specify a configuration setting, 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 specify a configuration setting.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

You can configure the settings at the organization or project level by using the following SQL statements. When you specify the configuration, you must specify the region where it applies. You can only use one region for each statement.

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 or europe-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);

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, the default batch query queue timeout to 20 minutes and enable reservation based fairness.

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,
  `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);

Replace the following:

• PROJECT_ID: the ID of the project.
• REGION: the region associated with your project or organization. For example, us or europe-west6.
• KMS_KEY: a user-defined Cloud KMS key. For more information about Cloud KMS keys, see Customer-managed Cloud KMS keys.
• CONNECTION_ID: the ID of the connection to use as the default connection for tables and models.

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,
  `region-REGION.default_cloud_resource_connection_id` = NULL,
  `region-REGION.default_sql_dialect_option` = 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.

You can view the configuration settings for an organization or project by using the following information schema views:

• INFORMATION_SCHEMA.PROJECT_OPTIONS: the configurations applied to a project
• INFORMATION_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 organization
• INFORMATION_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.

To retrieve configuration settings, you need the bigquery.config.get IAM permission for the specified project.

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.

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;

There is no additional charge to use the BigQuery configuration service. For more information, see pricing.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["This service allows for the configuration of default BigQuery settings at the organization or project level, which can manage jobs and enforce consistent behavior."],["Configurable settings include default timeouts for batch and interactive queries, Cloud KMS key usage, time zone settings, storage billing models, history-based query optimization, time travel window, and handling characters in column names."],["Settings can be configured using SQL statements for organizations (using `ALTER ORGANIZATION SET OPTIONS`) and projects (using `ALTER PROJECT SET OPTIONS`), allowing for the specification of region-specific defaults."],["Configuration settings can be viewed using information schema views, such as `INFORMATION_SCHEMA.PROJECT_OPTIONS`, `INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS`, and `INFORMATION_SCHEMA.ORGANIZATION_OPTIONS`."],["Project level configurations override those of the organization, and can be further overridden at the session and job level, offering a layered approach to managing BigQuery defaults."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4