Configurer une destination BigQuery

Cette page explique comment configurer votre destination BigQuery pour diffuser des données en flux continu à partir d'une base de données source à l'aide de Datastream.

Configurer les ensembles de données de destination

Lorsque vous configurez des ensembles de données pour la destination BigQuery, vous pouvez sélectionner l'une des options suivantes :

  • Ensemble de données pour chaque schéma : l'ensemble de données est sélectionné ou créé dans l'emplacement BigQuery spécifié, en fonction du nom du schéma de la source. Par conséquent, pour chaque schéma de la source, Datastream crée automatiquement un ensemble de données dans BigQuery.

    Par exemple, si vous disposez d'une source MySQL avec une base de données mydb et une table employees dans cette base de données, Datastream crée l'ensemble de données mydb et la table employees dans BigQuery.

    Si vous sélectionnez cette option, Datastream crée des ensembles de données dans le projet contenant le flux. Bien que vous n'ayez pas besoin de créer les ensembles de données dans la même région que votre flux, nous vous recommandons de conserver toutes les ressources du flux, ainsi que les ensembles de données, dans la même région pour optimiser les coûts et les performances.

  • Ensemble de données unique pour tous les schémas : vous pouvez sélectionner un ensemble de données BigQuery pour le flux. Datastream diffuse toutes les données dans cet ensemble de données. Pour l'ensemble de données que vous sélectionnez, Datastream crée toutes les tables au format <schema>_<table>.

    Par exemple, si vous disposez d'une source MySQL avec une base de données mydb et une table employees dans cette base de données, Datastream crée la table mydb_employees dans l'ensemble de données que vous sélectionnez.

Comportement d'écriture

  • La taille maximale d'un événement lorsque vous diffusez des données dans BigQuery est de 20 Mo.

  • Lorsque vous configurez votre flux, vous pouvez sélectionner la manière dont Datastream écrit vos données de modification dans BigQuery. Pour en savoir plus, consultez Configurer le mode écriture.

Configurer le mode écriture

Vous pouvez utiliser deux modes pour définir la façon dont vous souhaitez que vos données soient écrites dans BigQuery :

  • Fusionner : il s'agit du mode d'écriture par défaut. Lorsque cette option est sélectionnée, BigQuery reflète la façon dont vos données sont stockées dans la base de données source. Cela signifie que Datastream écrit toutes les modifications apportées à vos données dans BigQuery, qui les consolide ensuite avec les données existantes. Il crée ainsi des tables finales qui sont des répliques des tables sources. En mode Fusionner, aucun enregistrement historique des événements de modification n'est conservé. Par exemple, si vous insérez une ligne, puis la mettez à jour, BigQuery ne conserve que les données mises à jour. Si vous supprimez ensuite la ligne de la table source, BigQuery ne conserve plus aucun enregistrement de cette ligne.
  • Ajout uniquement : le mode d'écriture "Ajout uniquement" vous permet d'ajouter des données à BigQuery sous forme de flux de modifications (événements INSERT, UPDATE-INSERT, UPDATE-DELETE et DELETE). Utilisez ce mode lorsque vous devez conserver l'état historique de vos données. Pour mieux comprendre le mode écriture seule, examinons les scénarios suivants :
    • Remplissage initial : après le remplissage initial, tous les événements sont écrits dans BigQuery en tant qu'événements de type INSERT, avec le même code temporel, le même identifiant unique universel (UUID) et le même numéro de séquence de modification.
    • Mise à jour de la clé primaire : lorsqu'une clé primaire change, deux lignes sont écrites dans BigQuery :
      • Une ligne UPDATE-DELETE avec la clé primaire d'origine
      • Une ligne UPDATE-INSERT avec la nouvelle clé primaire
    • Mise à jour d'une ligne : lorsqu'une ligne est mise à jour, une seule ligne UPDATE-INSERT est écrite dans BigQuery.
    • Suppression de lignes : lorsqu'une ligne est supprimée, une seule ligne DELETE est écrite dans BigQuery.

Métadonnées de table

Datastream ajoute une colonne STRUCT nommée datastream_metadata à chaque table écrite dans la destination BigQuery.

