העברת נתונים הפוכה מ-Snowflake ל-Spanner באמצעות BQ

1. פיתוח צינור Reverse ETL מ-Snowflake ל-Spanner באמצעות Google Cloud Storage ו-BigQuery

מבוא

בשיעור הזה תלמדו איך ליצור צינור עיבוד נתונים של Reverse ETL מ-Snowflake ל-Spanner. באופן מסורתי, צינורות ETL (חילוץ, טרנספורמציה, טעינה) מעבירים נתונים ממסדי נתונים תפעוליים לתוך מחסן נתונים כמו Snowflake לצורך ניתוח. צינור ETL הפוך עושה את הפעולה ההפוכה: הוא מעביר נתונים מעובדים ומסודרים מ מחסן הנתונים בחזרה למערכות תפעוליות, שבהן אפשר להשתמש בנתונים כדי להפעיל אפליקציות, לספק תכונות שפונות למשתמשים או לקבל החלטות בזמן אמת.

המטרה היא להעביר מערך נתונים מצטבר מטבלת Snowflake Iceberg אל Spanner, מסד נתונים רלציוני מבוזר גלובלית שמתאים באופן אידיאלי לאפליקציות עם זמינות גבוהה.

כדי לעשות את זה, משתמשים ב-Google Cloud Storage‏ (GCS) וב-BigQuery כשלבי ביניים. הנה פירוט של זרימת הנתונים וההיגיון מאחורי הארכיטקטורה הזו:

  1. מ-Snowflake אל Google Cloud Storage‏ (GCS) בפורמט Iceberg:
  • השלב הראשון הוא לייצא את הנתונים מ-Snowflake בפורמט פתוח ומוגדר היטב. הטבלה מיוצאת בפורמט Apache Iceberg. במהלך התהליך הזה, הנתונים הבסיסיים נכתבים כקבוצה של קבצי Parquet והמטא-נתונים של הטבלה (סכימה, מחיצות, מיקומי קבצים) נכתבים כקבצי JSON ו-Avro. העברת המבנה המלא של הטבלה ל-GCS מאפשרת ניוד של הנתונים וגישה אליהם מכל מערכת שמזהה את פורמט Iceberg.
  1. המרת טבלאות Iceberg ב-GCS לטבלה חיצונית של BigLake ב-BigQuery:
  • במקום לטעון את הנתונים ישירות מ-GCS ל-Spanner, נעשה שימוש ב-BigQuery ככלי ביניים יעיל. תצרו טבלה חיצונית של BigLake ב-BigQuery שמפנה ישירות לקובץ המטא-נתונים של Iceberg ב-GCS. לגישה הזו יש כמה יתרונות:
  • ללא שכפול נתונים:‏ BigQuery קורא את מבנה הטבלה מהמטא-נתונים ומריץ שאילתות על קובצי הנתונים בפורמט Parquet במקום בלי להטמיע אותם, וכך חוסך זמן רב ועלויות אחסון.
  • שאילתות מאוחדות: מאפשרות להריץ שאילתות SQL מורכבות על נתונים ב-GCS כאילו היו טבלה מקורית ב-BigQuery.
  1. מ-BigQuery ל-Spanner:
  • השלב האחרון הוא להעביר את הנתונים מ-BigQuery ל-Spanner. כדי לעשות את זה, תשתמשו בתכונה עוצמתית ב-BigQuery שנקראת EXPORT DATA שאילתה, שהיא השלב של "העברת נתונים הפוכה".
  • מוכנות תפעולית:‏ Spanner מיועד לעומסי עבודה טרנזקציוניים, ומספק עקביות חזקה וזמינות גבוהה לאפליקציות. העברת הנתונים ל-Spanner מאפשרת גישה אליהם לאפליקציות שפונות למשתמשים, לממשקי API ולמערכות תפעוליות אחרות שנדרשות בהן בדיקות נקודתיות עם השהיה נמוכה.
  • מדרגיות: התבנית הזו מאפשרת להשתמש ביכולות הניתוח של BigQuery כדי לעבד מערכי נתונים גדולים, ואז להציג את התוצאות בצורה יעילה באמצעות התשתית הגלובלית של Spanner, שניתנת להרחבה.

