SQL Server 2022 propose deux nouvelles fonctionnalités, qui combinées, apportent un levier supplémentaire à l’exploitation de données non traditionnelles : la capacité de connexion à du stockage compatible S3 et le support des fichiers parquets (des fichiers qui seront produits par des systèmes externes à SQL Server, sous la forme de fichiers plats gérant les données de manière verticale, structurées et compressées).

Le stockage S3 peut être, bien entendu, le service d’AWS, mais dans cet article nous proposerons le déploiement d’un stockage S3 customisé avec le produit OpenSource « Min.IO ».

Déployer son stockage S3 avec Min.IO :

Il existe de nombreuses solutions de stockage compatible S3 à part le service AWS. Les SAN/NAS modernes proposent souvent un point d’accès S3, par exemple. On peut sinon concevoir son service avec une solution logicielle.

La solution Min.IO est :

• OpenSource (GNU AGPL v3)
• Multi-plateforme (binaires disponibles pour Linux, Windows et MacOS)
• Contenerisable sous docker

Cadre de notre démonstration :

On va utiliser une petite VM Linux Ubuntu 20.04 prise dans Azure, avec un disque ajouté de 512 Go.

On considère ce volume disque de 512 Go comme celui qui servira à stocker les données S3, sous /mnt/data.

SQL Server n’accepte d’échanger avec le stockage uniquement dans le cadre d’échanges sécurisés SSL. Il faut donc une paire de clé pour faire tourner notre service Min.IO. Pour ce PoC, on peut utiliser la solution « Certgen » proposée par Min.IO pour générer des certificats auto-signés (https://github.com/minio/certgen ).

En supposant que l’adresse IP de mon serveur Min.IO est 10.3.0.6, je génère ma paire de clé avec :

Certgen -host  "10.3.0.6,localhost"

Cela signifie que cela fonctionnera si le certificat est présenté par le service en tant que localhost ou bien en tant que 10.3.0.6. Si jamais on voulait utiliser la réplication active-active de Min.IO (entre deux serveurs par exemple et que le deuxième serveur est sur 10.3.0.5), on peut utiliser :

Certgen -host  "10.3.0.6,10.3.0.5,localhost"

Une fois la paire de clé générées, il faut mettre la clé publique dans le magasin de certificat des composants qui s’y connecteront.

Sous Linux, en particulier dans le cas d’une réplication active-active avec un autre serveur Min.IO, en copiant la clé publique dans /etc/ssl/certs/ en fichier .pem :

sudo cp ./public.crt /etc/ssl/My_Minio_public_key.pem

Sous Windows, on déploiera le fichier dans le magasin de certificat de l’ordinateur (dans trusted root certification authorities) :

Imaginons que la paire de fichiers est dans un répertoire du type /home/minio/certifs , on peut lancer le service tel que :

sudo MINIO_ROOT_USER=admin MINIO_ROOT_PASSWORD=password ./minio server /mnt/data --console-address ":9001" --certs-dir /home/minio/certifs/

Ensuite, on peut se connecter avec un navigateur en https sur la console d’admin : https://10.3.0.6:9001/

Configuration d’un bucket de stockage :

SQL Server a besoin d’un privilège qui n’est pas présent par défaut dans MinIO , celui de lister les objets.

On va donc aller dans la partie « Access » de l’interface de MinIO, et cliquer sur « Create Policy ».

 

Ici, on va créer un bucket « sqlserver2022bucket » :

La policy utilisée ici autorise s3.ListBucket (et qui correspond au standard S3 s3.ListObjectV2 , voir documentation https://docs.min.io/minio/baremetal/security/minio-identity-management/policy-based-access-control.html ), est donc la suivante :

{

"Version": "2012-10-17",

"Statement": [

{

"Effect": "Allow",

"Action": [

"s3:ListBucket"

],

"Resource": [

"arn:aws:s3:::*"

]

}

]

}

Elle autorise le listage des objets dans n’importe quel bucket. On peut affiner cela en mettant le chemin précis dans la partie Resource.

Une fois cette policy crée, on peut créer un bucket « mybucketsqlserver » :

On créera ensuite un dossier avec le bouton « create new path ».

Avec le bouton upload, j’ai chargé dans le bucket trois fichiers Parquet à titre de démonstration. Ce sont 3 fichiers de 1000 lignes chacun.

On va également créer un compte utilisateur pour SQL Server, appelé «  SQLUser »:

On lui assignera également la policy « MyListObjects ».

 

Configuration côté SQL Server :

L’accès à des données depuis un stockage compatible S3 passe l’utilisation du composant Polybase. Après l’avoir installé, on l’active par :

exec sp_configure @configname = 'polybase enabled', @configvalue = 1 ;

RECONFIGURE ;

exec sp_configure @configname = 'polybase enabled' ; 

 

On doit ensuite créer un credential qui sera celui utilisé pour se connecter au stockage S3 :

CREATE DATABASE SCOPED CREDENTIAL My_S3_Credential

WITH IDENTITY = 'S3 Access Key',

SECRET = 'sqluser:sqluser' ; 

On créer ensuite une source de données externe correspondant à notre service S3 :

CREATE EXTERNAL DATA SOURCE my_own_S3_DC

WITH (LOCATION = 's3://10.3.0.6 :9000/',   CREDENTIAL = My_S3_Credential);

GO 

Après, on déclare un format de fichier parquet :

CREATE EXTERNAL FILE FORMAT [SampleParquetFileFormat] WITH (FORMAT_TYPE = PARQUET)

GO 

Interrogation et manipulation d’un fichier Parquet :

Il y a deux méthodes pour les interroger :

• OPENROWSET()
• CREATE EXTERNAL TABLE

Avec OPENROWSET() :

Cette technique a l’avantage de ne pas forcément nécessiter de connaitre le modèle de données des fichiers. Par exemple :

SELECT  *  FROM    OPENROWSET

(   BULK '/mybucketsqlserver/parquetfiles'

,   FORMAT       = 'PARQUET'

,   DATA_SOURCE  = 'my_own_S3_DC'

) AS [cc];

Nous renvoi :

 

Les 3 fichiers Parquets ont été lus et le jeu de résultat est concaténé : on a bien 3000 lignes.

L’inconvénient de cette méthode est l’incapacité à établir une requête. On utilisera plutôt cette méthode pour faire un import massif de données dans une table locale.

Avec CREATE EXTERNAL TABLE :

On va donc créer une table soit parce qu’on connait le modèle de données, soit parce qu’on a fait un SELECT INTO depuis un OPENROWSET et obtenu le modèle de données :

CREATE EXTERNAL TABLE [dbo].[t_parquet_files]

(

[id] [int] NULL,

[first_name] [varchar](50) NULL,

[last_name] [varchar](50) NULL,

[email] [varchar](200) NULL,

[gender] [varchar](20) NULL,

[ip_address] [varchar](32) NULL,

[cc] [varchar](200) NULL,

[country] [varchar](200) NULL,

[birthdate] [varchar](200) NULL,

[salary] [float] NULL,

[title] [varchar](200) NULL,

[comments] [varchar](2000) NULL

)
WITH (DATA_SOURCE = my_own_S3_DC,LOCATION = N'/mybucketsqlserver/parquetfiles',FILE_FORMAT = [SampleParquetFileFormat],REJECT_TYPE = VALUE,REJECT_VALUE = 0)

GO 

 

On peut ensuite interroger la table externe comme n’importe quelle table :

 

 select * from [t_parquet_files]

where gender = 'male' 

Il devient intéressant de regarder le plan d’exécution :

 

On observe deux choses : tout d’abord SQL Server a été capable de produire une estimation correcte du nombre de lignes de plusieurs fichiers Parquets. Ensuite l’application d’un prédicat sur une colonne a renvoyé une estimation également correcte (il y a 1371 lignes plus précisément).

Vous allez peut-être penser que c’est une requête un peu simpliste pour se rassurer sur les estimations ?

Deux tests de fonctions d’agrégation :

select count(*), gender

from t_parquet_files

Group BY gender 

3 valeurs possibles pour la colonne gender : l’estimation est bonne.

 

 

select count(*), country

from t_parquet_files

Group BY country 

181 valeurs possibles dans la colonne country : l’estimation est également bonne.

 

En conclusion, l’élargissement de SQL Server, à travers Polybase, vers le stockage S3 et les fichiers Parquets, offre de nouvelles possibilités de traitement analytiques toujours plus grands.

Ce n’est pas juste un flux de données depuis un fichier plat, mais une vraie capacité de lecture intelligente des données grâce à des estimations de cardinalités, promettant des performances élevées.

 

Continuez votre lecture sur le blog :

• SQL Server 2022 Backup / Restore via un bucket S3 sous AWS (Louis PROU) [AWSSQL Server]
• SQL Server et accéder à des données tierces : Linked Servers vs. Polybase (Capdata team) [SQL Server]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Le chiffrement et SQL Server – Episode 1 : Transparent Data Encryption (TDE) vs Always Encrypted (Capdata team) [SQL Server]
• AWS : Backup Restore SQL Server RDS vers une EC2 ou On-Premise et vice versa ! (Emmanuel RAMI) [AWSSQL Server]