Compartilhar via

Práticas recomendadas para pool de SQL sem servidor no Azure Synapse Analytics

  • Artigo

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

O pool de SQL sem servidor permite consultar arquivos nas contas de Armazenamento do Microsoft Azure. Ele não tem armazenamento local nem recursos de ingestão. Todos os arquivos que as consultas direcionam são externos ao pool de SQL sem servidor. Tudo relacionado à leitura de arquivos do armazenamento pode afetar o desempenho da consulta.

Algumas diretrizes genéricas são:

  • Verifique se os aplicativos cliente estão colocados com o pool de SQL sem servidor.
    • Se você estiver usando aplicativos cliente fora do Azure, certifique-se de estar usando o pool de SQL sem servidor em uma região próxima ao computador cliente. Exemplos de aplicativo cliente incluem Power BI Desktop, SQL Server Management Studio e Azure Data Studio.
  • Verifique se o armazenamento e o pool de SQL sem servidor estejam na mesma região. Exemplos de Armazenamento incluem o Azure Data Lake Storage e o Azure Cosmos DB.
  • Tente otimizar o layout de armazenamento usando o particionamento e mantendo os arquivos no intervalo entre 100 MB e 10 GB.
  • Se você estiver retornando um grande número de resultados, certifique-se de estar usando SQL Server Management Studio ou Azure Data Studio e não o Azure Synapse Studio. O Azure Synapse Studio é uma ferramenta da Web que não foi projetada para grandes conjuntos de resultados.
  • Se você estiver filtrando os resultados por coluna de cadeia de caracteres, experimente usar um agrupamento BIN2_UTF8. Para obter mais informações sobre como alterar agrupamentos, consulte Tipos de agrupamentos compatíveis com o SQL do Synapse.
  • Considere armazenar em cache os resultados do lado do cliente usando o modo de importação do Power BI ou o Azure Analysis Services e atualize-os periodicamente. Os pools de SQL sem servidor não poderão fornecer uma experiência interativa no modo de consulta direta do Power BI, caso se você estiver usando consultas complexas ou processando uma grande quantidade de dados.
  • Não limitado e depende da complexidade da consulta e da quantidade de dados examinados. Um pool de SQL sem servidor pode lidar simultaneamente com 1.000 sessões ativas que estão executando consultas leves. Os números serão suspensos se as consultas forem mais complexas ou verificarem uma quantidade maior de dados, portanto, nesse caso, considere diminuir a simultaneidade e executar consultas por um período maior de tempo, se possível.

Aplicativos cliente e conexões de rede

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

  • Coloque um aplicativo cliente com o espaço de trabalho do Azure Synapse. Caso se você estiver usando aplicativos como o Power BI ou o Azure Analysis Services, confira se eles estão na mesma região em que você posicionou seu espaço de trabalho do Azure Synapse. Se necessário, crie os espaços de trabalho separados que são emparelhados com os aplicativos cliente. Posicionar um aplicativo cliente e o espaço de trabalho do Azure Synapse em diferentes regiões pode causar maior latência e fluxo mais lento de resultados.
  • Caso se você estiver lendo dados do aplicativo local, confira se o espaço de trabalho do Azure Synapse está na região perto do seu local.
  • Certifique-se de que você não tem problemas de largura de banda de rede ao ler 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 da Web que usa o protocolo HTTPS para transferir dados. Use o Azure Data Studio ou o SQL Server Management Studio para ler uma grande quantidade de dados.

Armazenamento e layout de conteúdo

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

Colocar o armazenamento e o pool de SQL sem servidor

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

Para melhor desempenho, caso você acesse outras contas de armazenamento com o pool de SQL sem servidor, verifique se elas estão na mesma região. Se elas não estiverem na mesma região, haverá uma latência maior para a transferência de rede dos dados entre a região remota e as regiões 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 o IOPS combinado ou a taxa de transferência gerada por aplicativos, serviços e carga de trabalho do pool de SQL sem servidor excede os limites da conta de armazenamento. Como resultado, você enfrentará um impacto negativo significativo no desempenho da consulta.

Quando a limitação é detectada, o pool de SQL sem servidor manipula internamente para solucioná-la. O pool de SQL sem servidor faz solicitações de armazenamento em um ritmo mais lento até que a limitação seja resolvida.

Dica

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

Preparar arquivos para consulta

Se possível, você pode preparar arquivos para melhorar o desempenho:

  • Converta grandes arquivos CSV e JSON em Parquet. Parquet é um formato de coluna. Como ele é compactado, os tamanhos de arquivo são menores do que arquivos CSV ou JSON com os mesmos dados. Como o pool de SQL sem servidor ignora as colunas e linhas que não são necessárias em uma consulta durante a leitura de arquivos Parquet. O pool de SQL sem servidor precisa de menos tempo e menos solicitações de armazenamento para lê-los.
  • Se uma consulta tiver como objetivo um único arquivo grande, dividi-lo em vários arquivos menores será vantajoso para você.
  • Tente manter o tamanho dos arquivos CSV entre 100 MB e 10 GB.
  • É melhor ter arquivos com o mesmo tamanho para um único caminho OPENROWSET ou um local de tabela externa.
  • Particione os dados armazenando partições em diferentes pastas ou nomes de arquivos. Consulte Usar as funções fileinfo e filepath para segmentar partições específicas.

