Google BigQuery'yi Kullanmak Ne Zaman Mantıklı?

Kariyerim boyunca, farklı türde raporlar ve çizelgeler için karmaşık analitik sorgular yazmak zorunda kaldım. Çoğu zaman, tarih, hafta, üç aylık dönem vb. bazında toplanan verileri görüntüleyen bazı grafiklerdi. Genellikle bu tür raporlar, müşterilerin eğilimleri belirlemelerine ve işlerinin yüksek düzeyde nasıl performans gösterdiğini göstermelerine yardımcı olmak için oluşturulur. Ancak veri bilimcileri ve mühendislerinin büyük bir veri kümesine dayalı çok daha kapsamlı bir rapor oluşturması gerektiğinde ne olur?

Raporun küçük bir veri kümesine dayanması durumunda, görev ilişkisel bir veritabanı altında bir SQL sorgusu yazılarak çözülebilir. Bu adımda, sorgu yazmanın temellerini ve bunların nasıl daha hızlı ve verimli hale getirileceğini bilmek önemlidir. Ancak, bazen rapor daha büyük bir veri kümesine (örneğin bir tabloda milyonlarca ve daha fazla satır) bağlıdır, ancak rapor girdi değişkenlerine (parametrelere) bağlı değildir veya değerlerin sayısının oldukça az olduğunu görebilirsiniz. Bu tür senaryolarda, bir SQL sorgusu yavaş olabilir, bu nedenle kullanıcıların sorgu yürütülene kadar beklemesi optimal olmaz. Bu gibi durumlarda en yaygın uygulama, önceden bir sorgu çalıştırmaktır - istemci bir rapor istemeden önce.

Ayrıca, bazı önbelleğe alma işlevlerinin uygulanmasını gerektirir, böylece istemci gerçek zamanlı olarak bir sorgu çalıştırmak yerine önbellekten veri alabilir. Bu yaklaşım, gerçek zamanlı verileri göstermenize gerek kalmaması koşuluyla mükemmel şekilde çalışır. Bir saat hatta bir gün önce hesaplanan verileri gösterebilir. Bu nedenle, gerçek rapor/grafik, gerçek zamanlı verilere dayalı değil, önbelleğe alınmış veriler kullanılarak gösterilir.

Google BigQuery'ye Dönmek

İlaç endüstrisinde analitik bir proje üzerinde çalışırken, posta kodunu ve ilaç adını giriş parametreleri olarak alan çizelgelere ihtiyacım vardı. Ayrıca Amerika Birleşik Devletleri'nin belirli bölgelerindeki ilaçlar arasında bazı karşılaştırmalar göstermem gerekiyordu.

