Using the bq command-line tool

The bq command-line tool 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 bq command-line tool, you must use the Google Cloud Console to create or select a project and install the Cloud SDK.

1. Sign in to your Google Account.

   If you don't already have one, sign up for a new account.

2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to the project selector page

3. Install and initialize the Cloud SDK.
4. BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to Enable the BigQuery API.

   Enable the API

5. 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.

Running queries from the bq command-line tool

To take a query you have developed in the Google Cloud Console and run it from the bq command-line tool, do the following:

• Include the query in a bq query command as follows:

  bq query --use_legacy_sql=false 'QUERY'

• Replace any single quotes (') in the query with double quotes (").

• Remove comments from the query.

For example, transform the following Google Cloud Console query:

-- count Shakespeare's use of the string "raisin"
SELECT
  word,
  SUM(word_count) AS count
FROM
  `bigquery-public-data`.samples.shakespeare
WHERE
  word LIKE '%raisin%'
GROUP BY
  word

into a bq command-line tool query as follows:

bq query --use_legacy_sql=false \
'SELECT
  word,
  SUM(word_count) AS count
FROM
  `bigquery-public-data`.samples.shakespeare
WHERE
  word LIKE "%raisin%"
GROUP BY
  word'

For more information, see Running interactive and batch query jobs.

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 Google Cloud 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 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, the BIGQUERYRC environment variable is used. If that is not specified, the path ~/.bigqueryrc is used. The default path is $HOME/.bigqueryrc.

Note: Creating a .bigqueryrc file using the bq init command is not recommended.

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 Google Cloud 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.

Running bq in a script

You can run bq in a script, as you would run a gcloud command-line tool 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'

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 bq 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 bq command-line tool to get information about resources, see:

• Getting information about datasets
• Getting information about tables
• Getting information about views

Listing resources

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

• Listing datasets
• Listing tables
• Listing views

Updating resources

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

• Updating dataset properties
• Updating table properties
• Updating view properties

Loading data

For information on using the bq 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 bq command-line tool to query data, see:

• Running queries
• Writing query results
• Running parameterized queries

Using external data sources

For information on using the bq 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 bq command-line tool to export data, see:

• Exporting data stored in BigQuery

Using the BigQuery Data Transfer Service

For information on using the bq command-line tool 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

• Getting information about transfer configurations
• Listing transfer configurations
• Viewing the run history
• Viewing transfer run details and log messages
• Updating transfer configurations