Calcoli con distribuzione in PolyBase

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.

Abilitare il calcolo con distribuzione

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

• Abilitare il calcolo con distribuzione in Hadoop
• Configurare PolyBase per l'accesso a dati esterni in Oracle
• Configurare PolyBase per l'accesso a dati esterni in Teradata
• Configurare PolyBase per l'accesso a dati esterni in MongoDB
• Configurare PolyBase per l'accesso a dati esterni con i tipi generici ODBC
• Configurare PolyBase per l'accesso a dati esterni in SQL Server

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

* 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.

+ Oracle supporta il pushdown per i join, ma potrebbe essere necessario creare statistiche sulle colonne di join per ottenere 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 calcolo pushdown 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.

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 ci sia un filtro sulla tabella esterna 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 pushdown 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 map-reduce 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 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

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 di database: nel contesto del database con gli 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.

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

Regole di confronto

Il pushdown potrebbe non essere possibile con dati con regole di confronto diverse. 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 pushdown delle seguenti operazioni:

• 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);

Contenuto correlato

• Per altre informazioni su PolyBase, vedere Introduzione alla virtualizzazione dei dati con PolyBase
• Come stabilire se si è verificato un pushdown esterno