Configurar o SQL Server para ingestão

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 configurar o site SQL Server para ingestão. Esse é um pré-requisito para ingerir dados do SQL Server e carregá-los no Databricks usando o LakeFlow Connect.

Change acompanhamento vs. captura de dados de alterações (CDC) (CDC)

Databricks suporta Microsoft captura de dados de alterações (CDC) (CDC) ou Microsoft change acompanhamento to extração de dados from SQL Server. Se uma tabela tiver uma tabela primária key, o site Databricks recomenda o uso do acompanhamento de alterações para otimizar o desempenho. Quando não houver um key primário, o CDC deverá ser usado. Para obter mais informações sobre essas duas opções, consulte Rastrear alterações de dados (SQL Server ) na documentação do site SQL Server.

Para usar o Microsoft captura de dados de alterações (CDC) (CDC), o senhor deve ter o SQL Server 2017 ou superior. Para usar o Microsoft change acompanhamento, o senhor deve ter o SQL Server 2012 ou o acima.

Criar um usuário do SQL Server

A Databricks recomenda que o senhor crie um usuário de banco de dados que seja usado exclusivamente para a ingestão da Databricks.

Esse usuário do banco de dados deve ter os seguintes privilégios, independentemente do método empregado para acompanhar as alterações de dados no banco de dados de origem:

• Acesso de leitura às seguintes tabelas e visualizações do sistema:

  • sys.databases

  • sys.schemas

  • sys.tables

  • sys.columns

  • sys.key_constraints

  • sys.foreign_keys

  • sys.check_constraints

  • sys.default_constraints

  • sys.change_tracking_tables

  • sys.change_tracking_databases

  • sys.objects

  • sys.triggers

• Permissões de execução nos seguintes procedimentos armazenados no sistema:

  • sp_tables

  • sp_columns

  • sp_columns_100

  • sp_pkeys

  • sp_statistics

• SELECT nos esquemas e tabelas que o senhor deseja ingerir.

Habilitar o acompanhamento de mudanças

Para usar o acompanhamento de alterações, o senhor deve ativar o acompanhamento de alterações em todos os bancos de dados e tabelas ingeridos.

Observação

O acompanhamento de alterações só pode ser usado em tabelas com um primário key.

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.

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

Permissões de leitura adicionais

O senhor precisa ter o privilégio VIEW CHANGE TRACKING nas tabelas ingeridas ou em um esquema que contenha tabelas que estejam 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 tem a capacidade de 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 de DDL, é necessário configurar objetos de banco de dados adicionais (por exemplo, tabelas internas, procedimentos armazenados e acionadores). Há duas maneiras de configurá-los:

• Automático: Exige que privilégios adicionais sejam concedidos ao usuário de replicação.

• Manual: Requer a criação manual de objetos de banco de dados.

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

Databricks requer privilégios adicionais para a captura automática de DDL ao usar o acompanhamento de alterações. Execute o seguinte comando para conceder esses privilégios ao usuário configurado para uso com 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 de DDL

Para configurar manualmente a captura de DDL, execute o seguinte:

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 TABLE dbo.replicate_io_audit_tbl_schema_1(

    "COLUMN_ID" BIGINT,

    "DATA_DEFAULT" BIGINT,

    "COLUMN_NAME" VARCHAR(128) NOT NULL,

         "TABLE_NAME" NVARCHAR(128) NOT NULL,

         "SCHEMA_NAME" NVARCHAR(128) NOT NULL,

         "HIDDEN_COLUMN" NVARCHAR(3),

         "DATA_TYPE" NVARCHAR(128),

         "DATA_LENGTH" BIGINT,

         "CHAR_LENGTH" BIGINT,

         "DATA_SCALE" BIGINT,

        "DATA_PRECISION" BIGINT,

        "IDENTITY_COLUMN" NVARCHAR(3),

    "VIRTUAL_COLUMN" NVARCHAR(3),

    "NULLABLE" NVARCHAR(1),

    "LOGICAL_POSITION" BIGINT);


CREATE TABLE dbo.replicate_io_audit_tbl_cons_1(

    "SCHEMA_NAME" VARCHAR(128),

    "TABLE_NAME" VARCHAR(128),

    "COLUMN_NAME" VARCHAR(4000),

    "COL_POSITION" BIGINT,

    "CONSTRAINT_NAME" VARCHAR(128),

    "CONSTRAINT_TYPE" VARCHAR(1),

    "LOGICAL_POSITION" BIGINT);


CREATE OR ALTER 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];

Habilitar a integração CDC para o banco de dados de origem

Para habilitar o banco de dados de origem para CDC, execute o seguinte procedimento armazenado em Azure SQL, substituindo o valor de <database-name>. O senhor deve estar conectado ao banco de dados que deseja habilitar para CDC.

USE <database-name>
EXEC sys.sp_cdc_enable_db

Para ativar o CDC em um banco de dados no Amazon RDS para SQL Server, execute o seguinte:

EXEC msdb.dbo.rds_cdc_enable_db '<database-name>'

Para obter mais informações, consulte Ativar captura de dados de alterações (CDC) para um banco de dados na documentação SQL Server.

Habilitar a integração CDC na tabela de origem

Para ativar o CDC na tabela de origem, execute o seguinte procedimento armazenado em Azure SQL:

EXEC sys.sp_cdc_enable_table
@source_schema = N'MySchema',
@source_name   = N'MyTable',
@role_name     = NULL,
@supports_net_changes = 1

• Substitua os valores de source_schema, source_name e role_name.

• @support_net_changes só aceita um valor de 1 se a tabela tiver um primário key.

Para obter mais informações, consulte Habilitar captura de dados de alterações (CDC) para uma tabela na documentação do site SQL Server.

Permissões de leitura adicionais

O senhor precisa ter o privilégio SELECT no esquema cdc que contém as tabelas de alteração criadas quando o CDC é ativado.

GRANT SELECT ON SCHEMA::cdc to [cdc-username];

Próximas etapas

Ingerir dados do SQL Server