Connect over ODBC

Any app that reads data over ODBC can read your project’s tables directly, without downloading files: spreadsheets, BI tools, SQL clients and scripts alike. You set the connection up once per computer, and from then on each refresh in the app brings the data up to date. An admin of the project gives each person access in Data Egress; the person then installs a driver and saves the connection on their computer.

Before you start

• A Windows computer. Amazon also publishes the driver for macOS and Linux, and its own documentation explains how to save the connection there; the steps below are for Windows.
• An app that connects over ODBC, in its 64-bit version. The driver only exists in 64-bit, and a 32-bit app does not see it. The app’s “About” window usually says which one you have: in Excel, File → Account → “About Excel”, where the first line ends in 64-bit or 32-bit.
• Permission to install software on the computer, or someone from IT who can.
• Your Teramot user: it is the one you sign in with. There is no separate password.
• Internet access. On a locked-down network, IT has to allow traffic to Teramot and to Amazon Web Services.

The connection is for apps a person runs on their own computer: signing in opens a browser there. A report published to an online service or to a shared server cannot refresh through it on a schedule; open it in the app on your computer and refresh there.

In Teramot: give the person access

Someone with the Admin role in the project does this, from Operations → Data Egress with the project open.

1. In User Management, click Set up on the person’s row. The list shows everyone in the project, including people added only to this project. If someone is missing, add them to the project, not to the workspace: a workspace member can open every project in it.
2. Click Activate access. It uses their Teramot user, so they get no new password.
3. Switch on each table they can read. Access is table by table: if they later miss one, switch it on here and it shows up the next time they refresh.
4. Click Copy connection data and send them the message. It says what to install and lists every value with the name of the field it goes in. None of the values is a password. The Connection data tab shows the same values one by one, and Copy all fields copies them all.

Install the driver

Your app talks to Teramot through the Amazon Athena ODBC driver, free and published by Amazon. You install it once per computer.

1. Open the Amazon Athena ODBC 2.x page.
2. Under “ODBC 2.x driver download”, on the Windows 64 bit row, click “Download link”. You get a file ending in .msi.
3. Open it, tick “I accept the terms in the License Agreement” and continue with “Next” and “Install” without changing anything. If Windows asks whether to allow changes, say yes. Close with “Finish”. No Amazon account is needed.

Create the connection in Windows

The connection, which Windows calls a DSN, keeps your project’s values so any app on the computer can use them every time it refreshes.

1. In the Windows Start menu, type ODBC. Right-click “ODBC Data Sources (64-bit)” and choose “Run as administrator”. It has to be the 64-bit one.
2. Go to the “System DSN” tab, so the connection works for every user of the computer.
3. Click “Add…”, choose “Amazon Athena ODBC (x64)” and click “Finish”. The installer may have left an empty example called Amazon Athena: you can fill that one in instead.
4. In the window that opens, fill each field with the value of the same name from the message. Copy and paste rather than type: one wrong letter and the connection fails. “Data Source Name” is up to you, for example Teramot: it is the name you pick in your app. Replace the example values the window comes with.
5. Click “Authentication Options”. Set “Authentication Type” to BrowserOidcTip, between JWT_TIP and Ping, not the look-alike BrowserSSOOIDC. Then fill in the fields that appear, the same way. Leave Client Secret empty and tick “Enable JWT TIP File Cache”, so it does not ask you to sign in every time.
6. Close that window and click “Test”. A browser opens: sign in with your Teramot user. When the success message comes back, click “OK” to save. Closing with “Cancel” saves nothing.

Use the connection from your app

If your app was open while you created the connection, close it and open it again first.

1. In your app, add a data source of type ODBC. Each app names it its own way, such as “ODBC”, “From ODBC” or “Other databases (ODBC)”.
2. Pick the connection by the name you gave it. If the app asks for a connection string instead, type DSN= followed by that name, for example DSN=Teramot.
3. If it asks for a user and password, or how to sign in, leave both empty and choose the option that uses the connection’s own settings: the sign-in happens in the browser.
4. The tables are in the AwsDataCatalog catalog, inside your project’s database: the Database value from the message. Only the tables you were given access to are there.

Example: Excel

With desktop Excel, 64-bit (Microsoft 365, or Excel 2016 and later):

1. Find the ODBC option, which depends on your version of Excel:
   • Microsoft 365, Excel 2019 or 2021: Data → Get Data → From Other Sources → From ODBC.
   • Excel 2016: Data → New Query → From Other Sources → From ODBC. That version’s “Get External Data” menu does not work for this.
2. Under “Data source name (DSN)”, choose the name you gave the connection and click “OK”. If it asks for a user and password, choose “Default or Custom” and click “Connect” without filling anything in.
3. In the “Navigator”, open AwsDataCatalog and then your project’s database, tick the table you need and click “Load”.
4. The table lands on a new sheet. If a column title with an accent or an ñ looks wrong, right-click the query → “Edit”, rename the column and choose “Close & Load”. Save the workbook: the connection is saved inside it.

If your connection is not in the list, choose “(None)”, open “Advanced options” and type DSN= followed by the name of your connection, for example DSN=Teramot.

Refresh

Each refresh in your app reads the tables as they are at that moment; in Excel, Data → Refresh All. If the browser opens and asks you to sign in, that is expected: the session renews every so often. To keep the data as it is today, save or export a copy from your app: the connection always reads the current data.

Example: a fixed copy in Excel

1. Refresh the original workbook and check the data is what you want to keep.
2. File → Save As, and save a copy in another folder. From then on, what you have open is the copy.
3. Click any cell of the table, then Table Design → “Convert to Range”, accept the warning and save.

The copy is now fixed: Refresh All no longer changes it, and the original stays connected. Keep that order: converting before saving the copy cuts the original’s connection instead.

If something goes wrong