كيفية تقسيم سلسلة بشكل فعال باستخدام وظيفة STRING_SPLIT في خادم SQL

على الرغم من أنه من المبهج والساحر أن تكون مهندس بيانات هذه الأيام، إلا أن ذلك يأتي مع مجموعة التحديات والفرص الخاصة به. يتم قضاء ما يقرب من 80 بالمائة من الوقت في تنظيف وجمع البيانات الأولية التي تأتي في تنوع وتعقيد متزايد من أجل تسهيل الاحتياجات التحليلية المعقدة؛ واحد منهم هو تحليلات السجل. سواء كان ذلك سجلات الأحداث أو سجلات المعاملات؛ فهي تساعدنا في الكشف عن الأنماط المخفية وتمكين اتخاذ قرارات أفضل. على سبيل المثال، تحتوي سجلات الأحداث مثل سجلات Active Directory على معلومات حيوية يمكن أن تساعدنا في تتبع سلوك المستخدم وأي نشاط مشبوه.

دعونا نأخذ مثالاً على سجل AD لنشاط تسجيل الخروج. يحتوي على السجل التالي داخل عمود:

الآن، نريد معالجة عمود "الرسالة" للحصول على عرض دلالي مثل هذا:

الصورة 2

لحسن الحظ، يتم تنظيم رسائل السجل، ويقوم الفاصل بفصل كياناتها بشكل منهجي. في الحالة المذكورة أعلاه، يتم فصل كل كيان في رسالة السجل بحرف سطر جديد أو حرف (10) في SQL. ومن ثم، نحتاج إلى تقسيم عمود الرسالة بناءً على الفاصل char(10). هنا يأتيSTRING_SPLITالوظيفة، والتي تم تقديمها في SQL Server 2017. فيما يلي دليل خطوة بخطوة للتحويل المطلوب:

1. تحميل البيانات الأولية في التدريج:

باستخدام Azure Data Factory، قم بتحميل البيانات الموجودةأزور داتا ليك الجيل الثانيإلى جدول التدريج Azure SQL DB يسمى ALogs. هيكل جدول ALogs هو كما يلي:

/****** 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

لقد قمت بإضافة عمود هوية يسمى LogNumber إلى هذا الجدول.

يمكنك استخدام هذانموذجلإنشاء نشاط نسخ في Azure Data Factory، أو يمكنك إنشاء نشاط نسخ خاص بكنشاط النسخفي وحدة التغذية التلقائية للمستندات. بمجرد تحميل البيانات في قاعدة بيانات SQL، تبدو هكذا.

2. استخدمSTRING_SPLIT()لتقسيم الرسالة:

الآن لتقسيم الرسالة إلى خلايا مختلفة، علينا استخدام الوظيفة المضمنة المقدمة في SQL Server 2017. ومن أجل فهم أهمية وظيفة التقسيم هذه، من الضروري إلقاء نظرة على الطريقة التقليدية للقيام بذلك، أي عن طريق إنشاء وظيفة محددة من قبل المستخدم. فيما يلي تعريف وظيفة UDF:

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

لاحقًا لاستدعاء وظيفة Split_String هذه، نستخدم التطبيق المتقاطع الذي يشبه الانضمام الداخلي. يصبح هذا ضروريًا لأننا نحتاج إلى الاحتفاظ بأعمدة أخرى من الصف الذي تم تقسيم الرسالة فيه. دعونا نأخذ صفًا واحدًا ونطبق دالة الانقسام.

إليك مقتطف الكود:

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

تبدو النتيجة كما يلي:

ومع ذلك، باستخدام الوظيفة المضمنة STRING_SPLIT، يمكننا تخطي تعريف الوظيفة واستخدامها مباشرة. تأخذ الدالة مدخلين، أي "السلسلة" و"الفاصل"، بينما تقوم بإرجاع متغير ذو قيمة في الجدول يسمى "القيمة".

إليك كيفية استخدامه مع التطبيق المتقاطع:

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

تبدو النتيجة كما يلي:

تعد STRING_SPLIT وظيفة ضرورية للحصول على البيانات بالتنسيق المعروض أعلاه. علاوة على ذلك، نحتاج إلى إجراء المزيد من التحويلات لإحضار البيانات بالشكل المعروض في الصورة 2.

3. تنقية البيانات وتحويلها:

نقوم بتحميل النتيجة أعلاه في جدول مؤقت بالكود التالي:

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

بعد ذلك، نقوم بتنظيف جميع البيانات غير المرغوب فيها في عمود الرسالة، أي الصفوف الفارغة والصفوف التي لا تحتوي على الحرف ':'

احذف من #TEMP_LOGS حيث القيمة = "أو القيمة ليست مثل '%:%'

التالي نستخدممؤشر شاروظيفة لتقسيم العمود "VALUE" بناءً على الحرف ":" باستخدام الكود التالي:

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

أخيرًا، نقوم بإجراء "upsert"، أي الإدراج والتحديث للحصول على العرض المطلوب باستخدام الكود التالي:

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 يمكن أن تختلف بناءً على العرض الدلالي المطلوب والبيانات. ومع ذلك، SPLIT_STRING هي الخطوة الأساسية لإدخال الخلية شبه المنظمة في نتيجة ذات قيمة لجدول منظم.

خاتمة

نأمل أن يكون هذا المنشور مفيدًا في إجراء "تقسيم السلسلة" بشكل فعال باستخدام T-SQL على SQL Server. إذا كان لديك أي أسئلة أو استفسارات، فلا تتردد في استخدام مربع التعليق أدناه. شكرا لزيارتكم والتعلم سعيدة!

إقرأ أيضاً:إليك خدعة رائعة لتقسيم ملفات PDF باستخدام Google Chrome