Function reference • dbplyr

dplyr verbs

Connecting, copying, and retrieving data

tbl(<src_dbi>): Use dplyr verbs with a remote database table

copy_to(<src_sql>): Copy a local data frame to a remote database

copy_inline(): Use a local data frame in a dbplyr query

collapse(<tbl_sql>) compute(<tbl_sql>) collect(<tbl_sql>): Compute results of a query

pull(<tbl_sql>): Extract a single column

Verbs that affect rows

arrange(<tbl_lazy>): Arrange rows by column values

distinct(<tbl_lazy>): Subset distinct/unique rows

filter(<tbl_lazy>): Subset rows using column values

head(<tbl_lazy>): Subset the first rows

slice_min(<tbl_lazy>) slice_max(<tbl_lazy>) slice_sample(<tbl_lazy>): Subset rows using their positions

Verbs that affect columns

mutate(<tbl_lazy>): Create, modify, and delete columns

select(<tbl_lazy>) rename(<tbl_lazy>) rename_with(<tbl_lazy>) relocate(<tbl_lazy>): Subset, rename, and reorder columns using their names

Grouping and summarising verbs

count(<tbl_lazy>) add_count(<tbl_lazy>) tally(<tbl_lazy>): Count observations by group

group_by(<tbl_lazy>): Group by one or more variables

summarise(<tbl_lazy>): Summarise each group to one row

do(<tbl_sql>): Perform arbitrary computation on remote backend

Verbs that work with multiple tables

inner_join(<tbl_lazy>) left_join(<tbl_lazy>) right_join(<tbl_lazy>) full_join(<tbl_lazy>) cross_join(<tbl_lazy>) semi_join(<tbl_lazy>) anti_join(<tbl_lazy>): Join SQL tables

intersect(<tbl_lazy>) union(<tbl_lazy>) union_all(<tbl_lazy>) setdiff(<tbl_lazy>): SQL set operations

Verbs that modify the underlying data

rows_insert(<tbl_lazy>) rows_append(<tbl_lazy>) rows_update(<tbl_lazy>) rows_patch(<tbl_lazy>) rows_upsert(<tbl_lazy>) rows_delete(<tbl_lazy>): Edit individual rows in the underlying database table

get_returned_rows() has_returned_rows(): Extract and check the RETURNING rows

tidyr verbs

complete(<tbl_lazy>): Complete a SQL table with missing combinations of data

dbplyr_uncount(): "Uncount" a database table

expand(<tbl_lazy>): Expand SQL tables to include all possible combinations of values

fill(<tbl_lazy>): Fill in missing values with previous or next value

pivot_longer(<tbl_lazy>): Pivot data from wide to long

pivot_wider(<tbl_lazy>) dbplyr_pivot_wider_spec(): Pivot data from long to wide

replace_na(<tbl_lazy>): Replace NAs with specified values

Built-in database backends

simulate_access(): Backend: MS Access

simulate_hana(): Backend: SAP HANA

simulate_hive(): Backend: Hive

simulate_impala(): Backend: Impala

simulate_mssql(): Backend: SQL server

simulate_mysql() simulate_mariadb(): Backend: MySQL/MariaDB

simulate_odbc(): Backend: ODBC

simulate_oracle(): Backend: Oracle

simulate_postgres(): Backend: PostgreSQL

simulate_redshift(): Backend: Redshift

simulate_snowflake(): Backend: Snowflake

simulate_spark_sql(): Backend: Databricks Spark SQL

simulate_sqlite(): Backend: SQLite

simulate_teradata(): Backend: Teradata

Database connection

memdb_frame() tbl_memdb() src_memdb(): Create a database table in temporary in-memory database.

remote_name() remote_table() remote_src() remote_con() remote_query() remote_query_plan(): Metadata about a remote table

SQL generation

build_sql(): Build a SQL string.

escape() escape_ansi() sql_vector(): Escape/quote a string.

partial_eval(): Partially evaluate an expression.

sql() is.sql() as.sql(): SQL escaping.

sql_expr() sql_call2(): Generate SQL from R expressions

translate_sql() translate_sql_(): Translate an expression to SQL

window_order() window_frame(): Override window order and frame

sql_options(): Options for generating SQL

dbplyr Backends

Documentation for authors of new dbplyr backends

db_copy_to() db_compute() db_collect() db_table_temporary(): Database I/O generics

db_connection_describe() sql_join_suffix() db_sql_render() db_col_types() dbplyr_edition(): Miscellaneous database generics

sql_expr_matches() sql_translation() sql_random() sql_table_analyze() sql_table_index() sql_query_explain() sql_query_fields() sql_query_save() sql_query_wrap() sql_indent_subquery() sql_query_rows() supports_window_clause() db_supports_table_alias_with_as() sql_query_select() sql_query_join() sql_query_multi_join() sql_query_semi_join() sql_query_set_op() sql_query_union() sql_returning_cols(): SQL generation generics

sql_escape_logical() sql_escape_date() sql_escape_datetime() sql_escape_raw(): SQL escaping/quoting generics

sql_query_insert() sql_query_append() sql_query_update_from() sql_query_upsert() sql_query_delete(): Generate SQL for Insert, Update, Upsert, and Delete