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 Cloud Storage URI of the file that contains the data that you want to load.

SCHEMA

The schema for the destination table.

For more information about loading data from Cloud Storage using the bq load command, see the following:

For more information about loading data from a local source using the bq load command, see the following:

bq ls

Use the bq ls command to list objects in a collection.

Synopsis

bq ls [FLAGS] [RESOURCE]

Example

bq ls myDataset

Flags and arguments

The bq ls command uses the following flags and arguments:

--all={true|false} or -a={true|false}
To show all results, set to true. Shows jobs from all users or all datasets, including hidden ones. This flag is not needed when listing transfer configurations or transfer runs. The default value is false.
--capacity_commitment={true|false}

To list capacity commitments, set to true and use the --location flag to specify the location. For more information, see View purchased commitments.

For example: bq ls --capacity_commitment=true --location='us'

--datasets={true|false} or -d={true|false}

To list datasets, set to true. The default value is false.

--filter="FILTER"

Filters the listed resources to match the FILTER argument.

For datasets, FILTER consists of one or more space-separated triples in the format labels.KEY:VALUE. If more than one triple is provided, then the command only returns datasets matching all of the triples (i.e., the command uses the AND logical operator, not OR). If you want to specify more than one triple, then surround the FILTER value with quotation marks.

  • To filter based on dataset labels, use the keys and values that you applied to your datasets.

    For example: 

    --filter "labels.department:marketing labels.team:sales"

For transfer configurations, use dataSourceIds as the key, and one of the following data sources as the value:

For example: 

   --filter labels.dataSourceIds:dcm_dt

For transfer runs, use states as the key, and one of the following transfer states as the value: + SUCCEEDED + FAILED + PENDING + RUNNING + CANCELLED

 For example:
 <pre>
 --filter labels.states:FAILED
 </pre>

For jobs, the filter flag is not supported.

--jobs={true|false} or -j={true|false}
To list jobs, set to true. The default value is false. By default, you are limited to 100,000 results.
--max_creation_time=MAX_CREATION_TIME_MS
An integer that represents a Unix epoch timestamp in milliseconds. When specified with the --jobs flag, this flag lists only the jobs created before the timestamp.
--max_results=MAX_RESULTS or -n=MAX_RESULTS
An integer indicating the maximum number of results. The default value is 50 and the maximum value is 1000. If you have more than 1000 jobs, then you can use the page_token flag to list all jobs using pagination.
--min_creation_time=MIN_CREATION_TIME_MS
An integer that represents a Unix epoch timestamp in milliseconds. When specified with the --jobs flag, this flag lists only the jobs created after the timestamp.
--message_type=messageTypes:MESSAGE_TYPE

To list only transfer run log messages of a particular type, specify messageTypes:MESSAGE_TYPE. Possible values are the following:

  • INFO
  • WARNING
  • ERROR
--models={true|false} or -m={true|false}

To list BigQuery ML models, set to true. The default value is false.

--page_token=TOKEN or -k=TOKEN

Lists items starting from the specified page token.

--projects={true|false} or -p={true|false}

To show all projects, set to true. The default value is false.

--reservation={true|false}

To list all reservations for a given project and location, set to true. The default value is false. Use with the --project_id and --location flags.

For example: 

bq ls --reservation=true --project_id=myProject --location=us

--reservation_assignment={true|false}

To list all reservation assignments for a given project and location, set to true. The default value is false. Use with the --project_id and --location flags.

--routines={true|false}

To list all routines in the specified dataset, set to true. The default value is false. Routines include persistent user-defined functions, table functions (Preview), and stored procedures.

--row_access_policies

When specified, lists all the row-level access policies on a table. Row-level access policies are used for row-level security. You must supply the table name in the format dataset.table.

--run_attempt=RUN_ATTEMPT

Use with the --transfer_run flag. To list all run attempts for the specified transfer run, set to RUN_ATTEMPT_UNSPECIFIED. To list only the latest run attempt, set to LATEST. The default is LATEST.

