ETL đảo ngược từ Snowflake sang Spanner bằng BQ

1. Xây dựng quy trình ETL đảo ngược từ Snowflake sang Spanner bằng Google Cloud Storage và BigQuery

Giới thiệu

Trong lớp học lập trình này, bạn sẽ xây dựng một quy trình Reverse ETL từ Snowflake đến Spanner. Theo truyền thống, các quy trình ETL (Trích xuất, Biến đổi, Tải) sẽ di chuyển dữ liệu từ cơ sở dữ liệu vận hành vào một kho dữ liệu như Snowflake để phân tích. Quy trình Reverse ETL làm ngược lại: quy trình này di chuyển dữ liệu đã được xử lý và tuyển chọn từ kho dữ liệu trở lại các hệ thống vận hành, nơi dữ liệu có thể hỗ trợ các ứng dụng, cung cấp các tính năng cho người dùng hoặc được dùng để đưa ra quyết định theo thời gian thực.

Mục tiêu là di chuyển một tập dữ liệu tổng hợp từ bảng Snowflake Iceberg vào Spanner, một cơ sở dữ liệu quan hệ được phân phối trên toàn cầu, lý tưởng cho các ứng dụng có tính sẵn sàng cao.

Để đạt được điều này, Google Cloud Storage (GCS) và BigQuery được dùng làm các bước trung gian. Sau đây là thông tin chi tiết về dòng dữ liệu và lý do đằng sau cấu trúc này:

  1. Snowflake sang Google Cloud Storage (GCS) ở định dạng Iceberg:
  • Bước đầu tiên là lấy dữ liệu từ Snowflake ở định dạng mở, được xác định rõ ràng. Bảng được xuất ở định dạng Apache Iceberg. Quy trình này ghi dữ liệu cơ bản dưới dạng một nhóm tệp Parquet và siêu dữ liệu của bảng (giản đồ, phân vùng, vị trí tệp) dưới dạng tệp JSON và Avro. Việc dàn dựng cấu trúc bảng hoàn chỉnh này trong GCS giúp dữ liệu có thể di chuyển và truy cập được vào mọi hệ thống hiểu định dạng Iceberg.
  1. Chuyển đổi bảng Iceberg trong GCS thành bảng bên ngoài BigLake của BigQuery:
  • Thay vì tải dữ liệu trực tiếp từ GCS vào Spanner, BigQuery được dùng làm một trung gian mạnh mẽ. Bạn sẽ tạo một bảng bên ngoài BigLake trong BigQuery, trỏ trực tiếp đến tệp siêu dữ liệu Iceberg trong GCS. Phương pháp này có một số ưu điểm:
  • Không trùng lặp dữ liệu: BigQuery đọc cấu trúc bảng từ siêu dữ liệu và truy vấn các tệp dữ liệu Parquet tại chỗ mà không cần nhập các tệp này, giúp tiết kiệm đáng kể thời gian và chi phí lưu trữ.
  • Truy vấn liên kết: Cho phép chạy các truy vấn SQL phức tạp trên dữ liệu GCS như thể đó là một bảng BigQuery gốc.
  1. BigQuery đến Spanner:
  • Bước cuối cùng là di chuyển dữ liệu từ BigQuery vào Spanner. Bạn sẽ đạt được điều này bằng cách sử dụng một tính năng mạnh mẽ trong BigQuery có tên là truy vấn EXPORT DATA, đây là bước "ETL đảo ngược".
  • Khả năng sẵn sàng hoạt động: Spanner được thiết kế cho các khối lượng công việc giao dịch, mang lại tính nhất quán cao và khả năng hoạt động cao cho các ứng dụng. Bằng cách di chuyển dữ liệu vào Spanner, dữ liệu sẽ có thể truy cập được đối với các ứng dụng, API và hệ thống vận hành khác mà người dùng sử dụng, yêu cầu tra cứu điểm có độ trễ thấp.
  • Khả năng mở rộng: Mẫu này cho phép khai thác sức mạnh phân tích của BigQuery để xử lý các tập dữ liệu lớn, sau đó phân phát kết quả một cách hiệu quả thông qua cơ sở hạ tầng có khả năng mở rộng trên toàn cầu của Spanner.

