Data type mappings in BigQuery

This page describes data type mappings from various source databases to theircorresponding BigQuery data types. Understand how different data typesconvert when migrating data to BigQuery, how BigQueryrepresents MongoDB binary JSON documents, and how to query PostgreSQL array dataas a BigQueryARRAY data type.

Map data types

The following table lists data type conversions from supported source databases to the BigQuery destination.

Source databaseSource data typeBigQuery data type
MySQLBIGINT(size)INT64
MySQLBIGINT (unsigned)DECIMAL
MySQLBINARY(size)STRING (hex encoded)
MySQLBIT(size)INT64
MySQLBLOB(size)STRING (hex encoded)
MySQLBOOLINT64
MySQLCHAR(size)STRING
MySQLDATEDATE
MySQLDATETIME(fsp)DATETIME
MySQLDECIMAL(precision, scale)If the precision value is<=38, and the scale value is<=9 thenNUMERIC. OtherwiseBIGNUMERIC
MySQLDOUBLE(size, d)FLOAT64
MySQLENUM(val1, val2, val3, ...)STRING
MySQLFLOAT(precision)FLOAT64
MySQLFLOAT(size, d)FLOAT64
MySQLINTEGER(size)INT64
MySQLINTEGER (unsigned)INT64
MySQL

JSON

JSONBOOLEAN,NUMBER,OBJECT,STRING,ARRAY andNULL data types are supported.
MySQLLONGBLOBSTRING (hex encoded)
MySQLLONGTEXTSTRING
MySQLMEDIUMBLOBSTRING (hex encoded)
MySQLMEDIUMINT(size)INT64
MySQLMEDIUMTEXTSTRING
MySQLSET(val1, val2, val3, ...)STRING
MySQLSMALLINT(size)INT64
MySQLTEXT(size)STRING
MySQLTIME(fsp)INTERVAL
MySQLTIMESTAMP(fsp)TIMESTAMP
MySQLTINYBLOBSTRING (hex encoded)
MySQLTINYINT(size)INT64
MySQLTINYTEXTSTRING
MySQLVARBINARY(size)STRING (hex encoded)
MySQLVARCHARSTRING
MySQLYEARINT64
OracleANYDATAUNSUPPORTED
OracleBFILEUNSUPPORTED
OracleBINARY DOUBLEFLOAT64
OracleBINARY FLOATFLOAT64
OracleBLOBBYTES
OracleCHARSTRING
OracleCLOBSTRING
OracleDATEDATETIMEZero dates aren't supported and are replaced withNULL values.
OracleDOUBLE PRECISIONFLOAT64
OracleFLOAT(p)FLOAT64
OracleINTERVAL DAY TO SECONDUNSUPPORTED
OracleINTERVAL YEAR TO MONTHUNSUPPORTED
OracleLONG/LONG RAWUNSUPPORTED
OracleNCHARSTRING
OracleNCLOBSTRING
OracleNUMBERSTRING
OracleNUMBER(precision=*)STRING
OracleNUMBER(precision, scale<=0)If p<=18, thenINT64. If 18<p=<78, then map toparameterized decimal types. If p>=79, map toSTRING
OracleNUMBER(precision, scale>0)If 0<p=<78, then map toparameterized decimal types. If p>=79, map toSTRING
OracleNVARCHAR2STRING
OracleRAWSTRING
OracleROWIDSTRING
OracleSDO_GEOMETRYUNSUPPORTED
OracleSMALLINTINT64
OracleTIMESTAMPTIMESTAMPZero dates aren't supported and are replaced withNULL values.
OracleTIMESTAMP WITH TIME ZONETIMESTAMPZero dates aren't supported and are replaced withNULL values.
OracleUDT (user-defined type)UNSUPPORTED
OracleUROWIDUNSUPPORTED
OracleVARCHARSTRING
OracleVARCHAR2STRING
OracleXMLTYPEUNSUPPORTED
PostgreSQLARRAYJSONArrays ofDATE,TIMESTAMP orTIMESTAMP WITH TIME ZONE data types aren't supported.
PostgreSQLBIGINTINT64
PostgreSQLBITBYTES
PostgreSQLBIT_VARYINGBYTES
PostgreSQLBOOLEANBOOLEAN
PostgreSQLBOXUNSUPPORTED
PostgreSQLBYTEABYTES
PostgreSQLCHARACTERSTRING
PostgreSQLCHARACTER_VARYINGSTRING
PostgreSQLCIDRSTRING
PostgreSQLCIRCLEUNSUPPORTED
PostgreSQLDATEDATE
PostgreSQLDOUBLE_PRECISIONFLOAT64
PostgreSQLENUMSTRING
PostgreSQLINETSTRING
PostgreSQLINTEGERINT64
PostgreSQLINTERVALINTERVAL
PostgreSQLJSONJSON
PostgreSQLJSONBJSON
PostgreSQLLINEUNSUPPORTED
PostgreSQLLSEGUNSUPPORTED
PostgreSQLMACADDRSTRING
PostgreSQLMONEYFLOAT64
PostgreSQLNUMERICIf precision =-1, thenSTRING (BigQueryNUMERIC types require fixed precision). OtherwiseBIGNUMERIC/NUMERIC. For more information, see theArbitrary precision numbers section in PostgreSQL documentation.
PostgreSQLOIDINT64
PostgreSQLPATHUNSUPPORTED
PostgreSQLPOINTUNSUPPORTED
PostgreSQLPOLYGONUNSUPPORTED
PostgreSQLREALFLOAT64
PostgreSQLSMALLINTINT64
PostgreSQLSMALLSERIALINT64
PostgreSQLSERIALINT64
PostgreSQLTEXTSTRING
PostgreSQLTIMETIME
PostgreSQLTIMESTAMPTIMESTAMP
PostgreSQLTIMESTAMP_WITH_TIMEZONETIMESTAMP
PostgreSQLTIME_WITH_TIMEZONETIME
PostgreSQLTSQUERYSTRING
PostgreSQLTSVECTORSTRING
PostgreSQLTXID_SNAPSHOTSTRING
PostgreSQLUUIDSTRING
PostgreSQLXMLSTRING
SQL ServerBIGINTINT64
SQL ServerBINARYBYTES
SQL ServerBITBOOL
SQL ServerCHARSTRING
SQL ServerDATEDATE
SQL ServerDATETIME2DATETIME
SQL ServerDATETIMEDATETIME
SQL ServerDATETIMEOFFSETTIMESTAMP
SQL ServerDECIMALBIGNUMERIC
SQL ServerFLOATFLOAT64
SQL ServerIMAGEBYTES
SQL ServerINTINT64
SQL ServerMONEYBIGNUMERIC
SQL ServerNCHARSTRING
SQL ServerNTEXTSTRING
SQL ServerNUMERICBIGNUMERIC
SQL ServerNVARCHARSTRING
SQL ServerNVARCHAR(MAX)STRING
SQL ServerREALFLOAT64
SQL ServerSMALLDATETIMEDATETIME
SQL ServerSMALLINTINT64
SQL ServerSMALLMONEYNUMERIC
SQL ServerTEXTSTRING
SQL ServerTIMETIME
SQL ServerTIMESTAMP/ROWVERSIONBYTES
SQL ServerTINYINTINT64
SQL ServerUNIQUEIDENTIFIERSTRING
SQL ServerVARBINARYBYTES
SQL ServerVARBINARY(MAX)BYTES
SQL ServerVARCHARSTRING
SQL ServerVARCHAR(MAX)STRING
SQL ServerXMLSTRING
SalesforceBOOLEANBOOLEAN
SalesforceBYTEBYTES
SalesforceDATEDATE
SalesforceDATETIMEDATETIME
SalesforceDOUBLEBIGNUMERIC
SalesforceINTINT64
SalesforceSTRINGSTRING
SalesforceTIMETIME
SalesforceANYTYPE (can be eitherSTRING,DATE,NUMBER, orBOOLEAN)STRING
SalesforceCOMBOBOXSTRING
SalesforceCURRENCYFLOAT64

Maximum allowed length is 18 digits.

SalesforceDATACATEGORYGROUPREFERENCESTRING
SalesforceEMAILSTRING
SalesforceENCRYPTEDSTRINGSTRING
SalesforceIDSTRING
SalesforceJUNCTIONIDLISTSTRING
SalesforceMASTERRECORDSTRING
SalesforceMULTIPICKLISTSTRING
SalesforcePERCENTFLOAT64

Maximum allowed length is 18 digits.

SalesforcePHONESTRING
SalesforcePICKLISTSTRING
SalesforceREFERENCESTRING
SalesforceTEXTAREASTRING

Maximum allowed length is 255 characters.

SalesforceURLSTRING

MongoDB data types

MongoDB binary JSON (BSON) documents are written to BigQuery inMongoDB Extended JSON (v1)strict mode format. The table shows how data types are represented inBigQuery, along with example values.

Source data typeExample valueBigQuery JSON type value
DOUBLE3.14159265353.1415926535
STRING"Hello, MongoDB!""Hello, MongoDB!"
ARRAY
[    "item1",    123,    true,    { subItem: "object in array" }  ]
["item1",123,true,{"subItem":"object in array"}]
BINARY DATAnew BinData(0, "SGVsbG8gQmluYXJ5IERhdGE="){"$binary":"SGVsbG8gQmluYXJ5IERhdGE=","$type":"00"}
BOOLEANtruetrue
DATE2024-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}}

Query a PostgreSQL array as a BigQuery array data type

If you prefer to query a PostgreSQL array as aBigQueryARRAY data type,you can convert theJSON values to a BigQuery array using the BigQueryJSON_VALUE_ARRAY function:

SELECTARRAY(SELECTCAST(elementASTYPE)FROMUNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$'))ASelement)ASarray_col

Replace the following:

  • TYPE: the BigQuery type that matches the element type inthe PostgreSQL source array. For example, if the source type is an array ofBIGINT values, then replaceTYPE withINT64.

    For more information about how to map the data types, seeMap data types.

  • BQ_COLUMN_NAME: the name of the relevant column in the BigQuerytable.

There are 2 exceptions to the way that you convert the values:

  • For arrays ofBIT,BIT_VARYING orBYTEA values in the source column,run the following query:

    SELECTARRAY(SELECTFROM_BASE64(element)FROMUNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$'))ASelement)ASarray_of_bytes

  • For arrays ofJSON orJSONB values in the source column, use theJSON_QUERY_ARRAYfunction:

    SELECTARRAY(SELECTelementFROMUNNEST(JSON_QUERY_ARRAY(BQ_COLUMN_NAME,'$'))ASelement)ASarray_of_jsons

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.