Configurer PolyBase pour accéder à des données externes dans MongoDB

S'applique à : SQL Server

L’article explique comment utiliser PolyBase sur une instance SQL Server pour interroger des données externes dans MongoDB.

Prérequis

Si vous n’avez pas installé PolyBase, consultez Installation de PolyBase.

Avant que vous ne créiez des informations d’identification incluses dans l’étendue de la base de données, la base de données doit avoir une clé principale pour protéger les informations d’identification. Pour plus d’informations, consultez CREATE MASTER KEY.

Configurer une source de données externes MongoDB

Pour interroger les données d’une source de données MongoDB, vous devez créer des tables externes pour référencer les données externes. Cette section fournit un exemple de code pour créer ces tables externes.

Les commandes Transact-SQL suivantes sont utilisées dans cette section :

• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE STATISTICS (Transact-SQL)

1. Créez des informations d’identification incluses dans l’étendue de la base de données pour accéder à la source MongoDB.

   Le script suivant crée des informations d’identification délimitées à la base de données. Avant d’exécuter le script, mettez-le à jour pour votre environnement :

   • Remplacez <credential_name> par le nom des informations d‘identification.
   • Remplacez <username> par le nom d’utilisateur de la source externe.
   • Remplacez <password> par le mot de passe approprié.

   CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
   

   Important

   Le connecteur ODBC MongoDB pour Polybase prend uniquement en charge l’authentification de base (l’authentification Kerberos n’est pas prise en charge).

