BQ का इस्तेमाल करके, Snowflake से Spanner में रिवर्स ईटीएल

1. Google Cloud Storage और BigQuery का इस्तेमाल करके, Snowflake से Spanner तक रिवर्स ईटीएल पाइपलाइन बनाना

परिचय

इस कोडलैब में, Snowflake से Spanner तक रिवर्स ईटीएल पाइपलाइन बनाई गई है. आम तौर पर, ईटीएल (डेटा निकालना, ट्रांसफ़ॉर्म करना, और लोड करना) पाइपलाइन, ऑपरेशनल डेटाबेस से डेटा को Snowflake जैसे डेटा वेयरहाउस में ले जाती हैं, ताकि उसका विश्लेषण किया जा सके. रिवर्स ईटीएल पाइपलाइन, ईटीएल पाइपलाइन के उलट काम करती है. यह प्रोसेस किए गए डेटा को डेटा वेयरहाउस से वापस ऑपरेशनल सिस्टम में ले जाती है. इससे ऐप्लिकेशन को बेहतर बनाने, उपयोगकर्ताओं को सुविधाएं देने या रीयल-टाइम में फ़ैसले लेने में मदद मिलती है.

इसका मकसद, एग्रीगेट किए गए डेटासेट को Snowflake Iceberg टेबल से Spanner में ले जाना है. Spanner, दुनिया भर में उपलब्ध एक रिलेशनल डेटाबेस है. यह ज़्यादा उपलब्धता वाले ऐप्लिकेशन के लिए सबसे सही है.

इसके लिए, Google Cloud Storage (GCS) और BigQuery का इस्तेमाल इंटरमीडिएट चरणों के तौर पर किया जाता है. डेटा फ़्लो और इस आर्किटेक्चर के पीछे की वजह के बारे में यहां बताया गया है:

  1. Snowflake से Google Cloud Storage (GCS) में Iceberg फ़ॉर्मैट में डेटा ट्रांसफ़र करना:
  • पहला चरण, Snowflake से डेटा को ओपन और अच्छी तरह से परिभाषित फ़ॉर्मैट में निकालना है. टेबल को Apache Iceberg फ़ॉर्मैट में एक्सपोर्ट किया जाता है. इस प्रोसेस में, डेटा को Parquet फ़ाइलों के सेट के तौर पर लिखा जाता है. साथ ही, टेबल के मेटाडेटा (स्कीमा, पार्टिशन, फ़ाइल की जगह) को JSON और Avro फ़ाइलों के तौर पर लिखा जाता है. इस पूरी टेबल स्ट्रक्चर को GCS में स्टेज करने से, डेटा को पोर्टेबल बनाया जा सकता है. साथ ही, इसे ऐसे किसी भी सिस्टम से ऐक्सेस किया जा सकता है जो Iceberg फ़ॉर्मैट को समझता है.
  1. GCS में मौजूद Iceberg टेबल को BigQuery BigLake की बाहरी टेबल में बदलना:
  • GCS से सीधे Spanner में डेटा लोड करने के बजाय, BigQuery का इस्तेमाल एक इंटरमीडियरी के तौर पर किया जाता है. BigQuery में एक BigLake एक्सटर्नल टेबल बनाई जाएगी. यह सीधे तौर पर GCS में मौजूद Iceberg मेटाडेटा फ़ाइल की ओर ले जाती है. इस तरीके के कई फ़ायदे हैं:
  • डेटा डुप्लीकेट नहीं होता: BigQuery, मेटाडेटा से टेबल स्ट्रक्चर को पढ़ता है. साथ ही, Parquet डेटा फ़ाइलों को इन प्लेस क्वेरी करता है. इसके लिए, उन्हें इनजेस्ट नहीं करता. इससे काफ़ी समय और स्टोरेज का खर्च बचता है.
  • फ़ेडरेटेड क्वेरी: इसकी मदद से, GCS डेटा पर SQL की जटिल क्वेरी चलाई जा सकती हैं. ऐसा लगता है कि यह BigQuery की नेटिव टेबल है.
  1. BigQuery से Spanner:
  • आखिरी चरण में, BigQuery से Spanner में डेटा ट्रांसफ़र किया जाता है. इसके लिए, आपको BigQuery की एक बेहतरीन सुविधा का इस्तेमाल करना होगा. इसे EXPORT DATA क्वेरी कहा जाता है. यह "रिवर्स ईटीएल" चरण है.
  • ऑपरेशनल रेडीनेस: Spanner को लेन-देन से जुड़े वर्कलोड के लिए डिज़ाइन किया गया है. यह ऐप्लिकेशन के लिए, डेटा में एकरूपता और उपलब्धता बनाए रखता है. डेटा को Spanner में ट्रांसफ़र करने से, इसे उपयोगकर्ता के लिए उपलब्ध ऐप्लिकेशन, एपीआई, और अन्य ऑपरेशनल सिस्टम के लिए ऐक्सेस किया जा सकता है. इन सिस्टम को कम समय में पॉइंट लुकअप की ज़रूरत होती है.
  • स्केलेबिलिटी: इस पैटर्न की मदद से, बड़े डेटासेट को प्रोसेस करने के लिए BigQuery की विश्लेषण क्षमता का फ़ायदा उठाया जा सकता है. इसके बाद, Spanner के ग्लोबल स्तर पर स्केलेबल इन्फ़्रास्ट्रक्चर की मदद से, नतीजों को असरदार तरीके से दिखाया जा सकता है.

