Condividi tramite

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.

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.

+ Oracle supporta il pushdown per i join, ma potrebbe essere necessario creare statistiche sulle colonne di join per ottenere il pushdown.

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 i due provider in termini di calcolo pushdown.

Per usare la funzionalità di pushdown per il calcolo con Hadoop, il cluster Hadoop di destinazione deve avere i componenti principali: HDFS, YARN e MapReduce, con il server di cronologia processi 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ò trasferire join, proiezioni, aggregazioni e filtri verso fonti di dati esterne, sfruttando il calcolo remoto e limitando i dati inviati sulla rete.

Join con pushdown

PolyBase può facilitare il pushdown dell'operatore di join quando si uniscono le due tabelle esterne sulla stessa origine dati esterna, migliorando notevolmente le prestazioni.

Quando l'origine dati esterna esegue il join, riduce la quantità di spostamento dei dati e migliora le prestazioni delle query. Senza il pushdown del join, SQL Server deve importare localmente i dati da entrambe le tabelle in tempdb e quindi eseguire il join.

Nel caso di join distribuiti (unione di una tabella locale a una tabella esterna), a meno che il filtro non si applichi alla tabella esterna unita in join, SQL Server deve inserire tutti i dati dalla tabella esterna 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, che comporta la lettura di tutti i dati dalla tabella esterna.

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

Poiché il join utilizza la colonna E.id della tabella esterna, quando si aggiunge una condizione di filtro a tale colonna, SQL Server può spingere il filtro, riducendo il numero delle 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 queste espressioni e operatori di base per il pushdown 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, INe LIKE possono essere inseriti in basso a seconda del modo in cui Query Optimizer riscrive le espressioni dell'operatore come una serie di istruzioni usando 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 seguenti funzioni per il predicate pushdown:

Funzioni 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

Queste funzioni T-SQL o gli elementi della sintassi 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

Quando si specifica una variabile in una clausola di filtro, per impostazione predefinita SQL Server non esegue il push della clausola di filtro. Ad esempio, la query seguente non spinge verso il basso la clausola di filtro:

DECLARE @BusinessEntityID INT

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

Per abilitare il pushdown della variabile, attivare la funzionalità di correzioni rapide del Query Optimizer utilizzando uno dei seguenti metodi:

  • Livello di istanza: attivare il flag di traccia 4199 come parametro di avvio per l'istanza.
  • Livello del Database: nel contesto del database che contiene gli oggetti esterni PolyBase, devi eseguire ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON.
  • Livello di query: utilizzare il suggerimento di query OPTION (QUERYTRACEON 4199) o OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Questa limitazione si applica all'esecuzione di sp_executesql e ad alcune funzioni nella clausola di filtro.

SQL Server 2019 CU5 ha introdotto per la prima volta la funzione di ottimizzazione delle variabili.

Per altre informazioni, vedere sp_executesql.

Regole di confronto

Il pushdown potrebbe non funzionare con dati con regole di confronto diverse. Gli operatori come COLLATE possono anche interferire con il risultato. SQL Server supporta regole di confronto uguali o regole di confronto binarie. Per altre informazioni, vedere Come stabilire se si è verificato un pushdown esterno.

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.

Origini dati esterne supportate

Il pushdown Parquet è supportato per le origini dati esterne seguenti:

  • Archiviazione di oggetti compatibile con S3
  • Blob Storage di Azure
  • Azure Data Lake Storage Gen2

Per informazioni dettagliate sulla configurazione, vedere:

Operazioni di pushdown

SQL Server può delegare queste operazioni con file parquet:

  • 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 su una colonna.

Questi fattori impediscono 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)
  • date
  • time (precisione predefinita e a 7 cifre)
  • Numerico *

* Supportato quando la scala dei parametri è allineata alla scala delle colonne o quando il parametro viene eseguito in modo esplicito su decimale.

Tipi di dati che impediscono il pushdown di parquet

  • denaro
  • smallmoney
  • datetime
  • smalldatetime

Eliminazione delle partizioni con strutture di cartelle

PolyBase può usare strutture di cartelle per l'eliminazione delle partizioni, riducendo la quantità di dati analizzati durante le query. Quando si organizzano i file parquet in cartelle gerarchica,ad esempio per anno, mese o altre chiavi di partizionamento, PolyBase può ignorare intere cartelle che non corrispondono ai predicati di query.

Ad esempio, se si strutturano i dati come segue:

/data/year=2024/month=01/*.parquet
/data/year=2024/month=02/*.parquet
/data/year=2025/month=01/*.parquet

È possibile interrogare partizioni specifiche utilizzando i wildcard in OPENROWSET o nei percorsi delle tabelle esterne.

-- Query only January 2025 data
SELECT *
FROM OPENROWSET(
    BULK '/data/year=2025/month=01/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) AS [data];

Per l'eliminazione dinamica delle cartelle, eseguire una query su un percorso di cartella più ampio e usare filepath() predicati per eliminare le partizioni in fase di esecuzione:

SELECT
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK '/data/year=*/month=*/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) WITH (
    customer_id INT,
    amount DECIMAL(10, 2)
) AS [r]
WHERE
    r.filepath(1) = '2025'
    AND r.filepath(2) = '01'
GROUP BY
    r.filepath(1),
    r.filepath(2);

Questo approccio combina l'eliminazione delle partizioni a livello di cartella con il pushdown a livello di file parquet per ottenere prestazioni ottimali delle query. Per un'esercitazione completa sull'esecuzione di query su file Parquet con modelli di cartella, vedere Virtualizzare un file Parquet in un archivio oggetti compatibile con S3 con PolyBase.

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

  • Last updated on