BQ kullanarak Snowflake'ten Spanner'a ters ETL

1. Google Cloud Storage ve BigQuery'yi kullanarak Snowflake'ten Spanner'a ters ETL ardışık düzeni oluşturma

Giriş

Bu codelab'de, Snowflake'ten Spanner'a ters ETL ardışık düzeni oluşturulur. Geleneksel olarak ETL (Ayıklama, Dönüştürme, Yükleme) ardışık düzenleri, verileri operasyonel veritabanlarından analitik için Snowflake gibi bir veri ambarına taşır. Ters ETL işlem hattı ise bunun tam tersini yapar: Seçilmiş, işlenmiş verileri veri ambarı içinden operasyonel sistemlere geri taşır. Bu sistemlerde uygulamalara güç verebilir, kullanıcıya yönelik özellikler sunabilir veya anlık karar verme için kullanılabilir.

Amaç, bir Snowflake Iceberg tablosundaki toplu veri kümesini, yüksek kullanılabilirlik uygulamaları için ideal olan küresel olarak dağıtılmış bir ilişkisel veritabanı olan Spanner'a taşımaktır.

Bunu yapmak için ara adımlar olarak Google Cloud Storage (GCS) ve BigQuery kullanılır. Veri akışı ve bu mimarinin arkasındaki mantıkla ilgili dökümü aşağıda bulabilirsiniz:

1. Iceberg biçiminde Snowflake'ten Google Cloud Storage'a (GCS) aktarma:

• İlk adım, verileri Snowflake'ten açık ve iyi tanımlanmış bir biçimde çıkarmaktır. Tablo, Apache Iceberg biçiminde dışa aktarılır. Bu işlem, temel verileri bir dizi Parquet dosyası olarak, tablonun meta verilerini (şema, bölümler, dosya konumları) ise JSON ve Avro dosyaları olarak yazar. Bu eksiksiz tablo yapısını GCS'de hazırlamak, verileri taşınabilir hale getirir ve Iceberg biçimini anlayan tüm sistemler tarafından erişilebilir kılar.

2. GCS'deki Iceberg tablolarını BigQuery BigLake harici tablosuna dönüştürme:

• Veriler doğrudan GCS'den Spanner'a yüklenmek yerine güçlü bir aracı olarak BigQuery kullanılır. BigQuery'de, doğrudan GCS'deki Iceberg meta veri dosyasına işaret eden bir BigLake harici tablosu oluşturacaksınız. Bu yaklaşımın bazı avantajları vardır:
• Veri Tekrarı Yok: BigQuery, tablo yapısını meta verilerden okur ve Parquet veri dosyalarını yerinde sorgular. Bu sayede, verileri içe aktarmadan önemli ölçüde zaman ve depolama maliyetlerinden tasarruf edebilirsiniz.
• Birleşik Sorgular: GCS verileri üzerinde, yerel bir BigQuery tablosuymuş gibi karmaşık SQL sorguları çalıştırmanıza olanak tanır.

3. BigQuery'den Spanner'a:

• Son adım, verileri BigQuery'den Spanner'a taşımaktır. Bu işlemi, BigQuery'deki EXPORT DATA sorgusu adı verilen güçlü bir özelliği kullanarak gerçekleştireceksiniz. Bu özellik, "ters ETL" adımıdır.
• Operasyonel Hazırlık: Spanner, işlemsel iş yükleri için tasarlanmıştır ve uygulamalar için güçlü tutarlılık ve yüksek kullanılabilirlik sağlar. Veriler Spanner'a taşındığında, düşük gecikmeli nokta aramaları gerektiren kullanıcıya yönelik uygulamalar, API'ler ve diğer operasyonel sistemler tarafından erişilebilir hale gelir.
• Ölçeklenebilirlik: Bu kalıp, büyük veri kümelerini işlemek için BigQuery'nin analitik gücünden yararlanmaya ve ardından sonuçları Spanner'ın küresel olarak ölçeklenebilir altyapısı aracılığıyla verimli bir şekilde sunmaya olanak tanır.

