SQL Server’da Tablo ve Kolon Detaylarını Elde Etme (T-SQL Script)

Microsoft SQL Server ile proje geliştirirken bazı durumlarda bir tabloya ait kolonları ve o kolonlara ait bilgileri elde etmek isteyebilirsiniz. SQL Server eğitimlerinde ve projelerde genellikle bu soru çok fazla karşımıza çıktığı için bu bilgiyi nasıl elde edebileceğimizi bir yazıya dökmek istedim.

SQL Server Database Engine kullanılarak geliştirilen yazılım projeleri, SQL Server Integration Services (SSIS) kullanılarak tasarlanan ETL projeleri veya SQL Server Reporting Services (SSRS) kullanılarak geliştirilen bir raporlama projesi gerçekleştirdiğinizi ve bu projenin herhangi bir safhasında projede kullanılan tablolar ile ilgili bir dokümantasyon oluşturmak istediğiniz bir senaryoyu düşünün. Böyle bir istek karşısında kullanılan bütün tablolara ait kolonlar ve bu kolonlara ait yapısal bilgileri bir yere dokümante etmek için öncelikle bu bilgilere erişmeniz gerekmektedir. SQL Server Management Studio (SSMS) kullanılarak tablo adını seçtikten sonra Alt + F1 tuş kombinasyonunu kullanarak ilgili tablonun çeşitli özelliklerini görebilmekteyiz. Ancak tek bir sonuç seti olacak şekilde ve istenilen ek bilgileri de dahil edecek şekilde customize etmek istediğimizde bu bilgileri elde edebilecek bir T-SQL scripti oluşturmamız gerekmektedir. Bu yazımızda bu bilgileri elde edebileceğiniz bir script örneğini sizlerle paylaşıyorum. Üzerinde sizler de ekleme / çıkarma yaparak istediğiniz değerlere göre düzenleme yapabilirsiniz. Umarım faydalı olur:

SQL Server’da bir tabloya ait kolonların detaylı bilgilerini elde edebileceğiniz T-SQL scriptini aşağıdaki TechNet Gallery linki üzerinden ücretsiz olarak indirip kullanabilirsiniz;

https://gallery.technet.microsoft.com/SQL-Server-Getting-Table-22bf4e81

Umarım faydalı olur…

Bir sonraki yazımızda görüşmek üzere…

Yazar: Abdullah ALTINTAŞ

Microsoft Azure Servisleri ile Advanced Data Analytics Çözümleri Kitabımız Yayınlandı!!!

Uzun zamandır Microsoft Azure çözümlerini kullanarak stream verilerinin analiz edilmesi ile ilgili yazılar paylaşmaktaydım. Hatta bu konuları kapsayacak şekilde blogumda Advanced Data Analytics isminde yeni bir kategori oluşturdum. Yazmış olduğum bütün bu alanla ilgili yazılarımı  bu kategori altında paylaşıyorum. Aynı zamanda Yüksek Lisans bitirme projem olan bu konular üzerine yaptığım çalışmaları bir kitap haline getirmeyi ve ücretsiz olarak faydalanmak isteyenler için pdf olarak paylaşmayı uzun zamandır planlıyordum. Sonunda bütün konuları kapsayacak şekilde ele aldığım ve güncellediğim kitabım yayınlanmaya hazır hale geldi.

Kitapta Advanced Data Analytics ve IoT Kavramlarına Giriş, İçerik Yönetim Sistemleri, Microsoft Azure Çözümleri ve Bu Alandaki Rakipleri (Apache Kafka, AWS Kinesis), Microsoft Azure Event Hubs, Microsoft Azure Stream Analytics, Microsoft Power BI ve Microsoft Azure SQL Database konuları ele alındı ve belirli senaryolar ile örnekler gerçekleştirilerek sonuçları paylaşıldı. Yazarken ben büyük keyif alarak yazdım. Umarım okurken sizler de aynı keyfi alırsınız.

Kitaba ait linki aşağıda paylaşıyorum. Keyifli okumalar…

Microsoft Azure Servisleri ile Advanced Data Analytics Çözümleri Kitabı

Kapak

 Yazar: Abdullah ALTINTAŞ

Stream Verinin Azure SQL Database’de Table Partitioning ile Tutulması (Demo)

Advanced Data Analytics başlığı altında daha önce, stream olarak gelen çok büyük miktarda verinin Microsoft Azure Event Hubs servisi ile nasıl elde edilebileceği, Microsoft Azure Stream Analytics servisi ile nasıl analiz edilebileceği ve Microsoft Power BI kullanılarak gerçek zamanlı olarak nasıl raporlanabileceğini ele almıştık. Aynı zamanda stream verinin daha sonra analizinin yapılması için Azure SQL Database‘de nasıl saklanabileceğini serinin önceki yazılarında göstermiştik. Bugünkü konumuza geçmeden önce göz atmak isteyenler için Advanced Data Analytics başlığı altındaki makale serisine aşağıdaki linkten erişebilirsiniz:

http://www.abdullahaltintas.com/index.php/real-time-click-stream-analizi-icin-microsoft-azure-cozumleri-serisi/

Günlük hayatımızda IoT kavramı odağımıza girdikçe üretilen verilerden anlamlı sonuçlar çıkartabilmek, çok büyük miktarlarda üretilen verileri hızlı ve doğru bir şekilde elde edip analizini yapabilmek özellikle veri bilimciler için çok önemli bir noktaya geldi. Internet of Things (IoT) cihazları, sensörler, akıllı çözümler, sistemlere ait loglar ve click stream verileri gibi saniyede milyonlarca veri üreten sistemlerden bu verileri almak ve işlemek için çeşitli yöntemler kullanılmakta ve bu ihtiyaçlar karşılanmaktadır. Microsoft teknolojileri açısından baktığımızda Azure Event Hubs, Azure Stream Analytics çözümleri ile bu veriler etkin bir şekilde elde edilebilmekte ve analizi yapılabilmektedir. Ardından analizi yapılan veriler ihtiyaca bağlı olarak farklı çıktılar üretebilmekte ve gerektiğinde tekrar analiz edilmek üzere saklanabilmektedir. Özellikle tekrar analiz edilmek üzere saklanmak istenilen bu büyük miktarda veriler Azure BLOB Storage gibi bir çözüm ile saklanabileceği gibi bazı durumlarda Microsoft’un bulut sistemlerdeki ilişkisel veritabanı çözümü olan Azure SQL Database hizmeti ile tablo yapısında da tutulabilmektedir. Ancak veri miktarı çok büyük olduğundan Azure SQL Database çözümünde bu veriler tek bir tabloda tutulmak istenildiğinde yönetilebilirlik ve ölçeklenebilirlik bakımından parçalı bir yapıda tutulması bizlere avantaj ve performans kazandıracaktır. Bu makalemizde stream olarak akan click-stream verisinin Azure Stream Analytics’ten çıktıktan sonra Azure SQL Database hizmetinde Table Partitioning yapısı kullanılarak nasıl tutulabileceğini ele alacağız.

Serinin önceki yazılarında kullanmış olduğumuz uygulamayı benzer şekilde devreye sokacağız. Uygulama üzerinde kullanıcıların oluşturduğu tıklamalardan meydana gelen click stream verisinin Azure Event Hubs ile nasıl elde edildiğini ve Azure Stream Analytics ile nasıl analizin yapıldığını linklerden öğrenebilirsiniz. Yapacağımız demoda bu aşamaya kadar olan süreci daha önce aktarmış olduğumuz için geçiyor ve direkt olarak Stream Analytics job’ının output kısmından anlatıma başlıyoruz. Senaryomuzda verileri saklamak için kullanacağımız Azure SQL Database‘i örneğimize başlarken oluşturmamız gerekiyor. Bunun için Azure portali üzerinde sol alt kısımda bulunan New sekmesine tıklayarak Data Services seçeneğini seçiyoruz. Devamında SQL Database servisini seçip Custom Create diyerek ilgili sayfaya erişiyoruz.

sqldbcreate

Yukarıdaki resimde olduğu gibi ilgili servis için gerekli olan kısımları doldurarak ilerliyoruz. Biz örneğimizde ilk kısımda veri tabanı adı olarak AltintasDb veriyoruz. Standart S0 seçeneği bizler için yeterli olacağından bu seçenekleri değiştirmiyoruz. Ardından daha önce oluşturulmuş bir server’ımız yoksa New SQL database server seçeneğini seçerek ilerliyoruz.

sqldbcreate2

Bir sonraki ekranda ilgili SQL DB server‘ına erişim için kullanacağımız yetkili bir kullanıcı için login ve password belirliyoruz. Datacenter için bölge seçimini yapıp aşağıda bulunan iki seçeneği de aktif hale getiriyoruz. Burada ilk seçenekte Azure’a ait kullanmış olduğumuz diğer servislerin bu database server’ına erişimi için yetki vermiş oluyoruz. İkinci seçenekte ise Azure SQL Database‘in en son versiyonu olan V12 versiyonu olacak şekilde database’i oluşturacağımızı seçiyoruz. Örneğimiz için bu seçenek oldukça önemli çünkü Azure SQL Database için Table Partitioning desteği V12 ile beraber gelen bir özellik olup daha önceki versiyonlarında bu özellik desteklenmemekteydi. Azure SQL Database V12 ile gelen yeniliklere göz atmak isterseniz daha önce yapmış olduğum bir webcast’e ait kayda  bu linkten erişebilirsiniz.

