[AppInsights] Data Sources - MySQL, PostgreSQL, Amazon Redshift connections

This tutorial is a more in-depth description of how to use our Database integrations. This tutorial is also applicable to Amazon Redshift, which at its hood is just a PostgreSQL database.

We currently support two types of relational databases: MySQL and PostgreSQL. Please send us any suggestions for other databases you want us to support!

Initial Setup

We strongly suggest you create a read-only user for your database, and you limit it only to the tables you wish to query. This is for security purposes; we do not sanitize the queries you enter in any way, so we would not want you to modify or delete any of your own data! Additionally, we suggest that you connect directly to a backup database or a read replica instead of your production database.

MySQL: This is a good tutorial on creating a read-only user: https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

PostgreSQL: The top-voted answer here has good instructions on creating a read-only Postgres user:
http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql

Additionally, many users keep their databases behind a firewall; in order for us to be able to access your database, please open up your database port to the following IPs:

52.73.237.42
54.152.187.254

If you wish to use SSL on Postgres or Amazon Redshift, this is supported by our integration and you do not need to do anything. SSL on MySQL is not yet supported.

Finally, please make sure your database character encoding is set to UTF-8. We only support this encoding!

Authentication
Authentication is largely the same for our three database types. 



For Database Engine, select between MySQL and PostgreSQL. (If you are using the Amazon Redshift integration, this field will not show up).

For the Host, enter the IP address of your database instance, in the format x.x.x.x. You can also enter a DNS name here, like mysql-us-east.rds.amazon.com, for example.

Username and Password should be the credentials for your read-only user.

Database Name is the name of the specific database you want to connect to.

Port is optional and only necessary if your database is using a non-default port.

After filling in the fields, click Connect. If there is an error, you will see a dialog and be prompted to re-enter your information. Typically, if there is a timeout error, check to make sure your firewall is open to the IPs earlier in this document.

Using AppInsights Query widgets
For this tutorial, we will use the following table of data. It has a row for every hat trick that was scored in the Spanish Football League. In our examples, the table will be named hat_tricks.

These queries are applicable to any of the three database types we support (MySQL, PostgreSQL and Amazon Redshift).

Number Query
For the number query widget, you must enter a query that returns a number. For example, entering the following query:

SELECT COUNT(*) from hat_tricks WHERE team = "Barcelona"

and clicking Save will result in the number 7 being displayed on your number widget. Note that each query type has a “Query Frequency” parameter. This allows you to select how often you wish this query to execute on your database server, and can be changed to various intervals from 10 minutes to 24 hours.

Table Query
The table query widget accepts data in a tabular format. Typically any database query can be represented as a table. For example, you can do something simple like:

SELECT player, date FROM hat_tricks

This will show you a table of players and the date of their hat tricks. 

Leaderboard Query
The leaderboard query widget requires your query to return two columns of data. The first column will be used as the labels and the second column as values. You can use a query like the following:

SELECT team, COUNT(team) FROM hat_tricks GROUP BY team

Which will return a leaderboard like the following. The leaderboard query can also be used for bar graphs and pie chars.



XY Query

The XY Query must return two columns of data, both of which will be interpreted as numerical data. The first column will correspond to X and the second to Y. You can use this to see trends, for example, number of hat tricks per month:

SELECT (EXTRACT(MONTH FROM date) + 4) % 12 as month, COUNT(id) FROM hat_tricks GROUP BY month

This query gives you an X-Y graph, where X is the month from September (1) to May (9), and Y is the number of hat tricks for that month.



Multibar Graph
Our multibar graph is our newest and most powerful visualization/data analysis widget, and its functionality is similar to that of a pivot table. A multibar query must return three columns of data; the first two columns are used as the labels for the bars and bar segments, respectively, and the third column is numerical data. 

For example, the following query shows the breakdowns of hat tricks by quarter, the quarters being September-November, December-February, and March-May.

SELECT ((((EXTRACT(MONTH FROM date) + 4) % 12) - 1) DIV 3 + 1) AS quarter, player, COUNT(id) FROM hat_tricks GROUP BY quarter, player