DOCSEARCHbyAlgolia
Submit Docs

BigQuery Emulator

repository·main·Indexed 22 days ago

https://github.com/goccy/bigquery-emulator

An open-source, BigQuery-compatible server for local testing and development. It supports GoogleSQL and utilizes SQLite for storage, allowing developers to run BigQuery workloads without cloud projects or credentials. It provides a REST API (default port 9050) and a gRPC Storage API (default port 9060), and can be installed via Go, Docker, or prebuilt binaries.

Tokens
7.1K
Snippets
24
Records
36
Agent score
75%

What's inside bigquery-emulator

  1. Understand Table type and Storage support

    main

    The emulator provides varying levels of support for different table types and storage features:

    FeatureStatusNotes
    Standard tables✅Fully supported
    Logical views✅Created via DDL or tables.insert; view schemas are hydrated
    Materialized views✅Registered and queryable
    External tables❌Table type exists but is not implemented
    Table snapshots❌Not supported
    Table clones❌Not supported
    Partitioned tables🟡Metadata is accepted, but partition pruning and _PARTITIONTIME semantics are not emulated
    Clustered tables🟡Metadata is accepted but does not affect execution
    Storage backend✅Embedded SQLite — in-memory or a persisted file

  2. Use GoogleSQL and Query features

    main

    Query execution is delegated to googlesqlite. The emulator supports most standard GoogleSQL features:

    • Statements: SELECT, DDL (CREATE, ALTER, DROP for tables, views, materialized views, functions), DML (INSERT, UPDATE, DELETE, MERGE, TRUNCATE), and Scripting/multi-statement queries.
    • Functions: ~570 built-in functions (including BigQuery-specific ones) and SQL UDFs.
    • JavaScript UDFs: Supported via CREATE ... LANGUAGE js. Note: Persisting a JS routine via routines.insert is not supported.
    • Other features: Wildcard tables, Templated-argument functions, and Table-valued functions.
    • Query Parameters: Supports named and positional parameters. Empty/absent numeric, temporal, and string parameters are treated as typed NULLs.

    Limitations:

    • INFORMATION_SCHEMA: Only SCHEMATA, TABLES, TABLE_OPTIONS, and COLUMNS are implemented. Other views like JOBS, VIEWS, ROUTINES, and PARTITIONS are not.
    • Time Travel: FOR SYSTEM_TIME AS OF is not supported.
    • Transactions: Sessions/multi-statement transactions over the REST API are not supported.
    • BigQuery ML: CREATE MODEL and ML.* functions are not supported.

  3. How the emulator handles data storage

    main

    The BigQuery emulator uses SQLite for its backend storage. You have two primary modes for data handling:

    1. In-Memory: By default, if no --database file is specified, all data is stored in memory and lost when the process exits.
    2. File-based Persistence: By providing a path to a file via the --database option, you can persist your data across server restarts.

    Additionally, you can use the --data-from-yaml flag to load initial datasets from a YAML file on startup.

  4. How the BigQuery Emulator works

    main

    The BigQuery Emulator implements a GoogleSQL execution engine by combining several components:

    1. Parser/Analyzer: When a GoogleSQL query is received via REST, the emulator uses go-googlesql (a ZetaSQL parser/analyzer) to parse and analyze the query.
    2. Execution Engine: The analyzed query is executed against an embedded SQLite database.
    3. Type Conversion: Since SQLite lacks native support for complex BigQuery types like ARRAY and STRUCT, the googlesqlite driver encodes these types along with their type information into SQLite. These are then decoded using a custom function registered with the SQLite driver during retrieval.

  5. Understand BigQuery Job support and limitations

    main

    The emulator supports specific job types via jobs.insert.

    Supported Job Types:

    • Query: Executes GoogleSQL. It exposes the anonymous results table as destinationTable.
    • Load: Supports data ingestion (see Data Ingestion section).
    • Extract: Supports data export (see Data Export section).

    Unsupported Job Types:

    • Copy: Table copy jobs are not implemented.

  6. Quick start with Docker and bq CLI

    main

    To quickly run a BigQuery emulator using Docker and execute a query using the bq command-line tool, follow these steps:

    1. Run the emulator container, mapping ports 9050 (REST) and 9060 (gRPC) and specifying a project name.
    2. Use the bq tool with the --api flag to point to the emulator's REST endpoint.

    Note: Ensure the --project_id in the bq command matches the --project name used when starting the emulator.

    $ docker run -it -p 9050:9050 -p 9060:9060 ghcr.io/goccy/bigquery-emulator:latest --project=test
