Estratégias de carregamento de dados para o Azure SQL Data WarehouseData loading strategies for Azure SQL Data Warehouse

Os data warehouses SMP tradicionais usam um processo ETL (extração, transformação e carregamento) para carregar dados.Traditional SMP data warehouses use an Extract, Transform, and Load (ETL) process for loading data. O SQL Data Warehouse do Azure é uma arquitetura MPP (processamento paralelo maciço) que aproveita a escalabilidade e flexibilidade de recursos de computação e de armazenamento.Azure SQL Data Warehouse is a massively parallel processing (MPP) architecture that takes advantage of the scalability and flexibility of compute and storage resources. A utilização de um processo ELT (Extrair, Carregar e Transformar) pode aproveitar o MPP e eliminar recursos necessários para transformar os dados antes do carregamento.Utilizing an Extract, Load, and Transform (ELT) process can take advantage of MPP and eliminate resources needed to transform the data prior to loading. Embora SQL Data Warehouse ofereça suporte a muitos métodos de carregamento, incluindo opções populares de SQL Server como BCP e a API do SQL BulkCopy, a maneira mais rápida e escalonável de carregar dados é por meio de tabelas externas do polybase e da instrução de cópia (visualização).While SQL Data Warehouse supports many loading methods including popular SQL Server options such as BCP and the SQL BulkCopy API, the fastest and most scalable way to load data is through PolyBase external tables and the COPY statement (preview). Com o polybase e a instrução de cópia, você pode acessar dados externos armazenados no armazenamento de BLOBs do Azure ou Azure Data Lake Store por meio da linguagem T-SQL.With PolyBase and the COPY statement, you can access external data stored in Azure Blob storage or Azure Data Lake Store via the T-SQL language. Para obter mais flexibilidade ao carregar no SQL Data Warehouse, é recomendável usar a instrução de cópia.For the most flexibility when loading into SQL Data Warehouse, we recommend using the COPY statement.

Observação

A instrução de cópia está atualmente em visualização pública.The COPY statement is currently in public preview. Para fornecer comentários, envie um email para a seguinte lista de distribuição: sqldwcopypreview@service.microsoft.com.To provide feedback, send email to the following distribution list: sqldwcopypreview@service.microsoft.com.

O que é ELT?What is ELT?

O ELT (extração, carregamento e transformação) é um processo pelo qual os dados são extraídos de um sistema de origem, carregados em um data warehouse e, em seguida, transformados.Extract, Load, and Transform (ELT) is a process by which data is extracted from a source system, loaded into a data warehouse, and then transformed.

As etapas básicas para implementar ELT para SQL Data Warehouse são:The basic steps for implementing ELT for SQL Data Warehouse are:

  1. Extrair os dados de origem em arquivos de texto.Extract the source data into text files.
  2. Descarregar os dados no armazenamento de Blobs do Azure ou no Azure Data Lake Store.Land the data into Azure Blob storage or Azure Data Lake Store.
  3. Preparar os dados para o carregamento.Prepare the data for loading.
  4. Carregue os dados em SQL Data Warehouse tabelas de preparo com o polybase ou o comando de cópia.Load the data into SQL Data Warehouse staging tables with PolyBase or the COPY command.
  5. Transformar os dados.Transform the data.
  6. Inserir os dados nas tabelas de produção.Insert the data into production tables.

Para obter um tutorial de carregamento do polybase, consulte usar o polybase para carregar dados do armazenamento de BLOBs do Azure para o Azure SQL data warehouse.For a PolyBase loading tutorial, see Use PolyBase to load data from Azure blob storage to Azure SQL Data Warehouse.

Para obter mais informações, consulte Blog de padrão de carga.For more information, see Loading patterns blog.

1. Extraia os dados de origem em arquivos de texto1. Extract the source data into text files

Obter dados de fora do seu sistema de origem depende da localização de armazenamento.Getting data out of your source system depends on the storage location. O objetivo é mover os dados para o polybase e a cópia com suporte de texto delimitado ou arquivos CSV.The goal is to move the data into PolyBase and the COPY supported delimited text or CSV files.

