Over the last month I've been using Superset to demonstrate the tool capacities in multiple situations. There are situations where Superset shines and others where it's hard to find full guidance. This post will detail how to use URL parameters to filter your Superset dashboards.
Superset is a project that has been developed within Airbnb years ago by Maxime Beauchemin — also the original creator of Airflow. Superset entered the Apache program in 2017 to become Apache Superset. In January 2019 Maxime founded Preset. Preset is the commercial company that mainly maintains Superset today — around 70 employees. The startup provides a Cloud version, that includes a free tier, with enterprise features on top of the tool. The objective is clear: become one of the leading data visualization tool while being open-source.
I detailed Preset fundraising last year in my weekly newsletter. If you don't want to miss this kind of information about data field you can subscribe to the Data News.
Vocabulary
As a preambule I want to give you the basic vocabulary. In Superset you have multiple entities:
- You first start with a database, the connection with your datasource.
- On top of your database you can have physical datasets, a table from your datasource (with calculated columns if needed) or virtual datasets the preparation layer of the data in Superset, like a view with no materialization. Virtual datasets are defined with a SQL query.
- Then you can explore your datasets and create charts. Charts are the obvious way to visualize data in Superset. You can setup your charts by using the explore menu. You can also create custom charts in React thanks to the plugin system... but this is for another post 🤭.
- And finally you can add charts in dashboards using a sticky grid layout.
- In dashboards you can have scoped native filters narrowing your charts' data when applied. Native filters have their own menu. By legacy in Superset you can also have filter box that does the same job. The filter box is a special chart that allows you to filter data for visualisation charts — this feature will be soon deprecated.
The needs
While working on Superset I needed to pass parameters in the dashboards' URL in order to filter my data. The use cases are for example:
- you want to redirect from a dashboard's chart to another dashboard with filters already set
- you want to include a dashboard link in an external application (intranet portal, a data catalog, slack notifications, etc.) and you need to filter depending on this application context
- you want to share your dashboard to someone with filters saved
Now let's explore all the possibilities Superset offers below.
I want to share my dashboard with filters saved
This is the easiest part of this post. The features have already been implemented in the UI. When you are on a dashboard you can click on the 3 dots on the top right and click on Copy permalink to clipboard it will generate a link with native filters saved.
Under the hood Superset is creating an entry in the key_value table with the filters value saved in pickle in the value column. You can query this table with your permalink uuid.
I want to preselect my Filter Box
Warning: if you still use filter boxes you have to be aware it will soon be deprecated.
So you have one or multiple filter boxes and you want to preselect the values with the URL in order to filter directly the data.
For this answer you will need to get the chart id of your filter box — also called slice_id. You can do it either by exploring the charts' library directly in the charts' url, or by inspecting the source code on a dashboard looking for a div with a data-test-chart-id attribute. Once you have your id you just have to add a GET param preselect_filters with the following value: {"<chart_id>":{"<column>":["value1"]}}.
For instance if you want to filter on color blue and yellow for a filter box with 4 as id you will have something like below in your URL. Note the importance of double quotes as the content of the param should be JSON valid.
/superset/dashboard/1/?preselect_filters={"4":{"color":["blue", "yellow"]}}
I want to preselect my Native Filters
Superset decided to implement a new way to filter dashboards called native filters as a replacement for filter boxes. Native filters are located in a collapsible bar on the left side of the screen. This is a bit more complex because the GET parameter format is longer than in the previous version but it works well.
So let's imagine we have a native filter on a color column and we want to filter on the green color.
In order to filter you will have to feed the native_filters param with the following (awful) pattern. The param uses the RISON2 format which is a JSON serialization more friendly for url params. In this pattern you will have to replace:
- <id> — contains the native filter id. You can get the filter id when you edit your "Dashboard properties" in the advance part by looking at the JSON metadata.
- <column> — the column you want to filter on. Be careful you have to use the column name and not the filter name.
- <value> — the value you want to select. Depending on what you want to do you can change the operation, in the example I use a
op:INbut it can be something else.
native_filters=(NATIVE_FILTER-<id>:(__cache:(label:'<value>',validateStatus:!f,value:!('<value>')),extraFormData:(filters:!((col:<column>,op:IN,val:!('<value>')))),filterState:(label:'<value>',validateStatus:!f,value:!('<value>')),id:NATIVE_FILTER-<id>,ownState:()))
Then it will redirect you to a new page with native_filters_key in the URL, it means that your filters selection has been saved in the key_value metadata table.
How to use these URLs
I just explained how you can from URLs set the filters in any Superset dashboard, but in order to complete the journey you'll also have to add links in another dashboard.
To to that you will have to create a calculated column in your dataset with a HTML content. This html content will be rendered in a table cell, so if you add a <a> it will do the trick. As an example if you use Postgres you can do something like below with a CONCAT.
CONCAT('<a href="/superset/dashboard/16/?preselect_filters={%224%22:{%22color%22:[%22blue%22, %22yellow%22]}}">', color, '</a>')Conclusion
To be honest this is an awful way to preselect filters and I hope in the future we'll have a more friendly or concise way to do it. I got stuck on this issue for some hours and to save other people time I decided to write a blog post on it.
I did not mention the url_param GET parameter that allows us to directly filter the datasets' SQL queries. It will be for another post because this one is already long. If you want me to cover a specific topic do not hesitate to reach me.
Superset grown a lot in the last year to become a serious alternative to usual dashboarding solutions like Tableau, Looker, PowerBI or Metabase (if OSS). The energy the Preset team put in the product to fix previous flaws is huge. I think the biggest issue of Superset is the complexity of the product compared to others, deploying Superset means a backend, frontend, database, Redis, etc. It could be complex compared to alternatives.
1 List of ASCII encoding reference: https://www.w3schools.com/tags/ref_urlencode.asp
2 Here a library that can help you doing RISON. If you want to try RISON conversion you can use this Observable notebook.