Mode écriture de fusion

Si une table possède une clé primaire au niveau de la source, la colonne contient les champs suivants :

  • UUID : ce champ est de type de données STRING.
  • SOURCE_TIMESTAMP : ce champ est de type de données INTEGER.

Si une table ne comporte pas de clé primaire, la colonne contient un champ supplémentaire : IS_DELETED. Ce champ est de type de données BOOLEAN. Il indique si les données que Datastream diffuse vers la destination sont associées à une opération DELETE au niveau de la source. Les tables sans clé primaire sont en mode ajout uniquement.

Mode écriture "Ajout uniquement"

La colonne datastream_metadata contient les mêmes champs pour les tables avec et sans clé primaire :

  • UUID : ce champ est de type de données STRING.
  • SOURCE_TIMESTAMP : ce champ est de type de données INTEGER.
  • CHANGE_SEQUENCE_NUMBER : ce champ est de type de données STRING. Il s'agit d'un numéro de séquence interne utilisé par Datastream pour chaque événement de modification.
  • CHANGE_TYPE : ce champ est de type de données STRING. Il indique le type d'événement de modification : INSERT, UPDATE-INSERT, UPDATE-DELETE ou DELETE.
  • SORT_KEYS : ce champ contient un tableau de valeurs STRING. Vous pouvez utiliser les valeurs pour trier les événements de modification.

Utiliser des tables BigQuery avec l'option max_staleness

Pour l'ingestion en temps quasi réel, Datastream utilise la compatibilité intégrée de BigQuery avec les opérations d'upsert, comme la mise à jour, l'insertion et la suppression de données. Les opérations d'upsert vous permettent de mettre à jour dynamiquement la destination BigQuery à mesure que des lignes sont ajoutées, modifiées ou supprimées. Datastream diffuse ces opérations d'upsert dans la table de destination à l'aide de l'API BigQuery Storage Write.

Spécifier la limite d'obsolescence des données

BigQuery applique les modifications de la source en arrière-plan de manière continue ou au moment de l'exécution de la requête, en fonction de la limite d'obsolescence des données configurée. Lorsque Datastream crée une table dans BigQuery, l'option max_staleness de la table est définie en fonction de la valeur actuelle de la limite d'obsolescence des données pour le flux.

Pour en savoir plus sur l'utilisation des tables BigQuery avec l'option max_staleness, consultez Latence des tables.

Contrôler les coûts BigQuery

Les coûts BigQuery sont facturés séparément de ceux de Datastream. Pour savoir comment contrôler vos coûts BigQuery, consultez Tarifs de la CDC BigQuery.

Mapper les types de données

Le tableau suivant liste les conversions de types de données des bases de données sources compatibles vers la destination BigQuery.


Base de données source Type de données source Type de données BigQuery
MySQL BIGINT(size) INT64
MySQL BIGINT (unsigned) DECIMAL
MySQL BINARY(size) STRING (hex encoded)
MySQL BIT(size) INT64
MySQL BLOB(size) STRING (hex encoded)
MySQL BOOL INT64
MySQL CHAR(size) STRING
MySQL DATE DATE
MySQL DATETIME(fsp) DATETIME
MySQL DECIMAL(precision, scale) Si la valeur de précision est <=38 et la valeur d'échelle est <=9, alors NUMERIC. Sinon, BIGNUMERIC
MySQL DOUBLE(size, d) FLOAT64
MySQL ENUM(val1, val2, val3, ...) STRING
MySQL FLOAT(precision) FLOAT64
MySQL FLOAT(size, d) FLOAT64
MySQL INTEGER(size) INT64
MySQL INTEGER (unsigned) INT64
MySQL

JSON

 JSON
