Azure Synapse ve SQL Pools

 

Azure Synapse ve SQL Pools

Azure Synapse Analytics,Microsoftun big data ve klasik DWH yapısını bir araya getirdiği bir servisi.Bunun içerisinde Sparkda var,SQL’de var,ADF’de var.Bir lake üzerine kurulu bir yapı olduğundan diğer iş birimlerininde bağlanıp datalar üzerinde işlem yapması veya machine learning üzerine bir servisin buradaki datalara erişimi son derece kolay.

 

 

 

Ölçeklenebilir olması bu kadar farklı servisin ayakta olması ve senkronize bir şekilde bir biri ile çalışmasıyla sizin uğraşmamanız bu servisi seçmeniz için yeterli gerçekten.

Çok büyük datalarda Spark dönüşümleri ve sourcedan data aktarımları için ADF gerçekten iyi.DB tarafında ise nodelar üzerinde bir mimari ve columnstore bir DB olması performansı çok arttırıyor.Elbette bunun bir dezavantajı da var klasik columnstore DB’lerde olduğu gibi update-delete komutları uzun sürebiliyor ama ben kendim çok ciddi sorunlar yaşamadığımı söylemeliyim.Ayrıca tablo seçimleri ve data skew konuları komplexleri arttıran konular klasik DWH’lara göre.Artık detaya inebiliriz sanırım.

 

Azure Synapse Analytics içerisinde iki tip SQL pool seçeneği bulunmaktadır.Biz her ne kadar Dedicated üzerine konuşacak olsakda serverlessda yerine göre kullanılabiliyor.

 

Aşağıdaki resim üzerine konuşacağız ama ilk bilmeniz gereken Dedicated SQL pool 7/24 sizin için ayrılmış başkasının kullanamadığı bir kaynak.Yani sanki clouddda bir makine satın almışsınız gibi düşünebilirsiniz.

Serverless ise siz ne zaman sorgu gönderirseniz o zaman ihtiyacınız kadar olan kaynağı genel havuzdan çekip sizin işinizi bitirip sonra başkasının işlerini yapmaya devam eden kaynaklar olarak düşünebilirsiniz.

Resmi incelemeye başlayalım çünkü bize genel mimariyi anlatacak aslında.Gördüğümüz ilk şey her iki mimaride de Node’lar üzerinde bir yapı kurulduğu.Yani biz sorguyu gönderdiğimizde Control Node uygun Compute Node’ları seçip işi dağıtıyor.

Serverless üzerinde konuşup hemen asıl konumuza dönelim şimdi.Control Node sizin sorgu isteğinizi aldıktan sonra sorgunuzun kullanacağı kaynak miktarını ölçüp ona göre node sayısını ayarlar ve sorgunuzu döndürür.Bunu genelde dedicated SQL pool seçimi yapmamış datayı Lake veya Storage üzerinde file formatta tutan firmalar kullanabiliyor.File’ı sanki tabloymuş gibi from kısmına koyup sonra SQL yazdığınızı düşünün😊Bunun içinde OPENROWSET fonksiyonu ile ve değişmekle beraber genelde parquet formatındaki dosyalar üzerinde işlem yaparlar.

Bunun dezavantajı şudur çok büyük kaynak isteyen sorgu gönderirseniz çok büyük bir maliyet ile karşılaşabilirsiniz.Burada kullandıkça öde mantığı olduğundan mümkün olduğunca az sorgu göndermek istersiniz.Hatta power bi veya başka bir bi toolu bağlanacaksa import mode yapmalısınızki her seferinde gelip sorguyu çalıştırmasın maliyet çıkarmasın.

Şunu unutmayın Serverless SQL Pool her Synapse içinde hazır kurulu gelir,sizin kurulum yapmanıza gerek yoktur.Ayrıca kullanmadığınız sürece hiç bir maliyet çıkarmaz açıp kapamaya çalışmayın diğer servisler gibi.

