polarssql backend for dbplyr

Use simulate_polarssql() with dbplyr::tbl_lazy() or dbplyr::lazy_frame() to see simulated SQL without converting to live access. tbl_polarssql() is similar to dbplyr::tbl_memdb(), but the backend is Polars instead of SQLite. It uses polarssql_default_connection() as the DBI connection.

Usage

simulate_polarssql()

tbl_polarssql(df, name = deparse(substitute(df)), ..., overwrite = FALSE)

Arguments

df

Data frame to copy

name

Name of table in database: defaults to a random name that's unlikely to conflict with an existing table.

...

Ignored.

overwrite

If TRUE, overwrite the existing table which has the same name. If not TRUE (default), skip writing a table if it already exists.

Examples

library(dplyr, warn.conflicts = FALSE)

# Test connection shows the SQL query.
dbplyr::tbl_lazy(mtcars, simulate_polarssql(), name = "mtcars") |>
  filter(cyl == 4) |>
  arrange(desc(mpg)) |>
  select(contains("c")) |>
  head(n = 3)
#> <SQL>
#> SELECT `cyl`, `qsec`, `carb`
#> FROM `mtcars`
#> WHERE (`cyl` = 4.0)
#> ORDER BY `mpg` DESC
#> LIMIT 3

# Actual polarssql connection shows the Polars naive plan (LazyFrame).
tbl_polarssql(mtcars) |>
  filter(cyl == 4) |>
  arrange(desc(mpg)) |>
  select(contains("c")) |>
  head(n = 3)
#> polars LazyFrame
#>  $describe_optimized_plan() : Show the optimized query plan.
#> 
#> Naive plan:
#> SLICE[offset: 0, len: 3]
#>    SELECT [col("cyl"), col("qsec"), col("carb")] FROM
#>     SORT BY [col("mpg")]
#>        WITH_COLUMNS:
#>        [col("cyl"), col("qsec"), col("carb")]
#>         FILTER [(col("cyl")) == (4.0)] FROM
#> 
#>         DF ["mpg", "cyl", "disp", "hp"]; PROJECT */11 COLUMNS; SELECTION: "None"

# Unlike other dbplyr backends, `compute` has a special behavior.
# It returns a polars DataFrame.
tbl_polarssql(mtcars) |>
  filter(cyl == 4) |>
  arrange(desc(mpg)) |>
  select(contains("c")) |>
  head(n = 3) |>
  compute()
#> shape: (3, 3)
#> ┌─────┬───────┬──────┐
#> │ cyl ┆ qsec  ┆ carb │
#> │ --- ┆ ---   ┆ ---  │
#> │ f64 ┆ f64   ┆ f64  │
#> ╞═════╪═══════╪══════╡
#> │ 4.0 ┆ 19.9  ┆ 1.0  │
#> │ 4.0 ┆ 19.47 ┆ 1.0  │
#> │ 4.0 ┆ 18.52 ┆ 2.0  │
#> └─────┴───────┴──────┘