Partilhar via

Cálculos de pressão no PolyBase

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

A computação pushdown melhora o desempenho de consultas em fontes de dados externas. A partir do SQL Server 2016 (13.x), os cálculos pushdown estavam disponíveis para fontes de dados externas do Hadoop. O SQL Server 2019 (15.x) introduziu cálculos pushdown para outros tipos de fontes de dados externas.

Observação

Para determinar se o cálculo pushdown do PolyBase está a beneficiar a sua consulta, consulte Como saber se ocorreu pushdown externo.

Ativar computação pushdown

Os artigos seguintes incluem informações sobre a configuração do cálculo por empurramento para tipos específicos de fontes de dados externas:

Esta tabela resume o suporte ao cálculo pushdown em diferentes fontes externas de dados:

Fonte de dados Joins Projeções Aggregations Filtros Estatísticas
ODBC genérico Yes Yes Yes Yes Yes
Oracle Sim+ Yes Yes Yes Yes
Servidor SQL Yes Yes Yes Yes Yes
Teradata Yes Yes Yes Yes Yes
MongoDB* No Yes Sim*** Sim*** Yes
Hadoop No Yes Alguns** Alguns** Yes
Armazenamento de Blobs do Azure Não Não Não Não Yes

* O suporte para pushdown do Azure Cosmos DB está ativado através da API Azure Cosmos DB para MongoDB.

** Ver Processamento Pushdown e fornecedores Hadoop.

O suporte pushdown para agregações e filtros para o conector MongoDB ODBC para SQL Server 2019 foi introduzido com o SQL Server 2019 CU18.

+ A Oracle suporta pushdown para joins, mas pode ser necessário criar estatísticas nas colunas de join para efetuar o pushdown.

Observação

O processamento em pushdown pode ser bloqueado por certa sintaxe T-SQL. Para mais informações, consulte a Sintaxe que previne o pushdown.

Computação por rebaixamento e fornecedores de Hadoop

Atualmente, o PolyBase suporta dois fornecedores Hadoop: Hortonworks Data Platform (HDP) e Cloudera Distributed Hadoop (CDH). Não existem diferenças entre as duas funcionalidades em termos de cálculo de pushdown.

Para usar a funcionalidade de empurramento de computação com o Hadoop, o cluster Hadoop alvo deve ter os componentes principais HDFS, YARN e MapReduce, com o servidor de histórico de trabalhos ativado. O PolyBase envia a consulta pushdown via MapReduce e extrai o status do servidor de histórico de trabalhos. Sem qualquer componente, a consulta falha.

Alguma agregação deve ocorrer depois de os dados chegarem ao SQL Server. Mas uma parte da agregação ocorre no Hadoop. Este método é comum na computação de agregações em sistemas de processamento massivamente paralelos.

Os fornecedores Hadoop suportam as seguintes agregações e filtros.

Agregações Filtros (comparação binária)
Count_Big NotEqual
Soma MenosQue
Média MenosOrIgual
Max Maior Ou Igual
Minuto GreaterThan
Approx_Count_Distinct É
IsNot

Cenários benéficos chave do cálculo pushdown

Com a computação de pushdown do PolyBase, é possível delegar tarefas de computação a fontes de dados externas. Isto reduz a carga de trabalho na instância do SQL Server e pode melhorar significativamente o desempenho.

O SQL Server pode enviar joins, projeções, agregações e filtros para fontes de dados externas, tirando partido da computação remota e restringindo os dados enviados pela rede.

Empurrão das junções

Em muitos casos, o PolyBase pode facilitar o pushdown do operador de junção ao unir duas tabelas externas na mesma fonte de dados externa, o que irá melhorar significativamente o desempenho.

Se a junção puder ser feita na fonte de dados externa, isso reduz a quantidade de movimento de dados e melhora o desempenho da consulta. Sem o join pushdown, os dados das tabelas a juntar devem ser trazidos localmente para o tempdb e depois juntos.