שירותים ומינוחים

  • ‫Snowflake – פלטפורמת נתונים בענן שמספקת מחסן נתונים כשירות.
  • ‫Spanner – מסד נתונים רלציוני מנוהל שמופץ באופן גלובלי.
  • ‫Google Cloud Storage – שירות אחסון ה-Blob של Google Cloud.
  • ‫BigQuery – מחסן נתונים (data warehouse) מנוהל וללא שרתים לניתוח נתונים.
  • ‫Iceberg – פורמט טבלה פתוח שהוגדר על ידי Apache ומספק הפשטה של פורמטים נפוצים של קובצי נתונים בקוד פתוח.
  • ‫Parquet – פורמט קובץ בינארי של נתונים עמודתיים בקוד פתוח מבית Apache.

מה תלמדו

  • איך טוענים נתונים ל-Snowflake
  • איך יוצרים קטגוריה ב-GCS
  • איך מייצאים טבלה מ-Snowflake ל-GCS בפורמט Iceberg
  • איך מגדירים מופע Spanner
  • איך טוענים טבלאות חיצוניות של BigLake ב-BigQuery אל Spanner

2. הגדרה, דרישות ומגבלות

דרישות מוקדמות

  • חשבון Snowflake
  • כדי לייצא מ-BigQuery ל-Spanner, צריך חשבון Google Cloud עם הזמנה ברמה Enterprise או ברמה גבוהה יותר ב-BigQuery.
  • גישה ל-Google Cloud Console דרך דפדפן אינטרנט
  • טרמינל להרצת פקודות של Google Cloud CLI
  • אם המדיניות iam.allowedPolicyMemberDomains מופעלת בארגון שלכם ב-Google Cloud, יכול להיות שאדמין יצטרך להעניק חריגה כדי לאפשר חשבונות שירות מדומיינים חיצוניים. הנושא הזה יוסבר בשלב מאוחר יותר, אם הוא רלוונטי.

מגבלות

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

פתית שלג לקרחון

סוגי הנתונים בעמודות שונים בין Snowflake לבין Iceberg. מידע על תרגום ביניהם זמין בתיעוד של Snowflake.

‫Iceberg ל-BigQuery

יש כמה מגבלות כשמשתמשים ב-BigQuery כדי לבצע שאילתות בטבלאות Iceberg. הרשימה המלאה זמינה במאמרי העזרה של BigQuery. שימו לב שסוגים כמו BIGNUMERIC,‏ INTERVAL,‏ JSON,‏ RANGE או GEOGRAPHY לא נתמכים כרגע.

‫BigQuery ל-Spanner

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

  • STRUCT
  • GEOGRAPHY
  • DATETIME
  • RANGE
  • TIME

בנוסף, אם בפרויקט BigQuery נעשה שימוש בניב GoogleSQL, לא תהיה תמיכה בייצוא ל-Spanner של הסוגים המספריים הבאים:

  • BIGNUMERIC

רשימה מלאה ועדכנית של המגבלות זמינה במסמכי התיעוד הרשמיים: מגבלות על ייצוא ל-Spanner.

פתית שלג

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

הרשאות IAM ב-Google Cloud Platform

כדי לבצע את כל השלבים ב-codelab הזה, לחשבון Google צריכות להיות ההרשאות הבאות.

חשבונות שירות

iam.serviceAccountKeys.create

מאפשרת יצירה של חשבונות שירות.

Spanner

spanner.instances.create

מאפשר ליצור מופע חדש של Spanner.

spanner.databases.create

מאפשר להריץ הצהרות DDL כדי ליצור

spanner.databases.updateDdl

מאפשר להריץ הצהרות DDL כדי ליצור טבלאות במסד הנתונים.

Google Cloud Storage

storage.buckets.create

מאפשר ליצור מאגר חדש ב-GCS לאחסון קובצי Parquet שיוצאו.

storage.objects.create

מאפשר לכתוב את קובצי Parquet המיוצאים לקטגוריית GCS.

storage.objects.get

מאפשר ל-BigQuery לקרוא את קובצי Parquet ממאגר GCS.

storage.objects.list

מאפשר ל-BigQuery להציג את קובצי Parquet בדלי GCS.

Dataflow

Dataflow.workitems.lease

מאפשרת לתבוע פריטי עבודה מ-Dataflow.

Dataflow.workitems.sendMessage