$ bq --api http://0.0.0.0:9050 query --project_id=test "SELECT 1"

  7. Use the BigQuery Emulator with a Python client

    main

    To use the emulator with the Python google-cloud-bigquery SDK, follow these steps:

    1. Start the standalone server: Run the emulator with a project and dataset.

      ./bigquery-emulator --project=test --dataset=dataset1

      The REST server defaults to 0.0.0.0:9050 and the gRPC server to 0.0.0.0:9060.

    2. Configure the Client: Create a bigquery.Client using ClientOptions to point to the emulator's REST endpoint and use AnonymousCredentials to bypass authentication.

    3. Handling DataFrames: If you use .to_dataframe(), you must either disable the BigQuery Storage client or configure it to use the local gRPC port.

    from google.api_core.client_options import ClientOptions
from google.auth.credentials import AnonymousCredentials
from google.cloud import bigquery
from google.cloud.bigquery import QueryJobConfig

# 1. Setup client options for the REST endpoint
client_options = ClientOptions(api_endpoint="http://0.0.0.0:9050")

# 2. Initialize client with project ID and anonymous credentials
client = bigquery.Client(
  "test",
  client_options=client_options,
  credentials=AnonymousCredentials(),
)

# 3. Execute query
client.query(query="...", job_config=QueryJobConfig())

# Note: If using to_dataframe(), disable storage client to avoid connection errors:
result = client.query(sql).to_dataframe(create_bqstorage_client=False)

  8. Configure Data Ingestion (Load) options

    main

    The emulator supports several methods for ingesting data:

    • Streaming inserts: Via tabledata.insertAll. Note that unknown fields are reported as errors.
    • Storage Write API: Supported via gRPC.
    • Google Cloud Storage (GCS): To load from a GCS emulator, set the STORAGE_EMULATOR_HOST environment variable to point to your GCS emulator.
    • Local Files: Supports multipart and resumable uploads.

    Supported Formats:

    • CSV: Schema autodetect is supported.
    • JSON: Newline-delimited format.
    • Parquet: Supported.

    Unsupported Formats/Services:

    • Avro/ORC: Not supported for loading.
    • BigQuery Data Transfer Service: Not supported.

  9. Install the BigQuery Emulator

    main

    You can install the emulator using several methods:

    Via Go

    If Go is installed, use go install to get the latest version:

    go install github.com/goccy/bigquery-emulator/cmd/bigquery-emulator@latest

    Via Docker

    Pull the multi-arch image (supports linux/amd64 and linux/arm64):

    docker pull ghcr.io/goccy/bigquery-emulator:latest

    *Note: On M1 Macs using Docker Desktop, you may need to use the --platform linux/x86_64 flag.

    Via Prebuilt Binaries

    Download binaries for Darwin, Linux, and Windows (amd64/arm64) or package managers (deb/rpm/apk) from the GitHub releases page.

    $ go install github.com/goccy/bigquery-emulator/cmd/bigquery-emulator@latest

  12. Call the BigQuery Storage API (gRPC) from Python

    main

    The emulator's gRPC server (default port 9060) uses plaintext and does not support TLS. Standard google-cloud-bigquery-storage clients attempt a TLS handshake and will fail with SSL_ERROR_SSL errors.

    To fix this, you must manually construct a client using an insecure gRPC channel via the appropriate transport class.

    import grpc
from google.cloud import bigquery_storage
from google.cloud.bigquery_storage_v1.services.big_query_read.transports import (
    BigQueryReadGrpcTransport,
)

# Create an insecure channel pointing to the emulator's gRPC port
transport = BigQueryReadGrpcTransport(
    channel=grpc.insecure_channel("0.0.0.0:9060"),
)

# Initialize the client with the custom transport
read_client = bigquery_storage.BigQueryReadClient(transport=transport)

# Use the client with your BigQuery client
result = client.query(sql).to_dataframe(bqstorage_client=read_client)