نقل البيانات من Snowflake إلى Spanner باستخدام BigQuery

1. إنشاء مسار Reverse ETL من Snowflake إلى Spanner باستخدام Google Cloud Storage وBigQuery

مقدمة

في هذا الدرس التطبيقي حول الترميز، يتم إنشاء مسار استخراج البيانات وتحويلها وتحميلها العكسي من Snowflake إلى Spanner. في العادة، تنقل مسارات ETL (الاستخراج والتحويل والتحميل) البيانات من قواعد البيانات التشغيلية إلى مستودع بيانات، مثل Snowflake، لإجراء الإحصاءات. يعمل مسار Reverse 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 في "خدمة التخزين السحابي من Google" إلى جدول خارجي في BigQuery BigLake:
  • بدلاً من تحميل البيانات مباشرةً من "خدمة التخزين السحابي من Google" إلى Spanner، يتم استخدام BigQuery كوسيط قوي. ستنشئ جدولاً خارجيًا في BigLake في BigQuery يشير مباشرةً إلى ملف بيانات Iceberg الوصفية في GCS. لهذا الأسلوب عدة مزايا:
  • عدم تكرار البيانات: تقرأ BigQuery بنية الجدول من البيانات الوصفية وتطلب ملفات بيانات Parquet في مكانها بدون نقلها، ما يؤدي إلى توفير الكثير من الوقت وتكاليف التخزين.
  • الطلبات الموحّدة: تتيح تنفيذ طلبات SQL معقّدة على بيانات GCS كما لو كانت جدول BigQuery أصليًا.
  1. من BigQuery إلى Spanner:
  • الخطوة الأخيرة هي نقل البيانات من BigQuery إلى Spanner. سيتم تحقيق ذلك باستخدام ميزة فعّالة في BigQuery تُعرف باسم EXPORT DATA، وهي خطوة "استخراج البيانات وتحويلها وتحميلها بشكل عكسي".
  • الاستعداد التشغيلي: تم تصميم Spanner لأحمال العمل المتعلقة بالمعاملات، ما يوفّر اتساقًا قويًا وتوفّرًا عاليًا للتطبيقات. من خلال نقل البيانات إلى Spanner، تصبح متاحة للتطبيقات التي تواجه المستخدمين وواجهات برمجة التطبيقات والأنظمة التشغيلية الأخرى التي تتطلّب عمليات بحث سريعة.
  • قابلية التوسّع: يتيح هذا النمط الاستفادة من قدرة BigQuery التحليلية لمعالجة مجموعات البيانات الكبيرة ثم عرض النتائج بكفاءة من خلال البنية الأساسية القابلة للتوسّع عالميًا في Spanner.

الخدمات والمصطلحات

  • ‫Snowflake: هي منصة بيانات مستنِدة إلى السحابة الإلكترونية توفّر مستودع بيانات كخدمة.
  • ‫Spanner: قاعدة بيانات ارتباطية موزّعة عالميًا ومُدارة بالكامل
  • Google Cloud Storage: خدمة تخزين البيانات الثنائية الكبيرة من Google Cloud
  • BigQuery: مستودع بيانات مُدار بالكامل وبدون خادم للإحصاءات
  • ‫Iceberg: هو تنسيق جدول مفتوح محدّد من قِبل Apache يوفّر تجريدًا لتنسيقات ملفات البيانات الشائعة مفتوحة المصدر.
  • Parquet: هو تنسيق ملف بيانات ثنائي عمودي مفتوح المصدر من Apache.

أهداف الدورة التعليمية

  • كيفية تحميل البيانات إلى Snowflake
  • كيفية إنشاء حزمة GCS
  • كيفية تصدير جدول Snowflake إلى GCS بتنسيق Iceberg
  • كيفية إعداد مثيل Spanner
  • كيفية تحميل جداول BigLake الخارجية في BigQuery إلى Spanner

2. الإعداد والمتطلبات والقيود

