Estratégias de carregamento de dados para o conjunto de SQL dedicado no Azure Synapse Analytics
Os conjuntos de SQL dedicados SMP tradicionais utilizam um processo de Extração, Transformação e Carregamento (ETL) para carregar dados. O Synapse SQL, no Azure Synapse Analytics, utiliza a arquitetura de processamento de consultas distribuída que tira partido da escalabilidade e flexibilidade dos recursos de computação e armazenamento.
A utilização de um processo de Extração, Carregamento e Transformação (ELT) tira partido das capacidades de processamento de consultas distribuídas incorporadas e elimina os recursos necessários para a transformação de dados antes do carregamento.
Embora os conjuntos de SQL dedicados suportem muitos métodos de carregamento, incluindo opções de SQL Server populares, como bcp e a API SqlBulkCopy, a forma mais rápida e dimensionável de carregar dados é através de tabelas externas do PolyBase e da instrução COPY.
Com o PolyBase e a instrução COPY, pode aceder a dados externos armazenados no armazenamento de Blobs do Azure ou no Azure Data Lake Store através da linguagem T-SQL. Para maior flexibilidade ao carregar, recomendamos que utilize a instrução COPY.
O que é o ELT?
Extrair, Carregar e Transformar (ELT) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados para um conjunto de SQL dedicado e, em seguida, transformados.
Os passos básicos para implementar o ELT são:
- Extraia os dados de origem para ficheiros de texto.
- Introduza os dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Store.
- Preparar os dados para carregamento.
- Carregue os dados para tabelas de teste com o PolyBase ou o comando COPY.
- Transforme os dados.
- Insira os dados em tabelas de produção.
Para obter um tutorial de carregamento, veja carregar dados do armazenamento de blobs do Azure.
1. Extrair os dados de origem para ficheiros de texto
A saída dos dados do sistema de origem depende da localização de armazenamento. O objetivo é mover os dados para texto delimitado suportado ou ficheiros CSV.
Formatos de ficheiro suportados
Com o PolyBase e a instrução COPY, pode carregar dados de texto delimitado codificado UTF-8 e UTF-16 ou ficheiros CSV. Além de texto delimitado ou ficheiros CSV, carrega a partir dos formatos de ficheiro Hadoop, como ORC e Parquet. O PolyBase e a instrução COPY também podem carregar dados de ficheiros comprimidos Gzip e Snappy.
AsCII expandida, formato de largura fixa e formatos aninhados, como WinZip ou XML, não são suportados. Se estiver a exportar a partir de SQL Server, pode utilizar a ferramenta de linha de comandos bcp para exportar os dados para ficheiros de texto delimitados.
2. Aceder aos dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Store
Para localizar os dados no armazenamento do Azure, pode movê-lo para o armazenamento de Blobs do Azure ou para o Azure Data Lake Store Gen2. Em qualquer uma das localizações, os dados devem ser armazenados em ficheiros de texto. O PolyBase e a instrução COPY podem ser carregados a partir de qualquer localização.
Ferramentas e serviços que pode utilizar para mover dados para o Armazenamento do Azure:
- O serviço Azure ExpressRoute melhora o débito de rede, o desempenho e a previsibilidade. O ExpressRoute é um serviço que encaminha os seus dados através de uma ligação privada dedicada ao Azure. As ligações do ExpressRoute não encaminham dados através da Internet pública. As ligações oferecem mais fiabilidade, velocidades mais rápidas, latências mais baixas e maior segurança do que as ligações típicas através da Internet pública.
- O utilitário AzCopy move dados para o Armazenamento do Azure através da Internet pública. Isto funciona se os tamanhos de dados forem inferiores a 10 TB. Para executar cargas regularmente com o AzCopy, teste a velocidade de rede para ver se é aceitável.
- Azure Data Factory (ADF) tem um gateway que pode instalar no servidor local. Em seguida, pode criar um pipeline para mover dados do servidor local para o Armazenamento do Azure. Para utilizar o Data Factory com conjuntos de SQL dedicados, veja Carregar dados para conjuntos de SQL dedicados.
3. Preparar os dados para o carregamento
Poderá ter de preparar e limpar os dados na sua conta de armazenamento antes de carregar. A preparação de dados pode ser efetuada enquanto os seus dados estão na origem, à medida que exporta os dados para ficheiros de texto ou após 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, tem de definir as tabelas para as quais está a carregar no conjunto de SQL dedicado ao utilizar a instrução COPY.
Se estiver a utilizar o PolyBase, terá de definir tabelas externas no conjunto de SQL dedicado antes de carregar. O PolyBase utiliza tabelas externas para definir e aceder aos dados no Armazenamento do Azure. Uma tabela externa é semelhante a uma vista de base de dados. A tabela externa contém o esquema da tabela e aponta para dados armazenados fora do conjunto de SQL dedicado.
Definir tabelas externas envolve especificar a origem de dados, o formato dos ficheiros de texto e as definições da tabela. Os artigos de referência da sintaxe T-SQL de que irá precisar são:
Utilize o seguinte mapeamento do tipo de dados SQL ao carregar ficheiros Parquet:
| Tipo parquet | Tipo lógico parquet (anotação) | Tipo de dados SQL |
|---|---|---|
| BOOLEANO | bit | |
| BINÁRIO/BYTE_ARRAY | varbinário | |
| DUPLO | float | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| BINÁRIO | UTF8 | nvarchar |
| BINÁRIO | CADEIA | nvarchar |
| BINÁRIO | ENUM | nvarchar |
| BINÁRIO | UUID | uniqueidentifier |
| BINÁRIO | DECIMAL | decimal |
| BINÁRIO | JSON | nvarchar(MAX) |
| BINÁRIO | BSON | varbinary(max) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL | decimal |
| BYTE_ARRAY | INTERVALO | varchar(max), |
| INT32 | INT(8; verdadeiro) | smallint |
| INT32 | INT(16; verdadeiro) | smallint |
| INT32 | INT(32; verdadeiro) | int |
| INT32 | INT(8; falso) | tinyint |
| INT32 | INT(16; falso) | int |
| INT32 | INT(32; falso) | bigint |
| INT32 | DATA | data |
| INT32 | DECIMAL | decimal |
| INT32 | TIME (MILLIS ) | hora |
| INT64 | INT(64; verdadeiro) | bigint |
| INT64 | INT(64; falso ) | decimal(20,0) |
| INT64 | DECIMAL | decimal |
| INT64 | TIME (MILLIS) | hora |
| INT64 | CARIMBO DE DATA/HORA (MILLIS) | datetime2 |
| Tipo complexo | LISTA | varchar(max) |
| Tipo complexo | MAPA | varchar(max) |
Importante
- Atualmente, os conjuntos dedicados do SQL não suportam tipos de dados Parquet com precisão MICROS e NANOS.
- Poderá deparar-se com o seguinte erro se os tipos forem incompatíveis entre Parquet e SQL ou se tiver tipos de dados Parquet não suportados:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - O carregamento de um valor fora do intervalo de 0-127 para uma coluna tinyint para o formato de ficheiro Parquet e ORC não é suportado.
Para obter um exemplo de criação de objetos externos, veja Criar tabelas externas.
Formatar ficheiros de texto
Se estiver a utilizar o PolyBase, os objetos externos definidos têm de alinhar as linhas dos ficheiros de texto com a definição de formato de ficheiro e tabela externa. Os dados em cada linha do ficheiro de texto têm de estar alinhados com a definição da tabela. Para formatar os ficheiros de texto:
- Se os seus dados forem provenientes de uma origem não relacional, terá de os transformar em linhas e colunas. Quer os dados sejam de uma origem relacional ou não relacional, os dados têm de ser transformados para se alinharem com as definições de coluna da tabela na qual planeia carregar os dados.
- Formate dados no ficheiro de texto para alinhar com as colunas e os tipos de dados na tabela de destino. O desalinhamento entre os tipos de dados nos ficheiros de texto externos e a tabela do conjunto de SQL dedicado faz com que as linhas sejam rejeitadas durante o carregamento.
- Separe os campos no ficheiro de texto com um terminador. Certifique-se de que utiliza um caráter ou uma sequência de carateres que não se encontra nos dados de origem. Utilize o terminador que especificou com CREATE EXTERNAL FILE FORMAT.
4. Carregar os dados com o PolyBase ou a instrução COPY
É melhor prática carregar dados para uma tabela de teste. As tabelas de teste permitem-lhe processar erros sem interferir com as tabelas de produção. Uma tabela de teste também lhe dá a oportunidade de utilizar a arquitetura de processamento paralelo do conjunto de SQL dedicado para transformações de dados antes de inserir os dados em tabelas de produção.
Opções de carregamento
Para carregar dados, pode utilizar qualquer uma destas opções de carregamento:
- A instrução COPY é o utilitário de carregamento recomendado, uma vez que lhe permite carregar dados de forma totalmente integrada e flexível. A instrução tem muitas capacidades de carregamento adicionais que o PolyBase não fornece. Veja o tutorial NY taxi cab COPY para ver um tutorial de exemplo.
- O PolyBase com T-SQL requer que defina objetos de dados externos.
- A instrução PolyBase e COPY com Azure Data Factory (ADF) é outra ferramenta de orquestração. Define um pipeline e agenda tarefas.
- O PolyBase com SSIS funciona bem quando os dados de origem estão no SQL Server. O SSIS define a origem para mapeamentos de tabelas de destino e também orquestra a carga. Se já tiver pacotes do SSIS, pode modificar os pacotes para funcionarem com o novo destino do armazém de dados.
- O PolyBase com o Azure Databricks transfere dados de uma tabela para um dataframe do Databricks e/ou escreve dados de um dataframe do Databricks para uma tabela com o PolyBase.
Outras opções de carregamento
Além do PolyBase e da instrução COPY, pode utilizar o bcp ou a API SqlBulkCopy. O bcp carrega diretamente para a base de dados sem passar pelo armazenamento de Blobs do Azure e destina-se apenas a pequenas cargas.
Nota
O desempenho de carga destas opções é mais lento do que o PolyBase e a instrução COPY.
5. Transformar os dados
Enquanto os dados estiverem na tabela de teste, execute as transformações necessárias para a carga de trabalho. Em seguida, mova os dados para uma tabela de produção.
6. Inserir os dados em tabelas de produção
A tecla INSERT INTO... A instrução SELECT move os dados da tabela de teste para a tabela permanente.
À medida que cria um processo etl, experimente executar o processo num pequeno exemplo de teste. Experimente extrair 1000 linhas da tabela para um ficheiro, movê-lo para o Azure e, em seguida, tente carregá-lo para uma tabela de teste.
Soluções de carregamento de parceiros
Muitos dos nossos parceiros têm soluções de carregamento. Para saber mais, veja uma lista dos nossos parceiros de solução.
Passos seguintes
Para obter orientações sobre o carregamento, veja Melhores práticas de carregamento de dados.