[SQL Server] PolyBase : introduction et utilisation avec Hadoop

Ce billet présente PolyBase et y donne quelques exemples d’utilisation.

Dans cet article, nous allons décrire comment installer cette fonctionnalité, sa configuration et son fonctionnement. L’environnement utilisé est constitué de deux machines virtuelles, une pour SQL Server 2016 sur Windows Server 2012 R2 avec 8 Go de RAM et 2 CPUs de 4 cœurs, et une pour Hadoop sur Linux CentOS avec 4 Go de RAM et 2 CPUs. La machine virtuelle Hadoop a été configurée à partir d’une Sandbox Hortonworks HDP, comme vu ici.

Avant de commencer

Présentation de PolyBase

L’une des nouvelles fonctionnalités de SQL Server 2016 est Polybase, le moteur qui donne accès à Hadoop grâce notamment à des commandes T-SQL. Polybase est présent depuis plusieurs versions de Microsoft APS (Analytical Platform System) avant son intégration avec SQL Server.


L'architecture de PolyBase est comparable à celle de Hadoop. En effet, PolyBase est constitué :
  • D'un nœud maître, appelé Control Node, qui est comparable au NameNode de Hadoop. Il s'agit d'une instance SQL Server utilisée comme hôte pour le moteur PolyBase chargé de contrôler toutes les interactions. En tant qu'interface entre les applications clientes et les sources externes, il a pour rôle de parser des requêtes, optimiser des plans d'exécution, contrôler le plan d'exécution des requêtes et restituer les résultats des requêtes. 
    
  • D'un ou plusieurs nœuds de calcul, appelés Compute Nodes, équivalents au DataNode de Hadoop. Ils permettent de gérer le transfert de données de Hadoop vers SQL Server. C'est le service Data Movement de Polybase qui permet l'exécution des nœuds de calcul. A noter qu'en cas de requête complexe et coûteuse à traiter, les nœuds de calcul peut être amenés à se répartir la charge de leur exécution en parallèle, sous la supervision du nœud de contrôle.
    

Pour SQL Server, les bénéfices sont importants, car PolyBase peut servir de passerelle avec le monde Hadoop, avec notamment la possibilité de permettre à ses outils de BI (PowerBI, SSRS,…) de directement interagir avec Hadoop, ou à son moteur de stockage OLTP de bénéficier d’un stockage encore plus flexible et étendu (i.e., HDFS, WABS) et de performances scalables .

Installation et configuration

Some minimum system requirements need to be met to be able to install Polybase:

  1. SQL Server 64 bits.
  2. Microsoft .NET Framework 4.0 ou plus.
  3. Oracle Java SE RunTime Environment (JRE) version 7.51 ou plus.
  4. 4 Go de RAM minimum.
  5. 2 Go d’espace disque au minimum.


Après installation de PolyBase, on peut jeter un coup d’oeil dans la liste des services Windows, et s’assurer de la presence de:

  1. SQL Server Polybase Data Movement, qui permet de créer, coordonner et executer des plans parallèles.
  2. SQL Server Polybase Engine, qui permet de gérer les transferts de données entre les sources externs et SQL Server.


L’installation de Polybase amène également à la creation de 3 bases de données SQL Server:

  • DWConfiguration
  • DWDiagnostics
  • DWQueue

Elles sont destinées à un usage interne, par le moteur SQL Server.

Côté configuration, des fichiers de configuration sont deposes au sein du dossier Polybase/Hadoop/Conf :

  • Core-site.xml
  • Hdfs-site.xml
  • hive-site.xml
  • Mapred-site.xml
  • Yarn-site.xml

Ces fichiers contiennent des options de configuration concernant notamment la sécurité (via Kerberos), la taille de bloc, ou, encore, le classpath pour les applications (yarn.application.classpath), nécessaire pour la connexion à des clusters YARN.

Dans notre cas, le classpath peut être configuré comme suit :

