Importer et query des données à l'aide du module complémentaire Excel Databricks

Le complément Excel Databricks connecte votre workspace Databricks à Microsoft Excel, ce qui permet d’importer des données Lakehouse gouvernées directement dans vos feuilles de calcul.

Cette page décrit comment utiliser le module complémentaire Excel Databricks pour importer et analyser des données de Databricks dans Excel. Vous pouvez parcourir et importer des tables Databricks via une interface intuitive où aucune connaissance de SQL n'est requise. Bien que le module complémentaire offre la flexibilité d'exécuter des query SQL personnalisées, cela reste facultatif.

Prérequis

• Avant d’utiliser le complément Excel, assurez-vous de l’avoir configuré.
• Vous disposez d’un accès Databricks SQL et d’au moins l’autorisation CAN USE sur un SQL Warehouse.

Sélectionnez un SQL Warehouse

Choisissez le SQL Warehouse à utiliser :

1. Dans le coin supérieur droit du volet du module complémentaire Databricks dans Excel, cliquez sur le menu déroulant.
2. Sélectionnez le SQL Warehouse que vous souhaitez utiliser.

Importer des données à partir de Databricks

Importez des données de Databricks dans Excel en sélectionnant une table, en écrivant une query SQL ou en important un tableau croisé dynamique.

remarque

Vous pouvez importer des vues de métriques Unity Catalog à l’aide de tableaux croisés dynamiques, de query SQL et de fonctions personnalisées.

Créer des tables croisées dynamiques

Pour créer un tableau croisé dynamique à partir de tables et de vues Unity Catalog dans Excel :

1. Dans le volet du module complémentaire Excel Databricks, sous le tab New import , sélectionnez Select data comme Import method .

2. Sous Catalog , sélectionnez la table à partir de laquelle vous souhaitez créer un tableau croisé dynamique, puis cliquez sur Select .

3. Sélectionnez la case à cocher Pivot Data .

4. (Facultatif) Sélectionnez Live query pour exécuter la query et mettre à jour automatiquement une nouvelle feuille de calcul pendant que vous créez le tableau croisé dynamique. Pour plus d'informations, consultez Live query.

5. Configurez Row , Column et Value en faisant glisser chaque champ vers la zone appropriée.

6. (Facultatif) Ajoutez un élément Filter . Pour plus d'informations sur les filtres, consultez Filter imported data.

7. (Facultatif) Définissez une limite de lignes pour votre importation.

8. Import your results. Choose one of the following:

   • Cliquez sur Enregistrer et importer pour enregistrer la query en vue d’une réutilisation dans le classeur Excel et importer les résultats.
   • Cliquez sur la flèche vers le bas, puis sur Importer les résultats pour importer les résultats sans enregistrer la query. Utilisez cette option lorsque vous souhaitez continuer à modifier une importation.

remarque

Les tableaux croisés dynamiques ne peuvent être importés que vers une nouvelle feuille.

Lorsque vous travaillez avec des métriques Unity Catalog dans des tableaux croisés dynamiques, vous pouvez voir Sum(measure) s’afficher dans les résultats. Il s’agit d’un comportement attendu et aucune agrégation supplémentaire n’a lieu. Excel exige que les valeurs possèdent une fonction d’agrégation, mais comme les données contiennent des valeurs uniques, aucune agrégation n’a lieu.

Live query

Une query en direct exécute votre pivot query et met à jour la feuille de calcul automatiquement à mesure que vous modifiez le tableau croisé, de sorte que vous visualisez les résultats au fur et à mesure que vous mettez à jour les champs, et non après avoir cliqué sur Import . Une query en direct réexécute la query après que vous avez modifié au moins une ligne ou colonne et une valeur. Pour revenir au flux standard, désactivez la fonctionnalité.

Lorsque la query en direct est activée, le tableau croisé dynamique est toujours créé et mis à jour dans une nouvelle feuille. De plus, le générateur de tableaux croisés dynamiques affiche un bouton Save import à la place de Import .

