DeltaDB

DeltaDB is a lightweight, fast, and scalable database built on polars and deltalake. It is designed to streamline data operations, providing features like upsert, delete, commit, and version control while harnessing the high performance of polars and deltalake.

Table of Contents

• Installation
• Key Features
• Getting Started
  • Connecting to a Database
  • Upserting Data
  • Upserting Multiple Records
  • Querying Data as a DataFrame
  • Committing with Schema Differences
  • Deleting Records
  • Checking Out Previous Table Versions
• Performance

Installation

To install DeltaDB, run the following command:

pip install deltadb

_[return]

Key Features

• Upsert: Efficiently insert or update records in your tables.
• Delete: Remove specific records or entire tables.
• Commit: Version control your data with commit functionality.
• Query: Execute SQL queries and retrieve results as dictionaries or dataframes.
• Schema Handling: Automatically manage schema changes during data operations.
• Versioning: Revert tables to previous versions when needed.

_[return]

Getting Started

Connecting to a Database

Establish a connection to your database:

from deltadb import delta

# connect to a database at the specified path
db = delta.connect(path="test.delta")

_[return]

Upserting Data

Insert or update data within a table:

db.upsert(
    table="test_table", 
    primary_key="id", 
    data={"id": 1, "name": "alice"}
)

# query the data
result = db.sql("select * from test_table")
print(result)  # output: [{'id': 1, 'name': 'alice'}]

# commit the changes
db.commit("test_table")

_[return]

Upserting Multiple Records

Upsert multiple records simultaneously, with automatic schema management:

db.upsert(
    table="test_table", 
    primary_key="id", 
    data=[
        {"id": 1, "name": "ali"},
        {"id": 2, "name": "bob", "job": "chef"},
        {"id": 3, "name": "sam"},
    ]
)

_[return]

Querying Data as a DataFrame

Execute SQL queries and return the results as a polars DataFrame for advanced data manipulation:

df_result = db.sql("select * from test_table", dtype="polars")
print(df_result)

_[return]

Committing with Schema Differences

Force a commit even when there are schema differences between the current and new data:

db.commit("test_table", force=True)

_[return]

Deleting Records

Remove specific records from a table using SQL or lambda functions, or delete the entire table:

# delete records using an sql filter
db.delete(table="test_table", filter="name='charles'")

# delete records using a lambda function
db.delete(table="test_table", filter=lambda row: row["name"] == "charles")

# delete the entire table
db.delete("test_table")

_[return]

Checking Out Previous Table Versions

Revert to a previous version of a table with ease:

db.checkout(table="test_table", version=0)

_[return]

Performance

DeltaDB excels in performance, particularly when compared to traditional databases like SQLite. A series of benchmarks were conducted to compare the average elapsed time for operations in both DeltaDB and SQLite.

Benchmark Setup

The benchmarks involved running 100 iterations of data insertion into tables of varying sizes, specifically with a width of 1,000 and a height of 10,000, to simulate realistic data load scenarios. For each iteration, the time taken to perform the operations was measured, and the average elapsed time for both DeltaDB and SQLite was calculated.

Results

• DeltaDB demonstrated a significant performance advantage, especially as the data size increased. For a table with a width of 1,000 and a height of 10,000, DeltaDB completed the operations in an average time of 1.03 seconds over 100 runs.
• In contrast, SQLite took an average of 8.06 seconds for the same operations and data size, making DeltaDB approximately 87.22% faster.

These results underscore the efficiency of DeltaDB in handling large datasets and performing complex operations, positioning it as a strong choice for data-intensive applications.

_[return]