מאפשר לעובד של Dataflow לשלוח הודעות בחזרה לשירות Dataflow.

Logging.logEntries.create

מאפשר לעובדי Dataflow לכתוב רשומות ביומן ב-Google Cloud Logging.

לנוחותכם, אפשר להשתמש בתפקידים מוגדרים מראש שכוללים את ההרשאות האלה.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

הגדרת מאפיינים לשימוש חוזר

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

  • ‫GCP_REGION – האזור הספציפי שבו ימוקמו המשאבים של GCP. כאן אפשר לראות את רשימת האזורים.
  • GCP_PROJECT – מזהה הפרויקט ב-GCP שבו רוצים להשתמש.
  • ‫GCP_BUCKET_NAME – שם קטגוריית GCS שתיצור, ושבה יאוחסנו קובצי הנתונים.
export GCP_REGION = <GCP REGION HERE> 
export GCP_PROJECT= <GCP PROJECT HERE>
export GCS_BUCKET_NAME = <GCS BUCKET NAME HERE>
export SPANNER_INSTANCE = <SPANNER INSTANCE ID HERE>
export SPANNER_DB = <SPANNER DATABASE ID HERE>

פרויקט ב-Google Cloud

פרויקט הוא יחידה בסיסית של ארגון ב-Google Cloud. אם האדמין סיפק לכם קוד לשימוש, אתם יכולים לדלג על השלב הזה.

אפשר ליצור פרויקט באמצעות ה-CLI באופן הבא:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

מידע נוסף על יצירה וניהול של פרויקטים

הגדרת Spanner

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

יצירת מכונה

gcloud spanner instances create $SPANNER_INSTANCE \
--config=regional-$GCP_REGION \
--description="Codelabs Snowflake RETL" \
--processing-units=100 \
--edition=ENTERPRISE

יצירת מסד הנתונים

gcloud spanner databases create $SPANNER_DB \
--instance=$SPANNER_INSTANCE

3. יצירת קטגוריה של Google Cloud Storage

‫Google Cloud Storage‏ (GCS) ישמש לאחסון קובצי הנתונים בפורמט Parquet ומטא-נתונים בפורמט Iceberg שנוצרו על ידי Snowflake. לשם כך, קודם צריך ליצור קטגוריה חדשה שתשמש כיעד הקובץ. מבצעים את השלבים הבאים מחלון Terminal במכונה מקומית.

יצירת הקטגוריה

כדי ליצור קטגוריה לאחסון באזור ספציפי (למשל us-central1), משתמשים בפקודה הבאה:

gcloud storage buckets create gs://$GCS_BUCKET_NAME --location=$GCP_REGION

אימות יצירת הקטגוריה

אחרי שהפקודה הזו מצליחה, בודקים את התוצאה על ידי הצגת רשימה של כל הדליים. הקטגוריה החדשה אמורה להופיע ברשימה שמתקבלת. הפניות לקטגוריות מופיעות בדרך כלל עם הקידומת gs:// לפני שם הקטגוריה.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

בדיקת הרשאות כתיבה

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

echo "Hello, GCS" | gcloud storage cp - gs://$GCS_BUCKET_NAME/hello.txt

אימות הקובץ שהועלה

הצגת רשימה של האובייקטים בקטגוריה. הנתיב המלא של הקובץ שהועלה אמור להופיע.

gcloud storage ls gs://$GCS_BUCKET_NAME

הפלט הבא אמור להתקבל:

gs://$GCS_BUCKET_NAME/hello.txt

כדי לראות את התוכן של אובייקט בקטגוריה, אפשר להשתמש ב-gcloud storage cat.

gcloud storage cat gs://$GCS_BUCKET_NAME/hello.txt

התוכן של הקובץ צריך להיות גלוי:

Hello, GCS

ניקוי קובץ הבדיקה

הקטגוריה של Cloud Storage מוגדרת עכשיו. עכשיו אפשר למחוק את קובץ הבדיקה הזמני.

gcloud storage rm gs://$GCS_BUCKET_NAME/hello.txt

הפלט אמור לאשר את המחיקה:

Removing gs://$GCS_BUCKET_NAME/hello.txt...
/ [1 objects]
Operation completed over 1 objects.

4. ייצוא מ-Snowflake ל-GCS

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

