Melhores práticas para o conjunto de SQL sem servidor no Azure Synapse Analytics

  • Artigo

Neste artigo, você encontrará uma coleção de práticas recomendadas para usar o pool SQL sem servidor. O pool SQL sem servidor é um recurso no Azure Synapse Analytics. Se você estiver trabalhando com um pool SQL dedicado, consulte Práticas recomendadas para pools SQL dedicados para obter orientações específicas.

O conjunto de SQL sem servidor permite-lhe consultar ficheiros nas suas contas do Armazenamento do Azure. Não tem armazenamento local nem recursos de ingestão. Todos os ficheiros que as consultas visam são externos ao conjunto de SQL sem servidor. Tudo o que está relacionado com a leitura de ficheiros do armazenamento pode afetar o desempenho da consulta.

Algumas orientações genéricas são:

  • Confirme que as aplicações cliente estão colocadas no conjunto de SQL sem servidor.
    • Se estiver a utilizar aplicações cliente fora do Azure, certifique-se de que está a utilizar um conjunto de SQL sem servidor numa região próxima do seu computador cliente. Os exemplos de aplicações cliente incluem o Power BI Desktop, o SQL Server Management Studio e o Azure Data Studio.
  • Verifique se o armazenamento e o conjunto de SQL sem servidor estão na mesma região. Os exemplos de armazenamento incluem o Azure Data Lake Storage e o Azure Cosmos DB.
  • Tente otimizar o layout de armazenamento usando particionamento e mantendo seus arquivos na faixa entre 100 MB e 10 GB.
  • Se estiver a devolver um grande número de resultados, verifique se está a utilizar o SQL Server Management Studio ou o Azure Data Studio e não o Azure Synapse Studio. O Azure Synapse Studio é uma ferramenta Web que não foi concebida para grandes conjuntos de resultados.
  • Se você estiver filtrando os resultados por coluna de cadeia de caracteres, tente usar um BIN2_UTF8 agrupamento. Para obter mais informações sobre como alterar agrupamentos, consulte Tipos de agrupamento suportados para Synapse SQL.
  • Considere colocação os resultados em cache do lado do cliente através do modo de importação do Power BI ou do Azure Analysis Services e atualize-os periodicamente. Os conjuntos de SQL sem servidor não podem proporcionar uma experiência interativa no modo Power BI Direct Query se estiver a utilizar consultas complexas ou a processar uma grande quantidade de dados.
  • A simultaneidade máxima não é limitada e depende da complexidade da consulta e da quantidade de dados examinados. Um conjunto de SQL sem servidor pode lidar simultaneamente com 1000 sessões ativas que estão a executar consultas leves. Os números cairão se as consultas forem mais complexas ou analisarem uma quantidade maior de dados, portanto, nesse caso, considere diminuir a simultaneidade e executar consultas por um período de tempo mais longo, se possível.

Aplicativos cliente e conexões de rede

Verifique se seu aplicativo cliente está conectado ao espaço de trabalho Azure Synapse mais próximo possível com a conexão ideal.

  • Coloque um aplicativo cliente com o espaço de trabalho do Azure Synapse. Se estiver a utilizar aplicações como o Power BI ou o Azure Analysis Service, certifique-se de que estão na mesma região onde colocou a sua área de trabalho do Azure Synapse. Se necessário, crie os espaços de trabalho separados que são emparelhados com seus aplicativos cliente. Colocar um aplicativo cliente e o espaço de trabalho do Azure Synapse em regiões diferentes pode causar maior latência e streaming mais lento de resultados.
  • Se você estiver lendo dados de seu aplicativo local, verifique se o espaço de trabalho do Azure Synapse está na região próxima ao seu local.
  • Certifique-se de que não tem problemas de largura de banda de rede durante a leitura de uma grande quantidade de dados.
  • Não use o Azure Synapse Studio para retornar uma grande quantidade de dados. O Azure Synapse Studio é uma ferramenta Web que utiliza o protocolo HTTPS para transferir dados. Use o Azure Data Studio ou o SQL Server Management Studio para ler uma grande quantidade de dados.

Layout de armazenamento e conteúdo

Aqui estão as práticas recomendadas para armazenamento e layout de conteúdo no pool SQL sem servidor.

