Configuring a JDBC connection - Amazon Redshift

Configuring a JDBC connection

You can use a JDBC connection to connect to your Amazon Redshift cluster from many third-party SQL client tools. To do this, you download a JDBC driver.

If you want to use a JDBC connection, perform the following steps.

Download an Amazon Redshift JDBC driver

Amazon Redshift offers drivers for tools that are compatible with the JDBC 4.2 API. For information about the functionality supported by these drivers, see the Amazon Redshift JDBC driver release notes.

For detailed information about how to install the JDBC driver, reference the JDBC driver libraries, and register the driver class, see Amazon Redshift JDBC driver installation and configuration guide.

JDBC drivers version 1.2.27.1051 and later support Amazon Redshift stored procedures. For more information, see Creating stored procedures in Amazon Redshift in the Amazon Redshift Database Developer Guide.

For each computer where you use the Amazon Redshift JDBC driver, make sure that Java Runtime Environment (JRE) 8.0 is installed.

If you use the Amazon Redshift JDBC driver for database authentication, make sure that you have AWS SDK for Java 1.11.118 or later in your Java class path. If you don't have AWS SDK for Java installed, you can use a driver that includes the AWS SDK.

For more information about previous driver versions, see Use previous JDBC driver versions with the AWS SDK for Java.

Then download and review the Amazon Redshift ODBC and JDBC driver license agreement.

If your tool requires a specific previous version of a driver, see Use previous JDBC driver versions in certain cases.

Obtain the JDBC URL

Before you can connect to your Amazon Redshift cluster from a SQL client tool, you need to know the JDBC URL of your cluster. The JDBC URL has the following format: jdbc:redshift://endpoint:port/database.

Note

A JDBC URL specified with the former format of jdbc:postgresql://endpoint:port/database still works.

The fields of the format shown preceding have the following values.

Field Value
jdbc The protocol for the connection.
redshift The subprotocol that specifies to use the Amazon Redshift driver to connect to the database.
endpoint The endpoint of the Amazon Redshift cluster.
port The port number that you specified when you launched the cluster. If you have a firewall, make sure that this port is open for you to use.
database The database that you created for your cluster.

The following is an example JDBC URL: jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev

For information about how to get your JDBC connection, see Finding your cluster connection string.

If the client computer fails to connect to the database, you can troubleshoot possible issues. For more information, see Troubleshooting connection issues in Amazon Redshift.

Configure authentication and SSL for JDBC connection

Configure the Amazon Redshift JDBC driver to authenticate your connection according to the security requirements of the Amazon Redshift server that you are connecting to.

To authenticate the connection, always provide your Amazon Redshift user name and password. The password is transmitted using a salted MD5 hash of the password. Depending on whether SSL is enabled and required on the server, you might also need to configure the driver to connect through SSL. You might need to use one-way SSL authentication so that the client (the driver itself) verifies the identity of the server.

For information about configuring the JDBC driver to authenticate the connection, see Amazon Redshift JDBC driver installation and configuration guide.

Configure TCP keepalives for JDBC connection

By default, the Amazon Redshift JDBC driver is configured to use TCP keepalives to prevent connections from timing out. You can specify when the driver starts sending keepalive packets or disable the feature by setting the relevant properties in the connection URL.

For information about configuring TCP keepalives for the JDBC driver, see Amazon Redshift JDBC driver installation and configuration guide.

Configure logging for JDBC connection

To help troubleshoot issues, you can enable logging in the JDBC driver.

For information about configuring logging for JDBC connection, see Amazon Redshift JDBC driver installation and configuration guide.

Configure JDBC connection with Apache Maven

Apache Maven is a software project management and comprehension tool. The AWS SDK for Java supports Apache Maven projects. For more information, see Using the SDK with Apache Maven in the AWS SDK for Java Developer Guide.

If you use Apache Maven, you can configure and build your projects to use an Amazon Redshift JDBC driver to connect to your Amazon Redshift cluster. To do this, add the JDBC driver as a dependency in your project's pom.xml file. If you use Maven to build your project and want to use a JDBC connection, take the steps in the following section.

Configuring the JDBC driver as a Maven dependency

