Utiliser le pilote ODBC Azure Cosmos DB pour se connecter à des outils de décisionnel et d’analytique données

S’APPLIQUE À : NoSQL

Cet article vous guide dans l’installation et l’utilisation du pilote ODBC Azure Cosmos DB pour créer des tables et vues normalisées pour vos données Azure Cosmos DB. Vous pouvez interroger les données normalisées à l’aide de requêtes SQL, ou importer les données dans Power BI ou dans d’autres logiciels de décisionnel et d’analytique pour créer des rapports et des visualisations.

Azure Cosmos DB est une base de données sans schéma, ce qui permet de développer rapidement des applications et d’effectuer une itération sur les modèles de données sans être limité à un schéma strict. Une même base de données Azure Cosmos DB peut contenir des documents JSON de différentes structures. Pour analyser ces données ou créer des rapports dessus, vous devrez peut-être les aplatir pour qu’elles s’intègrent à un schéma.

Le pilote ODBC vous permet de normaliser les données d’Azure Cosmos DB dans des tables et des vues adaptées à vos besoins d’analytique données et de création de rapports. Les schémas normalisés vous permettent d’utiliser des outils compatibles ODBC pour accéder aux données. Les schémas n’ont aucun effet sur les données sous-jacente et il n’est pas obligatoire pour les développeurs de les respecter. Le pilote ODBC permet de rendre les bases de données Azure Cosmos DB utiles aux analystes de données et aux équipes de développement.

Il vous permet d’effectuer des opérations SQL dans les tables et les vues normalisées, notamment des regroupements par requêtes, des insertions, des mises à jour et des suppressions. Le pilote ODBC est conforme à ODBC 3.8 et prend en charge la syntaxe ANSI SQL-92.

Important

Envisagez d’utiliser Azure Synapse Link pour Azure Cosmos DB pour créer des tables et des vues pour vos données. Synapse Link offre des avantages de performances distincts pour les jeux de données volumineux par rapport au pilote ODBC. Vous pouvez également connecter les données Azure Cosmos DB normalisées à d’autres solutions logicielles, telles que SQL Server Integration Services (SSIS), QlikSense, Tableau et d’autres logiciels d’analytique et de décisionnel et outils d’intégration de données. Vous pouvez utiliser ces solutions pour analyser, déplacer, transformer et créer des visualisations avec vos données Azure Cosmos DB.

Important

• La connexion à Azure Cosmos DB avec le pilote ODBC est actuellement prise en charge uniquement pour Azure Cosmos DB for NoSQL.
• Le pilote ODBC actuel ne prend pas en charge les pushdowns agrégés et présente des problèmes connus avec de nombreux outils d’analytique. Tant qu’une nouvelle version n’est pas publiée, vous pouvez utiliser l’une des alternatives suivantes :
  • Azure Synapse Link est la solution d’analytique recommandée pour Azure Cosmos DB. Avec les pools serverless Azure Synapse Link et Azure Synapse SQL, vous pouvez utiliser n’importe quel outil de décisionnel afin d’extraire des insights en temps quasi réel à partir de données SQL Azure Cosmos DB ou des données d’API pour MongoDB.
  • Pour Power BI, vous pouvez utiliser le connecteur Azure Cosmos DB pour Power BI.
  • Pour Qlik Sense, consultez Connecter Qlik Sense vers Azure Cosmos DB.

Installer le pilote ODBC et se connecter à votre base de données

1. Téléchargez les pilotes correspondant à votre environnement :

   Programme d’installation	Systèmes d’exploitation pris en charge
   Microsoft Azure Cosmos DB ODBC 64-bit.msi pour Windows 64 bits	Versions 64 bits de Windows 8.1 ou ultérieures, Windows 8, Windows 7. Version 64-bits de Windows Server 2012 R2, Windows Server 2012 et Windows Server 2008 R2.
   Microsoft Azure Cosmos DB ODBC 32x64-bit.msi pour 32 bits sur Windows 64 bits	Versions 64 bits de Windows 8.1 ou version ultérieure, Windows 8, Windows 7, Windows XP, Windows Vista. Versions 64-bit de Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2 et Windows Server 2003.
   Microsoft Azure Cosmos DB ODBC 32-bit.msi pour Windows 32 bits	Versions 32 bits de Windows 8.1 ou version ultérieure, Windows 8, Windows 7, Windows XP et Windows Vista.

