bigquery

​

Syntax

bigquery(project, dataset, table[, access_token][, key = value, ...])
bigquery(named_collection[, key = value, ...])

​

Arguments

​

Authentication

1. Access token. Any valid OAuth 2.0 access token, for example, from gcloud auth print-access-token. Tokens expire quickly (typically after one hour), so this method is best for interactive use.
2. Service account key (recommended for servers). Pass the content of a key file created in Google Cloud IAM with the service_account_key argument. ClickHouse signs a JWT with the key and exchanges it for an access token, refreshing it automatically.
3. Refresh token. Pass client_id, client_secret and refresh_token, for example, taken from ~/.config/gcloud/application_default_credentials.json after gcloud auth application-default login.

​

Data type mapping

• BigQuery DATETIME has no time zone; it is mapped to DateTime64(6, 'UTC') so that the displayed value does not depend on the server time zone.
• A NULLABLE RECORD is mapped to Nullable(Tuple(...)), so a whole-record NULL is preserved as NULL instead of collapsing to a Tuple of default values. A NULL (or empty) array becomes an empty array, because Array cannot be inside Nullable in ClickHouse. A BigQuery array cannot contain NULL elements (ARRAY<T> is equivalent to ARRAY<T NOT NULL>), so the element type of a REPEATED field is not Nullable (Array(T), or Array(Tuple(...)) for a RECORD element); a NULL element in a tabledata.list response is rejected as malformed input.
• Reading and writing Nullable(Tuple(...)) columns through the bigquery table function works without extra settings. Creating a persistent BigQuery-engine table that contains such a column (whether the structure is inferred or declared explicitly) requires the enable_nullable_tuple_type setting, as for any Nullable(Tuple) column. When declaring columns explicitly, a RECORD field may instead be declared as a plain Tuple(...) to avoid the setting, at the cost of coercing a whole-record NULL to a default tuple; the only accepted difference from the inferred type is dropping a Nullable that wraps a RECORD’s Tuple, and only at that same record — the nullability cannot be moved to a different (inner or outer) record.
• GEOGRAPHY is mapped to Geometry. BigQuery transfers a GEOGRAPHY value as WKT text, which is parsed into the matching alternative of Geometry (a Variant of Point, MultiPoint, Ring, LineString, MultiLineString, Polygon and MultiPolygon) on read, and serialized back to WKT on write. A GEOMETRYCOLLECTION and an empty geometry (such as POINT EMPTY) have no Geometry counterpart, so reading a row that contains such a value raises an error. Because Variant holds a NULL by itself, a NULLABLE GEOGRAPHY field is mapped to Geometry and not to Nullable(Geometry), and NULL still round-trips.
• JSON is mapped to String rather than to the JSON data type, because the ClickHouse JSON type accepts only an object ({...}) at the top level, while a BigQuery JSON value can be any JSON value — a scalar, an array, or null — so a table containing such values could not be read. In addition, JSON cannot be wrapped in Nullable, so an SQL NULL in a NULLABLE column would not be preserved. The String mapping is lossless; top-level objects can be converted with CAST(value AS JSON).
• BIGNUMERIC values with more than 38 digits in the integer part do not fit into Decimal(76, 38) and produce an error.
• TIMESTAMP and DATE values outside of the range of DateTime64/Date32 (years 1900-2299) are not supported.
• RANGE columns are read-only. tabledata.insertAll expects a RANGE<T> value as a structured {start, end} object, which cannot be reconstructed from the String mapping, so inserting into a RANGE column raises an error.
• INT64 values are sent to tabledata.insertAll as decimal strings, because the API parses JSON numbers as doubles and would otherwise corrupt values outside [-2^53 + 1, 2^53 - 1].

​

Examples

SELECT word, sum(word_count) AS c
FROM bigquery('bigquery-public-data', 'samples', 'shakespeare', '<access token>')
GROUP BY word
ORDER BY c DESC
LIMIT 5;

SELECT count()
FROM bigquery('my-project', 'my_dataset', 'my_table',
              service_account_key = '{"type": "service_account", "private_key": "...", "client_email": "...", ...}');

INSERT INTO FUNCTION bigquery('my-project', 'my_dataset', 'my_table', '<access token>')
SELECT number AS id, toString(number) AS name FROM numbers(10);