MySQL LONGBLOB STRING (hex encoded)
MySQL LONGTEXT STRING
MySQL MEDIUMBLOB STRING (hex encoded)
MySQL MEDIUMINT(size) INT64
MySQL MEDIUMTEXT STRING
MySQL SET(val1, val2, val3, ...) STRING
MySQL SMALLINT(size) INT64
MySQL TEXT(size) STRING
MySQL TIME(fsp) INTERVAL
MySQL TIMESTAMP(fsp) TIMESTAMP
MySQL TINYBLOB STRING (hex encoded)
MySQL TINYINT(size) INT64
MySQL TINYTEXT STRING
MySQL VARBINARY(size) STRING (hex encoded)
MySQL VARCHAR STRING
MySQL YEAR INT64
Oracle ANYDATA UNSUPPORTED
Oracle BFILE STRING
Oracle BINARY DOUBLE FLOAT64
Oracle BINARY FLOAT FLOAT64
Oracle BLOB BYTES
Oracle CHAR STRING
Oracle CLOB STRING
Oracle DATE DATETIME
Oracle DOUBLE PRECISION FLOAT64
Oracle FLOAT(p) FLOAT64
Oracle INTERVAL DAY TO SECOND UNSUPPORTED
Oracle INTERVAL YEAR TO MONTH UNSUPPORTED
Oracle LONG/LONG RAW STRING
Oracle NCHAR STRING
Oracle NCLOB STRING
Oracle NUMBER STRING
Oracle NUMBER(precision=*) STRING
Oracle NUMBER(precision, scale<=0) Si p<=18, alors INT64. Si 18<p=<78, mappez-le sur les types décimaux paramétrés. Si p>=79, mappez sur STRING
Oracle NUMBER(precision, scale>0) Si 0<p<78, mappez aux types décimaux paramétrés. Si p>=79, mappez sur STRING
Oracle NVARCHAR2 STRING
Oracle RAW STRING
Oracle ROWID STRING
Oracle SDO_GEOMETRY UNSUPPORTED
Oracle SMALLINT INT64
Oracle TIMESTAMP TIMESTAMP
Oracle TIMESTAMP WITH TIME ZONE TIMESTAMP
Oracle UDT (user-defined type) UNSUPPORTED
Oracle UROWID STRING
Oracle VARCHAR STRING
Oracle VARCHAR2 STRING
Oracle XMLTYPE UNSUPPORTED
PostgreSQL ARRAY JSON
PostgreSQL BIGINT INT64
PostgreSQL BIT BYTES
PostgreSQL BIT_VARYING BYTES
PostgreSQL BOOLEAN BOOLEAN
PostgreSQL BOX UNSUPPORTED
PostgreSQL BYTEA BYTES
PostgreSQL CHARACTER STRING
PostgreSQL CHARACTER_VARYING STRING
PostgreSQL CIDR STRING
PostgreSQL CIRCLE UNSUPPORTED
PostgreSQL DATE DATE
PostgreSQL DOUBLE_PRECISION FLOAT64
PostgreSQL ENUM STRING
PostgreSQL INET STRING
PostgreSQL INTEGER INT64
PostgreSQL INTERVAL INTERVAL
PostgreSQL JSON JSON
PostgreSQL JSONB JSON
PostgreSQL LINE UNSUPPORTED
PostgreSQL LSEG UNSUPPORTED
PostgreSQL MACADDR STRING
PostgreSQL MONEY FLOAT64
PostgreSQL NUMERIC Si la précision est définie sur -1, alors STRING (les types NUMERIC BigQuery nécessitent une précision fixe). Sinon, BIGNUMERIC/NUMERIC. Pour en savoir plus, consultez la section Nombres à précision arbitraire dans la documentation PostgreSQL.
PostgreSQL OID INT64
PostgreSQL PATH UNSUPPORTED
PostgreSQL POINT UNSUPPORTED
PostgreSQL POLYGON UNSUPPORTED
PostgreSQL REAL FLOAT64
PostgreSQL SMALLINT INT64
PostgreSQL SMALLSERIAL INT64
PostgreSQL SERIAL INT64
PostgreSQL TEXT STRING
PostgreSQL TIME TIME
PostgreSQL TIMESTAMP TIMESTAMP
PostgreSQL TIMESTAMP_WITH_TIMEZONE TIMESTAMP
PostgreSQL TIME_WITH_TIMEZONE TIME
PostgreSQL TSQUERY STRING
PostgreSQL TSVECTOR STRING
PostgreSQL TXID_SNAPSHOT STRING
PostgreSQL UUID STRING
PostgreSQL XML STRING
SQL Server BIGINT INT64
SQL Server BINARY BYTES
SQL Server BIT BOOL
SQL Server CHAR STRING
SQL Server DATE DATE
SQL Server DATETIME2 DATETIME
SQL Server DATETIME DATETIME
SQL Server DATETIMEOFFSET TIMESTAMP
SQL Server DECIMAL BIGNUMERIC
SQL Server FLOAT FLOAT64
SQL Server IMAGE BYTES
SQL Server INT INT64
SQL Server MONEY BIGNUMERIC
SQL Server NCHAR STRING
SQL Server NTEXT STRING
SQL Server NUMERIC BIGNUMERIC
SQL Server NVARCHAR STRING
SQL Server NVARCHAR(MAX) STRING
SQL Server REAL FLOAT64
SQL Server SMALLDATETIME DATETIME
SQL Server SMALLINT INT64
SQL Server SMALLMONEY NUMERIC
SQL Server TEXT STRING
SQL Server TIME TIME
SQL Server TIMESTAMP/ROWVERSION BYTES
SQL Server TINYINT INT64
SQL Server UNIQUEIDENTIFIER STRING
SQL Server VARBINARY BYTES
SQL Server VARBINARY(MAX) BYTES
SQL Server VARCHAR STRING
SQL Server VARCHAR(MAX) STRING
SQL Server XML STRING
Salesforce BOOLEAN BOOLEAN
Salesforce BYTE BYTES
Salesforce DATE DATE
Salesforce DATETIME DATETIME
Salesforce DOUBLE BIGNUMERIC
Salesforce INT INT64
Salesforce STRING STRING
Salesforce TIME TIME
Salesforce ANYTYPE (STRING, DATE, NUMBER ou BOOLEAN) STRING
Salesforce COMBOBOX STRING
Salesforce CURRENCY FLOAT64

