מדריך לתרגום Oracle SQL

במסמך הזה מפורטים קווי הדמיון וההבדלים בתחביר של SQL בין Oracle ל-BigQuery, כדי לעזור לכם לתכנן את ההעברה. אתם יכולים להשתמש בתרגום SQL באצווה כדי להעביר את הסקריפטים של SQL בכמות גדולה, או בתרגום SQL אינטראקטיבי כדי לתרגם שאילתות אד-הוק.

סוגי הנתונים

בקטע הזה מוצגים סוגי הנתונים המקבילים ב-Oracle וב-BigQuery.

Oracle BigQuery הערות
VARCHAR2 STRING
NVARCHAR2 STRING
CHAR STRING
NCHAR STRING
CLOB STRING
NCLOB STRING
INTEGER INT64
SHORTINTEGER INT64
LONGINTEGER INT64
NUMBER NUMERIC ב-BigQuery המשתמשים לא יכולים לציין ערכים מותאמים אישית לדיוק או לקנה מידה. כתוצאה מכך, יכול להיות שעמודה ב-Oracle תוגדר כך שהיא תהיה גדולה יותר ממה שנתמך ב-BigQuery.

בנוסף, לפני ש-Oracle שומרת מספר עשרוני, היא מעגלת אותו כלפי מעלה אם יש לו יותר ספרות אחרי הנקודה העשרונית מאשר מה שצוין בעמודה המתאימה. ב-BigQuery אפשר להטמיע את התכונה הזו באמצעות הפונקציה ROUND().

NUMBER(*, x) NUMERIC ב-BigQuery המשתמשים לא יכולים לציין ערכים מותאמים אישית לדיוק או לקנה מידה. כתוצאה מכך, יכול להיות שעמודה ב-Oracle תוגדר כך שהיא תהיה גדולה יותר ממה שנתמך ב-BigQuery.

בנוסף, לפני ש-Oracle שומרת מספר עשרוני, היא מעגלת אותו כלפי מעלה אם יש לו יותר ספרות אחרי הנקודה העשרונית מאשר מה שצוין בעמודה המתאימה. ב-BigQuery אפשר להטמיע את התכונה הזו באמצעות הפונקציה ROUND().

NUMBER(x, -y) INT64 אם משתמש מנסה לאחסן מספר עשרוני, Oracle מעגל אותו למספר שלם. ב-BigQuery, ניסיון לאחסן מספר עשרוני בעמודה שמוגדרת כ-INT64 יגרום לשגיאה. במקרה כזה, צריך להשתמש בפונקציה ROUND().

סוגי הנתונים INT64 ב-BigQuery מאפשרים דיוק של עד 18 ספרות. אם בשדה מספר יש יותר מ-18 ספרות, צריך להשתמש בסוג הנתונים FLOAT64 ב-BigQuery.

NUMBER(x) INT64 אם משתמש מנסה לאחסן מספר עשרוני, Oracle מעגל אותו למספר שלם. ב-BigQuery, ניסיון לאחסן מספר עשרוני בעמודה שמוגדרת כ-INT64 יגרום לשגיאה. במקרה כזה, צריך להשתמש בפונקציה ROUND().

סוגי הנתונים INT64 ב-BigQuery מאפשרים דיוק של עד 18 ספרות. אם בשדה מספר יש יותר מ-18 ספרות, צריך להשתמש בסוג הנתונים FLOAT64 ב-BigQuery.

FLOAT NUMERIC/FLOAT64 ‫FLOAT הוא סוג נתונים מדויק, והוא סוג משנה של NUMBER ב-Oracle. ב-BigQuery, הערך FLOAT64 הוא סוג נתונים משוער. יכול להיות ש-NUMERIC מתאים יותר לסוג FLOAT ב-BigQuery.
BINARY_DOUBLE NUMERIC/FLOAT64 ‫FLOAT הוא סוג נתונים מדויק, והוא סוג משנה של NUMBER ב-Oracle. ב-BigQuery, הערך FLOAT64 הוא סוג נתונים משוער. יכול להיות ש-NUMERIC מתאים יותר לסוג FLOAT ב-BigQuery.
BINARY_FLOAT NUMERIC/FLOAT64 ‫FLOAT הוא סוג נתונים מדויק, והוא סוג משנה של NUMBER ב-Oracle. ב-BigQuery, הערך FLOAT64 הוא סוג נתונים משוער. יכול להיות ש-NUMERIC מתאים יותר לסוג FLOAT ב-BigQuery.
LONG BYTES סוג הנתונים LONG משמש בגרסאות קודמות, ולא מומלץ להשתמש בו בגרסאות חדשות של Oracle Database.

אפשר להשתמש בסוג הנתונים BYTES ב-BigQuery אם צריך לשמור נתוני LONG ב-BigQuery. גישה טובה יותר היא לשים אובייקטים בינאריים ב-Cloud Storage ולשמור הפניות ב-BigQuery.

BLOB BYTES אפשר להשתמש בסוג הנתונים BYTES כדי לאחסן נתונים בינאריים באורך משתנה. אם לא שולחים שאילתות לשדה הזה ולא משתמשים בו בניתוח נתונים, עדיף לאחסן נתונים בינאריים ב-Cloud Storage.
BFILE STRING אפשר לאחסן קבצים בינאריים ב-Cloud Storage ולהשתמש בסוג הנתונים STRING כדי להפנות לקבצים בטבלה ב-BigQuery.
DATE DATETIME
TIMESTAMP TIMESTAMP ‫BigQuery תומך ברמת דיוק של מיקרו-שניות (‎10-6‎), בהשוואה ל-Oracle שתומך ברמת דיוק בטווח שבין 0 ל-9.

‫BigQuery תומך בשם של אזור זמן ממסד נתונים של אזורי זמן ובסטייה של אזור זמן מ-UTC.

ב-BigQuery צריך לבצע המרה של אזור הזמן באופן ידני כדי להתאים לתכונה TIMESTAMP WITH LOCAL TIME ZONE של Oracle.

TIMESTAMP(x) TIMESTAMP ‫BigQuery תומך ברמת דיוק של מיקרו-שניות (‎10-6‎), בהשוואה ל-Oracle שתומך ברמת דיוק בטווח שבין 0 ל-9.

‫BigQuery תומך בשם של אזור זמן ממסד נתונים של אזורי זמן ובסטייה של אזור זמן מ-UTC.

ב-BigQuery צריך לבצע המרה של אזור הזמן באופן ידני כדי להתאים לתכונה TIMESTAMP WITH LOCAL TIME ZONE של Oracle.

TIMESTAMP WITH TIME ZONE TIMESTAMP ‫BigQuery תומך ברמת דיוק של מיקרו-שניות (‎10-6‎), בהשוואה ל-Oracle שתומך ברמת דיוק בטווח שבין 0 ל-9.

‫BigQuery תומך בשם של אזור זמן ממסד נתונים של אזורי זמן ובסטייה של אזור זמן מ-UTC.

ב-BigQuery צריך לבצע המרה של אזור הזמן באופן ידני כדי להתאים לתכונה TIMESTAMP WITH LOCAL TIME ZONE של Oracle.

TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP ‫BigQuery תומך ברמת דיוק של מיקרו-שניות (‎10-6‎), בהשוואה ל-Oracle שתומך ברמת דיוק בטווח שבין 0 ל-9.

‫BigQuery תומך בשם של אזור זמן ממסד נתונים של אזורי זמן ובסטייה של אזור זמן מ-UTC.

ב-BigQuery צריך לבצע המרה של אזור הזמן באופן ידני כדי להתאים לתכונה TIMESTAMP WITH LOCAL TIME ZONE של Oracle.

INTERVAL YEAR TO MONTH STRING אפשר לאחסן ערכי מרווחים כסוג הנתונים STRING ב-BigQuery.
INTERVAL DAY TO SECOND STRING אפשר לאחסן ערכי מרווחים כסוג הנתונים STRING ב-BigQuery.
RAW BYTES אפשר להשתמש בסוג הנתונים BYTES כדי לאחסן נתונים בינאריים באורך משתנה. אם לא שולחים שאילתות לשדה הזה ולא משתמשים בו בניתוח נתונים, עדיף לאחסן נתונים בינאריים ב-Cloud Storage.
LONG RAW BYTES אפשר להשתמש בסוג הנתונים BYTES כדי לאחסן נתונים בינאריים באורך משתנה. אם לא שולחים שאילתות לשדה הזה ולא משתמשים בו בניתוח נתונים, עדיף לאחסן נתונים בינאריים ב-Cloud Storage.
ROWID STRING סוגי הנתונים האלה משמשים את Oracle באופן פנימי כדי לציין כתובות ייחודיות לשורות בטבלה. באופן כללי, לא מומלץ להשתמש בשדה ROWID או UROWID באפליקציות. אבל אם זה המצב, אפשר להשתמש בסוג הנתונים STRING כדי להחזיק את הנתונים האלה.

