Reverse ETL จาก Snowflake ไปยัง Spanner โดยใช้ BQ

1. สร้างไปป์ไลน์ Reverse ETL จาก Snowflake ไปยัง Spanner โดยใช้ Google Cloud Storage และ BigQuery

บทนำ

ใน Codelab นี้ เราจะสร้างไปป์ไลน์ Reverse ETL จาก Snowflake ไปยัง Spanner โดยปกติแล้ว ไปป์ไลน์ ETL (Extract, Transform, Load) จะย้ายข้อมูลจากฐานข้อมูลการดำเนินงานไปยังคลังข้อมูล เช่น Snowflake เพื่อการวิเคราะห์ ไปป์ไลน์ Reverse ETL จะทําตรงกันข้าม นั่นคือย้ายข้อมูลที่ดูแลจัดการและประมวลผลแล้วจากคลังข้อมูลกลับไปยังระบบปฏิบัติการ ซึ่งจะช่วยขับเคลื่อนแอปพลิเคชัน ให้บริการฟีเจอร์ที่แสดงต่อผู้ใช้ หรือใช้ในการตัดสินใจแบบเรียลไทม์

เป้าหมายคือการย้ายชุดข้อมูลที่รวบรวมแล้วจากตาราง Iceberg ของ Snowflake ไปยัง 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:
  • BigQuery จะใช้เป็นสื่อกลางที่มีประสิทธิภาพแทนการโหลดข้อมูลจาก GCS ไปยัง Spanner โดยตรง คุณจะสร้างตารางภายนอก BigLake ใน BigQuery ซึ่งชี้ไปยังไฟล์ข้อมูลเมตา Iceberg ใน GCS โดยตรง แนวทางนี้มีข้อดีหลายประการ ดังนี้
  • ไม่มีการทำซ้ำข้อมูล: BigQuery จะอ่านโครงสร้างตารางจากข้อมูลเมตาและค้นหาไฟล์ข้อมูล Parquet ในตำแหน่งโดยไม่ต้องนำเข้า ซึ่งช่วยประหยัดเวลาและค่าใช้จ่ายในการจัดเก็บได้อย่างมาก
  • การค้นหาแบบรวม: ช่วยให้เรียกใช้การค้นหา SQL ที่ซับซ้อนในข้อมูล GCS ได้ราวกับว่าเป็นตาราง BigQuery ดั้งเดิม
  1. BigQuery ไปยัง Spanner:
  • ขั้นตอนสุดท้ายคือการย้ายข้อมูลจาก BigQuery ไปยัง Spanner คุณจะทำได้โดยใช้ฟีเจอร์ที่มีประสิทธิภาพใน BigQuery ที่เรียกว่าEXPORT DATAการค้นหา ซึ่งเป็นขั้นตอน "Reverse ETL"
  • ความพร้อมในการปฏิบัติงาน: Spanner ออกแบบมาสำหรับภาระงานด้านธุรกรรม โดยมอบความสอดคล้องที่รัดกุมและความพร้อมใช้งานสูงสำหรับแอปพลิเคชัน การย้ายข้อมูลไปยัง Spanner จะทำให้แอปพลิเคชัน API และระบบปฏิบัติการอื่นๆ ที่ต้องมีการค้นหาแบบจุดที่มีเวลาในการตอบสนองต่ำซึ่งผู้ใช้มองเห็นได้เข้าถึงข้อมูลได้
  • ความสามารถในการปรับขนาด: รูปแบบนี้ช่วยให้ใช้ประโยชน์จากความสามารถในการวิเคราะห์ของ BigQuery เพื่อประมวลผลชุดข้อมูลขนาดใหญ่ แล้วแสดงผลลัพธ์อย่างมีประสิทธิภาพผ่านโครงสร้างพื้นฐานที่ปรับขนาดได้ทั่วโลกของ Spanner

