
ChartSQL: Create Charts and Dashboards from SQL - paulasmuth
http://fnordmetric.io/chartsql/
======
sida
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)

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

~~~
ChikkaChiChi
That doesn't make it right.

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

~~~
cerberusss
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?

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

~~~
paulasmuth
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.

~~~
sida
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?

~~~
zorbo
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...](http://fnordmetric.io/documentation/chartsql/external_data_sources/)

~~~
paulasmuth
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.

------
sinatra
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?

~~~
rahilsondhi
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](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 :)

~~~
sinatra
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

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

------
dvirsky
Another project that tries to achieve something similar is Redash
[https://github.com/EverythingMe/redash](https://github.com/EverythingMe/redash)

~~~
sida
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

~~~
arikfr
(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](https://github.com/EverythingMe/redash/issues/320)
[2]
[https://vagrantcloud.com/redash/boxes/demo](https://vagrantcloud.com/redash/boxes/demo)
[3] [https://github.com/EverythingMe/redash/wiki/Setting-up-
re:da...](https://github.com/EverythingMe/redash/wiki/Setting-up-re:dash-
instance)

------
mcx
This is pretty cool! For anyone interested in a paid solution, I saw a demo by
[http://www.looker.com/](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.

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

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

~~~
paulasmuth
It's actually fairly straightforward, you can check out the code here:
[https://github.com/paulasmuth/fnordmetric/tree/master/fnordm...](https://github.com/paulasmuth/fnordmetric/tree/master/fnordmetric-
core/src/sql_extensions)

------
davis
Any reason for the selection of GPL3 for the license?

~~~
paulasmuth
Yes, actually: [https://www.gnu.org/philosophy/why-not-
lgpl.html](https://www.gnu.org/philosophy/why-not-lgpl.html)

------
mmsimanga
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](https://www.drupal.org/project/forena)

------
gingerlime
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).

------
RA_Fisher
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]'

~~~
paulasmuth
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.

------
vram22
Interesting idea.

Sort of related:

plot.ly:

[http://jugad2.blogspot.in/2013/07/plotly-now-has-
api.html](http://jugad2.blogspot.in/2013/07/plotly-now-has-api.html)

and Domino:

[http://jugad2.blogspot.in/2014/02/domino-python-paas-now-
has...](http://jugad2.blogspot.in/2014/02/domino-python-paas-now-has-free-
plan.html)

------
Synergyse
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](https://developers.google.com/apps-script/guides/jdbc)

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

~~~
paulasmuth
Here is a working example:
[https://github.com/paulasmuth/fnordmetric/blob/master/fnordm...](https://github.com/paulasmuth/fnordmetric/blob/master/fnordmetric-
doc/examples/queries/mysql_backend.sql)

And a few bits of documentation here
[http://fnordmetric.io/documentation/chartsql/external_data_s...](http://fnordmetric.io/documentation/chartsql/external_data_sources/)

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

------
acbart
Does this allow Joins and Subqueries?

~~~
thomstabk
That's what I was wondering. Hard to tell from this:
[http://fnordmetric.io/documentation/chartsql/syntax_referenc...](http://fnordmetric.io/documentation/chartsql/syntax_reference/)

------
nodesocket
We use and love [https://www.cloud9charts.com](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).

~~~
gopalaj
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](https://www.cloud9charts.com/mysql)

fnordmetric.io looks cool. Nice work!

------
clubhi
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?

------
aluhut
Eris is strong in this one.

------
tutuca
Amazing work.

