Menu
I'm currently trying to connect to a redshift database in R. This needs to be done over an SSL connection but I can't seem to find options to specify the path of the certificate file to use in dbConnect. Google hasn't been to helpful either surprisingly enough.
Is it really that difficult establish a postgres SSL connection to redshift via R or am I just missing out on something fundamental?
514
Connecting R to Amazon Redshift with RJDBC In R, you can install the RJDBC package to load the JDBC driver and send SQL queries to Amazon Redshift. This requires a matching JDBC driver. Choose the latest JDBC driver provided by AWS (Configure a JDBC Connection).
To connect by using psql defaults Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/ . Where: <endpoint> is the Endpoint you recorded in the previous step. <userid> is a user ID with permissions to connect to the cluster.
In this section we shall add a new connection in Toad Data Point for a Redshift cluster. Click on Connect>New Connection as shown in Figure 1. In the list of data sources displayed select Amazon Redshift, as shown in Figure 2. In the Create New Connection window, shown in Figure 3, configure the connection parameters.
simply do:
host = 'redshift-name.xxxxxxxxxxxx.eu-west-1.redshift.amazonaws.com'
dbname = 'your_db_name'
port = 3306
password = 'hunter2'
username = 'rs_user'
redshift_cert = paste0(FILE_PATH, 'redshift-ssl-ca-cert.pem')
pg_dsn = paste0(
'dbname=', dbname, ' ',
'sslrootcert=', redshift_cert, ' ',
'sslmode=verify-full'
)
dbConnect(RPostgreSQL::PostgreSQL(), dbname=pg_dsn, host=host, port=port, password=password, user=username)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
