Skip to content
DuckDB community extension

dplyr

R dplyr pipeline syntax support for DuckDB - transpiles dplyr verbs to SQL

01 / SETUP

Install and load

Run these commands in a Haybarn SQL session. The extension is available from the community repository.

Haybarn 2.0.0 Β· SQL
INSTALL dplyr FROM community;
LOAD dplyr FROM community;

Try it in a temporary SQL session with dplyr already loaded. Queries run in this tab; examples using files or services need their own setup.

Using upstream DuckDB? Follow the maintainer’s installation instructions.

02 / QUERY

Usage example

Transform R dplyr pipeline syntax directly in DuckDB

SQL session and output
-- Transform R dplyr pipeline syntax directly in DuckDB
-- First, create a sample table
CREATE TABLE iris AS SELECT * FROM (VALUES
  (5.1, 3.5, 1.4, 0.2, 'setosa'),
  (4.9, 3.0, 1.4, 0.2, 'setosa'),
  (7.0, 3.2, 4.7, 1.4, 'versicolor'),
  (6.4, 3.2, 4.5, 1.5, 'versicolor'),
  (6.3, 3.3, 6.0, 2.5, 'virginica'),
  (5.8, 2.7, 5.1, 1.9, 'virginica')
) AS t(sepal_length, sepal_width, petal_length, petal_width, species);

-- Run a pipeline directly as a statement (parser extension)
iris %>%
  filter(sepal_length > 5) %>%
  select(species, sepal_length, petal_length) %>%
  arrange(desc(sepal_length));
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚  species   β”‚ sepal_length β”‚ petal_length β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ versicolor β”‚          7.0 β”‚          4.7 β”‚
β”‚ versicolor β”‚          6.4 β”‚          4.5 β”‚
β”‚ virginica  β”‚          6.3 β”‚          6.0 β”‚
β”‚ virginica  β”‚          5.8 β”‚          5.1 β”‚
β”‚ setosa     β”‚          5.1 β”‚          1.4 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

-- Group and summarize with dplyr syntax
iris %>%
  group_by(species) %>%
  summarise(avg_sepal = mean(sepal_length), count = n());
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”
  β”‚ avg_sepal β”‚ count β”‚
  β”‚  double   β”‚ int64 β”‚
  β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€
  β”‚      6.05 β”‚     2 β”‚
  β”‚       5.0 β”‚     2 β”‚
  β”‚       6.7 β”‚     2 β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”˜

-- Create derived columns with mutate
iris %>%
  mutate(sepal_ratio = sepal_length / sepal_width) %>%
  select(species, sepal_ratio) %>%
  arrange(sepal_ratio);

-- Embed a pipeline inside normal SQL using (| ... |)
SELECT species, COUNT(*) AS n
FROM (| iris %>% filter(sepal_length > 5) %>% select(species) |)
GROUP BY species
ORDER BY n DESC;

Provided by mrchypark. Not yet verified on Haybarn 2.0.0.

Sample data & setup

About this extension

dplyr is a DuckDB extension that brings R's popular dplyr package syntax to DuckDB. It transpiles dplyr pipeline syntax (using the %>% pipe operator) directly to SQL, allowing R users to write familiar data manipulation code in DuckDB.

Project overview from the community descriptor.

Documentation and resources

Project and build details
Repository
mrchypark/libdplyr
License
MIT
Maintainers
mrchypark
Written in
Rust & C++
Build source
53e70ca0e75d
Build definition
Community descriptor