במקום להשתמש בטבלאות הגולמיות והמנורמלות של TPC-H, תיצרו טבלה חדשה ומצטברת. הטבלה החדשה הזו תאחד נתונים מהטבלאות orders, customer ו-nation כדי ליצור תצוגה לא מנורמלת של סיכום סכומי המכירות ברמה הארצית. שלב הצבירה המוקדמת הזה הוא שיטה נפוצה בניתוח נתונים, כי הוא מכין את הנתונים לתרחיש שימוש ספציפי – בתרחיש הזה, לשימוש באפליקציה תפעולית.

איך מאפשרים ל-Snowflake לגשת ל-Google Cloud Storage

כדי לאפשר ל-Snowflake לכתוב נתונים בדלי GCS, צריך ליצור שני דברים: נפח חיצוני והרשאות נדרשות.

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

יצירת מסד נתונים

  1. בתפריט השמאלי, בקטע Horizon Catalog, מעבירים את העכבר מעל Catalog ולוחצים על Database Explorer.
  2. בדף מסדי נתונים, לוחצים על הלחצן + מסד נתונים בפינה השמאלית העליונה.
  3. נותנים שם למסד הנתונים החדש codelabs_retl_db

יצירת גיליון עבודה

כדי להריץ פקודות SQL מול מסד הנתונים, צריך להשתמש בגיליונות עבודה.

כדי ליצור גיליון עבודה:

  1. בתפריט השמאלי, בקטע עבודה עם נתונים, מעבירים את העכבר מעל פרויקטים ואז לוחצים על סביבות עבודה.
  2. בסרגל הצד My Workspaces (סביבות העבודה שלי), לוחצים על הלחצן + Add new (הוספת חדש) ובוחרים באפשרות SQL File (קובץ SQL).

יצירת נפח אחסון חיצוני

מריצים את הפקודה הבאה בגליון עבודה של Snowflake כדי ליצור את אמצעי האחסון.

CREATE EXTERNAL VOLUME codelabs_retl_ext_vol
STORAGE_LOCATIONS = 
(
  (
    NAME = 'codelabs_retl_ext_vol'
    STORAGE_PROVIDER = 'GCS'
    STORAGE_BASE_URL = 'gcs://<Your bucket name>/snowflake_extvol'
  )
);

קבלת חשבון השירות של Snowflake

‫DESC (describe) כדי לקבל את חשבון השירות הייחודי ש-Snowflake יצרה עבור הנפח החיצוני החדש.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;
  1. בחלונית התוצאות, מחפשים את מאפייני ה-JSON ואת הרשומה property_value שמכילה מחרוזת JSON שמתחילה ב-"NAME":"codelabs_retl_ext_vol"
  2. מחפשים את המאפיין STORAGE_GCP_SERVICE_ACCOUNT באובייקט ה-JSON ומעתיקים את הערך שלו (הוא ייראה כמו כתובת אימייל). זהו מזהה חשבון השירות שצריך לקבל גישה לדלי GCS.
  3. שומרים את חשבון השירות הזה במשתנה סביבה במופע של המעטפת לשימוש חוזר בהמשך
export GCP_SERVICE_ACCOUNT=<Your service account>

מתן הרשאות ל-Snowflake ב-GCS

עכשיו צריך לתת לחשבון השירות של Snowflake הרשאה לכתוב בקטגוריה של GCS.

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.objectAdmin"

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.legacyBucketReader"

אימות הגישה ב-Snowflake

חוזרים לגליון העבודה של Snowflake ומריצים את הפקודה הזו כדי לוודא ש-Snowflake יכול להתחבר עכשיו בהצלחה לקטגוריה של GCS.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

התוצאה צריכה להיות אובייקט JSON שמכיל את "success":true.

מידע נוסף על אמצעי אחסון חיצוניים ב-Snowflake זמין במסמכי העזרה הרשמיים.

ייצוא של נתוני הזמנות לדוגמה

עכשיו אפשר ליצור טבלת Iceberg ב-Snowflake. הפקודה הבאה מורה ל-Snowflake להריץ שאילתה ולאחסן את התוצאות ב-GCS בפורמט Iceberg. קבצי הנתונים יהיו בפורמט Parquet, והמטא-נתונים יהיו בפורמטים Avro ו-JSON. כל הקבצים האלה מאוחסנים במיקום שמוגדר על ידי codelabs_retl_ext_vol External Volume.