İlgili işlemleri tamamladığımızda Azure SQL Database server‘ı çok kısa bir sürede ayağa kalkacak ve kullanıma hazır hale gelecektir. Bu aşamadan sonra Azure SQL Database servisinde oluşturduğumuz AltintasDb adındaki veri tabanı üzerindeki işlemleri daha rahat yapabilmek için SQL Server Management Studio (SSMS) ile bu database server’ına bağlantı sağlıyoruz. ServerName olarak Azure SQL Database servisinin Dashboard ekranında bulunan servername’i, SQL Server Authentication kısmında ise kullanıcı adı ve şifre olarak az önce oluşturduğumuz yetkili kullanıcı adı ve şifresini giriyoruz. Burada bağlantı kısmında hata alınırsa Azure SQL Database‘e bağlantı için şu an kullanmakta olduğumuz pc’nin IP Adresi firewall rule olarak eklenmesi gerekmektedir. Bu işlem için de SSMS 2016 sürümünde çıkan pop-up ekranından veya Azure portal üzerinde bulunan SQL Database servisindeki Configure kısmından IP Adresimiz için rule tanımlayabilmekteyiz.

Bu aşamadan sonra artık Table Partitioning kullanarak her bir güne ait verilerin ayrı bir partition’da tutulmasını sağlayacak şekilde Partition Function, Partition Scheme ve tablomuzu oluşturuyoruz.

İlk olarak her bir güne ait verileri ayrı bir partition’da tutmak istediğimiz için her bir gün için bir partition oluşturacak şekilde pf_DayOfTheYear isminde bir partition function oluşturuyoruz. Bunun için gerekli olan kodu aşağıda bulabilirsiniz:

CREATE PARTITION FUNCTION [pf_DayOfTheYear](DATE) AS RANGE LEFT FOR VALUES 
(
'2016-01-01',
'2016-01-02',
'2016-01-03',
'2016-01-04',
'2016-01-05',
'2016-01-06',
'2016-01-07',
'2016-01-08',
'2016-01-09',
'2016-01-10',
'2016-01-11',
'2016-01-12',
'2016-01-13',
'2016-01-14',
'2016-01-15',
'2016-01-16',
'2016-01-17',
'2016-01-18',
'2016-01-19',
'2016-01-20',
'2016-01-21',
'2016-01-22',
'2016-01-23',
'2016-01-24',
'2016-01-25',
'2016-01-26',
'2016-01-27',
'2016-01-28',
'2016-01-29',
'2016-01-30',
'2016-01-31',
'2016-02-01',
'2016-02-02',
'2016-02-03',
'2016-02-04',
'2016-02-05',
'2016-02-06',
'2016-02-07',
'2016-02-08',
'2016-02-09',
'2016-02-10',
'2016-02-11',
'2016-02-12',
'2016-02-13',
'2016-02-14',
'2016-02-15',
'2016-02-16',
'2016-02-17',
'2016-02-18',
'2016-02-19',
'2016-02-20',
'2016-02-21',
'2016-02-22',
'2016-02-23',
'2016-02-24',
'2016-02-25',
'2016-02-26',
'2016-02-27',
'2016-02-28',
'2016-02-29',
'2016-03-01',
'2016-03-02',
'2016-03-03',
'2016-03-04',
'2016-03-05',
'2016-03-06',
'2016-03-07',
'2016-03-08',
'2016-03-09',
'2016-03-10',
'2016-03-11',
'2016-03-12',
'2016-03-13',
'2016-03-14',
'2016-03-15',
'2016-03-16',
'2016-03-17',
'2016-03-18',
'2016-03-19',
'2016-03-20',
'2016-03-21',
'2016-03-22',
'2016-03-23',
'2016-03-24',
'2016-03-25',
'2016-03-26',
'2016-03-27',
'2016-03-28',
'2016-03-29',
'2016-03-30',
'2016-03-31',
'2016-04-01',
'2016-04-02',
'2016-04-03',
'2016-04-04',
'2016-04-05',
'2016-04-06',
'2016-04-07',
'2016-04-08',
'2016-04-09',
'2016-04-10',
'2016-04-11',
'2016-04-12',
'2016-04-13',
'2016-04-14',
'2016-04-15',
'2016-04-16',
'2016-04-17',
'2016-04-18',
'2016-04-19',
'2016-04-20',
'2016-04-21',
'2016-04-22',
'2016-04-23',
'2016-04-24',
'2016-04-25',
'2016-04-26',
'2016-04-27',
'2016-04-28',
'2016-04-29',
'2016-04-30',
'2016-05-01',
'2016-05-02',
'2016-05-03',
'2016-05-04',
'2016-05-05',
'2016-05-06',
'2016-05-07',
'2016-05-08',
'2016-05-09',
'2016-05-10',
'2016-05-11',
'2016-05-12',
'2016-05-13',
'2016-05-14',
'2016-05-15',
'2016-05-16',
'2016-05-17',
'2016-05-18',
'2016-05-19',
'2016-05-20',
'2016-05-21',
'2016-05-22',
'2016-05-23',
'2016-05-24',
'2016-05-25',
'2016-05-26',
'2016-05-27',
'2016-05-28',
'2016-05-29',
'2016-05-30',
'2016-05-31',
'2016-06-01',
'2016-06-02',
'2016-06-03',
'2016-06-04',
'2016-06-05',
'2016-06-06',
'2016-06-07',
'2016-06-08',
'2016-06-09',
'2016-06-10',
'2016-06-11',
'2016-06-12',
'2016-06-13',
'2016-06-14',
'2016-06-15',
'2016-06-16',
'2016-06-17',
'2016-06-18',
'2016-06-19',
'2016-06-20',
'2016-06-21',
'2016-06-22',
'2016-06-23',
'2016-06-24',
'2016-06-25',
'2016-06-26',
'2016-06-27',
'2016-06-28',
'2016-06-29',
'2016-06-30',
'2016-07-01',
'2016-07-02',
'2016-07-03',
'2016-07-04',
'2016-07-05',
'2016-07-06',
'2016-07-07',
'2016-07-08',
'2016-07-09',
'2016-07-10',
'2016-07-11',
'2016-07-12',
'2016-07-13',
'2016-07-14',
'2016-07-15',
'2016-07-16',
'2016-07-17',
'2016-07-18',
'2016-07-19',
'2016-07-20',
'2016-07-21',
'2016-07-22',
'2016-07-23',
'2016-07-24',
'2016-07-25',
'2016-07-26',
'2016-07-27',
'2016-07-28',
'2016-07-29',
'2016-07-30',
'2016-07-31',
'2016-08-01',
'2016-08-02',
'2016-08-03',
'2016-08-04',
'2016-08-05',
'2016-08-06',
'2016-08-07',
'2016-08-08',
'2016-08-09',
'2016-08-10',
'2016-08-11',
'2016-08-12',
'2016-08-13',
'2016-08-14',
'2016-08-15',
'2016-08-16',
'2016-08-17',
'2016-08-18',
'2016-08-19',
'2016-08-20',
'2016-08-21',
'2016-08-22',
'2016-08-23',
'2016-08-24',
'2016-08-25',
'2016-08-26',
'2016-08-27',
'2016-08-28',
'2016-08-29',
'2016-08-30',
'2016-08-31',
'2016-09-01',
'2016-09-02',
'2016-09-03',
'2016-09-04',
'2016-09-05',
'2016-09-06',
'2016-09-07',
'2016-09-08',
'2016-09-09',
'2016-09-10',
'2016-09-11',
'2016-09-12',
'2016-09-13',
'2016-09-14',
'2016-09-15',
'2016-09-16',
'2016-09-17',
'2016-09-18',
'2016-09-19',
'2016-09-20',
'2016-09-21',
'2016-09-22',
'2016-09-23',
'2016-09-24',
'2016-09-25',
'2016-09-26',
'2016-09-27',
'2016-09-28',
'2016-09-29',
'2016-09-30',
'2016-10-01',
'2016-10-02',
'2016-10-03',
'2016-10-04',
'2016-10-05',
'2016-10-06',
'2016-10-07',
'2016-10-08',
'2016-10-09',
'2016-10-10',
'2016-10-11',
'2016-10-12',
'2016-10-13',
'2016-10-14',
'2016-10-15',
'2016-10-16',
'2016-10-17',
'2016-10-18',
'2016-10-19',
'2016-10-20',
'2016-10-21',
'2016-10-22',
'2016-10-23',
'2016-10-24',
'2016-10-25',
'2016-10-26',
'2016-10-27',
'2016-10-28',
'2016-10-29',
'2016-10-30',
'2016-10-31',
'2016-11-01',
'2016-11-02',
'2016-11-03',
'2016-11-04',
'2016-11-05',
'2016-11-06',
'2016-11-07',
'2016-11-08',
'2016-11-09',
'2016-11-10',
'2016-11-11',
'2016-11-12',
'2016-11-13',
'2016-11-14',
'2016-11-15',
'2016-11-16',
'2016-11-17',
'2016-11-18',
'2016-11-19',
'2016-11-20',
'2016-11-21',
'2016-11-22',
'2016-11-23',
'2016-11-24',
'2016-11-25',
'2016-11-26',
'2016-11-27',
'2016-11-28',
'2016-11-29',
'2016-11-30',
'2016-12-01',
'2016-12-02',
'2016-12-03',
'2016-12-04',
'2016-12-05',
'2016-12-06',
'2016-12-07',
'2016-12-08',
'2016-12-09',
'2016-12-10',
'2016-12-11',
'2016-12-12',
'2016-12-13',
'2016-12-14',
'2016-12-15',
'2016-12-16',
'2016-12-17',
'2016-12-18',
'2016-12-19',
'2016-12-20',
'2016-12-21',
'2016-12-22',
'2016-12-23',
'2016-12-24',
'2016-12-25',
'2016-12-26',
'2016-12-27',
'2016-12-28',
'2016-12-29',
'2016-12-30',
'2016-12-31'
)

