CSV kullanarak Databricks'ten Spanner'a ters ETL

1. GCS ve Dataflow'u kullanarak Databricks'ten Spanner'a ters ETL ardışık düzeni oluşturma

Giriş

Bu codelab'de, Google Cloud Storage'da depolanan CSV dosyalarını kullanarak Databricks'ten Spanner'a ters ETL ardışık düzeni oluşturacaksınız. Geleneksel olarak ETL (Ayıklama, Dönüştürme, Yükleme) ardışık düzenleri, verileri operasyonel veritabanlarından analiz için Databricks 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 örnek veri kümesini Databricks tablosundan Spanner'a taşımaktır. Spanner, yüksek kullanılabilirlik gerektiren uygulamalar için ideal olan, küresel olarak dağıtılmış bir ilişkisel veritabanıdır.

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

1. CSV biçiminde Databricks'ten Google Cloud Storage'a (GCS):

• İlk adım, verileri Databricks'ten açık ve evrensel bir biçimde çıkarmaktır. CSV'ye aktarma, taşınabilir veri dosyaları oluşturmak için yaygın ve basit bir yöntemdir. Bu dosyalar, ölçeklenebilir ve dayanıklı bir nesne depolama çözümü sunan GCS'de hazırlanır.

2. GCS'den Spanner'a (Dataflow aracılığıyla):

• GCS'den okuma ve Spanner'a yazma işlemleri için özel bir komut dosyası yazmak yerine, tam olarak yönetilen bir veri işleme hizmeti olan Google Dataflow kullanılır. Dataflow, bu tür görevler için özel olarak tasarlanmış önceden oluşturulmuş şablonlar sunar. "GCS Text to Cloud Spanner" şablonunu kullanmak, herhangi bir veri işleme kodu yazmadan yüksek işleme hızlı, paralelleştirilmiş veri içe aktarma işlemine olanak tanır ve önemli ölçüde geliştirme süresi kazandırır.

Neler öğreneceksiniz?

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

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

Ön koşullar

• Küme oluşturma ve kitaplık yükleme izinlerine sahip bir Databricks hesabı. Bu laboratuvar için ücretsiz deneme hesabı yeterli değildir.
• Spanner, Cloud Storage ve Dataflow API'lerinin etkinleştirildiği bir Google Cloud hesabı.
• Web tarayıcısı üzerinden Google Cloud Console'a erişim.
• Google Cloud KSA'nın yüklü olduğu 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.

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

Sınırlamalar

Verileri sistemler arasında taşırken veri türü farklılıklarının farkında olmak önemlidir.

• Databricks'ten CSV'ye: Dışa aktarma sırasında Databricks veri türleri standart metin gösterimlerine dönüştürülür.
• CSV'den Spanner'a: İçe aktarma sırasında hedef Spanner veri türlerinin, CSV dosyasındaki dize gösterimleriyle uyumlu olduğundan emin olmak gerekir. Bu laboratuvar, yaygın bir tür eşleme grubu konusunda size yol gösterir.

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.

Databricks

Bu laboratuvarda, GCS'de harici bir veri konumu tanımlamaya olanak tanıyan, GCP'de barındırılan bir Databricks hesabı kullanılmaktadır.

Databricks örneğiniz farklı bir bulut sağlayıcıdaysa verilerin önce söz konusu bulut sağlayıcının depolama çözümünde hazırlanması, ardından ayrı olarak GCS'ye taşınması gerekir.

Google Cloud

Bu laboratuvar için bir Google Cloud projesi gerekir.

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 CSV veri dosyaları, Spanner'a aktarılmadan önce geçici olarak depolanmak üzere Google Cloud Storage'da (GCS) saklanır.

Paketi oluşturma

Belirli bir bölgede 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. Databricks'ten GCS'ye dışa aktarma

Artık Databricks ortamı, GCS'ye güvenli bir şekilde bağlanacak ve verileri dışa aktaracak şekilde yapılandırılacak.

Kimlik bilgisi oluşturma

1. Sol taraftaki menüden Katalog'u tıklayın.
2. Katalog sayfasının üst kısmında Harici Veriler seçeneği varsa bu seçeneği tıklayın. Aksi takdirde, Bağlan açılır listesini ve ardından Kimlik bilgileri'ni tıklayın.
3. Henüz bu sekmede değilseniz Kimlik Bilgileri sekmesine geçin.
4. Kimlik bilgisi oluştur'u tıklayın.
5. Kimlik Bilgisi Türü için GCP Service Account'yı seçin.
6. Yeterlilik Belgesi Adı için codelabs-retl-credentials girin.
7. Oluştur'u tıklayın
8. İletişim kutusundan hizmet hesabı e-postasını kopyalayın ve Bitti'yi tıklayın.