सेवाएं और शब्दावली

  • Snowflake - यह क्लाउड डेटा प्लैटफ़ॉर्म है, जो डेटा वेयरहाउस-एज़-ए-सर्विस उपलब्ध कराता है.
  • Spanner - यह पूरी तरह से मैनेज किया जाने वाला, दुनिया भर में उपलब्ध रिलेशनल डेटाबेस है.
  • Google Cloud Storage - Google Cloud की ओर से उपलब्ध कराया जाने वाला, बड़े डेटा को सेव करने का विकल्प.
  • BigQuery - यह बिना सर्वर वाला डेटा वेयरहाउस है, जिसे Google पूरी तरह से मैनेज करता है. इसका इस्तेमाल डेटा के विश्लेषण के लिए किया जाता है.
  • Iceberg - यह Apache की ओर से तय किया गया ओपन टेबल फ़ॉर्मैट है. यह ओपन-सोर्स डेटा फ़ाइल फ़ॉर्मैट के लिए ऐब्स्ट्रैक्शन उपलब्ध कराता है.
  • Parquet - यह Apache का ओपन-सोर्स कॉलम वाला बाइनरी डेटा फ़ाइल फ़ॉर्मैट है.

आपको क्या सीखने को मिलेगा

  • Snowflake में डेटा लोड करने का तरीका
  • GCS बकेट बनाने का तरीका
  • Snowflake टेबल को Iceberg फ़ॉर्मैट में GCS में एक्सपोर्ट करने का तरीका
  • स्पैनर इंस्टेंस सेट अप करने का तरीका
  • BigQuery में मौजूद BigLake की बाहरी टेबल को Spanner में लोड करने का तरीका

2. सेटअप, ज़रूरी शर्तें, और सीमाएं

ज़रूरी शर्तें

  • Snowflake खाता
  • BigQuery से Spanner में डेटा एक्सपोर्ट करने के लिए, BigQuery के Enterprise-tier या उससे ऊपर के रिज़र्वेशन वाला Google Cloud खाता होना ज़रूरी है.
  • वेब ब्राउज़र से Google Cloud Console को ऐक्सेस करने की सुविधा
  • Google Cloud CLI कमांड चलाने के लिए टर्मिनल
  • अगर आपके Google Cloud संगठन में iam.allowedPolicyMemberDomains नीति चालू है, तो एडमिन को बाहरी डोमेन के सेवा खातों को अनुमति देने के लिए, अपवाद की अनुमति देनी पड़ सकती है. यह जानकारी, बाद के चरण में दी जाएगी.

सीमाएं

इस पाइपलाइन में कुछ सीमाएं और डेटा टाइप से जुड़ी समस्याएं आ सकती हैं. इनके बारे में आपको पता होना चाहिए.

Snowflake से Iceberg पर माइग्रेट करना

Snowflake और Iceberg के कॉलम के डेटा टाइप अलग-अलग होते हैं. इनके बीच अनुवाद करने के बारे में जानकारी, Snowflake के दस्तावेज़ में उपलब्ध है.

