UNNEST nedir? UNNEST yapısının kullanımı
UNNEST fonksiyonu repeated yapıdaki yani array olarak saklanan satırların normal bildiğimiz tek tek satırlardan oluşan yapıya döndürülmesi için kullanılır. Repeated yapıda bulunan satırlara sorgu attığımız zaman bu repeated satırlara tek satır gibi davranamaz. Ne demek istediğimi aşağıda yaptığım örneklerde daha net göreceksiniz. Bu da bazı sorgularda hataya neden olabilir. Bu hataları gidermek ve aranan sorgunun daha doğru çalışabilmesi adına UNNEST fonksiyonu kullanılır. Temel olarak, bir dizideki öğeleri alıp bu öğelerin her birini satır bazında genişletilmesini sağlar. Daha sonra, tabloya eklemek için orijinal satırın repeated olmayan kolonlarıyla join edilebilir. Kodları, örnekleri ve genel yapıyı GCP BigQuery üzerinden yorumlamaya çalışacağım.
Genel olarak verilerin tutulduğu tabloları yukarıdaki gibi düşünür böyle organize ederiz. Tabi ilişkisel veritabanları olarak düşündüğümüz zaman aksi halde bir çok veri saklama yöntemi mevcuttur. BigQuery bu yapının üzerinde biraz değişiklikler yaparak daha etkin kullanmaya çalışmış. Bunun yerine, veriler JSON formatına biraz daha benzeyen satırlardan oluşur; burada sütunlar tek bir bitlik veri içerebilir ve ya diziler, struct’lar ve hatta struct dizileri bile içerebilirler. Bunun sonucunda aşağıda da göreceğimiz gibi bir yapı ortaya çıkmıştır.
ilk olarak create or replace table ile tablomuzu oluşturuyoruz. Tablo oluşturduktan sonra tablonun yapısı aşağıdaki gibi gözükecektir.
Burada string yapısında bir race kolonu, sonrasında participants kolonu ise record olarak gözüküyor yani bu kolon struct olarak veri saklıyor. Participants struct yapısında, name ve splits’ler adında iki kolon tutuyor. participants ismi soluk olarak yazılmış ve repeated alanları name ve splits olarak bölünmüş. name string veri tipindedir fakat splits verileri burada repeated yani array olarak saklanmaktadır. Bu tabloya yukardaki kodlardan 1 numaralı sorguyu çalıştırdığımızda aşağıdaki gibi bir tablo dönecektir.
Tabloda çoklu satırlar var gibi görünse de BigQuery bu yapıda sadece 1 satır tutacak. row number’dan da göreceğimiz gibi sadece 1 var. Yani gördüğünüz tüm değerler aslında bir satır altında saklanıyor. Onun için biz burada diyelim “Murphy” isimli yarışçıya sorgu attığımızda Murphy’nin hangi satırını döneceğini bilemeyecektir. Bunun için bu yapıları UNNEST fonksiyonu ile tek tek satırlar haline getiririz. Daha sonra görsellerle açıklayacağım. 2 nolu sorguyu çalıştırdığımda aşadaki gibi bir sonuç dönecektir.
Bakın burada da yarışçılara göre UNNEST kullanarak her yarışçı için ayrı bir satır oluşturmuş olduk. fakat splits’leri yine repeated olarak saklanıyor. splits kolonunu da tek tek satırlar haline getirmek için 3.numaralı kodu kullanıyoruz.
Ve bu şekilde aslında genel olarak bildiğimiz gibi bir veritabanı yapısı haline getiriyoruz. Buradaki amaç aslında BigQuery’nin sorguları daha çabuk hale getirmek istemesi ve alandan tasarruf etmek istemesidir.
Eğer yukardaki kodlardan 1 numaralı sorguda olduğu gibi bir where cümlesi kurmaya çalışırsak aşağıdaki gibi hata alırız. bunun nedeni yukarda da anlatmaya çalıştığım gibi sorgu Rudisha’nın hangi değerini döndüreceğini bilemez çünkü repeated bir yapıdadır. Bu yapının doğru çalışmasını istiyorsak bu repeated yapıları tek tek satır haline getirmemiz gerekir.
2 numaralı sorgudaki gibi participant’ları UNNEST edersek. participant bazında where cümleler yazabiliriz. Burada mesela Rudisha’nın splits’lerini repeated yani array olara dönecektir. Ancak bu yapıda splits kolonu üzerinde where sorgusu atmaya çalışsaydım hata alacaktım. yani where splits > 25 gibi. Bunu bir sonraki örnekte doğru şekilde yapıp sonuçlarını gözlemleyebileceğiz. 2 numaralı sorgunun döndüğü sonuç aşağıdaki gibi olacaktır.
Yukarda da anlattığım gibi splits kolonuna where sorgusu atabilmek için onu da UNNEST etmemiz gerekiyor. 3 numaralı sorguda bunu yaptık ve sonuç aşağıdaki gibi oldu. Burada where splits > 25 diyerek splits kolonu 25 değerinden büyük olan bütün satırları döndürmüş olduk. Bu sorguyu önceki örneklerdeki tablolarda UNNEST olmayan yapılarda kullanmaya çalışsak hata alacaktık.
UNNEST fonksiyonun tersi işlevi gördüğünü varsayabileceğimiz array_agg() fonksiyonunu da bir sonraki yazımda anlatacağım. UNNEST nasıl repeated alanları tek tek satır haline getiriyorsa array_agg da kolon değerleri bazlı repeated yani array haline getiriyor. Son resimdeki tekrar eden “Rudisha” değerlerini bir satırda repeated splits’leri ilk halindeki yapıya dönüştürmeye yarar.
