Simple Iteration

Ever find yourself running the same sql commands, but just changing out a few parameters each time? If your parameters can be created by a query, you can use run_pipeline_gbq to repeatedly run a query with different parameters.

Here's a complete R script that will accomplish this kind of iterative query using both whisker and condusco:

library(bigrquery)
library(condusco)
library(whisker)

# create a simple function that will create a query
# using {{{mustache}}} placeholders for any parameters

create_results_table <- function(params){
  
  destination_table <- '{{{dataset_id}}}.{{{table_prefix}}}_results_{{{year_low}}}_{{{year_high}}}'
  
  query <- '
    SELECT *
    FROM `bigquery-public-data.samples.gsod`
    WHERE year > {{{year_low}}}
      AND year <= {{{year_high}}}
  '
  

  # use whisker to swap out {{{mustache}}} placeholders with parameters
  query_exec(
    whisker.render(query,params),
    project=whisker.render('{{{project}}}', params),
    destination_table = whisker.render(destination_table,params),
    use_legacy_sql = FALSE
  )
  
}

# create an invocation query to provide sets of parameters to create_results_table

invocation_query <- '
  SELECT
    "<YOUR PROJECT HERE>" as project,
    "<YOUR DATASET_ID HERE>" as dataset_id,
    "<YOUR TABLE PREFIX HERE>" as table_prefix,
    num as year_low,
    num+1 as year_high
  FROM `bigquery-public-data.common_us.num_999999`
  WHERE num BETWEEN 1992 AND 1995
'

# call condusco's run_pipeline_gbq to iteratively run create_results_table over invocation_query's results

run_pipeline_gbq(
  create_results_table,
  invocation_query,
  project = '<YOUR PROJECT HERE>',
  use_legacy_sql = FALSE
)