Melhores práticas para carregar dados para um conjunto de SQL dedicado no Azure Synapse Analytics

  • Artigo

Neste artigo, 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 conjunto de SQL dedicado.

Ao exportar dados para um Formato de Ficheiro ORC, poderá obter erros de memória esgotada Java quando existem colunas de texto grandes. Para contornar esta limitação, exporte apenas um subconjunto de colunas.

O PolyBase não consegue carregar linhas com mais de 1000 000 bytes de dados. Quando colocar dados nos ficheiros de texto no armazenamento de Blobs do Azure ou no Azure Data Lake Store, aqueles têm de ter menos de 1 000 000 bytes. Esta limitação de bytes acontece independentemente do esquema de tabela.

Todos os formatos de ficheiro têm características de desempenho diferentes. Para a carga mais rápida, utilize ficheiros de texto delimitados e comprimidos. A diferença entre o desempenho de UTF-8 e UTF-16 é mínima.

Divida grandes ficheiros comprimidos em ficheiros mais pequenos comprimidos.

Executar cargas com computação suficiente

Para a velocidade de carregamento mais rápida, execute apenas uma tarefa de carregamento de cada vez. Se não for viável, execute um número mínimo de cargas em simultâneo. Se espera uma tarefa de carregamento grande, considere aumentar verticalmente o conjunto de SQL dedicado antes da carga.

Para executar cargas com recursos de computação adequados, crie utilizadores de carregamento designados para a execução de cargas. Atribua cada utilizador de carregamento a uma classe de recursos ou grupo de carga de trabalho específico. Para executar uma carga, inicie sessão como um dos utilizadores que estão a carregar e, em seguida, execute a carga. A carga é executada com a classe de recursos do utilizador. Este método é mais simples do que tentar alterar a classe de recursos de um utilizador para que se ajuste à necessidade da classe de recursos atual.

Criar um utilizador de carregamento

Este exemplo cria um utilizador de carregamento classificado para um grupo de carga de trabalho específico. O primeiro passo é ligar ao master e criar um início de sessão.

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

Ligue-se ao conjunto de SQL dedicado e crie um utilizador. O código seguinte pressupõe que está ligado à base de dados denominada mySampleDataWarehouse. Mostra como criar um utilizador chamado carregador e dá ao utilizador permissões para criar tabelas e carregar com a instrução COPY. Em seguida, classifica o utilizador para o grupo de cargas de trabalho 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 alocar 100% de recursos do conjunto de SQL a uma única carga. Isto irá dar-lhe uma simultaneidade máxima de 1. Tenha em atenção que esta ação deve ser utilizada apenas para a carga inicial, onde terá de criar grupos de cargas de trabalho adicionais com as suas próprias configurações para equilibrar recursos nas cargas de trabalho.

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

Permitir o carregamento de vários utilizadores

Muitas vezes, é necessário ter vários utilizadores a realizar carregamentos para um armazém de dados. Carregar com a tabela CREATE TABLE AS SELECT (Transact-SQL) requer permissões de CONTROLO da base de dados. A permissão de CONTROL permite controlar o acesso a todos os esquemas. Poderá não querer que todos os utilizadores de carregamento tenham acesso de controlo em todos os esquemas. Para limitar as permissões, utilize a instrução DENY CONTROL.

Por exemplo, considere os esquemas de bases de dados schema_A, para departamento A, e schema_B, para departamento B. Permita que os utilizadores user_A e user_B sejam utilizadores do carregamento PolyBase nos departamentos A e B, respetivamente. Foram concedidas a ambos permissões de base de dados CONTROL. Agora, os criadores dos esquemas A e B bloqueiam os esquemas com 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 teste

Para alcançar a velocidade de carregamento mais rápida para mover dados para uma tabela de armazém de dados, carregue dados para uma tabela de testes. Definir a tabela de testes como uma área dinâmica para dados e utilizar o round-robin como opção de distribuição.

Considere que o carregamento é, normalmente, um processo de dois passos no qual pode carregar primeiro para uma tabela de teste e, em seguida, inserir os dados numa tabela de armazém de dados de produção. Se a tabela de produção utiliza uma distribuição hash, o tempo total para carregar e inserir poderá ser mais rápido, se definir a tabela de testes com a distribuição hash. O carregamento para a tabela de teste demora mais tempo, mas o segundo passo de inserção das linhas na tabela de produção não implica o movimento de dados entre as distribuições.

