Querying Amazon Athena from Oracle Database via ODBC with Heterogeneous Services

This guide walks through how to query Amazon Athena directly from an Oracle database using Oracle Heterogeneous Services (DG4ODBC) and the Simba Athena ODBC driver. This pattern enables Oracle-based applications to access data stored in Amazon S3 via Athena without requiring application code changes — queries execute through standard Oracle database links.

Tony Mullen

Published Jun 17, 2026

Last modified Jun 17, 2026

Overview

This approach is useful for customers who:

Have an existing Oracle environment (on-premises or on EC2) and want to query an AWS data lake
Need to join Oracle relational data with Athena-accessible datasets
Want to avoid building and maintaining a separate ETL pipeline for read-only access to S3 data

Architecture

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
┌─────────────────────┐        ┌──────────────────────┐
│  Oracle Database    │        │  Amazon Athena       │
│  (EC2 / On-Prem)   │        │                      │
│                     │        │  ┌────────────────┐  │
│  SQL Query          │        │  │  Query Engine  │  │
│    ↓                │        │  └────────────────┘  │
│  Database Link      │        │         ↓            │
│    ↓                │  ODBC  │  ┌────────────────┐  │
│  HS Gateway ────────┼────────┼──│  Simba Driver  │  │
│  (DG4ODBC)          │  TCP   │  └────────────────┘  │
│                     │  443   │         ↓            │
└─────────────────────┘        │  ┌────────────────┐  │
                               │  │  Amazon S3     │  │
                               │  │  (Data Lake)   │  │
                               │  └────────────────┘  │
                               └──────────────────────┘

Prerequisites

Component	Requirement
Oracle Database	12c or later (19c recommended)
Simba Athena ODBC Driver	Version 2.0.35 or later
unixODBC	Installed on the Oracle host
Network	Connectivity from Oracle host to Athena endpoint (port 443)
IAM Permissions	`athena:StartQueryExecution`, `athena:GetQueryResults`, `s3:GetObject`, `s3:PutObject`, `glue:GetTable`
S3 Bucket	For Athena query results output

Step 1: Install the Simba Athena ODBC Driver

Download the Simba Athena ODBC driver from the Amazon Athena documentation and install:

1
2
3
4
5
6
7
8
# Download the driver RPM (Amazon Linux 2 / RHEL)
sudo yum install -y unixODBC unixODBC-devel

# Install the Simba driver
sudo rpm -ivh SimbaAthenaODBC-<version>.x86_64.rpm

# Verify installation
ls /opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so

Step 2: Configure the ODBC Data Source (odbc.ini)

Create or edit /etc/odbc.ini (or ~/.odbc.ini for the Oracle OS user):

1
2
3
4
5
6
7
8
[Athena_DSN]
Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so
AuthenticationType=IAM Credentials
S3OutputLocation=s3://your-query-results-bucket/output/
AwsRegion=eu-west-1
Schema=your_database_name
Workgroup=primary
UseResultsetStreaming=0

Note on UseResultsetStreaming=0: Result set streaming over VPC Private Endpoints requires additional configuration (see Streaming Configuration below). Disabling streaming is a valid production configuration — the performance impact is minimal for Oracle HS use cases as Oracle fetches rows through the gateway in small batches regardless.

Step 3: Configure Oracle Heterogeneous Services

3a. Create the HS Initialisation File

Create $ORACLE_HOME/hs/admin/initathena_hs.ora:

1
2
3
4
5
6
HS_FDS_CONNECT_INFO = Athena_DSN
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# Uncomment for debugging:
# HS_FDS_TRACE_LEVEL = DEBUG
# HS_FDS_TRACE_FILE_NAME = /tmp/athena_hs.trc

3b. Configure the Oracle Listener

Add the following SID_DESC entry to $ORACLE_HOME/network/admin/listener.ora:

1
2
3
4
5
6
7
8
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = athena_hs)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

Reload the listener:

1
lsnrctl reload

3c. Configure TNS Names

Add the following to $ORACLE_HOME/network/admin/tnsnames.ora:

1
2
3
4
5
6
ATHENA_HS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SID = athena_hs))
    (HS = OK)
  )

Important: The (HS = OK) clause tells Oracle this is a Heterogeneous Services connection.

Step 4: Create the Database Link

Oracle Heterogeneous Services does not read credentials from odbc.ini. It passes credentials from the database link definition to the ODBC driver. You have three authentication options:

Option A: IAM Access Key in the Database Link (Simplest)

1
2
3
4
CREATE DATABASE LINK athena_link
  CONNECT TO "<your-iam-access-key-id>"
  IDENTIFIED BY "<your-iam-secret-access-key>"
  USING 'athena_hs';

Important: Double quotes are required. The Access Key ID and Secret Key are case-sensitive and may contain characters (such as / and +) that Oracle would otherwise reject or alter.

Option B: EC2 Instance Profile (Recommended for EC2)

If your Oracle host is an EC2 instance with an IAM role attached:

Set AuthenticationType=Instance Profile in odbc.ini
Create the database link with placeholder credentials:

1
2
3
CREATE DATABASE LINK athena_link
  CONNECT TO "/" IDENTIFIED BY "dummy"
  USING 'athena_hs';

The Simba driver retrieves temporary credentials from the EC2 instance metadata service automatically. No keys are stored anywhere.

Option C: Environment Variables in the HS Gateway

Set AWS credentials in the HS init file so they are available to the gateway process:

1
2
3
4
# In initathena_hs.ora:
set AWS_ACCESS_KEY_ID=<your-access-key-id>
set AWS_SECRET_ACCESS_KEY=<your-secret-access-key>
set AWS_DEFAULT_REGION=eu-west-1

The Simba driver reads these environment variables when AuthenticationType=IAM Credentials is set in the DSN.

Step 5: Validate the Connection

Test ODBC Connectivity (outside Oracle)

1
isql -v Athena_DSN "<your-access-key-id>" "<your-secret-access-key>"

If this succeeds, the DSN and network are correct. Any subsequent failures are isolated to the Oracle HS credential-passing mechanism.

Test via Database Link

1
2
3
4
5
6
7
8
9
-- Simple count query
SELECT count(*)
FROM your_schema.your_table@athena_link;

-- Joining Oracle data with Athena data
SELECT o.customer_id, a.event_count
FROM local_customers o
JOIN analytics.customer_events@athena_link a
  ON o.customer_id = a.customer_id;

Troubleshooting

MissingAuthenticationTokenException

Symptom:

1
2
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Simba][Athena] (1040) MissingAuthenticationTokenException

Cause: Oracle HS is passing the database link credentials (or the connecting user's credentials) instead of IAM keys.

Fix: Ensure the CONNECT TO clause in your database link contains valid IAM credentials (Option A), or switch to Instance Profile authentication (Option B).

HS Gateway Tracing

Enable debug tracing to see exactly what the gateway passes to the driver:

1
2
3
# In initathena_hs.ora:
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_NAME = /tmp/athena_hs.trc

Re-run the query, then inspect /tmp/athena_hs.trc for the connection parameters.

Remember to disable tracing in production — trace files grow rapidly.

Optional: Enable Result Set Streaming

If you need streaming for large result sets over VPC PrivateLink:

#	Requirement	Details
1	Streaming VPC Endpoint	Verify the endpoint resolves the streaming hostname to a private IP
2	Security Group	Allow inbound TCP 444 from the Oracle host
3	DNS Resolution	`nslookup athena.<region>.amazonaws.com` should return a private IP
4	Driver Version	Simba ODBC 2.0.35 or later
5	Explicit Endpoint	Configure `StreamingEndpointOverride` in the DSN

1
2
3
4
5
[Athena_DSN]
# ... existing settings ...
UseResultsetStreaming=1
StreamingEndpointOverride=vpce-xxxxxxxxx.athena.<region>.vpce.amazonaws.com
StreamingEndpointPort=444

Acceptable Workaround: If streaming cannot be enabled, UseResultsetStreaming=0 is a valid production configuration. The impact is slightly higher latency on large result sets, but for queries executed via Oracle HS database links this is generally acceptable.

Security Considerations

Prefer Instance Profile authentication (Option B) when running on EC2 — this eliminates static credentials entirely
Never store IAM keys in version control
Use IAM policies with least-privilege: scope permissions to specific S3 buckets, Athena workgroups, and Glue databases
Consider using AWS Secrets Manager with credential rotation for Option A/C deployments
Restrict the Oracle HS init file permissions: chmod 600 initathena_hs.ora

Summary

Configuration File	Purpose
`/etc/odbc.ini`	ODBC DSN — driver path, region, S3 output, authentication type
`$ORACLE_HOME/hs/admin/initathena_hs.ora`	HS gateway — maps to DSN, optional env vars
`$ORACLE_HOME/network/admin/listener.ora`	Listener — registers the HS SID
`$ORACLE_HOME/network/admin/tnsnames.ora`	TNS — client-side service name with `(HS=OK)`
Database Link (SQL)	Credentials and HS service reference

This pattern enables Oracle applications to transparently query data in Amazon S3 through Athena without modification — opening the door to hybrid analytics across relational and data lake environments.

This document is provided for technical guidance purposes. Configuration values shown are examples and must be replaced with actual environment-specific values.

Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.

Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article

Builder Center

Querying Amazon Athena from Oracle Database via ODBC with Heterogeneous Services

Tony Mullen

Overview

Architecture

Prerequisites

Step 1: Install the Simba Athena ODBC Driver

Step 2: Configure the ODBC Data Source (odbc.ini)

Step 3: Configure Oracle Heterogeneous Services

3a. Create the HS Initialisation File

3b. Configure the Oracle Listener

3c. Configure TNS Names

Step 4: Create the Database Link

Option A: IAM Access Key in the Database Link (Simplest)

Option B: EC2 Instance Profile (Recommended for EC2)

Option C: Environment Variables in the HS Gateway

Step 5: Validate the Connection

Test ODBC Connectivity (outside Oracle)

Test via Database Link

Troubleshooting

MissingAuthenticationTokenException

HS Gateway Tracing

Optional: Enable Result Set Streaming

Security Considerations

Summary