Conceber uma estratégia de carregamento de dados do PolyBase para o conjunto de SQL dedicado no Azure Synapse Analytics

  • Artigo

Os armazéns de dados SMP tradicionais utilizam um processo de Extração, Transformação e Carregamento (ETL) para carregar dados. SQL do Azure conjunto é uma arquitetura de processamento paralelo em massa (MPP) que tira partido da escalabilidade e flexibilidade dos recursos de computação e armazenamento. Um processo de Extração, Carregamento e Transformação (ELT) pode tirar partido das capacidades de processamento de consultas distribuídas incorporadas e eliminar os recursos necessários para transformar os dados antes de carregar.

Embora o conjunto de SQL suporte muitos métodos de carregamento, incluindo opções não Polybase, como BCP e API de BulkCopy do SQL, a forma mais rápida e dimensionável de carregar dados é através do PolyBase. O PolyBase é uma tecnologia que acede a dados externos armazenados no armazenamento de Blobs do Azure ou no Azure Data Lake Store através da linguagem T-SQL.

Extrair, Carregar e Transformar (ELT)

Extração, Carregamento e Transformação (ELT) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados para um armazém de dados e depois transformados.

Os passos básicos para implementar um PolyBase ELT para o conjunto de SQL dedicado são:

  1. Extraia os dados de origem para ficheiros de texto.
  2. Introduza os dados no Armazenamento de Blobs do Azure ou no Azure Data Lake Store.
  3. Preparar os dados para carregamento.
  4. Carregue os dados para tabelas de teste de conjuntos de SQL dedicadas com o PolyBase.
  5. Transforme os dados.
  6. Insira os dados em tabelas de produção.

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

Para obter mais informações, veja Blogue Carregar padrões.

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

Formatos de ficheiro externos do PolyBase

O PolyBase carrega dados de ficheiros de texto delimitados codificados UTF-8 e UTF-16. O PolyBase também carrega a partir dos formatos de ficheiro Hadoop RC File, ORC e Parquet. O PolyBase também pode carregar dados de ficheiros comprimidos Gzip e Snappy. Atualmente, o PolyBase não suporta formatos ASCII expandidos, de largura fixa e de formatos aninhados, como WinZip, JSON e XML.

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. O mapeamento do tipo de dados Parquet para Azure Synapse Analytics é o seguinte:

Tipo de Dados Parquet Tipo de Dados SQL
tinyint tinyint
smallint smallint
int int
bigint bigint
boolean bit
double float
float real
double dinheiro
double smallmoney
string nchar
string nvarchar
string caráter
string varchar
binary binary
binary varbinário
carimbo de data/hora data
carimbo de data/hora smalldatetime
carimbo de data/hora datetime2
carimbo de data/hora datetime
carimbo de data/hora hora
data data
decimal decimal

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. Em qualquer uma das localizações, os dados devem ser armazenados em ficheiros de texto. O PolyBase pode carregar 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 o conjunto de SQL dedicado, veja Carregar dados para o conjunto de SQL dedicado.

3. Preparar os dados para o carregamento

Poderá ter de preparar e limpar os dados na sua conta de armazenamento antes de os carregar para o conjunto de SQL dedicado. 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 tabelas externas

Antes de poder carregar dados, tem de definir tabelas externas no seu armazém de dados. 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 armazém de dados.

Definir tabelas externas envolve especificar a origem de dados, o formato dos ficheiros de texto e as definições da tabela. Seguem-se os tópicos de sintaxe T-SQL de que irá precisar:

Formatar ficheiros de texto

Assim que os objetos externos estiverem definidos, terá 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 do conjunto de SQL. O desalinhamento entre os tipos de dados nos ficheiros de texto externos e a tabela do armazém de dados 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 para tabelas de teste do conjunto de SQL dedicado com o PolyBase

É 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 as capacidades de processamento de consultas distribuídas incorporadas do conjunto de 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, pode utilizar qualquer uma destas opções de carregamento:

  • O PolyBase com T-SQL funciona bem quando os seus dados estão no armazenamento de Blobs do Azure ou no Azure Data Lake Store. Dá-lhe o maior controlo sobre o processo de carregamento, mas também requer que defina objetos de dados externos. Os outros métodos definem estes objetos em segundo plano à medida que mapeia tabelas de origem para tabelas de destino. Para orquestrar cargas T-SQL, pode utilizar Azure Data Factory, SSIS ou funções do Azure.
  • 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 Azure Data Factory (ADF) é outra ferramenta de orquestração. Define um pipeline e agenda tarefas.
  • O PolyBase com o Azure Databricks transfere dados de uma tabela do Azure Synapse Analytics para um dataframe do Databricks e/ou escreve dados de um dataframe do Databricks para uma tabela do Azure Synapse Analytics com o PolyBase.

Opções de carregamento não PolyBase

Se os seus dados não forem compatíveis com o PolyBase, pode utilizar o bcp ou a API SQLBulkCopy. O BCP carrega diretamente para o conjunto de SQL dedicado sem passar pelo armazenamento de Blobs do Azure e destina-se apenas a pequenas cargas. Tenha em atenção que o desempenho de carga destas opções é mais lento do que o PolyBase.

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 Documentação de orientação para carregar dados.