Cálculos de aplicação no PolyBase

  • Artigo

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

A computação de aplicação aprimora o desempenho de consultas em fontes de dados externas. A partir do SQL Server 2016 (13.x), as computações de pushdown ficaram disponíveis para fontes de dados externas do Hadoop. O SQL Server 2019 (15.x) introduziu as computações de pushdown para outros tipos de fontes de dados externas.

Observação

Para determinar se a computação de pushdown do PolyBase está beneficiando ou não sua consulta, confira Como saber se ocorreu o pushdown externo.

Habilitar a computação de pushdown

Os seguintes artigos incluem informações sobre como configurar a computação de aplicação para tipos específicos de fontes de dados externas:

Esta tabela resume o suporte à computação de pushdown em diferentes fontes de dados externas:

Fonte de dados Junções Projeções Agregações Filtros Estatísticas
ODBC Genérico Sim Sim Sim Sim Yes
Oracle Sim Sim Sim Sim Yes
SQL Server Sim Sim Sim Sim Sim
Teradata Sim Sim Sim Sim Yes
MongoDB* Não Sim Sim** Sim** Sim
Hadoop Não Sim Alguns** Alguns** Sim
Armazenamento de Blobs do Azure Não No No No Sim

* A compatibilidade com pushdown do Azure Cosmos DB é habilitada por meio da API do Azure Cosmos DB para MongoDB.

** Confira computação de pushdown e provedores de Hadoop.

*** A compatibilidade com pushdown para agregações e filtros para o conector ODBC do MongoDB para SQL Server 2019 foi introduzida com o SQL Server 2019 CU18.

Observação

A computação de pushdown pode ser bloqueada por alguma sintaxe T-SQL. Para obter mais informações, revise Sintaxe que impede o pushdown.

Computação de pushdown e provedores de Hadoop

Atualmente, o PolyBase é compatível com dois provedores de Hadoop: Hortonworks Data Platform (HDP) e Cloudera Distributed Hadoop (CDH). Não há diferenças entre os dois recursos em termos da computação de pushdown.

Para usar a funcionalidade de pushdown de computação no Hadoop, o cluster do Hadoop de destino deve ter os componentes principais do HDFS, YARN e MapReduce, com o servidor de histórico de trabalhos habilitado. O PolyBase envia a consulta de aplicação via MapReduce e recebe o status do servidor de histórico de trabalhos. A consulta falhará se não tiver um desses componentes.

Alguma agregação precisa ocorrer após os dados alcançarem o SQL Server. Porém, uma parte da agregação ocorre no Hadoop. Esse método é comum nas agregações de computação em sistemas de processamento massivamente paralelos.

Os provedores de Hadoop dão suporte às agregações e aos filtros a seguir.

Agregações Filtros (comparação binária)
Count_Big NotEqual
Somar LessThan
Avg LessOrEqual
Max GreaterOrEqual
Min GreaterThan
Approx_Count_Distinct É
IsNot

Principais cenários benéficos da computação de pushdown

Com o cálculo de pushdown do PolyBase, você pode delegar tarefas de computação a fontes de dados externas. Isso reduz a carga de trabalho na instância do SQL Server e pode aprimorar bastante o desempenho.

O SQL Server pode enviar por push junções, projeções, agregações e filtros para fontes de dados externas para aproveitar a computação remota e restringir os dados enviados pela rede.

Pushdown de junções

Em muitos casos, o PolyBase pode facilitar o pushdown do operador de junção para a junção de duas tabelas externas na mesma fonte de dados externa, o que aprimorará bastante o desempenho.

Se a junção puder ser feita na fonte de dados externa, isso reduzirá a quantidade de movimentação de dados e aprimorará o desempenho da consulta. Sem o pushdown da junção, os dados das tabelas a serem unidas devem ser colocados localmente no tempdb e, em seguida, unidos.

Observe que, no caso de junções distribuídas (unindo uma tabela local a uma tabela externa), a menos que haja algum critério de filtragem na tabela externa aplicado à condição de junção, todos os dados na tabela externa devem ser trazidos localmente para tempdb para executar a operação de junção. Por exemplo, a consulta a seguir não tem filtragem na condição de junção de 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 coluna E.id da tabela externa, se uma condição de filtro for adicionada a essa coluna, o filtro poderá ser empurrado 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

Selecionar um subconjunto de linhas

Use aplicação de predicado para melhorar o desempenho de uma consulta que seleciona um subconjunto de linhas de uma tabela externa.