Ardından bu function’ı kullanacak şekilde ps_DayOfTheYear isminde bir partition scheme oluşturuyoruz:

CREATE PARTITION SCHEME [ps_DayOfTheYear] AS PARTITION [pf_DayOfTheYear] ALL TO ([PRIMARY])

Bu işlemin ardından table partitioning yapacak şekilde oluşturduğumuz partition scheme’yı kullanan LogTable ismindeki tablomuzu aşağıdaki şekilde create ediyoruz:

CREATE TABLE dbo.LogTable
(
LogId int identity(1,1),
Ad nvarchar(50),
ResimAd nvarchar(50),
Zaman datetime,
X int,
Y int,
PartitionNo AS (CAST(Zaman as date)) PERSISTED
CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED 
(
	PartitionNo ASC,
	LogId ASC
)ON ps_DayOfTheYear (PartitionNo)
)

Tablomuzda LogId identity değer üreten bir kolon olup PartitionNo ile birlikte Primary Key oluşturmaktadır. Ad, ResimAd, Zaman, X ve Y kolonları uygulama tarafından gönderilen bilgileri tutmak için oluşturulmuştur. PartitionNo kolonu ise Persisted Computed Column olup, Zaman kolonunun değerini date veritipine dönüştürerek kalıcı olarak tutmaktadır. Aynı zamanda PartitionNo kolonu bizim partition scheme‘mız tarafından kullanılacak ve bu kolondaki değerlere göre farklı günler farklı partition’larda saklanacaktır.

Bu aşamadan sonra tablomuz da hazır olduğuna göre Azure Stream Analytics job‘ının output ekranına geri dönebiliriz. Burada output olarak Azure SQL Database tercihini yapıp server, veritabanı, kullanıcı adı, şifre vb. istenilen bilgileri doğru bir şekilde giriyoruz. Aynı zamanda tablo kısmına da AltintasDb altınta oluşturduğumuz LogTable ismini giriyoruz. İşlemler tamamlandığında output’u oluşturup job’ı sayfanın alt kısmından Start butonuna basarak başlatıyoruz.

Yapmış olduğumuz senaryomuzu test etmek için daha önceki yazılarda olduğu gibi uygulamamız üzerinden resimlere tıklayarak click stream verisinin Azure Event Hubs’a yönlendirilmesini sağlıyoruz. Ardından bu veriler Azure Stream Analytics ile analiz edilecek ve job’ın output seçeneğinde belirttiğimiz Azure SQL Database’de bulunan AltintasDb veritabanındaki LogTable adlı tablomuza insert edilecektir. Yalnız burada dikkat ederseniz standart tek bir partition’dan oluşan bir tablo kullanmak yerine ölçeklenebilirlik ve yönetilebilirlik açısından daha performanslı ve faydalı olması için LogTable tablomuzu her günü ayrı bir partition’da tutacak şekilde oluşturduk. Bu nedenle senaryomuz gereği farklı günlerde uygulamayı kullanarak click-stream verisinin tablomuza nasıl insert edildiğini görmek istiyoruz.

Farklı tarihlerde gerekli verinin gönderilmesinin ardından tablomuzdaki verileri sorgulamak için aşağıdaki sorgumuzu çalıştırıyoruz:

SELECT *
FROM dbo.LogTable

Sorgumuzu çalıştırdığımızda sonuçlar aşağıdaki gibi geliyor:

azuresqldbsonuc

Sonuçlardan gördüğümüz kadarıyla uygulamamız üzerinden click- stream olarak gelen veriler 4 Eylül, 5 Eylül, 6 Eylül ve ekranda görmesek de 7 Eylül tarihlerinde tabloya insert edilmiş. Toplamda 48 adet veri tablomuzda bulunmakta. Peki LogTable tablomuz için oluşturduğumuz partition’lar doğru bir şekilde çalışıyor mu onu da test edelim. Her güne ait veriler ayrı bir partition’da tutuluyor mu bakalım. Bunun için aşağıdaki kod bloğunu çalıştırmamız yeterli olacaktır:

SELECT o.name as TabloAdı,
		i.name as IndexAdı, 
		partition_id, 
		partition_number, 
		[rows] as SatırSayısı
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE 
	o.name LIKE '%LogTable%'
	AND [rows] > 0
ORDER BY o.name, i.name DESC, partition_number ASC

Bu sorguda LogTable tablomuzda bulunan partition’lar için her bir partition’da ne kadar veri tutulduğu gösterilmektedir. Sorgunun sonucu aşağıdaki gibidir:

partitionsonuc

Sonuçlardan da görülebileceği üzere LogTable tablomuzda şu ana kadar 4 ayrı partition bulunmaktadır. 248 nolu partition (4 Eylül tarihini tutan partition) 6 kayıt tutarken, 249 nolu partition (5 Eylül) 17, 250 nolu partition (6 Eylül) 21 ve 251 nolu partition (7 Eylül) 4 kayıt tutmaktadır. Görüleceği üzere her güne ait veriler ayrı partition’larda tutulmaktadır. Bu verilerin toplamı tablomuzda tutulan toplam satır sayısı olan 48’e eşittir.

Bu makalemizde çok yüksek miktarda veri üreten sistemlerden stream olarak gelen verilerin Azure SQL Database‘de daha performanslı, ölçeklenebilir ve yönetilebilir olmasını sağlamak için oluşturduğumuz Table Partitioning  yapısını kullanarak parçalı bir şekilde tutulmasını sağladığımız yapıyı ele aldık. Örnek demomuz üzerinde senaryomuzu test ettik ve sonuçları inceledik. Advanced Data Analytics başlığı altındaki diğer yazılarımızla yine karşınızda olacağız. Bir sonraki makalemizde tekrar görüşmek üzere…

Umarım faydalı olur… Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

MERGE Type 4 Slowly Changing Dimension (SCD) Kullanımı

Microsoft SQL Server‘da MERGE komutu ile neler yapılabildiğini serinin daha önceki yazılarında sizlerle paylaşmıştık. Aşağıda MERGE komutunun genel kullanımı, MERGE ile Slowly Changing Dimension (SCD) Type 1 , Type 2 ve Type 3 çözümlerinin nasıl yapılabileceğini aktardığım yazılarım mevcut. Yazının devamına geçmeden bunlara göz atmak isteyebilirsiniz. Eğer ihtiyacınız sadece MERGE komutu ile SCD Type 4 işleminin nasıl yapılabildiği ise direkt olarak makalenin devamına geçebilirsiniz.

  • SQL Server MERGE Komutu Kullanımı ve Performans Önerileri

http://www.abdullahaltintas.com/index.php/sql-server-merge-komutu-kullanimi-ve-performans-onerileri/

  • MERGE Type 1 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-1-slowly-changing-dimension-scd-kullanimi/

  • MERGE Type 2 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-2-slowly-changing-dimension-scd-kullanimi/

  • MERGE Type 3 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-3-slowly-changing-dimension-scd-kullanimi/

Bu makalemizde SQL Server’da kullanılan MERGE komutu yardımı ile Type 4 Slowly Changing Dimension (SCD) işleminin nasıl gerçekleştirilebileceğini ele alacağız. Serinin diğer yazılarında olduğu gibi isterseniz öncelikle Type 4 SCD‘nin ne olduğundan kısaca bahsedelim:

Serinin önceki yazılarında bahsettiğimiz gibi MERGE komutu ile yazdığımız SCD Type 1 yöntemi ile kaynaktan hedef  tabloya daha önce aktarımı yapılmış olan ve ardından kaynakta tekrar güncellenen kayıtlar, hedef tabloda overwrite edilerek yani var olan kaydın üzerinde güncellenerek tutulmaktaydı. Type 2 SCD ile aktarımı yapılan kayıtlarda ise daha önce aktarımı yapılmış olan kayıt pasife çekilerek güncellenmek istenen kaydın yeni halini tutan ve aktif olacak olan yeni bir kayıt tabloya eklenmekteydi. Yani bu yöntem ile verinin historical hali tarihsel versiyonu tutulabilmekteydi. Type 3 SCD kullanılarak yapılan tasarımda ise verinin yine tarihsel geçmişi tutulabilmekteydi ancak bu yöntemde satır bazlı olarak değil var olan kaydın üzerinde tasarım aşamasında eklenen kolonlarda  (denormalize bir şekilde) tarihsel geçmiş tutulabilmekteydi. Type 4 SCD yönteminde ise kaynaktan hedefe aktarımı yapılmış olan ve ardından kaynakta tekrar güncellenen kayıtlar için hedef tabloda verinin en güncel hali tutulmaktadır. Ancak verinin historical geçmişini yani tarihsel versiyonlarını tutabilmek için ayrı bir tablo oluşturulmakta ve MERGE komutu ile verinin güncellenmeden önceki eski hali bu tabloya insert edilmektedir. Yani Type 4 SCD ile verinin tarihsel geçmişi ayrı bir tabloda tutulmaktadır. Bir nevi bu tablo arşiv tablo görevi görmektedir.

Bu kadar teorik bilgiden sonra şimdi isterseniz örneğimize geçelim. Serinin önceki yazılarında olduğu gibi benzer tabloları kullanacağız. Sadece gerekli değişiklikleri yaparak kodumuzu oluşturacağız. Demomuz için gerekli olan tabloların create scriptleri aşağıdaki gibidir:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO

create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO

create table EmployeeTargetArchive
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
StartDate datetime,
EndDate datetime
)
GO

Burada dikkat etmemiz gereken nokta var olan kaynak ve hedef tablolarımıza ilave olarak bir de tarihsel veriyi tutmak açısından EmployeeTargetArchive isminde yeni tablo ekledik. Aynı zamanda bu tabloda ilgili verinin geçerlilik tarihlerini tutabilmek için de StartDate ve EndDate kolonlarını ekledik.