2. Exécutez le fichier .msi localement pour lancer l’Assistant d’installation du pilote ODBC Microsoft Azure Cosmos DB.

3. Terminez l’Assistant d’installation en utilisant l’entrée par défaut.

4. Une fois le pilote installé, tapez Sources de données ODBC dans la zone de recherche Windows, puis ouvrez l’Administrateur des sources de données ODBC.

5. Vérifiez que le pilote ODBC Microsoft Azure DocumentDB apparaît sous l’onglet Pilotes.

   

6. Sélectionnez l’onglet Nom de source de données utilisateur, puis sélectionnez Ajouter pour créer un nom de source de données (DSN). Vous pouvez également créer un DSN système.

7. Dans la fenêtre Créer une nouvelle source de données, sélectionnez Pilote ODBC Microsoft Azure DocumentDB, puis Terminer.

8. Dans la fenêtre Configuration DSN du pilote ODBC DocumentDB, répondez aux questions suivantes :

   

   • Nom de source de données : un nom convivial pour votre DSN ODBC. Ce nom est unique pour ce compte Azure Cosmos DB.
   • Description : courte description de la source de données.
   • Hôte : URI de votre compte Azure Cosmos DB. Vous pouvez obtenir ces informations sur la page Clés de votre compte Azure Cosmos DB dans le Portail Azure.
   • Clé d’accès : clé primaire ou secondaire, en lecture-écriture ou en lecture seule, affichée sur la page des clés Azure Cosmos DB du Portail Azure. Il est préférable d’utiliser les clés en lecture seule si vous utilisez le DSN pour le traitement des données en lecture seule et la création de rapports.

   Pour éviter une erreur d’authentification, utilisez les boutons de copie pour copier l’URI et la clé à partir du Portail Azure.

   

   • Chiffrer la clé d’accès pour : sélectionnez l’option optimale en fonction des utilisateurs de l’ordinateur.

9. Sélectionnez Tester pour vérifier que vous pouvez vous connecter à votre compte Azure Cosmos DB.

10. Sélectionnez Options avancées et définissez les valeurs suivantes :

    • Version de l’API REST : Sélectionnez la version de l’API REST pour vos opérations. La valeur par défaut est 2015-12-16.

      Si vous avez des conteneurs avec de grandes clés de partition qui ont besoin de l’API REST version 2018-12-31, tapez 2018-12-31, puis suivez les étapes à la fin de cette procédure.

    • Cohérence des requêtes : sélectionnez le niveau de cohérence de vos opérations. La valeur par défaut est Session.

    • Nombre de tentatives : entrez le nombre de tentatives d’une opération si la demande initiale n’aboutit pas en raison d’une limitation du débit du service.

    • Fichier de schéma : si vous ne sélectionnez pas de fichier de schéma, le pilote analyse la première page de données de chaque conteneur pour déterminer son schéma, appelé mappage de conteneur, pour chaque session. Ce processus peut entraîner un temps de démarrage long pour les applications qui utilisent le DSN. Il est préférable d’associer un fichier de schéma au DSN.

      • Si vous avez déjà un fichier de schéma, sélectionnez Parcourir, accédez au fichier, sélectionnez Enregistrer, puis OK.

      • Si vous n’avez pas encore de fichier de schéma, sélectionnez OK, puis suivez les étapes de la section suivante pour créer une définition de schéma. Après avoir créé le schéma, revenez à cette fenêtre Options avancées pour ajouter le fichier de schéma.

Une fois que vous avez sélectionné OK pour terminer et fermer la fenêtre Configuration DSN du pilote ODBC DocumentDB, le nouveau DSN utilisateur apparaît sous l’onglet DSN utilisateur de la fenêtre Administrateur des sources de données ODBC.

