![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>SQL Server 2022 propose deux nouvelles fonctionnalit\u00e9s, qui combin\u00e9es, apportent un levier suppl\u00e9mentaire \u00e0 l\u2019exploitation de donn\u00e9es non traditionnelles\u00a0: la capacit\u00e9 de connexion \u00e0 du stockage compatible S3 et le support des fichiers parquets<\/em> (des fichiers qui seront produits par des syst\u00e8mes externes \u00e0 SQL Server, sous la forme de fichiers plats g\u00e9rant les donn\u00e9es de mani\u00e8re verticale, structur\u00e9es et compress\u00e9es).<\/p>\n Le stockage S3 peut \u00eatre, bien entendu, le service d\u2019AWS, mais dans cet article nous proposerons le d\u00e9ploiement d\u2019un stockage S3 customis\u00e9 avec le produit OpenSource \u00ab\u00a0Min.IO\u00a0\u00bb.<\/p>\n Il existe de nombreuses solutions de stockage compatible S3 \u00e0 part le service AWS. Les SAN\/NAS modernes proposent souvent un point d\u2019acc\u00e8s S3, par exemple. On peut sinon concevoir son service avec une solution logicielle.<\/p>\n La solution Min.IO est\u00a0:<\/p>\n On va utiliser une petite VM Linux Ubuntu 20.04 prise dans Azure, avec un disque ajout\u00e9 de 512 Go.<\/p>\n On consid\u00e8re ce volume disque de 512 Go comme celui qui servira \u00e0 stocker les donn\u00e9es S3, sous \/mnt\/data.<\/p>\n SQL Server n\u2019accepte d\u2019\u00e9changer avec le stockage uniquement dans le cadre d\u2019\u00e9changes s\u00e9curis\u00e9s SSL. Il faut donc une paire de cl\u00e9 pour faire tourner notre service Min.IO. Pour ce PoC, on peut utiliser la solution \u00ab\u00a0Certgen\u00a0\u00bb propos\u00e9e par Min.IO pour g\u00e9n\u00e9rer des certificats auto-sign\u00e9s (https:\/\/github.com\/minio\/certgen<\/a> ).<\/p>\n En supposant que l\u2019adresse IP de mon serveur Min.IO est 10.3.0.6, je g\u00e9n\u00e8re ma paire de cl\u00e9 avec\u00a0:<\/p>\n Cela signifie que cela fonctionnera si le certificat est pr\u00e9sent\u00e9 par le service en tant que localhost ou bien en tant que 10.3.0.6. Si jamais on voulait utiliser la r\u00e9plication active-active de Min.IO (entre deux serveurs par exemple et que le deuxi\u00e8me serveur est sur 10.3.0.5), on peut utiliser\u00a0:<\/p>\n Une fois la paire de cl\u00e9 g\u00e9n\u00e9r\u00e9es, il faut mettre la cl\u00e9 publique dans le magasin de certificat des composants qui s\u2019y connecteront.<\/p>\n Sous Linux, en particulier dans le cas d\u2019une r\u00e9plication active-active avec un autre serveur Min.IO, en copiant la cl\u00e9 publique dans \/etc\/ssl\/certs\/ en fichier .pem\u00a0:<\/p>\n Sous Windows, on d\u00e9ploiera le fichier dans le magasin de certificat de l\u2019ordinateur (dans trusted root certification authorities) :<\/p>\n Imaginons que la paire de fichiers est dans un r\u00e9pertoire du type \/home\/minio\/certifs , on peut lancer le service tel que\u00a0:<\/p>\n Ensuite, on peut se connecter avec un navigateur en https sur la console d\u2019admin\u00a0: https:\/\/10.3.0.6:9001\/<\/a><\/p>\n SQL Server a besoin d\u2019un privil\u00e8ge qui n\u2019est pas pr\u00e9sent par d\u00e9faut dans MinIO , celui de lister les objets.<\/p>\n On va donc aller dans la partie \u00ab\u00a0Access\u00a0\u00bb de l\u2019interface de MinIO, et cliquer sur \u00ab\u00a0Create Policy\u00a0\u00bb.<\/p>\n <\/p>\n Ici, on va cr\u00e9er un bucket \u00ab\u00a0sqlserver2022bucket\u00a0\u00bb\u00a0:<\/p>\n La policy utilis\u00e9e 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<\/a> ), est donc la suivante\u00a0:<\/p>\n Elle autorise le listage des objets dans n\u2019importe quel bucket. On peut affiner cela en mettant le chemin pr\u00e9cis dans la partie Resource.<\/p>\n Une fois cette policy cr\u00e9e, on peut cr\u00e9er un bucket \u00ab\u00a0mybucketsqlserver\u00a0\u00bb\u00a0:<\/p>\n On cr\u00e9era ensuite un dossier avec le bouton \u00ab\u00a0create new path\u00a0\u00bb.<\/p>\n Avec le bouton upload, j\u2019ai charg\u00e9 dans le bucket trois fichiers Parquet \u00e0 titre de d\u00e9monstration. Ce sont 3 fichiers de 1000 lignes chacun.<\/p>\n On va \u00e9galement cr\u00e9er un compte utilisateur pour SQL Server, appel\u00e9 \u00ab\u00a0\u00a0SQLUser\u00a0\u00bb:<\/p>\n On lui assignera \u00e9galement la policy \u00ab MyListObjects \u00bb.<\/p>\n <\/p>\n L\u2019acc\u00e8s \u00e0 des donn\u00e9es depuis un stockage compatible S3 passe l\u2019utilisation du composant Polybase. Apr\u00e8s l\u2019avoir install\u00e9, on l\u2019active par\u00a0:<\/p>\n <\/p>\n On doit ensuite cr\u00e9er un credential qui sera celui utilis\u00e9 pour se connecter au stockage S3\u00a0:<\/p>\n On cr\u00e9er ensuite une source de donn\u00e9es externe correspondant \u00e0 notre service S3\u00a0:<\/p>\n Apr\u00e8s, on d\u00e9clare un format de fichier parquet\u00a0:<\/p>\n Il y a deux m\u00e9thodes pour les interroger\u00a0:<\/p>\n Cette technique a l\u2019avantage de ne pas forc\u00e9ment n\u00e9cessiter de connaitre le mod\u00e8le de donn\u00e9es des fichiers. Par exemple\u00a0:<\/p>\n Nous renvoi\u00a0:<\/p>\n <\/p>\n Les 3 fichiers Parquets ont \u00e9t\u00e9 lus et le jeu de r\u00e9sultat est concat\u00e9n\u00e9 : on a bien 3000 lignes.<\/p>\n L\u2019inconv\u00e9nient de cette m\u00e9thode est l\u2019incapacit\u00e9 \u00e0 \u00e9tablir une requ\u00eate. On utilisera plut\u00f4t cette m\u00e9thode pour faire un import massif de donn\u00e9es dans une table locale.<\/p>\n On va donc cr\u00e9er une table soit parce qu\u2019on connait le mod\u00e8le de donn\u00e9es, soit parce qu\u2019on a fait un SELECT INTO depuis un OPENROWSET et obtenu le mod\u00e8le de donn\u00e9es\u00a0:<\/p>\n <\/p>\n On peut ensuite interroger la table externe comme n\u2019importe quelle table\u00a0:<\/p>\n <\/p>\n Il devient int\u00e9ressant de regarder le plan d\u2019ex\u00e9cution\u00a0:<\/p>\n <\/p>\n On observe deux choses\u00a0: tout d\u2019abord SQL Server a \u00e9t\u00e9 capable de produire une estimation correcte du nombre de lignes de plusieurs fichiers Parquets. Ensuite l\u2019application d\u2019un pr\u00e9dicat sur une colonne a renvoy\u00e9 une estimation \u00e9galement correcte (il y a 1371 lignes plus pr\u00e9cis\u00e9ment).<\/p>\n Vous allez peut-\u00eatre penser que c\u2019est une requ\u00eate un peu simpliste pour se rassurer sur les estimations\u00a0?<\/p>\n Deux tests de fonctions d\u2019agr\u00e9gation\u00a0:<\/p>\n 3 valeurs possibles pour la colonne gender\u00a0: l\u2019estimation est bonne.<\/p>\n <\/p>\n <\/p>\n <\/p>\n En conclusion, l\u2019\u00e9largissement de SQL Server, \u00e0 travers Polybase, vers le stockage S3 et les fichiers Parquets, offre de nouvelles possibilit\u00e9s de traitement analytiques toujours plus grands.<\/p>\n Ce n\u2019est pas juste un flux de donn\u00e9es depuis un fichier plat, mais une vraie capacit\u00e9 de lecture intelligente des donn\u00e9es gr\u00e2ce \u00e0 des estimations de cardinalit\u00e9s, promettant des performances \u00e9lev\u00e9es.<\/p>\n <\/p>\n SQL Server 2022 propose deux nouvelles fonctionnalit\u00e9s, qui combin\u00e9es, apportent un levier suppl\u00e9mentaire \u00e0 l\u2019exploitation de donn\u00e9es non traditionnelles\u00a0: la capacit\u00e9 de connexion \u00e0 du stockage compatible S3 et le support des fichiers parquets (des fichiers qui seront produits par… Continuer la lecture D\u00e9ployer son stockage S3 avec Min.IO\u00a0:<\/h1>\n
\n
Cadre de notre d\u00e9monstration\u00a0:<\/h2>\n
Certgen -host\u00a0 "10.3.0.6,localhost"<\/pre>\n
Certgen -host\u00a0 "10.3.0.6,10.3.0.5,localhost"<\/pre>\n
sudo cp .\/public.crt \/etc\/ssl\/My_Minio_public_key.pem<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/certificat-300x175.png\")
<\/p>\nsudo MINIO_ROOT_USER=admin MINIO_ROOT_PASSWORD=password .\/minio server \/mnt\/data --console-address ":9001" --certs-dir \/home\/minio\/certifs\/<\/pre>\n
Configuration d\u2019un bucket de stockage\u00a0:<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/policy-300x199.png\")
<\/p>\n\r\n\r\n{\r\n\r\n"Version": "2012-10-17",\r\n\r\n"Statement": [\r\n\r\n{\r\n\r\n"Effect": "Allow",\r\n\r\n"Action": [\r\n\r\n"s3:ListBucket"\r\n\r\n],\r\n\r\n"Resource": [\r\n\r\n"arn:aws:s3:::*"\r\n\r\n]\r\n\r\n}\r\n\r\n]\r\n\r\n}<\/pre>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/create_bucket-300x151.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/14-300x84.png\")
<\/p>\n![\"path_2\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/16-300x107.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/bucket4-300x203.png\")
<\/p>\nConfiguration c\u00f4t\u00e9 SQL Server\u00a0:<\/h2>\n
exec sp_configure @configname = 'polybase enabled', @configvalue = 1 ;\r\n\r\nRECONFIGURE ;\r\n\r\nexec sp_configure @configname = 'polybase enabled' ; <\/pre>\n
CREATE DATABASE SCOPED CREDENTIAL My_S3_Credential\r\n\r\nWITH IDENTITY = 'S3 Access Key',\r\n\r\nSECRET = 'sqluser:sqluser' ; <\/pre>\n
<\/pre>\n
\r\n\r\nCREATE EXTERNAL DATA SOURCE my_own_S3_DC\r\n\r\nWITH (LOCATION = 's3:\/\/10.3.0.6\u00a0:9000\/',\u00a0\u00a0 CREDENTIAL = My_S3_Credential);\r\n\r\nGO <\/pre>\n
CREATE EXTERNAL FILE FORMAT [SampleParquetFileFormat] WITH (FORMAT_TYPE = PARQUET)\r\n\r\nGO <\/pre>\n
Interrogation et manipulation d\u2019un fichier Parquet\u00a0:<\/h2>\n
\n
Avec OPENROWSET()\u00a0:<\/u><\/h3>\n
\r\n\r\nSELECT\u00a0 *\u00a0 FROM\u00a0\u00a0\u00a0 OPENROWSET\r\n\r\n(\u00a0\u00a0 BULK '\/mybucketsqlserver\/parquetfiles'\r\n\r\n,\u00a0\u00a0 FORMAT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 'PARQUET'\r\n\r\n,\u00a0\u00a0 DATA_SOURCE\u00a0 = 'my_own_S3_DC'\r\n\r\n) AS [cc];\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/reslt2-300x94.png\")
<\/p>\nAvec CREATE EXTERNAL TABLE\u00a0: <\/u><\/h3>\n
CREATE EXTERNAL TABLE [dbo].[t_parquet_files]\r\n\r\n(\r\n\r\n[id] [int] NULL,\r\n\r\n[first_name] [varchar](50) NULL,\r\n\r\n[last_name] [varchar](50) NULL,\r\n\r\n[email] [varchar](200) NULL,\r\n\r\n[gender] [varchar](20) NULL,\r\n\r\n[ip_address] [varchar](32) NULL,\r\n\r\n[cc] [varchar](200) NULL,\r\n\r\n[country] [varchar](200) NULL,\r\n\r\n[birthdate] [varchar](200) NULL,\r\n\r\n[salary] [float] NULL,\r\n\r\n[title] [varchar](200) NULL,\r\n\r\n[comments] [varchar](2000) NULL\r\n\r\n)\r\nWITH (DATA_SOURCE = my_own_S3_DC,LOCATION = N'\/mybucketsqlserver\/parquetfiles',FILE_FORMAT = [SampleParquetFileFormat],REJECT_TYPE = VALUE,REJECT_VALUE = 0)\r\n\r\nGO <\/pre>\n
select * from [t_parquet_files]\r\n\r\nwhere gender = 'male' <\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/2-300x103.png\")
<\/p>\n![\"plan_exec3\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/4-298x300.png\")
<\/pre>\nselect count(*), gender\r\n\r\nfrom t_parquet_files\r\n\r\nGroup BY gender <\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/6-300x99.png\")
<\/pre>\nselect count(*), country\r\n\r\nfrom t_parquet_files\r\n\r\nGroup BY country <\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/07\/8-300x90.png\")
\n181 valeurs possibles dans la colonne country\u00a0: l\u2019estimation est \u00e9galement bonne.<\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"