Integrating the CData JDBC Driver for Amazon Athena with Ignition SCADA

Ignition is an industrial supervisory control and data acquisition (SCADA) and industrial Internet of Things (IIoT) platform from Inductive Automation. Its central Gateway service manages device connections, tags, and database connections. Ignition doesn't ship with a connector for Amazon Athena, so reaching Amazon Athena data usually means custom middleware or manual exports.

The CData JDBC Driver for Amazon Athena bridges this gap by exposing Amazon Athena as a standard Java Database Connectivity (JDBC) data source. Ignition treats any JDBC driver as a standard database connection. Once the driver is registered in the Gateway, you can query live Amazon Athena data from the Script Console, SQL Bridge, tags, or a Perspective or Vision screen, without replication, custom connectors, or extract, transform, load (ETL) pipelines.

This guide outlines the steps to install and commission Ignition, register the CData JDBC Driver for Amazon Athena, create the connection, and verify it with a live query.

Prerequisites

• Ignition for Windows (Windows 10/11 or Windows Server), with administrator rights to install a Windows service
• CData JDBC Driver for Amazon Athena
• A Amazon Athena account with valid credentials and application programming interface (API) access enabled
• A supported desktop browser (Google Chrome, Mozilla Firefox, Microsoft Edge, or Safari) to open the Gateway web interface

Getting Started

---

Step 1: Install and set up Ignition

If Ignition is already installed and running, skip to Step 2.

Download Ignition

1. Go to the Inductive Automation downloads page and download Ignition for Windows.
2. This single download is the full Ignition Gateway installer. It includes both the Gateway service and the Designer Launcher, so you don't need a separate Designer download.

Install Ignition

1. Run the installer, accept the license agreement, and choose an install location.
2. Leave Install as a Windows Service checked so the Gateway starts with Windows.
3. Finish the installer. The Gateway service starts, and a browser window opens to http://localhost:8088 and shows the Commissioning wizard. If the page shows a Browser Not Supported notice, open the same address in Chrome, Firefox, Edge, or Safari.

Commission the Gateway

1. In the Commission Ignition Gateway wizard, set a Gateway Name and create an administrator username and password.
2. Accept the End User License Agreement, then start a trial or activate a purchased Ignition license.
3. When commissioning finishes, log in with the administrator account. You land on the Gateway Home page.

Note: A trial shows a countdown timer on every Gateway page, and the timer resets when the Gateway service restarts. This is fine for testing, but production use requires a license.

Step 2: Register the CData JDBC Driver in Ignition

Ignition lets you upload any third-party JDBC driver through the Gateway web interface, without touching the file system directly.

1. Log in to the Gateway as an administrator.
2. Go to Connections > Databases > Settings, open the Drivers tab, and click Create JDBC Driver.
3. In the dialog, enter the following:
   • Name: Amazon Athena
   • Classname: cdata.jdbc.amazonathena.AmazonAthenaDriver
   • Driver Type: Generic
   • Default Validation Query: SELECT Name FROM Customers LIMIT 1
   
4. Click Create JDBC Driver. In current Ignition 8.3 releases, the create dialog doesn't include a Java archive (JAR) file upload field, so you add the JAR file in the next step.
5. Open the menu at the right of the new driver row, choose Manage JAR files, upload cdata.jdbc.amazonathena.jar from the lib folder of the driver installation, and save.
6. Confirm the driver shows an INSTALLED status in the drivers list.

Step 3: Create the Amazon Athena connection

1. Go to Connections > Databases > Connections and click Create Database Connection.
2. On the Driver Selection step, select Amazon Athena and click Next.
3. On the Database Setup step, set the Name to Amazon Athena. Leave Username empty and select None for Password, because the credentials go in the connect URL.
4. In Connect URL, enter the connection string:

   jdbc:amazonathena:AWSAccessKey='a123';AWSSecretKey='s123';AWSRegion='IRELAND';Database='sampledb';S3StagingDirectory='s3://bucket/staging/';

   
5. Click Create Database Connection and confirm the connection shows a VALID status on the Database Connections page.

Connection properties for Amazon Athena

Authenticating to Amazon Athena

To authorize Amazon Athena requests, provide the credentials for an administrator account or for an IAM user with custom permissions: Set AccessKey to the access key Id. Set SecretKey to the secret access key.

Note: Though you can connect as the AWS account administrator, it is recommended to use IAM user credentials to access AWS services.

Obtaining the Access Key

