Partilhar via

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

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

Ao exportar dados para um formato de arquivo ORC, você pode obter erros de falta de memória Java quando há colunas de texto grandes. Para contornar essa limitação, exporte apenas um subconjunto das colunas.

O PolyBase não pode carregar linhas com mais de 1.000.000 bytes de dados. Quando coloca dados nos ficheiros de texto no armazenamento de Blob do Azure ou no Azure Data Lake Store, estes devem ter menos de 1.000.000 bytes de dados. Essa limitação de bytes é verdadeira independentemente do esquema da tabela.

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

Divida arquivos compactados grandes em arquivos compactados menores.

Execute tarefas com computação suficiente

Para uma velocidade de carregamento mais rápida, execute apenas um trabalho de carga de cada vez. Se isso não for viável, execute um número mínimo de cargas simultaneamente. Se você espera um trabalho de carregamento grande, considere dimensionar seu pool 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 grupo de carga de trabalho específico. Para executar uma carga, entre como um dos usuários de carregamento e, em seguida, 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 de um usuário para se adequar à necessidade atual da classe de recurso.

Criar um usuário de carregamento

Este exemplo cria um usuário de carregamento classificado para um grupo de carga de trabalho específico. O primeiro passo é conectar-se ao mestre e criar um login.

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

Conecte-se ao pool 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 chamado loader e dá ao usuário permissões para criar tabelas e carregar usando a instrução COPY. Em seguida, ele classifica o usuário para o grupo de carga de trabalho DataLoads com recursos máximos.

   -- 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 recursos% do pool SQL para uma única carga. Isso lhe dará uma concorrência máxima de 1. Lembre-se de que isso deve ser usado apenas para a carga inicial, onde você precisará criar outros grupos de carga de trabalho com suas próprias configurações para equilibrar os recursos entre suas cargas de trabalho.

Para executar uma carga com recursos para o grupo de trabalho de carregamento, inicie sessão como carregador e execute a carga.

Permitir que vários usuários carreguem

Muitas vezes, é necessário que vários usuários carreguem dados em um data warehouse. Carregar com CREATE TABLE AS SELECT (Transact-SQL) requer permissões CONTROL da base de dados. A permissão CONTROL dá acesso de controle a todos os esquemas. Talvez você não queira que todos os usuários que carregam tenham acesso de controle em todos os esquemas. Para limitar as permissões, use a instrução DENY CONTROL.

Por exemplo, considere esquemas de banco de dados, schema_A para o dept A e schema_B para o dept B. Permita que os usuários do banco de dados user_A e user_B sejam usuários para carregamento do PolyBase no dept A e B, respectivamente. Ambos receberam permissões de banco de dados CONTROL. Os criadores dos esquemas A e B agora bloqueiam seus esquemas usando DENY:

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

User_A e user_B estão agora bloqueados do esquema do outro departamento.

Carregar para uma tabela de preparo

Para obter a velocidade de carregamento mais rápida ao mover dados para uma tabela de data warehouse, carregue os dados numa tabela de preparação. Defina a tabela de encenação como um heap e use round-robin para a opção de distribuição.

Considere que o carregamento geralmente é um processo de duas etapas em que se carrega primeiro numa tabela de estágio e, em seguida, se insere os dados num armazém de dados de produção. Se a tabela de produção usar uma distribuição de hash, o tempo total para carregar e inserir poderá ser mais rápido se definir a tabela de preparação com a distribuição de hash. O carregamento na tabela de preparo leva mais tempo, mas a segunda etapa de inserir as linhas na tabela de produção não incorre em movimentação de dados entre as distribuições.

Carregar para um índice columnstore

Os índices Columnstore exigem grandes quantidades de memória para compactar dados em grupos de linhas de alta qualidade. Para melhor compactação e eficiência de índice, o índice columnstore precisa compactar até 1.048.576 linhas em cada grupo de linhas. Quando há pressão de memória, o índice columnstore pode não conseguir alcançar os níveis máximos de compressão. Isso afeta o desempenho da consulta. Para uma análise detalhada, consulte as Otimizações de memória do Columnstore.

  • Para garantir que o usuário de carregamento tenha memória suficiente para atingir 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 grupos de linhas. Durante uma carga em massa, 1.048.576 linhas são compactadas diretamente no columnstore formando um grupo de linhas completo. Cargas com menos de 102.400 linhas enviam as linhas para o deltastore, onde as linhas são mantidas em um índice b-tree. Se você carregar poucas linhas, todas elas podem ir para o deltastore e não serem compactadas imediatamente no formato columnstore.

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

O carregamento com a instrução COPY fornecerá a taxa de transferência mais alta com pools SQL dedicados. Se você não pode usar o COPY para carregar e deve usar a API SqLBulkCopy ou bcp, você deve considerar aumentar o tamanho do lote para uma melhor taxa de transferência.

Sugestão

Um tamanho de lote entre 100 K a 1 M linhas é a linha de base recomendada para determinar a capacidade de tamanho de lote ótima.

Gerenciar falhas de carregamento

Uma carga usando uma tabela externa pode falhar com o erro "Consulta abortada - o limite máximo de rejeição foi atingido durante a leitura de uma fonte externa". Esta mensagem indica que os seus dados externos contêm registos sujos. Um registro de dados é considerado sujo se os tipos de dados e o número de colunas não corresponderem às definições de coluna da tabela externa ou se os dados não estiverem em conformidade com o formato de arquivo externo especificado.

Para corrigir os registros sujos, verifique se as definições de tabela externa e formato de arquivo externo estão corretas e se os dados externos estão em conformidade com essas definições. Caso um subconjunto de registros de dados externos esteja sujo, você pode optar por rejeitar esses registros para suas consultas usando as opções de rejeição em 'CREATE EXTERNAL TABLE' .

Inserir dados em uma tabela de produção

Um carregamento único para uma tabela pequena com uma instrução INSERT ou até mesmo uma recarga periódica de uma pesquisa pode ter um desempenho bom o suficiente com uma instrução como INSERT INTO MyLookup VALUES (1, 'Type 1'). No entanto, as inserções singleton não são tão eficientes quanto a execução de um carregamento em lote.

Caso tenhas milhares ou mais inserções individuais ao longo do dia, agrupa as inserções em lote para que possas carregá-las em massa. Desenvolva seus processos para anexar as inserções únicas a um arquivo e, em seguida, crie outro processo que carregue periodicamente o arquivo.

Criar estatísticas após o carregamento

Para melhorar o desempenho da consulta, é importante criar estatísticas em todas as colunas de todas as tabelas após o primeiro carregamento, ou grandes alterações ocorrem nos dados. Criar estatísticas pode ser feito manualmente ou você pode ativar a criação automática de estatísticas.

Para obter uma explicação detalhada das estatísticas, consulte Estatísticas. O exemplo a seguir mostra como criar manualmente estatísticas 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 chaves de armazenamento

É uma boa prática de segurança alterar a chave de acesso ao seu armazenamento de blob regularmente. Você tem duas chaves de armazenamento para a sua conta de armazenamento de blob, o que lhe permite alternar entre as chaves.

Para alternar chaves de contas do Azure Storage:

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'

Rodar a chave da tecla 1 para a chave 2

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

Não são necessárias outras alterações às fontes de dados externas subjacentes.

Conteúdo relacionado