Coloque seu armazenamento e pool SQL sem servidor

Para minimizar a latência, colocalize sua conta de Armazenamento do Azure ou o armazenamento analítico do Azure Cosmos DB e seu ponto de extremidade do pool SQL sem servidor. As contas de armazenamento e os pontos de extremidade provisionados durante a criação do espaço de trabalho estão localizados na mesma região.

Para um desempenho ideal, se você acessar outras contas de armazenamento com pool SQL sem servidor, verifique se elas estão na mesma região. Se eles não estiverem na mesma região, haverá maior latência para a transferência de rede dos dados entre a região remota e a região do ponto de extremidade.

Limitação do Armazenamento do Azure

Vários aplicativos e serviços podem acessar sua conta de armazenamento. A limitação de armazenamento ocorre quando a IOPS combinada ou a taxa de transferência gerada por aplicativos, serviços e cargas de trabalho de pool SQL sem servidor excede os limites da conta de armazenamento. Como resultado, você experimentará um efeito negativo significativo no desempenho da consulta.

Quando a limitação é detetada, o pool SQL sem servidor tem manipulação interna para resolvê-la. O pool SQL sem servidor faz solicitações para armazenamento em um ritmo mais lento até que a limitação seja resolvida.

Gorjeta

Para uma execução ideal da consulta, não sobrecarregue a conta de armazenamento com outras cargas de trabalho durante a execução da consulta.

Preparar ficheiros para consultas

Se possível, você pode preparar arquivos para um melhor desempenho:

  • Converta grandes arquivos CSV e JSON para Parquet. Parquet é um formato colunar. Como ele é compactado, seus tamanhos de arquivo são menores do que os arquivos CSV ou JSON que contêm os mesmos dados. O pool SQL sem servidor ignora as colunas e linhas que não são necessárias em uma consulta se você estiver lendo arquivos do Parquet. O pool SQL sem servidor precisa de menos tempo e menos solicitações de armazenamento para lê-lo.
  • Se uma consulta tiver como alvo um único arquivo grande, você se beneficiará de dividi-lo em vários arquivos menores.
  • Tente manter o tamanho do arquivo CSV entre 100 MB e 10 GB.
  • É melhor ter arquivos de tamanho igual para um único caminho OPENROWSET ou uma tabela externa LOCATION.
  • Particione seus dados armazenando partições em diferentes pastas ou nomes de arquivos. Consulte Usar funções de nome de arquivo e caminho de arquivo para direcionar partições específicas.

Colocalize seu armazenamento analítico do Azure Cosmos DB e o pool SQL sem servidor

Verifique se o armazenamento analítico do Azure Cosmos DB está colocado na mesma região que um espaço de trabalho do Azure Synapse. As consultas entre regiões podem causar grandes latências. Use a propriedade region na cadeia de conexão para especificar explicitamente a região onde o repositório analítico é colocado (consulte Consultar o Azure Cosmos DB usando o pool SQL sem servidor): account=<database account name>;database=<database name>;region=<region name>'

Otimizações CSV

Aqui estão as práticas recomendadas para usar arquivos CSV no pool SQL sem servidor.

Use o PARSER_VERSION 2.0 para consultar arquivos CSV

Você pode usar um analisador de desempenho otimizado ao consultar arquivos CSV. Para obter detalhes, consulte PARSER_VERSION.

Criar manualmente estatísticas para ficheiros CSV

O pool SQL sem servidor depende de estatísticas para gerar planos de execução de consulta ideais. As estatísticas são criadas automaticamente para colunas usando amostragem e, na maioria dos casos, a porcentagem de amostragem será inferior a 100%. Esse fluxo é o mesmo para todos os formatos de arquivo. Tenha em mente que, ao ler CSV com analisador versão 1.0, a amostragem não é suportada e a criação automática de estatísticas não acontecerá com a porcentagem de amostragem inferior a 100%. Para tabelas pequenas com cardinalidade baixa estimada (número de linhas), a criação automática de estatísticas será acionada com porcentagem de amostragem de 100%. Isso significa que o fullscan é acionado e estatísticas automáticas são criadas mesmo para CSV com analisador versão 1.0. Caso as estatísticas não sejam criadas automaticamente, crie estatísticas manualmente para colunas que você usa em consultas, especialmente aquelas usadas em DISTINCT, JOIN, WHERE, ORDER BY e GROUP BY. Verifique as estatísticas no pool SQL sem servidor para obter detalhes.

