Práticas recomendadas para carregar dados no SQL Data Warehouse do AzureBest practices for loading data into Azure SQL Data Warehouse

Recomendações e otimizações de desempenho para carregar dados no SQL Data Warehouse do Azure.Recommendations and performance optimizations for loading data into Azure SQL Data Warehouse.

Preparando dados no Armazenamento do AzurePreparing data in Azure Storage

Para minimizar a latência, colocalize sua camada de armazenamento e o data warehouse.To minimize latency, co-locate your storage layer and your data warehouse.

Ao exportar dados em um formato de arquivo ORC, você poderá receber erros de falta de memória Java quando houver colunas de texto grandes.When exporting data into an ORC File Format, you might get Java out-of-memory errors when there are large text columns. Para contornar essa limitação, exporte apenas um subconjunto das colunas.To work around this limitation, export only a subset of the columns.

O PolyBase não consegue carregar linhas que tenham mais de 1.000.000 de bytes de dados.PolyBase cannot load rows that have more than 1,000,000 bytes of data. Ao colocar dados nos arquivos de texto no armazenamento de blob do Azure ou do Azure Data Lake Store, eles devem ter menos de 1.000.000 de bytes de dados.When you put data into the text files in Azure Blob storage or Azure Data Lake Store, they must have fewer than 1,000,000 bytes of data. Essa limitação de bytes é verdadeira, independentemente do esquema de tabela.This byte limitation is true regardless of the table schema.

Todos os formatos de arquivo têm características diferentes de desempenho.All file formats have different performance characteristics. Para o carregamento mais rápido, use arquivos de texto delimitados compactados.For the fastest load, use compressed delimited text files. A diferença entre o desempenho de UTF-16 e UTF-8 é mínima.The difference between UTF-8 and UTF-16 performance is minimal.

Dividir arquivos compactados grandes em arquivos compactados menores.Split large compressed files into smaller compressed files.

Executando carregamentos com computação suficienteRunning loads with enough compute

Para uma velocidade mais alta de carregamento, execute apenas uma carga de trabalho por vez.For fastest loading speed, run only one load job at a time. Se isso não for possível, execute uma quantidade mínima de carregamento simultaneamente.If that is not feasible, run a minimal number of loads concurrently. Caso esteja esperando um grande trabalho de carregamento, considere a possibilidade de dimensionar seu data warehouse antes do carregamento.If you expect a large loading job, consider scaling up your data warehouse before the load.

Para executar cargas com recursos de computação apropriados, crie usuários de carregamento designados para executar cargas.To run loads with appropriate compute resources, create loading users designated for running loads. Atribua cada usuário de carregamento para uma classe de recurso específica.Assign each loading user to a specific resource class. Para executar uma carga, entre como um dos usuários de carregamento e, em seguida, execute a carga.To run a load, sign in as one of the loading users, and then run the load. A carga é executada com a classe de recurso do usuário.The load runs with the user's resource class. Esse método é mais simples do que tentar alterar a classe de recurso do usuário para se ajustar à necessidade de classe de recurso atual.This method is simpler than trying to change a user's resource class to fit the current resource class need.

Exemplo de como criar um usuário de carregamentoExample of creating a loading user

Este exemplo cria um usuário de carregamento para a classe de recurso staticrc20.This example creates a loading user for the staticrc20 resource class. A primeira etapa é conectar-se ao mestre e criar um logon.The first step is to connect to master and create a login.

   -- Connect to master
   CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';

Conecte-se ao data warehouse e crie um usuário.Connect to the data warehouse and create a user. O código a seguir pressupõe que você esteja conectado ao banco de dados chamado mySampleDataWarehouse.The following code assumes you are connected to the database called mySampleDataWarehouse. Ele mostra como criar um usuário chamado LoaderRC20 e conceder ao usuário a permissão de controle em um banco de dados.It shows how to create a user called LoaderRC20, give the user control permission on a database. Depois, ele adiciona o usuário como membro da função de banco de dados staticrc20.It then adds the user as a member of the staticrc20 database role.

   -- Connect to the database
   CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
   GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
   EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';

Para executar uma carga com recursos para as classes de recursos staticRC20, entre como LoaderRC20 e execute a carga.To run a load with resources for the staticRC20 resource classes, sign in as LoaderRC20 and run the load.

Execute cargas sob classes de recursos estáticas em vez de dinâmicas.Run loads under static rather than dynamic resource classes. Usar as classes de recursos estáticas garante os mesmos recursos, independentemente de suas unidades de data warehouse.Using the static resource classes guarantees the same resources regardless of your data warehouse units. Se você usar uma classe de recursos dinâmicos, os recursos variam de acordo com seu nível de serviço.If you use a dynamic resource class, the resources vary according to your service level. Para classes dinâmicas, um nível inferior do serviço significa que você provavelmente precisa usar uma classe de recursos maior para seu usuário de carregamento.For dynamic classes, a lower service level means you probably need to use a larger resource class for your loading user.