To configure the JDBC driver as a Maven dependency

  1. Add the following repository to the repositories section of your pom.xml file.

    Note

    The URL in the following code example returns an error if used in a browser. Use this URL only in the context of a Maven project.

    <repositories> <repository> <id>redshift</id> <url>http://redshift-maven-repository.s3-website-us-east-1.amazonaws.com/release</url> </repository> </repositories>

    To connect using SSL, add the following repository to your pom.xml file.

    <repositories> <repository> <id>redshift</id> <url>https://s3.amazonaws.com/redshift-maven-repository/release</url> </repository> </repositories>
  2. Declare the version of the driver that you want to use in the dependencies section of your pom.xml file.

    Amazon Redshift offers drivers for tools that are compatible with the JDBC 4.2 API. For information about the functionality supported by these drivers, see Download an Amazon Redshift JDBC driver.

    Add a dependency for the driver from the following list.

    Note

    For version 1.2.1.1001 and later, you can use either the generic driver class name com.amazon.redshift.jdbc.Driver or the version-specific class name listed with the driver in the list following, for example com.amazon.redshift.jdbc42.Driver. For releases before 1.2.1.1001, only version-specific class names are supported.

    Replace the driver-version in the following example with your driver version. For example, 1.2.45.1069.

    • JDBC 4.2–compatible driver:

      <dependency> <groupId>com.amazon.redshift</groupId> <artifactId>redshift-jdbc42</artifactId> <version>driver-version</version> </dependency>

      The class name for this driver is com.amazon.redshift.jdbc42.Driver.

  3. Download and review the Amazon Redshift ODBC and JDBC driver license agreement.

The standard Amazon Redshift JDBC drivers include the AWS SDK that is required to use IAM database authentication. We recommend using the standard drivers unless the size of the driver files is an issue for your application. If you need smaller driver files and you do not use IAM database authentication, or if you already have AWS SDK for Java 1.11. 118 or later in your Java class path, then add a dependency for the driver from the following list.

Replace the driver-version in the following example with your driver version. For example, 1.2.45.1069.

  • JDBC 4.2–compatible driver:

    <dependency> <groupId>com.amazon.redshift</groupId> <artifactId>redshift-jdbc42-no-awssdk</artifactId> <version>driver-version</version> </dependency>

    The class name for this driver is com.amazon.redshift.jdbc42.Driver.

The Amazon Redshift Maven drivers with no SDKs include the following optional dependencies that you can include in your project as needed.

<dependency> <groupId>com.amazonaws</groupId> <artifactId>aws-java-sdk-core</artifactId> <version>1.11.118</version> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>com.amazonaws</groupId> <artifactId>aws-java-sdk-redshift</artifactId> <version>1.11.118</version> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>com.amazonaws</groupId> <artifactId>aws-java-sdk-sts</artifactId> <version>1.11.118</version> <scope>runtime</scope> <optional>true</optional> </dependency>

If your tool requires a specific previous version of a driver, see Use previous JDBC driver versions with Maven.

Upgrading the driver to the latest version

To upgrade or change the Amazon Redshift JDBC driver to the latest version, first modify the version section of the dependency to the latest version of the driver. Then clean your project with the Maven Clean Plugin, as shown following.

mvn clean

Configure JDBC driver options

To control the behavior of the Amazon Redshift JDBC driver, you can append configuration options to the JDBC URL. For example, the following JDBC URL connects to your cluster using Secure Socket Layer (SSL), user (UID), and password (PWD).

jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev?ssl=true&UID=your_username&PWD=your_password

For more information about SSL options, see Connect using SSL.

For information about how to set up JDBC driver configuration options, see Amazon Redshift JDBC driver installation and configuration guide.

Use previous JDBC driver versions in certain cases

Download a previous version of the Amazon Redshift JDBC driver only if your tool requires a specific version of the driver. For information about the functionality supported in these versions of the drivers, see Download an Amazon Redshift JDBC driver.

For authentication using AWS Identity and Access Management (IAM) credentials or identity provider (IdP) credentials, use Amazon Redshift JDBC driver version 1.2.8.1005 or later.

Important

Amazon Redshift has changed the way that SSL certificates are managed. If you must use a driver version earlier than 1.2.8.1005, you might need to update your current trust root CA certificates to continue to connect to your clusters using SSL. For more information, see Transitioning to ACM certificates for SSL connections.

If you use the Amazon Redshift JDBC driver for database authentication, make sure that you have AWS SDK for Java 1.11.118 or later in your Java class path. If you don't have AWS SDK for Java installed, you can use a driver that includes the AWS SDK. For more information, see Use previous JDBC driver versions with the AWS SDK for Java.

These are JDBC 4.2–compatible drivers:

These are previous JDBC 4.1–compatible drivers:

These are previous JDBC 4.0–compatible drivers:

Use previous JDBC driver versions with the AWS SDK for Java

If you use the JDBC driver for database authentication, make sure that you have AWS SDK for Java 1.11.118 or later in your Java class path. If you don't have AWS SDK for Java installed, you can use one of the following drivers that include the AWS SDK.

Use previous JDBC driver versions with Maven

Add a previous version of the Amazon Redshift JDBC driver to your project only if your tool requires a specific version of the driver. For information about the functionality supported in these driver versions, see Download an Amazon Redshift JDBC driver. For information about configuring with Maven, see Configure JDBC connection with Apache Maven.