Zmień przechwytywanie danych i plik binarny __ $ update_mask

Używamy CDC do przechwytywania zmian wprowadzonych w tabeli produkcyjnej. Zmienione wiersze są eksportowane do hurtowni danych (informatica). Wiem, że kolumna __ $ update_mask przechowuje, które kolumny zostały zaktualizowane w formie varbinary. Wiem też, że mogę użyć różnych funkcji CDC, aby dowiedzieć się z tej maski, jakie były te kolumny.

Moje pytanie brzmi: Czy ktoś może dla mnie zdefiniować logikę tej maski, abyśmy mogli zidentyfikować kolumny, które zostały zmienione w magazynie? Ponieważ przetwarzamy poza serwerem, nie mamy łatwego dostępu do funkcji MSCQL CDC. Wolę po prostu sam rozbić maskę w kodzie. Wydajność funkcji cdc po stronie SQL jest problematyczna dla tego rozwiązania.

Krótko mówiąc, chciałbym ręcznie zidentyfikować zmienione kolumny w polu __ $ update_mask.

Aktualizacja:

Dopuszczalne było również przesłanie do magazynu czytelnej dla człowieka listy zmienionych kolumn. Odkryliśmy, że można tego dokonać przy wydajności znacznie większej niż nasze pierwotne podejście.

Odpowiedź CLR na to pytanie poniżej spełnia tę alternatywę i zawiera szczegóły dotyczące interpretacji maski dla przyszłych gości. Jednak zaakceptowana odpowiedź przy użyciu XML PATH jest najszybsza z dotychczasowych dla tego samego wyniku końcowego.

Morał tej historii jest… testuj, próbuj innych rzeczy, myśl duży, potem mały, zawsze zakładaj, że jest lepszy sposób.

Ciekawe naukowo, jak moja ostatnia odpowiedź. Postanowiłem wypróbować jeszcze jedno podejście. Przypomniałem sobie, że mogę połączyć sztuczkę XML PATH (''). Ponieważ wiedziałem, jak uzyskać porządek każdej zmienionej kolumny z listy captured_column z poprzedniej odpowiedzi, pomyślałem, że warto przetestować, czy funkcja bitu MS działałaby lepiej w taki sposób, jak tego potrzebowaliśmy.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

Jest o wiele czystszy niż (choć nie tak zabawny jak) cały ten CLR, zwraca to podejście tylko do natywnego kodu SQL. I rolka bębna .... zwraca te same wyniki w mniej niż sekundę . Ponieważ dane produkcyjne są 100 razy większe, liczy się każda sekunda.

Pozostawiam drugą odpowiedź do celów naukowych - ale na razie jest to nasza poprawna odpowiedź.

Po kilku badaniach postanowiliśmy jednak zrobić to po stronie SQL przed przekazaniem do hurtowni danych. Ale przyjmujemy to znacznie ulepszone podejście (w oparciu o nasze potrzeby i nowe zrozumienie działania maski).

Za pomocą tego zapytania otrzymujemy listę nazw kolumn i ich porządkowe pozycje. Zwrot powraca w formacie XML, dzięki czemu możemy przekazać do SQL CLR.

DECLARE @colListXML varchar(max);

SET @colListXML = (SELECT column_name, column_ordinal
    FROM  cdc.captured_columns 
    INNER JOIN cdc.change_tables 
    ON captured_columns.[object_id] = change_tables.[object_id]
    WHERE capture_instance = 'dbo_OurTableName'
    FOR XML Auto);

Następnie przekazujemy ten blok XML jako zmienną i pole maski do funkcji CLR, która zwraca łańcuch rozdzielany przecinkami kolumn, które zmieniły się zgodnie z polem binarnym _ $ update_mask. Ta funkcja clr sprawdza pole maski w celu zmiany bitu dla każdej kolumny na liście xml, a następnie zwraca jej nazwę z odpowiedniej porządkowej.

SELECT  cdc.udf_clr_ChangedColumns(@colListXML,
        CAST(__$update_mask AS VARCHAR(MAX))) AS changed
    FROM cdc.dbo_OurCaptureTableName
    WHERE NOT __$update_mask IS NULL;

Kod c # clr wygląda następująco: (skompilowany w zestawie o nazwie CDCUtilities)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_clr_cdcChangedColumns(string columnListXML, string updateMaskString)
    {
        /*  xml of column ordinals shall be formatted as follows:

            <cdc.captured_columns column_name="Column1" column_ordinal="1" />                
            <cdc.captured_columns column_name="Column2" column_ordinal="2" />                

        */

        System.Text.ASCIIEncoding encoding=new System.Text.ASCIIEncoding();
        byte[] updateMask = encoding.GetBytes(updateMaskString);

        string columnList = "";
        System.Xml.XmlDocument colList = new System.Xml.XmlDocument();
        colList.LoadXml("<columns>" + columnListXML + "</columns>"); /* generate xml with root node */

        for (int i = 0; i < colList["columns"].ChildNodes.Count; i++)
        {
            if (columnChanged(updateMask, int.Parse(colList["columns"].ChildNodes[i].Attributes["column_ordinal"].Value)))
            {
                columnList += colList["columns"].ChildNodes[i].Attributes["column_name"].Value + ",";
            }
        }

        if (columnList.LastIndexOf(',') > 0)
        {
            columnList = columnList.Remove(columnList.LastIndexOf(','));   /* get rid of trailing comma */
        }

        return columnList;  /* return the comma seperated list of columns that changed */
    }

    private static bool columnChanged(byte[] updateMask, int colOrdinal)
    {
        unchecked  
        {
            byte relevantByte = updateMask[(updateMask.Length - 1) - ((colOrdinal - 1) / 8)];
            int bitMask = 1 << ((colOrdinal - 1) % 8);  
            var hasChanged = (relevantByte & bitMask) != 0;
            return hasChanged;
        }
    }
}

I funkcja CLR wygląda następująco:

CREATE FUNCTION [cdc].[udf_clr_ChangedColumns]
       (@columnListXML [nvarchar](max), @updateMask [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [CDCUtilities].[UserDefinedFunctions].[udf_clr_cdcChangedColumns]

Następnie dołączamy tę listę kolumn do zestawu wierszy i przekazujemy do hurtowni danych do analizy. Używając zapytania i clr, unikamy konieczności używania dwóch wywołań funkcji na wiersz na zmianę. Możemy przejść bezpośrednio do mięsa z wynikami dostosowanymi do naszego wystąpienia przechwytywania zmian.

Dzięki temu postowi przepełnienia stosu zaproponowanemu przez Jona Seigela dla sposobu interpretacji maski.

Z naszego doświadczenia wynika, że ​​jesteśmy w stanie uzyskać listę wszystkich zmienionych kolumn z 10k wierszy cdc w mniej niż 3 sekundy.