2. Créez une source de données externe.

   Le script suivant crée la source de données externe. Pour référence, consultez CREATE EXTERNAL DATA SOURCE. Avant d’exécuter le script, mettez-le à jour pour votre environnement :

   • Mettez à jour l’emplacement. Définissez <server> et <port> pour votre environnement.
   • Remplacez <credential_name> par le nom des informations d‘identification que vous avez créées à l’étape précédente.
   • Vous pouvez également indiquer PUSHDOWN = ON ou PUSHDOWN = OFF pour spécifier le calcul pushdown sur la source externe.

   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (LOCATION = '<mongodb://<server>[:<port>]>'
   [ [ , ] CREDENTIAL = <credential_name> ]
   [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
   [ [ , ] PUSHDOWN = { ON | OFF } ])
   [ ; ]
   

3. Interrogez le schéma externe dans MongoDB.

   Vous devez utiliser l’extension de virtualisation des données pour Azure Data Studio afin de connecter et générer une instruction CREATE EXTERNAL TABLE basée sur le schéma détecté par le pilote ODBC PolyBase pour le pilote MongoDB. Vous pouvez également personnaliser manuellement un script en fonction de la sortie de la procédure stockée système sp_data_source_objects (Transact-SQL). L’extension de virtualisation des données pour Azure Data Studio et sp_data_source_table_columns utilise les mêmes procédures stockées internes pour interroger le schéma externe.

   Pour créer des tables externes dans des collections MongoDB qui contiennent des tableaux, il est recommandé d’utiliser l’extension Data Virtualization pour Azure Data Studio. Les actions d’aplatissement sont effectuées automatiquement par le pilote. La procédure stockée sp_data_source_table_columns effectue également automatiquement l’aplatissement via le pilote ODBC PolyBase pour MongoDB.

4. Créez une table externe.

   Si vous utilisez l’extension Data Virtualization pour Azure Data Studio, vous pouvez ignorer cette étape, car l’instruction CREATE EXTERNAL TABLE est générée pour vous. Pour fournir le schéma manuellement, tenez compte de l’exemple de script suivant pour créer une table externe. Pour référence, consultez CREATE EXTERNAL TABLE.

   Avant d’exécuter le script, mettez-le à jour pour votre environnement :

   • Mettez à jour les champs avec leur nom et leur classement. S’il s’agit de collections, spécifiez le nom de la collection et le nom du champ. Dans l’exemple, friends est un type de données personnalisé.
   • Mettez à jour l’emplacement. Définissez le nom de la base de données et le nom de la table. Notez que les noms en trois parties ne sont pas autorisés. Vous ne pouvez donc pas en créer pour la table system.profile. Vous ne pouvez pas non plus spécifier une vue, car elle ne pourra pas obtenir les métadonnées à partir de celle-ci.
   • Remplacez le nom de la source de données par le nom de celle que vous avez créée à l’étape précédente.

   CREATE EXTERNAL TABLE [MongoDbRandomData](
     [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [RandomData_friends_id] INT,
     [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
   WITH (
     LOCATION='MyDb.RandomData',
     DATA_SOURCE=[MongoDb])
   

5. Facultatif : Créez des statistiques sur une table externe.

   Pour des performances de requêtes optimales, nous vous recommandons de créer des statistiques sur les colonnes de table externe, en particulier celles utilisées pour les jointures, les filtres et les agrégats.

   CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 
   

Options de connexion MongoDB

Pour plus d’informations sur les options de connexion MongoDB, consultez la documentation MongoDB sur le format d’URI de la chaîne de connexion.

Aplanissement

L’aplanissement est activé pour les données imbriquées et répétées depuis des collections de document MongoDB. L’utilisateur doit activer create an external table et spécifier explicitement un schéma relationnel sur des collections de documents MongoDB dont les données peuvent être imbriquées et/ou répétées. Les types de données JSON imbriquées/répétées seront aplanis comme suit :

• Objets : collection de clés/valeurs non ordonnées mises entre accolades (imbriquées)

  • SQL Server crée une colonne de table pour chaque clé d’objet

    • Nom de colonne : objectname_keyname

• Tableau : valeurs ordonnées, séparées par des virgules, mises entre crochets (répétées)

  • SQL Server ajoute une nouvelle ligne de table pour chaque élément de tableau

  • SQL Server crée une colonne par tableau pour stocker l’index d’élément de tableau

    • Nom de colonne : arrayname_index

    • Type de données : bigint

Cette technique peut occasionner plusieurs problèmes, notamment les deux suivants :

• Un champ répété vide masque de facto les données contenues dans les champs plats du même enregistrement

• La présence de plusieurs champs répétés peut entraîner une explosion du nombre de lignes générées

À titre d’exemple, SQL Server évalue la collection de restaurants de l’exemple de jeu de données MongoDB stockée dans un format JSON non relationnel. Chaque restaurant dispose d’un champ d’adresse imbriqué et d’un tableau de notes (« grades ») attribuées à des jours différents. La figure ci-dessous illustre un restaurant type avec une adresse imbriquée et des notes imbriquées/répétées.

L’adresse de l’objet sera aplanie comme suit :

• Le champ imbriqué restaurant.address.building devient restaurant.address_building
• Le champ imbriqué restaurant.address.coord devient restaurant.address_coord
• Le champ imbriqué restaurant.address.street devient restaurant.address_street
• Le champ imbriqué restaurant.address.zipcode devient restaurant.address_zipcode

Les notes du tableau seront aplanies comme suit :

Connexion Cosmos DB

À l’aide de l’API Mongo Cosmos DB et du connecteur PolyBase MongoDB, vous pouvez créer une table externe pour une instance Cosmos DB. Pour cela, vous devez suivre les étapes listées ci-dessus. Vérifiez que les informations d’identification incluses dans l’étendue de la base de données, l’adresse du serveur, le port et la chaîne d’emplacement sont bien ceux du serveur Cosmos DB.

Exemples

L’exemple suivant crée une source de données externe avec les paramètres suivants :

CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (LOCATION = 'mongodb://mongodb0.example.com:27017',
    CONNECTION_OPTIONS = 'replicaSet=myRepl; tls=true',
    PUSHDOWN = ON ,
    CREDENTIAL = credential_name);

Étapes suivantes

Pour plus de tutoriels sur la création de sources de données externes et de tables externes vers diverses sources de données, consultez référence PolyBase Transact-SQL.

Pour en savoir plus sur PolyBase, consultez Vue d’ensemble de SQL Server PolyBase.