Derinlemesine Lookup Component

16. June 2010

ETL hayatım boyunca en çok kullandığım component'lerden biri olan Lookup component kısaca pipeline içerisinde gelen bir satır verisinin yanına ilişkisel bir başka veri eklemek için kullanılır. Yani, elimize ürün kodu var ancak ürünün fiyatı yoksa, ürün kodunu kullanarak fiyat tablosundan ürünün fiyatını öğrenip, ürün koduyla birlikte kaydetmek istiyorsak Lookup Component kullanabiliriz. Lookup Component bize inanılmaz bir rahatlık sağlıyor, diğer türlü join, sub query vb işlemlerle verileri kaynağında bağlayarak ilerlememiz gerekecekti. Hatta ürün kodlarıyla ürün fiyatları ayrı iki veritabanı sisteminde tutuluyorsa işte o zaman linked server vb metodolojilere girişip eğlencemize elence kattmak durumunda kalacaktık. Fazla sözü uzatmadan aslında Lookup Task'ı anlatmaya başlasam iyi olacak.

Lookup component belirli parçalardan oluşuyor aslında.

  1. Connection: Bağlantı bilgileri, hangi bağlantıyı kullanacağız? Hangi tabloda tutuluyor fiyat verilerimiz? (örn: FiyatTablosu)
  2. Columns: Eşleştirmeyi nasıl yapacağız (örn: ürün kodu üzerinden) hangi kolonları alacağız? (örn: Fiyat)
  3. Eğer fiyat bulamazsak ne yapacağız? (Örn: Başka yere bak, sıfır yaz, hata ver)

Bu ana parçalar dışında lookup içerisinde önemli olan diğer özellikler:

Cache Mode: SSIS lookup işlemine başlamadan önce referans tabloyu (Fiyatlar Tablosunu) hafızaya alıp önbellekleme yapar. (Full Cache) Böylece daha hızlı olabilir. Ancak bazen ssis paketi çalıştığı sırada referans tablosunun değerleri değişebilir. Bu durumda kısmi olarak önbellekleme yapmayı (Partial Cache) ya da hiç önbellek kullanmamayı (No Cache) seçmek durumunda kalabiliriz.

Connection Type: Verilerimizin tutulduğu yer ilişkisel bir veritabanı olabilir veya dosya sisteminde tutulan CAW uzantılı özel önbellek dosyaları olabilir. Daha detaylı bilgi için daha önce yazmış olduğum yazıya bakabilirsiniz: Cache Transform Component ve Lookup Component İçinde Cache Connection Kullanmak

No Matching Entries: Eğer SSIS referans tabloda aradığımız veriyi bulamazsa (yani ürüne ait fiyat bulamazsa) ne yapması gerektiğini söylememiz lazım. Burada hatayı umursamayıp NULL olarak hayatına devam etmesini (Ignore), hata vermesini ve hatalı satırları ayıklamasını (Redirect Rows to Error Output), Dataflow'un hata vererek durmasına yol açmasını (Fail component) veya bulunamayan kayıtları ayrıştırmasını (Redirect Rows to no match output) belirleyebiliriz.

Connection: Bağlantı bilgilerini tanımladıktan sonra referans olarak bir tabloyu tamamiyle ya da bir sql sorgusunun sonucunu kullanmasını isteyebiliriz.

Lookup Operation: Eğer aradığımız fiyatı fiyatlar tablosunda bulursa mevcut bir kolonu mu değiştirsin yoksa yeni bir kolon olarak mı eklesin onu belirleriz.

 

Dilerseniz konunun iyi anlaşılması açısından yine her zaman yaptığımız gibi bir senaryo üzerinden ilerleyelim.

Öncelikle Adventureworks2008 OLTP veritabanından [Production].[Product] tablosundaki ürünlerin bu zamana kadarki en yüksek fiyatlarını bulalım. [Production].[ProductListPriceHistory] tablosundan.

Eğer ilişkili bir fiyat bulamazsak, fiyatı sıfır olarak kabul edelim ve o şekilde veritabanına kaydedelim. Aşağıdaki ekran görüntüsü bu senaryonun sonunda erişeceğimiz hali göstermektedir.

 

OLEDB Source (Products) içerisinde Production.Product tablosunu seçelim.

 

Lookup Component (LK Product Price) özelliklerini aşağıdaki gibi belirleyelim:

Çalışma zamanı sırasında ürün fiyat tablosunun değişmeyeceğini kabul ettiğimizden dolayı önbelleklemeyi tam olarak yapalım.(Burada dikkat: 100GB'lik bir tabloyu tam önbellekleme yapma gibi bir fantaziye girmeyin, Eğer önbelleklemeyi tam olarak belirlediyseniz server'da yeterli RAM olduğundan emin olun yoksa patlar, Partial Cache kullanın o zaman) OLEDB Connection Manager kullanarak yine aynı sunucudaki veritabanındaki bir tabloya bağlanacağımızı belirleyelim ve eğer fiyat bulamazsak, "fiyatı olmayanlar" (yani eşleşmeyenler) olarak ayrıştırmasını isteyelim.

 

Bağlantı bilgilerini sağladıktan sonra bir SQL sorgusunun sonuçları içerisinde aramasını isteyelim.

SELECT [ProductID],MAX([ListPrice]) Price FROM [Production].[ProductListPriceHistory] GROUP BY ProductID

 

Eşleştirme işleminin nasıl yapıldığını burada belirliyoruz. Bizim senaryomuza göre Product tablosundaki ProductId, referans tablosundaki ProductId ile eşleştikten sonra bize Price yani fiyatı yeni bir kolon olarak getirecek.

