<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/join-dependency-sorting/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2017/07/join-dependency-sorting/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In our latest installment of “<a rel="nofollow" href="http://www.win-vector.com/blog/tag/r-and-big-data/" target="_blank"><code>R</code> and big data</a>” let’s again discuss the task of left joining many tables from a data warehouse using <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> and a system called &quot;a join controller&quot; (last discussed <a rel="nofollow" href="http://www.win-vector.com/blog/2017/06/use-a-join-controller-to-document-your-work/" target="_blank">here</a>).</p>
<p>One of the great advantages to specifying complicated sequences of operations in data (rather than in code) is: it is often easier to transform and extend data. Explicit rich data beats vague convention and complicated code.</p>
<p><span></span></p>
<p>For example suppose we have a <code>dplyr</code> <code>RSQLite</code> database handle in the variable <code>my_db</code> and we are using the following names for our tables in this database (keep in mind <code>replyr</code> allows different abstract and concrete table names, for this example we will assume they are the same):</p>
<div>
<pre><code>tableNames &lt;-<span> </span><span>c</span>(<span>'employeeanddate'</span>,
                <span>'revenue'</span>,
                <span>'activity'</span>,
                <span>'orgtable'</span>)</code></pre>
</div>
<p>We can now use <code>replyr::tableDescription()</code> to get descriptions of these tables <em>including</em> any declared primary key structure.</p>
<div>
<pre><code><span>suppressPackageStartupMessages</span>(<span>library</span>(<span>&quot;dplyr&quot;</span>))
<span>library</span>(<span>&quot;replyr&quot;</span>)

tDesc &lt;-<span> </span>tableNames %>%
<span> </span><span>lapply</span>(
    function(ni) {
      replyr::<span>tableDescription</span>(ni,
                               dplyr::<span>tbl</span>(my_db, ni),
                               <span>keyInspector=</span> key_inspector_sqlite)
    }) %>%
<span> </span><span>bind_rows</span>()

<span>print</span>(tDesc[, <span>c</span>(<span>'tableName'</span>, <span>'handle'</span>, <span>'keys'</span>, <span>'columns'</span>), ])</code></pre>
</div>
<pre><code>## # A tibble: 4 x 4
##         tableName        handle      keys   columns
##             </code></pre>