8 ms·
ChartSQL: Create Charts and Dashboards from SQL
- ChuckMcM 12y agoI 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 12y agoFnordMetric 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 12y agoThere 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?
- chenster 12y agoI too agree. It's the holy grail to directly draw using a database source by skipping the middle man.
- zorbo 12y agoI 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_sources/ http://fnordmetric.io/documentation/chartsql/external_data_s...
- paulasmuth 12y agoI 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.
- paulasmuth 12y agoYes, 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_sources/ 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/fnordmetric-doc/examples/queries/mysql_backend.sql 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.
- sida 12y agoI 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?
- paulasmuth 12y ago> 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!)
- paulbjensen 12y agoAs someone who built a dashboard app, I tip my hat: this is a very powerful new feature.
- haney 12y agoThis 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.
- sida 12y agoChart.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)
- paulasmuth 12y agoA "deploy to GCE" button is something I am planning to add in the next weeks.
- deleted 12y ago[deleted]
- rrggrr 12y agoChart.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.
- rgrieselhuber 12y agoAs 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.
- rrggrr 12y agoThe 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/ http://idl.cs.washington.edu/projects/lyra/
- acbart 12y agoDoes this allow Joins and Subqueries?
- thomstabk 12y agoThat's what I was wondering. Hard to tell from this: http://fnordmetric.io/documentation/chartsql/syntax_reference/ http://fnordmetric.io/documentation/chartsql/syntax_referenc...
- davis 12y agoAny reason for the selection of GPL3 for the license?
- paulasmuth 12y agoYes, actually: https://www.gnu.org/philosophy/why-not-lgpl.html https://www.gnu.org/philosophy/why-not-lgpl.html
- grundprinzip 12y agoLooks like a tool I need. Unfortunately, it looks like the MySQL backend is more a promise than reality.
- paulasmuth 12y agoHere is a working example: https://github.com/paulasmuth/fnordmetric/blob/master/fnordmetric-doc/examples/queries/mysql_backend.sql https://github.com/paulasmuth/fnordmetric/blob/master/fnordm... And a few bits of documentation here http://fnordmetric.io/documentation/chartsql/external_data_sources/ http://fnordmetric.io/documentation/chartsql/external_data_s...
- grundprinzip 12y ago./build/cmake/target/fnordmetric-server --http_port 8080 --storage_backend mysql [ERROR] unknown backend type: mysql
- jewel 12y agoIf 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.
- chenster 12y agoI 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.php 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 12y agoThis is rather amazing. I applaud the idea of extending SQL for this, but honestly how hard is it to implement the middleware?
- paulasmuth 12y agoIt's actually fairly straightforward, you can check out the code here: https://github.com/paulasmuth/fnordmetric/tree/master/fnordmetric-core/src/sql_extensions https://github.com/paulasmuth/fnordmetric/tree/master/fnordm...
- sinatra 12y agoThis 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 12y agoAt 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 12y agoThank 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 12y agoIf you can't afford Geckoboard, Dashing is open source https://shopify.github.io/dashing/ https://shopify.github.io/dashing/ There's a tutorial specifically about MySQL https://github.com/Shopify/dashing/wiki/How-to%3A-send-mysql-data-to-your-widgets 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 https://github.com/Shopify/dashing/wiki/Additional-Widgets
- cheald 12y agoElasticsearch + 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.
- dvirsky 12y agoAnother project that tries to achieve something similar is Redash https://github.com/EverythingMe/redash https://github.com/EverythingMe/redash
- sida 12y agoThis 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
- dvirsky 12y agoIn 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 12y 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 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:dash-instance https://github.com/EverythingMe/redash/wiki/Setting-up-re:da...
- nodesocket 12y agoWe 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 12y agoThanks 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!
- mcx 12y agoThis 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.
- aluhut 12y agoEris is strong in this one.
- RA_Fisher 12y agoLooks 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 12y agoThat 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 12y agoAlternatively 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
- mmsimanga 12y agoForena[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
- vram22 12y agoInteresting 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-free-plan.html http://jugad2.blogspot.in/2014/02/domino-python-paas-now-has...
- gingerlime 12y agoThis 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 12y agoI 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 12y agoAmazing work.