Skip to main content

BigQuery command-line tool

The BigQuery command-line tool is installed as part of the Cloud SDK and can be used to interact with BigQuery. When you use CLI commands in a notebook, the command must be prepended with a !.

View available commands​

To view the available commands for the BigQuery command-line tool, use the help command.

!bq help

Create a new dataset​

A dataset is contained within a specific project. Datasets are top-level containers that are used to organize and control access to your tables and views. A table or view must belong to a dataset. You need to create at least one dataset before loading data into BigQuery.

First, name your new dataset:

dataset_id = "your_new_dataset"

The following command creates a new dataset in the US using the ID defined above.

NOTE: In the examples in this notebook, the dataset_id variable is referenced in the commands using both {} and $. To avoid creating and using variables, replace these interpolated variables with literal values and remove the {} and $ characters.

!bq --location=US mk --dataset $dataset_id

The response should look like the following:

Dataset 'your-project-id:your_new_dataset' successfully created.

List datasets​

The following command lists all datasets in your default project.

!bq ls

The response should look like the following:

           datasetId            
 ------------------------------ 
  your_new_dataset

Load data from a local file to a table​

The following example demonstrates how to load a local CSV file into a new or existing table. See SourceFormat in the Python client library documentation for a list of available source formats. For more information, see Loading Data into BigQuery from a local data source in the BigQuery documentation.

!bq \
    --location=US \
    load \
    --autodetect \
    --skip_leading_rows=1 \
    --source_format=CSV \
    {dataset_id}.us_states_local_file \
    'resources/us-states.csv'

Load data from Cloud Storage to a table​

The following example demonstrates how to load a local CSV file into a new table. See SourceFormat in the Python client library documentation for a list of available source formats. For more information, see Introduction to loading data from Cloud Storage in the BigQuery documentation.

!bq \
    --location=US \
    load \
    --autodetect \
    --skip_leading_rows=1 \
    --source_format=CSV \
    {dataset_id}.us_states_gcs \
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv'

Run a query​

The BigQuery command-line tool has a query command for running queries, but it is recommended to use the magic command for this purpose.

Cleaning Up​

The following code deletes the dataset created for this tutorial, including all tables in the dataset.

!bq rm -r -f --dataset $dataset_id