Şimdi de örneğimiz için kaynak ve hedef tablomuza biraz veri girişi yapalım:

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 1)
GO

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
GO

Verileri insert ettikten sonra tablomuzu sorgulayalım ve gelen sonuçları ekranda inceleyelim:

select * from dbo.EmployeeSource
select * from dbo.EmployeeTarget
select * from dbo.EmployeeTargetArchive

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır. Aynı zamanda yeni oluşturduğumuz arşiv datayı tutacak olan EmployeeTargetArchive tablosunda henüz bir kod çalıştırmadığımız için hiç bir veri bulunmamaktadır. MERGE komutunu çalıştırdıktan sonra tarihsel geçmişi tutulacak olan veriler bu tabloya aktarılacaktır.

scdtype4baslangic

Örneğimizdeki amacımız kaynak EmployeeSource tablomuza yeni eklenen ve henüz hedef tablomuz EmployeeTarget‘a eklenmemiş olan kayıtların INSERT edilmesini sağlamak ve daha önce aktarımı yapıldıktan sonra kaynak EmployeeSource tablomuzda güncellenmiş kayıtların güncel hallerinin EmployeeTarget tablosuna aktarılmasını sağlamak olacaktır. Yalnız burada Type 4 SCD yapısını kullanarak aktarım yapacağımız için hedefte var olan kayıt üzerinde overwrite ile gerekli değişiklik yapılacak ve güncel bilgi hedef tablomuz olan EmployeeTarget‘ta tutulacaktır. Ayrıca güncellenen verinin eski versiyonu yani tarihsel geçmişi de EmployeeTargetArchive tablosuna insert edilecek ve dolayısıyla historical verinin ayrı bir tabloda tutulması sağlanacaktır.

Bu isteklerimizi karşılayabilmek için MERGE komutu ile aşağıdaki Type 4 SCD kodunu oluşturup çalıştıracağız:


-- Geçici olarak verilerin aktarılacağı Temp Table görevi gören Table Variable tanımlıyoruz.
DECLARE @ArchiveTemp TABLE
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
MergeAction nvarchar(10)
)

-- Merge komutumuzu aşağıdaki şekilde oluşturuyoruz.
MERGE INTO dbo.EmployeeTarget as t
	USING dbo.EmployeeSource as s
	ON t.EmployeeID = s.EmployeeID	
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
		VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, 1)
	WHEN MATCHED AND 
				ISNULL(t.FirstName, '') <> ISNULL(s.FirstName, '')
				OR ISNULL(t.LastName, '') <> ISNULL(s.LastName, '')
				OR ISNULL(t.Title, '') <> ISNULL(s.Title, '')
				OR ISNULL(t.RecruitmentDate, '') <> ISNULL(s.RecruitmentDate, '')
				OR ISNULL(t.Salary, '') <> ISNULL(s.Salary, '')
				OR ISNULL(t.IsActive, '') <> ISNULL(s.IsActive, '')
		THEN UPDATE SET  
				t.FirstName = s.FirstName,
				t.LastName = s.LastName,
				t.Title = s.Title,
				t.RecruitmentDate = s.RecruitmentDate,
				t.Salary = s.Salary,
				t.IsActive = s.IsActive
	OUTPUT 	DELETED.EmployeeID, 
			DELETED.FirstName, 
			DELETED.LastName, 
			DELETED.Title, 
			DELETED.RecruitmentDate, 
			DELETED.Salary, 
			DELETED.IsActive,
			$Action as MergeAction
	INTO @ArchiveTemp (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, MergeAction);

-- Güncellenen kaydın historical halini de son olarak Arşiv tablomuza ekleyelim.
INSERT INTO dbo.EmployeeTargetArchive (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, StartDate)
SELECT EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, 
		ISNULL((SELECT MAX(EndDate) FROM dbo.EmployeeTargetArchive
		WHERE EmployeeID = Tmp.EmployeeID), RecruitmentDate)
FROM @ArchiveTemp as Tmp
WHERE MergeAction = 'UPDATE'

-- Arşiv tablosunda verilerin aktif olup olmadığını ve güncellenme tarihlerini set edelim.
UPDATE eta
SET eta.EndDate = GETDATE()
FROM dbo.EmployeeTargetArchive as eta
INNER JOIN @ArchiveTemp as tmp
ON eta.EmployeeID = tmp.EmployeeID
WHERE eta.EndDate IS NULL

Yukarıdaki kodları çalıştırdığımızda istemiş olduğumuz sonuçları elde edeceğiz. Kaynak tablomuz EmployeeSource, hedef tablomuz EmployeeTarget ve arşiv tablomuz EmployeeTargetArchive tablolarımızın son durumunu görmek için tekrar sorgulayalım. Sonuçlar aşağıdaki gibi olacaktır:

scdtype4son

Sonuçları incelediğimizde hedefte olmayan ve kaynak tabloda bulunan 5 no‘lu id’ye sahip Şeydanur Sandıkçı‘nın hedef tablomuz olan EmployeeTarget tablosuna eklendiğini görmekteyiz. Aynı zamanda daha önce kaynaktan hedef tabloya aktarımı yapılmış olan 1 no‘lu id’ye sahip Abdullah Altıntaş‘ın maaş bilgisinin tutulduğu Salary kolonundaki değerin de 2000 olarak güncellendiğini ve güncel değerin EmployeeTarget tablosu üzerine yazıldığını görmekteyiz. Son olarak Abdullah Altıntaş‘ın güncellenmeden önceki maaş bilgisini (1000) içeren eski kaydın da historical amaçlı olarak EmployeeTargetArchive tablosuna eklendiğini görebilirsiniz. Ayrıca arşiv tablosuna aktarılan bu kaydın IsActive kolonundaki değerin 0 yani pasif olduğunu görebilir ve StartDateEndDate kolonlarında da bu kaydın geçerli olduğu tarih aralığını bulabilirsiniz.

Peki aynı kayıt üzerinde yeni bir güncelleme yapıldığında sonuçlar nasıl olacak? Abdullah Altıntaş‘ın maaş bilgisi üzerinde tekrar bir güncelleme yapalım:

update EmployeeSource
set Salary = 4000
where EmployeeID =1

Ardından da Type 4 SCD ihtiyacımızı karşılayacak şekilde yazmış olduğumuz MERGE komutunu tekrar çalıştıralım ve tablolarımızı tekrar sorgulayalım:

scdtype4son2

Görmüş olduğunuz gibi en güncel veri hedef tablomuzda overwrite edilerek tutulmakta ve ilgili kaydın eski versiyonu ise arşiv tablosuna geçerlilik tarihlerini de aktaracak şekilde insert edilmektedir. Type 4 Slowly Changing Dimension yönetimi ile yapmış olduğumuz tasarım sayesinde verinin tarihsel geçmişini ayrı bir tabloda saklayabildiğimizi bu şekilde görmüş oluyoruz.

Son olarak Type 4 SCD ile yazdığımız MERGE ifadesini de içeren kod bloğunu kısaca açıklayalım:

  • İlk olarak geçici olarak verilerin tutulacağı bir tane table variable tanımlıyoruz. Burada temp table oluşturup onu da kullanabilirsiniz.
  • İkinci kod bloğunda asıl MERGE komutu işlemleri yapılıyor. İlgili kaydın hedef ve kaynak tablolarda var olup olmadığı kontrol edildikten sonra eğer ilgili kayıt hedef tablosunda yoksa WHEN NOT MATCHED BY TARGET ifadesinde ilgili kayıt hedef tablomuza insert ediliyor.
  • Ardından ilgili veri daha önce hedef tablosuna aktarıldıysa ve üzerinde değişiklik yapıldıysa ne yapılması gerektiğini belirtmek için WHEN MATCHED ifadesi ile bu kontrol gerçekleştiriliyor ve kaynaktan gelen güncel bilgiler hedef tablosunda update ediliyor.
  • Merge komutunun OUTPUT kısmında değişikliğe uğrayan kayıtların eski halleri alınarak en başta oluşturulan table variable’a insert ediliyor.
  • MERGE komutu tamamlandıktan sonra yazılan INSERT komutu ile table variable’da bulunan değişikliğe uğramış kayıtların eski versiyonları asıl arşiv tablomuza insert ediliyor.
  • Son UPDATE komutunda ise ilgili kaydın geçerlilik tarihlerini güncellemek için EndDate kolonu güncelleniyor.

Bu yazımızda SQL Server’da bulunan MERGE komutu kullanılarak Type 4 Slowly Changing Dimension (SCD) yapısının nasıl tasarlanabileceğini ele almış olduk. Yaptığımız örnek ile senaryomuzu uyguladık ve sonuçlarını gözlemledik. Aynı zamanda daha önceki yazılarımızda bahsettiğimiz Type 1, Type 2, Type 3 ve bu yazıda ele aldığımız Type 4 SCD yapılarının çalışma şekillerini karşılaştırdık.  Başka yazılarla tekrar görüşmek üzere…

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

Not: Destekleri için Zafer Dörter‘e teşekkür ederim 🙂 Emeğine, aklına sağlık Zafer hocam 🙂

MERGE Type 3 Slowly Changing Dimension (SCD) Kullanımı

