מיפוי של סוגי נתונים ב-BigQuery

‫Datastream ממיר באופן אוטומטי סוגי נתונים ממקורות נתמכים לסוגי נתונים תואמים ב-BigQuery. הבנת המיפויים האלה עוזרת לתכנן סכימות של יעדים, לעבוד עם מסמכי JSON בינאריים (BSON) של MongoDB ב-BigQuery ולשאול שאילתות על מערכים של PostgreSQL כסוג נתונים של ARRAY ב-BigQuery.

מיפוי סוגי נתונים

בטבלה הבאה מפורטות המרות של סוגי נתונים ממסדי נתונים נתמכים של מקורות אל היעד ב-BigQuery.

מסד נתונים של המקור סוג נתוני המקור סוג נתונים ב-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) אם ערך הדיוק הוא ≤ 38 וערך קנה המידה הוא ≤ 9, אז NUMERIC. אחרת, 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 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 או LONG RAW UNSUPPORTED
Oracle NCHAR STRING
Oracle NCLOB STRING
Oracle NUMBER STRING
Oracle NUMBER(precision=*) STRING
Oracle NUMBER(precision, scale<=0) אם הדיוק קטן מ-18 או שווה ל-18, אז INT64. אם ‎18 < precision ≤ 78‎, ממפים לסוגים עשרוניים עם פרמטרים. אם רמת הדיוק ≥ 79, המיפוי הוא ל-STRING.
Oracle NUMBER(precision, scale>0) אם ‎0 < precision ≤ 78‎, ממפים לסוגים עשרוניים עם פרמטרים. אם רמת הדיוק היא 79 ומעלה, ממפים ל-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 אם precision = -1, אז STRING (סוגי NUMERIC ב-BigQuery דורשים דיוק קבוע). אחרת, BIGNUMERIC או NUMERIC. למידע נוסף, קראו את הקטע Arbitrary precision numbers במסמכי התיעוד של 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 BIGINT INT64
שרת SQL BINARY BYTES
שרת SQL BIT BOOL
שרת SQL CHAR STRING
שרת SQL DATE DATE
שרת SQL DATETIME2 DATETIME
שרת SQL DATETIME DATETIME
שרת SQL DATETIMEOFFSET TIMESTAMP
שרת SQL DECIMAL אם ערך הסולם הוא ≤ 9 והערך של (precision - scale) הוא ≤ 29, אז NUMERIC. אחרת, BIGNUMERIC.
שרת SQL FLOAT FLOAT64
שרת SQL GEOGRAPHY STRUCT
שרת SQL GEOMETRY STRUCT
שרת SQL IMAGE BYTES
שרת SQL INT INT64
שרת SQL MONEY NUMERIC
שרת SQL NCHAR STRING
שרת SQL NTEXT STRING
שרת SQL NUMERIC אם ערך הסולם הוא ≤ 9 והערך של (precision - scale) הוא ≤ 29, אז NUMERIC. אחרת, BIGNUMERIC.
שרת SQL NVARCHAR STRING
שרת SQL NVARCHAR(MAX) STRING
שרת SQL REAL FLOAT64
שרת SQL SMALLDATETIME DATETIME
שרת SQL SMALLINT INT64
שרת SQL SMALLMONEY NUMERIC
שרת SQL TEXT STRING
שרת SQL TIME TIME
שרת SQL TIMESTAMP או ROWVERSION BYTES
שרת SQL TINYINT INT64
שרת SQL UNIQUEIDENTIFIER STRING
שרת SQL VARBINARY BYTES
שרת SQL VARBINARY(MAX) BYTES
שרת SQL VARCHAR STRING
שרת SQL VARCHAR(MAX) STRING
שרת SQL 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 או BOOLEAN)		 STRING
Salesforce COMBOBOX STRING
Salesforce CURRENCY FLOAT64

