Geçenlerde bir iş arkadaşımla SQL'de birim testlerinin nasıl yapılacağı hakkında tartışıyordum ve fark ettim ki, henüz veri kariyerimin başlangıcında olmama rağmen, bu sorunla zaten birkaç kez karşılaştım ve her seferinde farklı bir yaklaşımım oldu. çöz onu. Öğrendiklerimi pekiştirmek ve ayrıca siz okuyuculardan ne yaptığınızı ve nasıl gittiğini öğrenmek için tüyolarımı paylaşmanın yararlı olabileceğini düşündüm. Yapalım mı?

Ama neden birim testi SQL?

Açıklığa kavuşturmak önemlidir, burada verileri test etmeyi değil, SQL mantığını test etmeyi tartışıyorum . Verileri test etmek, geçersizlik ve benzersizlik gibi şema özellikleri için varsayımların gerçekten eldeki değerler için geçerli olmasını sağlamaktır . Geleneksel bir RDBMS, tablo kısıtlamalarıyla bunu sağlar, ancak veri ambarlarında bu özelliğe sahip değiliz, bu nedenle veri kümesi üzerinden ön ve son koşulları gerçekten okumamız ve kontrol etmemiz gerekiyor.

Birim testi SQL, birim testi uygulama koduyla aynıdır: bir mantık parçasının, belirli bir girdi kümesi için beklenen çıktıyı üretmesini sağlayın. En iyi uygulamalar, ister C++ ister Python olsun, hemen hemen tüm uygulama kodunu birim testi yapmamızı zorunlu kılsa da, bunun SQL ile denendiğini nadiren görüyoruz. Neden?

Bana göre cevap, SQL yazmanın en zor kısım olmadığıdır . Çoğu zaman, veri dönüşümleri önemsiz ve tekrarlayıcıdır ve zorluk, bitleri A'dan B'ye taşımaktan çok verileri uygun şekilde modellemede yatar. Ancak, dönüşümlerde iş mantığı gerekli olduğunda, sonunda zor olan gerekli karmaşıklıkla karşılaşabilirsiniz parçalamak için - yedi yollu göz korkutucu bir JOIN'den gelen girdilerle bir sütun oluşturmak için karmaşık bir CASE-WHEN ifadesi ve neler olup bittiğini anlamak için bir doğruluk tablosunu garanti eden çok satırlı bir WHERE yan tümcesi gibi…

Deneyimlerime göre, mantığın sağlam olmasını sağlamak yalnızca manuel teste ve müşteri oturumunu kapatmaya bağlıydı; bu, SQL'in birim testi için ne kadar uygun olduğu düşünüldüğünde 1) kırılgan ve 2) neredeyse suçlu ! Uygulamalar değişken durumu kısıtlamak ve testlerde yeniden üretilebilirliği sağlamak için her türlü yapı iskelesini gerektirse de, SQL saf ve açıktır. Öyleyse neden mantığın beklendiği gibi çalıştığını doğrulamak için aynı altyapıyı kullanmıyorsunuz?

İlk deneme: analitik

SQL betiklerini test etmeye yönelik ilk girişimim, Dremel ve Powerdrill gibi dahili teknolojileri kullanarak Google'da çalışırken oldu (projelerinin günlüklerle çalışmakla ilgili adları olduğunu fark edeceksiniz ).

Yeni bir uygulama için bir analitik ardışık düzeni oluşturuyor, ülke veya grup başına aktif kullanıcılar gibi ürün yöneticileri için değerli ölçümler oluşturmak için kullanıcı günlüklerini işliyorduk. Temsili bir sorgu şöyle olacaktır:

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

Bir test çerçevesini hackleme

Daha iyisini yapmak ve betiklerimizin güvenilir olmasını sağlamak istedik, bu nedenle kendi test çerçevemizi oluşturduk:

1. Her kaynak tablonun, çok sayıda kullanıcı ve doldurulmuş sütun içeren sentetik bir CSV'si olacaktır. Her yeni test durumu için uygun kaynak tablolara satırlar eklerdik.
2. Bir test yürütüldüğünde, veri setinin tamamı veri ambarındaki geçici tablolara yüklendi.
3. Her komut dosyası için, kaynaklar metinsel olarak geçici tabloyla değiştirildi ve komut dosyası yürütülecekti.
4. Her komut dosyası için, beklenen bir çıktı CSV'si oluşturduk ve bunu gerçek çıktıyla farklılaştırarak farklılıkları bildirdik.

