Compartilhar via

Cópia delta de um banco de dados com uma tabela de controle

  • Artigo

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Dica

Experimente o Data Factory no Microsoft Fabric, uma solução de análise tudo-em-um para empresas. O Microsoft Fabric abrange desde movimentação de dados até ciência de dados, análise em tempo real, business intelligence e relatórios. Saiba como iniciar uma avaliação gratuita!

Este artigo descreve um modelo disponível para carregar de modo incremental linhas novas ou atualizadas de uma tabela de banco de dados no Azure usando uma tabela de controle externa que armazena um valor de marca-d'água alta.

Este modelo exige que o esquema do banco de dados de origem contenha uma coluna de carimbo de data/hora ou uma chave de incremento para identificar as linhas novas ou atualizadas.

Observação

Se você tiver uma coluna de carimbo de data/hora no banco de dados de origem para identificar linhas novas ou atualizadas, mas não quiser criar uma tabela de controle externa para uso na cópia delta, use a ferramenta Copiar Dados do Azure Data Factory para obter um pipeline. Essa ferramenta usa um tempo agendado por gatilho como uma variável para ler novas linhas do banco de dados de origem.

Sobre o modelo de solução

Esse modelo primeiro recupera o valor antigo da marca-d'água e compara-o com o valor atual da marca-d'água. Depois disso, ele só copia as alterações do banco de dados de origem com base em uma comparação entre os dois valores de marca-d'água. Por fim, ele armazena o novo valor da marca-d'água alta em uma tabela de controle externa para o próximo carregamento de dados delta.

O modelo contém três atividades:

  • A atividade Pesquisa recupera o valor antigo da marca-d'água alta, que está armazenado em uma tabela de controle externa.
  • Outra atividade Pesquisa recupera o valor atual da marca-d'água alta do banco de dados de origem.
  • A atividade Cópia só copia as alterações do banco de dados de origem para o repositório de destino. A consulta que identifica as alterações no banco de dados de origem é semelhante a 'SELECT * FROM Data_Source_Table WHERE TIMESTAMP_Column > “last high-watermark” e TIMESTAMP_Column <= “current high-watermark”'.
  • SqlServerStoredProcedure grava o valor atual da marca-d'água alta em uma tabela de controle externa para a próxima cópia delta.

O modelo define os seguintes parâmetros:

  • Data_Source_Table_Name é a tabela do banco de dados de origem da qual você deseja carregar os dados.
  • Data_Source_WaterMarkColumn é o nome da coluna na tabela de origem usada para identificar linhas novas ou atualizadas. O tipo dessa coluna normalmente é datetime, INT ou semelhante.
  • Data_Destination_Container é o caminho raiz para o qual os dados são copiados no repositório de destino.
  • Data_Destination_Directory é o caminho do diretório na raiz para o qual os dados são copiados no repositório de destino.
  • Data_Destination_Table_Name é o local para o qual os dados são copiados no repositório de destino (aplicável quando o "Azure Synapse Analytics" é selecionado como o destino dos dados).
  • Data_Destination_Folder_Path é o local para o qual os dados são copiados para o repositório de destino (aplicável quando "Sistema de Arquivos" ou "Azure Data Lake Storage Gen1" é selecionado como o destino dos dados).
  • Control_Table_Table_Name é a tabela de controle externa que armazena o valor da marca-d'água alta.
  • Control_Table_Column_Name é a coluna da tabela de controle externa que armazena o valor da marca-d'água alta.

Como usar este modelo de solução

  1. Explore a tabela de origem que deseja carregar e defina a coluna de marca-d'água alta que possa ser usada para identificar as linhas novas ou atualizadas. O tipo dessa coluna pode ser datetime, INT ou semelhante. O valor dessa coluna aumenta conforme novas linhas são adicionadas. No exemplo de tabela de origem a seguir (data_source_table), podemos usar a coluna LastModifytime como a coluna de marca-d'água alta.

    PersonID	Name            LastModifytime