Ayrıca serverless üzerinde hiç bir data saklayamacağınızı unutmayın sadece metada saklayabilirsiniz.Yani tablo veya materilazed view yaratamazsınız ama external table veya view yaratabilirsiniz çünkü bunlar datayı lakede tutmaya devam ederken metadayı serverless üzerinde tutabilir.

Serverless üzerine çok bile konuştuk bence artık Dedicated SQL Pool zamanı😊

 

 

Dedicated SQL Pool kurulumu yapacağınız zaman size bunun kaç node olması gerektiği sorulacaktır.Bunu seçmeden önce şunu bilmemiz lazım.MPP mimarisinde Control Node ve Compute Node’lar var.Control Node tek bir tane ve bu sorguları uygun Compute Node’lara dağıtıp döndürmekle ilgileniyor kendi üzerinde hiç bir işlem gerçekleşmiyor.Compute Node ise işin olduğu Node’lar aslında sorgularınız bunlar üzerinde hesaplanıp sonuç dönüyor.Haliyle ne kadar çok Compute Node olursa o kadar fazla paralel çalışıp yük dağıtılabilir oluyor.Buda performansı arttıryor.Fakat fazla Compute Node için daha yüksek servis seviyesi seçiyorsunuz bu da maliyet demek oluyor.

DW100C ile DW30000C arasında servis seviyesi seçebiliyorsunuz bu seçimler arttıkça Node sayısı ve Ram artıyor.Burada bilmemiz gereken bir kavram daha ortaya çıkıyor o da Distribution.

Synapse siz hangi servis seviyesinde çalışırsanız çalışın dataları 60 farklı distribution üzerinde tutuyor aynı zamanda sorgularıda 60 farklı distribution üzerinde çalıştırıyor.60 farklı parallelin sonucu birleştirilip döndürülüyor.Haliyle sorgu süresi en uzun distribution’ın çalışma süresi+birleştirme süresi kadar oluyor.Bu da data skew konusu buna değineceğiz.

Servis leveliniz arttıkça Compute Node 1’den 2’e,3’e max 60’a kadar yükselecektir.Örneğin 2 compute node için her bir node üzerinde 30 distribution olacaktır.Çünkü toplamda her zaman 60 distrbution var.60 Node seçtiğinizde ise her bir node’a 1 distribution düşecek ve hızı tahmin edebilirsiniz ve maliyeti de elbette 😊

Daha iyi anlamak isterseniz Node’ları fiziksel bilgisayarlar distrbutionları mantıksal bölmeler gibi düşünebilirsiniz.Dolayısıyla örneğin siz DWH100C de 1 bilgisayarda 60 işlemi aynı anda yapıp birleştirip döndürmeye çalışırken DWH30000C’de 1 bilgisayar 1 işlem yapar.Ayrıca dönüşüm işlemleri ciddi RAM harcadığından aynı anda çok fazla sorgu talebi gelirse hak verirsiniz sıraya almak zorunda kalacaktır.Örneğin DW100C için u sayı 4 sorgu ve en az %25 RAM boşta olmalı iken DW1000C için 32 sorgu %3 RAM seviyelerine düşebiliyor.

DWH200C’den DWH300C’ye geçişde bile performansımız ciddi artmıştı.Maliyet şirketinizin gücüne göre değişecektir elbette.Hesaplama aracından hesaplayabilrisiniz.

Burada şunu söylemekte lazım.Bazı firmalar SQL Pool kullanılmayan saatlerde bunu kapatıp açan mekanizmalar kuruyor çünkü Sql pool full performans kullanının veya kullanmayın aynı parayı kesiyor.

 

 

 

 Konuyu çok uzattım sanırım hadi SQL Pool tablo yapılarına girelim😊

Node ve Distribution’dan bahsettik.Buna göre tablolarımızın tiplerini seçmemiz gerekiyor.

·       Round Robin

·       Hash

