Superset – Datavisualisation & procédure stockée

Juin 14, 2021

Apache Superset

Superset – Dataviz libre

Superset est un logiciel open source de Datavisualisation utilisé au sein de Mindbaz pour visualiser nos données sur plus de 800 bases de données SqlServer.

Superset a besoin d’un dataset (une table ou une requête) issue d’une source de données (une bdd) pour afficher des données dans un dashboard. Or Superset n’autorise pas l’exécution de procédure stockée dans une requête d’un dataset.

Procédure stockée, dans quel cas en a-t-on besoin?

Notre besoin principal concrètement est de pouvoir générer un dashboard qui peut se connecter dynamiquement à l’une de nos bdds (grâce à un filtre affichant toutes nos bdds). Comme il n’est pas envisageable de créer 800 dataset se connectant à 800 bdds différentes, la seule solution est d’utiliser une procédure stockée qui prend en paramètre un nom de bdd en entrée et qui exécute notre requête en remote query.

De plus, une procédure stockée permet tout simplement de faire du sql dynamique plus avancé avec du tsql et des variables.C’est pourquoi de nombreux cas de figure peuvent exister nécessitant une procédure stockée.

Comment appeler une procédure stockée dans superset?

Une solution de contournement simple est d’utiliser OPENQUERY

OPENQUERY permet d’exécuter une requête sur un serveur lié spécifié. On peut donc lui passer une requête qui exécute notre procédure stockée en paramètre!

superset openquery

La requête a donc un format classique “SELECT * FROM “ et est donc acceptée par Superset.

Il vous suffit donc de créer cette requête dans le SQLLab, de l’exécuter et de cliquer sur le bouton “explore” pour pouvoir créer un dataset et l’utiliser dans un nouveau chart (de type table par ex).

Comment rendre dynamique les paramètres de ma procédure stockée?

Dans mon exemple, ma procédure stockée prend en paramètre param1 (int)  et param2 (texte). Je voudrais par exemple que l’utilisateur puisse choisir le param2 depuis une liste déroulante dans mon dashboard.

Pour ce faire, vous créez comme d’habitude un chart de type filter box qui permettra de sélectionner une colonne (“myFilterColumn” par ex) d’un dataset dans un formulaire.

superset filterbox

Et comme le système de filtrage de superset ajoute automatiquement une clause where à notre requête, nous ne pouvons pas modifier le paramètre passé à notre procédure stockée grâce à ça!

Pour contourner cela, il faut utiliser le système de templating jinja dans notre requête openquery afin d’écrire la valeur du filtre de notre paramètre au bon endroit dans le sql. Pour cela, on utilise la fonction filter_values(‘myFilterColumn’) qui retourne dans un dictionary les valeurs sélectionnées dans la liste déroulante.

 

Au final,  »param2 » est remplacé par 

''{{ ''.join(filter_values('myFilterColumn'))}}''

 

Dans la requête de notre dataset, cela donne :

superset requete
SELECT  * FROM  OPENQUERY(
LINKED_SERVER_NAME, 
'EXECUTE myStoredProcedure param1, ''{{ ''.join(filter_values('myFilterColumn'))}}''
WITH RESULT SETS((    id int,
    [name] varchar(255),
    id_sent int,
    nbAddressSent int,
    nbOpeners int,
    nbClickers int    ))'
    ) 

Le  ».join() permet d’avoir une chaîne vide ‘’ si le dictionary retourné par la fonction est vide!

et les {{ }} encapsulent notre code jinja.

 

Et voilà ! Vous n’avez plus qu’à tester votre chart dans un dashboard qui contient à la fois le filterbox et votre tableau.

 

L’inconvénient de cette méthode est que votre chart ne fonctionne que dans un dashboard qui a le bon filtre. Mais bon, l’avantage c’est que ça marche!