บริการและคำศัพท์

  • Snowflake - แพลตฟอร์มข้อมูลระบบคลาวด์ที่ให้บริการคลังข้อมูลเป็นบริการ
  • Spanner - ฐานข้อมูลเชิงสัมพันธ์ที่มีการจัดการครบวงจรและกระจายอยู่ทั่วโลก
  • Google Cloud Storage - ข้อเสนอที่เก็บข้อมูล Blob ของ Google Cloud
  • BigQuery - คลังข้อมูลแบบ Serverless ที่มีการจัดการอย่างเต็มรูปแบบสำหรับการวิเคราะห์
  • Iceberg - รูปแบบตารางแบบเปิดที่กำหนดโดย Apache ซึ่งให้การแยกข้อมูลเหนือรูปแบบไฟล์ข้อมูลโอเพนซอร์สทั่วไป
  • Parquet - รูปแบบไฟล์ข้อมูลไบนารีแบบคอลัมน์โอเพนซอร์สของ Apache

สิ่งที่คุณจะได้เรียนรู้

  • วิธีโหลดข้อมูลลงใน Snowflake
  • วิธีสร้างที่เก็บข้อมูล GCS
  • วิธีส่งออกตาราง Snowflake ไปยัง GCS ในรูปแบบ Iceberg
  • วิธีตั้งค่าอินสแตนซ์ Spanner
  • วิธีโหลดตารางภายนอก BigLake ใน BigQuery ไปยัง Spanner

2. การตั้งค่า ข้อกำหนด และข้อจำกัด

ข้อกำหนดเบื้องต้น

  • บัญชี Snowflake
  • คุณต้องมีบัญชี Google Cloud ที่มีการจองระดับ Enterprise ขึ้นไปของ BigQuery จึงจะส่งออกจาก BigQuery ไปยัง Spanner ได้
  • สิทธิ์เข้าถึง Google Cloud Console ผ่านเว็บเบราว์เซอร์
  • เทอร์มินัลเพื่อเรียกใช้คำสั่ง Google Cloud CLI
  • หากองค์กร Google Cloud มีiam.allowedPolicyMemberDomainsนโยบายที่เปิดใช้ ผู้ดูแลระบบอาจต้องให้ข้อยกเว้นเพื่ออนุญาตบัญชีบริการจากโดเมนภายนอก ซึ่งจะอธิบายในขั้นตอนถัดไป (หากมี)

ข้อจำกัด

คุณควรทราบข้อจำกัดบางอย่างและความไม่เข้ากันของประเภทข้อมูลที่อาจเกิดขึ้นในไปป์ไลน์นี้

จาก Snowflake สู่ Iceberg

ประเภทข้อมูลของคอลัมน์จะแตกต่างกันระหว่าง 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

บัญชี 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

อนุญาตให้ Worker ของ 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 - รหัสโปรเจ็กต์ 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 โดยจะต้องสร้างที่เก็บข้อมูลใหม่ก่อนเพื่อใช้เป็นปลายทางของไฟล์ จากหน้าต่างเทอร์มินัลในเครื่อง ให้ทำตามขั้นตอนต่อไปนี้

สร้างที่เก็บข้อมูล

