bigquery

Permet d’exécuter des requêtes SELECT et INSERT sur une table Google BigQuery, y compris dans des jeux de données publics.

Permet d’exécuter des requêtes SELECT et INSERT sur une table Google BigQuery, y compris sur des jeux de données publics. La structure de la table est automatiquement déduite du schéma de la table BigQuery.

La lecture utilise l’API REST de BigQuery (tabledata.list) ; seules les tables natives peuvent donc être lues (les vues, les vues matérialisées et les tables externes ne sont pas prises en charge). L’écriture utilise les insertions en streaming (tabledata.insertAll), ce qui nécessite l’activation de la facturation pour le projet.

​

Syntaxe

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

​

Arguments

Argument	Description
project	Le projet Google Cloud auquel appartient le jeu de données. Pour les jeux de données publics, il s’agit du projet du jeu de données, par exemple bigquery-public-data.
dataset	Le nom du jeu de données.
table	Le nom de la table.
access_token	Un jeton d’accès OAuth 2.0 (argument positionnel facultatif, voir Authentification).

Les arguments project, dataset, table et access_token peuvent également être fournis sous la forme key = value ; les arguments positionnels remplissent ces emplacements dans cet ordre. Indiquer un argument à la fois de manière positionnelle et sous forme de clé (ou indiquer deux fois la même clé) constitue une erreur.

Les arguments suivants peuvent être spécifiés sous la forme key = value (ou comme clés d’une collection nommée) :

Clé	Description
access_token	Un jeton d’accès OAuth 2.0.
service_account_key	Le contenu d’un fichier de clé de compte de service Google au format JSON.
client_id	ID client OAuth 2.0 (utilisé avec client_secret et refresh_token).
client_secret	Secret client OAuth 2.0.
refresh_token	Jeton d’actualisation OAuth 2.0.
billing_project	Projet facultatif auquel attribuer le quota et la facturation (envoyé dans l’en-tête X-Goog-User-Project).
base_url	Le point de terminaison de l’API, https://bigquery.googleapis.com par défaut. Peut être modifié pour les tests et les émulateurs.
token_url	Remplacement du point de terminaison des jetons OAuth pour les tests et les émulateurs. Par défaut, le token_uri de la clé de compte de service ou https://oauth2.googleapis.com/token.

​

Authentification

Vous devez fournir exactement une méthode d’authentification. BigQuery n’autorise pas l’accès anonyme. Des identifiants sont donc requis, même pour les jeux de données publics.

1. Jeton d’accès. Tout jeton d’accès OAuth 2.0 valide, par exemple obtenu avec gcloud auth print-access-token. Les jetons expirent rapidement (généralement au bout d’une heure) ; cette méthode est donc mieux adaptée à une utilisation interactive.
2. Clé de compte de service (recommandée pour les serveurs). Transmettez le contenu d’un fichier de clé créé dans Google Cloud IAM à l’aide de l’argument service_account_key. ClickHouse signe un JWT avec la clé et l’échange contre un jeton d’accès, qu’il renouvelle automatiquement.
3. Jeton d’actualisation. Transmettez client_id, client_secret et refresh_token, par exemple à partir de ~/.config/gcloud/application_default_credentials.json après avoir exécuté gcloud auth application-default login.

Stockez les identifiants dans une collection nommée afin d’éviter de devoir les spécifier dans chaque requête. Une table permanente créée à partir d’une collection nommée (avec le moteur de table BigQuery ou CREATE TABLE ... AS bigquery(...)) est enregistrée comme dépendance de la collection ; DROP NAMED COLLECTION est donc bloqué tant que la table existe.

​

Correspondance des types de données

Type BigQuery	Type ClickHouse
STRING	String
BYTES	String (octets bruts)
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), ou Decimal(P, S) lorsqu’il est paramétré
BIGNUMERIC	Decimal(76, 38), ou Decimal(P, S) lorsqu’il est paramétré
GEOGRAPHY	Geometry (analysé depuis le format WKT)
JSON	String
INTERVAL	String
RANGE	String (lecture seule)
RECORD / STRUCT	Tuple, ou Nullable(Tuple) en mode NULLABLE
Mode REPEATED	Array du type d’élément, avec des éléments non-Nullable (Array(Tuple(...)) pour un élément RECORD), car un tableau BigQuery ne peut pas contenir d’éléments NULL
Mode NULLABLE	Nullable (sauf pour GEOGRAPHY, dont le type Geometry peut contenir NULL directement)

Remarques :

