Being a little bit bored with the default visualization tools in R and Python, I decided to look into the available visualization tools that are out there in the data ecosystem. The Belgian visualization tool cumul.io focusses on integration in platforms, but it also seems like a solid entry-level visualization tool with a decent number of connectors that should work out of the box. Let’s try MySQL!
I hear you thinking out loud that MySQL might not be your first choice as a data warehouse. That’s correct, but I myself have bumped into some occasions where all website data (like users and purchases) are simply stored in a MySQL database. This is a situation that likely won’t occur in multinational companies with a robust big data stack. On the other hand, these are not the companies that are within cumul.io’s market segment. So if you work for an SME with most data in a MySQL database, this blog post might be of interest to you.
From the datasets section of the tool, you can easily add a new MySQL connection. As I said, it comes out of the box.
If you’re not working via an SSH tunnel. Here are steps you should take to keep the connection safe:
- Whitelist the IP’s of cumul.io — 88.99.71.232 and 52.213.28.47
- Create a user that has only reading rights
I host my MySQL database in Google Cloud Platform: whitelisting the IP addresses of cumul.io is straightforward. If you’re accessing a company database, you should contact your IT department.
You can’t create a read-only user through the GCP visual interface. Create a read-only user with the following commands. Once again, if you’re working in a company database, don’t fiddle around yourself and ask you IT department.
CREATE USER 'cumul'@'%' IDENTIFIED BY 'mypassword';
GRANT SELECT ON myatabase . * TO 'cumul'@'%';
FLUSH PRIVILEGES;Finally, fill in the credentials in the cumul.io interface.
There might be some errors that you run into. Here’s the first one:
The database host could not be resolved. Are you using a publically routable hostname or IP address (an IP from a public range)? Could not connect to this database. Try our troubleshooting guide!
If you run into this error there are two possible causes: (1) the IP address and port are incorrect or (2) you did not whitelist cumul.io’s IP addresses (see higher).
Here’s another error:
This username/password combination is not valid, the user is not allowed to connect remotely or this database is not accessible by this user. Could not connect to this database.
It is clear that cumul.io was able to detect your MySQL server. But once again, there might be two causes: (1) The username and password are incorrect. Make sure you took the earlier steps and that you specifically gave the username access to the database you need. (2) The database does not exist.
So, this should get you started. You can now select the tables you need and use them in your visualizations.