Presto connectivity to Redshift

This post helps you in adding redshift connector to presto so that you can directly access redshift using Presto.

Majority of the data resides on Redshift and our end users can only access Presto. So, tried on adding redshift connector to presto. By following below steps:

1. Add redshift properties under /etc/presto/conf/catalog/ (This will add redshift catalog)

========= connector.name=redshift connection-url=jdbc:postgresql://<redshift endpoint url>:<redshift port>/<databasename> connection-user=<username> connection-password=<user password> =========


2. Now, go to presto-CLI: to find redshift catalog as well

presto> show catalogs;

 Catalog  

----------

 hive     

 redshift 

 system   

(3 rows)

Query 20190724_115553_00001_kcxs8, FINISHED, 2 nodes

Splits: 36 total, 36 done (100.00%)

0:00 [0 rows, 0B] [0 rows/s, 0B/s]

NOTE:

- Restart presto-server if you cannot find redshift catalog

- Better to add these properties on all master and all the slave nodes

3. But, while trying to access schemas from redshift, I'm getting connection attempt failed error. Maybe because of the security group issues.

presto> show schemas from redshift;

Query 20190724_115839_00002_kcxs8 failed: The connection attempt failed.

The issue is with the jdbc url, added to the redshift properties file. After fixing it, it worked fine.