این صفحه به‌وسیله ‏Cloud Translation API‏ ترجمه شده است.

معکوس کردن ETL از Snowflake به Spanner با استفاده از BQ

۱. ساخت یک خط لوله ETL معکوس از Snowflake به Spanner با استفاده از Google Cloud Storage و BigQuery

مقدمه

در این آزمایشگاه کد، یک خط لوله ETL معکوس از Snowflake به Spanner ساخته شده است. به طور سنتی، خطوط لوله ETL (استخراج، تبدیل، بارگذاری) داده‌ها را از پایگاه‌های داده عملیاتی به یک انبار داده مانند Snowflake برای تجزیه و تحلیل منتقل می‌کنند. یک خط لوله ETL معکوس برعکس عمل می‌کند: داده‌های جمع‌آوری‌شده و پردازش‌شده را از انبار داده به سیستم‌های عملیاتی برمی‌گرداند، جایی که می‌توانند برنامه‌های کاربردی را پشتیبانی کنند، ویژگی‌های کاربرپسند را ارائه دهند یا برای تصمیم‌گیری در زمان واقعی استفاده شوند.

هدف، انتقال یک مجموعه داده تجمیع‌شده از جدول Snowflake Iceberg به Spanner است، که یک پایگاه داده رابطه‌ای توزیع‌شده جهانی و ایده‌آل برای برنامه‌های کاربردی با دسترسی‌پذیری بالا است.

برای دستیابی به این هدف، از فضای ذخیره‌سازی ابری گوگل (GCS) و BigQuery به عنوان مراحل میانی استفاده می‌شود. در اینجا خلاصه‌ای از جریان داده‌ها و دلیل این معماری آمده است:

انتقال فایل Snowflake به فضای ذخیره‌سازی ابری گوگل (GCS) با فرمت Iceberg:

اولین قدم، دریافت داده‌ها از Snowflake در قالبی باز و خوش‌تعریف است. جدول در قالب Apache Iceberg صادر می‌شود. این فرآیند، داده‌های زیربنایی را به صورت مجموعه‌ای از فایل‌های Parquet و فراداده‌های جدول (طرحواره، پارتیشن‌ها، مکان فایل‌ها) را به صورت فایل‌های JSON و Avro می‌نویسد. پیاده‌سازی این ساختار کامل جدول در GCS، داده‌ها را قابل حمل و در دسترس هر سیستمی که قالب Iceberg را درک می‌کند، قرار می‌دهد.

تبدیل جداول Iceberg در GCS به جدول خارجی BigQuery BigLake:

به جای بارگذاری مستقیم داده‌ها از GCS به Spanner، از BigQuery به عنوان یک واسطه قدرتمند استفاده می‌شود. شما یک جدول خارجی BigLake در BigQuery ایجاد خواهید کرد که مستقیماً به فایل فراداده Iceberg در GCS اشاره می‌کند. این رویکرد چندین مزیت دارد:
عدم تکرار داده‌ها: BigQuery ساختار جدول را از فراداده‌ها می‌خواند و فایل‌های داده Parquet را بدون نیاز به پردازش، جستجو می‌کند که این امر باعث صرفه‌جویی قابل توجه در زمان و هزینه‌های ذخیره‌سازی می‌شود.
کوئری‌های فدرال: این امکان را فراهم می‌کند تا کوئری‌های پیچیده SQL را روی داده‌های GCS اجرا کنید، گویی که یک جدول بومی BigQuery هستند.

BigQuery به آچار:

مرحله آخر، انتقال داده‌ها از BigQuery به Spanner است. شما با استفاده از یک ویژگی قدرتمند در BigQuery به نام EXPORT DATA query، که مرحله "ETL معکوس" است، به این هدف دست خواهید یافت.
آمادگی عملیاتی: Spanner برای بارهای کاری تراکنشی طراحی شده است و سازگاری قوی و دسترسی‌پذیری بالایی را برای برنامه‌ها فراهم می‌کند. با انتقال داده‌ها به Spanner، این داده‌ها برای برنامه‌های کاربردی کاربرپسند، APIها و سایر سیستم‌های عملیاتی که نیاز به جستجوی نقاط با تأخیر کم دارند، قابل دسترسی می‌شوند.
مقیاس‌پذیری: این الگو امکان بهره‌برداری از قدرت تحلیلی BigQuery را برای پردازش مجموعه داده‌های بزرگ و سپس ارائه نتایج به طور کارآمد از طریق زیرساخت مقیاس‌پذیر جهانی Spanner فراهم می‌کند.

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