·       Replicated

Round Robin

Genelde ODS tabloları için kullanılan bir tiptir.Çünkü özelliği olabildiğince hızlı bir şekilde datanın tabloya yazılmasıdır.Mümkün olabildiğince eşit bir şekilde tüm distributionlara datayı dağıtır.Ama datalar hızlı yüklenirken rastgele dağıldığından join işlemleri veya select işlemleri bu tablolar üzerinde çok daha yavaş olur bunlar asla nihai tablo gibi kullanılmamalıdır.ODS ve staging için sadece unutmayın.

Replicated

Tüm distributionlarda 1-1 aynı datayı yaratır tabloyu bölmez.Bu yüzden başka distributiondan veri getirmekle vakit kaybetmez kendi üzerindeki ile işini görür o yüzden çok hızlıdır.Ama aynı data 60 kere yazıldığından sadece küçük tablolar için kullanılmaldıır.Bunlar max 2-3 milyonluk dimension tablolar olabilir 100milyonluk dimensionlar için bu methodu kullanmayın!.

 

 

 

 

Hash

Büyük tablolar için en iyi saklama methoduur.Fact ve büyük dimensionlar bu tipte bir tabloda saklanır her zaman.Çünkü select ve join işlemleri çok hızlıdır.Her bir satır bir distributionda saklanır bu methodda ama burada şöyle bir soru ortaya çıkıyor hangisini nerede olacak nasıl biliyor?Bir Hash  fonksyion aracılığı ile bunu yapıyor.Örneğin siz FirmaID verdiniz buna göre hashle dediniz FirmaId 1 hep 1 nolu distrubitona gidiyor FirmaID 55 40 nolu distrbıutiona gidiyor.Fakat burada şöyle bir sorun var sizin datanızın %75’i firmaId 1 diyelim.Bu durumda %75’i 1 nolu distirbutiona gidiyor ve diğer distributionlar boş yatıyor.İşte buda data skew dediğimiz kavramı ortaya çıkarıyor.Yani Distrbutionlar arası data farkı.Bu tip durumlarda ekstra sütunlar vererek ya da daha normal dağılım gösteren tek bir sütun seçerek hash değerine ulaşmanız lazım.Açıkçası burada biraz deneme yanılma yapacaksınız en iyi performansı bulmak için.Şuda var ki mümkün olduğunca joinlerde kullacağınız veya aggregationda group by da gelecek sütunlar üzerinden hash değeri oluşturmak isteyeceksiniz ki performans artılarınız olsun.Genelde primarykey gibi sütunları seçmemeye dikkat edin hepsi unique olması değil tablonun ilgili sütun bazında 60’a en yakın eşit dağılımını istiyoruz çünkü.

Data skew değerine ulaşmak için var olan hazır sorgular inceleyebilirsiniz buraya yazıp yer kaplatmayayım.Aynı zamanda bir sorgunun hangi distributionda ne kadar veri işlediğinide görebilirsiniz.

 

Synapse’de işlem yaparken en önemli şeylerden biri data_skew olayı buna çok özen gösterin ve ne yaparsanız yapın DWH100C ile çokda efektif bir DWH yaratamazsınız biraz daha yüksek bir servis seçebilirsiniz

 

 

 

 

 

SCD(Slowly Changing Dimension) Nedir?Nasıl Uygulanır?

 

SCD(Slowly Changing Dimension) Nedir?Nasıl Uygulanır?

Bu tablo tipi hemen hemen tüm DWH yapılarında yer alır çünkü bizim DWH yapma sebeplerimizden biri de geçmişi saklamaktır.Daha doğrusu geçmişi geçmişin verileri ile yorumlamaktır.

 

