# Query Commands

Query commands allow you to perform SQL-based analysis and filtering on datasets (typically Parquet files) without downloading the entire dataset. They use DuckDB for efficient querying.

## `query less`

An interactive browser for dataset content, similar to the unix `less` command but for structured data.

### Usage

```bash
owi query less [OPTIONS]
```

### Options

- `-R SPECIFIER` / `--remote`: Remote dataset specifier (e.g., `lexis:latest`).
- `-L SPECIFIER` / `--local`: Local dataset specifier (e.g., `all:latest`).
- `--select SQL`: SQL selection string (default: `url,title,main_content`).
- `--where SQL`: SQL where clause for filtering.
- `--limit N`: Limit number of rows.
- `--page-size N`: Rows per page in table view (default: 10).
- `--pq-batch N`: Number of parquet files per query batch (default: 10).
- `--batch-size N`: Rows per processing batch (default: 100).
- `--files GLOB`: Glob pattern to select files (default: `**/*.parquet`).
- `--search TEXT`: Use the OWI remote full-text index to prefilter matching document IDs and dataset partitions before scanning parquet files.
- `--search-language LANG`: Language passed to the remote index search (default: `eng`).
- `--search-representation REPR`: Indexed field to search (`main_content`, `title`, `description`).
- `--search-limit N`: Maximum number of search hits to resolve before running the query.
- `--search-all`: Require all search terms to match instead of OR semantics.
- `--async/--sync`: Execution mode (see below).

### Execution Modes

- **Sync mode (default)**: Results stream as they arrive. Good for interactive use and seeing results quickly.
- **Async mode (`--async`)**: Uses concurrent async execution which can be faster for large datasets, but results stream via a background thread (overhead)

### Examples

**Interactive table view of remote data:**
```bash
owi query less -R lexis:latest --select "url,title"
```

**Stream results as JSONL with progress:**
```bash
owi --format jsonl+ query less -R lexis:latest --limit 1000
```

**Query local dataset:**
```bash
owi query less -L all:latest/collectionName=main --limit 100
```

**High-performance async mode:**
```bash
owi query less -R lexis:latest --async --pq-batch 10
```

**Prefilter a query with the remote index:**
```bash
owi query less -R all --search "open web search" --search-language eng --limit 100
```

**Combine search with a SQL filter:**
```bash
owi query less -R all --search "graz" --where "quality_score > 0.5" --select "url,title,quality_score"
```

### Search-Driven Query Flow

When `--search` is used, OWILIX first queries the remote DuckLake index to obtain ranked document IDs and the matching collection/day partitions. It then restricts the parquet scan to those partitions and injects an `id IN (...)` filter into the query execution path. This is usually much cheaper than scanning every parquet file in the selected dataset specifier.

---

## `query stats`

Calculate statistics for a dataset, such as record counts, date ranges, and distributions.

### Usage

```bash
owi query stats [OPTIONS]
```

### Options

- `-R SPECIFIER` / `--remote`: Remote dataset specifier.
- `-L SPECIFIER` / `--local`: Local dataset specifier.
- `--topk N`: Number of top frequent items to show for distributions.
- `--output FILE`: Save statistics to a JSON/text file.

### Examples

**Get statistics for a remote dataset:**
```bash
owi query stats -R lexis:latest
```

---

## `query sites`

Filter datasets based on a list of URLs or domains provided in a file.

### Usage

```bash
owi query sites [OPTIONS] URL_FILE
```

### Options

- `-R SPECIFIER` / `--remote`: Remote dataset specifier.
- `-L SPECIFIER` / `--local`: Local dataset specifier.
- `--select SQL`: Columns to select.
- `--output FILE`: Output file for results.

### Examples

**Extract records for domains listed in `domains.txt`:**
```bash
owi query sites -R lexis:latest/collectionName=curlie_full domains.txt --output filtered_data.jsonl
```

---

## `query aggregate`

Run arbitrary SQL aggregation queries (GROUP BY) on datasets.

### Usage

```bash
owi query aggregate [OPTIONS]
```

### Options

- `-R SPECIFIER` / `--remote`: Remote dataset specifier.
- `-L SPECIFIER` / `--local`: Local dataset specifier.
- `--select SQL`: Selection including aggregation functions (e.g., `count(*) as c`).
- `--groupby SQL`: Columns to group by.
- `--aggregate SQL`: Final logic on the aggregated result (e.g. `ORDER BY c DESC`).

### Examples

**Count URLs by top-level domain:**
```bash
owi query aggregate -R lexis:latest/collectionName=curlie_full --select "url_suffix, count(*) as c" --groupby "url_suffix"
```

---

### Subcommands

#### `owi query aggregate`
Run SQL aggregation queries across datasets. Automatically merges partial results for count/sum.

```bash
owi query aggregate -R lexis:latest [OPTIONS]
```
- `-s, --select TEXT`: SQL SELECT (default: "url_suffix,count(url) as c")
- `-g, --groupby TEXT`: SQL GROUP BY (default: "url_suffix")
- `-a, --aggregate TEXT`: Final aggregation query
- `--async`: Use asynchronous execution (streaming)

#### `owi query slice`
Create a new dataset slice based on a query. See [Query Slice Details](query_slice.md).

```bash
owi query slice -R lexis:latest/collectionName=source [OPTIONS]
```
- `-c, --collection TEXT`: Name for the new slice collection (default: "userslice")
- `-s, --select TEXT`: Columns to include (default: "*")
- `-w, --where TEXT`: Filter condition
- `-l, --limit TEXT`: Row limit
- `-t, --type TEXT`: Resource type (default: "owip")
- `-a, --access TEXT`: Access level (default: "public")
- `--ciff/--no-ciff`: Also slice associated CIFF files (default: True)
- `--overwrite`: Overwrite existing slice

#### `owi query stats`
Calculate statistics over datasets (counts, distributions).

```bash
owi query stats -R lexis:latest [OPTIONS]
```
- `-k, --topk INTEGER`: Top N items for distributions (default: 20)
- `-w, --write-back`: Save stats to dataset .stats folder
- `--force`: Force recalculation
- `--async`: Use asynchronous execution

#### `owi query sites`
Execute site-specific queries using a URL file filter.

```bash
owi query sites urls.txt -R lexis:latest [OPTIONS]
```
- `-s, --select TEXT`: Columns to select
- `-w, --where TEXT`: Additional filter
- `--async`: Use asynchronous execution

#### `owi query warc`
Extract WARC file locations from datasets ([details](warc.md))


```bash
owi query warc -R lexis:latest [OPTIONS]
```
- `-w, --where TEXT`: Filter condition
- `--warc-config PATH`: WARC location config file
- `-o, --output PATH`: Output directory for logs

## Other Query Commands

- **[query graphs](graphs.md)**: Web graph analysis (experimental)