Projetar uma estratégia de carregamento de dados PolyBase para pool SQL dedicado no Azure Synapse Analytics

Os armazéns de dados SMP tradicionais usam um processo de extração, transformação e carregamento (ETL) para carregar dados. O pool SQL do Azure é uma arquitetura de processamento paralelo maciço (MPP) que aproveita a escalabilidade e a flexibilidade dos recursos de computação e armazenamento. Um processo ELT (Extrair, Carregar e Transformar) pode tirar proveito dos recursos internos de processamento de consultas distribuídas e eliminar os recursos necessários para transformar os dados antes do carregamento.

Embora o pool SQL ofereça suporte a muitos métodos de carregamento, incluindo opções não Polybase, como BCP e API SQL BulkCopy, a maneira mais rápida e escalável de carregar dados é por meio do PolyBase. O PolyBase é uma tecnologia que acessa dados externos armazenados no armazenamento de Blob do Azure ou no Repositório Azure Data Lake por meio da linguagem T-SQL.

Extrair, carregar e transformar (ELT)

Extrair, carregar e transformar (ELT) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados em um data warehouse e, em seguida, transformados.

As etapas básicas para implementar um ELT PolyBase para pool SQL dedicado 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 de pool SQL dedicadas usando PolyBase.
5. Transforme os dados.
6. Insira os dados em tabelas de produção.

Para obter um tutorial de carregamento, consulte Usar o PolyBase para carregar dados do armazenamento de blobs do Azure para o Azure Synapse Analytics.

Para obter mais informações, consulte Blog Carregando padrões.

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 delimitados suportados pelo PolyBase.

Formatos de arquivo externos do PolyBase

O PolyBase carrega dados de arquivos de texto delimitados codificados UTF-8 e UTF-16. O PolyBase também carrega a partir dos formatos de arquivo Hadoop Arquivo RC, ORC e Parquet. O PolyBase também pode carregar dados de arquivos compactados Gzip e Snappy. Atualmente, o PolyBase não suporta ASCII estendido, formato de largura fixa e 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. O mapeamento de tipo de dados do Parquet to Azure Synapse Analytics é o seguinte:

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. Em qualquer local, os dados devem ser armazenados em arquivos de texto. O PolyBase pode ser carregado 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 pool SQL dedicado, consulte Carregar dados em pool SQL dedicado.

3. Preparar os dados para carregamento

Talvez seja necessário preparar e limpar os dados em sua conta de armazenamento antes de carregá-los no pool SQL dedicado. 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 tabelas externas

Antes de carregar dados, você precisa definir tabelas externas em seu data warehouse. 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 da tabela e aponta para os dados armazenados fora do data warehouse.

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. A seguir estão os tópicos de sintaxe T-SQL que você precisará:

• CRIAR UMA ORIGEM DE DADOS EXTERNA
• CREATE EXTERNAL FILE FORMAT
• CRIAR TABELA EXTERNA

Formatar arquivos de texto

Uma vez que os objetos externos são definidos, você precisa alinhar as linhas dos arquivos de texto com a tabela externa e 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 do pool SQL. O desalinhamento entre os tipos de dados nos arquivos de texto externos e na tabela do data warehouse 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 em tabelas de preparo de pool SQL dedicadas usando o PolyBase

É 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 recursos internos de processamento de consultas distribuídas do pool SQL para transformações de dados antes de inserir os dados em tabelas de produção.

Opções para carregar com o PolyBase

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

• Carregar dados externos usando o Microsoft Entra ID
• Carregar dados externos usando uma identidade gerenciada
• O PolyBase com T-SQL funciona bem quando seus dados estão no armazenamento de Blob do Azure ou no Azure Data Lake Store. Ele oferece mais controle sobre o processo de carregamento, mas também requer que você defina objetos de dados externos. Os outros métodos definem esses objetos nos bastidores à medida que você mapeia tabelas de origem para tabelas de destino. Para orquestrar cargas T-SQL, você pode usar as funções Azure Data Factory, SSIS ou Azure.
• 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 o Azure Data Factory (ADF) é outra ferramenta de orquestração. Ele define um pipeline e agenda trabalhos.
• O PolyBase com o Azure Databricks transfere dados de uma tabela do Azure Synapse Analytics para um dataframe do Databricks e/ou grava dados de um dataframe do Databricks para uma tabela do Azure Synapse Analytics usando o PolyBase.

Opções de carregamento não-PolyBase

Se seus dados não forem compatíveis com o PolyBase, você poderá usar bcp ou a API SQLBulkCopy. O BCP é carregado diretamente no pool SQL dedicado sem passar pelo armazenamento de Blob do Azure e destina-se apenas a pequenas cargas. Observe que o desempenho de carga dessas opções é mais lento do que o PolyBase.

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 1000 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óximos passos

Para obter orientação de carregamento, consulte Orientação para dados de carga.