How to Connect to Redshift via JDBC
There are a couple different drivers that can be used to connect to Amazon's Redshift database that runs on the AWS platform. Since Redshift is a PostgreSQL variant, the PostgreSQL JDBC driver can be used. Amazon also provides a Redshift specific JDBC driver. If connecting using any AWS / Redshift specific features like logging in with AWS security credentials, using temporary security tokens, etc. is required, the Redshift driver should be used. If the user is accessing Redshift in a more vanilla fashion, the PostgreSQL driver can be used. This article will assume the use of the Redshift driver instead of the PostgreSQL driver. See the links at the bottom of the page for how to connect using the PostgreSQL driver.
NOTE:Please note that if you are accessing Redshift over the internet, the appropriate firewall configuration on the AWS side will need to be completed to allow your machine to make a connection to the database.
Links to download the official Redshift JDBC driver can be found on the following page:
https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html
To connect to Redshift via JDBC, the class name of the driver is required. The class name will change depending on which version of the JDBC spec is used. In this example, we assume the 4.2 version of the spec will be used.
com.amazon.redshift.jdbc42.Driver
In addition to the class name, a JDBC URL needs constructed to connect to an AWS Redshift database. Listed below are examples of JDBC URLs to use with the Redshift JDBC driver.
Redshift Driver JDBC URL Formats
Connect to a Redshift database named sample using a standard login and password (Not IAM credentials):
jdbc:redshift://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?ssl=true&tcpKeepAlive=true
Connect to a Redshift database named sample using a standard login and password with SSL encryption enabled and TCP keep alive turned on:
jdbc:redshift://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?ssl=true&tcpKeepAlive=true
Connect to a Redshift database using IAM Access Key and Secret Key credentials. When connecting in this manner, the DbUser value needs passed in. This tells Redshift what DB User to model the access after:
jdbc:redshift:iam://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?AccessKeyID=ABC&SecretAccessKey=EFG&DbUser=admin&ssl=true&tcpKeepAlive=true
Connect to a Redshift database using IAM Access Key, Secret Key, and Session Token:
jdbc:redshift:iam://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?AccessKeyID=ABC&SecretAccessKey=EFG&DbUser=admin&SessionToken=HIJK
Sample Code for Making a JDBC Connection
Below is sample Java code for using the Redshift JDBC driver to make a connection to the database.
Class dbDriver = Class.forName("com.amazon.redshift.jdbc42.Driver");
String jdbcURL = "jdbc:redshift:iam://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?AccessKeyID=ABC&SecretAccessKey=EFG&DbUser=admin&ssl=true&tcpKeepAlive=true";
Connection connection = DriverManager.getConnection(jdbcURL);
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select * from employee");
while(rs.next())
{
System.out.println("name = " + rs.getString("name"));
System.out.println("id = " + rs.getInt("id"));
}
.
.
.
Additional JDBC Connection Articles for Other Databases
MySQL: How to Connect to MySQL via JDBC
Microsoft SQL Server: How to Connect to MS SQL Server via JDBC
Oracle: How to Connect to Oracle via JDBC
PostgreSQL: How to Connect to PostgreSQL via JDBC
SQLite: How to Connect to SQLite via JDBC
