Etiket: mssql

SQL Server Üzerinde TSQL ile RFM Analizi, Müşteri Segmentasyonu

omercolakogluYorum bırakın

Merhaba,

Bu yazıda veri bilimi konusunda sıklıkla yapılan çalışmalardan RFM konusunun SQL Server üzerinde T-SQL kodları yazarak nasıl yapılacağını uygulamalı şekilde anlatacağım.

Konuyu bilmeyenler için RFM Analizi ne demek biraz bahsedelim.

RFM nedir?

Recency, Frequency, Monetary kelimelerinin baş harflerinden oluşup, bu üç metriğin hesaplanmasından sonra birleştirilmesiyle meydana gelen bir skordur. Müşterilerin mevcut durumunun analiz edilip, bu skorlara göre segmentlere ayrılmasına yardımcı olur.

Recency: Müşterinin ne kadardır websitesinden/mağazadan hizmet aldığı, ne zamandır bize üye olduğu gibi bilgileri verir. Hesaplanması genellikle, bugünden son üyelik tarihi/son sipariş tarihinin çıkartılmasıyla elde edilir.

Frequency: Müşterinin ne sıklıkla alışveriş yaptığını, ne sıklıkla siteye giriş yaptığını gösteren metriktir. Genellikle sipariş numarası/sipariş kodunun saydırılmasıyla sonuç verir.

Monetary: Müşterinin harcamalarının toplamıdır. E-ticaret sitesine getirdiği ciro, aldığı hizmetler sonrası toplanan getiri olarak da tanımlanabilir. Ciro tanımı ne ise, müşteri bazında hayatı boyunca yapılan harcamalar toplanarak hesaplanır.

Bu metrikler belirlendikten sonra, metrik bazında müşteri verisi 5 eşit parçaya ayrılır. Sonrasında bu rakamlar bir araya getirilerek bir RFM skoru atanır.

Kaynak: https://www.veribilimiokulu.com/rfm-analizi-ile-musteri-segmentasyonu/

RFM analizi bir satış veriseti üzerinde çalışarak elde edilir ve yapılan çalışma sonucunda bir müşteri sınıflandırma işlemi gerçekleştirilir.

Elde etmek istediğimiz tablo aşağıdaki gibidir.

Burada alanların açıklamaları aşağıdaki gibidir.

CustomerID:Sınıflandırılamak istenen müşterinin ID’sidir. Burada müşteri kodu, müşteri adı gibi bilgiler de olabilir.

LastInvoiceDate:Müşterinin son alışveriş yaptığı tarih ve zaman bilgisini tutar. Bu bilgi bizim Recency değerimizi hesaplamak için kullanacağımız bir alandır.

Recency: Müşterinin en son ne zaman alışveriş yaptığı bilgisinin bir metriğidir. Bugün-Son alışveriş tarihi olarak hesaplanır.

Frequency: Müşterinin ne sıklıkta alışveriş yaptığı bilgisidir. Burada fatura numarası ya da sipariş numarası gibi alanlar distinct olarak sayılarak bulunur.

Monetary: Müşterinin harcamalarının toplamıdır. Yani toplamda bir müşteri parasal olarak ne kadarlık alışveriş yapıyor onun karşılığıdır.

Recency_Scale: Elde edilen Recency değerinin 1-5 arasına sıkıştırılmış halidir. Daha açıklayıcı anlatmak gerekirse, diyelim 100 satır kaydımız var.

100/5=20

Demek ki tüm veriyi Receny değerine göre sıralar isek

Sıralamada

1-20 arası=1

21-40 arası=2

41-60 arası=3

61-80 arası=4

81-100 arası=5 olacak şekilde bir yeniden boyutlandırma (Scale) işlemi yapılmaktadır.

Frequency _Scale: Elde edilen Frequency değerinin 1-5 arasına sıkıştırılmış halidir.

Monetary _Scale: Elde edilen Monetary değerinin 1-5 arasına sıkıştırılmış halidir.

Segment: Elde edilen Recency_Scale, Frequency _Scale, Monetary _Scale değerlerine göre belli bir formül ile müşterinin sınıflandırılmasıdır. Bu sınıflandırmada müşteriler Need_Attention, Cant_Loose,At_Risk,Potential_Loyalists, Loyal_Customers, About_to_Sleep,Hibernating,New_Customers, Promising, Champions

Sınıflarından birine göre sınıflandırılır.

Hadi şimdi işe koyulalım ve RFM analizi için önce veri setimizi indirelim.

Verisetimiz https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx adresindeki online_retail_II.xlsx isminde bir excel dosyası.

Bu dosyayı indirelim.

Görüldüğü gibi dosyamız bu şekilde bir görünüme sahip.

Dosyada 2009-2010 ve 2010-2011 yılına ait satış verileri bulunmakta. Biz uygulamamızda bu iki veriden birini seçip çalışacağız. İstenirse bu iki veri birleştirilebilir. Biz şimdilik Year 2010-2011 verilerini dikkate alalım.