--transfer_config={true|false}

To list transfer configurations in the specified project and location, set to true. Use with the --transfer_location and --project_id flag. The default value is false.

--transfer_location=LOCATION

List transfer configurations in the specified location. You set the transfer location when the transfer is created.

--transfer_log={true|false}

Use with the --transfer_run flag. To list transfer log messages for the specified transfer run, set to true. The default value is false.

--transfer_run={true|false}

Lists the transfer runs for the specified transfer configuration.

For example: 

bq ls --transfer_run=true projects/myProject/locations/us/transferConfigs/12345

RESOURCE

The collection whose objects that you want to list. The resource can be a dataset, project, reservation, or transfer configuration.

For more information about using the bq ls command, see the following:

bq mk

Use the bq mk command to create a BigQuery resource.

Synopsis

bq mk TYPE_FLAG [OTHER FLAGS] [ARGS]

Flags and arguments

The bq mk command takes a type flag that specifies the type of resource to create, and additional flags that depend on the resource type.

TYPE_FLAG: Set one of the following flags to true. Your selection specifies the type of resource to create.

The bq mk command supports the following flag for all types of resources:

--force={true|false} or -f={true|false}
To ignore errors if a resource with the same name already exists, set to true. If the resource already exists, then the exit code is 0, but setting this flag to true does not cause the bq mk command to overwrite the resource. The default value is false.

The bq mk command supports additional flags, depending on the type of resource you are creating, as described in the following sections.

bq mk --capacity_commitment

To purchase a capacity commitment, set --capacity_commitment to true and use the following flags:

--location=LOCATION
Specifies the location of the commitment.
--plan=PLAN_TYPE
Specifies the commitment plan type. Set to one of the following:
  • FLEX
  • MONTHLY
  • ANNUAL
--renewal_plan=RENEWAL_TYPE
Specifies the renewal plan type. Applicable and required only for an ANNUAL commitment plan. One of the following:
  • FLEX
  • MONTHLY
  • ANNUAL
--project_id=PROJECT_ID
Specifies the project that administers the slots.
--slots=NUMBER_OF_SLOTS
Specifies the number of slots to purchase.

For more information, see Purchase slots.

bq mk --connection

Creates a connection. The following flags are supported:

--connection_type=CONNECTION_TYPE
The type of the connection, for example CLOUD_SQL for Cloud SQL connections.
--properties=PROPERTIES
Connection specific parameters in JSON format. instanceId, database and type must be specified.
--connection_credential=CONNECTION_CREDENTIAL
The credentials of the connection in JSON format. username and password must be specified.
--project_id=PROJECT_ID
Specifies the ID of the project that the connection belongs to.
--location=LOCATION
Specifies the location that the connection will be stored.
--display_name=DISPLAY_NAME
Specifies an optional friendly name for the connection.
--description=DESCRIPTION
Specifies an optional description of the connection.
--iam_role_id=ROLE_ID

For BigQuery Omni on AWS, specifies an IAM role that allows access to the resource.

Use the following format: "arn:aws:iam::AWS_ACCOUNT_ID:role/POLICY_NAME", where:

  • AWS_ACCOUNT_ID is the ID number of the connection's AWS IAM user.
  • POLICY_NAME is the policy name.

Example: "arn:aws:iam::0123456789AB:policy/s3-read-role"

--tenant_id=TENANT_ID

For BigQuery Omni on Azure, specifies the tenant ID of the Azure directory that contains the Azure Storage account.

CONNECTION_ID

Specifies an optional connection id for the connection. If a connection id is not provided a unique id is automatically generated. The connection id can contain letters, numbers and underscores.

For more information, see Creating connections.

bq mk --dataset

Creates a dataset. The following flags are supported:

--default_kms_key=KEY
Specifies the default Cloud KMS key resource ID for encrypting the table data in a dataset if no explicit key is provided during table creation or query.
--default_partition_expiration=SECONDS
An integer that specifies the default expiration time, in seconds, for all partitions in newly-created partitioned tables in the dataset. A partition's expiration time is set to the partition's UTC date plus the integer value. If this property is set, then its value overrides the dataset-level default table expiration if it exists. If you supply the --time_partitioning_expiration flag when you create or update a partitioned table, then the table-level partition expiration takes precedence over the dataset-level default partition expiration.
--default_table_expiration=SECONDS
An integer that specifies the default lifetime, in seconds, for newly created tables in a dataset. The expiration time is set to the current UTC time plus this value.
--description=DESCRIPTION
Specifies the description of the dataset.
--label=KEY:VALUE
Specifies a label for the dataset. Repeat this flag to specify multiple labels.
--location=LOCATION or --data_location=LOCATION
Specifies the location of the dataset. Prefer the --location flag; the --data_location flag is a legacy flag.
--max_time_travel_hours=HOURS

In preview. Specifies the duration in hours of the time travel window for the dataset. The --max_time_travel_hours value must be an integer between 48 (2 days) and 168 (7 days). 168 hours is the default if this flag isn't specified.

For more information on the time travel window, see Configuring the time travel window.

--storage_billing_model=BILLING_MODEL

In preview. Specifies the storage billing model for the dataset. Set this flag value to LOGICAL to use logical bytes for storage billing, or to PHYSICAL to use physical bytes instead. LOGICAL is the default if this flag isn't specified.

For more information, see Dataset storage billing models.

For more information, see Creating datasets.

bq mk --materialized_view

Creates a materialized view. The following flags are supported:

--enable_refresh={true|false}
To disable automatic refresh for a materialized view, set to false. The default when creating a materialized view is true.
--refresh_interval_ms=MILLISECONDS
Specifies the number of milliseconds for the refresh interval of a materialized view. If this flag is not specified, then the default refresh interval for a materialized view that has refresh enabled is 1,800,000 milliseconds, which is 30 minutes.

For more information, see Creating and using materialized views.

bq mk --reservation

Creates a reservation with dedicated slots. The following flags are supported:

--concurrency=CONCURRENCY
In preview. Specifies the target number of queries that execute simultaneously. The default value is 0, which means that the concurrency is automatically computed based on the reservation size. For more information, see Use query queues.
--ignore_idle_slots={true|false}
To restrict jobs running in this reservation to only use slots allocated to the reservation, set to true. The default value is false; jobs in this reservation can use idle slots from other reservations, or slots that are not allocated to any reservation. For more information, see Idle slots.
--location=LOCATION
Specifies the location of the reservation.
--project_id=PROJECT_ID
Specifies the project that owns the reservation.
--slots=NUMBER_OF_SLOTS
Specifies the number of slots to allocate to this reservation.

For more information, see Create a reservation with dedicated slots.

bq mk --reservation_assignment

Assigns a project, folder, or organization to a reservation. The following flags are supported:

--assignee_id=ASSIGNEE_ID
Specifies the ID of the folder, organization, or project.
--assignee_type=ASSIGNEE_TYPE
Specifies the type of entity to assign to the reservation. One of the following:
  • FOLDER
  • ORGANIZATION
  • PROJECT
--job_type=JOB_TYPE
Specifies the type of job to assign to the reservation. One of the following:
  • QUERY
  • PIPELINE
  • ML_EXTERNAL
  • BACKGROUND
--location=LOCATION
Specifies the location of the reservation.
--project_id=PROJECT_ID
Specifies the project that owns the reservation.
--reservation_id=RESERVATION_ID
Specifies the ID of the reservation.

For more information, see Work with reservation assignments.

bq mk --table

Creates a table. The following flags are supported:

--clustering_fields=COLUMNS
A comma-separated list of up to four column names that specifies the fields to use for table clustering. If specified with partitioning, then the table is first partitioned, and then each partition is clustered using the supplied columns.
--description=DESCRIPTION
Specifies the description of the table.
--destination_kms_key=KEY
Specifies a Cloud KMS key resource ID for encrypting the destination table data.
--expiration=SECONDS
Specifies the lifetime for the table. If SECONDS is 0, then the table doesn't expire. If you don't specify the --expiration flag, then BigQuery creates the table with the dataset's default table lifetime.
--external_table_definition=STRING

