מדריך לתרגום SQL ב-Snowflake

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

סוגי הנתונים

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



פתית שלג BigQuery הערות
NUMBER/ DECIMAL/NUMERIC NUMERIC/BIGNUMERIC אפשר למפות אותו ל-NUMERIC או ל-BIGNUMERIC, בהתאם לדיוק ולסולם.

סוג הנתונים NUMBER ב-Snowflake תומך ב-38 ספרות של דיוק וב-37 ספרות של קנה מידה. אפשר לציין את הדיוק והקנה מידה בהתאם למשתמש.

‫BigQuery תומך ב-NUMERIC וב-BIGNUMERIC עם דיוק וקנה מידה שניתן לציין באופן אופציונלי בתוך גבולות מסוימים.
INT/INTEGER BIGNUMERIC ‫INT/INTEGER וכל סוגי הנתונים האחרים שדומים ל-INT, כמו BIGINT, TINYINT, SMALLINT, BYTEINT, מייצגים כינוי לסוג הנתונים NUMBER, שבו אי אפשר לציין את הדיוק והקנה מידה, והם תמיד NUMBER(38, 0)

כברירת מחדל, BigQuery ממיר INTEGER ל-INT64. כדי להגדיר את התרגום של SQL כך שהוא יומר לסוגי נתונים אחרים, אפשר להשתמש באפשרות ההגדרה REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
BIGINT BIGNUMERIC
SMALLINT BIGNUMERIC
TINYINT BIGNUMERIC
BYTEINT BIGNUMERIC
FLOAT/
FLOAT4/
FLOAT8		 FLOAT64 סוג הנתונים FLOAT ב-Snowflake קובע ש-NaN גדול מ-X, כאשר X הוא כל ערך FLOAT (חוץ מ-NaN עצמו).

סוג הנתונים FLOAT ב-BigQuery מגדיר את הערך NaN כ-< X, כאשר X הוא כל ערך FLOAT (חוץ מ-NaN עצמו).
DOUBLE/
DOUBLE PRECISION/
REAL		 FLOAT64 סוג הנתונים DOUBLE ב-Snowflake זהה לסוג הנתונים FLOAT ב-Snowflake, אבל בדרך כלל הוא מוצג בטעות כ-FLOAT. הוא מאוחסן בצורה תקינה כקובץ DOUBLE.
VARCHAR STRING אורך הנתונים המקסימלי של סוג הנתונים VARCHAR ב-Snowflake הוא 128MB (לא דחוס). אם לא מציינים אורך, ברירת המחדל היא האורך המקסימלי.

סוג הנתונים STRING ב-BigQuery מאוחסן כ-Unicode עם קידוד UTF-8 באורך משתנה. מידע נוסף על מגבלות של עמודות ושורות זמין במאמר Query jobs.
CHAR/CHARACTER STRING
STRING/TEXT STRING סוג הנתונים STRING ב-Snowflake זהה ל-VARCHAR ב-Snowflake.
BINARY BYTES
VARBINARY BYTES
BOOLEAN BOOL סוג הנתונים BOOL ב-BigQuery יכול לקבל רק TRUE/FALSE, בניגוד לסוג הנתונים BOOL ב-Snowflake, שיכול לקבל TRUE/FALSE/NULL.
DATE DATE הסוג DATE ב-Snowflake מקבל את רוב פורמטי התאריכים הנפוצים, בניגוד לסוג DATE ב-BigQuery, שמקבל רק תאריכים בפורמט YYYY-[M]M-[D]D.
TIME TIME הסוג TIME ב-Snowflake תומך ב-0 עד 9 ננו-שניות של דיוק, בעוד שהסוג TIME ב-BigQuery תומך ב-0 עד 6 ננו-שניות של דיוק.
TIMESTAMP DATETIME ‫TIMESTAMP הוא כינוי שניתן להגדרה על ידי המשתמש, ומוגדר כברירת מחדל כ-TIMESTAMP_NTZ, שממופה ל-DATETIME ב-BigQuery.
TIMESTAMP_LTZ TIMESTAMP
TIMESTAMP_NTZ/DATETIME
DATETIME
TIMESTAMP_TZ TIMESTAMP
OBJECT JSON
VARIANT JSON
ARRAY ARRAY<JSON> שירות התרגום של SQL שומר על סוג הנתונים של מערכים מוקלדים. במערכים לא מוקלדים, כמו ARRAY<VARIANT>, ‏ BigQuery ממיר אותם ל-ARRAY<JSON>

ב-BigQuery יש גם את סוגי הנתונים הבאים, שאין להם מקבילה ישירה ב-Snowflake:

תחביר CREATE FUNCTION

בטבלה הבאה מפורטים ההבדלים בתחביר של יצירת פונקציות מוגדרות על ידי המשתמש (UDF) ב-SQL בין Snowflake לבין BigQuery.