Bu hizmet hesabını, kabuk örneğinizde yeniden kullanmak üzere bir ortam değişkenine ayarlayın:

export GCP_SERVICE_ACCOUNT=<Your service account>

Databricks'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"

Harici konum oluşturma

6. Sayfanın üst kısmındaki gezinme yolunu kullanarak Kimlik Bilgileri sayfasına geri dönün.
7. Harici Konum sekmesine geçin.
8. Harici konum oluştur'u tıklayın.
9. Harici Konum Adı'nı codelabs-retl-gcs olarak ayarlayın.
10. Depolama Türü'nü GCP olarak tutun.
11. Paket yolunuzu URL olarak ayarlayın.
12. Depolama Kimliği'ni codelabs-retl-credentials olarak ayarlayın.
13. Oluştur'u tıklayın
14. Onayda. Oluştur'u tıklayın

Onayda File Events Resource Provision doğrulamasının başarısız olduğu gösteriliyorsa bu codelab'i etkilemeyeceğinden Force Create'i (Oluşturmayı Zorla) tıklayabilirsiniz.

Katalog ve Şema Oluşturma

1. Sol taraftaki menüden Katalog'u tıklayın.
2. Oluştur'u ve ardından Katalog oluştur'u tıklayın.
3. Katalog Adı'nı retl_tpch_project olarak ayarlayın.
4. Tür'ü Standard olarak ayarlayın.
5. Harici konum olarak codelabs-retl-gcs seçeneğini belirleyin.
6. Oluştur'u tıklayın
7. Katalog listesinde retl_tpch_project simgesini tıklayın.
8. Şema oluştur'u tıklayın.
9. Şema adı'nı tpch_data olarak ayarlayın.
10. Depolama konumu'nu codelabs-retl-gcs olarak seçin.
11. Oluştur'u tıklayın

Verileri CSV olarak dışa aktarma

Veriler artık dışa aktarılmaya hazır. Örnek TPC-H veri kümesi, CSV olarak harici olarak depolanacak yeni tablomuzu tanımlamak için kullanılacak.

Öncelikle örnek verileri çalışma alanındaki yeni bir tabloya kopyalayın. Bunun için SQL kodunun bir sorgudan çalıştırılması gerekir.

1. Sol taraftaki menüde SQL bölümünün altında Sorgular'ı tıklayın.
2. Sorgu oluştur düğmesini tıklayın.
3. Çalıştır düğmesinin yanındaki Çalışma alanı'nı retl_tpch_project olarak ayarlayın.

Aşağıdaki kod, GCS paketi adınız için daha önce tanımlanmış ortam değişkenleriyle kabukta çalıştırılmayacağından Your bucket name değerini kendi paket adınızla değiştirdiğinizden emin olun.

CREATE TABLE retl_tpch_project.tpch_data.regional_sales_csv
USING CSV
LOCATION 'gs://<Your bucket name>/regional_sales_csv'
OPTIONS (
  header "false",
  delimiter ","
)
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 samples.tpch.orders AS o
INNER JOIN samples.tpch.customer AS c
    ON o.o_custkey = c.c_custkey
INNER JOIN samples.tpch.nation AS n
    ON c.c_nationkey = n.n_nationkey
GROUP BY 1, 2, 3, 4;

GCS'deki verileri doğrulama

Databricks'in oluşturduğu dosyaları görmek için GCS paketini kontrol edin.

gcloud storage ls gs://$GCS_BUCKET_NAME/regional_sales_csv/

_SUCCESS ve günlük dosyalarıyla birlikte bir veya daha fazla .csv dosyası görünür olmalıdır.

5. Dataflow ile Spanner'a veri yükleme

GCS'deki CSV verilerini Spanner'a aktarmak için Google tarafından sağlanan bir Dataflow şablonu kullanılır.

Spanner tablosunu oluşturma

Öncelikle Spanner'da hedef tabloyu oluşturun. Şema, CSV dosyalarındaki verilerle uyumlu olmalı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
)"

Dataflow manifest dosyasını oluşturma

Dataflow şablonu için "manifest" dosyası gerekir. Bu, şablona kaynak veri dosyalarının nerede bulunacağını ve hangi Spanner tablosuna yükleneceğini bildiren bir JSON dosyasıdır.

Yeni bir regional_sales_manifest.json dosyası tanımlayıp GCS paketine yükleyin:

cat <<EOF | gcloud storage cp - gs://$GCS_BUCKET_NAME/regional_sales_manifest.json 
{ 
  "tables": [
    {
       "table_name": "regional_sales", 
       "file_patterns": [ 
         "gs://$GCS_BUCKET_NAME/regional_sales_csv/*.csv"
       ] 
    } 
  ] 
} 
EOF

