BQ를 사용하여 Snowflake에서 Spanner로 역방향 ETL

1. Google Cloud Storage 및 BigQuery를 사용하여 Snowflake에서 Spanner로 역방향 ETL 파이프라인 빌드

소개

이 Codelab에서는 Snowflake에서 Spanner로 역방향 ETL 파이프라인을 빌드합니다. 일반적으로 ETL (추출, 변환, 로드) 파이프라인은 분석을 위해 운영 데이터베이스에서 Snowflake와 같은 데이터 웨어하우스로 데이터를 이동합니다. 역방향 ETL 파이프라인은 그 반대로, 데이터 웨어하우스에서 선별되고 처리된 데이터를 애플리케이션을 지원하거나, 사용자 대상 기능을 제공하거나, 실시간 의사결정에 사용할 수 있는 운영 시스템으로 다시 이동합니다.

목표는 집계된 데이터 세트를 Snowflake Iceberg 테이블에서 고가용성 애플리케이션에 이상적인 전역적으로 분산된 관계형 데이터베이스인 Spanner로 이동하는 것입니다.

이를 위해 Google Cloud Storage (GCS)와 BigQuery가 중간 단계로 사용됩니다. 데이터 흐름과 이 아키텍처의 이유는 다음과 같습니다.

1. Snowflake에서 Google Cloud Storage (GCS)로 Iceberg 형식:

• 첫 번째 단계는 개방적이고 잘 정의된 형식으로 Snowflake에서 데이터를 가져오는 것입니다. 테이블이 Apache Iceberg 형식으로 내보내집니다. 이 프로세스는 기본 데이터를 Parquet 파일 집합으로, 테이블의 메타데이터 (스키마, 파티션, 파일 위치)를 JSON 및 Avro 파일로 작성합니다. 이 전체 테이블 구조를 GCS에 스테이징하면 Iceberg 형식을 이해하는 모든 시스템에서 데이터를 휴대하고 액세스할 수 있습니다.

2. GCS의 Iceberg 테이블을 BigQuery BigLake 외부 테이블로 변환:

• GCS에서 Spanner로 직접 데이터를 로드하는 대신 BigQuery가 강력한 중개자로 사용됩니다. GCS의 Iceberg 메타데이터 파일을 직접 가리키는 BigQuery의 BigLake 외부 테이블을 만듭니다. 이 방식의 장점은 다음과 같습니다.
• 데이터 중복 없음: BigQuery는 메타데이터에서 테이블 구조를 읽고 Parquet 데이터 파일을 수집하지 않고 제자리에서 쿼리하므로 상당한 시간과 스토리지 비용이 절약됩니다.
• 통합 쿼리: GCS 데이터가 네이티브 BigQuery 테이블인 것처럼 복잡한 SQL 쿼리를 실행할 수 있습니다.

3. BigQuery to Spanner:

• 마지막 단계는 BigQuery에서 Spanner로 데이터를 이동하는 것입니다. 이를 위해 '역방향 ETL' 단계인 BigQuery의 강력한 기능인 EXPORT DATA 쿼리를 사용합니다.
• 운영 준비 상태: Spanner는 트랜잭션 워크로드를 위해 설계되어 애플리케이션에 강력한 일관성과 고가용성을 제공합니다. 데이터를 Spanner로 이동하면 지연 시간이 짧은 포인트 조회가 필요한 사용자 대상 애플리케이션, API, 기타 운영 시스템에서 데이터에 액세스할 수 있습니다.
• 확장성: 이 패턴을 사용하면 BigQuery의 분석 기능을 활용하여 대규모 데이터 세트를 처리한 다음 Spanner의 전역적으로 확장 가능한 인프라를 통해 결과를 효율적으로 제공할 수 있습니다.

서비스 및 용어

• Snowflake - 서비스로서의 데이터 웨어하우스를 제공하는 클라우드 데이터 플랫폼입니다.
• Spanner: 완전 관리형의 전역 분산 관계형 데이터베이스입니다.
• Google Cloud Storage - Google Cloud의 블롭 스토리지 제품입니다.
• BigQuery - 분석을 위한 완전 관리형 서버리스 데이터 웨어하우스입니다.
• Iceberg: 일반적인 오픈소스 데이터 파일 형식에 대한 추상화를 제공하는 Apache에 의해 정의된 개방형 테이블 형식입니다.
• Parquet - Apache의 오픈소스 열 형식 바이너리 데이터 파일 형식입니다.

학습할 내용

• Snowflake에 데이터를 로드하는 방법
• GCS 버킷을 만드는 방법
• Snowflake 테이블을 Iceberg 형식으로 GCS에 내보내는 방법
• Spanner 인스턴스를 설정하는 방법
• BigQuery의 BigLake 외부 테이블을 Spanner에 로드하는 방법

2. 설정, 요구사항 및 제한사항

기본 요건

