Command-line tool reference

This document details the bq command-line tool's commands and flags. For information on using the bq command-line tool, see Using the bq command-line tool.

Global flags

You can use the following global flags with the bq command-line tool.

[DEPRECATED] bq authorization flags

The bq authorization flags are deprecated. To configure authorization for the bq command-line tool, see Authorizing Cloud SDK tools.

--application_default_credential_file

For more information, see Setting up authentication for server to server production applications. The default value is ''.

--credential_file

The filename used for storing your OAuth token. The default value is $HOME/.bigquery.v2.token.

--service_account

Use this service account email address for authorization. For example: [e-mail]. The default value is ''.

--service_account_credential_file

The file used as a credential store for service accounts. This flag must be set if you're using a service account.

--service_account_private_key_file

The file that contains the service account private key. This flag is required if the --service_account flag is specified. The default value is ''.

--service_account_private_key_password

The private key password. The password must match the password you set on the key when you create it. The default value is notasecret.

--use_gce_service_account

Specify this flag to use service account credentials instead of stored credentials when you're running on a Compute Engine instance. For more information, see: Creating and enabling service accounts for instances. The default value is false.

bq global flags

--api

The API endpoint to call. The default value is https://www.googleapis.com.

--api_versionThe API version to use. The default is v2.

--apilog

Log all API requests and responses to the file specified by this flag. You can also use stdout and stderr. If you specify the empty string (''), logs to stdout.

--bigqueryrc

The path to the bq command-line tool's configuration file. The configuration file specifies new defaults for any flags, and can be overridden by specifying the flag on the command line. If the --bigqueryrc flag is not specified, the BIGQUERYRC environment variable is used. If that is not specified, the path ~/.bigqueryrc is used. The default value is $HOME/.bigqueryrc.

--ca_certificates_file

The location of your CA certificate file. The default value is ''.

--dataset_id

The default dataset to use for requests. This flag is ignored when not applicable. You can set the value to PROJECT:DATASET or DATASET. If PROJECT is missing, the default project is used. You can override this setting by specifying the --project_id flag. The default value is ''.

--debug_mode

Show tracebacks on Python exceptions. The default value is false.

--disable_ssl_validation

Disables HTTPS certificate validation. The default value is false.

--discovery_file

The filename of the JSON file to read for discovery. The default value is ''.

--enable_gdrive

When set to true, a new OAuth token with GDrive scope is requested. When set to false, a new OAuth token without GDrive scope is requested.

--fingerprint_job_id

Specifies whether to use a job ID that is derived from a fingerprint of the job configuration. This prevents the same job from running multiple times accidentally. The default value is false.

--flagfile

When specified, flag definitions from the supplied file are inserted into the bq command-line tool. The default value is ''.

--format

Specifies the format of the command's output. Options include:

• pretty: formatted table output
• sparse: simpler table output
• prettyjson: easy-to-read JSON format
• json: maximally compact JSON
• csv: csv format with header

pretty, sparse, and prettyjson are intended to be human-readable. json and csv are for passing to another program. If none is specified, the command produces no output. If the --format flag is absent, an appropriate output format is chosen based on the command.

--headless

Specifies whether to run the bq session without user interaction. When set to true, interaction is disabled. For example, debug_mode does not break into the debugger, and the frequency of informational printing is lowered. The default value is false.

--httplib2_debuglevel=DEBUG_LEVEL

Specifies whether to show HTTP debugging information. If DEBUG_LEVEL is > 0, logs HTTP server requests and responses to stderr, in addition to error messages. If DEBUG_LEVEL is not > 0, or if the --httplib2_debuglevel flag is not used, only error messages are provided.

For example: --httplib2_debuglevel=1

Note: Multi-level debugging is not supported for this flag, so you can set DEBUG_LEVEL to any positive number.

--job_id

The unique job ID to use for the request. If not specified in a job creation request, a job ID is generated. This flag applies only to commands that create jobs: cp, extract, load, and query. For more information, see Running jobs programmatically.

--job_property

A key-value pair to include in the properties field of the job configuration. Repeat this flag to specify additional properties.

--location

A string corresponding to your region or multi-region location. The location flag is required for the cancel command and for the show command when you use the -j flag to show information about jobs. The location flag is optional for the following commands.

• query
• cp
• load
• extract
• partition
• wait
• mk when you use the -d flag to create a dataset

All other commands ignore the --location flag.

Note: The --location flag was introduced in bq version 2.0.29. To verify the version of the bq command-line tool, enter bq version.

--max_rows_per_request

An integer that specifies the maximum number of rows to return per read.

--project_id

The project ID to use for requests. The default value is ''.

--proxy_address

The name or IP address of the proxy host to use for connecting to Google Cloud. The default value is ''.

--proxy_password

The password to use when authenticating with the proxy host. The default value is ''.

--proxy_port

The port number to use to connect to the proxy host. The default value is ''.

--proxy_username

The username to use when authenticating with the proxy host. The default value is ''.

--quiet or -q

If set to true, ignore status updates while jobs are running. The default value is false.

--synchronous_mode or -sync

If set to true, wait for the command to complete before returning, and use the job completion status as the error code. If set to false, the job is created, and successful completion status is used for the error code. The default value is true.

--trace

A tracing token specified as token:TOKEN to include in API requests.

Command-specific flags

You can use the following command flags in the bq command-line tool.

bq add-iam-policy-binding

The add-iam-policy-binding command retrieves the Identity and Access Management (IAM) policy for a resource (table or view) and adds a binding to the policy, in one step.

This command is an alternative to the following three-step process:

1. Using the get-iam-policy command to retrieve the policy file (in JSON format)
2. Editing the policy file
3. Using the set-iam-policy command to update the policy with a new binding.

