Estratégias de carregamento de dados para pool de SQL dedicado no Azure Synapse Analytics
Os pools de SQL dedicados SMP tradicionais usam um processo ETL (Extrair, Transformar e Carregar) para carregar dados. O SQL do Synapse, dentro do Azure Synapse Analytics, usa arquitetura de processamento de consulta distribuída que aproveita a escalabilidade e flexibilidade dos recursos de computação e armazenamento.
O uso de um processo ELT (Extrair, Carregar e Transformar) aproveita os recursos integrados de processamento de consulta distribuída e elimina os recursos necessários para a transformação de dados antes do carregamento.
Embora os pools de SQL dedicados deem suporte a muitos métodos de carregamento, incluindo opções populares do SQL Server, como bcp e API SqlBulkCopy, a maneira mais rápida e escalonável de carregar dados é por meio de tabelas externas PolyBase e a instrução COPY.
Com o PolyBase e a instrução COPY, você pode acessar dados externos armazenados no Armazenamento de Blobs do Azure ou Azure Data Lake Storage por meio da linguagem T-SQL. Para obter mais flexibilidade ao carregar, é recomendável usar a instrução COPY.
O que é ELT?
ELT (Extrair, Carregar e Transformar) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados em um pool de SQL dedicado e transformados.
As etapas básicas para implementar ELT são:
- Extrair os dados de origem em arquivos de texto.
- Descarregar os dados no armazenamento de Blobs do Azure ou no Azure Data Lake Store.
- Preparar os dados para o carregamento.
- Carregar os dados em tabelas de preparo com o PolyBase ou o comando COPY.
- Transformar os dados.
- Inserir os dados nas tabelas de produção.
Para obter um tutorial de carregamento, veja Carregamento de dados do armazenamento de blob do Azure.
1. Extrair os dados de origem em arquivos de texto
Obter dados de fora do seu sistema de origem depende da localização de armazenamento. A meta é mover os dados para texto delimitado ou arquivos CSV com suporte.
Formatos de arquivo com suporte
Com o PolyBase e a instrução COPY, você pode carregar dados de texto delimitado codificado em UTF-8 e UTF-16 ou arquivos CSV. Além de texto delimitado ou arquivos CSV, ele é carregado de formatos de arquivo do Hadoop, como ORC e Parquet. O PolyBase e a instrução COPY também podem carregar dados de arquivos compactados Gzip e Snappy.
Não há suporte para ASCII estendido, formato de largura fixa nem formatos aninhados, como WinZip ou XML. Se você está exportando do SQL Server, pode usar a ferramenta de linha de comando bcp para exportar os dados para arquivos de texto delimitados.
2. Descarregar os dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage
Para descarregar dados para o armazenamento do Azure, você pode movê-los para o Armazenamento de Blobs do Azure ou Azure Data Lake Storage Gen2. Em qualquer localização, os dados devem ser armazenados em arquivos de texto. O PolyBase e a instrução COPY podem ser carregados de qualquer uma das localizações.
Ferramentas e serviços que você pode usar para mover dados para o Armazenamento do Microsoft Azure:
- O serviço Azure ExpressRoute melhora a taxa de transferência de rede, o desempenho e a previsibilidade. O ExpressRoute é um serviço que encaminha os dados por uma conexão privada dedicada para o Azure. As conexões do ExpressRoute não encaminham dados pela Internet pública. As conexões oferecem mais confiabilidade e velocidade, latências menores e maior segurança do que as conexões comuns pela Internet.
- O Utilitário AzCopy move os dados para o Armazenamento do Microsoft Azure pela internet pública. Isso funciona se os tamanhos dos seus dados forem inferiores a 10 TB. Para executar cargas regularmente com AzCopy, teste a velocidade da rede para ver se ela é aceitável.
- O Azure Data Factory (ADF) tem um gateway que você pode instalar no seu servidor local. Em seguida, você pode criar um pipeline para mover os dados do seu servidor local para o Armazenamento do Microsoft Azure. Para usar o Data Factory com pools de SQL dedicados, vejaCarregando dados para pools de SQL dedicados.
3. Preparar os dados para o carregamento
Você pode precisar preparar e limpar os dados na sua conta de armazenamento antes de carregá-los. A preparação de dados pode ser executada enquanto seus dados estiverem na origem, conforme você exporta os dados para arquivos de texto ou após os dados no Armazenamento do Microsoft Azure. É mais fácil trabalhar com os dados o mais precocemente possível no processo.
Definir as tabelas
Primeiro, você deve definir as tabelas que vai carregar em seu pool de SQL dedicado ao usar a instrução COPY.
Se você estiver usando o PolyBase, precisará definir tabelas externas em seu pool de SQL dedicado antes de carregar. O PolyBase usa tabelas externas para definir e acessar os dados no Armazenamento do Microsoft 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 os dados armazenados fora do pool de SQL dedicado.
Definir tabelas externas envolve a especificação da fonte de dados, o formato dos arquivos de texto e as definições de tabela. Os artigos de referência de sintaxe do T-SQL que serão necessários são:
Use o seguinte mapeamento de tipo de dados SQL ao carregar arquivos Parquet:
| Tipo do Parquet | Tipo lógico do Parquet (anotação) | Tipo de dados SQL |
|---|---|---|
| BOOLEAN | bit | |
| BINARY/BYTE_ARRAY | varbinary | |
| DOUBLE | FLOAT | |
| FLOAT | real | |
| INT32 | INT | |
| INT64 | BIGINT | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| BINARY | UTF8 | NVARCHAR |
| BINARY | STRING | NVARCHAR |
| BINARY | ENUM | NVARCHAR |
| BINARY | UUID | UNIQUEIDENTIFIER |
| BINARY | DECIMAL | decimal |
| BINARY | JSON | nvarchar(MAX) |
| BINARY | BSON | varbinary(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 | date |
| INT32 | DECIMAL | decimal |
| INT32 | TIME (MILLIS) | time |
| INT64 | INT(64, true) | BIGINT |
| INT64 | INT(64, false) | decimal(20,0) |
| INT64 | DECIMAL | decimal |
| INT64 | TIME (MILLIS) | time |
| INT64 | TIMESTAMP (MILLIS) | datetime2 |
| Tipo complexo | LISTA | varchar(max) |
| Tipo complexo | MAP | varchar(max) |
Importante
- Atualmente, os pools de SQL dedicados não dão suporte a tipos de dados Parquet com a precisão MICROS e NANOS.
- Você poderá enfrentar o seguinte erro se os tipos forem incompatíveis entre Parquet e SQL ou se você tiver tipos de dados Parquet não suportados:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - Não há suporte para o carregamento de um valor fora do intervalo de 0-127 em uma coluna tinyint para o formato de arquivo Parquet e ORC.
Para obter um exemplo de criação de objetos externos, veja Criar tabelas externas.
Arquivos de texto formatados
Se você está usando o PolyBase, os objetos externos definidos precisam 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 se alinhar com a definição da tabela. Para formatar os arquivos de texto:
- Se os seus dados forem provenientes de uma fonte não relacional, você precisa transformá-los em linhas e colunas. Se os dados forem de uma fonte relacional ou não, os dados devem ser transformados para se alinharem com as definições de coluna para a tabela na qual você planeja carregar os dados.
- Formatar dados no arquivo de texto para se alinharem aos tipos de dados e colunas na tabela de destino. O desalinhamento entre os tipos de dados nos arquivos de texto externos e a tabela do pool de SQL dedicado faz com que as linhas a sejam rejeitadas durante o carregamento.
- Separar os campos no arquivo de texto com um terminador. Certifique-se de usar um caractere ou uma sequência de caracteres que não sejam encontrados nos seus dados de origem. Use o terminador especificado com CRIAR FORMATO DE ARQUIVO EXTERNO.
4. Carregar os dados usando o PolyBase ou a instrução COPY
É uma melhor prática carregar dados em uma tabela de preparo. Tabelas de preparo permitem manipular erros sem interferir nas tabelas de produção. Uma tabela de preparo também oferece a oportunidade de usar a arquitetura de processamento paralelo de pool de SQL dedicado para transformações de dados antes de inserir os dados nas tabelas de produção.
Opções de carregamento
Para carregar dados, é possível usar uma destas opções de carregamento:
- A instrução COPY é o utilitário de carregamento recomendado, pois permite carregar dados de forma contínua e flexível. A instrução tem muitos recursos de carregamento adicionais que o PolyBase não fornece. Confira o tutorial de COPY de táxi de Nova York para executar um tutorial de exemplo.
- O PolyBase com T-SQL exige que você defina objetos de dados externos.
- O PolyBase e a instrução COPY com o ADF (Azure Data Factory) é outra ferramenta de orquestração. Ele define um pipeline e agenda de trabalhos.
- O PolyBase com SSIS funciona bem quando seus dados de origem estão no SQL Server. O SSIS define a origem para mapeamentos de tabela de destino e também coordena a carga. Se você já tiver pacotes SSIS, você pode modificar os pacotes para trabalhar com o novo destino do data warehouse.
- O PolyBase com Azure Databricks transfere dados de uma tabela para um dataframe do Databricks e/ou grava os dados de um dataframe do Databricks em uma tabela usando o PolyBase.
Outras opções de carregamento
Além do PolyBase e da instrução COPY, você pode usar bcp ou a API SqlBulkCopy. O bcp é carregado diretamente para o banco de dados sem passar pelo Armazenamento de Blobs do Azure e é destinado somente para pequenas cargas.
Observação
O desempenho de carga dessas opções é mais lento do que o PolyBase e a instrução COPY.
5. Transformar os dados
Enquanto os dados estão na tabela de preparo, execute as transformações que a sua carga de trabalho exige. Em seguida, mova os dados para uma tabela de produção.
6. Inserir os dados nas tabelas de produção
O INSERT INTO... A Instrução SELECT move os dados da tabela de preparo para a tabela permanente.
Ao criar um processo de ETL, tente executar o processo em uma amostra de teste pequena. Tente extrair 1000 linhas da tabela para um arquivo, movê-lo para o Azure e, em seguida, tente carregá-lo em uma tabela de preparo.
Soluções de carregamento de parceiros
Muitos de nossos parceiros têm soluções de carregamento. Para saber mais, consulte uma lista dos nossos parceiros de solução.
Próximas etapas
Para obter diretrizes de carregamento, consulte Melhores práticas de carregamento de dados.