Hizmetler ve Terminoloji

• Snowflake: Hizmet olarak veri ambarı sağlayan bir bulut veri platformu.
• Spanner: Tümüyle yönetilen, küresel olarak dağıtılmış ilişkisel veritabanı.
• Google Cloud Storage: Google Cloud'un blob depolama teklifi.
• BigQuery: Analiz için tümüyle yönetilen, sunucusuz bir veri ambarı.
• Iceberg: Apache tarafından tanımlanan ve yaygın açık kaynak veri dosyası biçimleri üzerinde soyutlama sağlayan açık bir tablo biçimidir.
• Parquet: Apache tarafından geliştirilen açık kaynaklı bir sütunlu ikili veri dosyası biçimi.

Neler öğreneceksiniz?

• Snowflake'e veri yükleme
• GCS paketi oluşturma
• Snowflake tablosunu Iceberg biçiminde GCS'ye aktarma
• Spanner örneği oluşturma
• BigQuery'deki BigLake harici tablolarını Spanner'a yükleme

2. Kurulum, Şartlar ve Sınırlamalar

Ön koşullar

• Snowflake hesabı
• BigQuery'den Spanner'a veri dışa aktarmak için BigQuery Enterprise katmanı veya daha yüksek bir rezervasyon içeren bir Google Cloud hesabı gerekir.
• Web tarayıcısı üzerinden Google Cloud Console'a erişim
• Google Cloud KSA komutlarını çalıştırmak için bir terminal
• Google Cloud kuruluşunuzda iam.allowedPolicyMemberDomains politikası etkinse bir yöneticinin, harici alanlardaki hizmet hesaplarına izin vermek için istisna vermesi gerekebilir. Bu konu, geçerli olduğu durumlarda sonraki bir adımda ele alınacaktır.

Sınırlamalar

Bu işlem hattında ortaya çıkabilecek belirli sınırlamalar ve veri türü uyumsuzlukları hakkında bilgi sahibi olmanız önemlidir.

Kar tanesinden buz dağına

Sütun veri türleri Snowflake ve Iceberg arasında farklılık gösterir. Bunlar arasında çeviri yapma hakkında bilgileri Snowflake belgelerinde bulabilirsiniz.

Iceberg'den BigQuery'ye

Iceberg tablolarını sorgulamak için BigQuery kullanılırken bazı sınırlamalar vardır. Tam liste için BigQuery belgelerine bakın. BIGNUMERIC, INTERVAL, JSON, RANGE veya GEOGRAPHY gibi türlerin şu anda desteklenmediğini unutmayın.

BigQuery'den Spanner'a

BigQuery'den Spanner'a aktarım için kullanılan EXPORT DATA komutu, tüm BigQuery veri türlerini desteklemez. Aşağıdaki türleri içeren bir tabloyu dışa aktarmak hataya neden olur:

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

Ayrıca, BigQuery projesi GoogleSQL lehçesini kullanıyorsa Spanner'a aktarma için aşağıdaki sayısal türler de desteklenmez:

• BIGNUMERIC