Colocar o armazenamento analítico do Azure Cosmos DB e o pool de SQL sem servidor

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

Otimizações de CSV

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

Usar PARSER_VERSION 2.0 para consultar arquivos CSV

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

Criar manualmente estatísticas para arquivos CSV

O pool de SQL sem servidor se baseia em 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, o percentual de amostragem será inferior a 100%. Esse fluxo é o mesmo para cada formato de arquivo. Lembre-se de que, ao ler CSV com a versão 1.0 do analisador, não há suporte para amostragem e a criação automática de estatísticas não ocorrerá com uma porcentagem de amostragem inferior a 100%. Para tabelas pequenas com baixa cardinalidade estimada (número de linhas), a criação automática de estatísticas será disparada com percentual de amostragem de 100%. Isso significa que o fullscan é disparado e as estatísticas automáticas são criadas mesmo para CSV com a versão 1.0 do analisador. Caso as estatísticas não sejam criadas automaticamente, crie-as manualmente para as colunas que você usa em consultas, particularmente aquelas usadas em DISTINCT, JOIN, WHERE, ORDER BY e GROUP BY. Confira Estatísticas em pool de SQL sem servidor para obter detalhes.

Tipos de dados

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

Usar tipos de dados apropriados

Os tipos de dados usados na consulta afetam o desempenho e a concorrência. Obtenha um melhor desempenho segundo as instruções a seguir:

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

Verificar tipos de dados inferidos

A inferência de esquema ajuda a escrever rapidamente consultas e explorar dados sem conhecer os esquemas de arquivos. O custo dessa conveniência é que os tipos de dados inferidos podem ser maiores que os tipos de dados reais. Esta 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. Por isso o pool de SQL sem servidor infere que é 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 do Spark fornecem tipos de dados diferentes dos mecanismos do Synapse SQL. O mapeamento entre tipos de dados de tabela do Spark e tipos SQL pode ser encontrado aqui.

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

O exemplo a seguir mostra como você pode otimizar os tipos de dados inferidos. Esse 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';

Este é o conjunto de resultados:

is_hidden column_ordinal name system_type_name max_length
0 1 vendor_id varchar(8000) 8000
0 2 pickup_datetime Datetime2 (7) 8
0 3 passenger_count INT 4

Depois de conhecermos os tipos de dados inferidos para a consulta, podemos 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 de filtro

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

Enviar curingas por push para níveis inferiores no caminho

Você pode usar curingas no seu caminho para consultar vários arquivos e pastas. O pool de SQL sem servidor lista os arquivos na conta de armazenamento, começando pelo primeiro asterisco (*), que usa a API de armazenamento. Ele elimina os arquivos que não correspondem ao caminho especificado. A redução da lista de arquivos inicial pode melhorar o desempenho se houver muitos arquivos que correspondam ao caminho especificado até o primeiro caractere curinga.

Usar as funções fileinfo e filepath para segmentar partições específicas

Os dados geralmente são organizados em partições. Você pode instruir o pool de SQL sem servidor para 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ê obterá um melhor desempenho.

Para obter mais informações, leia sobre as funções filename e filepath e veja os exemplos para consultar arquivos específicos.

Dica

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

Atualmente, não há suporte para funções usadas para eliminação de partição, filepath e filename, em tabelas externas diferentes daquelas criadas automaticamente para cada tabela criada no Apache Spark do 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ê consultar tabelas particionadas do Apache Spark para o Azure Synapse a partir do pool de SQL sem servidor, a consulta é automaticamente direcionada somente aos arquivos necessários.

Usar agrupamento adequado no pushdown de predicado para colunas de caracteres

Os dados no arquivo Parquet são organizados em grupos de linhas. O pool de SQL em servidor ignora grupos de linhas com base no predicado especificado na cláusula WHERE, o que reduz a E/S. O resultado é o aumento do desempenho da consulta.

O pushdown de predicado para colunas de caracteres em arquivos Parquet tem suporte somente no agrupamento Latin1_General_100_BIN2_UTF8. É possível especificar o agrupamento para uma determinada coluna usando uma cláusula WITH. Se esse agrupamento não for especificado usando uma cláusula WITH, é utilizado o agrupamento do banco de dados.

Otimizar consultas repetitivas

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

Use o CETAS para aprimorar o desempenho e as junções de consulta

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

Você pode usar o CETAS para materializar as partes mais utilizadas de consultas, como tabelas de referência unidas, em um novo conjunto de arquivos. Você pode unir a essa única tabela externa em vez de repetir junções comuns em várias consultas.

Como a operação CETAS gera arquivos Parquet, as estatísticas são criadas automaticamente quando a primeira consulta for direcionada a essa tabela externa. O resultado é um desempenho aprimorado para consultas subsequentes que direcionam a tabela gerada com o CETAS.

Consultar dados do Azure

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

Consultar dados CSV

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

Consultar arquivos Parquet

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

Consultar o Delta Lake

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. É possível criar exibições sobre os contêineres do Azure Cosmos DB.

Consultar dados JSON

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

Criar exibições, tabelas e outros objetos de banco de dados

Saiba como criar e usar exibições e tabelas externas ou configurar a segurança em nível de linha. Caso você tenha arquivos particionados, certifique-se de usar exibições particionadas.

Copiar e transformar dados (CETAS)

Saiba como armazenar resultados de consulta no repositório usando o comando CETAS.

Próximas etapas