dbplyr: db-sql – R documentation

Pricing

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

Get Started for Free

Documentation

dbplyr

db-sql

SQL generation generics

Description

SQL translation:

sql_expr_matches(con, x, y) generates an alternative to x = y when a pair of NULLs should match. The default translation uses a CASE WHEN as described in https://modern-sql.com/feature/is-distinct-from.
sql_translation(con) generates a SQL translation environment.

Tables:

sql_table_analyze(con, table) generates SQL that "analyzes" the table, ensuring that the database has up-to-date statistics for use in the query planner. It called from copy_to() when analyze = TRUE.
sql_table_index() generates SQL for adding an index to table. The

Query manipulation:

sql_query_explain(con, sql) generates SQL that "explains" a query, i.e. generates a query plan describing what indexes etc that the database will use.
sql_query_fields() generates SQL for a 0-row result that is used to capture field names in tbl_sql()
sql_query_save(con, sql) generates SQL for saving a query into a (temporary) table.
sql_query_wrap(con, from) generates SQL for wrapping a query into a subquery.

Query generation:

sql_query_select() generate SQL for a SELECT query
sql_query_join() generate SQL for joins
sql_query_semi_join() generate SQL for semi- and anti-joins
sql_query_set_op() generate SQL for UNION, INTERSECT, and EXCEPT queries.

Usage

sql_expr_matches(con, x, y)

sql_translation(con)

sql_table_analyze(con, table, ...)

sql_table_index(con, table, columns, name = NULL, unique = FALSE, ...)

sql_query_explain(con, sql, ...)

sql_query_fields(con, sql, ...)

sql_query_save(con, sql, name, temporary = TRUE, ...)

sql_query_wrap(con, from, name = unique_subquery_name(), ...)

sql_query_rows(con, sql, ...)

sql_query_select(
  con,
  select,
  from,
  where = NULL,
  group_by = NULL,
  having = NULL,
  order_by = NULL,
  limit = NULL,
  distinct = FALSE,
  ...,
  subquery = FALSE
)

sql_query_join(
  con,
  x,
  y,
  vars,
  type = "inner",
  by = NULL,
  na_matches = FALSE,
  ...
)

sql_query_semi_join(con, x, y, anti = FALSE, by = NULL, ...)

sql_query_set_op(con, x, y, method, ..., all = FALSE)

dbplyr 2.0.0

Many dplyr::db_* generics have been replaced by dbplyr::sql_* generics. To update your backend, you'll need to extract the SQL generation out of your existing code, and place it in a new method for a dbplyr sql_ generic.

dplyr::db_analyze() is replaced by dbplyr::sql_table_analyze()
dplyr::db_explain() is replaced by dbplyr::sql_query_explain()
dplyr::db_create_index() is replaced by dbplyr::sql_table_index()
dplyr::db_query_fields() is replaced by dbplyr::sql_query_fields()
dplyr::db_query_rows() is no longer used; you can delete it
dplyr::db_save_query() is replaced by dbplyr::sql_query_save()

The query generating functions have also changed names. Their behaviour is unchanged, so you just need to rename the generic and import from dbplyr instead of dplyr.

dplyr::sql_select() is replaced by dbplyr::sql_query_select()
dplyr::sql_join() is replaced by dbplyr::sql_query_join()
dplyr::sql_semi_join() is replaced by dbplyr::sql_query_semi_join()
dplyr::sql_set_op() is replaced by dbplyr::sql_query_set_op()
dplyr::sql_subquery() is replaced by dbplyr::sql_query_wrap()

Learn more in vignette("backend-2.0")

db-sql

Description

Usage

dbplyr 2.0.0

See Also

dbplyr

We don't support your browser anymore