La longueur maximale autorisée est de 18 chiffres.
Salesforce DATACATEGORYGROUPREFERENCE STRING
Salesforce EMAIL STRING
Salesforce ENCRYPTEDSTRING STRING
Salesforce ID STRING
Salesforce JUNCTIONIDLIST STRING
Salesforce MASTERRECORD STRING
Salesforce MULTIPICKLIST STRING
Salesforce PERCENT FLOAT64

La longueur maximale autorisée est de 18 chiffres.
Salesforce PHONE STRING
Salesforce PICKLIST STRING
Salesforce REFERENCE STRING
Salesforce TEXTAREA STRING

La longueur maximale autorisée est de 255 caractères.
Salesforce URL STRING

Types de données MongoDB

Les documents MongoDB au format BSON (Binary JSON) sont écrits dans BigQuery au format MongoDB Extended JSON (v1) en mode strict. Le tableau indique comment les types de données sont représentés dans BigQuery, avec des exemples de valeurs.

Type de données sourceExemple de valeurValeur de type JSON BigQuery
DOUBLE 3.1415926535 3.1415926535
STRING"Hello, MongoDB!""Hello, MongoDB!"
ARRAY
[
    "item1",
    123,
    true,
    { subItem: "object in array" }
  ]
["item1",123,true,{"subItem":"object in array"}]
BINARY DATA new BinData(0, "SGVsbG8gQmluYXJ5IERhdGE=") {"$binary":"SGVsbG8gQmluYXJ5IERhdGE=","$type":"00"}
BOOLEANtruetrue
DATE 2024-12-25T10:30:00.000+00:00 {"$date": 1735122600000}
NULLnullnull
REGEX/^mongo(db)?$/i{"$options":"i","$regex":"^mongo(db)?$"}
JAVASCRIPTfunction() {return this.stringField.length;}{"$code":"function() {\n return this.stringField.length;\n }"}
DECIMAL128NumberDecimal("1234567890.1234567890"){"$numberDecimal":"1234567890.1234567890"}
OBJECTIDObjectId('673c5d8dbfe2e51808cc2c3d'){"$oid": "673c5d8dbfe2e51808cc2c3d"}
LONG3567587327{"$numberLong": "3567587327"}
INT324242
INT641864712049423024127{"$numberLong": "1864712049423024127"}
TIMESTAMPnew Timestamp(1747888877, 1){"$timestamp":{"i":1,"t":1747888877}}

