Connecting to Data Warehouse Sync using ODBC

Jason Chu

September 20, 2023 15:07

Available for	Roles	Super Admin
	Permissions	• Manage Data Warehouse Sync integration
	Packages	Advanced Analytics add-on

In order to connect to your reporting data, you will need to connect to AWS Athena. This can be achieved in one of several ways:

Using your business intelligence tool. Your BI tool should have supporting documentation on how to connect.
Using an Amazon-provided driver like Open Database Connectivity (ODBC) or Java Database Connectivity (JDBC).
Using a third-party tool, such as DBeaver.

This guide walks through how to connect to AWS Athena on Windows using ODBC. If you are interested in learning more, check out our Data Warehouse Sync help article.

Configuring your connection to Athena using ODBC

Download and install the latest version of the ODBC driver here: https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html
- You should download the 64-bit version unless you are running an older version of Windows and are sure that you need the 32-bit version.
Open the ODBC Data Sources utility

ODBC data sources utility

Select the 'User DSN' tab and click Add...

User DSN tab on ODBC Data Source Administrator modal with arrow pointint to Add button

Select 'Simba Athena ODBC Driver' and click Finish

Create new data source modal with Simba Athena ODBC Driver selected

Add the following fields. Do not edit any other fields.

- Data Source Name: This can be anything you want, but it is recommended that you name is something descriptive, like "Lever Data Warehouse Sync"
The following details are listed in Lever in Settings > Integrations and API > Reporting Warehouse.
- AWS Region: This is the AWS region that you are connecting to. Currently, the only available region for DWS is "us-west-2".
- Workgroup: It's usually the same as the name of your Lever instance.
- S3 Output Location: Copy and paste the following location exactly, substituting {accountID} with your own Lever accountID.
```
s3:::services.lever.us-west-2.production.etl/accounts/{accountID}/output/
```
Select Authentication Options... and set the Authentication Type to 'IAM Credentials'; fill in the User and Password using the username and password listed in Lever in Settings > Integrations and API > Reporting Warehouse.

Authentication options modal with IAM Credentials in Authentication Type field

Click OK and then click Test...
- If the connection fails, save the error message and send it to Lever Support for further diagnosis; if the connection succeeds, you should see a message similar to the one shown below.

Test Results modal with success message

If you are having troubles connecting, check out our article on common connector issues for Data Warehouse Sync.