# SQL for Datasets: Join, Merge & Dedupe Files (`maliksaid/sql-over-datasets`) Actor

Run SQL on Apify datasets and CSV, JSON or Parquet files with DuckDB. Join, merge, dedupe, filter, group and transform data in one query. Presets for dedupe, VLOOKUP join and merge. Only-new-rows mode. Save rows to a dataset or a CSV, JSON or Parquet file.

- **URL**: https://apify.com/maliksaid/sql-over-datasets.md
- **Developed by:** [Malik Said](https://apify.com/maliksaid) (community)
- **Categories:** Developer tools, AI, Automation
- **Security:** Limited permissions
- **Stats:** 1 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.30 / 1,000 output rows

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

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

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

## SQL for Datasets: Join, Merge & Dedupe Files

SQL for Datasets runs one SQL query over your Apify datasets and your CSV, JSON or Parquet files. Join two datasets like a VLOOKUP, merge many runs into one table, remove duplicates, filter, group and sort. The Actor uses DuckDB, a fast analytical SQL engine. Results go to a dataset, or to a CSV, JSON, JSON Lines or Parquet file.

### Three jobs people use it for

- **Look up values from one dataset in another (VLOOKUP).** Pick the join preset, name the key column, and get one table with columns from both.
- **Merge a week of scraper runs into one table.** Add each run's dataset ID and pick the merge preset.
- **Drop rows you already have.** Turn on Only new rows and schedule the Actor. Each run saves only the rows that are new.

### SQL for datasets: what it does

- Reads Apify datasets, files in your key-value stores, and CSV, TSV, JSON, JSON Lines or Parquet files from URLs.
- Gives each input a table name: `dataset1`, `file1`, `record1`, or a name you choose.
- Runs any DuckDB SELECT query: joins, `GROUP BY`, window functions, `QUALIFY`, `PIVOT`, `UNION ALL BY NAME` and more.
- Runs several statements in one query. The last statement must return rows.
- Offers presets that need no SQL: merge all inputs, remove duplicates, and join two inputs by key.
- Only new rows mode: a scheduled run saves only rows that earlier runs did not save.
- Saves rows to the dataset, a file, or both.

### SQL inputs and table names

| Input | Table names | Example |
|---|---|---|
| Apify datasets | `dataset1`, `dataset2`, ... | The results of another Actor |
| File URLs | `file1`, `file2`, ... or `name = URL` | `orders = https://example.com/orders.csv` |
| Key-value store records | `record1`, `record2`, ... or `name = key` | `prices = prices.parquet` |

The Actor detects the format from the file's first bytes. CSV delimiters and column types are detected too. CSV files in Latin-1 (older Excel exports) are read as well. XLSX files are not read. Convert them to CSV first, for example with the Spreadsheet Converter Actor.

#### Other input options

| Input | Default | What it does |
|---|---|---|
| Key-value store with files | None | A key-value store of yours that holds CSV, JSON or Parquet files. The Actor only reads this store. Field `keyValueStoreId` |
| Record keys | None | Keys of the records to read from that store. They become `record1`, `record2` and so on. Write `name = key` to choose the table name. Field `keyValueStoreKeys` |
| Save rows to the dataset | On | Saves each result row as one dataset item, so you can export it as JSON, CSV, Excel or XML. Field `saveToDataset` |
| Maximum input size (MB) | 200 | Stops when one input file or dataset is larger than this. Field `maxFileMegabytes` |
| Query time limit (seconds) | 300 | Stops the query when it runs longer than this. Field `queryTimeoutSecs` |

### Use it after another Actor

1. Run any scraper on Apify and wait for it to finish.
2. Open the run, go to the Storage tab, and copy the Dataset ID.
3. Start SQL for Datasets and paste that ID into **Apify datasets**.
4. Pick a preset or write a query. The scraper's field names are your column names.

### How to run SQL on an Apify dataset

1. Pick one or more datasets in **Apify datasets**, or paste links in **File URLs**.
2. Write your query in **SQL query**. Use the table names above.
3. Set **Maximum rows** to 0 to save every row. The default of 100 keeps test runs cheap.
4. Optional: pick a file format in **Also save a file**.
5. Click **Start**. Open the dataset, or the **Result file** tab.

Example query over two datasets:

```sql
SELECT a.url, a.title, b.price
FROM dataset1 a
LEFT JOIN dataset2 b USING (url)
WHERE b.price IS NOT NULL
ORDER BY b.price DESC
```

### SQL presets: merge, remove duplicates, join

| Preset | What it does |
|---|---|
| Merge all inputs | Stacks every input into one table. Columns match by name. A `source_table` column says where each row came from |
| Remove duplicates | Keeps the first row for each value of **Key columns**. Without key columns it removes rows that are equal in every column |
| Join two inputs (VLOOKUP) | Adds the columns of the second input to the first one where **Key columns** match. Left, inner, full or right join. Rows keep the order of the first input |
| Join, anti | Keeps the rows of the first input that have no match in the second input. Only the first input's columns are returned |

Turn on **Add match column** to get a `_matched` column with each joined row. It is true when the row found a match in both inputs.

Example: find the leads that are missing from your CRM export. Put the leads dataset first and the CRM file second.

```json
{
    "preset": "join",
    "joinType": "anti",
    "keyColumns": ["email"],
    "datasetIds": ["<leads dataset ID>"],
    "fileUrls": ["https://example.com/crm-export.csv"]
}
```

### Output

Each result row becomes one dataset item. The fields are the columns your query returns. Rows from a test run of the sample query, GDP per person from two public CSV files:

```json
{
    "country": "Luxembourg",
    "country_code": "LUX",
    "year": 2023,
    "gdp_per_person_usd": 128678
}
```

```json
{
    "country": "Ireland",
    "country_code": "IRL",
    "year": 2023,
    "gdp_per_person_usd": 102725
}
```

Dates come out as `2026-10-19`. Timestamps come out in UTC. Nested JSON stays nested.

The run also writes an `OUTPUT` record to its key-value store. It holds the status, the final SQL, each input table with its columns, the result columns, the number of rows saved, and a link to the result file. When the query has an error, the run ends with the error in its status message and in `OUTPUT`.

### SQL sandbox and limits

- The query can read only the inputs you list. It cannot open other files, reach the network or install DuckDB extensions. Settings are locked.
- File URLs must be public http or https links. Local and private network addresses are refused. The Actor obeys robots.txt and does not log in.
- Each input can be up to 200 MB by default (setting up to 2,000 MB). Inputs are saved to disk, so they can be larger than the run's memory.
- DuckDB gets part of the run's memory and spills large sorts and joins to disk. A very large query can still run out of memory. Filter and aggregate early in the query.
- Each download or API request waits at most 60 seconds and is retried twice. The query stops after the time limit you set (300 seconds by default).

### Pricing

You pay per event:

- $0.02 per run.
- $0.30 per 1,000 output rows ($0.0003 per row), saved to the dataset or a file. A row saved to both counts once.
- $0.004 per 1,000 items read from Apify datasets. Each started block of 1,000 items counts as one. Reading file URLs and key-value store records is free.

A run that reads one dataset of 20,000 items and saves 5,000 rows costs $1.60. Rows that the query filters out are not charged as output rows. Set a maximum cost per run in the run options. The Actor then stops at that limit.

### Only new rows

Turn on **Only new rows** and schedule the Actor. Each run saves only rows that earlier runs with the same **Monitor name** did not save. Give **Key columns** to compare rows by those columns only, for example a URL or an ID. Without key columns the whole row is compared. The memory sits in the named key-value store `sql-over-datasets-state`. Inputs that still send the old field `stateKey` keep working.

### Run it on a schedule

1. Set the same inputs you tested, and turn on **Only new rows**.
2. Give **Key columns** (for example an ID or a URL) and a **Monitor name** for this watch list.
3. Create a Schedule in Apify and pick this Actor and its saved input.
4. The first run saves every row. Each later run saves only rows with a new key.

### FAQ

**Which SQL dialect does it use?** DuckDB SQL. It is close to PostgreSQL. Functions such as `regexp_extract`, `strftime`, `list_aggregate` and `json_extract` work.

**Can I read an Excel file?** No. Convert it to CSV with the Spreadsheet Converter Actor, then run SQL on the CSV.

**Does my data leave Apify?** No. The query runs inside the Actor's container. The temp files are deleted at the end of the run.

**Why do I get only 100 rows?** The default **Maximum rows** is 100. Set it to 0 to save every row.

### Support

Open an issue on the Actor's Issues tab. Please include the run ID and your query.

# Actor input Schema

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

DuckDB SQL. Each input is a table: dataset1, dataset2, file1, file2, record1 and so on, or the name you give it with 'name = ...'. You can run several statements separated by semicolons. The last one must return rows. Leave empty when you use a preset. With no inputs and no query, the Actor runs a sample on two public CSV files.

## `preset` (type: `string`):

Your SQL query runs the query above. Merge stacks all inputs and matches columns by name. Remove duplicates keeps the first row per key. Join (VLOOKUP) adds the columns of the second input to the first one by key.

## `keyColumns` (type: `array`):

Column names for Remove duplicates, Join and Only new rows. Remove duplicates without keys compares whole rows. Only new rows without keys compares whole rows.

## `joinType` (type: `string`):

Used by the Join preset. Left keeps every row of the first input. Inner keeps rows with a match only. Full keeps every row of both inputs. Right keeps every row of the second input. Anti keeps the rows of the first input that have no match, with the first input's columns only.

## `addMatchColumn` (type: `boolean`):

Used by the Join preset. Adds a `_matched` column: true when the row found a match in both inputs, false when it did not.

## `datasetIds` (type: `array`):

Pick datasets, for example the results of other Actors. They become the tables dataset1, dataset2 and so on. The Actor only reads them.

## `fileUrls` (type: `array`):

Links to CSV, TSV, JSON, JSON Lines or Parquet files, one per line. They become file1, file2 and so on. Write 'name = https://...' to choose the table name. The Actor reads robots.txt on each host and refuses local and private network addresses.

## `keyValueStoreId` (type: `string`):

Optional. Pick one of your key-value stores that holds CSV, JSON or Parquet files. The Actor only reads this store.

## `keyValueStoreKeys` (type: `array`):

Keys of the records to read from the store above. They become record1, record2 and so on. Write 'name = key' to choose the table name.

## `saveToDataset` (type: `boolean`):

Save each result row as one dataset item. You can then export the dataset from Apify as JSON, CSV, Excel or XML.

## `fileFormat` (type: `string`):

Save the whole result as one file in the run's key-value store, under the key result.csv, result.json, result.jsonl or result.parquet.

## `onlyNewRows` (type: `boolean`):

Save only rows that earlier runs with the same monitor name did not save. Schedule the Actor with this on to get a feed of new rows. Uses Key columns when you give them.

## `monitorName` (type: `string`):

Name of the memory that Only new rows uses. Use a different name for each separate job. Inputs that still send the old field `stateKey` keep working.

## `maxRows` (type: `integer`):

Save at most this many result rows. The default keeps test runs short and cheap. Set 0 to save every row.

## `maxFileMegabytes` (type: `integer`):

Stop when one input file or dataset is larger than this. Inputs are saved to disk, so they can be larger than the run's memory.

## `queryTimeoutSecs` (type: `integer`):

Stop the query when it runs longer than this.

## Actor input object example

```json
{
  "query": "SELECT g.\"Country Name\" AS country,\n       g.\"Country Code\" AS country_code,\n       g.Year AS year,\n       round(g.Value / p.Value) AS gdp_per_person_usd\nFROM gdp g\nJOIN population p USING (\"Country Code\", Year)\nWHERE g.Year = (SELECT max(Year) FROM gdp)\nORDER BY gdp_per_person_usd DESC",
  "preset": "custom",
  "keyColumns": [],
  "joinType": "left",
  "addMatchColumn": false,
  "fileUrls": [
    "gdp = https://datahub.io/core/gdp/r/gdp.csv",
    "population = https://datahub.io/core/population/r/population.csv"
  ],
  "keyValueStoreKeys": [],
  "saveToDataset": true,
  "fileFormat": "none",
  "onlyNewRows": false,
  "monitorName": "default",
  "maxRows": 100,
  "maxFileMegabytes": 200,
  "queryTimeoutSecs": 300
}
```

# Actor output Schema

## `results` (type: `string`):

No description

## `files` (type: `string`):

No description

## `summary` (type: `string`):

No description

# 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 = {
    "query": `SELECT g."Country Name" AS country,
       g."Country Code" AS country_code,
       g.Year AS year,
       round(g.Value / p.Value) AS gdp_per_person_usd
FROM gdp g
JOIN population p USING ("Country Code", Year)
WHERE g.Year = (SELECT max(Year) FROM gdp)
ORDER BY gdp_per_person_usd DESC`,
    "fileUrls": [
        "gdp = https://datahub.io/core/gdp/r/gdp.csv",
        "population = https://datahub.io/core/population/r/population.csv"
    ],
    "maxRows": 100
};