المتطلبات الأساسية

  • حساب على Snowflake
  • يجب توفّر حساب على Google Cloud يتضمّن حجزًا من فئة Enterprise أو أعلى لتصدير البيانات من BigQuery إلى Spanner.
  • الوصول إلى "وحدة تحكّم Google Cloud" من خلال متصفّح ويب
  • وحدة طرفية لتنفيذ أوامر Google Cloud CLI
  • إذا كانت مؤسستك على Google Cloud مفعَّلة فيها سياسة iam.allowedPolicyMemberDomains، قد يحتاج المشرف إلى منح استثناء للسماح بحسابات الخدمة من نطاقات خارجية. سيتم تناول هذا الموضوع في خطوة لاحقة عند الاقتضاء.

القيود

من المهم أن تكون على دراية ببعض القيود وحالات عدم التوافق بين أنواع البيانات التي يمكن أن تنشأ في مسار النقل هذا.

من ندفة ثلج إلى جبل جليدي

تختلف أنواع بيانات الأعمدة بين 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.

Snowflake

في هذا الدرس العملي، يمكنك استخدام حساب حالي على Snowflake أو إعداد حساب فترة تجريبية مجانية.

أذونات "إدارة الهوية وإمكانية الوصول" في Google Cloud Platform

يجب أن يتضمّن حساب 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

إعداد خصائص قابلة لإعادة الاستخدام

ستحتاج إلى بعض القيم بشكل متكرّر خلال هذا الدرس التطبيقي. لتسهيل ذلك، سنضبط هذه القيم على متغيرات shell لاستخدامها لاحقًا.

  • GCP_REGION: المنطقة المحدّدة التي سيتم فيها تحديد موقع موارد GCP. يمكنك الاطّلاع على قائمة المناطق هنا.
  • GCP_PROJECT: رقم تعريف مشروع Google Cloud Platform المطلوب استخدامه.
  • 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. إذا قدّم المشرف رمزًا لاستخدامه، يمكن تخطّي هذه الخطوة.

يمكن إنشاء مشروع باستخدام واجهة سطر الأوامر على النحو التالي:

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. لإجراء ذلك، يجب أولاً إنشاء حزمة جديدة لاستخدامها كوجهة للملف. من نافذة "الوحدة الطرفية" على جهاز محلي، اتّبِع الخطوات التالية.

إنشاء الحزمة