Polybase e copiar formatos de arquivo externoPolyBase and COPY external file formats

Com o polybase e a instrução de cópia, você pode carregar dados de texto delimitado codificado em UTF-8 e UTF-16 ou arquivos CSV.With PolyBase and the COPY statement, you can load data from UTF-8 and UTF-16 encoded delimited text or CSV files. Além de texto delimitado ou arquivos CSV, ele é carregado a partir dos formatos de arquivo do Hadoop, como ORC e parquet.In addition to delimited text or CSV files, it loads from the Hadoop file formats such as ORC and Parquet. O polybase e a instrução de cópia também podem carregar dados de arquivos compactados gzip e de instantâneo.PolyBase and the COPY statement can also load data from Gzip and Snappy compressed files. Não há suporte para ASCII estendido, formato de largura fixa e formatos aninhados como o WinZip ou XML.Extended ASCII, fixed-width format, and nested formats such as WinZip or XML are not supported. 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.If you are exporting from SQL Server, you can use the bcp command-line tool to export the data into delimited text files.

2. Pouse os dados no armazenamento de BLOBs do Azure ou Azure Data Lake Store2. Land the data into Azure Blob storage or Azure Data Lake Store

Para colocar os dados no armazenamento do Azure, você pode movê-los para o armazenamento de BLOBs do Azure ou Azure data Lake Store Gen2.To land the data in Azure storage, you can move it to Azure Blob storage or Azure Data Lake Store Gen2. Em qualquer localização, os dados devem ser armazenados em arquivos de texto.In either location, the data should be stored in text files. O polybase e a instrução de cópia podem ser carregados de qualquer um dos locais.PolyBase and the COPY statement can load from either location.

Ferramentas e serviços que você pode usar para mover dados para o Armazenamento do Microsoft Azure:Tools and services you can use to move data to Azure Storage:

  • O serviço Azure ExpressRoute melhora a taxa de transferência de rede, o desempenho e a previsibilidade.Azure ExpressRoute service enhances network throughput, performance, and predictability. O ExpressRoute é um serviço que encaminha os dados por uma conexão privada dedicada para o Azure.ExpressRoute is a service that routes your data through a dedicated private connection to Azure. As conexões do ExpressRoute não encaminham dados pela Internet pública.ExpressRoute connections do not route data through the public internet. As conexões oferecem mais confiabilidade e velocidade, latências menores e maior segurança do que as conexões comuns pela Internet.The connections offer more reliability, faster speeds, lower latencies, and higher security than typical connections over the public internet.
  • O Utilitário AZCopy move os dados para o Armazenamento do Microsoft Azure pela internet pública.AZCopy utility moves data to Azure Storage over the public internet. Isso funciona se os tamanhos dos seus dados forem inferiores a 10 TB.This works if your data sizes are less than 10 TB. Para executar cargas regularmente com AZCopy, teste a velocidade da rede para ver se ela é aceitável.To perform loads on a regular basis with AZCopy, test the network speed to see if it is acceptable.
  • O Azure Data Factory (ADF) tem um gateway que você pode instalar no seu servidor local.Azure Data Factory (ADF) has a gateway that you can install on your local server. Em seguida, você pode criar um pipeline para mover os dados do seu servidor local para o Armazenamento do Microsoft Azure.Then you can create a pipeline to move data from your local server up to Azure Storage. Para usar o Data Factory com o SQL Data Warehouse, consulte Carregar dados no SQL Data Warehouse.To use Data Factory with SQL Data Warehouse, see Load data into SQL Data Warehouse.

3. preparar os dados para carregar3. Prepare the data for loading

Você pode precisar preparar e limpar os dados na sua conta de armazenamento antes de carregá-los no SQL Data Warehouse.You might need to prepare and clean the data in your storage account before loading it into SQL Data Warehouse. 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.Data preparation can be performed while your data is in the source, as you export the data to text files, or after the data is in Azure Storage. É mais fácil trabalhar com os dados o mais precocemente possível no processo.It is easiest to work with the data as early in the process as possible.

Definir tabelas externasDefine external tables