SCD’ye neden ihtiyaç peki?Diyelim ki sizin bir ürün tablonuz var ve ürünlerinizin birim fiyatları zaman zaman değişebiliyor ve diyelim ki siz tutarı bulmak için satışlar tablosundaki ürün adedi ile birim fiyatını çarparak toplam tutarı bulmak istiyorsunuz.Bu durumda ürün tablosu her zaman son değeri tuttuğundan sanki 5 yıl öncede aynı fiyata satmışsınız gibi görürsünüz.Halbuki her yıl-ay farklı fiyatlandırma söz konusudur.İşte SCD ile bu karışıklığı çözebiliyoruz.

Sadece buda değil elbette iyice oturması için örnekleri arttırabiliriz diyelim ki sizin çalışanlarınız departman değiştiriyor bu durumda bunu tutmak isteyebilirsiniz veya servis güzergahları bir kişi değiştiriyor ise eskisini tutmak isteyebilirsiniz vb.

Burada dikkat ederseniz hep koca bir tablodaki tek bir sütundan bahsediyorum çünkü bizim için kritik bilgi o sütun-lar.Ürün tablosunda bizim için birim fiyat kritikti ama çalışan tablosundan departman oldu.Elbette tek sütun değil sizin için birden çok sütun kritik olabilir bu önemli değildir sadece kritik sütunları belirlemek burada önemlidir.Kritik sütundan kastım elbette geçmişini saklamak istediğiniz sütunlardır.

Farkındaysanız bu örnek verdiğimiz 3 tabloda aslında bir dimension tablo yani ölçüm değil ölçümün içeriği hakkında veri tutan tablolar.Fakat zamanla yavaş yavaş içerisindeki veri değişiyor.Aslında Slowly Changing dediğimiz kavramın bir karşılığı oluyor ve aynı zaman bir dimension tablo.Peki her dimension tablo değişiyor aslında neyin SCD olacağına nasıl karar veriyoruz diye sorarsak cevap geçmişi sizin için değerli olan tablolar olacaktır.

Peki bu geçmişi nasıl tutacağız ve sonrasında buna nasıl erişeceğiz?

Öncelikle SCD’nin bir çok tipi var şuanlık 6 😊

Ama bizim için en önemlisi her zaman Type 2 olacaktır bunu unutmayın.Type 2 hemen hemen her zaman işinizi görecektir.Açıkçası diğerlerini hiç yapma ihtiyacı hissetmedim ben ama sizin öyle bir caseniz olurki type 6 mantıklı olur o zaman o şekilde yapabilirsiniz.

 

Type 2 da dimension tablolarınıza 3 sütun eklersiniz.

·        Start Date

·        End Date

·        Is Current

Start Date:Bir önceki kaydın bitiş tarihi +1(etl çalıştığı gün)

End Date :Sonraki kaydın başlangıç tarihi-1(Etl çalıştığı gün-1)

Is Current:Her zaman son kayıtta 1 önceki kayıtlarda 0

Burada önemli nokta şudur her ürünün bir önceki veya sonraki kaydı olmak zorunda değil özellikle ilk yaptığınız anda hepsinin aslında tek bir kaydı vardır tabloda.Bu durumda Start Date 1900-01-01,End Date 2999-12-31 şeklinde girilir genelde!

Ve her zaman son eklenen kaydın bitiş tarihi 2999-12-31 olarak girilir çünkü zaten yeni bir kayıt geldiğinde buradaki değeri ezecek ve hangi gün geldiyse o günün değeri basılacaktır endişe etmeyin.

 

Hemen örnek bir tablo yaratalım diyelim Ürün tablosu olsun.

SK	ProductId	ProductName	ListPrice	StartDate	EndDate	IsCurrent
1	1	A Ürünü	10	1.01.1900	31.12.2999	1
2	2	B Ürünü	10	1.01.1900	14.08.2024	0
3	2	B Ürünü	20	15.08.2024	31.12.2999	1
4	3	C Ürünü	30	1.01.1900	31.12.2999	1
5	4	D Ürünü	50	1.01.1900	31.12.2999	1
6	5	E Ürünü	60	1.01.1900	31.12.2999	1
7	6	F Ürünü	70	1.01.1900	14.08.2024	0
8	6	F Ürünü	60	15.08.2024	20.08.2024	0
9	6	F Ürünü	80	21.08.2024	31.12.2999	1
10	7	G Ürünü	20	1.01.1900	31.12.2999	1

 

