Polybase

PolyBase permet depuis une instance SQL Server (2016 minimum), SQL Data Warehouse et APS (Analytics Platform System) d’intégrer et d’interroger des sources de données, fichiers, provenant d’Hadoop ou de Azure Blob Storage (génération 1 et 2 donc Azure Data Lake inclus). Il est aussi possible de croiser les données provenant de ces fichiers avec des tables relationnelles SQL Server. Toutes ces opérations se font en Transact-SQL. C’est une table externe qui assure la connexion à une source Hadoop ou Azure Blob Storage. En dernier lieu, il est également possible de faire des exports vers le système de stockage Hadoop ou Azure Blob Storage.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - polybase0000 Polybase

Polybase dans SQl Data Warehouse

Dans la continuité des articles sur SQL Data Warehouse, nous allons faire une halte sur l’utilisation de Polybase dans ce service. Pour rappel, SQL Data Warehouse est dédié aux entrepôts de données décisionnels en mode PaaS avec un moteur MPP. Il n’a absolument rien à installer, ni à activer au niveau instance. Voici les éléments de configuration pour la création d’une source externe qui pointe vers Azure Blob Storage :

En premier lieu, RDV dans le portail Azure, pour relever la clé d’accès à Azure Blob Storage :

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - Polybase_ABS-Key Polybase

Se connecter à SQL Data Warehouse via SSMS et spécifier le contexte de la base de données dans laquelle sera par la suite créée notre table externe. Dans cette base de données, on va générer une master key pour crypter le credential créé par la suite pour accéder à Azure Blob Storage. Remplacer <azure_storage_account_key> par la clé d’accès d’Azure Blob Storage.

La première chose qui saute aux yeux, c’est qu’Azure Blob Storage est estampillé Hadoop, étrange… Historiquement Polybase a été pensé pour accéder à Hadoop, tout simplement. En revanche, concernant l’emplacement, il n’y a pas d’ambiguïté sur la source, WASB étant l’acronyme de Windows Azure Storage Blob.

L’étape qui suit défini le format de notre source, à savoir un fichier texte ayant les caractéristiques suivantes :

  • Séparateur
  • Ligne d’entête à exclure (ce paramètre n’existe que sur SQL Data Warehouse)
  • Délimiteur de texte
  • Format des dates dans les fichiers
  • Non remplacement des valeurs non renseignées (conservation des NULL)

Documentation MS : https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-2017

Passons maintenant à la création de notre table externe appartenant au schéma dédié « ext » et pointe vers le dossier « sales » sur WABS :

Pour effectuer une simple sélection des données présentes sur notre Azure Blob Storage, procéder comme suit :

Pour intégrer ces données dans une table créée à la volée avec une distribution par hachage (colonne « CustomerKey »), procéder comme suit :

Pour ceux qui n’ont pas eu vent de l’épisode précédent sur la distribution des tables.

L’intégration de données via Polybase est vivement recommandée sur SQL Data Warehouse. Il existe d’autres méthodes comme SSIS ou encore BCP mais dans le cas de Polybase, on passe directement par les nœuds de calcul, ce qui élimine le goulot d’étranglement sur le nœud de contrôle. Il y a néanmoins quelques précautions à prendre : privilégier peu de gros fichiers (VS beaucoup de petits fichiers), et non compressés pour bénéficier du parallélisme (multi-thread).

Polybase dans SQL Server 2017

La fonctionnalité Poybase est par défaut présente sur le système distribué SQL Data Warehouse. Concernant SQL Server, en termes d’installation, il y a quelques différences. Mais avant toute chose, voici la liste des pré-requis :

La fonctionnalité Polybase va être déployée via l’installeur SQL Server.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - Polybase_011-908x1024 Polybase

Polybase peut utiliser deux modes d’accès au système de stockage pour les lectures : Standalone et Scale-Out.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - Polybase_02 Polybase

En mode Scale-out, il y’a deux types de nœuds un peu à l’image de SQL Data Warehouse : la tête et les jambes.

  • Nœud principal : L’édition Enterprise est requise, il est unique et constitue le point d’entrée des requêtes. Il orchestre leur exécution sur les nœuds de calculs pour la parallélisation des lectures
  • Nœud(s) de calcul : Disponible sur toutes les éditions, les nœuds de calculs permettent d’effectuer les lectures parallélisées.

Tous les nœuds portent le serve DMS (Data Movement Service). Il s’agit d’un service en charge du déplacement de données entre les nœuds pour la résolution d’une requête. Pour limiter le déplacement de données, le choix de la distribution d’une table est déterminante.

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - polybase-scale-out-groups Polybase

Le mode Scale-out imposent quelques contraintes :

  • Les instances doivent faire partie du même domaine.
  • Elles doivent utiliser la même version du moteur.
  • Elle doivent être configurée avec un compte de service Active Directory.

Si tant est que tout ait été installé correctement, la requête ci-dessous devrait renvoyer 1, s’agissant de vérifier si Polybase est bien présent :

3 BDD devraient être présentes :

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - polybase_bdd-300x280 Polybase

Sur SQL Server, il faudra spécifier la connectivité. Dans le cas présent, on utilisera d’Azure Blob Storage.

Voici la liste des sources disponibles :

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

Contrairement à SQL Data Warehouse, il est nécessaire de spécifier un mot de passe pour la création de la master key.

En revanche, la création de la table externe est quasiment identique à l’exception du paramètre FIRST_ROW qui a disparu, n’étant pas supporté sur cette version de SQL Server 2017. Il le saura vraisemblablement sur SQL Server 2019, on l’a vu passer dans la preview

Export depuis Polybase

Comme évoqué dans l’introduction, il est aussi possible de réaliser des exports via Polybase, ce que nous allons faire sur Azure Blob Storage. La première étape consiste à activer la fonctionnalité d’export dans les options avancées de SQL Server :

La seconde étape nous permet de procéder à un export en injectant le résultat d’une requête dans une table externe créée précédemment qui pointe vers la destination « sales/archive/2018 » :

Pour visualiser les fichiers d’export, j’ai utilisé Azure Storage Explorer :

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - export_ase Polybase

Fonctionnalités Polybase

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - synt_polybasepng Polybase

Plus d’informations : https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary?view=sql-server-2017

De nouvelles options d’accès en bloc (BULK INSERT et OPENROWSET(BULK…)) permettent d’accéder directement aux données à partir d’un fichier spécifié au format CSV et à partir de fichiers stockés dans Stockage Blob Azure avec la nouvelle option BLOB_STORAGE de EXTERNAL DATA SOURCE.

À partir de l’adresse <https://docs.microsoft.com/fr-fr/sql/database-engine/whats-new-in-sql-server-2017?view=sql-server-2017>

Auteur

Expert BI DAX PowerPivot Power BI Décisionnel Business Intelligence - cartoon_250px-150x150 Polybase
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, speaker aux SQL Saturday et Journées SQL Server et enfin formatrice Orsys, je dispose d'une vision d'ensemble des systèmes d'information, d'une appétence pour l'industrialisation et d'un souci permanent de la performance.
Posts liés

Leave a Reply

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

error: Contenu protégé !