Bu sayfaya baktığımızda 540.457 satırlık bir verinin olduğunu görüyoruz. Tabi burada bir müşteriye ait birden fazla fatura var ve bir faturanın altında da birden fazla ürün satırı var. O yüzden satır sayısı bu kadar fazla.

Şimdi kolonlardan biraz bahsedelim.

Invoice: Fatura numarası

StockCode: Satılan ürünün kodu

Description: Satılan ürünün adı

Quantity: Ürün adedi

InvoiceDate: Fatura tarihi

Price: Ürün birim fiyatı

Customer Id: Müşteri numarası

Country: Müşterinin ülkesi

Şimdi bu excel dosyamızı da gördüğümüze göre artık SQL Server platformuna geçme vakti. Malum yazımızın konusu RFM analizini MSSQL üzerinde gerçekleştirme.

İlk iş bu excel datasını SQL Server’a aktarmak.

Bunun için SQL Server üzerinde RFM isimli bir veritabanı oluşturalım.

Bunun için aşağıdaki gibi New Database diyerek yeni bir database oluşturabiliriz.

RFM isimli database imiz oluştu.

Şimdi bu database e excel dosyasındaki veriyi import edeceğiz. Bunun için database üzerinde sağ tıklayarak Task>Import Data diyoruz.

Next butonuna bastığımızda aşağıdaki hatayı alıyorsanız merak etmeyin çözümü var. Hata almıyorsanız bu kısmı okumasanız da olur.

Bu hatada Microsoft.Ace.Oledb.12.0 provider hatasını görüyoruz. Bu hatayı gidermek için Microsoft Access Database Engine’i bilgisayarınıza yüklemeniz gerekiyor. Bunun için aşağıdaki linki kullanabilirsiniz.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

Kurulumu next diyerek default ayarları ile yapabilirsiniz.

Ve kurulum tamamlandı.

Şimdi tekrardan Excel dosyamızı import ediyoruz. Import/Export wizard da kaynak olarak Excel dosyamızı göstermiştik. Hedef olarak ise SQL Server’ı göstereceğiz.

Bağlanacağımız SQL Server’ı seçiyor ve kullanıcı bilgilerini giriyoruz. Benim kullandığım SQL Server kendi makinem olduğu için server name kısmına localhost yazıyor, kullanıcı bilgilerine de Windows authentication’ı işaretliyoruz. Siz de kendi bağlandığınız SQL Server bilgilerini girebilirsiniz.

Copy data from one or more tables or views seçeneğini seçiyoruz.

Next dediğimizde karşımıza aşağıdaki ekran geliyor. Source kısmında Excel dosyasındaki sheet adı, Destination kısmında ise SQL Server’da oluşturacağımız tablonun adı geliyor. Burayı elle değiştirebiliyoruz. 2010-211 yılları arasındaki veriyi kullanmayı tercih ediyoruz.

SQL Server’a aktaracağımız tablonun adını ONLINERETAIL_2010 olarak değiştiriyoruz.

Burada Next deyip devam edebiliriz ancak Edit Mappings butonuna basıp yeni oluşan tablonun alanlarını ve veri tiplerini de görebiliriz. Edit Mappings butonuna basında biraz bekleyebilirsiniz. Zira 540.000 satır excel dosyasını okurken ki bekletme bu. Bilgisayar dondu diye panik yapmayın. Biraz beklediğinizde aşağıdaki ekranı göreceksiniz. Tablomuzun alanları ve veri tipleri. OK deyip geçebiliriz.

Next dediğimizde Run Immediately seçeneğini işaretliyoruz ve tekrar Next diyoruz.

Finish diyoruz ve satırlarımızın aktarılmasını bekliyoruz.

Import işlemi tamamlandı.

Şimdi kontrol edelim.

Artık excel dosyamız veritabanımızda. Buraya kadar ki işlemlerde hata yaşadıysanız. Çalıştığımız veritabanını buradaki linkten indirebilirsiniz.

https://1drv.ms/u/s!AoTudRti4cT8jLEZ3ShT6I2BtteHBw?e=3X4xfl

Artık verilerimizi aktardığımıza göre şimdi RFM analizi işlemlerine başlayabiliriz.

Yazımızın ilk başında RFM analizi sonucunda aşağıdaki gibi bir tablo elde etmek istediğimizden bahsetmiştik.

Bu tabloyu elde etmek için yapılan en büyük hatalardan biri karmaşık SQL cümleleri yazarak tek seferde bu tabloyu elde etmeye çalışmak. Şayet SQL bilginiz de çok iyi değilse geçmiş olsun. SQL ile RFM çalışmanız burada son bulacak büyük ihtimalle.

Şimdi daha basit düşünelim. Sonuçta bir excel tablomuz var. Burada tekrar etmeyen CustomerID ler var ve bu CustomerID lere göre hesaplanan bir takım sütunlar var. O zaman aynı bu mantıkta düşünelim ve bu mantıkta bir tablo oluşturup içine önce CustomerId’leri tekrar etmeyecek şekilde dolduralım. Sonra sırayla diğer alanları hesaplayarak gidelim.