Permitindo que vários usuários façam o carregamentoAllowing multiple users to load

Geralmente, há a necessidade de ter vários usuários carregando dados em um data warehouse.There is often a need to have multiple users load data into a data warehouse. Carregar com CREATE TABLE AS SELECT (Transact-SQL) requer permissões CONTROL do banco de dados.Loading with the CREATE TABLE AS SELECT (Transact-SQL) requires CONTROL permissions of the database. A permissão CONTROL dá acesso de controle para todos os esquemas.The CONTROL permission gives control access to all schemas. Talvez você não queira que todos os usuários de carregamento tenham acesso de controle em todos os esquemas.You might not want all loading users to have control access on all schemas. Para limitar as permissões, use a instrução DENY CONTROL.To limit permissions, use the DENY CONTROL statement.

Por exemplo: considere os esquemas de banco de dados, schema_A para o departamento A e schema_B para o departamento B. Os usuários de banco de dados user_A e user_B serão usuários de carregamento de PolyBase nos departamentos A e B respectivamente.For example, consider database schemas, schema_A for dept A, and schema_B for dept B. Let database users user_A and user_B be users for PolyBase loading in dept A and B, respectively. Ambos receberam permissões de banco de dados CONTROL.They both have been granted CONTROL database permissions. Os criadores dos esquemas A e B agora bloquearam seus esquemas usando DENY:The creators of schema A and B now lock down their schemas using DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

Agora os user_A e user_B são bloqueados do esquema do outro departamento.User_A and user_B are now locked out from the other dept’s schema.

Carregando uma tabela de preparoLoading to a staging table

Para obter a velocidade de carregamento mais rápida para a movimentação de dados em uma tabela de data warehouse, carregue dados em uma tabela de preparo.To achieve the fastest loading speed for moving data into a data warehouse table, load data into a staging table. Defina a tabela de preparo como um heap e use o round-robin para a opção de distribuição.Define the staging table as a heap and use round-robin for the distribution option.

Leve em consideração que o carregamento é geralmente um processo de duas etapas, no qual primeiro você faz o carregamento para uma tabela de preparo e depois insere os dados em uma tabela de data warehouse de produção.Consider that loading is usually a two-step process in which you first load to a staging table and then insert the data into a production data warehouse table. Caso a tabela de produção use uma distribuição hash, o tempo total para carregar e inserir pode ser mais rápido se você definir a tabela de preparo com a distribuição de hash.If the production table uses a hash distribution, the total time to load and insert might be faster if you define the staging table with the hash distribution. Carregar para a tabela de preparo demora mais, mas a segunda etapa de inserção das linhas na tabela de produção não incorrerá em movimentação de dados entre as distribuições.Loading to the staging table takes longer, but the second step of inserting the rows to the production table does not incur data movement across the distributions.

Carregando para um índice columnstoreLoading to a columnstore index

Os índices columnstore exigem grandes quantidades de memória para compactar dados em rowgroups de alta qualidade.Columnstore indexes require large amounts of memory to compress data into high-quality rowgroups. Para obter a melhor compactação e eficiência de índice, o índice columnstore precisa compactar um máximo de 1.048.576 linhas em cada rowgroup.For best compression and index efficiency, the columnstore index needs to compress the maximum of 1,048,576 rows into each rowgroup. Quando há pressão de memória, o índice columnstore pode não ser capaz de alcançar as taxas máximas de compactação.When there is memory pressure, the columnstore index might not be able to achieve maximum compression rates. Por sua vez, isso afeta o desempenho da consulta.This in turn effects query performance. Para uma análise profunda, consulte Otimizações de memória columnstore.For a deep dive, see Columnstore memory optimizations.

  • Para garantir que o usuário de carregamento tenha memória suficiente para alcançar as taxas máximas de compactação, use usuários de carregamento que sejam membros de uma classe de recursos média ou grande.To ensure the loading user has enough memory to achieve maximum compression rates, use loading users that are a member of a medium or large resource class.
  • Carregue linhas suficientes para preencher completamente novos rowgroups.Load enough rows to completely fill new rowgroups. Durante um carregamento em massa, a cada 1.048.576 linhas são compactadas diretamente para o columnstore como um rowgroup completo.During a bulk load, every 1,048,576 rows get compressed directly into the columnstore as a full rowgroup. Carregamentos com menos de 102.400 linhas enviam as linhas para o deltastore, onde as linhas são mantidas em um índice de árvore b.Loads with fewer than 102,400 rows send the rows to the deltastore where rows are held in a b-tree index. Se você carregar um número muito pequeno de linhas, elas podem ir todas para o deltastore e não serem compactadas imediatamente no formato de columnstore.If you load too few rows, they might all go to the deltastore and not get compressed immediately into columnstore format.