Si Excel se ferme pendant que vous modifiez un tableau croisé dynamique en direct, le tab Imports affiche une option de récupération afin que vous puissiez restaurer la dernière configuration enregistrée pour cet import.

Sélectionner les tables

Les données sont importées en tant qu’objet de tableau Excel. Vous pouvez déplacer le tableau ou renommer la feuille, et le module complémentaire Excel refresh les données dans le nouvel emplacement.

Pour importer des données à partir d'une table Databricks, procédez comme suit :

1. Dans le volet du module complémentaire Excel Databricks, sous le tab New import , sélectionnez Select data comme Import method .
2. Choose a table to import from the Catalog explorer. You can filter the catalog by owner, certification status, and other properties using filter.
3. Cliquez sur Sélectionner .
4. Sous Colonnes , cliquez sur la flèche vers le bas et désélectionnez les colonnes que vous ne souhaitez pas importer, ou laissez toutes les colonnes sélectionnées pour importer la table entière.
5. (Facultatif) Ajoutez un élément Filter . Pour plus d'informations sur les filtres, consultez Filter imported data.
6. (Optionnel) Pour afficher un exemple de l'importation, cliquez sur Prévisualiser .
7. (Optionnel) Définissez une limite de lignes pour restreindre le nombre de lignes importées.
8. (Facultatif) Pour identifier vos données importées, saisissez un Nom d'importation .
9. Sous Output Destination , choisissez d’importer les données dans une nouvelle feuille ou dans la feuille actuelle. Si vous importez vers la feuille actuelle, les données start à la référence de cellule que vous saisissez (par default A1).
10. Import your results. Choose one of the following:
    • Cliquez sur Enregistrer et importer pour enregistrer la query en vue d’une réutilisation dans le classeur Excel et importer les résultats.
    • Cliquez sur la flèche vers le bas, puis sur Importer les résultats pour importer les résultats sans enregistrer la query. Utilisez cette option lorsque vous souhaitez continuer à modifier une importation.

Écrire des queries SQL

La méthode d'importation Write SQL prend en charge les fonctions SQL et les procédures stockées.

Pour exécuter des queries personnalisées dans votre Workspace Databricks, procédez comme suit :

1. Dans le volet du module complémentaire Excel Databricks, sous l’ tab New import , sélectionnez Write SQL comme Import method.

2. Enter a name for your query to identify it later.

3. Rédigez une nouvelle query ou utilisez une query existante de votre workspace Databricks.

   • Rédigez votre query SQL dans l’éditeur. Vous pouvez exécuter une query sur n’importe quelle table de Unity Catalog à laquelle vous disposez des autorisations d’accès.

     • Cliquez sur Catalog explorer pour afficher vos schémas et vos tables.

   • Pour utiliser une query de votre workspace Databricks ou une query existante dans Excel, cliquez sur le dossier. Si vous utilisez une query existante provenant de votre workspace Databricks, les modifications apportées dans Excel ne sont pas reflétées sur Databricks.

remarque

Les query doivent être explicitement enregistrées dans Databricks à l'aide du bouton Save dans le coin supérieur droit de l'éditeur de query avant d'apparaître dans Excel.

4. (Optionnel) Pour ajouter des parameter de query, cliquez sur +Add à côté de parameter . Cliquez sur le parameter et saisissez le Parameter Name et la Parameter Value .

   • Pour la valeur du parameter, vous pouvez soit saisir une valeur spécifique, soit cliquer sur le bouton avec une boîte et une flèche pour spécifier une référence de cellule. Sélectionnez une cellule ou une plage de cellules et cliquez sur la flèche pour renseigner automatiquement la valeur du parameter.

5. Sous Output Destination , choisissez d’importer les données dans une nouvelle feuille ou dans la feuille actuelle. Si vous importez vers la feuille actuelle, les données start à la référence de cellule que vous saisissez (par default A1).