• Snowflake 계정
• BigQuery에서 Spanner로 내보내려면 BigQuery Enterprise 등급 이상의 예약이 있는 Google Cloud 계정이 필요합니다.
• 웹브라우저를 통한 Google Cloud 콘솔 액세스
• Google Cloud CLI 명령어를 실행하는 터미널
• Google Cloud 조직에 iam.allowedPolicyMemberDomains 정책이 사용 설정되어 있는 경우 관리자가 외부 도메인의 서비스 계정을 허용하기 위해 예외를 부여해야 할 수 있습니다. 해당되는 경우 나중에 설명합니다.

제한사항

이 파이프라인에서 발생할 수 있는 특정 제한사항과 데이터 유형 비호환성을 알고 있어야 합니다.

Snowflake에서 Iceberg로

Snowflake와 Iceberg의 열 데이터 유형이 다릅니다. 이들 간의 변환에 관한 정보는 Snowflake 문서에서 확인할 수 있습니다.

Iceberg to BigQuery

BigQuery를 사용하여 Iceberg 테이블을 쿼리할 때는 몇 가지 제한사항이 있습니다. 전체 목록은 BigQuery 문서를 참고하세요. BIGNUMERIC, INTERVAL, JSON, RANGE, GEOGRAPHY와 같은 유형은 현재 지원되지 않습니다.

BigQuery to Spanner

BigQuery에서 Spanner로의 EXPORT DATA 명령어는 모든 BigQuery 데이터 유형을 지원하지 않습니다. 다음 유형의 테이블을 내보내면 오류가 발생합니다.

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

또한 BigQuery 프로젝트에서 GoogleSQL 다이얼렉트를 사용하는 경우 다음 숫자 유형도 Spanner로 내보내기가 지원되지 않습니다.

• BIGNUMERIC

제한사항의 전체 최신 목록은 공식 문서인 Spanner로 내보내기 제한사항을 참고하세요.

Snowflake

이 Codelab에서는 기존 Snowflake 계정을 사용하거나 무료 체험판 계정을 설정할 수 있습니다.

Google Cloud Platform IAM 권한

이 Codelab의 모든 단계를 실행하려면 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
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 프로젝트 ID입니다.
• GCP_BUCKET_NAME - 생성할 GCS 버킷 이름이며 데이터 파일이 저장될 위치입니다.

버킷 이름은 전역에서 고유해야 합니다. 일반적인 패턴은 [organization_name]-[project_id]-[environment]-[purpose]입니다.

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)는 Snowflake에서 생성된 Parquet 데이터 파일과 Iceberg 메타데이터를 저장하는 데 사용됩니다. 이렇게 하려면 먼저 파일 대상으로 사용할 새 버킷을 만들어야 합니다. 로컬 머신의 터미널 창에서 다음 단계를 따르세요.

버킷 만들기

