HN Explorer

Ask HN: Tools to visualize data in SQL databases?

244 points by dyml ↗ HN
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.

136 comments

[ 3.3 ms ] story [ 214 ms ] thread
MaknMoreGtnLess
apache superset

or its commercialized offer: ‎Preset (Cloud)

glogla
> or its commercialized offer: ‎Preset (Cloud)

Which includes free version for five users. But it is SaaS so your database has to be visible from the internet.

I really like it though.

sa46
Not sure about Preset specifically, but most SaaS vendors advertise their IP address so you can limit incoming database connections to the SaaS vendor’s IP address.
tehlike
I briefly used metabase - it looks a little too high level but seem to work.
mongrelion
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
chris_hoyle
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
utmostGP
agreed it's legit and a great native SQL client and replacement now that pgAdmin has gone all browser-based
samwillis
Maybe not exactly what you are looking for but Datasette is brilliant for SQLite (and csv)

https://datasette.io/

simonw
It doesn't speak MariaDB (yet - I have a long-term goal to investigate adding alternative database backends as plugins) but you can instead use the https://datasette.io/tools/db-to-sqlite CLI tool to convert a PostgreSQL or MySQL (or other SQL Alchemy supported) database to SQLite, then use Datasette against the resulting file.

This actually works pretty well for small (<1GB) databases, where you can run a cron periodically to build the SQLite version.

Then you can visualize with plugins such as https://datasette.io/plugins/datasette-cluster-map or https://datasette.io/plugins/datasette-vega

I also often load data into Datasette and then do custom visualizations in Observable Notebooks by fetching data back out through the Datasette JSON API - here's an example notebook that does that, using the Observable Plot charting library: https://observablehq.com/@simonw/datasette-downloads-per-day...

btown
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...

geoduck14
>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.

This is a popular opinion, in my book.

jslove
As you get deeper into it they hook you into the server and other stuff and it ends up costing 000s.

Pandas is better but requires programming.

reureu
I second pandas, and would also highlight seaborn, plotly, and dash as complimentary data visualization libraries.

Tableau is fine for what it is, but I've found that the requests from stakeholders often grow to a point where you either can't do it in Tableau or have to move mountains to get it to work... so, in essence, sunk cost fallacy makes tableau millions.

disgruntledphd2
PSA: If you haven't already learned pandas, learn dplyr/ggplot2 instead. Yes, R is a pretty clunky language (but it's closer to lisp than pretty much anything else as popular) but ggplot2 and dplyr are 100% the best currently available way to visualise tables in SQL.
r_power_user
I would second the decision to use ggplot2 / dplyr, and would also add data.table to the mix. That combination has been invaluable for me, allowing me to visualize all of my structured data.
optimalonpaper
I started with R, but then switched to Python because all pipelines were already written in Python (web-scrapers, some data-processing scripts, REST APIs), so I just learned pandas and it's been fine, although I do think dplyr's syntax is great and I prefer it to pandas'.
disgruntledphd2
Yeah, totally. I spend a lot more of my time writing Python for anything that isn't data exploration/analysis, for exactly that reason.

I still refuse to learn pandas enough to replace dplyr though, as it's just so painful to use the API compared to how easy this stuff is in R.

rr808
Do you load the whole table into memory though?
adeelk93
$70/mo includes a server (Tableau online)
Psyladine
Anything solved by tableau is as easily solved by Excel, which likewise supports direct SQL connections. The template graphs & incorporation of function logic, for versatility & business availability make it no contest. Though Tableau has some gorgeous templates, it doesn't also have DAX & PowerBI.
mgradowski
I'm curious why do you think of DAX as a virtue. My poor SQL-shaped peg brain has never really fit the DAX hole of MS software.

Also, it always struck me as something too complex for the non-technical folks, and not expressive enough for tech-literate analysts/data engineers &c.

Psyladine
Just options. VBA is there as well. Excel's virtue is not specializing in any specific task, but being versatile enough to express a multitude of business solutions. 'Excel is my database' wasn't always a punchline.

That's more than an equally domain specific process like Qlik, and more than a specific vendor tool like tableau. And anyway if PowerBI didn't have a pain point it wouldn't be a MS product.

wenc
Tableau is my go-to too. People think of Tableau as a dashboarding tool (and it is), but it's actually a multidimensional exploratory data analysis tool. You can visualize more than 3 dimensions by way of colors, labels, sizes, etc. You can slice and dice your data and visualize it in so many different ways, and also do drill-downs and filters and aggregations in different ways. The downside is that you have to really understand SQL-like operations (GROUP BY, PARTITION BY, PIVOT) to truly take advantage of its power. Many Tableau users only scratch the surface of what Tableau can do, but there's a lot more underneath.

PowerBI on the other hand is a straight up dashboarding tool. I've used it and it isn't quite as powerful as Tableau, but it's an easier step up from Excel. It doesn't require you reorient your mindset as much as Tableau does. But it also doesn't let you probe your data as easily as Tableau does. It's essentially a supercharged Excel dashboarding tool