Snowflake - یک پلتفرم داده ابری که یک انبار داده به عنوان سرویس ارائه می‌دهد.
Spanner - یک پایگاه داده رابطه‌ای کاملاً مدیریت‌شده و توزیع‌شده در سطح جهانی.
فضای ذخیره‌سازی ابری گوگل - ارائه فضای ذخیره‌سازی بلاب (blob storage) گوگل کلود.
BigQuery - یک انبار داده کاملاً مدیریت‌شده و بدون سرور برای تجزیه و تحلیل.
کوه یخ - یک قالب جدول باز که توسط آپاچی تعریف شده و نسبت به قالب‌های رایج فایل‌های داده متن‌باز، انتزاعی‌سازی ارائه می‌دهد.
پارکت - یک فرمت فایل داده دودویی ستونی متن‌باز توسط آپاچی.

آنچه یاد خواهید گرفت

نحوه بارگذاری داده‌ها در Snowflake
نحوه ایجاد یک سطل GCS
نحوه خروجی گرفتن از جدول Snowflake به GCS با فرمت Iceberg
نحوه راه‌اندازی یک نمونه Spanner
نحوه بارگذاری جداول خارجی BigLake در BigQuery به Spanner

۲. راه‌اندازی، الزامات و محدودیت‌ها

پیش‌نیازها

حساب کاربری اسنوفلیک
برای خروجی گرفتن از BigQuery به Spanner، یک حساب Google Cloud با رزرو BigQuery Enterprise-tier یا بالاتر مورد نیاز است.
دسترسی به کنسول ابری گوگل از طریق مرورگر وب
یک ترمینال برای اجرای دستورات Google Cloud CLI
اگر سیاست iam.allowedPolicyMemberDomains در سازمان Google Cloud شما فعال باشد، ممکن است لازم باشد مدیر سیستم برای مجاز کردن حساب‌های سرویس از دامنه‌های خارجی، استثنا قائل شود. این موضوع در مرحله بعدی، در صورت لزوم، پوشش داده خواهد شد.

محدودیت‌ها

مهم است که از محدودیت‌های خاص و ناسازگاری‌های نوع داده‌ای که ممکن است در این خط لوله ایجاد شوند، آگاه باشید.

از دانه برف تا کوه یخ

انواع داده‌های ستون بین Snowflake و Iceberg متفاوت است. اطلاعات مربوط به ترجمه بین آنها در مستندات Snowflake موجود است.

کوه یخ به BigQuery

هنگام استفاده از BigQuery برای پرس‌وجو از جداول Iceberg، محدودیت‌هایی وجود دارد. برای مشاهده لیست کامل، به مستندات BigQuery مراجعه کنید. توجه داشته باشید که انواع داده‌ای مانند BIGNUMERIC ، INTERVAL ، JSON ، RANGE یا GEOGRAPHY در حال حاضر پشتیبانی نمی‌شوند.

BigQuery به آچار

دستور EXPORT DATA از BigQuery به Spanner از همه انواع داده BigQuery پشتیبانی نمی‌کند. خروجی گرفتن از جدولی با انواع داده زیر منجر به خطا خواهد شد:

STRUCT
GEOGRAPHY
DATETIME
RANGE
TIME

علاوه بر این، اگر پروژه BigQuery از گویش GoogleSQL استفاده کند، انواع عددی زیر نیز برای صادرات به Spanner پشتیبانی نمی‌شوند:

BIGNUMERIC

برای فهرست کامل و به‌روز محدودیت‌ها، به مستندات رسمی مراجعه کنید: Exporting to Spanner Limitations .

دانه برف

برای این codelab، می‌توانید از یک حساب کاربری موجود در Snowflake استفاده کنید یا یک حساب آزمایشی رایگان راه‌اندازی کنید.

مجوزهای IAM پلتفرم ابری گوگل

برای اجرای تمام مراحل این codelab، حساب گوگل به مجوزهای زیر نیاز دارد.

