Skip to content

BigQuery

floci-gcp emulates the BigQuery v2 REST API (the surface the google-cloud-bigquery SDKs and the Discovery document define): datasets, tables with schemas, tabledata.insertAll, tabledata.list, and query jobs that run GoogleSQL on an embedded DuckDB engine, plus the Storage Read and Write APIs over gRPC on the same port.

Configuration

Variable Default Description
FLOCI_GCP_SERVICES_BIGQUERY_ENABLED true Enable/disable BigQuery
FLOCI_GCP_SERVICES_BIGQUERY_MOCK false When true, queries run on a small built-in SQL subset and no Docker container is started
FLOCI_GCP_SERVICES_BIGQUERY_DUCK_DEFAULT_IMAGE floci/floci-duck:latest Image of the SQL engine sidecar
FLOCI_GCP_SERVICES_BIGQUERY_DUCK_URL (none) Use an already running floci-duck instead of starting one. The host may contain an underscore, as in Docker Compose's floci-duck.<project>_default
FLOCI_GCP_SERVICES_BIGQUERY_DUCK_CALLBACK_URL (derived) Base URL the sidecar reads staged rows from. Only needed when DUCK_URL points somewhere the resolved docker host cannot reach, such as another machine
FLOCI_GCP_SERVICES_BIGQUERY_UPLOAD_SESSION_IDLE_TIMEOUT_SECONDS 604800 Idle time before an unfinished resumable load-job upload is dropped with its buffered bytes. Lower it on long-lived instances
FLOCI_GCP_SERVICES_BIGQUERY_UPLOAD_SESSION_SWEEP_INTERVAL_SECONDS 3600 Interval between sweeps for expired upload sessions; 0 disables the sweeper

The SQL engine is the floci-duck sidecar, the same one floci (AWS) uses for Athena. floci-gcp starts it through the host Docker daemon on the first query and stops it on shutdown. It joins FLOCI_GCP_SERVICES_DOCKER_NETWORK when that is set, and it reads table rows back from floci-gcp over HTTP, so the sidecar must be able to reach the emulator (the same requirement as the Cloud Run, Cloud SQL and GKE sidecars).

Endpoint

BigQuery has no *_EMULATOR_HOST convention in the Java SDK: point the client at floci-gcp with setHost and disable credentials:

BigQuery bigquery = BigQueryOptions.newBuilder()
    .setHost("http://localhost:4588")
    .setProjectId("floci-local")
    .setCredentials(NoCredentials.getInstance())
    .build().getService();

Python:

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

client = bigquery.Client(project="floci-local", credentials=AnonymousCredentials(),
                         client_options=ClientOptions(api_endpoint="http://localhost:4588"))

REST paths live under /bigquery/v2/projects/{project}/....

Scope

  • Datasets: insert, get, list, patch/update, delete (deleteContents honored; deleting a non-empty dataset without it returns 400 resourceInUse). Duplicate create returns 409 duplicate.
  • Tables: insert, get, list, patch/update, delete. Schemas accept standard or legacy type names and are normalized to legacy names (INT64→INTEGER, FLOAT64→FLOAT, BOOL→BOOLEAN, STRUCT→RECORD) with NULLABLE as the default mode, matching SDK round-trips.
  • tabledata.insertAll: schema-validated per row: HTTP 200 always, failures reported via insertErrors (unknown fields honor ignoreUnknownValues; skipInvalidRows=false inserts nothing and marks valid rows stopped; missing REQUIRED fields and uncoercible values are rejected with reason invalid).
  • tabledata.list: {f:[{v:"..."}]} row encoding (scalars as strings, REPEATED as arrays of {v}, RECORD as nested {f:[...]}), maxResults/pageToken/startIndex paging. maxResults=0 returns zero rows (the SDK's Job.waitFor() contract); a pageToken wins over startIndex (the SDK sends both when auto-paging).
  • Jobs / queries: jobs.query, jobs.insert (QUERY, LOAD and COPY), jobs.get, jobs.list, jobs.cancel, jobs.delete, getQueryResults. Query results are materialized into a hidden anonymous table (_floci_anon.anon_<jobId>) referenced as the job's configuration.query.destinationTable, which is how the SDK's Job.getQueryResults() reads rows.
  • Copy jobs: configuration.copy accepts either sourceTable or non-empty sourceTables, plus destinationTable. Only operationType COPY, OPERATION_TYPE_UNSPECIFIED (treated as unset), or an omitted operation type is supported; SNAPSHOT, RESTORE and CLONE are rejected. destinationExpirationTime, when supplied, sets the created destination table's expiration time. Without it, the source expiration is not inherited and the destination dataset's default table expiration applies. It supports CREATE_IF_NEEDED/CREATE_NEVER and WRITE_EMPTY/WRITE_TRUNCATE/WRITE_APPEND, validates that multiple sources have identical schemas, partitioning and clustering, and copies table metadata when creating a destination. Partition-decorated source and destination references copy only the named partition. Supported decorators follow the table definition: $YYYYMMDDHH (hour), $YYYYMMDD (day), $YYYYMM (month), $YYYY (year), and $range_start for integer-range partitions. Copying a decorated source to an undecorated partitioned destination retains the source partition. Column- and range-partitioned decorated copies require identical partition specifications and IDs; ingestion-time copies may target the same granularity or a containing coarser partition. Copy references are currently limited to the project that owns the job. Malformed source or destination references and invalid destinationExpirationTime values return HTTP 400 with reason invalid before a job ID is reserved, even when the destination already exists. Resource and copy-compatibility errors remain completed jobs with status.errorResult. Successful jobs report statistics.copy.copiedRows as a string-encoded integer, counting only incoming rows (not pre-existing destination rows on append). Logical byte counts are not estimated. These fields follow the BigQuery v2 discovery document.
  • Query parameters: parameterMode NAMED (@name) and POSITIONAL (?), with scalar, ARRAY and STRUCT parameter types.
  • Scripts and temporary objects: request-scoped CREATE TEMP FUNCTION and CREATE TEMP TABLE declarations can be interleaved with queries in source order. Functions become temporary DuckDB macros; typed scalar, array and nested STRUCT parameters are accepted, and CTEs in a function body remain local to that body. A function body resolves against its parameters and the columns of the tables it reads, never against the script around it, so a body that reads a script variable is rejected: Query error: Invalid reference to script variable v; in function f. A body is an expression, not a query, so a query needs the extra parentheses of a scalar subquery, and AS (SELECT ...) answers BigQuery's The body of each CREATE FUNCTION statement is an expression, not a query. Temporary tables accept an explicit schema, an AS query, or both, and can be explicitly addressed through the _SESSION qualifier; a _SESSION name that was not declared answers Not found: Dataset <project>:_SESSION was not found, as BigQuery does. CREATE TEMP TABLE IF NOT EXISTS skips a temporary table that already exists and CREATE OR REPLACE TEMP TABLE replaces one, where CREATE TEMP FUNCTION rejects a clause such as IF NOT EXISTS or OR REPLACE: Clauses such as IF NOT EXISTS are not yet supported with CREATE TEMP FUNCTION. OR REPLACE after the object name answers Syntax error: Unexpected keyword OR, as BigQuery's grammar takes it before TEMP. A second declaration of the same temporary table answers Already Exists: Table <project>:_SESSION.<name>, as BigQuery answers Already Exists: Table .... Results from preceding statements are discarded; the final query or DML statement supplies the result. A script reports no DML counts, numDmlAffectedRows or dmlStats, since BigQuery runs its statements as child jobs and the parent carries only the final result. A declaration, an assignment, or the condition that ends a loop does not discard it, so a loop reports its final iteration's result and a script that declares or assigns only answers with no rows. A FOR loop reports no result of its own, even when it runs no iteration, so a script that ends with one answers with no rows. A request that holds only one procedural statement, such as DECLARE x INT64;, runs as a script, as BigQuery runs it. Procedural BEGIN/END blocks can be nested and contain supported statements or IF/ELSEIF/ELSE blocks; their statements execute in source order using the same temporary objects. Searched and simple CASE statements run the first matching branch, with optional ELSE. DECLARE/SET supports INT64, BOOL, STRING, FLOAT64, NUMERIC, DATE, TIMESTAMP, BYTES, DATETIME, TIME, JSON, STRUCT, and arrays of those non-array types, including ARRAY<STRUCT<...>>; multiple names may share a declaration type and default expression. Simple literals, array literals, and arithmetic are evaluated by floci-gcp; other supported expressions are evaluated by the configured SQL engine. A value of another type is refused with BigQuery's wording, so DECLARE x INT64 DEFAULT 1.9 answers Query error: Cannot coerce expression 1.9 to type INT64. A FLOAT64 literal stays floating point, so f + 0.2 over DECLARE f FLOAT64 DEFAULT 0.1 answers 0.30000000000000004, as BigQuery does, and the operands type the arithmetic, so SET f = 9007199254740993 - 9007199254740992 stores the exact 1.0 and an INT64 overflow fails even when the target is FLOAT64. A typed array literal keeps its element type, so DECLARE xs DEFAULT ARRAY<FLOAT64>[] declares an empty ARRAY<FLOAT64>, and a STRUCT value is assigned by field position, so STRUCT(1 AS b, 2 AS a) reaches STRUCT<a INT64, b INT64> as a = 1, b = 2. A NUMERIC variable takes a floating point literal as written, so DECLARE n NUMERIC DEFAULT 1.5 stores 1.5, and refuses a FLOAT64 value with BigQuery's wording, so DECLARE f FLOAT64 DEFAULT 2.5; DECLARE n NUMERIC DEFAULT f answers Query error: Cannot coerce expression f to type NUMERIC. An array takes only its own array type, since BigQuery coerces no array: [1, 2] is an ARRAY<INT64>, so it answers Query error: Cannot coerce expression [1, 2] to type ARRAY<NUMERIC>, [1.5] is an ARRAY<FLOAT64> and answers the same wording, and a typed literal must name the target's element type, ARRAY<FLOAT64>[1.5] and ARRAY<INT64>[1] answering the same. Inside a typed ARRAY<NUMERIC> a floating point literal is an element, so ARRAY<NUMERIC>[1.5] stores 1.5, while a FLOAT64 value answers Array element type FLOAT64 does not coerce to NUMERIC, and ARRAY<INT64>[1.9] the same for INT64. EXECUTE IMMEDIATE refuses a result of another type the same way, so INTO over a NUMERIC variable and a FLOAT64 result answers Value returned by Dynamic SQL has type FLOAT64 which is not assignable to variable n of type NUMERIC. Variable references are token-substituted as typed literals; the statement's own names win over a variable of the same name: a source column, a column a DML statement names, a CTE name, the columns a CTE's select list exposes, a function's parameters, a FROM item's alias (a table, UNNEST or derived table), the columns a derived table exposes, function names, and qualified fields are left intact, as are quoted names, which resolve like unquoted ones, so a column keeps its name while the values an INSERT supplies still bind the variables. Those names win in the query that reads them and in the queries nested inside it, never in a sibling query, so DECLARE v INT64 DEFAULT 1; CREATE TEMP TABLE t AS SELECT 4 AS v; SET v = v + (SELECT COUNT(*) FROM t) stores 2: the column answers 4 inside the subquery, and the variable the v around it, while SELECT COUNT(*) FROM t UNION ALL SELECT v answers 1 and the variable's 1. A WITH clause declares its CTE for every branch of a set operation, so WITH t AS (SELECT 4 AS v) SELECT 0 UNION ALL SELECT v FROM t answers 0 and 4, and a nested WITH of the same name shadows the CTE of the query around it, so WITH t AS (SELECT 4 AS v) SELECT 0 UNION ALL SELECT (WITH t AS (SELECT 2 AS w) SELECT v FROM t) answers 0 and the variable's 1. The expression a SET or DECLARE default evaluates and the condition of an IF, WHILE or REPEAT collect the same names from the tables they read and the CTEs they define, in the same queries, and a CTE nothing reads declares none of them. A variable that is a whole select item keeps its name as the result column, and so does one the item wraps in parentheses or backticks, SELECT (x) and SELECT `x` answering a column named x while SELECT f + 0.2 is named f0_, as BigQuery names it; so are a function argument such as SELECT COALESCE(NULL, f) and a grouped expression such as SELECT (f) + 1. A sort or group key takes the variable's value, which BigQuery reads as a constant, so ORDER BY f neither renames the item nor changes the order. A field of a JSON variable is named after its last field, SELECT j.a.b answering column b, where the subscript SELECT j['a'] keeps the generated name, as BigQuery keeps it. A script variable in LIMIT or OFFSET is rejected, Query error: LIMIT expects an integer literal or parameter, as BigQuery rejects it, and a query parameter is accepted there; the OFFSET(i) array subscript is an expression, so a variable is read there. OFFSET is not reserved, so a variable may be named offset; the WITH OFFSET of an UNNEST stays the clause. EXECUTE IMMEDIATE supports a single query, supported DDL or DML statement, or BEGIN block. USING binds named or positional query parameters, and INTO assigns a zero- or one-row result to declared variables. SQL-text expressions can use outer script variables. The dynamic statement itself only sees USING values, not outer script variables or request query parameters; nested EXECUTE IMMEDIATE is rejected, and the SQL it supplies is validated before any of it runs, so an invalid block writes nothing. WHILE supports simple scalar comparisons with a 1,000-iteration safety limit. BEGIN blocks scope variables. Full GoogleSQL expression and type semantics are not supported. Labels are supported on BEGIN blocks and loops, including labeled BREAK/LEAVE and CONTINUE/ITERATE; CONTINUE cannot target a block. BEGIN blocks do not provide transaction semantics, and transactions and exception handlers are not supported. Procedural IF blocks execute only their selected branch. Supported persistent DML and DDL statements execute in source order. A sequence of temporary function declarations followed by one query remains a single query, as in BigQuery. FOR … IN evaluates its table expression once and exposes each row as a struct of the query's fields in the loop body, so a field keeps its type. A CREATE TEMP TABLE whose declared schema makes a field NOT NULL rejects a row that would store a NULL there. All loop forms stop after 1,000 iterations. JavaScript UDFs and positional parameters in multi-statement scripts are not supported, where BigQuery binds positional parameters across the statements of a script; a parameter list sent without names rejects a script the same way, since that is the positional mode BigQuery infers for it. Multi-statement queries reject destination, disposition, schema update, partitioning, clustering, destination encryption, and user-defined function resource configuration fields.
  • Dry runs: dryRun on jobs.query or configuration.dryRun on jobs.insert validates the query and returns its result schema (statistics.query.schema on the job) and totalBytesProcessed, without running it or persisting a job. An invalid query in a dry run is an HTTP 400. DML and DDL dry runs resolve every table the statement reads or writes, and DML is bound against those tables, so a dry run fails wherever the real run would. A script dry run describes its CREATE TEMP TABLE declarations instead of storing them, so a later statement of the same script still resolves them by name and the reported schema is the last statement's. It stops at variable statements or control flow without validating their following statements, and stops after validating the first DDL statement; BigQuery validates every statement of a script, including the ones after a DECLARE and in unselected branches.
  • Destination tables: jobs.insert honors configuration.query.destinationTable with createDisposition (CREATE_IF_NEEDED default, CREATE_NEVER) and writeDisposition (WRITE_EMPTY default, WRITE_TRUNCATE, WRITE_TRUNCATE_DATA, WRITE_APPEND). Failures are reported in the job status (duplicate, notFound); any other disposition value is an HTTP 400 (invalid). A partition-decorated destination writes that named partition of an existing partitioned table; WRITE_TRUNCATE, WRITE_APPEND and WRITE_EMPTY apply to that partition. Queries over an ingestion-time-partitioned base table materialize _PARTITIONTIME for stored rows and _PARTITIONDATE for daily partitions, so they can filter or explicitly project a selected partition. These pseudocolumns are not expanded by SELECT *. An undecorated read sees all stored rows, while a decorator selects one partition.
  • Views: logical and materialized views, created with tables.insert (view.query) or DDL. The view query is validated and its schema derived on creation; views are expanded at query time, including views over views. A materialized view is evaluated on read, like a logical view.
  • Timestamp output: formatOptions.useInt64Timestamp and formatOptions.timestampOutputFormat (FLOAT64, INT64, ISO8601_STRING) are honored on jobs.query, getQueryResults and tabledata.list. The default is epoch seconds, which is what the SDKs parse.

Table and dataset metadata

Every client-writable Table and Dataset property of the BigQuery v2 API is stored and returned as sent, so SDK and Terraform round-trips keep partitioning, clustering, expiration, collation, rounding mode, encryption configuration, constraints, resource tags and view definitions. PATCH merges the fields in the request (an explicit null clears one); PUT replaces them all, except linkedDatasetSource: the reference says it "cannot be updated once it is set", so an attempt to change it on either verb is ignored rather than rejected. With updateMode=UPDATE_ACL neither verb touches these fields at all.

Server-side behavior driven by these fields:

  • New tables inherit the dataset's defaultTableExpirationMs as expirationTime, and new time-partitioned tables inherit defaultPartitionExpirationMs as timePartitioning.expirationMs (and then no table expiration). An explicit value on the table wins. defaultTableExpirationMs: 0 in a dataset PATCH clears the default.
  • A table past its expirationTime is deleted when it is next read or listed.
  • Output fields are filled in: dataset type (LINKED with a linkedDatasetSource, EXTERNAL with an externalDatasetReference, otherwise DEFAULT), location (US when not given) and maxTimeTravelHours (168 when not set); table location (the dataset's), numLongTermBytes and selfLink. Tables created with view, materializedView or externalDataConfiguration get the matching type.
  • Validation: defaultTableExpirationMs of at least one hour, maxTimeTravelHours from 48 to 168, an expirationTime that parses as an int64, timePartitioning.type one of DAY/HOUR/MONTH/YEAR with a positive expirationMs, and not both time and range partitioning. A time partition field must be a non-repeated DATE, DATETIME or TIMESTAMP field, with HOUR unavailable for DATE. A range partition needs a non-repeated INTEGER field and integer start, end and positive interval values. _PARTITIONDATE and _PARTITIONTIME are reserved schema names. Violations return 400 with reason invalid, and a rejected update leaves the stored resource unchanged.

Partitioning and clustering are metadata only apart from partition-aware reads and writes, _PARTITIONDATE/_PARTITIONTIME query pseudocolumns, and partition rows reported by INFORMATION_SCHEMA.PARTITIONS. Decorated tabledata.insertAll is limited, like BigQuery's legacy streaming API, to daily ingestion-time partitions from 31 days in the past through 16 days in the future. Streaming through tabledata.insertAll or the Storage Write API into a time-unit column partition accepts values from 10 years in the past through 1 year in the future. Queries do not physically prune partitions, partition expiration is not applied, and requirePartitionFilter is not enforced.

GoogleSQL support

Queries are translated to DuckDB SQL and executed on the sidecar, so most of GoogleSQL's SELECT surface works:

  • JOIN (all kinds, including correlated LEFT JOIN UNNEST(array) alias), GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, DISTINCT, UNION/INTERSECT/EXCEPT (ALL and DISTINCT), subqueries, WITH (CTEs), window functions, QUALIFY, PIVOT, UNPIVOT, UNNEST (including WITH OFFSET, FROM t, t.array_column AS x, and x IN UNNEST(...)), SELECT * EXCEPT (...), and SELECT * REPLACE (...).
  • SELECT * EXCEPT (...) and SELECT * REPLACE (...) also apply to a STRUCT expression (t.s.* EXCEPT (a), t.s.* REPLACE (expr AS field)), in the outermost query, a CTE or a subquery. The same item keeps BigQuery's DISTINCT, LIMIT/OFFSET and ORDER BY behavior, a positional GROUP BY and GROUP BY ALL count the projected fields, and a column of the query's own sources that an aggregate still needs is projected next to the expansion. An indexed aggregate star such as ARRAY_AGG(t ORDER BY id DESC LIMIT 1)[OFFSET(0)].* EXCEPT (id) applies its star list, with or without parentheses around the aggregate, and a hoisted ordering can dereference a nested field of a source STRUCT column (ORDER BY u.n). A plain t.s.* with no star list groups by position or GROUP BY ALL as BigQuery does. Replacement expressions read the original query's sources, including correlated columns. A bare name in a replacement, such as an UNNEST range variable, its WITH OFFSET alias or a column of a subquery without an alias, reads the FROM row, not a same-named field of the struct. Struct-field references remain bound to the original struct, not same-named sibling outputs or replaced fields. Like BigQuery, REPLACE rejects analytic functions directly in its expressions. Compute windows in a source query or a scalar subquery instead. Hoisted ordering can read unselected source columns. Generated projections avoid known field and output names, including fields from stored table schemas. Aggregate struct stars keep WHERE in source scope, separate from post-aggregation HAVING and QUALIFY. Field lookup preserves source aliases and complete nested paths, including stored table schemas, when joined sources expose same-named struct columns. A correlated SELECT AS STRUCT input.* EXCEPT (id) hides _PARTITIONDATE and _PARTITIONTIME in any query whose FROM provides input.
  • A duplicate output name takes BigQuery's _1 suffix in the result schema, including a sibling that collides with an expanded field under a positional GROUP BY or GROUP BY ALL, and a star over a subquery that repeats a name. A name two select outputs or two source columns expose is ambiguous wherever BigQuery resolves it (the select list, WHERE, ON, GROUP BY expressions, ORDER BY, HAVING, QUALIFY and a window in the select list), and a suffix generated for a duplicate is unrecognized unless a source column carries that name. A bare GROUP BY name resolves a unique output alias first, while a qualified item such as GROUP BY t.b still names its source alias. A window in the select list reads input columns rather than sibling aliases, and a window in ORDER BY, HAVING or QUALIFY resolves a unique output alias first, as the rest of that clause does. A star's EXCEPT list drops every output written with the name across the query's sources, including columns merged by a USING join, and a REPLACE on the same star is still applied to the surviving outputs. REPLACE rejects a name it would replace more than once. A bare star over an UNPIVOT source, including each UNPIVOT that decorates a source of a join, returns the surviving source columns, then the value columns, then the name column, as BigQuery does. A star's EXCEPT and REPLACE over PIVOT output resolve against the pivot's generated column names, and PIVOT and UNPIVOT reject a duplicated name they reference. A CREATE TABLE or CREATE VIEW whose query repeats a name is rejected, and querying a view whose definition repeats a name fails, as both fail in BigQuery.
  • Terminal-prefix wildcard tables such as events_* are expanded across matching base tables and expose _TABLE_SUFFIX for filtering.
  • GoogleSQL literals: double-quoted and triple-quoted strings, raw strings (r'...'), bytes literals, TIMESTAMP/DATETIME/DATE/TIME/NUMERIC/JSON typed literals, and typed array literals including ARRAY<STRUCT<...>>[...].
  • CAST/SAFE_CAST with GoogleSQL type names, including ARRAY<...> and STRUCT<...>.
  • Functions rewritten to their DuckDB equivalents: SAFE_DIVIDE, IEEE_DIVIDE, DIV, IF, COUNTIF, CONCAT, LOGICAL_AND/LOGICAL_OR, ARRAY_LENGTH, ARRAY_REVERSE, ARRAY_SLICE, ARRAY_CONCAT_AGG, GENERATE_ARRAY, GENERATE_DATE_ARRAY, GENERATE_TIMESTAMP_ARRAY, GENERATE_UUID, APPROX_COUNT_DISTINCT, APPROX_QUANTILES, SPLIT, FORMAT, TO_HEX, MD5, SHA1, SHA256, TO_JSON_STRING, JSON_VALUE/JSON_EXTRACT_SCALAR, JSON_QUERY/JSON_EXTRACT, REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_REPLACE, CURRENT_TIMESTAMP/CURRENT_DATE/CURRENT_DATETIME, UNIX_SECONDS/UNIX_MILLIS/UNIX_MICROS/UNIX_DATE, TIMESTAMP_SECONDS/TIMESTAMP_MILLIS/TIMESTAMP_MICROS, TIMESTAMP_ADD/TIMESTAMP_SUB, DATE_ADD/DATE_SUB, *_DIFF, *_TRUNC, FORMAT_TIMESTAMP/FORMAT_DATETIME/FORMAT_DATE/FORMAT_TIME, PARSE_TIMESTAMP/PARSE_DATETIME/PARSE_DATE, DATE(...)/DATETIME(...)/TIMESTAMP(...), EXTRACT (including DAYOFWEEK, 1 = Sunday) and STRUCT(... AS name), including the typed STRUCT<name type, ...>(...) constructor. Functions with the same name and meaning in both dialects (COALESCE, IFNULL, LOWER, STRING_AGG, ARRAY_AGG, ROUND, and many more) pass through.
  • COUNTIF(DISTINCT condition) counts distinct TRUE results, returning zero for empty, false-only, or null-only inputs, following COUNTIF semantics.
  • PIVOT sources support implicit column aliases and HAVING references to struct select aliases.
  • CONTAINS_SUBSTR supports scalar STRING inputs with NFKC-normalized, case-folded substring matching. Recursive STRUCT/ARRAY search and the optional JSON scope argument are not emulated.
  • RAND returns a uniformly distributed value in the half-open interval [0, 1).
  • ARRAY_AGG(value IGNORE NULLS ORDER BY ... LIMIT n) preserves BigQuery's null filtering, aggregate ordering, and inline result limit. As in BigQuery, these aggregate modifiers are rejected when the call is followed by a window OVER clause.
  • ARRAY_CONCAT_AGG applies its inline LIMIT to input arrays: null arrays are skipped and empty arrays count as one input, following the GoogleSQL aggregate rules.
  • An alias on an explicit UNNEST([STRUCT(...)]) literal can select the whole record even when a field has the same name, following GoogleSQL value-table alias semantics.
  • Aliased UNNEST with WITH OFFSET exposes record fields and the offset in SELECT *, while the range alias retains the original record and alias.* expands only its fields. Correlated scalar subqueries can reference the range alias. Nested and mixed * REPLACE projections keep internal offset columns out of the result schema.
  • SELECT AS STRUCT combines expanded star fields and explicit expressions as sibling fields in one record, following GoogleSQL select semantics.
  • Array subscripts using OFFSET(index) preserve GoogleSQL's zero-based indexing.
  • HAVING can dereference a field of an explicit STRUCT select alias, including an aggregate field such as latest.pt where latest contains MAX(...).
  • TIMESTAMP_ADD and TIMESTAMP_SUB coerce GoogleSQL string timestamp literals before interval arithmetic.
  • SAFE.PARSE_DATE and SAFE.PARSE_TIMESTAMP return NULL rather than failing when their input does not match the supplied format string.
  • PARSE_TIMESTAMP and SAFE.PARSE_TIMESTAMP preserve a zone parsed from the input. The optional time zone argument applies when the format does not parse a zone, following GoogleSQL timestamp parsing.
  • SAFE.FROM_HEX decodes hexadecimal strings to BYTES, including odd-length strings with an implicit leading zero. Invalid hex returns NULL; errors evaluating the argument still propagate. See FROM_HEX and the SAFE prefix.
  • DATE_TRUNC accepts BigQuery-coerced date string literals as well as typed DATE values.
  • UNPIVOT supports the single-value value FOR name IN (column, ...) form used by analytical queries. It retains BigQuery's default EXCLUDE NULLS behavior. Multi-column sets and aliases are not emulated.
  • Unaliased expressions in the select list are named f0_, f1_, ... like BigQuery names them.
  • Result schemas carry real types: INTEGER, FLOAT, NUMERIC, BOOLEAN, STRING, BYTES, DATE, TIME, DATETIME, TIMESTAMP, JSON, RECORD (with nested fields) and REPEATED arrays.

INFORMATION_SCHEMA

Queries can read these views, generated from the emulator's dataset and table metadata, with the columns BigQuery documents:

View Qualifiers
SCHEMATA [project.]INFORMATION_SCHEMA.SCHEMATA (US region), [project.]`region-x`.INFORMATION_SCHEMA.SCHEMATA
TABLES, COLUMNS, COLUMN_FIELD_PATHS, TABLE_OPTIONS, VIEWS [project.]dataset.INFORMATION_SCHEMA.VIEW or [project.]`region-x`.INFORMATION_SCHEMA.VIEW
PARTITIONS [project.]dataset.INFORMATION_SCHEMA.PARTITIONS
  • A region qualifier covers every dataset whose location matches (region-us matches US); a dataset qualifier for a missing dataset is a 404. Without a qualifier, the request's defaultDataset is used. View names are case-sensitive, as in BigQuery.
  • data_type uses GoogleSQL names (INT64, ARRAY<STRING>, STRUCT<name STRING, ...>); COLUMN_FIELD_PATHS has one row per nested field path (customer.name).
  • ddl is a CREATE TABLE / CREATE VIEW / CREATE SCHEMA statement in BigQuery's format.
  • TABLE_OPTIONS reports description, friendly_name, labels and a set expiration_timestamp as GoogleSQL literals ("text", [STRUCT("key", "value")], TIMESTAMP "2020-01-16T21:12:28.000Z").
  • PARTITIONS reports one row with a null partition_id for every unpartitioned table, reports decorator-written ingestion-time partitions, and derives partitions from rows in field-partitioned tables, including __NULL__ and __UNPARTITIONED__. total_logical_bytes and total_billable_bytes are the size of the emulator's stored JSON rows, and last_modified_time is the table-level modification time.
  • Columns describing features the emulator does not model (clones, snapshots, replicas, identity and generated columns, policy tags) are present and NULL, NO or empty. COLUMNS omits async_generation_status and data_policies; COLUMN_FIELD_PATHS omits data_policies.
  • Other views (JOBS, ROUTINES, TABLE_STORAGE, ...) are not emulated.

Storage Read API

google.cloud.bigquery.storage.v1.BigQueryRead is served over gRPC on the emulator port (CreateReadSession, ReadRows, SplitReadStream), which is what to_dataframe() / to_arrow() use in Python when google-cloud-bigquery-storage is installed, and what BigQueryReadClient, Spark and Beam connectors call. Point the client at the emulator with a plaintext channel, for example in Python:

import grpc
from google.cloud import bigquery_storage_v1
from google.cloud.bigquery_storage_v1.services.big_query_read.transports import BigQueryReadGrpcTransport

storage = bigquery_storage_v1.BigQueryReadClient(
    transport=BigQueryReadGrpcTransport(channel=grpc.insecure_channel("localhost:4588")))
df = client.query("SELECT ...").to_dataframe(bqstorage_client=storage)

When google-cloud-bigquery-storage is installed, the Python client's to_dataframe() and to_arrow() create a Storage Read client for Google's real endpoint unless you pass one: use bqstorage_client=storage as above, or create_bqstorage_client=False to read over REST.

  • Formats: ARROW (Arrow IPC schema and record batch messages, from the DuckDB engine) and AVRO (Avro schema plus binary-encoded row blocks of up to 1000 rows). Types follow BigQuery's mapping: TIMESTAMP is a UTC microsecond timestamp, DATETIME a timestamp without time zone, NUMERIC decimal(38, 9), DATE date32; in Avro, nullable columns are unions with null, NUMERIC is bytes/decimal(38, 9), BIGNUMERIC decimal(77, 38), TIMESTAMP timestamp-micros.
  • Read options: selected_fields (top-level fields, returned in table order) and row_restriction (a GoogleSQL filter, run through the SQL engine). The restriction must be a single predicate over the table being read: subqueries, UNION, other tables, ; and comments are rejected with INVALID_ARGUMENT.
  • A session snapshots the table when it is created and has one stream; SplitReadStream returns an empty response ("the original stream can no longer be split"). ReadRows honors offset (for ARROW, at record batch boundaries), sends the schema in its first response and reports row_count and progress. Sessions expire after 6 hours. Each session holds a snapshot in memory, so at most 256 are kept; creating one past that drops the oldest, whose stream then returns NOT_FOUND.
  • Query results can be read from their anonymous destination table, as the SDKs do.
  • Tables are read from the project in read_session.table.

Not supported: views, nested selected_fields (struct.field), table_modifiers.snapshot_time, Arrow buffer compression, sample_percentage, and ARROW in mock mode (use AVRO). BIGNUMERIC columns come back as decimal(38, 9) in Arrow.

Storage Write API

google.cloud.bigquery.storage.v1.BigQueryWrite is served over gRPC on the emulator port (CreateWriteStream, AppendRows, GetWriteStream, FinalizeWriteStream, BatchCommitWriteStreams, FlushRows). This is what Java's JsonStreamWriter and StreamWriter, Python's AppendRowsStream, Go's managedwriter, and the Beam and Dataflow BigQuery sinks use. In Java, give the client a plaintext channel:

BigQueryWriteClient client = BigQueryWriteClient.create(BigQueryWriteSettings.newBuilder()
        .setTransportChannelProvider(InstantiatingGrpcChannelProvider.newBuilder()
                .setEndpoint("localhost:4588")
                .setChannelConfigurator(builder -> builder.usePlaintext())
                .build())
        .setCredentialsProvider(NoCredentialsProvider.create())
        .build());
try (JsonStreamWriter writer = JsonStreamWriter.newBuilder(
        "projects/my-project/datasets/ds/tables/t", client).build()) {
    writer.append(new JSONArray().put(new JSONObject().put("name", "ada"))).get();
}
  • Stream types:
  • The _default stream (.../tables/{table}/streams/_default or .../tables/{table}/_default) and COMMITTED streams make rows visible as soon as each append is acknowledged.
  • PENDING streams hold their rows until FinalizeWriteStream and then BatchCommitWriteStreams, which commits all listed streams atomically. It reports stream_errors (STREAM_NOT_FOUND, INVALID_STREAM_TYPE, INVALID_STREAM_STATE, STREAM_ALREADY_COMMITTED) and commits nothing when any stream fails.
  • BUFFERED streams make rows visible up to the offset given to FlushRows.
  • Offsets: on application-created streams, an append with an offset must match the next row of the stream. A lower offset returns ALREADY_EXISTS (OFFSET_ALREADY_EXISTS), a higher one OUT_OF_RANGE (OFFSET_OUT_OF_RANGE), with the "expected offset N, received M" message the Java client parses. The _default stream rejects offsets.
  • Rows are proto_rows with a writer_schema (a self-contained DescriptorProto) on the first request of a connection or when the destination changes. They are decoded with proto2 semantics, so an unset field is a missing value (NULL).
  • Fields match columns by name, case-insensitively, or by the column_name field option that the client libraries use for column names that aren't valid proto identifiers.
  • Field types follow BigQuery's supported protocol buffer types. For example: DATE is days since the epoch or a string; DATETIME and TIME are CivilTimeEncoder-packed int64 or a string; TIMESTAMP is epoch microseconds or google.protobuf.Timestamp; NUMERIC and BIGNUMERIC are BigDecimalByteStringEncoder bytes, a number or a string.
  • Arrow rows are arrow_rows: a serialized Arrow IPC schema message as writer_schema, then record batch messages, as Arrow Java's MessageSerializer and pyarrow's serialize() write them. Fields match columns by name, case-insensitively, and a null value is a missing value (a null list is an empty array).
  • Types follow BigQuery's supported Arrow types: Int for INTEGER, FloatingPoint (single or double) for FLOAT, Bool, Binary for BYTES, Utf8 for STRING, JSON and GEOGRAPHY, Date (day) or Utf8 or int32 for DATE, and Timestamp in microseconds with a time zone for TIMESTAMP or without one for DATETIME. Decimal128 is NUMERIC, Decimal256 is BIGNUMERIC, Interval or Utf8 is INTERVAL, List is a REPEATED column and Struct is RECORD.
  • TIME columns also accept Time64 in microseconds or Utf8, which BigQuery's table does not list.
  • Nanosecond timestamps, dictionary-encoded fields and compressed (LZ4, ZSTD) record batches are rejected with INVALID_ARGUMENT.
  • A connection sends either proto or Arrow rows; switching format needs a new writer_schema.
  • Errors: rejected appends come back as in-stream error responses with a StorageError detail, and the connection stays open.
  • A proto or Arrow field with no column is SCHEMA_MISMATCH_EXTRA_FIELDS.
  • An incompatible field type is INVALID_ARGUMENT.
  • A row that fails validation (for example, a missing REQUIRED field) rejects the whole request with row_errors.
  • Appending to a finalized stream is STREAM_FINALIZED.
  • GetWriteStream returns the table schema with the FULL view, and the stream's location.

Not supported: column default values (DEFAULT_VALUE missing values are NULL, since table schemas carry no default expressions), RANGE columns, and partition decorators. Stream state, including rows not yet committed or flushed, is kept in the configured storage mode, so with persistent storage a PENDING or BUFFERED stream survives a restart. The table also records how many of each stream's rows it holds, in the same write as the rows, so an append, flush or commit retried after a crash is not applied twice. POST /_floci-gcp/state/reset drops every write stream. The emulator's gRPC request limit is 4 MiB, below BigQuery's 10 MB AppendRows limit.

Load jobs

jobs.insert with configuration.load loads data into a table, synchronously:

  • Sources: sourceUris in floci's Cloud Storage (gs://bucket/object, with one * wildcard after the bucket), or a media upload to /upload/bigquery/v2/projects/{project}/jobs with uploadType=multipart or uploadType=resumable (what the SDKs' load_table_from_file, load_table_from_dataframe and bigquery.writer(...) use).
  • Formats: CSV (the default), NEWLINE_DELIMITED_JSON and PARQUET.
  • Schema: the job's schema, else the destination table's, else auto-detection (autodetect: true, CSV and JSON) or the Parquet file's own schema. Auto-detection infers INTEGER, FLOAT, BOOLEAN, TIMESTAMP, DATE, TIME, STRING, and RECORD / REPEATED for JSON; CSV header names have invalid characters replaced with underscores, and a CSV without a header gets generic names such as string_field_0. skipLeadingRows follows BigQuery's auto-detection rules (unset: detect a header; 0: no header; N: skip N-1 rows and detect a header in row N).
  • Partition decorators: a destination such as table$YYYYMMDD reuses the pre-created partitioned base table's schema and writes only the selected partition.
  • CSV options: skipLeadingRows, fieldDelimiter (including \t), quote, allowJaggedRows, nullMarker, encoding (UTF-8, ISO-8859-1) and maxBadRecords. Without a nullMarker, an empty STRING value stays an empty string and empty values of other types are NULL.
  • JSON with a schema is validated row by row exactly like tabledata.insertAll (ignoreUnknownValues, maxBadRecords, and a precise badRecords count); it needs no SQL engine, so it also works in mock mode.
  • Dispositions: writeDisposition (WRITE_APPEND by default for loads, WRITE_TRUNCATE, WRITE_TRUNCATE_DATA, WRITE_EMPTY) and createDisposition.
  • Results: statistics.load reports inputFiles, inputFileBytes, outputRows, outputBytes and badRecords. Data, schema and destination errors are reported in the DONE job's status.errorResult (invalid, notFound, duplicate).

Not supported: AVRO, ORC and DATASTORE_BACKUP sources, Hive partitioning, schemaUpdateOptions, the /resumable/upload/... path variant, and non-GCS sources. For CSV, maxBadRecords greater than zero skips unparsable rows without counting them.

DML and DDL

Statement Notes
INSERT [INTO] t [(cols)] VALUES ... / SELECT ... numDmlAffectedRows, dmlStats.insertedRowCount
UPDATE t SET ... [FROM ...] WHERE ... WHERE is required, as in BigQuery
DELETE [FROM] t WHERE ... WHERE is required
MERGE [INTO] t USING s ON ... WHEN [NOT] MATCHED ... dmlStats splits inserted, updated and deleted rows
TRUNCATE TABLE t dmlStats.deletedRowCount
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] t (col TYPE [NOT NULL] [OPTIONS(description=...)], ...) Column types keep their exact BigQuery type (BIGNUMERIC, GEOGRAPHY, ARRAY<...>, STRUCT<...>); supported time and integer-range PARTITION BY expressions, CLUSTER BY fields, and table options populate metadata. BigQuery's remaining table options are accepted: require_partition_filter, partition_expiration_days and kms_key_name are stored, the rest are ignored
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] t [PARTITION BY ...] [CLUSTER BY ...] [OPTIONS(...)] AS SELECT ... Schema and rows come from the query; field-based time and integer-range PARTITION BY expressions, CLUSTER BY fields, and supported table options populate metadata; ingestion-time partitioning is rejected. BigQuery's remaining table options are accepted as on a plain CREATE TABLE
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] t COPY source [OPTIONS(...)] Copies a table, clone, or snapshot in the same job location; views are rejected. Trailing options override inherited metadata: description, friendly_name, labels, and expiration_timestamp, using the same literal values and NULL clearing as ALTER TABLE SET OPTIONS. Other options and computed expressions are unsupported
CREATE [OR REPLACE] [MATERIALIZED] VIEW [IF NOT EXISTS] v AS SELECT ...
DROP TABLE / VIEW / MATERIALIZED VIEW [IF EXISTS] Dropping a view with DROP TABLE (or the reverse) is rejected
ALTER TABLE / VIEW [IF EXISTS] t SET OPTIONS(...) Sets description, friendly_name, labels and expiration_timestamp, or clears one with NULL. expiration_timestamp accepts a timestamp literal or a timestamp-valued SQL expression, evaluated by the configured SQL engine. Only the listed options change, and labels replaces the old labels. Any other option fails with Unknown option. Altering a view with ALTER TABLE (or the reverse) is rejected, and IF EXISTS skips a missing or expired table
CREATE SCHEMA [IF NOT EXISTS] d, DROP SCHEMA [IF EXISTS] d [CASCADE \| RESTRICT] Datasets; a non-empty dataset needs CASCADE. CASCADE/RESTRICT are rejected on DROP TABLE/VIEW

