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:
1234567890@developer.gserviceaccount.com
. 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_version
The 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
andstderr
. If you specify the empty string (''
), logs tostdout
. --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, theBIGQUERYRC
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
orDATASET
. IfPROJECT
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 tofalse
, 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 outputsparse
: simpler table outputprettyjson
: easy-to-read JSON formatjson
: maximally compact JSONcsv
: csv format with header
pretty
,sparse
, andprettyjson
are intended to be human-readable.json
andcsv
are for passing to another program. Ifnone
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 totrue
, interaction is disabled. For example,debug_mode
does not break into the debugger, and the frequency of informational printing is lowered. The default value isfalse
.--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. IfDEBUG_LEVEL
is not > 0, or if the--httplib2_debuglevel
flag is not used, only error messages are provided.For example:
--httplib2_debuglevel=1
--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
, andquery
. 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 theshow
command when you use the-j
flag to show information about jobs. The location flag is optional for the following commands.All other commands ignore the
--location
flag.--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 isfalse
.--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 tofalse
, the job is created, and successful completion status is used for the error code. The default value istrue
.--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:
- Using the
get-iam-policy
command to retrieve the policy file (in JSON format) - Editing the policy file
- 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 istrue
.
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), andNONE
. The default value isNONE
. --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
andtab
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
andtab
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 toDATASTORE_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 isfalse
. You can use theTRUNCATE 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 addedALLOW_FIELD_RELAXATION
: Allow relaxingREQUIRED
fields toNULLABLE
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
, orYEAR
. The default partition type for time-based partitioning isDAY
.
--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 toPARQUET
, indicates whether to infer ParquetENUM
logical types asSTRING
values. --parquet_enable_list_inference
- If
sourceFormat
is set toPARQUET
, indicates whether to use schema inference for ParquetLIST
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 formatlabels.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 transferdcm_dt
- Campaign Manager data transfergoogle_cloud_storage
- Cloud Storage data transfercross_region_copy
- Dataset copydfp_dt
- Google Ad Manager data transferadwords
- Google Ads data transfermerchant_center
- Google Merchant Center data transferplay
- Google Play data transferdoubleclick_search
- Search Ads 360 data transferyoutube_channel
- YouTube Channel data transferyoutube_content_owner
- YouTube Content Owner data transferredshift
- Amazon Redshift migrationon_premises
- Teradata migration
--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 isfalse
.--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 isfalse
.
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. Iffalse
, jobs in this reservation can use idle slots from other reservations, or slots that are not allocated to any reservation. The default value isfalse
. 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
, orPROJECT
. --job_type
- The type of job to assign to the reservation. One of:
QUERY
,PIPELINE
, orML_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
is0
, 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
wherecolumn_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
, orYEAR
.--use_avro_logical_types
If
external_table_definition
is set toAVRO
, indicates whether to convert logical types into their corresponding types (such asTIMESTAMP
) instead of only using their raw types (such asINTEGER
).--parquet_enum_as_string
If
external_table_definition
is set toPARQUET
, indicates whether to infer ParquetENUM
logical types asSTRING
values.--parquet_enable_list_inference
If
external_table_definition
is set toPARQUET
, indicates whether to use schema inference for ParquetLIST
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 transferdcm_dt
- Campaign Manager data transfergoogle_cloud_storage
- Cloud Storage data transfercross_region_copy
- Dataset copydfp_dt
- Google Ad Manager data transferadwords
- Google Ads data transfermerchant_center
- Google Merchant Center data transferplay
- Google Play data transferdoubleclick_search
- Search Ads 360 data transferyoutube_channel
- YouTube Channel data transferyoutube_content_owner
- YouTube Content Owner data transferredshift
- Amazon Redshift migrationon_premises
- Teradata migration
--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
is0
, 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 istrue
(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 toAVRO
, indicates whether to convert logical types into their corresponding types (such asTIMESTAMP
) instead of only using their raw types (such asINTEGER
).--parquet_enum_as_string
If
source_format
is set toPARQUET
, indicates whether to infer ParquetENUM
logical types asSTRING
values.--parquet_enable_list_inference
If
source_format
is set toPARQUET
, indicates whether to use schema inference for ParquetLIST
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
, orYEAR
. The expected time-unit-suffix format is YYYYMMDD forDAY
, YYYYMMDDHH forHOUR
, YYYYMM forMONTH
, and YYYY forYEAR
.
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
. SpecifyTABLE
in the following format:PROJECT
:DATASET
.TABLE
. IfPROJECT
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
orTABLE::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 aSTRING
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 formcolumn_name,start,end,interval
, wherecolumn_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 isfalse
. To remove all rows from a table without deleting the schema, use theTRUNCATE 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 isfalse
.--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 addedALLOW_FIELD_RELAXATION
: Allow relaxingREQUIRED
fields toNULLABLE
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 includeDAY
,HOUR
,MONTH
, orYEAR
.--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 istrue
(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:
- Using the
get-iam-policy
command to retrieve the policy file (in JSON format) - Editing the policy file
- 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:
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
, orPROJECT
. --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
, orML_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. Specify0
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 theSECONDS
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
orTABLE::SCHEMA@SOURCE_FORMAT=CLOUD_STORAGE_URI
.--ignore_idle_slots
Use with the
--reservation
flag. Iftrue
, jobs running in the specified reservation only use slots allocated to that reservation. Iffalse
, jobs in the specified revervaion can use idle slots from other reservations, or slots that are not allocated to any reservation. The default value isfalse
. For more information, see Idle slots.--merge
When used with the
--capacity_commitment
flag, merges two capacity commitments. The default value isfalse
. 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 isfalse
. 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
, orYEAR
. 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 istrue
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
.