Polybase in SQL Server 2016 CTP2

Una delle nuove funzionalità presenti in SQL Server 2016 è Polybase, il motore che consente di accedere a dati presenti in Hadoop attraverso comandi T-SQL. Polybase è presente già da diverse release all’intero di Microsoft APS (Analytical Platform System). Per i dettagli si veda il manuale di APS, scaricabile al seguente indirizzo: http://www.microsoft.com/en-us/download/details.aspx?id=45294

In questo articolo desciviamo l’installazione della feature, la configurazione e l’utilizzo attraverso query T-SQL.
L’ambiente utilizzato è costituito da due macchine virtuali, una per SQL Server 2016 ed una per Hadoop.
La macchina SQL è un Windows Server 2012 R2 con 8GB di RAM e 4 core virtuali, mentre la macchina Hadoop è un sistema CentOS con 4GB di RAM e 2 processori virtuali.
La virtual machine Hadoop è stata scaricata dal sito Hortonworks. Si tratta della HDP Sandbox 2.2.4 (si veda http://hortonworks.com/products/hortonworks-sandbox/ ).

Installazione

Per installare Polybase occorre rispettare alcuni requisiti minimi di sistema:

1. 64-bit SQL Server Evaluation edition
2. Microsoft .NET Framework 4.0.
3. Oracle Java SE RunTime Environment (JRE) version 7.51 or higher.
4. Minimum memory: 4GB
5. Minimum hard disk space: 2GB

Dopo aver installato Polybase, attivando la corrispondente “instance feature” durante l’installazione, troviamo tra i servizi Windows due servizi ad esso dedicati:

1. SQL Server Polybase Data Movement, che si occupa di creare, coordinare ed eseguire I piani di esecuzione paralleli verso le sorgenti dati
2. SQL Server Polybase Engine, che si occupa della comunicazione e trasferimento dati tra le fonti esterne e SQL Server

L’installazione di Polybase crea anche 3 database in SQL Server:

1. DWConfiguration
2. DWDiagnostics
3. DWQueue

Tali database sono indicati nella documentazione come database per uso interno.

Configurazione

Nella cartella \Polybase\Hadoop\Conf sono presenti alcuni file di configurazione:

1. Core-site.xml
2. Hdfs-site.xml
3. hive-site.xml
4. Mapred-site.xml
5. Yarn-site.xml

I file contengono opzioni di configurazione riguardanti, per esempio la sicurezza (utilizzando kerberos), oppure il block size, oppure, per quanto riguarda yarn, il classpath per le applicazioni (yarn.application.classpath), che risulta essere una configurazione necessaria per il collegamento a cluster basati su YARN.

Nel nostro caso, il classpath è il seguente:

$HADOOP_CONF_DIR,/usr/hdp/current/hadoop-client/*,/usr/hdp/current/hadoop-client/lib/*,/usr/hdp/current/hadoop-hdfs-client/*,/usr/hdp/current/hadoop-hdfs-client/lib/*,/usr/hdp/current/hadoop-yarn-client/*,/usr/hdp/current/hadoop-yarn-client/lib/*

Per utilizzare Polybase occorre configurare la connettività Hadoop attraverso il seguente comando T-SQL

Le opzioni relative a @configvalue sono elencate di seguito:

• 0: Disable Hadoop connectivity
• 1: Hortonworks HDP 1.3 on Windows Server
• 1: Azure blob storage (WASB[S])
• 2: Hortonworks HDP 1.3 on Linux
• 3: Cloudera CDH 4.3 on Linux
• 4: Hortonworks HDP 2.0 on Windows Server
• 4: Azure blob storage (WASB[S])
• 5: Hortonworks HDP 2.0 on Linux
• 6: Cloudera 5.1 on Linux
• 7: Hortonworks 2.1 and 2.2 on Linux
• 7: Hortonworks 2.2 on Windows Server
• 7: Azure blob storage (WASB[S])

Volendo configurare per la macchina virtuale preinstallata fornita da Hortonworks, utilizziamo il seguente commando:

Il cambiamento della configurazione richiede il comando RECONFIGURE e il riavvio dei due servizi di Polybase.

Utilizzare Polybase

Per utilizzare Polybase ed eseguire query T-SQL dobbiamo compiere i seguenti passaggi:

1. Creazione di una fonte dati esterna
2. Creazione di un file format
3. Creazione di uan external table che faccia riferimento ai dati Hadoop

La creazione di una fonte esterna avviene con il comando seguente:

L’unica tipologia di fonte dati esterna, attualmente supportata è HADOOP; tuttavia la location può essere sia un percorso HDFS, sia un percorso Azure storage blob.
Specificando l’opzione RESOURCE_MANAGER_LOCATION, l’ottimizzatore può scegliere di eseguirela query attraverso job mapreduce, realizzando una logica di predicate pushdown.

Creiamo quindi l’external source, specificando l’indirizzo IP della macchina Hadoop con la porta 8020:

Il passaggio successivo consiste nella creazione di un “file format”, necessario per la lettura dei dati da Hadoop.
I formati supportati attualmente da Polybase sono:

• Delimited text
• Hive RCFile
• Hive ORC

Le tre sintassi sono elencate qui sotto:

Utilizziamo il formato di testo, attraverso il comando T-SQL presente nella figura qui sotto:

Siamo finalmente pronti per la creazione della tabella esterna. Il comando CREATE EXTERNAL TABLE richiede il nome della tabella da creare, le colonne con i rispettivi tipi di dato, il riferimento ai dati esterni, il formato del file esterno.

Per ulteriori dettagli si veda https://msdn.microsoft.com/en-us/library/dn935021%28v=sql.130%29.aspx

Esiste poi la possibilità di forzare la creazione di statistiche sui dati esterni, attraverso il comando CREATE STATISTICS. In generale, l’ottimizzatore crea le statistiche automaticamente, ma in alcuni casi si può rendere necessaria la creazione manuale. Si veda https://msdn.microsoft.com/en-us/library/ms188038%28v=sql.130%29.aspx

Utilizziamo, per la creazione della tabella esterna, un file contenente le quotazioni azionarie del NYSE. Le figure seguenti mostrano la location del file in Hadoop e la tabella HCatalog create nel sistema:

Creiamo quindi la tabella:

Una volta creata la tabella, essa è visibile in management studio, tra le tabelle esterne:

Eseguiamo ora alcune query. Per prima cosa contiamo le righe della tabella esterna:

La query impiega 3 secondi per l’esecuzione.

Un altro esempio: contiamo il numero di righe per ciascuna azione; inoltre calcoliamo il volume di negoziazione medio.

In questo caso le righe ritornate sono state 1734 e il tempo impiegato è stato 7 secondi.

Il piano di esecuzione in entrambi i casi è molto semplice. Esso mostra come la query sia risolta da Polybase in remoto

Proviamo ora con una query più complessa, nella quale calcoliamo la variazione giornaliera del prezzo di ciascuna azione.

In questo caso l’execution plan mostra come la query sia risolta in parte sul sistema remoto (estrazione dati) ed in parte in locale.

Le proprietà per la prima remote query mostra un numero di righe pari a 812989, dimostrando che la parte eseguita su Hadoop si limita ad estrarre i dati.

Vediamo ora che effetto ha la query, lato Hadoop, in termini di I/O.
Utilizzando Ambari possiamo monitorare la situazione, attivando il servizio di registrazione delle metriche. Nel caso della query precedente abbiamo due picchi di I/O di lettura in corrispondenza della query. Ciò significa che la query nella CTE è eseguita su Hadoop 2 volte.

Conclusioni

Polybase consente di accedere ai dati Hadoop attraverso un’interfaccia T-SQL. Attualmente, nella CTP2, le feature sono ancora limitate (per es. non è possibile scrivere su Hadoop, ma solo leggere dati). Si tratta in ogni caso di uno strumento molto interessante e potente per impostare un’architettura di tipo data lake.