Snowflake BigQuery

CREATE [ OR REPLACE ] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

s

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

AS sql_function_definition


הערה: ב-SQL UDF ב-BigQuery, סוג הנתונים שמוחזר הוא אופציונלי. מערכת BigQuery מסיקה את סוג התוצאה של הפונקציה מגוף הפונקציה של SQL כששאילתה קוראת לפונקציה.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS TABLE (col_name, col_data_type[,..])

AS sql_function_definition


CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


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

CREATE [SECURE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


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

CREATE FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


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

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


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

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


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

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS [' | $$]

sql_function_definition

[' | $$]

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


הערה: שימוש בגרשיים בודדים או ברצף תווים כמו ציטוט באמצעות סימן הדולר ($$) is not required or supported in BigQuery. BigQuery implicitly interprets the SQL expression.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note: Adding comments or descriptions in UDFs is not supported in BigQuery.

CREATE [OR REPLACE] FUNCTION function_name

(x integer, y integer)

RETURNS integer

AS $$

SELECT x + y

$$


Note: Snowflake does not support ANY TYPE for SQL UDFs. However, it supports using VARIANT data types.

CREATE [OR REPLACE] FUNCTION function_name

(x ANY TYPE, y ANY TYPE)

AS

SELECT x + y



Note: BigQuery supports using ANY TYPE as argument type. The function will accept an input of any type for this argument. For more information, see templated parameter in BigQuery.

BigQuery also supports the CREATE FUNCTION IF NOT EXISTS statement which treats the query as successful and takes no action if a function with the same name already exists.

BigQuery's CREATE FUNCTION statement also supports creating TEMPORARY or TEMP functions, which do not have a Snowflake equivalent. See calling UDFs for details on executing a BigQuery persistent UDF.

DROP FUNCTION syntax

The following table addresses differences in DROP FUNCTION syntax between Snowflake and BigQuery.

Snowflake BigQuery

DROP FUNCTION [IF EXISTS]

function_name

([arg_data_type, ... ])

DROP FUNCTION [IF EXISTS] dataset_name.function_name


Note: BigQuery does not require using the function's signature (argument data type) for deleting the function.

BigQuery requires that you specify the project_name if the function is not located in the current project.

Additional function commands

This section covers additional UDF commands supported by Snowflake that are not directly available in BigQuery.

ALTER FUNCTION syntax

Snowflake supports the following operations using ALTER FUNCTION syntax.

  • Renaming a UDF
  • Converting to (or reverting from) a secure UDF
  • Adding, overwriting, removing a comment for a UDF

As configuring function security and adding function comments is not available in BigQuery, ALTER FUNCTION syntax is not supported. However, the CREATE FUNCTION statement can be used to create a UDF with the same function definition but a different name.

DESCRIBE FUNCTION syntax

Snowflake supports describing a UDF using DESC[RIBE] FUNCTION syntax. This is not supported in BigQuery. However, querying UDF metadata via INFORMATION SCHEMA will be available soon as part of the product roadmap.

SHOW USER FUNCTIONS syntax

In Snowflake, SHOW USER FUNCTIONS syntax can be used to list all UDFs for which users have access privileges. This is not supported in BigQuery. However, querying UDF metadata via INFORMATION SCHEMA will be available soon as part of the product roadmap.

Stored procedures

Snowflake stored procedures are written in JavaScript, which can execute SQL statements by calling a JavaScript API. In BigQuery, stored procedures are defined using a block of SQL statements.

CREATE PROCEDURE syntax

In Snowflake, a stored procedure is executed with a CALL command while in BigQuery, stored procedures are executed like any other BigQuery function.

The following table addresses differences in stored procedure creation syntax between Snowflake and BigQuery.

Snowflake BigQuery

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS procedure_definition;


Note: Snowflake requires that stored procedures return a single value. Hence, return data type is a required option.		 CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_mode arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


arg_mode: IN | OUT | INOUT


Note: BigQuery doesn't support a return type for stored procedures. Also, it requires specifying argument mode for each argument passed.

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS

$$

javascript_code

$$;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

statement_list

END;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[{CALLED ON NULL INPUT | {RETURNS NULL ON NULL INPUT | STRICT}}]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


הערה: ההתנהגות של הפרוצדורה עבור קלט null מטופלת באופן מרומז ב-BigQuery ואין צורך לציין אותה כאפשרות נפרדת.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


הערה:נדיפות של פרוצדורה היא לא פרמטר שאפשר להגדיר ב-BigQuery. היא מקבילה לתנוד של Snowflake‏ IMMUTABLE.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


הערה: אי אפשר להוסיף הערות או תיאורים בהגדרות של פרוצדורות ב-BigQuery.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[EXECUTE AS { CALLER | OWNER }]

AS procedure_definition;


הערה: Snowflake תומך בציון של המתקשר או הבעלים של הפרוצדורה לביצוע

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


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

‫BigQuery תומך גם בהצהרה CREATE PROCEDURE IF NOT EXISTS, שאם קיימת פונקציה עם אותו שם, מתייחסת לשאילתה כאל שאילתה שהצליחה ולא מבצעת פעולה.

תחביר DROP PROCEDURE

בטבלה הבאה מפורטים ההבדלים בתחביר של DROP FUNCTION בין Snowflake לבין BigQuery.

Snowflake BigQuery

DROP PROCEDURE [IF EXISTS]

procedure_name

([arg_data_type, ... ])

DROP PROCEDURE [IF EXISTS] dataset_name.procedure_name


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

ב-BigQuery, חובה לציין את project_name אם הפרוצדורה לא נמצאת בפרויקט הנוכחי.

פקודות נוספות של הליך

ב-Snowflake יש פקודות נוספות כמו ALTER PROCEDURE, DESC[RIBE] PROCEDURE, ו- SHOW PROCEDURES לניהול הפרוצדורות המאוחסנות. אין תמיכה באפשרויות האלה ב-BigQuery.

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

Snowflake BigQuery

BEGIN [ { WORK | TRANSACTION } ] [ NAME <name> ]; START_TRANSACTION [ name <name> ];

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

COMMIT;

לא נעשה בו שימוש ב-BigQuery.

ROLLBACK;

לא בשימוש ב-BigQuery

SHOW LOCKS [ IN ACCOUNT ]; SHOW TRANSACTIONS [ IN ACCOUNT ]; Note: If the user has the ACCOUNTADMIN role, the user can see locks/transactions for all users in the account.

לא נעשה בו שימוש ב-BigQuery.

הצהרות SQL מרובות שורות והצהרות SQL מרובות

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

עמודות של מטא-נתונים לקבצים בהמתנה

‫Snowflake יוצרת אוטומטית מטא-נתונים לקבצים בשלבים פנימיים וחיצוניים. אפשר לשאול שאילתות על המטא-נתונים האלה ולטעון אותם לטבלה לצד עמודות נתונים רגילות. אפשר להשתמש בעמודות המטא-נתונים הבאות:

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

גם Snowflake וגם BigQuery הן אטומיות – כלומר, הן תואמות ל-ACID ברמת כל שינוי בהרבה שורות.

טרנזקציות

לכל עסקה ב-Snowflake מוקצה זמן התחלה ייחודי (כולל אלפיות השנייה) שמוגדר כמזהה העסקה. ‫Snowflake תומכת רק ברמת הבידוד READ COMMITTED. עם זאת, יכול להיות ששינויים שבוצעו על ידי הצהרה אחת יופיעו בהצהרה אחרת אם שתיהן נמצאות באותה עסקה – גם אם השינויים האלה עדיין לא אושרו. עסקאות ב-Snowflake מקבלות נעילות על משאבים (טבלאות) כשמשנים את המשאב הזה. המשתמשים יכולים לשנות את הזמן המקסימלי שבו ימתין משפט חסום עד שיפוג הזמן שלו. פקודות DML מבוצעות אוטומטית אם הפרמטר AUTOCOMMIT מופעל.

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

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

אם סשן של עסקה ב-Snowflake מסתיים באופן לא צפוי לפני שהעסקה מאושרת או מבוטלת, העסקה נשארת במצב מנותק. המשתמש צריך להריץ את הפקודה SYSTEM$ABORT_TRANSACTION כדי לבטל את העסקה המנותקת, אחרת Snowflake יבצע Rollback לעסקה המנותקת אחרי ארבע שעות של חוסר פעילות. אם מתרחש מצב של קיפאון, Snowflake מזהה את הקיפאון ובוחר את ההצהרה העדכנית ביותר לביטול. אם פקודת ה-DML בעסקה שנפתחה באופן מפורש נכשלת, השינויים מבוטלים, אבל העסקה נשארת פתוחה עד שהיא מאושרת או מבוטלת. אי אפשר לבטל הצהרות DDL ב-Snowflake כי הן מבוצעות אוטומטית.

‫BigQuery תומך בהצהרת ROLLBACK TRANSACTION. אין הצהרה של ABORT ב-BigQuery.

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

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

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

בטבלה הבאה מוצגת השוואה בין מגבלות מסד הנתונים של Snowflake ושל BigQuery.

מגבלה Snowflake BigQuery
גודל הטקסט של השאילתה ‫1MB ‫1MB
מספר מקסימלי של שאילתות בו-זמנית ‫XS Warehouse - 8
‫S Warehouse - 16
‫M Warehouse - 32
‫L Warehouse - 64
‫XL Warehouse - 128		 100