Usage:

bq add-iam-policy-binding --member="MEMBER" --role="ROLE" [-COMMAND_FLAGS] RESOURCE_IDENTIFIER

The add-iam-policy-binding command uses the following command-specific flags and argument.

--member

The member part of the IAM policy binding. The --member flag is required along with the --role flag. One combination of --member and --role equals one binding. See the IAM Policy reference for details on bindings.

--role

The role part of the IAM policy binding. The --role flag is required, with the --member flag. One combination of --member and --role flags equals one binding. See the IAM Policy reference for details on bindings.

-t --table/view

When specified, adds a binding to the IAM policy of a table or view. Optional. The default value is false.

RESOURCE_IDENTIFIER is the resource (table or view) whose policy is being updated.

bq cancel

The cancel command is used to cancel jobs. The cancel command has no command-specific flags.

For more information on using the cancel command, see Managing jobs.

The cancel command uses the following global flags.

--job_id

The unique job ID to use for the cancellation request. You can specify the job ID without using the --job_id flag, for example: bq cancel JOB_ID.

--synchronous_mode or --sync

When specified, wait for the command to complete before returning. If set to false, the command returns immediately. The default value is true.

bq cp

The cp command is used to copy tables. The cp command uses the following command-specific flags.

For more information on using the cp command, see Managing tables.

--append_table or -a

When specified, copy a table and append it to an existing table. The default value is false.

--destination_kms_key

The Cloud KMS key used for encryption of the destination table data.

--force or -f

When specified, if the destination table exists, overwrite it and don't prompt. The default value is false.

--no_clobber or -n

When specified, if the destination table exists, do not overwrite it. The default value is false.

bq extract

The extract command is used to export table data to Cloud Storage.

For more information on using the extract command, see Exporting table data.

Usage:

bq extract [-COMMAND_FLAGS] 'RESOURCE_IDENTIFIER' DESTINATION

The extract command uses the following command-specific flags:

--compression

The compression type to use for exported files. Possible values include GZIP (CSV and JSON), DEFLATE (Avro), SNAPPY (Avro and Parquet), and NONE. The default value is NONE.

--destination_format

The format for the exported data. Possible values include:

• CSV
• NEWLINE_DELIMITED_JSON
• AVRO
• PARQUET (Preview)

The default value is CSV.

--field_delimiter or -F

The character that indicates the boundary between columns in the output file for CSV exports. This delimiter can be any ISO-8859-1 single-byte character. Both \t and tab are allowed for tab delimiters.

--print_header or --noprint_header

To suppress printing header rows for formats that have headers, specify --noprint_header. If --print_header is specified, or if neither flag is used, header rows are included.

Example:

bq extract --compression GZIP --destination_format CSV --field_delimiter tab
    --noprintheader 'myDataset.myTable' gs://my-bucket/myFile.csv.gzip

bq get-iam-policy

The get-iam-policy command retrieves the IAM policy for a resource (table or view) and prints it to stdout. The policy is in JSON format.

For more information about the get-iam-policy command, with examples, see Introduction to table access controls.

Usage:

bq get-iam-policy [-COMMAND_FLAG] RESOURCE_IDENTIFIER

The get-iam-policy command uses the following command-specific flag.

-t --table/view

When specified, gets the IAM policy of a table or view. Optional. The default value is false.

RESOURCE_IDENTIFIER is the resource (table or view) whose policy is being updated.

bq head

The head command displays rows in a table.

For more information on using the head command, see Managing table data.

The head command uses the following command-specific flags.

--job or -j

Specify this flag with a valid job ID to read the results of a query job. The default value is false.

--max_rows or -n

An integer indicating the number of rows to print when showing table data. The default value is 100.

--selected_fields or -c

A comma-separated list indicating a subset of fields (including nested and repeated fields) to return when showing table data. If not specified, all columns are retrieved.

--start_row or -s

An integer indicating the number of rows to skip before showing table data. The default value is 0 (start at the first row).

--table or -t

Specify this flag with a table ID to read rows from a table. The default value is false.

bq insert

The insert command lets you insert rows of newline-delimited JSON formatted data using the streaming buffer. Data types are converted to match the column types of the destination table. This command is intended for testing purposes only. To stream data into BigQuery, use the insertAll API method.

For more information, see Streaming data into BigQuery.

Usage:

bq insert [COMMAND_FLAGS] TABLE FILE

The insert command uses the following command-specific flags.

--ignore_unknown_values or -i

When specified, ignore any key-value pairs that do not match the table's schema, and insert the row with the data that does match the schema. If the flag is not specified, rows with data that does not match the table's schema are not inserted.

--skip_invalid_rows or -s

When specified, attempt to insert any valid rows, even if invalid rows are present. If the flag is not specified, the command fails if any invalid rows are present.

--template_suffix or -x

When specified, treat the destination table as a base template, and insert the rows into an instance table named {destination}{templateSuffix}. BigQuery creates the instance table using the schema of the base template.

Examples:

bq insert --ignore_unknown_values myDataset.myTable /tmp/myData.json

echo '{"a":1, "b":2}' | bq insert myDataset.myTable

bq load

The load command loads data into a table.

For more information on loading data from Cloud Storage using the load command, see:

• Loading Avro data
• Loading CSV data
• Loading JSON data
• Loading ORC data
• Loading Parquet data
• Loading data from Datastore exports
• Loading data from Firestore exports

For more information on loading data from a local source using the load command, see Loading data from local files.

The load command uses the following command-specific flags.

--allow_jagged_rows

When specified, allow missing trailing optional columns in CSV data.

--allow_quoted_newlines

