Package 'dbplyr'

Description

Use .sql$foo(x, y) to make it clear that you're calling the SQL foo() function, not the R foo() function. This also makes it easier to reduce ⁠R CMD check⁠ notes in packages; just import .sql from dbplyr with e.g. ⁠@importFrom dbplyr .sql⁠.

Note that .sql itself does nothing and is just NULL; it is automatically removed when dbplyr translates your R code to SQL.

Usage

.sql

Examples

library(dplyr, warn.conflicts = FALSE)

db <- lazy_frame(x = 1, y = 2)
db |> mutate(z = .sql$CUMULATIVE_SUM(x, 1))

Description

This is an method for the dplyr arrange() generic. It generates the ⁠ORDER BY⁠ clause of the SQL query. It also affects the window_order() of windowed expressions in mutate.tbl_lazy().

Note that ⁠ORDER BY⁠ clauses can not generally appear in subqueries, which means that you should arrange() as late as possible in your pipelines.

Usage

## S3 method for class 'tbl_lazy'
arrange(.data, ..., .by_group = FALSE)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Missing values

Unlike R, most databases sorts NA (NULLs) at the front. You can can override this behaviour by explicitly sorting on is.na(x).

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(a = c(3, 4, 1, 2), b = c(5, 1, 2, NA))
db |> arrange(a) |> show_query()

# Note that NAs are sorted first
db |> arrange(b)
# override by sorting on is.na() first
db |> arrange(is.na(b), b)

Description

This backend supports Microsoft Access databases, typically accessed via odbc. Use dialect_access() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• SELECT uses TOP, not LIMIT

• Non-standard types and mathematical functions

• String concatenation uses &

• No ANALYZE equivalent

• TRUE and FALSE converted to 1 and 0

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_access()

simulate_access()

Examples

library(dplyr, warn.conflicts = FALSE)
lf <- lazy_frame(x = 1, y = 2, z = "a", con = dialect_access())

lf |> head()
lf |> mutate(y = as.numeric(y), z = sqrt(x^2 + 10))
lf |> mutate(a = paste0(z, " times"))

Description

This backend supports databases accessed via AdbiConnection created by adbi::adbi() and DBI::dbConnect(). dbplyr automatically detects the underlying database type by querying the ADBC driver's vendor name and uses the appropriate SQL dialect.

The following vendors are recognized:

• PostgreSQL and PostgreSQL-compatible systems (CrateDB, CockroachDB, Citus, Neon, ParadeDB, TimescaleDB, Yellowbrick, YugabyteDB, CedarDB): dialect_postgres()

• MySQL and MySQL-compatible systems (MariaDB, TiDB, Vitess): the appropriate dialect (dialect_mariadb() for MariaDB, dialect_mysql() otherwise)

• SQLite: dialect_sqlite()

• Microsoft SQL Server: dialect_mssql()

• Snowflake: dialect_snowflake()

• Amazon Redshift: dialect_redshift()

If your database is not recognized, dbplyr will fall back to a generic ODBC dialect. In this case, or if dbplyr guesses wrong, you can use with_dialect() to choose a specific dialect.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Examples

# ADBC connections require the adbi package and an ADBC driver.
# Once connected, dbplyr automatically detects the database type:
#
# library(adbi)
# library(dplyr, warn.conflicts = FALSE)
#
# con <- DBI::dbConnect(adbi::adbi("adbcsqlite"), uri = ":memory:")
# tbl(con, "my_table") |> filter(x > 1)

Description

This is the base dialect for ANSI compliant SQL, forming the foundation of all other dialects. Use dialect_ansi() with lazy_frame() to see simulated SQL without connecting to a live database.

See vignette("translation-function") for a list of functions that are translated.

Usage

dialect_ansi()

simulate_dbi(class = character(), ...)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_ansi())
lf |> transmute(x = mean(b, na.rm = TRUE))
lf |> transmute(x = log(b), y = log(b, base = 2))

Description

This backend supports IBM DB2 databases, typically accessed via ODBC. Use dialect_db2() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Uses ⁠FETCH FIRST n ROWS ONLY⁠ instead of ⁠LIMIT n⁠

• Uses double quotes for identifier quoting

• paste() uses ||

• DB2-specific data type names for casts (e.g. VARCHAR(255), DOUBLE)

• Date component extraction via YEAR(), MONTH(), ..., DAYOFYEAR(), DAYOFWEEK(), QUARTER(), WEEK()

• str_flatten() uses LISTAGG

• Statistical summaries sd(), var(), cor(), cov()

• runif() translates to RAND()

• Regular expression functions (DB2 11.1+)

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_db2()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_db2())
lf |> head()
lf |> transmute(x = paste0(d, " times"))
lf |> summarise(x = sd(b, na.rm = TRUE))

Description

This backend supports SAP HANA databases, typically accessed via HDBConnection created by DBI::dbConnect(). Use dialect_hana() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Temporary tables get ⁠#⁠ prefix and use ⁠LOCAL TEMPORARY COLUMN⁠.

• No table analysis performed in copy_to().

• paste() uses ||

• Note that you can't create new boolean columns from logical expressions; you need to wrap with explicit ifelse: ifelse(x > y, TRUE, FALSE).

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_hana()

simulate_hana()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_hana())
lf |> transmute(x = paste0(d, " times"))

Description

This backend supports Apache Hive, typically accessed via odbc. Use dialect_hive() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are a scattering of custom translations provided by users.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_hive()

simulate_hive()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, d = 2, c = "z", con = dialect_hive())
lf |> transmute(x = cot(b))
lf |> transmute(x = bitwShiftL(c, 1L))
lf |> transmute(x = str_replace_all(c, "a", "b"))

lf |> summarise(x = median(d, na.rm = TRUE))
lf |> summarise(x = var(c, na.rm = TRUE))

Description

This backend supports Apache Impala, typically accessed via odbc. Use dialect_impala() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are a scattering of custom translations provided by users, mostly focussed on bitwise operations.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_impala()

simulate_impala()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_impala())
lf |> transmute(X = bitwNot(bitwOr(b, c)))

Description

This backend supports databases accessed via JDBCConnection created by RJDBC::JDBC() and DBI::dbConnect(). dbplyr automatically detects the underlying database type from the JDBC connection class and uses the appropriate SQL dialect.

The following databases are recognized via their JDBC connection class:

• PostgreSQL (⁠org.postgresql.*⁠): dialect_postgres()

• MySQL (⁠com.mysql.*⁠): dialect_mysql()

• MariaDB (⁠org.mariadb.*⁠): dialect_mariadb()

• SQLite (⁠org.sqlite.*⁠): dialect_sqlite()

• Oracle (⁠oracle.*⁠): dialect_oracle()

• SQL Server (⁠com.microsoft.sqlserver.*⁠): dialect_mssql()

• IBM DB2 (⁠com.ibm.db2.*⁠): dialect_db2()