İlk iş bu formatta bir SQL tablosu oluşturmak.

Ekteki gibi bir tablo oluşturuyoruz.

Şimdi her seferinde aynı işlemi yapacağımız için önce tablomuzun içini boşaltacak kodumuzu yazalım.

TRUNCATE TABLE RFM

Sonra tablomuzun için tekrar etmeyecek şekilde CustomerID’ler ile dolduralım. Bunun için kullanacağımız komut,

INSERT INTO RFM (CUSTOMERID)
SELECT DISTINCT [Customer ID] FROM ONLINERETAIL_2010

Burada excelden aktarırken Customer ID kolonunda boşluk olduğu için Customer ID yazarken köşeli parantezler içinde yazıyoruz.

4373 kayıt eklendi dedi. Şimdi tablomuzu kontrol edelim.

Şu anda içinde sadece CustomerId olan diğer alanları null olan 4373 satır kaydımız var.

Şimdi sırayla diğer alanları hesaplayalım. İlk hesaplayacağımız alan LastInvoiceDate. Yani müşterinin yaptığı son satınalma zamanının bulunması. Bu değeri bulacağız ki Recency değeri bu tarih ile şimdiki zamanın farkı üzerinden çıkarılacak ve buna göre bulunacak.

Bu işlem için basit bir update cümlesi kullanabiliriz. Aşağıdaki sorgu her bir müşterinin ONLINERETAIL tablosunda son alışveriş yaptığı zamanı bulup update edecektir.

UPDATE RFM SET LastInvoiceDate=(SELECT MAX(InvoiceDate) 
FROM ONLINERETAIL_2010 where [Customer ID]=RFM.CustomerID)

Update ettik. Şimdi de sonuca bakalım. Artık LastInvoiceDate alanımız da güncellenmiş durumda.

Bir sonraki adım Recency değerinin bulunması. Bunun için şimdiki zamandan son alışveriş zamanını çıkarmamız ve tablomuzu güncellememiz gerekiyor. Tıpkı bir excel dosyasında satır satır formül çalıştırır gibi sorgu ile satır satır güncelleme yapacağız.

SQL Server’da iki tarih arasındaki farkı alan komut DateDiff komutu. Burada datediff komutu içine üç parametre alır.

1-Farkı ne türünden alacaksın? Gün, Ay, Yıl…

2-Başlangıç zamanı (LastInvoiceDate)

3-Bitiş zamanı (Şimdiki zaman. Fakat bizim veri setimiz 2011 yılında geçtiği için son zamanı 31.12.2011 olarak alabiliriz.

Şimdi update cümlemizi çalıştıralım.

UPDATE RFM SET Recency=DATEDIFF(DAY,LastInvoiceDate,'20111231')

Sonuca bakalım.

Görüldüğü gibi Recency değerini hesaplatmış durumdayız. Sırada Frequency var. Şimdi de onu aşağıdaki sorgu ile bulalım. Frequency bir kişinin ne sıklıkta alışveriş yaptığı bilgisi idi. Yani fatura numaralarını tekil olarak saydırırsak bu değeri bulabiliriz.

UPDATE RFM SET Frequency=(SELECT COUNT(Distinct Invoice) FROM ONLINERETAIL_2010 where CustomerID=RFM.CustomerID)

Şimdi sonuca tekrar bakalım. Görüldüğü gibi Frequency değerimizi de hesapladık.

Sırada Monatery değerimiz var. Yani bir müşterinin yapmış olduğu toplam alışverişlerin parasal değeri. Bunu da aşağıdaki sql cümlesi ile bulabiliriz. Burada her bir müşteri için birim fiyat ile miktarı çarptırıyoruz.

UPDATE RFM SET Monatery=(SELECT sum(Price*Quantity)  FROM ONLINERETAIL_2010 where CustomerID=RFM.CustomerID)

Sonuçlara bakalım. Görüldüğü gibi Monatery değeri de hesaplanmış oldu.

Artık bu aşamadan sonra R,F ve M değerleri için scale değerlerini hesaplamaya sıra geldi. Bunun için tüm değerleri istenilen kolona göre sıralayıp sıra numarasına göre 1-5 arası değerlendirmeye tabi tutmamız gerekiyor. Bunun için kullanacağımız komut ise Rank komutu.

Kullanımı ise aşağıdaki gibi. Kullanımı karışık gelirse copy-paste yapmanız yeterli.

UPDATE RFM SET Recency_Scale= 
(
 select RANK from
 (
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Recency desc) Rank
FROM RFM
) t where  CUSTOMERID=RFM. CUSTOMERID)

Sonuçlara baktığımızda artık Recency_Scale değerini de hesaplamış durumdayız.

Sırada Frequency_Scale var. Onun için de aşağıdaki komutu kullanıyoruz.

update RFM SET Frequency_Scale= 
(
 select RANK from
 (
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Frequency) Rank
FROM rfm 
) T where  CUSTOMERID=RFM. CUSTOMERID)