Burada dikkat edilmesi gereken en önemli husus ise eşleştirmenin yapılacağı kolonların veritipi, uzunluk ve büyük küçük harf duyarlılığı bakımından birebir aynı olmaları gerektiğidir. Bu oldukça sık yapılan bir hatadır.

Alacağınız benzer hata mesajı ise:

Cannot map the input column, 'Name', to the lookup column, 'Price', because the data types do not match.

Ve hatta özellikle Char alanların uzunluklarının da eşit olması lazım. Her ne kadar char olarak eşleştirdiğinizde bu tip bir hata almayacaksınız ama çalışma sırasında kolonların eşleşmeyeceğinden emin olabilirsiniz, sonra tırmalar durursunuz. Benzer bir şekilde Tarih alanları üzerinden eşleştirme yaparken de dikkatli olun. Salise farkı bile eşleşmemeye neden olabilir. Bunun için tarihleri metinsel ifadelere çevirin. CONVERT(varchar(50),GETDATE(),102) gibi.

Ayrıca unutmadan eklemekte fayda var: DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE veri tiplerini eşleştirme anahtarı olarak için kullanamazsınız.

Son dakika golü olarak da: NULL değer içeren eşleştirmelerden kaçınınız: ISNULL kullanınız.

 

Olası hata mesajlar:

[LookupABCDEFG]] Error: Row yielded no match during lookup (Çözüm: Redirect Rows to No match / Error Output)

Warning: 0x802090E4 at Data Flow Task, LK ProductPrice [1]: The component "LK ProductPrice" (1) encountered duplicate reference key values when caching reference data. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE. (Çözüm: Partial ya da No Cache kullanın. Bu referans tablonuzun kendi içerisinde mükerrer kayıtlara sahip, ya da sorgunuzu değiştirin)

 

 

Derrived Column içerisinde ise fiyatı bulamadıklarımızın değerini sıfır olarak belirleyeceğimizden sıfır değerli, Currency datatipinde bir kolon ekleyelim. Burada Currency tipi oldukça önemli. Çünkü referans tablosunda fiyat alanı currency formatında, daha sonra UNION ALL ile birleştirmek istediğimizde bu bize aşağıdaki gibi bir hata verecektir:

Error at Data Flow Task [Union All [123]]: The metadata for "input column "Price" (393)" does not match the metadata for the associated output column.

Error at Data Flow Task [Union All [123]]: Failed to set property "OutputColumnLineageID" on "input column "Price" (393)".

Bu arada önemli not: Data karmaşasından dolayı biraz problem yaşarsanız her zaman Metadata viewer'a bakmanızı öneririm. Bunun için Union All component'e bağlanan Dataflow Path çizgisine çift tıkladığınızda karşınıza Dataflow Path Editor penceresi çıkacaktır:

Görüntüden anlaşılacağı gibi Price alanı DT_CY data tipinde. Bu nedenle Union ALL içerisindeki Price kolonunda birleştireceğimiz input'ların data tiplerinin tümü DT_CY olmalıdır. (Bir başka yazıda bu konuyla ilgili bişiyler anlatacağım)

Bu şekilde çalıştırdığımızda veritabanına aşağıdaki gibi bir kayıt atılacaktır:

 

Dikkat edecek olursanız burada Full Cache kullandık. Ancak dediğim gibi Kısmi Önbellekleme (Partial Cache) ya da sıfır önbellekleme (No Cache) de kullanmamız gereken durumlar olabilir. Örn: Lookup yaptığımız tablodaki veriler değişiyorsa, bu değişikliğin doğasına göre tercihlerimizi değiştiririz. Tam Önbelleklemeden farklı bir önbellekleme metodu kullanmak istersek Lookup özelliklerinde Advanced sekmesi önem kazanmaktadır.

 

Partial Cache:

Partial Cache aslında anlamsız geliyor gibi olabilir ama çok büyük verisetlerinde lookup işlemi yaparken işinize yarayacaktır. Partial cache, ilk başta hiç önbellekleme yapmaz. Yeni değerler geldikçe veritabanını sorgular ve sonraki kullanımlarda bir kez daha bakmaz eğer önbellekte varsa.)

Partial Cache içerisinde önbellekleme büyüklüğünü belirleyebilirsiniz. Yukarıda, önbellekteki veri 25MB'yı aştıktan sonra sırasıyla en az görünene göre önbelleği boşaltır. Amaç en çok kullanılan kümeye göre optimizasyon sağlamak.

Enable cache for rows with no matching entries özelliği ise aslında ilk bakışta anlamsız görünebilir ama önbelleğin belirli bir kısmını fiyatı olmayan ürünlerin hangileri olduğunu hatırlamak için kullanmaya yarar. Böylece fiyatı olmayan ürün geldiğinde sürekli veritabanını yormaz.

Partial veya No Cache'in diğer bir kullanım amacı da aslında eğer içinde bulunduğunuz dataflow aynı zamanda lookup yaptığınız tabloya veri yazıyorsa kullanılır.

Unutmadan Cache Size hesaplamak için güzel bir yöntem de var aslında: http://blogs.msdn.com/b/mattm/archive/2008/10/18/calculating-the-size-of-your-lookup-cache.aspx

Ve bonus track: (Sık kullanılan Lookup desenleri) http://blogs.msdn.com/b/mattm/archive/2008/11/22/lookup-patterns.aspx

 

Haydi sağlıcakla (:

Dataflow Components ,

Add comment




biuquote
  • Comment
  • Preview
Loading