• SAP HANA (⁠com.sap.db.*⁠): dialect_hana()

• Teradata (⁠com.teradata.*⁠): dialect_teradata()

• Apache Hive (⁠org.apache.hive.*⁠): dialect_hive()

• Apache Spark (⁠org.apache.spark.*⁠ or ⁠com.simba.spark.*⁠): dialect_spark_sql()

• Snowflake (⁠net.snowflake.*⁠): dialect_snowflake()

• Impala (⁠com.cloudera.impala.*⁠): dialect_impala()

• Amazon Redshift (⁠com.amazon.redshift.*⁠): dialect_redshift()

If your database is not recognized, dbplyr will fall back to a generic ODBC dialect. Please file an issue if you'd like support for additional databases.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

See Also

with_dialect() to use a different dialect if dbplyr guesses incorrectly, or a more specific translation is available.

Examples

# JDBC connections require the RJDBC package and a JDBC driver JAR file.
# Once connected, dbplyr automatically detects the database type:
#
# library(RJDBC)
# library(dplyr, warn.conflicts = FALSE)
#
# drv <- JDBC("org.postgresql.Driver", "postgresql.jar")
# con <- dbConnect(drv, "jdbc:postgresql://localhost/mydb", "user", "password")
# tbl(con, "my_table") |> filter(x > 1)

Description

This backend supports Microsoft SQL Server, typically accessed via odbc. Use dialect_mssql() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• SELECT uses TOP not LIMIT

• Automatically prefixes ⁠#⁠ to create temporary tables. Add the prefix yourself to avoid the message.

• String basics: paste(), substr(), nchar()

• Custom types for ⁠as.*⁠ functions

• Lubridate extraction functions, year(), month(), day() etc

• Semi-automated bit <-> boolean translation (see below)

• stringr functions str_detect(), str_starts(), str_ends() with fixed() patterns work on all versions; regular expression patterns require SQL Server 2025+ (version 17.0)

• stringr functions str_replace(), str_replace_all(), str_remove(), str_remove_all(), str_extract(), and str_count() require SQL Server 2025+ (version 17.0)

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_mssql(version = "15.0")

simulate_mssql(version = "15.0")

Arguments

Bit vs boolean

SQL server uses two incompatible types to represent TRUE and FALSE values:

• The BOOLEAN type is the result of logical comparisons (e.g. x > y) and can be used WHERE but not to create new columns in SELECT. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql

• The BIT type is a special type of numeric column used to store TRUE and FALSE values, but can't be used in WHERE clauses. https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15

dbplyr does its best to automatically create the correct type when needed, but can't do it 100% correctly because it does not have a full type inference system. This means that you many need to manually do conversions from time to time.

• To convert from bit to boolean use x == 1

• To convert from boolean to bit use ⁠as.logical(if(x, 0, 1))⁠

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_mssql())
lf |> head()
lf |> transmute(x = paste(b, c, d))

# Can use boolean as is:
lf |> filter(c > d)
# Need to convert from boolean to bit:
lf |> transmute(x = c > d)
# Can use boolean as is:
lf |> transmute(x = ifelse(c > d, "c", "d"))

Description

This backend supports MySQL and MariaDB databases, typically accessed via MySQLConnection or MariaDBConnection created by DBI::dbConnect(). Use dialect_mysql() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• paste() uses CONCAT_WS()

• String translations for str_detect(), str_locate(), and str_replace_all()

• Clear error message for unsupported full joins

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_mariadb()

dialect_mysql()

simulate_mysql()

simulate_mariadb()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_mysql())
lf |> transmute(x = paste0(d, " times"))

Description

This backend supports databases accessed via OdbcConnection created by DBI::dbConnect(). Use dialect_odbc() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are minor translations for common data types.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_odbc()

simulate_odbc()

See Also

with_dialect() to use a different dialect if dbplyr guesses incorrectly, or a more specific translation is available.

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, d = 2, c = "z", con = dialect_odbc())
lf |> transmute(x = as.numeric(b))
lf |> transmute(x = as.integer(b))
lf |> transmute(x = as.character(b))

Description

This backend supports Oracle databases, typically accessed via OraConnection created by DBI::dbConnect(). Use dialect_oracle() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Use ⁠FETCH FIRST⁠ instead of LIMIT

• Custom types

• paste() uses ||

• Custom subquery generation (no AS)

• setdiff() uses MINUS instead of EXCEPT

Note that versions of Oracle prior to 23c have limited supported for TRUE and FALSE and you may need to use 1 and 0 instead. See https://oracle-base.com/articles/23/boolean-data-type-23 for more details.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_oracle()

simulate_oracle()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_oracle())
lf |> transmute(x = paste0(c, " times"))
lf |> setdiff(lf)

Description

This backend supports PostgreSQL databases, typically accessed via a PqConnection created by DBI::dbConnect(). Use dialect_postgres() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Many stringr functions

• lubridate date-time extraction functions

• More standard statistical summaries

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_postgres()

simulate_postgres()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_postgres())
lf |> summarise(x = sd(b, na.rm = TRUE))
lf |> summarise(y = cor(b, c), z = cov(b, c))

Description

This backend supports Amazon Redshift databases, typically accessed via a RedshiftConnection created by DBI::dbConnect(). Use dialect_redshift() with lazy_frame() to see simulated SQL without connecting to a live database.

Base translations come from PostgreSQL backend. There are generally few differences, apart from string manipulation.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_redshift()

simulate_redshift()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_redshift())
lf |> transmute(x = paste(c, " times"))
lf |> transmute(x = substr(c, 2, 3))
lf |> transmute(x = str_replace_all(c, "a", "z"))

Description

This backend supports Snowflake databases, typically accessed via odbc. Use dialect_snowflake() with lazy_frame() to see simulated SQL without connecting to a live database.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_snowflake()

simulate_snowflake()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_snowflake())
lf |> transmute(x = paste0(d, " times"))

Description

This backend supports Databricks Spark SQL, typically accessed via the Databricks ODBC or JDBC connector. Use dialect_spark_sql() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are better translation of statistical aggregate functions (e.g. var(), median()) and use of temporary views instead of temporary tables when copying data.

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_spark_sql()

simulate_spark_sql()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, d = 2, c = "z", con = dialect_spark_sql())

lf |> summarise(x = median(d, na.rm = TRUE))
lf |> summarise(x = var(c, na.rm = TRUE), .by = d)

lf |> mutate(x = first(c))
lf |> mutate(x = first(c), .by = d)

Description

This backend supports SQLite databases, typically accessed via a SQLiteConnection created by DBI::dbConnect(). Use dialect_sqlite() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Uses non-standard LOG() function

• Date-time extraction functions from lubridate

• Custom median translation

• Right and full joins are simulated using left joins

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_sqlite()

simulate_sqlite()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_sqlite())
lf |> transmute(x = paste(c, " times"))
lf |> transmute(x = log(b), y = log(b, base = 2))

Description

