Currently in Beta
Data Adapter is currently undergoing beta testing and is not yet available for all organisations. If you would like to get early access, please reach out to [email protected].
Root’s Data Adapter provides comprehensive, SQL-like access to you 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
Data Adapter creates snapshots of each organisation's data at midnight every day. This means that, when you connect to 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 Data Adapter, either through direct integration or using a dedicated plugin.
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.
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 and navigate to the Workbench () > Data Adapter page for your organisation. 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.
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.
|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: |
|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: |
For further technical information on accessing Athena you can visit this guide on AWS.
Updated about 2 months ago