ChartSQL: Create Charts and Dashboards from SQL

by paulasmuth • 12 years ago

271 72 12 years ago

ChuckMcM• 12 years ago

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.

paulasmuth11• 12 years ago

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.

sida5• 12 years ago

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?

paulbjensen• 12 years ago

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

haney• 12 years ago

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.

sida5• 12 years ago

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)

paulasmuth11• 12 years ago

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

rrggrr3• 12 years ago

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.

rgrieselhuber3• 12 years ago

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.

sida5• 12 years ago

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

timmclean• 12 years ago

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/

paulasmuth11• 12 years ago

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.

whouweling• 12 years ago

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.

atmosx• 12 years ago

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

radicalbyte• 12 years ago

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• 12 years ago

That doesn't make it right.

cmelbye• 12 years ago

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.

palmeida• 12 years ago

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.

ams6110• 12 years ago

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.

paulasmuth11• 12 years ago

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

acbart• 12 years ago

Does this allow Joins and Subqueries?

thomstabk• 12 years ago

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

davis• 12 years ago

Any reason for the selection of GPL3 for the license?

paulasmuth11• 12 years ago

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

grundprinzip2• 12 years ago

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

paulasmuth11• 12 years ago

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

grundprinzip2• 12 years ago

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

jewel• 12 years ago

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.

chenster2• 12 years ago

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.

zkhalique• 12 years ago

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

paulasmuth11• 12 years ago

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

sinatra2• 12 years ago

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• 12 years ago

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 :)

sinatra2• 12 years ago

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

Maxious• 12 years ago

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

cheald• 12 years ago

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.

zhte415• 12 years ago

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.

IndianAstronaut• 12 years ago

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

alexcash• 12 years ago

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

takemikazuchi• 12 years ago

Chartkick is amazing:

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

dvirsky2• 12 years ago

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

sida5• 12 years ago

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

dvirsky2• 12 years ago

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

arikfr• 12 years ago

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

nodesocket• 12 years ago

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

gopalaj• 12 years ago

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!

mcx• 12 years ago

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.

aluhut• 12 years ago

Eris is strong in this one.

RA_Fisher• 12 years ago

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

paulasmuth11• 12 years ago

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.

Synergyse• 12 years ago

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

mmsimanga• 12 years ago

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

vram22• 12 years ago

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

gingerlime• 12 years ago

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

clubhi• 12 years ago

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?

tutuca• 12 years ago

Amazing work.

ChartSQL: Create Charts and Dashboards from SQL

Related Stories