<clickhouse>
    <named_collections>
        <my_bigquery>
            <project>my-project</project>
            <dataset>my_dataset</dataset>
            <service_account_key><![CDATA[{"type": "service_account", ...}]]></service_account_key>
        </my_bigquery>
    </named_collections>
</clickhouse>

SELECT * FROM bigquery(my_bigquery, table = 'my_table');

​

Limitations

• Only native BigQuery tables can be read. Views and external tables require running a BigQuery query job, which this function does not do.
• RANGE columns can be read (as String) but not written: inserting into a RANGE column raises an error.
• A GEOGRAPHY value that is a GEOMETRYCOLLECTION or an empty geometry cannot be represented by the Geometry type, so reading a row containing one raises an error. Writing a NULL Geometry into a REQUIRED GEOGRAPHY field, or as an element of a REPEATED GEOGRAPHY field, is rejected, because BigQuery accepts no NULL there.
• Predicates are not pushed down: tabledata.list only lists the rows of a table and has no filtering parameter at all (it takes pagination, column selection and format options), and filtering would require running a BigQuery query job, which this function does not do. A WHERE condition is therefore applied in ClickHouse after the rows have been downloaded; use column selection to reduce the transferred data.
• A LIMIT, on the other hand, does reduce the amount of data read. Pages are requested lazily, with maxResults set to max_block_size, and no further page is requested once the query has enough rows. For a trivial LIMIT n (no WHERE, GROUP BY, ORDER BY, and n below max_block_size) ClickHouse lowers max_block_size to n, so exactly one request for exactly n rows is made; otherwise the read stops at the first page boundary past the limit, overshooting it by less than one page.
• The read is pinned to the schema seen at query analysis time by passing the explicit list of columns to tabledata.list. For a very wide read whose column list would exceed the request URL length limit (for example SELECT * from a table with thousands of columns), the query is rejected rather than read without a pin (an unpinned read could be misaligned by a concurrent schema change); select fewer columns so the list fits. The same URL length limit is checked before every paginated request (each page carries an opaque pageToken), so a read whose later pages would not fit the limit is rejected with the same error instead of failing part way through.
• If the BigQuery table is altered after its schema has been read, the query is rejected instead of silently returning or writing mismatched data: the live schema is re-fetched and compared with the analyzed one right before a read, and again before an INSERT streams its first row. The remaining window (a schema change between that check and the requests that follow it) cannot be closed, because the schema and the data are fetched by separate REST requests.
• The comparison is against the schema snapshot the query was analyzed with, which is taken when the table function resolves its structure or, for a persistent table (a BigQuery engine table, or a table created with CREATE TABLE ... AS bigquery(...), which persists its columns the same way), on its first read or write after CREATE, ATTACH, or a server restart. Table metadata persists the mapped ClickHouse columns, not the BigQuery schema, so a schema change made while the table was detached (or the server was down) is adopted by the next query rather than rejected: the declared columns are still validated against the live schema, and the rows are decoded with it, so a change that keeps the mapped ClickHouse types (STRING to BYTES, for example) is read with the new type’s rules under the same column type.
• Rows written with streaming inserts land in the BigQuery streaming buffer and may take a while to become visible to subsequent reads.
• A large INSERT is sent to tabledata.insertAll in batches: at most 500 rows per request, and also split so that each request stays under BigQuery’s 10 MB request-size limit (a single row larger than that limit is rejected with a clear error).
• Writes are not atomic, and a single tabledata.insertAll request may itself partially succeed: BigQuery can commit some rows of a request while rejecting the others with insertErrors. Requests are also committed independently of each other, so a later batch may be rejected after earlier batches have been accepted. In both cases the query reports an error, but the already-committed rows remain in BigQuery. To limit duplication, each row is sent with a stable insertId derived from the query id and the row’s ordinal position in the stream, which BigQuery uses for best-effort deduplication within its streaming-insert window. A query_id longer than BigQuery’s 128-character insertId limit is hashed to a fixed-length prefix, which stays stable for that query_id. Because the insertId depends on the ordinal position, deduplication is reliable only when the rerun produces the rows in the same order: a transport-level retry of a batch is always safe, and re-running the same INSERT with the same query_id deduplicates only if it presents the rows in the same order (for example a single-threaded insert, or an otherwise deterministic ordering — set max_threads = 1 and max_insert_threads = 1 for a parallel INSERT ... SELECT whose chunk order could otherwise change between attempts).

​

Related

• BigQuery table engine