<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/rstudio-1-2-preview-sql-integration/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://blog.rstudio.com/2018/10/02/rstudio-1-2-preview-sql/"> RStudio Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The <a rel="nofollow" href="https://www.rstudio.com/products/rstudio/download/preview/" target="_blank">RStudio 1.2 Preview Release</a>, available today, dramatically improves support and interoperability with many new programming languages and platforms, including SQL, D3, Python, Stan, and C++. Over the next few weeks on the blog, we’re going to be taking a look at improvements for each of these in turn.</p>
<p>Today, we’re looking at SQL, and as a motivating example, we’re going to connect to a sample <em>Chinook</em> database to get a list of album titles.</p>
<h2>Keyring Integration</h2>
<p>We’ll start by connecting to the database. When connecting to databases that use usernames and passwords, it’s not uncommon to see passwords stored in plain text in the connection string. It’s not good practice, but it’s understandable; it can be a big hassle to store and retrieve the password securely.</p>
<p>In RStudio 1.2, we’ve made it much easier to secure your credentials. RStudio now integrates with the <a rel="nofollow" href="https://github.com/r-lib/keyring" target="_blank">keyring</a> package. Your password is stored, secure and encrypted, on your system’s credential store (such as the MacOS Keychain or Windows Credential Store), so you can share your R code without leaking your password.</p>
<p>Instead of being prompted to make the password part of the connection string, you’ll get a prompt to save it to your keyring.</p>
<p><img src="https://i0.wp.com/blog.rstudio.com/images/2018-10-02-rstudio-preview-sql-keyring.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>You can also take advantage of RStudio’s API to prompt for secrets in your own packages. See <a rel="nofollow" href="https://support.rstudio.com/hc/en-us/articles/360000969634-Using-Keyring" target="_blank">Using Keyring</a> for more information.</p>
<h2>Instant Query</h2>
<p>Great, we’re connected; it’s time to make a query! It’s now a lot easier to build and execute SQL queries in RStudio. First, use the SQL button to generate a new SQL file with the open connection:</p>
<p><img src="https://i0.wp.com/blog.rstudio.com/images/2018-10-02-rstudio-preview-sql-template.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h2>Autocompletion</h2>
<p>Now we need to refine our query with the fields we’re interested in. RStudio can now autocomplete table names and field names associated with a connection. This works in <code>.sql</code> files, R Markdown documents, and R Notebooks. We’ll use this to pick up the name of the <code>Title</code> field without extra typing or guessing.</p>
<p><img src="https://i1.wp.com/blog.rstudio.com/images/2018-10-02-rstudio-preview-sql-autocomplete.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h2>Instant Preview</h2>
<p>You’ll notice that there’s a magic comment RStudio added to the top of the file:</p>
<pre><code>-- !preview conn=con
</code></pre>
<p>This comment tells RStudio to execute the query against the open connection named <code>con</code>. We can now click <em>Preview</em> or press <em>Ctrl + Shift + Enter</em> to run the query. Results appear in a new tab:</p>
<p><img src="https://i1.wp.com/blog.rstudio.com/images/2018-10-02-rstudio-preview-sql-preview.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>You can also preview every time you save, if you’re iterating quickly on your query and want to watch the results take shape as you go.</p>
<h2>Filter</h2>
<p>Finally, you can now filter the list of displayed tables in the Connections pane by name. This is very useful when your database has a lot of tables!</p>
<p><img src="https://i2.wp.com/blog.rstudio.com/images/2018-10-02-rstudio-preview-sql-filter.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h2>Try it out!</h2>
<p>If you’d like to try these new features out now, you can download the latest preview release of RStudio from <a rel="nofollow" href="https://www.rstudio.com/products/rstudio/download/preview/" target="_blank">https://www.rstudio.com/products/rstudio/download/preview/</a>. If you do, we’d very much appreciate your feedback on the <a rel="nofollow" href="https://community.rstudio.com/c/rstudio-ide" target="_blank">RStudio Community Forum</a>!</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://blog.rstudio.com/2018/10/02/rstudio-1-2-preview-sql/"> RStudio Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/Cxj7v5Qu8OY" height="1"></a></div>