Eric Hill ( Eric Hill ), développeur principal de systèmes JMP, SAS
REMARQUE : Ceci n'est que le texte de l'article ; les captures d'écran ne sont pas incluses. Veuillez consulter la version ci-jointe pour obtenir l'intégralité de l'article.
Générateur de requêtes
Le nouvel outil JMP 12 pour importer vos données SQL dans JMP
(Mis à jour pour JMP 12.1)
Par Eric Hill, développeur de logiciels, JMP
Contenu
- Introduction. 2
Section 1 : Établir le lien. 3
Section 2 : Sélection des tables pour la requête. 6
Section 3 : Construction de la requête. 11
Ajout de colonnes. 13
Sauvegarde de la requête. 15
Exécution de la requête. 15
Répondre aux questions. 16
- Filtration. 19
Filtres d'invite. 23
- Échantillonnage. 25
Écrire votre propre SQL. 26
Section 4 : Gestion des requêtes en arrière-plan. 27
Panneau d'état de la requête 28
Fenêtre de requêtes Runninq..28
Section 5 : Partage de requêtes. 28
Section 6 : Exécution de requêtes à partir du langage de script JMP (JSL). 29
Quoi de neuf dans JMP 12.1 ?. 31
- Recherche. 31
Ajouter des tables associées. 32
Ouvrir comme modèle. 34
Modifier une formule de colonne calculée. 34
Corrections de bugs. 35
- Conclusion. 35
Introduction
JMP met à votre disposition une vaste gamme d'outils d'analyse, mais ces outils ne vous seront d'aucune utilité si vous ne parvenez pas à intégrer les données nécessaires à l'analyse dans JMP, comme vous le souhaitez. JMP permet depuis longtemps d'importer des données de bases de données relationnelles via Fichier > Base de données > Ouvrir une table . Ouvrir une table est une option satisfaisante, mais elle présente quelques limitations notables :
- La plupart des données dans les bases de données relationnelles sont stockées de manière normalisée, nécessitant des jointures pour obtenir toutes les données dont vous avez besoin, mais Table ouverte ne vous aide pas avec les jointures ; vous devez écrire le SQL vous-même.
- Bien que Table ouverte fournisse l' éditeur de formules pour créer une clause WHERE, il ne vous montre pas, par exemple, les valeurs distinctes d'une variable catégorielle pour vous aider à filtrer dessus.
- Il peut être difficile de partager vos requêtes en toute sécurité avec d'autres, car Table ouverte stocke votre mot de passe dans la chaîne de connexion des scripts JSL qu'il génère.
- Table ouverte ne vous aide pas avec les étapes de post-traitement qui sont nécessaires une fois les données importées, telles que le nettoyage des données, les types de modélisation et le formatage.
Générateur de requêtes a été conçu dès le départ pour répondre aux limitations de Table ouverte , vous aidant à créer des requêtes multi-tables avec un filtrage interactif invité qui peut être partagé en toute sécurité avec vos collègues.
Dans cet article, j'utiliserai le Générateur de requêtes avec une base de données d'exemple PostgreSQL gratuite appelée dellstore2 . PostgreSQL est une base de données open source téléchargeable gratuitement ici :
http://www.postgresql.org/download/
L'exemple de base de données dellstore2 peut être trouvé ici :
http://wiki.postgresql.org/wiki/Sample_Databases
L'exemple de base de données dellstore2 est également disponible pour d'autres plates-formes de base de données (MySQL, Oracle et SQL Server).
Section 1 : Établir le lien
Initiate Générateur de requêtes using File > Database > Query Builder from the menu:
Vous pouvez également le lancer depuis la première page de JMP Starter :
Enfin, sous Windows, vous pouvez également trouver un outil Générateur de requêtes dans la barre d'outils de la fenêtre d'accueil :
Après avoir lancé Générateur de requêtes , vous serez accueilli par la boîte de dialogue Sélectionner la connexion à la base de données :
The Sélectionner la connexion à la base de données dialog box is a subset of the Table ouverte window. Générateur de requêtes and Table ouverte use the same ODBC connections and in fact share connections, so any connections you make in either place will show up in both.
Cliquez sur « Nouvelle connexion… » pour vous connecter à votre base de données via un DSN existant ou pour en créer un nouveau. Vous serez invité à fournir les identifiants requis :
Après la connexion, JMP récupérera les schémas de la connexion pour vous aider à vous assurer qu'il s'agit de la connexion que vous souhaitez :
Vous pouvez également voir les tables disponibles dans le schéma sélectionné. Cliquez sur « Suivant » pour passer à l'étape suivante.
Nouveautés de JMP 12.2 : Remarquez les deux champs de recherche dans le panneau Schémas – Tables . Nouveauté de JMP 12.1, ils filtrent les listes Schémas et Tables au fur et à mesure de la saisie. Nous savons que beaucoup d'entre vous possèdent des centaines de schémas et de tables dans un schéma donné. Les nouveaux champs de recherche vous aident donc à affiner la liste et à trouver ce que vous cherchez.
Section 2 : Sélection des tables pour la requête
Figure 1 : Sélection des tables pour la requête
En cliquant sur Suivant , la boîte de dialogue Sélectionner les tables apparaît. Dans cette boîte de dialogue , sélectionnez les tables à inclure dans votre requête (voir Figure 1 ci-dessus). Sélectionnez les tables disponibles dans la liste et ajoutez-les aux listes Tables principales et Tables secondaires en haut à droite.
- La table principale d'une requête est généralement celle d'où toutes les lignes doivent être extraites, qu'elles aient ou non une valeur correspondante dans une autre table. Il s'agit de votre table de faits , en termes d'entreposage de données. Il ne peut y avoir qu'une seule table principale dans une requête.
- Les tables secondaires sont généralement des tables de recherche. Par défaut, les lignes des tables secondaires ne sont incluses dans la requête que si la valeur d'une colonne de la table principale correspond à celle d'une colonne spécifiée de la table secondaire . Il s'agit de vos tables de dimension , en termes d'entreposage de données. Vous pouvez ajouter autant de tables secondaires que vous le souhaitez à la requête.
Le panneau en bas à droite affiche des informations sur la table sélectionnée. L'onglet « Colonnes » affiche des informations sur chaque colonne de la table, notamment s'il s'agit d'une clé.
Nouveautés de JMP 12.2 :Notez la zone de recherche dans le panneau « Tables disponibles » , une nouveauté de JMP 12.1. Utilisez-la pour trouver la table souhaitée lorsque vos schémas en contiennent beaucoup.
Le panneau Instantané du tableau affiche un instantané des données afin que vous puissiez vous assurer que le tableau contient les informations dont vous avez besoin :
En général, lorsqu'on construit une requête, on a une ou plusieurs questions en tête auxquelles on souhaite répondre, et ces questions influencent le choix des tables à inclure dans la requête. Les données que nous explorons proviennent d'une société fictive de location de DVD. Les questions auxquelles je souhaite répondre sont les suivantes :
- Quels genres de films génèrent le plus de revenus ?
- Quels acteurs génèrent le plus de revenus ?
La table de location contient un enregistrement pour chaque location de DVD effectuée au cours de la vie de l'entreprise ; elle sera donc la table principale pour cette requête. Le panneau Colonnes montre que la table de location comporte des colonnes avec des relations de clé étrangère vers les tables d'inventaire et de clients :
Figure 2 : Clés étrangères
Une relation de clé étrangère (également appelée contrainte de clé étrangère) signifie que les valeurs d'une colonne d'une table doivent obligatoirement figurer dans la colonne de clé primaire d' une autre table. La figure 2 montre que les valeurs de la colonne inventaire_id de la table locations doivent figurer dans la colonne de clé primaire de la table inventaire , que celles de la colonne identifiant client de la table locations doivent figurer dans la colonne de clé primaire de la table client , et que celles de la colonne identifiant du personnel de la table rentals doivent figurer dans la colonne de clé primaire de la table personnel . Pour cette requête, je recherche des informations sur le film loué, plutôt que sur le client du membre du personnel impliqué dans la location. J'ajouterai donc la table inventaire à la requête comme table secondaire.
En regardant le panneau Colonnes de la table d'inventaire , je peux également voir une relation de clé étrangère avec la table de film :
Figure 3 : détails du tableau d'inventaire
La table des films est donc également incluse dans la requête. En examinant les détails de la table , je vois une colonne « taux de location » , qui devrait être nécessaire pour calculer les revenus, mais je ne vois rien concernant la catégorie du film ni les acteurs.
Figure 4 : Colonnes du tableau des films
Cependant, en consultant la liste des tables disponibles , je vois une table « film_actor » et une table « film_category » , qui semblent associer l'identifiant d'un film à ses acteurs et à sa catégorie, ainsi que des tables « acteurs » et « catégories » contenant plus d'informations sur les acteurs et les catégories. Je souhaite donc inclure ces tables dans la requête. Cependant, si je les ajoute simplement, un problème se produit :
Figure 5 : Échec de la jonction automatique
Les icônes « stop » avec un « X » au milieu signifient que JMP n'a pas pu joindre automatiquement ces deux tables ; je vais donc devoir configurer les jointures manuellement. Si vous constatez que la fonction de jointure automatique de JMP ne fonctionne pas toujours correctement, vous pouvez la désactiver via le triangle rouge à côté de « Sélectionner les tables pour la requête » .
Si je sélectionne public.film_actor (t14) et que je clique sur le bouton « Modifier la jointure » ( ), la boîte de dialogue « Modifier la condition » s'affiche, me permettant de configurer la jointure. Initialement, la boîte de dialogue ressemble à ceci :
Figure 6 : Modification de la condition de jointure
Comme aucune condition de jointure n'existe encore, je souhaite joindre film_actor.film_id à inventaire.film_id . Pour ce faire, je sélectionne simplement la table inventaire dans la liste de la colonne de gauche , puis film_id dans les deux listes :
Figure 7 : Modification de la condition de jointure
De même, je peux définir la condition de jointure pour public.film_category sur inventaire.film_id = catégorie_film.film_id . Après cela, la situation s'améliore :
Figure 8 : Les jointures entre les tables sont valides
Toutes les tables secondaires affichent l'icône de jointure externe gauche du diagramme de Venn. Prenons l'exemple de la jointure réalisée entre « acteur de cinéma » et « inventaire » . Une jointure externe gauche signifie que toutes les lignes de la table « inventaire », la table de gauche dans ce cas, seront incluses dans la requête, qu'il y ait ou non une correspondance dans la table « acteur de cinéma », mais que seules les lignes de la table « film_actor » correspondant à une ligne de la table « inventaire » seront incluses.
Au fait, les valeurs t1 , t2 , t3 , etc., entre parenthèses après le nom des tables, correspondent aux alias que JMP attribue aux tables lors de leur ajout à la requête. Les alias facilitent la génération d'un code SQL correct et rendent ce dernier plus lisible. Vous pouvez modifier l'alias d'une table en faisant un clic droit sur la table dans la liste des tables primaires ou secondaires , puis en sélectionnant « Modifier l'alias » . Les alias de table ne peuvent contenir ni espaces ni caractères spéciaux.
Si vous avez besoin d'une autre fonction qu'une jointure externe gauche, vous pouvez modifier le type de jointure à l'aide des cases à cocher de la boîte de dialogue « Modifier la jointure externe gauche » . Le type de jointure et l'icône du diagramme de Venn s'adapteront à l'état des cases à cocher.
Figure 9 : Modification du type de jointure
Pour vérifier que vos jointures sont correctement configurées, cliquez sur le bouton « Aperçu de la jointure » . La requête sera alors exécutée et un instantané des premières centaines de lignes de données s'affichera pour vous permettre de vérifier que tout est correct.
Maintenant que nous avons toutes les tables dans la requête dont nous avons besoin, nous pouvons cliquer sur Créer une requête pour faire apparaître le Générateur de requêtes lui-même, où nous pouvons spécifier les colonnes à inclure dans la requête, les critères de filtrage à utiliser, la manière dont les données résultantes doivent être ordonnées, et bien plus encore.
Section 3 : Construction de la requête
Lorsque la fenêtre Générateur de requêtes s'affiche initialement pour cette requête, elle ressemble à ceci :
- Le champ Nom de la requête contient le nom de la requête, qui correspond par défaut au nom de la table principale. Il s'agit du nom attribué à la table de données JMP résultant de l'exécution de la requête et du nom de fichier par défaut lors de l'enregistrement de la requête. La source de données indique le nom de la source de données utilisée par la requête.
- La zone « Tables » affiche les tables actuellement dans la requête. Pour ajouter ou supprimer des tables ou modifier des jointures, cliquez sur le bouton « Modifier… » .
- La liste des colonnes disponibles affiche toutes les colonnes de toutes les tables de la requête. Sélectionnez-les et faites-les glisser vers les panneaux « Colonnes incluses » , « Filtres » ou « Trier par » . Vous pouvez également effectuer la même opération en cliquant avec le bouton droit de la souris.
Nouveauté de JMP 12.1 : le panneau « Colonnes disponibles » intègre désormais une zone de recherche pour trouver une colonne particulière dans une longue liste. Comme les autres zones de recherche , elle filtre au fur et à mesure de la saisie. - Le panneau « Colonnes incluses » affiche une grille de toutes les colonnes de votre requête. Utilisez-la pour définir le format et le type de modélisation des données de la colonne après leur importation dans JMP. Vous pouvez également spécifier des agrégations et des options de regroupement pour les colonnes si vous souhaitez que vos données soient synthétisées avant leur importation dans JMP.
Il existe également un onglet Échantillon dans ce panneau pour générer des échantillons aléatoires de données sur le serveur, pour les bases de données qui prennent en charge l'échantillonnage. - L'onglet Aperçu de la requête de cette zone se met automatiquement à jour pour afficher un aperçu des données renvoyées par votre requête. L'onglet SQL affiche le code SQL généré par le Générateur de requêtes en fonction des sélections actuelles. L'onglet Script post-requête vous permet de spécifier un script JSL à exécuter une fois votre requête terminée. L'onglet État de la requête affiche l'état des requêtes exécutées en arrière-plan et vous permet de les annuler.
- Le panneau Filtres vous permet de spécifier des critères de filtrage des données (en créant la clause WHERE) et de transformer le filtre en une invite qui s'exécute à chaque fois que vous exécutez la requête.
- Le panneau Trier par vous permet de spécifier la ou les colonnes à utiliser pour ordonner les lignes renvoyées (clause ORDER BY).
Ajout de colonnes
Chaque requête nécessite des colonnes. Ajoutez des colonnes à la requête en utilisant l'une des méthodes suivantes :
- Cliquez sur le bouton Ajouter tout pour ajouter toutes les colonnes disponibles de toutes les tables de la requête
- Sélectionnez des colonnes spécifiques dans la liste Colonnes disponibles et cliquez sur Ajouter ou faites-les glisser.
- Double-cliquez sur une colonne dans la liste Colonnes disponibles .
Pour cette requête, j'ajouterai film_id , titre et taux de location de la table film , catégorie_id et nom de la table catégorie , et acteur_id , prénom et nom de famille de la table acteur .
Figure 10 : Après l’ajout initial de colonnes
En utilisant les colonnes Nom JMP, Type de modélisation et Format des Colonnes incluses , je peux contrôler le nom, le type de modélisation et le format de chaque colonne après l'importation des données dans JMP.
Figure 11 : Après avoir ajusté les détails de la colonne
Pour les besoins de cette analyse, je préférerais également que les noms des acteurs soient concaténés dans une seule colonne plutôt que divisés en colonnes distinctes pour les prénoms et les noms de famille . Pour ce faire, je peux ajouter une colonne calculée en sélectionnant « Ajouter une colonne calculée » dans le menu en forme de triangle rouge du panneau « Colonnes disponibles » .
Cela ajoutera une colonne nommée Calc1 à la liste Colonnes disponibles . Je peux également faire un clic droit dessus et utiliser « Renommer la colonne » pour la renommer en Nom de l'acteur , l'ajouter à la requête et supprimer « prénom » et « nom de famille » . Voici à quoi ressemble l'aperçu de la requête après cette opération :
Figure 12 : Aperçu de la requête avec colonne calculée
(Ces valeurs de nom d'acteur peuvent sembler un peu étranges, mais ce sont en fait les noms corrects de la base de données. Les noms d'acteur semblent avoir été générés en associant aléatoirement un prénom à un nom de famille.)
Sauvegarde de la requête
J'ai passé suffisamment de temps sur cette requête et je souhaite l'enregistrer afin de ne pas perdre mon travail. Cliquez simplement sur le bouton Enregistrer en bas à droite de la fenêtre du Générateur de requêtes pour enregistrer la requête. Vous serez invité à saisir un nom de fichier, le texte du champ « Nom de la requête » étant le nom par défaut. Je nommerai la requête « film_category_actor » . Un message s'affichera en bas à gauche de la fenêtre du Générateur de requêtes confirmant l'enregistrement :
Enregistrer une requête crée un fichier avec l'extension .jmpquery . De retour dans la fenêtre « Générateur de requêtes » , vous remarquerez que le nom de la requête a été modifié pour correspondre au nom du fichier : il s'agit désormais de catégorie_film_acteur .
Si vous devez créer une autre requête similaire à celle que vous avez ouverte, mais que vous souhaitez conserver l'original, vous pouvez utiliser le bouton Enregistrer sous… pour enregistrer une copie sous un nom différent.
Exécution de la requête
Je peux également exécuter la requête à ce stade pour créer une table de données JMP contenant toutes les lignes de la base de données. Cliquez sur le bouton Exécuter la requête en bas de la fenêtre du Générateur de requêtes pour exécuter la requête. Par défaut, JMP exécute les requêtes en arrière-plan afin que vous puissiez continuer à travailler pendant leur exécution. Utilisez l'onglet État de la requête en bas au centre de la fenêtre pour consulter l'état des requêtes en cours d'exécution dans cette fenêtre , ou utilisez Affichage > Requêtes en cours d'exécution pour consulter l'état de toutes les requêtes en cours d'exécution en arrière-plan. Cette requête ne prend qu'une ou deux secondes et renvoie 88 020 lignes :
Figure 13 : Résultats de la requête dans la table de données JMP
Notez que la table de données JMP résultante comporte 3 scripts et une variable de table SQL définie :
Répondre aux questions
À ce stade, je suis presque en mesure de répondre aux questions initiales, mais je dois prendre une décision : dois-je laisser la base de données synthétiser les résultats, ou dois-je importer les données brutes dans JMP et laisser JMP faire le travail ? Si les données brutes ne sont pas trop volumineuses pour la mémoire et que leur importation ne prend pas trop de temps, alors importer les données brutes dans JMP et les analyser présente de nombreux avantages ; JMP est bien plus performant qu'une base de données relationnelle pour analyser les données.
Pour la tâche en question, il suffit d'utiliser la plateforme de distribution sur les noms de catégorie et d'acteur , en utilisant le taux de location comme variable de fréquence . Si j'ordonne les histogrammes obtenus par ordre croissant , j'obtiens un résultat qui répond à nos deux questions :
Figure 14 : Utilisation de la plateforme de distribution sur le résultat de la requête
Si, toutefois, l'intégration de toutes les données brutes était trop volumineuse ou trop longue, je peux utiliser la colonne « Agrégation » du panneau « Colonnes incluses » pour que la base de données synthétise les données. Pour cela, je devrai répondre aux deux questions l'une après l'autre.
Pour répondre à la question « Quels genres de films génèrent le plus de revenus ? » , je peux supprimer les colonnes relatives aux acteurs de la requête et demander à la base de données de totaliser les groupes de tarifs de location par nom de catégorie . Je peux ensuite trier le résultat selon le tarif de location totalisé. Voici la procédure :
- Cliquez sur la colonne Agrégation pour Tarif de location et sélectionnez Somme .
Notez que la fonction Grouper par est activée pour toutes les variables sans agrégation spécifiée. Dès qu'une colonne d'une requête spécifie une agrégation, toutes les colonnes de la requête qui n'en spécifient pas doivent être des colonnes Grouper par . - Cliquez avec le bouton droit sur la colonne Nom de la variable pour Tarif de location et sélectionnez Trier par .
- Sélectionnez SOMME(t4.taux_de_location) dans le panneau Trier par et cliquez sur le bouton fléché pointant vers le bas ( Descendant ).
Le résultat ressemble à ceci :
De même, en retirant les colonnes de catégories et en remettant les colonnes d'acteurs, je peux répondre à la question Quels acteurs génèrent le plus de revenus ? :
Filtration
Revenons un instant à l'époque où notre requête récupérait toutes les données brutes et où nous utilisions la plateforme de distribution pour répondre à nos questions sur les revenus. Lors de l'analyse dans JMP, nous pouvons notamment demander le script d'analyse JMP :
I can then paste that script into the Script post-requête panel of Générateur de requêtes:
Figure 15 : Ajout d'un script JSL post-requête
Ce script s'exécutera désormais à chaque fois que j'exécuterai la requête.
J'aimerais maintenant affiner mes questions. Plutôt que de regarder les revenus par catégorie et par acteur pour toutes les locations de la base de données, je préférerais peut-être les analyser en fonction du pays de location du film, du magasin où il a été loué ou d'une période donnée.
La table de location contient les champs date_de_location et identifiant client ; j'ai donc presque ce dont j'ai besoin. Nous avons vu précédemment qu'identifiant client est une référence de clé étrangère vers la table client . Cette table nous permet d'obtenir le pays d'origine du client et le magasin où le DVD a été loué.
En cliquant sur le bouton « Changement… » dans le panneau « Tables » du Générateur de requêtes, je peux revenir en arrière et ajouter d'autres tables à la requête. Pour obtenir les informations sur le pays, je dois ajouter les tables « client » , « adresse » , « ville » et « pays » . Heureusement, grâce aux relations de clés configurées par l'administrateur de cette base de données, toutes les tables se joignent automatiquement.
Après avoir ajouté les tables supplémentaires nécessaires, de retour dans le Générateur de requêtes, je peux ajouter les colonnes nécessaires à la requête : date_de_location , identifiant_du_magasin et pays . J'ai également défini des noms JMP conviviaux et corrigé le type de modélisation de identifiant_du_magasin :
Figure 16 : Après avoir ajouté les colonnes Date de location, ID du magasin et Pays
Commençons par filtrer selon le pays du client. Je fais un clic droit sur la variable t7.pays dans le panneau « Colonnes incluses » et je sélectionne « Filtrer par ». Un filtre par pays apparaît alors dans le panneau « Filtres » :
Figure 17 : Filtre par pays
À ce stade, je peux simplement faire défiler la liste des pays et cocher ceux qui m'intéressent, puis exécuter la requête, et seules les lignes de ces pays seront incluses.
De même, je peux ajouter des filtres pour identifiant_du_magasin et date_de_location , après quoi le panneau Filtres ressemble à ceci :
Lorsque j'ajoute le filtre date_de_location , comme il s'agit d'une variable continue, j'obtiens par défaut un filtre de comparaison simple . Je souhaiterais plutôt un filtre de plage . À l'aide du menu triangulaire rouge à côté de t1.date_de_location , je peux choisir le type de filtre « Plage » :
Maintenant, le filtre pour t1.date_de_location ressemble à :
Cela me permet de spécifier une plage de dates. Ainsi, si je souhaite trouver toutes les locations du second semestre 2005, je peux :
Figure 18 : Filtre de plage de dates
Tous les types de filtres du Générateur de requêtes permettent de les désactiver efficacement, ce qui permet d'ajouter plusieurs filtres à une requête, même s'ils ne sont pas tous actifs. Pour les filtres catégoriels affichant une liste de valeurs, sélectionner l'option (Tous) en haut désactive le filtre. Pour les filtres demandant des valeurs numériques, définir la valeur sur manquant (.) désactive le filtre (ou une partie du filtre). Ainsi, si je souhaite consulter toutes les locations à partir du 1er janvier 2006, je peux procéder ainsi :
Maintenant, le panneau Filtres pour ma requête ressemble à la figure 19 ci-dessous.
| Figure 19 : Utilisation de « Manquant » pour désactiver le filtre | Figure 20 : Filtre avec deux groupes OU |
Avec les filtres sélectionnés illustrés à la figure 19 , l'exécution de la requête renvoie désormais des lignes où le pays du client est la France ou l'Allemagne, et où le DVD a été loué au magasin 2 à compter du 1er janvier 2006. Par défaut, plusieurs filtres sont associés par un ET ; une ligne doit donc correspondre à tous les filtres pour apparaître dans le résultat. Si vous avez besoin d'un filtre OU, cliquez sur le bouton OU , ce qui créera un autre groupe de filtres, et les deux groupes de filtres seront associés par un OU. Par exemple, le filtre illustré à la figure 20 ci-dessus renvoie les locations dont la catégorie est Drame ou dont le titre contient « Seau » .
Filtres d'invite
Bien qu'il soit pratique de pouvoir spécifier des critères de filtrage dans le Générateur de requêtes , vous pouvez également créer une requête destinée à d'autres utilisateurs qui n'ont pas besoin de l'interface utilisateur complète du Générateur de requêtes , mais qui souhaitent pouvoir saisir des critères de filtrage différents à chaque exécution. Le plus simple est de sélectionner « Toutes les invites à l'exécution » dans le menu en forme de triangle rouge, à côté de « Filtres » .
Cela transforme tous les filtres de la requête en filtres d'invite . Si vous souhaitez que certains filtres soient uniquement intuitifs, vous pouvez activer ou désactiver l'invite pour chaque filtre via le menu triangulaire rouge.
Après avoir défini Toutes les invites à l'exécution , lorsque j'exécute cette requête, une invite s'affiche pour définir les valeurs de filtre comme je le souhaite :
Les paramètres par défaut de l'invite seront ceux qui ont été sélectionnés dans Générateur de requêtes (ou dans le fichier de requête enregistré si la requête est exécutée directement à partir du fichier) avant l'exécution de la requête.
Vous pouvez également personnaliser les libellés utilisés dans l'invite. Par exemple, supposons qu'au lieu de « Pays : », je souhaite que l'invite indique « Pays du client : ». De retour dans le Générateur de requêtes, utilisez le menu triangulaire rouge du filtre t7.pays et sélectionnez « Configurer l'invite » . Une boîte de dialogue s'affiche pour vous permettre de modifier le libellé de l'invite et la table qui fournit les niveaux de catégorie de l'invite :
Échantillonnage
Si votre base de données prend en charge l'échantillonnage aléatoire, l'onglet « Échantillon » en haut au centre du Générateur de requêtes affichera les options d'échantillonnage prises en charge par votre base de données. J'utilise PostgreSQL pour les exemples de cet article, qui prend en charge l'échantillonnage aléatoire simple d'un nombre spécifié de lignes :
SQL Server et Oracle prennent en charge différentes options d'échantillonnage, l'onglet Échantillon reflétera donc les différentes options :
| Figure 21 : Options d'échantillonnage de SQL Server | Figure 22 : Options d'échantillonnage Oracle |
L'échantillonnage est effectué par la base de données , avant que les données ne soient téléchargées sur JMP. C'est donc un bon moyen de limiter le nombre de lignes récupérées à partir de requêtes qui, autrement, récupéreraient un nombre prohibitif de lignes.
Écrire votre propre SQL
Nous sommes conscients que, malgré les nombreuses fonctionnalités actuelles et futures du Générateur de requêtes , il peut arriver que vous ayez besoin d'une requête dont l'exécution n'est pas directement prise en charge par le Générateur de requêtes . De plus, vous avez peut-être déjà écrit et minutieusement débogué du code SQL qui exécute exactement la requête que JMP doit exécuter. Le Générateur de requêtes propose deux options pour fournir votre propre code SQL :
- Expression de filtre personnalisée – Si le Générateur de requêtes fait ce dont vous avez besoin en termes de sélection de tables et de spécification des colonnes à inclure, mais que vous souhaitez simplement inclure une expression SQL personnalisée dans le cadre du filtre, vous pouvez sélectionner Ajouter une expression personnalisée dans le menu du triangle rouge Filtres :
| Figure 23 : Ajout d'une expression de filtre personnalisée | Figure 24 : Le filtre résultant |
Entrez le code SQL souhaité dans la zone Expression personnalisée , à condition qu'il s'agisse d'un code SQL valide pour la requête.
Si un filtre d' expression personnalisée est défini comme filtre d'invite, l'expression personnalisée apparaîtra dans la boîte de dialogue d'invite de filtre et sera modifiable.
- Convertir la requête entière en requête SQL personnalisée – Si vous souhaitez simplement écrire votre propre SQL pour une requête entière, alors, après avoir lancé Générateur de requêtes et spécifié la connexion de données, vous pouvez utiliser le menu triangle rouge en haut à gauche de Générateur de requêtes à côté du champ Nom de la requête et sélectionner Convertir en SQL personnalisé :
Figure 25 : Conversion en SQL personnalisé
La fenêtre « Générateur de requêtes » passe en mode SQL personnalisé, vous offrant un large espace pour saisir votre propre code SQL. Si vous avez commencé à créer la requête de manière interactive, la zone « SQL personnalisé » sera initialisée avec le code SQL généré selon vos sélections (voir figure 26 ci-dessous).
Figure 26 : Requête SQL personnalisée
Vous pouvez modifier le code SQL selon vos besoins ou le coller depuis une autre source. Cliquez ensuite sur le bouton « Mettre à jour » pour mettre à jour l' Aperçu de la requête . Quelques mises en garde :
- Vous ne pouvez pas effectuer de filtrage avec une requête SQL personnalisée.
- Une fois une requête convertie en SQL personnalisé et modifiée, si vous rétablissez le mode interactif, vous perdrez toutes les personnalisations apportées au SQL. Le Générateur de requêtes ne tente pas d'interpréter le SQL.
Section 4 : Gestion des requêtes en arrière-plan
Le Générateur de requêtes exécute les requêtes en arrière-plan par défaut, et il est également possible d'exécuter des requêtes en arrière-plan depuis JMP Scripting Language (JSL) (voir Section 6 ). JMP propose deux méthodes pour afficher la liste des requêtes en cours d'exécution en arrière-plan et les arrêter si nécessaire.
Panneau d'état de la requête
Dans la partie inférieure centrale du Générateur de requêtes, se trouve l'onglet « Statut de la requête » . Toutes les requêtes d'arrière-plan lancées depuis cette fenêtre du Générateur de requêtes y apparaîtront :
Pour interrompre une requête en cours d'exécution, sélectionnez-la simplement dans la liste et cliquez sur le bouton Arrêter . Si des lignes ont été récupérées, JMP ouvrira une table de données contenant les lignes déjà récupérées. Le nom de la table de données indiquera que le résultat est partiel.
Fenêtre de requêtes Runninq
Dans le menu Affichage de la fenêtre d'accueil ou d'une autre fenêtre JMP incluant un menu, sélectionnez « Exécution de requêtes » . Une fenêtre similaire à l'onglet « Statut de la requête » du Générateur de requêtes s'ouvre, mais la fenêtre « Exécution de requêtes » contient toutes les requêtes exécutées en arrière-plan, quel que soit leur mode d'appel (générateur de requêtes ou langage de script JMP) :
Ici encore, sélectionnez une requête et cliquez sur Arrêt pour arrêter son exécution, ouvrant ainsi tout résultat partiel.
Section 5 : Partage de requêtes
Dans l'introduction, il a été mentionné que Table ouverte créait des scripts difficiles à partager, car le mot de passe de la base de données est inclus dans la chaîne de connexion. Le Générateur de requêtes fonctionne différemment. Si vous enregistrez un fichier .jmpquery pour une requête dont la connexion nécessite un mot de passe et que vous ouvrez ce fichier dans un éditeur de texte, vous obtiendrez un résultat similaire à celui-ci :
Vous pouvez constater que le nom d'utilisateur ( UID ) est présent dans la chaîne de connexion, mais vous voyez ensuite PWD=%_PWD_% . « %_PWD_% » n'est pas le mot de passe ; il s'agit d'un espace réservé placé à cet endroit par le Générateur de requêtes. Lorsque le Générateur de requêtes voit cet espace réservé, il sait qu'un mot de passe était requis initialement. Ainsi, si cette requête est ouverte alors que l'utilisateur qui l'ouvre n'est pas déjà connecté au DSN dvdrental de Postgres , le Générateur de requêtes saura qu'il doit le lui demander.
Et qu'en est-il du DSN lui-même ? Que se passe-t-il si vous envoyez ce fichier .jmpquery à un utilisateur JMP qui n'a pas de DSN nommé Postgres dvdrental défini sur sa machine ? Table ouverte ne peut pas fonctionner dans ce cas, mais le Générateur de requêtes en informe l'utilisateur et lui donne la possibilité de se connecter à un autre DSN (probablement connecté à la même base de données) ou d'en créer un nouveau.
Étant donné que différents utilisateurs peuvent avoir des DSN différents définis sur leurs machines, certaines organisations ont opté pour des connexions sans DSN . Générateur de requêtes ne peut actuellement pas convertir une chaîne de connexion basée sur un DSN en une chaîne sans DSN. Cependant, si vous modifiez une chaîne de connexion dans le script d'une requête pour spécifier un CONDUCTEUR et un SERVER au lieu d'un DSN , Générateur de requêtes propagera cette connexion sans DSN dans tous les scripts ou fichiers .jmpquery qu'il génère.
Section 6 : Exécution de requêtes à partir du langage de script JMP (JSL)
Les requêtes créées par le Générateur de requêtes peuvent également être exécutées à partir de scripts JSL (JMP Scripting Language) . La meilleure solution consiste à enregistrer la requête au format .jmpquery , à l'ouvrir depuis JSL et à l'exécuter. Supposons, par exemple, que vous ayez une requête enregistrée dans le dossier c:\users\public\temp nommé pg_rentals.jmpquery . Voici quelques opérations possibles avec ce fichier de requête depuis JSL :
- Ouvrez un fichier .jmpquery dans le générateur de requêtes :
ouvrir( « c:\users\public\temp\pg_rentals.jmpquery » );
requête= ouvrir( « c:\users\public\temp\pg_rentals.jmpquery »,Privé );
- Open the query in Générateur de requêtes for editing:
requête<< Modifier;
requête<< Courir();
- Exécutez la requête au premier plan. La table de données résultante s'ouvrira dans JMP une fois la requête terminée. dtResult sera une référence à la table de données résultante.
dtRésultat=requête<< Exécuter au premier plan();
- Exécutez la requête au premier plan, mais n'ouvrez pas la table de données résultante dans JMP ; gardez-la privée. dtResult sera une référence à la table de données résultante.
dtRésultat=requête<< Exécuter au premier plan(Privé );
- Exécutez la requête en arrière-plan. La table de données résultante s'ouvrira dans JMP une fois la requête terminée. L'option « Exécuter en arrière-plan » ne peut pas renvoyer de référence à la table de données résultante, car la requête est simplement lancée en arrière-plan. Les exemples suivants montrent comment obtenir une référence à la table de données résultante lors de l'exécution de requêtes en arrière-plan.
requête<< Exécuter en arrière-plan();
- Exécutez la requête en arrière-plan. Si elle aboutit, appelez la plateforme de distribution sur la table de données de résultat ( résultat de la requête est une variable JSL spéciale au sein d'un script En cours d'exécution terminé qui fait référence à la table de données de résultat). Si la requête est annulée par l'utilisateur ou comporte une erreur, écrivez un message dans le journal JMP.
requête<< Exécuter en arrière-plan(
En cours d'exécution terminé (
résultat de la requête<< Distribution( Distribution nominale( Colonne( :titre ) ) );
),
En cours d'exécution annulé (Écrire( "\!NLa requête a été annulée !" ) ),
En cas d'erreur (Écrire( "\!Erreur lors de l'exécution de la requête !" ); )
);
- Exécutez la requête en arrière-plan. Une fois la requête terminée, appelez la fonction MyFunc. La table de données résultant de la requête est toujours transmise en premier paramètre à la fonction.
::MaFonction= Fonction({ dt },{ Local par défaut },
Écrire( "\!nNombre de locations : ", Nrows(dt ) );
dt<< Distribution( Distribution nominale( Colonne( :titre ) ) );
);
requête<< Exécuter en arrière-plan(En cours d'exécution terminé (::MaFonction ) );
Si l'ouverture d'une requête à partir d'un fichier .jmpquery séparé dans votre script JSL n'est pas satisfaisante, vous pouvez obtenir le script brut pour créer un objet scriptable de requête à partir du menu du triangle rouge en haut à gauche de la fenêtre du générateur de requêtes :
Par exemple, l'appel de Enregistrer le script d'exécution dans la fenêtre de script pour une requête simple me donne :
La nouvelle requête SQL renvoie un objet scriptable de requête SQL, tout comme Ouvrir le fait lors de l'ouverture d'un fichier .jmpquery et peut être utilisé de toutes les mêmes manières.
Quoi de neuf dans JMP 12.1 ?
Recherche
De nombreuses personnes avec lesquelles nous échangeons possèdent des montagnes de données dans des centaines de schémas, tables et colonnes dans leurs bases de données. Dans la version JMP 12.1 du Générateur de requêtes, nous avons ajouté des zones de recherche aux listes de schémas, tables et colonnes pour vous aider à trouver rapidement ce que vous cherchez :
Toutes ces zones de recherche se mettent à jour au fur et à mesure que vous tapez, et vous avez la possibilité d'utiliser des expressions régulières pour affiner votre recherche.
Ajouter des tables associées
Dans la boîte de dialogue Sélectionner les tables pour la requête , si une table que vous avez ajoutée à la requête possède des références de clé étrangère vers d'autres tables, vous pouvez ajouter toutes ces tables associées à la requête en cliquant sur le bouton Ajouter des tables associées .
Ouvrir comme modèle
Il est fréquent de devoir créer une requête très similaire à une autre requête que vous avez créée et enregistrée dans un fichier .jmpquery. Vous pouvez toujours ouvrir la requête précédemment enregistrée, la modifier et utiliser « Enregistrer sous » pour l'enregistrer dans un autre fichier .jmpquery. Cependant, si vous appuyez accidentellement sur « Sauvegarder » , votre requête d'origine sera écrasée. La fonctionnalité « Ouvrir comme modèle » permet d'éviter ce problème. L'option « Ouvrir comme modèle » est disponible depuis le menu contextuel de la fenêtre d'accueil pour les fichiers .jmpquery sur Mac et Windows :
Lorsque vous ouvrez un fichier .jmpquery comme modèle, vous ouvrez en réalité une copie de la requête d'origine, sans lien avec le fichier .jmpquery sur le disque. Si vous avez ouvert une requête comme modèle, apporté des modifications et cliqué par inadvertance sur le bouton « Sauvegarder » , vous serez invité à fournir un fichier pour l'enregistrer.
Vous pouvez également ouvrir un fichier .jmpquery comme modèle à partir du langage de script JMP (JSL) :
ouvrir( « C:\Users\Public\Temp\rentals.jmpquery »,(En tant que modèle );
Modifier une formule de colonne calculée
Dans JMP 12.0, vous pouviez ajouter une colonne calculée à votre requête. Celle-ci était calculée sur le serveur de base de données et les valeurs résultantes étaient renvoyées dans la requête. Cependant, il était impossible de modifier la formule d'une colonne calculée précédemment créée. Il fallait alors supprimer la colonne calculée et en ajouter une nouvelle, ce qui pouvait nécessiter la recréation d'une formule complexe. Dans JMP 12.1, vous pouvez désormais modifier une colonne calculée. Il vous suffit de faire un clic droit sur la colonne dans « Colonnes disponibles » et de sélectionner « Modifier la formule » .
Corrections de bugs
Comme prévu, nous avons corrigé plusieurs bugs dans le Générateur de requêtes de JMP 12.1, dont certains ont été signalés par nos clients. Si vous rencontrez un problème avec le Générateur de requêtes, veuillez le signaler au support technique SAS afin que nous puissions le résoudre.
Conclusion
Le Générateur de requêtes est une nouvelle fonctionnalité de JMP 12 (mise à jour dans JMP 12.1) qui vous permet de créer des requêtes SQL multi-tables hautement personnalisées sur vos bases de données relationnelles. L'objectif est de faciliter l'intégration de vos données dans JMP sous une forme prête à l'analyse, plus rapidement qu'auparavant, et de faciliter le partage sécurisé de vos requêtes. Nous espérons que vous l'essayerez et que vous nous ferez part de vos commentaires.eric.hill@jmp.com) sur ce qui fonctionne bien et ce qui ne fonctionne pas pour vous.
