Documentation

Custom Database Connections

 

datapine easily connects to all standard relational databases, such as Oracle, MySQL, Postgres or MS Server. Depending on the database type or instance on which your database is hosted, the database connection setup in datapine might slightly differ. Please see our tips and hints below on how to prepare your custom database instances to connect them fast and easy to datapine.

 

AmazonRDS

 

If your MySQL, Oracle or SQL Server database is hosted on Amazon RDS instance you will need to provide us with the Amazon RDS endpoint as well as the port number. The database connection setup works like any other database setup, you will simply need to add the credentials from Amazon RDS in the host and port fields in our database connection screen.

 

Before connecting your MySQL database please make sure that you have white-labeled our IP addresses (83.141.3.28 and 83.141.3.29) in your Amazon RDS account by adding them to your security groups.

 

Azure

 

To connect SQL Server via Azure to datapine log in to your Windows Azure account and retrieve the server name and port number of the database you wish to connect from the Windows Azure management console.

 

Before you start connecting your SQL Server via Azure to datapine you will need to edit the IP settings in Windows Azure. Therefore, go to the dashboard screen of your database in Windows Azure console and click on Manage IP-addresses. Add our IP addresses 83.141.3.28 and 83.141.3.29 and save these changes.

 

Google Cloud SQL

 

Before you may connect datapine to your Google Cloud SQL Instance you will need to authorize our IP to allow us to access your hosted database and in case your instance is a MySQL 5.7 you will need to additionally configure the default SQL Mode of your Google SQL instance. Therefore, login into your Google Cloud console and access your Cloud SQL Instance under Resources. On the instance page of your Console, select the instance on the left to open its Overview page.

 

a) Add the network

 

1) Enable the Edit Mode to be able to modify your instance.

 

2) Select the Access Control > Authorization section, click on + Add network and enter our IP 83.141.3.29 to the authorized networks.

 

how to add a network in google cloud SQL

b) Configure Cloud SQL flags

 

The Cloud SQL flags allow you to adjust the MySQL parameters and options of your instance. For MySQL 5.7 instances the SQL mode will be set to sql_mode=only_full_group_by on default.This mode might interfere with some of the analytical functions run in datapine and needs to be adjusted accordingly. We recommend to use sql_mode=TRADITIONAL.

 

1) On your instance overview page, enable the Edit mode with a click on Edit and scroll down to the Cloud SQL flags section at the bottom of the page.

 

2) Click on the sql_mode dropdown and select TRADITIONAL

 

3) Click Done and then click Save at the bottom of the page to save the changes.

 

how to add a SQL flag in google cloud SQL

 

You may now connect your databases hosted on your Google Cloud SQL to datapine. For more information please click here.

 

Heroku

 

If your Postgres database is hosted on Heroku you can connect to your Heroku Postgres account using the database URL provided in the Heroku interface. To setup a connection, you will need to retrieve the connection URL from your Heroku Account.

 

Here is how to get the connection URL to Heroku Postgres:

 

  1. Log in to your Heroku Postgres interface.
  2. Go to Apps in the navigation bar and select the app that you wish to connect to datapine.
  3. Click on settings and then select Reveal config vars.
  4. Copy the DATABASE_URL and paste this into the Host/IP field in the database setup in datapine. Please make sure that you have previously selected Postgres as database type in the connection setup screen.

MySQL

 

To connect your MySQL database you will be asked to provide the username and password to your database. Please make sure that your user has read-only access to the tables in your database which you wish to connect to datapine. You can also restrict the access of the user to certain tables.

 

Oracle

 

If you are connecting an Oracle database please make sure that you have the Oracle SID on hand. The Oracle SID is the instance name of your databases and will be required when entering your credentials into the database connection screen. You can either select one of the default Oracle SIDs given in the dropdown menu or simply type in your unique SID into the field.

 

PosgreSQL

 

Connecting a Postgres database requires you to provide the credentials of a read-only user to your database. In the database connection screen simply select Postgres as database type and proceed by following the step-by-step instructions for connect a database.

 

SQL Server

 

SQL Server belongs to the standard connections that you can choose from in the database connection process. We support connections between our server and Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2012.

 

Please make sure you have created a read-only user on your Microsoft SQL Server before connecting to datapine.