from Hacker News

Ask HN: Tools to visualize data in SQL databases?

by dyml on 2/13/22, 4:52 PM with 133 comments

I’d like to hear what tools you use to easily visualize the data in a sql table?

Preferably I’d just like to click on a MariaDB table and receive some plots and statistics on the columns.

Whats your experience on this?

Edit: to clarify, I don’t want to visualize the database itself (Schema’s, keys etc). Just the data within it.

  • by btown on 2/13/22, 7:23 PM

    This may be an unpopular opinion, but if you have US$70/mo to spare, it's hard to beat Tableau for this exact use case.

    "Connect to an arbitrary database, create a view that joins numerous tables (including foreign tables, via blending) together, load to columnar storage on a local SSD for performance if necessary, add arbitrary derived columns (including well-defined lateral lookups for things like 'annotate this action with the date of the first action of this action's user' [0]), group by 4 of the derived columns, map two of the groupings to nested dimensions along the horizontal axis and two to the vertical axis, and show the sum or count at each cell in a resulting table, then when satisfied, drill down into a slice and turn it into a bar chart with colors that match your branding needs" - every one of those clauses can be accomplished with drag-and-drop mouse commands almost at the speed of thought.

    And once you get the hang of it, there's zero impedance mismatch with hand-rolled SQL, it's just way faster to iterate on, especially with schemas where you may not remember all the columns available to you, and especially when you're doing so over screenshare with non-technical colleagues.

    [0] https://help.tableau.com/current/pro/desktop/en-us/calculati...

  • by vijucat on 2/14/22, 2:32 AM

    I love Metabase (https://www.metabase.com/) and used it successfully with business users, too (small hedge fund). If it's not too much data, getting it out as .csv and using R + data.table + dplyr, etc; = incredible productivity. See [1]

    Use Excel, QlikSense and Tableau if Business Users need visualization. Excel pivot tables = OG data reshaping. Resist more complicated solutions : do you really need more than Excel?

    QlikSense doesn't get enough love. It's actually better than Tableau at some scenarios. Or PowerBI if you're a Microsoft shop. Last time I checked, Power BI's Q&A [2] was a KILLER FEATURE. "Show me Sales per Region, Quarterly" and then you get to fine tune it. R and Shiny dashboards = last resort; too much bespoke work. 2 months using R + Shiny can be 1 week in Tableau / QlikSense / PowerBI.

    1. "Efficient reshaping using data.tables" https://cran.r-project.org/web/packages/data.table/vignettes...

    2. https://docs.microsoft.com/en-us/power-bi/natural-language/q...

  • by emilsedgh on 2/13/22, 8:13 PM

    Metabase is what you're looking for.

    https://metabase.com/

  • by justindeguzman on 2/13/22, 11:31 PM

    You should check out Arctype! (https://arctype.com)

    We're trying to re-imagine popular SQL clients (phpMyAdmin, MySQL Workbench) to have the design and software quality of modern tools like Superhuman, Linear, etc. We make it very easy to query and create charts from your database then share it with your team.

    We currently support MySQL, Postgres, PlanetScale, Yugabyte, and in the next couple of weeks SQLite and ClickHouse.

  • by sfifs on 2/13/22, 10:10 PM

    Nowadays this is pretty much the space of either PowerBI or Tableau.

    There used to be a lot of good candidates in this space even just a few years ago but Power BI has improved it's product and integrations very rapidly and with its affordability has displaced them at many big companies. Power BI also recently added some NLP capabilities from one of Microsoft's acquisitions which makes usage by non technical users easier.

    If you're willing to put your data on BigQuery, then Google Data Studio / Looker is an even better solution for larger datasets due to the seamless integration and intelligent caching which (purely in my perception) seems to work better than Azure Analytics Services in the Microsoft side. Also BigQueryML works within SQL.

    Source: i lead an Analytics and Data Science team at a Fortune 50

  • by chrisjc on 2/14/22, 1:25 PM

    One thing that has become apparent to me over the years is that most of these exploration and visualization tools will be pretty ineffective unless the data is modeled correctly. Some of the tools mentioned here will actually do a lot of the modeling before the exploration and visualization kick in, but the tool is probably only doing the best it can but will most likely not understand a lot of the structure and business nuance that have been accumulated throughout its existence.

    Moreover, if you plan to adopt and build upon one of these tools that infers and generates the models as well as provide the explore and visualization functionality, you might be painting yourself into a corner and forcing all current and future workloads to use this layer. Otherwise you'll be having to reinterpret and reimplement your models all over the place; one off SQL scripts/reports, web analytics, dashboards/visualizations/reports on other analytics tools. Then you'll also end up having to scale this tool up in both compute and storage to handle the load that grows over time. This can end up being quite costly in time, money and responsibility.

    While these tools will offer a lot of value providing visibility and insight into your data, it'll probably be worth circling back and seeing if the data can and should be modeled correctly (semantic layer) before hitching your wagon to your first choice.

    Once your data is all modeled, it might be worth re-evaluating all the tools that you started with and see how they manage now that your house is a little more in order.

    Remember, your modeling doesn't have to be done by the same tool that does your exploration and visualization.

    This is a great article related to these ideas: https://benn.substack.com/p/is-bi-dead

  • by superdupershant on 2/13/22, 7:23 PM

    Redash(https://redash.io/) is pretty easy to use and get up and started with, especially if you know SQL. It's free and open source.

  • by samwillis on 2/13/22, 7:20 PM

    Maybe not exactly what you are looking for but Datasette is brilliant for SQLite (and csv)

    https://datasette.io/

  • by datacoffee on 2/13/22, 8:59 PM

  • by js4ever on 2/13/22, 9:39 PM

    Metabase is amazing, it's very intuitive and quick to create questions (widgets) and assemble them in dashboards. I really liked the dashboard subscription to mail, and configurable alerts based on any questions built with the UI or SQL I've literally discovered it last Friday morning and now it's up and running at my company and also for 2 customers.

  • by jmnicolas on 2/13/22, 10:56 PM

    I tried most of the free tools but I don't like any of them. In my experience they're all clunky and don't have a beautiful design (I'm on Windows at work and Linux at home).

    I just settled on DBeaver, but don't consider that an endorsement from my part.

    I found DB Browser for SQLite to be the least bad, but it's obviously limited to SQLite.

    My problem may come from the fact I have simple needs and they're all very complex apps. My SQL queries are rarely longer than 50 lines and I do DB admin tasks from the command line.

    Among the unending list of apps I should code for myself there's a SQLPad project. Maybe one day.

  • by otoburb on 2/13/22, 8:50 PM

    Visidata[1] supports sqlite, mysql and postgres.

    [1] https://www.visidata.org/docs/formats/

  • by Simon_O_Rourke on 2/14/22, 7:01 AM

    I use Gephi a good deal to visualize connections between tables and schemas in a messy production MySQL database, mostly with the aim to stop people (read: consultants, junior developers, managers trying their hand writing SQL) from developing ad hoc duplicate tables for their own niche purposes.

    Gephi lets me show how this kind of table bloat happens over time and helps explain performance degradation.

  • by dataspun on 2/14/22, 5:12 AM

    Couple of tools not yet mentioned:

    PopSql - https://popsql.com

    Trevor - https://trevor.io

  • by jraph on 2/13/22, 7:46 PM

    No plots and statistics (well, maybe, but I've not used them if there are there). But DBeaver is nice to browse a database.

    For SQLite databases, I use sqlitebrowser.

    Both tools are open source.

  • by chasil on 2/13/22, 8:33 PM

    Oracle SQL Developer has a data model tab.

    This is very much a Java application, and appears to allow several JDBC drivers for 3rd party databases.

    It's free, and is designed to compete with (or drag underwater) Quest Software's Toad.

    https://www.oracle.com/database/technologies/appdev/sqldevel...

    3rd party drivers:

    https://www.oracle.com/database/technologies/appdev/sqldev/t...

  • by mongrelion on 2/13/22, 6:36 PM

    Off the bat I would say Metabase but it'd be good to know what kind of data you have because you can connect Grafana to Marinade and it'll give you really nice graphics but again, it depends on the kind of data you have

  • by ttz on 2/15/22, 1:50 AM

    Google Datastudio, if you're fine with something in the cloud hooking up to your DB (and whitelisting the IP).

    Metabase or Apache Superset, as others have mentioned, can be deployed on-prem so it's a bit more isolated/secure.

  • by d--b on 2/13/22, 8:08 PM

    I am building Jig (https://www.jigdev.com), which you could use for that.

    It's based on Observable (https://www.observablehq.com), which has a nice Summary table feature, sounds like what you need (https://observablehq.com/@observablehq/summary-table)

  • by fredrik_skne_se on 2/13/22, 7:20 PM

    Power BI is popular in many organisations https://powerbi.microsoft.com/en-us/

  • by go_prodev on 2/13/22, 7:55 PM

    Free: Power BI is probably going to give you everything you need. It's free, easy to use and provides a lot of features to grow into.

    Paid: If you have the budget, Aqua Data Studio gives you the database management functionality AND all of the visualizations you'll find in Tableau in the 1 product.

    (My company shifted from Tableau to Power BI. At first it seemed like a beta product with lower fidelity. But Microsoft has made the whole power suite into a force to be reckoned with... highly recommended)

  • by bessarabov on 2/13/22, 6:55 PM

  • by sdesol on 2/13/22, 11:16 PM

    Since superset, metabase and redash was mentioned, I'll share some development insights regarding these OSS projects. Note, I've never used these solutions before, so I can't speak to their quality, but I can speak to how active and invested they appear to be.

    Looking at recently merged pull requests that were less than 120 days old, apache/superset had 89 unique authors, which is very high, as the following shows:

    https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...

    Metabase had 37 authors, which is also quite high for an opensource project, which the following shows:

    https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...

    And redash has 11, but most of the contributions were more than 28 days ago, and is significantly less active than metabase and superset as the following shows:

    https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...

    I was actually quite surprised by superset, as I never heard of them before, but they are backed by serious investment (https://preset.io/about/), which clearly shows in how active their repository is.

  • by chris_hoyle on 2/13/22, 7:00 PM

    I recently started using Arctype (https://arctype.com/) and have really enjoyed the experience so far. Relatively new tool, but the team is awesome. It's a SQL client that has some basic data visualization features seemingly geared towards engineers

  • by eatonphil on 2/13/22, 7:42 PM

    It's not what you mean, but I noticed yesterday that Kaggle does what you're asking on datasets.

    See https://www.kaggle.com/rhuebner/human-resources-data-set. I think it's a great view on top of a datatable.

  • by telchar on 2/13/22, 10:59 PM

    Try the Pandas Profiling library [0]. Why do all the clicking and plotting and specifying variables when you can have a couple lines of code do it all for you?

    [0] https://pypi.org/project/pandas-profiling/

  • by text2db on 2/14/22, 11:38 AM

    Maybe a bit off topic, but I created a simple tool which converts text to database scripts, it reduces time to create database tables and foreign key relationships, currently, MySQL, PostgreSql and MSSQL are supported

    https://text2db.com/

  • by abotsis on 2/13/22, 7:55 PM

    I’ve used tableau and such, but lately Apache superset is filling my needs. Check it out!

  • by _wei_ on 2/13/22, 10:18 PM

    I recently made a tool called Daigo. It's not as powerful as Power BI, Tableau or any OSS alternatives. But if you're looking to create a line chart or a dashboard from a SQL table or a CSV file, you may find Daigo a quick and easy solution.

    It works with SQL databases and CSV file. Since it's an offline desktop app, it's free to use and you don't need to set up a server or upload data.

    https://daigoapp.com/

  • by petilon on 2/13/22, 8:27 PM

    Reverse question: What tool do you use to get data into the database? Google Forms is great, but if you want the data to go into your own database what tools are available?

  • by kinj28 on 2/16/22, 9:49 AM

    You can use DronaHQ ( a low code tool builder with drag and drop UI components to quickly build dashboards, frontends, etc) to visualize your data from sql table or other databases via table grid, plotly charts. https://www.youtube.com/watch?v=vfaqaC2rdzs

    Give it a go at https://www.dronahq.com

  • by MaknMoreGtnLess on 2/13/22, 6:16 PM

    apache superset

    or its commercialized offer: ‎Preset (Cloud)

  • by johnhess on 2/13/22, 7:36 PM

    wizardmac.com

    The most effective, efficient data exploration tool I've ever used. I'm a data scientist, but I use this before I write so much as a line of code.

  • by ioseph on 2/14/22, 3:59 AM

    Piggy backing onto this, which of these options has the best out of the box offering for geospatial data (coming from a postgres backend or REST api)?

    I've tried google data studio, superset, but what I need is a integrated control where user's can filter the report based on their location. Or alternatively which would make it easiest for me to develop this control myself.

  • by adrianthedev on 2/13/22, 8:33 PM

    https://basetool.io

    Credentials to admin panel in one click.

  • by nicodjimenez on 2/13/22, 8:12 PM

    ReTool. The fact it’s interactive and scriptable with JS makes it better IMO than all other BI tools I’ve seen.

  • by FujiApple on 2/13/22, 11:24 PM

    Someone recently posted this list to a similar question:

    https://github.com/thenaturalist/awesome-business-intelligen...

    From this I picked Metabase and found it to be pretty good.

  • by RyanHamilton on 2/13/22, 8:57 PM

    If you want live updating charts with under < 1s refresh, I make http://www.sqldashboards.com/ . It allows interactive forms as demoed in the video. Disclaimer: It costs $47.

  • by feupan on 2/14/22, 3:43 AM

    How has no one mentioned Sequel Pro and its successor Sequel Ace? It’s a classic, free native Mac app.

    https://github.com/Sequel-Ace/Sequel-Ace

  • by tehlike on 2/13/22, 6:26 PM

    I briefly used metabase - it looks a little too high level but seem to work.

  • by arey_abhishek on 2/16/22, 8:35 AM

    Check out Appsmith. It can be used to visualise data using charts or tables. There's an integration for MySQL/Mariadb that you can use. You can also write SQL in Appsmith.

  • by a1445c8b on 2/14/22, 1:58 AM

  • by interlocutor on 2/13/22, 8:16 PM

    This is a Windows app but can do the job: http://pebblereports.com/

  • by caoxhua on 2/15/22, 8:07 PM

    You already said "on the columns", so it will be https://columns.ai, :)

  • by sawaali on 2/13/22, 8:36 PM

    I wrote an app recently for macOS called Metaset: https://metaset.io/

  • by zobione on 2/13/22, 7:43 PM

    Maybe not exactly what you are looking for but https://whaly.io can do the trick !

  • by muhehe on 2/13/22, 7:37 PM

    Grafana

  • by rekoros on 2/14/22, 2:36 AM

    I’ve enjoyed using Cluvio (cluvio.com)

  • by tbrock on 2/13/22, 11:00 PM

    Metabase, looker, redash, periscope

  • by sc4les on 2/14/22, 12:11 AM

    Metabase

  • by pnathan on 2/14/22, 5:48 AM

    grafana is what I use.

  • by hoofhearted on 2/14/22, 12:35 AM

    HeidiSQL possibly?

  • by exotree on 2/13/22, 10:24 PM

    Infogr.am

  • by gavinray on 2/13/22, 8:33 PM

    Metabase