Picture by the writer
Duck db A fast, analytical database designed for modern data analysis. It runs directly from your script, which means that it does not need to retire on complex questions thanks to its column storage and vectors.
Since it is becoming more important to understand how to deal with the data, today I want to show you how to build a workflow with Duckdb and how to discover its key features.
Let’s dive!
What is Dick DB?
Dick DB is a free, open source, these process OLAP database made for fast, local analytics. Unlike the traditional database that runs as external services, Dick DB runs within your request, without any server. As an OLAP system, Dick Stores data in DB columns (not rows like OLTP system), which makes it very effective for analytical questions, such as involvement, gatherings and grouping.
Think of Dick DB as a lightweight version of Sqlite, a better version of analytics, to bring the simplicity of the modern database with the power of the modern data warehouse. And it leads us to the following natural question …
What are the main features of Dick DB?
Fast -paced analytical questions
Duckdb OLAP offers impressive performance for work load, often amazing users traditional databases such as postgris QL users. Unlike traditional OLAP systems, which can be slowed down due to a large amount of data, Dick DB takes advantage of a column, vectopular processing engine. This design improves the use of CPU cache and significantly accelerates analytical queries efficiency.
Ancestral SQL Support + Integration of Smooth Language
Dick DB provides full support for complex SQL questions and exposes APIS in several languages, including Java, C, and C ++. Ideal for its strict integration integration data analysis makes it ideal. You can write direct questions in your preferred environment, adding extra SQL syntax (eg, excluding, changing, and all) to make it easy to write.
And the best thing is that Dick DB contains fully self -contained, which does not have external dependence or setup headache.
Free and Open Source
The Dick DB is fully open source and is actively maintained by a growing group of partners. This ensures the growth of the feature and the bug fix. And yes, it’s free to use. Although changes in the future licensing are always a possibility, for now, you get a powerful analytical engine at a zero cost.
Now when we know its important features, let’s start with it!
To start with Dick DB
The installation process for the dick DB depends a bit on your environment, but overall, it is fast and easy. Since Dick DB is an embedded database engine that does not have server requirements or external dependence, the setup usually takes only a few lines of code. You can find a complete installation guide in the official Dickdb document.
Provisions
Before diving, make sure you have the following:
- Installed 3.13 or later
- Basic understanding of SQL and Data Analysis in Azar
You can easily install Dickdb in your environment by following the following command:
Working with Dick DB in Azgar
Once you install the Dick DB, it is very easy to start. You easily import duckdb into your environment, then contact the current database or make new if needed.
For example:
import duckdb
connection = duckdb.connect()If no database file has been provided connect() Method, Duckdb will create a new memory database as default. That’s the easiest way to run SQL questions is to use it sql() Method directly.
# Source: Basic API usage -
import duckdb
duckdb.sql('SELECT 42').show()Running this command begins the global memory of the Dick DB within the module of Azigar and returns a relationship, a symbolic representation of the inquiry.
The important thing is, this question is not processed until you clearly request the results, as shown below:
# Source: Execute SQL -
results = duckdb.sql('SELECT 42').fetchall()
print(results)
"""
((42,))
"""Let’s work with some real data now. The Dick DB supports a wide range of file formats, which include CSV, JSON, and hands, and easy to load them.
You can see how straightforward it is in the example of:
# Source: Python API -
import duckdb
duckdb.read_csv('example.csv') # read a CSV file into a Relation
duckdb.read_parquet('example.parquet')# read a Parquet file into a Relation
duckdb.read_json('example.json') # read a JSON file into a Relation
duckdb.sql('SELECT * FROM "example.csv"') # directly query a CSV fileWorking with outdoor data sources in Dick DB
One of the Dick DB stand -out features is that external data files have the ability to directly inquire, need to be imported in a database or load the entire datases in memory. Unlike the traditional database that requires the first data, Dick DB supports a “zero copy” implementation model, which allows it to read the data required for the question given.
This approach brings many important benefits:
- Use of minimum memory: Only related sections of the file are read in memory.
- No Import/Export Over Head: Investigate your data in place – it does not need to be transferred or copied.
- Smooth workflows: easily inquire into numerous files and formats using the same SQL statement.
Example of Dick DB Use LEW, We will use a simple CSV file you can get from the following Kagal link .
We can easily explain a simple inquiry that indicates the path of our file.
# Query data directly from a CSV file
result = duckdb.query(f"SELECT * FROM '{source}'").fetchall()
print(result)Now we can easily handle data using Dickdb directly using SQL -like logic.
Filtering rows
To focus on specific sub -subtracts of the data, use the clause in the dick DB. It filters a line based on the terms using logical operators (and, or, not, not, for more complex impression.
# Select only students with a score above 80
result = duckdb.query(f"SELECT * FROM '{source}' WHERE total_passengers > 500").fetchall()
resultSorting results
Use the order through clause to configure results through one or more columns. It is set before the climb (ASC), but you can explain the descep. To configure through numerous columns, separate them from the coma.
#Sort months by number of passengers
sorted_result = duckdb.query(f"SELECT * FROM '{source}' ORDER BY total_passengers DESC ").fetchall()
print("\nMonths sorted by total traffic:")
print(sorted_result)Adding calculation column
Make a new column in your query using expression and keywords. Use math operators or built-in functions to change the data-these columns appear in the results but do not affect the original file.
# Add 10 bonus points to each score
bonus_result = duckdb.query(f"""
SELECT
month,
total_passengers,
total_passengers/1000 AS traffic_in_thousands
FROM '{source}'
""").fetchall()
print("\nScores with 10 bonus points:")
print(bonus_result)Using the impression of the case
For more complicated changes, the SQL provides a case. This programming languages ​​work according to your statements, which you can apply to the conditional logic in your questions.
segmented_result = duckdb.query(f"""
SELECT
month,
total_passengers,
CASE
WHEN total_passengers >= 100 THEN 'HIGH'
WHEN total_passengers >= 50 THEN 'MEDIUM'
ELSE 'LOW'
END AS affluency
FROM '{source}'
""").fetchall()
print("\nMonth by affluency of passangers")
print(segmented_result)Conclusion
Dick DB is a high -performance OLAP database built for data professionals who need to discover and analyze major datases effectively. In this process, the SQL engine runs directly analytical questions in your environment-no separate server is required. With no obstacle support for Azgar, R, Java, C ++, and much more, Dick DB naturally fits your current workflow, even if your preferred language does not matter.
You can check the full code on it After the gut hub repository
Jozep Ferrer Barcelona is an analytical engineer. He graduated in physics engineering and is currently working in the data science field applied to humanitarian movement. He is a part -time content creator focused on data science and technology. Joseph writes everything on AI, covering the application of the explosion at the field.