1

I am trying to work out if there is a way to pass parameters to the sql chunks connected to BigQuery in RMarkdown:

```{r setup}
library(bigrquery)
bq_auth(path = "access_token.json")
db <- dbConnect(
  bigquery(), 
  dataset = 'my_data', 
  project = 'my-project', 
  use_legacy_sql = FALSE
)

parameter_value = 10L
```

```{sql, echo=FALSE, connection=db, output.var="x}
SELECT @parameter_value
```

```{r}
print(x)
# I want to see 10 here.
```

See BigQuery parameterised query documentation here - https://cloud.google.com/bigquery/docs/parameterized-queries

Update 1

Though ?parameter_value injections seems to work fine for scalars, it does not apply to vectors, e.g.:

  ```{r}
  parameter_value = c(10L, 20L)
  ```

  ```{sql, echo=FALSE, connection=db, output.var="x}
    SELECT UNNEST(?parameter_value)
  ```

will fail with:

Error in vapply(values, function(x) dbQuoteLiteral(conn, x), character(1)) : values must be length 1, but FUN(X[1]) result is length 2

Event with scalars it is not using BigQuery engine to parameterise the query.

Update 2

I think it will not be possible to do this right now, because some of the DBI APIs are not implemented in bigrquery package, which I have raised an issue for. And knitr package here

Bulat
  • 6,869
  • 1
  • 29
  • 52
  • I don't know much about `bigquery` (nor have I experience using `sql` chunks) but is it possible that you need to pass a properly formatted string as `parameter_value`? Something like `paste(c(10, 20), collapse = ", ")` or whatever syntax SQL expects? – CL. Jun 24 '20 at 07:26
  • Thumb up this https://github.com/r-dbi/bigrquery/issues/444 if you would like to use queries directly in the markdown – Bulat Nov 02 '22 at 10:47

3 Answers3

1

You can use ? to insert R variables in the sql chunck, see using R variables in queries

    ```{sql connection=db,output.vars="x"}
    SELECT ?parameter_value
    ```

print(x)
<int>
10
Waldi
  • 39,242
  • 6
  • 30
  • 78
  • `Error in UseMethod("as_bq_table") : no applicable method for 'as_bq_table' applied to an object of class "NULL"` – Bulat Jun 22 '20 at 12:30
  • or `' failed x Syntax error: Unexpected "@" at [1:9] [invalidQuery]` – Bulat Jun 22 '20 at 12:31
  • I have updated your answer to a version that worked for me – Bulat Jun 22 '20 at 12:37
  • As I don't have a BigQuery connection, I extrapolated what works perfectly on SQL Server and though this would work because R replaces the ?parameter_value by its R value before sending the query. Is SELECT @parameter_value a valid query in BigQuery? Can you try if the DECLARE part without SELECT throws an error? – Waldi Jun 22 '20 at 12:37
  • Happy that you could adapt it! what is the final BQ syntax? – Waldi Jun 22 '20 at 12:38
  • DECLARE probably has different syntax in BigQuery, but it is not needed anyway here. – Bulat Jun 22 '20 at 12:39
0

Just to clarify, you are using r chunks, right? according to this example it is possible.

```{r}
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
```

```{sql, connection=db}
SELECT * FROM trials
```
Daniel_j_iii
  • 3,041
  • 2
  • 11
  • 27
  • I need to do it with parameters for BigQuery https://cloud.google.com/bigquery/docs/parameterized-queries. – Bulat Jun 21 '20 at 14:26
0

I'm not sure about bigquery, but this works with PostgreSQL. You need glue::glue_sql() to pass few parameters. Discussed here

```{r }
parameter_value <- c(10L, 20L)
parameter_value = glue::glue_sql("{parameter_value*}", .con = wcon)
```

```{sql, connection=wcon, output.var="x"}
select * from t
where t.price in (?parameter_value)
limit 10
```
Yuriy Barvinchenko
  • 1,465
  • 1
  • 12
  • 17