Using sql chunks in Rmarkdown with query parameters

Question

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

Update 3

It is now possible to use params argument in the sql chunks.

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.
– CL., Commented Jun 24, 2020 at 7:26
Thumb up this github.com/r-dbi/bigrquery/issues/444 if you would like to use queries directly in the markdown — Bulat
– Bulat, Commented Nov 2, 2022 at 10:47

Waldi · Accepted Answer · 2021-02-05 21:08:46Z

2

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

edited Feb 5, 2021 at 21:08

answered Jun 22, 2020 at 10:23

Waldi

41.6k6 gold badges38 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bulat Over a year ago

Error in UseMethod("as_bq_table") : no applicable method for 'as_bq_table' applied to an object of class "NULL"

Bulat Over a year ago

or ' failed x Syntax error: Unexpected "@" at [1:9] [invalidQuery]

Bulat Over a year ago

I have updated your answer to a version that worked for me

Waldi Over a year ago

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 Over a year ago

Happy that you could adapt it! what is the final BQ syntax?

|

Daniel_j_iii · Accepted Answer · 2020-06-20 13:53:04Z

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
```

answered Jun 20, 2020 at 13:53

Daniel_j_iii

3,2643 gold badges14 silver badges29 bronze badges

1 Comment

Bulat Over a year ago

I need to do it with parameters for BigQuery cloud.google.com/bigquery/docs/parameterized-queries.

Yuriy Barvinchenko · Accepted Answer · 2020-11-29 17:20:47Z

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
```

edited Nov 29, 2020 at 17:20

answered Nov 28, 2020 at 16:23

Yuriy Barvinchenko

1,6151 gold badge14 silver badges19 bronze badges

2 Comments

Bulat Over a year ago

does this work with rmarkdown sql chunks?

Yuriy Barvinchenko Over a year ago

yes, it works. Updated answer, chunk was hidden by the interface

Collectives™ on Stack Overflow

Using sql chunks in Rmarkdown with query parameters

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related