Picture by Writer
DuckDB is a quick, in-process analytical database designed for contemporary information evaluation. It runs immediately out of your Python script, which signifies that there isn’t a separate server wanted, and it excels at complicated queries due to its columnar storage and vectorized execution.
As understanding cope with information is changing into extra essential, at present I wish to present you construct a Python workflow with DuckDB and discover its key options.
Let’s dive in!
What Is DuckDB?
DuckDB is a free, open-source, in-process OLAP database constructed for quick, native analytics. In contrast to conventional databases that run as exterior companies, DuckDB runs inside your utility, with no server required. As an OLAP system, DuckDB shops information in columns (not rows like OLTP methods), making it extremely environment friendly for analytical queries comparable to joins, aggregations, and groupings.
Consider DuckDB as a light-weight, analytics-optimized model of SQLite, bringing the simplicity of native databases along with the ability of recent information warehousing. And this leads us to the next pure query…
What Are DuckDB’s Important Options?
Blazing-Quick Analytical Queries
DuckDB delivers spectacular efficiency for OLAP workloads, usually stunning customers aware of conventional databases like PostgreSQL. In contrast to standard OLAP methods that may be sluggish resulting from processing massive volumes of knowledge, DuckDB leverages a columnar, vectorized execution engine. This design optimizes CPU cache utilization and considerably accelerates analytical question efficiency.
Native SQL Help + Seamless Language Integration
DuckDB presents full help for complicated SQL queries and exposes APIs in a number of languages, together with Java, C, and C++. Its tight integration with Python and R makes it ultimate for interactive information evaluation. You possibly can write queries immediately in your most well-liked surroundings, with additional SQL syntax enhancements (e.g., EXCLUDE, REPLACE, and ALL) to simplify question writing.
And the very best half is that DuckDB is totally self-contained, with no exterior dependencies or setup complications.
Free And Open Supply
DuckDB is totally open-source and actively maintained by a rising group of contributors. This ensures fast function improvement and bug fixes. And sure, it’s free to make use of. Whereas future licensing modifications are at all times a chance, for now, you get a robust analytics engine at zero price.
Now that we all know its predominant options, let’s get began with it!
Getting Began With DuckDB
The set up course of for DuckDB relies upon barely in your surroundings, however total, it’s fast and easy. Since DuckDB is an embedded database engine with no server necessities or exterior dependencies, setup sometimes takes only a few traces of code. Yow will discover the whole set up information within the official DuckDB documentation.
Stipulations
Earlier than diving in, guarantee you might have the next:
- Python 3.13 or later put in
- A primary understanding of SQL and information evaluation in Python
You possibly can simply set up DuckDB in your surroundings by executing the next command:
Working With DuckDB in Python
When you’ve put in DuckDB, it’s fairly easy to get began. You merely import DuckDB into your surroundings, then hook up with an present database or create a brand new one if required.
For instance:
import duckdb
connection = duckdb.join()
If no database file is offered to the join() methodology, DuckDB will create a brand new in-memory database by default. That mentioned, the best technique to begin working SQL queries is through the use of the sql() methodology immediately.
# Supply: Primary API utilization - https://duckdb.org/docs/api/python/overview.html
import duckdb
duckdb.sql('SELECT 42').present()
Working this command initializes a world in-memory DuckDB occasion throughout the Python module and returns a relation, a symbolic illustration of the question.
Importantly, the question itself is not executed till you explicitly request the outcome, as proven under:
# Supply: Execute SQL - https://duckdb.org/docs/guides/python/execute_sql.html
outcomes = duckdb.sql('SELECT 42').fetchall()
print(outcomes)
"""
[(42,)]
"""
Let’s now work with some actual information. DuckDB helps a variety of file codecs, together with CSV, JSON, and Parquet, and loading them is straightforward.
You possibly can see how simple it’s within the instance under:
# Supply: Python API - https://duckdb.org/docs/api/python/overview.html
import duckdb
duckdb.read_csv('instance.csv') # learn a CSV file right into a Relation
duckdb.read_parquet('instance.parquet')# learn a Parquet file right into a Relation
duckdb.read_json('instance.json') # learn a JSON file right into a Relation
duckdb.sql('SELECT * FROM "instance.csv"') # immediately question a CSV file
Working With Exterior Information Sources In DuckDB
Certainly one of DuckDB’s standout options is its means to question exterior information recordsdata immediately, while not having to import them right into a database or load whole datasets into reminiscence. In contrast to conventional databases that require information to be ingested first, DuckDB helps a “zero-copy” execution mannequin, permitting it to learn solely the information required for a given question.
This strategy brings a number of key benefits:
- Minimal reminiscence utilization: Solely the related parts of the file are learn into reminiscence.
- No import/export overhead: Question your information in place—no want to maneuver or duplicate it.
- Streamlined workflows: Simply question throughout a number of recordsdata and codecs utilizing a single SQL assertion.
To exemplify the utilization of DuckDB, we can be utilizing a easy CSV file which you could receive from the next Kaggle hyperlink .
To question the information, we are able to simply outline a easy question that factors out to our file path.
# Question information immediately from a CSV file
outcome = duckdb.question(f"SELECT * FROM '{supply}'").fetchall()
print(outcome)
Now we are able to simply deal with information utilizing SQL-like logic immediately with DuckDB.
Filtering Rows
To give attention to particular subsets of knowledge, use the WHERE clause in DuckDB. It filters rows primarily based on situations utilizing comparability operators (>, <, =, <>, and so forth.) and logical operators (AND, OR, NOT) for extra complicated expressions.
# Choose solely college students with a rating above 80
outcome = duckdb.question(f"SELECT * FROM '{supply}' WHERE total_passengers > 500").fetchall()
outcome
Sorting Outcomes
Use the ORDER BY clause to type outcomes by a number of columns. It defaults to ascending (ASC), however you’ll be able to specify descending (DESC). To type by a number of columns, separate them with commas.
#Type months by variety of passengers
sorted_result = duckdb.question(f"SELECT * FROM '{supply}' ORDER BY total_passengers DESC ").fetchall()
print("nMonths sorted by complete visitors:")
print(sorted_result)
Including Calculated Columns
Create new columns in your question utilizing expressions and the AS key phrase. Use arithmetic operators or built-in capabilities to rework information—these columns seem within the outcomes however don’t have an effect on the unique file.
# Add 10 bonus factors to every rating
bonus_result = duckdb.question(f"""
SELECT
month,
total_passengers,
total_passengers/1000 AS traffic_in_thousands
FROM '{supply}'
""").fetchall()
print("nScores with 10 bonus factors:")
print(bonus_result)
Utilizing CASE Expressions
For extra complicated transformations, SQL supplies the CASE expression. This works equally to if-else statements in programming languages, permitting you to use conditional logic in your queries.
segmented_result = duckdb.question(f"""
SELECT
month,
total_passengers,
CASE
WHEN total_passengers >= 100 THEN 'HIGH'
WHEN total_passengers >= 50 THEN 'MEDIUM'
ELSE 'LOW'
END AS affluency
FROM '{supply}'
""").fetchall()
print("nMonth by affluency of passangers")
print(segmented_result)
Conclusion
DuckDB is a high-performance OLAP database constructed for information professionals who have to discover and analyze massive datasets effectively. Its in-process SQL engine runs complicated analytical queries immediately in your surroundings — no separate server is required. With seamless help for Python, R, Java, C++, and extra, DuckDB matches naturally into your present workflow, regardless of your most well-liked language.
You possibly can go examine the complete code on the following GitHub repository.
Josep Ferrer is an analytics engineer from Barcelona. He graduated in physics engineering and is at the moment working within the information science discipline utilized to human mobility. He’s a part-time content material creator centered on information science and know-how. Josep writes on all issues AI, masking the applying of the continued explosion within the discipline.
Picture by Writer
DuckDB is a quick, in-process analytical database designed for contemporary information evaluation. It runs immediately out of your Python script, which signifies that there isn’t a separate server wanted, and it excels at complicated queries due to its columnar storage and vectorized execution.
As understanding cope with information is changing into extra essential, at present I wish to present you construct a Python workflow with DuckDB and discover its key options.
Let’s dive in!
What Is DuckDB?
DuckDB is a free, open-source, in-process OLAP database constructed for quick, native analytics. In contrast to conventional databases that run as exterior companies, DuckDB runs inside your utility, with no server required. As an OLAP system, DuckDB shops information in columns (not rows like OLTP methods), making it extremely environment friendly for analytical queries comparable to joins, aggregations, and groupings.
Consider DuckDB as a light-weight, analytics-optimized model of SQLite, bringing the simplicity of native databases along with the ability of recent information warehousing. And this leads us to the next pure query…
What Are DuckDB’s Important Options?
Blazing-Quick Analytical Queries
DuckDB delivers spectacular efficiency for OLAP workloads, usually stunning customers aware of conventional databases like PostgreSQL. In contrast to standard OLAP methods that may be sluggish resulting from processing massive volumes of knowledge, DuckDB leverages a columnar, vectorized execution engine. This design optimizes CPU cache utilization and considerably accelerates analytical question efficiency.
Native SQL Help + Seamless Language Integration
DuckDB presents full help for complicated SQL queries and exposes APIs in a number of languages, together with Java, C, and C++. Its tight integration with Python and R makes it ultimate for interactive information evaluation. You possibly can write queries immediately in your most well-liked surroundings, with additional SQL syntax enhancements (e.g., EXCLUDE, REPLACE, and ALL) to simplify question writing.
And the very best half is that DuckDB is totally self-contained, with no exterior dependencies or setup complications.
Free And Open Supply
DuckDB is totally open-source and actively maintained by a rising group of contributors. This ensures fast function improvement and bug fixes. And sure, it’s free to make use of. Whereas future licensing modifications are at all times a chance, for now, you get a robust analytics engine at zero price.
Now that we all know its predominant options, let’s get began with it!
Getting Began With DuckDB
The set up course of for DuckDB relies upon barely in your surroundings, however total, it’s fast and easy. Since DuckDB is an embedded database engine with no server necessities or exterior dependencies, setup sometimes takes only a few traces of code. Yow will discover the whole set up information within the official DuckDB documentation.
Stipulations
Earlier than diving in, guarantee you might have the next:
- Python 3.13 or later put in
- A primary understanding of SQL and information evaluation in Python
You possibly can simply set up DuckDB in your surroundings by executing the next command:
Working With DuckDB in Python
When you’ve put in DuckDB, it’s fairly easy to get began. You merely import DuckDB into your surroundings, then hook up with an present database or create a brand new one if required.
For instance:
import duckdb
connection = duckdb.join()
If no database file is offered to the join() methodology, DuckDB will create a brand new in-memory database by default. That mentioned, the best technique to begin working SQL queries is through the use of the sql() methodology immediately.
# Supply: Primary API utilization - https://duckdb.org/docs/api/python/overview.html
import duckdb
duckdb.sql('SELECT 42').present()
Working this command initializes a world in-memory DuckDB occasion throughout the Python module and returns a relation, a symbolic illustration of the question.
Importantly, the question itself is not executed till you explicitly request the outcome, as proven under:
# Supply: Execute SQL - https://duckdb.org/docs/guides/python/execute_sql.html
outcomes = duckdb.sql('SELECT 42').fetchall()
print(outcomes)
"""
[(42,)]
"""
Let’s now work with some actual information. DuckDB helps a variety of file codecs, together with CSV, JSON, and Parquet, and loading them is straightforward.
You possibly can see how simple it’s within the instance under:
# Supply: Python API - https://duckdb.org/docs/api/python/overview.html
import duckdb
duckdb.read_csv('instance.csv') # learn a CSV file right into a Relation
duckdb.read_parquet('instance.parquet')# learn a Parquet file right into a Relation
duckdb.read_json('instance.json') # learn a JSON file right into a Relation
duckdb.sql('SELECT * FROM "instance.csv"') # immediately question a CSV file
Working With Exterior Information Sources In DuckDB
Certainly one of DuckDB’s standout options is its means to question exterior information recordsdata immediately, while not having to import them right into a database or load whole datasets into reminiscence. In contrast to conventional databases that require information to be ingested first, DuckDB helps a “zero-copy” execution mannequin, permitting it to learn solely the information required for a given question.
This strategy brings a number of key benefits:
- Minimal reminiscence utilization: Solely the related parts of the file are learn into reminiscence.
- No import/export overhead: Question your information in place—no want to maneuver or duplicate it.
- Streamlined workflows: Simply question throughout a number of recordsdata and codecs utilizing a single SQL assertion.
To exemplify the utilization of DuckDB, we can be utilizing a easy CSV file which you could receive from the next Kaggle hyperlink .
To question the information, we are able to simply outline a easy question that factors out to our file path.
# Question information immediately from a CSV file
outcome = duckdb.question(f"SELECT * FROM '{supply}'").fetchall()
print(outcome)
Now we are able to simply deal with information utilizing SQL-like logic immediately with DuckDB.
Filtering Rows
To give attention to particular subsets of knowledge, use the WHERE clause in DuckDB. It filters rows primarily based on situations utilizing comparability operators (>, <, =, <>, and so forth.) and logical operators (AND, OR, NOT) for extra complicated expressions.
# Choose solely college students with a rating above 80
outcome = duckdb.question(f"SELECT * FROM '{supply}' WHERE total_passengers > 500").fetchall()
outcome
Sorting Outcomes
Use the ORDER BY clause to type outcomes by a number of columns. It defaults to ascending (ASC), however you’ll be able to specify descending (DESC). To type by a number of columns, separate them with commas.
#Type months by variety of passengers
sorted_result = duckdb.question(f"SELECT * FROM '{supply}' ORDER BY total_passengers DESC ").fetchall()
print("nMonths sorted by complete visitors:")
print(sorted_result)
Including Calculated Columns
Create new columns in your question utilizing expressions and the AS key phrase. Use arithmetic operators or built-in capabilities to rework information—these columns seem within the outcomes however don’t have an effect on the unique file.
# Add 10 bonus factors to every rating
bonus_result = duckdb.question(f"""
SELECT
month,
total_passengers,
total_passengers/1000 AS traffic_in_thousands
FROM '{supply}'
""").fetchall()
print("nScores with 10 bonus factors:")
print(bonus_result)
Utilizing CASE Expressions
For extra complicated transformations, SQL supplies the CASE expression. This works equally to if-else statements in programming languages, permitting you to use conditional logic in your queries.
segmented_result = duckdb.question(f"""
SELECT
month,
total_passengers,
CASE
WHEN total_passengers >= 100 THEN 'HIGH'
WHEN total_passengers >= 50 THEN 'MEDIUM'
ELSE 'LOW'
END AS affluency
FROM '{supply}'
""").fetchall()
print("nMonth by affluency of passangers")
print(segmented_result)
Conclusion
DuckDB is a high-performance OLAP database constructed for information professionals who have to discover and analyze massive datasets effectively. Its in-process SQL engine runs complicated analytical queries immediately in your surroundings — no separate server is required. With seamless help for Python, R, Java, C++, and extra, DuckDB matches naturally into your present workflow, regardless of your most well-liked language.
You possibly can go examine the complete code on the following GitHub repository.
Josep Ferrer is an analytics engineer from Barcelona. He graduated in physics engineering and is at the moment working within the information science discipline utilized to human mobility. He’s a part-time content material creator centered on information science and know-how. Josep writes on all issues AI, masking the applying of the continued explosion within the discipline.