This backend supports Teradata databases, typically accessed via odbc. Use dialect_teradata() with lazy_frame() to see simulated SQL without connecting to a live database.

Key differences for this backend are:

• Uses TOP instead of LIMIT

• Selection of user supplied translations

See vignette("translation-function") and vignette("translation-verb") for details of overall translation technology.

Usage

dialect_teradata()

simulate_teradata()

Examples

library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(a = TRUE, b = 1, c = 2, d = "z", con = dialect_teradata())
lf |> head()

Description

Combine multiple lazy queries into a single query using ⁠UNION ALL⁠. This is a convenient wrapper around purrr::reduce(tables, union_all). Like dplyr::bind_rows() (and unlike ⁠UNION ALL⁠), bind_queries() will automatically align columns based on their name, and fill in any missing columns with missing values.

Usage

bind_queries(...)

Arguments

Value

A lazy query.

Examples

lf1 <- lazy_frame(x = 1, y = "a")
lf2 <- lazy_frame(x = 2, y = "b")
bind_queries(lf1, lf2)

lf3 <- lazy_frame(y = "c", x = 3, z = 10)
bind_queries(lf2, lf3)

# If you already have a list, you can use splice operator
queries <- list(lf1, lf2)
bind_queries(!!!queries)

Description

collapse() forces computation of a lazy query by wrapping it in a subquery. This is not generally needed, but can be useful if you need to work around database/dbplyr limitations.

Usage

## S3 method for class 'tbl_sql'
collapse(x, ...)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(a = c(3, 4, 1, 2), b = c(5, 1, 2, NA))
db |> filter(a <= 2) |> show_query()
db |> filter(a <= 2) |> collapse() |> show_query()

Description

collect() executes the query and retrieves the results into a local tibble. This brings all the data from the database into R's memory, which is useful once you've done as much as possible in the database, and now need to use R functions.

Usage

## S3 method for class 'tbl_sql'
collect(
  x,
  ...,
  n = Inf,
  warn_incomplete = TRUE,
  sql_options = NULL,
  cte = deprecated()
)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(a = c(3, 4, 1, 2), b = c(5, 1, 2, NA))
db |> filter(a <= 2) |> collect()

Description

Turns implicit missing values into explicit missing values. This is a method for the tidyr::complete() generic.

Usage

## S3 method for class 'tbl_lazy'
complete(data, ..., fill = list())

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

df <- memdb_frame(
  group = c(1:2, 1),
  item_id = c(1:2, 2),
  item_name = c("a", "b", "b"),
  value1 = 1:3,
  value2 = 4:6
)

df |> tidyr::complete(group, nesting(item_id, item_name))

# You can also choose to fill in missing values
df |> tidyr::complete(group, nesting(item_id, item_name), fill = list(value1 = 0))

Description

compute() executes the query and stores the results in a new remote table. This is useful when you want to cache intermediate results for reuse or to improve performance by avoiding repeated computation of complex queries.

Usage

## S3 method for class 'tbl_sql'
compute(
  x,
  name = NULL,
  temporary = TRUE,
  overwrite = FALSE,
  unique_indexes = list(),
  indexes = list(),
  analyze = TRUE,
  ...,
  sql_options = NULL,
  cte = deprecated()
)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(a = c(3, 4, 1, 2), b = c(5, 1, 2, NA))
db |> filter(a <= 2) |> show_query()
db |> filter(a <= 2) |> compute() |> show_query()

Description

This is an alternative to copy_to() that does not need write access and is faster for small data.

Usage

copy_inline(con, df, types = NULL)

Arguments

Details

It writes the data directly in the SQL query via the VALUES clause.

Value

A tbl_lazy.

See Also

copy_to() to copy the data into a new database table.

Examples

df <- data.frame(x = 1:3, y = c("a", "b", "c"))
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

copy_inline(con, df)

copy_inline(con, df) |> dplyr::show_query()

Description

This is an implementation of the dplyr copy_to() generic and it mostly a wrapper around DBI::dbWriteTable().

It is useful for copying small amounts of data to a database for examples, experiments, and joins. By default, it creates temporary tables which are only visible within the current connection to the database.

Usage

## S3 method for class 'src_sql'
copy_to(
  dest,
  df,
  name = deparse(substitute(df)),
  overwrite = FALSE,
  types = NULL,
  temporary = TRUE,
  unique_indexes = NULL,
  indexes = NULL,
  analyze = TRUE,
  ...,
  in_transaction = TRUE
)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

See Also

copy_inline() to use small data in an SQL query without actually writing to a table.

Examples

library(dplyr, warn.conflicts = FALSE)

df <- data.frame(x = 1:5, y = letters[5:1])
db <- copy_to(memdb(), df)
db

df2 <- data.frame(y = c("a", "d"), fruit = c("apple", "date"))
# copy_to() is called automatically if you set copy = TRUE
# in the join functions
db |> left_join(df2, copy = TRUE)

Description

These are methods for the dplyr dplyr::count() and tally() generics. They wrap up group_by.tbl_lazy(), summarise.tbl_lazy() and, optionally, arrange.tbl_lazy().

Usage

## S3 method for class 'tbl_lazy'
count(x, ..., wt = NULL, sort = FALSE, name = NULL)

## S3 method for class 'tbl_lazy'
add_count(x, ..., wt = NULL, sort = FALSE, name = NULL, .drop = NULL)

## S3 method for class 'tbl_lazy'
tally(x, wt = NULL, sort = FALSE, name = NULL)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(g = c(1, 1, 1, 2, 2), x = c(4, 3, 6, 9, 2))
db |> count(g) |> show_query()
db |> count(g, wt = x) |> show_query()
db |> count(g, wt = x, sort = TRUE) |> show_query()

Description

This is a method for the tidyr uncount() generic. It uses a temporary table, so your database user needs permissions to create one.

Usage

dbplyr_uncount(data, weights, .remove = TRUE, .id = NULL)

Arguments

Examples

df <- memdb_frame(x = c("a", "b"), n = c(1, 2))
dbplyr_uncount(df, n)
dbplyr_uncount(df, n, .id = "id")

# You can also use constants
dbplyr_uncount(df, 2)

# Or expressions
dbplyr_uncount(df, 2 / n)

Description

These are methods for the dplyr generics slice_min(), slice_max(), and slice_sample(). They are translated to SQL using dplyr::filter() and window functions (ROWNUMBER, MIN_RANK, or CUME_DIST depending on arguments). slice(), slice_head(), and slice_tail() are not supported since database tables have no intrinsic order.

If data is grouped, the operation will be performed on each group so that (e.g.) slice_min(db, x, n = 3) will select the three rows with the smallest value of x in each group.

Usage

## S3 method for class 'tbl_lazy'
slice_min(
  .data,
  order_by,
  ...,
  n,
  prop,
  by = NULL,
  with_ties = TRUE,
  na_rm = TRUE
)