6. Pour prévisualiser les résultats de votre query, cliquez sur Run .

7. Import your results. Choose one of the following:

   • Cliquez sur Enregistrer et importer pour enregistrer la query en vue d’une réutilisation dans le classeur Excel et importer les résultats.
   • Cliquez sur la flèche vers le bas, puis sur Importer les résultats pour importer les résultats sans enregistrer la query. Utilisez cette option lorsque vous souhaitez continuer à modifier une importation.

Vous pouvez également utiliser des fonctions personnalisées pour ajouter des query parameter. Voir Écrire du SQL.

Filtrer les données importées

Lorsque vous importez des données en sélectionnant une table ou en créant un tableau croisé dynamique, vous pouvez appliquer des filtres pour affiner les résultats.

Les filtres de chaîne ne sont pas sensibles à la casse et sont en cascade. Lorsque vous appliquez plusieurs filtres, les valeurs disponibles pour chaque filtre dépendent des sélections des filtres précédents. Par exemple, si vous filtrez par pays puis ajoutez un filtre sur la ville, le filtre de ville ne propose que les villes situées dans le pays sélectionné.

Pour définir des filtres, cliquez sur + en regard de Filters , sélectionnez la colonne à laquelle vous souhaitez appliquer un filtre, puis saisissez votre condition de filtrage. Pour les filtres qui requièrent une valeur, vous pouvez procéder de l’une des manières suivantes :

• Enter the value.

• Pour générer une liste de jusqu'à 5 000 valeurs de filtre distinctes, vous pouvez utiliser :

  1. Cliquez sur Values , puis sur Get filter values .
  2. Cliquez sur la flèche vers le bas et sélectionnez une ou plusieurs valeurs dans la liste.

• Pour utiliser une référence de cellule :

  1. Cliquez sur Cells .
  2. Sélectionnez une cellule ou une plage de cellules.
  3. Cliquez sur le curseur .

Le tableau suivant décrit chaque filtre disponible et son entrée attendue.

Filtrer	Entrée attendue	Description
IS NULL	Aucun	Recherche les lignes pour lesquelles la valeur de la colonne est nulle.
IS NOT NULL	Aucun	Recherche les lignes pour lesquelles la valeur de la colonne n'est pas nulle.
EQUALS	Un nombre ou une chaîne de texte	Recherche les lignes dont la valeur de colonne correspond exactement à la valeur spécifiée.
NOT EQUALS	Un nombre ou une chaîne de texte	Recherche les lignes où la valeur de la colonne ne correspond pas à la valeur spécifiée.
STARTS WITH	Une chaîne de texte	Recherche les lignes dont la valeur de colonne commence par le texte spécifié.
ENDS WITH	Une chaîne de texte	Recherche les lignes où la valeur de la colonne se termine par le texte spécifié.
CONTAINS	Une chaîne de texte	Recherche les lignes où la valeur de la colonne contient le texte spécifié n’importe où dans la chaîne.

Filtrer	Entrée attendue	Description
IS NULL	Aucun	Recherche les lignes pour lesquelles la valeur de la colonne est nulle.
IS NOT NULL	Aucun	Recherche les lignes pour lesquelles la valeur de la colonne n'est pas nulle.
EQUALS	Un nombre ou une chaîne de texte	Recherche les lignes dont la valeur de colonne correspond exactement à la valeur spécifiée.
NOT EQUALS	Un nombre ou une chaîne de texte	Recherche les lignes où la valeur de la colonne ne correspond pas à la valeur spécifiée.
STARTS WITH	Une chaîne de texte	Recherche les lignes dont la valeur de colonne commence par le texte spécifié.
ENDS WITH	Une chaîne de texte	Recherche les lignes où la valeur de la colonne se termine par le texte spécifié.
CONTAINS	Une chaîne de texte	Recherche les lignes où la valeur de la colonne contient le texte spécifié n’importe où dans la chaîne.

Champs calculés

