Using the bq Command-Line Tool

bq is a python-based, command-line tool for BigQuery. This page contains general information on using the bq command-line tool.

For a complete reference of all bq commands and flags, see bq Command-Line Tool Reference.

Installation

To download and install the Google Cloud SDK on your local machine, follow the steps in the Cloud SDK documentation.

Alternatively, you can use a pre-installed version of the Cloud SDK in Google Cloud Shell.

General usage

Flag positioning

bq supports two kinds of flags — global flags and command flags. They should be used in the order shown here:

    bq --global_flag [ARGUMENT] bq_command --command-specific_flag [ARGUMENT]

Global flags (or common flags) can be used in all commands.
Command-specific flags apply to a specific command.

Separate multiple global or command-specific flags using a space. For example:

    bq --global_flag [ARGUMENT] --global_flag [ARGUMENT] bq_command --command-specific_flag [ARGUMENT] --command-specific_flag [ARGUMENT]

You can specify command arguments in one of the following ways:

--flag=[ARGUMENT]
--flag='[ARGUMENT]'
--flag="[ARGUMENT]"
--flag [ARGUMENT]
--flag '[ARGUMENT]'
--flag "[ARGUMENT]"

Each of these methods is used throughout the BigQuery documentation.

Some commands require the use of single or double quotes around arguments. This is often true when the argument contains spaces, commas, or other special characters. For example:

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 you must specify =[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 would enter:

bq query --use_legacy_sql=false 'select count(*) from `bigquery-public-data.samples.shakespeare`'

For a list of available global and command-specific flags, see bq Command-Line Tool Reference.

Getting help

You can enter the following commands to get help for the bq command-line tool:

For the installed version of bq, enter bq version.
For a full list of commands, enter bq help.
For a list of global flags, enter bq --help.
For help on a specific command, enter bq help [COMMAND].
For help on a specific command plus a list of global flags, enter bq [COMMAND] --help.

Debugging

You can enter the following commands to debug bq:

To see requests sent and received

Add the --apilog=[PATH_TO_FILE] flag to save a log of operations to a local file. bq works by making standard REST-based API calls, which can be useful to see. It is also useful to attach this log when reporting issues. Using - or stdout instead of a file path will print the log to the console. Setting --apilog to stderr outputs to the standard error file.
To help troubleshoot errors

Enter the --format=prettyjson flag when getting a job's status or when viewing 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 look up troubleshooting steps.

Setting default values for command-line flags

You can set default values for command-line flags by including them in the 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, the BIGQUERYRC environment variable is used. If that is not specified, the path ~/.bigqueryrc is used. The default path is $HOME/.bigqueryrc.

Adding 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) below it in the following format:
```
[COMMAND]
--command-specific_flag=[ARGUMENT]
--command-specific_flag=[ARGUMENT]
```

When you enter command-line flags in .bigqueryrc, you must specify the flag's argument in the following format =[ARGUMENT].

.bigqueryrc is read every time you run bq so changes should be updated immediately. When you run bq in interactive mode (bq shell), you must restart the shell for the changes to take effect.

Example

This example sets default values for the following global flags:

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

This example sets default values for the following query command-specific flags:

--use_legacy_sql is set to false to make standard SQL the default query syntax.

Note: You cannot use --nouse_legacy_sql in .bigqueryrc.
--max_rows is set to 100 to control the number of rows in the query output.
--maximum_bytes_billed is set to 10,000,000 bytes (10 MB) to fail queries that read more than 10 MB of data.

This example sets a default value for the following load command-specific flag:

--destination_kms_key is set to projects/myproject/locations/mylocation/keyRings/myRing/cryptoKeys/myKey.

credential_file = [PATH_TO_CREDENTIAL_FILE]
--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

To confirm your settings, enter the following command:

cat ~/.bigqueryrc

Running `bq` in an interactive shell

You can run bq in an interactive shell where you don't need to prefix the commands with bq. To start interactive mode, enter bq shell. After launching the shell, the prompt changes to the ID of your default project. To exit interactive mode, enter exit.

Examples

You can find command-line examples throughout the How-to Guides section of the BigQuery documentation. Below are links to common command-line tasks such as creating, getting, listing, deleting, and modifying BigQuery resources.

Creating resources

For information on using the command-line tool to create resources, see:

For examples of creating a table using a data file, see Loading data.

Getting information about resources

For information on using the command-line tool to get information about resources, see:

Listing resources

For information on using the command-line tool to list resources, see:

Updating resources

For information on using the command-line tool to update resources, see:

Loading data

For information on using the command-line tool to load data, see:

Querying data

For information on using the command-line tool to query data, see:

Using external data sources

For information on using the command-line tool to query data in external data sources, see:

Exporting data

For information on using the command-line tool to export data, see:

Exporting data stored in BigQuery

Using the BigQuery Data Transfer Service

For information on using the command-line tool with the BigQuery Data Transfer Service, see: