bq command-line tool reference

This document describes the syntax, commands, flags, and arguments for bq, the Python-based command-line tool for BigQuery.

For a tutorial on using the bq command-line tool, see Load and query data with the bq tool.

Ways to use the bq command-line tool

You can enter bq command-line tool commands in Cloud Shell either from the Google Cloud console or from a local installation of the Google Cloud CLI.

• To use the bq command-line tool from the Google Cloud console, activate Cloud Shell:

  Activate Cloud Shell

• To use the bq command-line tool locally, install and configure the gcloud CLI. To keep your installation current, see Manage an installation in the gcloud CLI documentation.

Command format

The bq command-line tool uses the following format:

bq COMMAND [FLAGS] [ARGUMENTS]

Some flags can be used with multiple bq command-line tool commands; these flags are described in the Global flags section.

Other flags are command-specific; they can only be used with a particular bq command-line tool command. The command-specific flags are described in the command sections.

Specifying values for flags

When you specify a value for a flag, the equals sign (=) is optional. For example, the following two commands are equivalent:

bq ls --format prettyjson myDataset
bq ls --format=prettyjson myDataset

This document uses the equals sign for clarity.

Some bq command-line tool flags are boolean; you can set the flag's value to either true or false. The bq command-line tool accepts the following formats for setting boolean flags.

This document uses the --FLAGNAME=VALUE format for boolean flags.

All boolean flags are optional; if a boolean flag is not present, then BigQuery uses the flag's default value.

Specifying BigQuery resources in arguments

The format for specifying a resource depends on the context; in some cases the separator between the project and dataset is a colon (:) and in some cases, it is a period (.). The following table describes how to specify a BigQuery table in different contexts.

If you don't specify a project, then BigQuery uses the current project. For example, if the current project is myProject, then BigQuery interprets myDataset.myTable as myProject:myDataset.myTable (or myProject.myDataset.myTable).