Dịch vụ và thuật ngữ

  • Snowflake – Một nền tảng dữ liệu trên đám mây cung cấp kho dữ liệu dưới dạng dịch vụ.
  • Spanner – Cơ sở dữ liệu quan hệ được quản lý toàn diện và phân phối trên toàn cầu.
  • Google Cloud Storage – Sản phẩm lưu trữ blob của Google Cloud.
  • BigQuery – Kho dữ liệu phân tích hoàn toàn không máy chủ và do Google quản lý.
  • Iceberg – Một định dạng bảng mở do Apache xác định, cung cấp khả năng trừu tượng hoá đối với các định dạng tệp dữ liệu nguồn mở phổ biến.
  • Parquet – Một định dạng tệp dữ liệu nhị phân dạng cột mã nguồn mở của Apache.

Kiến thức bạn sẽ học được

  • Cách tải dữ liệu vào Snowflake
  • Cách tạo một Nhóm lưu trữ GCS
  • Cách xuất bảng Snowflake sang GCS ở định dạng Iceberg
  • Cách thiết lập một phiên bản Spanner
  • Cách tải Bảng bên ngoài BigLake trong BigQuery lên Spanner

2. Thiết lập, yêu cầu và hạn chế

Điều kiện tiên quyết

  • Tài khoản Snowflake
  • Bạn cần có tài khoản Google Cloud có mức đặt trước từ cấp doanh nghiệp trở lên để xuất dữ liệu từ BigQuery sang Spanner.
  • Quyền truy cập vào Google Cloud Console thông qua trình duyệt web
  • Một cửa sổ dòng lệnh để chạy các lệnh Google Cloud CLI
  • Nếu tổ chức của bạn trên Google Cloud đã bật chính sách iam.allowedPolicyMemberDomains, thì quản trị viên có thể cần cấp một trường hợp ngoại lệ để cho phép tài khoản dịch vụ từ các miền bên ngoài. Chúng ta sẽ đề cập đến vấn đề này trong một bước sau (nếu có).

Các điểm hạn chế

Bạn cần lưu ý đến một số hạn chế và sự không tương thích về kiểu dữ liệu có thể xảy ra trong quy trình này.

Từ bông tuyết đến tảng băng trôi

Các loại dữ liệu cột khác nhau giữa Snowflake và Iceberg. Thông tin về cách dịch giữa các loại này có trong tài liệu của Snowflake.

Iceberg sang BigQuery

Khi sử dụng BigQuery để truy vấn các bảng Iceberg, bạn sẽ gặp phải một số hạn chế. Để xem danh sách đầy đủ, hãy xem tài liệu của BigQuery. Xin lưu ý rằng các loại như BIGNUMERIC, INTERVAL, JSON, RANGE hoặc GEOGRAPHY hiện không được hỗ trợ.

BigQuery đến Spanner

Lệnh EXPORT DATA từ BigQuery đến Spanner không hỗ trợ tất cả các kiểu dữ liệu BigQuery. Việc xuất bảng có các loại sau sẽ dẫn đến lỗi:

  • STRUCT
  • GEOGRAPHY
  • DATETIME
  • RANGE
  • TIME

Ngoài ra, nếu dự án BigQuery đang sử dụng phương ngữ GoogleSQL, thì các kiểu số sau đây cũng không được hỗ trợ để xuất sang Spanner:

  • BIGNUMERIC

Để xem danh sách đầy đủ và mới nhất về các hạn chế, hãy tham khảo tài liệu chính thức: Các hạn chế khi xuất sang Spanner.

Bông tuyết

Trong lớp học lập trình này, bạn có thể sử dụng tài khoản Snowflake hiện có hoặc thiết lập tài khoản dùng thử miễn phí.

Quyền IAM của Google Cloud Platform

Tài khoản Google sẽ cần có các quyền sau để thực hiện tất cả các bước trong lớp học lập trình này.