האורך המקסימלי המותר הוא 18 ספרות.
Salesforce DATACATEGORYGROUPREFERENCE STRING
Salesforce EMAIL STRING
Salesforce ENCRYPTEDSTRING STRING
Salesforce ID STRING
Salesforce JUNCTIONIDLIST STRING
Salesforce MASTERRECORD STRING
Salesforce MULTIPICKLIST STRING
Salesforce PERCENT FLOAT64

האורך המקסימלי המותר הוא 18 ספרות.
Salesforce PHONE STRING
Salesforce PICKLIST STRING
Salesforce REFERENCE STRING
Salesforce TEXTAREA STRING

האורך המקסימלי המותר הוא 255 תווים.
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

סוגי נתונים ב-MongoDB

מסמכי JSON בינאריים (BSON) של MongoDB נכתבים ב-BigQuery באחד משני מצבים, בהתאם למה שציינתם במהלך יצירת הסטרימינג (ראו ניהול סטרימינג): מצב קנוני (ברירת המחדל) או מצב קפדני.

  • מצב קנוני (ברירת מחדל): הפורמט המודרני והתיאורי יותר, שנותן עדיפות לנאמנות הנתונים. הוא מבטיח שכל סוג BSON מתויג באופן מפורש, למשל, שהוא מבחין בין מספר שלם של 32 ביט ל-64 ביט, כדי למנוע אובדן דיוק במהלך חילופי הנתונים. יכול להיות שהמצב הקנוני יהיה קצת יותר איטי מהמצב הקפדני, אבל הוא תומך בכל סוגי הנתונים. מידע נוסף מופיע במאמר MongoDB Extended JSON.
  • מצב קפדני: פורמט מדור קודם שנועד להיות תואם למנתחי JSON, באמצעות מוסכמות ספציפיות, כמו "$date", כדי לייצג סוגים שלא קיימים ב-JSON רגיל. סוגי נתונים מסוימים, כמו Infinity,‏ -Infinity ו-NaN, לא נתמכים. מידע נוסף מופיע במאמר MongoDB Extended JSON (v1)‎.

בטבלה הבאה מוצגות דרכי הייצוג של סוגי הנתונים ב-BigQuery בשני המצבים, יחד עם ערכים לדוגמה:

סוג הנתונים BSON ערך לדוגמה מצב קנוני מצב קפדני
DOUBLE 3.1415926535 {"$numberDouble":"3.1415926535"} 3.1415926535
Infinity Infinity {"$numberDouble":"Infinity"} לא נתמך
-Infinity -Infinity {"$numberDouble":"-Infinity"} לא נתמך
NaN NaN {"$numberDouble":"NaN"} לא נתמך
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}}

הרצת שאילתה על מערך ב-PostgreSQL כסוג נתונים של מערך ב-BigQuery

כדי להריץ שאילתה על מערך PostgreSQL כARRAY סוג נתונים של BigQuery, צריך להמיר את הערכים של JSON למערך BigQuery באמצעות הפונקציה JSON_VALUE_ARRAY של BigQuery:

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

מחליפים את מה שכתוב בשדות הבאים:

  • ‫TYPE: הסוג ב-BigQuery שתואם לסוג הרכיב במערך המקור ב-PostgreSQL. לדוגמה, אם סוג המקור הוא מערך של ערכי BIGINT, צריך להחליף את TYPE ב-INT64. מידע נוסף על מיפוי סוגי הנתונים זמין במאמר בנושא מיפוי סוגי נתונים.
  • ‫BQ_COLUMN_NAME: שם העמודה הרלוונטית בטבלה ב-BigQuery.

יש שני חריגים לאופן שבו ממירים את הערכים:

  • כדי להמיר מערכים של ערכים BIT, BIT_VARYING או BYTEA בעמודת המקור, מריצים את השאילתה הבאה:

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

  • כדי להשתמש במערכים של ערכים JSON או JSONB בעמודת המקור, צריך להשתמש בפונקציה JSON_QUERY_ARRAY:

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