Aumentar o tamanho do lote ao usar a API SQLBulkCopy ou o BCPIncrease batch size when using SQLBulkCopy API or BCP

Como mencionado anteriormente, o carregamento com o polybase fornecerá a maior taxa de transferência com SQL Data Warehouse.As mentioned before, loading with PolyBase will provide the highest throughput with SQL Data Warehouse. Se você não pode usar o polybase para carregar e deve usar a API SQLBulkCopy (ou BCP), considere aumentar o tamanho do lote para obter uma melhor taxa de transferência.If you cannot use PolyBase to load and must use the SQLBulkCopy API (or BCP) you should consider increasing batch size for better throughput.

Tratamento de falhas de carregamentoHandling loading failures

Um carregamento usando uma tabela externa pode falhar com o erro “Consulta anulada – o limite de rejeição máximo foi atingido durante a leitura de uma fonte externa” .A load using an external table can fail with the error "Query aborted-- the maximum reject threshold was reached while reading from an external source". Essa mensagem indica que os dados externos contêm registros sujos.This message indicates that your external data contains dirty records. Um registro de dados é considerado sujo se os tipos de dados e o número de colunas não correspondem às definições de coluna da tabela externa ou se os dados não são compatíveis com o formato de arquivo externo especificado.A data record is considered dirty if the data types and number of columns do not match the column definitions of the external table, or if the data doesn't conform to the specified external file format.

Para corrigir os registros sujos, verifique se a tabela externa e as definições de formato de arquivo externo estão corretas e se os dados externos são compatíveis com essas definições.To fix the dirty records, ensure that your external table and external file format definitions are correct and your external data conforms to these definitions. Caso um subconjunto de registros de dados externos esteja sujo, é possível rejeitar esses registros para suas consultas usando as opções de rejeição em CREATE EXTERNAL TABLE.In case a subset of external data records are dirty, you can choose to reject these records for your queries by using the reject options in CREATE EXTERNAL TABLE.

Inserindo dados em uma tabela de produçãoInserting data into a production table

Uma única carga para uma pequena tabela com uma instrução INSERT, ou mesmo uma recarga periódica de uma pesquisa pode ser boa o suficiente com uma instrução como INSERT INTO MyLookup VALUES (1, 'Type 1').A one-time load to a small table with an INSERT statement, or even a periodic reload of a look-up might perform good enough with a statement like INSERT INTO MyLookup VALUES (1, 'Type 1'). Porém, inserções únicas de toneladas não são tão eficientes como executar um carregamento em massa.However, singleton inserts are not as efficient as performing a bulk load.

Se você tiver milhares ou mais de inserções únicas ao longo do dia, crie lotes para as inserções para que possa carregá-las em massa.If you have thousands or more single inserts throughout the day, batch the inserts so you can bulk load them. Desenvolva seus processos para acrescentar as inserções únicas para um arquivo e depois crie outro processo que periodicamente carrega o arquivo.Develop your processes to append the single inserts to a file, and then create another process that periodically loads the file.

Criando estatísticas após o carregamentoCreating statistics after the load

Para melhorar o desempenho de suas consultas, é importante que as estatísticas sejam criadas em todas as colunas de todas as tabelas após o primeiro carregamento ou ocorrem alterações significativas nos dados.To improve query performance, it's important to create statistics on all columns of all tables after the first load, or substantial changes occur in the data. Isso pode ser feito manualmente ou você pode habilitar Estatísticas de criação automática.This can be done manually or you can enable auto-create statistics.

Para obter uma explicação detalhada das estatísticas, confira Estatísticas.For a detailed explanation of statistics, see Statistics. O exemplo a seguir mostra como criar estatísticas manualmente em cinco colunas da tabela Customer_Speed.The following example shows how to manually create statistics on five columns of the Customer_Speed table.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Girar as chaves de armazenamentoRotate storage keys

É uma boa prática de segurança alterar a chave de acesso para seu armazenamento de blob regularmente.It is good security practice to change the access key to your blob storage on a regular basis. Você tem duas chaves de armazenamento para sua conta de armazenamento de blob, o que permite a transição das chaves.You have two storage keys for your blob storage account, which enables you to transition the keys.

Para girar chaves de conta de armazenamento do Azure:To rotate Azure Storage account keys:

Para cada conta de armazenamento cuja chave foi alterada, execute ALTER DATABASE SCOPED CREDENTIAL.For each storage account whose key has changed, issue ALTER DATABASE SCOPED CREDENTIAL.

Exemplo:Example:

A chave original é criadaOriginal key is created

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Altere a chave 1 para a chave 2Rotate key from key 1 to key 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2' 

Não é necessária nenhuma outra alteração nas fontes de dados externas subjacentes.No other changes to underlying external data sources are needed.

Próximas etapasNext steps