Stay organized with collections Save and categorize content based on your preferences.

bq command-line tool reference

This document describes the syntax, commands, flags, and arguments for bq, the BigQuery command-line tool. It is intended for users who are familiar with BigQuery, but want to know how to use a particular bq command-line tool command. For general information about how to use the bq command-line tool, see Using the bq command-line tool.

Synopsis

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.

Boolean flags

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.

Value Format Example
true --FLAGNAME=true --debug_mode=true
true --FLAGNAME --debug_mode
false --FLAGNAME=false --debug_mode=false
false --noFLAGNAME --nodebug_mode

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

Online help

Documentation is available in the bq command-line tool, as follows:

Description Help command format Example
List of all commands with examples bq help bq help
Description of global flags bq --help bq --help
Description of a particular command bq help COMMAND bq help mk

Resource specification

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.

Context Format Example
bq command-line tool PROJECT:DATASET.TABLE myProject:myDataset.myTable
GoogleSQL query PROJECT.DATASET.TABLE myProject.myDataset.myTable
Legacy SQL query PROJECT:DATASET.TABLE myProject:myDataset.myTable

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.

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

--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:

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.

Deprecated global flags

The following global flag for specifying bq command-line tool flags from a file is deprecated. To specify flags from a file, use the --bigqueryrc flag.

--flagfile=PATH

When specified, flag definitions from the supplied file are inserted into the bq command-line tool. The default value is ''. For more information, see Setting default values for command-line flags.

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:myAccount@gmail.com \
  --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:

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.
--clone={true|false}
To create a table clone, set to true. The source 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 source table.
--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 source 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 source 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:

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 or a view. The policy is in JSON format.

Synopsis

bq get-iam-policy [FLAGS] RESOURCE

Example

bq get-iam-policy myDataset.myTable

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.
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 Managing table data.

bq help

Use the bq help command to display bq command-line tool documentation within the tool.

Synopsis

bq help [COMMAND]

Flags and arguments

The bq help command uses the following flags and arguments:

COMMAND
Specifies a particular bq command-line tool command that you want to get online help for.

bq insert

Use the bq insert command to insert rows of newline-delimited, JSON-formatted data into a table from a file using the streaming insert. 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.

Synopsis

bq insert [FLAGS] TABLE FILE

Examples

bq insert --ignore_unknown_values --template_suffix=_insert myDataset.myTable /tmp/myData.json

echo '{"a":1, "b":2}' | bq insert myDataset.myTable

Flags and arguments

The bq insert command uses the following flags and arguments:

--ignore_unknown_values={true|false} or -i={true|false}
When set to true, BigQuery ignores any key-value pairs that do not match the table's schema, and inserts the row with the data that does match the schema. When set to false, rows with data that does not match the table's schema are not inserted. The default is false.
--skip_invalid_rows={true|false} or -s={true|false}
When set to true, BigQuery attempts to insert any valid rows, even if invalid rows are present. When set to false, the command fails if any invalid rows are present. The default is false.
--template_suffix=SUFFIX or -x=SUFFIX
When specified, treat the destination table 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.
TABLE
The table that you want to insert data into.
FILE
The file containing the data that you want to insert.

For more information about using the bq insert command, see Streaming data into BigQuery.

bq load

Use the bq load command to load data into a table.

Synopsis

bq load [FLAGS] DESTINATION_TABLE SOURCE_DATA [SCHEMA]

Example

bq load myDataset.newTable gs://mybucket/info.csv ./info_schema.json

Flags and arguments

The bq load command uses the following flags and arguments:

--allow_jagged_rows={true|false}
To allow missing trailing optional columns in CSV data, set to true.
--preserve_ascii_control_characters={true|false}
To allow embedded ASCII control characters in CSV data, set to true.
--allow_quoted_newlines={true|false}
To allow quoted newlines in CSV data, set to true.
--autodetect={true|false}
To enable schema auto-detection for CSV and JSON data, set to true. The default is false. If --autodetect is false, and no schema is specified by using the --schema flag, and the destination table exists, then the schema of the destination table is used.
--clustering_fields=COLUMNS
A comma-separated list of up to four column names that specifies the fields to use for table clustering.
--destination_kms_key=KEY
Specifies a Cloud KMS key resource ID for encrypting the destination table data.
--encoding=ENCODING_TYPE or -E=ENCODING_TYPE
The character encoding used in the data. Use one of the following values:
  • ISO-8859-1 (also known as Latin-1)
  • UTF-8