Sonuca bakalım. Görüldüğü gibi Frequency_Scale’ da hesaplanmış durumda.

Ve son olarak Monatey_Scale değeri. Onu da aşağıdaki gibi hesaplıyoruz.

update RFM SET Monatery_Scale= 
(
 select RANK from
 (
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Monatery) Rank
FROM rfm 
) t where  CustomerID=RFM.CustomerID)

Sonuçlara bakalım. Görüldüğü gibi Monatery_Scale’da hesaplandı.

Son olarak artık tüm değişkenlerimiz hesaplandığına göre geriye bir tek sınıflandırma etiketi kaldı. Onun sorgusu hazır durumda. Aşağıdaki sorguya göre sınıflandırmalar yapılabilir.

UPDATE RFM SET Segment ='Hibernating' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[1-2]%'  
UPDATE RFM SET Segment ='At_Risk' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[3-4]%'  
UPDATE RFM SET Segment ='Cant_Loose' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[5]%'  
UPDATE RFM SET Segment ='About_to_Sleep' 
WHERE Recency_Scale LIKE  '[3]%' AND Frequency_Scale LIKE '[1-2]%'  
UPDATE RFM SET Segment ='Need_Attention' 
WHERE Recency_Scale LIKE  '[3]%' AND Frequency_Scale LIKE '[3]%' 
UPDATE RFM SET Segment ='Loyal_Customers' 
WHERE Recency_Scale LIKE  '[3-4]%' AND Frequency_Scale LIKE '[4-5]%' 

UPDATE RFM SET Segment ='Promising' 
WHERE Recency_Scale LIKE  '[4]%' AND Frequency_Scale LIKE '[1]%' 
UPDATE RFM SET Segment ='New_Customers' 
WHERE Recency_Scale LIKE  '[5]%' AND Frequency_Scale LIKE '[1]%' 
UPDATE RFM SET Segment ='Potential_Loyalists' 
WHERE Recency_Scale LIKE  '[4-5]%' AND Frequency_Scale LIKE '[2-3]%' 
UPDATE RFM SET Segment ='Champions' 
WHERE Recency_Scale LIKE  '[5]%' AND Frequency_Scale LIKE '[4-5]%'

Sonuçlara bakalım.

Artık tüm müşterilerimizi sınıflandırmış durumdayız. Hatta hangi sınıftan kaç müşteri olduğuna da bakalım.

Vee işlem tamam.

Bu yazımızda SQL Server üzerinde sadece TSQL kodları kullanarak RFM Analizi çalışması yaptık. Çalışmada Online Retail datasını kullandık. Aşağıdaki kodu kullanarak OnlineRetail datasını aktardıktan sonraki tüm RFM hesaplama işlemlerini tek seferde yapabilirsiniz.

Buraya kadar sabırla okuduğunuz için çok teşekkür ederim.

Sağlıcakla…

JSON Formatındaki Veriyi SQL Server ile Sorgulama

omercolakogluYorum bırakın

Merhaba,

Bu yazımızda son yılların popüler veri formatı olan JSON formatını OPENJSON komutunu kullanarak nasıl sorgulayacağımızdan  basitçe bahsediyor olacağım.

Elimizde şu şekilde bir JSON verisi var.

{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
}'

Bu Json’ı SQL Server ile aşağıdaki gibi sorgulayabiliriz.

DECLARE @JSON AS NVARCHAR(MAX)='{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
}'


SELECT * FROM  
 OPENJSON ( @json )  
WITH (   

		 firstname varchar(100) '$.firstName' ,
		 lastName varchar(100) '$.lastName' ,
		 age int '$.age' ,
		 gender varchar(100) '$.gender' ,
		 streetAddress varchar(100) '$.address.streetAddress' ,
		 city varchar(100) '$.address.city' ,
		 postalCode varchar(100) '$.address.postalCode' ,
		 state varchar(100) '$.address.state' ,
		 address varchar(100) '$.address.streetAddress' ,
		 phoneNumbers varchar(100) '$.phoneNumbers.type'
)

Ve bu da elde ettiğimiz sonuç.

Şimdi veri sayısını biraz çoğaltalım.

DECLARE @JSON AS NVARCHAR(MAX)='
[
{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
	},
	
	{

    "firstName": "Marrie",
    "lastName": "Coldman",
    "gender": "woman",
    "age": 39,
    "address": {
        "streetAddress": "156",
        "city": "Newyork",
        "state": "NY",
        "postalCode": "10019"
    },
    "phoneNumbers": 
        { "type": "home", "number": "555689998" }   
}	
]'


SELECT * FROM  
 OPENJSON ( @json )  
WITH (   

		 firstname varchar(100) '$.firstName' ,
		 lastName varchar(100) '$.lastName' ,
		 age int '$.age' ,
		 gender varchar(100) '$.gender' ,
		 streetAddress varchar(100) '$.address.streetAddress' ,
		 city varchar(100) '$.address.city' ,
		 postalCode varchar(100) '$.address.postalCode' ,
		 state varchar(100) '$.address.state' ,
		 address varchar(100) '$.address.streetAddress' ,
		 phoneNumbers varchar(100) '$.phoneNumbers.type'
)