Tipos de dados

Aqui estão as práticas recomendadas para usar tipos de dados no pool SQL sem servidor.

Usar tipos de dados apropriados

Os tipos de dados usados na consulta afetam o desempenho e a simultaneidade. Poderá obter um desempenho melhor se seguir estes orientações:

  • Use o menor tamanho de dados que possa acomodar o maior valor possível.
    • Se o comprimento máximo do valor do caractere for de 30 caracteres, use um tipo de dados de caractere de comprimento 30.
    • Se todos os valores de coluna de caracteres forem de tamanho fixo, use char ou nchar. Caso contrário, use varchar ou nvarchar.
    • Se o valor máximo da coluna inteira for 500, use smallint porque é o menor tipo de dados que pode acomodar esse valor. Para obter mais informações, consulte Intervalos de tipos de dados inteiros.
  • Se possível, use varchar e char em vez de nvarchar e nchar.
    • Use o tipo varchar com algum agrupamento UTF8 se estiver lendo dados do Parquet, Azure Cosmos DB, Delta Lake ou CSV com codificação UTF-8.
    • Use o tipo varchar sem agrupamento UTF8 se estiver lendo dados de arquivos CSV não-Unicode (por exemplo, ASCII).
    • Use o tipo nvarchar se estiver lendo dados de um arquivo CSV UTF-16.
  • Use tipos de dados baseados em inteiros, se possível. As operações SORT, JOIN e GROUP BY são concluídas mais rapidamente em inteiros do que em dados de caracteres.
  • Se você estiver usando inferência de esquema, verifique os tipos de dados inferidos e substitua-os explicitamente pelos tipos menores, se possível.

Verificar tipos de dados inferidos

A inferência de esquema ajuda você a escrever consultas e explorar dados rapidamente sem conhecer esquemas de arquivo. O custo dessa conveniência é que os tipos de dados inferidos podem ser maiores do que os tipos de dados reais. Essa discrepância acontece quando não há informações suficientes nos arquivos de origem para garantir que o tipo de dados apropriado seja usado. Por exemplo, os arquivos Parquet não contêm metadados sobre o comprimento máximo da coluna de caracteres. Então, o pool SQL sem servidor infere como varchar(8000).

Tenha em mente que a situação pode ser diferente no caso das tabelas Spark gerenciadas e externas compartilháveis expostas no mecanismo SQL como tabelas externas. As tabelas Spark fornecem tipos de dados diferentes dos mecanismos Synapse SQL. O mapeamento entre tipos de dados de tabela do Spark e tipos SQL pode ser encontrado aqui.

Você pode usar o sp_describe_first_results_set de procedimento armazenado do sistema para verificar os tipos de dados resultantes da sua consulta.

O exemplo a seguir mostra como você pode otimizar os tipos de dados inferidos. Este procedimento é usado para mostrar os tipos de dados inferidos:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Aqui está o conjunto de resultados:

is_hidden column_ordinal nome system_type_name max_length
0 1 vendor_id Varchar(8000) 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count número inteiro 4

Depois de conhecer os tipos de dados inferidos para a consulta, você pode especificar os tipos de dados apropriados:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Otimização do filtro

Aqui estão as práticas recomendadas para usar consultas no pool SQL sem servidor.

Empurre curingas para níveis mais baixos no caminho

Você pode usar curingas em seu caminho para consultar vários arquivos e pastas. O pool SQL sem servidor lista os arquivos em sua conta de armazenamento, começando a partir do primeiro asterisco (*), usando a API de armazenamento. Elimina os ficheiros que não correspondem ao caminho especificado. Reduzir a lista inicial de ficheiros pode melhorar o desempenho se existirem muitos ficheiros que correspondam ao caminho especificado até ao primeiro caráter universal.

Usar funções de nome de arquivo e caminho de arquivo para direcionar partições específicas

