Habilitar o acompanhamento de mudanças no SQL Server

Prévia

O LakeFlow Connect está em um Public Preview fechado. Para participar da pré-visualização, entre em contato com a equipe do Databricks account .

Este artigo descreve como habilitar o acompanhamento de mudanças em SQL Server. É necessário um acompanhamento de mudança ou o site CDC para a ingestão em Databricks. Para obter orientação sobre qual opção escolher, consulte Change acompanhamento vs. captura de dados de alterações (CDC).

Habilitar o acompanhamento de mudanças em um banco de dados

execute o seguinte, substituindo <database-name> pelo nome do banco de dados no qual o senhor deseja ativar o acompanhamento de alterações. Defina CHANGE_RETENTION para o tempo máximo em que o gateway provavelmente ficará inativo. Após esse período de tempo, é necessário um refresh completo para retomar o gateway.

ALTER DATABASE <database-name> SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)

Habilitar a alteração do acompanhamento em uma tabela

execute o seguinte, substituindo <schema-name>.<table-name> pelo nome do esquema e da tabela em que o senhor deseja ativar o acompanhamento de alterações.

ALTER TABLE <schema-name>.<table-name> ENABLE CHANGE_TRACKING

Conceder a visualização CHANGE acompanhamento em tabelas ingeridas

Além dos privilégios descritos na configuração de origem, o usuário do banco de dados precisa do privilégio VIEW CHANGE TRACKING nas tabelas ingeridas ou em um esquema que contém tabelas que estão sendo rastreadas.

Para conceder permissões no nível do esquema, execute o seguinte:

GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema-name> TO <cdc-username>;

Para conceder permissões em nível de tabela, execute o seguinte:

GRANT VIEW CHANGE TRACKING ON OBJECT::<schema-name>.<table-name> TO <cdc-username>;

Configurar a captura de DDL e a evolução do esquema

O conector do SQL Server pode rastrear a linguagem de definição de dados (DDL) nos objetos de banco de dados ingeridos e aplicar alterações relevantes no esquema da tabela às tabelas de destino ou adicionar novas tabelas no caso de replicação de esquema completo.

Para realizar a captura DDL, é necessária uma configuração adicional do objeto de banco de dados (por exemplo, tabelas internas, procedimentos armazenados e gatilhos). Há duas maneiras de fazer isso:

Automático: Requer privilégios adicionais a serem concedidos ao usuário do banco de dados configurado para ingestão no Databricks.
Manual: Escolha essa opção se quiser que um usuário somente leitura execute a captura de DDL e a evolução do esquema. Requer a criação manual de objetos de banco de dados.

Opção 1: Configuração automática para captura DDL

Execute o seguinte comando para conceder privilégios de captura automática de DDL ao usuário do banco de dados configurado para ingestão em Databricks:

CREATE PROCEDURE no banco de dados:

GRANT CREATE PROCEDURE TO <cdc-username>;

CREATE TABLE no banco de dados:
```
GRANT CREATE TABLE TO <cdc-username>;
```

SELECT, EXECUTE e INSERT no esquema:

GRANT SELECT,INSERT,EXECUTE ON SCHEMA::dbo TO <cdc-username>;

ALTER no banco de dados:

GRANT ALTER ON DATABASE::<database-name> TO <cdc-username>;

ALTER no esquema ou em todas as tabelas a serem ingeridas:

GRANT ALTER ON SCHEMA::[<schema-name>] TO [<cdc-username>];

Opção 2: configuração manual para captura DDL

Se quiser que um usuário somente leitura realize a captura DDL e a evolução do esquema, execute o seguinte comando para configurar manualmente a captura DDL:

CREATE TABLE "dbo"."replicate_io_audit_ddl_1"(
    "SERIAL_NUMBER" INT IDENTITY NOT NULL,
    "CURRENT_USER" NVARCHAR(128),
    "SCHEMA_NAME" NVARCHAR(128),
    "TABLE_NAME" NVARCHAR(128),
    "TYPE" NVARCHAR(30),
    "OPERATION_TYPE" NVARCHAR(30),
    "SQL_TXT" NVARCHAR(2000),
    "LOGICAL_POSITION" BIGINT,
    CONSTRAINT "replicate_io_audit_ddlPK" PRIMARY KEY("SERIAL_NUMBER","LOGICAL_POSITION")
)

ALTER TABLE dbo.replicate_io_audit_ddl_1 ENABLE CHANGE_TRACKING;

GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.replicate_io_audit_ddl_1 TO [CdcUserName];

CREATE TRIGGER "replicate_io_audit_ddl_trigger_1"
            ON DATABASE
            AFTER ALTER_TABLE
            AS
            SET NOCOUNT ON;
        DECLARE @DbName nvarchar(255), @SchemaName nvarchar(max), @TableName nvarchar(255), @data XML, @operation NVARCHAR(30), @isCTEnabledDBLevel bit, @isCTEnabledTableLevel bit;
        SET @data = EVENTDATA();
        SET @DbName = DB_NAME();
        SET @SchemaName = @data.value(' (/EVENT_INSTANCE/SchemaName) [1] ', ' NVARCHAR(MAX) ');
        SET @TableName = @data.value(' (/EVENT_INSTANCE/ObjectName) [1] ', ' NVARCHAR(255) ');
        SET @operation = @data.value(' (/EVENT_INSTANCE/EventType) [1] ', ' NVARCHAR(30) ');
        SET @isCTEnabledDBLevel =
                (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id = DB_ID(@DbName));
        SET @isCTEnabledTableLevel = (SELECT COUNT(*)
                                      FROM sys.change_tracking_tables
                                      WHERE object_id = object_id('[' + @SchemaName + '].[' + @TableName + ']'));
        IF (@isCTEnabledDBLevel = 1 AND @isCTEnabledTableLevel = 1)
            BEGIN
                INSERT INTO dbo.replicate_io_audit_ddl_1 ("CURRENT_USER", "SCHEMA_NAME", "TABLE_NAME", "TYPE",
                                                          "OPERATION_TYPE", "SQL_TXT", "LOGICAL_POSITION")
                VALUES (SUSER_NAME(),
                        @data.value(' (/EVENT_INSTANCE/SchemaName) [1] ', ' NVARCHAR(128) '),
                        @data.value(' (/EVENT_INSTANCE/EventType) [1] ', ' NVARCHAR(128) '),
                        @data.value(' (/EVENT_INSTANCE/ObjectType) [1] ', ' NVARCHAR(30) '),
                        @data.value(' (/EVENT_INSTANCE/EventType) [1] ', ' NVARCHAR(30) '),
                        @data.value(' (/EVENT_INSTANCE/TSQLCommand/CommandText) [1] ', ' NVARCHAR(2000) '),
                        CHANGE_TRACKING_CURRENT_VERSION());
            END

GRANT VIEW DEFINITION ON DATABASE::[YourDatabaseName] TO [CdcUserName];