Microsoft SQL Server‘da MERGE komutunun kullanımı ve Slowly Changing Dimension çözümlerinin MERGE komutu ile nasıl yapılabileceğini önceki makalelerimde yazmıştım. Özellikle Type 1 Slowly Changing Dimension (SCD) ve Type 2 Slowly Changing Dimension (SCD) yapılarını ayrı ayrı nasıl çözümleyebileceğimizi daha önce ele almıştık. İhtiyacınız olan tüm SCD yapılarını öğrenmek ve bunların MERGE ile nasıl yapılabildiğini öğrenmek ise yeni konuya başlamadan önce bu makalelere bir göz atmak isteyebilirsiniz. Eğer sadece Type 3 SCD ile ilgileniyorsanız bu durumda direkt olarak makalemize devam edebilirsiniz.

  • SQL Server MERGE Komutu Kullanımı ve Performans Önerileri

http://www.abdullahaltintas.com/index.php/sql-server-merge-komutu-kullanimi-ve-performans-onerileri/

  • MERGE Type 1 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-1-slowly-changing-dimension-scd-kullanimi/

  • MERGE Type 2 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-2-slowly-changing-dimension-scd-kullanimi/

Bu makalemizde Slowly Changing Dimension Type 3 çözümümün ne olduğu ve SQL Server’da MERGE komutu ile nasıl ele alınabileceğini göstereceğiz. Diğer makalelerimizde olduğu gibi yine bu durumunu bir örnek demo üzerinde gerçekleştireceğiz. İsterseniz ilk olarak Type 3 SCD‘nin ne olduğunu ve Type 1 SCD ile Type 2 SCD‘den nasıl ayrıştığını bahsedelim.

Type 1 SCD ile kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler overwrite edilerek hedefte daha önce var olan kaydın üzerinde güncelleme yapılmaktadır. Dolayısıyla update işlemi ile var olan kayıt üzerinde güncelleme çalışmaktadır.

Type 2 SCD ile kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler verinin historical yani tarihsel geçmişini tutabilmek için versiyonlu bir şekilde tutulmaktadır. Bunu gerçekleştirmek için var olan kayıt pasif olarak işaretlenecek ve yeni gelen güncel bilgiyi içeren yeni bir satır hedef tabloya aktif olacak şekilde eklenecektir. Bu şekilde Type 2 SCD ile verinin tarihsel geçmişini de satır bazlı normalize bir yapıda tutulmaktadır.

Type 3 SCD ile ise kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler verinin yine benzer şekilde historical yani tarihsel geçmişini tutabilmek için kullanılabilir. Burada Type 3 SCD’nin Type 2 SCD’den farklı olan tarafı yeni gelen güncel bilgiyi tutmak için yeni bir satırda tutmak yerine denormalize bir şekilde yeni bir kolonda bu bilgiyi tutmaktadır. Dolayısıyla Type 3 SCD ile verinin tarihsel olarak geçmiş bilgisine tasarım anında eklenen versiyon adedi kadar olan ek kolonlar üzerinden erişilebilmektedir.

Type 3 Slowly Changing Dimension‘ın ne olduğundan kısaca bahsettik. Type 2 SCD’ye çok benzediğini ancak verinin historical geçmişini satır bazlı olarak değil sütun bazlı olarak tuttuğunu belirttik. Tabi ki bu şekilde kolon bazlı geçmişi tutmak sadece tablo tasarımına başlarken verinin kaç versiyon geriye yönelik tutulacağının belirlenmesi gerekliliği ve normalizasyon kurallarının uygulanmaması nedeniyle çoğu durumda pek tercih edilmemektedir. Genellikle Type 2 SCD ile verinin tarihsel geçmişini tutmak çok daha verimli ve performanslı olacaktır.

Bu kadar teorik bilgiden sonra hızlıca örneğimize geçelim. Diğer SCD type’larında kullandığımız tablo ve verileri gerektiği kadar değiştirerek bu örnekte de kullanacağız. Bunun için ilk olarak demomuzda kullanacağımız tablolarımızı create edelim:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO

create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
Salary_Previous_1 decimal,
Salary_Previous_1_ValidTo datetime,
Salary_Previous_2 decimal,
Salary_Previous_2_ValidTo datetime
)
GO

Burada daha önceki örneklerden farklı olarak hedef tablomuz olan EmployeeTarget tablosuna 4 adet yeni kolon ekledik. Biz örneğimiz için maaş bilgisi üzerinde yapılan değişikliklerin sadece son 2 versiyonunu tutmak istediğimiz için Salary_Previous_1 kolonu ile maaşın değişmeden önceki son halini yani birinci tarihsel geçmişini tutuyoruz. Aynı şekilde Salary_Previous_2 kolonunda da maaşın daha önceki hali olan ikinci tarihsel geçmiş versiyonunu tutuyoruz. Burada güncelleme yapıldığı tarihleri de Salary_Previous_1_ValidTo ve Salary_Previous_2_ValidTo kolonlarında tutacağız.

Oluşturduğumuz tablolarımıza örnek olması açısından bir kaç adet veri girişi yapalım:

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 1)
GO

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1, NULL, NULL, NULL, NULL),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1, NULL, NULL, NULL, NULL),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1, NULL, NULL, NULL, NULL),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1, NULL, NULL, NULL, NULL)
GO

Burada hedef tablomuzda henüz verinin geçmiş bilgisi olmadığını simule etmek için son eklenen dört kolondaki değerleri NULL olarak bıraktık. Tablomuzu sorgulayalım ve işleme başlamadan önceki halini görelim:

sorgu_baslamadan_once

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuzEmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.

Örneğimizdeki amacımız olan maaş bilgisinin tutulduğu Salary kolonu üzerinde yapılacak olan değişiklikleri Type 3 Slowly Changing Dimension (SCD) ile ele almak olduğu için MERGE komutumuzu bu senaryoyu karşılayacak şekilde yazacağız. Böylece Salary kolonu üzerinde güncelleme yapıldığında yeni bir satır eklenmeyecek, onun yerine güncel maaş bilgisi Salary kolonuna yazılacak, geçmiş maaş bilgisi de Salary_Previous_1 kolonuna aktarılacaktır.

İstediğimiz senaryoyu karşılayabilmek için gerekli olan MERGE komutu aşağıda bulunmaktadır:

MERGE INTO dbo.EmployeeTarget as t
USING dbo.EmployeeSource as s
ON t.EmployeeID = s.EmployeeID
WHEN NOT MATCHED BY TARGET THEN
	INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
	VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, s.IsActive)
WHEN MATCHED AND t.Salary <> s.Salary  THEN
	UPDATE SET  t.Salary = s.Salary,
				t.Salary_Previous_1 = t.Salary,
				t.Salary_Previous_1_ValidTo = GETDATE(),
				t.Salary_Previous_2 = t.Salary_Previous_1,
				t.Salary_Previous_2_ValidTo = t.Salary_Previous_1_ValidTo;

Yukarıdaki MERGE komutunu çalıştırdığımızda ve tablolarımız tekrar sorguladığımızda aşağıdaki gibi bir sonuç karşımıza gelecektir:

scd_type_3_sonra

Sonuçlardan görüleceği üzere Abdullah Altıntaş‘a ait maaş bilgisi değişikliğe uğradığı için ve bu durumu Type 3 SCD ile yönetmek istediğimiz için var olan kaydın Salary kolonu yeni güncel değeri almış ve eski maaş bilgisi Salary_Previous_1 kolonuna aktarılmıştır. Aynı zamanda işlemin gerçekleştiği zaman bilgisi de geçerlilik tarihini tutmak için Salary_Previous_1_ValidTo kolonuna yazılmıştır. Bu arada 5 no’lu id’ye sahip olan Şeydanur Sandıkçı da yeni gelen bir kayıt olduğu için son dört kolon NULL olacak şekilde EmployeeTarget tablosuna eklenmiştir.

Peki ilerleyen zamanlarda ilgili kişiye ait tekrar maaş bilgisi değiştiğinde durum ne olacak? Bunu test etmek için Abdullah Altıntaş‘ın kaynak tablomuz olan EmployeeSource tablosundaki maaş bilgisini 3000 olarak güncelleyelim:

update dbo.EmployeeSource
set Salary = 3000
where EmployeeID = 1

Ardından MERGE komutunu tekrar çalıştırıp neler olduğuna bakalım:

scd_type_3_sonra_2

Yukarıdaki sonuçlarda görüleceği üzere Abdullah Altıntaş‘a ait Salary kolonu en güncel versiyon olan 3000 değerini tutmaktadır. Değişen Salary bilgisi olan 2000 değeri Salary_Previous_1 kolonunda tutulurken, daha önceki maaş olan 1000 değeri ise Salary_Previous_2 kolonunda tutulmaktadır. Aynı zamanda değişikliklerin yapıldığı tarih bilgileri de Salary_Previous_1_ValidTo ve Salary_Previous_2_ValidTo kolonlarında tutulmaktadır. Biz örneğimizde Salary kolonuna ait değişikliklerin sadece son 2 versiyonunu tutacak şekilde tasarladık ancak istenilen durumlarda bu değer ihtiyaca göre tasarım anında değiştirilerek gereklilikler karşılanabilir.

Bu yazımızda Slowly Changing Dimension Type 3 işlemini MERGE komutu ile nasıl tasarlayabileceğimizi ele aldık. Aynı zamanda Type 1 SCD, Type 2 SCD ve Type 3 SCD karşılaştırması yaparak çalışma mekanizmalarının nasıl farklı olduğunu gösterdik. Yapmış olduğumuz örnek ile de Type 3 SCD‘ye bir senaryo üzerinden yaklaşarak sonuçlarını değerlendirdik. Bir sonraki yazımızda MERGE serisinin devamı olarak Type 4 SCD işlemini MERGE komutu ile nasıl yapabileceğimizi ele alacağız.

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

MERGE Type 2 Slowly Changing Dimension (SCD) Kullanımı

