Google BigQuery

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