No caso das junções distribuídas (juntar uma tabela local a uma tabela externa), a menos que exista um filtro na tabela externa unida, todos os dados da tabela externa devem ser introduzidos tempdb localmente para realizar a operação de junção. Por exemplo, a consulta seguinte não tem filtragem na condição de junção da tabela externa, o que resultará na leitura de todos os dados da tabela externa.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id

Como a junção está na E.id coluna da tabela externa, se uma condição de filtro for adicionada a essa coluna, o filtro pode ser pressionado para baixo, reduzindo assim o número de linhas lidas da tabela externa.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id
WHERE E.id = 20000

Selecione um subconjunto de linhas

Use o predicate pushdown para melhorar o desempenho de uma consulta que seleciona um subconjunto de linhas de uma tabela externa.

Neste exemplo, o SQL Server inicia uma tarefa de map-reduce para recuperar as linhas que correspondem ao predicado customer.account_balance < 200000 no Hadoop. Como a consulta pode ser concluída com sucesso sem passar por todas as linhas da tabela, apenas as linhas que cumprem os critérios de predicado são copiadas para o SQL Server. Isto poupa tempo significativo e requer menos espaço de armazenamento temporário quando o número de saldos < de clientes 200000 é pequeno em comparação com o número de clientes com saldos >de conta = 200000.

SELECT * FROM customer WHERE customer.account_balance < 200000;
SELECT * FROM SensorData WHERE Speed > 65;

Selecione um subconjunto de colunas

Use o predicate pushdown para melhorar o desempenho de uma consulta que seleciona um subconjunto de colunas de uma tabela externa.

Nesta consulta, o SQL Server inicia um trabalho de map-reduce para pré-processar o ficheiro de texto delimitado do Hadoop, de modo a que apenas os dados das duas colunas, customer.name e customer.zip_code, sejam copiados para o SQL Server.

SELECT customer.name, customer.zip_code
FROM customer
WHERE customer.account_balance < 200000;

Pushdown para expressões e operadores básicos

O SQL Server permite as seguintes expressões básicas e operadores para o pushdown de predicados.

  • Operadores binários de comparação (<, >, =, !=, <>, >=, , ) <=para valores numéricos, de data e de tempo.
  • Operadores aritméticos (+, -, *, /, %).
  • Operadores lógicos (AND, OR).
  • Operadores unários (NOT, IS NULL, IS NOT NULL).

Os operadores BETWEEN, NOT, IN, e LIKE podem ser empurrados para baixo. O comportamento real depende de como o otimizador de consultas reescreve as expressões de operadores como uma série de instruções que utilizam operadores relacionais básicos.

A consulta neste exemplo tem múltiplos predicados que podem ser enviados para o Hadoop. O SQL Server pode enviar trabalhos map-reduce para o Hadoop para executar o predicado customer.account_balance <= 200000. A expressão BETWEEN 92656 AND 92677 é também composta por operações binárias e lógicas que podem ser enviadas para Hadoop. O lógico AND em customer.account_balance AND customer.zipcode é uma expressão final.

Dada esta combinação de predicados, os processos de map-reduce podem executar a totalidade da cláusula WHERE. Apenas os dados que cumprem os SELECT critérios são copiados de volta para o SQL Server.

SELECT * FROM customer 
WHERE customer.account_balance <= 200000 
AND customer.zipcode BETWEEN 92656 AND 92677;

Funções suportadas para pushdown

O SQL Server permite as seguintes funções para a redução de predicados.

Funções de cadeia de caracteres

  • CONCAT
  • DATALENGTH
  • LEN
  • LIKE
  • LOWER
  • LTRIM
  • RTRIM
  • SUBSTRING
  • UPPER

Funções matemáticas

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • CEILING
  • COS
  • EXP
  • FLOOR
  • POWER
  • SIGN
  • SIN
  • SQRT
  • TAN