Tài khoản dịch vụ

iam.serviceAccountKeys.create

Cho phép tạo Tài khoản dịch vụ.

Spanner

spanner.instances.create

Cho phép tạo một phiên bản Spanner mới.

spanner.databases.create

Cho phép chạy các câu lệnh DDL để tạo

spanner.databases.updateDdl

Cho phép chạy câu lệnh DDL để tạo bảng trong cơ sở dữ liệu.

Google Cloud Storage

storage.buckets.create

Cho phép tạo một vùng lưu trữ GCS mới để lưu trữ các tệp Parquet đã xuất.

storage.objects.create

Cho phép ghi các tệp Parquet đã xuất vào bộ chứa GCS.

storage.objects.get

Cho phép BigQuery đọc các tệp Parquet trong vùng lưu trữ GCS.

storage.objects.list

Cho phép BigQuery liệt kê các tệp Parquet trong vùng lưu trữ GCS.

Dataflow

Dataflow.workitems.lease

Cho phép yêu cầu các mục công việc từ Dataflow.

Dataflow.workitems.sendMessage

Cho phép worker Dataflow gửi thông báo trở lại dịch vụ Dataflow.

Logging.logEntries.create

Cho phép các worker Dataflow ghi các mục nhật ký vào Google Cloud Logging.

Để thuận tiện, bạn có thể sử dụng các vai trò được xác định trước có chứa những quyền này.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Thiết lập các thuộc tính có thể dùng lại

Bạn sẽ cần một số giá trị nhiều lần trong suốt quá trình thực hành này. Để đơn giản hoá việc này, chúng ta sẽ đặt các giá trị này thành các biến shell để sử dụng sau.

  • GCP_REGION – Khu vực cụ thể nơi các tài nguyên GCP sẽ được đặt. Bạn có thể xem danh sách các khu vực tại đây.
  • GCP_PROJECT – Mã dự án GCP cần sử dụng.
  • GCP_BUCKET_NAME – Tên của Nhóm GCS sẽ được tạo và nơi lưu trữ các tệp dữ liệu.
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>

Dự án trên Google Cloud

Dự án là đơn vị tổ chức cơ bản trong Google Cloud. Nếu quản trị viên đã cung cấp một khoá để sử dụng, bạn có thể bỏ qua bước này.

Bạn có thể tạo một dự án bằng CLI như sau:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Tìm hiểu thêm về cách tạo và quản lý dự án tại đây.

Thiết lập Spanner

Để bắt đầu sử dụng Spanner, bạn cần cung cấp một phiên bản và một cơ sở dữ liệu. Bạn có thể xem thông tin chi tiết về cách định cấu hình và tạo một phiên bản Spanner tại đây.

Tạo đối tượng

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

Tạo Database

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

3. Tạo một bộ chứa Google Cloud Storage

Google Cloud Storage (GCS) sẽ được dùng để lưu trữ các tệp dữ liệu Parquet và siêu dữ liệu Iceberg do Snowflake tạo. Để làm như vậy, trước tiên, bạn cần tạo một vùng chứa mới để dùng làm đích đến của tệp. Từ cửa sổ dòng lệnh trên máy cục bộ, hãy làm theo các bước sau.

Tạo vùng chứa

Sử dụng lệnh sau để tạo một nhóm lưu trữ ở một khu vực cụ thể (ví dụ: us-central1).

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

Xác minh việc tạo vùng chứa

Sau khi lệnh đó thành công, hãy kiểm tra kết quả bằng cách liệt kê tất cả các vùng chứa. Nhóm mới sẽ xuất hiện trong danh sách kết quả. Tham chiếu đến nhóm thường xuất hiện với tiền tố gs:// ở trước tên nhóm.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Kiểm thử quyền ghi

Bước này giúp đảm bảo rằng môi trường cục bộ được xác thực đúng cách và có các quyền cần thiết để ghi tệp vào bộ chứa mới tạo.

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

Xác minh tệp đã tải lên

