Asignaciones de tipos de datos en BigQuery

Datastream convierte automáticamente los tipos de datos de las fuentes compatibles en tipos de datos de BigQuery compatibles. Comprender estas asignaciones te ayuda a planificar esquemas de destino, trabajar con documentos JSON binarios (BSON) de MongoDB en BigQuery y consultar arrays de PostgreSQL como un tipo de datos ARRAY 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) 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 el valor de precisión es ≤ 38 y el valor de escala es ≤ 9, entonces NUMERIC. En caso 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) INT64
MySQL

JSON

 JSON Se admiten los tipos de datos
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 UNSUPPORTED
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 o LONG RAW UNSUPPORTED
Oracle NCHAR STRING
Oracle NCLOB STRING
Oracle NUMBER STRING
Oracle NUMBER(precision=*) STRING
Oracle NUMBER(precision, scale<=0) Si la precisión es ≤ 18, entonces INT64. Si 18 < precisión ≤ 78, asigna a tipos decimales parametrizados. Si la precisión es ≥79, se asigna a STRING.
Oracle NUMBER(precision, scale>0) Si 0 < precisión ≤ 78, asigna a tipos decimales parametrizados. Si la precisión es ≥79, se 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 UNSUPPORTED
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 -1, entonces STRING (los tipos NUMERIC de BigQuery requieren una precisión fija). De lo contrario, BIGNUMERIC o NUMERIC. Para obtener más información, consulta la sección Números de precisión arbitraria 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 TIME ZONE TIMESTAMP
PostgreSQL TIME WITH TIME ZONE 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 Si el valor de la escala es ≤ 9 y el valor de (precision - scale) es ≤ 29, entonces NUMERIC. De lo contrario, BIGNUMERIC.
SQL Server FLOAT FLOAT64
SQL Server GEOGRAPHY STRUCT
SQL Server GEOMETRY STRUCT
SQL Server IMAGE BYTES
SQL Server INT INT64
SQL Server MONEY NUMERIC
SQL Server NCHAR STRING
SQL Server NTEXT STRING
SQL Server NUMERIC Si el valor de la escala es ≤ 9 y el valor de (precision - scale) es ≤ 29, entonces NUMERIC. De lo contrario, 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 o 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
(puede ser STRING, DATE, NUMBER o BOOLEAN)		 STRING
Salesforce COMBOBOX STRING
Salesforce CURRENCY FLOAT64

La longitud máxima permitida es de 18 dígitos.
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 longitud máxima permitida es de 18 dígitos.
Salesforce PHONE STRING
Salesforce PICKLIST STRING
Salesforce REFERENCE STRING
Salesforce TEXTAREA STRING

La longitud máxima permitida es de 255 caracteres.
Salesforce URL STRING
Spanner (GoogleSQL) ARRAY JSON
Spanner (GoogleSQL) BOOL BOOLEAN
Spanner (GoogleSQL) BYTES BYTES
Spanner (GoogleSQL) DATE DATE
Spanner (GoogleSQL) FLOAT32 FLOAT64
Spanner (GoogleSQL) FLOAT64 FLOAT64
Spanner (GoogleSQL) INT64 INT64
Spanner (GoogleSQL) JSON JSON
Spanner (GoogleSQL) NUMERIC STRING
Spanner (GoogleSQL) STRING STRING
Spanner (GoogleSQL) TIMESTAMP TIMESTAMP WITH TIME ZONE
Spanner (GoogleSQL) UUID STRING
Spanner (GoogleSQL) PROTO UNSUPPORTED
Spanner (GoogleSQL) ENUM UNSUPPORTED
Spanner (PostgreSQL) ARRAY JSON
Spanner (PostgreSQL) BIGINT INT64
Spanner (PostgreSQL) BOOL BOOLEAN
Spanner (PostgreSQL) BYTEA BYTES
Spanner (PostgreSQL) DATE DATE
Spanner (PostgreSQL) DECIMAL STRING
Spanner (PostgreSQL) DOUBLE PRECISION FLOAT64
Spanner (PostgreSQL) FLOAT8 FLOAT64
Spanner (PostgreSQL) INT8 INT64
Spanner (PostgreSQL) JSONB JSON
Spanner (PostgreSQL) NUMERIC STRING
Spanner (PostgreSQL) TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
Spanner (PostgreSQL) TIMESTAMPZ TIMESTAMP WITH TIME ZONE
Spanner (PostgreSQL) UUID STRING
Spanner (PostgreSQL) VARCHAR STRING

