# 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:**

```python
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.

```python
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.

```python
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):

```bash
# 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