Liệt kê các đối tượng trong vùng chứa. Đường dẫn đầy đủ của tệp vừa tải lên sẽ xuất hiện.

gcloud storage ls gs://$GCS_BUCKET_NAME

Bạn sẽ thấy kết quả sau đây:

gs://$GCS_BUCKET_NAME/hello.txt

Để xem nội dung của một đối tượng trong một nhóm, bạn có thể sử dụng gcloud storage cat.

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

Nội dung của tệp phải hiển thị:

Hello, GCS

Dọn dẹp tệp kiểm thử

Bộ chứa Cloud Storage hiện đã được thiết lập. Giờ đây, bạn có thể xoá tệp kiểm thử tạm thời.

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

Kết quả đầu ra sẽ xác nhận việc xoá:

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

4. Xuất từ Snowflake sang GCS

Trong lớp học lập trình này, bạn sẽ sử dụng tập dữ liệu TPC-H. Đây là điểm chuẩn theo tiêu chuẩn ngành cho các hệ thống hỗ trợ quyết định. Lược đồ này mô hình hoá một môi trường kinh doanh thực tế với khách hàng, đơn đặt hàng, nhà cung cấp và phụ tùng, khiến lược đồ này trở nên hoàn hảo để minh hoạ một kịch bản phân tích và di chuyển dữ liệu trong thế giới thực. Theo mặc định, tập dữ liệu này có trong mọi tài khoản Snowflake.

Thay vì sử dụng các bảng TPC-H thô, được chuẩn hoá, bạn sẽ tạo một bảng tổng hợp mới. Bảng mới này sẽ kết hợp dữ liệu từ các bảng orders, customer và nation để tạo ra một chế độ xem tóm tắt, không chuẩn hoá về tổng doanh số bán hàng theo quốc gia. Bước tổng hợp trước này là một phương pháp phổ biến trong hoạt động phân tích, vì bước này chuẩn bị dữ liệu cho một trường hợp sử dụng cụ thể – trong trường hợp này, để một ứng dụng vận hành sử dụng.

Cho phép Snowflake truy cập vào Google Cloud Storage

Để cho phép Snowflake ghi dữ liệu vào vùng lưu trữ GCS, bạn cần tạo 2 thứ: một External Volume (Ổ đĩa ngoài) và các quyền cần thiết.

  • Ổ đĩa ngoài là một đối tượng Snowflake cung cấp đường liên kết bảo mật đến một vị trí cụ thể trong một vùng chứa GCS. Bản thân trình điều khiển này không lưu trữ dữ liệu mà chỉ giữ cấu hình cần thiết để Snowflake truy cập vào bộ nhớ đám mây.
  • Để đảm bảo an toàn, các bộ chứa lưu trữ trên đám mây được đặt ở chế độ riêng tư theo mặc định. Khi bạn tạo một Ổ đĩa ngoài, Snowflake sẽ tạo một Tài khoản dịch vụ chuyên dụng. Bạn phải cấp cho tài khoản dịch vụ này quyền đọc và ghi vào bộ chứa.

Tạo cơ sở dữ liệu

  1. Trong trình đơn bên trái, trong mục Danh mục Horizon, hãy di chuột lên Danh mục, rồi nhấp vào Database Explorer (Công cụ khám phá cơ sở dữ liệu)
  2. Sau khi truy cập vào trang Cơ sở dữ liệu, hãy nhấp vào nút + Cơ sở dữ liệu ở trên cùng bên phải.
  3. Đặt tên cho db codelabs_retl_db mới

Tạo trang tính

Để chạy các lệnh sql đối với cơ sở dữ liệu, bạn sẽ cần trang tính.

Cách tạo trang tính:

  1. Trong trình đơn bên trái, trong mục Làm việc với dữ liệu, hãy di chuột lên Dự án, rồi nhấp vào Không gian làm việc
  2. Trong thanh bên Không gian làm việc của tôi, hãy nhấp vào nút + Thêm mới rồi chọn Tệp SQL

Tạo ổ đĩa ngoài

