[HN Gopher] Ask HN: Tools to visualize data in SQL databases?
___________________________________________________________________
Ask HN: Tools to visualize data in SQL databases?
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.
Author : dyml
Score : 78 points
Date : 2022-02-13 16:52 UTC (6 hours ago)
| abotsis wrote:
| I've used tableau and such, but lately Apache superset is filling
| my needs. Check it out!
| go_prodev wrote:
| 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)
| eyeball wrote:
| 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.
| chasil wrote:
| 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 wrote:
| 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.
| superninjy wrote:
| *Free - just as in "beer".
| robertlagrant wrote:
| We use PowerBI. Wouldn't recommend. I also don't believe it's
| free, but perhaps that's just the version we use.
| Fnoord wrote:
| 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.
| mongrelion wrote:
| 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 wrote:
| 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
| otoburb wrote:
| Visidata[1] supports sqlite, mysql and postgres.
|
| [1] https://www.visidata.org/docs/formats/
| petilon wrote:
| 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 wrote:
| 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.
| [deleted]
| MaknMoreGtnLess wrote:
| apache superset
|
| or its commercialized offer: Preset (Cloud)
| glogla wrote:
| > 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 wrote:
| 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.
| vtuyllugyly wrote:
| https://github.com/hvf/franchise
| johnhess wrote:
| 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.
| muhehe wrote:
| Grafana
| gavinray wrote:
| Metabase
| RyanHamilton wrote:
| 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.
| d--b wrote:
| 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)
| adrianthedev wrote:
| https://basetool.io
|
| Credentials to admin panel in one click.
| chasil wrote:
| 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...
| datacoffee wrote:
| Apache Superset https://superset.apache.org
| sfifs wrote:
| 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
| sawaali wrote:
| I wrote an app recently for macOS called Metaset:
| https://metaset.io/
| eatonphil wrote:
| 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.
| nicodjimenez wrote:
| ReTool. The fact it's interactive and scriptable with JS makes it
| better IMO than all other BI tools I've seen.
| mst wrote:
| 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 wrote:
| 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.
| zobione wrote:
| Maybe not exactly what you are looking for but https://whaly.io
| can do the trick !
| jmnicolas wrote:
| 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.
| emilsedgh wrote:
| Metabase is what you're looking for.
|
| https://metabase.com/
| allan_s wrote:
| 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.
| dillondoyle wrote:
| 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..
| robertlagrant wrote:
| That looks great, but AGPL makes everyone nervous.
| tehlike wrote:
| I briefly used metabase - it looks a little too high level but
| seem to work.
| _wei_ wrote:
| 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/
| telchar wrote:
| 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/
| js4ever wrote:
| 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.
| tbrock wrote:
| Metabase, looker, redash, periscope
| interlocutor wrote:
| This is a Windows app but can do the job:
| http://pebblereports.com/
| jraph wrote:
| 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 wrote:
| 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.
| fredrik_skne_se wrote:
| Power BI is popular in many organisations
| https://powerbi.microsoft.com/en-us/
| samwillis wrote:
| Maybe not exactly what you are looking for but Datasette is
| brilliant for SQLite (and csv)
|
| https://datasette.io/
| simonw wrote:
| 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...
| bessarabov wrote:
| https://observablehq.com/@observablehq/databases
| exotree wrote:
| Infogr.am
| btown wrote:
| 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 wrote:
| >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 wrote:
| 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.
| rr808 wrote:
| Do you load the whole table into memory though?
| adeelk93 wrote:
| $70/mo includes a server (Tableau online)
| reureu wrote:
| 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.
| MaknMoreGtnLess wrote:
| What's the closest Tableau alternative that's OSS?
|
| PowerBI?
|
| Apache Superset? https://superset.apache.org
| kfk wrote:
| 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...
| eatonphil wrote:
| 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
| MaknMoreGtnLess wrote:
| Hey! Thank You for creating datastation.
|
| Are there some videos that walk a user through setting it
| up to using it?
| eatonphil wrote:
| There are primarily a bunch of tutorials for getting
| started [0] (these are up to date) and some old videos
| (not up to date) [1].
|
| [0] https://datastation.multiprocess.io/docs/
|
| [1] https://www.youtube.com/channel/UCGOQFKonPUVo5LgxQDW2
| 6yg/vid...
| wenc wrote:
| 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.
| csdvrx wrote:
| It's good, but wait until you discover Mathematica :)
| nalzok wrote:
| What functions of Mathematica can do the job? I'm interested
| in checking it out.
| Psyladine wrote:
| 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.
| superdupershant wrote:
| 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.
| stephane-klein wrote:
| https://www.metabase.com/ free and open source.
___________________________________________________________________
(page generated 2022-02-13 23:00 UTC)