/amazon-timestream-driver-jdbc

Primary LanguageJavaApache License 2.0Apache-2.0

Amazon Timestream Driver for Java

This package provides a JDBC driver to Amazon Timestream for Java.

User Documentation

Prerequisites

  1. Sign up for AWS — Before beginning, have an AWS account. For more information about creating an AWS account and retrieving your AWS credentials, see Sign Up for AWS and Create an IAM User in the AWS SDK for Java Developer Guide.
  2. Minimum requirements — To use the Amazon Timestream JDBC driver for Java, Java 1.8+ is required.

Using the Timestream JDBC driver

The Timestream JDBC driver is distributed as two JAR files.

JAR With All Dependencies Shaded Except SLF4J (amazon-timestream-jdbc-<version>-jar-with-all-dependencies.jar)

This is the shaded JAR which bundles up all the required dependencies and can be used directly in Java projects and external applications, without explicitly adding external dependencies. This JAR does not relocate the classpath of SLF4J, allowing logging to be enabled in external applications.

Driver Setup in BI Applications

The JDBC driver is compatible with a number of BI tools. Instructions are outlined here for:

For other BI tools, please refer to that tool's product documentation

JAR With No Dependencies (amazon-timestream-jdbc-<version>.jar)

This is the lightweight JAR which does not include any dependencies. This JAR could be used if the application developer does not want duplicate dependencies and want full control over their dependency tree.

Driver Class

Some database connection tools require specifying the class name. The driver class for Timestream JDBC driver is:

software.amazon.timestream.jdbc.TimestreamDriver

Creating a Connection

To create a connection, construct a connection URL by appending optional connection properties to the default Timestream URL jdbc:timestream. The URL could be directly constructed, or a Properties object could be used in a Java application to do so.

The default URL for Timestream is jdbc:timestream.

Constructing the Connection URL

Timestream supports optional connection properties that could be specified through the JDBC URL. To specify these optional properties use the following URL format: jdbc:timestream://PropertyName1=value1;PropertyName2=value2.... Note the property name is case sensitive.

An example of the JDBC URL with properties: jdbc:timestream://AccessKeyId=myAccessKeyId;SecretAccessKey=mySecretAccessKey;SessionToken=mySessionToken;Region=myRegion

Using a Properties Object to Specify Connection Properties

A Properties object could be used to construct a connection URL in a Java application.

Properties prop = new Properties();
prop.put("AccessKeyId", "myAccessKeyId");
prop.put("SecretAccessKey", "mySecretAccessKey");
prop.put("SessionToken", "mySessionToken");
prop.put("Region", "myRegion");

DriverManager.getConnection(URL, prop);

If the same connection properties are specified in both the URL and the Properties instance, the connection property in the URL will take precedence. For instance, in the following example "foo" will be used during authentication:

final String url = "jdbc:timestream://AccessKeyId=foo";
final Properties prop = new Properties();
prop.put("AccessKeyId", "userName");
 
DriverManager.getConnection(url, prop);

Optional Connection Properties

NOTE: All property keys are case-sensitive.

All the connection properties are optional. If none of these properties are provided in the url jdbc:timestream, the Timestream JDBC driver will use the AWS default credentials chain to load the credentials.

Standard Client Info Option

Option Description Default
ApplicationName The name of the application currently utilizing the connection. This name is used for users' own debugging purposes and will not be communicated to the Timestream service. The application name detected by the driver.

Driver Configuration Option

Option Description Default
EnableMetaDataPreparedStatement Enables Timestream JDBC driver to return metadata for PreparedStatements, but this will incur an additional cost with Timestream when retrieving the metadata. false

SDK Options

Option Description Default
RequestTimeout The time in milliseconds the AWS SDK will wait for a query request before timing out. Non-positive value disables request timeout. 0
SocketTimeout The time in milliseconds the AWS SDK will wait for data to be transferred over an open connection before timing out. Value must be non-negative. A value of 0 disables socket timeout. 50000
MaxRetryCountClient The maximum number of retry attempts for retryable errors with 5XX error codes in the SDK. The value must be non-negative. NONE
MaxConnections The maximum number of allowed concurrently opened HTTP connections to the Timestream service. The value must be positive. 50

Endpoint Configuration Options

Option Description Default
Endpoint The endpoint for the Timestream service. NONE
Region The signing region for the Timestream service endpoint. The signing region can be specified without an endpoint, but must be specified if a custom endpoint is given. NONE

Basic Authentication Options

Option Description Default
AccessKeyId The AWS user access key id. NONE
SecretAccessKey The AWS user secret access key. NONE
SessionToken The temporary session token required to access a database with multi-factor authentication (MFA) enabled. NONE

Credentials Provider Options

Option Description Default
AwsCredentialsProviderClass One of PropertiesFileCredentialsProvider or InstanceProfileCredentialsProvider to use for authentication. NONE
CustomCredentialsFilePath The path to a properties file containing AWS security credentials accessKey andsecretKey. This is only required if AwsCredentialsProviderClass is specified as PropertiesFileCredentialsProvider. NONE

SAML-Based Authentication Options for Okta

Option Description Default
IdpName The Identity Provider (IdP) name to use for SAML-based authentication. One of Okta or AzureAD. NONE
IdpHost The hostname of the specified IdP. NONE
IdpUserName The user name for the specified IdP account. NONE
IdpPassword The password for the specified IdP account. NONE
OktaApplicationID The unique Okta-provided ID associated with the Timestream application. A place to find the AppId is in the entityID field provided in the application metadata. An example entityID="http://www.okta.com//<IdpAppID>" NONE
RoleARN The Amazon Resource Name (ARN) of the role that the caller is assuming. NONE
IdpARN The Amazon Resource Name (ARN) of the SAML provider in IAM that describes the IdP. NONE

SAML-Based Authentication Options for Azure Active Directory

Option Description Default
IdpName The Identity Provider (IdP) name to use for SAML-based authentication. One of Okta or AzureAD NONE
IdpUserName The user name for the specified IdP account. NONE
IdpPassword The password for the specified IdP account. NONE
AADApplicationID The unique id of the registered application on Azure AD. NONE
AADClientSecret The client secret associated with the registered application on Azure AD used to authorize fetching tokens. NONE
AADTenant The Azure AD Tenant ID. NONE
IdpARN The Amazon Resource Name (ARN) of the SAML provider in IAM that describes the IdP. NONE

SAML-Based Authentication Configuration

Timestream supports SAML-based authentication using both Okta and Azure Active Directory.

Configure SAML 2.0 for AWS with Okta.

Prerequisites

  1. Admin permissions in AWS to create the identity provider and the roles.

  2. An Okta account: https://www.okta.com/login/.

  3. Access to Timestream.

  4. Creating the AWS Account Federation application on Okta.

  5. Set up SAML for Okta.

  6. Configure Timestream JDBC driver to use Okta to access Timestream.

Creating AWS Account Federation on Okta

  1. Sign in to the Okta Admin dashboard: https://<company domain name>-admin.okta.com/admin/apps/active.

  2. Select Add Application and search for AWS Account Federation.

  3. Click Add.

  4. Change the Login URL to the appropriate URL, e.g. https://console.aws.amazon.com/console/home?region=us-east-1.

  5. Click Next.

  6. Select SAML 2.0 As the Sign-On method.

  7. Click Identity Provider metadata to open the metadata XML file. Save the file locally, this is required later when creating the Okta identity provider.

  8. Leave the other configuration as blank for now.

  9. Click Done.

Setting up Okta for SAML

Select the Sign On tab. Click the View Setup Instructions button in the Settings section to set up SAML with Okta.

Alternatively use the following link to view the setup instruction:

https://saml-doc.okta.com/SAML_Docs/How-to-Configure-SAML-2.0-for-Amazon-Web-Service.html

The difference between these two links is that the latter link does not provide the link to the Metadata Document required when creating the Okta IdP.

This document could be found at:

  1. https://<company domain name>-admin.okta.com/admin/apps/active.

  2. Select the AWS Account Federation application.

  3. Select the Sign On tab.

  4. Click on the Identity Provider metadata hyperlink under the View Setup Instruction button to be redirected to the XML file.

