خدمات یکپارچه سازی سرور SQL وظایف CDC برای بارگذاری اطلاعات افزایشی

اردیبهشت ۲۲, ۱۴۰۳
ارسال شده توسط
SQL
SQL

دوستان عزیز , این مقاله از یک سایت خارجی ترجمه شده اگر اشتباهی دیدید , به بزرگواری خودتون ببخشید و ممنون میشیم از طریق راههای ارتباطی (ایمیل , تلگرام یا واتساپ ) به اطلاع ما برسانید . سپاس 

مسئله

انبارهای داده SQL Server حاوی حجم زیادی از داده ها از چندین منبع داده ناهمگن هستند. با این حال، در طول فرآیند استخراج داده ها، یک چالش بزرگ این است که فقط تغییراتی را که پس از آخرین بارگذاری در انبار داده رخ داده است، بازیابی کنیم. از هر منبع داده می‌توان تغییرات، اضافات و حذف‌هایی را انجام داد که برای شناسایی آنچه که با آنچه قبلاً در انبار داده وجود دارد، یک چالش است. از آنجایی که بارگیری کل مجموعه داده قابل اجرا نیست، باید مکانیسم بهتری برای بازیابی فقط تغییرات برای به‌روزرسانی‌های افزایشی داده وجود داشته باشد.

راه حل

راه حل های کمی برای مقابله با این چالش وجود دارد که از بین آنها می توانید بهترین را متناسب با محیط خود انتخاب کنید. در اینجا چند گزینه وجود دارد:

  • به طور معمول، در منبع داده می‌توانید ستون‌های حسابرسی مانند UpdatedDateTime یا ModifiedDateTime داشته باشید . این ستون ها را می توان فیلتر کرد تا رکوردهای جدید اضافه شده و اصلاح شده را پیدا کند. یک مشکل احتمالی عملکرد نیاز به اسکن کل مجموعه رکورد برای یافتن ردیف‌های خاص است، مگر اینکه شاخص‌هایی روی این ستون‌ها داشته باشید تا تأثیر عملکرد را به حداقل برسانید. همچنین، رکوردهایی که به صورت فیزیکی از جداول حذف می شوند، برای به روز رسانی لازم قابل شناسایی نیستند.
  • از تریگرها نیز می توان برای ثبت تغییرات در رکوردها استفاده کرد. با این حال، محرک ها می توانند هزینه عملکرد را به سیستم های تراکنش موجود اضافه کنند.
  • Change Data Capture (CDC) یکی از ویژگی‌های SQL Server Enterprise Edition است که از شماره‌های ترتیب گزارش تراکنش (LSN) برای شناسایی عملیات درج، به‌روزرسانی و حذف که در یک بازه زمانی مشخص رخ داده‌اند، استفاده می‌کند. CDC را می توان در نسخه Enterprise SQL Server 2008 یا نسخه جدیدتر پیاده سازی کرد. اگرچه از گزارش تراکنش استفاده می کند، نیازی نیست پایگاه داده در مدل بازیابی کامل باشد و CDC را می توان حتی در مدل بازیابی ساده فعال کرد. ما از وظایف CDC در SSIS استفاده خواهیم کرد تا ببینیم چگونه می توان این کار را انجام داد.

مثال : تنظیم تغییر ضبط داده (CDC)

بیایید یک جدول نمونه برای نشان دادن استفاده از CDC ایجاد کنیم.

