Data Adapter overview
Get SQL-like access to the full set of your organisation's data
Overview
Root’s Data Adapter provides comprehensive, SQL-like access to your organisation's data on Root for Business Intelligence (BI) and engineering teams. This means you can use analytics and data processing tools you are familiar with to build and automate regulatory, sales and financial reporting, create bespoke performance dashboards, and more. The available data includes all entities returned over the API.
Data Adapter creates daily snapshots
The Data Adapter creates snapshots of each organisation's data at midnight every day. This means that, when you connect to the Data Adapter, you are accessing the latest snapshot. Any data changes that occurred after the snapshot was created will not be reflected.
The SQL interface is provided through AWS Athena. Most BI tools such as Power BI, Google Looker, Tableau and Qlik can access the Data Adapter, either through direct integration or using a dedicated plugin.
Set up the Data Adapter
To connect to Athena you will need your unique organisation-specific AWS Athena credentials. This guide will show you how to find your credentials and connect to Athena.
Step 1: Get your credentials
Keep your credentials safe
Please keep your credentials safe and only share them with trusted users. If you think your credentials have been compromised, please create a new access key pair and get in touch with Root support ([email protected] as soon as possible.
To get your credentials, log into the Root Dashboard, access your organisation and navigate to Data Management (
) > Data Adapter. You will find the following information for your organisation:
- AWS access key ID (a.k.a. username)
- AWS secret access key (a.k.a. password)
- AWS region
- AWS S3 path
- Athena workgroup
- Athena schema
To generate an AWS secret access key click on the "Fetch new secret" button. The newly generated secret key cannot be viewed again once the modal has been closed, so make sure you keep it somewhere safe.
Step 2: Connect your data tool to the Data Adapter
You can connect to Athena either by using the AWS SDK or by using the AWS Athena JDBC drivers.
The AWS SDK allows you to connect your app to Athena and write code that uses the data available on the Data Adapter. You can read more about using the SDK here.
To start using the Data Adapter with your preferred tool, your tool needs to support the AWS Athena JDBC driver. Here is a list of popular tools that are compatible with the JDBC driver:
Some tools and services require you to manually download the JDBC or ODBC drivers available on the AWS website. These drivers allow you to connect various tools and services such as Microsoft SQL Server. To set up your tool or service with these drivers you may require assistance from your internal IT department. Please visit the AWS website to find the latest drivers and find further guidance from your tool or service provider.
If you struggle to connect, we suggest completing the Tutorial: Using Data Adapter with DBeaver to familiarise yourself with the setup process.
Common issues when connecting to AWS Athena
| Issue | Resolution |
|---|---|
| AWS Athena server address required | If your tool requires a server address you can find the correct one for your region here. Typically, it would be the following: athena.<aws-region>.amazonaws.com. |
| JDBC driver required | Your tool may require the JDBC driver to connect to AWS Athena. Please refer to your tool's guides. The latest driver can be downloaded from Amazon here. |
| ODBC driver required and DSN | Some tools like the Amazon Athena Power BI connector require the ODBC driver to connect. To install and configure the ODBC driver (and create a DSN) you can follow the instructions in the official ODBC driver installation and configuration guide on the AWS website. Alternatively, if you are configuring the driver on Windows, you can follow this YouTube tutorial. |
| AWS workgroup required | To run SQL queries through AWS Athena, the AWS workgroup needs to be added to the connection configuration. Depending on the tool you are using there might not be a separate input for the workgroup. You may need to look for tutorials online to help configure the workgroup for your tool. Some platforms require you to add the workgroup to the server address like this: athena.<aws-region>.amazonaws.com:443;workgroup=<athena-workgroup>; |
For further technical information on accessing Athena you can visit this guide on AWS.
Updated 11 months ago