DDL jobs report statistics.query.statementType, ddlOperationPerformed (CREATE, REPLACE, ALTER, SKIP, DROP) and ddlTargetTable / ddlTargetDataset. DML and DDL results have no rows, so getQueryResults returns totalRows: "0" and the SDKs return an empty result. CREATE OR REPLACE TABLE must preserve the existing partitioning kind, field, granularity and range specification. DML that writes _PARTITIONTIME requires an exact partition boundary from 1970-01-01 through 2159-12-31.

COPY ... OPTIONS uses the override semantics in the CREATE TABLE COPY reference. The supported option subset also works in dry runs without writing table metadata or rows.

DML needs a floci-duck image that supports followup_sql (see floci-duck). Table, dataset and plain CREATE TABLE / DROP / TRUNCATE statements run without the SQL engine, so they also work in mock mode.

Errors: invalid SQL via jobs.query returns HTTP 400 with reason invalidQuery; via jobs.insert it returns an HTTP 200 DONE job carrying status.errorResult (and getQueryResults on that job returns HTTP 400), matching real job semantics. Error messages come from DuckDB, so their wording differs from BigQuery's.

Job locations

A job runs in one location, and every dataset it reads or writes must live there:

  • jobReference.location (jobs.insert) or location (jobs.query) sets the job's location. When it is absent the job takes the location of the datasets it references, then of its destination table, and US when it has neither. Locations compare case-insensitively (US and us are the same), but a multi-region and a region inside it are different locations (US is not us-central1).
  • A dataset in another location returns 404 with reason notFound and the message Not found: Dataset {project}:{dataset} was not found in location {LOCATION}: an HTTP error from jobs.query, a DONE job carrying status.errorResult from jobs.insert. A job that touches datasets from two locations fails the same way. Dry runs report the same error.
  • What is compared:
    • SELECT: every dataset it reads, and the dataset of configuration.query.destinationTable.
    • INSERT, UPDATE, DELETE, MERGE: the target's dataset and every dataset they read.
    • TRUNCATE TABLE, ALTER TABLE, ALTER VIEW, DROP TABLE, DROP VIEW: the target's dataset, also when IF EXISTS finds no such table.
    • CREATE TABLE and CREATE VIEW, in every form: the new table's dataset, also when IF NOT EXISTS skips an existing table, and every dataset the query or COPY source reads.
    • DROP SCHEMA: the dataset being dropped.
    • Load jobs: the destination table's dataset.
    • Copy jobs: the source datasets. A destination in another location is a cross-region copy.
  • A dataset that does not exist has nothing to compare, so the statement's own error applies, or its IF EXISTS skip.
  • CREATE SCHEMA is not compared with anything: it creates the dataset in the job's location. It ignores OPTIONS, including location. IF NOT EXISTS skips an existing dataset in any location.
  • A job that fails in jobs.insert is recorded in the location it had resolved to, so the failed job is found there.
  • A dataset-qualified INFORMATION_SCHEMA view (eu_ds.INFORMATION_SCHEMA.TABLES) is checked like a table of that dataset. A region-qualified view (region-eu.INFORMATION_SCHEMA.TABLES) sets the location when nothing else has, but a region that differs from the job's location is not rejected.
  • jobs.get, jobs.getQueryResults, jobs.cancel and jobs.delete honor the location query parameter: a job in another location is not found (404 notFound, Not found: Job {project}:{LOCATION}.{jobId}). Without the parameter the job is found by id.

