Bugünlerde Veri Mühendisi olmak gösterişli ve göz alıcı olsa da, kendine has zorlukları ve fırsatları da beraberinde getiriyor. Karmaşık analitik ihtiyaçları kolaylaştırmak amacıyla artan çeşitlilik ve karmaşıklıkla gelen ham verilerin temizlenmesi ve temizlenmesi için yaklaşık yüzde 80 zaman harcanıyor; bunlardan biri log analitiğidir. İster olay günlükleri ister işlem günlükleri olsun; gizli kalıpları ortaya çıkarmamıza ve daha iyi karar vermemizi sağlamamıza yardımcı olurlar. Örneğin, Active Directory günlükleri gibi olay günlükleri, kullanıcı davranışını ve şüpheli etkinlikleri izlememize yardımcı olabilecek hayati bilgiler içerir.
SQL sunucusundaki STRING_SPLIT işlevi:
Oturum kapatma etkinliği için AD Günlüğü örneğini ele alalım. Bir sütunun içinde aşağıdaki günlüğü içerir:
Şimdi aşağıdaki gibi anlamsal bir görünüm elde etmek için 'Mesaj' sütununu işlemek istiyoruz:
Resim 2
Neyse ki, günlük mesajları yapılandırılmıştır ve bir ayırıcı, bunların varlıklarını sistematik olarak ayırır. Yukarıdaki durumda, bir günlük mesajındaki her varlık, SQL'de yeni satır karakteri veya char(10) ile ayrılır. Bu nedenle, mesaj sütununu ayırıcı karaktere (10) göre bölmemiz gerekir. İşte geliyorSTRING_SPLITSQL Server 2017'de tanıtılan işlev. Gerekli dönüşümün adım adım kılavuzu aşağıda verilmiştir:
1. Ham verileri aşamalandırmaya yükleyin:
Azure Data Factory ile yerleşik verileri yükleyinAzure Veri Gölü gen2ADLogs olarak adlandırılan Azure SQL DB hazırlama tablosuna. ADLog'ların tablo yapısı aşağıdaki gibidir:
/****** Object: Table [dbo].[ADLog] Script Date: 4/15/2019 3:45:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ADLog]( [LogNumber] [int] IDENTITY(1,1) NOT NULL, [Keywords] [varchar](100) NULL, [DateandTime] [varchar](100) NULL, [Source] [varchar](500) NULL, [EventID] [int] NULL, [TaskCategory] [varchar](50) NULL, [User] [varchar](50) NULL, [Computer] [varchar](200) NULL, [Message] [varchar](4000) NULL ) ON [PRIMARY] GO
Bu tabloya LogNumber adında bir kimlik sütunu ekledim.
Bunu kullanabilirsinşablonAzure Data Factory'de bir kopyalama etkinliği oluşturmak için veyaetkinliği kopyalaADF'de. Veriler SQL Veritabanına yüklendikten sonra şunun gibi görünür.
2. KullanSTRING_SPLIT()mesajı bölmek için:
Şimdi mesajı farklı hücrelere bölmek için SQL Server 2017'de tanıtılan yerleşik işlevi kullanmamız gerekiyor. Bu bölme işlevinin önemini anlamak için, bunu yapmanın geleneksel yoluna, yani kullanıcı tanımlı bir işlev oluşturmaya bakmak zorunludur. UDF işlevinin tanımı şöyledir:
create function split_String ( @input nvarchar(max), @character char(1) ) returns @output table ( item nvarchar(1000) ) as begin declare @startindex int, @endindex int set @startindex = 1 if substring(@input, len(@input) - 1, len(@input)) <> @character begin set @input = @input + @character end while charindex(@character, @input) > 0 begin set @endindex = charindex(@character, @input) insert into @output(item) select substring(@input, @startindex, @endindex - 1) set @input = substring(@input, @endindex + 1, len(@input)) end return end go Code Reference
Daha sonra bu split_String işlevini çağırmak için Inner join'e benzeyen cross application'ı kullanırız. Mesajın bölündüğü satırın diğer sütunlarını korumamız gerektiğinden bu gerekli hale gelir. Bir satırı alıp split fonksiyonunu uygulayalım.
İşte kod pasajı:
SELECT TOP 1 * FROM ADLog WHERE TaskCategory = 'Logoff' SELECT T.*, S.Item FROM DBO.ADLOG T CROSS APPLY split_String(T.[MESSAGE], CHAR(10)) S WHERE LogNumber="4" ORDER BY LOGNUMBER
Sonuç şuna benzer:
Ancak STRING_SPLIT yerleşik işleviyle işlev tanımını atlayıp doğrudan kullanabiliriz. İşlev, 'dize' ve 'ayırıcı' olmak üzere iki girdi alır ve 'değer' adında tablo değerli bir değişken döndürür.
Çapraz uygulama ile bunu şu şekilde kullanabilirsiniz:
Daha fazla bilgi edin:Lego Star Wars: The Skywalker Saga'da Bölünmüş Ekran ve Kumanda Sorunlarını Düzeltme
SELECT TOP 1 * FROM ADLog WHERE TaskCategory = 'Logoff' SELECT T.*, S.VALUE FROM DBO.ADLOG T CROSS APPLY STRING_SPLIT(T.[MESSAGE], CHAR(10)) S WHERE LogNumber="4" ORDER BY LOGNUMBER
Sonuç şuna benzer:
Bu STRING_SPLIT, verileri yukarıda gösterilen biçimde almak için gerekli bir işlevdir. Ayrıca verileri Resim 2'de gösterilen forma getirmek için daha fazla dönüşüm yapmamız gerekiyor.
3. Veri temizleme ve dönüştürme:
Yukarıdaki sonucu aşağıdaki kodla geçici bir tabloya yüklüyoruz:
SELECT T.*, S.VALUE INTO #TEMP_LOGS FROM DBO.ADLOG T CROSS APPLY STRING_SPLIT(T.[MESSAGE], CHAR(10)) S WHERE LOGNUMBER= 4 ORDER BY LOGNUMBER
Daha sonra mesaj sütunundaki tüm istenmeyen verileri, yani boş satırları ve ':' karakterini içermeyen satırları temizliyoruz.
#TEMP_LOGS'TAN SİLİN NEREDE DEĞER = ” VEYA DEĞER '%:%' GİBİ DEĞİLDİR
Daha sonra kullanıyoruzCharIndexAşağıdaki kodu kullanarak 'VALUE' sütununu ':' karakterine göre bölme işlevi:
SELECT *, LTRIM(RTRIM(LEFT(VALUE, CHARINDEX(':', VALUE) - 1))) AS TITLE,
LTRIM(RTRIM(REVERSE(LEFT(REVERSE(VALUE), CHARINDEX(':', REVERSE(VALUE)) - 1)))) AS [DATA] INTO #FINALDATA
FROM #TEMP_LOGS WHERE LOGNUMBER =4
SELECT TITLE, DATA FROM #FINALDATA
Son olarak, aşağıdaki kodla istenen görünümü elde etmek için bir 'upsert' yani ekleme ve güncelleme gerçekleştiriyoruz:
INSERT INTO LogoffData SELECT DATEANDTIME,COMPUTER,NULL, NULL FROM #FINALDATA GROUP BY DATEANDTIME,COMPUTER,LOGNUMBER UPDATE LogoffData SET SECURITYID= B.[DATA] FROM #FINALDATA B WHERE B.TITLE LIKE '%SECURITY ID%'AND B.LOGNUMBER =4 UPDATE LogoffData SET [ACCOUNTDOMAIN]= B.[DATA] FROM #FINALDATA B WHERE B.TITLE LIKE '%ACCOUNT DOMAIN%'AND B.LOGNUMBER =4 SELECT * FROM LogoffData
STRING_SPLIT uygulamasından sonraki dönüşümlerin istenen semantik görünüme ve verilere göre değişebileceğini lütfen unutmayın. Ancak SPLIT_STRING, yarı yapılandırılmış hücreyi yapılandırılmış tablo değerli bir sonuca dönüştürmenin anahtar adımıdır.
Çözüm
Bu gönderinin, SQL Server'da T-SQL kullanarak etkili bir şekilde "dize bölünmesi" yapılmasına yardımcı olduğunu umuyoruz. Herhangi bir sorunuz veya endişeniz varsa aşağıdaki yorum kutusunu kullanmaktan çekinmeyin. Ziyaretiniz için teşekkürler ve mutlu öğrenim!