Daha önceki yazılarımda MERGE komutunun SQL Server‘da kullanılma seçenekleri ve komut yapısını ele almıştım. Ayrıca MERGE komutu kullanılarak nasıl bir Type 1 Slowly Changing Dimension (SCD) oluşturulabileceğini örnekler üzerinde göstermiştim. Bunlar ile ilgili yazılarıma aşağıdaki linklerden erişebilir ve konunun devamına geçmeden kısaca göz atmak isteyebilirsiniz:

  • SQL Server MERGE Komutu Kullanımı ve Performans Önerileri

http://www.abdullahaltintas.com/index.php/sql-server-merge-komutu-kullanimi-ve-performans-onerileri/

  • MERGE Type 1 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-1-slowly-changing-dimension-scd-kullanimi/

Bu yazımızda ise SQL Server’da MERGE komutu kullanılarak Slowly Changing Dimension (SCD) Type 2 çözümünün nasıl uygulanabileceğini ele alacağız. Örneğimize geçmeden önce kısaca Type 2 SCD‘nin ne olduğunu da açıklayalım. Type1 SCD ile kaynaktan gelen yeni veriler hedef tabloya insert edilmekte ve kaynakta değişikliğe uğramış olan ve hedef tabloya daha önce aktarılmış olan veriler hedefte daha önce bulundukları için update ile overwrite edilerek var olan kayıt üzerinde ilgili değişiklik yansıtılmaktaydı. Type 2 SCD ile ise hedef tabloda olmayan kaynaktan yeni gelen veriler insert edilecek, hedefte bulunan ancak kaynakta değişikliğe uğramış olan kayıtlar overwrite ile üzerine (var olan satır üzerinde) güncelleme yapılmak yerine history tutulabilmesi için eski kayıt pasife çekilecek ve güncel değeri tutan yeni bir kayıt tabloya eklenecektir. Bu nedenle Type 2 SCD ile verinin historical yani tarihsel geçmişinin de tutulabilmesine olanak sağlanmaktadır.

Bu kadar teorik bilgiden sonra örneğimizi gerçekleştirerek demo üzerinde sonuçları inceleyelim. Daha önceki MERGE yazılarımızda kullandığımıza çok benzer bir yapıyı bu örnekte de kullanıyor olacağız. Sadece farklı olarak ilgili satırın güncel kayıt olup olmadığını tutmak için bir flag kolonu (IsActive) ekleyeceğiz. Aynı zamanda kaydın eklenme ve güncellenme tarihlerini elde edebilmemiz için başlangıç ve bitiş tarihlerini tutabileceğimiz 2 kolon (StartDate ve EndDate) daha ekleyeceğiz. Örneğimiz için tabloların oluşturulma scriptleri aşağıdaki gibi olacaktır:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO


create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
StartDate datetime,
EndDate datetime
)
GO

Kaynak ve hedef tablolarımızı oluşturduktan sonra bunlara demomuz için biraz veri girişi yapalım:

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 1)
GO

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1, '20120721', '99991231'),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1, '20090101', '99991231'),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1, '20140103', '99991231'),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1, '20150618', '99991231')
GO

Örneğimizde yeni eklenen IsActive kolonuna şu an örnek verilerin hepsi için 1 yani Aktif kayıt olduğunu girdik. Aynı zamanda kaydın başlangıç tarihi kolonu olan StartDate için RecruitmentDate kolonundaki işe giriş tarihini girdik. Kaydın bitiş tarihi için ise henüz kayıtlar aktif kayıt olduğu için 31.12.9999 tarihini girdik. Burada başka kullanımlar da mevcut olup bunların detaylarına bu makalede girmeyeceğiz. Ancak başka bir yazımızda bu kullanımları da ele alacağız.

Verileri insert ettikten sonra tablomuzu sorgulayalım ve gelen sonuçları ekranda inceleyelim:

select * from dbo.EmployeeSource
select * from dbo.EmployeeTarget

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.

scd2sorgusonucu

Örneğimizdeki amacımız kaynak EmployeeSource tablomuza yeni eklenen ve henüz hedef tablomuz EmployeeTarget‘a eklenmemiş olan kayıtların INSERT edilmesini sağlamak ve daha önce aktarımı yapıldıktan sonra kaynak EmployeeSource tablomuzda güncellenmiş kayıtların güncel hallerinin EmployeeTarget tablosuna aktarılmasını sağlamak olacaktır. Yalnız burada Type 1 SCD yapısını kullanarak var olan kaydın üzerinde overwrite ederek güncelleme yapmak yerine, Type 2 SCD kullanarak personellerin maaşlarında yapılan değişikliklerin geçmişini (historical data) de tutmak istemekteyiz. Bu nedenle Salary kolonu üzerinde Type 2 SCD uygulayacağız.

Bu isteklerimizi karşılayabilmek için MERGE komutu ile aşağıdaki Type 2 SCD kodunu oluşturup çalıştıracağız:

INSERT INTO dbo.EmployeeTarget
SELECT EmployeeID,
		FirstName,
		LastName,
		Title,
		RecruitmentDate,
		Salary,
		IsActive,
		StartDate,
		EndDate		
FROM
	(
	MERGE INTO dbo.EmployeeTarget as t
	USING dbo.EmployeeSource as s
	ON t.EmployeeID = s.EmployeeID	
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, StartDate, EndDate)
		VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, 1, GETDATE(), '99991231')
	WHEN MATCHED AND t.IsActive = 1 AND t.Salary <> s.Salary  THEN
		UPDATE SET  t.IsActive = 0, t.EndDate = GETDATE()
	OUTPUT $Action ActionOut, 
			s.EmployeeID, 
			s.FirstName, 
			s.LastName, 
			s.Title, 
			s.RecruitmentDate, 
			s.Salary, 
			1 as IsActive,
			GETDATE() as StartDate, 
			'99991231' as EndDate			
	) as MergeTbl
WHERE MergeTbl.ActionOut = 'UPDATE';

Yukarıda yazmış olduğumuz MERGE komutunu çalıştırdığımızda istemiş olduğumuz sonucu elde etmiş olacağız. Kaynaktan yeni gelen 5 no’lu id’ye sahip Şeydanur Sandıkçı hedef tablomuza insert edilmiş olacak. Aynı zamanda Salary kolonu üzerinde Type 2 Slowly Changing Dimesnion (SCD) yapısını oluşturduğumuz için Abdullah Altıntaş’a ait maaş bilgisi değiştiği için hedefte daha önce 1000 değerine sahip olan satır historical amaçlı korunmuş ve IsActive kolonu 0 olarak update edilmiştir. Yine benzer şekilde bu kaydın başlangıç zamanı işe giriş tarihi olarak korunmuş ancak bitiş tarihi kolonu olan EndDate güncellemenin yapıldığı tarihi alarak bu kaydın eski kayıt olduğu set edilmiştir. Bunlarla birlikte Abdullah Altıntaş‘a ait yeni bir kayıt eklenmiş ve güncel maaş bilgisi olan 2000 değeri buraya eklenmiştir. Bu satırın başlangıç tarihi olan StartDate kolonu ilgili değişikliğin yapıldığı update tarihini almış durumdadır. EndDate kolonu ise kayıt hala aktif olduğu için 31.12.9999 tarihini göstermektedir.

Son durumda verilerimizi kontrol etmek için tekrar tablolarımızı sorguladığımızda aşağıdaki gibi sonuçlarla karşılaşmaktayız:

scd2sonucseti

İstenilen verinin tarihsel geçmişini tutabileceğimiz yapıyı MERGE Type 2 SCD ile tasarlamış olduk ve sonuçları da yukarıdaki gibi listeledik. Şimdi yazılan MERGE komutunun karışıklığı nedeniyle kısaca parça parça üzerinden geçelim:

  • Kodun ilk kısmında INSERT INTO … SELECT … yapısı kullanılarak select ifadesi içinden dönecek olan verileri hedef tabloya insert etmekteyiz.
  • FROM ifadesinden sonra parantezler içerisine aldığımız MergeTbl ismini verdiğimiz derived table içinde asıl ihtiyacımız olan MERGE komutunu kullanmaktayız.
    • Bu kısımda ilk olarak hedef tablomuzu veriyoruz. Ardından USING ifadesi ile kaynak tablomuzu belirtiyoruz. ON koşulunda da ilgili satırın hedef ve kaynakta bulunup bulunmadığını kontrol etmek için unique kolonlar üzerinden eşleştirme yapıyoruz.
    • İkinci kısımda WHEN NOT MATCHED BY TARGET ifadesi ile daha önce olmayan yeni eklenmesi gereken kayıtlar için INSERT komutunu belirtiyoruz.
    • Üçüncü kısımda WHEN MATCHED ifadesi ile kaynaktan hedefe daha önce aktarılmış kayıtları buluyoruz ve IsActive = 1 koşulu ile aktif olan kayıtları filtreliyoruz. Aynı zamanda Salary kolonu üzerinde Type 2 SCD yapmak istediğimiz için bu kayıtların Salary değerleri eşleşmeyenleri yani değişikliğe uğrayanlarını elde edip güncelliyoruz. Burada historical olarak eski kaydı temsil edebilmesi için ilgili kayıtların IsActive değerini 0 olarak, EndDate değerini de sistemde işlemin yapıldığı tarih olarak güncelleyip kapatıyoruz.
    • Dördüncü kısım olan OUTPUT bölümünde ise değişikliğe uğramış kaydın güncel yeni halini INSERT edebilmek için gerekli olan kolonları ve hesaplamaları OUTPUT‘a gönderiyoruz. $Action ile daha önceki makalelerimizde belirttiğimiz gibi MERGE içinde yapılan işlemi (INSERT, UPDATE, DELETE) elde edebiliyoruz. Burada dikkat etmemiz gereken son nokta ise yeni kayıt güncel kayıt olacağı için IsActive değerini 1 olarak, StartDate kolonunu işlemin yapıldığı tarih olarak, EndDate kolonunu ise 31.12.9999 olarak set etmiş olmamız.
  • En son kısım olan WHERE ifadesinde ise MERGE komutunun yer aldığı derived table içinden sadece UPDATE ile güncelleme yapılmış kayıtları elde edecek şekilde filreliyoruz. Dolayısıyla değişikliğe uğramış olan kayıtların yeni halleri MERGE içinde yazılan OUTPUT ile buraya gönderilecek ve ilk ifade olan INSERT INTO … SELECT … komutu sayesinde güncel kayıt hedef tablomuza ayrı bir satır olarak eklenecektir.