Os dados são frequentemente organizados em partições. Você pode instruir o pool SQL sem servidor a consultar pastas e arquivos específicos. Isso reduz o número de arquivos e a quantidade de dados que a consulta precisa ler e processar. Um bônus adicional é que você alcançará um melhor desempenho.

Para obter mais informações, leia sobre as funções de nome de arquivo e caminho de arquivo e consulte os exemplos para consultar arquivos específicos.

Gorjeta

Sempre converta os resultados das funções filepath e filename para os tipos de dados apropriados. Se você usar tipos de dados de caracteres, certifique-se de usar o comprimento apropriado.

Atualmente, as funções usadas para eliminação de partição, caminho de arquivo e nome de arquivo não são suportadas para tabelas externas, exceto aquelas criadas automaticamente para cada tabela criada no Apache Spark for Azure Synapse Analytics.

Se os dados armazenados não estiverem particionados, considere particioná-los. Dessa forma, você pode usar essas funções para otimizar consultas direcionadas a esses arquivos. Quando você consulta tabelas particionadas do Apache Spark for Azure Synapse a partir do pool SQL sem servidor, a consulta direciona automaticamente apenas os arquivos necessários.

Use o agrupamento adequado para utilizar pushdown de predicado para colunas de caracteres

Os dados num ficheiro Parquet estão organizados em grupos de linhas. O conjunto de SQL sem servidor ignora os grupos de linhas baseados no predicado especificado na cláusula WHERE, o que reduz as E/S. O resultado é um aumento do desempenho das consultas.

A pressão de predicados para colunas de caracteres em arquivos Parquet é suportada apenas para agrupamento Latin1_General_100_BIN2_UTF8. Pode especificar o agrupamento para uma coluna em particular com a cláusula WITH. Se não especificar este agrupamento com a cláusula WITH, será utilizado o agrupamento da base de dados.

Otimizar a repetição de consultas

Aqui estão as práticas recomendadas para usar o CETAS no pool SQL sem servidor.

Utilizar o CETAS para melhorar o desempenho das consultas e as associações

O CETAS é um dos recursos mais importantes disponíveis no pool SQL sem servidor. O CETAS é uma operação paralela que cria metadados de tabelas externas e exporta os resultados da consulta SELECT para um conjunto de ficheiros na sua conta de armazenamento.

Pode utilizar o CETAS para materializar partes de consultas frequentemente utilizadas, como tabelas de referência associadas, num novo conjunto de ficheiros. Em seguida, você pode se juntar a essa única tabela externa em vez de repetir junções comuns em várias consultas.

Como o CETAS gera arquivos Parquet, as estatísticas são criadas automaticamente quando a primeira consulta tem como alvo essa tabela externa. O resultado é um melhor desempenho para a tabela de segmentação de consultas subsequentes gerada com o CETAS.

Consultar dados do Azure

Os pools SQL sem servidor permitem que você consulte dados no Armazenamento do Azure ou no Azure Cosmos DB usando tabelas externas e a função OPENROWSET. Certifique-se de que tem a permissão adequada configurada no seu armazenamento.

Consultar dados CSV

Saiba como consultar um único arquivo ou pastas CSV e vários arquivos CSV. Você também pode consultar arquivos particionados

Consultar dados do Parquet

Saiba como consultar arquivos do Parquet com tipos aninhados. Você também pode consultar arquivos particionados.

Consulta Lago Delta

Saiba como consultar arquivos Delta Lake com tipos aninhados.

Consultar dados do Azure Cosmos DB

Saiba como consultar o repositório analítico do Azure Cosmos DB. Você pode usar um gerador online para gerar a cláusula WITH com base em um documento de exemplo do Azure Cosmos DB. Você pode criar modos de exibição sobre contêineres do Azure Cosmos DB.

Consultar dados JSON

Saiba como consultar arquivos JSON. Você também pode consultar arquivos particionados.

Criar modos de exibição, tabelas e outros objetos de banco de dados

Saiba como criar e utilizar vistas e tabelas externas ou configurar a segurança ao nível da linha. Se tiver ficheiros particionados, certifique-se de que utiliza vistas particionadas.

Copiar e transformar dados (CETAS)

Saiba como armazenar os resultados da consulta no armazenamento usando o comando CETAS.

Próximos passos