How to pull data from SaaS software, enterprise apps behind a firewall and SQL databases onto every device you own.
Integrations | Feb 3
Boris Sagadin on September 13, 2016 • 7 minute read
Is there an organization out there nowadays that doesn’t use some kind of an SQL database?
Not very likely.
Love it or hate it, from simple blogs to fortune 500 companies, everyone uses SQL. When data needs to be structured and stored in an organized manner, SQL is very likely the most efficient tool to use.
At the moment, MySQL, PostgreSQL, Microsoft Azure SQL, and Amazon Redshift are supported out of the box on Databox. You have the data ready in your database, now it just needs to get visualized in an easy and concise manner so that everyone – even your boss – can use.
So, let’s get to it!
What Will We Accomplish?
In this article, we will explore MySQL connection setup from the server point of view, then we’ll connect it to Databox and confirm that the connection is working. Lastly, we will create a Datacard visualizing the data. All this without a single line of code – except for the MySQL query, of course 🙂
To get started, you’ll need to allow remote connections from Databox’s public IP 18.104.22.168 to your database. The necessary steps to do that will depend on your database, server infrastructure and firewall. VPC IP must be able to connect to the database port directly.
We will create a user named ‘user’ with permissions to remotely access the database, aptly named ‘mydb’ in our example:
GRANT SELECT ON mydb.* TO 'user'@'22.214.171.124' IDENTIFIED BY 'securePassword';
Note: for simplicity’s sake, we have given this user a SELECT permission on all tables in our database. Permission could be given to select from one table only, or better yet: create a custom view and give permissions to select from this view only. But this is a bit beyond the scope of this document. A basic example on how to achieve this can be read here.
Configure MySQL server to listen on all IPs, as by default it listens on local interface only. Open
/etc/mysql/my.cnf, depending on linux distro) and check that it contains:
#skip-networking # commented out! bind-address = 0.0.0.0 # Will listen on all IPs
If you’ve made any changes, restart the mysql server and it’s ready to accept remote connections.
Port 3306/TCP, which is MySQL default port, should be accessible from our VPC IP mentioned above. This must be done on your firewall, Linux iptables example:
iptables -A FORWARD -s 126.96.36.199/32 -p tcp --dport 3306 -j ACCEPT
Note: you might have to substitute FORWARD with INPUT if your Linux box has a public IP and the database runs on the host itself.
Your server should now be successfully set up to accept requests from our IP to your chosen database/table, using chosen user and password.
With our database ready, the next logical step is to connect it and test that it’s returning the data we want.
Great! We have just successfully connected our database to Databox. In the next step, we’ll write a custom query that will regularly fetch data from your database and make it available for use in any Datacard.
Troubleshooting: If you get a “wrong credentials” message, double-check your user data. If you’re stuck on ‘Activate’ for a minute or so, it’s probably having issues connecting to your database host due to firewall / server / networking issues.
Now that the database is connected, we will use the Designer to query, shape and display the data in a format that’s most appropriate and useful for our needs:
SELECT COUNT(p.ID) AS posts, u.display_name FROM dbwp_users u, dbwp_posts p WHERE p.post_author=u.ID AND p.post_type='post' GROUP BY u.ID
We have just written a custom MySQL query and displayed its results. Databox will continuously, each hour, fetch data from this resource and store it in the selected target data source (in our example ‘WordPress SQL’).
Note: we’ve used the
AS SQL construct in our query (i.e.
AS posts). That’s not mandatory, but it will describe the data as a metric key. If you use
AS date this column will represent then the date and time of the value (ISO 8601 date and time standard is supported). You can leave it as it is, or rename the result column instead. Semicolon at the end of the query is not needed either.
Troubleshooting: If you don’t see any data, double-check your SQL query, try it directly on your database. If it’s not displaying results there, you have an error somewhere in your query. Also check that MySQL user has necessary permissions to access the database from Databox IP.
Well done! Your database is now connected to Databox, queries can be executed and then displayed on your mobile / big screen / computer.
Go ahead and explore further. Add more queries, add blocks, explore different types of visualizations. Make that perfect Datacard (or Datawall of course) you always needed but didn’t know how to get. Now you can! Clean and professional, right at your fingertips. Only data that matters, without clutter. The possibilities are truly endless.
Ready to try it for yourself? Signup for free today and let us know how it went for you.
Remember: we’re always glad to help if you run into any obstacles!
Integrations | Feb 3
Integrations | Jan 19