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Ş

Yorum Yaz