• Le DATETIME de BigQuery n’a pas de fuseau horaire ; il est mappé sur DateTime64(6, 'UTC') afin que la valeur affichée ne dépende pas du fuseau horaire du serveur.
• Un RECORD NULLABLE est mappé sur Nullable(Tuple(...)), afin qu’un NULL pour l’ensemble de l’enregistrement soit conservé comme NULL au lieu d’être réduit à un Tuple de valeurs par défaut. Un tableau NULL (ou vide) devient un tableau vide, car Array ne peut pas être contenu dans Nullable dans ClickHouse. Un tableau BigQuery ne peut pas contenir d’éléments NULL (ARRAY<T> équivaut à ARRAY<T NOT NULL>), donc le type d’élément d’un champ REPEATED n’est pas Nullable (Array(T), ou Array(Tuple(...)) pour un élément RECORD) ; un élément NULL dans une réponse tabledata.list est rejeté comme entrée malformée.
• La lecture et l’écriture de colonnes Nullable(Tuple(...)) via la fonction de table bigquery fonctionnent sans paramètre supplémentaire. La création d’une table persistante utilisant le moteur BigQuery qui contient une telle colonne (que la structure soit inférée ou déclarée explicitement) nécessite le paramètre enable_nullable_tuple_type, comme pour toute colonne Nullable(Tuple). Lors de la déclaration explicite des colonnes, un champ RECORD peut également être déclaré comme un simple Tuple(...) pour éviter ce paramètre, au prix de la conversion d’un NULL pour l’ensemble de l’enregistrement en tuple par défaut ; la seule différence acceptée par rapport au type inféré consiste à supprimer un Nullable qui enveloppe le Tuple d’un RECORD, et uniquement pour ce même enregistrement — la nullabilité ne peut pas être déplacée vers un autre enregistrement, interne ou externe.
• GEOGRAPHY est mappé sur Geometry. BigQuery transfère une valeur GEOGRAPHY sous forme de texte WKT, qui est analysé à la lecture en l’alternative correspondante de Geometry (un Variant de Point, MultiPoint, Ring, LineString, MultiLineString, Polygon et MultiPolygon), puis sérialisé à nouveau en WKT à l’écriture. Un GEOMETRYCOLLECTION et une géométrie vide (telle que POINT EMPTY) n’ont pas d’équivalent dans Geometry ; la lecture d’une ligne contenant une telle valeur génère donc une erreur. Comme Variant peut contenir NULL directement, un champ GEOGRAPHY NULLABLE est mappé sur Geometry et non sur Nullable(Geometry), et NULL est toujours préservé lors de l’aller-retour.
• JSON est mappé sur String plutôt que sur le type de données JSON, car le type JSON de ClickHouse n’accepte qu’un objet ({...}) au niveau supérieur, alors qu’une valeur JSON BigQuery peut être n’importe quelle valeur JSON — un scalaire, un tableau ou null — et qu’une table contenant de telles valeurs ne pourrait donc pas être lue. De plus, JSON ne peut pas être enveloppé dans Nullable, de sorte qu’un NULL SQL dans une colonne NULLABLE ne serait pas préservé. Le mappage vers String est sans perte ; les objets de niveau supérieur peuvent être convertis avec CAST(value AS JSON).
• Les valeurs BIGNUMERIC dont la partie entière comporte plus de 38 chiffres ne tiennent pas dans Decimal(76, 38) et génèrent une erreur.
• Les valeurs TIMESTAMP et DATE hors de la plage prise en charge par DateTime64/Date32 (années 1900 à 2299) ne sont pas prises en charge.
• Les colonnes RANGE sont en lecture seule. tabledata.insertAll attend une valeur RANGE<T> sous la forme d’un objet structuré {start, end}, qui ne peut pas être reconstruit à partir du mappage vers String ; l’insertion dans une colonne RANGE génère donc une erreur.
• Les valeurs INT64 sont envoyées à tabledata.insertAll sous forme de chaînes décimales, car l’API analyse les nombres JSON comme des nombres à virgule flottante double précision et corromprait sinon les valeurs hors de [-2^53 + 1, 2^53 - 1].

​

Exemples

Lisez un jeu de données public à l’aide d’un jeton 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;

Lire une table privée à l’aide d’un fichier de clé de compte de service :

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

Insérer des données (insertion en streaming, nécessite que la facturation soit activée) :

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

Utilisez une collection nommée :

<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

