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.
Before you begin
Before you can use the BigQuery command-line tool, you must use the Google Cloud Console to create or select a project and install the Cloud SDK.
-
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
-
In the Cloud Console, on the project selector page, select or create a Google Cloud project.
- Install and initialize the Cloud SDK.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.
- BigQuery provides a sandbox if you do not want to provide a credit card or enable billing for your project. The steps in this topic work for a project whether or not your project has billing enabled. If you optionally want to enable billing, see Learn how to enable billing.
Instead of downloading and installing the Cloud SDK, 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
, enterbq 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-
orstdout
instead of a file path will print the log to the console. Setting--apilog
tostderr
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 thereason
property. You can use thereason
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 tostdout
to print debugging output to the console.--format
is set toprettyjson
to display command output in a human-readable JSON format.--location
is set to theUS
multi-region location.
This example sets default values for the following query
command-specific
flags:
--use_legacy_sql
is set tofalse
to make standard SQL the default query syntax.--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 toprojects/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:
- Creating a dataset
- Creating an empty table with a schema definition
- Creating a table from a query result
- Creating an ingestion-time partitioned table
- Creating a view
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:
- Loading Avro data from Cloud Storage
- Loading JSON data from Cloud Storage
- Loading CSV data from Cloud Storage
- Loading data from a local file
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:
- Creating a table definition using a JSON schema file
- Querying Cloud Bigtable data using permanent external tables
- Querying Cloud Storage data using permanent external tables
- Querying Google Drive data using permanent external tables
Exporting data
For information on using the command-line tool to export data, see:
Using the BigQuery Data Transfer Service
For information on using the command-line tool with the BigQuery Data Transfer Service, see:
- Setting up a Campaign Manager transfer
- Setting up a Cloud Storage transfer (beta)
- 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 (beta)
- Setting up a Search Ads 360 transfer (beta)
- Setting up a YouTube Channel transfer
- Setting up a YouTube Content Owner transfer