Configure SAML 2.0 for AWS with Azure Active Directory.

Prerequisites

  1. Admin permissions in AWS to create the identity provider and the roles.

  2. An Azure Active Directory account: https://azure.microsoft.com/en-ca/services/active-directory/

  3. Access to Timestream.

  4. Setting up the Azure enterprise non-gallery application using single sign-on (SSO) with SAML.

  5. Setting up the IAM provider and roles:

    • Creating the SAML identity provider.

    • Creating an IAM role for access to Timestream.

    • Creating an IAM policy allowing the IAM role to generate an AWS STS token to connect to Timestream.

  6. Start provisioning for Azure AD access to the newly created IAM IdP and Roles.

  7. Configure Timestream JDBC driver to use Azure AD to access Timestream.

Setting up Azure AD

  1. Sign in to Azure Portal.

  2. Select Azure Active Directory in the list of Azure services, this will redirect to the Default Directory page.

  3. Select Enterprise Applications under the Manage section on the sidebar:

  4. Select + New application

  5. Find and select Amazon Web Services

  6. Select Single sign-on under Manage section in the sidebar:

  7. Choose SAML as the single sign-on method.

  8. In the Basic SAML Configuration, enter https://signin.aws.amazon.com/saml for both the Identifier and the Reply URL:

  9. Click Save.

  10. Download the Federation Metadata XML in the SAML Signing Certificate section. This will be used when creating the IAM Identity Provider later.

  11. Go back to the Default Directory page, select App registrations under Manage.

  12. Select Timestream from the All Applications section, the page will be redirected to the application’s Overview page.

  13. Take notes of the Application (client) ID and the Directory (tenant) ID, these values are required for when creating a connection:

  14. Select Certificates & secrets

  15. Under Client secrets, create a new client secret with + New client secret. Take note of the generated value, this is required when creating a connection to Timestream.

  16. On the sidebar under Manage, select API permissions.

  17. In the Configured permissions, use Add a permission to grant Azure AD permission to sign in to Timestream. Select Microsoft Graph on the Request API permissions page.

  18. Select Delegated permissions, select the User.Read permission.

    Click Add permissions.

  19. Click Grant admin consent for Default Directory.

Azure AD set up process is now complete.

Setting up IAM Identity Provider and Roles in AWS

This set up goes through the following:

  1. Creating the SAML identity provider.

  2. Creating an IAM role for access to Timestream.

  3. Creating an IAM policy allowing the IAM role to generate an AWS STS token to connect to Timestream.

Create a SAML Identity Provider

  1. Sign in to the AWS Management Console.

  2. Select Services and select IAM under Security, Identity, & Compliance.

  3. Select Identity providers under Access management.

  4. Select Create Provider and choose SAML as the provider type. Enter the Provider Name, this example will use AzureADProvider.

  5. Upload the previously downloaded Federation Metadata XML file.

  6. Select Next then Create.

Upon completion, the page will be redirected back to the Identity providers page.

Create an IAM role

  1. On the sidebar select Roles under Access management.

  2. Select Create role.

  3. Choose SAML 2.0 federation as the trusted entity.

  4. Choose the Azure AD provider.

  5. Select Allow programmatic and AWS Management Console access.

  6. Click Next: Permissions

  7. Attach permissions policies or continue to Next:Tags.

  8. Add optional tags or continue to Next:Review

  9. Enter a Role name, this example will use AzureSAMLRole.

  10. Provide a role description.

  11. Click Create Role to complete.

Create an IAM Policy

  1. On the sidebar select Policies under Access management.

  2. Select Create policy and select the JSON tab.

  3. Add the following policy:

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "iam:ListRoles",
                "iam:ListAccountAliases"
            ],
            "Resource": "*"
        }
    ]
}

  4. Select Create policy.

  5. Enter a policy name, this example will use TimestreamAccessPolicy

  6. Select Create Policy.

  7. On the sidebar select Roles under Access management. Select the previously created Azure AD role, select Attach policies under Permissions.

  8. Select the previously created access policy.