Un champ calculé est une colonne dérivée de données existantes, telle que profit compute à partir de revenue et cost. Le module complémentaire Excel ne prend pas en charge la création de champs calculés à l’aide de la méthode d’importation Select data . Pour ajouter un champ calculé, utilisez l’une des méthodes suivantes :

• Write SQL : utilisez la méthode d'importation Write SQL pour compute des colonnes calculées avec n'importe quelle expression SQL. Consultez Écrire des query SQL.
• Genie One : demandez à Genie One de renvoyer vos données avec les colonnes calculées dont vous avez besoin, puis importez les résultats. Voir Utiliser Genie One dans Microsoft Excel.

Databricks recommande d'utiliser Genie One pour les champs calculés.

Utiliser des fonctions personnalisées Databricks dans Excel

Le module complémentaire Excel fournit des fonctions personnalisées que vous pouvez utiliser dans les formules Excel pour importer des données depuis Databricks.

Sélectionner une table

La fonction DATABRICKS.Table importe des données à partir d’une table Unity Catalog.

Syntaxe :

Text

=DATABRICKS.Table(catalog_name.schema_name.table_name, [column1, ...], [limit])

Paramètres :

• catalog_name.schema_name.table_name (obligatoire) : le nom de table entièrement qualifié.
• columns (Facultatif) : Un tableau de noms de colonnes à importer. Omettez ce parameter pour importer toutes les colonnes.
• limit (facultatif) : le nombre maximal de lignes à importer. Omettez ce parameter pour importer toutes les lignes, jusqu'à la limite de 10 Mo.

Exemple :

Text

=DATABRICKS.Table("main.default.customers", {"customer_id", "customer_name"}, 100)

Cette formule importe les colonnes customer_id et customer_name de la table main.default.customers, avec une limite de 100 lignes.

Écrire du SQL

La fonction DATABRICKS.SQL exécute une query SQL qui utilise des query parameters et renvoie les résultats.

Syntaxe :

Spécifiez les parameters à l'aide de valeurs.

Text

=DATABRICKS.SQL("query_text", {parameter1_name, parameter1_value; ...})

Spécifiez les parameters à l'aide d'une plage de cellules. Définissez le nom et les parameter de valeur dans des cellules situées sur la même ligne.

Text

=DATABRICKS.SQL("query_text", {param_name_cell: param_value_cell; ...})

Paramètres :

• query_text (requis) : la query SQL à exécuter.
• parameters (requis) : mappage des valeurs de parameter à substituer dans la query.

Exemple :

Text

=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE longitude > :long_param AND latitude > :lat_param LIMIT 10", {"long_param",20; "lat_param",10})

=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE city = :city", M4:N4)

Cette formule exécute une query qui filtre les données de ventes par longitude et latitude, en utilisant les valeurs de parameter fournies.

Gérer les queries

Gérez vos importations existantes depuis la page Imports.

Modifier une importation existante

Pour modifier une importation existante :

1. Dans le volet du module complémentaire Databricks dans Excel, cliquez sur le tab Imports .
2. Recherchez l’importation que vous souhaitez modifier.
3. Cliquez sur le menu à trois points situé à côté de l'importation.
4. Cliquez sur Edit pour modifier votre import.

Refresh les données

Le complément Excel ne refresh pas automatiquement les données. La manière dont vous refresh les données dépend de la manière dont vous les avez importées. Les données importées à l'aide d'une méthode d'importation (sélectionner une table, écrire une query SQL ou créer un tableau croisé dynamique) refresh à partir de l'Imports tab . Les données importées à l'aide d'une fonction personnalisée doivent être recalculées.

Mettez à jour les importations avec les dernières valeurs de Databricks. Le complément exécute à nouveau la query d'origine ou la sélection de table et met à jour votre feuille de calcul avec des données fraîches :

• Pour refresh une importation unique :

  1. Dans le volet du module complémentaire Databricks dans Excel, cliquez sur le tab Imports .
  2. Cliquez sur refresh à côté de l'import que vous souhaitez actualiser.

