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 (
deleteContentshonored; deleting a non-empty dataset without it returns 400resourceInUse). Duplicate create returns 409duplicate. - 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) withNULLABLEas the default mode, matching SDK round-trips. - tabledata.insertAll: schema-validated per row: HTTP 200 always, failures reported via
insertErrors(unknown fields honorignoreUnknownValues;skipInvalidRows=falseinserts nothing and marks valid rowsstopped; missingREQUIREDfields and uncoercible values are rejected with reasoninvalid). - tabledata.list:
{f:[{v:"..."}]}row encoding (scalars as strings,REPEATEDas arrays of{v},RECORDas nested{f:[...]}),maxResults/pageToken/startIndexpaging.maxResults=0returns zero rows (the SDK'sJob.waitFor()contract); apageTokenwins overstartIndex(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'sconfiguration.query.destinationTable, which is how the SDK'sJob.getQueryResults()reads rows. - Copy jobs:
configuration.copyaccepts eithersourceTableor non-emptysourceTables, plusdestinationTable. OnlyoperationTypeCOPY,OPERATION_TYPE_UNSPECIFIED(treated as unset), or an omitted operation type is supported;SNAPSHOT,RESTOREandCLONEare 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 supportsCREATE_IF_NEEDED/CREATE_NEVERandWRITE_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_startfor 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 invaliddestinationExpirationTimevalues return HTTP 400 with reasoninvalidbefore a job ID is reserved, even when the destination already exists. Resource and copy-compatibility errors remain completed jobs withstatus.errorResult. Successful jobs reportstatistics.copy.copiedRowsas 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:
parameterModeNAMED(@name) andPOSITIONAL(?), with scalar,ARRAYandSTRUCTparameter types. - Scripts and temporary objects: request-scoped
CREATE TEMP FUNCTIONandCREATE TEMP TABLEdeclarations can be interleaved with queries in source order. Functions become temporary DuckDB macros; typed scalar, array and nestedSTRUCTparameters 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, andAS (SELECT ...)answers BigQuery'sThe body of each CREATE FUNCTION statement is an expression, not a query. Temporary tables accept an explicit schema, anASquery, or both, and can be explicitly addressed through the_SESSIONqualifier; a_SESSIONname that was not declared answersNot found: Dataset <project>:_SESSION was not found, as BigQuery does.CREATE TEMP TABLE IF NOT EXISTSskips a temporary table that already exists andCREATE OR REPLACE TEMP TABLEreplaces one, whereCREATE TEMP FUNCTIONrejects a clause such asIF NOT EXISTSorOR REPLACE:Clauses such as IF NOT EXISTS are not yet supported with CREATE TEMP FUNCTION.OR REPLACEafter the object name answersSyntax error: Unexpected keyword OR, as BigQuery's grammar takes it beforeTEMP. A second declaration of the same temporary table answersAlready Exists: Table <project>:_SESSION.<name>, as BigQuery answersAlready Exists: Table .... Results from preceding statements are discarded; the final query or DML statement supplies the result. A script reports no DML counts,numDmlAffectedRowsordmlStats, 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. AFORloop 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 asDECLARE x INT64;, runs as a script, as BigQuery runs it. ProceduralBEGIN/ENDblocks can be nested and contain supported statements orIF/ELSEIF/ELSEblocks; their statements execute in source order using the same temporary objects. Searched and simpleCASEstatements run the first matching branch, with optionalELSE.DECLARE/SETsupportsINT64,BOOL,STRING,FLOAT64,NUMERIC,DATE,TIMESTAMP,BYTES,DATETIME,TIME,JSON,STRUCT, and arrays of those non-array types, includingARRAY<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, soDECLARE x INT64 DEFAULT 1.9answersQuery error: Cannot coerce expression 1.9 to type INT64. AFLOAT64literal stays floating point, sof + 0.2overDECLARE f FLOAT64 DEFAULT 0.1answers0.30000000000000004, as BigQuery does, and the operands type the arithmetic, soSET f = 9007199254740993 - 9007199254740992stores the exact1.0and anINT64overflow fails even when the target isFLOAT64. A typed array literal keeps its element type, soDECLARE xs DEFAULT ARRAY<FLOAT64>[]declares an emptyARRAY<FLOAT64>, and aSTRUCTvalue is assigned by field position, soSTRUCT(1 AS b, 2 AS a)reachesSTRUCT<a INT64, b INT64>asa = 1, b = 2. ANUMERICvariable takes a floating point literal as written, soDECLARE n NUMERIC DEFAULT 1.5stores1.5, and refuses aFLOAT64value with BigQuery's wording, soDECLARE f FLOAT64 DEFAULT 2.5; DECLARE n NUMERIC DEFAULT fanswersQuery error: Cannot coerce expression f to type NUMERIC. An array takes only its own array type, since BigQuery coerces no array:[1, 2]is anARRAY<INT64>, so it answersQuery error: Cannot coerce expression [1, 2] to type ARRAY<NUMERIC>,[1.5]is anARRAY<FLOAT64>and answers the same wording, and a typed literal must name the target's element type,ARRAY<FLOAT64>[1.5]andARRAY<INT64>[1]answering the same. Inside a typedARRAY<NUMERIC>a floating point literal is an element, soARRAY<NUMERIC>[1.5]stores1.5, while aFLOAT64value answersArray element type FLOAT64 does not coerce to NUMERIC, andARRAY<INT64>[1.9]the same forINT64.EXECUTE IMMEDIATErefuses a result of another type the same way, soINTOover aNUMERICvariable and aFLOAT64result answersValue 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, aFROMitem's alias (a table,UNNESTor 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 anINSERTsupplies 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, soDECLARE v INT64 DEFAULT 1; CREATE TEMP TABLE t AS SELECT 4 AS v; SET v = v + (SELECT COUNT(*) FROM t)stores2: the column answers4inside the subquery, and the variable thevaround it, whileSELECT COUNT(*) FROM t UNION ALL SELECT vanswers1and the variable's1. AWITHclause declares its CTE for every branch of a set operation, soWITH t AS (SELECT 4 AS v) SELECT 0 UNION ALL SELECT v FROM tanswers0and4, and a nestedWITHof the same name shadows the CTE of the query around it, soWITH t AS (SELECT 4 AS v) SELECT 0 UNION ALL SELECT (WITH t AS (SELECT 2 AS w) SELECT v FROM t)answers0and the variable's1. The expression aSETorDECLAREdefault evaluates and the condition of anIF,WHILEorREPEATcollect 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)andSELECT `x`answering a column namedxwhileSELECT f + 0.2is namedf0_, as BigQuery names it; so are a function argument such asSELECT COALESCE(NULL, f)and a grouped expression such asSELECT (f) + 1. A sort or group key takes the variable's value, which BigQuery reads as a constant, soORDER BY fneither renames the item nor changes the order. A field of aJSONvariable is named after its last field,SELECT j.a.banswering columnb, where the subscriptSELECT j['a']keeps the generated name, as BigQuery keeps it. A script variable inLIMITorOFFSETis rejected,Query error: LIMIT expects an integer literal or parameter, as BigQuery rejects it, and a query parameter is accepted there; theOFFSET(i)array subscript is an expression, so a variable is read there.OFFSETis not reserved, so a variable may be namedoffset; theWITH OFFSETof anUNNESTstays the clause.EXECUTE IMMEDIATEsupports a single query, supported DDL or DML statement, orBEGINblock.USINGbinds named or positional query parameters, andINTOassigns a zero- or one-row result to declared variables. SQL-text expressions can use outer script variables. The dynamic statement itself only seesUSINGvalues, not outer script variables or request query parameters; nestedEXECUTE IMMEDIATEis rejected, and the SQL it supplies is validated before any of it runs, so an invalid block writes nothing.WHILEsupports simple scalar comparisons with a 1,000-iteration safety limit.BEGINblocks scope variables. Full GoogleSQL expression and type semantics are not supported. Labels are supported onBEGINblocks and loops, including labeledBREAK/LEAVEandCONTINUE/ITERATE;CONTINUEcannot target a block.BEGINblocks do not provide transaction semantics, and transactions and exception handlers are not supported. ProceduralIFblocks 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 … INevaluates 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. ACREATE TEMP TABLEwhose declared schema makes a fieldNOT NULLrejects 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:
dryRunonjobs.queryorconfiguration.dryRunonjobs.insertvalidates the query and returns its result schema (statistics.query.schemaon the job) andtotalBytesProcessed, 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 itsCREATE TEMP TABLEdeclarations 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 aDECLAREand in unselected branches. - Destination tables:
jobs.inserthonorsconfiguration.query.destinationTablewithcreateDisposition(CREATE_IF_NEEDEDdefault,CREATE_NEVER) andwriteDisposition(WRITE_EMPTYdefault,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_APPENDandWRITE_EMPTYapply to that partition. Queries over an ingestion-time-partitioned base table materialize_PARTITIONTIMEfor stored rows and_PARTITIONDATEfor daily partitions, so they can filter or explicitly project a selected partition. These pseudocolumns are not expanded bySELECT *. 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.useInt64TimestampandformatOptions.timestampOutputFormat(FLOAT64,INT64,ISO8601_STRING) are honored onjobs.query,getQueryResultsandtabledata.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
defaultTableExpirationMsasexpirationTime, and new time-partitioned tables inheritdefaultPartitionExpirationMsastimePartitioning.expirationMs(and then no table expiration). An explicit value on the table wins.defaultTableExpirationMs: 0in a datasetPATCHclears the default. - A table past its
expirationTimeis deleted when it is next read or listed. - Output fields are filled in: dataset
type(LINKEDwith alinkedDatasetSource,EXTERNALwith anexternalDatasetReference, otherwiseDEFAULT),location(USwhen not given) andmaxTimeTravelHours(168when not set); tablelocation(the dataset's),numLongTermBytesandselfLink. Tables created withview,materializedVieworexternalDataConfigurationget the matchingtype. - Validation:
defaultTableExpirationMsof at least one hour,maxTimeTravelHoursfrom 48 to 168, anexpirationTimethat parses as an int64,timePartitioning.typeone ofDAY/HOUR/MONTH/YEARwith a positiveexpirationMs, and not both time and range partitioning. A time partition field must be a non-repeatedDATE,DATETIMEorTIMESTAMPfield, withHOURunavailable forDATE. A range partition needs a non-repeatedINTEGERfield and integerstart,endand positiveintervalvalues._PARTITIONDATEand_PARTITIONTIMEare reserved schema names. Violations return 400 with reasoninvalid, 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 correlatedLEFT JOIN UNNEST(array) alias),GROUP BY,HAVING,ORDER BY,LIMIT/OFFSET,DISTINCT,UNION/INTERSECT/EXCEPT(ALLandDISTINCT), subqueries,WITH(CTEs), window functions,QUALIFY,PIVOT,UNPIVOT,UNNEST(includingWITH OFFSET,FROM t, t.array_column AS x, andx IN UNNEST(...)),SELECT * EXCEPT (...), andSELECT * REPLACE (...).SELECT * EXCEPT (...)andSELECT * REPLACE (...)also apply to aSTRUCTexpression (t.s.* EXCEPT (a),t.s.* REPLACE (expr AS field)), in the outermost query, a CTE or a subquery. The same item keeps BigQuery'sDISTINCT,LIMIT/OFFSETandORDER BYbehavior, a positionalGROUP BYandGROUP BY ALLcount 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 asARRAY_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 sourceSTRUCTcolumn (ORDER BY u.n). A plaint.s.*with no star list groups by position orGROUP BY ALLas BigQuery does. Replacement expressions read the original query's sources, including correlated columns. A bare name in a replacement, such as anUNNESTrange variable, itsWITH OFFSETalias or a column of a subquery without an alias, reads theFROMrow, 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,REPLACErejects 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 keepWHEREin source scope, separate from post-aggregationHAVINGandQUALIFY. Field lookup preserves source aliases and complete nested paths, including stored table schemas, when joined sources expose same-named struct columns. A correlatedSELECT AS STRUCT input.* EXCEPT (id)hides_PARTITIONDATEand_PARTITIONTIMEin any query whoseFROMprovidesinput.- A duplicate output name takes BigQuery's
_1suffix in the result schema, including a sibling that collides with an expanded field under a positionalGROUP BYorGROUP 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 BYexpressions,ORDER BY,HAVING,QUALIFYand a window in the select list), and a suffix generated for a duplicate is unrecognized unless a source column carries that name. A bareGROUP BYname resolves a unique output alias first, while a qualified item such asGROUP BY t.bstill names its source alias. A window in the select list reads input columns rather than sibling aliases, and a window inORDER BY,HAVINGorQUALIFYresolves a unique output alias first, as the rest of that clause does. A star'sEXCEPTlist drops every output written with the name across the query's sources, including columns merged by aUSINGjoin, and aREPLACEon the same star is still applied to the surviving outputs.REPLACErejects a name it would replace more than once. A bare star over anUNPIVOTsource, including eachUNPIVOTthat decorates a source of a join, returns the surviving source columns, then the value columns, then the name column, as BigQuery does. A star'sEXCEPTandREPLACEoverPIVOToutput resolve against the pivot's generated column names, andPIVOTandUNPIVOTreject a duplicated name they reference. ACREATE TABLEorCREATE VIEWwhose 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_SUFFIXfor filtering. - GoogleSQL literals: double-quoted and triple-quoted strings, raw strings (
r'...'), bytes literals,TIMESTAMP/DATETIME/DATE/TIME/NUMERIC/JSONtyped literals, and typed array literals includingARRAY<STRUCT<...>>[...]. CAST/SAFE_CASTwith GoogleSQL type names, includingARRAY<...>andSTRUCT<...>.- 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(includingDAYOFWEEK, 1 = Sunday) andSTRUCT(... AS name), including the typedSTRUCT<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 distinctTRUEresults, returning zero for empty, false-only, or null-only inputs, following COUNTIF semantics.PIVOTsources support implicit column aliases andHAVINGreferences to struct select aliases.CONTAINS_SUBSTRsupports scalarSTRINGinputs with NFKC-normalized, case-folded substring matching. RecursiveSTRUCT/ARRAYsearch and the optional JSON scope argument are not emulated.RANDreturns 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 windowOVERclause.ARRAY_CONCAT_AGGapplies its inlineLIMITto 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
UNNESTwithWITH OFFSETexposes record fields and the offset inSELECT *, while the range alias retains the original record andalias.*expands only its fields. Correlated scalar subqueries can reference the range alias. Nested and mixed* REPLACEprojections keep internal offset columns out of the result schema. SELECT AS STRUCTcombines 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. HAVINGcan dereference a field of an explicitSTRUCTselect alias, including an aggregate field such aslatest.ptwherelatestcontainsMAX(...).TIMESTAMP_ADDandTIMESTAMP_SUBcoerce GoogleSQL string timestamp literals before interval arithmetic.SAFE.PARSE_DATEandSAFE.PARSE_TIMESTAMPreturnNULLrather than failing when their input does not match the supplied format string.PARSE_TIMESTAMPandSAFE.PARSE_TIMESTAMPpreserve 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_HEXdecodes hexadecimal strings toBYTES, including odd-length strings with an implicit leading zero. Invalid hex returnsNULL; errors evaluating the argument still propagate. See FROM_HEX and the SAFE prefix.DATE_TRUNCaccepts BigQuery-coerced date string literals as well as typedDATEvalues.UNPIVOTsupports the single-valuevalue FOR name IN (column, ...)form used by analytical queries. It retains BigQuery's defaultEXCLUDE NULLSbehavior. 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) andREPEATEDarrays.
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-usmatchesUS); a dataset qualifier for a missing dataset is a 404. Without a qualifier, the request'sdefaultDatasetis used. View names are case-sensitive, as in BigQuery. data_typeuses GoogleSQL names (INT64,ARRAY<STRING>,STRUCT<name STRING, ...>);COLUMN_FIELD_PATHShas one row per nested field path (customer.name).ddlis aCREATE TABLE/CREATE VIEW/CREATE SCHEMAstatement in BigQuery's format.TABLE_OPTIONSreportsdescription,friendly_name,labelsand a setexpiration_timestampas GoogleSQL literals ("text",[STRUCT("key", "value")],TIMESTAMP "2020-01-16T21:12:28.000Z").PARTITIONSreports one row with a nullpartition_idfor every unpartitioned table, reports decorator-written ingestion-time partitions, and derives partitions from rows in field-partitioned tables, including__NULL__and__UNPARTITIONED__.total_logical_bytesandtotal_billable_bytesare the size of the emulator's stored JSON rows, andlast_modified_timeis 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,NOor empty.COLUMNSomitsasync_generation_statusanddata_policies;COLUMN_FIELD_PATHSomitsdata_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) andAVRO(Avro schema plus binary-encoded row blocks of up to 1000 rows). Types follow BigQuery's mapping:TIMESTAMPis a UTC microsecond timestamp,DATETIMEa timestamp without time zone,NUMERICdecimal(38, 9),DATEdate32; in Avro, nullable columns are unions withnull,NUMERICisbytes/decimal(38, 9),BIGNUMERICdecimal(77, 38),TIMESTAMPtimestamp-micros. - Read options:
selected_fields(top-level fields, returned in table order) androw_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 withINVALID_ARGUMENT. - A session snapshots the table when it is created and has one stream;
SplitReadStreamreturns an empty response ("the original stream can no longer be split").ReadRowshonorsoffset(forARROW, at record batch boundaries), sends the schema in its first response and reportsrow_countand 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 returnsNOT_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
_defaultstream (.../tables/{table}/streams/_defaultor.../tables/{table}/_default) andCOMMITTEDstreams make rows visible as soon as each append is acknowledged. PENDINGstreams hold their rows untilFinalizeWriteStreamand thenBatchCommitWriteStreams, which commits all listed streams atomically. It reportsstream_errors(STREAM_NOT_FOUND,INVALID_STREAM_TYPE,INVALID_STREAM_STATE,STREAM_ALREADY_COMMITTED) and commits nothing when any stream fails.BUFFEREDstreams make rows visible up to the offset given toFlushRows.- Offsets: on application-created streams, an append with an
offsetmust match the next row of the stream. A lower offset returnsALREADY_EXISTS(OFFSET_ALREADY_EXISTS), a higher oneOUT_OF_RANGE(OFFSET_OUT_OF_RANGE), with the "expected offset N, received M" message the Java client parses. The_defaultstream rejects offsets. - Rows are
proto_rowswith awriter_schema(a self-containedDescriptorProto) 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_namefield 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:
DATEis days since the epoch or a string;DATETIMEandTIMEareCivilTimeEncoder-packedint64or a string;TIMESTAMPis epoch microseconds orgoogle.protobuf.Timestamp;NUMERICandBIGNUMERICareBigDecimalByteStringEncoderbytes, a number or a string. - Arrow rows are
arrow_rows: a serialized Arrow IPC schema message aswriter_schema, then record batch messages, as Arrow Java'sMessageSerializerand pyarrow'sserialize()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:
IntforINTEGER,FloatingPoint(single or double) forFLOAT,Bool,BinaryforBYTES,Utf8forSTRING,JSONandGEOGRAPHY,Date(day) orUtf8orint32forDATE, andTimestampin microseconds with a time zone forTIMESTAMPor without one forDATETIME.Decimal128isNUMERIC,Decimal256isBIGNUMERIC,IntervalorUtf8isINTERVAL,Listis aREPEATEDcolumn andStructisRECORD. TIMEcolumns also acceptTime64in microseconds orUtf8, 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
StorageErrordetail, 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
REQUIREDfield) rejects the whole request withrow_errors. - Appending to a finalized stream is
STREAM_FINALIZED. GetWriteStreamreturns the table schema with theFULLview, 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:
sourceUrisin floci's Cloud Storage (gs://bucket/object, with one*wildcard after the bucket), or a media upload to/upload/bigquery/v2/projects/{project}/jobswithuploadType=multipartoruploadType=resumable(what the SDKs'load_table_from_file,load_table_from_dataframeandbigquery.writer(...)use). - Formats:
CSV(the default),NEWLINE_DELIMITED_JSONandPARQUET. - 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 infersINTEGER,FLOAT,BOOLEAN,TIMESTAMP,DATE,TIME,STRING, andRECORD/REPEATEDfor JSON; CSV header names have invalid characters replaced with underscores, and a CSV without a header gets generic names such asstring_field_0.skipLeadingRowsfollows BigQuery's auto-detection rules (unset: detect a header;0: no header;N: skipN-1rows and detect a header in rowN). - Partition decorators: a destination such as
table$YYYYMMDDreuses 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) andmaxBadRecords. Without anullMarker, an emptySTRINGvalue stays an empty string and empty values of other types areNULL. - JSON with a schema is validated row by row exactly like
tabledata.insertAll(ignoreUnknownValues,maxBadRecords, and a precisebadRecordscount); it needs no SQL engine, so it also works in mock mode. - Dispositions:
writeDisposition(WRITE_APPENDby default for loads,WRITE_TRUNCATE,WRITE_TRUNCATE_DATA,WRITE_EMPTY) andcreateDisposition. - Results:
statistics.loadreportsinputFiles,inputFileBytes,outputRows,outputBytesandbadRecords. Data, schema and destination errors are reported in the DONE job'sstatus.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) orlocation(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, andUSwhen it has neither. Locations compare case-insensitively (USandusare the same), but a multi-region and a region inside it are different locations (USis notus-central1).- A dataset in another location returns 404 with reason
notFoundand the messageNot found: Dataset {project}:{dataset} was not found in location {LOCATION}: an HTTP error fromjobs.query, aDONEjob carryingstatus.errorResultfromjobs.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 ofconfiguration.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 whenIF EXISTSfinds no such table.CREATE TABLEandCREATE VIEW, in every form: the new table's dataset, also whenIF NOT EXISTSskips an existing table, and every dataset the query orCOPYsource 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 EXISTSskip. CREATE SCHEMAis not compared with anything: it creates the dataset in the job's location. It ignoresOPTIONS, includinglocation.IF NOT EXISTSskips an existing dataset in any location.- A job that fails in
jobs.insertis 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.cancelandjobs.deletehonor thelocationquery parameter: a job in another location is not found (404notFound,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
tablerequires the request'sdefaultDataset. - Typed equality per column:
INTEGER/FLOAT/BOOLEAN/STRING; comparing with a mismatched literal type → 400invalidQuery("No matching signature for operator ="). COUNT(*)returns a singleINTEGERcolumn namedf0_and respectsWHERE.- Anything else (JOIN, GROUP BY, ORDER BY, OR,
!=,<, functions, aliases, subqueries, DML/DDL,= NULL), and any query with parameters, fails fast with 400 and reasoninvalidQuerynaming 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,HAVINGorQUALIFYaccepts 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/REPLACEtranslation 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 withinvalidQuery. - A star over a join that mixes
USINGwithONor comma joins, or anUNPIVOTcombined with aUSINGjoin, is not positionally rewritten, so a repeated input name can keep DuckDB's generated name and order. A* EXCEPToverPIVOToutput whose generated column names cannot be derived from literalINvalues and aggregate aliases is not validated. - Scripts support nested
BEGINblocks,IF, searched/simpleCASE, scalar variables, andWHILE,LOOP,REPEAT, andFORwith labels and loop control. Full GoogleSQL variable type and expression semantics, exception handlers, transactions, JavaScript UDFs, persistentCREATE FUNCTION/PROCEDURE,EXPORT DATA,LOAD DATA,ALTER TABLE, andALTER VIEWinside scripts are not supported; they fail withinvalidQuery, and so do theASSERTandRETURNstatements, which a script here cannot run yet. Dynamic SQL goes throughEXECUTE IMMEDIATEand is supported only in the shapes listed above. ABIGNUMERICvariable is not supported: DuckDB'sDECIMAL(38,9)is BigQuery'sNUMERICprecision 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 BOOLfor BigQuery'sQuery 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
NUMERICvariable refuses aFLOAT64value, apart from two shapes that still store one: aNUMERICfield of aSTRUCTdefault accepts aFLOAT64value,STRUCT(f AS n), where BigQuery coerces aSTRUCTfield only when the value is a literal; and arithmetic over floating point literals staysNUMERIChere, soSET n = 1.5 + 0.5stores2, where BigQuery types the expressionFLOAT64and refuses it. The last one follows from a bare decimal literal beingNUMERIChere andFLOAT64in BigQuery, which holds outside scripts too:SELECT 1.5reports aNUMERICcolumn. - 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 answersUnrecognized name: x at [1:8]. The same holds for the other errors DuckDB raises while a statement runs, such as aNOT NULLtemporary column receiving a NULL, which answersConstraint Error: NOT NULL constraint failed: t.idwhere BigQuery answersRequired 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 TABLEandALTER VIEWsupport onlySET 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 BYis stored as table metadata but does not physically cluster rows. PlainCREATE TABLEandCREATE TABLE AS SELECTsupport the table optionsdescription,friendly_name,labels, andexpiration_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_daysandkms_key_nameare stored in the Table resource (requirePartitionFilter,timePartitioning.expirationMsandencryptionConfiguration.kmsKeyName), whiledefault_rounding_mode,enable_change_history,max_staleness,enable_fine_grained_mutations,storage_uri,file_format,table_formatandtagsare accepted and ignored. An expression value for an option the emulator does not evaluate leaves the option unset.ALTER TABLE SET OPTIONSandCREATE TABLE COPYstill reject these options, as before.CREATE TABLE COPYapplies the option subset documented above. Time-basedPARTITION BYmetadata is stored for the supported expressions documented above.- Option values in
ALTER TABLE/ALTER VIEW SET OPTIONSandCREATE TABLE COPYare 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 asLPADandRAND(Found unsupported function call); floci evaluates them. Other scalar functions, includingREPLACE,FORMATandUPPER, work in both. - Legacy SQL (
useLegacySql: true) is rejected. A request that omitsuseLegacySqlruns as GoogleSQL (the API default is legacy SQL, but every SDK sendsfalse). SELECT * FROM UNNEST(array)expandsSTRUCTelements into columns; with an alias (UNNEST(array) AS x) the element is one columnx.- INFORMATION_SCHEMA views other than the seven above, non-terminal wildcard table patterns,
FOR SYSTEM_TIME AS OF,GEOGRAPHYfunctions, BigQuery ML, and remote functions are not emulated. - Most
SAFE.-prefixed functions are not emulated.SAFE_CAST,SAFE.FROM_HEX,SAFE.PARSE_DATE, andSAFE.PARSE_TIMESTAMPare supported explicitly. FORMAT_TIMESTAMPformats in UTC and ignores its time zone argument, so%zand%Ezprint+0000and+00:00.PARSE_DATE,PARSE_DATETIMEandPARSE_TIMESTAMPneed a constant format (a literal or a query parameter): DuckDB'sstrptimetakes no format read from a column.FORMAT_*accepts a format read from a column.- A bare subscript
[i]on anARRAYreached through a field path, such asCAST(STRUCT([1, 2] AS a) AS STRUCT<a ARRAY<INT64>>).a[1], is read as DuckDB's one-based subscript (1here, where BigQuery returns2): 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 asSTRUCT<n INT64>('abc'), fails while the query runs with DuckDB'sCould 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 asSTRUCT<n INT64>('1'), succeeds. A value for a nestedSTRUCT<...>field must use the declared field names, since DuckDB casts structs by name where BigQuery matches them by position. - A
STRINGquery parameter shaped like a timestamp is coerced toDATE,DATETIMEorTIMESTAMPwhen compared with one, with an uppercaseTandZ,UTCor a numeric offset, and toTIMESTAMPwith a time zone name (America/Los_Angeles,EST5EDT), as in BigQuery. A lowercasetorz(2014-09-27t12:30:00.45,2014-09-27 12:30:00.45z) is coerced when the other side of the comparison orBETWEENis temporal as written: a typed literal, a temporal function orCAST, or a table column of that type. There the value is also checked as BigQuery checks it, with BigQuery's message: aDATEtakes a date alone (Invalid date: '2014-09-27 12:30:00'), aDATETIMEno zone (Invalid datetime string "2014-09-27T12:30:00Z"), and aTIMESTAMPneeds 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 DAYis aDATETIME), and a parameter tested itself (@a BETWEEN x AND y) are not checked. Compared with anything else (anUNNESTelement, a subquery column) a lowercasetorzvalue fails withCannot 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 aDATEorDATETIME, and an unknown zone name fails with DuckDB'sUnknown TimeZone. ORDER BYsorts aFLOAT64NaNjust afterNULL(ascending) as BigQuery does, through an extra sort key that is added only when the query visibly can hold aNaN(a'NaN'string,IEEE_DIVIDE, aFLOAT64parameter, or a table with aFLOAT64column read right afterFROMorJOIN).NaNstill sorts last, as DuckDB orders it, when that key is left out: in a query with a set operation, in a query that usesGROUP BY ALL, in a window with aRANGEframe (also a named window anOVER (w RANGE ...)extends), in aDISTINCTaggregate, 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()orGENERATE_UUID(), and when the onlyFLOAT64column comes from the second table of a comma join (FROM a, b), which is not looked at.NUMERICandBIGNUMERICare computed asDECIMAL(38, 9).TIMESTAMPvalues are staged as ISO-8601 strings for DuckDB, including values nested insideRECORDorREPEATEDcolumns; the BigQuery wire format continues to use epoch seconds.totalBytesProcessedis an estimate from the size of the tables a query reads.- No
insertIdde-duplication (real BigQuery is best-effort anyway). - Jobs always complete synchronously (
jobComplete=true, stateDONE), 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"}}]}'