SQL Server 2008 BI és DW újdonságok - Change Data Capture

A Change Data Capture az SQL Server 2008 egy olyan újdonsága amely lehetővé teszi, hogy egy SQL 2008-as adatbázis változásait elkapja és kitegye őket egy külön táblába. (pongyolán fogalmazva). Mindjárt meg is nézzük, hogy hogyan működik, milyen korlátai vannak adattárházas környezetben, csak előtte ejtsünk néhány szót magáról a problémáról, ami miatt az egészet kitalálták.

Egy adattárház vagy egy üzleti intelligencia rendszer napi rendszeres feltöltése kb. a következőképen néz ki: Kikeressük azokat a rekordokat, amelyek a tegnapi betöltés óta keletkeztek a forrásrendszerben, és azokat rátöltjük az adattárházra. (Akit részletesen érdekelnek ezek, azt arra kérem olvassa el az adatleválogatási módszerek című tanulmányt.)

A nehézséget az okozza, hogy eldöntsük: Melyek az új adatok, melyek azok, amelyek megváltoztak, és melyek azok amelyeket ne adj Isten kitöröltek a forrásrendszerből a tegnapi betöltés óta. Erre - a forrásrendszerek típusától függően létezik pár megoldás. Hogy csak a legelterjedtebbet említsem: Ha a forrásrendszerek táblái tartalmaznak időbélyeget, akkor ez alapján le tudjuk válogatni, hogy melyek azok a rekordok, amelyek az utolsó betöltés óta keletkeztek.

De mi történik akkor, ha a forrásrendszerekben töröltek egy rekordot? Hát az cumi. - ahogy az egyik kollegám mondaná. Hát tényleg az, ugyanis nem fogjuk észrevenni, és szép lassan elkezd eltávolodni az adattárház a forrásrendszerektől. Ha a felhasználó ugyanazt az üzleti mutatót mindkét rendszerből lekérdezi, akkor az más és más számokat fog mutatni.

Bár manapság már nem divat fizikailag törölni (Hard delete) a forrásrendszerekből, de ha még is előfordul és az a forrás rendszer SQL server 2008-on fog futni, akkor itt a megoldás:

Change Data Capture (CDC)

A Change Data Capture elkapja nekünk az adat és táblamódosításokat is, de a mostani cikkben csak az adatmódosításokkal fogunk foglalkozni. Ami nagyon fontos:

A Change Data Capture kiírja nekünk egy külön táblába azokat a rekordokat, amelyek

  1. újak
  2. Megváltoztak (kiírja azt a rekordot, amiről megváltozott és azt is, amire megváltozott)
  3. és amelyek törölve lettek

Hogyan működik a Change Data Capture?

Logikailag a Change Data Capture a következőképpen működik:

A Change Data Capture logikai működési

A Change Data Capture logikai működési

A Change Data Capture a tranzakció naplóból dolgozik. Azt figyeli, és ha abban bejegyzést talál arra a táblára vonatkozóan, amelyik változását figyeli, akkor a megváltozott rekordokat kiírja egy külön táblába és a rekordokat ellátja olyan információkkal, mint például törölve lett az a rekord, vagy beszúrva, ...

A Change Data Capture fizikai működése