İlerleyen zamanlarda konu ile alakalı daha detaylı yazılar gelecek inşallah.

2011 Yılında yaşadığım bir deneyim. SQL Server ve Jumbo Paket Sorunu.

omercolakogluYorum bırakın

Bu yazı 2011 yılında yazılmıştır. İlginç bir sorun ve çözüm içerdiği için tekrar paylaşmak istedim.


Geçenlerde bir sistem upgrade’i yaptık. Server ımız değişti, Sistemde database olarak SQL 2005’ten SQL 2008’e, İşletim sistemi olarak Windows Server 2003’ten Windows Server 2008’e geçtik ve cluster yapısı kurduk. ERP Sistemimizde de versiyon geçişi yaptık ve yeni versiyonun çalışması için client bilgisayarlarda mdac versiyon upgrade’ine ihtiyaç duyduk.
Sonuç olarak çok ilginç bir sorunla karşılaştık. Kullanıcı tarafındaki çok basit bir işlem kimi bilgisayarda 1 sn sürerken kimi bilgisayarda 20 sn sürüyordu. Sonuç olarak sorun server kaynaklı, İşletim sistemi kaynaklı, SQL 2008 kaynaklı, Erp programı kaynaklı, ya da mdac kaynaklı olabilirdi. Çünkü bunların hepsi de değişmişti. Epey bir inceleme yaptık sorun üzerinde.
Öncelikle şunu söyleyim bu ayarla alakalı sql server üzerinde bir article bulamadım. Ancak başka uygulamalarda benzer sıkıntılar yaşanmış onun üzerine bu konu üzerine gittik. Burada yeni ethernetler Jumbo frame denilen yapıyı destekliyor ve normalde 1500 byte lık olan network paketleri 9000 byte olarak tek seferde gönderiyor. Paketleri parçalama işini client ın etherneti yapıyor. Özellikle benzer işlem tekrarlarında bu durumu sistem otomatik olarak yapıyor yani kendince optimize etmeye çalışıyor. Client’ta özellikle döngüye takıp aynı sonucu döndüren tek satırlık ya da çoğunlukla sıfır satırlık select cümlelerinin kullanıldığı yerlerde bu özellik devreye giriyor. Eğer karşıdaki ethernet jumbo paketi desteklemiyor ise paket tekrardan servera gönderiliyor bu kez server bu paketi tekrardan parçalayıp client a gönderiyor. Bu da her paket için yapıldığında yaklaşık 10 kat bazen daha fazla gecikmeye sebep oluyor.
Çözüm iki türlü ya serverdan Large Recive Offload Data özelliğini disable etmek ya da client da jumbo paket size değerini arttırmak. Ancak clientta işlem yapmanın iki dezavantajı var bunlardan biri ethernet ya da switchler desteklemiyor olabilir ikincisi de bu özellik enable yapıldığında networkte büyük paketler dolaşmaya başladığından networku tıkayabilir. Bu konuda bir kaç kişiye sorduk pek önermediler büyük paketleri. En doğrusu server üzerinde bu ayarı disable etmek gibi görünüyor.
Biz bu ayarı serverda disable ederek sorunu çözdük. Zaten eski serverda ethernet desteklemiyormuş ondan sorun olmamış.
Bu bahsettiğim sorundan kaynaklı sıkıntı olduğunu düşündüğün makinada sorun olup olmadığını anlamak için performance monitorden send packet/sec değerlerine bakılabilir. Hızlı makina ile yavaş makina arasındaki fark en az 10 kat oluyor. Aşağıda bu ayarın nasıl yapıldığının resmi mevcut.

SQL Server’da Bir Database’i Page Seviyesinde Backup’tan Dönme

omercolakogluYorum bırakın

“Sql server detected a logical consistency-based i/o error” diye başlayan bir hatayla karşılaşmışsanız geçmiş olsun. Muhtemelen diskte bir sektör okuma hatası var. SQL Server’ın en küçük yapıtaşı olan 8 KB’lık page’lerden bir ya da birkaçı bozulmuş.
Koskoca milyon satırlık tabloda sadece 3-5 satır bozuk diye sorgunuz çalışmıyor. Tüm database i yedekten dönseniz yeni eklenen kayıtlar gidecek bu kez. Neyse ki kolay bir yolu var. Database’i page bazlı olarak yedekten dönebilme.
Yazdım. Beğenmeniz dileğiyle.

SQL Server’da Bir Database’i Page Seviyesinde Backup’tan Dönme

SQL Server Change Data Capture ile Değişiklik Yapılan Kayıtların Loglanması

omercolakoglu3 Yorum

Merhaba,

Bu makalemizde SQL Server tarafında yapılan maniplasyonların (Insert, Update, Delete) geri planda otomatik olarak kayıt altına alınmasını anlatıyor olacağız.