Interroger un tableau PostgreSQL en tant que type de données de tableau BigQuery

Si vous préférez interroger un tableau PostgreSQL en tant que type de données ARRAY BigQuery, vous pouvez convertir les valeurs JSON en tableau BigQuery à l'aide de la fonction JSON_VALUE_ARRAY de BigQuery :

  SELECT ARRAY(SELECT CAST(element AS TYPE) FROM UNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_col

Remplacez les éléments suivants :

  • TYPE : type BigQuery correspondant au type d'élément du tableau source PostgreSQL. Par exemple, si le type de source est un tableau de valeurs BIGINT, remplacez TYPE par INT64.

    Pour en savoir plus sur le mappage des types de données, consultez Mapper les types de données.

  • BQ_COLUMN_NAME : nom de la colonne concernée dans la table BigQuery.

Il existe deux exceptions à la façon dont vous convertissez les valeurs :

  • Pour les tableaux de valeurs BIT, BIT_VARYING ou BYTEA dans la colonne source, exécutez la requête suivante :

    SELECT ARRAY(SELECT FROM_BASE64(element) FROM UNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_of_bytes

  • Pour les tableaux de valeurs JSON ou JSONB dans la colonne source, utilisez la fonction JSON_QUERY_ARRAY :

    SELECT ARRAY(SELECT element FROM UNNEST(JSON_QUERY_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_of_jsons

Limitations connues

Les limites connues d'utilisation de BigQuery en tant que destination incluent les suivantes :

  • Vous ne pouvez répliquer des données dans un ensemble de données BigQuery que s'il se trouve dans le même projet Google Cloud que le flux Datastream.
  • Par défaut, Datastream n'accepte pas l'ajout d'une clé primaire à une table déjà répliquée dans BigQuery sans clé primaire, ni la suppression d'une clé primaire d'une table répliquée dans BigQuery avec une clé primaire. Si vous devez effectuer de telles modifications, contactez l'assistance Google. Pour savoir comment modifier la définition de la clé primaire d'une table source qui en possède déjà une, consultez Diagnostiquer les problèmes.

  • Dans BigQuery, les clés primaires doivent être des types de données suivants :

    • DATE
    • BOOL
    • GEOGRAPHY
    • INT64
    • NUMERIC
    • BIGNUMERIC
    • STRING
    • TIMESTAMP
    • DATETIME

    Les tables contenant des clés primaires de types de données non compatibles ne sont pas répliquées par Datastream.

  • BigQuery n'accepte pas les noms de tables contenant les caractères ., $, /, @ ou +. Datastream remplace ces caractères par des traits de soulignement lors de la création de tables de destination.

    Par exemple, table.name dans la base de données source devient table_name dans BigQuery.

    Pour en savoir plus sur les noms de tables dans BigQuery, consultez Nommer les tables.

  • BigQuery n'accepte pas plus de quatre colonnes de clustering. Lorsque vous répliquez une table comportant plus de quatre colonnes de clé primaire, Datastream utilise quatre colonnes de clé primaire comme colonnes de clustering.

  • Datastream mappe les littéraux de date et d'heure hors plage, tels que les types de date PostgreSQL "infinity", sur les valeurs suivantes :

    • Définissez DATE sur la valeur positive de 9999-12-31.
    • Attribuez la valeur négative de 0001-01-01 à DATE.
    • Définissez TIMESTAMP sur la valeur positive de 9999-12-31 23:59:59.999000 UTC.
    • Attribuez la valeur négative de 0001-01-01 00:00:00 UTC à TIMESTAMP.

  • BigQuery n'accepte pas les tables de flux qui comportent des clés primaires de types de données FLOAT ou REAL. Ces tables ne sont pas répliquées. Pour en savoir plus sur les types et les plages de dates BigQuery, consultez Types de données.

  • Si votre source est Salesforce, l'option de configuration Ensemble de données pour chaque schéma n'est pas disponible.

Étapes suivantes