Discuss DSL Design for R Client Translating dplyr Verbs to ODSQL Syntax

[ramiromagno]

DSL interface based on dplyr verbs

Before jumping into implementation, I'd like to start a design discussion around the idea of offering a DSL interface based on dplyr verbs, which would get translated internally into valid ODSQL query strings.

ODSQL is a SQL-inspired, URL-encoded query language, but using it directly via httr2 or manually building strings can be error-prone and unidiomatic for R users.

By designing a DSL that mirrors the tidyverse's data manipulation grammar, we can let users write code like this:

odsql("dataset_id") |>
  select(city, population) |>
  filter(population > 1000000) |>
  arrange(desc(population)) |>
  slice_head(n = 5) |>
  collect()

Which would internally generate:

GET /api/explore/v2.1/catalog/datasets/dataset_id/records?
  select=city,population&
  where=population>1000000&
  order_by=population desc&
  limit=5

Design Questions

1. Core verbs: Which dplyr verbs do we want to support out-of-the-box? Obvious candidates are:

• select()
• filter()
• group_by() + summarise()
• arrange()
• slice_head() for limit= possibly mutate() for things like year = year(date)

2. Custom expression translation: How do we handle things like startsWith(), in(), and date/time functions? Should we reimplement a small expression parser using {rlang}?

3. S3 query object: Should we introduce a class like odsql_query that holds the pipeline before it's collected via collect()?

4. Caching, error handling, metadata introspection: What should be built into the request pipeline vs delegated to user code?

Precedents

This kind of DSL translation has good grounding in the R ecosystem:

• {dbplyr} translates dplyr verbs to SQL for remote databases.
• {httr2} itself uses a piped DSL to build HTTP requests.

Worth looking into

• {sos4R}
• {ckanr}
• {eurostat}

[lucassantosbicalho]

Design Questions

Here are some thoughts that came to my mind while reading the design questions.

1. Core verbs: I agree with the verbs listed, and also I want to bring to discussion a very common thing while querying a database: rename fields. Specifically, when selecting fields on ODSQL, the documentations allows users use the AS keyword to rename, something like field1 AS my_field, field2. Maybe add rename() verb can be useful for those situations or we can just take advantage of the "renaming field while selecting" feature, like iris |> select(S = Species).
   An example:

odsql("dataset_id") |>
  select(cidade = city, populacao = population) |>
  filter(population > 1000000) |>
  arrange(desc(population)) |>
  slice_head(n = 5) |>
  collect()

Or

odsql("dataset_id") |>
  select(city, population) |>
  rename(cidade = city, populacao = population) |>
  filter(population > 1000000) |>
  arrange(desc(population)) |>
  slice_head(n = 5) |>
  collect()

That would Internally generate:

GET /api/explore/v2.1/catalog/datasets/dataset_id/records?
  select=city AS cidade,population AS populacao&
  where=population>1000000&
  order_by=population desc&
  limit=5

3. S3 query object: Holds the pipeline before collect data is an interesting way to do this. Since we have it, may be valid add methods like explain or show_query before the execution. May help debugging or even the user may want to see it.

For 2. Custom expression translation, a parser might be a good idea, but let's be careful to not make it too complex (thinking on future maintanence). For 4. Caching, error handling, metadata introspection I think we need to develop more the idea.

[ramiromagno]

Totally agree with the rename / as suggestion!

3. S3 query object: I think we can draw inspiration from how {httr2} does it; we might even extend their class httr2_request, see https://github.com/r-lib/httr2/blob/main/R/req.R.

[lucassantosbicalho]

Hey @ramiromagno,

I'm getting some progress, but still need to work more on details before making the PR.
Here's a brief of what I did:

• define the odsql object (S3)
• define select verb
• define rename verb
• define filter verb. Here with just basic operations yet, not Dates, or Spatial objects.
• define collect verb, when called it will call another function to build the query and store it on odsql object.

The way I thought, the collect will make request to the API after build the query. But I'd like to discuss this with you. What would be better: Build query within collect, or isolate it as build_query function? On first, the operation seems to be transparent to the user, and if the query has a problem, the Opendatasoft API will say it. On second, the user may want to see the query and not collect. Edit the query if needed, and just when they're sure of what to ask, call the collect. What you think?

Example of use:

odsql("iris") |> 
  select(Species, Sepal.Length, Sepal.Width, Petal.Length, Petal.Width) |> 
  filter(Sepal.Length >= 2.5) |> 
  rename(SL = Sepal.Length, SW = Sepal.Width, PL = Petal.Length, PW = Petal.Width) |> 
  collect() -> o

Then looking to the query attribute in o odsql object:

o$query
[1] "GET /api/explore/v2.1/catalog/datasets/iris/records?select=Species, Sepal.Length AS SL, Sepal.Width AS SW, Petal.Length AS PL, Petal.Width AS PW&where=(Sepal.Length >= 2.5)"

Equivalent to:

GET /api/explore/v2.1/catalog/datasets/iris/records?
 select=Species, Sepal.Length AS SL, Sepal.Width AS SW, Petal.Length AS PL, Petal.Width AS PW&
 where=(Sepal.Length >= 2.5)

Until now, the verbs do just basic operations. The Opendatasoft has more ways to select, or filter objects. First I'm working in make the basics work, and then add complexity later.