Mock mode

With FLOCI_GCP_SERVICES_BIGQUERY_MOCK=true no container is started and queries run on a small built-in subset, useful where Docker is not available:

query     := SELECT selection FROM table_ref [WHERE predicate {AND predicate}] [LIMIT int] [;]
selection := * | COUNT ( * ) | column {, column}
table_ref := [project .] dataset . table | table        (backtick-quoted forms accepted)
predicate := column = literal
literal   := 'string' | "string" | integer | float | TRUE | FALSE
  • An unqualified table requires the request's defaultDataset.
  • Typed equality per column: INTEGER/FLOAT/BOOLEAN/STRING; comparing with a mismatched literal type → 400 invalidQuery ("No matching signature for operator =").
  • COUNT(*) returns a single INTEGER column named f0_ and respects WHERE.
  • Anything else (JOIN, GROUP BY, ORDER BY, OR, !=, <, functions, aliases, subqueries, DML/DDL, = NULL), and any query with parameters, fails fast with 400 and reason invalidQuery naming the construct: never silent divergence.

Type support

Legacy type Accepted insertAll values Wire encoding
STRING string, number, boolean as-is
INTEGER (INT64) integral number, decimal string decimal string
FLOAT (FLOAT64) number, decimal string decimal string
BOOLEAN (BOOL) boolean, "true"/"false" "true"/"false"
RECORD (STRUCT) object (validated against nested fields) {f:[...]}
any + REPEATED mode array of the base type array of {v}
other (TIMESTAMP, DATE, ...) stored/echoed textually as-is

