Partilhar via

Estratégias de carregamento de dados para o conjunto de SQL dedicado no Azure Synapse Analytics

  • Artigo

Os pools SQL dedicados SMP tradicionais usam um processo ETL (Extract, Transform and Load) para carregar dados. O Synapse SQL, dentro do Azure Synapse Analytics, usa a arquitetura de processamento de consultas distribuídas que aproveita a escalabilidade e a flexibilidade dos recursos de computação e armazenamento.

O uso de um processo ELT (Extract, Load and Transform) usa recursos internos de processamento de consultas distribuídas e elimina os recursos necessários para a transformação de dados antes do carregamento.

Embora os pools SQL dedicados ofereçam suporte a muitos métodos de carregamento, incluindo opções populares do SQL Server, como bcp e a API SqlBulkCopy, a maneira mais rápida e escalável de carregar dados é por meio de tabelas externas PolyBase e da instrução COPY.

Com o PolyBase e a instrução COPY, você pode acessar dados externos armazenados no armazenamento de Blob do Azure ou no Repositório Azure Data Lake por meio da linguagem T-SQL. Para obter a maior flexibilidade ao carregar, recomendamos o uso da instrução COPY.

O que é ELT?

Extrair, Carregar e Transformar (ELT) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados em um pool SQL dedicado e, em seguida, transformados.

Os passos básicos para a implementação do ELT são:

  1. Extraia os dados de origem para ficheiros de texto.
  2. Coloque os dados no armazenamento de Blob do Azure ou no Azure Data Lake Store.
  3. Prepare os dados para carregamento.
  4. Carregue os dados em tabelas de preparo com o PolyBase ou o comando COPY.
  5. Transforme os dados.
  6. Insira os dados em tabelas de produção.

Para obter um tutorial de carregamento, consulte Carregando dados do armazenamento de blob do Azure.

1. Extraia os dados de origem em arquivos de texto

A obtenção de dados do sistema de origem depende do local de armazenamento. O objetivo é mover os dados para arquivos de texto delimitado ou CSV suportados.

Formatos de ficheiro suportados

Com o PolyBase e a instrução COPY, você pode carregar dados de texto delimitado codificado UTF-8 e UTF-16 ou arquivos CSV. Além de arquivos de texto ou CSV delimitados, ele é carregado a partir dos formatos de arquivo Hadoop, como ORC e Parquet. O PolyBase e a instrução COPY também podem carregar dados de arquivos compactados Gzip e Snappy.

ASCII estendido, formato de largura fixa e formatos aninhados, como WinZip ou XML, não são suportados. Se você estiver exportando do SQL Server, poderá usar a ferramenta de linha de comando bcp para exportar os dados para arquivos de texto delimitados.

2. Coloque os dados no armazenamento de Blob do Azure ou no Repositório Azure Data Lake

Para colocar os dados no armazenamento do Azure, você pode movê-los para o armazenamento de Blob do Azure ou para o Azure Data Lake Store Gen2. Em qualquer local, os dados devem ser armazenados em arquivos de texto. O PolyBase e a instrução COPY podem ser carregados de qualquer local.

Ferramentas e serviços que você pode usar para mover dados para o Armazenamento do Azure:

  • O serviço Azure ExpressRoute melhora a taxa de transferência, o desempenho e a previsibilidade da rede. O ExpressRoute é um serviço que encaminha seus dados por meio de uma conexão privada dedicada ao Azure. As conexões de Rota Expressa não roteiam dados pela Internet pública. As conexões oferecem mais confiabilidade, velocidades mais rápidas, latências mais baixas e maior segurança do que as conexões típicas pela internet pública.
  • O utilitário AzCopy move dados para o Armazenamento do Azure através da Internet pública. Isso funciona se os tamanhos dos dados forem inferiores a 10 TB. Para executar cargas regularmente com o AzCopy, teste a velocidade da rede para ver se é aceitável.
  • O Azure Data Factory (ADF) tem um gateway que você pode instalar em seu servidor local. Em seguida, você pode criar um pipeline para mover dados do servidor local para o Armazenamento do Azure. Para usar o Data Factory com pools SQL dedicados, consulte Carregando dados para pools SQL dedicados.

3. Preparar os dados para carregamento

Talvez seja necessário preparar e limpar os dados em sua conta de armazenamento antes de carregar. A preparação de dados pode ser executada enquanto os dados estão na origem, à medida que exporta os dados para ficheiros de texto ou depois de os dados estarem no Armazenamento do Azure. É mais fácil trabalhar com os dados o mais cedo possível no processo.

Definir as tabelas

Primeiro, defina as tabelas para as quais você está carregando em seu pool SQL dedicado ao usar a instrução COPY.

Se você estiver usando o PolyBase, precisará definir tabelas externas em seu pool SQL dedicado antes de carregar. O PolyBase usa tabelas externas para definir e acessar os dados no Armazenamento do Azure. Uma tabela externa é semelhante a uma exibição de banco de dados. A tabela externa contém o esquema de tabela e aponta para dados armazenados fora do pool SQL dedicado.

A definição de tabelas externas envolve a especificação da fonte de dados, do formato dos arquivos de texto e das definições de tabela. Os artigos de referência de sintaxe T-SQL de que você precisará são:

Use o seguinte mapeamento de tipo de dados SQL ao carregar arquivos Parquet:

Tipo de parquet Tipo lógico de Parquet (anotação) Tipo de dados SQL
BOOLEANO bit
BINÁRIO / BYTE_ARRAY Varbinary
DUPLO flutuar
FLUTUAR reais
INT32 Int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY binário
BINÁRIO UTF8 Nvarchar
BINÁRIO STRING Nvarchar
BINÁRIO ENUM Nvarchar
BINÁRIO UUID uniqueidentifier
BINÁRIO DECIMAL decimal
BINÁRIO JSON nvarchar (MAX)
BINÁRIO BSON varbinário (MAX)
FIXED_LEN_BYTE_ARRAY DECIMAL decimal
BYTE_ARRAY INTERVAL varchar (MAX)
INT32 INT(8, true) Smallint
INT32 INT(16, true) Smallint
INT32 INT(32, true) Int
INT32 INT(8, false) tinyint
INT32 INT(16, false) Int
INT32 INT(32, false) bigint
INT32 DATE data
INT32 DECIMAL decimal
INT32 TIME (MILLIS) Hora
INT64 INT(64, true) bigint
INT64 INT(64, false ) decimal(20,0)
INT64 DECIMAL decimal
INT64 TIME (MILLIS) Hora
INT64 TIMESTAMP (MILLIS) datetime2
Tipo complexo LIST varchar (máx.)
Tipo complexo MAP varchar (máx.)

Importante

  • Atualmente, os pools dedicados SQL não oferecem suporte a tipos de dados Parquet com precisão MICROS e NANOS.
  • Você pode enfrentar o seguinte erro se os tipos forem incompatíveis entre Parquet e SQL ou se você tiver tipos de dados Parquet sem suporte: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:...
  • Não há suporte para carregar um valor fora do intervalo de 0 a 127 em uma coluna minúscula para o formato de arquivo Parquet e ORC.

Para obter um exemplo de criação de objetos externos, consulte Criar tabelas externas.

Formatar arquivos de texto

Se você estiver usando PolyBase, os objetos externos definidos precisarão alinhar as linhas dos arquivos de texto com a tabela externa e a definição de formato de arquivo. Os dados em cada linha do arquivo de texto devem estar alinhados com a definição da tabela.

Para formatar os ficheiros de texto:

  • Se seus dados forem provenientes de uma fonte não relacional, você precisará transformá-los em linhas e colunas. Quer os dados sejam de uma fonte relacional ou não relacional, os dados devem ser transformados para se alinharem com as definições de coluna da tabela na qual você planeja carregar os dados.
  • Formate os dados no arquivo de texto para alinhá-los com as colunas e os tipos de dados na tabela de destino. O desalinhamento entre os tipos de dados nos arquivos de texto externos e na tabela de pool SQL dedicada faz com que as linhas sejam rejeitadas durante o carregamento.
  • Separe campos no arquivo de texto com um terminador. Certifique-se de usar um caractere ou uma sequência de caracteres que não seja encontrada em seus dados de origem. Use o terminador especificado com CREATE EXTERNAL FILE FORMAT.

4. Carregue os dados usando o PolyBase ou a instrução COPY

É uma prática recomendada carregar dados em uma tabela de preparo. As tabelas de preparo permitem lidar com erros sem interferir com as tabelas de produção. Uma tabela de preparo também oferece a oportunidade de usar a arquitetura de processamento paralelo do pool SQL dedicado para transformações de dados antes de inserir os dados em tabelas de produção.

Opções de carregamento

Para carregar dados, você pode usar qualquer uma destas opções de carregamento:

  • A instrução COPY é o utilitário de carregamento recomendado, pois permite que você carregue dados de forma fácil e flexível. A instrução tem muitos recursos de carregamento adicionais que o PolyBase não fornece. Consulte o tutorial COPY do táxi de NY para executar um tutorial de exemplo.
  • O PolyBase com T-SQL requer que você defina objetos de dados externos.
  • A instrução PolyBase e COPY com o Azure Data Factory (ADF) é outra ferramenta de orquestração. Ele define um pipeline e agenda trabalhos.
  • O PolyBase com SSIS funciona bem quando os dados de origem estão no SQL Server. O SSIS define os mapeamentos da tabela de origem para destino e também orquestra a carga. Se você já tiver pacotes SSIS, poderá modificá-los para trabalhar com o novo destino do data warehouse.
  • O PolyBase com Azure Databricks transfere dados de uma tabela para um dataframe Databricks e/ou grava dados de um dataframe Databricks em uma tabela usando PolyBase.

Reveja os tutoriais disponíveis:

Outras opções de carregamento

Além do PolyBase e da instrução COPY, você pode usar bcp ou a API SqlBulkCopy. O bcp utilitário é carregado diretamente no banco de dados sem passar pelo armazenamento de Blob do Azure e destina-se apenas a pequenas cargas.

Nota

O desempenho de carga dessas opções é mais lento do que o PolyBase e a instrução COPY.

5. Transforme os dados

Enquanto os dados estiverem na tabela de preparo, execute as transformações exigidas pela sua carga de trabalho. Em seguida, mova os dados para uma tabela de produção.

6. Inserir os dados nos quadros de produção

O INSERT INTO ... A instrução SELECT move os dados da tabela de preparo para a tabela permanente.

Ao projetar um processo ETL, tente executar o processo em uma pequena amostra de teste. Tente extrair 1.000 linhas da tabela para um arquivo, mova-o para o Azure e tente carregá-lo em uma tabela de preparo.

Soluções de carregamento de parceiros

Muitos dos nossos parceiros têm soluções de carregamento. Para saber mais, consulte uma lista dos nossos parceiros de soluções.

Próximo passo

Práticas recomendadas de carregamento de dados