Chạy lệnh sau trong một trang tính Snowflake để tạo ổ đĩa.

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'
  )
);

Lấy tài khoản dịch vụ Snowflake

DESC (mô tả) ổ đĩa ngoài mới tạo để lấy tài khoản dịch vụ duy nhất mà Snowflake đã tạo cho ổ đĩa đó.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;
  1. Trong ngăn kết quả, hãy tìm các thuộc tính json, tìm mục property_value chứa một chuỗi JSON bắt đầu bằng "NAME":"codelabs_retl_ext_vol"
  2. Tìm thuộc tính STORAGE_GCP_SERVICE_ACCOUNT trong đối tượng json rồi sao chép giá trị của thuộc tính đó (giá trị này sẽ trông giống như một địa chỉ email). Đây là giá trị nhận dạng tài khoản dịch vụ cần có quyền truy cập vào vùng lưu trữ GCS.
  3. Lưu trữ tài khoản dịch vụ này vào một biến môi trường trong phiên bản shell để sử dụng lại sau này
export GCP_SERVICE_ACCOUNT=<Your service account>

Cấp quyền GCS cho Snowflake

Giờ đây, bạn phải cấp cho tài khoản dịch vụ Snowflake quyền ghi vào bộ chứa 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"

Xác minh quyền truy cập trong Snowflake

Trong trang tính Snowflake, hãy chạy lệnh này để xác minh rằng Snowflake hiện có thể kết nối thành công với vùng chứa GCS.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Kết quả phải là một đối tượng JSON chứa "success":true.

Để biết thêm thông tin về các ổ đĩa ngoài trong Snowflake, hãy tham khảo tài liệu chính thức.

Xuất dữ liệu mẫu về đơn đặt hàng

Giờ đây, bạn có thể tạo một bảng Iceberg trong Snowflake. Lệnh sau đây yêu cầu Snowflake chạy một truy vấn và lưu trữ kết quả trong GCS bằng định dạng Iceberg. Các tệp dữ liệu sẽ là Parquet, còn siêu dữ liệu sẽ là Avro và JSON, tất cả đều được lưu trữ ở vị trí do codelabs_retl_ext_vol Ổ đĩa ngoài xác định.

Tạo cơ sở dữ liệu

  1. Trong trình đơn bên trái, trong mục Danh mục Horizon, hãy di chuột lên Danh mục, rồi nhấp vào Database Explorer (Công cụ khám phá cơ sở dữ liệu)
  2. Sau khi truy cập vào trang Cơ sở dữ liệu, hãy nhấp vào nút + Cơ sở dữ liệu ở trên cùng bên phải.
  3. Đặt tên cho db codelabs_retl_db mới
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
);

Để biết thêm thông tin về cách tạo và quản lý bảng Iceberg bằng Snowflake, hãy tham khảo tài liệu chính thức.

Xác minh dữ liệu trong GCP

Bây giờ, hãy kiểm tra vùng lưu trữ GCS. Bạn sẽ thấy các tệp mà Snowflake đã tạo. Điều này xác nhận rằng quá trình xuất đã thành công. Bạn sẽ tìm thấy siêu dữ liệu Iceberg trong thư mục metadata và dữ liệu thực tế dưới dạng tệp Parquet trong thư mục data.

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

Tên tệp chính xác sẽ khác nhau, nhưng cấu trúc phải có dạng như sau:

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
...

Dữ liệu hiện đã được sao chép từ Snowflake vào Google Cloud Storage ở định dạng Iceberg.

Trong khi có danh sách này, hãy lưu tệp metadata.json vào một biến môi trường vì chúng ta sẽ cần đến tệp này sau.

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

5. Định cấu hình Bảng bên ngoài BigQuery

Giờ đây, khi bảng Iceberg đã có trong Google Cloud Storage, bước tiếp theo là cấp quyền truy cập cho BigQuery. Bạn có thể thực hiện việc này bằng cách tạo một bảng bên ngoài BigLake.

BigLake là một công cụ lưu trữ cho phép tạo các bảng trong BigQuery để đọc dữ liệu trực tiếp từ các nguồn bên ngoài như Google Cloud Storage. Đối với phòng thí nghiệm này, đây là công nghệ chính giúp BigQuery hiểu được bảng Iceberg vừa xuất mà không cần phải nhập dữ liệu.

Để làm được việc này, bạn cần có 2 thành phần:

  1. Mối kết nối tài nguyên trên đám mây: Đây là mối liên kết bảo mật giữa BigQuery và GCS. Dịch vụ này sử dụng một tài khoản dịch vụ đặc biệt để xử lý việc xác thực, đảm bảo BigQuery có các quyền cần thiết để đọc tệp từ vùng chứa GCS.
  2. Định nghĩa bảng bên ngoài: Định nghĩa này cho BigQuery biết vị trí tìm tệp siêu dữ liệu của bảng Iceberg trong GCS và cách diễn giải tệp đó.

Định cấu hình kết nối với Google Cloud Storage

Trước tiên, hệ thống sẽ tạo kết nối cho phép BigQuery truy cập vào GCS. Lệnh này sẽ tạo một tài nguyên kết nối trong BigQuery.

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

Thành công sẽ có dạng như sau:

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

Bạn có thể xem thêm thông tin về các mối kết nối Tài nguyên trên đám mây trong BigQuery trong tài liệu của Google Cloud.

Cho phép kết nối BigQuery đọc dữ liệu

Kết nối BigQuery mới có tài khoản dịch vụ riêng và cần có quyền đọc dữ liệu từ bộ chứa Google Cloud Storage.

1. Lấy tài khoản dịch vụ kết nối

Trước tiên, hãy lấy mã nhận dạng tài khoản dịch vụ từ mối kết nối vừa tạo:

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

Kết quả sẽ cho thấy một bảng gồm các mối kết nối trùng khớp.

Hãy đặt serviceAccountId thành một biến môi trường để sử dụng sau này.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. Cấp quyền

Cho phép tài khoản dịch vụ xem dữ liệu trong bộ chứa GCS bằng cách chạy lệnh sau.

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

Tạo bảng bên ngoài

Bây giờ, hãy tạo bảng bên ngoài BigLake trong BigQuery. Lệnh này không di chuyển dữ liệu. Thao tác này chỉ tạo một con trỏ đến dữ liệu hiện có trong GCS. Bạn cần có đường dẫn đến một trong các tệp .metadata.json mà Snowflake đã tạo.

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

Xác minh dữ liệu trong BigQuery

Giờ đây, bạn có thể truy vấn bảng này bằng SQL chuẩn, giống như với mọi bảng BigQuery khác. BigQuery sẽ sử dụng kết nối này để đọc các tệp Parquet từ GCS ngay lập tức.

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

6. Nhập dữ liệu từ BigQuery vào Spanner: Bước cuối cùng

Đã đến phần cuối cùng và quan trọng nhất của quy trình: di chuyển dữ liệu từ bảng BigLake vào Spanner. Đây là bước "ETL đảo ngược", trong đó dữ liệu sau khi được xử lý và tuyển chọn trong kho dữ liệu sẽ được tải vào một hệ thống vận hành để các ứng dụng sử dụng.

Spanner là một cơ sở dữ liệu quan hệ được quản lý toàn diện và phân phối trên toàn cầu. Dịch vụ này mang đến tính nhất quán giao dịch của một cơ sở dữ liệu quan hệ truyền thống nhưng có khả năng mở rộng theo chiều ngang của một cơ sở dữ liệu NoSQL. Nhờ đó, đây là lựa chọn lý tưởng để tạo các ứng dụng có khả năng mở rộng và tính sẵn sàng cao.

Quy trình sẽ như sau:

  1. Tạo một giản đồ bảng trong cơ sở dữ liệu Spanner khớp với cấu trúc của dữ liệu.
  2. Chạy truy vấn BigQuery EXPORT DATA để tải dữ liệu từ bảng BigLake trực tiếp vào bảng Spanner.

Tạo bảng Spanner

