Configura un destino de BigQuery

En esta página, se describe cómo configurar tu destino de BigQuery para transmitir datos de una base de datos de origen con Datastream.

Configura los conjuntos de datos de destino

Cuando configures conjuntos de datos para el destino de BigQuery, puedes seleccionar una de las siguientes opciones:

  • Conjunto de datos para cada esquema: El conjunto de datos se selecciona o crea en la ubicación de BigQuery especificada en función del nombre del esquema de la fuente. Como resultado, para cada esquema en la fuente, Datastream crea automáticamente un conjunto de datos en BigQuery.

    Si seleccionas esta opción, Datastream creará conjuntos de datos en el proyecto que contiene la transmisión.

    Por ejemplo, si tienes una fuente de MySQL que esta fuente tiene una base de datos mydb y una tabla employees dentro de la base de datos, Datastream creará el conjunto de datos mydb y la tabla employees en BigQuery.

  • Conjunto de datos único para todos los esquemas: Puedes seleccionar un conjunto de datos de BigQuery para la transmisión. Datastream transmite todos los datos a este conjunto de datos. Para el conjunto de datos que seleccionas, Datastream crea todas las tablas como <schema>_<table>.

    Por ejemplo, si tienes una fuente de MySQL y esta fuente tiene una base de datos mydb y una tabla employees dentro de la base de datos, Datastream crea la tabla mydb_employees en el conjunto de datos que selecciones.

Comportamiento de escritura

  • El tamaño máximo del evento cuando transmites datos a BigQuery es de 20 MB.

  • Cuando configures tu flujo, podrás seleccionar la forma en que Datastream escribirá tus datos de cambios en BigQuery. Para obtener más información, consulta Configura el modo de escritura.

Configura el modo de escritura

Hay dos modos que puedes usar para definir cómo quieres que se escriban tus datos BigQuery:

  • Combinar: Este es el modo de escritura predeterminado. Cuando se selecciona, BigQuery refleja la forma en que se almacenan tus datos en la base de datos de origen. Esto significa que Datastream escribe todos los cambios en tus datos en BigQuery y, luego, BigQuery los consolida con los datos existentes, lo que crea tablas finales que son réplicas de las tablas de origen. Con el modo Merge, no se conserva ningún registro histórico de los eventos de cambio. Por ejemplo, si insertas y, luego, actualizas una fila, BigQuery solo conserva los datos actualizados. Si luego borras la fila de la tabla de origen, BigQuery ya no conservará ningún registro de esa fila.
  • Solo con el modo de escritura: El modo de escritura solo con el modo de escritura te permite agregar datos a BigQuery como un flujo de cambios (eventos INSERT, UPDATE-INSERT, UPDATE-DELETE y DELETE). Usa este modo cuando necesites retener el estado histórico de tus datos. Para comprender mejor el modo de escritura de solo inserción, considera las siguientes situaciones:
    • Reabastecimiento inicial: Después del reabastecimiento inicial, todos los eventos se escriben en BigQuery como eventos de tipo INSERT, con la misma marca de tiempo, identificador único universal (UUID) y cambio del número de secuencia.
    • Actualización de clave primaria: Cuando cambia una clave primaria, se escriben dos filas en BigQuery:
      • Una fila UPDATE-DELETE con la clave primaria original
      • Una fila UPDATE-INSERT con la clave primaria nueva
    • Actualización de filas: Cuando actualizas una fila, se escribe una sola fila UPDATE-INSERT en BigQuery.
    • Eliminación de filas: Cuando borras una fila, se escribe una sola fila DELETE en BigQuery.

Metadatos de tablas

Datastream agrega una columna STRUCT llamada datastream_metadata a cada una que está escrita en el destino de BigQuery.

Modo de escritura de combinación

Si una tabla tiene una clave primaria en la fuente, la columna contiene los siguientes campos:

  • UUID: Este campo tiene el tipo de datos STRING.
  • SOURCE_TIMESTAMP: Este campo tiene el tipo de datos INTEGER.

Si una tabla no tiene una clave primaria, la columna contiene un campo adicional: IS_DELETED. Este campo tiene el tipo de datos BOOLEAN y indica si los datos que Datastream transmite al destino están asociados con una operación DELETE en la fuente. Las tablas sin claves primarias son de solo anexar.

Modo de escritura de solo agregado

La columna datastream_metadata contiene los mismos campos para las tablas con y sin claves primarias:

  • UUID: Este campo tiene el tipo de datos STRING.
  • SOURCE_TIMESTAMP: Este campo tiene el tipo de datos INTEGER.
  • CHANGE_SEQUENCE_NUMBER: Este campo tiene el tipo de datos STRING. Es un número de secuencia interno que usa Datastream para cada evento de cambio.
  • CHANGE_TYPE: Este campo tiene el tipo de datos STRING. Indica el tipo de evento de cambio: INSERT, UPDATE-INSERT, UPDATE-DELETE o DELETE.
  • SORT_KEYS: Este campo contiene un array de valores STRING. Puedes usar los valores para ordenar los eventos de cambio.

Usa tablas de BigQuery con la opción max_staleness