Excel viz is rudimentary. It gets the job done for simple plots, but it's a hassle to join data (you have to do cell VLOOKUPs or INDEX(MATCH())) and pivot tables are a poor-man's approximation of the true power of SQL operations. It doesn't scale to large datasets but the cell-based spreadsheet paradigm (vs. a relational database paradigm) is easy to understand which has an appeal of its own. But you're ultimately limited to what fits on a spreadsheet.

stadium
I'd throw in excel power pivot over SSAS cubes. It fills In some of the gaps you described.
systemvoltage
Tableau is a webapp version of basically SAS JMP, especially the plotting engine. So easy to create plots - literally 100x faster than matplotlib. I can drag things faster than I can type.

Tableau is really amazing actually.

MaknMoreGtnLess
What's the closest Tableau alternative that's OSS?

PowerBI?

Apache Superset? https://superset.apache.org

kfk
In spirit, but with no UI, vega js, they follow the same “grammar of graphics” idea from the same research. Altair does vega charting but in Python. Kibana is using vega in more UI fashion but I haven’t tested it. I think someone should put a proper web ui on top of vega…
jaxn
I really like the visualization language idea.

I have used Vega + Jupyter a few times. As well as the Vega Viewer extension for VS Code. I haven't used it enough to be proficient and fast, but I have used it enough to know that I want to be.

eatonphil
I'm working on something in between a BI tool and a SQL IDE. It's definitely code-heavy on the user at the moment compared to a BI tool but an improvement on switching between Python scripts, Postman, a SQL GUI and Excel.

https://github.com/multiprocessio/datastation

bernardom
I've had bad experiences with superset (though it may have been misconfigured).

On the other hand, Redash has served my startup really well for the first 3 years (and now we're finally moving to Tableau)

csdvrx
It's good, but wait until you discover Mathematica :)
nalzok
What functions of Mathematica can do the job? I'm interested in checking it out.
csdvrx
The various visualizations like MatrixPlot, Manipulate ... just google around and you'll see many interesting examples!
zedfoxus
My go-to has been Qlik Sense. I have enjoyed using Qlik Sense. Tableau is good also. Compared to Tableau, I’ve found Qlik to be faster and more responsive to slicing and dicing data.
Simon_O_Rourke
Tableau is great, but that initial fee, while being peanuts in terms of absolute IT spend, has been a blocker getting it rolled out in one large BI team I worked with.

The backstory was a large BI team having no proper tools for reporting, except maybe Looker for tabular KPI reports. So any addition of Tableau was an all our nothing buy, every single analyst or whoever on the BI team needed a seat, even though most of them wouldn't ever use it. For that matter 95 percent of the BI team would never move away from Excel. Anyway, the costs were huge for something that should have been a leap in capabilities.

[deleted]
(comment deleted)
johnhess
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.

jraph
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.

NoNotTheDuo
DBeaver is my choice. Saw it being used at Re:Invent by a presenter a few years back and switched immediately. I had been using SQLDeveloper and don't miss it one bit.
abotsis
I’ve used tableau and such, but lately Apache superset is filling my needs. Check it out!
go_prodev
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)

chasil
Power Bi has been very abusive of my systems, although this has lessened of late.

I can see hundreds of logins to my database per user, and when I cut the logins per userid to 5, their applications collapse. Their queries cannot be tuned to available indexes according to my users.

They remain the very first ones that I throw off a database if there is a performance problem, with some degree of prejudice.

mst
I've always preferred to shunt such analytics work over to a query replica dedicated to the purpose so the people doing analytics can generally only interfere with each other.

Whether that's a viable answer in any given situation is of course highly variable, but even if the analytics queries are my own hand-rolled SQL it's still my preference any time it -is- viable if nothing else so I don't have to worry as much about screwing up and taking too many locks / using too few indices while I'm iterating on the query in question.

go_prodev
Your users may benefit from using import mode rather than direct query mode.

Under the covers Power BI is running a tabular Analysis Services Cube, so import mode will be optimized for reporting regardless of the source database indexing.

The PBI dataset can be shared across users, so only 1 connection is required to update it, instead of dozens of users hitting the db directly.

Also as another person mentioned, reporting is usually done on an separate database to production applications like an operational data store, data lake, or warehouse.

robertlagrant
We use PowerBI. Wouldn't recommend. I also don't believe it's free, but perhaps that's just the version we use.
Fnoord
PowerBI is not free to use, its part of Office 365 (Microsoft 365 or whatever it is called today). I remember 2 companies ago (in 2019) some data scientists had to get a license for it approved.
go_prodev
For personal analytics the desktop version is free. The web service has different pricing tiers (which can be very expensive)

Edit...just to add to this. I personally pay for it and it's $5/mth. Premium capacity can be a lot more...but if the Data Scientist needed a license, it may have only been $5/mth, paltry compared to a data scientist salary.

