Calcoli con distribuzione in PolyBase

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive

Il calcolo con distribuzione migliora le prestazioni delle query su origini dati esterne. A partire da SQL Server 2016 (13.x), sono disponibili calcoli con distribuzione per le origini dati esterne Hadoop. SQL Server 2019 (15.x) ha introdotto i calcoli con distribuzione per altri tipi di origini dati esterne.

Nota

Per determinare se il calcolo con distribuzione di PolyBase tragga vantaggio dalla query, vedere Come stabilire se si è verificato un pushdown esterno.

Abilitare il calcolo con distribuzione

Gli articoli seguenti includono informazioni sulla configurazione del calcolo con distribuzione per tipi specifici di origini dati esterne:

Questa tabella riepiloga il supporto per il calcolo pushdown in origini dati esterne diverse:

Origine dati Join Proiezioni Aggregazioni Filtri Statistiche
ODBC generico Sì Sì Sì Sì Sì
Oracle Sì Sì Sì Sì Sì
SQL Server Sì Sì Sì Sì Sì
Teradata Sì Sì Sì Sì Sì
MongoDB* No Sì Sì*** Sì*** Sì
Hadoop No Sì In parte** In parte** Sì
Archiviazione BLOB di Azure No No No No Sì

* Il supporto per il pushdown di Azure Cosmos DB è abilitato tramite l'API di Azure Cosmos DB per MongoDB.

** Vedere Calcolo con distribuzione e provider Hadoop.

Il supporto pushdown per aggregazioni e filtri per il connettore ODBC MongoDB per SQL Server 2019 è stato introdotto con SQL Server 2019 CU18.

Nota

Il calcolo con distribuzione può essere bloccato da una sintassi T-SQL. Per altre informazioni, vedere Sintassi che impedisce il pushdown.

Calcolo con distribuzione e provider Hadoop

PolyBase supporta attualmente due provider di Hadoop: Hortonworks Data Platform (HDP) e Cloudera Distributed Hadoop (CDH). Non esistono differenze tra le due funzionalità in termini di calcolo con distribuzione.

Per usare la funzionalità di distribuzione di calcolo con Hadoop, il cluster Hadoop di destinazione deve disporre dei componenti principali di HDFS, YARN e MapReduce con il server della cronologia processo abilitato. PolyBase invia la query di distribuzione tramite MapReduce e recupera lo stato dal server della cronologia processo. Senza uno dei due componenti la query ha esito negativo.

Alcune aggregazioni devono verificarsi dopo che i dati hanno raggiunto SQL Server. Tuttavia, una parte dell'aggregazione viene eseguita in Hadoop. Si tratta di un metodo comune di calcolo delle aggregazioni nei sistemi con la funzionalità di elaborazione parallela massiva.

I provider Hadoop supportano le aggregazioni e i filtri seguenti.

Aggregazioni Filtri (confronto binario)
Count_Big NotEqual
Sum LessThan
Media LessOrEqual
Max GreaterOrEqual
Min GreaterThan
Approx_Count_Distinct È
IsNot

Principali scenari vantaggiosi per il calcolo con distribuzione

Con il calcolo con distribuzione di PolyBase, è possibile delegare le attività di calcolo alle origini dati esterne. Ciò riduce il carico di lavoro nell'istanza di SQL Server e può migliorare sensibilmente le prestazioni.

SQL Server può eseguire il push di join, proiezioni, aggregazioni e filtri alle origini dati esterne per sfruttare il calcolo remoto e limitare i dati inviati in rete.

Pushdown dei join

In molti casi, PolyBase può facilitare il pushdown dell'operatore di join per il join di due tabelle esterne nella stessa origine dati esterna, con conseguente notevole miglioramento delle prestazioni.

Se il join può essere eseguito nell'origine dati esterna, ciò riduce la quantità di spostamento dati e migliora le prestazioni della query. Senza eseguire il pushdown di join, i dati delle tabelle da unire devono essere inseriti in locale in tempdb, quindi uniti.

Nel caso di join distribuiti (join di una tabella locale a una tabella esterna), a meno che non esistano alcuni criteri di filtro nella tabella esterna applicata alla condizione di join, tutti i dati nella tabella esterna devono essere inseriti localmente in tempdb per eseguire l'operazione di join. Ad esempio, la query seguente non dispone di filtri sulla condizione di join della tabella esterna, il che comporterà la lettura di tutti i dati della tabella esterna.

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

Poiché il join si trova nella colonna E.id della tabella esterna, se viene aggiunta una condizione di filtro a tale colonna, è possibile eseguire il push del filtro riducendo così il numero di righe lette dalla tabella esterna

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

Selezionare un subset di righe

Usare la distribuzione del predicato per migliorare le prestazioni se la query seleziona un subset di righe da una tabella esterna.

In questo esempio, SQL Server avvia un processo MapReduce per recuperare le righe corrispondenti al predicato customer.account_balance < 200000 in Hadoop. Dato che la query può essere completata correttamente senza eseguire la scansione di tutte le righe della tabella, vengono copiate in SQL Server solo le righe che soddisfano i criteri del predicato. In questo modo si risparmia molto tempo ed è necessario meno spazio per l'archiviazione temporanea quando il numero di saldi dei clienti < 200000 è ridotto rispetto al numero di clienti con saldi >= a 200000.

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

