Polybase in SQL Server 2016 CTP2

Polybase In Sql Server 2016 Ctp2 00

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
Polybase In Sql Server 2016 Ctp2 01

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

Polybase In Sql Server 2016 Ctp2 02

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:

Polybase In Sql Server 2016 Ctp2 03

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:

Polybase In Sql Server 2016 Ctp2 04

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:

Polybase In Sql Server 2016 Ctp2 05

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:

Polybase In Sql Server 2016 Ctp2 06

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

Polybase In Sql Server 2016 Ctp2 07

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.

Polybase In Sql Server 2016 Ctp2 08

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:

Polybase In Sql Server 2016 Ctp2 09
Polybase In Sql Server 2016 Ctp2 10

Creiamo quindi la tabella:

Polybase In Sql Server 2016 Ctp2 11

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

Polybase In Sql Server 2016 Ctp2 12

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

Polybase In Sql Server 2016 Ctp2 13

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.

Polybase In Sql Server 2016 Ctp2 14

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

Polybase In Sql Server 2016 Ctp2 15

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

Polybase In Sql Server 2016 Ctp2 15 2

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

Polybase In Sql Server 2016 Ctp2 16

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.

Polybase In Sql Server 2016 Ctp2 17

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.

Polybase In Sql Server 2016 Ctp2 18
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.

Avatar User 2 1549298890

Alessandro Rezzani

Sono un consulente senior nell’ambito della Business Intelligence, specializzato in analisi di Big Data e tecniche di Analisi Predittiva. Nel 2016 ho fondato Dataskills, presto diventata azienda di riferimento nel territorio italiano per soluzioni di Data Science. Sono anche ricercatore e professore presso l’Università Bocconi di Milano.
Leggi la mia Biografia

Leave A Comment

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

Iscrivi alla newsletter














    Dichiaro di aver letto ed accetto l’informativa sulla privacy

    Avatar User 2 1549298890

    Alessandro Rezzani

    Sono un consulente senior nell’ambito della Business Intelligence, specializzato in analisi di Big Data e tecniche di Analisi Predittiva. Nel 2016 ho fondato Dataskills, presto diventata azienda di riferimento nel territorio italiano per soluzioni di Data Science. Sono anche ricercatore e professore presso l’Università Bocconi di Milano.
    Leggi la mia Biografia