Iceberg से BigQuery में डेटा माइग्रेट करना

Iceberg टेबल से क्वेरी करने के लिए BigQuery का इस्तेमाल करते समय, कुछ सीमाएं होती हैं. पूरी सूची देखने के लिए, BigQuery दस्तावेज़ देखें. ध्यान दें कि फ़िलहाल, BIGNUMERIC, INTERVAL, JSON, RANGE या GEOGRAPHY जैसे टाइप काम नहीं करते.

BigQuery से Spanner

BigQuery से Spanner में डेटा ट्रांसफ़र करने के लिए इस्तेमाल की जाने वाली EXPORT DATA कमांड, BigQuery के सभी डेटा टाइप के साथ काम नहीं करती. इस तरह की टेबल एक्सपोर्ट करने पर गड़बड़ी होगी:

  • STRUCT
  • GEOGRAPHY
  • DATETIME
  • RANGE
  • TIME

इसके अलावा, अगर BigQuery प्रोजेक्ट GoogleSQL डायलेक्ट का इस्तेमाल कर रहा है, तो Spanner में एक्सपोर्ट करने के लिए, यहां दिए गए संख्या वाले टाइप भी काम नहीं करते:

  • BIGNUMERIC

सीमाओं की पूरी और अप-टू-डेट सूची देखने के लिए, आधिकारिक दस्तावेज़ देखें: Spanner में एक्सपोर्ट करने से जुड़ी सीमाएं.

Snowflake

इस कोडलैब के लिए, किसी मौजूदा Snowflake खाते का इस्तेमाल किया जा सकता है. इसके अलावा, मुफ़्त में आज़माने की सुविधा वाला खाता भी सेट अप किया जा सकता है.

Google Cloud Platform IAM की अनुमतियां

इस कोडलैब में दिए गए सभी चरणों को पूरा करने के लिए, Google खाते के पास ये अनुमतियां होनी चाहिए.

सेवा खाते

iam.serviceAccountKeys.create

इससे सेवा खाते बनाए जा सकते हैं.

Spanner

spanner.instances.create

इससे नया Spanner इंस्टेंस बनाया जा सकता है.

spanner.databases.create

DDL स्टेटमेंट चलाने की अनुमति देता है, ताकि

spanner.databases.updateDdl

इसकी मदद से, डेटाबेस में टेबल बनाने के लिए DDL स्टेटमेंट चलाए जा सकते हैं.

Google Cloud Storage

storage.buckets.create

इस विकल्प की मदद से, एक्सपोर्ट की गई Parquet फ़ाइलों को सेव करने के लिए, नया GCS बकेट बनाया जा सकता है.

storage.objects.create

इस भूमिका की मदद से, एक्सपोर्ट की गई Parquet फ़ाइलों को GCS बकेट में लिखा जा सकता है.

storage.objects.get

इससे BigQuery को GCS बकेट से Parquet फ़ाइलें पढ़ने की अनुमति मिलती है.

storage.objects.list

इससे BigQuery को GCS बकेट में मौजूद Parquet फ़ाइलों की सूची बनाने की अनुमति मिलती है.

डेटाफ़्लो

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 प्रोजेक्ट

प्रोजेक्ट, 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 बकेट बनाना

Snowflake से जनरेट की गई Parquet डेटा फ़ाइलों और Iceberg मेटाडेटा को सेव करने के लिए, Google Cloud Storage (GCS) का इस्तेमाल किया जाएगा. इसके लिए, फ़ाइल डेस्टिनेशन के तौर पर इस्तेमाल करने के लिए, पहले एक नया बकेट बनाना होगा. लोकल मशीन पर मौजूद 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 में एक्सपोर्ट करना

इस लैब के लिए, TPC-H डेटासेट का इस्तेमाल किया जाएगा. यह फ़ैसले लेने में मदद करने वाले सिस्टम के लिए, इंडस्ट्री स्टैंडर्ड बेंचमार्क है. इसका स्कीमा, ग्राहकों, ऑर्डर, सप्लायर, और पार्ट्स के साथ-साथ कारोबार के असल माहौल को मॉडल करता है. इसलिए, यह असल दुनिया के आंकड़ों और डेटा मूवमेंट के उदाहरण को दिखाने के लिए सबसे सही है. यह डेटासेट, सभी Snowflake खातों में डिफ़ॉल्ट रूप से उपलब्ध होता है.