When specified, allow quoted newlines in CSV data.

--autodetect

When specified, enable schema auto-detection for CSV and JSON data.

--clustering_fields

A comma-separated list of up to four column names.

--destination_kms_key

The Cloud KMS key for encryption of the destination table data.

--encoding or -E

The character encoding used in the data. Possible values include:

• ISO-8859-1 (also known as Latin-1)
• UTF-8

--field_delimiter or -F

The character that indicates the boundary between columns in the data. This delimiter can be any ISO-8859-1 single-byte character. Both \t and tab are allowed for tab delimiters.

--ignore_unknown_values

When specified, for CSV and JSON files, rows with extra column values that do not match the table schema are ignored and are not loaded. Similarly, for Avro, Parquet and ORC files, fields in the file schema that do not exist in the table schema are ignored and are not loaded.

--max_bad_records

An integer that specifies the maximum number of bad records allowed before the entire job fails. The default value is 0. At most, five errors of any type are returned regardless of the --max_bad_records value. This flag applies for loading CSV, JSON, and GOOGLE_SHEETS data only.

--null_marker

An optional custom string that represents a NULL value in CSV data.

--projection_fields

If used with --source_format set to DATASTORE_BACKUP, indicates which entity properties to load from a Datastore export as a comma-separated list. Property names are case sensitive and must refer to top-level properties. The default value is ''. This flag can also be used with Firestore exports.

--quote CHARACTER

To specify a quote character to surround fields, include this flag. The CHARACTER argument can be any one-byte character. The default value is " (double quote). To specify that there is no quote character, use an empty string ("").

--replace

When specified, any existing data and schema are erased when new data is loaded. Any Cloud KMS key is also removed, unless you specify the --destination_kms_key flag. The default value is false. You can use the TRUNCATE TABLE statement to remove all rows from a table without deleting the schema.

--schema

Either the path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE,FIELD:DATA_TYPE. If you use a schema file, do not give it an extension.

--schema_update_option

When appending data to a table (in a load job or a query job), or when overwriting a table partition, specifies how to update the schema of the destination table. Possible values include:

• ALLOW_FIELD_ADDITION: Allow new fields to be added
• ALLOW_FIELD_RELAXATION: Allow relaxing REQUIRED fields to NULLABLE

Repeat this flag to specify multiple schema update options.

--skip_leading_rows

An integer that specifies the number of rows to skip at the beginning of the source file.

--source_format

The format of the source data. Possible values include:

• CSV
• NEWLINE_DELIMITED_JSON
• AVRO
• DATASTORE_BACKUP
• PARQUET
• ORC

--time_partitioning_expiration

