Criar uma estratégia de carregamento de dados PolyBase para pool de SQL dedicado no Azure Synapse Analytics
Os tradicionais data warehouses SMP usam um processo ETL (Extrair, Transformar e Carregar) 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. Um processo ELT (Extração, Carregamento e Transformação) pode aproveitar os recursos de processamento de consulta distribuída integrado e eliminar os recursos necessários para transformar os dados antes do carregamento.
Embora o pool de SQL dê suporte a muitos métodos de carregamento, incluindo opções não PolyBase, como a API SQL BulkCopy e BCP, 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 T-SQL.
ELT (Extração, Carregamento e Transformação)
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:
- 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 nas tabelas de preparo do pool de SQL dedicado usando o PolyBase.
- Transformar os dados.
- Inserir os dados nas tabelas de produção.
Para ver 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 de padrão de carga.
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 os arquivos de texto delimitados compatíveis com 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 suporta ASCII estendido, formato de largura fixa, e formatos aninhados, como XML, JSON e WinZip.
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 Parquet para o Azure Synapse Analytics é o seguinte:
| 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 |
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. Em qualquer localização, os dados devem ser armazenados em arquivos de texto. O PolyBase pode carregar dessas 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 Data Factory com o pool de SQL dedicado, consulte Carregar dados no pool de SQL dedicado.
3. 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 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 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. Veja abaixo os tópicos de sintaxe T-SQL que você precisará:
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 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 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 uma sequência de caracteres que não sejam encontrados na fonte de dados. Use o terminador especificado com CRIAR FORMATO DE ARQUIVO EXTERNO.
4. Carregar os dados nas tabelas de preparo do pool de SQL dedicado usando o PolyBase
É 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 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:
- O PolyBase com o T-SQL funciona bem quando os seus dados estiverem no armazenamento de Blobs do Azure ou no Azure Data Lake Store. 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 coordenar as cargas de T-SQL, você pode usar o Azure Data Factory, SSIS ou as funções do Azure.
- 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 o Azure Data Factory (ADF) é 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 seus dados não forem compatíveis com o PolyBase, você pode usar bcp ou a API do 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.
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 orientações sobre carregamento, consulte as Diretrizes para carregar dados.