از Master Master استفاده کنید
GO GO
ایجاد پایگاه داده SourceDB ایجاد پایگاه داده SourceDB
GO GO
استفاده از SourceDB استفاده از SourceDB
GO IF
IF EXISTS (انتخاب 1 از Sys.tables WHERE NAME = ‘مشتریان ‘ ) IF EXISTS ( SELECT 1 FROM SysHEREom ‘s . جدولها )
BEGIN BEGIN
DROP TABLE مشتریان DROP TABLE مشتریان
END END
CREATE TABLE مشتریان ایجاد جدول مشتریان
(ID INT IDENTITY PRIMARY KEY, ( ID INT IDENTITY PRIMARY KEY ,
Name VARCHAR(50), VARCHAR ( 50 VARCHAR
(VARCHAR( VARCHAR ) ) 50 ),
وضعیت VARCHAR(2 ) VARCHAR ( 2 )
)

پس از ایجاد جدول، چند رکورد را به صورت زیر درج می کنیم.

درج کردن مشتریان در مشتریان ( 
نام 
، شهر ، وضعیت ) ( نام ، شهر ، وضعیت ) 
_ _ , 
('Rose','Denver','A'), ( 'Rose' , 'Denver' , 'A' ), 
('Kent','Settle','A') ( 'Kent' , 'Settle' , 'A' ) 
برو برو

بیایید با استفاده از اسکریپت های زیر سه جدول برای نگهداری داده های افزایشی ایجاد کنیم.

استفاده از Master Master 
GO GO 
CREATE Database StagingDB CREATE Database StagingDB 
GO GO 
USE StagingDB استفاده از StagingDB 
GO GO 
IF EXISTS (انتخاب 1 از Sys.tables WHERE NAME = ' Customers_INS ' ) IF EXISTS ( SELECTs 1 FROOMC . ) 
BEGIN BEGIN 
   DROP TABLE Customers_INS DROP TABLE Customers_INS 
END END IF EXISTS (انتخاب 1 FROM Sys.tables WHERE NAME = ' Customers_UPD ' 
) IF EXISTS ( انتخاب 1 FROM Sys . 
   جداول WHEREMIN NAME_ABINGust's میز رها 
کردن Customers_UPD 
END END IF EXISTS (انتخاب 1 FROM Sys.tables 
WHERE 
NAME = 'Customers_DEL') IF EXISTS ( SELECT 1 FROM Sys . tables WHERE NAME = ' 
Customers_DEL ' ) 
BEGIN BEGIN 
   DROP TABLE مشتریان --ENDREDEL 
مشتریان --ENDREDELD TABLE Customers_INS CREATE TABLE Customers_INS 
(ID INT PRIMARY KEY, ( ID INT PRIMARY KEY , 
نام VARCHAR(50), VARCHAR ( 50 ), 
City VARCHAR(50), VARCHAR ( 50 ), 
Status VARCHAR( 2 ) VARCHAR ) 
( 2 
CREATE TABLE Customers_UPD ایجاد جدول Customers_UPD 
(ID INT PRIMARY KEY, ( ID INT PRIMARY KEY , 
نام VARCHAR(50), VARCHAR ( 50 ), 
City VARCHAR(50), 
VARCHAR ( 50 ) , 
Status VARCHAR ( 2 ) VARCHAR ) 
CREATE TABLE Customers_DEL CREATE TABLE
 

                  
   
    
 Customers_DEL 
(ID INT PRIMARY KEY، ( ID INT PRIMARY KEY ، 
نام VARCHAR(50)، VARCHAR ( 50 )، شهر VARCHAR 
(50)، VARCHAR ( 50 )، 
وضعیت VARCHAR(2) VARCHAR ( 2 ) 
)
بیشتر بخوانید :مفهوم ” تغییر ضبط داده ” (CDC) در SQL Server

فعال کردن CDC برای پایگاه داده SQL Server

اسکریپت زیر CDC را برای پایگاه داده در صورتی که فعال نباشد فعال می کند و CDC را برای جدول مشتریان فعال می کند. وجود یک محدودیت کلید اصلی در جدولی که CDC را فعال می کنید ضروری است.

از SourceDB استفاده کنید SourceDB 
GO GO 
- بررسی کنید sa دارای مجوزهای owner_sid است، اگر نه، مالک db را تغییر دهید. - بررسی کنید sa دارای مجوزهای owner_sid است، اگر نه، مالک db را تغییر دهید. 
IF (SUSER_SNAME(owner_sid) را از sys.databases انتخاب کنید WHERE NAME='SourceDB') <> 'sa' IF ( SUSER_SNAME ( owner_sid ) را از پایگاه داده های sys انتخاب کنید WHERE NAME = 'SourceDB' ) <> 'sa' 
BEGIN BEGIN 
   EXbonged 'sa' EXEC sp_changedbowner 'sa' 
END END 
GO GO 
- بررسی کنید SourceDB قبلاً CDC فعال نیست، اگر نه، CDC را برای پایگاه داده فعال کنید - بررسی کنید SourceDB قبلاً CDC فعال نیست، اگر نه، CDC را برای پایگاه داده فعال کنید 
اگر ( is_cdc_enabled from sys.databases WHERE NAME='SourceDB') = 'false' if ( is_cdc_enabled from sys . databases WHERE NAME = ' SourceDB' ) = ' false' 
BEGIN BEGIN 
   EXEC sys.sp_cdc_EXs . sp_cdc_enable_db 
END END 
GO GO 
- بررسی کنید dbo.customers قبلاً CDC فعال نیست، اگر نه، CDC را برای جدول فعال کنید - بررسی کنید dbo.customers قبلاً CDC فعال نیست، اگر نه، CDC را برای جدول فعال کنید 
اگر (انتخاب is_tracked_by_cdc از sys.tables WHERE SCHEMA_NAME(schema_id)='dbo' و NAME = 'Customers') = 'false' if ( is_tracked_by_cdc را از sys . جداول WHERE SCHEMA_NAME ( schema_id ) = 'dbo' و '   NAME = 'Customers' انتخاب کنید . ' 
شروع کنید 
EXEC sys.sp_cdc_enable_table EXEC sys . sp_cdc_enable_table 
   @source_schema = N'dbo'، = N 'dbo' ، 
   @source_name = N'Customers'، = N 'مشتریان' ، 
   @role_name = NULL، = NULL ،

توجه به این نکته ضروری است که جداول در پایگاه داده منبع نیازی به ستونی ندارند که تاریخ و زمان آخرین اصلاح را نشان دهد. این بدان معنی است که برای فعال کردن CDC برای استخراج نیازی به تغییرات ساختاری نیست.

اطمینان حاصل کنید که SQL Server Agent به عنوان SQL Server Agent Job برای ضبط داده های CDC استفاده می شود.

بیشتر بخوانید : اجرای دستورات تبیولار از طریق SQL Server

بسته SSIS برای بار اولیه داده

در هر انبار داده، یک بار اولیه وجود دارد که تمام داده ها را در ابتدا به یک پایگاه داده مرحله بندی استخراج می کند. این یک بار اجرا خواهد بود. ما از SSIS برای استخراج با استفاده از CDC Control Task استفاده خواهیم کرد.

در وظیفه کنترل جریان SSIS ، ما موارد زیر را داریم: یک وظیفه کنترل CDC، یک وظیفه جریان داده و یک وظیفه کنترل دیگر CDC مطابق شکل زیر. نام اول را به “علامت گذاری شروع بار اولیه” و آخرین مورد را به “علامت گذاری پایان بار اولیه” تغییر می دهیم.

برای ویرایش کار روی “Mark Initial Load Start” کلیک راست کنید. این کار یک مقدار رمزگذاری شده، از جمله LSN شروع به یک متغیر بسته را می نویسد.

برای “Mark Initial Load Start” به صورت زیر پیکربندی کنید:

  • پایگاه داده CDC سرور SQL ADO.NET مدیر اتصال : برای پایگاه داده منبع پیکربندی شده است.
  • عملیات کنترل CDC : "علامت گذاری شروع بار اولیه" را از منوی کشویی انتخاب کنید.
  • متغیر حاوی وضعیت CDC : روی New کلیک کنید و یک متغیر جدید به نام CDC_State ایجاد کنید.
  • ذخیره خودکار وضعیت در جدول پایگاه داده : انتخاب شده است.
  • مدیر اتصال برای پایگاه داده ای که وضعیت در آن ذخیره می شود: پایگاه داده مرحله بندی.
  • جدول مورد استفاده برای ذخیره سازی حالت : روی New کلیک کنید و سپس روی Run کلیک کنید تا جدول cdc_states ایجاد شود. در زیر طرح جدول ایالت CDC آمده است. هنگامی که بسته SSIS برای تولید مستقر می شود، جدول cdc_state باید با استفاده از اسکریپت T-SQL زیر ایجاد شود.
  • نام ایالت : CDC_State.

این جدول cdc_states است که ایجاد می شود. در اینجا شمای جدول و شاخص است.

				
					CREATE TABLE [ dbo ] . [ cdc_states ] ( [ name ] [ nvarchar ] ( 256 ) NOT NULL , [ state ] [ nvarchar ] ( 256 ) NOT NULL ) ON [ PRIMARY ] GO CREATE UNIQUE INDCLUSTERNONCLUSTER [ cd ] [ cdc_states ] ( [ name ] ASC ) WITH ( PAD_INDEX   = OFF ) ON [ PRIMARY ] GO

وظیفه جریان داده تمام ردیف ها را از منبع استخراج می کند و آنها را در مقصد بارگذاری می کند که در این سناریو یک جدول مرحله بندی است. این وظیفه جریان داده به هیچ جزء خاص CDC نیاز ندارد.

برای “علامت گذاری پایان بار اولیه” به صورت زیر پیکربندی کنید:

  • پایگاه داده CDC سرور SQL ADO.NET مدیر اتصال : برای پایگاه داده منبع پیکربندی شده است.
  • عملیات کنترل CDC : "علامت گذاری پایان بار اولیه" را از منوی کشویی انتخاب کنید.
  • متغیر حاوی وضعیت CDC : متغیری با نام CDC_State را از منوی کشویی انتخاب کنید.
  • ذخیره خودکار وضعیت در جدول پایگاه داده : انتخاب شده است.
  • مدیر اتصال برای پایگاه داده ای که وضعیت در آن ذخیره می شود: پایگاه داده مرحله بندی
  • جدول مورد استفاده برای ذخیره وضعیت : جدول cdc_states را که قبلا ایجاد شده است انتخاب کنید.
  • نام ایالت : CDC_State.

هنگامی که این بسته اجرا می شود، تمام رکوردهای جدول منبع در جدول Customer_INS که در بالا ایجاد کردیم وارد می شوند. همچنین موارد زیر در جدول cdc_states ذخیره می شوند.

برای مشاهده تأثیر به‌روزرسانی‌های افزایشی SSIS، برخی از داده‌ها را به‌روزرسانی کنید

پس از استخراج بار اولیه، کار بعدی استخراج بار داده افزایشی است. برای شبیه سازی این، بیایید کوئری زیر را برای افزودن، اصلاح و حذف برخی رکوردها اجرا کنیم.

قبل از اجرای این کوئری ها، رکوردهای زیر در جدول Customer آمده است.

شناسهنامشهروضعیت
1جاننیویورکآ
2گل سرخدنورآ
3کنتحل کنآ

به روز رسانی های زیر را اجرا کنید.

				
					استفاده از SourceDB GO INSERT INTO Customers ( نام ، شهر ، وضعیت ) ارزش ها ( «کری» ، «شیکاگو» ، «A» )، ( «لورل» ، «تگزاس» ، «الف» ) ، ( «مت» ، «وگاس» , 'A' ) UPDATE Customers SET Status = 'I' WHERE ID   = 5 UPDATE Customers SET Status = 'I' WHERE ID   = 1 DELETE Customers WHERE ID = 2 DELETE Customers WHERE ID = 6 GO

پس از اجرای کوئری های فوق، رکوردهای زیر در جدول آمده است.

شناسهنامشهروضعیت
1جاننیویورکمن
3کنتحل کنآ
4کریشیکاگوآ
5لورلتگزاسمن

اگر رکوردها را تجزیه و تحلیل کنید، می بینید که شناسه رکورد 1 اصلاح شده و شناسه رکورد 2 حذف شده است. شناسه رکورد 4 اضافه می شود، شناسه رکورد 5 اضافه و اصلاح می شود، شناسه رکورد 6 اضافه شده و سپس حذف می شود. با این حال، زمانی که بار افزایشی در نظر گرفته می شود، باید تنها تغییرات خالص را در نظر بگیریم. به عنوان مثال، از آنجایی که Record ID 6 در نهایت حذف می شود، نیازی نیست که برای بارگذاری در نظر گرفته شود.

بسته SSIS برای به روز رسانی داده های افزایشی

بیایید بسته SSIS را برای استخراج داده های افزایشی از منابع ایجاد کنیم. این همان چیزی است که بسته به نظر می رسد.

Truncate Staging Tables یک Execute SQL Task برای حذف داده های درج شده قبلی در جداول مرحله بندی است، به طوری که هر بار جداول مرحله بندی مجموعه جدیدی از داده ها را دریافت کنند.

Get Processing Range یک وظیفه کنترل CDC است که قبلاً پیکربندی کردیم، تنها تفاوت این است که از گستره پردازش Get برای عملیات کنترل CDC استفاده می کنیم .

کار بعدی، Extract Modified Records یک وظیفه جریان داده است که در صورت پیکربندی به این شکل خواهد بود.

اینها وظایف CDC در جعبه ابزار SSIS هستند.

CDC مشتری یک منبع CDC است که به صورت زیر برای جدول مشتریان پیکربندی شده است.

پیکربندی مهم این است که حالت پردازش CDC به منظور ثبت تغییرات خالص به جای ثبت همه رکوردهایی که برای انبار داده ها ضروری نیست، روی Net تنظیم شده است.

CDC Splitter ، برای تقسیم درج، حذف و به‌روزرسانی رکوردها استفاده می‌شود و رکوردهای تقسیم‌شده در جداول مرحله‌بندی مختلف Customers_INS، Customers_UPD و Customers_DEL درج می‌شوند.

برای کمک به دیدن آنچه در بسته می‌گذرد، نمایشگر داده را فعال کردم تا بتوان سوابق را تأیید و بررسی کرد تا مطمئن شویم داده‌ها درست هستند و به مکان صحیح می‌روند.

در شکل بالا می بینیم که رکورد ID 6 در یک جلسه درج و حذف نشده است. این بدان معنی است که تغییرات خالص ثبت می شود که پیچیدگی و مدت زمان استخراج را کاهش می دهد. از آنجایی که هیچ تغییری در رکورد ID 3 وجود ندارد، آن رکورد برای استخراج ضبط نمی شود.

در نهایت، کنترل Mark Processed Range CDC نشان می دهد که این مجموعه پردازش شده است، بنابراین دفعه بعد که بسته SSIS اجرا می شود، فقط رکوردهای جدید استخراج و پردازش می شوند.

پس از دریافت داده ها به جداول بیان مختلف، مرحله بعدی بارگذاری داده ها بسیار آسان تر خواهد بود.

کانال تلگرام ما را دنبال کنید
صفحه اینستاگرام ما را دنبال کنید

آموزش ویدیویی هوش تجاری در SQL

شما در این دوره آموزشی (آموزش ویدیویی هوش تجاری در SQL) با مفاهیم و اصول هوش تجاری در SQL آشنا می شوید.

مشاهده دوره

آموزش صفر تا صد داشبورد سازی در سی شارپ

 آموزش کامل و پروژه محور مباحث به همراه مثال کاملا عملی در محیط سی شارپ که یک محیط برنامه نویسی ماکروسافتی می باشد. دوره آموزش داشبورد سازی در سی شارپ شامل ساخت یک داشبود زیبا در محیط سی شارپ به کمک کامپوننتهای ssrs و Chart.js می باشد.

مشاهده دوره

آموزش کاملا حرفه ای ویدیویی هوش تجاری در Oracle

آموزش ویدیویی هوش تجاری در Oracle ابتدا به بررسی ساختار کلی هوش تجاری اوراکل پرداخته و سپس به مراحل نصب نرم افزارهای مرتبط با کار خود می پردازیم. سپس مباحث آنالیز داده و فاندامنتال کار را بررسی کرده و در خصوص ریپازیتوری و مسایل مربوط به ان صحبت می کنیم.

مشاهده دوره

دوره کامل تضمینی هوش تجاری با رویکرد کسب درآمد در داخل و خارج از کشور

 

 آنالیز داده و هوش تجاری یکی از پردرآمدهای شغل های دنیا چه در داخل کشور و چه خارج از کشور است. ما در دوره آموزشی  صفر تا صد هوش تجاری را به صورت تضمینی آموزش میدهیم.

مشاهده دوره
برچسب ها:

دیدگاهتان را بنویسید