עיצוב סוג הנתונים

ב-Oracle SQL יש קבוצה של פורמטים שמוגדרים כברירת מחדל כפרמטרים להצגת ביטויים ונתוני עמודות, ולהמרות בין סוגי נתונים. לדוגמה, אם NLS_DATE_FORMAT מוגדר כ-YYYY/MM/DD, הפורמט של התאריכים הוא YYYY/MM/DD כברירת מחדל. מידע נוסף על הגדרות NLS זמין במסמכי התיעוד אונליין של Oracle. ב-BigQuery אין פרמטרים של אתחול.

כברירת מחדל, BigQuery מצפה שכל נתוני המקור יהיו בקידוד UTF-8 בזמן הטעינה. אם יש לכם קובצי CSV עם נתונים בקידוד ISO-8859-1, אתם יכולים לציין במפורש את הקידוד כשאתם מייבאים את הנתונים, כדי ש-BigQuery יוכל להמיר אותם ל-UTF-8 במהלך הייבוא.

אפשר לייבא רק נתונים שמקודדים ב-ISO-8859-1 או ב-UTF-8. ‫BigQuery מאחסן ומחזיר את הנתונים בקידוד UTF-8. אפשר להגדיר את הפורמט הרצוי של התאריך או אזור הזמן בפונקציות DATE ו-TIMESTAMP.

פורמט של חותמת זמן וסוג תאריך

כשממירים רכיבים של פורמט חותמת זמן ותאריך מ-Oracle ל-BigQuery, חשוב לשים לב להבדלים באזורי הזמן בין TIMESTAMP ל-DATETIME, כפי שמסוכם בטבלה הבאה.

שימו לב שאין סוגריים בפורמטים של Oracle כי הפורמטים (CURRENT_*) הם מילות מפתח, לא פונקציות.

Oracle BigQuery הערות
CURRENT_TIMESTAMP פרטי TIMESTAMP ב-Oracle יכולים לכלול פרטי אזור זמן שונים, שמוגדרים באמצעות WITH TIME ZONE בהגדרה של העמודה או בהגדרה של המשתנה TIME_ZONE. אם אפשר, השתמשו בפונקציה CURRENT_TIMESTAMP(), שמוגדרת בפורמט ISO. עם זאת, בפורמט הפלט תמיד מוצג אזור הזמן UTC. (באופן פנימי, ל-BigQuery אין אזור זמן).

חשוב לשים לב לפרטים הבאים לגבי ההבדלים בפורמט ISO:

הפורמט של DATETIME מבוסס על המוסכמות של ערוץ הפלט. בכלי שורת הפקודה של BigQuery ובמסוף BigQuery, הפורמט של DATETIME כולל מפריד T בהתאם ל-RFC 3339. עם זאת, ב-Python וב-Java JDBC, משתמשים ברווח כמפריד.

אם רוצים להשתמש בפורמט מפורש, משתמשים בפונקציה FORMAT_DATETIME()‎, שממירה באופן מפורש למחרוזת. לדוגמה, הביטוי הבא תמיד מחזיר תו רווח: CAST(CURRENT_DATETIME() AS STRING)

CURRENT_DATE
SYSDATE 		ב-Oracle יש שני סוגים של תאריכים:
  • סוג 12
  • סוג 13
‫Oracle משתמשת בסוג 12 כשהיא מאחסנת תאריכים. באופן פנימי, אלה מספרים באורך קבוע. ‫Oracle משתמשת בסוג 13 כשתאריך מוחזר על ידי SYSDATE or CURRENT_DATE 		ל-BigQuery יש פורמט DATE נפרד שתמיד מחזיר תאריך בפורמט ISO 8601.

אי אפשר להשתמש ב-DATE_FROM_UNIX_DATE כי הוא מבוסס על שנת 1970.

CURRENT_DATE-3 ערכי התאריכים מיוצגים כמספרים שלמים. ב-Oracle יש תמיכה באופרטורים אריתמטיים לסוגי תאריכים. לסוגי תאריכים, משתמשים ב-DATE_ADD() או ב-DATE_SUB(). ב-BigQuery משתמשים באופרטורים אריתמטיים לסוגי נתונים: INT64,‏ NUMERIC ו-FLOAT64.
NLS_DATE_FORMAT מגדירים את פורמט התאריך של הסשן או של המערכת. ‫BigQuery תמיד משתמש ב-ISO 8601, לכן חשוב להמיר את התאריכים והשעות של Oracle.

תחביר של שאילתות

בקטע הזה מוסברים ההבדלים בתחביר השאילתות בין Oracle ל-BigQuery.

‫SELECT דפי חשבון

רוב ההצהרות של Oracle SELECT תואמות ל-BigQuery.

פונקציות, אופרטורים וביטויים

בקטעים הבאים מפורטים המיפויים בין פונקציות של Oracle לבין הפונקציות המקבילות ב-BigQuery.

אופרטורים להשוואה

האופרטורים להשוואה של Oracle ו-BigQuery תואמים ל-ANSI SQL:2011. האופרטורים להשוואה בטבלה הבאה זהים ב-BigQuery וב-Oracle. ב-BigQuery אפשר להשתמש ב-REGEXP_CONTAINS במקום ב-REGEXP_LIKE.

אופרטור תיאור
"=" שווה
<> לא שווה
!= לא שווה
> גדול מ-
>= גדול או שווה ל-
< פחות מ-
<= קטן או שווה ל-
IN ( ) תואם לערך ברשימה
NOT ביטול תנאי
BETWEEN בטווח (כולל)
IS NULL הערך של NULL
IS NOT NULL לא ערך NULL
LIKE התאמת דפוסים באמצעות %
EXISTS התנאי מתקיים אם שאילתת המשנה מחזירה לפחות שורה אחת

האופרטורים בטבלה זהים גם ב-BigQuery וגם ב-Oracle.

ביטויים ופונקציות לוגיים

Oracle BigQuery
CASE CASE
COALESCE COALESCE(expr1, ..., exprN)
DECODE CASE.. WHEN.. END
NANVL IFNULL
FETCH NEXT> LIMIT
NULLIF NULLIF(expression, expression_to_match)
NVL IFNULL(expr, 0), COALESCE(exp, 0)
NVL2 IF(expr, true_result, else_result)

פונקציות צבירה

בטבלה הבאה מוצג מיפוי בין פונקציות נפוצות של צבירה, צבירה סטטיסטית וצבירה משוערת ב-Oracle לבין הפונקציות המקבילות ב-BigQuery:

Oracle BigQuery
ANY_VALUE
(מ-Oracle 19c)		 ANY_VALUE
APPROX_COUNT HLL_COUNT set of functions with specified precision
APPROX_COUNT_DISTINCT APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_AGG APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_DETAIL APPROX_COUNT_DISTINCT
APPROX_PERCENTILE(percentile) WITHIN GROUP (ORDER BY expression) APPROX_QUANTILES(expression, 100)[
OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
ב-BigQuery אין תמיכה בשאר הארגומנטים שמוגדרים ב-Oracle.
APPROX_PERCENTILE_AGG APPROX_QUANTILES(expression, 100)[
OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
APPROX_PERCENTILE_DETAIL APPROX_QUANTILES(expression, 100)[OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
APPROX_SUM APPROX_TOP_SUM(expression, weight, number)
AVG AVG
BIT_COMPLEMENT אופרטור NOT ברמת הביט: ~
BIT_OR BIT_OR, X | Y
BIT_XOR BIT_XOR, X ^ Y
BITAND BIT_AND, X & Y
CARDINALITY COUNT
COLLECT ‫BigQuery לא תומך ב-TYPE AS TABLE OF. כדאי להשתמש ב-STRING_AGG() או ב-ARRAY_AGG() ב-BigQuery
CORR/CORR_K/ CORR_S CORR
COUNT COUNT
COVAR_POP COVAR_POP
COVAR_SAMP COVAR_SAMP
FIRST לא קיים באופן משתמע ב-BigQuery. כדאי להשתמש בפונקציות בהגדרת המשתמש (UDF).
GROUP_ID לא בשימוש ב-BigQuery
GROUPING GROUPING
GROUPING_ID לא בשימוש ב-BigQuery.
LAST לא קיים באופן משתמע ב-BigQuery. כדאי להשתמש בפונקציות בהגדרת משתמש (UDF).
LISTAGG STRING_AGG, ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
MAX MAX
MIN MIN
OLAP_CONDITION ספציפי ל-Oracle, לא קיים ב-BigQuery.
OLAP_EXPRESSION ספציפי ל-Oracle, לא קיים ב-BigQuery.
OLAP_EXPRESSION_BOOL ספציפי ל-Oracle, לא קיים ב-BigQuery.
OLAP_EXPRESSION_DATE ספציפי ל-Oracle, לא קיים ב-BigQuery.
OLAP_EXPRESSION_TEXT ספציפי ל-Oracle, לא קיים ב-BigQuery.
OLAP_TABLE ספציפי ל-Oracle, לא קיים ב-BigQuery.
POWERMULTISET ספציפי ל-Oracle, לא קיים ב-BigQuery.
POWERMULTISET_BY_CARDINALITY ספציפי ל-Oracle, לא קיים ב-BigQuery.
QUALIFY ספציפי ל-Oracle, לא קיים ב-BigQuery.
REGR_AVGX AVG(
IF(dep_var_expr is NULL
OR ind_var_expr is NULL,
NULL, ind_var_expr)
)
REGR_AVGY AVG(
IF(dep_var_expr is NULL
OR ind_var_expr is NULL,
NULL, dep_var_expr)
)
REGR_COUNT SUM(
IF(dep_var_expr is NULL
OR ind_var_expr is NULL,
NULL, 1)
)
REGR_INTERCEPT AVG(dep_var_expr)
- AVG(ind_var_expr)
* (COVAR_SAMP(ind_var_expr,dep_var_expr)
/ VARIANCE(ind_var_expr)
)
REGR_R2 (COUNT(dep_var_expr) *
SUM(ind_var_expr * dep_var_expr) -
SUM(dep_var_expr) * SUM(ind_var_expr))
/ SQRT(
(COUNT(ind_var_expr) *
SUM(POWER(ind_var_expr, 2)) *
POWER(SUM(ind_var_expr),2)) *
(COUNT(dep_var_expr) *
SUM(POWER(dep_var_expr, 2)) *
POWER(SUM(dep_var_expr), 2)))
REGR_SLOPE COVAR_SAMP(ind_var_expr,

dep_var_expr)

/ VARIANCE(ind_var_expr)

REGR_SXX SUM(POWER(ind_var_expr, 2)) - COUNT(ind_var_expr) * POWER(AVG(ind_var_expr),2)
REGR_SXY SUM(ind_var_expr*dep_var_expr) - COUNT(ind_var_expr) * AVG(ind) * AVG(dep_var_expr)
REGR_SYY SUM(POWER(dep_var_expr, 2)) - COUNT(dep_var_expr) * POWER(AVG(dep_var_expr),2)
ROLLUP ROLLUP
STDDEV_POP STDDEV_POP
STDDEV_SAMP STDDEV_SAMP, STDDEV
SUM SUM
VAR_POP VAR_POP
VAR_SAMP VAR_SAMP, VARIANCE
WM_CONCAT STRING_AGG

ב-BigQuery יש את פונקציות הצבירה הנוספות הבאות:

פונקציות אנליטיות

בטבלה הבאה מוצגים מיפויים בין פונקציות ניתוח ופונקציות ניתוח מצטברות נפוצות של Oracle לבין המקבילות שלהן ב-BigQuery.

Oracle BigQuery
AVG AVG
BIT_COMPLEMENT אופרטור NOT ברמת הביט: ~
BIT_OR BIT_OR, X | Y
BIT_XOR BIT_XOR, X ^ Y
BITAND BIT_AND, X & Y
BOOL_TO_INT CAST(X AS INT64)
COUNT COUNT
COVAR_POP COVAR_POP
COVAR_SAMP COVAR_SAMP
CUBE_TABLE לא נתמך ב-BigQuery. כדאי להשתמש בכלי BI או בפונקציה מותאמת אישית (UDF)
CUME_DIST CUME_DIST
DENSE_RANK(ANSI) DENSE_RANK
FEATURE_COMPARE לא קיים באופן משתמע ב-BigQuery. שימוש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-BigQuery ML
FEATURE_DETAILS לא קיים באופן משתמע ב-BigQuery. שימוש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-BigQuery ML
FEATURE_ID לא קיים באופן משתמע ב-BigQuery. שימוש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-BigQuery ML
FEATURE_SET לא קיים באופן משתמע ב-BigQuery. שימוש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-BigQuery ML
FEATURE_VALUE לא קיים באופן משתמע ב-BigQuery. שימוש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-BigQuery ML
FIRST_VALUE FIRST_VALUE
HIER_CAPTION ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_CHILD_COUNT ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_COLUMN ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_DEPTH ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_DESCRIPTION ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_HAS_CHILDREN ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_LEVEL ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_MEMBER_NAME ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_ORDER ב-BigQuery אין תמיכה בשאילתות היררכיות.
HIER_UNIQUE_MEMBER_NAME ב-BigQuery אין תמיכה בשאילתות היררכיות.
LAST_VALUE LAST_VALUE
LAG LAG
LEAD LEAD
LISTAGG ARRAY_AGG
STRING_AGG
ARRAY_CONCAT_AGG
MATCH_NUMBER אפשר לבצע זיהוי דפוסים וחישובים באמצעות ביטויים רגולריים ופונקציות בהגדרת המשתמש (UDF) ב-BigQuery
MATCH_RECOGNIZE אפשר לבצע זיהוי דפוסים וחישובים באמצעות ביטויים רגולריים ופונקציות בהגדרת המשתמש (UDF) ב-BigQuery
MAX MAX
MEDIAN PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER()
MIN MIN
NTH_VALUE NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
NTILE NTILE(constant_integer_expression)
PERCENT_RANK
PERCENT_RANKM 		PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC 		PERCENTILE_CONT
PERCENTILE_CONT
PERCENTILE_DISC 		PERCENTILE_DISC
PRESENTNNV ספציפי ל-Oracle, לא קיים ב-BigQuery.
PRESENTV ספציפי ל-Oracle, לא קיים ב-BigQuery.
PREVIOUS ספציפי ל-Oracle, לא קיים ב-BigQuery.
RANK(ANSI) RANK
RATIO_TO_REPORT(expr) OVER (partition clause) expr / SUM(expr) OVER (partition clause)
ROW_NUMBER ROW_NUMBER
STDDEV_POP STDDEV_POP
STDDEV_SAMP STDDEV_SAMP, STDDEV
SUM SUM
VAR_POP VAR_POP
VAR_SAMP VAR_SAMP, VARIANCE
VARIANCE VARIANCE()
WIDTH_BUCKET אפשר להשתמש ב-UDF.

פונקציות של תאריך/שעה

בטבלה הבאה מוצגים מיפויים בין פונקציות תאריך/שעה נפוצות של Oracle לבין הפונקציות המקבילות להן ב-BigQuery.

Oracle BigQuery
ADD_MONTHS(date, integer) DATE_ADD(date, INTERVAL integer MONTH),
אם התאריך הוא TIMESTAMP, אפשר להשתמש ב-

EXTRACT(DATE FROM TIMESTAMP_ADD(date, INTERVAL integer MONTH))

CURRENT_DATE CURRENT_DATE
CURRENT_TIME CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_TIMESTAMP
DATE - k DATE_SUB(date_expression, INTERVAL k DAY)
DATE + k DATE_ADD(date_expression, INTERVAL k DAY)
DBTIMEZONE ‫BigQuery לא תומך באזור הזמן של מסד הנתונים.
EXTRACT EXTRACT(DATE), EXTRACT(TIMESTAMP)
LAST_DAY DATE_SUB(
  DATE_TRUNC(
    DATE_ADD(
      date_expression,
      INTERVAL 1 MONTH
    ),
  MONTH
  ),
INTERVAL 1 DAY
)
LOCALTIMESTAMP ב-BigQuery אין תמיכה בהגדרות אזור הזמן.
MONTHS_BETWEEN DATE_DIFF(date_expression, date_expression, MONTH)
NEW_TIME DATE(timestamp_expression, time zone)
TIME(timestamp, time zone)
DATETIME(timestamp_expression, time zone)
NEXT_DAY DATE_ADD(
  DATE_TRUNC(
    date_expression,
    WEEK(day_value)
  ),
  INTERVAL 1 WEEK
)
SYS_AT_TIME_ZONE CURRENT_DATE([time_zone])
SYSDATE CURRENT_DATE()
SYSTIMESTAMP CURRENT_TIMESTAMP()
TO_DATE PARSE_DATE
TO_TIMESTAMP PARSE_TIMESTAMP
TO_TIMESTAMP_TZ PARSE_TIMESTAMP
TZ_OFFSET לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF).
WM_CONTAINS
WM_EQUALS
WM_GREATERTHAN
WM_INTERSECTION
WM_LDIFF
WM_LESSTHAN
WM_MEETS
WM_OVERLAPS
WM_RDIFF
 ב-BigQuery לא משתמשים בנקודות. אפשר להשתמש בפונקציות UDF כדי להשוות בין שתי תקופות.

ב-BigQuery יש פונקציות נוספות של תאריך/שעה:

פונקציות של מחרוזות

בטבלה הבאה מוצג המיפוי בין פונקציות המחרוזת של Oracle לבין הפונקציות המקבילות ב-BigQuery:

Oracle BigQuery
ASCII TO_CODE_POINTS(string_expr)[OFFSET(0)]
ASCIISTR ‫BigQuery לא תומך ב-UTF-16
RAWTOHEX TO_HEX
LENGTH CHAR_LENGTH
LENGTH CHARACTER_LENGTH
CHR CODE_POINTS_TO_STRING(
[mod(numeric_expr, 256)]
)
COLLATION לא קיים ב-BigQuery. ‫BigQuery לא תומך ב-COLLATE ב-DML
COMPOSE פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
CONCAT, (|| operator) CONCAT
DECOMPOSE פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
ESCAPE_REFERENCE (UTL_I18N) לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה בהגדרת המשתמש (UDF).
INITCAP INITCAP
INSTR/INSTR2/INSTR4/INSTRB/INSTRC פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
LENGTH/LENGTH2/LENGTH4/LENGTHB/LENGTHC LENGTH
LOWER LOWER
LPAD LPAD
LTRIM LTRIM
NLS_INITCAP פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
NLS_LOWER LOWER
NLS_UPPER UPPER
NLSSORT ספציפי ל-Oracle, לא קיים ב-BigQuery.
POSITION STRPOS(string, substring)
PRINTBLOBTOCLOB ספציפי ל-Oracle, לא קיים ב-BigQuery.
REGEXP_COUNT ARRAY_LENGTH(REGEXP_EXTRACT_ALL(value, regex))
REGEXP_INSTR STRPOS(source_string, REGEXP_EXTRACT(source_string, regexp_string))

הערה: הפונקציה מחזירה את המופע הראשון.

REGEXP_REPLACE REGEXP_REPLACE
REGEXP_LIKE IF(REGEXP_CONTAINS,1,0)
REGEXP_SUBSTR REGEXP_EXTRACT, REGEXP_EXTRACT_ALL
REPLACE REPLACE
REVERSE REVERSE
RIGHT SUBSTR(source_string, -1, length)
RPAD RPAD
RTRIM RTRIM
SOUNDEX לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF)
STRTOK SPLIT(instring, delimiter)[ORDINAL(tokennum)]

Note: The entire delimiter string argument is used as a single delimiter. The default delimiter is a comma.

SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4 SUBSTR
TRANSLATE REPLACE
TRANSLATE USING REPLACE
TRIM TRIM
UNISTR CODE_POINTS_TO_STRING
UPPER UPPER
|| (עמודות אנכיות) CONCAT

ב-BigQuery יש פונקציות נוספות של מחרוזות:

פונקציות מתמטיות

בטבלה הבאה מוצג מיפוי בין פונקציות מתמטיות של Oracle לבין הפונקציות המקבילות ב-BigQuery.

Oracle BigQuery
ABS ABS
ACOS ACOS
ACOSH ACOSH
ASIN ASIN
ASINH ASINH
ATAN ATAN
ATAN2 ATAN2
ATANH ATANH
CEIL CEIL
CEILING CEILING
COS COS
COSH COSH
EXP EXP
FLOOR FLOOR
GREATEST GREATEST
LEAST LEAST
LN LN
LNNVL שימוש עם ISNULL
LOG LOG
MOD (% operator) MOD
POWER (** operator) POWER, POW
DBMS_RANDOM.VALUE RAND
RANDOMBYTES לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF) ובפונקציה RAND
RANDOMINTEGER CAST(FLOOR(10*RAND()) AS INT64)
RANDOMNUMBER לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF) ובפונקציה RAND
REMAINDER MOD
ROUND ROUND
ROUND_TIES_TO_EVEN ROUND()
SIGN SIGN
SIN SIN
SINH SINH
SQRT SQRT
STANDARD_HASH FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
STDDEV STDDEV
TAN TAN
TANH TANH
TRUNC TRUNC
NVL IFNULL(expr, 0), COALESCE(exp, 0)