Ha bekapcsoljuk a Change Data Capture szolgáltatást egy táblára, akkor a következő folyamatok zajlanak a háttérben:

  1. Létrejön egy (illetve két) új job az SQL Server Agent alatt (gondolom ő fog figyeli a változásokat)
  2. Tegyük fel, hogy a [forrásTábla] az a tábla amelynek változásait kell elkapnunk. Ez esetben létrejön egy új tábla (Nevezzük VáltozásTábla-nak) a [ForrásTábla] adatbázisában (!) és a [ForrásTábla szerkezetének megfelelően, de azt kiegészítve pár oszloppal (ezekről később még részletesen beszélünk), és néhány tábla értékű függvény, amely a megváltozott rekordok lekérdezését fogja megkönnyíteni. (A dbo.VáltozásTábla így lesz elnevezve: cdc.dbo_ForrásTábla_CT)
  3. Ha változás történik a ForrásTáblában, akkor az a rekord, amely megváltozott (és amire megváltozott) átkerül a dbo_ForrásTábla_CT táblába

Mi a baj ezzel a modellel?

A legnagyobb probléma az, hogy a változás táblát a forrástábla adatbázisában hozza létre az SQL Server 2008.

Tegyük fel, hogy Ön egy IT igazgató. Ön engedné, hogy az adattárház szállító beleírjon a forrásrendszer adatbázisába? Szerintem nem. Ezt egyrészről tiltják a forrásrendszer szállítók is, másrészről ha meg is tűrik, akkor is azonnal elveszít minden garanciát. Ezt a kockázatot csak kevesen merik felvállalni.

De üljünk át egy kicsit a másik oldalra. Mi a helyzet, ha Ön ERP szállító és adattárház baráttá szeretné tenni az ERP rendszerét? (Vagy ráragasztani egy matricát a dobozra, hogy „Data Warehouse Ready")

Az ERP fejlesztőknek már érdemes lehet elgondolkodni a Change Data Capture szolgáltatás használatán, mi pedig nagyon fogunk neki örülni, mert egyszer és mindenkorra megoldódik a forrásrendszeri törlések kezelésének problémája az adattárházban.

Rész-összefoglalás: A Change Data Capture legnagyobb előnye, hogy el tudja kapni a fizikai törlést a forrásrendszerekben, és a törölt rekordokat át tudja adni az adattárháznak. Ez az, amitől az adattárház építők nagyon-nagyon szenvednek, tehát ennek a szolgáltatásnak nagyon fognak örülni. Persze ehhez az kell, hogy a forrásrendszer tulajdonosa engedélyezze Change Data Capture bekapcsolását az adatbázison és azok tábláin. Ha ez megtörténik, akkor nyert ügyünk van

change data capture

Change Data Capture

És itt véget is ért a döntéshozókat érdeklő rész. Ha Ön fejlesztő, akkor itt az ideje kicsit feljebb csúszni a széken, közelebb hajolni a monitorhoz, mert most jönnek a kódok!

A Change Data Capture szolgáltatás bekapcsolás

Az első lépés, hogy létrehozzunk egy adatbázist, abban egy táblát. (Ez az adatbázis fogja szimulálni a forrásrendszert) Ezután engedélyezni kell a Change Data Capture-t adatbázis és tábla szinten is.

CREATE DATABASE SQL2008_ChangeDataCapture GO USE SQL2008_ChangeDataCapture; GO EXEC sp_cdc_enable_db_change_data_capture; GO

CREATE TABLE dbo.VevoTorzs( VevoKod int Primary Key NOT NULL, VevoNev nvarchar(100) NOT NULL, VevoEmail nvarchar(100) NOT NULL) GO

EXEC sp_cdc_enable_table_change_data_capture 'dbo', 'VevoTorzs', @role_name = NULL, @supports_net_changes =1; GO

Ezután beszúrunk egy új sort a táblába (forrástábla = VevoTorzs), módosítjuk azt, majd kitöröljük az egész sort, és megnézzük, hogy mi került be a változás táblába. Mit gondol hány sort fog tartalmazni a változás tábla? 3-at vagy 4-et?

INSERT INTO dbo.VevoTorzs values (1, N'Kovari Attila', N'Kovari.Attila@biprojekt.hu') GO

UPDATE dbo.VevoTorzs SET VevoEmail = N'Attila.Kovari@biprojekt.hu', VevoNev = N'Attila Kovari' WHERE VevoKod = 1; GO

Delete dbo.VevoTorzs WHERE VevoKod = 1; GO select * from cdc.dbo_VevoTorzs_CT

Íme az eredmény amit kicsit lejjebb még részletezünk:

Change Data Captue: a változás tábla tartalma

a változás tábla tartalma

Az __$operation oszlop jelentese:

  • 1: delete,
  • 2: insert
  • 3: Update Before: Update előtti kepe a rekordnak
  • 4: Update After: Update After utáni kepe a rekordnak

Tehát nem csak azt kapjuk vissza, hogy melyis sor változott meg, hanem azt is, hogy miről, mire. De ez még nekünk kevés, hiszen nem tudjuk még, hogy az utolsó leválogatás hány új rekord keletkezett, módosult vagy törlődött.

Hogyan válogathatom le az utolsó betöltés óta keletkezett megváltozott és törölt adatokat?

Ki kell szedni azokat a log sequence number (LSN)-eket, amelyek az utolsó betöltés óta keletkeztek, és az így kiválogatott tranzakciókhoz tartozó rekordokat át kell emelni az adattárházba. A konkrét lekérdezést itt találja

Összefoglalás: Sok időnek kell még eltelnie, amíg megjelennek az első olyan forrásrendszerek, amelyek SQL 2008-on fognak futni és engedélyezve lesz rajtuk a change data capture szolgáltatás. De ettől a pillanattól kezdve egyszerűsödni fog az adattárház építők dolga, hiszen nem kell foglalkozni a törölt sorok keresésével, tudni fogjuk, hogy mely sorok újak és melyek változtak meg. Így már csak a megváltozott rekordok historizálásval (verziózásával) kell foglalkoznunk.

Kővári Attila - BI projekt

Új hozzászólás