Databases: MySQL, PostgreSQL, Amazon Redshift connections

This tutorial is a more in-depth description of how to use our new Database integration. 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. 

integration database connection 1

For Database Engine, select between MySQL and PostgreSQL. (If you 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.

 

id

player

team

against

date

1

Cristiano Ronaldo

Real Madrid

Deportivo La Coruna

9/20/2014

2

Cristiano Ronaldo

Real Madrid

Elche

9/23/2014

3

Neymar

Barcelona

Granada

9/27/2014

4

Cristiano Ronaldo

Real Madrid

Athletic Bilbao

10/5/2014

5

Lionel Messi

Barcelona

Sevilla

11/22/2014

6

Carlos Vela

Real Sociedad

Elche

11/28/2014

7

Cristiano Ronaldo

Real Madrid

Celta Vigo

12/6/2014

8

Lionel Messi

Barcelona

Espanyol

12/7/2014

9

Antoine Griezmann

Atletico Madrid

Athletic Bilbao

12/21/2014

10

Lionel Messi

Barcelona

Deportivo La Coruna

1/18/2015

11

David Barral

Levante

Malaga

2/7/2015

12

Lionel Messi

Barcelona

Levante

2/15/2015

13

Alberto Bueno

Rayo Vallecano

Levante

2/28/2015

14

Lionel Messi

Barcelona

Rayo Vallecano

3/8/2015

15

David Barral

Levante

Almeria

4/4/2015

16

Cristiano Ronaldo

Real Madrid

Granada

4/5/2015

17

Santi Mina

Celta Vigo

Rayo Vallecano

4/11/2015

18

Luis Suarez

Barcelona

Cordoba

5/2/2015

19

Cristiano Ronaldo

Real Madrid

Sevilla

5/2/2015

20

Cristiano Ronaldo

Real Madrid

Espanyol

5/17/2015

21

Cristiano Ronaldo

Real Madrid

Getafe

5/23/2015


integration database connection 2

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.

integration database connection 4

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

integration database connection 5

Was this tutorial helpful?  Please take a moment to submit your “Yes” or “No” answer below!  You can also contact us or post a public question for further instruction.

Did you find this article helpful?