Şimdi bir senaryo düşünelim. Bir ticari yazılımımız var. Bu yazılımı dışarıdan satınaldık ve özelliklerine müdahele edemiyoruz kaynağı bizde olmadığı için.

Sistem üzerinde önemli bir fatura hareketinin değiştirildiğini ya da çıkarıldığını düşünelim. Son dönemlerdeki ticari yazılımlar bunların kayıt altına alınmasına izin veriyor ancak vermeyenler de var. Bu anlamda bizim database bazında bu kayıtların loglanmasına ihtiyacımız söz konusu.

Bu işlerle biraz uğraşanlar için ilk akla gelen tabiki trigger yazılması. Doğru bu bir çözümdür ancak sıkıntıları vardır.

Bu sıkıntılar genel olarak şöyledir;

  • Sizin yazdığınız trigger ticari programın kendisinin hata vermesine sebep olabilir ve kayıtların yapılmamasına sebep olabilir. Zira trigger lar transactionların bir parçasıdır ve trigger da gerçekleşen hata tüm transaction ı rollback  yapar.
  • Özellikle mevzuat değişimi gereği sıklıkla versiyon geçişi söz konusudur ve bu versiyon geçişlerinde database düzenlemesi yapıldığı için büyük ihtimal trigger larınız silinir ve her seferinde yeniden oluşturacak scriptler oluşturmanız gerekecektir.
  • Genel olarak Türkiye şartlarında dönem mali dönem bağımlı çalışmak tercih edildiği için her yıl başında fiziken yeni tablolar oluşturulmaktadır ve bunlar için de trigger lar yeniden yazılmalıdır.

Anlaşılacağı üzere trigger meselesi etkin bir çözümdür fakat biraz zahmetlidir.

Peki bizim yazımızın da konusu olan bu durum için bir çözüm yok mu? Birim fiyatı 5000 TL olan bir malzemenin satış faturasındaki fiyatını 50 TL olarak  değiştiren bir kişiyi tespit etmenin pratik bir yolu yok mudur?

Bu noktada imdadımıza SQL Server Change Data Capture (CDC) dediğimiz özellik yetişiyor. Bu arkadaş yetenekli bir arkadaş. SQL Server’da bildiğiniz üzere tüm manipülasyon işlemleri önce Log dosyasına sonra Data dosyasına yazılır.  Burada log dosyası diye bahsettiğim SQL server’ın sistem log dosyası değil database’in Log dosyasıdır (LDF).

İşte CDC sistem üzerinde Log dosyasını izler ve olan değişiklikleri hızlı bir şekilde kayıt altına alır.

Örnek olarak siz aşağıdaki gibi bir UPDATE cümlesi çalıştırdınız.

CUSTOMERS tablosunun 20 alandan oluştuğunu varsayalım oysa biz sadece bir alanı update ettik. Dolayısıyla SQL Server transaction log üzerinde sadece bir alanlık işlem hacmi söz konusu.

İşte Change Data Capture sadece bu bilgiyi okuyarak arka planda veriyi logluyor.

Siz  CDC yi configure ederken belli bir süreliğine dataları loglayıp belli bir tarihten öncesini sildirebiliyorsunuz. Burada yazacağınız bir script ile önce bu datalara herhangi bir warehouse ortamına alıp daha sonra sistemden temizleyebilirsiniz.

Öncelikle şunu başta belirtmek isterim ki bu özellik SQL Server 2008 den beri vardır ancak Enterprise edition üzerinde çalışır. Tabi test ortamları için developer edition da enterprise ın tüm özelliklerine sahiptir.

Şimdi bu CDC nasıl çalışıyor bir bakalım.

1. Önce bir tablo oluşturalım.

2.Database imizde CDC yi enable yapıyoruz.

3.Tablomuzda CDC yi enable yapalım.

CDC yi enable ettikten sonra system tables altında aşağıdaki tablolar oluşur.

  • cdc.captured_columns : Adından da anlaşılacağı üzere değişikliklerin takip edileceği kritik alanları tutar. Bu tablo manuel olarak edit edilebilir durumda olup içeriği değiştirilebilir.
  • cdc.change_tables :Hangi tabloların değişiminin takip edileceği bilgisini tutar.
  • cdc.ddl_history :Şema bilgilerindeki değişiklikleri tutar.
  • cdc.lsn_time_mapping: Asıl Tablo üzerinde yapılan her transaction işlemi bu tablo içerisinde tutulur ve içerisindeki lsn bilgisine göre hangi sırada yapıldığı bilgisi tutulur.

4.Şimdi bir kayıt ekleyelim.

5.UPDATE yapalım.

6.DELETE Yapalım

Görüldüğü gibi tablo üzerinde 2 insert,1 update ve 1 delete işlemi yaptık. Burada sistemde 4 satır kaydın logunun tutulması gerekiyor. Bakalım görebilecek miyiz?

Şimdi tablolarımıza bir bakalım.

CDC.captured_colums tablosu

CDC.ddl_history tablosu

CDC.index_column tablosu

CDC.lsn_time_mapping tablosu

