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

• Iniciar uma avaliação gratuita Fabric.
• Assistente de Migração para Fabric Data Warehouse.

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 para um formato de arquivo ORC, você pode encontrar erros de memória insuficiente do Java quando existirem 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 você coloca dados em arquivos de texto no Armazenamento de Blobs do Azure ou no Azure Data Lake Store, eles devem ter menos de 1.000.000 bytes de dados. Essa limitação de bytes é verdadeira independentemente do esquema de tabela.

Todos os formatos de arquivo têm características de desempenho diferentes. Para a carga mais rápida, 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.

Executar cargas com capacidade computacional suficiente

Para a velocidade de carregamento mais rápida, execute apenas um trabalho de carga por 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 o pool de SQL dedicado antes da carga.

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 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 atender à necessidade da 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 master e criar um login.

   -- 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 chamado carregador e fornece ao usuário permissões para criar tabelas e carregar usando a instrução COPY. Em seguida, classifica o usuário 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'
   );

Para executar uma carga com recursos para o grupo de trabalho de carregamento, entre como responsável pelo carregamento e realize o carregamento.

Permitir que vários usuários carreguem

Geralmente, é necessário que vários usuários carreguem dados em um data warehouse. Carregar com o CREATE TABLE AS SELECT (Transact-SQL) requer permissões CONTROL do banco de dados. A permissão CONTROL fornece acesso de controle a 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. Que os usuários de banco de dados user_A e user_B sejam usuários para o carregamento do PolyBase nos departamentos A e B, respectivamente. Ambos receberam permissões de banco de dados CONTROL. Os criadores do esquema 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 agora estão bloqueados do esquema do outro departamento.

Carregar em uma tabela de preparo

Para obter a máxima velocidade de carregamento ao mover dados para uma tabela de armazém de dados, carregue dados em uma tabela intermediária. Defina a tabela de estágio como um heap e use round robin para a opção de distribuição.

Considere que o carregamento geralmente é um processo de duas etapas no qual você primeiro carrega em uma tabela de preparo e, em seguida, insere os dados em uma tabela de 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 você definir a tabela de estágio com a distribuição de hash. O carregamento na tabela intermediária leva mais tempo, mas a segunda etapa de inserção das linhas na tabela de produção não requer movimentação de dados entre as distribuições.

Carregar em um índice columnstore

Os índices Columnstore exigem grandes quantidades de memória para comprimir dados em rowgroups de alta qualidade. Para obter a melhor compactação e eficiência de índice, o índice columnstore precisa compactar o máximo de 1.048.576 linhas em cada grupo de linhas. Quando há pressão de memória, o índice columnstore pode não conseguir atingir os níveis máximos de compressão. Isso afeta o desempenho da consulta. Para obter um aprofundamento, consulte as otimizações de memória columnstore.

• Para garantir que o usuário de carregamento tenha memória suficiente para atingir as taxas máximas de compactação, utilize usuários de carregamento que sejam membros de uma classe de recursos média ou grande.
• Carregue linhas suficientes para preencher totalmente os novos rowgroups. Durante uma carga em massa, cada 1.048.576 linha é compactada diretamente no armazenamento de colunas como um grupo de linhas completo. Cargas com menos de 102.400 linhas enviam as linhas para o deltastore, onde são mantidas em um índice de árvore B. Se você carregar poucas linhas, todas elas poderão ir para o deltastore e não serem compactadas imediatamente no formato columnstore.

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

O carregamento usando a instrução COPY proporcionará a maior taxa de transferência com pools dedicados de SQL. Se você não puder usar o COPY para carregar e precisar usar a API do SqLBulkCopy ou bcp, considere aumentar o tamanho do lote para obter uma melhor taxa de transferência.

Gerenciar falhas de carregamento

Uma carga usando uma tabela externa pode falhar com o erro "Consulta anulada– o limite máximo de rejeição foi atingido durante a leitura de uma fonte externa". Esta mensagem indica que seus dados externos contêm registros sujos. Um registro de dados será 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 formato de arquivo externo e de tabela externa estão corretas e seus 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

Uma carga única 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 executar uma carga em massa.

Se você tiver milhares ou mais inserções simples ao longo do dia, coloque em lote as inserções para poder carregá-las em massa. Desenvolva seus processos para acrescentar as inserções simples a um arquivo e, em seguida, crie outro processo que carregue periodicamente o arquivo.

Criar estatísticas após a carga

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

Para obter uma explicação detalhada das estatísticas, consulte 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 chaves de armazenamento

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

Para rotacionar as chaves da conta do Armazenamento do Azure:

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

Exemplo:

A chave original é criada

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

Girar a chave da chave 1 para a chave 2

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

Nenhuma outra alteração nas fontes de dados externas subjacentes é necessária.

Conteúdo relacionado

• Para saber mais sobre o PolyBase e a criação de um processo de Extração, Carregamento e Transformação (ELT), consulte Design ELT para Azure Synapse Analytics.
• Para um tutorial de carregamento, Use o PolyBase para carregar dados do armazenamento de blobs Azure para Azure Synapse Analytics.
• Para monitorar cargas de dados, consulte Monitorar sua carga de trabalho usando DMVs.