Como parte de la transferencia casi en tiempo real, Datastream usa la compatibilidad integrada de BigQuery para operaciones de inserción y actualización, como actualizar, insertar y borrar datos. Las operaciones de inserción y actualización te permiten actualizar de forma dinámica el destino de BigQuery a medida que se agregan, modifican o borran filas. Datastream transmite estas operaciones de inserción y actualización en la tabla de destino con la API de Storage Write de BigQuery.

Especifica el límite de inactividad de los datos

BigQuery aplica modificaciones de origen en segundo plano de manera continua o en el tiempo de ejecución de la consulta, según el límite de inactividad de los datos configurado. Cuando Datastream crea una tabla nueva en BigQuery, la opción max_staleness de la tabla se establece según el valor actual del límite de inactividad de los datos de la transmisión.

Para obtener más información sobre el uso de las tablas de BigQuery con la opción max_staleness, consulta Inactividad de la tabla.

Controla los costos de BigQuery

Los costos de BigQuery se cobran por separado de Datastream. Para aprender sobre cómo controlar los costos de BigQuery, consulta los precios de los CDC de BigQuery.

Tipos de datos de mapas

En la siguiente tabla, se enumeran las conversiones de tipos de datos de las bases de datos de origen compatibles al destino de BigQuery.


Base de datos de origen Tipo de datos fuente Tipo de datos de BigQuery
MySQL BIGINT(size) LONG
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 el valor de precisión es <=38 y el valor de escala es <=9, entonces es NUMERIC. De lo contrario, 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) LONG
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, entonces INT64. Si 18<p=<78, asigna a tipos decimales con parámetros. Si p>=79, asigna a STRING.
Oracle NUMBER(precision, scale>0) Si 0<p=<78, asigna a tipos decimales con parámetros. Si p>=79, asigna a 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 precisión es igual a -1, entonces STRING (los tipos NUMERIC de BigQuery requieren una precisión fija). De lo contrario, BIGNUMERIC/NUMERIC. Para obtener más información, consulta la sección Números de precisión arbitrarios en la documentación de 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

Consulta un array de PostgreSQL como un tipo de datos de array de BigQuery

Si prefieres consultar un array de PostgreSQL como un tipo de datos ARRAY de BigQuery, puedes convertir los valores JSON en un array de BigQuery con la función 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

Reemplaza lo siguiente:

  • TYPE: Es el tipo de BigQuery que coincide con el tipo de elemento en del array de PostgreSQL de origen. Por ejemplo, si el tipo de origen es un array de BIGINT y, luego, reemplaza TYPE por INT64.

    Para obtener más información sobre cómo asignar los tipos de datos, consulta Cómo asignar tipos de datos.

  • BQ_COLUMN_NAME: Es el nombre de la columna relevante en la tabla de BigQuery.

Existen 2 excepciones en la forma en que conviertes los valores:

  • Para arrays de valores BIT, BIT_VARYING o BYTEA en la columna de origen, ejecuta la siguiente consulta:

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

  • Para los arreglos de valores JSON o JSONB en la columna de origen, usa la función JSON_QUERY_ARRAY:

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

Limitaciones conocidas

Entre las limitaciones conocidas para usar BigQuery como destino, se incluyen las siguientes:

  • Solo puedes replicar datos en un conjunto de datos de BigQuery que se encuentre en el mismo proyecto de Google Cloud que el flujo de Datastream.
  • De forma predeterminada, Datastream no admite agregar una clave primaria a una tabla que ya está replicada en BigQuery sin una clave primaria ni quitar una clave primaria de una tabla replicada en BigQuery con una clave primaria. Si necesitas realizar esos cambios, comunícate con Atención al cliente de Google. Para obtener información sobre cómo cambiar la definición de clave primaria de una tabla de origen que ya tiene una clave primaria, consulta Cómo diagnosticar problemas.

  • Las claves primarias en BigQuery deben ser de los siguientes tipos de datos:

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

    Datastream no replica las tablas que contienen claves primarias de tipos de datos no admitidos.

  • BigQuery no admite nombres de tablas con caracteres ., $, /, @ ni +. Datastream reemplaza esos caracteres por guiones bajos cuando crea tablas de destino.

    Por ejemplo, table.name en la base de datos de origen se convierte en table_name en BigQuery.

    Para obtener más información sobre los nombres de las tablas en BigQuery, consulta Nombres de tablas.

  • BigQuery no admite más de cuatro columnas de agrupación. Cuando se replica una tabla con más de cuatro columnas de clave primaria, Datastream usa cuatro columnas de clave primaria como columnas de agrupación.
  • Datastream asigna literales de fecha y hora fuera de rango, como los tipos de fecha infinito de PostgreSQL, a los siguientes valores:
    • DATE positivo al valor de 9999-12-31
    • DATE negativo al valor de 0001-01-01
    • TIMESTAMP positivo al valor de 9999-12-31 23:59:59.999000 UTC
    • TIMESTAMP negativo al valor de 0001-01-01 00:00:00 UTC
  • BigQuery no admite tablas de transmisión que tengan claves primarias de tipos de datos FLOAT o REAL. Esas tablas no se replican.

    • Para obtener más información sobre los tipos de fechas y los períodos de BigQuery, consulta Tipos de datos.

¿Qué sigue?