CSV'leri oluşturmak zaman alıyordu çünkü 1 günlük, 7 günlük ve 28 günlük pencerelerdeki aktif kullanıcı sayısını hesaplamakla ilgileniyorduk, bu nedenle yalnızca basit bir komut dosyasını test etmek için günlük olaylarını içeren birçok satır oluşturmamız gerekecekti. yeni bir boyut ekledi. Mantıklıysa, bir test için yazılan beklenen çıktıyı diğerine girdi olarak kullanmak da dahil olmak üzere mümkün olduğunca yeniden kullandık.

Bozulma

Bir süre sonra, potansiyel olarak herhangi bir sayıda başka testi etkileyebileceğiniz için, yeni bir test durumu eklemek çok kırılgan hale geldi! Test A'nın göreceli bir frekans hesaplaması gerekiyorsa ve bunun için tüm satırları COUNT(*) ile saydıysa, test B için "altın" veri kümesine yeni bir satır eklediğimiz anda test A'dan beklenen çıktı bozulur.

Uzun bir iç çekişin ardından, mermiyi ısırdık ve altın veri setini komut dosyası başına bir veri setine ayırdık . Komut dosyası başına birden fazla test durumu eklediğimiz için yine de mükemmel değildi, ancak daha yönetilebilir hale geldi. Bununla şunları öğrendik:

• Testleri mümkün olduğunca izole edin . Bir birim testinin değeri vardır çünkü tek bir bileşen varsayımı artık geçerli olmadığında bozulur. İlişkisiz bir test paylaşılan ortamı değiştirdiği için kırılması yararlı değildir.
• CSV korkunç bir dosya formatıdır . Cidden. Boş bir dizeye karşı bir NULL dizesiyle mantığın nasıl davrandığını test etmek isteseydik, yükleme adımına özel bir değer ve dönüştürme mantığı eklememiz gerekirdi. pandas.read_csv'nin , CSV'nin standardize edemediği tüm durumları işlemek için 52 argümana sahip olmasına şaşmamalı .
• Testler basitlik ve açıklıktan yararlansa da, sentetik veriler çok ayrıntılıdır ve bir testten diğerine neyin farklı olduğunu belirlemeyi zorlaştırır. Her satırın ve test durumunun amacının ne olduğunu açıklığa kavuşturmak için büyük ölçüde yorumlara güvendik (yine, CSV'de standartlaştırılmış bir şey değil).

İkinci deneme: JSON ile müşteri oturumunu kapatma

İkinci deneyimimde, Latin Amerika'nın en büyük bankası olan Itaú'da bir ekibe tahsis edilen Zup Innovation'da veri mühendisi danışmanı olarak çalışıyordum. Şirket içi bir Hadoop dağıtımıyla çalışıyorduk.

Buradaki çalışmanın türü, çoğunlukla düzleştirme ve tekilleştirme kayıtlarından oluşan analitikten çok farklıydı. Belirli bir dönüşümün çıktısının bir öncekinin çıktısına bağlı olarak artımlı olması betiklerden elde edilen ana zorluktur . Bunu genellikle iki adımda test ederdik: T0'a önceki durumu olmayan bir çalıştırma ve T1'e T0 üzerine inşa edilen bir çalıştırma adını verdik.

Daha önce olduğu gibi, yalnızca konuşlandırmadan sonra hataları bulmakta zorlanıyorduk ve geliştirme sırasında tüm köşe vakalarının ele alındığından emin olmak istiyorduk. İş analisti ekibi, her biri için bir sütunun iyi veya kötü bir değere sahip olup olmayacağını ve beklenen çıktının ne olduğunu belirten kapsamlı bir test senaryoları listesi hazırladı. Bunların yalnızca bir tablodaki açıklamalar olduğunu, ancak yine de parametreleştirilmiş bir teste oldukça yakın olduğunu unutmayın .

Asıl zorluk, bu test senaryoları listesini gerçek Hadoop girdilerine çevirmekti! İlk başta aşağıdaki süreci yaşadık:

1. Her sekmenin birinci çalıştırmadan (T0) veya ikinci çalıştırmadan (T1) bir tablo olduğu paylaşılan bir Excel dosyası oluşturduk. Her test durumu, her tabloda 0-2 satır olur.
2. Her T0 tablosunu bir CSV olarak çıktılıyoruz, kozmetik dönüşümler gerçekleştiriyoruz, bunları Hadoop'a yüklüyoruz ve SQL betiğini çalıştırıyoruz. T0 çıkışı toplanır.
3. Her T1 tablosunu bir CSV olarak çıktılıyoruz, kozmetik dönüşümler gerçekleştiriyoruz, bunları Hadoop'a yüklüyoruz ve SQL betiğini çalıştırıyoruz. T1 çıkışı toplanır.

Bir tablo üretecini hacklemek

Bu hataların neden olduğu ileri geri hareketlerden bıkmış bir şekilde, bir test durumunu açıklayan JSON dosyalarını girdi olarak alacak ve ardından ihtiyacımız olan tüm tabloları bir kerede çıkaran küçük bir sentezleyici aracı oluşturdum . İlk başta bunun mühendislik ve kalite kontrol ekipleri tarafından kullanılacağını ummuştum, ancak iş analistlerinden biri teknik olarak bilgili ve JSON'u anlıyordu ve kendi test senaryolarını bu formata dönüştürmeye yardımcı oldu! Test durumu olan bir dosya şuna benziyordu:

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

Araç, her bir test senaryosundaki tüm satırları topladı ve daha sonra Hadoop'a yüklenecek olan Companies_t0.csv, Companies_t1.csv vb. dosyaları oluşturdu. Bir hata ortaya çıktığında, JSON dosyasına gidip bunun test senaryosu kodunda bir hata olup olmadığını kontrol edebiliyorduk; bu, birçok Excel sekmesinde belirli bir test senaryosunu etkilemiş olabilecek belirli bir satırı aramaktan çok daha kolaydı. .

Bu aracın bir başka özelliği de, her satırın belirli bir şablondan sütunları "devralmasına" izin vermesiydi. Bu, yalnızca kendileriyle ilgili sütunları içerdikleri için test senaryolarını okumayı keyifli hale getirdi.

Yeni dersler

Bu aracın başarılı olduğunu söyleyemem. İlk olarak, birkaç kişi bunu benimsedi ve bunun genel sürece nasıl uyacağını mühendisler ve QA ile daha iyi iletişim kurmalıydım. İkinci olarak, yinelenen kimliklere sahip vakalar oluşturma, kopyala-yapıştır işleminde sütunları değiştirmeden bırakma veya JSON progenitörleri yerine doğrudan CSV dosyalarını düzenleme gibi yeni manuel hata fırsatları ortaya çıkardı.

Bundan şu dersleri çıkardım:

• İş, tablolara yayılan durumlarda düşünür ve kod, test senaryolarını içeren tablolarda düşünür . Bir test koşum takımı oluştururken aralarında yapılması gereken bir devrik vardır.
• Müşterinin sizin için test senaryoları yazmasını sağlamak altındır . İlgili girdilerin ve beklenen çıktıların ne olduğunu alan uzmanından daha iyi kimse bilemez! Kodun işlemesi gereken her son durum hakkında düşünmeyebilirler, ancak zorlu dönüşümler hakkında bir avuç test durumuyla içgörü sağlayabilirler. Ayrıca, onlarla ortak bir girdi-çıktı dili oluşturmak, bu uç vakaları daha sonra iletmenize yardımcı olur!
• Tüm test durumlarını aynı anda çalıştırmak, kontrol etmeyi ve hata ayıklamayı zorlaştırır . Sonuçları test durumlarına geri döndürmek ve kontrol etmek için user ID-test durumu arasında bir eşleme tutmamız gerekiyordu. Verileri "bant içi" olarak etiketleyerek test vaka numarasını kullanıcı kimliğine eklemeye çalıştık, ancak bu yalnızca geçersiz kimliklerle ilgili daha fazla sorun getirdi. Ya her durumu ayrı ayrı çalıştırmak ya da belirli bir çıktının hangi duruma karşılık geldiğini izlemenin bir yolunun olması daha iyi olurdu.
• JSON, insanlar için iyi bir format değildir. Yukarıdaki örnekte, neyin test edildiği açık değildir ve JSON, yorumların bunu açıklamasına izin vermez. Evet, bir alan kullanabiliriz "_comment": "blah", ancak bu, CSV'ye dönüştürülürken geri alınması gereken bir saldırıdır. Ayrıca, virgülleri takip etmek ve nesne tuşlarını alıntılamak biraz sinir bozucu .
• Satır şablonları okunabilirliği büyük ölçüde artırır. Testlerin çok açık ve çok az gizli mantıkla olmasını istesek de, ilginç olmayan sütunları doldurmak için bir şablon kullanmak, eldeki test durumu için gerçekten önemli olan değerler arasındaki farkları belirlemede çok yardımcı olur.

Bu üçüncü ve son deneyim, emlak şirketlerine veri bağlantısı sağlayan önceki işverenim Cherre'de yaşandı. Dremel'in ticari ürünü olan Google BigQuery'de dönüşümleri yazmak için çoğunlukla dbt'yi kullandık. Bir müşteri, mantığı büyük ölçüde değiştirmemizi (neyse ki daha basit hale getirmemizi) talep etmişti ve biz de istenmeyen herhangi bir değişikliğe yol açmayacağımızdan emin olmak istedik.

dbt modellerini yeniden kullanma

Mevcut mantık birim testli olmadığı için, basitçe girdiler oluşturan ve bunlarla hiçbir şeyin bozulmadığından emin olan bir duman testi ile başladım. Test edilen modelin bağımlılık grafiği derindi ve ~20 kaynakla bitiyordu, bu nedenle kaynak kaynağı taklit etmek baş ağrısı olurdu. Bunun yerine, uygun bir DAG kesintisi seçtim ve yalnızca ~7 ref'yi SQL'in kendisiyle oluşturulan sentetik verilerle değiştirdim :

WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

Girdileri ve çıktıları daha iyi görselleştirmek ve paylaşmak için, CTE'lerin her biri (yani, ara tabloların her biri) için betiğin bir kopyasını oluşturdum ve sonuna ekledim SELECT * FROM <cte>. Daha sonra her bir kopyayı çalıştırdım ve her ara dönüşüm için bir CSV aldım - foo.csv, bar.csv, model_x.csv, vb. insanlar, CSV formatında bazı sentetik girdiler verildiğinde mantık değişikliklerinin sonuçlarının ne olacağını.

Sınırlı kazançlar

Sonuçta bu test, mevcut ve yeni mantık için aynı girdiler kullanıldığından, mantık değişikliğiyle herhangi bir hata eklemememizi sağlamada başarılı oldu. Ayrıca, bir üretim sorununu sentetik verilerle yeniden oluşturmamıza izin vererek daha sonra hata ayıklamaya yardımcı oldu. Ne yazık ki, belirli bir amaç için inşa edildiğinden, diğer modellerde testler yazmak için onu genelleştirmeye çalışmadık.

Şimdiye kadar alınan ana dersler şunlar olmuştur:

• SQL, sentetik verileri bildirmek için en az direnç uyuşmazlığına sahip seçenektir. Sentetik verileri saf SQL'de yazarak, döküm, NULL'lar, yorumlar, okunabilirlik hakkında endişelenmemize gerek yok… çünkü dil sözdizimi zaten bunları sağlıyor!
• Referanslarla alay etmek önemlidir. SQL mantığının en karmaşık kısmı, büyük bir DAG'nin sonunda yer alır ve kaynakların her biriyle dalga geçmek zorunda kalmak ters tepebilirdi. Kaynaklarla alay ederek, tüm ara modelleri test edebileceğiz … ama hepsini test etmek istemedim, sadece ilginç olanları !
• Satırları test durumlarına göre gruplandırmak, tabloya göre gruplandırmaktan daha iyidir. Bunu önceki deneyimimde zaten öğrenmiştim, ancak SQL'de yorumlarla birlikte test senaryoları yazıldığında bile, vakaları okurken ve çıktılardan testlere geri dönerken bazı bağlamların hala kaybolduğunu yinelemek isterim.

SQL'i test etmek için mükemmel bir çözüme ulaşabilir miyiz bilmiyorum ama yine de bunun hayalini kurabiliriz:

• Test senaryoları tek başına çalışacaktır;
• Her çıktı, hangi vakadan geldiğini gösterir;
• İlgi çekmeyen sütunlar otomatik olarak doldurulabilir ve/veya göz ardı edilebilir;
• Tüm mantık yeniden kullanılır, test için herhangi bir uyarlama gerekmez;
• Bağımlılık grafiği kesme noktası keyfi olabilir;
• Test senaryoları ve sonuçları, bir alan uzmanı/iş uzmanı tarafından anlaşılabilir ve yazılabilir olmalıdır;

sonsöz

İşte bu, okuduğunuz için teşekkürler! Buraya kadar geldiyseniz, bu makalenin sizin gibi başkaları tarafından görülmesine yardımcı olmak için lütfen 10 alkış bırakın. En önemlisi, lütfen veri mühendisliğinde birim testiyle ilgili geçmiş deneyimlerinizi paylaşın, henüz yeni başlıyorum ve herhangi bir rapora açığım!

David Barer, Bruno Santos ve Rodrigo Donizetti'ye müsait olduğunuz ve bu gönderinin taslaklarını incelediğiniz için teşekkürler.