bigquery

Permite realizar consultas SELECT e INSERT en una tabla de Google BigQuery, incluidos los conjuntos de datos públicos. La estructura de la tabla se infiere automáticamente a partir de su esquema en BigQuery. La lectura utiliza la API REST de BigQuery (tabledata.list), por lo que solo se pueden leer tablas nativas; no se admiten vistas, vistas materializadas ni tablas externas. La escritura utiliza inserciones en streaming (tabledata.insertAll), lo que requiere tener habilitada la facturación en el proyecto.

Sintaxis

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

Argumentos

Argumento	Descripción
`project`	El proyecto de Google Cloud propietario del conjunto de datos. En el caso de los conjuntos de datos públicos, es el proyecto del conjunto de datos; por ejemplo, `bigquery-public-data`.
`dataset`	El nombre del conjunto de datos.
`table`	El nombre de la tabla.
`access_token`	Un token de acceso OAuth 2.0 (argumento posicional opcional; consulte Autenticación).

Los argumentos project, dataset, table y access_token también se pueden proporcionar en formato key = value; los argumentos posicionales ocupan estas posiciones en este orden, y especificar un argumento tanto por posición como mediante una clave (o especificar la misma clave dos veces) produce un error. Los siguientes argumentos se pueden especificar en formato key = value (o como claves de una colección con nombre):

Clave	Descripción
`access_token`	Un token de acceso OAuth 2.0.
`service_account_key`	El contenido de un archivo JSON de clave de una cuenta de servicio de Google.
`client_id`	ID de cliente OAuth 2.0 (utilizado junto con `client_secret` y `refresh_token`).
`client_secret`	Secreto de cliente OAuth 2.0.
`refresh_token`	Token de actualización OAuth 2.0.
`billing_project`	Proyecto opcional al que se atribuyen la cuota y la facturación (se envía en el header `X-Goog-User-Project`).
`base_url`	El endpoint de la API; de forma predeterminada, `https://bigquery.googleapis.com`. Puede cambiarse para pruebas y emuladores.
`token_url`	Sobrescritura del endpoint de token OAuth para pruebas y emuladores. De forma predeterminada, el `token_uri` de la clave de la cuenta de servicio o `https://oauth2.googleapis.com/token`.

Autenticación

Debe proporcionarse exactamente un método de autenticación. BigQuery no permite el acceso anónimo, por lo que se requieren credenciales incluso para los conjuntos de datos públicos.

Token de acceso. Cualquier token de acceso OAuth 2.0 válido; por ejemplo, obtenido con gcloud auth print-access-token. Los tokens caducan rápidamente (por lo general, después de una hora), por lo que este método es más adecuado para el uso interactivo.
Clave de cuenta de servicio (recomendada para servidores). Pase el contenido de un archivo de clave creado en Google Cloud IAM mediante el argumento service_account_key. ClickHouse firma un JWT con la clave y lo intercambia por un token de acceso, que renueva automáticamente.
Token de actualización. Pase client_id, client_secret y refresh_token; por ejemplo, obtenidos de ~/.config/gcloud/application_default_credentials.json después de ejecutar gcloud auth application-default login.

Almacene las credenciales en una colección con nombre para evitar especificarlas en cada consulta. Una tabla permanente creada a partir de una colección con nombre (con el motor de tabla BigQuery o CREATE TABLE ... AS bigquery(...)) se registra como dependencia de la colección, por lo que DROP NAMED COLLECTION queda bloqueado mientras exista la tabla.

Correspondencia de tipos de datos

Tipo de BigQuery	Tipo de ClickHouse
`STRING`	String
`BYTES`	String (bytes sin procesar)
`INTEGER` / `INT64`	Int64
`FLOAT` / `FLOAT64`	Float64
`BOOLEAN` / `BOOL`	Bool
`TIMESTAMP`	DateTime64(6, ‘UTC’)
`DATE`	Date32
`TIME`	Time64(6)
`DATETIME`	DateTime64(6, ‘UTC’)
`NUMERIC` / `DECIMAL`	Decimal(38, 9), o `Decimal(P, S)` si está parametrizado
`BIGNUMERIC`	Decimal(76, 38), o `Decimal(P, S)` si está parametrizado
`GEOGRAPHY`	Geometry (analizado desde WKT)
`JSON`	String
`INTERVAL`	String
`RANGE`	String (solo lectura)
`RECORD` / `STRUCT`	Tuple, o Nullable(`Tuple`) en modo `NULLABLE`
Modo `REPEATED`	Array del tipo de elemento, con elementos no `Nullable` (`Array(Tuple(...))` para elementos `RECORD`), ya que un array de BigQuery no puede contener elementos `NULL`
Modo `NULLABLE`	Nullable (excepto `GEOGRAPHY`, cuyo tipo `Geometry` admite `NULL` por sí mismo)

Notas:

DATETIME de BigQuery no tiene zona horaria; se asigna a DateTime64(6, 'UTC') para que el valor mostrado no dependa de la zona horaria del servidor.
Un RECORD NULLABLE se asigna a Nullable(Tuple(...)), de modo que un NULL de todo el registro se conserva como NULL en lugar de reducirse a un Tuple de valores predeterminados. Un array NULL (o vacío) se convierte en un array vacío, porque Array no puede estar dentro de Nullable en ClickHouse. Un array de BigQuery no puede contener elementos NULL (ARRAY<T> equivale a ARRAY<T NOT NULL>), por lo que el tipo de elemento de un campo REPEATED no es Nullable (Array(T) o Array(Tuple(...)) para un elemento RECORD); un elemento NULL en una respuesta de tabledata.list se rechaza como entrada malformada.
La lectura y escritura de columnas Nullable(Tuple(...)) mediante la función de tabla bigquery funciona sin configuraciones adicionales. Para crear una tabla persistente con engine BigQuery que contenga una columna de este tipo (tanto si la estructura se infiere como si se declara explícitamente), se requiere la configuración enable_nullable_tuple_type, como para cualquier columna Nullable(Tuple). Al declarar columnas explícitamente, un campo RECORD puede declararse como un Tuple(...) simple para evitar esta configuración, a costa de convertir un NULL de todo el registro en una tupla predeterminada; la única diferencia aceptada respecto al tipo inferido es eliminar el Nullable que envuelve el Tuple de un RECORD, y únicamente en ese mismo registro: la anulabilidad no puede trasladarse a un registro distinto, ya sea interno o externo.
GEOGRAPHY se asigna a Geometry. BigQuery transfiere un valor GEOGRAPHY como texto WKT, que se analiza como la alternativa correspondiente de Geometry (un Variant de Point, MultiPoint, Ring, LineString, MultiLineString, Polygon y MultiPolygon) durante la lectura y se serializa de nuevo como WKT durante la escritura. Una GEOMETRYCOLLECTION y una geometría vacía (como POINT EMPTY) no tienen equivalente en Geometry, por lo que leer una fila que contenga alguno de estos valores genera un error. Como Variant ya admite NULL, un campo GEOGRAPHY NULLABLE se asigna a Geometry y no a Nullable(Geometry), y NULL sigue conservándose en el recorrido de ida y vuelta.
JSON se asigna a String en lugar del tipo de datos JSON, porque el tipo JSON de ClickHouse solo acepta un objeto ({...}) en el nivel superior, mientras que un valor JSON de BigQuery puede ser cualquier valor JSON —un escalar, un array o null—, por lo que no se podría leer una tabla que contenga dichos valores. Además, JSON no puede envolverse en Nullable, por lo que un NULL de SQL en una columna NULLABLE no se conservaría. La correspondencia con String no pierde información; los objetos de nivel superior se pueden convertir con CAST(value AS JSON).
Los valores BIGNUMERIC con más de 38 dígitos en la parte entera no caben en Decimal(76, 38) y generan un error.
Los valores TIMESTAMP y DATE fuera del rango de DateTime64/Date32 (años 1900-2299) no son compatibles.
Las columnas RANGE son de solo lectura. tabledata.insertAll espera un valor RANGE<T> como un objeto estructurado {start, end}, que no se puede reconstruir a partir de la correspondencia con String, por lo que insertar en una columna RANGE genera un error.
Los valores INT64 se envían a tabledata.insertAll como cadenas decimales, porque la API analiza los números JSON como valores de doble precisión y, de otro modo, corrompería los valores fuera de [-2^53 + 1, 2^53 - 1].

Ejemplos

Lee un conjunto de datos público con un token de gcloud:

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;

Lea una tabla privada mediante un archivo de clave de cuenta de servicio:

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

Insertar datos (inserción en streaming; requiere tener habilitada la facturación):

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

Use una colección con nombre:

<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');

Limitaciones