Modifier le Registre Windows pour prendre en charge l’API REST version 2018-12-31

Si vous avez des conteneurs avec de grandes clés de partition qui ont besoin de l’API REST version 2018-12-31, procédez comme suit pour mettre à jour le Registre Windows afin de prendre en charge cette version.

1. Dans le menu Démarrer de Windows, tapez regedit pour rechercher et ouvrir l’Éditeur du Registre.

2. Dans l’Éditeur du Registre, accédez au chemin Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.

3. Créez une sous-clé qui a le même nom que votre DSN (par exemple, DSN ODBC compte Contoso).

4. Accédez à la nouvelle sous-clé DSN ODBC compte Contoso, puis cliquez avec le bouton droit pour ajouter une nouvelle valeur String :

   • Nom de la valeur : IgnoreSessionToken

   • Données de valeur : 1

   

Créer une définition de schéma

Il existe deux types de méthodes d’échantillonnage que vous pouvez utiliser pour créer un schéma : mappage de conteneur ou mappage de délimiteur de table. Une session d’échantillonnage peut utiliser ces deux méthodes d’échantillonnage, mais chaque conteneur ne peut utiliser qu’une des méthodes d’échantillonnage. La méthode à utiliser dépend des caractéristiques de vos données.

• Le mappage de conteneur récupère les données sur une page de conteneur pour déterminer la structure des données et transpose le conteneur dans une table côté ODBC. Cette méthode d’échantillonnage est rapide et efficace lorsque les données d’un conteneur sont homogènes.

• Le mappage de délimiteur de table fournit un échantillonnage plus robuste pour les données hétérogènes. Cette méthode définit l’étendue de l’échantillonnage sur un ensemble d’attributs et des valeurs correspondantes.

  Par exemple, si un document contient une propriété Type, vous pouvez étendre l’échantillonnage aux valeurs de cette propriété. Le résultat final de l’échantillonnage est un ensemble de tables pour chacune des valeurs Type que vous avez spécifiée. Type = Car produit une table Car, alors que Type = Plane produit une table Plane.

Pour définir un schéma, procédez comme suit. Pour la méthode de mappage de délimiteur de table, vous devez effectuer des étapes supplémentaires pour définir des attributs et des valeurs pour le schéma.

1. Sous l’onglet DSN utilisateur de la fenêtre Administrateur des sources de données ODBC, sélectionnez votre nom DSN utilisateur Azure Cosmos DB, puis Configurer.

2. Dans la fenêtre Configuration DSN du pilote ODBC DocumentDB, sélectionnez Éditeur de schéma.

   

3. Dans la fenêtre Éditeur de schéma, sélectionnez Créer.

4. La fenêtre Générer le schéma affiche toutes les collections du compte Azure Cosmos DB. Cochez les cases en regard des conteneurs que vous souhaitez échantillonner.

5. Pour utiliser la méthode de mappage de conteneur, sélectionnez Échantillon.

   Ou, pour utiliser le mappage de délimiteur de table, effectuez les étapes suivantes pour définir des attributs et des valeurs pour définir l’étendue de l’exemple.

   1. Sélectionnez Modifier dans la colonne Définition de mappage pour votre DSN.

   2. Dans la fenêtre Définition de mappage, sous Méthode de mappage, sélectionnez Délimiteurs de table.

   3. Dans la zone Attributs, tapez le nom d’une propriété de délimiteur dans votre document que vous souhaitez utiliser pour définir l’étendue de l’échantillonnage (par exemple, Ville). Appuyez sur Entrée.

   4. Si vous souhaitez définir l’étendue de l’échantillonnage sur certaines valeurs pour l’attribut que vous avez entré, sélectionnez l’attribut, entrez une valeur dans la zone Valeur (par exemple, Seattle), puis appuyez sur Entrée. Vous pouvez ajouter plusieurs valeurs pour les attributs. Assurez-vous simplement que l’attribut approprié est sélectionné lorsque vous entrez des valeurs.

   5. Lorsque vous avez terminé d’entrer des attributs et des valeurs, sélectionnez OK.

   6. Dans la fenêtre Générer le schéma, sélectionnez Échantillon.

