references/cli-usage.md
# BigQuery CLI Usage
The `bq` command-line tool is used to interact with BigQuery for managing
resources and running jobs.
## Basic Syntax
```bash
bq COMMAND [FLAGS] [ARGUMENTS]
```
## Essential Commands
### Dataset Management
- **Create a dataset:**
```bash
bq mk --dataset --location=us my_dataset
```
- **List datasets:**
```bash
bq ls --project_id my_project
```
### Table Management
- **Create a table from a schema file:**
```bash
bq mk --table my_dataset.my_table schema.json
```
- **Copy a table within or across datasets:**
```bash
bq cp my_dataset.my_table my_other_dataset.my_table_copy
```
- **Create a table snapshot (read-only copy):**
```bash
bq cp --snapshot --no_clobber my_dataset.my_table my_other_dataset.my_table_snapshot
```
- **Load data from Cloud Storage (CSV):**
```bash
bq load --source_format=CSV my_dataset.my_table gs://my-bucket/data.csv
```
- **Stream data into a table from a newline-delimited JSON file:**
```bash
bq insert my_dataset.my_table data.json
```
- **Delete a table:**
```bash
bq rm -f my_dataset.my_table
```
### Querying Data
- **Run a standard SQL query:**
```bash
bq query --use_legacy_sql=false \
'SELECT count(*) FROM `my_project.my_dataset.my_table`'
```
- **Run a dry run to estimate bytes processed:**
```bash
bq query --use_legacy_sql=false --dry_run \
'SELECT * FROM `my_project.my_dataset.my_table`'
```
### Job Management
- **List recent jobs:**
```bash
bq ls -j
```
- **Show job details:**
```bash
bq show -j job_id
```
- **Cancel a job:**
```bash
bq cancel job_id
```
## Global Flags
- `--location`: Specifies the geographic location for the job or resource.
- `--project_id`: Overrides the default project for the command.
- `--format`: Changes output format (e.g., `prettyjson`, `sparse`, `csv`).
For the complete BigQuery CLI reference guide, visit:
[bq command-line tool reference](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference.md.txt).
references/client-library-usage.md
# BigQuery Client Libraries
Google Cloud client libraries provide an idiomatic way to interact with BigQuery
from your preferred programming language.
## Getting Started
To use the client libraries, ensure you have the Google Cloud SDK installed and
authenticated.
[Install Google Cloud SDK](https://cloud.google.com/sdk/docs/install)
### Python
- **Installation:**
```bash
pip install --upgrade google-cloud-bigquery
```
- **Usage Example:**
```python
from google.cloud import bigquery
client = bigquery.Client()
query_job = client.query("SELECT * FROM `project.dataset.table` LIMIT 10")
results = query_job.result()
```
- [Python Reference](https://docs.cloud.google.com/python/docs/reference/bigquery/latest.md.txt)
### Java
- **Maven Dependency:**
```xml
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-bigquery</artifactId>
</dependency>
```
- **Usage Example:**
```java
BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(
"SELECT * FROM dataset.table").build();
TableResult results = bigquery.query(queryConfig);
```
- [Java Reference](https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/overview.md.txt)
### Node.js (TypeScript)
- **Installation:**
```bash
npm install @google-cloud/bigquery
```
- **Usage Example:**
```typescript
import {BigQuery} from '@google-cloud/bigquery';
const bigquery = new BigQuery();
const [rows] = await bigquery.query('SELECT * FROM dataset.table');
```
- [Node.js Reference](https://googleapis.dev/nodejs/bigquery/latest/index.html)
### Go
- **Installation:**
```bash
go get cloud.google.com/go/bigquery
```
- **Usage Example:**
```go
ctx := context.Background()
client, _ := bigquery.NewClient(ctx, "project-id")
q := client.Query("SELECT * FROM dataset.table")
it, _ := q.Read(ctx)
```
- [Go Reference](https://docs.cloud.google.com/go/docs/reference/cloud.google.com/go/bigquery/latest.md.txt)
## BigQuery DataFrames (BigFrames)
For Python users, `bigframes` provides a pandas-like API that executes directly
in BigQuery.
```bash
pip install --upgrade bigframes
```
- [BigFrames Guide](https://dataframes.bigquery.dev/)
references/core-concepts.md
# BigQuery Core Concepts
BigQuery is a fully managed, AI-ready data platform that helps you manage and
analyze your data with built-in features like machine learning, search,
geospatial analysis, and business intelligence. BigQuery's serverless
architecture lets you use languages like SQL and Python to answer your
organization's biggest questions with zero infrastructure management.
BigQuery provides a uniform way to work with both structured and unstructured
data and supports open table formats like Apache Iceberg. BigQuery streaming
supports continuous data ingestion and analysis while BigQuery's scalable,
distributed analysis engine lets you query terabytes in seconds and petabytes in
minutes.
## Architecture
BigQuery's architecture separates compute and storage, connected by a
petabit-scale network.
- **BigQuery Storage:** A columnar storage format optimized for analytical
queries. It can be replicated across multiple locations for high
availability.
- **BigQuery Analytics:** A scalable, distributed analysis engine that can
process data in BigQuery and in external sources.
## Resource Hierarchy
BigQuery organizes resources in a structured hierarchy:
1. **Organization/Folder/Project:** Standard Google Cloud resource containers.
2. **Dataset:** The top-level container for tables and views.
3. **Table/View:** The basic unit of data storage and logical representation.
## Analytics Workflows
- **Ad Hoc Analysis:** Using GoogleSQL for interactive queries.
- **Geospatial Analysis:** Analyzing and visualizing spatial data using
geography types.
- **Machine Learning (BigQuery ML):** Creating and executing ML models
directly in BigQuery using SQL.
- **Gemini in BigQuery:** AI-powered assistance for data preparation, SQL
generation, and visualization. Refer to the [Gemini
Models](https://ai.google.dev/gemini-api/docs/models) for more information.
- **Stream Processing (BigQuery continuous queries):** Long running SQL
statements that analyze and transform incoming data in near real time as it
arrives in BigQuery. This feature enables unbounded streaming pipelines for
real-time AI inference (using Vertex AI) and Reverse ETL to downstream
systems. Results can be exported to Pub/Sub, Bigtable, Spanner, or other
BigQuery tables. Note that running continuous queries requires a BigQuery
reservation with a `CONTINUOUS` assignment type.
## BigQuery Studio
A unified workspace for data engineering, analysis, and predictive modeling.
- **SQL Editor:** With code completion and generation.
- **Python Notebooks:** Built-in support for Colab Enterprise and BigQuery
DataFrames (BigFrames).
- **Data Discovery:** Integrated with Dataplex for search and profiling.
## Pricing
BigQuery pricing consists of two main components: compute (analysis) costs and
storage costs.
- **Storage:** Storage costs are based on the amount of data stored in
BigQuery tables. Storage is classified as either active storage (any table
or partition modified in the last 90 days) and long-term storage (data that
hasn't been modified for 90 consecutive days, resulting in a price drop of
approximately 50%).
- **Analysis:** Billed based on bytes processed (On-demand) or dedicated slots
(Capacity/Reservations).
For the latest pricing details, visit: [BigQuery
Pricing](https://cloud.google.com/bigquery/pricing).
references/iac-usage.md
# BigQuery Infrastructure as Code
Managing BigQuery resources using Infrastructure as Code (IaC) ensures
consistency and repeatability across environments.
## Terraform
The Google Cloud Terraform provider supports BigQuery datasets, tables, jobs,
and reservations.
### Dataset and Table Example
```terraform
resource "google_bigquery_dataset" "dataset" {
dataset_id = "example_dataset"
friendly_name = "test"
description = "This is a test description"
location = "US"
default_table_expiration_ms = 3600000
labels = {
env = "default"
}
}
resource "google_bigquery_table" "default" {
dataset_id = google_bigquery_dataset.dataset.dataset_id
table_id = "example_table"
time_partitioning {
type = "DAY"
}
labels = {
env = "default"
}
schema = <<EOF
[
{
"name": "name",
"type": "STRING",
"mode": "REQUIRED",
"description": "The user's name"
},
{
"name": "age",
"type": "INTEGER",
"mode": "NULLABLE",
"description": "The user's age"
}
]
EOF
}
```
### Reference Documentation
- [Terraform Google Provider - BigQuery Dataset](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_dataset)
- [Terraform Google Provider - BigQuery Table](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_table)
- [Terraform Google Provider - BigQuery Job](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_job)
## YAML Samples
BigQuery resources can also be managed via Deployment Manager or other tools
using YAML configurations.
- [BigQuery YAML Samples](https://docs.cloud.google.com/docs/samples?language=yaml&text=bigquery)
references/iam-security.md
# BigQuery IAM & Security
BigQuery uses Identity and Access Management (IAM) to provide granular access
control to its resources. As a security best practice, follow the
**principle of least privilege**: grant only the permissions required to
perform a specific action. This includes using the least permissive IAM role at
the most granular level—such as the table or view level—that is necessary.
## Predefined IAM Roles
For a complete list of predefined roles and detailed usage information, see [BigQuery IAM roles](https://docs.cloud.google.com/bigquery/docs/access-control.md.txt).
## Service Accounts and Agents
- **Default Service Account:** BigQuery uses a managed service account
(`bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com` or the more
general BigQuery Service Agent
`service-PROJECT_NUMBER@gcp-sa-bigquery.iam.gserviceaccount.com`) for
internal operations.
- **Service Account Impersonation:** Use
`gcloud config set auth/impersonate_service_account` for secure, temporary
credential access.
## Data Security
- **Encryption at Rest:** All data is encrypted by default using Google-managed
keys. Use Customer-Managed Encryption Keys (CMEK) for greater control.
- **VPC Service Controls:** Define a service perimeter to prevent data
exfiltration.
- **Column-Level Security:** Use policy tags to restrict access to sensitive
columns.
- **Row-Level Security:** Use row access policies to filter data based on user
identity.
- **Data Masking:** Obscure sensitive data in a table while still permitting
authorized users to access surrounding data.
- **Audit Logs:** Record user activity and system events to enforce data
governance policies and identify potential security risks.
- **Authorized Views:** Allow users to query a view without granting them access
to the underlying tables.
For more detailed information, see:
[BigQuery Security Overview](https://cloud.google.com/bigquery/docs/data-governance).
references/mcp-usage.md
# BigQuery MCP Usage
BigQuery is supported by a remote Model Context Protocol (MCP) server that
provides a set of tools for automated data management and analysis.
## MCP Tools for BigQuery
- **list_dataset_ids:** List BigQuery dataset IDs in a Google Cloud project.
- **get_dataset_info:** Get metadata information about a BigQuery dataset.
- **list_table_ids:** List table ids in a BigQuery dataset.
- **get_table_info:** Get metadata information about a BigQuery table.
- **execute_sql:** Run a SQL query in the project and return the result. This
tool is restricted to only `SELECT` statements. `INSERT`, `UPDATE`, and
`DELETE` statements and stored procedures aren't allowed. If the query
doesn't include a `SELECT` statement, an error is returned. For information
on creating queries, see the GoogleSQL documentation. The `execute_sql` tool
can also have side effects if the query invokes remote functions or Python
UDFs. All queries that are run using the `execute_sql` tool have a label that
identifies the tool as the source. You can use this label to filter the
queries using the label and value pair `goog-mcp-server: true`. Queries are
charged to the project specified in the `project_id` field.
## Setup Instructions
To connect to the BigQuery MCP server, see [Configure a client connection](https://docs.cloud.google.com/bigquery/docs/use-bigquery-mcp.md.txt).
## Supported Operations
Agents using the BigQuery MCP remote server can perform tasks such as:
- Answering questions about data by generating and running SQL.
- Getting dataset metadata.
- Getting table metadata.
For more information about the BigQuery MCP server, visit:
[Use the BigQuery MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-mcp.md.txt).
Alternatively, you can use
[MCP Toolbox](https://mcp-toolbox.dev/integrations/bigquery/source/), an
open-source CLI tool that runs a local MCP server for BigQuery connections. For
more on connecting BigQuery to your tools, see
[Connect LLMs to BigQuery with MCP](https://docs.cloud.google.com/bigquery/docs/pre-built-tools-with-mcp-toolbox.md.txt)
for details. For additional specialized skills and advanced analytics workflows,
install the
[BigQuery Data Analytics extension](https://github.com/gemini-cli-extensions/bigquery-data-analytics)
for the Gemini CLI or plugin for Claude Code and Codex.