bigquery-basics

# BigQuery Change History

BigQuery change history lets you track the history of changes made to a BigQuery
table. You can use SQL functions to see particular types of changes made during
a specified time range, so that you can process incremental changes made to a
table. Understanding what changes have been made to a table can help you do
things like incrementally maintain a table replica outside of BigQuery while
avoiding costly copies.

BigQuery provides two functions to track table modifications. The
`APPENDS` function returns all rows appended to a table for a given time range,
while the `CHANGES` function returns all rows that have changed in a table for a
given time range, including inserts, updates, and deletes.

## Enabling Change History

To use the `CHANGES` function on a table, you must set the table's
`enable_change_history` option to `TRUE`. The `APPENDS` function does not
require this option to be set.

```sql
ALTER TABLE `my_project.my_dataset.my_table`
SET OPTIONS (enable_change_history = TRUE);
```

## Querying Change History

Note: Timestamp arguments can be strings in the format `YYYY-MM-DD HH:MM:SS` or
`TIMESTAMP` objects.

### APPENDS function

The `APPENDS` function returns all rows appended to a table for a given time
range.

```sql
SELECT
  *,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE `my_dataset.my_table`, '2023-12-31 08:00:00', '2023-12-31 12:00:00');
```

### CHANGES function

The `CHANGES` function returns all rows that have changed in a table for a given
time range. This includes inserts, updates, and deletes.

```sql
SELECT
  *,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time,
  _CHANGE_IS_FOR_UPDATE as change_is_for_update
FROM
  CHANGES(TABLE `my_dataset.my_table`, '2023-12-31 08:00:00', '2023-12-31 12:00:00');
```

**Key Output Columns:**

-   `_CHANGE_TYPE`: A `STRING` value indicating the type of change (`INSERT`,
    `UPDATE`, `DELETE`).
-   `_CHANGE_TIMESTAMP`: A `TIMESTAMP` value indicating the commit time of the
    transaction that made the change.
-   `_CHANGE_IS_FOR_UPDATE`: A `BOOL` value that is `TRUE` for a `DELETE` event
    produced by a row update. Otherwise, the value is `FALSE` (only present in
    `CHANGES`).

## Limitations

For a full list of constraints, such as unsupported table types and time range
limits, please refer to the public documentation for BigQuery change
history:
https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time-series-functions.md.txt

# 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).

# 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/)

# BigQuery Continuous Queries

BigQuery continuous queries are SQL statements that run continuously in an
unbounded fashion. They let you analyze incoming data in BigQuery in real time.

You can output the results of a continuous query in several ways:

-   Write to a BigQuery table by using an `INSERT` statement.
-   Export to Pub/Sub, Bigtable, or Spanner by using an `EXPORT DATA`
    statement.

## Use Cases

Continuous queries turn BigQuery into an event-driven data processing engine,
unlocking real-time capabilities:

-   Event-Driven Workflows & Agentic Systems: You can trigger downstream
    applications or autonomous agents based on complex events detected in
    incoming data streams. For example, integrate with Pub/Sub to send real-time
    events to downstream agentic systems for further processing.
-   Real-Time AI Inference: Apply generative AI models directly on live data
    streams to generate text or embeddings on the fly, enabling personalized
    customer interactions or real-time anomaly detection.
-   Reverse ETL: Seamlessly push enhanced event data from BigQuery directly
    to operational databases like Spanner or Bigtable for low-latency
    application serving.

## Syntax and Usage

To run a continuous query, you must specify the earliest data to process using
the `APPENDS` function (or `CHANGES` for certain Pub/Sub exports) in the `FROM`
clause.

The start timestamp defines the point in time at which the continuous query
begins processing data.

### Example: Writing to a BigQuery Table

```sql
INSERT INTO `myproject.real_time_taxi_streaming.transformed_taxirides`
SELECT
  timestamp,
  meter_reading,
  ride_status
FROM
  APPENDS(TABLE `myproject.real_time_taxi_streaming.taxirides`,
    CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE)
WHERE
  ride_status = 'dropoff';
```

### Example: Writing to a Pub/Sub Topic