ב-BigQuery יש פונקציות מתמטיות נוספות:

פונקציות להמרת סוגים

בטבלה הבאה מוצגים המיפויים בין פונקציות המרת הטיפוסים של Oracle לבין המקבילות שלהן ב-BigQuery.

Oracle BigQuery
BIN_TO_NUM SAFE_CONVERT_BYTES_TO_STRING(value)

CAST(x AS INT64)

BINARY2VARCHAR SAFE_CONVERT_BYTES_TO_STRING(value)
CAST
CAST_FROM_BINARY_DOUBLE
CAST_FROM_BINARY_FLOAT
CAST_FROM_BINARY_INTEGER
CAST_FROM_NUMBER
CAST_TO_BINARY_DOUBLE
CAST_TO_BINARY_FLOAT
CAST_TO_BINARY_INTEGER
CAST_TO_NUMBER
CAST_TO_NVARCHAR2
CAST_TO_RAW
>CAST_TO_VARCHAR 		CAST(expr AS typename)
CHARTOROWID לא צריך את Oracle.
CONVERT ב-BigQuery אין תמיכה בקבוצות תווים. כדאי להשתמש בפונקציה בהגדרת המשתמש (UDF).
EMPTY_BLOB השדה BLOB לא נמצא בשימוש ב-BigQuery.
EMPTY_CLOB השדה CLOB לא נמצא בשימוש ב-BigQuery.
FROM_TZ סוגים עם אזורי זמן לא נתמכים ב-BigQuery. כדאי להשתמש בפונקציה בהגדרת המשתמש (UDF) וב-FORMAT_TIMESTAMP
INT_TO_BOOL CAST
IS_BIT_SET לא קיים באופן משתמע ב-BigQuery. כדאי להשתמש בפונקציות מוגדרות על ידי המשתמש (UDF)
NCHR אפשר להשתמש ב-UDF כדי לקבל את המקבילה של תו בינארי
NUMTODSINTERVAL סוג הנתונים INTERVAL לא נתמך ב-BigQuery
NUMTOHEX לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה TO_HEX ובפונקציה מותאמת אישית שמוגדרת על ידי המשתמש (UDF)
NUMTOHEX2
NUMTOYMINTERVAL סוג הנתונים INTERVAL לא נתמך ב-BigQuery.
RAW_TO_CHAR ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAW_TO_NCHAR ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAW_TO_VARCHAR2 ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAWTOHEX ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAWTONHEX ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAWTONUM ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAWTONUM2 ספציפי ל-Oracle, לא קיים ב-BigQuery.
RAWTOREF ספציפי ל-Oracle, לא קיים ב-BigQuery.
REFTOHEX ספציפי ל-Oracle, לא קיים ב-BigQuery.
REFTORAW ספציפי ל-Oracle, לא קיים ב-BigQuery.
ROWIDTOCHAR ‫ROWID הוא סוג ספציפי ל-Oracle ולא קיים ב-BigQuery. הערך הזה צריך להיות מיוצג כמחרוזת.
ROWIDTONCHAR ‫ROWID הוא סוג ספציפי ל-Oracle ולא קיים ב-BigQuery. הערך הזה צריך להיות מיוצג כמחרוזת.
SCN_TO_TIMESTAMP ‫SCN הוא סוג ספציפי ל-Oracle ולא קיים ב-BigQuery. הערך הזה צריך להיות מיוצג כחותמת זמן.
TO_ACLID
TO_ANYLOB
TO_APPROX_COUNT_DISTINCT
TO_APPROX_PERCENTILE
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_BLOB
TO_CHAR
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_RAW
TO_SINGLE_BYTE
TO_TIME
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_TIME_TZ
TO_UTC_TIMEZONE_TZ
TO_YMINTERVAL
 CAST(expr AS typename)