• Seules les tables BigQuery natives peuvent être lues. Les vues et les tables externes nécessitent l’exécution d’une tâche de requête BigQuery, ce que cette fonction ne fait pas.
• Les colonnes RANGE peuvent être lues (en tant que String), mais pas écrites : l’insertion dans une colonne RANGE génère une erreur.
• Une valeur GEOGRAPHY correspondant à une GEOMETRYCOLLECTION ou à une géométrie vide ne peut pas être représentée par le type Geometry ; la lecture d’une ligne qui en contient une génère donc une erreur. L’écriture d’un Geometry NULL dans un champ GEOGRAPHY REQUIRED, ou comme élément d’un champ GEOGRAPHY REPEATED, est rejetée, car BigQuery n’y accepte pas de NULL.
• Les prédicats ne sont pas poussés vers la source : tabledata.list ne renvoie que les lignes d’une table et ne dispose d’aucun paramètre de filtrage (il accepte des options de pagination, de sélection de colonnes et de format). Or, le filtrage nécessiterait l’exécution d’une tâche de requête BigQuery, ce que cette fonction ne fait pas. Une condition WHERE est donc appliquée dans ClickHouse après le téléchargement des lignes ; utilisez la sélection de colonnes pour réduire le volume de données transférées.
• Un LIMIT, en revanche, réduit bien la quantité de données lues. Les pages sont demandées à la demande, avec maxResults défini sur max_block_size, et aucune page supplémentaire n’est demandée dès que la requête dispose d’un nombre suffisant de lignes. Pour un simple LIMIT n (sans WHERE, GROUP BY ni ORDER BY, et avec n inférieur à max_block_size), ClickHouse réduit max_block_size à n, de sorte qu’une seule requête portant exactement sur n lignes est effectuée ; sinon, la lecture s’arrête à la première limite de page au-delà de la limite, avec un dépassement inférieur à une page.
• La lecture est liée au schéma observé lors de l’analyse de la requête en transmettant la liste explicite des colonnes à tabledata.list. Pour une lecture très large dont la liste de colonnes dépasserait la limite de longueur de l’URL de requête (par exemple, SELECT * sur une table comptant des milliers de colonnes), la requête est rejetée plutôt que d’être effectuée sans cette liaison au schéma (une lecture non liée pourrait être désalignée par une modification concurrente du schéma) ; sélectionnez moins de colonnes afin que la liste tienne. Cette même limite de longueur d’URL est vérifiée avant chaque requête paginée (chaque page comporte un pageToken opaque) ; ainsi, une lecture dont les pages ultérieures dépasseraient la limite est rejetée avec la même erreur au lieu d’échouer en cours de route.
• Si la table BigQuery est modifiée après la lecture de son schéma, la requête est rejetée plutôt que de renvoyer ou d’écrire silencieusement des données non concordantes : le schéma actuel est récupéré à nouveau et comparé à celui analysé juste avant une lecture, puis de nouveau avant qu’un INSERT ne transmette sa première ligne. La fenêtre restante (une modification du schéma entre cette vérification et les requêtes qui la suivent) ne peut pas être éliminée, car le schéma et les données sont récupérés par des requêtes REST distinctes.
• La comparaison s’effectue avec l’instantané du schéma utilisé lors de l’analyse de la requête. Cet instantané est pris lorsque la fonction de table résout sa structure ou, pour une table persistante (une table utilisant le moteur BigQuery, ou une table créée avec CREATE TABLE ... AS bigquery(...), qui conserve ses colonnes de la même manière), lors de sa première lecture ou écriture après CREATE, ATTACH ou un redémarrage du serveur. Les métadonnées de la table conservent les colonnes ClickHouse mappées, et non le schéma BigQuery. Une modification du schéma effectuée pendant que la table était détachée (ou que le serveur était arrêté) est donc prise en compte par la requête suivante au lieu d’être rejetée : les colonnes déclarées sont toujours validées par rapport au schéma actuel, et les lignes sont décodées en fonction de celui-ci. Ainsi, une modification qui conserve les types ClickHouse mappés (STRING vers BYTES, par exemple) est lue selon les règles du nouveau type, avec le même type de colonne.
• Les lignes écrites via des insertions en streaming arrivent dans le buffer de streaming BigQuery et peuvent mettre un certain temps à devenir visibles lors des lectures ultérieures.
• Un grand INSERT est envoyé à tabledata.insertAll par lots : au plus 500 lignes par requête, avec un découpage supplémentaire afin que chaque requête reste sous la limite de taille de 10 Mo imposée par BigQuery (une ligne unique dépassant cette limite est rejetée avec une erreur explicite).
• Les écritures ne sont pas atomiques et une même requête tabledata.insertAll peut réussir partiellement : BigQuery peut valider certaines lignes d’une requête tout en rejetant les autres avec insertErrors. Les requêtes sont également validées indépendamment les unes des autres ; un lot ultérieur peut donc être rejeté après acceptation de lots précédents. Dans les deux cas, la requête renvoie une erreur, mais les lignes déjà validées restent dans BigQuery. Afin de limiter les doublons, chaque ligne est envoyée avec un insertId stable dérivé de l’ID de requête et de la position ordinale de la ligne dans le flux. BigQuery l’utilise pour effectuer une déduplication au mieux pendant sa fenêtre d’insertion en flux. Un query_id dépassant la limite de 128 caractères de l’insertId BigQuery est haché en un préfixe de longueur fixe, qui reste stable pour ce query_id. Comme l’insertId dépend de la position ordinale, la déduplication n’est fiable que si la réexécution produit les lignes dans le même ordre : une nouvelle tentative d’un lot au niveau du transport est toujours sûre, et la réexécution du même INSERT avec le même query_id ne permet la déduplication que si les lignes sont présentées dans le même ordre (par exemple, avec une insertion monothread ou un ordre déterministe — définissez max_threads = 1 et max_insert_threads = 1 pour un INSERT ... SELECT parallèle dont l’ordre des fragments pourrait autrement varier d’une tentative à l’autre).

​

Voir aussi

• Moteur de table BigQuery