आपको रॉ और सामान्य की गई टीपीसी-एच टेबल का इस्तेमाल करने के बजाय, एग्रीगेट की गई नई टेबल बनानी होगी. यह नई टेबल, orders, customer, और nation टेबल से डेटा को एक साथ जोड़ देगी. इससे, देश के हिसाब से कुल बिक्री का डीनॉर्मलाइज़ किया गया खास जानकारी वाला व्यू तैयार होगा. प्री-एग्रीगेशन, आंकड़ों के विश्लेषण में आम तौर पर इस्तेमाल किया जाता है. इससे डेटा को किसी खास इस्तेमाल के लिए तैयार किया जाता है. इस उदाहरण में, डेटा को ऑपरेशनल ऐप्लिकेशन के इस्तेमाल के लिए तैयार किया जाता है.

Snowflake को Google Cloud Storage का ऐक्सेस देना

Snowflake को GCS बकेट में डेटा लिखने की अनुमति देने के लिए, दो चीज़ें बनानी होंगी: एक External Volume और ज़रूरी अनुमतियां.

  • External Volume, Snowflake ऑब्जेक्ट होता है. यह GCS बकेट में किसी खास जगह के लिए सुरक्षित लिंक उपलब्ध कराता है. यह खुद डेटा सेव नहीं करता है. हालांकि, इसमें Snowflake को क्लाउड स्टोरेज ऐक्सेस करने के लिए ज़रूरी कॉन्फ़िगरेशन होता है.
  • सुरक्षा के लिए, Cloud Storage बकेट डिफ़ॉल्ट रूप से निजी होते हैं. External Volume बनाने पर, Snowflake एक खास Service Account जनरेट करता है. इस सेवा खाते को बकेट से पढ़ने और उसमें बदलाव करने की अनुमतियां दी जानी चाहिए.

डेटाबेस बनाना

  1. बाईं ओर मौजूद मेन्यू में, Horizon Catalog में जाकर, Catalog पर कर्सर घुमाएं. इसके बाद, Database Explorer पर क्लिक करें
  2. डेटाबेस पेज पर जाकर, सबसे ऊपर दाईं ओर मौजूद + डेटाबेस बटन पर क्लिक करें.
  3. नई db codelabs_retl_db को नाम दें

वर्कशीट बनाना

डेटाबेस के ख़िलाफ़ SQL कमांड चलाने के लिए, वर्कशीट की ज़रूरत होगी.

वर्कशीट बनाने के लिए:

  1. बाईं ओर मौजूद मेन्यू में, डेटा के साथ काम करें में जाकर, प्रोजेक्ट पर कर्सर घुमाएं. इसके बाद, वर्कस्पेस पर क्लिक करें
  2. मेरे फ़ाइल फ़ोल्डर साइड बार में, + नया जोड़ें बटन पर क्लिक करें और एसक्यूएल फ़ाइल चुनें

कोई बाहरी वॉल्यूम बनाना

वॉल्यूम बनाने के लिए, 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 एंट्री ढूंढें, जिसमें "NAME":"codelabs_retl_ext_vol" से शुरू होने वाली JSON स्ट्रिंग मौजूद हो
  2. json ऑब्जेक्ट में मौजूद STORAGE_GCP_SERVICE_ACCOUNT प्रॉपर्टी ढूंढें और उसकी वैल्यू कॉपी करें. यह वैल्यू, ईमेल पते की तरह दिखेगी. यह सेवा खाते का आइडेंटिफ़ायर है. इसे 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');

नतीजा, "success":true वाला JSON ऑब्जेक्ट होना चाहिए.

Snowflake में बाहरी वॉल्यूम के बारे में ज़्यादा जानने के लिए, आधिकारिक दस्तावेज़ पढ़ें.

सैंपल ऑर्डर का डेटा एक्सपोर्ट करना