Deviations from real BigQuery

  • A window in ORDER BY, HAVING or QUALIFY accepts a name BigQuery resolves to a unique select alias, but DuckDB binds that name to a source column of the same name when one exists, so a window whose name shadows a source column can order differently. Ambiguity checks follow BigQuery.
  • Struct-star EXCEPT / REPLACE translation does not cover every combination allowed by the GoogleSQL star-list rules and correlated subquery semantics: a sibling unqualified * beside a struct star can fail with invalidQuery.
  • A star over a join that mixes USING with ON or comma joins, or an UNPIVOT combined with a USING join, is not positionally rewritten, so a repeated input name can keep DuckDB's generated name and order. A * EXCEPT over PIVOT output whose generated column names cannot be derived from literal IN values and aggregate aliases is not validated.
  • Scripts support nested BEGIN blocks, IF, searched/simple CASE, scalar variables, and WHILE, LOOP, REPEAT, and FOR with labels and loop control. Full GoogleSQL variable type and expression semantics, exception handlers, transactions, JavaScript UDFs, persistent CREATE FUNCTION / PROCEDURE, EXPORT DATA, LOAD DATA, ALTER TABLE, and ALTER VIEW inside scripts are not supported; they fail with invalidQuery, and so do the ASSERT and RETURN statements, which a script here cannot run yet. Dynamic SQL goes through EXECUTE IMMEDIATE and is supported only in the shapes listed above. A BIGNUMERIC variable is not supported: DuckDB's DECIMAL(38,9) is BigQuery's NUMERIC precision and cannot hold its 76 digits. Rejections use floci's own wording in a few places where BigQuery adds a position: Query error: Cannot coerce expression 1 to type BOOL for BigQuery's Query error: Cannot coerce expression 1 to type BOOL at [1:4]. A rejection of a shape BigQuery words differently keeps floci's own wording.
  • A NUMERIC variable refuses a FLOAT64 value, apart from two shapes that still store one: a NUMERIC field of a STRUCT default accepts a FLOAT64 value, STRUCT(f AS n), where BigQuery coerces a STRUCT field only when the value is a literal; and arithmetic over floating point literals stays NUMERIC here, so SET n = 1.5 + 0.5 stores 2, where BigQuery types the expression FLOAT64 and refuses it. The last one follows from a bare decimal literal being NUMERIC here and FLOAT64 in BigQuery, which holds outside scripts too: SELECT 1.5 reports a NUMERIC column.
  • A reserved keyword used as a loop or block label runs here, where BigQuery refuses it with Reserved keyword 'outer' may not be used as a label name without backticks.
  • An unknown name reports DuckDB's wording rather than BigQuery's: a query that names no such column answers Binder Error: Referenced column "x" was not found ..., where BigQuery answers Unrecognized name: x at [1:8]. The same holds for the other errors DuckDB raises while a statement runs, such as a NOT NULL temporary column receiving a NULL, which answers Constraint Error: NOT NULL constraint failed: t.id where BigQuery answers Required field id cannot be null.
  • Values a script needs at validation time are what a dry run cannot supply: a script dry run stops at the first variable or control-flow statement and after the first DDL statement, where BigQuery validates every statement of the script. A dry run whose script declares a temporary table reports the last statement's schema, where BigQuery reports none.
  • Script statements execute synchronously within the parent job. Separate child jobs are not created or returned by jobs.list.
  • Outside scripts, ALTER TABLE and ALTER VIEW support only SET OPTIONS.
  • DML statements are applied atomically per statement by reading and rewriting the whole target table; there is no fine-grained DML or streaming-buffer interaction.
  • CLUSTER BY is stored as table metadata but does not physically cluster rows. Plain CREATE TABLE and CREATE TABLE AS SELECT support the table options description, friendly_name, labels, and expiration_timestamp; timestamp-valued expiration expressions are evaluated by the configured SQL engine. They also accept BigQuery's remaining table options: require_partition_filter, partition_expiration_days and kms_key_name are stored in the Table resource (requirePartitionFilter, timePartitioning.expirationMs and encryptionConfiguration.kmsKeyName), while default_rounding_mode, enable_change_history, max_staleness, enable_fine_grained_mutations, storage_uri, file_format, table_format and tags are accepted and ignored. An expression value for an option the emulator does not evaluate leaves the option unset. ALTER TABLE SET OPTIONS and CREATE TABLE COPY still reject these options, as before. CREATE TABLE COPY applies the option subset documented above. Time-based PARTITION BY metadata is stored for the supported expressions documented above.
  • Option values in ALTER TABLE / ALTER VIEW SET OPTIONS and CREATE TABLE COPY are evaluated without BigQuery's limits on the functions they call. BigQuery rejects aggregate functions (Aggregate function MAX not allowed in option), analytic functions and some scalar functions such as LPAD and RAND (Found unsupported function call); floci evaluates them. Other scalar functions, including REPLACE, FORMAT and UPPER, work in both.
  • Legacy SQL (useLegacySql: true) is rejected. A request that omits useLegacySql runs as GoogleSQL (the API default is legacy SQL, but every SDK sends false).
  • SELECT * FROM UNNEST(array) expands STRUCT elements into columns; with an alias (UNNEST(array) AS x) the element is one column x.
  • INFORMATION_SCHEMA views other than the seven above, non-terminal wildcard table patterns, FOR SYSTEM_TIME AS OF, GEOGRAPHY functions, BigQuery ML, and remote functions are not emulated.
  • Most SAFE.-prefixed functions are not emulated. SAFE_CAST, SAFE.FROM_HEX, SAFE.PARSE_DATE, and SAFE.PARSE_TIMESTAMP are supported explicitly.
  • FORMAT_TIMESTAMP formats in UTC and ignores its time zone argument, so %z and %Ez print +0000 and +00:00. PARSE_DATE, PARSE_DATETIME and PARSE_TIMESTAMP need a constant format (a literal or a query parameter): DuckDB's strptime takes no format read from a column. FORMAT_* accepts a format read from a column.
  • A bare subscript [i] on an ARRAY reached through a field path, such as CAST(STRUCT([1, 2] AS a) AS STRUCT<a ARRAY<INT64>>).a[1], is read as DuckDB's one-based subscript (1 here, where BigQuery returns 2): the translator does not know the field is an array. Write [OFFSET(i)] or [ORDINAL(i)] there.
  • A typed STRUCT<name type, ...>(...) constructor casts each value to its declared type, where BigQuery coerces it at analysis time: a value that does not coerce, such as STRUCT<n INT64>('abc'), fails while the query runs with DuckDB's Could not convert string 'abc' to INT64 (BigQuery: Struct field 1 has type STRING which does not coerce to INT64), and one BigQuery rejects but DuckDB can cast, such as STRUCT<n INT64>('1'), succeeds. A value for a nested STRUCT<...> field must use the declared field names, since DuckDB casts structs by name where BigQuery matches them by position.
  • A STRING query parameter shaped like a timestamp is coerced to DATE, DATETIME or TIMESTAMP when compared with one, with an uppercase T and Z, UTC or a numeric offset, and to TIMESTAMP with a time zone name (America/Los_Angeles, EST5EDT), as in BigQuery. A lowercase t or z (2014-09-27t12:30:00.45, 2014-09-27 12:30:00.45z) is coerced when the other side of the comparison or BETWEEN is temporal as written: a typed literal, a temporal function or CAST, or a table column of that type. There the value is also checked as BigQuery checks it, with BigQuery's message: a DATE takes a date alone (Invalid date: '2014-09-27 12:30:00'), a DATETIME no zone (Invalid datetime string "2014-09-27T12:30:00Z"), and a TIMESTAMP needs the seconds of any time and a zone name it knows, written in its own case (Invalid timestamp: '2014-09-27T12:30', Invalid time zone: utc). DATE_TRUNC, whose type follows its argument, an operand in + or - arithmetic (DATE '2014-09-27' + INTERVAL 1 DAY is a DATETIME), and a parameter tested itself (@a BETWEEN x AND y) are not checked. Compared with anything else (an UNNEST element, a subquery column) a lowercase t or z value fails with Cannot compare values of type TIMESTAMP WITH TIME ZONE and type VARCHAR, where BigQuery accepts it, and any other value is cast as DuckDB casts it: a time part or zone is dropped for a DATE or DATETIME, and an unknown zone name fails with DuckDB's Unknown TimeZone.
  • ORDER BY sorts a FLOAT64 NaN just after NULL (ascending) as BigQuery does, through an extra sort key that is added only when the query visibly can hold a NaN (a 'NaN' string, IEEE_DIVIDE, a FLOAT64 parameter, or a table with a FLOAT64 column read right after FROM or JOIN). NaN still sorts last, as DuckDB orders it, when that key is left out: in a query with a set operation, in a query that uses GROUP BY ALL, in a window with a RANGE frame (also a named window an OVER (w RANGE ...) extends), in a DISTINCT aggregate, for an ordinal key (ORDER BY 1), for a key holding a positional parameter, for a select alias whose expression holds a window function, RAND() or GENERATE_UUID(), and when the only FLOAT64 column comes from the second table of a comma join (FROM a, b), which is not looked at.
  • NUMERIC and BIGNUMERIC are computed as DECIMAL(38, 9).
  • TIMESTAMP values are staged as ISO-8601 strings for DuckDB, including values nested inside RECORD or REPEATED columns; the BigQuery wire format continues to use epoch seconds.
  • totalBytesProcessed is an estimate from the size of the tables a query reads.
  • No insertId de-duplication (real BigQuery is best-effort anyway).
  • Jobs always complete synchronously (jobComplete=true, state DONE), with no PENDING/RUNNING phase.
  • Cross-project table references are rejected.
  • Anonymous result tables persist until jobs.delete; they are hidden from dataset listings.

Quick smoke (curl)

B=http://localhost:4588/bigquery/v2/projects/demo
curl -sX POST $B/datasets -H 'Content-Type: application/json' \
  -d '{"datasetReference":{"datasetId":"ds1"}}'
curl -sX POST $B/datasets/ds1/tables -H 'Content-Type: application/json' \
  -d '{"tableReference":{"tableId":"t1"},"schema":{"fields":[{"name":"name","type":"STRING"},{"name":"age","type":"INT64"}]}}'
curl -sX POST $B/datasets/ds1/tables/t1/insertAll -H 'Content-Type: application/json' \
  -d '{"rows":[{"json":{"name":"ana","age":30}},{"json":{"name":"bo","age":41}}]}'
curl -sX POST $B/queries -H 'Content-Type: application/json' \
  -d '{"query":"SELECT name, age FROM ds1.t1 WHERE age > @min ORDER BY age DESC","useLegacySql":false,
       "queryParameters":[{"name":"min","parameterType":{"type":"INT64"},"parameterValue":{"value":"18"}}]}'