ใช้คำสั่งต่อไปนี้เพื่อสร้างที่เก็บข้อมูลในภูมิภาคที่เฉพาะเจาะจง (เช่น 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 คุณต้องสร้าง 2 สิ่ง ได้แก่ External Volume และสิทธิ์ที่จำเป็น

  • โวลุ่มภายนอกคือออบเจ็กต์ Snowflake ที่มีลิงก์ที่ปลอดภัยไปยังตำแหน่งที่เฉพาะเจาะจงในที่เก็บข้อมูล GCS โดยจะไม่จัดเก็บข้อมูลด้วยตัวเอง แต่จะเก็บการกำหนดค่าที่จำเป็นเพื่อให้ Snowflake เข้าถึงพื้นที่เก็บข้อมูลระบบคลาวด์
  • ที่เก็บข้อมูล Cloud Storage จะเป็นแบบส่วนตัวโดยค่าเริ่มต้นเพื่อความปลอดภัย เมื่อสร้างโวลุ่มภายนอก Snowflake จะสร้างบัญชีบริการเฉพาะ บัญชีบริการนี้ต้องได้รับสิทธิ์ในการอ่านและเขียนไปยังที่เก็บข้อมูล

สร้างฐานข้อมูล

  1. ในเมนูด้านซ้าย ภายใต้แคตตาล็อก Horizon ให้วางเมาส์เหนือแคตตาล็อก แล้วคลิกDatabase Explorer
  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 (อธิบาย) โวลุ่มภายนอกที่สร้างขึ้นใหม่เพื่อรับบัญชีบริการที่ไม่ซ้ำกันซึ่ง 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>

ให้สิทธิ์ 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 ให้วางเมาส์เหนือแคตตาล็อก แล้วคลิก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 ที่เพิ่งส่งออกโดยไม่ต้องนำเข้าข้อมูล

หากต้องการให้การทำงานนี้เป็นไปได้ คุณจะต้องมี 2 องค์ประกอบต่อไปนี้

  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

ดูข้อมูลเพิ่มเติมเกี่ยวกับการเชื่อมต่อทรัพยากรระบบคลาวด์ใน 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 นี่คือขั้นตอน "Reverse ETL" ซึ่งจะโหลดข้อมูลที่ประมวลผลและดูแลจัดการในคลังข้อมูลลงในระบบปฏิบัติการเพื่อให้แอปพลิเคชันใช้งาน

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 ให้วางเมาส์เหนือแคตตาล็อก แล้วคลิกDatabase Explorer
  2. คลิก ... ทางด้านขวาของฐานข้อมูล CODELABS_RETL_DB เพื่อขยายตัวเลือก แล้วเลือกทิ้ง
  3. ในกล่องโต้ตอบการยืนยันที่ปรากฏขึ้น ให้เลือกดรอปฐานข้อมูล

ลบเวิร์กบุ๊ก

  1. ในเมนูด้านซ้าย ภายใต้ทำงานกับข้อมูล ให้วางเมาส์เหนือโปรเจ็กต์ แล้วคลิกพื้นที่ทำงาน
  2. ในแถบด้านข้างพื้นที่ทำงานของฉัน ให้วางเมาส์เหนือไฟล์พื้นที่ทำงานต่างๆ ที่คุณใช้สำหรับแล็บนี้เพื่อแสดงตัวเลือกเพิ่มเติม ... แล้วคลิก
  3. เลือกลบ แล้วเลือกลบอีกครั้งในกล่องโต้ตอบการยืนยันที่ปรากฏขึ้น
  4. ทำเช่นนี้กับไฟล์พื้นที่ทำงาน SQL ทั้งหมดที่คุณสร้างขึ้นสำหรับแล็บนี้

ลบวอลุ่มภายนอก

  1. ในเมนูด้านซ้าย ภายใต้แคตตาล็อก Horizon ให้วางเมาส์เหนือแคตตาล็อก แล้วคลิกข้อมูลภายนอก
  2. คลิก 227b3e306c3d609d.png ทางด้านขวาของ CODELABS_RETL_EXT_VOL แล้วเลือกลดระดับเสียงภายนอก จากนั้นคลิกลดระดับเสียงภายนอกอีกครั้งในกล่องโต้ตอบการยืนยัน

9. ขอแสดงความยินดี

ขอแสดงความยินดีที่ทำ Codelab เสร็จสมบูรณ์

สิ่งที่เราได้พูดถึงไปแล้ว

  • วิธีโหลดข้อมูลลงใน Snowflake
  • วิธีสร้างที่เก็บข้อมูล GCS
  • วิธีส่งออกตาราง Snowflake ไปยัง GCS ในรูปแบบ CSV
  • วิธีตั้งค่าอินสแตนซ์ Spanner
  • วิธีโหลดตาราง CSV ไปยัง Spanner ด้วย Dataflow