<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/"></div></div><div><a data-url="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/" data-counturl="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/" data-count="horizontal" data-text="How to bulk upload your data from R into Redshift" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.datascienceheroes.com/how-to-bulk-upload-your-r-data-frames-to-redshift/"> R - Data Science Heroes Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Amazon’s columnar database, Redshift is a great companion for a lot of Data Science tasks, it allows for fast processing of very big datasets, with a familiar query language (SQL).</p>
<p>There are 2 ways to load data into Redshift, the classic one, using the <code>INSERT</code> statement, works, but it is highly inefficient when loading big datasets. The other one, recommended in <a target="_blank" href="http://docs.aws.amazon.com/redshift/latest/dg/t_Loading_data.html">Redshift’s docs</a>, consists on using the <code>COPY</code> statement.</p>
<p>One of the easiests ways to accomplish this, since we are already using Amazon’s infrastructure, is to do a <a target="_blank" href="http://docs.aws.amazon.com/redshift/latest/dg/t_Loading-data-from-S3.html">load from S3</a>. S3 loading requires that you upload your data to Redshift and then run a <code>COPY</code> statement specifying where your data is.</p>
<p>Also, because Redshift is a distributed database, they recommend you to split your file, in a number of files which are a multiple of the number of slices on your database, so they can load it in parallel, and they also let you gzip your files for a faster upload.</p>
<p>Wait a second, now to upload a big dataset fast we have to:</p>
<ul>
<li>Create a table in Redshift with the same structure as my data frame</li>
<li>Split the data into N parts</li>
<li>Convert the parts into a format readable by Redshift</li>
<li>Upload all the parts to Amazon S3</li>
<li>Run the COPY statement on Redshift</li>
<li>Delete the temporary files on Amazon S3</li>
</ul>
<p>That does seem like a lot of work, but don’t worry, i’ve got your back! I’ve created an R Package which does exactly this, it’s <code>redshiftTools</code>! The code is available at <a target="_blank" href="https://github.com/sicarul/redshiftTools">github.com/sicarul/redshiftTools</a>.</p>
<p>To install the package, you’ll need to do: </p>
<pre><code>    install.packages('devtools')
    devtools::install_github(&quot;RcppCore/Rcpp&quot;)
    devtools::install_github(&quot;rstats-db/DBI&quot;)
    devtools::install_github(&quot;rstats-db/RPostgres&quot;)
    install.packages(&quot;aws.s3&quot;, repos = c(getOption(&quot;repos&quot;), &quot;http://cloudyr.github.io/drat&quot;))
    devtools::install_github(&quot;sicarul/redshiftTools&quot;)
</code></pre>
<p>Afterwards, you’ll be able to use it like this:</p>
<pre><code>    library(&quot;aws.s3&quot;)
    library(RPostgres)
    library(redshiftTools)

    con &lt;- dbConnect(RPostgres::Postgres(), dbname=&quot;dbname&quot;,
    host='my-redshift-url.amazon.com', port='5439',
    user='myuser', password='mypassword',sslmode='require')

    rs_replace_table(my_data, dbcon=con, tableName='mytable', bucket=&quot;mybucket&quot;)
    rs_upsert_table(my_other_data, dbcon=con, tableName = 'mytable', bucket=&quot;mybucket&quot;, keys=c('id', 'date'))
</code></pre>
<p><code>rs_replace_table</code> truncates the target table and then loads it entirely from the data frame, only do this if you don’t care about the current data it holds. On the other hand, <code>rs_upsert_table</code> replaces rows which have coinciding keys, and inserts those that do not exist in the table.</p>
<p>Please open an issue in Github if you find any issues. Have fun!</p>
<hr>
<div>
<a target="_blank" href="https://twitter.com/DataSciHeroes"><img title="Data Science Heroes Twitter" alt="Data Science Heroes Twitter" src="http://i2.wp.com/datascienceheroes.com/img/blog/twitter_logo.PNG?w=456" data-recalc-dims="1"></a>
<p><a target="_blank" href="https://www.facebook.com/datasciheroes"><img title="Data Science Heroes Facebook" alt="Data Science Heroes Facebook" src="http://i2.wp.com/datascienceheroes.com/img/blog/fb_logo.PNG?w=456" data-recalc-dims="1"></a></p>
<p><a target="_blank" href="http://blog.datascienceheroes.com/"><img title="Data Science Heroes Blog" alt="Data Science Heroes Blog" src="http://i2.wp.com/datascienceheroes.com/img/blog/logo_dsh.png?w=456" data-recalc-dims="1"></a>
</div>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/"></div></div><div><a data-url="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/" data-counturl="http://www.r-bloggers.com/how-to-bulk-upload-your-data-from-r-into-redshift/" data-count="vertical" data-text="How to bulk upload your data from R into Redshift" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://blog.datascienceheroes.com/how-to-bulk-upload-your-r-data-frames-to-redshift/"> R - Data Science Heroes Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/8ukL1qnsQYI"></a></div>