File: DBI-arrow.html

package info (click to toggle)
dbi 1.3.0-1
links: PTS, VCS
area: main
in suites: forky, sid
size: 2,740 kB
sloc: makefile: 2
file content (582 lines) | stat: -rw-r--r-- 25,625 bytes
<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />

<meta name="viewport" content="width=device-width, initial-scale=1" />

<meta name="author" content="Kirill Müller" />


<title>Using DBI with Arrow</title>

<script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to
// be compatible with the behavior of Pandoc < 2.8).
document.addEventListener('DOMContentLoaded', function(e) {
  var hs = document.querySelectorAll("div.section[class*='level'] > :first-child");
  var i, h, a;
  for (i = 0; i < hs.length; i++) {
    h = hs[i];
    if (!/^h[1-6]$/i.test(h.tagName)) continue;  // it should be a header h1-h6
    a = h.attributes;
    while (a.length > 0) h.removeAttribute(a[0].name);
  }
});
</script>

<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>



<style type="text/css">
code {
white-space: pre;
}
.sourceCode {
overflow: visible;
}
</style>
<style type="text/css" data-origin="pandoc">
html { -webkit-text-size-adjust: 100%; }
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { display: inline-block; line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } 
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.at { color: #7d9029; } 
code span.bn { color: #40a070; } 
code span.bu { color: #008000; } 
code span.cf { color: #007020; font-weight: bold; } 
code span.ch { color: #4070a0; } 
code span.cn { color: #880000; } 
code span.co { color: #60a0b0; font-style: italic; } 
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.do { color: #ba2121; font-style: italic; } 
code span.dt { color: #902000; } 
code span.dv { color: #40a070; } 
code span.er { color: #ff0000; font-weight: bold; } 
code span.ex { } 
code span.fl { color: #40a070; } 
code span.fu { color: #06287e; } 
code span.im { color: #008000; font-weight: bold; } 
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.kw { color: #007020; font-weight: bold; } 
code span.op { color: #666666; } 
code span.ot { color: #007020; } 
code span.pp { color: #bc7a00; } 
code span.sc { color: #4070a0; } 
code span.ss { color: #bb6688; } 
code span.st { color: #4070a0; } 
code span.va { color: #19177c; } 
code span.vs { color: #4070a0; } 
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } 
</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
  var sheets = document.styleSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
    try { var rules = sheets[i].cssRules; } catch (e) { continue; }
    var j = 0;
    while (j < rules.length) {
      var rule = rules[j];
      // check if there is a div.sourceCode rule
      if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") {
        j++;
        continue;
      }
      var style = rule.style.cssText;
      // check if color or background-color is set
      if (rule.style.color === '' && rule.style.backgroundColor === '') {
        j++;
        continue;
      }
      // replace div.sourceCode by a pre.sourceCode rule
      sheets[i].deleteRule(j);
      sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
    }
  }
})();
</script>




<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap; 
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }

code > span.kw { color: #555; font-weight: bold; } 
code > span.dt { color: #902000; } 
code > span.dv { color: #40a070; } 
code > span.bn { color: #d14; } 
code > span.fl { color: #d14; } 
code > span.ch { color: #d14; } 
code > span.st { color: #d14; } 
code > span.co { color: #888888; font-style: italic; } 
code > span.ot { color: #007020; } 
code > span.al { color: #ff0000; font-weight: bold; } 
code > span.fu { color: #900; font-weight: bold; } 
code > span.er { color: #a61717; background-color: #e3d2d2; } 
</style>




</head>

<body>




<h1 class="title toc-ignore">Using DBI with Arrow</h1>
<h4 class="author">Kirill Müller</h4>
<h4 class="date">25/12/2023</h4>



<div id="who-this-tutorial-is-for" class="section level2">
<h2>Who this tutorial is for</h2>
<p>This tutorial is for you if you want to leverage <a href="https://arrow.apache.org/">Apache Arrow</a> for accessing and
manipulating data on databases. See
<code>vignette(&quot;DBI&quot;, package = &quot;DBI&quot;)</code> and
<code>vignette(&quot;DBI-advanced&quot;, package = &quot;DBI&quot;)</code> for tutorials on
accessing data using R’s data frames instead of Arrow’s structures.</p>
</div>
<div id="rationale" class="section level2">
<h2>Rationale</h2>
<p>Apache Arrow is</p>
<blockquote>
<p>a cross-language development platform for in-memory analytics,</p>
</blockquote>
<p>suitable for large and huge data, with support for out-of-memory
operation. Arrow is also a data exchange format, the data types covered
by Arrow align well with the data types supported by SQL databases.</p>
<p>DBI 1.2.0 introduced support for Arrow as a format for exchanging
data between R and databases. The aim is to:</p>
<ul>
<li>accelerate data retrieval and loading, by using fewer costly data
conversions;</li>
<li>better support reading and summarizing data from a database that is
larger than memory;</li>
<li>provide better type fidelity with workflows centered around
Arrow.</li>
</ul>
<p>This allows existing code to be used with Arrow, and it allows new
code to be written that is more efficient and more flexible than code
that uses R’s data frames.</p>
<p>The interface is built around the {nanoarrow} R package, with
<code>nanoarrow::as_nanoarrow_array</code> and
<code>nanoarrow::as_nanoarrow_array_stream</code> as fundamental data
structures.</p>
</div>
<div id="new-classes-and-generics" class="section level2">
<h2>New classes and generics</h2>
<p>DBI 1.2.0 introduces new classes and generics for working with Arrow
data:</p>
<ul>
<li><code>dbReadTableArrow()</code></li>
<li><code>dbWriteTableArrow()</code></li>
<li><code>dbCreateTableArrow()</code></li>
<li><code>dbAppendTableArrow()</code></li>
<li><code>dbGetQueryArrow()</code></li>
<li><code>dbSendQueryArrow()</code></li>
<li><code>dbBindArrow()</code></li>
<li><code>dbFetchArrow()</code></li>
<li><code>dbFetchArrowChunk()</code></li>
<li><code>DBIResultArrow-class</code></li>
<li><code>DBIResultArrowDefault-class</code></li>
</ul>
<p>Compatibility is important for DBI, and implementing new generics and
classes greatly reduces the risk of breaking existing code. The DBI
package comes with a fully functional fallback implementation for all
existing DBI backends. The fallback is not improving performance, but it
allows existing code to be used with Arrow before switching to a backend
with native Arrow support. Backends with native support, like the <a href="https://adbi.r-dbi.org/">adbi</a> package, implement the new
generics and classes for direct support and improved performance.</p>
<p>In the remainder of this tutorial, we will demonstrate the new
generics and classes using the RSQLite package. SQLite is an in-memory
database, this code does not need a database server to be installed and
running.</p>
</div>
<div id="prepare" class="section level2">
<h2>Prepare</h2>
<p>We start by setting up a database connection and creating a table
with some data, using the original <code>dbWriteTable()</code>
method.</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" tabindex="-1"></a><span class="fu">library</span>(DBI)</span>
<span id="cb1-2"><a href="#cb1-2" tabindex="-1"></a></span>
<span id="cb1-3"><a href="#cb1-3" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>())</span>
<span id="cb1-4"><a href="#cb1-4" tabindex="-1"></a></span>
<span id="cb1-5"><a href="#cb1-5" tabindex="-1"></a>data <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(</span>
<span id="cb1-6"><a href="#cb1-6" tabindex="-1"></a>  <span class="at">a =</span> <span class="dv">1</span><span class="sc">:</span><span class="dv">3</span>,</span>
<span id="cb1-7"><a href="#cb1-7" tabindex="-1"></a>  <span class="at">b =</span> <span class="fl">4.5</span>,</span>
<span id="cb1-8"><a href="#cb1-8" tabindex="-1"></a>  <span class="at">c =</span> <span class="st">&quot;five&quot;</span></span>
<span id="cb1-9"><a href="#cb1-9" tabindex="-1"></a>)</span>
<span id="cb1-10"><a href="#cb1-10" tabindex="-1"></a></span>
<span id="cb1-11"><a href="#cb1-11" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;tbl&quot;</span>, data)</span></code></pre></div>
</div>
<div id="read-all-rows-from-a-table" class="section level2">
<h2>Read all rows from a table</h2>
<p>The <code>dbReadTableArrow()</code> method reads all rows from a
table into an Arrow stream, similarly to <code>dbReadTable()</code>.
Arrow objects implement the <code>as.data.frame()</code> method, so we
can convert the stream to a data frame.</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbReadTableArrow</span>(con, <span class="st">&quot;tbl&quot;</span>)</span>
<span id="cb2-2"><a href="#cb2-2" tabindex="-1"></a>stream</span></code></pre></div>
<pre><code>## &lt;nanoarrow_array_stream struct&lt;a: int32, b: double, c: string&gt;&gt;
##  $ get_schema:function ()  
##  $ get_next  :function (schema = x$get_schema(), validate = TRUE)  
##  $ release   :function ()</code></pre>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" tabindex="-1"></a><span class="fu">as.data.frame</span>(stream)</span></code></pre></div>
<pre><code>##   a   b    c
## 1 1 4.5 five
## 2 2 4.5 five
## 3 3 4.5 five</code></pre>
</div>
<div id="run-queries" class="section level2">
<h2>Run queries</h2>
<p>The <code>dbGetQueryArrow()</code> method runs a query and returns
the result as an Arrow stream. This stream can be turned into an
<code>arrow::RecordBatchReader</code> object and processed further,
without bringing it into R.</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT COUNT(*) AS n FROM tbl WHERE a &lt; 3&quot;</span>)</span>
<span id="cb6-2"><a href="#cb6-2" tabindex="-1"></a>stream</span></code></pre></div>
<pre><code>## &lt;nanoarrow_array_stream struct&lt;n: int32&gt;&gt;
##  $ get_schema:function ()  
##  $ get_next  :function (schema = x$get_schema(), validate = TRUE)  
##  $ release   :function ()</code></pre>
<div class="sourceCode" id="cb8"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" tabindex="-1"></a>path <span class="ot">&lt;-</span> <span class="fu">tempfile</span>(<span class="at">fileext =</span> <span class="st">&quot;.parquet&quot;</span>)</span>
<span id="cb8-2"><a href="#cb8-2" tabindex="-1"></a>arrow<span class="sc">::</span><span class="fu">write_parquet</span>(arrow<span class="sc">::</span><span class="fu">as_record_batch_reader</span>(stream), path)</span>
<span id="cb8-3"><a href="#cb8-3" tabindex="-1"></a>arrow<span class="sc">::</span><span class="fu">read_parquet</span>(path)</span></code></pre></div>
<pre><code>## # A data frame: 1 × 1
##       n
## * &lt;int&gt;
## 1     2</code></pre>
</div>
<div id="prepared-queries" class="section level2">
<h2>Prepared queries</h2>
<p>The <code>dbGetQueryArrow()</code> method supports prepared queries,
using the <code>params</code> argument which accepts a data frame or a
list.</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" tabindex="-1"></a>params <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">a =</span> <span class="dv">3</span><span class="dt">L</span>)</span>
<span id="cb10-2"><a href="#cb10-2" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT $a AS batch, * FROM tbl WHERE a &lt; $a&quot;</span>, <span class="at">params =</span> params)</span>
<span id="cb10-3"><a href="#cb10-3" tabindex="-1"></a><span class="fu">as.data.frame</span>(stream)</span></code></pre></div>
<pre><code>##   batch a   b    c
## 1     3 1 4.5 five
## 2     3 2 4.5 five</code></pre>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" tabindex="-1"></a>params <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">a =</span> <span class="fu">c</span>(<span class="dv">2</span><span class="dt">L</span>, <span class="dv">4</span><span class="dt">L</span>))</span>
<span id="cb12-2"><a href="#cb12-2" tabindex="-1"></a><span class="co"># Equivalent to dbBind()</span></span>
<span id="cb12-3"><a href="#cb12-3" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT $a AS batch, * FROM tbl WHERE a &lt; $a&quot;</span>, <span class="at">params =</span> params)</span>
<span id="cb12-4"><a href="#cb12-4" tabindex="-1"></a><span class="fu">as.data.frame</span>(stream)</span></code></pre></div>
<pre><code>##   batch a   b    c
## 1     2 1 4.5 five
## 2     4 1 4.5 five
## 3     4 2 4.5 five
## 4     4 3 4.5 five</code></pre>
</div>
<div id="manual-flow" class="section level2">
<h2>Manual flow</h2>
<p>For the manual flow, use <code>dbSendQueryArrow()</code> to send a
query to the database, and <code>dbFetchArrow()</code> to fetch the
result. This also allows using the new <code>dbBindArrow()</code> method
to bind data in Arrow format to a prepared query. Result objects must be
cleared with <code>dbClearResult()</code>.</p>
<div class="sourceCode" id="cb14"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb14-1"><a href="#cb14-1" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQueryArrow</span>(con, <span class="st">&quot;SELECT $a AS batch, * FROM tbl WHERE a &lt; $a&quot;</span>)</span>
<span id="cb14-2"><a href="#cb14-2" tabindex="-1"></a></span>
<span id="cb14-3"><a href="#cb14-3" tabindex="-1"></a>in_arrow <span class="ot">&lt;-</span> nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array</span>(<span class="fu">data.frame</span>(<span class="at">a =</span> <span class="dv">1</span><span class="dt">L</span>))</span>
<span id="cb14-4"><a href="#cb14-4" tabindex="-1"></a><span class="fu">dbBindArrow</span>(rs, in_arrow)</span>
<span id="cb14-5"><a href="#cb14-5" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(rs))</span></code></pre></div>
<pre><code>## [1] batch a     b     c    
## &lt;0 rows&gt; (or 0-length row.names)</code></pre>
<div class="sourceCode" id="cb16"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb16-1"><a href="#cb16-1" tabindex="-1"></a>in_arrow <span class="ot">&lt;-</span> nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array</span>(<span class="fu">data.frame</span>(<span class="at">a =</span> <span class="dv">2</span><span class="dt">L</span>))</span>
<span id="cb16-2"><a href="#cb16-2" tabindex="-1"></a><span class="fu">dbBindArrow</span>(rs, in_arrow)</span>
<span id="cb16-3"><a href="#cb16-3" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(rs))</span></code></pre></div>
<pre><code>##   batch a   b    c
## 1     2 1 4.5 five</code></pre>
<div class="sourceCode" id="cb18"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb18-1"><a href="#cb18-1" tabindex="-1"></a>in_arrow <span class="ot">&lt;-</span> nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array</span>(<span class="fu">data.frame</span>(<span class="at">a =</span> <span class="dv">3</span><span class="dt">L</span>))</span>
<span id="cb18-2"><a href="#cb18-2" tabindex="-1"></a><span class="fu">dbBindArrow</span>(rs, in_arrow)</span>
<span id="cb18-3"><a href="#cb18-3" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(rs))</span></code></pre></div>
<pre><code>##   batch a   b    c
## 1     3 1 4.5 five
## 2     3 2 4.5 five</code></pre>
<div class="sourceCode" id="cb20"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb20-1"><a href="#cb20-1" tabindex="-1"></a>in_arrow <span class="ot">&lt;-</span> nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array</span>(<span class="fu">data.frame</span>(<span class="at">a =</span> <span class="dv">1</span><span class="sc">:</span><span class="dv">4</span><span class="dt">L</span>))</span>
<span id="cb20-2"><a href="#cb20-2" tabindex="-1"></a><span class="fu">dbBindArrow</span>(rs, in_arrow)</span>
<span id="cb20-3"><a href="#cb20-3" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(rs))</span></code></pre></div>
<pre><code>##   batch a   b    c
## 1     2 1 4.5 five
## 2     3 1 4.5 five
## 3     3 2 4.5 five
## 4     4 1 4.5 five
## 5     4 2 4.5 five
## 6     4 3 4.5 five</code></pre>
<div class="sourceCode" id="cb22"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb22-1"><a href="#cb22-1" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span></code></pre></div>
</div>
<div id="writing-data" class="section level2">
<h2>Writing data</h2>
<p>Streams returned by <code>dbGetQueryArrow()</code> and
<code>dbReadTableArrow()</code> can be written to a table using
<code>dbWriteTableArrow()</code>.</p>
<div class="sourceCode" id="cb23"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb23-1"><a href="#cb23-1" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT * FROM tbl WHERE a &lt; 3&quot;</span>)</span>
<span id="cb23-2"><a href="#cb23-2" tabindex="-1"></a><span class="fu">dbWriteTableArrow</span>(con, <span class="st">&quot;tbl_new&quot;</span>, stream)</span>
<span id="cb23-3"><a href="#cb23-3" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;tbl_new&quot;</span>)</span></code></pre></div>
<pre><code>##   a   b    c
## 1 1 4.5 five
## 2 2 4.5 five</code></pre>
</div>
<div id="appending-data" class="section level2">
<h2>Appending data</h2>
<p>For more control over the writing process, use
<code>dbCreateTableArrow()</code> and
<code>dbAppendTableArrow()</code>.</p>
<div class="sourceCode" id="cb25"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb25-1"><a href="#cb25-1" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT * FROM tbl WHERE a &lt; 3&quot;</span>)</span>
<span id="cb25-2"><a href="#cb25-2" tabindex="-1"></a><span class="fu">dbCreateTableArrow</span>(con, <span class="st">&quot;tbl_split&quot;</span>, stream)</span>
<span id="cb25-3"><a href="#cb25-3" tabindex="-1"></a><span class="fu">dbAppendTableArrow</span>(con, <span class="st">&quot;tbl_split&quot;</span>, stream)</span></code></pre></div>
<pre><code>## [1] 2</code></pre>
<div class="sourceCode" id="cb27"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb27-1"><a href="#cb27-1" tabindex="-1"></a>stream <span class="ot">&lt;-</span> <span class="fu">dbGetQueryArrow</span>(con, <span class="st">&quot;SELECT * FROM tbl WHERE a &gt;= 3&quot;</span>)</span>
<span id="cb27-2"><a href="#cb27-2" tabindex="-1"></a><span class="fu">dbAppendTableArrow</span>(con, <span class="st">&quot;tbl_split&quot;</span>, stream)</span></code></pre></div>
<pre><code>## [1] 1</code></pre>
<div class="sourceCode" id="cb29"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb29-1"><a href="#cb29-1" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;tbl_split&quot;</span>)</span></code></pre></div>
<pre><code>##   a   b    c
## 1 1 4.5 five
## 2 2 4.5 five
## 3 3 4.5 five</code></pre>
</div>
<div id="conclusion" class="section level2">
<h2>Conclusion</h2>
<p>Do not forget to disconnect from the database when done.</p>
<div class="sourceCode" id="cb31"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb31-1"><a href="#cb31-1" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
<p>That concludes the major features of DBI’s new Arrow interface. For
more details on the library functions covered in this tutorial see the
DBI specification at <code>vignette(&quot;spec&quot;, package = &quot;DBI&quot;)</code>. See
the <a href="https://adbi.r-dbi.org/">adbi</a> package for a backend
with native Arrow support, and <a href="https://github.com/apache/arrow-nanoarrow">nanoarrow</a> and <a href="https://arrow.apache.org/docs/r/">arrow</a> for packages to work
with the Arrow format.</p>
</div>



<!-- code folding -->


<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
  (function () {
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src  = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
    document.getElementsByTagName("head")[0].appendChild(script);
  })();
</script>

</body>
</html>