Práticas recomendadas para carregar dados em um pool de SQL dedicado no Azure Synapse Analytics

  • Artigo

Neste artigo, você encontrará recomendações e otimizações de desempenho para carregar dados.

Preparar dados no Armazenamento do Azure

Para minimizar a latência, coloque a camada de armazenamento e o pool de SQL dedicado.

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. Para contornar essa limitação, exporte apenas um subconjunto das colunas.

O PolyBase não consegue carregar linhas que tenham mais de 1.000.000 de bytes de dados. 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. Essa limitação de bytes é verdadeira, independentemente do esquema de tabela.

Todos os formatos de arquivo têm características diferentes de desempenho. Para o carregamento mais rápido, use arquivos de texto delimitados compactados. A diferença entre o desempenho de UTF-16 e UTF-8 é mínima.

Dividir arquivos compactados grandes em arquivos compactados menores.

Executar carregamentos com computação suficiente

Para uma velocidade mais alta de carregamento, execute apenas uma carga de trabalho por vez. Se isso não for possível, execute uma quantidade mínima de carregamento simultaneamente. Caso esteja esperando um grande trabalho de carregamento, considere a possibilidade de dimensionar o pool de SQL dedicado antes do carregamento.

Para executar cargas com recursos de computação apropriados, crie usuários de carregamento designados para executar cargas. Atribua cada usuário de carregamento a uma classe de recurso ou um grupo de carga de trabalho. Para executar uma carga, entre como um dos usuários de carregamento e execute a carga. A carga é executada com a classe de recurso do usuário. 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.

Criar um usuário de carregamento

Este exemplo cria um usuário de carregamento classificado para um grupo de carga de trabalho específico. A primeira etapa é conectar-se ao mestre e criar um logon.

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

Conecte-se ao pool de SQL dedicado e crie um usuário. O código a seguir pressupõe que você esteja conectado ao banco de dados chamado mySampleDataWarehouse. Ele mostra como criar um usuário de nome loader e fornece permissões de usuário para criar e carregar tabelas usando a instrução COPY. Em seguida, classifica o usuário para o grupo de cargas de trabalho de carregamentos DataLoads com o máximo de recursos.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Importante

Este é um exemplo extremo de alocação de 100% dos recursos do pool de SQL para uma única carga. Ele retornará uma simultaneidade máxima de 1. Lembre-se de que ele deve ser usado apenas para a carga inicial, quando você precisará criar grupos de carga de trabalho adicionais com configurações próprias para balancear os recursos entre as cargas de trabalho.

Para executar uma carga de recursos para o grupo de carga de trabalho que estiver sendo carregado, entre como loader e execute a carga.

Permitir que vários usuários façam o carregamento

Geralmente, há a necessidade de ter vários usuários carregando dados em um data warehouse. Carregar com CREATE TABLE AS SELECT (Transact-SQL) requer permissões CONTROL do banco de dados. A permissão CONTROL dá acesso de controle para todos os esquemas. Talvez você não queira que todos os usuários de carregamento tenham acesso de controle em todos os esquemas. Para limitar as permissões, use a instrução DENY CONTROL.

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. Ambos receberam permissões de banco de dados CONTROL. Os criadores dos esquemas A e B agora bloquearam seus esquemas usando DENY:

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

Agora user_A e user_B são bloqueados do esquema do outro departamento.

Carregar para uma tabela de preparo

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. Defina a tabela de preparo como um heap e use o round-robin para a opção de distribuição.

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. 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. 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.

Carregar em um índice columnstore

Os índices columnstore exigem grandes quantidades de memória para compactar dados em rowgroups de alta qualidade. 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. 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. Isso afeta o desempenho da consulta. Para uma análise profunda, consulte Otimizações de memória columnstore.

  • 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.
  • Carregue linhas suficientes para preencher completamente novos rowgroups. Durante um carregamento em massa, cada 1.048.576 linhas são compactadas diretamente para o columnstore como um rowgroup completo. 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. 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.

Aumentar o tamanho do lote ao usar a API SQLBulkCopy ou o BCP

Ao carregar usando a instrução COPY, você obtém a maior taxa de transferência para pools de SQL dedicados. Se você não pode usar COPY e precisa usar a API SqLBulkCopy ou o bcp, considere aumentar o tamanho do lote para obter uma melhor taxa de transferência.

Dica

Um tamanho de lote entre 100 mil a 1 milhão de linhas é a linha de base recomendada para se determinar a capacidade de tamanho de lote ideal.

Gerenciar falhas de carregamento

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”. Essa mensagem indica que os dados externos contêm registros sujos. 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.

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. 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'.

Inserir os dados em uma tabela de produção

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'). No entanto, inserções de banco de dados individual não são tão eficientes quando o carregamento em massa.

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. Desenvolva seus processos para acrescentar as inserções únicas para um arquivo e depois crie outro processo que periodicamente carrega o arquivo.

Criar estatísticas após o carregamento

Para melhorar o desempenho das consultas, é importante criar estatísticas sobre todas as colunas de todas as tabelas após o primeiro carregamento ou quando há grandes alterações nos dados. A criação de estatísticas pode ser manual, ou você pode habilitar a criação automática.

Para obter uma explicação detalhada das estatísticas, confira Estatísticas. O exemplo a seguir mostra como criar estatísticas manualmente em cinco colunas da tabela Customer_Speed.

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 armazenamento

É uma boa prática de segurança alterar a chave de acesso para seu armazenamento de blob regularmente. Você tem duas chaves de armazenamento para sua conta de armazenamento de blob, o que permite a transição das chaves.

Para girar chaves de conta de armazenamento do Azure:

Para cada conta de armazenamento cuja chave foi alterada, execute ALTER DATABASE SCOPED CREDENTIAL.

Exemplo:

A chave original é criada

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

Altere a chave 1 para a chave 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.

Próximas etapas