An integer that specifies (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value. A negative number indicates no expiration.

--time_partitioning_field

The field used to determine how to create a time-based partition. If time-based partitioning is enabled without this value, the table is partitioned based on the load time.

--time_partitioning_type

Enables time-based partitioning on a table and sets the partition type. Possible values include DAY, HOUR, MONTH, or YEAR. The default partition type for time-based partitioning is DAY.

--use_avro_logical_types

: If sourceFormat is set to AVRO, indicates whether to convert logical types into their corresponding types (such as TIMESTAMP) instead of only using their raw types (such as INTEGER).

--decimal_target_types

: Determines how to convert a Decimal logical type. Equivalent to JobConfigurationLoad.decimalTargetTypes. Repeat this flag to specify multiple target types.

--parquet_enum_as_string

If sourceFormat is set to PARQUET, indicates whether to infer Parquet ENUM logical types as STRING values.

--parquet_enable_list_inference

If sourceFormat is set to PARQUET, indicates whether to use schema inference for Parquet LIST logical types.

bq ls

The ls command lists objects in a collection.

For more information on using the ls command, see:

• Managing jobs
• Listing datasets in a project
• Creating and using tables
• Listing views in a dataset
• Working with transfers
• Working with Reservations

The ls command uses the following command-specific flags.

--all or -a

When specified, show all results: jobs from all users or all datasets (including hidden ones). This flag is not needed when listing transfer configurations or transfer runs.

--capacity_commitment

When specified, lists capacity commitments. The default value is false.

--datasets or -d

When specified, list datasets. The default value is false.

--filter "FILTER"

Lists datasets that match FILTER, which consists of one or more space-separated triples in the format labels.KEY:VALUE. If more than one triple is provided, only returns datasets matching all of the triples (i.e., uses the AND logical operator, not OR).

• To filter based on dataset labels, use the keys and values you applied to your datasets. For example: --filter "labels.department:marketing labels.team:sales"

• To filter based on transfer configurations, use dataSourceIds as the key, and one of the following data sources as the value:

  • amazon_s3 - Amazon S3 data transfer
  • dcm_dt - Campaign Manager data transfer
  • google_cloud_storage - Cloud Storage data transfer
  • cross_region_copy - Dataset copy
  • dfp_dt- Google Ad Manager data transfer
  • adwords - Google Ads data transfer
  • merchant_center - Google Merchant Center data transfer
  • play - Google Play data transfer
  • doubleclick_search- Search Ads 360 data transfer
  • youtube_channel - YouTube Channel data transfer
  • youtube_content_owner - YouTube Content Owner data transfer
  • redshift - Amazon Redshift migration
  • on_premises- Teradata migration
  For example: --filter "labels.dataSourceIds:dcm_dt"

• To filter based on transfer runs, use states as the key, and one of the following transfer states as the value:

  • SUCCEEDED
  • FAILED
  • PENDING
  • RUNNING
  • CANCELLED

  For example: --filter "labels.states:FAILED"

--jobs or -j

When specified, list jobs. The default value is false. By default, you are limited to 100,000 results.

--max_creation_time

An integer that represents a timestamp in milliseconds. When specified with the -j flag, this flag lists jobs created before the timestamp.

--max_results or -n

An integer indicating the maximum number of results. The default value is 50.

--min_creation_time

An integer that represents a timestamp in milliseconds. When specified with the -j flag, this flag lists jobs created after the timestamp.

--message_type

To list transfer run log messages of a particular type, specify messageTypes:MESSAGE_TYPE. Possible values include:

• INFO
• WARNING
• ERROR

--models or -m

When specified, lists BigQuery ML models.

--page_token or -k

When specified, list items starting from this page token.

--projects or -p

When specified, show all projects. The default value is false.

--reservation

When specified, lists all reservations for a given project and location. The default value is false.

--reservation_assignment

When specified, lists all reservation assignments for a given project and location. The default value is false.

--run_attempt

Set this flag to LATEST to list only the latest runs for a transfer.

--transfer_config

When specified, lists transfer configurations. When using this flag, you must also specify --transfer_location. The default value is false.

--transfer_location

Lists transfer configurations in the specified location. You set the transfer location when the transfer is created.

--transfer_log

When specified, list transfer log messages for the specified transfer run. The default value is false.

--transfer_run

When specified, list transfer runs. The default value is false.

bq mk

The mk command creates various BigQuery resources, including datasets, tables, views, materialized views, and transfer configurations.

The mk command takes a type flag that specifies the type of resource to create and other flags that depend on the resource type.

bq mk TYPE_FLAG [ OTHER FLAGS ] [ ARGS ]

Where TYPE_FLAG is one of:

• --capacity_commitment: Purchase a capacity commitment.

• --dataset or -d: Create a dataset.

• --materialized_view: Create a materialized view.

• --reservation: Create a reservation.

• --reservation_assignment. Assign a folder, project, or organization to a reservation.

• --table or -t: Create a table.

• --transfer_config: Create a transfer configuration.

• --view: Create a view.

• --transfer_run: Create a transfer run for a time range.

In addition to command-specific flags listed below, bq mk supports the following flag:

--force or -f

Ignore errors if a resource with the same name already exists. When specified, if the resource already exists, the exit code is 0. This flag does not cause the mk command to overwrite the resource. The default value is false.

bq mk --capacity_commitment

Purchase a capacity commitment. For more information, see Working with commitments. The following flags are supported:

--location

The location of the project.

--plan

The plan type. One of: FLEX, MONTHLY, ANNUAL.

--project_id

The project ID of the project that administers the slots.

--slots

The number of slots to purchase.

bq mk --dataset

Creates a dataset. For more information, see Creating datasets. The following flags are supported:

--data_location

(Legacy) Specifies the location of the dataset. Use the --location global flag instead.

--default_kms_key

Specifies the Cloud KMS key for encrypting the table data in a dataset if no explicit key is provided during table creation or query.

--default_partition_expiration

An integer that specifies the default expiration time, in seconds, for all partitions in newly created partitioned tables in the dataset. A partition's expiration time is set to the partition's UTC date plus the integer value. If this property is set, it overrides the dataset-level default table expiration if it exists. If you supply the --time_partitioning_expiration flag when you create or update a partitioned table, the table-level partition expiration takes precedence over the dataset-level default partition expiration.

--default_table_expiration

An integer that specifies the default lifetime, in seconds, for newly created tables in a dataset. The expiration time is set to the current UTC time plus this value.

--description

The description of the dataset.

--label

A label for the dataset. The format is KEY:VALUE. Repeat this flag to specify multiple labels.

bq mk --materialized_view

Creates a materialized view. The following flags are supported:

--enable_refresh

Whether automatic refresh is enabled for a materialized view. The default when creating a materialized view is true.

--refresh_interval_ms

The time, in milliseconds, for the refresh interval of a materialized view. If not specified, the refresh interval for a materialized view that has refresh enabled is 1,800,000 milliseconds, which is 30 minutes.

For more information, see Creating and using materialized views.

bq mk --reservation

Creates a reservation with dedicated slots. For more information, see Working with reservations. The following flags are supported:

--ignore_idle_slots

If true, jobs running in this reservation only use slots allocated to the reservation. If false, jobs in this reservation can use idle slots from other reservations, or slots that are not allocated to any reservation. The default value is false. For more information, see Idle slots.

--location

The location of the project.

--project_id

The project ID of the project that owns the reservation.

--slots

The number of slots to allocate to this reservation.

bq mk --reservation_assignment

Assigns a project, folder, or organization to a reservation. For more information, see Working with assignments. The following flags are supported:

--assignee_id

The ID of the folder, organization, or project.

--assignee_type

The type of entity to assign to the reservation. One of: FOLDER, ORGANIZATION, or PROJECT.

--job_type

The type of job to assign to the reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.

--location

The location of the project.

--project_id

The project ID of the project that owns the reservation.

reservation_id

The ID of the reservation.

bq mk --table

Creates a table. For more information, see Creating and using tables. The following flags are supported:

--clustering_fields

A comma-separated list of column names used to cluster a table. When specified, the table is clustered using the supplied columns. If specified with partitioning, the table is first partitioned, and then each partition is clustered using the supplied columns.

--description

The description of the table.

--destination_kms_key

The Cloud KMS key used to encrypt the table data.

--expiration SECONDS

Specifies the lifetime for the table. If SECONDS is 0, the table doesn't expire. If you don't include the --expiration flag, BigQuery creates the table with the dataset's default table lifetime.

--external_table_definition

Specifies a table definition to be used to create an external table. The value can be either an inline table definition or a path to a file containing a JSON table definition. The format of an inline definition is schema@format=uri.

--label

A label to set on the table. The format is KEY:VALUE. Repeat this flag to specify multiple labels.

--range_partitioning

Specifies options for an integer-range partition, as a comma-separated list of the form column_name,start,end,interval where

• column_name is the column used to create the integer range partitions.
• start is the start of range partitioning, inclusive.
• end is the end of range partitioning, exclusive.
• interval is the width of each range within the partition.

For example: --range_partitioning=customer_id,0,10000,100

--require_partition_filter

When specified, this flag determines whether to require a partition filter for queries over the supplied table. This flag only applies to partitioned tables. The default value is false.

--schema

The path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE, FIELD:DATA_TYPE. The default value is ''.

--time_partitioning_expiration

An integer that specifies (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value. A negative number indicates no expiration.

--time_partitioning_field

The field used to determine how to create a time-based partition. If time-based partitioning is enabled without this value, the table is partitioned based on the load time.

--time_partitioning_type

Enables time-based partitioning on a table and sets the partition type. Possible values include DAY, HOUR, MONTH, or YEAR.

--use_avro_logical_types

If external_table_definition is set to AVRO, indicates whether to convert logical types into their corresponding types (such as TIMESTAMP) instead of only using their raw types (such as INTEGER).

--parquet_enum_as_string

If external_table_definition is set to PARQUET, indicates whether to infer Parquet ENUM logical types as STRING values.

--parquet_enable_list_inference

If external_table_definition is set to PARQUET, indicates whether to use schema inference for Parquet LIST logical types.

bq mk --transfer_config

Creates a transfer configuation. The following flags are supported:

--data_source Specifies the data source. Required when creating a transfer configuration. Possible values include:

• amazon_s3 - Amazon S3 data transfer
• dcm_dt - Campaign Manager data transfer
• google_cloud_storage - Cloud Storage data transfer
• cross_region_copy - Dataset copy
• dfp_dt- Google Ad Manager data transfer
• adwords - Google Ads data transfer
• merchant_center - Google Merchant Center data transfer
• play - Google Play data transfer
• doubleclick_search- Search Ads 360 data transfer
• youtube_channel - YouTube Channel data transfer
• youtube_content_owner - YouTube Content Owner data transfer
• redshift - Amazon Redshift migration
• on_premises- Teradata migration

Note: The redshift and on_premises values are for data migrations; before you use the bq mk --transfer_config command with these values, consult the linked documentation from the preceding list.

--display_name

The display name for the transfer configuration. The default value is ''.

--params or -p

The parameters for the transfer configuration in JSON format: {"PARAMETER":"VALUE"}. The parameters vary depending on the data source. For more information, see Introduction to BigQuery Data Transfer Service.

--refresh_window_days

An integer that specifies the refresh window for the transfer configuration in days. The default value is 0.

--target_dataset

The target dataset for the transfer configuration. The default value is ''.

For information on using the mk command with the BigQuery Data Transfer Service, see:

• Setting up an Amazon S3 transfer
• Setting up a Campaign Manager transfer
• Setting up a Cloud Storage transfer
• Setting up a Google Ad Manager transfer
• Setting up a Google Ads transfer
• Setting up a Google Merchant Center transfer (beta)
• Setting up a Google Play transfer
• Setting up a Search Ads 360 transfer (beta)
• Setting up a YouTube Channel transfer
• Setting up a YouTube Content Owner transfer
• Migrating data from Amazon Redshift
• Migrating data from Teradata

bq mk --transfer_run

Creates a data transfer run at the specified time or time range using the specified data transfer configuration.

Usage:

bq mk --transfer_run [--run_time RUN_TIME | --start_time START_TIME --end_time END_TIME] CONFIG

The following flags are supported:

--run_time

A timestamp that specifies the time to schedule the data transfer run.

--start_time

A timestamp that specifies the start time for a range of data transfer runs.

--end_time

A timestamp that specifies the end time for a range of data transfer runs.

The format for the timestamps is RFC3339 UTC "Zulu".

The CONFIG argument specifies a preexisting data transfer configuration.

Examples:

bq mk --transfer_run \
      --run_time 2021-01-20T17:00:00.00Z \
      projects/p/locations/l/transferConfigs/c

bq mk --transfer_run \
      --start_time 2020-12-19T16:39:57-08:00 \
      --end_time 2020-12-19T20:39:57-08:00 \
      projects/p/locations/l/transferConfigs/c

bq mk --view

Creates a view. For more information, see Creating views. The following flags are supported:

--description

The description of the view.

--expiration SECONDS

Specifies the lifetime for the view. If SECONDS is 0, the view doesn't expire. If you don't include the --expiration flag, BigQuery creates the view with the dataset's default table lifetime.

--label

A label to set on the view. The format is KEY:VALUE. Repeat this flag to specify multiple labels.

--use_legacy_sql

When set to false, uses a standard SQL query to create a view. The default value is true (uses legacy SQL).

--view_udf_resource

The Cloud Storage URI or the path to a local code file that is loaded and evaluated immediately as a user-defined function resource used by a view's SQL query. Repeat this flag to specify multiple files.

bq mkdef

The mkdef command creates a table definition in JSON format for data stored in Cloud Storage or Drive.

For more information on using the mkdef command, see Creating a table definition file for an external data source.

The mkdef command uses the following command-specific flags.

--autodetect

When specified, use schema auto-detection for CSV and JSON data.

--ignore_unknown_values or -i

When specified, ignore any values in a row that are not present in the schema.

--source_format

The format of the source data. Possible values include:

• AVRO
• CSV
• DATASTORE_BACKUP
• GOOGLE_SHEETS
• NEWLINE_DELIMITED_JSON
• ORC
• PARQUET

The default value is CSV.

--use_avro_logical_types

If sourceFormat is set to AVRO, indicates whether to convert logical types into their corresponding types (such as TIMESTAMP) instead of only using their raw types (such as INTEGER).

--parquet_enum_as_string

If source_format is set to PARQUET, indicates whether to infer Parquet ENUM logical types as STRING values.

--parquet_enable_list_inference

If source_format is set to PARQUET, indicates whether to use schema inference for Parquet LIST logical types.

bq partition

The partition command is used to convert time-unit-suffix tables (such as tables ending in YYYYMMDD for date partitioning) into partitioned tables.

For more information on using the partition command, see Converting date-sharded tables into ingestion-time partitioned tables.

The partition command uses the following command-specific flags.

--no_clobber or -n

When specified, do not overwrite an existing partition. The default value is false.

--time_partitioning_expiration

An integer that specifies (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value. A negative number indicates no expiration.

--time_partitioning_type

Specifies the partition type. Possible values include DAY, HOUR, MONTH, or YEAR. The expected time-unit-suffix format is YYYYMMDD for DAY, YYYYMMDDHH for HOUR, YYYYMM for MONTH, and YYYY for YEAR.

bq query

The query command creates a query job that runs the supplied SQL query.

For more information on using the query command, see Running interactive and batch queries.

The query command uses the following command-specific flags.

--allow_large_results

When specified, enables large destination table sizes for legacy SQL queries.

--append_table

When specified, append data to a destination table. The default value is false.

--batch

When specified, run the query in batch mode. The default value is false.

--clustering_fields

If specified, a comma-separated list of columns is used to cluster the destination table in a query. When specified, the table is clustered using the supplied columns. If specified with partitioning, the table is first partitioned, and then each partition is clustered using the supplied columns.

--destination_kms_key

The Cloud KMS key used to encrypt the destination table data.

--destination_schema

The path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE,FIELD:DATA_TYPE. The default value is ''.

--destination_table TABLE

When specified, the query results are saved to TABLE. Specify TABLE in the following format: PROJECT:DATASET.TABLE. If PROJECT is not specified, then the current project is assumed. If the --destination_table flag is not specified, then the query results are saved to a temporary table.

Examples:

• --destination_table myProject:myDataset.myTable
• --destination_table myDataset.myTable

--dry_run

When specified, the query is validated but not run.

--external_table_definition

The table name and schema definition used in an external table query. The schema can be a path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE,FIELD:DATA_TYPE. The format for supplying the table name and schema is: TABLE::PATH_TO_FILE or TABLE::SCHEMA@SOURCE_FORMAT=CLOUD_STORAGE_URI. Repeat this flag to query multiple tables.

--flatten_results

When specified, flatten nested and repeated fields in the results for legacy SQL queries. The default value is true.

--label

A label to apply to a query job in the form KEY:VALUE. Repeat this flag to specify multiple labels.

--max_rows or -n

An integer specifying the number of rows to return in the query results. The default value is 100.

--maximum_bytes_billed

An integer that limits the bytes billed for the query. If the query goes beyond the limit, it fails (without incurring a charge). If not specified, the bytes billed is set to the project default.

--min_completion_ratio

[Experimental] A number from 0 through 1.0 that specifies the minimum fraction of data that must be scanned before a query returns. If not set, the default server value 1.0 is used.

--parameter

Either a JSON file containing a list of query parameters, or a query parameter in the form NAME:TYPE:VALUE. An empty name creates a positional parameter. TYPE may be omitted to assume a STRING value in the form: NAME::VALUE or ::VALUE. NULL produces a null value. Repeat this flag to specify multiple parameters.

--range_partitioning

Use with the --destination_table flag. Specifies options for integer-range partitioning in the destination table. The value is a comma-separated list of the form column_name,start,end,interval, where

• column_name is the column used to create the integer range partitions.
• start is the start of range partitioning, inclusive.
• end is the end of range partitioning, exclusive.
• interval is the width of each range within the partition.

For example: --range_partitioning=customer_id,0,10000,100

--replace

If specified, overwrite the destination table with the query results. Any existing data and schema are erased. Any Cloud KMS key is also removed, unless you specify the --destination_kms_key flag. The default value is false. To remove all rows from a table without deleting the schema, use the TRUNCATE TABLE statement.

--require_cache

If specified, run the query only if results can be retrieved from the cache.

--require_partition_filter

If specified, a partition filter is required for queries over the supplied table. This flag can only be used with a partitioned table.

--rpc

If specified, use the rpc-style query API instead of the REST API jobs.insert method. The default value is false.

--schedule

Makes a query a recurring scheduled query. A schedule for how often the query should run is required. Examples:

• --schedule='every 24 hours'
• --schedule='every 3 hours'

For a description of the schedule syntax, see Formatting the schedule.

--schema_update_option

When appending data to a table (in a load job or a query job), or when overwriting a table partition, specifies how to update the schema of the destination table. Possible values include:

• ALLOW_FIELD_ADDITION: Allow new fields to be added
• ALLOW_FIELD_RELAXATION: Allow relaxing REQUIRED fields to NULLABLE

Repeat this flag to specify multiple schema update options.

--start_row or -s

An integer that specifies the first row to return in the query result. The default value is 0.

--target_dataset

When specified with --schedule, updates the target dataset for a scheduled query. The query must be DDL or DML.

--time_partitioning_expiration

Use with the --destination_table flag. An integer that specifies (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value. A negative number indicates no expiration.

--time_partitioning_field

Use with the --destination_table flag. Specifies the partitioning column for time-based partitioning. If time-based partitioning is enabled without this value, the table is partitioned based on the ingestion time.

--time_partitioning_type

Use with the --destination_table flag. Specifies the partition type for the destination table. Possible values include DAY, HOUR, MONTH, or YEAR.

--udf_resource

This flag applies only to legacy SQL queries. Specifies the Cloud Storage URI or the path to a local file containing a user-defined function resource to be used by a legacy SQL query. Repeat this flag to specify multiple files.

--use_cache

When specified, caches the query results. The default value is true.

--use_legacy_sql

When set to false, runs a standard SQL query. The default value is true (uses legacy SQL).

bq remove-iam-policy-binding

The remove-iam-policy-binding command retrieves the IAM policy for a resource (table or view) and removes a binding from the policy, in one step.

This command is an alternative to the following three-step process:

1. Using the get-iam-policy command to retrieve the policy file (in JSON format)
2. Editing the policy file
3. Using the set-iam-policy command to update the policy without the binding

Usage:

bq remove-iam-policy-binding --member="MEMBER" --role="ROLE" [-COMMAND_FLAGS] RESOURCE_IDENTIFIER

The remove-iam-policy-binding command uses the following command-specific flags and argument.

--member

The member part of the IAM policy binding. The --member flag is required along with the --role flag. One combination of --member and --role equals one binding. See the IAM Policy reference for details on bindings.

--role

The role part of the IAM policy binding. The --role flag is required, with the --member flag. One combination of --member and --role flags equals one binding. See the IAM Policy reference for details on bindings.

-t --table/view

When specified, removes a binding from the IAM policy of a table or view. Optional. The default value is false.

RESOURCE_IDENTIFIER is the resource (table or view) whose policy is being updated.

bq rm

The rm command deletes a capacity commitment, dataset, model, reservation, reservation assignment, table, transfer configuration, or view.

For more information on using the rm command, see:

• Managing datasets
• Managing tables
• Managing views
• Working with transfers
• Working with Reservations

The rm command uses the following command-specific flags.

--capacity_commitment

When specified, deletes a capacity commitment. The default value is false.

--dataset or -d

When specified, deletes a dataset. The default value is false.

--force or -f

When specified, deletes a table, view, model, or dataset without prompting. The default value is false.

--model or -m

When specified, deletes a BigQuery ML model.

--recursive or -r

When specified, deletes a dataset and any tables, table data, or models in it. The default value is false.

--reservation

When specified, deletes a reservation. The default value is false.

--reservation_assignment

When specified, deletes a reservation assignment. The default value is false.

--table or -t

When specified, deletes a table. The default value is false.

--transfer_config

When specified, deletes a transfer configuration. The default value is false.

bq set-iam-policy

The set-iam-policy command sets (or updates) the IAM policy for a resource (table or view). After setting the policy, the new policy is printed to stdout. The policy is in JSON format.

The etag field in the updated policy must match the etag value of the current policy, otherwise the update fails. This feature prevents concurrent updates.

You can obtain the current policy and etag value with the bq get-iam-policy command.

For more information about the set-iam-policy command, with examples, see Introduction to table access controls.

Usage:

bq get-iam-policy [-COMMAND_FLAG] RESOURCE_IDENTIFIER FILE_NAME

The set-iam-policy command uses the following command-specific flags and arguments.

-t --table/view

When specified, sets the IAM policy of a table or view. Optional. The default value is false.

RESOURCE_IDENTIFIER is the resource (table or view) whose policy is being updated.

FILE_NAME is the name of a file containing the policy in JSON format.

bq show

The show command displays information about an object.

For more information on using the show command, see:

• Getting information about datasets
• Creating and using tables
• Getting information about views
• Working with transfers
• Managing jobs
• Working with Reservations

The show command uses the following command-specific flags.

--assignee_id

When used with the reservation_assignment flag, specifies the ID of a folder, organization, or project. Use the --assignee_type flag to specify which type of assignee to show.

--assignee_type

When used with the reservation_assignment flag, specifies the assignee type to show. One of: FOLDER, ORGANIZATION, or PROJECT.

--reservation

If specified, shows information about a reservation. The default value is false.

--dataset or -d

When specified, displays information about a dataset. The default value is false.

--encryption_service_account

When specified, displays the service account for a user if it exists, or creates one if it doesn't exist. The default value is false.

--job or -j

If specified, show information about a job. The default value is false.

--job_type

When used with the reservation_assignment flag, specifies the type of job reservation assignment to show. One of: QUERY, PIPELINE, or ML_EXTERNAL.

--model or -m

If specified, show information about a BigQuery ML model.

--reservation

If specified, shows information about a reservation. The default value is false.

--reservation_assignment

Displays reservation assignments for a specified folder, organization, or project. Displays the target resource's explicit assignments, if any; otherwise, displays assignments inherited from parent resources. For example, a project might inherit assignments from its parent folder. When using this flag, the --job_type, --assignee_type, and --assignee_id flags apply.

--schema

When specified, displays only the table's schema. The default value is false.

--transfer_config

When specified, displays information about a transfer configuration. The default value is false.

--transfer_run

When specified, displays information about a transfer run. The default value is false.

--view

When specified, displays information about a view. The default value is false.

bq update

The update command updates a capacity commitment, dataset, model, reservation, reservation assignment, table, transfer configuration, or view.

For more information on using the update command, see:

• Updating dataset properties
• Managing tables
• Updating view properties
• Updating labels
• Working with transfers
• Working with Reservations

The update command uses the following command-specific flags.

--capacity_commitment

If specified, updates a capacity commitment. The default value is false. Use this flag with the --merge, --plan, --renewal_plan, --split, and --slots flags.

--clear_label

Removes a label using the format KEY:VALUE. Repeat this flag to remove multiple labels.

--clustering_fields

Updates a table's clustering specification. The value is a comma-separated list of column names to cluster on. To remove the clustering, set an empty value. For more information, see Modifying clustering specification.

--dataset or -d

Updates a dataset. The default value is false.

--default_kms_key

Sets the default Cloud KMS key for encrypting table data in a dataset. The default key is used if no explicit key is provided for a table creation or a query.

--default_partition_expiration

An integer that specifies the default expiration time, in seconds, for all partitions in newly created partitioned tables in the dataset. This flag has no minimum value.

A partition's expiration time is set to the partition's UTC date plus the integer value. If this property is set, it overrides the dataset-level default table expiration if it exists. If you supply the --time_partitioning_expiration flag when you create or update a partitioned table, the table-level partition expiration takes precedence over the dataset- level default partition expiration. Specify 0 to remove an existing expiration.

--default_table_expiration

An integer that updates the default lifetime, in seconds, for newly created tables in a dataset. The expiration time is set to the current UTC time plus this value. Specify 0 to remove the existing expiration.

--description

Updates the description of a dataset, table, model, or view.

--destination_reservation_id

When used with the --reservation_assignment flag, moves an existing reservation assignment to the specified reservation. The value is the ID of the destination reservation. For more information, see Move an assignment to a different reservation.

--display_name

Updates the display name for a transfer configuration. The default value is ''.

--etag ETAG

Updates the resource only if the resource has an ETag that matches the string specified in the ETAG argument.

--expiration SECONDS

To update the expiration for the table, model, or view, include this flag. Replace SECONDS with the number of seconds from the update time to the expiration time. To remove the expiration for a table, model, or view, set the SECONDS argument to 0.

--external_table_definition

Updates an external table with the specified table definition. The schema can be a path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE, FIELD:DATA_TYPE. The format for supplying the table name and schema is: TABLE::PATH_TO_FILE or TABLE::SCHEMA@SOURCE_FORMAT=CLOUD_STORAGE_URI.

--ignore_idle_slots

Use with the --reservation flag. If true, jobs running in the specified reservation only use slots allocated to that reservation. If false, jobs in the specified revervaion can use idle slots from other reservations, or slots that are not allocated to any reservation. The default value is false. For more information, see Idle slots.

--merge

When used with the --capacity_commitment flag, merges two capacity commitments. The default value is false. For more information, see Merge two commitments.

--model or -m

Updates metadata for a BigQuery ML model.

--params or -p

Updates parameters for a transfer configuration in JSON format: {"PARAMETER":"VALUE"}. The parameters vary depending on the data source. For more information, see Introduction to BigQuery Data Transfer Service.

--plan

When used with the --capacity_commitment flag, converts a capacity commitment to a longer-duration commitment plan. One of: FLEX, MONTHLY, ANNUAL. The default value is ''.

--refresh_window_days

An integer that specifies an updated refresh window (in days) for a transfer configuration.

--renewal_plan

When used with the --capacity_commitment flag, specifies the renewal plan for an existing capacity commitment. One of: FLEX, MONTHLY, ANNUAL. The default value is ''.

--reservation

If specified, updates a reservation. The default value is false.

--reservation_assignment

If specified, updates a reservation assignment. The default value is false.

--schema

The path to a local JSON schema file or a comma-separated list of column definitions in the form FIELD:DATA_TYPE,FIELD:DATA_TYPE. The default value is ''.

--set_label

A label to update in the form KEY:VALUE. Repeat this flag to update multiple labels.

--slots

When used with the --capacity_commitment and --split flags, specifies the number of slots to split from an existing capacity commitment into a new commitment. When used with the --reservation flag, updates the number of slots in a reservation.

--source

The path to a local JSON file containing a payload used to update a resource. For example, you can use this flag to specify a JSON file that contains a dataset resource with an updated access property. The file is used to overwrite the dataset's access controls.

--split

When used with the --capacity_commitment flag, splits an existing capacity commitment. The default value is false. For more information, see Split a commitment.

--table or -t

When specified, updates a table. The default value is false.

--target_dataset

When specified, updates the target dataset for a transfer configuration. The default value is ''.

--time_partitioning_expiration

An integer that updates (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value. A negative number indicates no expiration.

--time_partitioning_field

Updates the field used to determine how to create a time-based partition. If time-based partitioning is enabled without this value, the table is partitioned based on the load time.

--time_partitioning_type

Specifies the partitioning type. Possible values include DAY, HOUR, MONTH, or YEAR. You cannot change the partitioning type of an existing table.

--transfer_config

When specified, updates a transfer configuration. The default value is false.

--update_credentials

When specified, updates the transfer configuration credentials. The default value is false.

--use_legacy_sql

When set to false, updates the SQL query for a view from legacy SQL to standard SQL. The default value is true which uses legacy SQL.

--view

When specified, updates the SQL query for a view. The default value is ''.

--view_udf_resource

Updates the Cloud Storage URI or the path to a local code file that is loaded and evaluated immediately as a user-defined function resource in a view's SQL query. Repeat this flag to specify multiple files.

bq wait

The wait command waits some number of seconds for a job to finish.

The wait command uses the --job_id global flag and the following command-specific flags.

SECONDS

An integer value >= 0 that specifies the wait time (this value is not a flag — you specify the integer on the command line). If you enter 0, the command polls for job completion and returns immediately. If you do not specify an integer value, the command waits forever.

--fail_on_error

When specified, after the wait time elapses, exit with an error if the job is still running, or ended in failure. The default value is true.

--wait_for_status

When specified, wait for a particular job status before exiting. Possible values include:

• PENDING
• RUNNING
• DONE

The default value is DONE.