جلسه دهم - 93/08/28 شناسایی تغییرات در جداول تراکنش OLTP

جلسه دهم –28/08/93

شناسایی تغییرات در جداول تراکنش OLTP

برخی از روش های کلاسیک

1. افزودن ستون/ستون­هایی جهت شناسایی رکوردهای جدید

   • اشکال 1: عدم امکان شناسایی سطرهای حذف شده

   • اشکال 2: نیاز به دسترسی ایجاد تغییرات در ساختار پایه

2. استفاده از Trigger

   • اشکال 1: نیاز به دسترسی ایجاد تغییرات در ساختار پایه

   • اشکال 2: ایجاد سربار برروی عملیات جاری پایگاه داده

   • اشکال3: پیچیدگی مدیریت تغییرات در این روش

CDC – Change Data Capture: یکی از سرویس­های پلتفرم SQL است که از ورژن 2008 به بعد قابل استفاده می­باشد و با استفاده از آن می­توان تغییرات رخ داده شده برروی یک جدول را شناسایی کرد.

مزایا:

• عدم نیاز به دسترسی اعمال تغییرات در پایگاه داده مورد نظر

• نداشتن سربار برروی عملیات جاری پایگاه داده

• سادگی استفاده

مکانیزم سرویس CDC براساس خواندن تغییرات از logfile دیتابیس می­باشد. لذا تاثیر مستقیم برروی عملیات جاری پایگاه داده ندارد. این سرویس براساس خواندن شماره LSN (Log Sequence Number)  کار می­کند. این شماره منطبق بر زمان است و به یک نقطه زمانی خاص اشاره دارد. از طرفی به ازای هرنقطه­ی زمانی می­توان نزدیکترین LSN را شناسایی نمود و به این ترتیب می­توان تغییرات یک بازه زمانی دلخواه را شناسایی کرده و استخراج کرد.

پیش نیاز راه اندازی: لازم است تا ابتدا سرویس­های SQL Server Engine و SQL Server Agent فعال باشند.

فعال سازی CDC  (با دسترسی sysadmin یا dbowner  روی بانک اطلاعاتی مقدور است)

1. فعال سازی در سطح DB

Exec sys.sp_cdc_enable_db;

1. فعال سازی در سطح Table

Exec sys.sp_cdc_enable_table @SourceSchema, @SourceName,@RoleName,@Support_Net_Changes

• @SourceSchema – مثلا dbo

• - @SourceName نام جدول

•  - @RoleNameپیش فرض آن null است، در این صورت تنها sysadmin دسترسی خواندن اطلاعات جدول CDC را دارد

•  -@Support_Net_Changesمی تواند مقادیر 0 یا 1 را داشته باشد. درصورتیکه با 1 تنظیم شده باشد، امکان دریافت خلاصه تغییرات مقدور می شود.

روش استفاده از CDC در SSIS برای هر جدول

• Initial Load: بارگذاری اولیه

  1. خواندن کوچکترین LSN

  2. انتقال داده ها به جدول مقصد

  3. خواندن آخرین LSN

• Incremental Load: بارگذاری تغییرات

  1. خواندن LSN از جدول جانبی (cdc_state)

  2. خواندن تغییرات از LSN فوق تا آخرین LSN و انتقال آنها به مقصد

  3. خواندن آخرین LSN و ثبت آنها در جدول جانبی

امکانات SSIS در استفاده از CDC

• CDC Control Task: وظیفه پیدا کردن شماره LSN مناسب و استفاده از آن و ذخیره آن در جدول جانبی درصورت لزوم

• Data Flow Task

  • CDC Source: اتصال به سرویس CDC و واکشی تغییرات مورد نظر

  • CDC Splitter: جداسازی تغییرات برحسب نوع آنها

در هریک از حالات فوق کامپوننت هایی مورد استفاده به ترتیب زیر است:

CDC Control Task -> Data Flow Task -> CDC Control Task

CDC Control Operation