استخدِم الأمر التالي لإنشاء حزمة تخزين في منطقة معيّنة (مثل 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

في هذا التمرين العملي، ستستخدم مجموعة بيانات TPC-H، وهي معيار قياس أداء متّبَع في المجال لأنظمة دعم اتخاذ القرار. يصمّم المخطط البياني بيئة نشاط تجاري واقعية تتضمّن عملاء وطلبات ومورّدين وأجزاء، ما يجعله مثاليًا لعرض سيناريو واقعي للتحليلات ونقل البيانات. تتوفّر مجموعة البيانات هذه تلقائيًا في جميع حسابات Snowflake.

بدلاً من استخدام جداول TPC-H الأولية والموحّدة، ستنشئ جدولاً جديدًا مجمّعًا. سينضم هذا الجدول الجديد إلى البيانات من الجداول orders وcustomer وnation لإنشاء طريقة عرض غير طبيعية وموجزة لإجمالي المبيعات على مستوى البلد. تُعدّ خطوة التجميع المُسبَق هذه من الممارسات الشائعة في الإحصاءات، لأنّها تُجهّز البيانات لحالة استخدام محدّدة، وهي في هذا السيناريو، للاستهلاك من خلال تطبيق تشغيلي.

السماح لخدمة Snowflake بالوصول إلى Google Cloud Storage

للسماح لـ Snowflake بكتابة البيانات في حزمة GCS، يجب إنشاء عنصرَين: وحدة تخزين خارجية والأذونات اللازمة.

  • وحدة التخزين الخارجية هي عنصر Snowflake يوفّر رابطًا آمنًا إلى موقع جغرافي محدّد في حزمة GCS. لا يخزّن هذا المكوّن البيانات بنفسه، بل يحتفظ بالإعدادات اللازمة لكي تصل Snowflake إلى مساحة التخزين السحابية.
  • لأسباب تتعلّق بالأمان، تكون حِزم مساحة التخزين على السحابة الإلكترونية خاصة بشكلٍ تلقائي. عند إنشاء وحدة تخزين خارجية، تنشئ Snowflake حساب خدمة مخصّصًا. يجب منح حساب الخدمة هذا أذونات القراءة والكتابة في الحزمة.

إنشاء قاعدة بيانات

  1. في القائمة الجانبية على يمين الصفحة، ضَع مؤشر الماوس على الفهرس ضمن فهرس Horizon، ثم انقر على مستكشف قاعدة البيانات.
  2. بعد الانتقال إلى صفحة قواعد البيانات، انقر على الزر + قاعدة بيانات في أعلى يسار الصفحة.
  3. تسمية قاعدة البيانات الجديدة codelabs_retl_db

إنشاء ورقة عمل

لتنفيذ أوامر SQL على قاعدة البيانات، يجب توفير أوراق عمل.

لإنشاء ورقة عمل، اتّبِع الخطوات التالية:

  1. في القائمة الجانبية على يمين الصفحة، ضَع مؤشر الماوس على المشاريع ضمن العمل باستخدام البيانات، ثم انقر على مساحات العمل.
  2. ضمن الشريط الجانبي مساحات العمل الخاصة بي، انقر على الزر + إضافة جديد واختَر ملف 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. تخزين حساب الخدمة هذا في متغيّر بيئة في مثيل shell لإعادة استخدامه لاحقًا
export GCP_SERVICE_ACCOUNT=<Your service account>

منح أذونات GCS إلى Snowflake

الآن، يجب منح حساب خدمة 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، ثم انقر على مستكشف قاعدة البيانات.
  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، يُرجى الرجوع إلى المستندات الرسمية.

التحقّق من البيانات في Google Cloud Platform

الآن، تحقَّق من حزمة 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. في هذا المختبر، هذه هي التكنولوجيا الأساسية التي تتيح لأداة 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. نفِّذ طلب بحث EXPORT DATA في BigQuery لتحميل البيانات من جدول 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

عند انتهاء طلب البحث، من المفترض أن تعرض لوحة "النتائج" الرسالة "اكتمل التعديل".

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. في الشريط الجانبي مساحة العمل الخاصة بي، مرِّر مؤشر الماوس فوق ملفات مساحة العمل المختلفة التي استخدمتها في هذا المختبر لعرض خيارات ... الإضافية وانقر عليها.
  3. انقر على حذف، ثم على حذف مرة أخرى في مربّع حوار التأكيد الذي يظهر.
  4. كرِّر هذه الخطوات لجميع ملفات مساحة عمل SQL التي أنشأتها لهذا الدرس التطبيقي.

حذف وحدات التخزين الخارجية

  1. في القائمة الجانبية على يمين الصفحة، ضَع مؤشر الماوس على الفهرس ضمن فهرس Horizon، ثم انقر على البيانات الخارجية.
  2. انقر على 227b3e306c3d609d.pngعلى يسار CODELABS_RETL_EXT_VOL، ثم اختَر إزالة وحدة تخزين خارجية، ثم انقر مرة أخرى على إزالة وحدة تخزين خارجية في مربّع حوار التأكيد.

9- تهانينا

تهانينا على إكمال تجربة البرمجة.

المواضيع التي تناولناها

  • كيفية تحميل البيانات إلى Snowflake
  • كيفية إنشاء حزمة GCS
  • كيفية تصدير جدول Snowflake إلى GCS بتنسيق CSV
  • كيفية إعداد مثيل Spanner
  • كيفية تحميل جداول CSV إلى Spanner باستخدام Dataflow