다음 명령어를 사용하여 특정 리전 (예: 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 버킷에 데이터를 쓸 수 있도록 외부 볼륨과 필요한 권한을 만들어야 합니다.

• 외부 볼륨은 GCS 버킷의 특정 위치에 대한 보안 링크를 제공하는 Snowflake 객체입니다. 데이터 자체를 저장하는 것이 아니라 Snowflake가 클라우드 스토리지에 액세스하는 데 필요한 구성을 보유합니다.
• 보안을 위해 클라우드 스토리지 버킷은 기본적으로 비공개입니다. 외부 볼륨이 생성되면 Snowflake에서 전용 서비스 계정을 생성합니다. 이 서비스 계정에는 버킷에서 읽고 쓸 수 있는 권한이 부여되어야 합니다.

데이터베이스 만들기

1. 왼쪽 사이드 메뉴의 Horizon Catalog에서 Catalog 위로 마우스를 가져간 다음 Database Explorer를 클릭합니다.
2. 데이터베이스 페이지에서 오른쪽 상단의 + 데이터베이스 버튼을 클릭합니다.
3. 새 DB 이름을 codelabs_retl_db로 지정합니다.

워크시트 만들기

데이터베이스에 대해 SQL 명령어를 실행하려면 워크시트가 필요합니다.

워크시트를 만들려면 다음 단계를 따르세요.

1. 왼쪽 사이드 메뉴의 데이터 작업에서 프로젝트 위로 마우스를 가져간 다음 작업공간을 클릭합니다.
2. 내 작업공간 사이드바에서 + 새로 추가 버튼을 클릭하고 SQL 파일을 선택합니다.

이 파일의 콘텐츠를 바꾸면 Snowflake에 대한 모든 추가 SQL 명령어를 실행할 수 있습니다. 파일의 단락 부분을 실행하려면 해당 단락에 커서를 놓고 키보드에서 Ctrl + Enter를 누릅니다.

외부 볼륨 만들기

Snowflake 워크시트에서 다음 명령어를 실행하여 볼륨을 만듭니다.

Snowflake는 Google Cloud Storage 경로에 gs://이 아닌 gcs:// 접두사를 사용합니다.

다음 SQL 코드는 환경 변수가 설정된 셸 인스턴스의 일부로 실행되지 않으므로 GCS 버킷 이름 값이 표시되는 위치에서 값을 바꿔야 합니다.

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 속성을 찾아 "NAME":"codelabs_retl_ext_vol"로 시작하는 JSON 문자열을 보유한 property_value 항목을 찾습니다.
2. JSON 객체 내에서 STORAGE_GCP_SERVICE_ACCOUNT 속성을 찾아 값을 복사합니다 (이메일 주소와 유사함). GCS 버킷에 액세스해야 하는 서비스 계정 식별자입니다.
3. 나중에 재사용할 수 있도록 이 서비스 계정을 셸 인스턴스의 환경 변수에 저장합니다.

export GCP_SERVICE_ACCOUNT=<Your service account>

참고: Google Cloud 조직에서 iam.allowedPolicyMemberDomains 제약 조건을 적용하는 경우 이 서비스 계정의 도메인 (@ 기호 뒤에 오는 부분)을 보안 관리자가 허용 목록에 추가해야 합니다.

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에서 Iceberg 테이블을 만들 수 있습니다. 다음 명령어는 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에서 만든 파일이 표시됩니다. 내보내기가 성공했음을 확인할 수 있습니다. Iceberg 메타데이터는 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는 Google Cloud Storage와 같은 외부 소스에서 직접 데이터를 읽는 BigQuery 테이블을 만들 수 있는 스토리지 엔진입니다. 이 실습에서는 데이터를 수집하지 않고도 BigQuery가 방금 내보낸 Iceberg 테이블을 이해할 수 있도록 지원하는 핵심 기술입니다.

이 기능을 사용하려면 다음 두 가지 구성요소가 필요합니다.

1. Cloud 리소스 연결: 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 리소스 연결에 대한 자세한 내용은 Google Cloud 문서를 참고하세요.

데이터를 읽을 수 있도록 BigQuery 연결 승인

새 BigQuery 연결에는 Google Cloud Storage 버킷에서 데이터를 읽을 수 있는 권한이 필요한 자체 서비스 계정이 있습니다.

1. 연결 서비스 계정 가져오기

먼저 방금 만든 연결에서 서비스 계정 ID를 가져옵니다.

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 파일 중 하나의 경로가 필요합니다.

다음 명령어에서 external_table_definition 값을 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 테이블과 마찬가지로 표준 SQL을 사용하여 이 테이블을 쿼리할 수 있습니다. 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로 데이터를 이동하는 단계에 도달했습니다. 데이터 웨어하우스에서 처리되고 선별된 데이터가 애플리케이션에서 사용할 수 있도록 운영 시스템에 로드되는 '역방향 ETL' 단계입니다.

Spanner는 완전 관리형의 전역 분산 관계형 데이터베이스입니다. 기존 관계형 데이터베이스의 트랜잭션 일관성을 제공하지만 NoSQL 데이터베이스의 수평 확장성을 갖습니다. 따라서 확장 가능하고 가용성이 높은 애플리케이션을 빌드하는 데 이상적입니다.

프로세스는 다음과 같습니다.

1. 데이터 구조와 일치하는 테이블 스키마를 Spanner 데이터베이스에 만듭니다.
2. BigQuery EXPORT DATA 쿼리를 실행하여 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에서 데이터 내보내기

마지막 단계입니다. BigQuery BigLake 테이블에 소스 데이터가 준비되고 Spanner에 대상 테이블이 생성되면 실제 데이터 이동은 매우 간단합니다. 단일 BigQuery SQL 쿼리(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에서 데이터 확인

축하합니다. 전체 리버스 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 Catalog에서 Catalog 위로 마우스를 가져간 다음 Database Explorer를 클릭합니다.
2. CODELABS_RETL_DB 데이터베이스 오른쪽에 있는 ...을 클릭하여 옵션을 펼치고 삭제를 선택합니다.
3. 팝업되는 확인 대화상자에서 데이터베이스 삭제를 선택합니다.

워크북 삭제

4. 왼쪽 사이드 메뉴의 데이터 작업에서 프로젝트 위로 마우스를 가져간 다음 작업공간을 클릭합니다.
5. 내 작업공간 사이드바에서 이 실습에 사용한 다양한 작업공간 파일 위로 마우스를 가져가 ... 추가 옵션을 표시하고 클릭합니다.
6. 삭제를 선택한 다음 팝업되는 확인 대화상자에서 삭제를 다시 선택합니다.
7. 이 실습을 위해 만든 모든 SQL 워크스페이스 파일에 대해 이 작업을 실행합니다.

외부 볼륨 삭제

8. 왼쪽 사이드 메뉴의 Horizon 카탈로그에서 카탈로그 위로 마우스를 가져간 다음 외부 데이터를 클릭합니다.
9. CODELABS_RETL_EXT_VOL 오른쪽에 있는 를 클릭하고 외부 볼륨 삭제를 선택한 다음 확인 대화상자에서 외부 볼륨 삭제를 다시 선택합니다.

9. 축하합니다

축하합니다. Codelab을 완료했습니다.

학습한 내용

• Snowflake에 데이터를 로드하는 방법
• GCS 버킷을 만드는 방법
• Snowflake 테이블을 CSV 형식으로 GCS에 내보내는 방법
• Spanner 인스턴스를 설정하는 방법
• Dataflow를 사용하여 CSV 테이블을 Spanner에 로드하는 방법