حساب‌های خدماتی
`iam.serviceAccountKeys.create`	امکان ایجاد حساب‌های کاربری سرویس (Service Accounts) را فراهم می‌کند.
آچار
`spanner.instances.create`	امکان ایجاد یک نمونه جدید از Spanner را فراهم می‌کند.
`spanner.databases.create`	به اجرای دستورات DDL اجازه ایجاد می‌دهد.
`spanner.databases.updateDdl`	به اجرای دستورات DDL اجازه می‌دهد تا جداولی در پایگاه داده ایجاد کنند.
فضای ذخیره‌سازی ابری گوگل
`storage.buckets.create`	امکان ایجاد یک سطل GCS جدید برای ذخیره فایل‌های پارکت صادر شده را فراهم می‌کند.
`storage.objects.create`	امکان نوشتن فایل‌های پارکت صادر شده را در سطل GCS فراهم می‌کند.
`storage.objects.get`	به BigQuery اجازه می‌دهد فایل‌های Parquet را از سطل GCS بخواند.
`storage.objects.list`	به BigQuery اجازه می‌دهد تا فایل‌های Parquet را در سطل GCS فهرست کند.
جریان داده
`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

تنظیم ویژگی‌های قابل استفاده مجدد

چند مقدار وجود دارد که در طول این تمرین بارها مورد نیاز خواهند بود. برای آسان‌تر کردن این کار، این مقادیر را به متغیرهای پوسته تنظیم می‌کنیم تا بعداً مورد استفاده قرار گیرند.

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 است. اگر مدیر پروژه‌ای را برای استفاده ارائه کرده باشد، می‌توان از این مرحله صرف نظر کرد.

یک پروژه می‌تواند با استفاده از CLI به صورت زیر ایجاد شود:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

درباره ایجاد و مدیریت پروژه‌ها اینجا بیشتر بیاموزید.

تنظیم آچار

برای شروع استفاده از 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

۳. یک مخزن ذخیره‌سازی ابری گوگل ایجاد کنید

از فضای ذخیره‌سازی ابری گوگل (GCS) برای ذخیره فایل‌های داده پارکت و فراداده‌های 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

فایل تست را پاک کنید

اکنون فضای ذخیره‌سازی ابری راه‌اندازی شده است. اکنون می‌توانید فایل آزمایشی موقت را حذف کنید.

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

خروجی باید حذف را تأیید کند:

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

۴. خروجی گرفتن از Snowflake به GCS

برای این آزمایش، شما از مجموعه داده TPC-H استفاده خواهید کرد که یک معیار استاندارد صنعتی برای سیستم‌های پشتیبانی تصمیم‌گیری است. طرحواره آن یک محیط تجاری واقع‌گرایانه با مشتریان، سفارشات، تأمین‌کنندگان و قطعات را مدل‌سازی می‌کند و آن را برای نشان دادن یک سناریوی تجزیه و تحلیل و انتقال داده در دنیای واقعی ایده‌آل می‌سازد. این مجموعه داده به طور پیش‌فرض در همه حساب‌های Snowflake موجود است.

به جای استفاده از جداول خام و نرمال‌شده‌ی TPC-H، یک جدول جدید و تجمیع‌شده ایجاد خواهید کرد. این جدول جدید، داده‌های جداول orders ، customer و nation را به هم متصل می‌کند تا یک نمای غیر نرمال‌شده و خلاصه‌شده از مجموع فروش ملی ایجاد کند. این مرحله‌ی پیش‌تجمیع، یک روش رایج در تجزیه و تحلیل است، زیرا داده‌ها را برای یک مورد استفاده‌ی خاص - در این سناریو، برای مصرف توسط یک برنامه‌ی عملیاتی - آماده می‌کند.

به Snowflake اجازه دهید به فضای ذخیره‌سازی ابری گوگل دسترسی داشته باشد

برای اینکه Snowflake بتواند داده‌ها را در سطل GCS بنویسد، دو چیز باید ایجاد شود: یک درایو خارجی و مجوزهای لازم.

یک فضای خارجی (External Volume) یک شیء از Snowflake است که یک لینک امن به یک مکان خاص در یک سطل GCS فراهم می‌کند. این فضا خود داده‌ها را ذخیره نمی‌کند، بلکه پیکربندی مورد نیاز برای دسترسی Snowflake به فضای ذخیره‌سازی ابری را در خود جای داده است.
برای امنیت، مخازن ذخیره‌سازی ابری به طور پیش‌فرض خصوصی هستند. وقتی یک Volume خارجی ایجاد می‌شود، Snowflake یک حساب سرویس اختصاصی ایجاد می‌کند. این حساب سرویس باید مجوزهای خواندن و نوشتن در مخزن را داشته باشد.

ایجاد پایگاه داده

در منوی سمت چپ، در زیر Horizon Catalog ، نشانگر ماوس را روی Catalog قرار دهید، سپس روی Database Explorer کلیک کنید.
وقتی در صفحه پایگاه‌های داده هستید، روی دکمه + پایگاه داده در بالا سمت راست کلیک کنید.
نام پایگاه داده جدید را codelabs_retl_db قرار دهید.

ایجاد یک برگه کاری

برای اجرای دستورات sql روی پایگاه داده، به برگه‌های کاری (worksheets) نیاز است.

برای ایجاد یک برگه کار:

در منوی سمت چپ، در قسمت «کار با داده‌ها» ، نشانگر ماوس را روی «پروژه‌ها» نگه دارید، سپس روی «فضاهای کاری» کلیک کنید.
در نوار کناری My Workspaces ، روی دکمه + Add new کلیک کنید و SQL File را انتخاب کنید.

ایجاد یک مجلد خارجی

برای ایجاد حجم، دستور زیر را در یک برگه 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 (توصیف) درایو خارجی تازه ایجاد شده برای دریافت حساب سرویس منحصر به فردی که Snowflake برای آن ایجاد کرده است.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

در پنجره نتایج، به دنبال ویژگی‌های json بگردید و ورودی property_value را که حاوی یک رشته JSON است که با "NAME":"codelabs_retl_ext_vol"
ویژگی STORAGE_GCP_SERVICE_ACCOUNT را در شیء json پیدا کنید و مقدار آن را کپی کنید (شبیه یک آدرس ایمیل خواهد بود). این شناسه حساب سرویس است که نیاز به دسترسی به سطل GCS دارد.
این حساب سرویس را در یک متغیر محیطی در نمونه پوسته خود برای استفاده مجدد در آینده ذخیره کنید.

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 می‌گوید که یک کوئری اجرا کند و نتایج را با استفاده از فرمت Iceberg در GCS ذخیره کند. فایل‌های داده Parquet و متادیتا Avro و JSON خواهند بود که همگی در مکانی که توسط codelabs_retl_ext_vol External Volume تعریف شده است، ذخیره می‌شوند.

ایجاد پایگاه داده

در منوی سمت چپ، در زیر Horizon Catalog ، نشانگر ماوس را روی Catalog قرار دهید، سپس روی Database Explorer کلیک کنید.
وقتی در صفحه پایگاه‌های داده هستید، روی دکمه + پایگاه داده در بالا سمت راست کلیک کنید.
نام پایگاه داده جدید را 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 bucket را بررسی کنید. فایل‌هایی که 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 کپی شده و با فرمت Iceberg به Google Cloud Storage منتقل شده‌اند.

حالا که این لیست را داریم، بیایید فایل metadata.json را در یک متغیر محیطی ذخیره کنیم، چون بعداً به آن نیاز خواهیم داشت.

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

۵. پیکربندی یک جدول خارجی BigQuery

حالا که جدول Iceberg در فضای ذخیره‌سازی ابری گوگل قرار دارد، قدم بعدی این است که آن را برای BigQuery قابل دسترسی کنیم. این کار را می‌توان با ایجاد یک جدول خارجی BigLake انجام داد.

BigLake یک موتور ذخیره‌سازی است که امکان ایجاد جداول در BigQuery را فراهم می‌کند که داده‌ها را مستقیماً از منابع خارجی مانند Google Cloud Storage می‌خوانند. برای این آزمایشگاه، این فناوری کلیدی است که BigQuery را قادر می‌سازد جدول Iceberg را که به تازگی صادر شده است، بدون نیاز به دریافت داده‌ها، درک کند.

برای انجام این کار، دو جزء مورد نیاز است:

اتصال منابع ابری: این یک پیوند امن بین BigQuery و GCS است. این اتصال از یک حساب کاربری سرویس ویژه برای مدیریت احراز هویت استفاده می‌کند و تضمین می‌کند که BigQuery مجوزهای لازم برای خواندن فایل‌ها از سطل GCS را دارد.
تعریف جدول خارجی: این به BigQuery می‌گوید که فایل متادیتای جدول Iceberg را در GCS کجا پیدا کند و چگونه باید تفسیر شود.

پیکربندی اتصال به فضای ذخیره‌سازی ابری گوگل

ابتدا، اتصالی که به 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

اطلاعات بیشتر در مورد اتصالات منابع ابری در BigQuery در مستندات Google Cloud موجود است.

اتصال BigQuery را برای خواندن داده‌ها مجاز کنید

اتصال جدید BigQuery حساب کاربری سرویس مخصوص به خود را دارد که برای خواندن داده‌ها از مخزن ذخیره‌سازی ابری گوگل به مجوز نیاز دارد.

۱. حساب کاربری سرویس اتصال را دریافت کنید

ابتدا، شناسه حساب سرویس را از اتصالی که ایجاد کرده‌اید دریافت کنید:

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

نتایج، جدولی از اتصالات منطبق را نشان می‌دهد.

بیایید serviceAccountId را روی یک متغیر محیطی تنظیم کنیم تا بعداً از آن استفاده کنیم.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

۲. اعطای مجوزها

با اجرای دستور زیر، به حساب سرویس اجازه دهید تا داده‌های موجود در سطل 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;"

۶. وارد کردن داده‌ها از BigQuery به Spanner: مرحله آخر

به آخرین و مهم‌ترین بخش خط لوله رسیده‌ایم: انتقال داده‌ها از جدول BigLake به Spanner. این مرحله "ETL معکوس" است، که در آن داده‌ها، پس از پردازش و تنظیم در انبار داده، برای استفاده توسط برنامه‌ها در یک سیستم عملیاتی بارگذاری می‌شوند.

Spanner یک پایگاه داده رابطه‌ای کاملاً مدیریت‌شده و توزیع‌شده در سطح جهانی است. این پایگاه داده، ثبات تراکنشی یک پایگاه داده رابطه‌ای سنتی را ارائه می‌دهد، اما با مقیاس‌پذیری افقی یک پایگاه داده NoSQL. این ویژگی، آن را به انتخابی ایده‌آل برای ساخت برنامه‌های کاربردی مقیاس‌پذیر و با دسترسی بالا تبدیل می‌کند.

این فرآیند به شرح زیر خواهد بود:

یک طرح جدول در پایگاه داده Spanner ایجاد کنید که با ساختار داده‌ها مطابقت داشته باشد.
یک کوئری BigQuery EXPORT DATA اجرا کنید تا داده‌ها را از جدول BigLake مستقیماً در جدول 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

این مرحله نهایی است. با آماده شدن داده‌های منبع در جدول BigQuery BigLake و ایجاد جدول مقصد در 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

وقتی جستجو تمام شد، در قسمت نتایج باید عبارت «به‌روزرسانی کامل شد» نمایش داده شود.

۷. داده‌ها را در Spanner تأیید کنید

تبریک! یک خط لوله 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

شکاف بین دنیای داده‌های تحلیلی و عملیاتی با موفقیت از بین رفته است.

۸. تمیز کردن

آچار تمیز کننده

پایگاه داده Spanner و نمونه آن را حذف کنید

gcloud spanner instances delete $SPANNER_INSTANCE

GCS را تمیز کنید

سطل GCS ایجاد شده برای میزبانی داده‌ها را حذف کنید

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

پاکسازی بیگ‌کوئری

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

برف ریزه را تمیز کنید

پایگاه داده را رها کنید

در منوی سمت چپ، زیر Horizon Catalog ، نشانگر ماوس را روی Catalog قرار دهید، سپس روی Database Explorer کلیک کنید.
برای باز کردن گزینه‌ها، روی ... در سمت راست پایگاه داده CODELABS_RETL_DB کلیک کنید و Drop را انتخاب کنید.
در پنجره تأیید که ظاهر می‌شود، حذف پایگاه داده را انتخاب کنید.

حذف کتاب‌های کاری

در منوی سمت چپ، در قسمت «کار با داده‌ها» ، نشانگر ماوس را روی «پروژه‌ها» نگه دارید، سپس روی «فضاهای کاری» کلیک کنید.
در نوار کناری «فضای کاری من» ، نشانگر ماوس را روی فایل‌های فضای کاری مختلفی که برای این آزمایشگاه استفاده کرده‌اید، نگه دارید تا گزینه‌های اضافی نمایش داده شوند و روی آن کلیک کنید.
گزینه حذف را انتخاب کنید و سپس در پنجره تأیید که ظاهر می‌شود، دوباره حذف را انتخاب کنید.
این کار را برای تمام فایل‌های فضای کاری sql که برای این تمرین ایجاد کرده‌اید، انجام دهید.

حذف حجم‌های خارجی

در منوی سمت چپ، در زیر Horizon Catalog ، نشانگر ماوس را روی Catalog قرار دهید، سپس روی External Data کلیک کنید.
کلیک کنید در سمت راست CODELABS_RETL_EXT_VOL ، و گزینه‌ی Drop external volume را انتخاب کنید، و سپس در پنجره‌ی تأیید، دوباره گزینه‌ی Drop external volume را انتخاب کنید.

۹. تبریک

تبریک می‌گویم که آزمایشگاه کد را تمام کردی.

آنچه ما پوشش داده‌ایم

نحوه بارگذاری داده‌ها در Snowflake
نحوه ایجاد یک سطل GCS
نحوه خروجی گرفتن از جدول Snowflake به GCS با فرمت CSV
نحوه راه‌اندازی یک نمونه Spanner
نحوه بارگذاری جداول CSV در Spanner با Dataflow