```sql
EXPORT DATA
  OPTIONS (
    format = 'CLOUD_PUBSUB',
    uri = 'https://pubsub.googleapis.com/projects/myproject/topics/taxi-real-time-rides')
AS (
  SELECT
    TO_JSON_STRING(
      STRUCT(
        ride_id,
        timestamp,
        latitude,
        longitude)) AS message,
    TO_JSON(
      STRUCT(
        CAST(passenger_comment AS STRING) AS passenger_comment))
  FROM
    CHANGES(TABLE `myproject.real_time_taxi_streaming.taxi_rides`,
      CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE)
  WHERE _CHANGE_TYPE = 'DELETE'
);
```

## Important Considerations & Limitations

-   Authorization: A continuous query run by a user account runs for a
    maximum of two days and then automatically stops. To run a continuous query
    for up to 150 days, you must use a service account.
-   Reservations: Running continuous queries requires an Enterprise edition
    or Enterprise Plus edition reservation with a `CONTINUOUS` job type
    assignment.
-   Supported Operations: Continuous queries support a limited set of
    stateful operations, such as specific types of `JOIN`s, aggregations, and
    windowing functions. Many standard SQL capabilities like `SELECT DISTINCT`,
    `PIVOT`, and subqueries like `EXISTS` are not supported unless part of a
    supported stateful operation.

For more detail on how to use or structure continuous queries, please refer
to the public documentation for BigQuery continuous queries:
-   https://docs.cloud.google.com/bigquery/docs/continuous-queries-introduction.md.txt
-   https://docs.cloud.google.com/bigquery/docs/continuous-queries.md.txt
-   https://docs.cloud.google.com/bigquery/docs/continuous-query-joins.md.txt
-   https://docs.cloud.google.com/bigquery/docs/window-aggregations.md.txt
-   https://docs.cloud.google.com/bigquery/docs/continuous-queries-monitor.md.txt

# 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. See [Continuous Queries](continuous-queries.md) for more
    detail.

## 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).

# 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)

# 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
  (`[email protected]` or the more
  general BigQuery Service Agent
  `[email protected]`) 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).

# 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.

---
name: bigquery-basics
metadata:
  category: BigDataAndAnalytics
description: >-
  Manages datasets, tables, and jobs in BigQuery. Use when you need to interact
  with BigQuery, run SQL queries, manage BigQuery resources (datasets, tables,
  views), or perform basic data ingestion and analysis.
---

# BigQuery Basics

BigQuery is a serverless, AI-ready data platform that enables high-speed
analysis of large datasets using SQL and Python. Its disaggregated architecture
separates compute and storage, allowing them to scale independently while
providing built-in machine learning, geospatial analysis, and business
intelligence capabilities.

## Setup and Basic Usage

1.  **Enable the BigQuery API:**

    ```bash
    gcloud services enable bigquery.googleapis.com --quiet
    ```

2.  **Create a Dataset:**

    ```bash
    bq mk --dataset --location=US my_dataset
    ```

3.  **Create a Table:**

    Create a file named `schema.json` with your table schema:

    ```json
    [
      {
        "name": "name",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "post_abbr",
        "type": "STRING",
        "mode": "NULLABLE"
      }
    ]
    ```

    Then create the table with the `bq` tool:

    ```bash
    bq mk --table my_dataset.mytable schema.json
    ```

4.  **Run a Query:**

    ```bash
    bq query --use_legacy_sql=false \
    'SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` \
    WHERE state = "TX" LIMIT 10'
    ```

## Reference Directory

- [Core Concepts](references/core-concepts.md): Storage types, analytics
  workflows, and BigQuery Studio features.

- [Change History](references/change-history.md): Tracking and querying
  incremental table changes using APPENDS and CHANGES.

-   [Continuous Queries](references/continuous-queries.md): Running continuous
    SQL statements to analyze incoming data in real time.

- [CLI Usage](references/cli-usage.md): Essential `bq` command-line tool
  operations for managing data and jobs.

- [Client Libraries](references/client-library-usage.md): Using Google Cloud
  client libraries for Python, Java, Node.js, and Go.

- [MCP Usage](references/mcp-usage.md): Using the BigQuery remote MCP server and
  Gemini CLI extension.

- [Infrastructure as Code](references/iac-usage.md): Terraform examples for
  datasets, tables, and reservations.

- [IAM & Security](references/iam-security.md): Roles, permissions, and data
  governance best practices.

*If you need product information not found in these references, use the
Developer Knowledge MCP server `search_documents` tool.*

## Related Skills

- [BigQuery AI & ML Skill](../bigquery-ai-ml):
  SKILL.md file for BigQuery AI and ML capabilities (forecast, anomaly
  detection, text generation).