יצירת מסד נתונים

  1. בתפריט השמאלי, בקטע Horizon Catalog, מעבירים את העכבר מעל Catalog ולוחצים על Database Explorer.
  2. בדף מסדי נתונים, לוחצים על הלחצן + מסד נתונים בפינה השמאלית העליונה.
  3. נותנים שם למסד הנתונים החדש codelabs_retl_db
USE DATABASE codelabs_retl_db; 

CREATE ICEBERG TABLE REGIONAL_SALES_ICEBERG (
    NATION_NAME STRING,
    MARKET_SEGMENT STRING,
    ORDER_YEAR INTEGER,
    ORDER_PRIORITY STRING,
    TOTAL_ORDER_COUNT INTEGER,
    TOTAL_REVENUE NUMBER(24,2),
    UNIQUE_CUSTOMER_COUNT INTEGER
) 
EXTERNAL_VOLUME = 'codelabs_retl_ext_vol'
CATALOG = 'SNOWFLAKE'
BASE_LOCATION = 'regional_sales_iceberg'
AS (
    SELECT 
        n.n_name AS nation_name,
        c.c_mktsegment AS market_segment,
        YEAR(o.o_orderdate) AS order_year,
        o.o_orderpriority AS order_priority,
        COUNT(o.o_orderkey) AS total_order_count,
        ROUND(SUM(o.o_totalprice), 2) AS total_revenue,
        COUNT(DISTINCT c.c_custkey) AS unique_customer_count
    FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders AS o
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.customer AS c 
        ON o.o_custkey = c.c_custkey
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.nation AS n
        ON c.c_nationkey = n.n_nationkey
    GROUP BY 
        n.n_name, 
        c.c_mktsegment, 
        YEAR(o.o_orderdate), 
        o.o_orderpriority
);

מידע נוסף על יצירה וניהול של טבלאות Iceberg באמצעות Snowflake זמין בתיעוד הרשמי.

אימות הנתונים ב-GCP

עכשיו בודקים את דלי ה-GCS. הקבצים שנוצרו על ידי Snowflake אמורים להיות גלויים. זהו אישור לכך שהייצוא בוצע בהצלחה. המטא-נתונים של Iceberg נמצאים בתיקייה metadata והנתונים בפועל כקובצי Parquet בתיקייה data.

gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**"

השמות המדויקים של הקבצים משתנים, אבל המבנה צריך להיראות כך:

gs://$GCS_BUCKET_NAME/snowflake_extvol/
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/data/snow_cbsKIRmdDmo_wLg128fugxg_0_2_009.parquet
...
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/metadata/00001-62f831ff-6708-4494-94c5-c891b7ad447f.metadata.json
...

הנתונים הועתקו מ-Snowflake ל-Google Cloud Storage בפורמט Iceberg.

בזמן שיש לנו את הרשימה הזו, נשמור את הקובץ metadata.json כמשתנה סביבתי כי נצטרך אותו בהמשך.

