<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/a-comprehensive-guide-to-connect-r-to-amazon-redshift/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://blog.datascienceheroes.com/a-comprehensive-guide-to-connect-r-to-amazon-redshift/"> R - Data Science Heroes Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><img alt="A comprehensive guide to connect R to Amazon Redshift" src="https://i1.wp.com/blog.datascienceheroes.com/content/images/2017/12/logo-1.png?w=456" data-recalc-dims="1"></p>
<p><a rel="nofollow" href="https://aws.amazon.com/redshift/" target="_blank">Amazon Redshift</a> is one of the hottest databases for Data Warehousing right now, it’s one of the most cost-effective solutions available, and allows for integration with many popular BI tools. Unfortunately, the status of the drivers compatibility is a little more shaky, but there is a way to make it work very nicely with <a rel="nofollow" href="https://www.r-project.org/" target="_blank">R</a>!</p>
<p><em><a rel="nofollow" href="https://auth0.com/blog/a-comprehensive-guide-for-connecting-with-r-to-redshift/" target="_blank">Originally posted on the Auth0 Blog</a></em></p>
<p>First of all, let’s go through the 3 options we have for connecting to Amazon Redshift. For all of the connections, we’ll define these variables for connecting:</p>
<pre><code>dbname=&quot;dbname&quot;  
host='my-redshift-url.amazon.com'  
port='5439'  
user='myuser'  
password='mypassword'  
</code></pre>
<h2>RJDBC</h2>
<p>This is the <a rel="nofollow" href="https://aws.amazon.com/blogs/big-data/connecting-r-with-amazon-redshift/" target="_blank">“official” way to use Amazon Redshift with R</a>, using the JDBC driver on <a rel="nofollow" href="http://www.sql-workbench.net/" target="_blank">SQL Workbench/J</a> is the official way to connect to it according to the documentation, and this driver can be loaded like this:</p>
<pre><code># Save the driver into a directory
dir.create('~/.redshiftTools')  
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar','~/.redshiftTools/redshift-driver.jar')  
install.packages('RJDBC')  
</code></pre>
<pre><code>suppressPackageStartupMessages(library(RJDBC))

# Use Redshift driver
driver &lt;- JDBC(&quot;com.amazon.redshift.jdbc41.Driver&quot;, &quot;~/.redshiftTools/redshift-driver.jar&quot;, identifier.quote=&quot;`&quot;)

# Create connection    
url &lt;- sprintf(&quot;jdbc:redshift://%s:%s/%s?tcpKeepAlive=true&amp;ssl=true&amp;sslfactory=com.amazon.redshift.ssl.NonValidatingFactory&quot;, host, port, dbname)  
jconn &lt;- dbConnect(driver, url, user, password)  
</code></pre>
<p>Of course you can change the url with the options you need for your particular setup. The first 3 lines you only need to run them once, they download Redshift’s official JDBC driver and install the RJDBC package.</p>
<p>This package was the only one I found which supported transactions on Redshift (<code>BEGIN</code>, <code>COMMIT</code>, <code>ROLLBACK</code>) until recently, otherwise on other packages DDL operations will autocommit.</p>
<p>Until a few days ago since the writing of this blogpost, this was the recommended package for uploading data and inserting/deleting data, since you usually want be able to unite your operations which modify data into a single transaction.</p>
<p>This package has some big problems though, which I’ll explain later on this post.</p>
<h2>RPostgreSQL</h2>
<p>Amazon Redshift is mostly PostgreSQL compatible, so most PostgreSQL drivers work well. <a rel="nofollow" href="https://blog.datascienceheroes.com/a-comprehensive-guide-to-connect-r-to-amazon-redshift/code.google.com/p/rpostgresql" target="_blank">RPostgreSQL</a> is configured like this:</p>
<pre><code>install.packages('RPostgreSQL')  
</code></pre>
<pre><code>library(RPostgreSQL)  
drv &lt;- dbDriver(&quot;PostgreSQL&quot;)  
pconn_rsql &lt;- dbConnect(drv,  
                 host = host,
                 port = port,
                 user = user,
                 password = password,
                 dbname = dbname)
</code></pre>
<p>There are two major problems with this driver though: It <a rel="nofollow" href="https://stackoverflow.com/questions/38942118/can-not-connect-postgresqlover-ssl-with-rpostgresql-on-windows" target="_blank">lacks SSL support</a>, and you don’t have transactions like you do with the official driver. In my humble opinion this is not a good option, even if you don’t use SSL now, if your company requires it in the future you may need to change all your code to switch drivers. The good thing is this driver works fine with <a rel="nofollow" href="http://dbplyr.tidyverse.org/" target="_blank">dbplyr</a>.</p>
<h2>RPostgres</h2>
<p>This is another PostgreSQL library, that has much better support using the <a rel="nofollow" href="https://www.postgresql.org/docs/9.6/static/libpq.html" target="_blank">libpq library</a>, you’ll probably need to install the postgresql development header libraries locally for this one to be installed succesfully. <a rel="nofollow" href="https://github.com/r-dbi/RPostgres" target="_blank">Check out the official docs for installing instructions if you run into issues.</a></p>
<pre><code>install.packages('RPostgres')  
</code></pre>
<pre><code>library(RPostgres)  
pconn_r &lt;- dbConnect(RPostgres::Postgres(),  
               host = host,
               port = port,
               user = user,
               password = password,
               dbname = dbname,
               sslmode='require')
</code></pre>
<p>RPostgres supports transactions, SSL and works fine with dbplyr.</p>
<h2>Gotchas With The Libraries</h2>
<p>So, what are some gotchas with these libraries?. We’ll run this in SQL Workbench/J to have some data to test:</p>
<pre><code>create table sicatest (  
  a varchar(100),
  b bigint,
  c date,
  d timestamp,
  e boolean
);
insert into sicatest values  
('Hello', 1, '2017-10-01', '2017-10-01 20:00:00', TRUE);
insert into sicatest values  
('Hello ñandu', 9223372036854775807, '2017-12-30', '2017-12-11 23:59:00', FALSE);
insert into sicatest values  
('こんにちは', 9223372036854775807, '2017-12-30', '2017-12-11 23:59:00', NULL);
commit;  
</code></pre>
<p>We deliberately added the Ñ letter from spanish, and こんにちは (Kon’nichiwa) which is hello in japanese to have some non-ascii stuff, and see if it’s handled correctly.</p>
<p>So, let’s start with RJDBC:</p>
<pre><code>suppressPackageStartupMessages(library(dplyr))  
sicatest = dbGetQuery(jconn, 'select * from sicatest')  
dplyr::glimpse(sicatest)  
</code></pre>
<pre><code>## Observations: 3
## Variables: 5
## $ a </code></pre>