6. Dans l’onglet Mode Création, affinez votre schéma. Le Mode Création représente la base de données, le schéma et la table. La vue de la table affiche l’ensemble des propriétés associées aux noms de colonne (par exemple, Nom SQL et Nom de la source.).

   Pour chaque colonne, vous pouvez modifier le nom SQL, le type SQL, la longueur SQL, l’échelle, la précision et la valeur Nullable le cas échéant.

   Vous pouvez définir Masquer la colonne sur true si vous souhaitez exclure cette colonne des résultats de la requête. Les colonnes marquées Masquer la colonne = true ne sont pas retournées pour la sélection et la projection, bien qu’elles fassent toujours partie du schéma. Par exemple, vous pouvez masquer toutes les propriétés système Azure Cosmos DB requises commençant par _. La colonne ID est le seul champ que vous ne pouvez pas masquer, car il s’agit de la clé primaire dans le schéma normalisé.

7. Une fois que vous avez défini le schéma, sélectionnez Fichier>Enregistrer, accédez au répertoire dans lequel l’enregistrer, puis sélectionnez Enregistrer.

8. Pour utiliser ce schéma avec un DSN, dans la fenêtre Configuration DSN du pilote ODBC DocumentDB, sélectionnez Options avancées. Sélectionnez la zone Fichier de schéma, accédez au schéma enregistré, sélectionnez OK, puis OK à nouveau. L’enregistrement du fichier de schéma modifie la connexion du DSN afin de définir l’étendue des données et de la structure définie par le schéma.

Créer des vues

Si vous le souhaitez, vous pouvez définir et créer des vues dans l’Éditeur de schéma dans le cadre du processus d’échantillonnage. Ces vues sont équivalentes aux vues SQL. Elles sont en lecture seule et affichent les sélections et les projections de la requête SQL Azure Cosmos DB définie.

Procédez comme suit pour créer une vue pour vos données :

1. Sous l’onglet Exemple de vue de la fenêtre Éditeur de schéma, sélectionnez les conteneurs que vous souhaitez échantillonner, puis Ajouter dans la colonne Définition de la vue.

   

2. Dans la fenêtre Définitions de vue, sélectionnez Nouveau. Entrez un nom pour la vue (par exemple, EmployeesfromSeattleView), puis sélectionnez OK.

3. Dans la fenêtre Modifier la vue, entrez une requête Azure Cosmos DB, par exemple :

   SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle"

4. Sélectionnez OK.

   

Vous pouvez créer autant de vues que vous le souhaitez. Une fois que vous avez terminé de définir les vues, sélectionnez Échantillon pour échantillonner les données.

Important

Le texte de la requête dans la définition de la vue ne doit pas contenir de sauts de ligne. Sinon, une erreur générique survient lors de l’aperçu de la vue.

Soumettre des requêtes avec SQL Server Management Studio

Une fois que vous avez configuré un DSN utilisateur de pilote ODBC Azure Cosmos DB, vous pouvez interroger Azure Cosmos DB à partir de SQL Server Management Studio (SSMS) en configurant une connexion à un serveur lié.

1. Installez SQL Server Management Studio et connectez-vous au serveur.

2. Dans l’éditeur de requête SSMS, créez un objet de serveur lié pour la source de données en exécutant les commandes suivantes. Remplacez DEMOCOSMOS par le nom de votre serveur lié, et SDS Name par votre nom de source de données.

   USE [master]
   GO
   
   EXEC master.dbo.sp_addlinkedserver @server = N'DEMOCOSMOS', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SDS Name'
   
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEMOCOSMOS', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
   
   GO
   

Pour voir le nom du nouveau serveur lié, actualisez la liste des serveurs liés.

Pour interroger la base de données liée, entrez une requête SSMS. Dans cet exemple, la requête effectue une sélection dans la table du conteneur nommé customers:

SELECT * FROM OPENQUERY(DEMOCOSMOS, 'SELECT *  FROM [customers].[customers]')