अब Snowflake में आइसबर्ग टेबल बनाई जा सकती है. यहां दिए गए निर्देश से Snowflake को क्वेरी चलाने और Iceberg फ़ॉर्मैट का इस्तेमाल करके, नतीजों को GCS में सेव करने के लिए कहा गया है. डेटा फ़ाइलें Parquet फ़ॉर्मैट में होंगी. साथ ही, मेटाडेटा Avro और JSON फ़ॉर्मैट में होगा. यह सब codelabs_retl_ext_vol के बाहरी वॉल्यूम में तय की गई जगह पर सेव होगा.

डेटाबेस बनाना

  1. बाईं ओर मौजूद मेन्यू में, Horizon Catalog में जाकर, Catalog पर कर्सर घुमाएं. इसके बाद, Database Explorer पर क्लिक करें
  2. डेटाबेस पेज पर जाकर, सबसे ऊपर दाईं ओर मौजूद + डेटाबेस बटन पर क्लिक करें.
  3. नई db 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
);

Snowflake का इस्तेमाल करके Iceberg टेबल बनाने और मैनेज करने के बारे में ज़्यादा जानने के लिए, आधिकारिक दस्तावेज़ पढ़ें.

GCP में डेटा की पुष्टि करना

अब GCS बकेट देखें. Snowflake की बनाई गई फ़ाइलें दिखनी चाहिए. इससे पुष्टि होती है कि एक्सपोर्ट हो गया है. आइसबर्ग मेटाडेटा, metadata फ़ोल्डर में और असल डेटा, data फ़ोल्डर में Parquet फ़ाइलों के तौर पर मिलेगा.

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 को यह पता चलता है कि GCS में Iceberg टेबल की मेटाडेटा फ़ाइल कहां है और इसे कैसे समझा जाना चाहिए.

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

BigQuery में Cloud Resource कनेक्शन के बारे में ज़्यादा जानकारी, 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

बाहरी टेबल बनाना

अब BigQuery में BigLake की बाहरी टेबल बनाएं. इस कमांड से कोई भी डेटा ट्रांसफ़र नहीं होता है. यह सिर्फ़ GCS में मौजूद डेटा का पॉइंटर बनाता है. Snowflake की बनाई गई .metadata.json फ़ाइलों में से किसी एक का पाथ ज़रूरी होगा.

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 में डेटा की पुष्टि करना

अब इस टेबल को स्टैंडर्ड एसक्यूएल का इस्तेमाल करके क्वेरी किया जा सकता है. ठीक वैसे ही जैसे किसी अन्य BigQuery टेबल को क्वेरी किया जाता है. BigQuery, GCS से Parquet फ़ाइलों को तुरंत पढ़ने के लिए इस कनेक्शन का इस्तेमाल करेगा.

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 में एक डेस्टिनेशन टेबल बनानी होगी. इसका स्कीमा, BigQuery के स्कीमा के साथ काम करने वाला होना चाहिए.

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 में डेस्टिनेशन टेबल बनने के बाद, डेटा ट्रांसफ़र करना बहुत आसान हो जाता है. एक ही 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 में डेटा की पुष्टि करना

बधाई हो! आपने रिवर्स ईटीएल पाइपलाइन को पूरी तरह से बना लिया है और उसे लागू कर दिया है. आखिरी चरण में, यह पुष्टि करना होता है कि डेटा, 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 Catalog में जाकर, Catalog पर कर्सर घुमाएं. इसके बाद, External Data पर क्लिक करें
  2. CODELABS_RETL_EXT_VOL के दाईं ओर मौजूद 227b3e306c3d609d.png पर क्लिक करें. इसके बाद, बाहरी वॉल्यूम छोड़ें को चुनें. इसके बाद, पुष्टि करने वाले डायलॉग बॉक्स में बाहरी वॉल्यूम छोड़ें पर फिर से क्लिक करें

9. बधाई हो

कोडलैब पूरा करने के लिए बधाई.

हमने क्या-क्या कवर किया है

  • Snowflake में डेटा लोड करने का तरीका
  • GCS बकेट बनाने का तरीका
  • Snowflake टेबल को CSV फ़ॉर्मैट में GCS में एक्सपोर्ट करने का तरीका
  • स्पैनर इंस्टेंस सेट अप करने का तरीका
  • Dataflow की मदद से, CSV टेबल को Spanner में लोड करने का तरीका