خواندن ثبت اولین LSN	Load initial load start
خواندن ثبت آخرین LSN	Load initial load end
خواندن logfile از یک شماره LSN خاص	Mark CDC start
خواندن LSN ثبت شده در جدول جانبی جهت بارگذاری تغییرات (افزایشی)	Get processing range
خواندن آخرین LSN و ثبت آن در جدول جانبی	Mark processed range

از جدول فوق دو مورد اول برای بارگذاری اولیه و سایر موارد برای بارگذاری تغییرات استفاده می شود.

توجه: در تنظیم SSIS به موارد زیر توجه بفرمایید:

تعریف متغییر برای نگهداری LSN حین عملیات	Variable containing the CDC state
جدول میانی برای ثبت LSN ها، این جدول شامل دو ستون اطلاعاتی می باشد. این امکان وجود دارد که بتوان برای هر جدول که CDC برای آن فعال شده است جدولی جداگانه در نظر گرفته شود. همچنین از ستون name می توان برای معرفی وضعیت هرجدول استفاده نمود. در این صورت بهتر است تا ستون name تداعی کننده نام جدول باشد.	Table to use for storing state

__$Operation ها : انواع operation در خروجی

• 1 = Delete

• 2 = Insert

• 3 = Update (old value)  قبل از به روزرسانی

• 4 = Update (new value)  مقدار قبل از به روزرسانی

• 5 = Merge

CDC Processing Mode

• All: کلیه تغییرات شناسایی می شوند.

  • __$Operation : 1و2و4

  • مثال:

فروش	محل	نماینده فروش	تاریخ
1000000	تهران	B	01/08/92
1000000	کرج	B	01/08/92
1000000	اصفهان	B	01/08/92

• All with old values: کلیه تغییرات به همراه داده قبل از تغییر شناسایی می­شوند.

  • __$Operation : 1و2و3و4

  • مثال:

فروش	محل	نماینده فروش	تاریخ
1000000	تهران	A	01/08/92
1000000	تهران	B	01/08/92
1000000	کرج	B	01/08/92
1000000	اصفهان	B	01/08/92

• Net: خلاصه تغییرات

  • __$Operation : 1و2و4

  • مثال:

فروش	محل	نماینده فروش	تاریخ
1000000	اصفهان	B	01/08/92

• Net with update mask: خلاصه تغییرات به همراه اینکه چه ستون­هایی تغییر کرده­اند. این روش برای Performance بالاتر مورد استفاده قرار می­گیرد.

  • __$Operation : 1و2و4

  • مثال:

تغییر فروش	تغییر محل	تغییر نماینده	تغییر تاریخ	فروش	محل	نماینده فروش	تاریخ
False	True	True	False	1000000	اصفهان	B	01/08/92

• Net with range: خلاصه تغییرات بطوریکه سطرهای Insert  شده و Update شده در یک خروجی ارائه می­شوند( جهت استفاده در دستور Merge).

  • __$Operation : 1و 5

کامپوننت CDC_Splitter: خروجی CDC_Source را براساس Operation ها تفکیک می­کند.

__$UpdateMask : ستون باینری است که نمایشگر آن است که کدام ستون تغییر یافته است. برای استفاده از آن باید یکسری توابع را به کار گرفت.

ترکیب کلی DFT در بخش Incremental Load به ترتیب زیر است:

CDC Source -> CDC Splitter (I/U/D)   -> OLE DB Destination

ابزارهای موجود در SSIS  جهت انجام فرآیند پردازش اشیاء SSAS

• Analysis Services Processing: اجرای عملیات پردازش بر روی اشیاء SSAS است. در این حالت داده­ها از جداول خوانده می­شوند که در طراحی اشیاء از آنها استفاده شده است. بنابراین با استفاده از این امکان می­توان در پایان عملیات ETL ، دستور ساخت CUBE را نیز از داخل SSIS داد.

• Data Flow Task: در هر دو مورد زیر بارگذاری یک جریان داده مورد نظر در یک بعد یا در یک FACT مقدور است. با استفاده از این ابزار می­توان مقادیری به جز مقادیر جداول اصلی و قابل استخراج از انبار داده ها را جایگزین Dimension یا FACT اصلی کرد.

  • Dimension Processing

  • Partition Processing