Analitik sorgu çok karmaşıktı ve Postgres sunucumuzda (16 GB RAM'li dört çekirdekli CPU) yaklaşık 50 dakika çalıştı. Sorgu, posta kodlarını ve ilaçları giriş parametreleri olarak aldığından, önceden çalıştırıp sonuçları önbelleğe alamadım, bu nedenle binlerce kombinasyon vardı ve hangi müşterinin seçeceğini tahmin etmek imkansızdı.

Tüm giriş parametresi kombinasyonlarını çalıştırmayı denemek istesem bile, veritabanım büyük olasılıkla çökerdi. Bu yüzden farklı bir yaklaşım seçmenin ve kullanımı kolay bir çözüm seçmenin zamanı gelmişti. Bu çizelge müşteri için önemliydi, ancak müşteri mimaride büyük değişiklikler yapmaya veya tamamen başka bir DB'ye geçmeye hazır değildi.

Söz konusu projede birkaç farklı yaklaşım denedik:

• Sunucunun dikey ölçeklendirilmesi (Postgres sunucusuna RAM ve CPU eklenmesi)
• Amazon Redshift ve diğerleri gibi alternatif DB'leri kullanma.
• Ayrıca bir NoSQL çözümü araştırdık, ancak bunların çoğu oldukça karmaşıktır ve mimaride pek çok değişiklik gerektirir, bunların çoğu istemci için çok büyük olurdu.

Sonunda Google BigQuery'yi denedik. Beklentilerimizi karşıladı ve müşterinin onaylamaya isteksiz olacağı büyük değişiklikler yapmadan işi halletmemize izin verdi. Ancak Google BigQuery nedir ve nasıl performans gösterir?

BigQuery, büyük veri kümeleri altında karmaşık analitik SQL tabanlı sorgular çalıştırmanıza olanak tanıyan REST tabanlı bir web hizmetidir. Verileri BigQuery'ye yükledikten ve Postgres'te yaptığımızla aynı sorguyu yürüttükten sonra (sözdizimi ürkütücü bir şekilde benzer), sorgumuz çok daha hızlı çalışıyordu ve tamamlanması yaklaşık bir dakika sürdü. Sonuç olarak, sadece farklı bir hizmet kullanarak 50 kat performans artışı elde ettik. Diğer DB'lerin aynı performans kazancını sağlamadığını belirtmekte fayda var ve cömert olalım ve sadece yakın bile olmadıklarını söyleyelim. Dürüst olmak gerekirse, rakamlar herhangi birimizin umduğundan daha iyi olduğu için BigQuery'nin sağladığı performans artışından gerçekten etkilendim.

Buna rağmen, BigQuery'yi dünyanın en iyi veritabanı çözümü olarak tanıtmam. Projemiz için iyi çalışsa da, günlük tabloda sınırlı sayıda güncelleme, istek başına veri boyutuyla ilgili sınırlamalar ve diğerleri gibi hala birçok sınırlamaya sahiptir. BigQuery'nin ilişkisel bir veritabanının yerini almak için kullanılamayacağını ve basit CRUD işlemleri ve sorguları için değil, analitik sorgular çalıştırmaya yönelik olduğunu anlamanız gerekir.

Bu yazıda, gerçek dünya kullanım senaryoları için Postgres (en sevdiğim ilişkisel veritabanı) ve BigQuery kullanarak karşılaştırmaya çalışacağım. Ayrıca, yol boyunca birkaç öneride bulunacağım, yani BigQuery'yi kullanmanın gerçekten ne zaman mantıklı olduğu konusundaki fikrim.

Örnek veri

Postgres ve Google BigQuery'yi karşılaştırmak için her ülke için ülke, yaş, yıl ve cinsiyete göre gruplandırılmış herkese açık demografik bilgiler aldım (aynı verileri bu bağlantıdan indirebilirsiniz).

Verileri dört tabloya ekledim:

1. populations
2. locations
3. age_groups
4. populations_aggregated

Son tablo, yalnızca önceki üç tablodan toplanan verilerdir. İşte DB şeması:

Sonunda bulduğum populations tablosu 6,9 milyondan fazla satır içeriyor. Çok fazla değil ama testim için yeterliydi.

Örnek verilere dayanarak, gerçek hayattaki analitik raporlar ve çizelgeler oluşturmak için kullanılabilecek sorgular oluşturmaya çalıştım. Bu yüzden sonraki raporlar için sorgular hazırladım:

1. ABD'deki nüfus yıllara göre toplanmıştır.
2. En büyük ülkelerden başlayarak tüm ülkeler için 2019'daki nüfus.
3. Her yıl için ilk beş "en eski" ülke. "En eski", 60 yaş üstü kişilerin toplam nüfus içindeki yüzdesinin en yüksek olduğu ülkeleri ifade eder. Sorgu, her yıl için beş sonuç vermelidir.
4. Erkek ve kadın nüfus arasındaki farkın en büyük olduğu, yıllara göre toplanmış ilk beş ülke.
5. “En yaşlı” ülkelerden “en genç” ülkelere kadar her yıl için ülke başına medyan (ortalama) yaşı alın.
6. Her yıl ilk beş “ölmekte olan” ülkeyi bulun. “Ölmek”, nüfusun azaldığı (nüfusun azaldığı ülkeler) anlamına gelir.

1, 2 ve 6 numaralı sorgular oldukça kolay ve anlaşılır, ancak 3, 4 ve 5 numaralı sorguları yazmak o kadar kolay değildi - en azından benim için. Arka uç mühendisi olduğumu ve karmaşık SQL sorguları yazmanın tam olarak benim uzmanlık alanım olmadığını, bu nedenle daha fazla SQL deneyimine sahip birinin muhtemelen daha akıllı sorgular oluşturabileceğini unutmayın. Ancak şu anda Postgres ve BigQuery'nin aynı sorguları aynı verilerle nasıl işlediğini kontrol etmemiz gerekiyor.

Toplamda 24 sorgu oluşturdum:

• 6, toplu olmayan tablolar kullanan Postgres DB için ( populations , locations , age_groups grupları)
• populations_aggregated tablosunu kullanan Postgres DB için 6
• Birleştirilmiş ve toplanmamış tabloları kullanan BigQuery için 6+6 sorgu.

Basit (#1) ve karmaşık #5 sorgularının karmaşıklığını anlayabilmeniz için birleştirilmiş veriler için 1 ve 5 numaralı BigQuery sorgularını paylaşmama izin verin.

Yıllar sorgusuna göre toplanan ABD'deki nüfus:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

En eskiden en küçüğüne sıralanmış olarak her yıl için ülke başına medyan yaş sorgusu:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Not: Tüm sorguları bitbucket depomda bulabilirsiniz (bağlantı makalenin sonundadır).

Test sonuçları

Sorguları çalıştırmak için iki farklı Postgres sunucusu kullandım. İlki, bir SSD sürücüsü tarafından desteklenen 1 CPU çekirdeğine ve 4 GB RAM'e sahiptir. İkincisi 16 CPU çekirdeğine, 64 GB RAM'e sahip ve ayrıca bir SSD sürücüsü kullanıyordu (ikinci sunucuda 16x CPU ve RAM potansiyeli var).

Ayrıca, testin çalıştırılması sırasında veritabanlarında herhangi bir yük olmadığına dikkat edin. Bunları yalnızca sorguları çalıştırmak için oluşturdum. Gerçek hayattaki durumlarda, diğer sorgular aynı anda çalışabileceğinden sorgular daha uzun sürer ve ayrıca paralel olarak çalışan sorgular tabloları kilitleyebilir. Sorgu hızını kontrol etmek için pgAdmin3 ve BigQuery web arayüzünü kullanıyordum.

Testimde şu sonuçları aldım:

Bu sonuçları sorgu 1 ve sorgu 5 için bir çubuk grafikte göstereyim.

Not: Postgres veritabanı ABD tabanlı sunucuda bulunuyordu ve ben Avrupa'da yaşıyorum, bu nedenle Postgres'in veri aktarımında ek bir gecikme oldu.

BigQuery Performansı ve Sonuçları

Aldığım sonuçlara dayanarak, aşağıdaki sonuçları çıkardım:

• Postgres'leri dikey olarak ölçeklendirme durumunda, 16x kez bile, tek bir sorguyu çalıştırmada sadece %10-25 performans veriyor. Başka bir deyişle, yalnızca bir CPU çekirdeğine ve 4 GB RAM'e sahip bir Postgres sunucusu, 16 CPU çekirdeği ve 64 GB RAM'e sahip sunucu için gereken süreye çok benzer sürelerde sorgular çalıştırıyordu. Elbette, daha büyük sunucular çok daha büyük veri kümelerini işleyebilir, ancak bu, sorgu yürütme süresinde pek bir gelişme sağlamaz.
• Küçük tablolarla Postgres birleşimleri için ( locations tablosu yaklaşık 400 satıra ve age_groups 100 satıra sahiptir), tek bir tabloda bulunan toplu veriler altında çalıştırılan sorgulara kıyasla çok büyük bir fark sağlamaz. Ayrıca, bir ila iki saniye süren sorgular için iç birleşimli sorguların daha hızlı olduğunu, ancak uzun süredir devam eden sorgular için durumun farklı olduğunu buldum.
• BigQuery'de birleşimlerle durum tamamen farklıdır. BigQuery birleştirmeleri sevmez. Birleştirilmiş ve toplanmamış verileri kullanan sorgular arasındaki zaman farkı oldukça büyüktür (3 numaralı ve 5 dolarlık sorgular için yaklaşık iki saniyeydi). Bu, BigQuery için istediğiniz kadar alt sorgu yapabileceğiniz, ancak iyi performans için sorgunun bir tablo kullanması gerektiği anlamına gelir.
• Postgres, basit toplama veya filtreleme veya küçük bir veri kümesi kullanan sorgular için daha hızlıdır. Postgres'te beş saniyeden az süren sorguların BigQuery'de daha yavaş çalıştığını buldum.
• BigQuery, uzun süredir devam eden sorgular için çok daha iyi performans gösteriyor. Veri kümesi boyutundaki fark arttıkça, bu sorguların tamamlanma süresindeki fark da artacaktır.

BigQuery Kullanmak Mantıklı Olduğunda

Şimdi, bu makalede tartışılan temel soruna geri dönelim: Google BigQuery'yi gerçekten ne zaman kullanmalısınız? Sonuçlarıma dayanarak, aşağıdaki koşullar karşılandığında BigQuery'yi kullanmanızı öneririm:

• İlişkisel bir veritabanında beş saniyeden fazla çalışan sorgularınız olduğunda kullanın. BigQuery fikri, karmaşık analitik sorgular çalıştırmaktır; bu, basit toplama veya filtreleme yapan sorguları çalıştırmanın bir anlamı olmadığı anlamına gelir. BigQuery, büyük bir veri kümesi kullanarak çalışan "ağır" sorgular için uygundur. Veri kümesi ne kadar büyükse, BigQuery kullanarak performans kazanma olasılığınız o kadar artar. Kullandığım veri kümesi yalnızca 330 MB'dı (megabayt, gigabayt bile değil).
• BigQuery birleştirmelerden hoşlanmaz, bu nedenle daha iyi yürütme süresi elde etmek için verilerinizi tek bir tabloda birleştirmeniz gerekir. BigQuery, sorgu sonuçlarının yeni bir tabloda kaydedilmesine olanak tanır; bu nedenle, yeni bir toplu tablo oluşturmak için tüm verilerinizi BigQuery'ye yükleyin, tüm verileri birleştirecek bir sorgu çalıştırın ve yeni bir tabloya kaydedin.
• BigQuery, verilerin sık sık değişmediği ve yerleşik önbelleğe sahip olduğu için önbelleği kullanmak istediğiniz senaryolar için iyidir. Ne anlama geliyor? Aynı sorguyu çalıştırırsanız ve tablolardaki veriler değişmezse (güncellenirse), BigQuery yalnızca önbelleğe alınmış sonuçları kullanır ve sorguyu yeniden çalıştırmayı denemez. Ayrıca BigQuery, önbelleğe alınmış sorgular için ücret almıyor. Not: Önbelleğe alınmış sorguların bile sonuçları döndürmesi 1-1.2 saniye sürer.
• İlişkisel veritabanınızdaki yükü azaltmak istediğinizde BigQuery'yi de kullanabilirsiniz. Analitik sorgular "ağırdır" ve bunları ilişkisel bir veritabanı altında aşırı kullanmak performans sorunlarına yol açabilir. Bu nedenle, sonunda sunucunuzu ölçeklendirmeyi düşünmek zorunda kalabilirsiniz. Ancak BigQuery ile bu çalışan sorguları bir üçüncü taraf hizmetine taşıyabilirsiniz, böylece bunlar ana ilişkisel veritabanınızı etkilemez.

Son olarak, BigQuery'yi gerçek hayatta kullanma hakkında birkaç kelime daha. Gerçek dünya projemizde, raporların verileri haftalık veya aylık olarak değişiyordu, bu nedenle verileri manuel olarak BigQuery'ye yükleyebiliyorduk. Ancak verileriniz sık sık değişiyorsa, ilişkisel veritabanınız ile BigQuery arasında veri senkronizasyonu yapmak o kadar kolay olmayabilir ve bu, akılda tutulması gereken bir uyarıdır.

Bağlantılar

Bu makalede kullanılan örnek verilere buradan, CSV formatındaki sorgulara ve verilere ise buradan ulaşabilirsiniz.