Bu tabloyu inceleyelim şimdi bakın A Ürünü tek bir kayıt var fiyatı hiç değişmemiş bu yüzden başlangıç bitiş tarihleri maximum değerde ve iscurrent 1.

Fakat B Ürününe baktığımızda 14.08.2024 tarihinde bir değişiklik olmuş fiyat 10 ken 20 olmuş.İşte biz bu tarihten önceki satışlar için 10 lira ile sonrası için 20 lira ile çarpacağız tutarı bulmak için.

Veya F Ürünü 3 kez değişmiş bu n kez değişebilir hiç önemli değildir.

Tüm ETL araçlarında SCD type 2 için hazır yöntemler vardır fakat yok veya kendiniz yazmak istersenizde row by row bir loop ile değil bulk olarak bunu yapmaya dikkat edin.

PL/SQL,T-SQL ile de yapabilirsiniz bu süreci bilginiz olsun.

Peki dikkatinizi çeken bu SK sütunu nedir?

SK(Surrogate Key)’in kısaltmasıdır.Dikkat ederseniz ProductId çokluyor çünkü biz ProductID bazında geçmişi saklıyoruz.Bu durumda bize unique bir key lazım işte bu SK oluyor.Özellikle DWH ortamlarında çok sık kullanılan bir kavramdır.SCD tablolarında olmazsa olmazdır!!!

SK üretmek için auto increment sütun tanımlayabilirsiniz veya sequence’den değer getirtebilirsiniz.

Unutmadan söylemiş olalım SCD tabloları asla ama asla truncate edilemez!!

SCD tablomuzu type 2 formatta tutmaya başladık diyelim bitti mi?

SCD anlamında evet ama fact anlamında hayır.

Raporlama katmanında her tarihi o zamanki değer ile eşleştirmek için iki yönteminiz vardır.

Ya fact tablonuzda ProductID ve raporlama tarihi vardır bunları aşağıdaki joine benzer bir şekilde joinlersiniz BI Toolıunuzda.

Select * from Fact

inner join scd on fact.product_id=scd.product_id and fact.report_Date between scd.start_date and scd.end_date

Ya da direkt fact üzerine SCD’nin SK değerini basarsınız.SK unique olduğu için bir daha joinlerde bu kadar uğraşmazsanız hatta Power BI’da mecbursunuz mesela çünkü ikili joinlere izin vermiyor.

Peki fact üzerine SK değerini nasıl basacaksınız?

Aslında yukardaki joinin bir benzeri fact’inizi dolduran ETL mappinginizde yapacaksınız ve o join sonucu dönen SK değerini fact’e basacaksınız.

 

Baştan uca bir SCD işlemi böyle oluyor fakat bazen Snapshot fact tablo gibi düşünülebiliyor.Buradan raporlama yapamazsınız bunu unutmayın.Örneğin x tarihinde kaç çalışanım vardı veya ürünüm vardı bunlar SCD’nin görevi değil.Bunlar için verilerin x tarihindeki durumlarını bulamıyorsanız snapshot fact tutmalısınız bu da değişen değil tüm datayı tuttuğundan çok tercih edilmez zorunda değilseniz.

 

Geçmişe dönük SCD doldurmak işkence gibidir iş birimini ikna edebiliyorsanız bugünden başlatın.Zaten elinizde log yoksa hiçbir şey yapamazsınız ama varsa da çok zorlayabilir.

 

Umarım faydalı olmuştur,bir sonraki yazımda görüşmek üzere 😊

 

 

 

SQL Serverda JSON veri ile çalışma

 

