Carregar dados do Azure Data Lake Storage em pools de SQL dedicados no Azure Synapse Analytics

  • Artigo

Este guia descreve como usar a instrução COPY para carregar dados do Azure Data Lake Storage. Para obter exemplos rápidos sobre como usar a instrução COPY em todos os métodos de autenticação, acesse a seguinte documentação: Carregar dados com segurança usando pools de SQL dedicados.

Observação

Para fornecer comentários sobre a instrução COPY, envie um email para a seguinte lista de distribuição: sqldwcopypreview@service.microsoft.com.

  • Crie a tabela de destino para carregar dados do Azure Data Lake Storage.
  • Crie a instrução COPY para carregar dados no data warehouse.

Caso você não tenha uma assinatura do Azure, crie uma conta gratuita do Azure antes de começar.

Antes de começar

Antes de iniciar este tutorial, baixe e instale a versão mais recente do SSMS (SQL Server Management Studio).

Para este tutorial, você precisa do:

  • Um pool de SQL dedicado. Veja Criar um pool de SQL dedicado e dados de consulta.
  • Uma conta do Data Lake Storage. Veja Introdução ao Azure Data Lake Storage. Para essa conta de armazenamento, você precisará configurar ou especificar uma das seguintes credenciais a serem carregadas: uma chave de conta de armazenamento, uma chave de assinatura de acesso compartilhado (SAS), um usuário do Aplicativo do Azure Directory ou um usuário do Microsoft Entra que tenha a função apropriada do Azure para a conta de armazenamento.
  • Atualmente, a ingestão de dados usando o comando COPY em uma conta de Armazenamento do Azure que usa o novo recurso de partição DNS do Armazenamento do Azure resulta em erro. Provisione uma conta de armazenamento em uma assinatura que não use o particionamento DNS para este tutorial.

Criar a tabela de destino

Conecte-se ao seu pool de SQL dedicado e crie a tabela de destino em que ele será carregado. Neste exemplo, estamos criando uma tabela de dimensões de produto.

-- A: Create the target table
-- DimProduct
CREATE TABLE [dbo].[DimProduct]
(
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL
)
WITH
(
    DISTRIBUTION = HASH([ProductKey]),
    CLUSTERED COLUMNSTORE INDEX
    --HEAP
);

Criar a instrução COPY

Conecte-se ao pool de SQL dedicado e execute a instrução COPY. Para obter uma lista completa de exemplos, acesse a seguinte documentação: Carregar dados com segurança usando pools de SQL dedicados.

-- B: Create and execute the COPY statement

COPY INTO [dbo].[DimProduct]  
--The column list allows you map, omit, or reorder input file columns to target table columns.  
--You can also specify the default value when there is a NULL value in the file.
--When the column list is not specified, columns will be mapped based on source and target ordinality
(
    ProductKey default -1 1,
    ProductLabel default 'myStringDefaultWhenNull' 2,
    ProductName default 'myStringDefaultWhenNull' 3
)
--The storage account location where you data is staged
FROM 'https://storageaccount.blob.core.windows.net/container/directory/'
WITH  
(
   --CREDENTIAL: Specifies the authentication method and credential access your storage account
   CREDENTIAL = (IDENTITY = '', SECRET = ''),
   --FILE_TYPE: Specifies the file type in your storage account location
   FILE_TYPE = 'CSV',
   --FIELD_TERMINATOR: Marks the end of each field (column) in a delimited text (CSV) file
   FIELDTERMINATOR = '|',
   --ROWTERMINATOR: Marks the end of a record in the file
   ROWTERMINATOR = '0x0A',
   --FIELDQUOTE: Specifies the delimiter for data of type string in a delimited text (CSV) file
   FIELDQUOTE = '',
   ENCODING = 'UTF8',
   DATEFORMAT = 'ymd',
   --MAXERRORS: Maximum number of reject rows allowed in the load before the COPY operation is canceled
   MAXERRORS = 10,
   --ERRORFILE: Specifies the directory where the rejected rows and the corresponding error reason should be written
   ERRORFILE = '/errorsfolder',
) OPTION (LABEL = 'COPY: ADLS tutorial');

Otimizar a compactação columnstore

Por padrão, as tabelas são definidas como um índice columnstore clusterizado. Após a conclusão do carregamento, algumas das linhas de dados não podem ser compactadas no columnstore. Há várias razões para isso acontecer. Para obter mais informações, confira gerenciar índices columnstore.

Para otimizar o desempenho da consulta e a compactação columnstore após um carregamento, recrie a tabela para forçar o índice columnstore a compactar todas as linhas.


ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

Otimizar estatísticas

É melhor criar estatísticas de coluna única imediatamente após um carregamento. Há algumas opções de estatísticas. Por exemplo, se você criar estatísticas de coluna única em todas as colunas, poderá levar muito tempo para recompilar todas as estatísticas. Se você souber que determinadas colunas não estarão em predicados de consulta, você poderá ignorar a criação de estatísticas nessas colunas.

Se você decidir criar estatísticas com uma coluna em cada coluna de cada tabela, poderá usar o exemplo de código do procedimento armazenado prc_sqldw_create_stats no artigo estatísticas.

O exemplo a seguir é um bom ponto de partida para a criação de estatísticas. Ele cria estatísticas de coluna única em cada coluna na tabela de dimensões e em cada coluna de junção das tabelas de fatos. Você poderá adicionar estatísticas de coluna única ou múltipla às colunas de tabelas de fatos posteriormente.

Missão cumprida!

Você carregou com êxito os dados no data warehouse. Bom trabalho!

Próximas etapas

Carregar dados é a primeira etapa para desenvolver uma solução de data warehouse usando o Azure Synapse Analytics. Confira nossos recursos de desenvolvimento.

Saiba como desenvolver tabelas para o data warehouse

Para obter mais exemplos de carregamento e referências, veja a seguinte documentação: