How to run a stored procedure on Superset

by | Nov 29, 2023

Superset is an open source datavisualization software used within Mindbaz. It is perfect to visualize our data on more than 800 Sql Server databases.

Sommaire masquer
1. Stored procedure, when is it needed?
2. How to call a stored procedure in superset?
3. How can I make the parameters of my stored procedure dynamic?
4. Glossary of stored procedures and data visualization

Superset needs a dataset, i.e. a table or a query, from a data source (like a database) to display data in a dashboard. 

    Problem: Superset does not allow execution of stored procedure in a dataset query.

    Mindbaz data experts give you their guide to execute a stored procedure on Superset.

    Stored procedure, when is it needed?

    Generate an effective dashboard

    Our main need is to be able to generate a dashboard that can dynamically connect to one of our databases. We will use a filter that displays all our databases. It is not possible to create 800 datasets connecting to 800 different databases: the rendering would be unreadable.

    To achieve this, use a stored procedure that takes a database name as input and executes the query as a remote query.

    Filter efficiently

    A stored procedure also allows to do more advanced dynamic sql with tsql and variables. This is why many cases may exist that require a stored procedure. Indeed, when many databases are distributed on several dozens of servers, the simple procedure is not possible anymore. The solution is to call a stored procedure.

    How to call a stored procedure in superset?

    A simple workaround is to use OPENQUERY.

    OPENQUERY allows to execute a request on a specified linked server. So we can pass it a request that executes our procedure stored in parameter!

    how_to_call_stored_procedure_superset_mindbaz_email_inbox

    The query has a classic format “SELECT * FROM ” and is therefore accepted by Superset.

    You just have to create this query in the SQLLab, to execute it and to click on the “explore” button to be able to create a dataset and to use it in a new chart (of type table for example).

    How can I make the parameters of my stored procedure dynamic?

    In my example, my stored procedure takes as parameters param1 (int) and param2 (text). I would like the user to be able to choose param2 from a dropdown list in my dashboard.

    1. Create a filter box chart

    This chart allows to select a column (“myFilterColumn” for example) of a dataset in a form.

    stored_dynamic_procedure_superset_mindbaz_email_inbox

    This graphic lets you select a column (“myFilterColumn” for example) from a dataset in a form.

    2. Use the filter_values function

    To bypass this filter, we need to use the jinja templating system in our openquery. The goal is to write the filter value of our parameter to the right place in the sql.

    To do this, we use the function filter_values(‘myFilterColumn’) which returns in a dictionary the values selected in the dropdown list.

    At the end, ”param2” is replaced by 

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

     Results 

    In our dataset request, it would be :

    use_filter_values_fonction_to_run_superset_procedure
    <span class="token keyword">SELECT</span>  <span class="token operator">*</span> <span class="token keyword">FROM</span>  <span class="token keyword">OPENQUERY</span><span class="token punctuation">(</span>
LINKED_SERVER_NAME<span class="token punctuation">,</span> 
<span class="token string">'EXECUTE myStoredProcedure param1, '</span>'{{ <span class="token string">''</span><span class="token punctuation">.</span><span class="token keyword">join</span><span class="token punctuation">(</span>filter_values<span class="token punctuation">(</span><span class="token string">'myFilterColumn'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>}}<span class="token string">''</span>
<span class="token keyword">WITH</span> RESULT SETS<span class="token punctuation">(</span><span class="token punctuation">(</span>    id <span class="token keyword">int</span><span class="token punctuation">,</span>
    <span class="token punctuation">[</span>name<span class="token punctuation">]</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    id_sent <span class="token keyword">int</span><span class="token punctuation">,</span>
    nbAddressSent <span class="token keyword">int</span><span class="token punctuation">,</span>
    nbOpeners <span class="token keyword">int</span><span class="token punctuation">,</span>
    nbClickers <span class="token keyword">int</span>    <span class="token punctuation">)</span><span class="token punctuation">)</span>'
    <span class="token punctuation">)</span>
    SQL

    z 

    e ”.join() allows to have an empty string ” if the dictionary returned by the function is empty!

    and the {{ }} encapsulate our jinja code.

    Warning: with this method, your chart will only work in a dashboard that has the right filter. 

    You now know our 2-step technique to run a stored procedure on Superset, Mindbaz’ datavisualization tool. 

    All you have to do now is test your chart in a dashboard that contains both the filterbox and your chart.

    For the neophytes, we have prepared a glossary of terms used: 

    Glossary of stored procedures and data visualization

    Datavizualisation

    Data visualization (or Dataviz) groups together the means that allow to graphically represent mass data. Datavisualization is particularly useful to facilitate the understanding of the collected data. 

    The advantages of using a data visualization tool

    • Save time
    • Accelerate decision-making processes
    • Interact with the data
    • Understand the information
    • Easily record data
    • Present key figures internally and externally

    Stored procedure

    A stored procedure is a block of code created to execute a set of SQL statements. These procedures are stored in a Database Management System (DBMS) and can be called to be executed on another server.

    Open source software

    Open source software means that the software code is publicly available. Anyone with knowledge of web development can use and modify the program. The goal of open source tools is to encourage collaboration. 

    Dataset

     Dataset is a set of data that can be consulted, analyzed and modified. 

    4 popular beliefs about Gmail deliverability

    idees recues sur la delivrabilite des emails chez Gmail

    +130 spam words that will help you boost your email deliverability in 2022

    article about spam words by Mindbaz email solution

    Catégories

    Tags

    There’s no content to show here yet.