Trước khi chuyển dữ liệu từ BigQuery, bạn phải tạo một bảng đích trong Spanner có giản đồ tương thích.

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
)"

Xuất dữ liệu từ BigQuery

Đây là bước cuối cùng. Khi dữ liệu nguồn đã sẵn sàng trong bảng BigLake của BigQuery và bảng đích được tạo trong Spanner, quá trình di chuyển dữ liệu thực tế diễn ra rất đơn giản. Một truy vấn SQL BigQuery sẽ được dùng: EXPORT DATA.

Truy vấn này được thiết kế riêng cho những trường hợp như vậy. Công cụ này xuất dữ liệu một cách hiệu quả từ một bảng BigQuery (bao gồm cả các bảng bên ngoài như bảng BigLake) sang một đích đến bên ngoài. Trong trường hợp này, đích đến là bảng 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

Khi truy vấn kết thúc, ngăn Kết quả sẽ hiển thị "Đã hoàn tất việc cập nhật".

7. Xác minh dữ liệu trong Spanner

Xin chúc mừng! Bạn đã tạo và thực thi thành công một quy trình Reverse ETL hoàn chỉnh. Bước cuối cùng là xác minh rằng dữ liệu đã được chuyển đến Spanner như dự kiến.

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

Dữ liệu mẫu đã nhập sẽ xuất hiện như yêu cầu:

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

Khoảng cách giữa thế giới dữ liệu phân tích và dữ liệu vận hành đã được thu hẹp thành công.

8. Dọn dẹp

Dọn dẹp Spanner

Xoá Cơ sở dữ liệu và Đối tượng Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

Dọn dẹp GCS

Xoá Nhóm GCS đã tạo để lưu trữ dữ liệu

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

Dọn dẹp BigQuery

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

Dọn dẹp Snowflake

Xoá cơ sở dữ liệu

  1. Trong trình đơn bên trái, trong mục Danh mục Horizon, hãy di chuột lên Danh mục,rồi nhấp vào Database Explorer (Công cụ khám phá cơ sở dữ liệu)
  2. Nhấp vào biểu tượng ... ở bên phải cơ sở dữ liệu CODELABS_RETL_DB để mở rộng các lựa chọn rồi chọn Drop (Xoá)
  3. Trong hộp thoại xác nhận bật lên, hãy chọn Drop Database (Xoá cơ sở dữ liệu)

Xoá sổ làm việc

  1. Trong trình đơn bên trái, trong mục Làm việc với dữ liệu, hãy di chuột lên Dự án, rồi nhấp vào Không gian làm việc
  2. Trong thanh bên Không gian làm việc của tôi, hãy di chuột lên các tệp không gian làm việc mà bạn đã sử dụng cho bài thực hành này để hiện các lựa chọn bổ sung ... rồi nhấp vào đó
  3. Chọn Xoá, rồi chọn Xoá một lần nữa trong hộp thoại xác nhận bật lên.
  4. Thực hiện việc này cho tất cả các tệp không gian làm việc sql mà bạn đã tạo cho lớp học này.

Xoá ổ đĩa ngoài

  1. Trong trình đơn bên trái, trong mục Danh mục Horizon, hãy di chuột lên Danh mục, rồi nhấp vào Dữ liệu bên ngoài
  2. Nhấp vào biểu tượng 227b3e306c3d609d.png ở bên phải biểu tượng CODELABS_RETL_EXT_VOL, rồi chọn Tháo ổ đĩa ngoài, sau đó nhấp lại vào Tháo ổ đĩa ngoài trên hộp thoại xác nhận

9. Xin chúc mừng

Chúc mừng bạn đã hoàn thành lớp học lập trình này.

Nội dung đã đề cập

  • Cách tải dữ liệu vào Snowflake
  • Cách tạo một Nhóm lưu trữ GCS
  • Cách xuất một bảng Snowflake sang GCS ở định dạng CSV
  • Cách thiết lập một phiên bản Spanner
  • Cách tải Bảng CSV lên Spanner bằng Dataflow