SQL Serverda JSON veri ile çalışma

Günümüzde artık çoğu data json formatta tutulmaya başlanıyor.Çünkü developer için datayı bu şekilde göndermek çok kolay çünkü yeni bir alan eklemek ile ilgili hiçbir endişe yok ve datayı da saklıyor win-win durum söz konusu.Fakat bu analiz yapmak isteyenler için zorlayıcı bir hale gelebiliyor.

 

İşte aslında burada bu JSON fonksiyonlarını ve bunlardan istediğimiz verileri nasıl alabileceğimizi göreceğiz.Örnek olarak bir tablo oluşturdum ve uzunca bir örnek JSON data oluşturttum.Bir çok site var sizde benzer jsonlar ürettirebilirsiniz JSON Generator yazarak.Ben https://json-generator.com/ sitesini kullandım.

 

create table json_test

(

id int,

json_data nvarchar(max),

notjson_data nvarchar(max)

)

 

insert into dbo.json_test values (1,'

[

  {

    "_id": "66bb99724449934db2fa9cc5",

    "index": 0,

    "guid": "438a3a2f-114e-4afa-ab78-f9ba31dd25c9",

    "isActive": false,

    "balance": "$2,026.56",

    "picture": "http://placehold.it/32x32",

    "age": 35,

    "eyeColor": "brown",

    "name": "Miranda Horton",

    "gender": "male",

    "company": "MACRONAUT",

    "email": "mirandahorton@macronaut.com",

    "phone": "+1 (908) 500-3849",

    "address": "298 Cameron Court, Chestnut, South Carolina, 2417",

    "registered": "2023-08-01T07:10:58 -03:00",

    "latitude": -15.860973,

    "longitude": -160.799826,

    "tags": [

      "quis",

      "reprehenderit",

      "dolore",

      "mollit",

      "labore",

      "elit",

      "eu"

    ],

    "friends": [

      {

        "id": 0,

        "name": "Chris Morrison"

      },

      {

        "id": 1,

        "name": "Stein Manning"

      },

      {

        "id": 2,

        "name": "Summers Norton"

      }

    ],

    "greeting": "Hello, Miranda Horton! You have 9 unread messages.",

    "favoriteFruit": "apple"

  },

  {

    "_id": "66bb99720797a80b7dc0a591",

    "index": 1,

    "guid": "31e754af-b1b9-4906-a51c-e1f40b345850",

    "isActive": false,

    "balance": "$2,582.52",

    "picture": "http://placehold.it/32x32",

    "age": 20,

    "eyeColor": "blue",

    "name": "Baldwin Blackwell",

    "gender": "male",

    "company": "LIQUIDOC",

    "email": "baldwinblackwell@liquidoc.com",

    "phone": "+1 (888) 489-3186",

    "address": "210 Beayer Place, Finderne, Virginia, 6791",

    "registered": "2017-02-14T04:35:37 -03:00",

    "latitude": -20.942119,

    "longitude": 113.878654,

    "tags": [

      "eu",

      "laborum",

      "id",

      "et",

      "duis",

      "ut",

      "et"

    ],

    "friends": [

      {

        "id": 0,

        "name": "Brianna May"

      },

      {

        "id": 1,

        "name": "Daniel Pruitt"

      },

      {

        "id": 2,

        "name": "Clara Finch"

      }

    ],

    "greeting": "Hello, Baldwin Blackwell! You have 2 unread messages.",

    "favoriteFruit": "banana"

  }

]',

'test data'

)

 

Verimi tabloya ekledikden sonra başlayabilirim.SQL Server’ın JSON tipi için özel olarak ürettiği 7 fonksiyon var şu anda bunların üzerinden tek tek geçeceğiz.

1.     ISJSON

2.     JSON_ARRAY

3.     JSON_MODIFY

4.     JSON_OBJECT

5.     JSON_PATH_EXISTS

6.     JSON_QUERY