1           aaaa            2017-09-01 00:56:00.000
2           bbbb            2017-09-02 05:23:00.000
3           cccc            2017-09-03 02:36:00.000
4           dddd            2017-09-04 03:21:00.000
5           eeee            2017-09-05 08:06:00.000
6           fffffff         2017-09-06 02:23:00.000
7           gggg            2017-09-07 09:01:00.000
8           hhhh            2017-09-08 09:01:00.000
9           iiiiiiiii       2017-09-09 09:01:00.000

  2. Crie uma tabela de controle no SQL Server ou no Banco de Dados SQL do Azure para armazenar o valor da marca-d'água alta para o carregamento de dados delta. No exemplo a seguir, o nome da tabela de controle é watermarktable. Nessa tabela, WatermarkValue é a coluna que armazena o valor da marca-d'água alta, e o tipo é datetime.

    create table watermarktable
(
WatermarkValue datetime,
);
INSERT INTO watermarktable
VALUES ('1/1/2010 12:00:00 AM')

  3. Crie um procedimento armazenado na mesma instância do SQL Server ou do Banco de Dados SQL do Azure usada para criar a tabela de controle. O procedimento armazenado é usado para gravar o novo valor da marca-d'água alta na tabela de controle externa para o próximo carregamento de dados delta.

    CREATE PROCEDURE update_watermark @LastModifiedtime datetime
AS

BEGIN

    UPDATE watermarktable
    SET [WatermarkValue] = @LastModifiedtime 

END

  4. Acesse o modelo Cópia delta do Banco de Dados. Crie uma Conexão com o banco de dados de origem do qual você deseja copiar os dados.

    Captura de tela mostrando a criação de uma nova conexão com a tabela de origem.

  5. Crie uma Conexão com o armazenamento de dados de destino para o qual você deseja copiar os dados.

    Captura de tela mostrando a criação de uma nova conexão com a tabela de destino.

  6. Crie uma Conexão com a tabela de controle externa e o procedimento armazenado criado nas etapas 2 e 3.

    Captura de tela mostrando a criação de uma nova conexão com o armazenamento de dados da tabela de controle.

  7. Selecione Usar este modelo.

  8. Você verá o pipeline disponível, conforme mostrado no seguinte exemplo:

    Captura de tela mostrando o pipeline.

  9. Selecione Procedimento Armazenado. Em Nome do procedimento armazenado, escolha [dbo].[update_watermark] . Selecione Importar parâmetro e Adicionar conteúdo dinâmico.

    Captura de tela mostrando onde definir a atividade de procedimento armazenado.

  10. Grave o conteúdo @{activity('LookupCurrentWaterMark').output.firstRow.NewWatermarkValue} e selecione Concluir.

    Captura de tela mostrando onde gravar o conteúdo dos parâmetros do procedimento armazenado.

  11. Selecione Depurar, insira os Parâmetros e, em seguida, selecione Concluir.

    Captura de tela mostrando o botão Depurar.

  12. Serão exibidos resultados semelhantes ao seguinte exemplo:

    Captura de tela mostrando o resultado da execução do pipeline.

  13. É possível criar novas linhas na tabela de origem. Este é um exemplo de linguagem SQL usado para criar linhas:

    INSERT INTO data_source_table
VALUES (10, 'newdata','9/10/2017 2:23:00 AM')

INSERT INTO data_source_table
VALUES (11, 'newdata','9/11/2017 9:01:00 AM')

  14. Para executar o pipeline novamente, selecione Depurar, insira os Parâmetros e escolha Concluir.

    Você verá que apenas as novas linhas foram copiadas para o destino.

  15. (Opcional) Se você selecionar o Azure Synapse Analytics como o destino dos dados, também precisará fornecer uma conexão com o Armazenamento de Blobs do Azure para preparo, que é necessário para o PolyBase do Azure Synapse Analytics. O modelo vai gerar um caminho de contêiner para você. Após a execução de pipeline, verifique se o contêiner foi criado no Armazenamento de Blobs.

    Captura de tela mostrando onde configurar o Polybase.

Conteúdo relacionado