In addition to API access, the Common Data Model (CDM) can be read from a database connection via SQL to support bulk data transfer use cases such as feeding data in to machine learning models for further analysis. The SQL access is supported via a MySQL compatible database protocol accessed via an SSH tunnel. This connection type is generally well supported by various programming language database connection libraries as well as popular business intelligence tools.
Before gaining access to your database you will need to exchange an SSH key with your administrator.
Before you Start
There are a number of steps that need to be completed to access data via SQL, they are:
An SSH key needs to be created and shared with Boss Insights to gain access to a secure tunnel to access data
Your administrator needs to enable SQL Access for your user account in the admin portal
You need to obtain your access identifier from your profile
Exchange of SSH Keys
Send your public key to Boss Insights and take note of location of keys, particularly your <private key file>. To send the key file and request access create a support request for SQL access.
If you already have existing SSH key pairs on your local machine for accessing other resources, please ensure you do not overwrite them when creating the required keys for your project. This can be avoided by assigning a unique name to the key pair file that is specific to your Boss Insights project (e.g Users/userid/.ssh/boss).
Generate a pair of SSH keys
Generate your SSH keys on Windows
1. Download Cygwin from https://cygwin.com/install.html
2. Install Cygwin on Windows (reference https://www.youtube.com/watch?v=hh-V6el8Oxk ), On the 'Select Packages' page, enter 'openssh' in the search box, expand 'Net' and select to install.
3. Generate a pair of ssh keys on Cygwin terminal with the command:
The default location of the pair of keys is: C:\cygwin64\home<userid>.ssh
Or if the key file name is changed during key pair generation without pointing to an absolute path, the location of the pair of keys: C:\cygwin64\home<userid>Where <userid> is your local computer's username
Generate your SSH keys on macOS, Linux or Unix-like system
1. Open a terminal on your computer
2. Generate a pair of ssh keys in the terminal window with the command:
3. Choose a location to put the generated key files, being careful not to overwrite existing keys.
4. Then enter a passphrase and confirm the passphrase. The key pair is generated.
An administrator of your account will need to edit your user account from the administration portal and choose
Show Advanced Options and then choose to enable SQL Data Access. It can take up to one hour after this access is enabled for our backend database systems to synchronize.
Exact instructions will differ depending on the tool being used to connect to the database but generally you will need the following connection information:
The SSH host name you connect to is region dependant:
Within the main portal click on the user icon in the top right corner and choose “View Profile” and then click the “Show Access Identifier” button.
N/A - Use a key
To test connectivity to the SSH Tunnel you will need to connect using a MySQL client that supports SSH tunnels such as MySQL Workbench. ( https://www.mysql.com/products/workbench/ ) or a compatible database library if connecting from a specific programming language.
You cannot connect to the bastion host with a regular SSH client as shell access is disabled. The bastion only allows MySQL traffic to be tunnelled.
User / Password
Available within your user profile, click the “Show Access Identifier” button
Once connected you can access each CDM by the schema name “database-KEY” where KEY is the unique alphanumeric account key. If multiple accounts share data with you they will be shown as individual schemas.
Python is a popular programming language choice for our users, the following package and code example has been a helpful starter: https://github.com/pahaz/sshtunnel/
# pip install pymysql sshtunnel
from sshtunnel import SSHTunnelForwarder
tunnel = SSHTunnelForwarder(
ssh_private_key="<INSERT PRIVATE KEY>",
connection = pymysql.connect(user='<INSERT USER>', passwd='<INSERT PASSWORD>',
database='database-<INSERT ACCOUNT KEY>')
cursor = connection.cursor()
Other languages and tools
If you are using a language or tool that doesn't support SSH tunnelling natively you can use SSH itself to forward the remote connection to a local network port that you can then connect to:
ssh -fNg -L 3307:db:3306 <INSERT USER>@db.myintranetapps.com
This will open the local network port 3307 so you can connect to localhost:3307 using your mysql client.