PARSE_DATE
PARSE_TIMESTAMP
תחביר ההמרה משמש בשאילתה כדי לציין שצריך להמיר את סוג התוצאה של ביטוי לסוג אחר.
TREAT ספציפי ל-Oracle, לא קיים ב-BigQuery.
VALIDATE_CONVERSION לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF)
VSIZE לא נתמך ב-BigQuery. כדאי להשתמש בפונקציה מותאמת אישית (UDF)

פונקציות JSON

בטבלה הבאה מוצג המיפוי בין פונקציות JSON של Oracle לבין הפונקציות המקבילות ב-BigQuery.

Oracle BigQuery
AS_JSON TO_JSON_STRING(value[, pretty_print])
JSON_ARRAY כדאי להשתמש בפונקציות מוגדרות על ידי המשתמש (UDF) ובפונקציה TO_JSON_STRING
JSON_ARRAYAGG כדאי להשתמש בפונקציות מוגדרות על ידי המשתמש (UDF) ובפונקציה TO_JSON_STRING
JSON_DATAGUIDE פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
JSON_EQUAL פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
JSON_EXIST כדאי להשתמש בפונקציות UDF וב-JSON_EXTRACT או ב-JSON_EXTRACT_SCALAR
JSON_MERGEPATCH פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
JSON_OBJECT לא נתמך ב-BigQuery.
JSON_OBJECTAGG לא נתמך ב-BigQuery.
JSON_QUERY כדאי להשתמש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-JSON_EXTRACT או ב-JSON_EXTRACT_SCALAR.
JSON_TABLE פונקציה בהגדרת המשתמש (UDF) בהתאמה אישית.
JSON_TEXTCONTAINS כדאי להשתמש בפונקציות מוגדרות על ידי המשתמש (UDF) וב-JSON_EXTRACT או ב-JSON_EXTRACT_SCALAR.
JSON_VALUE JSON_EXTRACT_SCALAR

פונקציות XML

ב-BigQuery אין פונקציות XML מרומזות. אפשר לטעון XML ל-BigQuery כמחרוזת ולהשתמש בפונקציות UDF כדי לנתח XML. לחלופין, אפשר לעבד XML באמצעות כלי ETL/ELT כמו Dataflow. ברשימה הבאה מוצגות פונקציות XML של Oracle:

Oracle BigQuery
DELETEXML כדי לעבד XML, אפשר להשתמש ב-UDFs של BigQuery או בכלי ETL כמו Dataflow.
ENCODE_SQL_XML
EXISTSNODE
EXTRACTCLOBXML
EXTRACTVALUE
INSERTCHILDXML
INSERTCHILDXMLAFTER
INSERTCHILDXMLBEFORE
INSERTXMLAFTER
INSERTXMLBEFORE
SYS_XMLAGG
SYS_XMLANALYZE
SYS_XMLCONTAINS
SYS_XMLCONV
SYS_XMLEXNSURI
SYS_XMLGEN
SYS_XMLI_LOC_ISNODE
SYS_XMLI_LOC_ISTEXT
SYS_XMLINSTR
SYS_XMLLOCATOR_GETSVAL
SYS_XMLNODEID
SYS_XMLNODEID_GETLOCATOR
SYS_XMLNODEID_GETOKEY
SYS_XMLNODEID_GETPATHID
SYS_XMLNODEID_GETPTRID
SYS_XMLNODEID_GETRID
SYS_XMLNODEID_GETSVAL
SYS_XMLT_2_SC
SYS_XMLTRANSLATE
SYS_XMLTYPE2SQL
UPDATEXML
XML2OBJECT
XMLCAST
XMLCDATA
XMLCOLLATVAL
XMLCOMMENT
XMLCONCAT
XMLDIFF
XMLELEMENT
XMLEXISTS
XMLEXISTS2
XMLFOREST
XMLISNODE
XMLISVALID
XMLPARSE
XMLPATCH
XMLPI
XMLQUERY
XMLQUERYVAL
XMLSERIALIZE
XMLTABLE
XMLTOJSON
XMLTRANSFORM
XMLTRANSFORMBLOB
XMLTYPE

פונקציות של למידת מכונה

פונקציות של למידת מכונה (ML) ב-Oracle וב-BigQuery הן שונות. כדי להפעיל למידת מכונה במסד הנתונים, ב-Oracle צריך חבילת ניתוח מתקדם ורישיונות. ‫Oracle משתמשת בחבילה DBMS_DATA_MINING ל-ML. כדי להמיר משימות של Oracle Data Miner, צריך לשכתב את הקוד כך שיעבוד עם התכונות של BigQuery. אתם יכולים לבחור מתוך מגוון רחב של מוצרי Google AI, כולל המוצרים והתכונות הבאים:

אתם יכולים להשתמש ב-BigQuery ML או בכלי הפיתוח של Agent Platform כדי לפתח, לאמן ולהעריך מודלים של למידת מכונה.

בטבלה הבאה מוצגות פונקציות של Oracle ML:

Oracle BigQuery
CLASSIFIER אפשרויות לסיווג ולרגרסיה של למידת מכונה מפורטות במאמר BigQuery ML.
CLUSTER_DETAILS
CLUSTER_DISTANCE
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
PREDICTION
PREDICTION_BOUNDS
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET

פונקציות אבטחה

בטבלה הבאה מוצגות הפונקציות לזיהוי המשתמש ב-Oracle וב-BigQuery:

Oracle BigQuery
UID SESSION_USER
USER/SESSION_USER/CURRENT_USER SESSION_USER()

פונקציות של קבוצות או מערכים

בטבלה הבאה מוצגות פונקציות של קבוצות או מערכים ב-Oracle והפונקציות המקבילות להן ב-BigQuery:

Oracle BigQuery
MULTISET ARRAY_AGG
MULTISET EXCEPT ARRAY_AGG([DISTINCT] expression)
MULTISET INTERSECT ARRAY_AGG([DISTINCT])
MULTISET UNION ARRAY_AGG

פונקציות אנליטיות (window functions)

בטבלה הבאה מוצגות פונקציות אנליטיות ב-Oracle והמקבילות שלהן ב-BigQuery.

Oracle BigQuery
LAG LAG (value_expression[, offset [, default_expression]])
LEAD LEAD (value_expression[, offset [, default_expression]])

שאילתות היררכיות או רקורסיביות

ב-BigQuery לא משתמשים בשאילתות היררכיות או רקורסיביות. אם עומק ההיררכיה ידוע, אפשר להשיג פונקציונליות דומה באמצעות צירופים, כמו בדוגמה הבאה. פתרון אחר הוא להשתמש ב-BigQueryStorage API וב-Spark.

select
  array(
    select e.update.element
    union all
    select c1 from e.update.element.child as c1
    union all
    select c2 from e.update.element.child as c1, c1.child as c2
    union all
    select c3 from e.update.element.child as c1, c1.child as c2, c2.child as c3
    union all
    select c4 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4
    union all
    select c5 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4, c4.child as c5
  ) as flattened,
  e as event
from t, t.events as e

בטבלה הבאה מוצגות פונקציות היררכיות ב-Oracle.

Oracle BigQuery
DEPTH ב-BigQuery לא משתמשים בשאילתות היררכיות.
PATH
SYS_CONNECT_BY_PATH (hierarchical)

פונקציות UTL

החבילה UTL_File משמשת בעיקר לקריאה ולכתיבה של קובצי מערכת ההפעלה מ-PL/SQL. אפשר להשתמש ב-Cloud Storage לכל סוג של אחסון קבצים גולמיים. כדי לקרוא ולכתוב קבצים מ-Cloud Storage ואליו, צריך להשתמש בטבלאות חיצוניות ובטעינה וייצוא של BigQuery. למידע נוסף, קראו את מבוא למקורות נתונים חיצוניים.

פונקציות מרחביות