Funções gerais

  • COALESCE *
  • NULLIF

* Usar com COLLATE pode evitar o efeito de compressão em alguns cenários. Para mais informações, veja em Conflito de colação.

Funções de data e hora

  • DATEADD
  • DATEDIFF
  • DATEPART

Sintaxe que previne o empurrão

As seguintes funções ou sintaxe do T-SQL impedem a computação de pushdown:

  • AT TIME ZONE
  • CONCAT_WS
  • TRANSLATE
  • RAND
  • CHECKSUM
  • BINARY_CHECKSUM
  • HASHBYTES
  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • NEWID
  • STRING_ESCAPE
  • COMPRESS
  • DECOMPRESS
  • GREATEST
  • LEAST
  • PARSE

O suporte para pushdown da sintaxe FORMAT e TRIM foi introduzido no SQL Server 2019 (15.x) CU10.

Cláusula de filtro com variável

Ao especificar uma variável numa cláusula de filtro, por defeito isto impede o empurramento da cláusula de filtro. Por exemplo, se executar a seguinte consulta, a cláusula de filtro não será pressionada:

DECLARE @BusinessEntityID INT

SELECT * FROM [Person].[BusinessEntity]  
WHERE BusinessEntityID = @BusinessEntityID;

Para conseguir o pushdown da variável, é necessário ativar a funcionalidade de hotfixes do otimizador de consultas. Isto pode ser feito de uma das seguintes formas:

  • Nível de Instância: Ativar o flag de traço 4199 como parâmetro de arranque para a instância
  • Nível da Base de Dados: No contexto da base de dados que contém os objetos externos PolyBase, execute ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Nível de consulta: Usar consulta hint OPTION (QUERYTRACEON 4199) ou OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Esta limitação aplica-se à execução de sp_executesql. A limitação aplica-se também à utilização de algumas funções na cláusula de filtro.

A capacidade de pressionar a variável foi introduzida pela primeira vez no SQL Server 2019 CU5.

Conflito de colação

O pushdown pode não ser possível com dados com diferentes colações. Operadores como COLLATE também podem interferir com o resultado. São suportadas colações iguais ou binárias. Para mais informações, veja Como saber se ocorreu um empurramento.

Otimização para ficheiros de parquet

A partir do SQL Server 2022 (16.x), o PolyBase introduziu suporte para ficheiros parquet. O SQL Server é capaz de realizar tanto a eliminação de linhas como de colunas ao aplicar pushdown com parquet. Com ficheiros de parquet, as seguintes operações podem ser empurradas para baixo:

  • Operadores binários de comparação (>, >=, <=, <) para valores numéricos, de data e de tempo.
  • Combinação de operadores de comparação (> AND <, >= AND <, > AND <=, <= AND >=).
  • No filtro de lista (col1 = val1 OU col1 = val2 OU vol1 = val3).
  • NÃO É NULO sobre a coluna.

A presença do seguinte impede o pushdown para ficheiros de parquet:

  • Colunas virtuais.
  • Comparação de colunas.
  • Conversão de tipos de parâmetros.

Tipos de dados suportados

  • Pouco
  • TinyInt
  • SmallInt
  • BigInt
  • real
  • Flutuar
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binary
  • DateTime2 (padrão e precisão de 7 dígitos)
  • Date
  • Tempo (padrão e precisão de 7 dígitos)
  • Numérico *

* Suportado quando a escala dos parâmetros se alinha com a escala da coluna, ou quando o parâmetro é explicitamente convertido em decimal.

Tipos de dados que impedem o empurramento do parquet

  • Dinheiro
  • SmallMoney
  • DateTime
  • SmallDateTime

Examples

Empurrar de força

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (FORCE EXTERNALPUSHDOWN);

Desativar o empurrão

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (DISABLE EXTERNALPUSHDOWN);

Conteúdo relacionado

  • Last updated on