## S3 method for class 'tbl_lazy'
slice_max(
  .data,
  order_by,
  ...,
  n,
  by = NULL,
  prop,
  with_ties = TRUE,
  na_rm = TRUE
)

## S3 method for class 'tbl_lazy'
slice_sample(.data, ..., n, prop, by = NULL, weight_by = NULL, replace = FALSE)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = 1:3, y = c(1, 1, 2))
db |> slice_min(x) |> show_query()
db |> slice_max(x) |> show_query()
db |> slice_sample() |> show_query()

db |> group_by(y) |> slice_min(x) |> show_query()

# By default, ties are includes so you may get more rows
# than you expect
db |> slice_min(y, n = 1)
db |> slice_min(y, n = 1, with_ties = FALSE)

# Non-integer group sizes are rounded down
db |> slice_min(x, prop = 0.5)

Description

This is a method for the dplyr distinct() generic. It adds the DISTINCT clause to the SQL query.

Usage

## S3 method for class 'tbl_lazy'
distinct(.data, ..., .keep_all = FALSE)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = c(1, 1, 2, 2), y = c(1, 2, 1, 1))
db |> distinct() |> show_query()
db |> distinct(x) |> show_query()

Description

do() is deprecated. Instead of do() you should use collect() and then your favourite combination of purrr and dplyr functions.

Usage

## S3 method for class 'tbl_sql'
do(.data, ..., .chunk_size = 10000L)

Arguments

Description

escape() turns R values into SQL literals. It implements double dispatch via two sets of generics: first escape() dispatches on the class of x, then that method calls sql_escape_ident(), sql_escape_logical(), etc, which dispatch on con.

These generics translate individual values into SQL. The core generics are DBI::dbQuoteIdentifier() and DBI::dbQuoteString() for quoting identifiers and strings, but dbplyr needs additional tools for inserting logical, date, date-time, and raw values into queries.

Usage

escape(x, parens = NA, collapse = " ", con = NULL)

sql_escape_ident(con, x)

sql_escape_logical(con, x)

sql_escape_date(con, x)

sql_escape_datetime(con, x)

sql_escape_string(con, x)

sql_escape_raw(con, x)

sql_vector(x, parens = NA, collapse = " ", con = NULL)

Arguments

Value

A sql vector.

See Also

Other generic: db-sql, db_connection_describe(), db_copy_to()

Examples

con <- dialect_ansi()

# Doubles vs. integers
escape(1:5, con = con)
escape(c(1, 5.4), con = con)

# String vs known sql vs. sql identifier
escape("X", con = con)
escape(sql("X"), con = con)
escape(ident("X"), con = con)

# Escaping is idempotent
escape("X", con = con)
escape(escape("X", con = con), con = con)

# Database specific generics
sql_escape_logical(con, c(TRUE, FALSE, NA))
sql_escape_date(con, Sys.Date())
sql_escape_date(con, Sys.time())
sql_escape_raw(con, charToRaw("hi"))

Description

This is a method for the tidyr::expand generics. It doesn't sort the result explicitly, so the order might be different to what expand() returns for data frames.

Usage

## S3 method for class 'tbl_lazy'
expand(data, ..., .name_repair = "check_unique")

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

fruits <- memdb_frame(
  type   = c("apple", "orange", "apple", "orange", "orange", "orange"),
  year   = c(2010, 2010, 2012, 2010, 2010, 2012),
  size = c("XS", "S",  "M", "S", "S", "M"),
  weights = rnorm(6)
)

# All possible combinations ---------------------------------------
fruits |> tidyr::expand(type)
fruits |> tidyr::expand(type, size)

# Only combinations that already appear in the data ---------------
fruits |> tidyr::expand(nesting(type, size))

Description

Fill in missing values with previous or next value

Usage

## S3 method for class 'tbl_lazy'
fill(.data, ..., .direction = c("down", "up", "updown", "downup"))

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

squirrels <- tibble::tribble(
  ~group,    ~name,     ~role,     ~n_squirrels, ~ n_squirrels2,
  1,      "Sam",    "Observer",   NA,                 1,
  1,     "Mara", "Scorekeeper",    8,                NA,
  1,    "Jesse",    "Observer",   NA,                NA,
  1,      "Tom",    "Observer",   NA,                 4,
  2,     "Mike",    "Observer",   NA,                NA,
  2,  "Rachael",    "Observer",   NA,                 6,
  2,  "Sydekea", "Scorekeeper",   14,                NA,
  2, "Gabriela",    "Observer",   NA,                NA,
  3,  "Derrick",    "Observer",   NA,                NA,
  3,     "Kara", "Scorekeeper",    9,                 10,
  3,    "Emily",    "Observer",   NA,                NA,
  3, "Danielle",    "Observer",   NA,                NA
)
squirrels$id <- 1:12

squirrels_db <- copy_to(memdb(), squirrels)
squirrels_db |>
  window_order(id) |>
  tidyr::fill(n_squirrels, n_squirrels2)

Description

These are methods for the dplyr dplyr::filter() and dplyr::filter_out() generics. They generate the WHERE clause of the SQL query.

filter() is translated directly to WHERE, which already matches dplyr's behaviour of treating NA like FALSE (SQL's three-valued logic drops NULL rows from WHERE).

filter_out() requires an additional step, where the combined condition is wrapped in is_distinct_from(., TRUE), which is then translated using the backend (e.g. to ⁠IS DISTINCT FROM⁠ on PostgreSQL, ⁠IS NOT⁠ on SQLite). This ensures that the SQL translation matches dplyr's semantics.

Usage

## S3 method for class 'tbl_lazy'
filter(.data, ..., .by = NULL, .preserve = FALSE)

## S3 method for class 'tbl_lazy'
filter_out(.data, ..., .by = NULL, .preserve = FALSE)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = c(2, NA, 5, NA, 10), y = 1:5)
db |> filter(x < 5) |> show_query()
db |> filter_out(x < 5) |> show_query()
db |> filter(is.na(x)) |> show_query()

Description

get_returned_rows() extracts the RETURNING rows produced by rows_insert(), rows_append(), rows_update(), rows_upsert(), or rows_delete() if these are called with the returning argument. An error is raised if this information is not available.

has_returned_rows() checks if x has stored RETURNING rows produced by rows_insert(), rows_append(), rows_update(), rows_upsert(), or rows_delete().

Usage

get_returned_rows(x)

has_returned_rows(x)

Arguments

Value

For get_returned_rows(), a tibble.

For has_returned_rows(), a scalar logical.

Examples

library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbExecute(con, "CREATE TABLE Info (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   number INTEGER
)")
info <- tbl(con, "Info")

rows1 <- copy_inline(con, data.frame(number = c(1, 5)))
rows_insert(info, rows1, conflict = "ignore", in_place = TRUE)
info

# If the table has an auto incrementing primary key, you can use
# the returning argument + `get_returned_rows()` its value
rows2 <- copy_inline(con, data.frame(number = c(13, 27)))
info <- rows_insert(
  info,
  rows2,
  conflict = "ignore",
  in_place = TRUE,
  returning = id
)
info
get_returned_rows(info)