Sistemde loglanan kayıtları ya doğrudan ya da tarih parametresi alan table valued function lar ile görebiliyoruz. Bu tablodaki kayıtlar ise log sequence number (lsn) ile tutuluyor. Bu fonksiyonlar da yine tablo bazlı olarak otomatik oluşuyor. Aşağıdaki resimde bu fonksiyonları görebilirsiniz.

Log kayıtlarını ulaşmak  istediğimizde  eğer tablonun tamamına ulaşmak istiyor isek

select

* from cdc.dbo_customers_CT şeklinde kullanıyoruz.

burada tablo formatı cdc.<schema>_<tablename>_CT şeklinde.

Bunun kullanımını sonucu aşağıdaki gibi.

table valued functionlar  ise aşağıdaki gibi kullanılıyor.

DECLARE @from_lsn binary(10), @to_lsn binary(10);

–minimum lsn numarasını buluyoruz.

SET @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_customers’);

–maximum lsn numarasını buluyoruz.

SET @to_lsn = sys.fn_cdc_get_max_lsn();

— CDC ile ilgili işlemlerde tablo bazlı oluşan cdc function larını kullanıyoruz.

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customers(@from_lsn, @to_lsn, ‘all’);

Görüldüğü üzere sistem 4 adet fazladan alan ve sistemde yapılan değişiklik üzerine loglanan kayıtları getirdi.

Burada

__$start_lsn log: sequence number bilgisini içeriyor. Buradan kayıt tarihine erişebiliyoruz.

__$seqval: Sequnce değeri yani işlemin hangi sırada gerçekleştiği bilgisine erişmek için bu alan kullanılıyorç

__$operation:2 Insert, 4 Update ve 1 Delete için kullanılıyor.

__$operation:1 Insert,Delete 0 Update

anlamına gelmektedir.

Burada kayıt zamanını elde etmek istediğimizde

sys.fn_cdc_map_lsn_to_time function ını kullanıyoruz.

select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as KayitZamani,

* from cdc.dbo_customers_CT

Burada oluşan log kayıtlarını temizlemek için ise

sp_cdc_cleanup_change_table

komutunu kullanıyoruz.

Kullanımı aşağıdaki gibi.


— aşağıdaki kod  3 gün öncesine ait logları temizliyor.
declare @lsn binary(10);
set @lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,getdate()-3);
exec sys.sp_cdc_cleanup_change_table @capture_instance = ‘dbo_Customers’, @low_water_mark=@lsn

–CDC yi disable etmek için ise 
sp_cdc_disable_db,
sp_cdc_disable_table 
komutları kullanlr 

EXECUTE sp_cdc_disable_table@source_schema = N’dbo’,@source_name = N’Customers’,@capture_instance =N’dbo_Customers’

CDC çalıştırabilmek için SQL Server agent a ihtiyacımız söz konusu.  Sistem 2 adet job ı otomatik olarak oluşturmaktadır. Bunlardan birisi değişen datanın capture edilmesini sağlarken diğeri de logları temizlemektedir.

Sonuç:

  • CDC gerçekten çok ihtiyaç duyulan ve çok kullanışlı bir araç.
  • Sistemdeki insert, update ve delete leri loglayabiliyor.
  • Eğer update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor.
  • Örneğin: UPDATE CUSTOMERS SET NAME=NAME cümlesini çalıştırdığımızda herhangi bir loglama yapmıyor çünkü değişen bir şey yok.
  • Sistemin çalışıyor olması için SQL Server Agent’ın mutlaka çalışması gerekir. Çünkü loğları okuyan bir job bu işleri yerine getirmektedir.
  • Yazacağımız bir script ile istediğimiz tablolarda çalıştırıp istemediklerimizde çalıştırmayabiliriz. Hatta çok fazla kolon olan bir tabloda istediğimiz kolonlar için aktif hale getirirken istemediklerimizi es geçebiliriz.

Bir sonraki makalede görüşmek üzere.

SQL Server’da Canlı Veriyi Tablo Bazlı Sıkıştırma (Compress)

omercolakoglu6 Yorum

Merhaba,

Bu yazımızda SQL Server’daki bir tabloyu sıkıştırma yani Compress özelliğinden bahsediyor olacağım. Bir çoğumuz veritabanlarında text veriler kullanıyoruz. Bu verilerde ise gerek veritabanı mimarisi sebebiyle ya da gerekse içerisindeki veriler sebebiyle boşluklar bulunmakta. Bu boşluklar ise gereksiz yer teşkil etmekte.

Özellikle varchar, varbinary gibi alanlar yerine char,binary gibi veri tipleri kullanımı veritabanımızın gereksiz büyümesine sebep oluyor. Hazır paket programlarda bu veritabanı mimarisinde değişiklik yapamıyoruz ancak SQL 2008’den bu tarafa olan compress özelliğini kullanabiliriz.

Şimdi elimizde bir tane tablosu olan bir database i miz var. Özellikle tek tablo kullandım ki yaptığımız kazancı rahatlıkla görebilelim.

Tablomuz yaklaşık olarak bu şekilde.

