Evaluating engineering tools? Get the comparison in Google Sheets

Connection

bq init
Initialize the BigQuery CLI and set up configuration

bq ls
List datasets in your default project

bq ls projectid:
List datasets in a specific project

gcloud auth login
Authenticate with Google Cloud

gcloud config set project PROJECT_ID
Set default project

Data Querying

bq query --use_legacy_sql=false 'SELECT * FROM `project.dataset.table` LIMIT 10'
Run a basic query

bq query --nouse_legacy_sql 'SELECT * FROM `project.dataset.table` LIMIT 10'
Alternative way to specify standard SQL

bq query --max_rows=100 --use_legacy_sql=false 'SELECT * FROM `project.dataset.table`'
Query with custom row limit

bq query --format=json 'SELECT * FROM `project.dataset.table` LIMIT 10'
Output query results in JSON format

Table Operations

bq show dataset.table
Display table metadata

bq head -n 10 dataset.table
Preview first 10 rows of a table

bq mk --table dataset.new_table field1:STRING,field2:INTEGER
Create a new table with schema

bq rm -f dataset.table
Delete a table (force without confirmation)

bq cp dataset.source_table dataset.destination_table
Copy a table

Data Loading

bq load --source_format=CSV dataset.table gs://bucket/path/file.csv field1:STRING,field2:INTEGER
Load CSV data from GCS

bq load --source_format=JSON dataset.table gs://bucket/path/file.json
Load JSON data from GCS

bq load --source_format=AVRO dataset.table gs://bucket/path/file.avro
Load AVRO data from GCS

bq load --source_format=PARQUET dataset.table gs://bucket/path/file.parquet
Load Parquet data from GCS

Data Export

bq extract dataset.table gs://bucket/path/file.csv
Export table data to CSV in GCS

bq extract --destination_format=AVRO dataset.table gs://bucket/path/file.avro
Export table data to AVRO in GCS

bq extract --destination_format=JSON dataset.table gs://bucket/path/file.json
Export table data to JSON in GCS

Dataset Operations

bq mk dataset
Create a new dataset

bq mk --dataset --description="Description" --location=US project:dataset
Create dataset with specific location

bq rm -r -f dataset
Delete a dataset and all its tables (force without confirmation)

bq update --description "New description" dataset
Update dataset description

Job Management

bq ls -j
List recent jobs

bq show -j job_id
Get details about a specific job

bq cancel job_id
Cancel a running job

SQL Helper Commands

SELECT * FROM `project.dataset.table` LIMIT 10
Basic query to fetch data

SELECT * FROM `project.dataset.table` WHERE column = 'value'
Filter data

SELECT column1, column2 FROM `project.dataset.table` GROUP BY column1
Group data

CREATE OR REPLACE TABLE `project.dataset.new_table` AS SELECT * FROM `project.dataset.source_table`
Create a table from query results

SELECT * FROM `project.dataset.table` WHERE DATE(timestamp_column) = CURRENT_DATE()
Filter by date