SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Connect to Amazon Redshift using DBeaver Database Management Tool


Since DBeaver is a free universal database management tool and it supports Amazon Redshift as a target database, Amazon Redshift datawarehouse developers and administrators can use DBeaver to connect to Amazon Redshift database and manage Redshift cluster objects and query data within DBeaver. In this tutorial, I want to show the steps to create a database connection from DBeaver database manager to Amazon Redshift cluster. Developers frequently experience connection problems to Redshift databases. I also tried to suggest a few actions on AWS for developers to check in case they fail to connect DBeaver and Redshift databases.

connect DBeaver to Amazon Redshift database

If you have not yet downloaded the free community edition of DBeaver database management tool, please read my recent tutorial on how to download and install DBeaver Database Manager to connect Exasol database. There you will find where to download free DBeaver Database tool and step by step installing it on your Mac.

After launching DBeaver database management tool, using top menu SQL developers can add additional databases into Database Navigator.
Since in this tutorial I will show how SQL developers can connect to an Amazon Redshift cluster, let's start following menu options on DBeaver software: Database > New Database Connection

create new database connection in DBeaver database manager

The first screen in database connection wizard is for selecting the target database. If you want to connect to Amazon Redshift database, you can find Redshift among Analytical databases.

connect Amazon Redshift database using DBeaver

When you select Redshift, you will see above the AWS Redshift JDBC driver is required and will be used for connecting to target database by DBeaver database manager. Click Next button for next step.

As seen below, the next screen in Connect to database wizard is the input screen where we provide all parameter for the Amazon Redshift connection.

Host is the public IP address or the JDBC endpoint of the Amazon Redshift cluster. All these information is available for each cluster on Amazon Redshift service dashboard.
Port for Amazon Redshift database connection is 5439 by default. Again the port number can be viewed at Redshift dashboard.
Database is the Redshift database which you want to connect using DBeaver. By default AWS suggests "dev" as the default database name while launching a new Amazon Redshift cluster.
User and Password are required for authenticating on Redshift database. You must provide a valid database user and password here.

DBeaver Amazon Redshift connection settings

Click on "Test connection" button. Since we are connecting to an Amazon Redshift cluster for the first time, the required driver for the Redshift database connection is missing. At this step, DBeaver database management tool will request your approval to download missing AWS Redshift driver.

download AWS Redshift driver files in DBeaver

Click Download button to download and install driver files to complete database connection to Amazon Redshift from DBeaver database manager.

After download is completed, the connection wizard will try to connect to Redshift database automatically.

Your database connection can fail. For example, below error is one of the most common problem experienced in database connections to Redshift databases.

Connection error
[Amazon](500150) Error setting/closing connection: SocketTimeoutException.

Connection error [Amazon](500150) Error setting/closing connection

In such a situation, SQL administrator and database developers can take a number of actions and checks for connection settings.

Public IP Address and EndPoint Address of Amazon Redshift cluster
First of all, if you used the Public IP address of the Amazon Redshift cluster, you can change the IP address with Redshift cluster end point address. Compare the Host connection parameter you have provided in previous steps with the endpoint or public IP address of the target Redshift database.
You can simply use JDBC endpoint instead of public IP address.

Amazon Redshift Security Group
AWS services are secure by default. So even you have created your Amazon Redshift cluster in a public VPC, the security group assigned to the target Redshift cluster can prevent inbound connections to database cluster. In simple words, Security Group settings of Redshift database play a role of a firewall and prevent inbound database connections over port 5439.
Findthe assigned Security Group and check its Inbound rules.
If it does not exist, add your IP address to the allowed links including Redshift port number.
SQL developers can find their own IP information by simply search on web for "what is my ip" and add their IP address as an exception in Security Group Inbound rules.
Please do not forget to use CIDR notation, so if you have one IP to address in security group, you can use for example "1.2.3.4/32"
"/32" which is at the end of your IP will format the IP adderss in CIDR notation and AWS Security Group settings will accept the entry.

Security Group settings for Amazon Redshift

VPC Network ACL
If security group settings allow your IP address to connect to Amazon Redshift database, maybe the VPC Network Access Control List aka Network ACL is preventing database connection. Go to the VPC definition where the Amazon Redshift cluster is created in and check that VPC's Network ACL inbound settings.

If the connection settings are correct and the security setings in AWS side is allowing inbound connections from your computer where the DBeaver is running, you will be displayed following success message:

successfully connected to Amazon Redshift from DBeaver

One last note; if you mark "Show all databases" and "Show template databases" checkboxes, this will enable to see and easily connect to all databases created on Amazon Redshift cluster.

After the Redshift connection is successfully created between DBeaver Database Manager tool and Amazon Redshift cluster, SQL developers will have a tool enabling access to Redshift resources as seen in following screenshot showing database objects

Amazon Redshift database objects in DBeaver Database Navigator

I displayed the sample database table "aircraft" within DBeaver database management tool. SQL developers will like to access easily to properties of the tabase, its DDL scripts, etc. in one place.

Redshift table properties in DBeaver database manager

The "Data" tab enables DBeaver users to display the table data easily via GUI without executing any SQL code.

Amazon Redshift database table data browser

Database developers can create or display ER Diagrams and model their database solution, too

By launching a new SQL Editor screen, SQL developers can build and run their own SQL queries on Amazon Redshift database.





AWS Tutorials

AWS Tutorials

AWS








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems