<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/cost-effective-bigquery-with-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rviews.rstudio.com/2018/02/02/cost-effective-bigquery-with-r/"> R Views</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<h2>Introduction</h2>
<p>Companies using Google BigQuery for production analytics often run into the following problem: the company has a large user hit table that spans many years. Since queries are billed based on the fields accessed, and not on the date-ranges queried, queries on the table are billed for all available days and are increasingly wasteful.</p>
<div>
<img src="https://i0.wp.com/rviews.rstudio.com/post/2018-01-30-BigQuery_files/partition.png?w=456&ssl=1" data-recalc-dims="1">
</div>
<p>A solution is to partition the table by date, so that users can query a particular range of dates; saving costs and decreasing query duration. Partitioning an un-partitioned table can be expensive if done the brute-force way. This article explores one cost-effective partitioning method, and uses the <a rel="nofollow" href="https://cran.r-project.org/package=condusco" target="_blank">condusco</a> R Package to automate the query generation and partitioning steps.</p>
</div>
<div>
<h2>Migrating non-partitioned tables to partitioned tables in Google BigQuery</h2>
<p>Let’s implement the accepted solution on StackOverflow for <a rel="nofollow" href="https://stackoverflow.com/questions/38993877/migrating-from-non-partitioned-to-partitioned-tables" target="_blank">migrating from non-partitioned to partitioned tables in Google BigQuery</a>.</p>
<p>The brute-force way to partition a non-partitioned table is to repeatedly query the table for anything matching a particular day and then save that data to a new table with the date suffix, ie. _20171201.</p>
<p>The problem is the cost for this method is the cost of querying the full table’s worth of data, multiplied by the number of days it needs to be partitioned into. For a 10 Terabyte table spanning three years, one SELECT * might cost $50 (BigQuery charges $5 per TB accessed). Hence, splitting the table into three years of daily partitions will cost $50*365*3 = <strong>$54,750</strong>!</p>
<p>The more cost-effective <a rel="nofollow" href="https://stackoverflow.com/questions/38993877/migrating-from-non-partitioned-to-partitioned-tables" target="_blank">solution</a> described on StackOverflow is to ARRAY_AGG the entire table into one record for each day. This requires one query over the table’s data to ARRAY_AGG each day you are interested in, and then multiple UNNEST queries using a single query on a single column.</p>
<p>This solution queries the full table’s worth of data twice, instead of the number of days. That’s a cost of $100, saving <strong>$54,650</strong>.</p>
<p>Here is an implementation of the solution using <a rel="nofollow" href="https://github.com/ras44/condusco" target="_blank">condusco</a> to automate both the query generation and the partitioning:</p>
<pre><code>library(whisker)
library(bigrquery)
library(condusco)

# Set GBQ project
project &lt;- '</code></pre></div>