Se você estiver usando o polybase, precisará definir tabelas externas no seu data warehouse antes de carregar.If you are using PolyBase, you need to define external tables in your data warehouse before loading. As tabelas externas não são exigidas pela instrução de cópia.External tables are not required by the COPY statement. O PolyBase usa tabelas externas para definir e acessar os dados no Armazenamento do Microsoft Azure.PolyBase uses external tables to define and access the data in Azure Storage. Uma tabela externa é semelhante a uma exibição de banco de dados.An external table is similar to a database view. A tabela externa contém o esquema de tabela e aponta para os dados armazenados fora do data warehouse.The external table contains the table schema and points to data that is stored outside the 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.Defining external tables involves specifying the data source, the format of the text files, and the table definitions. Os tópicos de sintaxe T-SQL que serão necessários são:T-SQL syntax topics that you will need are:

Ao carregar parquet, o mapeamento de tipo de dados com o SQL DW é:When loading Parquet, the data type mapping with SQL DW is:

Tipo de dados parquetParquet Data Type Tipo de dados SQLSQL Data Type
tinyinttinyint tinyinttinyint
smallintsmallint smallintsmallint
intint intint
bigintbigint bigintbigint
booleanoboolean bitbit
doubledouble flutuantefloat
flutuantefloat realreal
doubledouble moneymoney
doubledouble smallmoneysmallmoney
stringstring ncharnchar
stringstring nvarcharnvarchar
stringstring charchar
stringstring varcharvarchar
bináriobinary bináriobinary
bináriobinary varbinaryvarbinary
timestamptimestamp datadate
timestamptimestamp smalldatetimesmalldatetime
timestamptimestamp datetime2datetime2
timestamptimestamp datetimedatetime
timestamptimestamp tempo realtime
datadate datadate
Decimaldecimal Decimaldecimal

Para obter um exemplo de criação de objetos externos, consulte a etapa Criar tabelas externas no tutorial de carregamento.For an example of creating external objects, see the Create external tables step in the loading tutorial.

Arquivos de texto formatadosFormat text files

Se você estiver usando o polybase, os objetos externos definidos precisarão alinhar as linhas dos arquivos de texto com a tabela externa e a definição de formato de arquivo.If you are using PolyBase, the external objects defined need to align the rows of the text files with the external table and file format definition. Os dados em cada linha do arquivo de texto devem se alinhar com a definição da tabela.The data in each row of the text file must align with the table definition. Para formatar os arquivos de texto:To format the text files:

  • Se os seus dados forem provenientes de uma fonte não relacional, você precisa transformá-los em linhas e colunas.If your data is coming from a non-relational source, you need to transform it into rows and columns. 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.Whether the data is from a relational or non-relational source, the data must be transformed to align with the column definitions for the table into which you plan to load the data.
  • Formatar dados no arquivo de texto para se alinharem aos tipos de dados e colunas na tabela de destino do SQL Data Warehouse.Format data in the text file to align with the columns and data types in the SQL Data Warehouse destination table. 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.Misalignment between data types in the external text files and the data warehouse table causes rows to be rejected during the load.
  • Separar os campos no arquivo de texto com um terminador.Separate fields in the text file with a terminator. Certifique-se de usar um caractere ou uma sequência de caracteres que não sejam encontrados na fonte de dados.Be sure to use a character or a character sequence that is not found in your source data. Use o terminador especificado com CRIAR FORMATO DE ARQUIVO EXTERNO.Use the terminator you specified with CREATE EXTERNAL FILE FORMAT.

4. carregar os dados em SQL Data Warehouse tabelas de preparo usando o polybase ou a instrução de cópia4. Load the data into SQL Data Warehouse staging tables using PolyBase or the COPY statement

É uma melhor prática carregar dados em uma tabela de preparo.It is best practice to load data into a staging table. Tabelas de preparo permitem manipular erros sem interferir nas tabelas de produção.Staging tables allow you to handle errors without interfering with the production tables. Uma tabela de preparo também oferece a oportunidade de usar o MPP do SQL Data Warehouse para transformações de dados antes da inserção deles em tabelas de produção.A staging table also gives you the opportunity to use SQL Data Warehouse MPP for data transformations before inserting the data into production tables. A tabela precisará ser criada previamente ao carregar em uma tabela de preparo com cópia.The table will need to be pre-created when loading into a staging table with COPY.