אתם יכולים להשתמש בניתוח נתונים גיאו-מרחביים ב-BigQuery כדי להחליף את הפונקציונליות המרחבית. יש SDO_* פונקציות וסוגים ב-Oracle, כמו SDO_GEOM_KEY, ‏ SDO_GEOM_MBR ו-SDO_GEOM_MMB. הפונקציות האלה משמשות לניתוח מרחבי. אתם יכולים להשתמש בניתוח נתונים גיאו-מרחביים כדי לבצע ניתוח מרחבי.

תחביר DML

בקטע הזה נתייחס להבדלים בתחביר של שפת ניהול הנתונים בין Oracle ל-BigQuery.

הצהרה של INSERT

רוב ההצהרות של Oracle INSERT תואמות ל-BigQuery. בטבלה הבאה מפורטים החריגים.

לסקריפטים של DML ב-BigQuery יש סמנטיקה של עקביות שונה במקצת מההצהרות המקבילות ב-Oracle. לסקירה כללית על בידוד snapshot ועל הטיפול בסשנים ובעסקאות, קראו את CREATE [UNIQUE] INDEX section במקום אחר במסמך הזה.

Oracle BigQuery
INSERT INTO table VALUES (...); INSERT INTO table (...) VALUES (...);

ב-Oracle יש מילת מפתח DEFAULT לעמודות שלא יכולות להיות ריקות.

הערה: ב-BigQuery, השמטת שמות העמודות בהצהרה INSERT פועלת רק אם הערכים של כל העמודות בטבלת היעד נכללים בסדר עולה על סמך המיקומים הסידוריים שלהם.

INSERT INTO table VALUES (1,2,3);
INSERT INTO table VALUES (4,5,6);
INSERT INTO table VALUES (7,8,9);
INSERT ALL
INTO table (col1, col2) VALUES ('val1_1', 'val1_2')
INTO table (col1, col2) VALUES ('val2_1', 'val2_2')
INTO table (col1, col2) VALUES ('val3_1', 'val3_2')
.
.
.
SELECT 1 FROM DUAL; 		INSERT INTO table VALUES (1,2,3), (4,5,6),
(7,8,9);

ב-BigQuery יש מכסות DML שמגבילות את מספר פקודות ה-DML שאפשר להריץ מדי יום. כדי לנצל את המכסה בצורה הטובה ביותר, כדאי להשתמש בגישות הבאות:

  • לשלב כמה שורות בהצהרת INSERT אחת, במקום שורה אחת לכל פעולת INSERT.
  • משלבים כמה פקודות DML (כולל INSERT) באמצעות פקודת MERGE.
  • משתמשים ב-CREATE TABLE ... AS SELECT כדי ליצור טבלאות חדשות ולאכלס אותן.

הצהרה של UPDATE

הצהרות UPDATE של Oracle תואמות ברובן ל-BigQuery, אבל ב-BigQuery ההצהרה UPDATE חייבת לכלול סעיף WHERE.

מומלץ להשתמש בפקודות DML באצווה במקום בכמה פקודות UPDATE ו-INSERT בודדות. לסקריפטים של DML ב-BigQuery יש סמנטיקה של עקביות ששונה קצת מהצהרות מקבילות ב-Oracle. לסקירה כללית על בידוד snapshot ועל הטיפול בסשנים ובעסקאות, אפשר לעיין בקטע CREATE INDEX במסמך הזה.

בטבלה הבאה מוצגות הצהרות ב-Oracle UPDATE והצהרות ב-BigQuery שמבצעות את אותן משימות.

ב-BigQuery, ההצהרה UPDATE חייבת לכלול את הסעיף WHERE. למידע נוסף על UPDATE ב-BigQuery, קראו את הדוגמאות של UPDATE ב-BigQuery במסמכי התיעוד של DML.

הצהרות DELETE ו-TRUNCATE

ההצהרות DELETE ו-TRUNCATE הן שתי דרכים להסיר שורות מטבלה בלי להשפיע על הסכימה של הטבלה. השדה TRUNCATE לא נמצא בשימוש ב-BigQuery. עם זאת, אפשר להשתמש בהצהרות DELETE כדי להשיג את אותה תוצאה.

ב-BigQuery, ההצהרה DELETE חייבת לכלול את הסעיף WHERE. למידע נוסף על DELETE ב-BigQuery, קראו את הדוגמאות של DELETE ב-BigQuery במאמרי העזרה של DML.

Oracle BigQuery
DELETE database.table; DELETE FROM table WHERE TRUE;

הצהרה של MERGE

ההצהרה MERGE יכולה לשלב את הפעולות INSERT, ‏ UPDATE ו-DELETE בהצהרת UPSERT אחת, ולבצע את הפעולות באופן אטומי. הפעולה MERGE חייבת להתאים לכל היותר לשורת מקור אחת לכל שורת יעד. גם BigQuery וגם Oracle פועלים לפי תחביר ANSI.

עם זאת, לסקריפטים של DML ב-BigQuery יש סמנטיקה של עקביות שונה במקצת מההצהרות המקבילות ב-Oracle.

תחביר DDL

בקטע הזה נסביר על ההבדלים בתחביר של שפת הגדרת הנתונים בין Oracle ל-BigQuery.

הצהרה של CREATE TABLE

רוב ההצהרות של Oracle CREATE TABLE תואמות ל-BigQuery, חוץ מהאילוצים ורכיבי התחביר הבאים שלא משתמשים בהם ב-BigQuery:

  • STORAGE
  • TABLESPACE
  • DEFAULT
  • GENERATED ALWAYS AS
  • ENCRYPT
  • PRIMARY KEY (col, ...). מידע נוסף זמין במאמר CREATE INDEX.
  • UNIQUE INDEX. מידע נוסף זמין במאמר CREATE INDEX.
  • CONSTRAINT..REFERENCES
  • DEFAULT
  • PARALLEL
  • COMPRESS

למידע נוסף על CREATE TABLE ב-BigQuery, קראו את הדוגמאות של CREATE TABLE ב-BigQuery.

אפשרויות ומאפיינים של עמודות

עמודות זהות נוספו בגרסת Oracle 12c, שמאפשרת הגדלה אוטומטית של עמודה. האפשרות הזו לא קיימת ב-BigQuery, אבל אפשר להשיג את אותה תוצאה באמצעות הפעולה הבאה באצווה. למידע נוסף על מפתחות פונדקאיים ומאפיינים שמשתנים לאט (SCD), אפשר לעיין במדריכים הבאים:

Oracle BigQuery
CREATE TABLE table (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
); 		INSERT INTO dataset.table SELECT
  *,
  ROW_NUMBER() OVER () AS id
FROM dataset.table

תגובות בעמודה

ב-Oracle משתמשים בתחביר Comment כדי להוסיף הערות לעמודות. אפשר להטמיע את התכונה הזו באופן דומה ב-BigQuery באמצעות תיאור העמודה, כמו שמוצג בטבלה הבאה:

Oracle BigQuery
Comment on column table is 'column desc'; CREATE TABLE dataset.table (
   col1 STRING
OPTIONS(description="column desc")
);

טבלאות זמניות

‫Oracle תומכת בטבלאות זמניות, שמשמשות לעיתים קרובות לאחסון תוצאות ביניים בסקריפטים. ב-BigQuery יש תמיכה בטבלאות זמניות.

Oracle BigQuery
CREATE GLOBAL TEMPORARY TABLE
temp_tab
    (x INTEGER,
    y VARCHAR2(50))
  ON COMMIT DELETE ROWS;
COMMIT; 		CREATE TEMP TABLE temp_tab
(
  x INT64,
  y STRING
);
DELETE FROM temp_tab WHERE TRUE;

הרכיבים הבאים של Oracle לא נמצאים בשימוש ב-BigQuery:

  • ON COMMIT DELETE ROWS;
  • ON COMMIT PRESERVE ROWS;

יש עוד כמה דרכים לדמות טבלאות זמניות ב-BigQuery:

  • אורך חיים (TTL) של קבוצת נתונים: יוצרים קבוצת נתונים עם אורך חיים (TTL) קצר (למשל, שעה אחת) כדי שכל הטבלאות שנוצרות בקבוצת הנתונים יהיו זמניות (כי הן לא יישארו מעבר לאורך החיים (TTL) של קבוצת הנתונים). כדי לציין בבירור שהטבלאות זמניות, אפשר להוסיף את הקידומת temp לכל שמות הטבלאות במערך הנתונים הזה.

  • ‫TTL של טבלה: יוצרים טבלה עם זמן חיים קצר שספציפי לטבלה, באמצעות הצהרות DDL שדומות לאלה:

    CREATE TABLE temp.name (col1, col2, ...)
    
OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR));

  • סעיף WITH: אם צריך טבלה זמנית רק באותו בלוק, משתמשים בתוצאה זמנית באמצעות הצהרת WITH או שאילתת משנה.

הצהרה של CREATE SEQUENCE

ב-BigQuery לא משתמשים ברצפים, אבל אפשר להשיג את אותה תוצאה באמצעות השיטה הבאה לעיבוד באצווה. למידע נוסף על מפתחות פונדקאיים ומאפיינים שמשתנים לאט (SCD), אפשר לעיין במדריכים הבאים:

INSERT INTO dataset.table
    SELECT *,
      ROW_NUMBER() OVER () AS id
      FROM dataset.table

הצהרה של CREATE VIEW

בטבלה הבאה מוצגים המקבילים בין Oracle ל-BigQuery להצהרה CREATE VIEW.

Oracle BigQuery הערות
CREATE VIEW view_name AS SELECT ... CREATE VIEW view_name AS SELECT ...
CREATE OR REPLACE VIEW view_name AS SELECT ... CREATE OR REPLACE VIEW view_name AS SELECT ...
לא נתמך CREATE VIEW IF NOT EXISTS view_name OPTIONS(view_option_list) AS SELECT ... יוצר תצוגה חדשה רק אם התצוגה לא קיימת כרגע במערך הנתונים שצוין.

הצהרה של CREATE MATERIALIZED VIEW

ב-BigQuery, פעולות הרענון של תצוגות מהותיות מתבצעות באופן אוטומטי. אין צורך לציין אפשרויות רענון (לדוגמה, בזמן commit או לפי לוח זמנים) ב-BigQuery. מידע נוסף זמין במאמר מבוא לתצוגות מהותיות.

אם מסד הנתונים הטבלאי משתנה כל הזמן רק על ידי הוספות, השאילתה שמשתמשת בתצוגה המהותית (בין אם התצוגה מאוזכרת באופן מפורש או נבחרת על ידי הכלי לאופטימיזציה של שאילתות) סורקת את כל התצוגה המהותית, וגם דלתא במסד הנתונים הטבלאי מאז הרענון האחרון של התצוגה. המשמעות היא שהשאילתות מהירות וזולות יותר.

לעומת זאת, אם היו עדכונים (DML UPDATE / MERGE) או מחיקות (DML DELETE, חיתוך, פקיעת תוקף של מחיצה) במסד הנתונים הטבלאי מאז הרענון האחרון של התצוגה, התצוגה המהותית לא נסרקת ולכן השאילתה לא חוסכת בעלויות עד לרענון הבא של התצוגה. בעיקרון, כל עדכון או מחיקה במסד הנתונים הטבלאי מבטלים את התוקף של מצב התצוגה המהותית.

בנוסף, הנתונים ממאגר הנתונים (buffer) של הסטרימינג של מסד הנתונים הטבלאי לא נשמרים בתצוגה המהותית. מאגר הנתונים הזמני (buffer) של הסטרימינג עדיין נסרק במלואו, גם אם משתמשים בתצוגה המהותית.

בטבלה הבאה מוצגים המקבילים בין Oracle ל-BigQuery להצהרה CREATE MATERIALIZED VIEW.

Oracle BigQuery הערות
CREATE MATERIALIZED VIEW view_name
REFRESH FAST NEXT sysdate + 7
AS SELECT … FROM TABLE_1 		CREATE MATERIALIZED VIEW
view_name AS SELECT ...

הצהרה של CREATE [UNIQUE] INDEX

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

הוספה לאינדקס לשיפור הביצועים

ב-BigQuery אין צורך באינדקסים מפורשים, כי זהו מסד נתונים מבוסס-עמודות עם אופטימיזציה של שאילתות ואחסון. ב-BigQuery יש פונקציות כמו חלוקה למחיצות ואשכולות ושדות בתצוגת עץ, שיכולות לשפר את היעילות והביצועים של השאילתות באמצעות אופטימיזציה של אופן אחסון הנתונים.

הוספה לאינדקס לצורך עקביות (UNIQUE, PRIMARY INDEX)

ב-Oracle, אפשר להשתמש באינדקס ייחודי כדי למנוע שורות עם מפתחות לא ייחודיים בטבלה. אם תהליך מנסה להוסיף או לעדכן נתונים עם ערך שכבר קיים באינדקס, הפעולה נכשלת בגלל הפרת אינדקס.

ב-BigQuery אין אינדקסים מפורשים, ולכן אפשר להשתמש בהצהרת MERGE כדי להוסיף רק רשומות ייחודיות לטבלת יעד מטבלת ביניים, תוך התעלמות מרשומות כפולות. עם זאת, אין דרך למנוע ממשתמש עם הרשאות עריכה להוסיף רשומה כפולה.

כדי ליצור שגיאה על רשומות כפולות ב-BigQuery, אפשר להשתמש בהצהרת MERGE מטבלת ה-staging, כמו בדוגמה הבאה:

Oracle BigQuery
CREATE [UNIQUE] INDEX name; MERGE `prototype.FIN_MERGE` t \
USING `prototype.FIN_TEMP_IMPORT` m \
ON t.col1 = m.col1 \
  AND t.col2 = m.col2 \
WHEN MATCHED THEN \
  UPDATE SET t.col1 = ERROR(CONCAT('Encountered Error for ', m.col1, ' ', m.col2)) \