Some resource identifiers must be quoted using back ticks (`). If your resource identifier begins with a letter or underscore character, and contains only characters that are letters, numbers, and underscores, then you don't need to quote it. However, if your resource identifier contains other types of characters, or reserved keywords, you need to surround the identifier (or the part of the identifier with the special characters or reserved keywords) with back ticks. For more information, see Identifiers.

How to run commands

Place any global flags before the bq command, and then include command-specific flags. You can include multiple global or command-specific flags. For example:

bq --location=us mk --reservation --project_id=project reservation_name

You can specify command arguments in the following ways:

• --FLAG ARGUMENT (as shown in the previous examples)
• --FLAG=ARGUMENT
• --FLAG='ARGUMENT'
• --FLAG="ARGUMENT"
• --FLAG 'ARGUMENT'
• --FLAG "ARGUMENT"

Replace the following:

• FLAG: a global or command-specific flag
• ARGUMENT: the flag's argument

Some commands require the use of quotes around arguments. If quotes are required, either single or double quotes are acceptable. Arguments that require quotes are typically values that contain spaces, commas, or other special characters. If your argument contains a BigQuery resource, be sure to follow the rules for specifying resource names in commands.

This example shows how to run a GoogleSQL query on the command line:

bq query --nouse_legacy_sql \
'SELECT
   COUNT(*)
 FROM
   `bigquery-public-data`.samples.shakespeare'

Flags with boolean values can be specified without an argument. If you specify true or false, then you must use the format FLAG=ARGUMENT.

For example, this command specifies false for the boolean flag --use_legacy_sql by placing no at the front of the flag:

bq query --nouse_legacy_sql \
'SELECT
   COUNT(*)
 FROM
   `bigquery-public-data`.samples.shakespeare'

Alternatively, to specify false as the flag's argument, you can enter the following:

bq query --use_legacy_sql=false \
'SELECT
   COUNT(*)
 FROM
   `bigquery-public-data`.samples.shakespeare'

Run commands in a script

You can run the bq command-line tool in a script, as you would run a Google Cloud CLI command. The following is an example of gcloud and bq commands in a bash script:

#!/bin/bash
gcloud config set project myProject
bq query --use_legacy_sql=false --destination_table=myDataset.myTable \
'SELECT
   word,
   SUM(word_count) AS count
 FROM
   `bigquery-public-data`.samples.shakespeare
 WHERE
   word LIKE "%raisin%"
 GROUP BY
   word'

Use a service account

You can use a service account to make authorized API calls or run query jobs on your behalf. To use a service account in the bq command-line tool, authorize access to Google Cloud from the service account. For more information, see gcloud auth activate-service-account.

To start running bq commands using service account impersonation, run the following command:

gcloud config set auth/impersonate_service_account SERVICE_ACCOUNT_NAME

Replace SERVICE_ACCOUNT_NAME with your service account name.

bq commands that you run now use the service account credentials.

To stop running bq commands from a service account, run the following command:

gcloud config unset auth/impersonate_service_account

Set default values for command-line flags

You can set default values for command-line flags by including them in the bq command-line tool's configuration file, .bigqueryrc. Before you configure your default options, you must first create a .bigqueryrc file. You can use your preferred text editor to create the file. After you create the .bigqueryrc file, you can specify the path to the file using the --bigqueryrc global flag.

If the --bigqueryrc flag is not specified, then the BIGQUERYRC environment variable is used. If that is not specified, then the path ~/.bigqueryrc is used. The default path is $HOME/.bigqueryrc.

How to add flags to .bigqueryrc

To add default values for command-line flags to .bigqueryrc:

• Place global flags at the top of the file without a header.
• For command-specific flags, enter the command name (in brackets) and add the command-specific flags (one per line) after the command name.

For example:

--apilog=stdout
--format=prettyjson
--location=US

[query]
--use_legacy_sql=false
--max_rows=100
--maximum_bytes_billed=10000000

[load]
--destination_kms_key=projects/myproject/locations/mylocation/keyRings/myRing/cryptoKeys/myKey

The preceding example sets default values for the following flags:

• The global flag --apilog is set to stdout to print debugging output to the Google Cloud console.
• The global flag --format is set to prettyjson to display command output in a human-readable JSON format.
• The global flag --location is set to the US multi-region location.

• The query command-specific flag --use_legacy_sql is set to false to make GoogleSQL the default query syntax.

• The query command-specific flag --max_rows is set to 100 to control the number of rows in the query output.

• The query command-specific flag --maximum_bytes_billed is set to 10,000,000 bytes (10 MB) to fail queries that read more than 10 MB of data.

• The load command-specific flag --destination_kms_key is set to projects/myproject/locations/mylocation/keyRings/myRing/cryptoKeys/myKey.

CLI help

You can get help with the bq command-line tool by running the following commands:

Troubleshooting CLI commands

To log requests sent and received:

Add the --apilog=PATH_TO_FILE flag to save a log of operations to a local file. Replace PATH_TO_FILE with the location where you want to save the log. The bq command-line tool works by making standard REST-based API calls, which can be useful to see when troubleshooting. It's also useful to attach this log when you're reporting issues to Cloud Customer Care.

Using - or stdout instead of a path prints the log to the Google Cloud console. Setting --apilog to stderr outputs to the standard error file. To log more requests, use the --httplib2_debuglevel=LOG_LEVEL flag. A higher LOG_LEVEL logs more information about the HTTP requests.

To troubleshoot errors:

Enter the --format=prettyjson flag when you get a job's status or when you view detailed information about resources such as tables and datasets. Using this flag outputs the response in JSON format, including the reason property. You can use the reason property to find error messages.

For more information about errors when you run a command, use the --debug_mode flag.

Global flags

You can use the following flags with any bq command, where applicable:

--api=ENDPOINT

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

--api_version=VERSION

Specifies the API version to use. The default is v2.

--apilog=FILE

Logs all API requests and responses to the file specified by FILE. Possible values are the following:

• the path to a file - logs to the specified file
• stdout - logs to standard output
• stderr - logs to standard error
• false - API requests and responses are not logged (default)

--use_google_auth={true|false}

If set to true, enables authentication using Google Auth libraries. The default value is true.

--bigqueryrc=PATH

Specifies the path to the bq command-line tool configuration file. If you don't specify the --bigqueryrc flag, then the command uses the BIGQUERYRC environment variable. If the environment variable is not set, then $HOME/.bigqueryrc is used. If that file does not exist, then ~/.bigqueryrc is used. For more information, see Setting default values for command-line flags.

--ca_certificates_file=PATH

Specifies the location of your Certificate Authority Service (CA) file.

--dataset_id=DATASET_ID

Specifies the default dataset to use with the command. This flag is ignored when not applicable. You can specify the DATASET_ID argument using the format PROJECT:DATASET or DATASET. If the PROJECT part is missing, then the default project is used. You can override the default project setting by specifying the --project_id flag.

--debug_mode={true|false}

If set to true, shows tracebacks on Python exceptions. The default value is false.

--disable_ssl_validation={true|false}

If set to true, enables HTTPS certificate validation. The default value is false.

--discovery_file=PATH

Specifies the JSON file to read for discovery.

--enable_gdrive={true|false}

If set to false, requests a new OAuth token without Google Drive scope. The default value is true; requests a new OAuth token with Drive scope. To set this flag to false when authenticated using a user account, the --use_google_auth flag must be set to false.

--fingerprint_job_id={true|false}

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

--format=FORMAT

Specifies the format of the command's output. Use one of the following values:

• 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 intended to be used by another program. If none is specified, then the command produces no output. If the --format flag is absent, then an appropriate output format is chosen based on the command.

--headless={true|false}

To run the bq session without user interaction, set to true. 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 greater than 0, then the command 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, then only error messages are provided.

For example:

--httplib2_debuglevel=1

--job_id=JOB_ID

Specifies a job identifier for a new job. This flag applies only to commands that create jobs: cp, extract, load, and query. If you don't use the --job_id flag, then the commands generate a unique job identifier. For more information, see Running jobs programmatically.

--job_property=KEY:VALUE

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

--location=LOCATION

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

• query
• cp
• load
• extract
• partition
• update
• wait
• mk when you use the --dataset, --reservation, --capacity_commitment, or --reservation_assignment flags
• ls when you use the --reservation, --capacity_commitment, or --reservation_assignment flags

All other commands ignore the --location flag.

--max_rows_per_request=MAX_ROWS

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

--project_id=PROJECT

Specifies the project to use for commands.

--proxy_address=PROXY

Specifies the name or IP address of the proxy host to use for connecting to Google Cloud.

--proxy_password=PASSWORD

Specifies the password to use when authenticating with the proxy host.

--proxy_port=PORT

Specifies the port number to use to connect to the proxy host.

--proxy_username=USERNAME

Specifies the username to use when authenticating with the proxy host.

--quiet={true|false} or -q={true|false}

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

--synchronous_mode={true|false} or -sync={true|false}

To create the job and immediately return, with a successful completion status as the error code, set to false. If set to true, then the command waits for the job to complete before returning, and returns the job completion status as the error code. The default value is true.

--trace=token:TOKEN

Specifies a tracing token to include in API requests.

--use_regional_endpoints={true|false}

In preview. To connect to a regional endpoint, set the --use_regional_endpoints flag to true and the --location flag to the region you want to connect to. The default value is false.

Commands

The following sections describe the bq command-line tool commands, along with their command-specific flags and arguments.

bq add-iam-policy-binding

Use the bq add-iam-policy-binding command to retrieve the Identity and Access Management (IAM) policy for a table or view and add a binding to the policy, in one step.

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

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

Synopsis

bq add-iam-policy-binding [FLAGS] --member=MEMBER_TYPE:MEMBER --role=ROLE
  [--table] RESOURCE

Example

bq add-iam-policy-binding --member=user:[email protected] \
  --role=roles/bigquery.dataViewer myDataset.myTable

Flags and arguments

The bq add-iam-policy-binding command uses the following flags and arguments:

--member=MEMBER_TYPE:MEMBER

Required. Use the --member flag to specify the member part of the IAM policy binding. The --member flag is required along with the --role flag. One combination of --member and --role flags equals one binding.

The MEMBER_TYPE value specifies the type of member in the IAM policy binding. Use one of the following values:

• user
• serviceAccount
• group
• domain

The MEMBER value specifies the email address or domain of the member in the IAM policy binding.

--role=ROLE

Required. Specifies the role part of the IAM policy binding. The --role flag is required along with the --member flag. One combination of --member and --role flags equals one binding.

--table={true|false}

To return an error if the RESOURCE argument is not a table or view identifier, set the --table flag to true. The default value is false. This flag is supported for consistency with other commands.

RESOURCE

The table or view whose policy you want to add to.

For more information, see the IAM policy reference.

bq cancel

Use the bq cancel command to cancel BigQuery jobs.

Synopsis

bq [--synchronous_mode=false] cancel JOB_ID

Examples

bq cancel bqjob_12345

bq --synchronous_mode=false cancel bqjob_12345

Flags and arguments

The bq cancel command uses the following flags and arguments:

--synchronous_mode=false

If you don't want to wait for the bq cancel command to complete ,set the global --synchronous_mode flag to false. The default is true.

JOB_ID

The job you want to cancel.

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

bq cp

Use the bq cp command for the following tasks:

• Create a copy of a table, table clone, or table snapshot.
• Create a table clone.
• Create a table snapshot.

Synopsis

bq cp [FLAGS] SOURCE_TABLE DESTINATION_TABLE

Example

bq cp myDataset.myTable myDataset.myTableCopy

Flags and arguments

The bq cp command uses the following flags and arguments:

--append_table={true|false} or -a={true|false}

To append a table to an existing table, set to true. The default value is false.

You can't use the flag settings --append_table=true and --clone=true at the same time.

--clone={true|false}

To create a table clone, set to true. The base table can be a standard table, a table clone, or a table snapshot. The destination table is a table clone. The default is false; if neither --clone=true nor --snapshot=true is specified, then the destination table is the same type of table as the base table. Requires the --no_clobber flag.

You can't use the flag settings --append_table=true and --clone=true at the same time.

--destination_kms_key=KEY

Specifies a Cloud KMS key resource ID for encrypting the destination table data.

For example:

--destination_kms_key=projects/myProject/locations/global/keyRings/myKeyRing/cryptoKeys/myKey

--expiration=SECONDS

The number of seconds until a table snapshot expires. If not included, the table snapshot expiration is set to the default expiration of the dataset containing the new table snapshot. Use with the --snapshot flag.

--force={true|false} or -f={true|false}

To overwrite the destination table, if it exists, without prompting, set to true. The default value is false; if the destination table exists, then the command prompts for confirmation before overwriting.

--no_clobber={true|false} or -n={true|false}

To disallow overwriting the destination table, if it exists, set to true. The default value is false; if the destination table exists, then it is overwritten.

--restore={true|false}

This flag is being deprecated. To create a writeable table from a table snapshot, use the bq cp command or the bq cp --clone command.

--snapshot={true|false}

To create a table snapshot of the table that's specified in the SOURCE_TABLE argument, set to true. The base table can be a standard table, a table clone, or another table snapshot. The default is false; if neither --clone=true nor --snapshot=true is specified, then the destination table is the same type of table as the base table. Requires the --no_clobber flag.

SOURCE_TABLE

The table that you want to copy.

DESTINATION_TABLE

The table that you want to copy to.

For more information about using the cp command, see the following:

• Copy a table
• Create table clones
• Create table snapshots
• Restore table snapshots

bq extract

Use the bq extract command to export table data to Cloud Storage.

Synopsis

bq extract [FLAGS] RESOURCE DESTINATION

Examples

bq extract --compression=GZIP --destination_format=CSV --field_delimiter=tab \
    --print_header=false myDataset.myTable gs://my-bucket/myFile.csv.gzip

bq extract --destination_format=CSV --field_delimiter='|' myDataset.myTable \
  gs://myBucket/myFile.csv

Flags and arguments

The bq extract command uses the following flags and arguments:

--compression=COMPRESSION_TYPE

Specifies the type of compression to use for exported files. Possible values are the following:

• GZIP
• DEFLATE
• SNAPPY
• NONE

The default value is NONE.

For information about which formats are supported for each compression type, see Export formats and compression types.

--destination_format=FORMAT

Specifies the format for the exported data. Possible values are the following:

• CSV
• NEWLINE_DELIMITED_JSON
• AVRO
• PARQUET

The default value is CSV.

--field_delimiter=DELIMITER

For CSV exports, specifies the character that marks the boundary between columns in the output file. The delimiter can be any ISO-8859-1 single-byte character. You can use \t or tab to specify tab delimiters.

--print_header={true|false}

To suppress printing header rows for formats that have headers, set to false. The default is true; header rows are included.

RESOURCE

The table that you are exporting from.

DESTINATION

The storage location that receives the exported data.

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

bq get-iam-policy

Use the bq get-iam-policy command to retrieve the IAM policy for a resource and print it to stdout. The resource can be a table, a view, or a slot reservation. The policy is in JSON format.

Synopsis

bq get-iam-policy [FLAGS] RESOURCE

Examples

bq get-iam-policy myDataset.myTable

bq get-iam-policy --reservation myReservation

Flags and arguments

The bq get-iam-policy command uses the following flags and arguments:

--table={true|false} or --t={true|false}

To return an error if RESOURCE is not a table or view identifier, set the --table flag to true. The default value is false. This flag is supported for consistency with other commands.

--reservation={true|false}

To get the IAM policy of a reservation, set to true (Preview). The default value is false. When this flag is used, RESOURCE is treated as a reservation identifier. The reservation can have optional project and location prefixes: myProject:myLocation.myReservation.

RESOURCE

The table or view whose policy you want to get.

For more information about the bq get-iam-policy command, see Control access to resources with IAM.

bq head

Use the bq head command to display the specified rows and columns of a table. By default, it displays all columns of the first 100 rows.

Synopsis

bq head [FLAGS] [TABLE]

Example

bq head --max_rows=10 --start_row=50 --selected_fields=field1,field3 \
  myDataset.myTable

Flags and arguments

The bq head command uses the following flags and arguments:

--job=JOB or -j=JOB

To read the results of a query job, specify this flag with a valid job ID.

--max_rows=MAX or -n=MAX

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

--selected_fields=COLUMN_NAMES or -c=COLUMN_NAMES

A comma-separated list that specifies a subset of fields (including nested and repeated fields) to return when showing table data. If this flag is not specified, then all columns are returned.

--start_row=START_ROW or -s=START_ROW

An integer that specifies the number of rows to skip before showing table data. The default value is 0; the table data starts at the first row.

--table={true|false} or -t={true|false}

To return an error if the command argument is not a table or view, set to true. The default value is false. This flag is supported for consistency with other commands.

TABLE

The table whose data you want to retrieve.

For more information about using the bq head command, see