Dataflow API'yi etkinleştir

Dataflow'u kullanmadan önce etkinleştirmeniz gerekir. Bunu

gcloud services enable dataflow.googleapis.com --project=$GCP_PROJECT

Dataflow işini oluşturma ve çalıştırma

İçe aktarma işi artık çalıştırılmaya hazır. Bu komut, GCS_Text_to_Cloud_Spanner şablonunu kullanarak bir Dataflow işi başlatır.

Komut uzun ve birkaç parametre içeriyor. Ayrıntılı bilgi:

• --gcs-location: GCS'deki önceden oluşturulmuş şablonun yolu.
• --region: Dataflow işinin çalışacağı bölge.
• --parameters: Şablona özgü anahtar/değer çiftlerinin listesi:
• instanceId, databaseId: Hedef Spanner örneği ve veritabanı.
• importManifest: Yeni oluşturulan manifest dosyasının GCS yolu.

gcloud dataflow jobs run spanner-import-from-gcs \
  --gcs-location=gs://dataflow-templates/latest/GCS_Text_to_Cloud_Spanner \
  --region=$GCP_REGION \
  --staging-location=gs://$GCS_BUCKET_NAME/staging \
  --parameters \
instanceId=$SPANNER_INSTANCE,\
databaseId=$SPANNER_DB,\
importManifest=gs://$GCS_BUCKET_NAME/regional_sales_manifest.json,escape='\'

Dataflow işinin durumu aşağıdaki komutla kontrol edilebilir:

gcloud dataflow jobs list \
    --filter="name:spanner-import-from-gcs" \
    --region="$GCP_REGION" \
    --sort-by="~creationTime" \
    --limit=1

Bu görevin tamamlanması yaklaşık 5 dakika sürer.

Spanner'daki verileri doğrulama

Dataflow işi başarılı olduğunda verilerin Spanner'a yüklendiğini doğrulayın.

Öncelikle satır sayısını kontrol edin. Satır sayısı 4.375 olmalıdır.

gcloud spanner databases execute-sql $SPANNER_DB \
--instance=$SPANNER_INSTANCE \
--sql='SELECT COUNT(*) FROM regional_sales;'

Ardından, verileri incelemek için birkaç satır sorgulayın.

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

Databricks tablosundan içe aktarılan veriler görünür olmalıdır.

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

Databricks'i temizleme

Katalog/Şema/Tablo Silme

1. Databricks örneğinizde oturum açma
2. Sol taraftaki menüden simgesini tıklayın.
3. Katalog listesinden daha önce oluşturulan retl_tpch_project öğesini seçin.

4. Şema listesinde, tpch_data oluşturulanı seçin.
5. Tablo listesinden daha önce oluşturulan regional_sales_csv öğesini seçin.
6. simgesini tıklayarak tablo seçeneklerini genişletin ve Sil'i seçin.
7. Tabloyu silmek için onay iletişim kutusunda Sil'i tıklayın.
8. Tablo silindikten sonra şema sayfasına geri yönlendirilirsiniz.
9. simgesini tıklayarak şema seçeneklerini genişletin ve Sil'i seçin.
10. Şemayı silmek için onay iletişim kutusunda Sil'i tıklayın.
11. Şema silindikten sonra katalog sayfasına geri yönlendirilirsiniz.
12. Varsa default şemasını silmek için 4-11. adımları tekrar uygulayın.
13. Katalog sayfasında, simgesini tıklayarak katalog seçeneklerini genişletin ve Sil'i seçin.
14. Kataloğu silmek için onay iletişim kutusunda Sil'i tıklayın.

Harici Veri Konumunu / Kimlik Bilgilerini Silme

15. Katalog ekranında simgesini tıklayın.
16. External Data seçeneğini görmüyorsanız External Location, Connect açılır listesinde yer alabilir.
17. Daha önce oluşturulan retl-gcs-location harici veri konumunu tıklayın.
18. Harici konum sayfasında, simgesini tıklayarak konum seçeneklerini genişletin ve Delete simgesini seçin.
19. Harici konumu silmek için onay iletişim kutusunda Sil'i tıklayın.
20. simgesini tıklayın.
21. Daha önce oluşturulan retl-gcs-credential simgesini tıklayın.
22. Kimlik bilgisi sayfasında, simgesini tıklayarak kimlik bilgisi seçeneklerini genişletin ve Delete simgesini seçin.
23. Kimlik bilgilerini silmek için onay iletişim kutusunda Sil'i tıklayın.

7. Tebrikler

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

İşlediğimiz konular

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