Package index • dbplyr

dplyr verbs

Connecting, copying, and retrieving data

tbl(<src_dbi>): Create a lazy query backed by a database
collapse(<tbl_sql>): Collapse a query into a subquery
collect(<tbl_sql>): Collect results into a local data frame
compute(<tbl_sql>): Save results into a new remote 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
pull(<tbl_sql>): Extract a single column
show_query(<tbl_lazy>) explain(<tbl_sql>): Show generated SQL and query plan
last_sql(): Retrieve the last SQL query generated
.sql: Flag SQL function usage

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>) deprecated: Perform arbitrary computation on remote backend

Verbs that work with multiple tables

bind_queries(): Combine multiple lazy queries
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() experimental: 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() memdb_frame() local_memdb_frame(): A temporary in-memory database
remote_name() remote_table() remote_src() remote_con() remote_query() remote_query_plan(): Metadata about a remote table

SQL generation

escape() sql_escape_ident() sql_escape_logical() sql_escape_date() sql_escape_datetime() sql_escape_string() sql_escape_raw() sql_vector(): Escape/quote a value
sql() is.sql(): Literal SQL escaping
sql_glue() sql_glue2(): Build SQL strings with glue syntax
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_returning_cols() sql_query_multi_join() sql_query_join() sql_query_select() sql_query_semi_join() sql_query_set_op() sql_query_union(): SQL generation generics
escape() sql_escape_ident() sql_escape_logical() sql_escape_date() sql_escape_datetime() sql_escape_string() sql_escape_raw() sql_vector(): Escape/quote a value
sql_glue() sql_glue2(): Build SQL strings with glue syntax
sql_variant() sql_translator() base_scalar base_agg base_win base_no_win base_odbc_scalar base_odbc_agg base_odbc_win: Create an SQL translator
sql_query_insert() sql_query_append() sql_query_update_from() sql_query_upsert() sql_query_delete(): Generate SQL for Insert, Update, Upsert, and Delete
sql_infix() sql_prefix() sql_cast() sql_try_cast() sql_log() sql_cot() sql_runif(): SQL helpers for scalar functions
sql_substr() sql_str_sub() sql_paste() sql_paste_infix(): SQL helpers for string functions
sql_aggregate() sql_aggregate_2() sql_aggregate_n() sql_check_na_rm() sql_not_supported(): SQL helpers for aggregate functions
win_over() win_rank() win_aggregate() win_aggregate_2() win_cumulative() win_absent() win_current_group() win_current_order() win_current_frame(): SQL helpers for window functions