Description

This is a method for the dplyr group_by() generic. It is translated to the ⁠GROUP BY⁠ clause of the SQL query when used with summarise() and to the ⁠PARTITION BY⁠ clause of window functions when used with mutate().

Usage

## S3 method for class 'tbl_lazy'
group_by(.data, ..., .add = FALSE, .drop = TRUE)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(g = c(1, 1, 1, 2, 2), x = c(4, 3, 6, 9, 2))
db |>
  group_by(g) |>
  summarise(n()) |>
  show_query()

db |>
  group_by(g) |>
  mutate(x2 = x / sum(x, na.rm = TRUE)) |>
  show_query()

Description

This is a method for the head() generic. It is usually translated to the LIMIT clause of the SQL query. Because LIMIT is not an official part of the SQL specification, some database use other clauses like TOP or ⁠FETCH ROWS⁠.

Note that databases don't really have a sense of row order, so what "first" means is subject to interpretation. Most databases will respect ordering performed with arrange(), but it's not guaranteed. tail() is not supported at all because the situation is even murkier for the "last" rows. Additionally, LIMIT clauses can not generally appear in subqueries, which means that you should use head() as late as possible in your pipelines.

Usage

## S3 method for class 'tbl_lazy'
head(x, n = 6L, ...)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = 1:100)
db |> head() |> show_query()

# Pretend we have data in a SQL server database
db2 <- lazy_frame(x = 1:100, con = dialect_mssql())
db2 |> head() |> show_query()

Description

These are methods for the dplyr generics dplyr::intersect(), dplyr::union(), and dplyr::setdiff(). They are translated to INTERSECT, UNION, and EXCEPT respectively.

Usage

## S3 method for class 'tbl_lazy'
intersect(x, y, copy = "none", ..., all = FALSE)

## S3 method for class 'tbl_lazy'
union(x, y, copy = "none", ..., all = FALSE)

## S3 method for class 'tbl_lazy'
union_all(x, y, copy = "none", ...)

## S3 method for class 'tbl_lazy'
setdiff(x, y, copy = "none", ..., all = FALSE)

Arguments

Description

These are methods for the dplyr join generics. They are translated to the following SQL queries:

• inner_join(x, y): ⁠SELECT * FROM x JOIN y ON x.a = y.a⁠

• left_join(x, y): ⁠SELECT * FROM x LEFT JOIN y ON x.a = y.a⁠

• right_join(x, y): ⁠SELECT * FROM x RIGHT JOIN y ON x.a = y.a⁠

• full_join(x, y): ⁠SELECT * FROM x FULL JOIN y ON x.a = y.a⁠

• semi_join(x, y): ⁠SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WHERE x.a = y.a)⁠

• anti_join(x, y): ⁠SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a)⁠

Usage