Neste exemplo, o SQL Server inicia um trabalho de redução de mapa para recuperar as linhas que correspondem ao predicado customer.account_balance < 200000 no Hadoop. Como a consulta pode ser concluída com êxito sem examinar todas as linhas na tabela, somente as linhas que atendem aos critérios de predicado são copiadas para o SQL Server. Isto economiza bastante tempo e exige menos espaço de armazenamento temporário quando o número de saldos do cliente < 200000 é pequeno em comparação com o número de clientes com saldos da conta >= 200000.

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

Selecionar um subconjunto de colunas

Use aplicação de predicado 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 map-reduce para pré-processar o arquivo de texto delimitado por Hadoop para que somente os dados de 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;

Aplicação de operadores e expressões básicas

O SQL Server permite os seguintes operadores e expressões básicas para a aplicação de predicado.

  • Operadores de comparação binária (<, >, =, !=, <>, >=, <=) para valores temporais, de data e numéricos.
  • 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 propagados. O comportamento real depende de como o otimizador de consulta reescreve as expressões do operador como uma série de instruções que usam operadores relacionais básicos.

A consulta neste exemplo tem vários predicados que podem ser propagados para o Hadoop. O SQL Server pode enviar por push 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 por push para Hadoop. A lógica E no customer.account_balance AND customer.zipcode é uma expressão final.

Dada essa combinação de predicados, os trabalhos map-reduce podem executar tudo da cláusula WHERE. Somente os dados que atendem aos critérios SELECT 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 compatíveis para pushdown

O SQL Server permite as funções a seguir para pushdown de predicado.

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 pushdown em alguns cenários. Para obter mais informações, consulte Conflito de agrupamento.

Funções de data & hora

  • DATEADD
  • DATEDIFF
  • DATEPART

Sintaxe que impede o pushdown

As seguintes funções ou sintaxe T-SQL impedirão 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

A compatibilidade com pushdown para a sintaxe FORMAT e TRIM foi introduzida no SQL Server 2019 (15.x) CU10.

Cláusula de filtro com variável

Se você estiver especificando uma variável em uma cláusula de filtro, por padrão, isso impedirá o pushdown dessa cláusula. Por exemplo, se você executar a seguinte consulta, a cláusula de filtro não sofrerá pushdown:

DECLARE @BusinessEntityID INT

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

Para obter o pushdown da variável, você precisa habilitar a funcionalidade de hotfixes do otimizador de consulta. Isso pode ser feito de uma das seguintes maneiras:

  • Nível da instância: habilite o sinalizador de rastreamento 4199 como um parâmetro de inicialização para a instância
  • Nível do banco de dados: no contexto do banco de dados que contém os objetos externos PolyBase, execute ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Nível da consulta: use a dica de consulta OPTION (QUERYTRACEON 4199) ou OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

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

Observação: a capacidade de pushdown da variável foi apresentada pela primeira vez no SQL Server 2019 CU5.

Conflito de agrupamento

Se o trabalho com dados com pushdown de agrupamento diferente não for possível, operadores como COLLATE também poderão interferir no resultado. Agrupamentos iguais ou agrupamentos binários são compatíveis. Para obter mais informações, consulte Como saber se ocorreu pushdown.

Pushdown para arquivos parquet

A partir do SQL Server 2022 (16.x), o PolyBase introduziu a compatibilidade com arquivos parquet. O SQL Server é capaz de executar a eliminação de linhas e colunas ao executar o pushdown com parquet. Ao trabalhar com arquivos parquet, as seguintes operações podem ser empurradas para baixo:

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

A presença dos seguintes itens evita pushdown para arquivos parquet:

  • Colunas virtuais.
  • Comparação de coluna.
  • Conversão de tipo de parâmetro.

Tipos de dados com suporte

  • bit
  • TinyInt
  • SmallInt
  • BigInt
  • Real
  • Float
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binário
  • DateTime2 (padrão e precisão de sete dígitos)
  • Data
  • Tempo (padrão e precisão de sete dígitos)
  • Numérico*

* Compatível quando a escala de parâmetros se alinha com a escala de coluna ou quando o parâmetro é explicitamente convertido em decimal.

Tipos de dados que impedem o pushdown do parquet

  • Quantia
  • SmallMoney
  • DateTime
  • SmallDateTime

Exemplos

Forçar aplicação

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

Desabilitar aplicação

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

Próximas etapas

Confira também