Bu yazımızda daha önce genel hatlarını ve Type 1 SCD yapısını ele aldığımız MERGE komutu ile Type 2 Slowly Changing Dimension (SCD) yapısını nasıl gerçekleştirebileceğimizi ele aldık. Veritabanlarında ilgili kayıt update edildiğinde var olan kayıt üzerine overwrite ederek değil de ilgili satırın hem eski halini hem de güncel halini tutmamıza olanak sağlayan historical veri tutma mekanizmasını MERGE komutu ile nasıl yapabileceğimizi bir örnek ile göstermiş olduk. Bir sonraki yazımızda ise MERGE komutu ile Type 3 SCD işleminin nasıl yapılabileceğini ele alacağız.

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

Microsoft Açık Akademi Yaz Okulu – Microsoft Azure Stream Analytics Sunumu

Her yıl Microsoft Türkiye lokasyonunda düzenlenen ve Türkiye’nin çeşitli üniversitelerinden öğrencilerin katılımıyla gerçekleştirilen Microsoft Açık Akademi Yaz Okulu etkinliğinde bu yıl da sunumlarımızla katılımcılara güncel teknolojiler hakkında bilgi aktardık. Microsoft’un bulut çözümleri platformu olan Microsoft Azure üzerinde veri çözümlerinin neler olduğu ile ilgili katılımcılara sunumlar gerçekleştirdik.

acikakademi foto.jpg-large

Geçtiğimiz hafta gerçekleştirdiğimiz sunumda öncelikle Data Platform MVP’lerinden Koray Kocabaş hocamız Big Data kavramlarından bahsetti ve Big Data konsepti içerisinde neler olduğunu katılımcılara çok güzel örneklerle aktardı. Devamında bana devrettiği sunum kısmında katılımcılara Big Data ve Data Analytics kapsamında önemli bir yer tutan stream olarak gelen verinin ele alınması konusunda Microsoft’un çözümleri olan Azure Event Hubs ve Azure Stream Analytics servisleri ile ilgili katılımcılara bilgiler verdik.

Sunum esnasında 2 farklı senaryo ele aldık ve bunlarla ilgili demo gerçekleştirdik. İlk olarak stream olarak akan verinin analizinin yapılmasının ardından anlık olarak dashboard’larda gerçek zamanlı olarak nasıl raporlanabileceği Microsoft Power BI kullanarak gerçekleştirildi. İkinci case altında ise analizi yapılan verinin Azure SQL Database hizmetinde nasıl depolanabileceği gösterildi. Günün devamında yine Koray Kocabaş hocamız bu depolanan verinin Microsoft Big Data çözümleri ile nasıl yönetilebileceğini gösteren sunumlar ve örneklerle günü tamamladı.

Böyle bir organizasyonda yer almak ve öğrencilere sunum yapmak benim açımdan çok keyifliydi. Öncelikle bu organizasyonun düzenlenmesinde emeği geçenler olmak üzere sunuma katılan ve bir şeyler öğrenmek için can atan bütün katılımcılara çok teşekkür ederim. Benzer etkinliklerde tekrar görüşmek dileğiyle…

acikakademisinif

Yazar: Abdullah ALTINTAŞ

Stream Analytics ile Analiz Edilen Verinin Azure SQL Database’de Depolanması (Demo)

Advanced Data Analytics konu başlığı altında daha önce ele aldığımız makalelerde stream olarak gelen, akan verilerin Microsoft Azure Event Hubs servisi ile elde edilebileceğini ve Microsoft Azure Stream Analytics servisi ile bu verilerin analiz edilebileceğini ele almıştık. Stream Analytics’ten çıkan bu verilerin de Microsoft Power BI ürünü ile bağdaştırılarak gerçek zamanlı olarak raporlanabileceğini göstermiştik. Bu konuda daha önce yazmış olduğumuz makalelere aşağıdaki linklerden erişebilirsiniz:

http://www.abdullahaltintas.com/index.php/real-time-click-stream-analizi-icin-microsoft-azure-cozumleri-serisi/

Stream olarak akan verinin analizi yapıldıktan sonra gerçek zamanlı olarak raporlanması ihtiyacının dışında, daha sonra tekrar ele alınabilmesi için bir veri tabanında depolanması ihtiyacı da karşımıza çıkabilir. Bu makalemizde stream olarak akan verinin Event Hubs ile elde edilmesi ve Stream Analytics ile analiz edilmesinin ardından Azure SQL Database‘de nasıl depolanabileceğini ele alacağız.

Serinin önceki yazılarında kullanmış olduğumuz uygulamayı kullanarak aynı şekilde click stream verisini üreteceğiz ve daha önceki makalemizde ele aldığımız üzere Azure Event Hubs ile bu veriyi elde edeceğiz.

Yine serinin önceki yazılarında ele aldığımız üzere Event Hubs ile elde edilen veriyi Stream Analytics ile analiz edeceğiz. Burada sadece farklı olarak Stream Analytics job’ının output kısmında Power BI yerine Azure SQL Database tercihini yapacağız. Böylelikle analiz edilen veriyi Azure SQL Database içinde oluşturulan tabloda depolayabileceğiz.

Verilerin Azure SQL Database hizmetinde tutulabilmesi için bu aşamada ya var olan hazır bir Azure SQL Database servisini kullanarak ya da yeni bir Azure SQL Database oluşturarak işlemlerimizi tamamlıyoruz. Yeni bir Azure SQL DB oluşturabilmek için Azure portal üzerinde alt tarafta bulunan New sekmesine tıklıyoruz ve Data Services altında bulunan SQL Database hizmetini seçiyoruz. Quick Create ile veya Custom olarak oluşturacağımız database’e bir isim veriyoruz. Örneğimizde biz “altintasdb” adını vermiş olduk. Ayrıca oluşturulma aşamasında yetkili bir kullanıcı adı ve şifreyi belirleyerek ilerliyoruz.

Azure SQL Database servisi oluşturulduktan sonra erişim için gerekli izinlerin ve firewall kurallarının oluşturulması gerekmektedir. Lokal bilgisayarımızda bulunan SQL Server Management Studio (SSMS) ile Azure SQL Database’e bağlanabilmek için kullanmakta olduğumuz IP adresine erişimi açmamız gerekmektedir. Bu işlemi Azure SQL Database sayfasında bulunan Server kısmındaki Configure tabına gelerek Ip Rule (Add rule diyerek) tanımlayabilirsiniz. Aynı zamanda Azure SQL Database’in diğer Azure servisleri tarafından kullanılabilmesi için de aynı sayfanın alt kısmında bulunan izin verme kısmında gerekli seçeneği Yes olarak işaretleyerek aktif hale getiriyoruz. İlgili değişiklikleri yaptıktan sonra sayfanın altında bulunan Save butonu ile değişiklikleri kaydediyoruz.

sqldb_configure

Bu aşamadan sonra Azure SQL Database hizmeti kullanıma hazır hale gelecektir. İlgili database’e daha rahat erişim için SSMS üzerinden gerekli connection’ı oluşturuyoruz. Burada server name kısmına Azure portal üzerinden database sekmesinde bulunan Server Name’i ([uniqueisim].database.windows.net şeklinde olacak) yazarak bağlanıyoruz. Ayrıca Azure SQL Database’e bağlantı için Windows Authentication desteklenmediği için SQL Authentication seçerek Azure SQL Database oluştururken girilen yetkili kullanıcı adı ve şifresini yazarak bağlantı sağlıyoruz.

sqldb_connection

Bağlantıyı oluşturduktan sonra Azure SQL Database’de bulunan altintasdb‘nin altında altintas_table isminde akan verilen tutulacağı bir tablo oluşturuyoruz. Tablonun create scripti aşağıdaki gibi olacaktır:

CREATE TABLE altintas_table
(
Ad nvarchar(50),
ResimAd nvarchar(50),
Zaman nvarchar(50) primary key,
X nvarchar(50),
Y nvarchar(50)
)
GO

Bu kodu çalıştırarak ilgili verilerin tutulacağı altintas_table oluşturulduktan sonra artık Azure Stream Analytics‘in output seçeneğini ayarlayabiliriz. Bunun için Azure portal üzerinde Stream Analytics servisinin output sekmesine geliyoruz ve aşağıdaki şekilde Azure SQL Databse seçeneğini seçiyoruz.

output_sqldb

Bir sonraki gelen ekranda output alias, database name, username, password ve table name alanlarını daha önce konfigure ettiğimiz şekilde girerek işlemi aşağıdaki şekilde tamamlıyoruz.

output_sqldb2

Bu aşamadan sonra artık ilgili Stream Analytics job’ını çalıştırarak akan verilerin output olarak database’e kaydedilmesini sağlayabiliriz. Serinin daha önceki yazılarında belirtildiği gibi job’ın bulunduğu sayfanın alt kısmında bulunan Start butonuna tıklayarak ilgili job’ı başlatabilirsiniz.