## S3 method for class 'tbl_lazy'
inner_join(
  x,
  y,
  by = NULL,
  copy = "none",
  suffix = NULL,
  ...,
  keep = NULL,
  na_matches = c("never", "na"),
  multiple = NULL,
  unmatched = "drop",
  relationship = NULL,
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
left_join(
  x,
  y,
  by = NULL,
  copy = "none",
  suffix = NULL,
  ...,
  keep = NULL,
  na_matches = c("never", "na"),
  multiple = NULL,
  unmatched = "drop",
  relationship = NULL,
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
right_join(
  x,
  y,
  by = NULL,
  copy = "none",
  suffix = NULL,
  ...,
  keep = NULL,
  na_matches = c("never", "na"),
  multiple = NULL,
  unmatched = "drop",
  relationship = NULL,
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
full_join(
  x,
  y,
  by = NULL,
  copy = "none",
  suffix = NULL,
  ...,
  keep = NULL,
  na_matches = c("never", "na"),
  multiple = NULL,
  relationship = NULL,
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
cross_join(
  x,
  y,
  ...,
  copy = "none",
  suffix = c(".x", ".y"),
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
semi_join(
  x,
  y,
  by = NULL,
  copy = "none",
  ...,
  na_matches = c("never", "na"),
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

## S3 method for class 'tbl_lazy'
anti_join(
  x,
  y,
  by = NULL,
  copy = "none",
  ...,
  na_matches = c("never", "na"),
  sql_on = NULL,
  auto_index = FALSE,
  x_as = NULL,
  y_as = NULL
)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

library(dplyr, warn.conflicts = FALSE)

band_db <- copy_to(memdb(), dplyr::band_members)
instrument_db <- copy_to(memdb(), dplyr::band_instruments)
band_db |> left_join(instrument_db) |> show_query()

# Can join with local data frames by setting copy = TRUE
band_db |>
  left_join(dplyr::band_instruments, copy = TRUE)

# Unlike R, joins in SQL don't usually match NAs (NULLs)
db <- memdb_frame(x = c(1, 2, NA))
label <- memdb_frame(x = c(1, NA), label = c("one", "missing"))
db |> left_join(label, by = "x")
# But you can activate R's usual behaviour with the na_matches argument
db |> left_join(label, by = "x", na_matches = "na")

# By default, joins are equijoins, but you can use `sql_on` to
# express richer relationships
db1 <- memdb_frame(x = 1:5)
db2 <- memdb_frame(x = 1:3, y = letters[1:3])
db1 |> left_join(db2) |> show_query()
db1 |> left_join(db2, sql_on = "LHS.x < RHS.x") |> show_query()

Description

This is a helper function that retrieves the most recent SQL query generated by dbplyr, which can be useful for debugging.

Usage

last_sql()

Value

A SQL string, or NULL if no query has been generated yet.

Examples

library(dplyr, warn.conflicts = FALSE)

df <- lazy_frame(x = 1:3)
df |> filter(x > 1)

last_sql()

Description

memdb() creates a temporary in-memory database that disappears when the R session ends. It's a convenient way to learn about and experiment with dbplyr without having to connect to a "real" database.

memdb_frame() works like tibble::tibble(), but instead of creating a new data frame in R, it creates a table in memdb(). local_memdb_frame() is like memdb_frame() but the table will be automatically deleted when the current scope ends. It's useful for tests. But beware: this function will overwrite an existing table of the same name.

Usage

memdb()

memdb_frame(.name = unique_table_name(), ...)

local_memdb_frame(.name = unique_table_name(), ..., frame = caller_env())

Arguments

Examples

library(dplyr)

# use memdb_frame() to create a new database table
df <- memdb_frame(x = runif(100), y = runif(100))
df |> arrange(x)
df |> arrange(x) |> show_query()

# Use memdb() + copy_to() to copy an existing data frame
iris_db <- copy_to(memdb(), iris)
iris_db

Description

These are methods for the dplyr mutate() and transmute() generics. They are translated to computed expressions in the SELECT clause of the SQL query.

Usage

## S3 method for class 'tbl_lazy'
mutate(
  .data,
  ...,
  .by = NULL,
  .order = NULL,
  .frame = NULL,
  .keep = c("all", "used", "unused", "none"),
  .before = NULL,
  .after = NULL
)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = 1:5, y = 5:1)
db |>
  mutate(a = (x + y) / 2, b = sqrt(x^2L + y^2L)) |>
  show_query()

# dbplyr automatically creates subqueries as needed
db |>
  mutate(x1 = x + 1, x2 = x1 * 2) |>
  show_query()

# `.order` and `.frame` control window functions
db <- memdb_frame(g = c(1, 1, 2, 2, 2), x = c(5, 3, 1, 4, 2))
db |>
  mutate(rolling_sum = sum(x), .by = g, .order = x, .frame = c(-2, 2)) |>
  show_query()

Description

pivot_longer() "lengthens" data, increasing the number of rows and decreasing the number of columns. The inverse transformation is tidyr::pivot_wider().

Learn more in vignette("pivot", "tidyr").

While most functionality is identical there are some differences to pivot_longer() on local data frames:

• the output is sorted differently/not explicitly,

• the coercion of mixed column types is left to the database,

• values_ptypes NOT supported.

Note that build_longer_spec() and pivot_longer_spec() do not work with remote tables.

Usage

## S3 method for class 'tbl_lazy'
pivot_longer(
  data,
  cols,
  ...,
  cols_vary,
  names_to = "name",
  names_prefix = NULL,
  names_sep = NULL,
  names_pattern = NULL,
  names_ptypes = NULL,
  names_transform = NULL,
  names_repair = "check_unique",
  values_to = "value",
  values_drop_na = FALSE,
  values_ptypes,
  values_transform = NULL
)

Arguments

Details

The SQL translation basically works as follows:

1. split the specification by its key columns i.e. by variables crammed into the column names.

2. for each part in the split specification transmute() data into the following columns

• id columns i.e. columns that are not pivotted

• key columns

• value columns i.e. columns that are pivotted

1. combine all the parts with union_all()

Examples

# See vignette("pivot") for examples and explanation

# Simplest case where column names are character data
memdb_frame(
  id = c("a", "b"),
  x = 1:2,
  y = 3:4
) |>
  tidyr::pivot_longer(-id)

Description

pivot_wider() "widens" data, increasing the number of columns and decreasing the number of rows. The inverse transformation is pivot_longer(). Learn more in vignette("pivot", "tidyr").

pivot_wider() on database tables comes with some caveats, please make sure to read below for details.

Usage

## S3 method for class 'tbl_lazy'
pivot_wider(
  data,
  ...,
  id_cols = NULL,
  id_expand = FALSE,
  names_from = name,
  names_prefix = "",
  names_sep = "_",
  names_glue = NULL,
  names_sort = FALSE,
  names_vary = "fastest",
  names_expand = FALSE,
  names_repair = "check_unique",
  values_from = value,
  values_fill = NULL,
  values_fn = ~max(.x, na.rm = TRUE),
  unused_fn = NULL
)

dbplyr_pivot_wider_spec(
  data,
  spec,
  ...,
  names_repair = "check_unique",
  id_cols = NULL,
  id_expand = FALSE,
  values_fill = NULL,
  values_fn = ~max(.x, na.rm = TRUE),
  unused_fn = NULL,
  error_call = current_env()
)

Arguments

Caveats

pivot_wider() is eager

Note that pivot_wider() cannot be lazy because we need to look at the data to figure out what the new column names will be. If you have a long-running query you have two options:

• Temporarily store the result of the query via compute().

• Create a spec before and use dbplyr_pivot_wider_spec() - dbplyr's version of tidyr::pivot_wider_spec().

You must supply values_fn

The big difference to pivot_wider() for local data frames is that values_fn must not be NULL. By default it is max() which yields the same results as for local data frames if three conditions are true:

1. The combination of id_cols and value uniquely identify an observation.

2. The column has a comparable type (e.g. numeric, date-time, or (for most databases) string).

3. values_fill is NULL.

If either the second or third condition is not met, you must supply a custom values_fn. Unfortunately there is no generally available alternative and you'll need to look for something database specific, like FIRST() or ANY_VALUE().

How does it work?

The translation to SQL code basically works as follows:

1. Get unique keys in names_from column.

2. For each key value generate an expression of the form:

   values_fn(
     CASE WHEN (`names from column` == `key value`) THEN (`value column`) END
   ) AS `output column`
   

3. Group data by id columns.

4. Summarise the grouped data with the expressions from step 2.

Examples

memdb_frame(
  id = 1,
  key = c("x", "y"),
  value = 1:2
) |>
  tidyr::pivot_wider(
    id_cols = id,
    names_from = key,
    values_from = value
  )

Description

This is a method for the dplyr pull() generic. It evaluates the query retrieving just the specified column.

Usage

## S3 method for class 'tbl_sql'
pull(.data, var = -1, name = NULL, ...)

Arguments

Value

A vector of data.

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = 1:5, y = 5:1)
db |>
  mutate(z = x + y * 2) |>
  pull()

Description

remote_name() gives the unescaped name of the remote table, or NULL if it is a query (created by sql()) or already escape (created by ident_q()). remote_table() gives the remote table or the query. remote_query() gives the text of the query, and remote_query_plan() the query plan (as computed by the remote database). remote_src() and remote_con() give the dplyr source and DBI connection respectively.

Usage

remote_name(x, null_if_local = TRUE)

remote_table(x, null_if_local = TRUE)

remote_src(x)

remote_con(x)

remote_query(x, sql_options = NULL, cte = deprecated())

remote_query_plan(x, ...)

Arguments

Value

• remote_name() returns a string, or NULL if not a remote table, or not applicable. For example, computed queries do not have a "name".

• remote_table() returns a sql() object containing the (correctly quoted) table identifier, or the custom query if the table was created from sql(). Returns NULL if applied to a lazy query that no longer corresponds to a single table (e.g. after a filter() or a join), or if applied to a local lazy table.

Examples

mf <- memdb_frame(x = 1:5, y = 5:1, .name = "blorp")
remote_name(mf)
remote_src(mf)
remote_con(mf)
remote_query(mf)

mf2 <- dplyr::filter(mf, x > 3)
remote_name(mf2)
remote_src(mf2)
remote_con(mf2)
remote_query(mf2)

Description

This is a method for the tidyr::replace_na() generic.

Usage

## S3 method for class 'tbl_lazy'
replace_na(data, replace = list(), ...)

Arguments

Value

Another tbl_lazy. Use show_query() to see the generated query, and use collect() to execute the query and return data to R.

Examples

df <- memdb_frame(x = c(1, 2, NA), y = c("a", NA, "b"))
df |> tidyr::replace_na(list(x = 0, y = "unknown"))

Description

These are methods for the dplyr rows_insert(), rows_append(), rows_update(), rows_patch(), rows_upsert(), and rows_delete() generics.

When in_place = TRUE these verbs do not generate SELECT queries, but instead directly modify the underlying data using INSERT, UPDATE, or DELETE operators. This will require that you have write access to the database: the connection needs permission to insert, modify or delete rows, but not to alter the structure of the table.

The default, in_place = FALSE, generates equivalent lazy tables (using SELECT queries) that allow previewing the result without actually modifying the underlying table on the database.

Usage

## S3 method for class 'tbl_lazy'
rows_insert(
  x,
  y,
  by = NULL,
  ...,
  conflict = c("error", "ignore"),
  copy = "none",
  in_place = FALSE,
  returning = NULL,
  method = NULL
)

## S3 method for class 'tbl_lazy'
rows_append(x, y, ..., copy = "none", in_place = FALSE, returning = NULL)

## S3 method for class 'tbl_lazy'
rows_update(
  x,
  y,
  by = NULL,
  ...,
  unmatched = c("error", "ignore"),
  copy = "none",
  in_place = FALSE,
  returning = NULL
)

## S3 method for class 'tbl_lazy'
rows_patch(
  x,
  y,
  by = NULL,
  ...,
  unmatched = c("error", "ignore"),
  copy = "none",
  in_place = FALSE,
  returning = NULL
)

## S3 method for class 'tbl_lazy'
rows_upsert(
  x,
  y,
  by = NULL,
  ...,
  copy = "none",
  in_place = FALSE,
  returning = NULL,
  method = NULL
)

## S3 method for class 'tbl_lazy'
rows_delete(
  x,
  y,
  by = NULL,
  ...,
  unmatched = c("error", "ignore"),
  copy = "none",
  in_place = FALSE,
  returning = NULL
)

Arguments

Value

A new tbl_lazy of the modified data. With in_place = FALSE, the result is a lazy query that prints visibly, because the purpose of this operation is to preview the results. With in_place = TRUE, x is returned invisibly, because the purpose of this operation is the side effect of modifying rows in the table behind x.

Examples

library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbExecute(con, "CREATE TABLE Ponies (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT,
   cutie_mark TEXT
)")

ponies <- tbl(con, "Ponies")

applejack <- copy_inline(con, data.frame(
  name = "Apple Jack",
  cutie_mark = "three apples"
))

# The default behavior is to generate a SELECT query
rows_insert(ponies, applejack, conflict = "ignore")
# And the original table is left unchanged:
ponies

# You can also choose to modify the table with in_place = TRUE:
rows_insert(ponies, applejack, conflict = "ignore", in_place = TRUE)
# In this case `rows_insert()` returns nothing and the underlying
# data is modified
ponies

Description

These are methods for the dplyr select(), rename(), and relocate() generics. They generate the SELECT clause of the SQL query.

These functions do not support predicate functions, i.e. you can not use where(is.numeric) to select all numeric variables.

Usage

## S3 method for class 'tbl_lazy'
select(.data, ...)

## S3 method for class 'tbl_lazy'
rename(.data, ...)

## S3 method for class 'tbl_lazy'
rename_with(.data, .fn, .cols = everything(), ...)

## S3 method for class 'tbl_lazy'
relocate(.data, ..., .before = NULL, .after = NULL)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)

db <- memdb_frame(x = 1, y = 2, z = 3)
db |> select(-y) |> show_query()
db |> relocate(z) |> show_query()
db |> rename(first = x, last = z) |> show_query()

Description

show_sql() displays the SQL query that will be dispatched to the database; explain() displays both the SQL query and the query plan.

Usage

## S3 method for class 'tbl_lazy'
show_query(x, ..., use_colour = TRUE, sql_options = NULL, cte = deprecated())

## S3 method for class 'tbl_sql'
explain(x, ...)

Arguments

Description

Use sql() to declare that a string is literal SQL and should be used as is, without quoting.

Usage

sql(...)

is.sql(x)

Arguments

Examples

library(dplyr, warn.conflicts = FALSE)
# sql() just adds a class
sql("x + 1")
is.sql(sql("x + 1"))

# You can use it when you need to insert some literal SQL in a query
db <- memdb_frame(x = 1:3)
db |> mutate(y = sql("CAST(x as VARCHAR)"))

Description

The dialect system allows multiple database connection classes to share SQL generation code. A dialect object encapsulates the SQL syntax rules for a particular database, independent of the connection mechanism.

• sql_dialect() returns the dialect for a connection. For connections that haven't implemented a dialect method, returns the connection itself for backward compatibility.

• new_sql_dialect() creates a new dialect object. This is primarily intended for dbplyr backend authors.

Usage

sql_dialect(con)

new_sql_dialect(
  dialect,
  quote_identifier,
  has_window_clause = FALSE,
  has_table_alias_with_as = TRUE,
  has_star_table_prefix = FALSE
)

Arguments

Value

• sql_dialect() returns a dialect object (class sql_dialect) or the connection itself for backward compatibility.

• new_sql_dialect() returns a dialect object with class c("sql_dialect_{name}", "sql_dialect").

Dispatching on dialect

For backward compatibility, all sql_ generics (and a handful of others) call sql_dialect() on the con argument in order to dispatch further on the dialect object, if possible:

sql_generic <- function(con, arg1, arg2, ...) {
  UseMethod("sql_generic", sql_dialect(con))
}

Unfortunately, due to the way that UseMethod() works, this uses sql_dialect(con) to control which method is selected, but still passes the original con to the method. This means that if you are implementing a method for a dialect and need to access dialect properties, you must call sql_dialect(con) again inside the method.

Examples

# Create a custom dialect
my_dialect <- new_sql_dialect(
  "custom",
  quote_identifier = function(x) sql_quote(x, "`"),
  has_window_clause = TRUE
)
class(my_dialect)

Description

sql_glue() and sql_glue2() are designed to help dbplyr extenders generate custom SQL. They differ only in whether or not they require a connection. sql_glue() retrieves the ambient connection, making it suitable for use inside sql_translation() methods; sql_glue2() requires a connection, making it suitable for use inside all other sql_ methods.

As the name suggests, these functions use glue syntax to make it easy to mix fixed SQL with varying user inputs. The default glue syntax, {x}, will escape x using the database connection. If there are multiple values in x, they'll be collapsed into a single string with ⁠,⁠. If you want them to be wrapped in ⁠()⁠, use a * suffix, e.g. ⁠{x*}⁠.

You can also use type markers to control how the value is treated:

• ⁠{.sql x}⁠: x is literal SQL that should be interpolated as is, without additional escaping. x must be a string.

• ⁠{.tbl x}⁠: x is a table identifier like a string, I(), or one of the older forms like DBI::Id() or in_schema().

• ⁠{.id x}⁠: x is a generic identifier, e.g. for a column or index. x must be a character vector

Usage

sql_glue(sql, envir = parent.frame())

sql_glue2(con, sql, envir = parent.frame())

Arguments

Value

An SQL string.

Examples

con <- dialect_ansi()

tbl <- "my_table"
sql_glue2(con, "SELECT * FROM {.tbl tbl}")

# Values are properly escaped
name <- "Robert'); DROP TABLE students;--"
sql_glue2(con, "INSERT INTO students (name) VALUES ({name})")

# Control wrapping with *
x <- c("name", "age", "grade")
sql_glue2(con, "SELECT {.id x} FROM students")
sql_glue2(con, "SELECT * WHERE variable IN {x*}")

Description

Customise SQL generation with these three options.

Usage

sql_options(cte = FALSE, use_star = TRUE, qualify_all_columns = FALSE)

Arguments

Value

A <dbplyr_sql_options> object.

Examples

library(dplyr, warn.conflicts = FALSE)
lf1 <- lazy_frame(key = 1, a = 1, b = 2)
lf2 <- lazy_frame(key = 1, a = 1, c = 3)

result <- left_join(lf1, lf2, by = "key") |>
  filter(c >= 3)

show_query(result)
sql_options <- sql_options(cte = TRUE, qualify_all_columns = TRUE)
show_query(result, sql_options = sql_options)

Description

These functions generate the SQL used in rows_*(in_place = TRUE).

Usage

sql_query_insert(
  con,
  table,
  from,
  insert_cols,
  by,
  ...,
  conflict = c("error", "ignore"),
  returning_cols = NULL,
  method = NULL
)

sql_query_append(con, table, from, insert_cols, ..., returning_cols = NULL)

sql_query_update_from(
  con,
  table,
  from,
  by,
  update_values,
  ...,
  returning_cols = NULL
)

sql_query_upsert(
  con,
  table,
  from,
  by,
  update_cols,
  ...,
  returning_cols = NULL,
  method = NULL
)

sql_query_delete(con, table, from, by, ..., returning_cols = NULL)

Arguments

Details

Insert Methods

"where_not_exists"

The default for most databases.

INSERT INTO x_name
SELECT *
FROM y
WHERE NOT EXISTS <match on by columns>

"on_conflict"

Supported by:

• Postgres

• SQLite

This method uses the ⁠ON CONFLICT⁠ clause and therefore requires a unique index on the columns specified in by.

Upsert Methods

"merge"

The upsert method according to the SQL standard. It uses the MERGE statement

MERGE INTO x_name
USING y
  ON <match on by columns>
WHEN MATCHED THEN
  UPDATE SET ...
WHEN NOT MATCHED THEN
  INSERT ...

"on_conflict"

Supported by:

• Postgres

• SQLite

This method uses the ⁠ON CONFLICT⁠ clause and therefore requires a unique index on the columns specified in by.

"cte_update"

Supported by:

• Postgres

• SQLite

• Oracle

The classical way to upsert in Postgres and SQLite before support for ⁠ON CONFLICT⁠ was added. The update is done in a CTE clause and the unmatched values are then inserted outside of the CTE.

Value

A SQL query.

Examples

sql_query_upsert(
  con = dialect_postgres(),
  table = "airlines",
  from = "df",
  by = "carrier",
  update_cols = "name"
)

Description

These functions help you create custom aggregate SQL translations when implementing a new backend. They are typically used within sql_translator() to define how R aggregate functions should be translated to SQL.

• sql_aggregate() creates a SQL aggregate function translator for functions with a single argument and an optional na.rm parameter (e.g., SUM(), AVG()).

• sql_aggregate_2() creates a SQL aggregate function translator for functions with exactly two arguments (e.g., CORR(), COVAR_SAMP()).

• sql_aggregate_n() creates a SQL aggregate function translator for functions with any number of arguments and an optional na.rm parameter (e.g., LEAST(), GREATEST()).

• sql_not_supported() creates a function that throws an informative error when a function is not supported in SQL.

• sql_check_na_rm() is a helper that you can use in aggregate functions to direct the user towards setting na.rm = TRUE.

Usage

sql_aggregate(f, f_r = f)

sql_aggregate_2(f)

sql_aggregate_n(f, f_r = f)

sql_check_na_rm(na.rm)

sql_not_supported(f)

Arguments

See Also

Other SQL translation helpers: sql_translation_scalar, sql_translation_string, sql_translation_window, sql_variant()

Description

These functions help you create custom scalar SQL translations when implementing a new backend. They are typically used within sql_translator() to define how R functions should be translated to SQL.

• sql_infix() creates SQL infix operators like +, -, *, /.

• sql_prefix() creates SQL prefix functions like ABS(), SQRT().

• sql_cast() creates SQL cast expressions like ⁠CAST(x AS type)⁠.

• sql_try_cast() creates SQL try_cast expressions (for safe casting).

• sql_log() creates a SQL logarithm function with optional base.

• sql_cot() creates a SQL cotangent function (as 1 / TAN(x)).

• sql_runif() creates a SQL expression for generating uniform random numbers.

Usage

sql_infix(f, pad = TRUE)

sql_prefix(f, n = NULL)

sql_cast(type)

sql_try_cast(type)

sql_log()

sql_cot()

sql_runif(rand_expr, n = n(), min = 0, max = 1)

Arguments

See Also

Other SQL translation helpers: sql_translation_agg, sql_translation_string, sql_translation_window, sql_variant()

Description

These functions help you create custom string SQL translations when implementing a new backend. They are typically used within sql_translator() to define how R string functions should be translated to SQL.

• sql_substr() creates a SQL substring function translator that converts R's substr(x, start, stop) to SQL's SUBSTR(x, start, length).

• sql_str_sub() creates a SQL substring function translator that handles stringr's str_sub() with support for negative indices.

• sql_paste() creates a SQL paste function using CONCAT_WS() or similar.

• sql_paste_infix() creates a SQL paste function using an infix operator like ||.

Usage

sql_substr(f = "SUBSTR")

sql_str_sub(subset_f = "SUBSTR", length_f = "LENGTH", optional_length = TRUE)

sql_paste(default_sep, f = "CONCAT_WS")

sql_paste_infix(default_sep, op, cast = sql_cast("text"))

Arguments

See Also

Other SQL translation helpers: sql_translation_agg, sql_translation_scalar, sql_translation_window, sql_variant()

Description

These functions help you create custom window SQL translations when implementing a new backend. They are typically used within sql_translator() to define how R window functions should be translated to SQL.

• win_over() makes it easy to generate the window function specification.

• win_absent(), win_rank(), win_aggregate(), and win_cumulative() provide helpers for constructing common types of window functions.

• win_current_group() and win_current_order() allow you to access the grouping and order context set up by group_by() and arrange().

Usage

win_over(
  expr,
  partition = NULL,
  order = NULL,
  frame = NULL,
  con = sql_current_con()
)

win_rank(f, empty_order = FALSE)

win_aggregate(f)

win_aggregate_2(f)

win_cumulative(f)

win_absent(f)

win_current_group()

win_current_order()

win_current_frame()

Arguments