Fnoord
It was a non-profit, every single EUR had to be accounted for. On the good side, they did get heavy discounts on some licenses, including from Microsoft Windows/Office. Except IT manager didn't know (even though he was a Microsoft fanboy), he only figured that out after paying full price for years...
mindfulmark
I'm curious why you don't recommend PowerBI. What are the pain points?
robertlagrant
Thankfully my memory is cloudy :-) maybe two points:

1. In general the idea of a separate reporting DB maintained by separate people who might not understand the semantics of schema changes seems inefficient in a world of cheap compute, compared to an application exposing key metrics directly. Upgrading every two weeks with an ETL pipeline off an app can result in RTL breaking every two weeks. Better just to release metrics from the same team that makes any other changes.

2. I think connecting hosted PowerBI to a IP-whitelisted Azure blob store is not possible, and that just seemed silly.

go_prodev
I guess it comes down to what you are trying to accomplish.

The desktop version is free, no strings attached. However the value comes from publishing to the web service for sharing etc. That's not free. It can be cheap, but when you have a lot of users the "premium capacity" can be quite expensive.

eyeball
I used to like qlik more than pbi/tableau but they stopped making the personal desktop version free. Qlik’s scripting and “associative” engine is great.
nicodjimenez
ReTool. The fact it’s interactive and scriptable with JS makes it better IMO than all other BI tools I’ve seen.
mst
I'm deeply confused as to why people are downvoting this - maybe it's not a suitable answer for many use cases (it doesn't look like it'd be my thing most of the time, certainly), but I'm aware of enough very happy ReTool users to assume it must be useful for at least a decent subset.

Can somebody please explain why they consider this to've been a terrible answer?

1123581321
Could be reflexive downvoting due to a perception that "low code" is not a data tool (I would disagree with this.) It could also be because Retool's reputation took a bit of a bit on HN several months ago (though marak has since taken an even bigger reputational hit...): https://news.ycombinator.com/item?id=27252066

Just guesses.

emilsedgh
Metabase is what you're looking for.

https://metabase.com/

allan_s
metabase is pretty nice, and their "x-ray" features that generates a lot of different visualisation is a clever way to get started and to have example to iterate on.
robertlagrant
That looks great, but AGPL makes everyone nervous.
FridgeSeal
You’re not building against their code, you’re just using the end product, why would AGPL matter here at all?
indymike
Curious more than anything. If your intent is to use the tool as opposed to importing the tool as a feature or product, is the AGPL a problem?

Also, "everyone nervous" is an interesting choice of words - I'm pretty sure most people wouldn't really care one way or the other. What is it about AGPL that causes stress among the masses?

heavyset_go
> Also, "everyone nervous" is an interesting choice of words - I'm pretty sure most people wouldn't really care one way or the other. What is it about AGPL that causes stress among the masses?

Things like Google's AGPL internal policy[1] existing as a public piece of corporate-friendly IP propaganda have snowballed into a lot of FUD about the license.

People like to parrot perceived and secondhand opinions about the AGPL, and "some companies don't like the AGPL" transformed into "the AGPL makes everyone nervous."

[1] https://opensource.google/documentation/reference/using/agpl...

robertlagrant
Fair point.

Clarifications: no, using the tool directly is not a problem for us.

"Everyone" - appreciate your question and not guessing :-) I meant everyone at my company. That phrasing maybe speaks to my mindset :-D Basically I really mean legal and those in charge who might listen to legal over tech.

heavyset_go
They have a commercial license, but just using an AGPL product doesn't mean you must AGPL your project.
dillondoyle
We use it and or staff are able to get at least a bit of value without knowing sql. but i still have to write most things or create a template they can copy and paste.

the visual builder still relies on knowing fundamentally what a db is, what a join is. like mega obvious stuff that actually isn't obvious to non technical people.

we area also multi tenent and it's not the best for that. like the default permissions when you add a db are broad, the organization of dashboards, questions, etc is not great. through different versions they have re organized and stuffed things oddly.

i've also messed up the elastic beanstalk a few times. i am not even close to an expert on aws so might just be me being stupid.

probably worth just paying for their service in the end..

dgb23
My experience is that these kind of tools have an interesting positive effect, if they are well made (and metabase seems to be):

There are maybe 1/10-1/5 or maybe more who will be enabled by them on a technical level. The same kind of people who will build a site out of WP themes and plugins or build quite involved spreadsheets and so on.

I think it’s generally important and useful to enable and respect power users like that. They occupy a unique niche.

petilon
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?
wantoncl
I don't use it, but LibreOffice has the Base component that can be a data entry front-end for many ODBC and JDBC supported databases. I think OpenOffice has/had the same.

If you're using Microsoft, you can use MS Access for data entry to similar ODBC and SQL Server backends. If you want to do some VBA programming you can set up a UI in an Excel workbook too.

emmelaich
Depending on the source, debezium might be useful.

Otherwise, there are many ETL solutions.