WHEN NOT MATCHED THEN \
  INSERT (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES(col1,col2,col3,col4,col5,col6, CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

בדרך כלל, המשתמשים מעדיפים להסיר כפילויות בעצמם כדי למצוא שגיאות במערכות במורד הזרם.

ב-BigQuery אין תמיכה בעמודות DEFAULT ו-IDENTITY (רצפים).

מתבצעת נעילה

ב-BigQuery אין מנגנון נעילה כמו ב-Oracle, ואפשר להריץ בו שאילתות בו-זמנית (עד המכסה). רק לפקודות DML יש מגבלות מסוימות על בו-זמניות, ויכול להיות שבתרחישים מסוימים הן ידרשו נעילת טבלה במהלך הביצוע.

הצהרות SQL פרוצדורליות

בקטע הזה מוסבר איך להמיר הצהרות SQL פרוצדורליות שמשמשות בפרוצדורות, בפונקציות ובטריגרים מאוחסנים מ-Oracle ל-BigQuery.

הצהרה של CREATE PROCEDURE

התהליך המאוחסן נתמך כחלק מגרסת הבטא של BigQuery Scripting.

Oracle BigQuery הערות
CREATE PROCEDURE CREATE PROCEDURE בדומה ל-Oracle, ‏BigQuery תומך ב-IN, OUT, INOUT מצבי ארגומנטים. מפרטי תחביר אחרים לא נתמכים ב-BigQuery.
CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE
CALL CALL

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

הצהרה של CREATE TRIGGER

ב-BigQuery לא משתמשים בטריגרים. לוגיקת האפליקציה שמבוססת על שורות צריכה להיות מטופלת בשכבת האפליקציות. אפשר להפעיל את הפונקציונליות של הטריגר באמצעות כלי ההטמעה, Pub/Sub ו/או פונקציות Cloud Run במהלך ההטמעה או באמצעות סריקות קבועות.

הצהרה על משתנה והקצאה

בטבלה הבאה מוצגות הצהרות DECLARE ב-Oracle וההצהרות המקבילות להן ב-BigQuery.

Oracle BigQuery
DECLARE
  L_VAR NUMBER;
BEGIN
  L_VAR := 10 + 20;
END; 		DECLARE L_VAR int64;
BEGIN
  SET L_VAR = 10 + 20;
  SELECT L_VAR;
END
SET var = value; SET var = value;

הצהרות ופעולות של סמן

ב-BigQuery אין תמיכה בסמנים, ולכן ההצהרות הבאות לא משמשות ב-BigQuery:

הצהרות SQL דינמיות

הצהרת ה-SQL הדינמית הבאה של Oracle והמקבילה שלה ב-BigQuery:

Oracle BigQuery
EXECUTE IMMEDIATE sql_str

[USING IN OUT [, ...]];

EXECUTE IMMEDIATE

sql_expression [INTO variable[, ...]]

[USING identifier[, ...]];

;

הצהרות של בקרת זרימה

בטבלה הבאה מוצגות הצהרות בקרת זרימה של Oracle והמקבילות שלהן ב-BigQuery.

Oracle BigQuery
IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF; 		IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;
SET SERVEROUTPUT ON;
DECLARE
x INTEGER DEFAULT 0;
y INTEGER DEFAULT 0;
BEGIN
LOOP
  IF x>= 10 THEN
    EXIT;
  ELSIF x>= 5 THEN
     y := 5;
  END IF;
  x := x + 1;
END LOOP;
dbms_output.put_line(x||','||y);
END;
/ 		DECLARE x INT64 DEFAULT 0;
DECLARE y INT64 DEFAULT 0;
LOOP
  IF x>= 10 THEN
     LEAVE;
  ELSE IF x>= 5 THEN
    SET y = 5;
    END IF;
  END IF;
  SET x = x + 1;
END LOOP;
SELECT x,y;
LOOP
  sql_statement_list
END LOOP; 		LOOP
  sql_statement_list
END LOOP;
WHILE boolean_expression DO
  sql_statement_list
END WHILE; 		WHILE boolean_expression DO
  sql_statement_list
END WHILE;
FOR LOOP השדה FOR LOOP לא נמצא בשימוש ב-BigQuery. משתמשים בהצהרות LOOP אחרות.
BREAK BREAK
CONTINUE CONTINUE
CONTINUE/EXIT WHEN צריך להשתמש ב-CONTINUE עם התנאי IF.
GOTO ההצהרה GOTO לא קיימת ב-BigQuery. צריך להשתמש בתנאי IF.

הצהרות SQL של מטא-נתונים ועסקאות

Oracle BigQuery
GATHER_STATS_JOB עדיין לא בשימוש ב-BigQuery.
LOCK TABLE table_name IN [SHARE/EXCLUSIVE] MODE NOWAIT; עדיין לא בשימוש ב-BigQuery.
Alter session set isolation_level=serializable; /

SET TRANSACTION ...

ב-BigQuery תמיד נעשה שימוש בבידוד snapshot. לפרטים נוספים, ראו הבטחות עקביות ורמת בידוד של טרנזקציה במאמר הזה.
EXPLAIN PLAN ... לא בשימוש ב-BigQuery.

תכונות דומות הן הסבר על תוכנית לביצוע שאילתה בממשק המשתמש של BigQuery באינטרנט והקצאת יחידות קיבולת (Slot), ורישום ביומן הביקורת ב-Stackdriver.

SELECT * FROM DBA_[*];

(תצוגות Oracle DBA_/ALL_/V$ ‎)

SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES;

מידע נוסף זמין במאמר מבוא ל-INFORMATION_SCHEMA ב-BigQuery.

SELECT * FROM GV$SESSION;

SELECT * FROM V$ACTIVE_SESSION_HISTORY;

ב-BigQuery אין את הקונספט המסורתי של סשן. אפשר להציג משימות של שאילתות בממשק המשתמש או לייצא יומני ביקורת של Stackdriver ל-BigQuery ולנתח את יומני BigQuery כדי לנתח משימות. מידע נוסף זמין במאמר הצגת פרטי המשימה.
START TRANSACTION;

LOCK TABLE table_A IN EXCLUSIVE MODE NOWAIT;

DELETE FROM table_A;

INSERT INTO table_A SELECT * FROM table_B;

COMMIT;

 החלפת התוכן של טבלה בפלט של שאילתה שקולה לעסקה. אפשר לעשות את זה באמצעות פעולת שאילתה או העתקה.

באמצעות שאילתה:

bq query --replace --destination_table table_A 'SELECT * FROM table_B';

שימוש בעותק:

bq cp -f table_A table_B

הצהרות SQL עם מספר הצהרות ומספר שורות

גם Oracle וגם BigQuery תומכים בטרנזקציות (סשנים), ולכן תומכים בהצהרות שמופרדות בנקודה ופסיק ומבוצעות יחד באופן עקבי. מידע נוסף מופיע במאמר טרנזקציות עם כמה הצהרות.

קודי שגיאה והודעות שגיאה

קודי השגיאה של Oracle וקודי השגיאה של BigQuery שונים. אם לוגיקת האפליקציה שלכם תופסת כרגע את השגיאות, נסו לבטל את מקור השגיאה, כי BigQuery לא מחזיר את אותם קודי שגיאה.

התחייבויות לעקביות ורמת בידוד של טרנזקציה

גם Oracle וגם BigQuery הן אטומיות, כלומר תואמות ל-ACID ברמת המוטציה על פני שורות רבות. לדוגמה, פעולת MERGE היא אטומית, גם אם מוסיפים ומעדכנים כמה ערכים.

טרנזקציות

‫Oracle מספקת רמות בידוד של טרנזקציות שמאפשרות קריאה של טרנזקציות שבוצעו או סריאליזציה. יכול להיות שיהיו מבויים סתומים. משימות הוספה של Oracle פועלות באופן עצמאי.

ב-BigQuery אפשר גם לבצע טרנזקציות. ב-BigQuery, בקרת בו-זמניות אופטימית (הראשון שמבצע commit מנצח) מובטחת באמצעות בידוד snapshot, שבו שאילתה קוראת את הנתונים האחרונים שבוצע להם commit לפני שהשאילתה מתחילה. הגישה הזו מבטיחה את אותה רמת עקביות לכל שורה, לכל מוטציה ובכל השורות באותה פקודת DML, אבל מונעת מבוי סתום. אם יש כמה הצהרות UPDATE באותה טבלה, BigQuery עובר לבקרת בו-זמניות פסימית ומוסיף לתור כמה הצהרות UPDATE, ומנסה שוב באופן אוטומטי במקרה של התנגשויות. ‫INSERT אפשר להריץ פקודות DML ומשימות טעינה בו-זמנית ובאופן עצמאי כדי להוסיף נתונים לטבלאות.

חזרה לגרסה קודמת

‫Oracle תומכת בהחזרות למצב קודם. ב-BigQuery אין גבולות מפורשים לעסקאות, ולכן אין מושג של ביטול מפורש. הפתרונות העקיפים הם מעצבי טבלאות או שימוש ב-FOR SYSTEM_TIME AS OF.

מגבלות של מסדי נתונים

המכסות והמגבלות העדכניות של BigQuery משתמשים עם נפח שימוש גדול יכולים לפנות ל-Cloud Customer Care כדי להגדיל הרבה מהמכסות. בטבלה הבאה מוצגת השוואה בין המגבלות של מסדי הנתונים של Oracle ו-BigQuery.

הגבלה Oracle BigQuery
טבלאות לכל מסד נתונים לא מוגבל לא מוגבל
עמודות בכל טבלה 1000 10,000
גודל שורה מקסימלי ללא הגבלה (תלוי בסוג העמודה) ‫100MB
אורך השמות של עמודות וטבלאות אם גרסה 12.2>= 128 בתים

אחרת 30 בייטים

‫16,384 תווים ב-Unicode
שורות בכל טבלה ללא הגבלה ללא הגבלה
האורך המקסימלי של בקשת SQL ללא הגבלה ‫1MB (האורך המקסימלי של שאילתת GoogleSQL לא פתורה)

‫12MB (אורך מקסימלי של שאילתת GoogleSQL ושאילתה מדור קודם)

סטרימינג:

  • ‫10MB (מגבלת הגודל של בקשת HTTP)
  • ‫10,000 (מספר השורות המקסימלי לבקשה)
גודל מקסימלי של בקשה ותגובה ללא הגבלה ‫10MB (בקשה) ו-10GB (תגובה), או כמעט ללא הגבלה אם משתמשים בחלוקה לדפים או ב-Cloud Storage API.
מספר מקסימלי של סשנים בו-זמנית מוגבל על ידי הפרמטרים של הסשנים או התהליכים ‫100 שאילתות בו-זמניות (אפשר להגדיל את המספר באמצעות הזמנת משבצות), 300 בקשות API בו-זמניות לכל משתמש.
מספר מקסימלי של טעינות בו-זמניות (מהירות) מוגבל על ידי הפרמטרים של הסשנים או התהליכים אין מגבלה על מספר המשימות בו-זמנית, המשימות נכנסות לתור. ‫100,000 משימות טעינה ליום בכל פרויקט.

מגבלות אחרות של Oracle Database כוללות מגבלות על סוגי נתונים, מגבלות פיזיות על מסדי נתונים, מגבלות לוגיות על מסדי נתונים ומגבלות על תהליכים וזמני ריצה.