Ad-hoc analysis is an integral part of anyone in the data field. We have to on a regular basis combine data from various sources like CSV files, parquet files and databases for some ad-hoc testing or quick reporting. The most common tool at our disposal is Python's Pandas where we can read data from different sources into dataframes and then do the analysis. However, for someone who is more comfortable with SQL than Python, DuckDB is an excellent alternative. It lets you query data using SQL, without needing to load it into a database or convert it into a Pandas dataframe.
But first, what is DuckDB?
DuckDB is a modern, in-process analytical database. It supports a feature-rich SQL dialect and thanks to its columnar engine, it is blazingly fast. DuckDB is super quick to install (yes, you can get it up and running in less than a minute). Unlike traditional databases, it doesn't require a server, meaning you can embed it directly into your applications or run it directly on your local machine with minimal setup. It can read and write file formats such as CSV, Parquet, and JSON, to and from the local file system and remote endpoints such as S3 buckets. You can also pull data from databases like MySQL, BigQuery and others.
Why use DuckDB for Ad-Hoc Analysis?
If you're comfortable with SQL, DuckDB lets you connect to various sources and process data using SQL dialect. Given its in-memory columnar engine, it is extremely fast (yes, it leaves Pandas in the dust). And it can handle medium-sized data loads (up to a few GBs) comfortably on your local machine. Yes, it can handle larger-than-memory workloads by spilling to disk. I hope I have convinced you how cool DuckDB is.
If you prefer using an SQL client, DBeaver currently supports DuckDB. You can simply select DuckDB on the connection page on DBeaver and give some location on your local machine if you want persistent storage or run it in-memory completely by adding ":memory:" in the path. More detailed instructions . And the cherry on top is that you can save costs as well by pulling data to your local machine and processing it in DuckDB.
And you can read more on what is possible in DuckDB from their documentation here - https://duckdb.org/docs/
Do comment and share your thoughts on how you want to use DuckDB in your day-to-day work. Let's wait and watch how this amazing piece of technology will evolve in the coming years.
Thanks for reading. Do share any comments and feedback!
SOCIAL SHARE CARD GENERATOR