Bu da tablomuzun normalizasyon yapısı. Görüldüğü gibi çok sayıda char tipinde alanlar kullanılmış.

Şimdi bir de tablomuzun diskte kapladığı alana bakalım.

Satır sayısı: 529.324

Kaplanan alan:1,4 GB

Görüldüğü gibi yaklaşık 1.4 GB büyüklüğünde tek tablolu bir database imiz var.

Şimdi bu tabloyu sıkıştırmayı deneyelim.

Tablo üzerinde sağ tık Storage>Manage Compression diyoruz.

Karşımıza bir wizard çıkıyor.

Tablo ile alakalı 3 tür sıkıştırma yapısı var.

None:Sıkıştırma yok

Row:Satır bazlı sıkıştırma

Page:Page bazlı sıkıştırma.

Şimdi Row seçelim ve Calculate tuşuna basalım.

1.378 MB’lık tablonun 163 MB’a düşeceğini öngörüyor. Yani 1378/163=8.5 kat sıkıştırma.

Şimdi de Page seçelim ve Calculate tuşuna basalım.

O da 116 MB çıkardı. Yani Yani 1378/116=11,7 kat sıkıştırma. Burada page ya da row based sıkıştırma daha iyi diye bir yorum yapmak zor. O yüzden calculate yapıp hesaplamak daha mantıklı.

Şimdi akla gelen bir diğer soru ise performans. Yani sıkıştırılmış bir tabloda sorgu performansı ne olur.

Gelin onu da hep birlikte deneyelim.

Tablomuzdan 2019 Ağustos ayında Adana şehrinde yapılan satışları indexli,indexsiz olarak çekeceğiz. Bakalım compression aktif ve pasif olduğunda nasıl sonuç döndürecek.

Şimdi SQL Server’ın bize önerdiği indexi ekleyelim.

Şimdi row compression yapıp deneyelim.

İşlem tamamlandı. Şimdi tablo boyutuna bakalım. Gördüğümüz gibi 160 MB civarına indi. Yani yaklaşık 8.5 kat sıkıştı.

Şimdi index’i silip sorgumuzu çalıştıralım.

Normalde sıkıştırma yaptığı zaman daha uzun  sürede gelmesini bekleriz. Oysa daha az okuma yaptığı için sistem 8 kat daha performanslı çalıştı.

Şimdi index ekleyip tekrar çalıştıralım.

Gördüğümüz gibi indexin şu an için sıkıştırmada bir payı olmadığından sıkıştırma aktifken ya da pasifken bir fark olmadı.

Şimdi de page bazlı compression’a bakalım.

Data boyutu tahmin edilenden daha aza indi. Yaklaşık 80 MB oldu.

Şimdi performansa bakalım.

Önce index i silelim.

Index yokken bile çok iyi sonuç getirdi. Hatırlayalım sıkıştırmadan önce 1,44 GB lık okuma yapıyordu burada ise sadece 87 MB.

Şimdi de indexi tekrar ekleyerek bakalım.

Özetle

  • Bu yazıda SQL Server’da compression özelliğini ve nasıl kullanılacağını anlattık.
  • 2 GB lık bir database’i 85 MB’a kadar küçülttük.
  • Ayrıca row based ve page based sıkıştırmanın farkını gördük.
  • Son olarak compress aktif bir tabloda pasif olana göre beklediğimizin tam aksi daha az okuma ve daha fazla performans gösterdiğini gördük.

Sonraki yazıda görüşmek dileğiyle.

Sağlıcakla kalın.

SQL Server Database Mail ve Gmail ile Kullanımı

omercolakoglu1 yorum

Zaman zaman SQL Server üzerinden otomatik mail gönderme ihtiyacımız olur. Örneğin bir yedek alma işlemi sorunsuz tamamlandığında ya da yedek alırken sorun yaşandığında sistem bize otomatik mail atsın isteriz.
Ya da bir sql sorgusunun sonucu bize mail olarak gelsin isteriz.
İşte bu işlemler için SQL Mail konfigürasyonunun yapılmış olması gerekir.
SQL Mail konfigürasyonunun örnek bir gmail hesabı ile nasıl yapılacağını anlattığım yeni yazım.

SQL Server Database Mail ve Gmail ile Kullanımı

Devexpress Dashboard ve SQL Server’da Büyük Veri ile Dashboard Performansı

omercolakogluYorum bırakın

Devexpress’i diğer iş zekası uygulamalarından ayıran en temel özelliği data modelleri oluşturmadan doğrudan SQL cümlesi yazarak çok pratik şekillerde interaktif dashboardlar oluşturmayı sağlaması idi.

Aslında bunu yaparken de çok pratik bir yöntem kullanıyor ve siz aksini belirtmedikçe tüm işi veritabanı sunucuya yaptırıyor. Ona bir işi yaptırmak için ise onun dilinden iyi anlaması gerekiyor tabi.

Ne demek istediğimi şöyle anlatayım.

Çözümpark’ta yayınladığım makaleme bir gözatın.