Sınırlamaların tam ve güncel listesi için resmi belgelere (Spanner'a Aktarma Sınırlamaları) bakın.

Snowflake

Bu codelab için mevcut bir Snowflake hesabını kullanabilir veya ücretsiz deneme hesabı oluşturabilirsiniz.

Google Cloud Platform IAM İzinleri

Google Hesabı'nın bu codelab'deki tüm adımları yürütmek için aşağıdaki izinlere sahip olması gerekir.

Hizmet Hesapları
iam.serviceAccountKeys.create	Hizmet hesaplarının oluşturulmasına izin verir.
Spanner
spanner.instances.create	Yeni bir Spanner örneği oluşturmaya izin verir.
spanner.databases.create	DDL ifadelerinin çalıştırılmasına izin vererek
spanner.databases.updateDdl	Veritabanında tablo oluşturmak için DDL ifadelerinin çalıştırılmasına izin verir.
Google Cloud Storage
storage.buckets.create	Dışa aktarılan Parquet dosyalarını depolamak için yeni bir GCS paketi oluşturulmasına olanak tanır.
storage.objects.create	Dışa aktarılan Parquet dosyalarının GCS paketine yazılmasına izin verir.
storage.objects.get	BigQuery'nin GCS paketindeki Parquet dosyalarını okumasına izin verir.
storage.objects.list	BigQuery'nin GCS paketindeki Parquet dosyalarını listelemesine olanak tanır.
Dataflow
Dataflow.workitems.lease	Dataflow'daki iş öğelerinin talep edilmesine izin verir.
Dataflow.workitems.sendMessage	Dataflow çalışanının Dataflow hizmetine mesaj göndermesine izin verir.
Logging.logEntries.create	Dataflow çalışanlarının Google Cloud Logging'e günlük girişleri yazmasına izin verir.

Kolaylık sağlamak için bu izinleri içeren önceden tanımlanmış roller kullanılabilir.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Yeniden kullanılabilir özellikler ayarlama

Bu laboratuvar boyunca birkaç değerin tekrar tekrar kullanılması gerekecek. Bu işlemi kolaylaştırmak için bu değerleri daha sonra kullanılacak kabuk değişkenlerine ayarlayacağız.

• GCP_REGION: GCP kaynaklarının bulunacağı belirli bölge. Bölgelerin listesini burada bulabilirsiniz.
• GCP_PROJECT: Kullanılacak GCP projesinin kimliği.
• GCP_BUCKET_NAME: Oluşturulacak ve veri dosyalarının depolanacağı GCS paketi adı.

Paket adları genel olarak benzersiz olmalıdır. Yaygın bir kalıp şöyledir: [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>

Bu ortam değişkenlerinin yalnızca çalıştırıldıkları kabuk örneğinde ayarlanacağını ve bu laboratuvardaki komutları daha sonra farklı bir kabuk örneğinde çalıştırırsanız tekrar ayarlanması gerektiğini unutmayın.

Google Cloud projesi

Proje, Google Cloud'daki temel düzenleme birimidir. Bir yönetici kullanmak üzere bir tane sağladıysa bu adım atlanabilir.

CLI kullanılarak şu şekilde proje oluşturulabilir:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Proje oluşturma ve yönetme hakkında daha fazla bilgiyi burada bulabilirsiniz.

Spanner'ı ayarlama

Spanner'ı kullanmaya başlamak için bir örnek ve bir veritabanı sağlamanız gerekir. Spanner örneği yapılandırma ve oluşturma hakkında ayrıntılı bilgiyi burada bulabilirsiniz.

Örneği oluşturma

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

Veritabanını oluşturma

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

3. Google Cloud Storage paketi oluşturma

Snowflake tarafından oluşturulan Parquet veri dosyalarını ve Iceberg meta verilerini depolamak için Google Cloud Storage (GCS) kullanılır. Bunun için öncelikle dosya hedefi olarak kullanılacak yeni bir paket oluşturulması gerekir. Yerel makinedeki bir Terminal penceresinde aşağıdaki adımları uygulayın.

Paketi oluşturma

Belirli bir bölgede (ör. us-central1) depolama paketi oluşturmak için aşağıdaki komutu kullanın.

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

Paket oluşturmayı doğrulama

Bu komut başarılı olduğunda tüm paketleri listeleyerek sonucu kontrol edin. Yeni paket, sonuç listesinde görünür. Bucket referansları genellikle bucket adının önünde gs:// ön ekiyle gösterilir.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Yazma izinlerini test etme

Bu adım, yerel ortamın doğru şekilde kimlik doğrulandığından ve yeni oluşturulan pakete dosya yazmak için gerekli izinlere sahip olduğundan emin olmanızı sağlar.

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

Yüklenen dosyayı doğrulama

Paketteki nesneleri listeleyin. Yeni yüklenen dosyanın tam yolu görünmelidir.

gcloud storage ls gs://$GCS_BUCKET_NAME

Aşağıdaki çıkışı göreceksiniz:

gs://$GCS_BUCKET_NAME/hello.txt

Bir paketteki nesnenin içeriğini görüntülemek için gcloud storage cat kullanılabilir.

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

Dosyanın içeriği görünür olmalıdır:

Hello, GCS

Test dosyasını temizleme

Cloud Storage paketi kuruldu. Geçici test dosyası artık silinebilir.

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

Çıkış, silme işlemini onaylamalıdır:

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

4. Snowflake'ten GCS'ye aktarma

Bu laboratuvarda, karar destek sistemleri için endüstri standardı bir karşılaştırma testi olan TPC-H veri kümesini kullanacaksınız. Şeması, müşteriler, siparişler, tedarikçiler ve parçalarla gerçekçi bir iş ortamını modelliyor. Bu nedenle, gerçek dünya analizi ve veri taşıma senaryosunu göstermek için mükemmel bir seçenek. Bu veri kümesi, tüm Snowflake hesaplarında varsayılan olarak kullanılabilir.

Ham, normalleştirilmiş TPC-H tablolarını kullanmak yerine yeni bir toplu tablo oluşturacaksınız. Bu yeni tablo, orders, customer ve nation tablolarındaki verileri birleştirerek ulusal satış toplamlarının normalleştirilmemiş ve özetlenmiş bir görünümünü oluşturur. Bu ön toplama adımı, verileri belirli bir kullanım alanına (bu senaryoda, operasyonel bir uygulama tarafından tüketilmek üzere) hazırladığından analizde yaygın olarak kullanılan bir yöntemdir.

Snowflake'in Google Cloud Storage'a Erişmesine İzin Verme

Snowflake'in GCS paketine veri yazmasına izin vermek için iki şeyin oluşturulması gerekir: bir harici birim ve gerekli izinler.

• Harici birim, GCS paketindeki belirli bir konuma güvenli bir bağlantı sağlayan bir Snowflake nesnesidir. Kendisi veri depolamaz ancak Snowflake'in bulut depolama alanına erişmesi için gereken yapılandırmayı tutar.
• Güvenlik nedeniyle, Cloud Storage paketleri varsayılan olarak gizlidir. Harici bir birim oluşturulduğunda Snowflake, özel bir hizmet hesabı oluşturur. Bu hizmet hesabına, paketten okuma ve pakete yazma izni verilmelidir.

Veritabanı oluşturun

1. Sol taraftaki menüde, Horizon Kataloğu bölümünde Katalog'un üzerine gelin ve Veritabanı Gezgini'ni tıklayın.
2. Veritabanları sayfasına geldiğinizde sağ üstteki + Veritabanı düğmesini tıklayın.
3. Yeni veritabanına codelabs_retl_db adını verin.

Çalışma sayfası oluşturma

Veritabanında SQL komutlarını çalıştırmak için çalışma sayfaları gerekir.

Çalışma sayfası oluşturmak için:

1. Soldaki menüde, Verilerle çalışma bölümünde Projeler'in üzerine gelin ve Çalışma alanları'nı tıklayın.
2. Çalışma Alanlarım kenar çubuğunda + Yeni ekle düğmesini tıklayın ve SQL Dosyası'nı seçin.

Snowflake'e yönelik diğer tüm SQL komutları, bu tek dosyanın içeriği değiştirilerek çalıştırılabilir. Dosyanın paragraf bölümleri, imlecinizi ilgili paragrafın üzerine getirip klavyenizde Ctrl + Enter tuşlarına basılarak çalıştırılabilir.

Harici bir birim oluşturma

Birimi oluşturmak için Snowflake çalışma sayfasında aşağıdaki komutu çalıştırın.

Snowflake'in Google Cloud Storage yolları için gs:// değil, gcs:// önekini kullandığını unutmayın.

Aşağıdaki SQL kodu, ortam değişkenleri ayarlanmış kabuk örneğinin bir parçası olarak çalıştırılmayacağından GCS paketi adınızın değerlerini göründüğü yerlerde değiştirdiğinizden emin olun.

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 hizmet hesabını alma

Snowflake'in yeni oluşturulan harici birim için oluşturduğu benzersiz hizmet hesabını almak üzere DESC (açıklayın).

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

1. Sonuçlar bölmesinde, JSON özelliklerini bulun ve property_value girişinde "NAME":"codelabs_retl_ext_vol" ile başlayan bir JSON dizesi arayın.
2. JSON nesnesinde STORAGE_GCP_SERVICE_ACCOUNT özelliğini bulun ve değerini kopyalayın (e-posta adresine benzer). Bu, GCS paketine erişmesi gereken hizmet hesabı tanımlayıcısıdır.
3. Bu hizmet hesabını, daha sonra yeniden kullanmak üzere kabuk örneğinizdeki bir ortam değişkeninde saklayın.

export GCP_SERVICE_ACCOUNT=<Your service account>

Not: Google Cloud kuruluşunuz iam.allowedPolicyMemberDomains kısıtlamasını zorunlu kılıyorsa bu hizmet hesabının alan adının (iam.allowedPolicyMemberDomains sembolünden sonraki kısım) bir güvenlik yöneticisi tarafından izin verilenler listesine eklenmesi gerekir.@

Snowflake'e GCS izinleri verme

Şimdi Snowflake hizmet hesabına GCS paketine yazma izni verilmelidir.

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'te erişimi doğrulama

Snowflake çalışma sayfasına geri dönün ve Snowflake'in artık GCS paketine başarıyla bağlanabildiğini doğrulamak için bu komutu çalıştırın.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Sonuç, "success":true içeren bir JSON nesnesi olmalıdır.

Snowflake'teki harici birimler hakkında daha fazla bilgi için resmi belgelere bakın.

Örnek sipariş verilerini dışa aktarma

Artık Snowflake'te Iceberg tablosu oluşturabilirsiniz. Aşağıdaki komut, Snowflake'e bir sorgu çalıştırmasını ve sonuçları Iceberg biçiminde GCS'de depolamasını söyler. Veri dosyaları Parquet, meta veriler ise Avro ve JSON biçiminde olur. Tüm dosyalar, codelabs_retl_ext_vol Harici Birim tarafından tanımlanan konumda depolanır.

Veritabanı oluşturun

1. Sol taraftaki menüde, Horizon Kataloğu bölümünde Katalog'un üzerine gelin ve Veritabanı Gezgini'ni tıklayın.
2. Veritabanları sayfasına geldiğinizde sağ üstteki + Veritabanı düğmesini tıklayın.
3. Yeni veritabanına codelabs_retl_db adını verin.

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 kullanarak Iceberg tabloları oluşturma ve yönetme hakkında daha fazla bilgi için resmi belgelere bakın.

GCP'deki verileri doğrulama

Şimdi GCS paketini kontrol edin. Snowflake'in oluşturduğu dosyalar görünür olmalıdır. Bu, dışa aktarma işleminin başarılı olduğunu onaylar. Iceberg meta verileri, metadata klasöründe, gerçek veriler ise data klasöründe Parquet dosyaları olarak bulunur.

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

Dosya adları farklılık gösterebilir ancak yapı şu şekilde olmalıdır:

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

Veriler artık Snowflake'ten kopyalanıp Iceberg biçiminde Google Cloud Storage'a aktarılmıştır.

Bu listeyi kullanırken metadata.json dosyasını daha sonra kullanacağımız için bir ortam değişkenine kaydedelim.

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

5. BigQuery Harici Tablosu Yapılandırma

Iceberg tablosu Google Cloud Storage'a taşındığına göre, bir sonraki adım bu tabloyu BigQuery'ye erişilebilir hale getirmektir. Bu işlem, BigLake harici tablosu oluşturularak yapılabilir.

BigLake, BigQuery'de tablolar oluşturarak verileri doğrudan Google Cloud Storage gibi harici kaynaklardan okumaya olanak tanıyan bir depolama motorudur. Bu laboratuvarda, verilerin alınmasına gerek kalmadan BigQuery'nin yeni dışa aktarılan Iceberg tablosunu anlamasını sağlayan temel teknolojidir.

Bu özelliğin çalışması için iki bileşen gerekir:

1. Cloud Resource Connection: Bu, BigQuery ile GCS arasında güvenli bir bağlantıdır. Kimlik doğrulama için özel bir hizmet hesabı kullanılır. Bu sayede, BigQuery'nin GCS paketindeki dosyaları okumak için gerekli izinlere sahip olması sağlanır.
2. Harici Tablo Tanımı: Bu, BigQuery'ye Iceberg tablosunun meta veri dosyasının GCS'de nerede bulunacağını ve nasıl yorumlanacağını söyler.

Google Cloud Storage'a bağlantı yapılandırma

İlk olarak, BigQuery'nin GCS'ye erişmesine izin veren bağlantı oluşturulur. Bu komut, BigQuery'de bir bağlantı kaynağı oluşturur.

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

Başarı şöyle görünür:

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

BigQuery'deki Cloud Resource bağlantıları hakkında daha fazla bilgi için Google Cloud belgelerine bakın.

BigQuery bağlantısının verileri okumasına izin verme

Yeni BigQuery bağlantısının, Google Cloud Storage paketinden veri okuma izni gerektiren kendi hizmet hesabı vardır.

1. Bağlantı hizmeti hesabını alma

Öncelikle, yeni oluşturulan bağlantıdan hizmet hesabı kimliğini alın:

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

Sonuçlarda, eşleşen bağlantıların tablosu gösterilir.

serviceAccountId değerini, daha sonra kullanılacak bir ortam değişkeni olarak ayarlayalım.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. İzin Ver

Aşağıdaki komutu çalıştırarak hizmet hesabının GCS paketindeki verileri görüntülemesine izin verin.

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

Harici tabloyu oluşturma

Şimdi BigQuery'de BigLake harici tablosunu oluşturun. Bu komut herhangi bir veriyi taşımaz. Bu işlem, GCS'deki mevcut verilere yönelik bir işaretçi oluşturur. Snowflake'in oluşturduğu .metadata.json dosyalarından birinin yolu gerekir.

Aşağıdaki komutta external_table_definition değerini metadata.json dosyanızın uygun yoluyla değiştirin.

Bu tablonun, önceki adımdaki bağlantıyla aynı bölgede oluşturulduğundan emin olun.

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'deki verileri doğrulama

Bu tablo artık diğer tüm BigQuery tablolarında olduğu gibi standart SQL kullanılarak sorgulanabilir. BigQuery, GCS'deki Parquet dosyalarını anında okumak için bağlantıyı kullanır.

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

6. BigQuery'den Spanner'a Veri Aktarma: Son Adım

Ardışık düzenin son ve en önemli kısmı olan verilerin BigLake tablosundan Spanner'a taşınması tamamlandı. Bu, "ters ETL" adımıdır. Bu adımda, veri ambarında işlenip düzenlenmiş olan veriler, uygulamalar tarafından kullanılmak üzere bir operasyonel sisteme yüklenir.

Spanner, tümüyle yönetilen, küresel olarak dağıtılmış bir ilişkisel veritabanıdır. Geleneksel bir ilişkisel veritabanının işlemsel tutarlılığını sunar ancak NoSQL veritabanının yatay ölçeklenebilirliğine sahiptir. Bu nedenle, ölçeklenebilir ve yüksek düzeyde kullanılabilir uygulamalar oluşturmak için ideal bir seçimdir.

Süreç şu şekilde işler:

1. Spanner veritabanında, verilerin yapısıyla eşleşen bir tablo şeması oluşturun.
2. BigLake tablosundaki verileri doğrudan Spanner tablosuna yüklemek için bir BigQuery EXPORT DATA sorgusu çalıştırın.

Spanner tablosunu oluşturma

BigQuery'den veri aktarmadan önce Spanner'da uyumlu bir şemaya sahip bir hedef tablo oluşturulmalıdır.

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'den veri dışa aktarma

Bu son adımdır. Kaynak veriler BigQuery BigLake tablosunda hazır olduğunda ve hedef tablo Spanner'da oluşturulduğunda gerçek veri taşıma işlemi şaşırtıcı derecede basittir. Tek bir BigQuery SQL sorgusu kullanılır: EXPORT DATA.

Bu sorgu, özellikle bu gibi senaryolar için tasarlanmıştır. BigQuery tablosundaki (BigLake tablosu gibi harici olanlar dahil) verileri harici bir hedefe verimli bir şekilde aktarır. Bu durumda hedef, Spanner tablosudur.

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

Sorgu tamamlandığında, Sonuçlar bölmesinde "Güncelleme tamamlandı" mesajı gösterilir.

7. Spanner'daki verileri doğrulama

Tebrikler! Ters ETL ardışık düzeni başarıyla oluşturulup yürütüldü. Son adım, verilerin Spanner'a beklendiği gibi ulaştığını doğrulamaktır.

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

İçe aktarılan örnek veriler istendiği gibi görünüyor:

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

Analitik ve operasyonel veri dünyaları arasındaki boşluk başarıyla kapatıldı.

8. Temizleme

Spanner'ı temizleme

Spanner veritabanını ve örneğini silme

gcloud spanner instances delete $SPANNER_INSTANCE

GCS'de yer açma

Verileri barındırmak için oluşturulan GCS paketini silin.

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

BigQuery'de temizlik yapma

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

Snowflake'i temizleme

Veritabanını bırakma

1. Sol taraftaki menüde, Horizon Catalog bölümünde Catalog'un (Katalog) üzerine gelin ve Database Explorer'ı (Veritabanı Gezgini) tıklayın.
2. Seçenekleri genişletmek için CODELABS_RETL_DB veritabanının sağındaki ... simgesini tıklayın ve Bırak'ı seçin.
3. Açılan onay iletişim kutusunda Veritabanını Bırak'ı seçin.

Çalışma kitaplarını silme

4. Soldaki menüde, Verilerle çalışma bölümünde Projeler'in üzerine gelin ve Çalışma alanları'nı tıklayın.
5. Çalışma Alanım kenar çubuğunda, bu laboratuvarda kullandığınız farklı çalışma alanı dosyalarının üzerine gelerek ... ek seçeneklerini gösterin ve bu seçenekleri tıklayın.
6. Sil'i ve ardından açılan onay iletişim kutusunda tekrar Sil'i seçin.
7. Bu işlemi, bu laboratuvar için oluşturduğunuz tüm SQL çalışma alanı dosyaları için yapın.

Harici birimleri silme

8. Soldaki menüde, Horizon Catalog bölümünde Catalog'un (Katalog) üzerine gelin ve External Data'yı (Harici Veriler) tıklayın.
9. simgesini tıklayın, CODELABS_RETL_EXT_VOL simgesinin sağında Harici birimi bırak'ı ve onay iletişim kutusunda tekrar Harici birimi bırak'ı seçin.

9. Tebrikler

Codelab'i tamamladığınız için tebrik ederiz.

İşlediğimiz konular

• Snowflake'e veri yükleme
• GCS paketi oluşturma
• Snowflake tablosunu CSV biçiminde GCS'ye aktarma
• Spanner örneği oluşturma
• Dataflow ile CSV tablolarını Spanner'a yükleme