How to use R with DuckDB (so you can turbo charge your data queries and handle big data effortlessly)
#rstats #bigdata #duckdb
1.
#rstats #bigdata #duckdb
1.
DuckDB is a fantastic tool currently seeing a rapid rise in the data world.
It is designed for rapid analytical queries and works brilliantly with big data.
Best of all you can use it directly in tools like #rstats, #python, #java and others.
Let's see how it works in R.
2.
It is designed for rapid analytical queries and works brilliantly with big data.
Best of all you can use it directly in tools like #rstats, #python, #java and others.
Let's see how it works in R.
2.
You can install DuckDB straight from the console with install.packages("duckdb").
Unlike some other big data tools it is entirely self-contained.
This means no extra ongoing maintenance - your IT department will thank you for that.
3.
Unlike some other big data tools it is entirely self-contained.
This means no extra ongoing maintenance - your IT department will thank you for that.
3.
Let's look at an example.
The data I'll use is NYC Taxi Trips Data from Google on Kaggle in CSV format. It contains 10 million rows and is 1.5GB. Is it big data? Not quite, I can still open in R with my laptop but it's not far off.
Data: kaggle.com/datasets/neilc…
4.
The data I'll use is NYC Taxi Trips Data from Google on Kaggle in CSV format. It contains 10 million rows and is 1.5GB. Is it big data? Not quite, I can still open in R with my laptop but it's not far off.
Data: kaggle.com/datasets/neilc…
4.
First we will create our database.
With a little SQL code inside the dbExecute function we create a table called trips and populate it with the taxis data from the CSV file.
The database is now ready to query.
5.
With a little SQL code inside the dbExecute function we create a table called trips and populate it with the taxis data from the CSV file.
The database is now ready to query.
5.
Now we can use dplyr syntax to query (or if you prefer pass SQL code to the function dbGetQuery).
For me it works rapidly, around 0.15 seconds. But how does that compare to a more standard approach?
6.
For me it works rapidly, around 0.15 seconds. But how does that compare to a more standard approach?
6.
Using vroom (which is no slouch) + dplyr it runs in 4.65 seconds. Though there is some overhead setting up the connection.
That's a massive difference when you start using big data with complicated processing.
This is just a flavour of DuckDB's capabilities.
7.
That's a massive difference when you start using big data with complicated processing.
This is just a flavour of DuckDB's capabilities.
7.
Links to code are here:
github.com/neilcuz/thread…
Code and links to my other threads:
github.com/neilcuz/thread…
8.
github.com/neilcuz/thread…
Code and links to my other threads:
github.com/neilcuz/thread…
8.
To recap:
- DuckDB is a brilliant data tool for writing fast analytical queries
- It handles big data with ease
- You can use it directly in #rstats (+ #python #java & others)
- It uses syntax you will be familiar with if you know a little dplyr
9.
- DuckDB is a brilliant data tool for writing fast analytical queries
- It handles big data with ease
- You can use it directly in #rstats (+ #python #java & others)
- It uses syntax you will be familiar with if you know a little dplyr
9.
I hope you've found this thread helpful. Follow me @neilgcurrie for more R and data tweets.
Like/Retweet the first tweet below if you can:
10.
Like/Retweet the first tweet below if you can:
10.
https://twitter.com/neilgcurrie/status/1567543254668955653
• • •
Missing some Tweet in this thread? You can try to
force a refresh
