ChartSQL: Create Charts and Dashboards from SQL | Hacker News Reader

ChartSQL: Create Charts and Dashboards from SQL

fnordmetric.io

271 pointspaulasmuth72 comments

Chart.io et al are awesome. But all of them are extremely expensive. Chart.io for example starts at $1000 per month and periscope.io starts at $300 per month.

It is frankly not very affordable for a bootstrapped startup.

My questions are how easy is it to host this thing. (It would be perfect if someone makes a deploy to heroku button so that we can just do this in one click)

A "deploy to GCE" button is something I am planning to add in the next weeks.

Chart.io was too expensive and we bailed on it, but not before we tried to negotiate a discount to no avail. Chart.io pricing model would benefit from more flexibility and options.

As someone who sells a product in a similar price range, your point is understandable but quite difficult to cater to from a vendor's perspective. Both Chartio and our product involve spending some time with the customer up front in order to really make it work for them. A lower price point plus monthly contracts plus churn as a result of finicky customers is the mark of death for SaaS startups.

The problem is one size company fits all pricing. Small business who can benefit the most from SAAS (and have the least regulatory/bureaucratic resistance) can't justify enterprise or middle-market pricing. And the valuation/exit strategy for most SAAS offerings is quantity of users, not profitability - like it or not. I predict either Chart.io changes pricing or its market share is eroded by FOSS and cheaper alternatives like: http://idl.cs.washington.edu/projects/lyra/

> And the valuation/exit strategy for most SAAS offerings is quantity of users, not profitability - like it or not.

That's not true at all.

SaaS companies are valued on recurring revenue, lifetime value, retention and future cash flow. Number of users is almost irrelevant as long as those metrics are moving up and to the right to a healthy degree.

Chart.io has already changed their pricing once and I share your prediction that they will change it again: to charge even more. And grow further as a result.

SMBs are a tough market for SaaS companies with a product that has any complexity whatsoever, both in terms of usage and distribution.

Lets meet again in a year. SAAS is a falling knife. I bet 20 Satoshi on it.

Been at it for 4 years already and SaaS wasn't always this hot.

Edit: what exactly are you wagering on?

Upon second look I believe I misunderstood the purpose of the project.

I initially thought the project was a subtitute for chart.io. However, it seems like the project is about "collect and visualize data".

It is not to visual data by connecting to your own database. Rather it allows you to import data from an mysql database

In case this is helpful: I interned at a company, Polychart, where we built a product similar to chart.io that is now open source and can be self-hosted: https://www.polychart.com/

Looks really interesting. Have you got any idea why they open sourced it?

Thanks. Just what I needed.

Looks neat. However it appears to actually only support MySQL, despite it's website's claims

Maybe we need to help them get wider support then.

You didn't mistunderstand the purpose (However it's not an explicit goal of FnordMetric to substitute chart.io)

Here is an example that connects to a MySQL database on localhost: https://github.com/paulasmuth/fnordmetric/blob/master/fnordm...

Note that the IMPORT statement might be a bit misleading. The IMPORT statement only creates a "virtual table", it doesn't actually copy any data. As much of the query as possible is pushed down into MySQL/the external data source and the charts will be generated from the query result that is returned by the external data source.

I'm currently working on a similar (hopefully more simple and cheaper) product MetricBoard (direct demo: https://metricboard.io/webui/demo)

Although this currently only let's you push metrics via the API or upload a CSV. I personally would not be comfortable having a 3th party connecting directly to my database.

The product is still in development but I would love to get some first feedback / user testing.

Does Chartkick (highcharts) and other open source libraries not support your use cases?!

That's because it's targeted at the Enterprise, where it's not uncommon to pay $100-$200k in yearly licensing for comparable software.

That doesn't make it right.

How does that make it wrong? Obviously people are willing to pay that much for those solutions, otherwise they would lower their prices.

If you're in the GNU/free software camp, it's wrong to charge for closed-source software. Perhaps that's what your OP ment?

The point is, there's obviously demand for this from companies that won't pay thousands/month for it, so it's a question of who will build it for them.

I am working on a similar product Viur http://www.viur.pt with a very flexible pricing targeted to startups, small and mid-size businesses. Our private beta is going live for our subscribers in the next few weeks.

It's generally not very difficult to get SQL query results in JSON rather than tabular format and then feed that to your favorite charting tools.

However that means you have to write a heap of reptitive glue code (or sed incantations if that's your thing) to mangle your SQL Results into the JSON format your charting tool wants. If you run a lot of ad-hoc queries you have to waste a significant amount of time on this boring legwork that could much better be spent on interesting tasks.

FnordMetric aims to fix that by extending standard SQL; it allows you to express the data query and the chart specification in a coherent fashion (SQL).

I guess I am a bit dense here, this imports CSV files as a table and then adds a SQLish 'CHART' command ?

I'm guessing it is for automating Excel or another spreadsheet? I'm not seeing the whole tool chain and I'm wondering why this isn't built into an RRD of some sort rather than the middleware step of CSV files.

FnordMetric allows you to query data from a number of sources. CSV Files are one of them, but there are other backends like MySQL and the built-in statsd server. So ideally there is no "middleware step": you generate charts using only sql from your backend data source in a single step.

There really is no documentation on how to use your own database as a data source.

Am I misunderstanding what FnordMetric is meant to do? Is it meant to allow me to generate charts from my existing postgres database or is it something where I have to feed data into it?

I too agree. It's the holy grail to directly draw using a database source by skipping the middle man.

I too am wondering this. This tool would be great if it could directly use data from my own databases. But it seems data has to be imported first:

http://fnordmetric.io/documentation/chartsql/external_data_s...

I think we need to improve the wording in the documentation. The IMPORT statement only creates a "virtual table", it doesn't actually import any data. As much of the query as possible is pushed down into MySQL/the external data source.

Yes, you can use FnordMetric to create charts from data in your existing database. Have a look at this documentation page: http://fnordmetric.io/documentation/chartsql/external_data_s...

Here is a simple example for a query that uses an external MySQL database: https://github.com/paulasmuth/fnordmetric/blob/master/fnordm...

Currently we only support CSV and MySQL as external data sources, but a postgres plugin is on it's way.

I have some questions:

1. Does IMPORT TABLE mean that it has to copy data from MySQL Database into its own storage or does it merely mean it will connect to the MySQL db and query it directly?

2. If it is a long query (e.g. a 60 second query), does FnordMetric server cache previous results?

> 1. Does IMPORT TABLE mean that it has to copy data from MySQL Database into its own storage or does it merely mean it will connect to the MySQL db and query it directly?

It will only connect to the MySQL database and won't copy any data into it's own storage. The SQL query will be pushed down into the MySQL database and the chart will be rendered from the result set that is returned by MySQL database (or other external storage system).

> 2. If it is a long query (e.g. a 60 second query), does FnordMetric server cache previous results?

Not yet but this is on the roadmap. (Pull Requests always welcome!)

This may be a good place to ask this question -- We are a small startup that has its data in MySQL database and google analytics. We are now focusing a lot more on our marketing/advertising efforts, and would love to have a dashboard on a big screen that can collect, show, and update this data everyday (or better still, real-time). What do people use for something like this?

At InternMatch.com, we use Geckoboard - it pings our Rails app periodically for stats. Our Rails app uses this gem to expose endpoints to Geckoboard: https://github.com/ejdraper/chameleon

We do a bunch of other cool stuff too for stats. I'd be happy to help you if you ping me :)

Thank you! I'll definitely have to ping you in a day or two. BTW, Geckoboard seems to have most integrations that we would need. I'm wondering if you used any of them and if you'll say that the integration was very straight forward or if it needed to be told a lot to be useful

If you can't afford Geckoboard, Dashing is open source https://shopify.github.io/dashing/

There's a tutorial specifically about MySQL https://github.com/Shopify/dashing/wiki/How-to%3A-send-mysql...

And a variety of Google Analytics (and other platform) widgets https://github.com/Shopify/dashing/wiki/Additional-Widgets

Elasticsearch + Logstash + Kibana is a wonderful match for this if you want really deep dive into your data. You can slice and dice and aggregate and display it in a ton of different ways.

For counters, statsd + Librato is dead easy to set up and is really nice for communicating key metrics easily.

Big screens are great for dashboards. I don't use Google Analytics (access and reliability of Google products is unstable where I am) so turned to Piwik, which stores analytical data in a database, so you have immediate access to, well, everything. Querying this can be easily automated.

Check out Shiny, which is a super easy platform I R to make dashboards.

Check out http://www.sqldashboards.com/ live database charting and a command line version is available on the site aswell for generating reports.

Chartkick is amazing:

http://ankane.github.io/chartkick/

This is pretty amazing. My team just started using Chart.io because we couldn't find an easy way to turn data in our relational database into charts. Chart.io is pretty great, but there are several other very expensive BI tools that don't offer much more value than this tool provides. Great job.

Another project that tries to achieve something similar is Redash https://github.com/EverythingMe/redash

This is awesome and does almost everything I need.

The only thing on my wish list is the ability to embed charts. (So that I can generate charts here and embed the chart into admin dashboards)

Thanks for sharing this

In general, the idea is that you build your dashboards in redash, but it would be a nice addition.

Feel free to open a PR with an embed code, or at least opening an issue about it.

BTW This is developed where I work (not by me) and is used internally for BI and some monitoring (though we use graphite for system metrics mainly).

(I'm re:dash project maintainer/creator)

Embedding is something that we want to add, although currently not a priority for us internally. You can follow #320 [1] for updates and details on when it will be implemented.

Also, if you just want to try re:dash out there is a Vagrant box [2] and GCE/AWS images [3].

You can always ping me for help or details, either through the mailing list, GitHub Issues, Gitter or just by email (arik at arikfr dot com).

[1] https://github.com/EverythingMe/redash/issues/320 [2] https://vagrantcloud.com/redash/boxes/demo [3] https://github.com/EverythingMe/redash/wiki/Setting-up-re:da...

This is pretty cool! For anyone interested in a paid solution, I saw a demo by http://www.looker.com/ at AWS re:Invent. It connects to your database (can be run on premises), can generate the required SQL so analysts can easily use it and looks like a very cool service for business intelligence. The person I spoke with said they were planning to open source their customized d3 visualizations.

As someone who built a dashboard app, I tip my hat: this is a very powerful new feature.

This is rather amazing. I applaud the idea of extending SQL for this, but honestly how hard is it to implement the middleware?

It's actually fairly straightforward, you can check out the code here: https://github.com/paulasmuth/fnordmetric/tree/master/fnordm...

Any reason for the selection of GPL3 for the license?

Yes, actually: https://www.gnu.org/philosophy/why-not-lgpl.html

Forena[1] is a Drupal module that has the following features:

1. Connect to any relational database with a PDO supported database (SQLite, MySQL, MS SQL, etc). 2. Write SQL to create tabular reports or charts in SVG

This is not a solution for everyone but works well for me because I use Drupal to store meta data about the reports and graphs I created using Forena

Yes I am aware of the recent security issues with Drupal.

[1]https://www.drupal.org/project/forena

This looks neat. I'm curious to hear about the reasons for "competing" with Graphite / Statsd. What's the main motivation? and what makes fonrdmetric better than those alternatives?

(I personally find the composable graphite functions quite powerful and in some ways more accessible than SQL, but perhaps it's just because I'm used to it by now).

Looks great but I can't get it to pass tests from a fairly vanilla Ubuntu machine. The errors aren't really informative.

'fnordmetric-core/build/cmake/stage/src/fnordmetric/util/datetime.h:85:24: error: specialization of ‘template<class _Tp> struct std::numeric_limits’ in different namespace [-fpermissive]'

That should be fixed if you pull the latest master branch from github. We'll provide binary packages for apt-get and homebrew in the next weeks.

Interesting idea.

Sort of related:

plot.ly:

http://jugad2.blogspot.in/2013/07/plotly-now-has-api.html

and Domino:

http://jugad2.blogspot.in/2014/02/domino-python-paas-now-has...

Alternatively you can use Google Apps Script to pull SQL data into a Google Sheet, where you can manipulate the data and generate charts etc. https://developers.google.com/apps-script/guides/jdbc

Looks like a tool I need. Unfortunately, it looks like the MySQL backend is more a promise than reality.

Here is a working example: https://github.com/paulasmuth/fnordmetric/blob/master/fnordm...

And a few bits of documentation here http://fnordmetric.io/documentation/chartsql/external_data_s...

./build/cmake/target/fnordmetric-server --http_port 8080 --storage_backend mysql [ERROR] unknown backend type: mysql

If it already supports CSV then hacking in a working MySQL backend might not be too difficult. I've gotten a lot of mileage out of `ssh dbserver mysql -b -e \'select * from pants where ...\'` in nagios and munin scripts.

I would love to completely skip integration between a database (not .csv or .sql) and chart for something like this: http://phpdatagrid.com/examples/phpgrid_phpchart_integration.... As you can see, it's the most tedious task combing server and client side scripts. However, it also the most flexible. So the important question is how flexible is to create complex charts that have many different attributes.

Does this allow Joins and Subqueries?

That's what I was wondering. Hard to tell from this: http://fnordmetric.io/documentation/chartsql/syntax_referenc...

We use and love https://www.cloud9charts.com. They support various databases such as MySQL, PostgreSQL, MongoDB, and pricing is very affordable (they even have a free plan to get started).

Thanks for the love!

To plug in queries and visualize data, our database specific pages might be worth a look. Example: https://www.cloud9charts.com/mysql

fnordmetric.io looks cool. Nice work!

I see you are familiar with some dynamic languages such as javascript and ruby. How much more effort do you think it was to create this app in modern C++ instead of something like ruby?

Eris is strong in this one.

Amazing work.

Reply on news.ycombinator.com