Opções para carregamento com o polybase e a instrução de cópiaOptions for loading with PolyBase and COPY statement

Para carregar dados com o PolyBase, é possível usar qualquer uma destas opções de carregamento:To load data with PolyBase, you can use any of these loading options:

  • 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.PolyBase with T-SQL works well when your data is in Azure Blob storage or Azure Data Lake Store. Ele oferece mais controle sobre o processo de carregamento, mas também exige que você defina objetos de dados externos.It gives you the most control over the loading process, but also requires you to define external data objects. Os outros métodos definem esses objetos em segundo plano, como mapear as tabelas de origem para as tabelas de destino.The other methods define these objects behind the scenes as you map source tables to destination tables. Para coordenar as cargas de T-SQL, você pode usar o Azure Data Factory, SSIS ou as funções do Azure.To orchestrate T-SQL loads, you can use Azure Data Factory, SSIS, or Azure functions.
  • O PolyBase com o SSIS funciona bem quando os seus dados de origem estiverem no SQL Server, no SQL Server local ou na nuvem.PolyBase with SSIS works well when your source data is in SQL Server, either SQL Server on-premises or in the cloud. O SSIS define a origem para mapeamentos de tabela de destino e também coordena a carga.SSIS defines the source to destination table mappings, and also orchestrates the load. Se você já tiver pacotes SSIS, você pode modificar os pacotes para trabalhar com o novo destino do data warehouse.If you already have SSIS packages, you can modify the packages to work with the new data warehouse destination.
  • O polybase e a instrução de cópia com Azure data Factory (ADF) é outra ferramenta de orquestração.PolyBase and COPY statement with Azure Data Factory (ADF) is another orchestration tool. Ele define um pipeline e agenda de trabalhos.It defines a pipeline and schedules jobs.
  • O polybase com Azure Databricks transfere dados de uma tabela SQL data warehouse para um dataframe do databricks e/ou grava dados de um dataframe do databricks em uma tabela SQL data warehouse usando o polybase.PolyBase with Azure Databricks transfers data from a SQL Data Warehouse table to a Databricks dataframe and/or writes data from a Databricks dataframe to a SQL Data Warehouse table using PolyBase.

Outras opções de carregamentoOther loading options

Além do polybase e da instrução COPY, você pode usar o bcp ou a API SQLBulkCopy.In addition to PolyBase and the COPY statement, you can use bcp or the SQLBulkCopy API. O bcp carrega diretamente para o SQL Data Warehouse sem passar pelo armazenamento de Blobs do Azure e é destinado somente para pequenas cargas.bcp loads directly to SQL Data Warehouse without going through Azure Blob storage, and is intended only for small loads. Observe que o desempenho de carga dessas opções é mais lento do que o polybase e a instrução de cópia.Note, the load performance of these options is slower than PolyBase and the COPY statement.

5. transformar os dados5. Transform the data

Enquanto os dados estão na tabela de preparo, execute as transformações que a sua carga de trabalho exige.While data is in the staging table, perform transformations that your workload requires. Em seguida, mova os dados para uma tabela de produção.Then move the data into a production table.

6. inserir os dados em tabelas de produção6. Insert the data into production tables

A instrução INSERT INTO... A instrução SELECT move os dados da tabela de preparo para a tabela permanente.The INSERT INTO ... SELECT statement moves the data from the staging table to the permanent table.

Ao criar um processo de ETL, tente executar o processo em uma amostra de teste pequena.As you design an ETL process, try running the process on a small test sample. 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.Try extracting 1000 rows from the table to a file, move it to Azure, and then try loading it into a staging table.

Soluções de carregamento de parceirosPartner loading solutions

Muitos de nossos parceiros têm soluções de carregamento.Many of our partners have loading solutions. Para saber mais, consulte uma lista dos nossos parceiros de solução.To find out more, see a list of our solution partners.

Próximas etapasNext steps

Para orientações sobre carregamento, consulte as Diretrizes para carregar dados.For loading guidance, see Guidance for load data.