The bq command-line tool is a Python-based command-line tool for BigQuery. It provides several functionalities to users, such as running queries, creating datasets and tables, loading data, copying tables, and printing help information. This article will provide several examples illustrating the use cases of the bq command.

Use case 1: Run query against a BigQuery table using standard SQL

Code:

bq query --nouse_legacy_sql 'SELECT COUNT(*) FROM DATASET_NAME.TABLE_NAME'

Motivation: This use case is useful when you want to run a query against a BigQuery table to retrieve the count of rows.

Explanation:

• query: Specifies that the bq command should run a query.
• --nouse_legacy_sql: Uses standard SQL syntax instead of legacy SQL. It enables using the latest SQL syntax and features.
• 'SELECT COUNT(*) FROM DATASET_NAME.TABLE_NAME': The SQL query to be run.

Example Output: The output will be the count of rows in the specified table.

Use case 2: Run a parameterized query

Code:

bq query --use_legacy_sql=false --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR)'

Motivation: This use case is useful when you want to run a parameterized query, where the values of the parameters can be passed dynamically.

Explanation:

• --use_legacy_sql=false: Specifies that the query should use standard SQL syntax.
• --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00': Specifies the parameter ts_value with a value of 2016-12-07 08:00:00 in TIMESTAMP format.
• 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR)': The SQL query to be run using the parameter ts_value.

Example Output: The output will be the timestamp value 2016-12-07 09:00:00.

Use case 3: Create a new dataset or table in the US location

Code:

bq mk --location=US dataset_name.table_name

Motivation: This use case is useful when you want to create a new dataset or table in the US location.

Explanation:

• mk: Specifies that the bq command should create a new object.
• --location=US: Specifies the location of the dataset or table as the US.
• dataset_name.table_name: The name of the dataset or table to be created, in the format dataset_name.table_name.

Example Output: The dataset or table will be successfully created in the US location.

Use case 4: List all datasets in a project

Code:

bq ls --filter labels.key:value --max_results integer --format=prettyjson --project_id project_id

Motivation: This use case is useful when you want to list all datasets in a project, filtered by a specific label key-value pair and limited to a maximum number of results.

Explanation:

• ls: Specifies that the bq command should list datasets.
• --filter labels.key:value: Filters the datasets based on the specified label key-value pair.
• --max_results integer: Limits the number of results displayed to the specified integer value.
• --format=prettyjson: Formats the output in a pretty JSON format.
• --project_id project_id: Specifies the project ID to list the datasets from.

Example Output: The output will be a list of datasets in the project, filtered based on the label key-value pair, limited to the specified number of results, and formatted in pretty JSON.

Use case 5: Batch load data from a specific file to a table

Code:

bq load --location=location --source_format=CSV|JSON|PARQUET|AVRO dataset.table path_to_source

Motivation: This use case is useful when you want to batch load data from a specific file in formats such as CSV, JSON, Parquet, or Avro to a table.

Explanation:

• load: Specifies that the bq command should load data.
• --location=location: Specifies the location of the dataset or table.
• --source_format=CSV|JSON|PARQUET|AVRO: Specifies the format of the source file as CSV, JSON, Parquet, or Avro.
• dataset.table: Specifies the destination table in the format dataset.table.
• path_to_source: Specifies the path to the source file.

Example Output: The data from the source file will be loaded into the specified table.

Use case 6: Copy one table to another

Code:

bq cp dataset.OLD_TABLE dataset.new_table

Motivation: This use case is useful when you want to copy the contents of one table to another.

Explanation:

• cp: Specifies that the bq command should copy a table.
• dataset.OLD_TABLE: Specifies the source table to be copied in the format dataset.table.
• dataset.new_table: Specifies the destination table in the format dataset.table.

Example Output: The contents of the source table will be copied to the destination table.

Use case 7: Print help

Code:

bq help

Motivation: This use case is useful when you need to access help information about the bq command and its functionalities.

Explanation:

• help: Specifies that the bq command should provide help information.

Example Output: The help information for the bq command will be displayed.

Conclusion:

The bq command-line tool provides a wide range of functionalities for interacting with Google BigQuery. By familiarizing yourself with the various use cases and their examples, you can effectively utilize the bq command to manage datasets, tables, and queries in BigQuery.