Criar uma estratégia de carregamento de dados PolyBase para pool de SQL dedicado
Os data warehouses de sistema de multiprocessamento simétrico (SMP) tradicionais usam um processo de extração, transformação e carregamento (ETL) para carregar dados. O pool de SQL do Azure é uma arquitetura MPP (processamento paralelo maciço) que aproveita a escalabilidade e flexibilidade de recursos de computação e de armazenamento.
Por outro lado, um processo de extração, carregamento e transformação (ELT) pode aproveitar os recursos internos de processamento de consulta distribuída e eliminar os recursos necessários para transformar os dados antes de carregar.
Embora o pool de SQL dê suporte a muitos métodos de carregamento, incluindo opções que não são do PolyBase, como o programa de cópia em massa (bcp) e a API do SQL BulkCopy, a maneira mais rápida e escalonável de carregar dados é por meio do PolyBase. O PolyBase é uma tecnologia que acessa dados externos armazenados no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage por meio da linguagem Transact-SQL (T-SQL).
Implementar a ELT do Polybase
ELT (Extração, Carregamento e Transformação) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados para um data warehouse e transformados.
As etapas básicas para implementar um ELT PolyBase para pool de SQL dedicado são:
- Extraia os dados de origem em arquivos de texto.
- Coloque os dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage.
- Prepare os dados para carregamento.
- Carregue os dados em tabelas de preparo dedicadas do pool de SQL usando o PolyBase.
- Transforme os dados.
- Insira os dados em tabelas de produção.
Para um tutorial de carregamento, confira Carregar o conjunto de dados do Táxi de Nova York.
Para obter mais informações, confira Padrões e estratégias de carregamento.
Extrair os dados de origem em arquivos de texto
Obter dados de fora do seu sistema de origem depende da localização de armazenamento. O objetivo é mover os dados para arquivos de texto delimitados com suporte do PolyBase.
Formatos de arquivos externos do PolyBase
O PolyBase carrega dados de arquivos de texto delimitados e codificados de UTF-8 e UTF-16. O PolyBase também carrega dos formatos de arquivo Hadoop RC File, ORC e Parquet. O PolyBase também pode carregar dados de arquivos compactados Gzip e Snappy. Atualmente, o PolyBase não dá suporte a ASCII estendido, formato de largura fixa ou formatos aninhados, como WinZip, JSON e XML.
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. A tabela a seguir lista os tipos de dados Parquet mapeados para o Azure Synapse Analytics.
| Tipo de dados Parquet | Tipo de dados SQL |
|---|---|
| TINYINT | TINYINT |
| SMALLINT | SMALLINT |
| INT | INT |
| BIGINT | BIGINT |
| booleano | bit |
| double | FLOAT |
| FLOAT | real |
| double | money |
| double | SMALLMONEY |
| string | NCHAR |
| string | NVARCHAR |
| string | char |
| string | varchar |
| binary | binary |
| binary | varbinary |
| timestamp | date |
| timestamp | smalldatetime |
| timestamp | datetime2 |
| timestamp | DATETIME |
| timestamp | time |
| date | date |
| decimal | decimal |
Descarregar os dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage
Para obter os dados no Armazenamento do Microsoft Azure, você pode movê-los para o Armazenamento de Blobs do Azure ou para o Azure Data Lake Storage. Em qualquer localização, os dados devem ser armazenados em arquivos de texto. O PolyBase pode carregar dessas localizações.
Você pode usar as seguintes ferramentas e serviços 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 roteiam 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 o AzCopy, teste a velocidade da rede para ver se ela é aceitável.
- O Azure Data Factory tem um gateway que você pode instalar no 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 o pool de SQL dedicado, confira Carregar dados no Azure Synapse Analytics.
Preparar os dados para o carregamento
Você pode precisar preparar e limpar os dados na sua conta de armazenamento antes de carregá-los no pool de SQL dedicado. A preparação de dados pode ser executada enquanto os dados estão na origem, à medida que você exporta os dados para arquivos de texto ou depois que os dados estão no Armazenamento do Microsoft Azure. É mais fácil trabalhar com os dados o mais cedo possível no processo.
Definir tabelas externas
Antes que você possa carregar os seus dados, você precisa definir tabelas externas no seu data warehouse. 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 data warehouse.
Definir tabelas externas envolve a especificação da fonte de dados, o formato dos arquivos de texto e as definições de tabela. O que se segue são os tópicos de sintaxe T-SQL de que você precisa:
Arquivos de texto formatados
Depois que os objetos externos são definidos, você precisa 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 dados forem provenientes de uma fonte não relacional, você precisará 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 do pool de SQL. O desalinhamento entre os tipos de dados nos arquivos de texto externos e a tabela do data warehouse 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 sequência de caracteres não encontrados em seus dados de origem. Use o terminador especificado com CRIAR FORMATO DE ARQUIVO EXTERNO.
Carregar os dados em tabelas de preparo dedicadas do pool de SQL usando o PolyBase
É uma prática recomendada 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 as funcionalidades de processamento de consulta distribuída integrado no pool de SQL para transformações de dados antes da inserção deles em tabelas de produção.
Opções de carregamento com PolyBase
Para carregar dados com o PolyBase, é possível usar qualquer uma destas opções de carregamento:
- Carregue dados externos usando o Microsoft Entra ID.
- Carregue dados externos usando uma identidade gerenciada.
- O PolyBase com T-SQL funciona bem quando seus dados estão no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage. Ele oferece mais controle sobre o processo de carregamento, mas também exige que você defina objetos de dados externos. Os outros métodos definem esses objetos em segundo plano, como mapear as tabelas de origem para as tabelas de destino. Para orquestrar cargas T-SQL, você pode usar o Azure Data Factory, o SSIS ou o Azure Functions.
- O PolyBase com SQL Server Integration Services (SSIS) funciona bem quando os dados de origem estão no SQL Server. O SSIS define os mapeamentos de tabela de origem para destino e também orquestra 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 o Azure Data Factory é outra ferramenta de orquestração. Ele define um pipeline e agenda de trabalhos.
- O PolyBase com Azure Databricks transfere dados de uma tabela do Azure Synapse Analytics para um dataframe do Databricks e/ou grava os dados de um dataframe do Databricks em uma tabela do Azure Synapse Analytics usando o PolyBase.
Opções de carregamento que não sejam PolyBase
Se os dados não forem compatíveis com o PolyBase, você poderá usar bcp ou a API SQLBulkCopy. O BCP carrega diretamente para o pool de SQL dedicado sem passar pelo armazenamento de Blobs do Azure e é destinado somente para pequenas cargas. Observe que o desempenho de carga dessas opções é mais lento do que o PolyBase.
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.
Inserir os dados nas tabelas de produção
A instrução INSERT INTO ... 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 1.000 linhas da tabela para um arquivo, mova-o para o Azure e tente carregá-lo em uma tabela de preparação.
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.