export GCS_METADATA_JSON=$(gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**" | grep .metadata.json)

5. הגדרת טבלה חיצונית ב-BigQuery

עכשיו שטבלת Iceberg נמצאת ב-Google Cloud Storage, השלב הבא הוא להפוך אותה לנגישה ל-BigQuery. כדי לעשות את זה, צריך ליצור טבלה חיצונית ב-BigLake.

‫BigLake הוא מנוע אחסון שמאפשר ליצור טבלאות ב-BigQuery שקוראות נתונים ישירות ממקורות חיצוניים כמו Google Cloud Storage. בשיעור ה-Lab הזה, זו הטכנולוגיה העיקרית שמאפשרת ל-BigQuery להבין את טבלת Iceberg שיוצאה זה עתה בלי צורך להטמיע את הנתונים.

כדי שהשילוב הזה יפעל, צריך שני רכיבים:

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

הגדרת חיבור ל-Google Cloud Storage

קודם כל, תיווצר ההרשאה שתאפשר ל-BigQuery לגשת ל-GCS. הפקודה הזו יוצרת משאב חיבור ב-BigQuery.

bq mk \
  --connection \
  --project_id=$GCP_PROJECT \
  --location=$GCP_REGION \
  --connection_type=CLOUD_RESOURCE \
  codelabs-retl-connection

הצלחה תיראה בערך כך:

Connection 12345678.region.codelabs-retl-connection successfully created

מידע נוסף על קישורים למשאבי Cloud ב-BigQuery זמין במאמרי העזרה של Google Cloud.

אישור החיבור ל-BigQuery כדי לקרוא נתונים

לחיבור החדש ל-BigQuery יש חשבון שירות משלו שצריך לקבל הרשאה לקרוא נתונים ממאגר Google Cloud Storage.

1. קבלת חשבון השירות של החיבור

קודם כל, מאתרים את מזהה חשבון השירות מהחיבור שנוצר:

bq show \
  --location $GCP_REGION \
  --connection codelabs-retl-connection

בתוצאות תופיע טבלה של חיבורים תואמים.

מגדירים את serviceAccountId כמשתנה סביבה כדי להשתמש בו בהמשך.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. מתן הרשאות

מריצים את הפקודה הבאה כדי לתת לחשבון השירות הרשאה לצפייה בנתונים בדלי GCS.

gcloud storage buckets add-iam-policy-binding \
  gs://$GCS_BUCKET_NAME \
  --member serviceAccount:$GCP_BQ_SERVICE_ACCOUNT \
  --role roles/storage.objectViewer

יצירת טבלה חיצונית

עכשיו יוצרים את הטבלה החיצונית BigLake ב-BigQuery. הפקודה הזו לא מעבירה נתונים. הוא פשוט יוצר מצביע לנתונים הקיימים ב-GCS. תצטרכו את הנתיב לאחד מקובצי .metadata.json שנוצרו על ידי Snowflake.

bq mk --dataset --location=$GCP_REGION codelabs_retl

bq mk \
    --table \
    --location=$GCP_REGION \
--external_table_definition=ICEBERG=$GCS_METADATA_JSON@projects/$GCP_PROJECT/locations/$GCP_REGION/connections/codelabs-retl-connection \
    codelabs_retl.regional_sales

אימות הנתונים ב-BigQuery

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

bq query \
  --location=$GCP_REGION \
  --nouse_legacy_sql "SELECT * FROM \`$GCP_PROJECT.codelabs_retl.regional_sales\` LIMIT 10;"

6. ייבוא נתונים מ-BigQuery ל-Spanner: השלב האחרון

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

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

התהליך יהיה כזה:

  1. יוצרים סכימת טבלה במסד הנתונים של Spanner שתואמת למבנה הנתונים.
  2. מריצים שאילתת BigQuery EXPORT DATA כדי לטעון את הנתונים מטבלת BigLake ישירות לטבלת Spanner.

יצירת טבלת Spanner

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

gcloud spanner databases ddl update $SPANNER_DB \
  --instance=$SPANNER_INSTANCE \
  --ddl="$(cat <<EOF
CREATE TABLE regional_sales (
    nation_name STRING(MAX),
    market_segment STRING(MAX),
    order_year INT64,
    order_priority STRING(MAX),
    total_order_count INT64,
    total_revenue NUMERIC,
    unique_customer_count INT64
) PRIMARY KEY (nation_name, market_segment, order_year, order_priority);
EOF
)"

ייצוא נתונים מ-BigQuery

זה השלב האחרון. אחרי שהנתונים מוכנים בטבלת BigLake ב-BigQuery וטבלת היעד נוצרה ב-Spanner, העברת הנתונים עצמה פשוטה באופן מפתיע. תתבצע שאילתת SQL אחת ב-BigQuery: ‏ EXPORT DATA.

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

