AWS Redshift and DBeaver Connection
In this article, we are going to create a suitable Redshift cluster and connect to this cluster using DBeaver.
AWS Redshift Cluster
First of all, we have to create an IAM role which will include the following policies:
- AmazonS3FullAccess
- AmazonRedshiftAllCommandsFullAccess
After creating the IAM role, we should also create a subnet group since it will require us to use a subnet group while creating the cluster. Under Configuration -> Subnet groups, we can create a new subnet group. If we use the cluster for comparably simpler data loads, we can choose the smallest Node type and choose the number as 1 so that it won’t cost too much.
Then, we are going to define the username and password. This part is essential since we will be using these while connecting to DBeaver.
After creating the username and password, we should uncheck the default settings and define the Network and security section ourselves.
We should choose the subnet group we already created and the VPC it is located. We can also choose a suitable security group. After creating the cluster, we will add Redshift as an inbound rule to our security group.
We should definitely check the Turn on Publicly accessible button so that we will be able to access our cluster from DBeaver.
We can leave all other options as default and create our cluster. It will take around 10 minutes. After creating, we can also run our queries using the Query editor on the Redshift dashboard, but we will use DBeaver.
DBeaver Connection
After our cluster is created, we can open DBeaver and create a new connection. We should choose the source as Redshift. After choosing, the below screen will appear.
We can choose either the Host or the URL above. On the main page of Redshift, we can see the endpoint which will be <our_host>:5439. We can copy the host and paste it here. The URL will automatically be created. Port is 5439. dev is the default database while creating the Redshift cluster. We might have changed its name. But if we don’t change, it will stay as dev. We should also populate username and password parameters. After all, we can Test Connection, if successful, we can Finish.
We should be able to see the above on the left side of DBeaver. We can now run all necessary SQL queries on DBeaver. This environment is exactly the same as the Query editor of Redshift.
We can create tables upon a file located in the S3 bucket. We can also create external schemas, databases, and tables to get multiple files located in a certain S3 directory. After creating the tables, we can run SQL queries against them. We can also run our queries for the tables that already exist.
