WebsiteWe're hiringCourses & Certifications→ Get started for free
Guides

Connect to Data Adapter

Get SQL access to your organisation's data

Suggest Edits

📘

Fair usage policy

As part of our commitment to provide a high-quality, fast and reliable service, we have a fair usage policy (the Fair Usage Policy) for certain of our Software-as-a-Service offerings, including the Data Adapter.

By using our services, you agree to be bound by this Fair Usage Policy.

Overview

Root's Data Adapter provides comprehensive access to all of your organisation's data on the Root platform. We provide SQL access to all entities that can be retrieved over the API. This allows your engineering, Business Intelligence (BI) and Machine Learning (ML) teams to connect their favourite data processing and analytic tools to Root. Build your own dashboards, answer operational queries, automate reporting (eg. sales, financial, regulatory), and more.

📘

Daily snapshots

The Data Adapter works on snapshots that are created daily at midnight UTC. This means that any updates that occurred after the last snapshot will only reflect in the next snapshot.

Each day at midnight UTC, Root generates a snapshot of your organisation's data. Production data is made available via AWS Athena.

Your engineering team can access AWS Athena via the AWS SDK or CLI tools. You can also execute SQL queries directly by configuring JDBC or ODBC connections.

Most BI tools support integration with AWS Athena, natively or by installing a plugin). Tools such as Microsoft Power BI, Google Looker, Tableau and Qlik can all integrate with Data Adapter.

1600

Data Adapter example query

Connect to Data Adapter

Step 1: Set your role permissions

To fetch the credentials that will allow you to start using the Data Adapter, you need to have the right permissions enabled for your role. Your Root Admin needs to:

  1. Go to your "Organisation settings" (the building logo at the bottom of the blue menu bar on the left)
  2. In the grey menu, select "Roles" and click on the role that you would like to edit
  3. Click "Edit role"
  4. Select the "Data adapter" checkbox
  5. Click "Update"

Step 2: Retrieve your credentials

To get your credentials, log into the Root Dashboard and navigate to the " Data Management > Data Adapter".

On this page you will find the information you need to connect to Data Adapter:

If your organisation has an existing key for the current environment, you will be able to view the access key ID, but not the secret access key. The secret access key is only displayed once when generating a new access key pair, and is not stored on the Root platform.

To generate a new access key pair, you can click the "Generate new" button. Make sure you copy the secret access key somewhere safe before closing the modal.

Note: Each organisation is limited to one access key pair per environment. Creating a new access key pair will delete the existing pair for that environment.

To keep track of Data Adapter access, you can view a generation history log. This shows who generated credentials and when. You’ll find this log under Data Management > Data Adapter in the Root Dashboard, helping you monitor credential activity for added security.

📘

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 the Root support team as soon as possible.

Step 3: Connect your data tool(s)

After retrieving your credentials, the next step is to configure your preferred data tool(s) to connect to AWS Athena. Some tools will have built-in support for connecting directly to AWS Athena, while others require installing plugins or configuring JDBC or ODBC drivers.

📘

Configuring JDBC or ODBC drivers

Some tools or services require installing and configuring ODBC or JDBC drivers. You may need assistance from your internal IT department to connect your specific tool, for example configuring a Microsoft SQL Server connector. Please consult the AWS documentation for the latest drivers, and for further guidance.

You can find suggested solutions for common issues the the last section of this guide.

We suggest completing the DBeaver tutorial to familiarise yourself with the setup process and the Root data structures.

Here are setup guides for some popular tools:

Common issues connecting to AWS Athena

IssueResolution
ODBC driver required (and DSN)Certain tools (like the Amazon Athena Power BI connector) require the ODBC driver to be installed and configured (to create a DSN). You can follow the official AWS guide for ODBC driver installation and configuration. Alternatively, you can follow this video tutorial for installing and configuring the driver on Windows.
JDBC driver requiredCertain tools require the JDBC driver to connect to AWS Athena. Please refer to your tool's guides. The latest driver can be downloaded from Amazon directly.
AWS Athena server addressIf your tool requires a server address, you will need to look up the correct Service endpoint, for the AWS region as specified in your Data Adapter credentials.

Typically, it would be the following: athena.<aws-region>.amazonaws.com
AWS Athena workgroupThe AWS workgroup field is required for executing queries on AWS Athena. If your tool does not provide an explicit field for configuring the workgroup, you can often specify it as part of the Server address field:

e.g. athena.<aws-region>.amazonaws.com:443;workgroup=<athena-workgroup>;
Failure to query dataAWS Athena uses port 444 for streaming query results. If this port is closed or blocked on your network, you may encounter errors when attempting to query data.

Common error messages include:
AthenaJDBC An error has occurred. Exception during column initialization.
Port configurationAthena uses port 443 to receive requests and port 444 to respond. If your environment has strict firewall settings, you will need to ensure that both ports are whitelisted. Failure to do so may result in connection issues.

Updated 28 days ago

What’s Next