Dans le monde de la gestion de données, l’accès à des sources de données externes est souvent une nécessité pour de nombreux cas d’utilisation. Les entreprises ont souvent des données stockées dans différents systèmes et doivent être en mesure d’accéder à ces données de manière transparente pour les intégrer dans leur entrepôt de données ou pour effectuer des analyses. Les serveurs liés dans SQL Server et PolyBase sont deux outils qui permettent aux utilisateurs d’accéder à des données externes à partir de SQL Server. Dans cet article, nous allons explorer les différences entre ces deux outils et discuter des cas d’utilisation appropriés pour chacun.
Introduction aux serveurs liés
Les Serveur Liés (Linked Servers) sont la solution historique pour accéder à des données externes depuis SQL Server. Ils sont fait pour accéder à des serveurs de bases de données ou à des connecteurs ODBC.
Polybase, lui, a tout d’abord été introduit dans SQL Server 2016 pour accéder à MongoDb et à des systèmes HADOOP. Avec SQL Server 2019, Polybase renonce à HADOOP mais s’ouvre à de nombreuses options de connectivités :
- SQL Server
- Oracle
- MongoDB
- Terradata
- ODBC
- Fichiers CSV sur stockage Azure
- Fichiers plats (CSV et Apache Parquet) sur stockage compatible S3
Utilisation d’un serveur lié :
Si vous n’avez jamais utilisé de serveur lié, par exemple vers une instance SQL Server, c’est relativement simple. Tout d’abord on déclare un “Serveur Lié”. Ici, on a deux serveurs SQL dans un domaine Windows. Le serveur distant est derrière l’IP 10.2.0.4.
EXEC master.dbo.sp_addlinkedserver @server = N'MonServeurDistant', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.2.0.4' GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MonServeurDistant',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO
Il existe de nombreuses options que l’on peut paramétrer pour un Linked Server :
Dans notre cas, et avec les options par défaut, on peut désormais interroger de la manière suivante :
select * from MonServeurDistant.master.sys.databases ;
Cela nous renverra le contenu de la table sys.databases de la base master sur notre serveur distant.
L’intégralité de la documentation sur les serveurs liés dans SQL Server est disponible ici :
Utilisation de PolyBase :
A noter que le composant doit être déployé, au même titre que les Full-Text Index.
On doit ensuite activer le composant dans SQL Server sur le serveur qui interrogera des sources distantes :
sp_configure 'polybase enabled' , 1 GO RECONFIGURE GO
Une fois que c’est fait, on doit créer une master key dans la base depuis lequel on interrogera des sources distantes :
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='MonMdp2Mast3rKey!' go
On crée ensuite un database scoped user :
CREATE DATABASE SCOPED CREDENTIAL Polybase_Credential WITH IDENTITY = 'Polybase_User', SECRET = 'Polybase_User'; go
Cet utilisateur devra exister côté distant. L’authentification Windows n’est pas possible : uniquement SQL.
On peut ensuite déclarer notre source de données externe, où LAB1SQL2 est le serveur distant à interroger :
CREATE EXTERNAL DATA SOURCE LAB1SQL2_DS WITH ( LOCATION = 'sqlserver://LAB1SQL2:1433', PUSHDOWN = ON, CREDENTIAL = Polybase_Credential);
La plus grosse différence entre les Linked Servers et les tables externes Polybase est la nécessité, dans le second cas, de déclarer chaque table à laquelle on voudra accéder.
Par exemple, si le serveur distant contient la base AdventureWorks, on pourrait déclarer la table externe représentant la vue Sales.vSalesPerson :
CREATE EXTERNAL TABLE dbo.[External_vSalesPerson] ( BusinessEntityID int, Title nvarchar(16), FirstName nvarchar(50), MiddleName nvarchar(50), LastName nvarchar(50), Suffix nvarchar(50), JobTitle nvarchar(50), PhoneNumber nvarchar(50), PhoneNumberType nvarchar(50), EmailAddress nvarchar(50), EmailPromotion int, AddressLine1 nvarchar(50), AddressLine2 nvarchar(50), City nvarchar(50), StateProvinceName nvarchar(50), PostalCode nvarchar(15), CountryRegionName nvarchar(50), TerritoryName nvarchar(50), TerritoryGroup nvarchar(50), SalesQuota money, SalesYTD money, SalesLastYear money) WITH (LOCATION='AdventureWorks.Sales.vSalesPerson' , DATA_SOURCE=LAB1SQL2_DS);
On note donc les deux paramètres importants : LOCATION= le nom l’objet à présenter, et DATA_SOURCE = le nom de l’External Data Source.
On peut ensuite interroger localement la table externe dbo.External_vSalesPerson.
Différence de comportement et de performances :
Il existe des limitations à l’interrogation de table ou de vues par les Serveurs Liés. Celles-ci ne sont pas toujours très bien documentées. Par exemple, si une table/vue a des données de type XML ou Geography , cela ne fonctionnera pas.
Mais là où la situation devient vraiment compliquée, c’est lorsque l’on veut faire des jointures entre des données distantes et locales. Historiquement, il y avait les Serveurs Liés. Il faut comprendre que les requêtes basées sur des Serveurs Liés présentent un risque important de faibles performances, ou du moins de variations de performances. En effet, l’obtention des statistiques à travers un serveur lié est dépendant des paramètres suivants :
- Disposer de privilèges suffisants sur la base de données distante : sysadmin, db_owner ou db_ddladmin
- L’intégration d’une fonction dans un prédicat (encore + vrai qu’en local) : getdate() par exemple est calculé localement (pour cause de serveur possiblement dans des fuseaux horaires différents), et peut perturber la remontée des statistiques
- Certaines jointures avec de gros écarts de cardinalités entre la table de build et de probe
- Les cardinalités sont fixes lors de l’interrogation d’une vue : si le niveau de compatibilité est de 120 ou supérieur il sera de 100, tandis que 110 et inférieur cela sera de 1000.
Exemple :
En utilisant la base StackOverflow2010 de 10 Go, copiée sur les deux serveurs, on peut tenter des requêtes avec des jointures. De base, on a donc la requête suivante :
select vt.name, count(*) from VoteTypes vt INNER JOIN Votes V on v.VoteTypeId = Vt.id INNER JOIN dbo.Posts P on V.PostId = P.Id GROUP BY vt.Name
On a un coût de 54 et une durée d’exécution d’une seconde avec le plan ci-dessous :
Ensuite, en utilisant un Linked Server :
select vt.name, count(*) from VoteTypes vt INNER JOIN Votes V on v.VoteTypeId = Vt.id INNER JOIN MonServeurDistant.StackOverflow2010.dbo.Posts P on V.PostId = P.Id GROUP BY vt.Name
On a un coût de 1431 et une durée d’exécution de 2 minutes 06 avec le plan ci-dessous :
Enfin, avec une table externe reprenant la table Posts :
select vt.name, count(*) from VoteTypes vt INNER JOIN Votes V on v.VoteTypeId = Vt.id INNER JOIN External_Posts P on V.PostId = P.Id GROUP BY vt.Name
On a un coût de 46.8 et une durée d’exécution de 16 secondes.
On a donc sur cet exemple une erreur d’estimation lié à un cas de jointure avec de gros écarts de cardinalités. Ce ne sera pas cependant systématiquement le cas et même on peut observer des situations où les Linked Server ont de meilleurs résultats que les tables externes.
Par contre, dans le cas d’une vue derrière un serveur lié : la cardinalité étant fixée à 10 000 (en l’absence de prédicat, 100 avec, et avec un niveau de compatibilité supérieur à 120), les erreurs seront inévitables et seront forcément en faveur des tables externes.
Imaginons maintenant une requête, mais attaquant une vue qui représente la table Comments (où un index sur la colonne Score a été ajouté) :
select P.Score from Posts P INNER JOIN vComments C ON P.Id = C.PostId where C.Score = 1
Le plan d’exécution est assez classique, avec un coût de 8.46 et une durée d’exécution de 2 secondes :
Si on utilise désormais un Serveur Lié, le plan d’exécution change radicalement, avec un coût de … 0.08 ?!! et une durée d’exécution de … 1 minute 43 seconde !
En effet, dans le cas de l’utilisation d’une vue derrière un Serveur Lié, la cardinalité est de 100. SQL Server estimera donc un Nested Loop, alors que le nombre de ligne réel est bien supérieur !
Avec les tables externes Polybase, il faut penser déjà à déclarer la table externe derrière la vue, et à penser aux statistiques à créer manuellement :
CREATE EXTERNAL TABLE [dbo].[External_vComments] ( [Id] [int] NULL, CreationDate [Datetime] , PostId [int] , Score [int] NULL, Text nvarchar(1400) , UserId [int] NULL ) WITH (DATA_SOURCE = [LAB1SQL2_DS],LOCATION = N'StackOverflow2010.dbo.vComments') GO CREATE STATISTICS External_vComments_PostId ON External_vComments(PostId) with FULLSCAN CREATE STATISTICS External_vComments_Score ON External_vComments(Score) with FULLSCAN
Et là, en faisant la jointure sur la table externe, on aura un plan d’exécution encore différent :
On voit que la cardinalité estimée derrière External_vComments est bien supérieur à 100 (411 000, conforme à la vue/table réelle). On a donc un hash match join au lieu du loop join. Le coût de la requête estimé est de 149, et la durée est de 4 secondes (contre 103 secondes avec un Linked Server).
On a donc sacrifié de la souplesse de conception contre de la performance. En effet, la déclaration de la table externe et ses statistiques nécessite un peu de préparation, mais le gain en performance est tout de même significatif.
Conclusion
Attention cependant, il ne faut pas croire que les tables externes sont des solutions ultimes aux sujet de requêtes vers des serveurs externes. Il peut aussi arriver que SQL Server rencontre des problèmes d’estimation de plan et se trompe. Ici, on voit bien que le coût de 149 est loin de la réalité, et une branche d’une requête faisant appel à une table externe dans une requête complexe, basée sur des tables locale, pourrait donc être déséquilibrée.
La règle à favoriser malgré tout est de penser que lors de l’utilisation de vues, les Linked Server vont très très probablement poser problème. De là, 3 options : remonter les données sur le serveur local par une mécanique de votre choix, attaquer les données directement et non pas la vue, ou bien utiliser une table externe Polybase.
Continuez votre lecture sur le blog :
- SQL Server 2022 : stockage S3 sans AWS et fichiers Parquet (Capdata team) [AWSSQL Server]
- Oracle 19c : Les partitions hybrides (Emmanuel RAMI) [Oracle]
- Elastic Job Agent : l’Agent SQL Server pour le PaaS Azure (Capdata team) [AzureSQL Server]
- Stocker ses bases de données dans un Azure Blob Storage : l’impossible dilemme ? (Capdata team) [AzureSQL Server]
- Retrouver les tables dont les stats ne sont plus compilées en automatique (David Baffaleuf) [SQL Server]