exécutez la requête. Les résultats se présentent comme suit :

attachments/  1507476156    521 Bassett Avenue, Wikieup, Missouri, 5422   "2602bc56-0000-0000-0000-59da42bc0000"   2015-02-06T05:32:32 +05:00 f1ca3044f17149f3bc61f7b9c78a26df
attachments/  1507476156    167 Nassau Street, Tuskahoma, Illinois, 5998   "2602bd56-0000-0000-0000-59da42bc0000"   2015-06-16T08:54:17 +04:00 f75f949ea8de466a9ef2bdb7ce065ac8
attachments/  1507476156    885 Strong Place, Cassel, Montana, 2069       "2602be56-0000-0000-0000-59da42bc0000"   2015-03-20T07:21:47 +04:00 ef0365fb40c04bb6a3ffc4bc77c905fd
attachments/  1507476156    515 Barwell Terrace, Defiance, Tennessee, 6439     "2602c056-0000-0000-0000-59da42bc0000"   2014-10-16T06:49:04 +04:00      e913fe543490432f871bc42019663518
attachments/  1507476156    570 Ruby Street, Spokane, Idaho, 9025       "2602c156-0000-0000-0000-59da42bc0000"   2014-10-30T05:49:33 +04:00 e53072057d314bc9b36c89a8350048f3

Afficher vos données dans Power BI Desktop

Vous pouvez utiliser votre DSN pour vous connecter à Azure Cosmos DB à l’aide d’outils compatibles ODBC. Cette procédure vous montre comment vous connecter à Power BI Desktop pour créer une visualisation Power BI.

1. Dans Power BI Desktop, sélectionnez Obtenir les données.

   

2. Dans la fenêtre Obtenir des données, sélectionnez Autres>ODBC, puis Connecter.

   

3. Dans la fenêtre À partir d’ODBC, sélectionnez le DNS que vous avez créé, puis OK.

   

4. Dans la fenêtre Accéder à une source de données à l’aide d’un pilote ODBC, sélectionnez Par défaut ou Personnalisé, puis Connecter.

5. Dans la fenêtre Navigateur, dans le volet gauche, développez la base de données, le schéma, puis sélectionnez la table. Le volet des résultats inclut les données utilisant le schéma que vous avez créé.

   

6. Pour visualiser les données dans Power BI Desktop, cochez la case en regard du nom de la table, puis sélectionnez Charger.

7. Dans Power BI Desktop, sélectionnez l’onglet Données à gauche de l’écran pour confirmer que vos données ont été importées.

8. Sélectionnez l’onglet Rapport à gauche de l’écran, sélectionnez Nouveau visuel dans le ruban, puis personnalisez le visuel.

Dépannage

• Problème : vous obtenez l’erreur suivante quand vous essayez de vous connecter :

  [HY000]: [Microsoft][Azure Cosmos DB] (401) HTTP 401 Authentication Error: {"code":"Unauthorized","message":"The input authorization token can't serve the request. Please check that the expected payload is built as per the protocol, and check the key being used. Server used the following payload to sign: 'get\ndbs\n\nfri, 20 jan 2017 03:43:55 gmt\n\n'\r\nActivityId: 9acb3c0d-cb31-4b78-ac0a-413c8d33e373"}
  

  Solution : vérifiez que les valeurs Hôte et Clé d’accès que vous avez copiées à partir du Portail Azure sont correctes, puis réessayez.

• Problème : vous obtenez l’erreur suivante dans SSMS lorsque vous essayez de créer un serveur Azure Cosmos DB lié :

  Msg 7312, Level 16, State 1, Line 44
  
  Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "DEMOCOSMOS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
  

  Solution : un serveur Azure Cosmos DB lié ne prend pas en charge l’attribution de noms en quatre parties.

Étapes suivantes

• Pour en savoir plus sur Azure Cosmos DB, consultez la page Bienvenue dans Azure Cosmos DB.
• Pour plus d’informations sur la création de visualisations dans Power BI Desktop, consultez Types de visualisation dans Power BI.