• Pour refresh tous les imports :

  1. Click Refresh All in the Databricks Add-in pane.

important

Lors de l'actualisation des données, le module complémentaire Excel efface toutes les données existantes dans la table spécifiée et recharge les données les plus récentes depuis Databricks. Toutes les colonnes personnalisées que vous avez ajoutées à la table sont supprimées pendant le processus de refresh.

Les données importées à partir de fonctions personnalisées, telles que DATABRICKS.Table et DATABRICKS.SQL, ne refresh pas lorsque vous rouvrez un classeur. Pour refresh les données importées à partir de fonctions personnalisées, connectez-vous au module complémentaire Databricks, puis recalculez le classeur ou modifiez une valeur référencée par la fonction personnalisée.

Implications du partage

Lorsque vous partagez un classeur Excel contenant des données Databricks, tenez compte des implications suivantes en matière de sécurité et d’accès aux données :

Visibilité sur les données importées

Lorsqu'un destinataire effectue un refresh d'un import, le module complémentaire utilise les autorisations Unity Catalog du destinataire. S'ils n'ont pas accès aux données sous-jacentes, le refresh échoue.

Pour les classeurs où la confidentialité des données pose problème, vous pouvez utiliser la solution de contournement suivante :

1. Créez un classeur avec toutes les formules et importations nécessaires.
2. Supprimez les données importées de la feuille.
3. Partagez le classeur avec le destinataire.
4. Demandez au destinataire de refresh les données.

Le destinataire voit uniquement les données auxquelles il a accès en fonction de ses autorisations Unity Catalog.

Accès aux Workspace et aux assets de données

• Les utilisateurs n’ayant pas accès aux objets Unity Catalog référencés dans le classeur ne peuvent pas refresh les données. Pour refresh les données, les utilisateurs doivent disposer d’autorisations de lecture sur les tables et vues sous-jacentes dans Unity Catalog.
• Les utilisateurs doivent avoir accès à la table sous-jacente dans Databricks pour modifier les imports existants.

Visibilité de la query

Les utilisateurs disposant d'un accès en modification au classeur peuvent afficher les requêtes utilisées pour générer les données via le module complémentaire Databricks, même s'ils n'ont pas accès aux données sous-jacentes dans Unity Catalog.

Alternative pour enregistrer sous forme de template

Le module complémentaire Excel Databricks ne prend pas en charge l'enregistrement d'un classeur en tant que Template, mais vous pouvez partager un classeur afin que d'autres utilisateurs puissent voir les query importées. Consultez la rubrique Sharing implications pour connaître les considérations relatives à l'accès aux données et à la sécurité.

En guise de solution de contournement pour le partage d'un classeur en tant que Template, procédez de l'une des manières suivantes :

• Partagez le fichier local avec un autre utilisateur. Le destinataire peut renommer le fichier et voir les queries enregistrées.
• Sur SharePoint, partagez le classeur avec un autre utilisateur. Lorsqu’un autre utilisateur download le fichier, les imports enregistrés sont conservés.

Limitations

• Fonctions personnalisées : pour les fonctions personnalisées, les résultats de query sont limités à 25 MiB en raison des limitations de l’API SQL execution.
• Chargement des données : le chargement des données peut échouer si une cellule du classeur est en mode d’édition.
• Limite de lignes d'Excel Desktop : Excel Desktop prend en charge un maximum de 1 048 576 lignes par feuille.
• Limite de taille de fichier pour Excel for the web : Excel for the web prend en charge une taille de fichier de classeur maximale d'environ 25 Mo pour la consultation et la modification.
• Live query performance : avec la fonction Live query activée, chaque modification de tableau croisé dynamique exécute une nouvelle query sur votre SQL Warehouse. Les résultats ne sont pas mis en cache localement, de sorte que les modifications fréquentes sur de grands tableaux croisés dynamiques peuvent augmenter la latence et le coût du warehouse.