--field_delimiter=DELIMITER or -F=DELIMITER
Specifies the character that marks the boundary between columns in the data. The delimiter can be any ISO-8859-1 single-byte character. You can use either \t or tab to specify tab delimiters.
--ignore_unknown_values={true|false}
When set to true, 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.
--json_extension=JSON_TYPE

Specifies the type of JSON file to load. Applies only to JSON files. Possible values are the following:

  • GEOJSON - newline-delimited GeoJSON file

To use this flag, the --source_format flag must be set to NEWLINE_DELIMITED_JSON.

For more information, see Loading newline-delimited GeoJSON files.

--max_bad_records=MAX

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=STRING

An optional custom string that represents a NULL value in CSV data.

--projection_fields=PROPERTY_NAMES

If you set --source_format to DATASTORE_BACKUP, then this flag indicates which entity properties to load from a Datastore export. Specify the property names in a comma-separated list. Property names are case sensitive and must refer to top-level properties. You can also use this flag with Firestore exports.

--quote=CHARACTER

Specifies a quote character to surround fields in CSV data. 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={true|false}

To erase any existing data and schema when new data is loaded, set to true. Any Cloud KMS key is also removed, unless you specify the --destination_kms_key flag. The default value is false.

Equivalent to the WRITE_TRUNCATE value for JobConfigurationLoad.writeDisposition.

--schema={SCHEMA_FILE|SCHEMA}

Specifies 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, and so on. If you use a schema file, then do not give it an extension.

For example: 

--schema=/tmp/tabledef

--schema=Region:STRING,Quarter:STRING,Total_sales:INTEGER

If no schema is specified, and --autodetect is false, and the destination table exists, then the schema of the destination table is used.

--schema_update_option=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. Use one of the following values:

  • ALLOW_FIELD_ADDITION: Allow new fields to be added
  • ALLOW_FIELD_RELAXATION: Allow relaxing REQUIRED fields to NULLABLE

Repeat this flag to specify multiple schema update options.

--skip_leading_rows=NUMBER_OF_ROWS

An integer that specifies the number of rows to skip at the beginning of the source file. The default is 0.

--source_format=FORMAT

The format of the source data. Use one of the following values:

  • CSV
  • NEWLINE_DELIMITED_JSON
  • AVRO
  • DATASTORE_BACKUP (use this value for Filestore)
  • PARQUET
  • ORC
--time_partitioning_expiration=SECONDS

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=COLUMN_NAME

Specifies the field that determines how to create a time-based partition. If time-based partitioning is enabled without this value, then the table is partitioned based on the load time.

--time_partitioning_type=INTERVAL

Enables time-based partitioning on a table and sets the partition type. Use one of the following values:

  • DAY
  • HOUR
  • MONTH
  • YEAR

The default partition type for time-based partitioning is DAY.

--use_avro_logical_types={true|false}

If the --source_format flag is set to AVRO, then set this flag to true to convert logical types into their corresponding types (such as TIMESTAMP) instead of only using their raw types (such as INTEGER).

--decimal_target_types=DECIMAL_TYPE

Determines how to convert a Decimal logical type. Equivalent to JobConfigurationLoad.decimalTargetTypes. Repeat this flag to specify multiple target types.

--parquet_enum_as_string={true|false}

If the --source_format flag is set to PARQUET, and you want BigQuery to infer Parquet ENUM logical types as STRING values, then set this flag to true. The default is false.

--parquet_enable_list_inference={true|false}

If the --source_format flag is set to PARQUET, then this flag indicates whether to use schema inference for Parquet LIST logical types.

--reference_file_schema_uri=URI

Specifies the path to a reference file with the expected table schema for creating external tables. Equivalent to ExternalDataConfiguration.referenceFileSchemaUri. This flag is enabled for Avro, ORC, and PARQUET formats.

DESTINATION_TABLE

The table that you want to load data into.

SOURCE_DATA

The