主页 / dba / 问题 / 35936
Accepted
RThomas
Asked: 2013-03-05 16:10:53 +0800 CST

更改数据捕获和 __$update_mask 二进制文件

  • 772

我们正在使用 CDC 来捕获对生产表所做的更改。更改的行正在导出到数据仓库 (informatica)。我知道 __$update_mask 列存储了哪些列以 varbinary 形式更新。我也知道我可以使用各种 CDC 函数从掩码中找出那些列是什么。

我的问题是这个。谁能为我定义该掩码背后的逻辑，以便我们可以识别仓库中已更改的列？由于我们在服务器之外进行处理，因此我们无法轻松访问那些 MSSQL CDC 函数。我宁愿自己在代码中分解面具。SQL 端的 cdc 函数的性能对于此解决方案是有问题的。

简而言之，我想从 __$update_mask 字段中手动识别更改的列。

更新：

作为替代方案，将更改列的人类可读列表发送到仓库也是可以接受的。我们发现这可以在性能上远远高于我们原来的方法。

以下 CLR 对此问题的回答符合此替代方案，并包括为未来访问者解释掩码的详细信息。然而，对于相同的最终结果，使用 XML PATH 的公认答案是最快的。

sql-server-2008 change-data-capture

2 个回答

  1. Best Answer

    这个故事的寓意是……测试，尝试其他事情，从大处思考，然后从小处思考，总是假设有更好的方法。

    和我上一个回答一样科学有趣。我决定尝试另一种方法。我记得我可以使用 XML PATH('') 技巧进行连接。因为我知道如何从上一个答案的 capture_column 列表中获取每个更改列的序号，所以我认为如果 MS 位函数能更好地满足我们的需要，那么值得测试一下。

    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

    它比所有 CLR 都干净（尽管没有那么有趣），它只将方法返回到本机 SQL 代码。而且，drum roll.... 在不到一秒的时间内返回相同的结果。由于生产数据每秒增加 100 倍，因此很重要。

    出于科学目的，我将保留另一个答案 - 但就目前而言，这是我们的正确答案。

    • 13

  2. 因此，经过一些研究，我们决定在移交给数据仓库之前仍然在 SQL 端执行此操作。但是我们正在采取这种改进的方法（基于我们的需求和对面具工作原理的新理解）。

    我们通过此查询获得列名及其序号位置的列表。返回以 XML 格式返回，以便我们可以传递给 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);

    然后，我们将该 XML 块作为变量和掩码字段传递给 CLR 函数，该函数返回一个逗号分隔的字符串，该字符串由 _$update_mask 二进制字段更改的列组成。此 clr 函数询问 xml 列表中每一列的更改位的掩码字段，然后从相关的序号返回它的名称。

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

    c# clr 代码如下所示：（ 编译成一个名为 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;
        }
    }
}

    CLR 的功能如下：

    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]

    然后，我们将此列列表附加到行集并传递到数据仓库进行分析。通过使用查询和 clr，我们避免了每次更改每行必须使用两个函数调用。我们可以直接跳过为我们的变更捕获实例定制的结果。

    感谢Jon Seigel 建议的这篇 stackoverflow 帖子，用于解释掩码的方式。

    根据我们使用这种方法的经验，我们能够在 3 秒内从 10k cdc 行中获取所有更改列的列表。

    • 2

相关问题