DuckDB Integration #

OWILIX integrates DuckDB for high-performance querying of remote parquet datasets stored in LEXIS/iRODS, utilizing the Http2IrodsFileSystem for efficient data access.

Architecture #

The integration handles SQL query execution on remote parquet files without full downloads, accessing only required byte ranges.

Filesystem: Http2IrodsFileSystem #

The Http2IrodsFileSystem (owilix/core/fsspec/http2irods.py) implements the fsspec interface for iRODS via HTTP.

• Read-Only: Designed primarily for reading data.
• Range Requests: Supports optimized range requests crucial for DuckDB parquet reading.
• Authentication: Uses py4lexis session tokens.

Executors #

The owilix/core/db package provides executors to manage DuckDB connections and query execution.

OWIDuckDBSelectExecutor #

Executes SELECT queries across multiple parquet files.

• Connection Pooling: Manages DuckDBConnectionPool for concurrency.
• Batching: Processes files in batches to manage memory and connection limits.
• Resilience: Implements retries for transient network errors.

Usage:

from owilix.core.db import OWIDuckDBSelectExecutor, ParquetBatch, OWIlixSQLQuery

# Define files and query
files = {fs_instance: [("path/to/file.parquet", "root_path")]}
batch = ParquetBatch(files=files['mock_fs'], query_args={})
sql = OWIlixSQLQuery("SELECT count(*) FROM read_parquet(${owi_remote_files})")

# Execute
executor = OWIDuckDBSelectExecutor(pq_files=files, owilix_sql=sql)
for result in executor.query_aggregator():
    print(result.rows)
executor.close()

OWIDuckDBCopyExecutor #

Handles COPY operations to export query results, typically to local files for further processing or caching.

from owilix.core.db import OWIDuckDBCopyExecutor

executor = OWIDuckDBCopyExecutor(
    pq_files=files, 
    owilix_sql=sql, 
    output_dir="/local/output/path"
)

Note: OWIDuckDBCopyExecutor currently supports local output directories only.

OWIDuckDBAsyncExecutor ✨ New #

High-performance async executor using httpx for IO operations. ~42% faster than sync executor.

from owilix.core.db import OWIDuckDBAsyncExecutor
import asyncio

async def query_data():
    executor = OWIDuckDBAsyncExecutor(
        pq_files=files,
        owilix_sql=sql,
        url_base="https://api.lexis.tech/irods",
        token="your-token",
        pq_batch_size=10,
        max_concurrent=10
    )
    async for result in executor.query_aggregator_async():
        print(result.rows)
    await executor.close()

asyncio.run(query_data())

Features:

• httpx async client with connection pooling
• Concurrent query execution via asyncio
• run_in_executor for DuckDB (no native async)
• Semaphore-based concurrency limiting

CLI Integration #

The owilix query command utilizes these components:

• less: Interactive dataset browser (uses async executor by default)
• sites: Filters datasets by URL/Domain.
• warc: Extracts and manages WARC file locations.
• stream: Streams query results via Apache Arrow.

Async Mode #

The less command uses sync executor by default for streaming output. Enable async mode with --async-mode for batch processing (~42% faster, but waits for all results):

# Sync mode (default, streaming output)
owi query less . main:latest

# Async mode (faster for batch jobs, no streaming)
owi query less . main:latest --async-mode

Performance Tuning #

Parameter	Default	Impact
pq_batch_size	10	Files per DuckDB query. Higher = faster but more memory
prefetch	1	Parallel batch prefetch (sync executor)
max_concurrent	10	Async concurrent operations
async_mode	False	Enable for batch jobs (~46% faster, no streaming)

Benchmark Results #

Query Type Comparison (20 files):

Query Type	Sync	Async	Improvement
COUNT(*)	25.0s	13.5s	+46%
SELECT 3 cols	23.6s	13.2s	+44%
SELECT 6 cols	19.7s	11.2s	+43%
GROUP BY language	24.9s	13.4s	+46%
GROUP BY domain	26.6s	14.3s	+46%
WHERE filter	26.6s	13.4s	+50%
AVERAGE			+46%

Concurrency Tuning:

Setting	Time	Note
sync prefetch=1	25.4s	Baseline
sync prefetch=2	23.6s	+7%
sync prefetch=4	23.6s	No additional gain
async max_concurrent=2	13.6s	Best async
async max_concurrent=10	13.4s	Similar
async max_concurrent=20	14.5s	Overhead

Key Findings:

• COUNT(id) is ~13% slower than COUNT(*) (DuckDB optimization)
• Increasing prefetch >2 has minimal impact (DuckDB internal parallelism)
• Async max_concurrent >10 adds overhead without benefit

Testing #

Integration tests are located in tests/owilix/core/db/:

• test_executors.py: Unit tests for all executor classes
• benchmark_comprehensive.py: Full benchmark suite with pytest integration