$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/*

Pour pouvoir utiliser PolyBase, il faut préalablement configurer la connexion à Hadoopvia l’ordre sp_configure, sur SQL Server :

--List all of the configuration options  
sp_configure  
[;]  
--Configure Hadoop connectivity  
sp_configure [ @configname = ] 'hadoop connectivity',  
             [ @configvalue = ] { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 }  
[;]  
RECONFIGURE  
[;]  
GO

A la date d’écriture de ce billet, voici ci-dessous les valeurs possibles pour @configvalue:

  • 0: Désactivation de la connexion à Hadoop.
  • 1: Hortonworks HDP 1.3 sur Windows Server.
  • 1: Azure Blob Storage (WASB[S]).
  • 2: Hortonworks HDP 1.3 sur Linux.
  • 3: Cloudera CDH 4.3 sur Linux.
  • 4: Hortonworks HDP 2.0 sur Windows Server
  • 4: Azure blob storage (WASB[S]).
  • 5: Hortonworks HDP 2.0 sur Linux.
  • 6: Cloudera 5.1 sur Linux.
  • 7: Hortonworks HDP 2.1 à 2.5 sur Linux.
  • 7: Hortonworks HDP 2.1 à 2.3 sur Windows Server.
  • 7: Azure Blob Storage (WASB[S]).


Dans notre cas, nous utiliserons une installation de Hortonworks HDP 2.4 basée sur Linux.

EXEC sp_configure 'hadoop connectivity', 7
GO
RECONFIGURE
GO

Utilisation de PolyBase

Afin d’utiliser PolyBase et d’y effectuer quelques operations, il faut grosso-modo suivre la roadmap générique suivante:

  • Création d’une source de données Hadoop externe.


La syntaxe de creation d’une source de données externe pour SQL Server (et SQL Data Warehouse) est la suivante :

-- PolyBase only:  Hadoop cluster as data source   
-- (on SQL Server)  
CREATE EXTERNAL DATA SOURCE data_source_name  
    WITH (   
        TYPE = HADOOP,
        LOCATION = 'hdfs://NameNode_URI[:port]'  
        [, RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]' ]  
        [, CREDENTIAL = credential_name ]
    )
[;]  
-- PolyBase only: Azure Storage Blob as data source   
-- (on SQL Server and Azure SQL Data Warehouse)  
CREATE EXTERNAL DATA SOURCE data_source_name  
    WITH (   
        TYPE = HADOOP,  
        LOCATION = 'wasb[s]://container@account_name.blob.core.windows.net'
        [, CREDENTIAL = credential_name ]
    )  
[;]
GO

L’utilisation de l’option RESOURCE_MANAGER_LOCATION permet à l’optimiseur de traitements PolyBase de pré-exécuter une requête avant traitement par PolyBase, dans le but d’améliorer le TCO.

Pour créer notre source d edonnées externe HDP2 située sur un espace HDFS et accessible via le port 8020, nous pouvons procéder comme suit:

 CREATE EXTERNAL DATA SOURCE HDP2 WITH ( TYPE = HADOOP, LOCATION='hdfs://192.168.16.1:8020' ); GO 
  • Creation d’un fichier externe.

La creation d’un fichier externe est necessaire pour la lecture des données de Hadoop.. A l’heure de l’écriture du billet, les formats actuellement supportés par PolyBase sont:

  • Texte.
  • Hive RCFile.
  • Hive ORC.
  • Parquet.

Et voici leurs syntaxes de creation ci-dessous:

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
     [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  
--Create an external file format for ORC files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = ORC  
     [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.DefaultCodec'      }  
    ]);  
--Create an external file format for RCFILE.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = RCFILE,  
    SERDE_METHOD = {  
        'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'  
      | 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'  
    }  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec' ]);  
--Create an external file format for DELIMITED TEXT files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    [ , DATA_COMPRESSION = {  
           'org.apache.hadoop.io.compress.GzipCodec'  
         | 'org.apache.hadoop.io.compress.DefaultCodec'  
        }  
     ]);  
<format_options>; ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter  
    | DATE_FORMAT = datetime_format  
    | USE_TYPE_DEFAULT = { TRUE | FALSE }  
} 
GO

Dans notre cas, on utilise un fichier texte en procédant comme suit, en T-SQL;

CREATE EXTERNAL FILEFORMAT TSV_file
WITH (
	FORMAT_TYPE = DELIMITEDTEXT,
	FORMAT_OPTIONS (
					FIELD_TERMINATOR = '\t',
					DATE_FORMAT = 'MM/dd/yyyy'),
					DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
 );
GO

Quelques remarques générales:

  • PolyBase ne supporte que l’encodage UTF-8.
  • Le nombre maximum de fichiers à créer et recommandé est de 30 000, sachant que le nombre de requêtes concurrentes supportées par PolyBase est de 32, ce qui signifie que jusqu’à 33 000 fichiers peuvent être lus et traités à partir d’une source de données externe.
  • L’utilisation de la compression permet d’améliorer la performance des transferts de données. Toutefois, la contrepartie est l’usage intensif des CPUs. Si le fichier est trop volumineux, il est recommandé de le splitter en plusieurs fichiers stockés au sein d’un même répertoire.
  • Création d’une table externe liée à Hadoop.

La dernière étape est la creation d’une table externe. La syntaxe T-SQL est la suivante:

-- Create a new external table  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition>; [ ,...n ] )  
    WITH (   
        LOCATION = 'folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name  
        [ , <reject_options>; [ ,...n ] ]  
    )  
[;]  
 <reject_options> ::=  
{  
    | REJECT_TYPE = value | percentage  
    | REJECT_VALUE = reject_value  
    | REJECT_SAMPLE_VALUE = reject_sample_value  
} 
GO
La création de la structure de la table externe avec les données d'un fichier texte de stocks, localisé au sein du dossier /data de la source externe :
CREATE EXTERNAL TABLE stock
(
	sname VARCHAR(50),
	CO VARCHAR(50) NULL,
	[DATE] VARCHAR(50) NULL,
	div VARCHAR(200) NULL
)
WITH (
	LOCATION='/data/stock.tbl',
	DATA_SOURCE = HDP2,
	FILE_FORMAT = TSV_file
)
GO

Quelques remarques générales:

  • Seuls les ordres DDL suivants sont supportés sur les tables externes : CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW et DROP VIEW.
  • Bien que les statistiques soient automatiquement créées (par défaut) lors de la création d’une table externe, elles ne peuvent pas être mises à jour. Pour contourner cela, il faut donc procéder à un DROP/CREATE STATISTICS.

Au sein de l’explorateur d’objets de SQL Server Management Studio (SSMS), suite au travail préliminaire effectué plus haut, on peut apercevoir les objets créés au sein de la base de données DWConfiguration :

  • La table externe :


  • La source de données externe et le fichier texte :


On peut également vérifier si la table externe est bien remplie, en déterminant son nombre de lignes :


Ou en jetant un œil à son contenu :



On peut d’ailleurs noter, via le plan d’exécution de la requête lancée, que la requête a été traitée à distance par PolyBase :


Pour aller plus loin…

Nous avons vu dans ce billet que PolyBase permettait d’accéder à des sources externes Hadoop Avec le temps, et les évolutions futures de cette technologie, d’autres billets viendront enrichir la section dédiée au sein du blog.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s