Specifies a table definition for creating an external table.

For Cloud Storage and Google Drive external tables:

--external_table_definition={PATH_TO_FILE|DEFINITION}
The value can be either a path to a file containing a table definition (PATH_TO_FILE) or an inline table definition (DEFINITION).
  • The format for the DEFINITION field is SCHEMA@FORMAT=URI.

  • The format for the SCHEMA value is a comma-separated list of column definitions in the form FIELD:DATA_TYPE, FIELD:DATA_TYPE, and so on. You can omit the SCHEMA value if the data format is self-describing (such as Avro) or if you are using schema auto-detection.

  • The FORMAT value specifies the data format; one of the following:

    • AVRO
    • CSV
    • DATASTORE_BACKUP (use this value for Filestore)
    • ICEBERG
    • NEWLINE_DELIMITED_JSON
    • ORC
    • PARQUET

If you specify a table definition file, do not give it an extension.

For example: 

--external_table_definition=/tmp/tabledef

--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv

For Cloud Bigtable external tables, and BigLake tables based on AWS and Azure:

--external_table_definition=PATH_TO_FILE
The value must be a path to a file containing a table definition.

For BigLake tables based on Cloud Storage:

--external_table_definition=FORMAT=BUCKET_PATH@REGION.CONNECTION_NAME :

  • The FORMAT value specifies the data format; one of the following:

    • AVRO
    • CSV
    • NEWLINE_DELIMITED_JSON
    • ICEBERG
    • ORC
    • PARQUET

  • BUCKET_PATH is the path to one or more files in Cloud Storage that contain the BigLake table data. You can specify BUCKET_PATH in the following formats:

    • For a single file: gs://bucket_name/[folder_name/]file_name.
    • For multiple files in a single bucket: gs://bucket_name/[folder_name/]*.

    • For multiple files in multiple buckets: gs://mybucket1/*,gs://mybucket2/folder5/*.

      You can use wildcards to limit the files included in the BigLake table. For example, if the bucket contains several types of data, you could have the table only use PARQUET files by specifying gs://bucket_name/*.parquet. For more information about using wildcards, see URI wildcards.

  • The REGION value specifies the region or multi-region that contains the connection.

  • The CONNECTION_NAME value specifies the name of the cloud resource connection to use with this external table. The connection determines which service account is used to read data from Cloud Storage.

For object tables:

--external_table_definition=BUCKET_PATH@REGION.CONNECTION_NAME :

  • BUCKET_PATH is the path to the Cloud Storage bucket that contains the objects represented by the object table, in the format gs://bucket_name/[folder_name/]*.You can specify multiple buckets by providing multiple paths, for example gs://mybucket1/*,gs://mybucket2/folder5/*.

    You can use wildcards to limit the objects included in the object table. For example, if the bucket contains several types of unstructured data, you could create the object table over only PDF objects by specifying gs://bucket_name/*.pdf. For more information about using wildcards, see URI wildcards.

  • The REGION value specifies the region or multi-region that contains the connection.

  • The CONNECTION_NAME value specifies the name of the cloud resource connection to use with this external table. The connection determines which service account is used to read data from Cloud Storage.

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

--label=KEY:VALUE

Specifies a label for the table. Repeat this flag to specify multiple labels.

--max_staleness=INTERVAL

Specifies whether cached metadata is used by operations against the table, and how fresh the cached metadata must be in order for the operation to use it.

Applicable for BigLake tables and object tables. In preview.

To disable metadata caching, specify 0. This is the default.

To enable metadata caching, specify an interval value between 30 minutes and 7 days, using the Y-M D H:M:S format described in the INTERVAL data type documentation. For example, specify 0-0 0 4:0:0 for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the c