Solo se pueden leer tablas nativas de BigQuery. Las vistas y las tablas externas requieren ejecutar un trabajo de consulta de BigQuery, algo que esta función no hace.
Las columnas RANGE se pueden leer (como String), pero no se pueden escribir: insertar en una columna RANGE genera un error.
Un valor GEOGRAPHY que sea una GEOMETRYCOLLECTION o una geometría vacía no puede representarse mediante el tipo Geometry, por lo que leer una fila que contenga uno de estos valores genera un error. Se rechaza escribir un Geometry NULL en un campo GEOGRAPHY REQUIRED, o como elemento de un campo GEOGRAPHY REPEATED, porque BigQuery no admite valores NULL en esos casos.
No se hace pushdown de predicados: tabledata.list solo enumera las filas de una tabla y no dispone de ningún parámetro de filtrado (admite opciones de paginación, selección de columnas y formato), y filtrar requeriría ejecutar un trabajo de consulta de BigQuery, algo que esta función no hace. Por lo tanto, una condición WHERE se aplica en ClickHouse después de descargar las filas; use la selección de columnas para reducir los datos transferidos.
En cambio, un LIMIT sí reduce la cantidad de datos leídos. Las páginas se solicitan de forma diferida, con maxResults establecido en max_block_size, y no se solicita ninguna página adicional cuando la consulta ya tiene suficientes filas. Para un LIMIT n trivial (sin WHERE, GROUP BY ni ORDER BY, y con n inferior a max_block_size), ClickHouse reduce max_block_size a n, por lo que se realiza exactamente una solicitud de exactamente n filas; de lo contrario, la lectura se detiene en el primer límite de página posterior al límite, superándolo en menos de una página.
La lectura queda fijada al esquema observado en el momento del análisis de la consulta al pasar la lista explícita de columnas a tabledata.list. Para una lectura muy amplia cuya lista de columnas exceda el límite de longitud de la URL de la solicitud (por ejemplo, SELECT * de una tabla con miles de columnas), la consulta se rechaza en lugar de leer sin fijación (una lectura sin fijación podría desalinearse debido a un cambio de esquema concurrente); seleccione menos columnas para que la lista quepa. El mismo límite de longitud de URL se comprueba antes de cada solicitud paginada (cada página incluye un pageToken opaco), por lo que una lectura cuyas páginas posteriores no quepan dentro del límite se rechaza con el mismo error en lugar de fallar a mitad del proceso.
Si la tabla de BigQuery se modifica después de leer su esquema, la consulta se rechaza en lugar de devolver o escribir silenciosamente datos no coincidentes: el esquema actual se vuelve a obtener y se compara con el analizado justo antes de una lectura, y de nuevo antes de que un INSERT transmita su primera fila. La ventana restante (un cambio de esquema entre esa comprobación y las solicitudes posteriores) no puede eliminarse, porque el esquema y los datos se obtienen mediante solicitudes REST independientes.
La comparación se realiza con la instantánea del esquema con la que se analizó la consulta, que se toma cuando la función de tabla resuelve su estructura o, en el caso de una tabla persistente (una tabla con engine BigQuery, o una tabla creada con CREATE TABLE ... AS bigquery(...), que conserva sus columnas de la misma forma), en su primera lectura o escritura después de CREATE, ATTACH o un reinicio del servidor. Los metadatos de la tabla conservan las columnas de ClickHouse asignadas, no el esquema de BigQuery, por lo que un cambio de esquema realizado mientras la tabla estaba separada (o el servidor estaba apagado) es adoptado por la siguiente consulta en lugar de rechazarse: las columnas declaradas siguen validándose con respecto al esquema actual y las filas se decodifican con él, por lo que un cambio que conserve los tipos de ClickHouse asignados (de STRING a BYTES, por ejemplo) se lee según las reglas del nuevo tipo bajo el mismo tipo de columna.
Las filas escritas mediante inserciones de streaming llegan al búfer de streaming de BigQuery y pueden tardar un tiempo en ser visibles en lecturas posteriores.
Un INSERT grande se envía a tabledata.insertAll en lotes: como máximo 500 filas por solicitud, y también se divide para que cada solicitud se mantenga por debajo del límite de tamaño de solicitud de 10 MB de BigQuery (una sola fila que supere ese límite se rechaza con un error claro).
Las operaciones de escritura no son atómicas y una sola solicitud tabledata.insertAll puede completarse parcialmente: BigQuery puede confirmar algunas filas de una solicitud y rechazar otras con insertErrors. Además, las solicitudes se confirman de forma independiente, por lo que un lote posterior puede rechazarse después de que se hayan aceptado lotes anteriores. En ambos casos, la consulta informa de un error, pero las filas ya confirmadas permanecen en BigQuery. Para limitar los duplicados, cada fila se envía con un insertId estable derivado del id de consulta y de la posición ordinal de la fila en el flujo, que BigQuery utiliza para realizar una deduplicación de máximo esfuerzo dentro de su ventana de inserciones en streaming. Si un query_id supera el límite de 128 caracteres de BigQuery para insertId, se le aplica hash para obtener un prefijo de longitud fija que se mantiene estable para ese query_id. Dado que el insertId depende de la posición ordinal, la deduplicación solo es fiable cuando la reejecución produce las filas en el mismo orden: reintentar un lote a nivel de transporte siempre es seguro, y volver a ejecutar el mismo INSERT con el mismo query_id solo deduplica si presenta las filas en el mismo orden (por ejemplo, mediante una inserción de un solo hilo o un orden determinista; establezca max_threads = 1 y max_insert_threads = 1 para un INSERT ... SELECT paralelo cuyo orden de fragmentos podría cambiar entre intentos).

Motor de tabla BigQuery

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Sintaxis

Argumentos

Autenticación

Correspondencia de tipos de datos

Ejemplos

Limitaciones

​Sintaxis

​Argumentos

​Autenticación

​Correspondencia de tipos de datos

​Ejemplos

​Limitaciones

​Relacionado

Sintaxis

Argumentos

Autenticación

Correspondencia de tipos de datos

Ejemplos

Limitaciones

Relacionado