7.     JSON_VALUE

 

ISJSON

Bu fonksiyon çok kolay bir fonksiyon verdiğiniz sütundaki veriler Json tipinde mi değil mi size bunu dönüyor.Bunun içinde JSON ise 1 değil ise 0 dönüyor.

JSON_ARRAY

JSON oluşturmak için kullandığımız bir fonksiyondur.Biz parçalamak istiyoruz ama karşınıza böyle bir case çıkabilir bilmekte fayda var.

İster bir tablonun sütunlarını alıp yapabilirsiniz isterseniz kendi değerlerinizi verebilirsiniz.

Mesela bir tablonun belirli sütunları ile oluşturmak isterseniz bu şekilde yapabilirsiniz.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName)

  FROM [AdventureWorks2022].[Person].[Person]

 

Böyle bir çıktı elde edersiniz.Sütun sayısı size kalmıştır.

İsterseniz elbette tüm satırlara default değerde ekleyebilrisiniz.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName,'Tüm satırlara Hüseyin Ekle')

  FROM [AdventureWorks2022].[Person].[Person]

Bu dönen datanın nvarchar(max) tipinde olduğunu unutmayın.Yalnız bizim döndürdüğümüz bu değerler bir stringdir aslında iç içe JSON yani Json içinde bir JSON objesi döndürmek isterseniz ise bir sonraki fonksiyonumuz olan JSON_OBJECT’i kullanmanız gerekir.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,JSON_OBJECT('ad_sutunu':FirstName,'soyad_sutunu':LastName))

  FROM [AdventureWorks2022].[Person].[Person]

 

Dikkat ederseniz bir iç içe JSON söz konusu ve içteki jsonın key’ini ben belirledim ama value tablodan geldi!

 

 

JSON_MODIFY

Var olan bir JSON2ı güncellemenize imkan verir bunu yine ister default bir değer ile isterseniz de tablodaki bir sütun ile yapabilirsiniz kolaylıkla.

3 tane parametre alır:

Sütun,path(yani değiştirilecek değer),yeni değer

Bizim örneğimiz üzerinden gidersek bizim örneğimizde2 tane farklı element barındıran bir JSON’dı.Örneğin ilk dizideki index değerine bakarsanız bunu 0 görürsünüz ben bunu 15 yapmak istiyorum bu durumda işte bu fonksiyonu kullanabiliriz.

select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].index',15) from dbo.json_test

 

Dikkat ederseniz index artık 15 geliyor.

Veya

select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin') from dbo.json_test

Yukardaki sorgu ile ilk dizideki friends dizisindeki 2. Arkadaşın ismini hüseyin yapmak istediğimizde yukarıdaki kodu yazabiliriz.

Burada dikkatini çeken [0] ve [1] kullanımları olmuştur.JSON’da diziler 0’dan başlar yani 0 aslında 1.ci elementi ifade 1 ise 2.elementi ifade eder.

Ve friends bir dizi idi bu dizinin ikinci elementine gitmek için friends[1] kullanımı yapmanız gerekir.

Burada genelde biz JSON değiştirdik mi eskisi ile update etmek isteriz bunun içinde şöyle bir kod ile rahatlıkla yapabilirsiniz.

 

update dbo.json_test set JSON_DATA=JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin') 

tabi Where koymayı unutmayın siz 😊

Birden çok alanı tek seferde güncellemek isterseniz de iç içe JSON_MODIFY kullanabilirsiniz.Örneğin:

select JSON_DATA,JSON_MODIFY(JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin'),'$[0].index',25) from dbo.json_test

JSON_OBJECT

Bir JSON objesi döndürür.JSON_ARRAY ile arasındaki fark biri dizi diğeri objedir.Dizilerde key value ilişkisi olmaz indis sırası olur.Fakat JSON objelerinde bir key value ilişkisi vardır.Aslında Pythondaki list,dictionary ile aynı gibi düşünebilirsiniz.

Hatta dikkat ederseniz JSON_ARRAY [ ] içinde veri döndürürken JSON_OBJECT { } içerisinde veri döndürür.

Dolayısıyla bir JSON_OBJECT içerisine n  tane veri gönderebilirsiniz ama key vermek zorundasınız.

 

select JSON_OBJECT('name':'Hüseyin','surname':'Albayrak')

{"name":"Hüseyin","surname":"Albayrak"}

Gördüğünüz gibi istersek bir tablodan da value’leri getirebiliriz.Keyleri siz vereceksiniz değişemez.

SELECT JSON_OBJECT('urun_Adi':Name,'liste_fiyat':ListPrice)

  FROM [AdventureWorks2022].[Production].[Product]

 

JSON_PATH_EXISTS

Verilen path JSON içerisinde var mı yok mu onu döner.Var ise 1 değil ise 0 döner.Önemsiz gibi gözükse de çok önemli bir fonksiyondur özellikle bir loop vb yapıda bu fonksiyonuz hataları çok ciddi engelleyecektir.

 

select

JSON_PATH_EXISTS(JSON_DATA,'$[0]'), --1 döndü çünkü ilk element vasr gerçektende

JSON_PATH_EXISTS(JSON_DATA,'$[156]'), --0 döndü çünkü 158 elementi yok.

JSON_PATH_EXISTS(JSON_DATA,'$[0].age'),--1 döndü age sütunu var 

JSON_PATH_EXISTS(JSON_DATA,'$[0].age[0]'),--0 döndü çünkü age bir dizi değil 0.cı indisi yok

JSON_PATH_EXISTS(JSON_DATA,'$[0].friends[2].name')--1 döndü çünkü friends dizisinin 3 elementi var bu elementler json formatında yani name alanınına gidilebilir.

from dbo.json_test

 

 

JSON_QUERY

Bir JSON içerisinden bir obje veya dizi döndürmemizi sağlar.Burada önemli nokta son değeri bu fonksiyon ile döndüremezsiniz sadece obje veya dizi döndürürsünüz bunu unutmayın!

select

JSON_QUERY(JSON_DATA,'$[0]'), 

JSON_QUERY(JSON_DATA,'$[0].age'),

JSON_QUERY(JSON_DATA,'$[0].friends[2]') ,

JSON_QUERY(JSON_DATA,'$[0].friends')

from dbo.json_test

 

Bakın 2.sütun değer döndürüyor dizi veya obje gelmediği için NULL geliyor fakat diğerleri hepsi dolu geldi.

JSON_VALUE

Ve aslında hepimizin beklediği fonksiyon belkide çünkü artık JSON’dan klasik değer döndüğünü göreceğiz.

Bakın yukarıdaki aynı sorguyu sadece JSON_VALUE ile yazdım ve sonuç tam tersi

select

JSON_VALUE(JSON_DATA,'$[0]'), 

JSON_VALUE(JSON_DATA,'$[0].age'),

JSON_VALUE(JSON_DATA,'$[0].friends[2]') ,

JSON_VALUE(JSON_DATA,'$[0].friends')

from dbo.json_test

 

 

Çünkü bu fonksiyon sadece scalar dediğimiz klasik data tiğpindekileri döner.

select

JSON_VALUE(JSON_DATA,'$[0].index'), 

JSON_VALUE(JSON_DATA,'$[0].age'),

JSON_VALUE(JSON_DATA,'$[0].friends[2].name') ,

JSON_VALUE(JSON_DATA,'$[0].tags[0]')

from dbo.json_test

 

Hepsini scalar değer verdiğimizde döndürdüğünü görüyoruz.

 

JSON_ARRAYAGG

JSON_OBJECTAGG

Azure SQL’de kullanılanılabilir sadece

OPENJSON genelde Azure üzrinde kullanılır on-premde görmedim hiç.

 

Umarım faydalı olmuştur 😊