# Dataset Query Engine (`jiri.spilka/dataset-query-engine`) Actor

Use natural language queries to retrieve results from an Apify dataset.\
This Actor provides a query engine that loads a dataset, executes SQL queries, and synthesizes results.

- **URL**: https://apify.com/jiri.spilka/dataset-query-engine.md
- **Developed by:** [Jiří Spilka](https://apify.com/jiri.spilka) (community)
- **Categories:** Developer tools, Agents, Open source
- **Features:** Full permissions, Single-tenant, Server interface
- **Stats:** 23 total users, 1 monthly users, 0.0% runs succeeded, 6 bookmarks
- **User rating**: 4.63 out of 5 stars

## Pricing

Pay per usage

This Actor is paid per platform usage. The Actor is free to use, and you only pay for the Apify platform usage, which gets cheaper the higher subscription plan you have.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-usage

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## Dataset query engine

This Actor allows you to use natural language to query and retrieve results from an [Apify dataset](https://docs.apify.com/platform/storage/dataset).
It provides a query engine that loads a dataset, executes SQL queries against the data, and synthesizes results.

### 🎯 How to use the dataset query engine?

If you have a dataset scraped using any Apify Actor, you can easily extract relevant insights from it.

For example, if you use the [Google Maps Email Extractor](https://apify.com/lukaskrivka/google-maps-with-contact-details) to search for **"the best pizza in New York,"** you’ll get a dataset containing contact details and restaurant information.

With the **query engine**, you can ask questions like:

```
"Provide a list of top restaurants with the best reviews, along with their phone numbers."
```

The Actor will respond with:

```
Here are some restaurants with outstanding reviews and their corresponding phone numbers:

1. Smith Street Pizza - (347) ....
2. Gravesend Pizza - (718) ....
3. Lucia Pizza of Avenue X - (718) ....
```

This makes it easy to extract useful data without manually filtering through large datasets. 🚀

### 🛢 How does the query engine work?

The query engine operates using two configurable approaches: **AI Agent** or **Agentic workflow**.
While the AI Agent provides flexibility and autonomous reasoning, the Agentic Workflow ensures predictable and controlled processing.
The choice is determined by the useAgent parameter.

#### AI Agent using [LlamaIndex ReAct Agent](https://docs.llamaindex.ai/en/stable/understanding/agent/)

When `useAgent` is set to `true`, the system employs the ReAct (Reasoning and Acting) framework.
In this mode, the agent works autonomously and interprets the user's query and determines the optimal strategy to achieve the desired outcome.
It utilizes a set of tools, such as `is_query_sql`, `user_query_to_sql`, `execute_sql`, and `synthesize_results`, to process the query.
The agent decides which tools to use and in what sequence.

#### Agentic [Workflow](https://docs.llamaindex.ai/en/stable/module_guides/workflow/)

When `useAgent` is set to `false`, the system follows a predefined, deterministic workflow.
In this mode, the user's query is processed through a fixed sequence of steps.
This approach ensures predictable outcomes.
Workflows chain together several events (tools) and follow a predefined flow.
They consist of individual `steps`, with each step designed to process specific event types and generate subsequent events.

### Tools

- **`load_dataset(dataset_id, refresh_dataset=False)`** – Loads a dataset from Apify into **DuckDB**, extracts schema, and maps SQL types to Python.
- **`is_query_sql(query)`** – Detects if a query is in SQL using regex.
- **`user_query_to_sql(query, table_name, table_schema)`** – Converts natural language to SQL using **LLM**.
- **`execute_sql(sql_query)`** – Runs an SQL query in **DuckDB** and returns results.
- **`synthesize_results(query, sql_query, db_results, table_schema)`** – Generates a **human-readable response** from SQL results using **LLM**.

### ⚙️ Usage

Actor can be used in two ways: **as a standard Actor** by passing an input, or in **Standby mode** via an HTTP request.

#### Normal Actor run

You can run the Actor "normally" via the **Apify API, schedule, integrations, or manually** in the Apify Console.
On start, you provide an input JSON object with settings, such as `query` and `datasetId`.
The Actor loads the dataset into **DuckDB (in-memory)** and uses it to generate an answer.
The disadvantage of this approach is that for every subsequent run, the dataset needs to be **reloaded into memory**, which adds overhead.
Additionally, starting a **Docker container** takes time, and the Actor can handle only **one query at a time**, making it inefficient for high-frequency queries.

#### Standby web server

The Actor supports **[Standby mode](https://docs.apify.com/platform/actors/running/standby)**, where it runs an HTTP server that processes queries on demand.
This mode eliminates the need to **reload the dataset** for each request and **removes container startup time**.

To use the Dataset query engine in **Standby mode**, send an HTTP GET request to:

```
https://database-query-engine.apify.actor/query?token=<APIFY_API_TOKEN>&query=return+phone+number
```

where `<OPENAI_API_KEY>` is your **[OpenAI API Key](https://platform.openai.com/api-keys)**, and `<APIFY_API_TOKEN>` is your **[Apify API Token](https://console.apify.com/settings/integrations)**.
Alternatively, you can pass the Apify `token` using the `Authorization` HTTP header for improved security.

The response is a JSON object containing the query results.

##### Query parameters

The `/` GET HTTP endpoint supports the following parameters:

| Parameter        | Type    | Default        | Description                                                                                                                         |
|------------------|---------|----------------|-------------------------------------------------------------------------------------------------------------------------------------|
| `query`          | string  | N/A            | SQL query or a natural language query. If a natural language query is provided, it is converted to SQL before execution.            |
| `datasetId`      | string  | N/A            | The ID of the dataset to query.                                                                                                     |
| `modelName`      | string  | `gpt-4o-mini`  | Specifies the LLM for SQL generation and query synthesis. Currently supports OpenAI models.                                         |
| `refreshDataset` | boolean | `false`        | If enabled, reloads the dataset to ensure updated data is available.                                                                |
| `limit`          | integer | No limit       | Maximum number of items to return.                                                                                                  |
| `offset`         | integer | `0`            | Number of items to skip before returning data.                                                                                      |
| `useAgent`       | boolean | `true`         | Enables AI-powered query handling instead of a deterministic workflow. The AI Agent can handle more tasks but may be less reliable. |

### 🔌 Integration with LLMs

Dataset query engine has been designed for easy integration with LLM applications, GPTs, and Model Context Protocol.

#### OpenAPI schema

Here you can find the [OpenAPI 3.1.0 schema](https://apify.com/jiri.spilka/database-query-engine/api/openapi).
The schema includes all available query parameters, but only `query` and `datasetId` are required.
You can omit other parameters if their default values are suitable for your application.

#### OpenAI GPTs

You can add the Dataset query engine to your GPTs by creating a custom action. Here's a quick guide:

1. Go to [**My GPTs**](https://chatgpt.com/gpts/mine) on ChatGPT website and click **+ Create a GPT**.
2. Complete all required details in the form.
3. Under the **Actions** section, click **Create new action**.
4. In the Action settings, set **Authentication** to **API key** and choose Bearer as **Auth Type**.
5. In the **schema** field, paste the OpenAPI 3.1.0 schema

Learn more about [adding custom actions to your GPTs with Apify Actors](https://blog.apify.com/add-custom-actions-to-your-gpts/) on Apify Blog.

#### Anthropic: Model Context Protocol (MCP) Server

The dataset query engine can also be used as an [MCP server](https://github.com/modelcontextprotocol) and integrated with AI applications and other AI agents, such as Claude Desktop.
You can integrate it using **Apify's [Actors MCP Server](https://apify.com/apify/actors-mcp-server)**. Simply provide `query`, `datasetId`.
To use it, start the **Actors MCP Server** with the **Dataset query engine** included in the list of available Actors.

- For deployment on the **Apify platform**, follow the **[Standby mode setup](https://apify.com/apify/actors-mcp-server#standby-web-server)**.
- For running the Actor **locally over stdio**, refer to the **[Claude Desktop setup](https://apify.com/apify/actors-mcp-server#claude-desktop)**.

### 💰 Pricing

This Actor uses the [Pay Per Event](https://docs.apify.com/sdk/js/docs/next/guides/pay-per-event) (PPE) model for flexible, usage-based pricing.
It currently charges for Actor start and a flat fee per task completion.

| Event           | Price (USD) |
|-----------------|-------------|
| Actor start     | $0.05       |
| Query completed | $0.01       |

### 👷🏼 Development

The dataset query engine Actor has open source available on [GitHub](https://github.com/apify/actor-dataset-query-engine.git),
so that you can modify and develop it yourself. Here are the steps how to run it locally on your computer.

Download the source code:

```bash
git clone https://github.com/apify/actor-dataset-query-engine.git
cd actor-dataset-query-engine
```

Install dependencies:

```bash
uv sync
```

Setup input arguments in `actor-dataset-query-engine/storage/key_value_stores/default/INPUT.json`

```
{
  "query": "email of Italian restaurants in New York",
  "datasetId": "YOUR-DATASET-ID"
}
```

And then you can run it locally using [Apify CLI](https://docs.apify.com/cli) as follows:

```bash
apify run -p
```

### 📖 Learn more

- [AI agent workflow: building an agent to query Apify datasets](https://blog.apify.com/ai-agent-workflow/)
- [AI agent architecture](https://blog.apify.com/ai-agent-architecture)
- [What are AI agents?](https://blog.apify.com/what-are-ai-agents/)
- [How to build an AI agent on Apify](https://blog.apify.com/how-to-build-an-ai-agent/)
- [What is Anthropic's Model Context Protocol (and why does it matter)?](https://blog.apify.com/what-is-model-context-protocol/)

# Changelog

This Actor's version history is a separate document: https://apify.com/jiri.spilka/dataset-query-engine/changelog.md

# Actor input Schema

## `query` (type: `string`):

Provide a query to specify the data to be retrieved from a dataset. The query can be in natural language or SQL format. For SQL, use the table name 'dataset'.

Examples (when your dataset contains data scraped from Google Maps):

- Find restaurant names with reviews above 4 and with at least 100 reviews.
- SELECT title, totalScore, reviewsCount FROM dataset;

## `datasetId` (type: `string`):

Dataset ID to retrieve data from.

## `modelName` (type: `string`):

Specify the LLM for SQL generation and query synthesis. Currently supports OpenAI models with varying capabilities and performance characteristics.

## `refreshDataset` (type: `boolean`):

In standby mode, the dataset is loaded once and reused to optimize performance. This parameter allows reloading the dataset to ensure updated data is available.

## `limit` (type: `integer`):

Maximum number of items to return. By default, there is no limit.

## `offset` (type: `integer`):

Number of items that should be skipped at the start. The default value is 0.

## `useAgent` (type: `boolean`):

When enabled, an AI Agent is used over a deterministic workflow. When to use an AI Agent? The agent can solve a broader range of tasks but can also be less reliable.

## `debugMode` (type: `boolean`):

When enabled, Actor will log additional debug messages from the Agent, including detailed reasoning steps.

## Actor input object example

```json
{
  "modelName": "gpt-4o-mini",
  "refreshDataset": false,
  "offset": 0,
  "useAgent": true,
  "debugMode": false
}
```

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {};

// Run the Actor and wait for it to finish
const run = await client.actor("jiri.spilka/dataset-query-engine").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {}

# Run the Actor and wait for it to finish
run = client.actor("jiri.spilka/dataset-query-engine").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{}' |
apify call jiri.spilka/dataset-query-engine --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,jiri.spilka/dataset-query-engine"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/vYhZNvISh70s4czXl/builds/uBmYLYjOYmfcZCMvV/openapi.json