// Run the Actor and wait for it to finish
const run = await client.actor("maliksaid/sql-over-datasets").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 = {
    "query": """SELECT g.\"Country Name\" AS country,
       g.\"Country Code\" AS country_code,
       g.Year AS year,
       round(g.Value / p.Value) AS gdp_per_person_usd
FROM gdp g
JOIN population p USING (\"Country Code\", Year)
WHERE g.Year = (SELECT max(Year) FROM gdp)
ORDER BY gdp_per_person_usd DESC""",
    "fileUrls": [
        "gdp = https://datahub.io/core/gdp/r/gdp.csv",
        "population = https://datahub.io/core/population/r/population.csv",
    ],
    "maxRows": 100,
}

# Run the Actor and wait for it to finish
run = client.actor("maliksaid/sql-over-datasets").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 '{
  "query": "SELECT g.\\"Country Name\\" AS country,\\n       g.\\"Country Code\\" AS country_code,\\n       g.Year AS year,\\n       round(g.Value / p.Value) AS gdp_per_person_usd\\nFROM gdp g\\nJOIN population p USING (\\"Country Code\\", Year)\\nWHERE g.Year = (SELECT max(Year) FROM gdp)\\nORDER BY gdp_per_person_usd DESC",
  "fileUrls": [
    "gdp = https://datahub.io/core/gdp/r/gdp.csv",
    "population = https://datahub.io/core/population/r/population.csv"
  ],
  "maxRows": 100
}' |
apify call maliksaid/sql-over-datasets --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,maliksaid/sql-over-datasets"
        }
    }
}
```

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/FeO9xgPjenasI8Aq0/builds/ZLnAZFhf7BJHCz7tv/openapi.json