Provisioning

  1. Go back to Azure Portal.

  2. Select Azure Active Directory in the list of Azure services, this will redirect to the Default Directory page.

  3. Select Enterprise Applications under the Manage section on the sidebar.

  4. Select Provisioning

  5. Select Automatic mode for the Provisioning Method.

  6. Under Admin Credentials, enter AwsAccessKeyID for clientsecret, and SecretAccessKey for Secret Token.

  7. Set the Provisioning Statusto On.

  8. Click save. This allows Azure AD to load the necessary IAM Roles.

  9. Once the Current cycle status is completed, select Users and groups on the sidebar.

  10. Select + Add user.

  11. Select the Azure AD user to provide access to Timestream.

  12. Select the IAM Azure AD role and the corresponding Azure Identity Provider created in AWS.

  13. Select Assign.

Developer Documentation

Building the driver

Building the fully shaded JAR With All Dependencies Shaded Except SLF4J (amazon-timestream-jdbc-<version>-jar-with-all-dependencies.jar)

To build a fully shaded JAR, comment out the following two lines in the pom file in the /jdbc/pom.xml then run mvn install.

<scope>system</scope>
<systemPath>${project.basedir}/resources/aws-sdk-java-timestreamquery-1.11.805-SNAPSHOT.jar</systemPath>

Since the Timestream Query SDK is not yet available on Maven Repository, these two lines are included to ensure that Github Actions can build the project.

Building and using the JAR with no dependencies (amazon-timestream-jdbc-<version>.jar)

To build the JAR with no dependencies, run mvn install.

To use the JAR with no dependencies in a Java application, the following required dependencies need to be added inside the pom file of your project.

<dependencies>
    <dependency>
        <groupId>com.amazonaws</groupId>
        <artifactId>aws-java-sdk-core</artifactId>
        <version>1.11.804</version>
    </dependency>
    <dependency>
        <groupId>com.amazonaws</groupId>
        <artifactId>aws-java-sdk-sts</artifactId>
        <version>1.11.804</version>
    </dependency>
      <dependency>
        <groupId>com.amazonaws</groupId>
        <artifactId>timestreamquery</artifactId>
        <version>1.11.805-SNAPSHOT</version>
      </dependency>
      <dependency>
        <groupId>com.amazonaws</groupId>
        <artifactId>timestreamwrite</artifactId>
        <version>1.11.805-SNAPSHOT</version>
      </dependency>
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>28.0-jre</version>
    </dependency>
    <dependency>
        <groupId>org.jsoup</groupId>
        <artifactId>jsoup</artifactId>
        <version>1.13.1</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>jul-to-slf4j</artifactId>
        <version>1.7.24</version>
    </dependency>
</dependencies>

Building and using the Javadoc JAR to extract the Javadoc HTML files (amazon-timestream-jdbc-<version>-javadoc.jar)

Javadoc JAR builds with mvn install alongside the other JAR files. To extract the Javadoc HTML files, use the following command: jar -xvf amazon-timestream-jdbc-1.0.0-javadoc.jar

Known Issues

  1. Timestream does not support fully qualified table names.
  2. Timestream does not support the queries that contain ":" in the column aliases. Tools like Tableau may not work as expected.

Caveats

  1. Timestream JDBC driver supports getSchemas and does not support getCatalogs, so Tableau will show database as schemas instead of catalogs.
  2. SQuirreL SQL Client does not support Rows and Arrays. Running a SELECT query that returns an Array or a Struct will result in UnknownType<2,003>/UnknownType<2,002>.
  3. SQLWorkbench/J does not support using Database Explorer to describe Timestream databases with an underscore in the name, e.g. grafana_db.
  4. SQLWorkbench/J does not display the array values within a java.sql.Struct, instead SQLWorkbench/J displays the array's base type, e.g.com.tsshaded.amazonaws.services.timestreamquery.model.Row(DOUBLE, 2) instead of ({1.3},2).

License

This library is licensed under the Apache 2.0 License.