bq query --location=$GCP_REGION --use_legacy_sql=false <<EOF
EXPORT DATA OPTIONS (
uri="https://spanner.googleapis.com/projects/${GCP_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DB}",
  format='CLOUD_SPANNER',
  spanner_options="""{ 
      "table": "regional_sales", 
      "priority": "HIGH" 
      }"""
) AS 
SELECT * FROM \`${PROJECT_ID}.codelabs_retl.regional_sales\`
EOF

כשהשאילתה מסתיימת, בחלונית Results (תוצאות) אמור להופיע הכיתוב Update completed (העדכון הושלם).

7. אימות הנתונים ב-Spanner

מעולה! תהליך מלא של Reverse ETL נוצר והופעל בהצלחה. השלב האחרון הוא לוודא שהנתונים הגיעו ל-Spanner כמו שציפיתם.

gcloud spanner databases execute-sql \
  --instance=$SPANNER_INSTANCE \
  $SPANNER_DB \
  --sql='SELECT * FROM regional_sales LIMIT 10'

נתוני הדוגמה המיובאים מופיעים כמו שביקשתם:

nation_name  market_segment  order_year  order_priority   total_order_count  total_revenue  unique_customer_count
ALGERIA      AUTOMOBILE      1992        1-URGENT         375                59232423.66    298
ALGERIA      AUTOMOBILE      1992        2-HIGH           328                47371891.08    269
ALGERIA      AUTOMOBILE      1992        3-MEDIUM         346                52823195.87    262
ALGERIA      AUTOMOBILE      1992        4-NOT SPECIFIED  365                52935998.34    288
ALGERIA      AUTOMOBILE      1992        5-LOW            380                54920263.68    293
ALGERIA      AUTOMOBILE      1993        1-URGENT         394                63145618.78    312
ALGERIA      AUTOMOBILE      1993        2-HIGH           340                50737488.4     277
ALGERIA      AUTOMOBILE      1993        3-MEDIUM         383                55871057.46    298
ALGERIA      AUTOMOBILE      1993        4-NOT SPECIFIED  365                56424662.05    291
ALGERIA      AUTOMOBILE      1993        5-LOW            363                54673249.06    283

הצלחנו לגשר על הפער בין עולמות הנתונים האנליטיים והתפעוליים.

8. ניקוי תלונות

ניקוי Spanner

מחיקה של מסד הנתונים והמכונה של Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

ניקוי GCS

מחיקת דלי GCS שנוצר לאירוח הנתונים

gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

ניקוי ב-BigQuery

bq rm -r codelabs_retl
bq rm --connection --location=$GCP_REGION codelabs-retl-connection

ניקוי של Snowflake

מחיקת מסד הנתונים

  1. בתפריט הימני, בקטע קטלוג Horizon, מעבירים את העכבר מעל קטלוג ולוחצים על כלי לבדיקת מסדי נתונים.
  2. לוחצים על … משמאל למסד הנתונים CODELABS_RETL_DB כדי להרחיב את האפשרויות ובוחרים באפשרות מחיקה.
  3. בתיבת הדו-שיח הקופצת לאישור, בוחרים באפשרות מחיקת מסד נתונים.

מחיקת חוברות עבודה

  1. בתפריט השמאלי, בקטע עבודה עם נתונים, מעבירים את העכבר מעל פרויקטים ואז לוחצים על סביבות עבודה.
  2. בסרגל הצד My Workspace (סביבת העבודה שלי), מעבירים את העכבר מעל הקבצים השונים של סביבת העבודה שבהם השתמשתם בשיעור Lab הזה כדי להציג את האפשרויות הנוספות ... ולוחצים עליהן.
  3. בוחרים באפשרות מחיקה, ואז שוב באפשרות מחיקה בתיבת הדו-שיח לאישור שמופיעה.
  4. מבצעים את הפעולה הזו לכל קובצי ה-SQL Workspace שיצרתם לשיעור ה-Lab הזה.

מחיקת נפחי אחסון חיצוניים

  1. בתפריט השמאלי, בקטע קטלוג Horizon, מעבירים את העכבר מעל קטלוג ואז לוחצים על נתונים חיצוניים.
  2. לוחצים על הסמל 227b3e306c3d609d.png משמאל ל-CODELABS_RETL_EXT_VOL, בוחרים באפשרות הסרת עוצמת קול חיצונית ולוחצים שוב על הסרת עוצמת קול חיצונית בתיבת הדו-שיח לאישור.

9. מזל טוב

כל הכבוד, סיימתם את ה-Codelab.

מה נכלל

  • איך טוענים נתונים ל-Snowflake
  • איך יוצרים קטגוריה ב-GCS
  • איך מייצאים טבלת Snowflake ל-GCS בפורמט CSV
  • איך מגדירים מופע Spanner
  • איך טוענים טבלאות CSV ל-Spanner באמצעות Dataflow