Buraya kadar yapılan işlemler başarılı bir şekilde tamamlandıysa ve Stream Analytics job’ı başarılı bir şekilde başlatılabildiyse uygulamamız üzerinde gerçekleştirilen click’ler anlık olarak Event Hubs tarafından toplanacak, Stream Analytics ile analiz edilecek ve ardından tanımlanan output ile Azure SQL Database’de bulunan altintas_table ismindeki tabloya kayıt edilecektir. Tablomuzu aşağıdaki gibi sorgulayıp verilerin geldiğinden emin olalım:

sql_query_out

Görmüş olduğunuz gibi uygulama üzerinde yapılan her bir tıklamaya ait veriler oluşturulan tabloya kaydedilmektedir. Tıklamayı geçekleştiren kullanıcının adı, tıklanan remin adı, tıklamanın gerçekleştiği zaman, tıklanan remin X ve Y koordinat bilgileri ilgili tabloya satır satır kaydedilmektedir.

Bu yazımızda Azure Event Hubs, Azure Stream Analytics ve Azure SQL Database çözümlerini kullanarak akan verinin analiz edilmesi ve ardından bir veritabanında saklanması için gereken çözümleri ele almış olduk. İlerleyen günlerde Advanced Data Analytics konu başlığı altında yeni yazılarımızla yine birlikte olmak üzere…

Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

MERGE Type 1 Slowly Changing Dimension (SCD) Kullanımı

Serinin daha önceki yazılarında MERGE komutunun genel kullanımını ve performans önerilerini ele almıştık. İncelemek isterseniz aşağıdaki linkten erişebilirsiniz:

http://www.abdullahaltintas.com/index.php/sql-server-merge-komutu-kullanimi-ve-performans-onerileri/

Veritabanı sistemlerinde ETL süreçleri ile veri aktarımı çok sık karşılaştığımız durumlardan biridir. Özellikle OLTP sistemlerden DataWarehouse yapılarına veri aktarırken ETL süreçlerini ve toollarını kullanarak aktarımlar gerçekleştirmekteyiz. Microsoft SQL Server ürün ailesinde de bu amaçla kullanabileceğimiz SQL Server Integration Services (SSIS) adında bir servis bulunmaktadır. SSIS servisi ve SQL Server Data Tools (eski adıyla Business Intelligence Development Studio) kullanılarak ETL süreçleri tasarlanabilmektedir.

Temel ETL süreçlerinde en sık karşılaştığımız noktalardan biri daha önce aktarımı yapılmış olan bir tablodaki verilerin incremental bir yapıda (sadece değişen verilerin) ele alınması sürecidir. Daha önce kaynak sistemden verileri alınmış ve hedef sisteme yüklenmiş bir tablo için bütün verinin doldur boşalt yöntemiyle (verilerin silinip tekrar yüklenmesi) tekrar yüklenmesi performans açısından bizleri olumsuz etkilemektedir. Dolayısıyla en son aktarımdan sonra sadece değişikliğe uğramış olan kayıtların ve yeni eklenen kayıtların ele alınması performansımızı arttıracaktır. Bu işleme literatürde Slowly Changing Dimension (SCD) denilmektedir. SSIS içerisinde de bu işlemi gerçekleştirmek için kurulumla birlikte gelen SCD komponenti bulunmaktadır. Ancak bu SCD komponenti row by row çalıştığı için büyük boyutlara sahip tablolarda işlem yaparken performans açısından oldukça yavaş çalışmaktadır. SCD kullanımına alternatif olarak SQL Server 2008 ile birlikte hayatımıza giren MERGE komutu kullanılarak çok daha performanslı bir çözüm üretilebilmektedir. Bu yazımızda MERGE komutunu kullanarak Type 1 SCD çözümünü nasıl gerçekleştirebileceğimizi ele alacağız.

Öncelikle örnekleri yapabilmemiz için gerekli olan kaynak ve hedef tablolarımızı oluşturalım. Örneğimizde EmployeeSource isminde bir kaynak tablomuzu ve aynı yapıya sahip EmployeeTarget isminde bir hedef tablomuzu kullanacağız. Bu iki tablonun scriptleri aşağıdaki gibidir:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO


create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO

Bu iki tabloyu create ettikten sonra içine örneğimizi gerçekleştireceğimiz birkaç tane veri girişi yapalım:

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
GO

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 0)
GO

Ardından bu komutları da çalıştırdıktan sonra tablolarımız üzerindeki verileri kontrol edelim:

select * from dbo.EmployeeSource
select * from dbo.EmployeeTarget

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.

kaynak ve hedef

Örneğimizdeki amacımız kaynak EmployeeSource tablomuza yeni eklenen ve henüz hedef tablomuz EmployeeTarget‘a eklenmemiş olan kayıtların INSERT edilmesini sağlamak ve daha önce aktarımı yapıldıktan sonra kaynak EmployeeSource tablomuzda güncellenmiş kayıtların güncel hallerinin EmployeeTarget tablosuna aktarılmasını sağlamak olacaktır. Böylece Type 1 SCD yapısını MERGE komutu ile kullanarak sadece değişikliğe uğramış olan kayıtların işlenmesini sağlamış olacağız. Bu açıdan tablomuzda sadece maaş kolonu üzerinde yapılan değişikliklerin hedef tablo üzerinde halihazırda bulunan kayıt için overwrite edilerek güncellenmesini sağlayacağız.

 Bu işlemi gerçekleştirmek için aşağıdaki kod bloğunu çalıştırmamız gerekmektedir;

MERGE INTO dbo.EmployeeTarget as t
USING dbo.EmployeeSource as s
ON t.EmployeeID = s.EmployeeID
WHEN MATCHED AND t.Salary <> s.Salary  THEN
	UPDATE SET  t.Salary = s.Salary
WHEN NOT MATCHED BY TARGET THEN
	INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
	VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, s.IsActive);

Yukarıdaki kod bloğu ile kaynak ve hedef tablolarımızda bulunan veriler EmployeeID kolonları üzerinden kontrol edilerek ilgili kaydın daha önce aktarımının yapılmış olup olmadığını kontrol edilmektedir. Ardından her iki tabloda da eşleşen EmployeeID’ler daha önce aktarımı yapılmış olan kayıtları temsil ettiği için bu kayıtların Salary kolonunda değişiklik yapılıp yapılmadığını kontrol edilmektedir. Değişikliğe uğramış olan kayıtlar için UPDATE komutu ile ilgili kaydın maaş bilgisi güncellenmektedir. Bu şekilde ilgili kayıt overwrite edilerek maaş bilgisi güncellenmekte ve Type 1 SCD gerçekleştirilmektedir.

Aynı zamanda EmployeeID’leri eşleşmeyen kayıtlar için de henüz aktarımı yapılmadığı için INSERT komutu kullanılarak hedef tabloya eklenmesi sağlanmaktadır. Yukarıdaki komutu çalıştırdığımızda aşağıdaki gibi bir çıktı üretmektedir;

merge type1

Görüldüğü üzere 1 nolu kayıt Abdullah Altıntaş’a ait Salary kolonundaki bilgi 2000 olarak update edilmiş ve ilgili kayıt overwrite edilerek bu güncelleme yapılmıştır. Aynı zamanda 5 nolu kayıt Şeydanur Sandıkçı da yeni gelen bir kayıt olduğu için EmployeeTarget tablosuna INSERT edilmiştir.

Bu yazımızda MERGE komutunu kullanarak Slowly Changing Dimension (SCD) Type 1 işlemini nasıl gerçekleştirebileceğimizi ele almış olduk. Yaptığımız örnek üzerinde de değişikliğe uğrayan kayıtların nasıl overwrite edildiğini görmüş olduk. Bir sonraki yazımızda MERGE komutu kullanılarak Type 2 SCD işlemini nasıl gerçekleştirebileceğimizi göstereceğiz. Umarım faydalı olur.

Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

 

Veritabanı Kavramı ve Veritabanı Yönetim Sistemleri (Level 1 – 01) – Video

Uzun zamandır teknik konularla ilgili makaleler yazmaktayım. Makale yazmak ve sorunlara ait çözümleri sunmak gerçekten keyifli oluyor. Ancak bazen bir sorunun çözümü için çok fazla ekran görüntüsü ekleme ihtiyacı olabiliyor. Bu nedenle bazı konuları makale olarak yazmak yerine video çekimi yaparak paylaşmanın da oldukça yararlı olacağının farkına vardım. Bu nedenle uzun süredir yapmak istediğim video serisine bu hafta itibarıyla başlıyorum. İlk olarak Microsoft SQL Server’a ait konuları Level – 1 (Başlangıç),  Level – 2 (Orta) ve Level – 3 (İleri Seviye) olacak şekilde sınıflandırarak çekeceğim. Çektiğim video serilerini youtube kanalım üzerinden sizlerle paylaşıyor olacağım. Ayrıca içeriğin dağılmaması açısından çektiğim videoları fırsat buldukça www.abdullahaltintas.com adlı blogumda da paylaşıyor olacağım.

İlk olarak Başlangıç düzeyi konuları ele aldığım videoları bu video ile birlikte yayınlamaya başlıyorum. Umarım faydalı olur.

Bu video içinde;

  • Data & Database Concepts
  • Relational Database Management Systems (RDBMS)
  • SQL Server as a RDBMS
  • SQL Server Editions & Versions
  • SQL Server Management Studio (SSMS)

konularını bulabilirsiniz. Ayrıca slayt setini de aşağıya ekliyorum.

Keyifli izlemeler 🙂

SlideShare Linki:

http://www.slideshare.net/AbdullahALTINTA/veritaban-kavram-ve-veritaban-ynetim-sistemleri-level-1-01-63526506

Ayrıca slayta aşağıdan da erişebilirisniz;