Cópia delta de um banco de dados com uma tabela de controle
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
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.000Crie 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')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 ENDAcesse 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.
Crie uma Conexão com o armazenamento de dados de destino para o qual você deseja copiar os dados.
Crie uma Conexão com a tabela de controle externa e o procedimento armazenado criado nas etapas 2 e 3.
Selecione Usar este modelo.
Você verá o pipeline disponível, conforme mostrado no seguinte exemplo:
Selecione Procedimento Armazenado. Em Nome do procedimento armazenado, escolha [dbo].[update_watermark] . Selecione Importar parâmetro e Adicionar conteúdo dinâmico.
Grave o conteúdo @{activity('LookupCurrentWaterMark').output.firstRow.NewWatermarkValue} e selecione Concluir.
Selecione Depurar, insira os Parâmetros e, em seguida, selecione Concluir.
Serão exibidos resultados semelhantes ao seguinte exemplo:
É 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')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.
(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.
