# Dataset SQL reference

Dataset SQL runs on [DuckDB](https://duckdb.org/docs/sql/introduction). You write
one read-only `SELECT`; the service validates it, registers the datasets in
scope as relations, executes it in an isolated DuckDB session, and returns a
paginated result.

## Relations in scope

<table>
<thead>
  <tr>
    <th>
      Query path
    </th>
    
    <th>
      Relations available
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        QueryDataset
      </code>
      
       (single dataset)
    </td>
    
    <td>
      <code>
        dataset
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        QueryDatasets
      </code>
      
       (project-wide)
    </td>
    
    <td>
      Every dataset in the project, by name
    </td>
  </tr>
  
  <tr>
    <td>
      Saved views
    </td>
    
    <td>
      Every dataset and saved view in the project, by name
    </td>
  </tr>
  
  <tr>
    <td>
      Export
    </td>
    
    <td>
      <code>
        dataset
      </code>
    </td>
  </tr>
</tbody>
</table>

Names follow the dataset rules: letters, digits, and dashes, starting with a
letter. Dash names must be quoted in SQL:

```sql
SELECT * FROM "live-hosts"
```

Column names may contain uppercase letters. Unquoted identifiers are folded to
lowercase by DuckDB, so quote mixed-case columns: `"Status_Code"`.

## Where SQL runs

<table>
<thead>
  <tr>
    <th>
      Surface
    </th>
    
    <th>
      Query
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Datasets → Query → <strong>
        Advanced SQL
      </strong>
    </td>
    
    <td>
      One dataset, via <code>
        QueryDataset
      </code>
      
      .
    </td>
  </tr>
  
  <tr>
    <td>
      Datasets → Views
    </td>
    
    <td>
      A saved view definition, via <code>
        DatasetViewService
      </code>
      
      .
    </td>
  </tr>
  
  <tr>
    <td>
      API <code>
        QueryDatasets
      </code>
    </td>
    
    <td>
      All datasets in a project in one statement.
    </td>
  </tr>
  
  <tr>
    <td>
      API <code>
        ExportDataset
      </code>
      
       / query download
    </td>
    
    <td>
      One dataset, streamed to a file.
    </td>
  </tr>
</tbody>
</table>

The Hub UI's SQL box is scoped to the selected dataset. Project-wide SQL is
available through the API; saved views are the UI's way to combine datasets.

## Query policy

A request is rejected before execution unless it satisfies all of these:

- Exactly one statement. For direct dataset queries (`QueryDataset`,
`QueryDatasets`, and exports) the text must start with `SELECT`
(case-insensitive) and contain no semicolons. A leading `WITH` CTE or a
leading comment is rejected by that check.
- Read-only: `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP`, `ALTER`, `COPY`,
`ATTACH`, and `DETACH` are rejected.
- No file, network, glob, or catalog access. Table functions such as
`read_csv`, `read_parquet`, `read_text`, `glob`, `http_get`, and
`range` are rejected, as are DuckDB replacement scans
(`FROM '/path/file.parquet'`) and `information_schema`.
- Every referenced relation must be registered in the query scope. A query
against a different dataset, another project, or a file path fails with
"not a dataset in this query scope".

Joins, `GROUP BY`, `HAVING`, window functions, `UNION`, and scalar functions
(`lower`, `strpos`, `coalesce`, `date_trunc`, `now`, …) are allowed.

> Saved views validate with the SQL parser rather than the `SELECT`-prefix
> check, so a view **may** start with `WITH ... SELECT`. Direct dataset queries
> cannot. See [Saved views](/datasets/views).

## Ordering and pagination

The service wraps your statement to paginate it:

```sql
SELECT * FROM (<your SELECT>) AS __bh_dataset_query
ORDER BY 1
LIMIT <page_size + 1> OFFSET <offset>
```

That means:

- **Pages are ordered by the first column of your projection, ascending.**
An `ORDER BY` inside your statement does not survive the wrapper — put the
column you want to sort by first. Descending page order is not available
through the paginated API.
- Page size: default **100**, maximum **1,000** for single-dataset and
project-wide queries; default **100**, maximum **10,000** for saved views.
- `next_page_token` is an opaque cursor. Pass it back as `page_token`; an empty
token means the last page. The cursor is an offset, not a snapshot: changing
the SQL, filters, or underlying data between pages can shift rows.
- The wrapper fetches one extra row to decide whether another page exists.
- The engine has a 100,000-row materialization guard; normal page sizes are far
smaller. Use [export](/datasets/api-cli#exportdataset) for large result sets.

If your statement includes its own `LIMIT`, the outer pagination applies after
that limit. Prefer letting the service paginate.

## Result types

Query results are mapped to dataset value types:

<table>
<thead>
  <tr>
    <th>
      DuckDB type
    </th>
    
    <th>
      Result kind
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        TEXT
      </code>
      
      , <code>
        VARCHAR
      </code>
    </td>
    
    <td>
      text
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        BOOLEAN
      </code>
    </td>
    
    <td>
      boolean
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        TINYINT
      </code>
      
      , <code>
        SMALLINT
      </code>
      
      , <code>
        INTEGER
      </code>
      
      , <code>
        BIGINT
      </code>
    </td>
    
    <td>
      integer
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        FLOAT
      </code>
      
      , <code>
        DOUBLE
      </code>
    </td>
    
    <td>
      number
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        TIMESTAMP
      </code>
      
      , <code>
        DATE
      </code>
    </td>
    
    <td>
      timestamp
    </td>
  </tr>
</tbody>
</table>

Other DuckDB types (`DECIMAL`, `HUGEINT` and other wide/unsigned integers,
`BLOB`, `UUID`, `INTERVAL`, `TIME`, lists, structs, maps) are rejected with an
unsupported-type error. Cast unusual expressions to a supported type:

```sql
SELECT
  CAST(count(*) AS BIGINT) AS rows,
  CAST(sum(attempts) AS BIGINT) AS attempts,
  CAST(avg(cvss) AS DOUBLE) AS avg_cvss,
  CAST(min(first_seen) AS TIMESTAMP) AS earliest
FROM dataset
```

Alias calculated columns; otherwise DuckDB names them (`count_star()`,
`sum(x)`) and they become the result column names.

## Current state and history

By default the `dataset` relation is the current logical state: latest
observation per identity, stale rows excluded. Columns include your schema plus
`__ingested_at` and `__deleted`.

Set `includeHistory = true` (UI **Include history**, CLI `--include-history`) to
query every observation instead:

```sql
SELECT host, "__ingested_at"
FROM dataset
```

History keeps duplicate identities and older versions; there is no history mode
for project-wide queries or saved views.

## Time ranges

`ingestedAtFrom` and `ingestedAtTo` bound `__ingested_at`. Like structured
filters, they are applied **outside** your `SELECT`:

- The default `SELECT * FROM dataset` includes `__ingested_at`, so the time
range works as expected.
- If you project a subset, include `__ingested_at` or filter on it inside the
SQL:

```sql
SELECT host, status_code
FROM dataset
WHERE "__ingested_at" >= now() - INTERVAL 7 DAY
```

- `ingestedAtFrom` must not be after `ingestedAtTo`.

## Examples

### Inspect a dataset

```sql
SELECT *
FROM dataset
LIMIT 100
```

A bare `SELECT *` is rewritten to `SELECT * FROM dataset` for single-dataset
queries.

### Filter rows in SQL

```sql
SELECT host, ip, resolved
FROM dataset
WHERE resolved
  AND host LIKE 'api.%'
```

DuckDB is case-sensitive for `LIKE` and `=` on text. Use `lower(host) LIKE ...`
or `ILIKE` when you need case-insensitive matching.

### Aggregate by the first column

```sql
SELECT severity, COUNT(*) AS findings
FROM dataset
GROUP BY severity
```

Results are sorted by `severity` ascending because it is the first column. To
sort the page by count instead, put it first:

```sql
SELECT COUNT(*) AS findings, severity
FROM dataset
GROUP BY severity
```

### Window functions

```sql
SELECT
  host,
  row_number() OVER (PARTITION BY program ORDER BY "__ingested_at" DESC) AS rn
FROM dataset
```

### Join datasets (project-wide SQL)

```sql
SELECT s.host, w.status_code, w.title
FROM subdomains AS s
JOIN "web-services" AS w ON s.host = w.host
WHERE w.status_code BETWEEN 200 AND 299
```

This runs through `QueryDatasets`; it cannot reference saved views.

### Inspect history

```sql
SELECT host, ip, "__ingested_at"
FROM dataset
```

Run it with `include_history = true` to see every observation.

## Errors

<table>
<thead>
  <tr>
    <th>
      Error
    </th>
    
    <th>
      Cause
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        Invalid dataset SQL query
      </code>
      
       / <code>
        Syntax error
      </code>
    </td>
    
    <td>
      DuckDB could not parse the statement.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        Forbidden dataset SQL query
      </code>
    </td>
    
    <td>
      A non-SELECT statement, forbidden function, or table function.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        Unknown dataset in query
      </code>
    </td>
    
    <td>
      A relation outside the registered scope, including file paths.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        DuckDB could not prepare the query
      </code>
    </td>
    
    <td>
      Unknown column, type mismatch, or invalid expression.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        Unsupported type
      </code>
    </td>
    
    <td>
      A projection returned a type the Hub cannot map.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        Too many requests
      </code>
    </td>
    
    <td>
      The per-user concurrency or rate limit was exceeded.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        invalid page_token
      </code>
    </td>
    
    <td>
      The cursor is malformed.
    </td>
  </tr>
</tbody>
</table>

## Operational limits

<table>
<thead>
  <tr>
    <th>
      Limit
    </th>
    
    <th>
      Default
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Page size (single/project-wide)
    </td>
    
    <td>
      100, maximum 1,000
    </td>
  </tr>
  
  <tr>
    <td>
      Page size (views)
    </td>
    
    <td>
      100, maximum 10,000
    </td>
  </tr>
  
  <tr>
    <td>
      Row materialization guard
    </td>
    
    <td>
      100,000
    </td>
  </tr>
  
  <tr>
    <td>
      Query memory
    </td>
    
    <td>
      2 GB
    </td>
  </tr>
  
  <tr>
    <td>
      Concurrent queries per user
    </td>
    
    <td>
      2
    </td>
  </tr>
  
  <tr>
    <td>
      Queries per user per minute
    </td>
    
    <td>
      60
    </td>
  </tr>
</tbody>
</table>

Operators can tune these with `BOUNTYHUB_HUB_DATASET_QUERY_MAX_ROWS`,
`BOUNTYHUB_HUB_DATASET_QUERY_MEMORY_LIMIT`,
`BOUNTYHUB_HUB_DATASET_QUERY_MAX_CONCURRENT_PER_USER`, and
`BOUNTYHUB_HUB_DATASET_QUERY_PER_MINUTE`.

## Related

- [Structured filters](/datasets/filters) — typed filters applied on top of a
query.
- [Saved views](/datasets/views) — reusable SQL over multiple datasets.
- [API and CLI](/datasets/api-cli) — request and response fields.
- [Pushing dataset rows](/datasets/workflows) — pushing data with
`step.update_dataset`.