Tipos de datos de MongoDB

Los documentos JSON binarios (BSON) de MongoDB se escriben en BigQuery en uno de dos modos, según lo que especifiques durante la creación de la transmisión (consulta Administra transmisiones): Modo canónico (predeterminado) o modo estricto.

  • Modo canónico (predeterminado): Es el formato moderno y más descriptivo que prioriza la fidelidad de los datos. Garantiza que cada tipo de BSON esté etiquetado de forma explícita, por ejemplo, que distinga entre un número entero de 32 y 64 bits, para evitar la pérdida de precisión durante el intercambio de datos. El modo canónico puede ser un poco más lento que el modo estricto, pero admite todos los tipos de datos. Para obtener más información, consulta JSON extendido de MongoDB.
  • Modo estricto: Es un formato heredado diseñado para ser compatible con los analizadores de JSON, que usa convenciones específicas, como "$date", para representar tipos que no existen en el JSON estándar. No se admiten ciertos tipos de datos, como Infinity, -Infinity y NaN. Para obtener más información, consulta JSON extendido de MongoDB (v1).

En la siguiente tabla, se muestra cómo se representan los tipos de datos en BigQuery en ambos modos, junto con valores de ejemplo:

Tipo de datos BSON Valor de ejemplo Modo canónico Modo estricto
DOUBLE 3.1415926535 {"$numberDouble":"3.1415926535"} 3.1415926535
Infinity Infinity {"$numberDouble":"Infinity"} No compatible
-Infinity -Infinity {"$numberDouble":"-Infinity"} No compatible
NaN NaN {"$numberDouble":"NaN"} No compatible
STRING "Hello, MongoDB!" "Hello, MongoDB!" "Hello, MongoDB!"
ARRAY 
[
  "item1",
  123,
  true,
  { subItem: "object in array" }
]
["item1",{"$numberInt":"123"},true,{"subItem":"object in array"}] ["item1",123,true,{"subItem":"object in array"}]
BINARY DATA new BinData(0, "SGVsbG8gQmluYXJ5IERhdGE=") {"$binary":{"base64":"SGVsbG8gQmluYXJ5IERhdGE=","subType":"00"}} {"$binary":"SGVsbG8gQmluYXJ5IERhdGE=","$type":"00"}
BOOLEAN true true true
DATE 2024-12-25T10:30:00.000+00:00 {"$date":{"$numberLong":"1735122600000"}} {"$date": 1735122600000}
NULL null null null
REGEX /^mongo(db)?$/i {"$regularExpression":{"pattern":"^mongo(db)?$","options":"i"}} {"$options":"i","$regex":"^mongo(db)?$"}
JAVASCRIPT function() {return this.stringField.length;} {"$code":"function() {\n return this.stringField.length;\n }"} {"$code":"function() {\n return this.stringField.length;\n }"}
DECIMAL128 NumberDecimal("1234567890.1234567890") {"$numberDecimal":"1234567890.1234567890"} {"$numberDecimal":"1234567890.1234567890"}
OBJECTID ObjectId('673c5d8dbfe2e51808cc2c3d') {"$oid": "673c5d8dbfe2e51808cc2c3d"} {"$oid": "673c5d8dbfe2e51808cc2c3d"}
LONG 3567587327 {"$numberLong": "3567587327"} {"$numberLong": "3567587327"}
INT32 42 {"$numberInt": "42"} 42
INT64 1864712049423024127 {"$numberLong": "1864712049423024127"} {"$numberLong": "1864712049423024127"}
TIMESTAMP new Timestamp(1747888877, 1) {"$timestamp":{"t":1747888877,"i":1}} {"$timestamp":{"i":1,"t":1747888877}}

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

Para consultar un array de PostgreSQL como un tipo de datos ARRAY de BigQuery, convierte los valores de 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 el array de origen de PostgreSQL. Por ejemplo, si el tipo de fuente es un array de valores BIGINT, reemplaza TYPE por INT64. Para obtener más información sobre cómo asignar los tipos de datos, consulta Asigna tipos de datos.
  • BQ_COLUMN_NAME: Es el nombre de la columna pertinente en la tabla de BigQuery.

Existen dos excepciones a la forma en que conviertes los valores:

  • Para los 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 arrays 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