To obtain the credentials for an IAM user, follow the steps below:

1. Sign into the IAM console.
2. In the navigation pane, select Users.
3. To create or manage the access keys for a user, select the user and then select the Security Credentials tab.

To obtain the credentials for your AWS root account, follow the steps below:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

Authenticating from an EC2 Instance

If you are using the CData Data Provider for Amazon Athena 2018 from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set UseEC2Roles to true and leave AccessKey and SecretKey empty. The CData Data Provider for Amazon Athena 2018 will automatically obtain your IAM Role credentials and authenticate with them.

Authenticating as an AWS Role

In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user. An AWS role may be used instead by specifying the RoleARN. This will cause the CData Data Provider for Amazon Athena 2018 to attempt to retrieve credentials for the specified role. If you are connecting to AWS (instead of already being connected such as on an EC2 instance), you must additionally specify the AccessKey and SecretKey of an IAM user to assume the role for. Roles may not be used when specifying the AccessKey and SecretKey of an AWS root user.

Authenticating with MFA

For users and roles that require Multi-factor Authentication, specify the MFASerialNumber and MFAToken connection properties. This will cause the CData Data Provider for Amazon Athena 2018 to submit the MFA credentials in a request to retrieve temporary authentication credentials. Note that the duration of the temporary credentials may be controlled via the TemporaryTokenDuration (default 3600 seconds).

Connecting to Amazon Athena

In addition to the AccessKey and SecretKey properties, specify Database, S3StagingDirectory and Region. Set Region to the region where your Amazon Athena data is hosted. Set S3StagingDirectory to a folder in S3 where you would like to store the results of queries.

If Database is not set in the connection, the data provider connects to the default database set in Amazon Athena.

Note: If a password or token contains a semicolon, the connect URL breaks, so reset it to a value without one.

Step 4: Query live Amazon Athena data from Ignition

With the connection valid, you can query live Amazon Athena data from the Designer.

1. Open the Ignition Designer Launcher, select your Gateway (click Add Designer and enter http://localhost:8088 if it isn't listed), and click Open Designer. Log in with the Gateway administrator account.
2. Open or create a project. The Designer opens with the Project Browser, Tag Browser, and Component Palette panels.
3. In the Designer, go to Tools > Script Console. In the Multiline Buffer, enter a test query and click Execute. Replace Customers with any table available in your Amazon Athena connection:

   print system.db.runQuery(
       "SELECT Name, TotalDue FROM Customers LIMIT 10",
       "Amazon Athena")

   
4. A successful query prints PyDataset rows:10 cols:2 in the Interactive Interpreter panel, which confirms Ignition retrieved live data from Amazon Athena through the CData JDBC Driver.

Note: The connection name in the script must match the name you gave the connection in Step 3.

Step 5: Verify the results

1. Print the rows and compare the values with the same records in Amazon Athena:

   ds = system.db.runQuery("SELECT Name, TotalDue FROM Customers LIMIT 10", "Amazon Athena")
   for row in ds:
       print row["Name"], row["TotalDue"]

2. Optionally, run a second query against another table and a filtered query to confirm schema discovery and filtering.
3. Optionally, restart the Gateway service and confirm the connection returns to VALID.

Troubleshooting

Test the driver outside Ignition

To confirm the driver works independently of the Gateway, run this standalone Java example. The cdata.jdbc.amazonathena.jar and cdata.jdbc.amazonathena.lic files must be in the same directory and on the classpath.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CDataExample {
    public static void main(String[] args) throws Exception {
        Class.forName("cdata.jdbc.amazonathena.AmazonAthenaDriver");
        String connectionString = "jdbc:amazonathena:AWSAccessKey='a123';AWSSecretKey='s123';AWSRegion='IRELAND';Database='sampledb';S3StagingDirectory='s3://bucket/staging/';";
        try (Connection conn = DriverManager.getConnection(connectionString)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT Name, TotalDue FROM Customers LIMIT 10");
            while (rs.next()) {
                System.out.println(rs.getString("Name") + ", " + rs.getString("TotalDue"));
            }
        }
    }
}

---

Build live, governed Amazon Athena connectivity in Ignition with CData

With the CData JDBC Driver for Amazon Athena registered, your team can query live Amazon Athena data from Ignition without ETL infrastructure, manual exports, or custom integration code, and reuse the same connection in tags, SQL Bridge, and Perspective or Vision screens.

Start your free trial today to see how you can give Ignition live, secure access to hundreds of external systems with CData.