Connecting Superset

=Connecting Superset=

"Apache Superset is a modern data exploration and visualization platform" - Zymonic Systems can be configured such that selected filters can be denormalised (all data cisible to the user in a filter stored in a single table for fast access and filtering) and single sign on to Superset can be achieved with Zymonic's OAuth server capability.

Choosing How To Host
Both Zymonic Application Server and Superset will need access to a common database (this can be configured as a separate database to Zymonic's main database using the 'denormalised database' system options). To use the Zymonic toolkit to install / configure Superset the system that Superset is installed on will need at least a 'minimal' Zymonic install that allows the Configuration to be loaded (can be done by NFS share / copying the XML dir) and access to the DB server that contains the system options - if desired/needed for security reason then this DB access can be disabled once the install is done and superset is running (but will be needed again if restarting Superset).

To allow Zymonic to automatically trigger the login of the user to Superset (using OAuth), Zymonic and Superset must appear to be coming from the same server and port - this can either be done with reverse pass proxying (the 'connect_superset' command will generate a recommended config) or can be done using mod_wsgi (not tried at this point) to run Superset as an Apache module.

Installation
The installation and connection is designed to be as automated as possible.

The first step is to use the Zymonic Installer Toolkit (or a wrapper e.g. zymobuild) to bring your system up to date and a 'config_build' of each system you wish to connect to Superset.

Next install the superset manifest:

sudo zymonic_toolkit.pl Installer install --manifests superset

The installer will ask some additional questions related to Superset:

Superset bin dir - The bin dir of superset (where superset_config.py resides) (currently set to ) Superset bin dir: /home/superset/.local/bin/ Superset group - The group that the superset server will run as. (currently set to ) Superset group: superset Superset hostname - The hostname users will use to connect to Superset (currently set to ) Superset hostname: md5.zednax.com Superset ip address - The IP address superset will listen on (currently set to ) Superset ip address: 0.0.0.0 Superset log directory - The superset log file - will be rotated daily (currently set to ) Superset log directory: /var/log/superset Superset master system - The system of which the superusers are also Admin users of Superset (currently set to ) Superset master system: ztsm Superset port - The port on which superset will listen (currently set to ) Superset port: 8088 Superset proxy path - The proxy path being used in apache (with no slashes) e.g. "analytics" Superset proxy path: analytics Superset SQLite file - The path of the Superset SQLite file (usually [sqlite home]/.superset/superset.db (currently set to ) Superset SQLite file: /home/superset/.superset/superset.db  Superset SSL cert - The path of the Superset SSL certificate file (currently set to )  Superset SSL cert: /etc/ssl/wildcard/wildcard.zednax.com.2019.crt  Superset SSL key - The path of the Superset SSL key file (currently set to )  Superset SSL key: /etc/ssl/wildcard/wildcard.zednax.com.2019.key  Superset user - The user that the superset server will run as. (currently set to )  Superset user: superset

The values above are examples and may or may not be correct for you system.

Next ensure that the the Python and PIP present and correctly installed (Python needs to have been compiled with sqlite flag).

A determination then needs to be made about which user to run Apache Superset as - bear in mind that both the apache process and superset process will need to write to the Superset SQLite database. So far having a superset and an apache user with permissions on the SQLite DB allowing access to both users has been tested.

Once Python (with SQLite) and PIP are installed then run the following command as the selected user:

zymonic_toolkit.pl Superset install_superset

It is possible that Python module requirements may not be properly installed (e.g. currently, on the 4th Jan 2021, PyJWT needs to be installed at a lower version that is current); if so they will need to be manually resolved and the install command re-run - this can be done a number of times if necessary, but .profile may need the extra lines removing from the end of it as duplicates will be created.

Connecting Superset and Zymonic
Firstly link the ZymonicSuperset.xml file into any systems that you wish to connect to Superset - the connect will fail with a message telling you to do this if you forget.

sudo ln -s /root/trunk/modules/ZymonicSuperset/xml/ZymonicSuperset.xml /etc/zymonic/ztsm/ Then do a config build.

sudo zymonic_toolkit.pl System config_build --system ztsm

Finally run the connect_superset command

sudo zymonic_toolkit.pl Superset connect_superset --system ztsm --username alexm --password ! --hostname md4.zednax.com

This command will generate some Apache configuration - check with your system administrator where to put the Apache configuration fragment and that Apache has the Proxy Modules enabled.

Starting Superset
To start Superset run

sudo zymonic_toolkit.pl Superset start_superset --system ztsm

This will start Superset in the foreground _and_ will show the full command being used - you will need to discuss with your sysadmin the best way of routinely starting / running Superset.

A successful start should look something like: 04-01-2021 13:39:27 - Running: sudo su - apache -c 'export LC_ALL=C.UTF-8; export LANG=C.UTF-8; PATH=${PATH}:/home/superset/.local/bin; /var/www/.local/bin//superset run --cert "/etc/ssl/wildcard/wildcard.zednax.com.2019.crt"  --key "/etc/ssl/wildcard/wildcard.zednax.com.2019.key"  -h 0.0.0.0 -p 8088 --with-threads --reload  --debugger' Loaded your LOCAL configuration at [/var/www/.local/bin/superset_config.py] logging was configured successfully INFO:superset.utils.logging_configurator:logging was configured successfully /var/www/.local/lib64/python3.6/site-packages/flask_caching/__init__.py:192: UserWarning: Flask-Caching: CACHE_TYPE is set to null, caching is effectively disabled. "Flask-Caching: CACHE_TYPE is set to null, " No PIL installation found INFO:superset.utils.screenshots:No PIL installation found * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off INFO:werkzeug: * Running on https://0.0.0.0:8088/ (Press CTRL+C to quit) INFO:werkzeug: * Restarting with stat Loaded your LOCAL configuration at [/var/www/.local/bin/superset_config.py] logging was configured successfully INFO:superset.utils.logging_configurator:logging was configured successfully /var/www/.local/lib64/python3.6/site-packages/flask_caching/__init__.py:192: UserWarning: Flask-Caching: CACHE_TYPE is set to null, caching is effectively disabled. "Flask-Caching: CACHE_TYPE is set to null, " No PIL installation found INFO:superset.utils.screenshots:No PIL installation found WARNING:werkzeug: * Debugger is active! INFO:werkzeug: * Debugger PIN: 228-075-511

Note that there is a warning about WSGI being a development server - see https://github.com/apache/incubator-superset/issues/8673 about alternate installs if this would be more appropriate to your set-up.

Creating a Source
A source is a denormalised table and a corresponding entry (and permissions) in Superset's configuration database. A source is created by going to the 'Superset Sources List' in the Zymonic Admin UI (via Admin Area or in the 'Zymonic' menu in desktop mode) and clicking add. The fields are documented in the UI and the process should look something like:



The source should then be available in Superset...

Creating a Chart
You can either navigate directly to the Superset UI through the host/port you configured during the install or each time you save a source there will be a link to Superset. Once in Superset you will create a chart - https://superset.apache.org/docs/creating-charts-dashboards/ is the official documentation, but bear in mind that the 'standard' implementation will be individual charts being embedded in Zymonic rather than a Dashboard and that the sources will be already created. There are other guides and documents that give a better description e.g. https://medium.com/datadriveninvestor/create-your-first-sales-dashboard-in-apache-superset-c6a7f3d628d6

Once you have create a chart you will have a preview screen like the following:



At the top is a button labelled '' which gives a pop-up with a fragment of HTML that will generate an iframe and a copy button - use the copy button to copy the content.

In the Zymonic UI open the 'Superset Charts List' (from the Zymonic Menu in desktop mode or the Admin Area) and create a new entry, then paste the html source into 'Chart Definition'. Fill in the remaining required fields including which role permission to apply to the chart and which Source was used to create the chart.



Once you have saved the chart you should be able to open it from the Desktop menu.



Adding Superset Charts to a System
Creating individual charts and sources 'by hand' as described in the previous two sections would be used for development (and in principle could be used to adding custom charts to a running system) - however most charts and source would be created using 'DefaultProcess' XML e.g.

Note that the chart json is automatically extracted and decoded when creating charts via the Zymonic UI however when creating XML for DefaultProcesses the developer will need to manually extract the chart json and decode it or save the chart on a development system and take the decoded JSON from there.

Updating Sources
Updating sources, i.e., updating the denormalised data, is done by running:

[Sun Jan 10 19:39:22] amasidlover@md4 ~ $ sudo zymonic_toolkit.pl Superset update_sources --system ztsm --username alexm --password ! password: 10-01-2021 19:41:13 - Updated Orders List Method update_sources completed.

The source update will occur if it was last done longer ago than the update frequency configured on the source, if not the update will report:

[Sun Jan 10 19:41:14] amasidlover@md4 ~ $ sudo zymonic_toolkit.pl Superset update_sources --system ztsm --username alexm --password ! password: 10-01-2021 19:43:55 - Orders List not updated - last update was 2021-01-10 19:41:13, minimum seconds between updates is 300 Method update_sources completed.

This should be scheduled via a cron job on a production system.

Once SR: 100035 is implemented the sources will be automatically updated whenever the filter is run and will only be updated if the denormalised data is older than the records in the filter's source tables.

Logs
Logs can be found in the location configured during the install in the example '/var/log/superset/superset.log' e.g.

[Mon Jan 04 13:53:39] amasidlover@md4 ~ $ tail /var/log/superset/superset.log from authlib.integrations.flask_client import OAuth ModuleNotFoundError: No module named 'authlib' logging was configured successfully No PIL installation found * Running on https://0.0.0.0:8088/ (Press CTRL+C to quit) * Restarting with stat logging was configured successfully No PIL installation found * Debugger is active! * Debugger PIN: 228-075-511

It is also possible to enable debugs for all OAuth requests made to the Zymonic server by setting the following system option:

update zz_system_options set option_value = 'true' where option_name = "zz_oauth_debugging"

This will cause debugs (with an 'oauth' prefix in the filename) to be generated in the debug directory configured for the system.

SQLite
The SQLite DB can be accessed using

sqlite3 [path to superset db]

The path will usually be the home dir of the user that superset was installed as, followed by .superset/superset.db.

Useful SQLite specific commands:

.tables

.schema [table name]

.quit

The key tables that Zymonic uses and updates are:


 * dbs - the list of denormalised DBs that Superset connects to
 * tables - the list of denormalised tables that Superset connects to
 * table_columns - the list of columns in the denormalised tables
 * ab_permission - the list of permissions that Superset can apply to its 'objects'
 * ab_view_menu - the list of 'objects' e.g. tables or DBs that can be viewed by users
 * ab_permission_view - a list of which permissions apply to which viewable objects
 * ab_role - a list of roles (Zymonic roles will be created in here as [system]_[Zymonic role]
 * ab_permission_view_role - a list of which roles have which 'permission view' permissions.

Resetting
If you ever need to reset zymonic and superset to start from "scratch", the following can be run.

zTSM db: delete from zz_superset_source; delete from zz_superset_charts;

superset sqlite db: delete from table_columns; delete from tables; delete from ab_view_menu where name like '%[znames used for sources]%'; delete from ab_permission_view where view_menu_id not in (select id from ab_view_menu); delete from ab_permission_view_role where permission_view_id not in (select id from ab_permission_view);