![\"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>Dans le monde de la gestion de donn\u00e9es, l’acc\u00e8s \u00e0 des sources de donn\u00e9es externes est souvent une n\u00e9cessit\u00e9 pour de nombreux cas d’utilisation. Les entreprises ont souvent des donn\u00e9es stock\u00e9es dans diff\u00e9rents syst\u00e8mes et doivent \u00eatre en mesure d’acc\u00e9der \u00e0 ces donn\u00e9es de mani\u00e8re transparente pour les int\u00e9grer dans leur entrep\u00f4t de donn\u00e9es ou pour effectuer des analyses. Les serveurs li\u00e9s dans SQL Server et PolyBase sont deux outils qui permettent aux utilisateurs d’acc\u00e9der \u00e0 des donn\u00e9es externes \u00e0 partir de SQL Server. Dans cet article, nous allons explorer les diff\u00e9rences entre ces deux outils et discuter des cas d’utilisation appropri\u00e9s pour chacun.<\/p>\n Les Serveur Li\u00e9s (Linked Servers) sont la solution historique pour acc\u00e9der \u00e0 des donn\u00e9es externes depuis SQL Server. Ils sont fait pour acc\u00e9der \u00e0 des serveurs de bases de donn\u00e9es ou \u00e0 des connecteurs ODBC.<\/p>\n Polybase, lui, a tout d’abord \u00e9t\u00e9 introduit dans SQL Server 2016 pour acc\u00e9der \u00e0 MongoDb et \u00e0 des syst\u00e8mes HADOOP. Avec SQL Server 2019, Polybase renonce \u00e0 HADOOP mais s’ouvre \u00e0 de nombreuses options de connectivit\u00e9s :<\/p>\n Si vous n’avez jamais utilis\u00e9 de serveur li\u00e9, par exemple vers une instance SQL Server, c’est relativement simple. Tout d’abord on d\u00e9clare un “Serveur Li\u00e9”. Ici, on a deux serveurs SQL dans un domaine Windows. Le serveur distant est derri\u00e8re l’IP 10.2.0.4.<\/p>\n Il existe de nombreuses options que l’on peut param\u00e9trer pour un Linked Server\u00a0 :<\/p>\n Cela nous renverra le contenu de la table sys.databases de la base master sur notre serveur distant.<\/p>\n L’int\u00e9gralit\u00e9 de la documentation sur les serveurs li\u00e9s dans SQL Server est disponible ici\u00a0 :<\/p>\n https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/linked-servers\/linked-servers-database-engine?view=sql-server-ver16<\/a><\/p>\n A noter que le composant doit \u00eatre d\u00e9ploy\u00e9, au m\u00eame titre que les Full-Text Index.<\/p>\n On doit ensuite activer le composant dans SQL Server sur le serveur qui interrogera des sources distantes :<\/p>\n Une fois que c’est fait, on doit cr\u00e9er une master key dans la base depuis lequel on interrogera des sources distantes :<\/p>\n On cr\u00e9e ensuite un database scoped user<\/em> :<\/p>\n Cet utilisateur devra exister c\u00f4t\u00e9 distant. L’authentification Windows n’est pas possible : uniquement SQL.<\/p>\n On peut ensuite d\u00e9clarer notre source de donn\u00e9es externe, o\u00f9 LAB1SQL2 est le serveur distant \u00e0 interroger :<\/p>\n La plus grosse diff\u00e9rence entre les Linked Servers et les tables externes Polybase est la n\u00e9cessit\u00e9, dans le second cas, de d\u00e9clarer chaque table \u00e0 laquelle on voudra acc\u00e9der.<\/p>\n Par exemple, si le serveur distant contient la base AdventureWorks, on pourrait d\u00e9clarer la table externe repr\u00e9sentant la vue Sales.vSalesPerson :<\/p>\n On note donc les deux param\u00e8tres importants : LOCATION= le nom l’objet \u00e0 pr\u00e9senter, et DATA_SOURCE = le nom de l’External Data Source.<\/p>\n On peut ensuite interroger localement la table externe dbo.External_vSalesPerson.<\/p>\n <\/p>\n Il existe des limitations \u00e0 l’interrogation de table ou de vues par les Serveurs Li\u00e9s. Celles-ci ne sont pas toujours tr\u00e8s bien document\u00e9es. Par exemple, si une table\/vue a des donn\u00e9es de type XML ou Geography , cela ne fonctionnera pas.<\/p>\n Mais l\u00e0 o\u00f9 la situation devient vraiment compliqu\u00e9e, c’est lorsque l’on veut faire des jointures entre des donn\u00e9es distantes et locales. Historiquement, il y avait les Serveurs Li\u00e9s. Il faut comprendre que les requ\u00eates bas\u00e9es sur des Serveurs Li\u00e9s pr\u00e9sentent un risque important de faibles performances, ou du moins de variations de performances. En effet, l’obtention des statistiques \u00e0 travers un serveur li\u00e9 est d\u00e9pendant des param\u00e8tres suivants :<\/p>\n <\/p>\n Exemple :<\/p>\n En utilisant la base StackOverflow2010 de 10 Go, copi\u00e9e sur les deux serveurs, on peut tenter des requ\u00eates avec des jointures. De base, on a donc la requ\u00eate suivante :<\/p>\n On a un co\u00fbt de 54 et une dur\u00e9e d’ex\u00e9cution d’une seconde avec le plan ci-dessous :<\/p>\n Ensuite, en utilisant un Linked Server :<\/p>\n On a un co\u00fbt de 1431 et une dur\u00e9e d’ex\u00e9cution de 2 minutes 06 avec le plan ci-dessous :<\/p>\n Enfin, avec une table externe reprenant la table Posts :<\/p>\n On a un co\u00fbt de 46.8 et une dur\u00e9e d’ex\u00e9cution de 16 secondes.<\/p>\n <\/p>\n <\/p>\n On a donc sur cet exemple une erreur d’estimation li\u00e9 \u00e0 un cas de jointure avec de gros \u00e9carts de cardinalit\u00e9s. Ce ne sera pas cependant syst\u00e9matiquement le cas et m\u00eame on peut observer des situations o\u00f9 les Linked Server ont de meilleurs r\u00e9sultats que les tables externes.<\/p>\n <\/p>\n Par contre, dans le cas d’une vue derri\u00e8re un serveur li\u00e9 : la cardinalit\u00e9 \u00e9tant fix\u00e9e \u00e0 10 000 (en l’absence de pr\u00e9dicat, 100 avec, et avec un niveau de compatibilit\u00e9 sup\u00e9rieur \u00e0 120), les erreurs seront in\u00e9vitables et seront forc\u00e9ment en faveur des tables externes.<\/p>\n Imaginons maintenant une requ\u00eate, mais attaquant une vue qui repr\u00e9sente la table Comments (o\u00f9 un index sur la colonne Score a \u00e9t\u00e9 ajout\u00e9) :<\/p>\n Le plan d’ex\u00e9cution est assez classique, avec un co\u00fbt de 8.46 et une dur\u00e9e d’ex\u00e9cution de 2 secondes :<\/p>\n Si on utilise d\u00e9sormais un Serveur Li\u00e9, le plan d’ex\u00e9cution change radicalement, avec un co\u00fbt de … 0.08 ?!! et une dur\u00e9e d’ex\u00e9cution de … 1 minute 43 seconde !<\/p>\n En effet, dans le cas de l’utilisation d’une vue derri\u00e8re un Serveur Li\u00e9, la cardinalit\u00e9 est de 100. SQL Server estimera donc un Nested Loop, alors que le nombre de ligne r\u00e9el est bien sup\u00e9rieur !<\/p>\n <\/p>\n Avec les tables externes Polybase, il faut penser d\u00e9j\u00e0 \u00e0 d\u00e9clarer la table externe derri\u00e8re la vue, et \u00e0 penser aux statistiques \u00e0 cr\u00e9er manuellement :<\/p>\n Et l\u00e0, en faisant la jointure sur la table externe, on aura un plan d’ex\u00e9cution encore diff\u00e9rent :<\/p>\n <\/p>\n On voit que la cardinalit\u00e9 estim\u00e9e derri\u00e8re External_vComments est bien sup\u00e9rieur \u00e0 100 (411 000, conforme \u00e0 la vue\/table r\u00e9elle). On a donc un hash match join au lieu du loop join. Le co\u00fbt de la requ\u00eate estim\u00e9 est de 149, et la dur\u00e9e est de 4 secondes (contre 103 secondes avec un Linked Server).<\/p>\n On a donc sacrifi\u00e9 de la souplesse de conception contre de la performance. En effet, la d\u00e9claration de la table externe et ses statistiques n\u00e9cessite un peu de pr\u00e9paration, mais le gain en performance est tout de m\u00eame significatif.<\/p>\n <\/p>\n Attention cependant, il ne faut pas croire que les tables externes sont des solutions ultimes aux sujet de requ\u00eates vers des serveurs externes. Il peut aussi arriver que SQL Server rencontre des probl\u00e8mes d’estimation de plan et se trompe. Ici, on voit bien que le co\u00fbt de 149 est loin de la r\u00e9alit\u00e9, et une branche d’une requ\u00eate faisant appel \u00e0 une table externe dans une requ\u00eate complexe, bas\u00e9e sur des tables locale, pourrait donc \u00eatre d\u00e9s\u00e9quilibr\u00e9e.<\/p>\n La r\u00e8gle \u00e0 favoriser malgr\u00e9 tout est de penser que lors de l’utilisation de vues, les Linked Server vont tr\u00e8s tr\u00e8s probablement poser probl\u00e8me. De l\u00e0, 3 options : remonter les donn\u00e9es sur le serveur local par une m\u00e9canique de votre choix, attaquer les donn\u00e9es directement et non pas la vue, ou bien utiliser une table externe Polybase.<\/p>\n Dans le monde de la gestion de donn\u00e9es, l’acc\u00e8s \u00e0 des sources de donn\u00e9es externes est souvent une n\u00e9cessit\u00e9 pour de nombreux cas d’utilisation. Les entreprises ont souvent des donn\u00e9es stock\u00e9es dans diff\u00e9rents syst\u00e8mes et doivent \u00eatre en mesure d’acc\u00e9der… Continuer la lecture ![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/05\/neuronal.png\")
<\/a>
\n<\/p>\nIntroduction aux serveurs li\u00e9s<\/h2>\n
\n
Utilisation d’un serveur li\u00e9 :<\/h2>\n
\r\n\r\nEXEC master.dbo.sp_addlinkedserver @server = N'MonServeurDistant', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.2.0.4'\r\nGO\r\nEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MonServeurDistant',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL\r\nGO\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/linked_server1-300x272.png\")
\nDans notre cas, et avec les options par d\u00e9faut, on peut d\u00e9sormais interroger de la mani\u00e8re suivante :<\/p>\n\r\n\r\nselect * from MonServeurDistant.master.sys.databases ;\r\n\r\n<\/pre>\n
Utilisation de PolyBase :<\/h2>\n
\r\n\r\nsp_configure 'polybase enabled' , 1\r\nGO\r\nRECONFIGURE\r\nGO\r\n\r\n<\/pre>\n
\r\n\r\nCREATE MASTER KEY ENCRYPTION BY PASSWORD ='MonMdp2Mast3rKey!'\r\ngo\r\n\r\n<\/pre>\n
\r\n\r\nCREATE DATABASE SCOPED CREDENTIAL Polybase_Credential\r\nWITH IDENTITY = 'Polybase_User', SECRET = 'Polybase_User';\r\ngo\r\n\r\n<\/pre>\n
\r\n\r\nCREATE EXTERNAL DATA SOURCE LAB1SQL2_DS\r\nWITH ( LOCATION = 'sqlserver:\/\/LAB1SQL2:1433',\r\nPUSHDOWN = ON,\r\nCREDENTIAL = Polybase_Credential);\r\n\r\n<\/pre>\n
\r\n\r\nCREATE EXTERNAL TABLE dbo.[External_vSalesPerson]\r\n(\r\nBusinessEntityID int,\r\nTitle nvarchar(16),\r\nFirstName nvarchar(50),\r\nMiddleName nvarchar(50),\r\nLastName nvarchar(50),\r\nSuffix nvarchar(50),\r\nJobTitle nvarchar(50),\r\nPhoneNumber nvarchar(50),\r\nPhoneNumberType nvarchar(50),\r\nEmailAddress nvarchar(50),\r\nEmailPromotion int,\r\nAddressLine1 nvarchar(50),\r\nAddressLine2 nvarchar(50),\r\nCity nvarchar(50),\r\nStateProvinceName nvarchar(50),\r\nPostalCode nvarchar(15),\r\nCountryRegionName nvarchar(50),\r\nTerritoryName nvarchar(50),\r\nTerritoryGroup nvarchar(50),\r\nSalesQuota money,\r\nSalesYTD money,\r\nSalesLastYear money)\r\nWITH (LOCATION='AdventureWorks.Sales.vSalesPerson' , DATA_SOURCE=LAB1SQL2_DS);\r\n\r\n<\/pre>\n
Diff\u00e9rence de comportement et de performances :<\/h2>\n
\n
\r\n\r\nselect vt.name, count(*)\r\nfrom VoteTypes vt\r\nINNER JOIN Votes V on v.VoteTypeId = Vt.id\r\nINNER JOIN dbo.Posts P on V.PostId = P.Id\r\nGROUP BY vt.Name\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/base-300x72.png\")
<\/p>\n\r\nselect vt.name, count(*)\r\nfrom VoteTypes vt\r\nINNER JOIN Votes V on v.VoteTypeId = Vt.id\r\nINNER JOIN MonServeurDistant.StackOverflow2010.dbo.Posts P on V.PostId = P.Id\r\nGROUP BY vt.Name\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/req_linked_server-1-300x43.png\")
<\/p>\n\r\n\r\nselect vt.name, count(*)\r\nfrom VoteTypes vt\r\nINNER JOIN Votes V on v.VoteTypeId = Vt.id\r\nINNER JOIN External_Posts P on V.PostId = P.Id\r\nGROUP BY vt.Name\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/05\/external_table1-300x111.jpg\")
<\/p>\n\r\n\r\nselect P.Score\r\nfrom Posts P\r\nINNER JOIN vComments C ON P.Id = C.PostId\r\nwhere C.Score = 1\r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/Req_view_base-300x123.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/req_view_LS-1-300x125.png\")
<\/p>\n\r\n\r\nCREATE EXTERNAL TABLE [dbo].[External_vComments]\r\n(\r\n[Id] [int] NULL,\r\nCreationDate [Datetime] ,\r\nPostId [int] ,\r\nScore [int] NULL,\r\nText nvarchar(1400) ,\r\nUserId [int] NULL\r\n\r\n)\r\nWITH (DATA_SOURCE = [LAB1SQL2_DS],LOCATION = N'StackOverflow2010.dbo.vComments')\r\nGO\r\n\r\nCREATE STATISTICS External_vComments_PostId ON External_vComments(PostId) with FULLSCAN\r\nCREATE STATISTICS External_vComments_Score ON External_vComments(Score) with FULLSCAN\r\n\r\n \r\n\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/04\/req_view_Polybase-300x169.png\")
<\/p>\nConclusion<\/h2>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"