Investigate suitable SQL playground

[valiafetisov]

Goal

List of compared SQL playgrounds with a conclusion on which to use and how to integrate it

Context

Since we want to provide analysts the possibility to investigate data stored in the database, we eventually want to expose interface for running SQL queries over the read-only database instance. Therefore, the whole integration will be done in several steps:

1. Investigate if there is a good SQL playground that we can use (this issue)
2. Separate users/sessions from the rest of the database (will be done as part of the document-model-libs integration, i.e. https://github.com/makerdao-ses/switchboard-boilerplate/issues/31 of before)
3. Expose additional read-only replica of the database, but protected via our tokens/cookies
4. Integrate SQL playground

Note: I don't think "SQL playground" is a commonly used term, so we rather need to investigate what tools data analyst use in order to explore databases and perform complex SQL operations

Potential outcome of this issue might also be that there is no good open-source service available and we should instead provide instructions on how to use external services/desktop apps of their choice.

Tasks

• [ ] Determine the list of qualities to judge contenders (e.g.: licence, self-hosted, popularity)
• [ ] Discuss list of qualities
• [ ] List contenders
  • [ ] Provide screenshot of the interface
  • [ ] Provide link to the demo or docker command to run it locally, if possible
• [ ] Discuss contenders
• [ ] Conclude

[BracketJohn]

pointers:

• metabase
• redash
• apache superset

[KirillDogadin-std]

Let's first agree on list of qualities. My suggestion:

• popularity (as above)
• can/how easy host ourselves (as above) / can we run local instance in the docker
• how easy is it to preconfigure? (e.g. setting up the db url while we run)
• lightweight-ness
  • depends on what we want to provide as part of the end product. Here we would have to agree on what is 'better'. If the tool is heavy, it will be a pain to run locally to test things out. E.g. superset requires 6gb of ram (donk. (Cmd+f: Once you have Docker for Mac installed.)) So i would go for something lightweight and let everyone who is not comfortable with whatever is shipped use their own tool.
• ability to export data for manual processing. E.g. if a person would like to export the query and play with it via numpy, matplotlib, ... stack, should be possible.
• visualisation tooling
• authentication / security features.
• drill down presence
• sql editor ux