Selezionare un subset di colonne

Usare la distribuzione del predicato per migliorare le prestazioni se la query seleziona un subset di colonne da una tabella esterna.

In questa query, SQL Server avvia un processo MapReduce per pre-elaborare il file di testo delimitato di Hadoop in modo tale che solo i dati per le due colonne, customer.name e customer.zip_code, vengano copiati in SQL Server.

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

Distribuzione per operatori ed espressioni di base

SQL Server consente gli operatori e le espressioni di base seguenti per la distribuzione del predicato.

  • Operatori di confronto binari (<, >, =, !=, <>, >=, <=) per valori numerici, di data e di ora.
  • Operatori aritmetici (+, -, *, /, %).
  • Operatori logici (AND, OR).
  • Operatori unari (NOT, IS NULL, IS NOT NULL).

Gli operatori BETWEEN, NOT, IN e LIKE potrebbero essere distribuiti. Il comportamento effettivo dipende dal modo in cui Query Optimizer riscrive le espressioni degli operatori come serie di istruzioni che usano operatori relazionali di base.

La query in questo esempio include più predicati di cui è possibile eseguire il push in Hadoop. SQL Server è in grado di eseguire il push di processi MapReduce in Hadoop per eseguire il predicato customer.account_balance <= 200000. Anche l'espressione BETWEEN 92656 AND 92677 è costituita da operazioni binarie e logiche di cui è possibile eseguire il push in Hadoop. L'operatore AND logico in customer.account_balance AND customer.zipcode è un'espressione finale.

Con questa combinazione di predicati, i processi MapReduce possono eseguire interamente la clausola WHERE. Solo i dati che soddisfano i criteri SELECT vengono copiati in SQL Server.

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

Funzioni supportate per il pushdown

SQL Server consente le funzioni seguenti per il pushdown del predicato.

Funzioni di stringa

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

Funzioni matematiche

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

Funzioni generali

  • COALESCE *
  • NULLIF

* L'uso con COLLATE può impedire il pushdown in alcuni scenari. Per altre informazioni, vedere Regole di confronto.

Funzioni di data e ora

  • DATEADD
  • DATEDIFF
  • DATEPART

Sintassi che impedisce il pushdown

Le funzioni o la sintassi T-SQL seguenti impediscono il calcolo con distribuzione:

  • 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

Il supporto pushdown per la sintassi FORMAT e TRIM è stato introdotto in SQL Server 2019 (15.x) CU10.

Clausola di filtro con variabile

Se si specifica una variabile in una clausola di filtro, per impostazione predefinita si impedisce il pushdown della clausola di filtro. Ad esempio, se si esegue la seguente query, non sarà possibile il pushdown della clausola di filtro:

DECLARE @BusinessEntityID INT

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

Per ottenere il pushdown della variabile, è necessario abilitare la funzionalità degli hotfix di Query Optimizer. Questo può essere fatto in uno dei seguenti modi:

  • Livello di istanza: abilitare il flag di traccia 4199 come parametro di avvio per l'istanza
  • Livello database: nel contesto del database con oggetti esterni PolyBase eseguire ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Livello di query: usare l'hint per la query OPTION (QUERYTRACEON 4199) o OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Questa limitazione vale per l'esecuzione di sp_executesql. La limitazione si applica anche all'utilizzo di alcune funzioni nella clausola di filtro.

Nota: la possibilità di eseguire il pushdown della variabile è stata introdotta per la prima volta in SQL Server 2019 CU5.

Regole di confronto

Quando si lavora con dati con regole di confronto diverse, il pushdown potrebbe non essere possibile, gli operatori come COLLATE possono anche interferire con il risultato. Sono supportate regole di confronto uguali o regole di confronto binarie. Per ulteriori informazioni, vedere Come stabilire se si è verificato un pushdown.

Pushdown per i file Parquet

A partire da SQL Server 2022 (16.x), PolyBase ha introdotto il supporto per i file parquet. SQL Server è in grado di eseguire l'eliminazione di righe e colonne durante l'esecuzione del pushdown con parquet. Quando si utilizzano file Parquet, è possibile eseguire il push delle operazioni seguenti:

  • Operatori di confronto binari (>, >=, <=, <) per valori numerici, di data e di ora.
  • Combinazione di operatori di confronto (> AND <, >= AND <, > AND <=, <= AND >=).
  • Filtro in elenco (col1 = val1 OR col1 = val2 OR vol1 = val3).
  • IS NOT NULL sulla colonna.

La presenza di quanto segue impedisce il pushdown per i file parquet:

  • Colonne virtuali.
  • Confronto colonne.
  • Conversioni di tipo di parametri.

Tipi di dati supportati

  • Bit
  • TinyInt
  • SmallInt
  • BigInt
  • Reale
  • Float
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binario
  • DateTime2 (precisione predefinita e a 7 cifre)
  • Data
  • Ora (precisione predefinita e a 7 cifre)
  • Numerico *

* Supportato quando la scala dei parametri è allineata alla scala delle colonne o quando viene eseguito il cast esplicito del parametro su decimal.

Tipi di dati che impediscono il pushdown di parquet

  • Moneta
  • SmallMoney
  • Data/Ora
  • SmallDateTime

Esempi

Forzare la distribuzione

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

Disabilitare la distribuzione

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

Passaggi successivi

Vedi anche