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.
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 :
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.
|
1 2 3 4 5 6 7 8 |
USE [AdventureWorksDW2017] GO CREATE MASTER KEY; CREATE DATABASE SCOPED CREDENTIAL ABSCredential WITH IDENTITY = 'MyID' , SECRET = '<azure_storage_account_key>' |
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.
|
1 2 3 4 5 6 7 |
CREATE EXTERNAL DATA SOURCE [AZBSDS] WITH ( TYPE = Hadoop, LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net', CREDENTIAL = ABSCredential ); |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ';', STRING_DELIMITER = '', FIRST_ROW = 2, DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss', USE_TYPE_DEFAULT = FALSE ) ); GO |
Passons maintenant à la création de notre table externe appartenant au schéma dédié « ext » et pointe vers le dossier « sales » sur WABS :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE SCHEMA [ext]; GO CREATE EXTERNAL TABLE [ext].[FactInternetSales]( [ProductKey] [int] NOT NULL, [OrderDateKey] [int] NOT NULL, [DueDateKey] [int] NOT NULL, [ShipDateKey] [int] NOT NULL, [CustomerKey] [int] NOT NULL, [PromotionKey] [int] NOT NULL, [CurrencyKey] [int] NOT NULL, [SalesTerritoryKey] [int] NOT NULL, [SalesOrderNumber] [nvarchar](20) NOT NULL, [SalesOrderLineNumber] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderQuantity] [smallint] NOT NULL, [UnitPrice] [money] NOT NULL, [ExtendedAmount] [money] NOT NULL, [UnitPriceDiscountPct] [float] NOT NULL, [DiscountAmount] [float] NOT NULL, [ProductStandardCost] [money] NOT NULL, [TotalProductCost] [money] NOT NULL, [SalesAmount] [money] NOT NULL, [TaxAmt] [money] NOT NULL, [Freight] [money] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [CustomerPONumber] [nvarchar](25) NULL, [OrderDate] [datetime] NULL, [DueDate] [datetime] NULL, [ShipDate] [datetime] NULL ) WITH (LOCATION='/sales/', DATA_SOURCE = AZBSDS, FILE_FORMAT = TextFileFormat, REJECT_TYPE = VALUE, REJECT_VALUE = 0 ); |
Pour effectuer une simple sélection des données présentes sur notre Azure Blob Storage, procéder comme suit :
|
1 |
SELECT * FROM [ext].[FactInternetSales] |
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 :
|
1 2 3 4 |
CREATE TABLE [stg].[ImportedFactInternetSales] WITH (DISTRIBUTION = HASH(CustomerKey)) AS SELECT * FROM [ext].[FactInternetSales] OPTION (LABEL = 'Chargement des ventes Internet') |
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 :
- Microsoft .NET Framework 4.5
- Oracle Java SE Runtime Environment (JRE), à partir de la version 7.51
- Mémoire minimale : 4Go, recommandée : 16Go
- Espace libre requis sur le disque : 2Go.
- TCP/IP doit être activé
La fonctionnalité Polybase va être déployée via l’installeur SQL Server.
Polybase peut utiliser deux modes d’accès au système de stockage pour les lectures : Standalone et Scale-Out.
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.
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 :
|
1 |
SELECT SERVERPROPERTY ('IsPolybaseInstalled') |
3 BDD devraient être présentes :
Sur SQL Server, il faudra spécifier la connectivité. Dans le cas présent, on utilisera d’Azure Blob Storage.
|
1 2 3 4 |
EXEC sp_configure 'hadoop connectivity', 4; GO RECONFIGURE; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [AdventureWorksDW2017] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password_encryption>'; GO CREATE DATABASE SCOPED CREDENTIAL ABSCredential WITH IDENTITY = 'MyID', SECRET = '<azure_storage_account_key>' ; GO |
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…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
USE [AdventureWorksDW2017] GO CREATE EXTERNAL DATA SOURCE [AZBSDS] WITH ( TYPE = Hadoop, LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net', CREDENTIAL = ABSCredential ); CREATE EXTERNAL FILE FORMAT TextFileFormat WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ';', STRING_DELIMITER = '', DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss', USE_TYPE_DEFAULT = FALSE ) ); GO CREATE SCHEMA [ext]; GO CREATE EXTERNAL TABLE [ext].[FactInternetSales]( [ProductKey] [int] NOT NULL, [OrderDateKey] [int] NOT NULL, [DueDateKey] [int] NOT NULL, [ShipDateKey] [int] NOT NULL, [CustomerKey] [int] NOT NULL, [PromotionKey] [int] NOT NULL, [CurrencyKey] [int] NOT NULL, [SalesTerritoryKey] [int] NOT NULL, [SalesOrderNumber] [nvarchar](20) NOT NULL, [SalesOrderLineNumber] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderQuantity] [smallint] NOT NULL, [UnitPrice] [money] NOT NULL, [ExtendedAmount] [money] NOT NULL, [UnitPriceDiscountPct] [float] NOT NULL, [DiscountAmount] [float] NOT NULL, [ProductStandardCost] [money] NOT NULL, [TotalProductCost] [money] NOT NULL, [SalesAmount] [money] NOT NULL, [TaxAmt] [money] NOT NULL, [Freight] [money] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [CustomerPONumber] [nvarchar](25) NULL, [OrderDate] [datetime] NULL, [DueDate] [datetime] NULL, [ShipDate] [datetime] NULL ) WITH (LOCATION='/sales/', DATA_SOURCE = AZBSDS, FILE_FORMAT = TextFileFormat, REJECT_TYPE = VALUE, REJECT_VALUE = 0 ); SELECT * FROM [ext].[FactInternetSales] |
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 :
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'allow polybase export', 1; GO RECONFIGURE; GO |
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 » :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
CREATE EXTERNAL TABLE [dbo].[FactInternetSales2018] ( [ProductKey] [int] NOT NULL, [OrderDateKey] [int] NOT NULL, [DueDateKey] [int] NOT NULL, [ShipDateKey] [int] NOT NULL, [CustomerKey] [int] NOT NULL, [PromotionKey] [int] NOT NULL, [CurrencyKey] [int] NOT NULL, [SalesTerritoryKey] [int] NOT NULL, [SalesOrderNumber] [nvarchar](20) NOT NULL, [SalesOrderLineNumber] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderQuantity] [smallint] NOT NULL, [UnitPrice] [money] NOT NULL, [ExtendedAmount] [money] NOT NULL, [UnitPriceDiscountPct] [float] NOT NULL, [DiscountAmount] [float] NOT NULL, [ProductStandardCost] [money] NOT NULL, [TotalProductCost] [money] NOT NULL, [SalesAmount] [money] NOT NULL, [TaxAmt] [money] NOT NULL, [Freight] [money] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [CustomerPONumber] [nvarchar](25) NULL, [OrderDate] [datetime] NULL, [DueDate] [datetime] NULL, [ShipDate] [datetime] NULL ) WITH (LOCATION='/sales/archive/2018/', DATA_SOURCE = AZBSDS, FILE_FORMAT = TextFileFormat, REJECT_TYPE = VALUE, REJECT_VALUE = 0); INSERT INTO [dbo].[FactInternetSales2018] SELECT [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate] FROM [dbo].[FactInternetSales] WHERE [OrderDateKey] BETWEEN 20180101 AND 20181231 |
Pour visualiser les fichiers d’export, j’ai utilisé Azure Storage Explorer :
Fonctionnalités 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
- 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.