Carregar para um índice columnstore

Os índices columnstore exigem grandes quantidades de memória para comprimir os dados em grupos de linhas de elevada qualidade. Para a melhor eficiência em termos de compressão e indexação, o índice columnstore tem de comprimir um máximo de 1 048 576 linhas em cada rowgroup. Quando existe pressão de memória, o índice columnstore poderá não conseguir alcançar as velocidades de compressão máxima. Isto afeta o desempenho das consultas. Para obter uma descrição aprofundada, veja Columnstore memory optimizations (Otimizações de memória de columnstore).

  • Para garantir que o utilizador de carregamento tem memória suficiente para alcançar as velocidades de compressão máximas, utilize utilizadores de carregamento que sejam membros de uma classe de recursos média ou grande.
  • Carregue linhas suficientes para preencher completamente rowgroups novos. Durante um carregamento em massa, cada 1.048.576 linhas são comprimidas diretamente no columnstore como um grupo de linhas completo. Carregamentos com menos de 102,400 linhas enviam as linhas para o deltastore onde as linhas são guardadas num índice de árvore b. Se carregar muito poucas linhas, podem ir todas para o deltastore e não ser comprimidas de imediato no formato columnstore.

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

O carregamento com a instrução COPY proporcionará o débito mais elevado com conjuntos de SQL dedicados. Se não conseguir utilizar o COPY para carregar e tiver de utilizar a API sqLBulkCopy ou o bcp, deve considerar aumentar o tamanho do lote para um melhor débito.

Dica

Um tamanho de lote entre 100 K e 1 M é a linha de base recomendada para determinar a capacidade de tamanho de lote ideal.

Gerir falhas de carregamento

Uma carga que utilize uma tabela externa pode falhar com o erro "Query aborted-- the maximum reject threshold was reached while reading from an external source" (“Consulta abortada. O limiar de rejeição máximo foi atingido ao ler a partir de uma origem externa”). Esta mensagem indica que os dados externos contém registos desatualizados. Os registos de dados são considerados desatualizados se os tipos de dados e 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 ficheiro externo especificado.

Para corrigir os registos desatualizados, confirme que as definições de tabela externa e de formato de ficheiro externo estão corretas e que os dados externos estão em conformidade com estas definições. Caso um subconjunto de registos de dados externos esteja danificado, pode optar por rejeitar estes registos para as suas consultas ao utilizar as opções de rejeição em "CRIAR TABELA EXTERNA" .

Inserir dados numa tabela de produção

Um carregamento único para uma pequena tabela com uma instrução INSERT ou mesmo um recarregamento periódico de uma consulta pode ajustar-se às suas necessidades 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 como executar um carregamento em massa.

Se tiver milhares ou mais de inserções individuais durante o dia, junte-as para poder carregá-las em massa. Desenvolva os seus processos de modo a que anexem as inserções individuais a um ficheiro e crie outro processo que o carregue periodicamente.

Criar estatísticas após o carregamento

Para melhorar o desempenho das consultas, é importante criar estatísticas em todas as colunas de todas as tabelas após o primeiro carregamento ou ocorrem alterações importantes nos dados. A criação de estatísticas pode ser feita manualmente ou pode ativar as estatísticas de criação automática.

Para uma explicação detalhada das estatísticas, consulte Estatísticas. O exemplo seguinte 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]);

Alternar chaves de armazenamento

É boa prática de segurança alterar a chave de acesso ao seu armazenamento de blobs regularmente. Tem duas chaves de armazenamento para a conta de armazenamento de blobs, que lhe permite efetuar a transição das chaves.

Para alternar as chaves da conta de Armazenamento do Azure:

Para cada conta de armazenamento cuja chave mudou, emita ALTER DATABASE SCOPED CREDENTIAL.

Exemplo:

A chave original é criada

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

Efetue a rotação de chaves da chave 1 para a chave 2

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

Não é preciso fazer outras alterações às origens de dados externas subjacentes.

Passos seguintes