Analytics with DuckDB

Overture Maps publishes open map data as Parquet files in a public S3 bucket. Its places dataset alone is about 81 million points of interest worldwide, in 11 GB of Parquet. This guide attaches that bucket to an Archil disk and queries it in place with DuckDB, with no download step and no AWS credentials. You’ll run ad-hoc SQL against the bucket from a persistent sandbox, save an extract as a DuckDB database on a second disk, put a small HTTP query endpoint in front of it, and run a one-off query with serverless execution.

Prerequisites

The Overture bucket is in AWS us-west-2, so this guide uses the aws-us-west-2 Archil region. Export an API key created in that region, then install the SDK for the language you’ll orchestrate from. You’ll also use curl to call the query endpoint.

export ARCHIL_API_KEY=key-...
export ARCHIL_REGION=aws-us-west-2

npm init -y && npm pkg set type=module
npm install disk tsx

pip install archil

Save each script in this guide in your project directory. Run TypeScript scripts with npx tsx <name>.ts and Python scripts with python <name>.py.

Create the disks and a sandbox

You need two disks:

overture-maps is backed by the public bucket, using the release/2026-09-23.0/ prefix as its root. Archil reads the Parquet files from the bucket as DuckDB asks for them, and caches what it reads.
duckdb-workspace is a regular Archil disk for everything you write: the DuckDB binary and the database you build.

A disk backed by a public bucket is read-only: Archil has no credentials to write to the bucket. Always mount it with readOnly: true (read_only=True in Python), in sandboxes and in serverless commands, and write your output to another disk.

This script runs inside the sandbox. It shows what the bucket contains, puts the DuckDB command-line binary on the workspace disk, and installs the DuckDB Python package for the query server you’ll run later.

setup.sh

set -euo pipefail

ls /mnt/overture/theme=places/type=place | wc -l
du -sh --apparent-size /mnt/overture/theme=places/type=place

# Keep the CLI on the workspace disk so serverless commands can use it too
mkdir -p /mnt/work/bin
curl -sfL -o /tmp/duckdb.zip https://github.com/duckdb/duckdb/releases/download/v1.5.6/duckdb_cli-linux-amd64.zip
unzip -oq /tmp/duckdb.zip -d /mnt/work/bin
/mnt/work/bin/duckdb --version

pip install -q --root-user-action=ignore duckdb==1.5.6

Create both disks and a sandbox that mounts the bucket-backed disk read-only at /mnt/overture and the workspace at /mnt/work, then run the script:

import * as archil from "disk";
import { readFile } from "node:fs/promises";

const { disk: overture } = await archil.createDisk({
  name: "overture-maps",
  mounts: [{ type: "s3", bucketName: "overturemaps-us-west-2", bucketPrefix: "release/2026-09-23.0/" }],
});
const { disk: workspace } = await archil.createDisk({ name: "duckdb-workspace" });

const sandbox = await archil.createSandbox({
  name: "duckdb",
  baseImage: "python:3.13",
  vcpuCount: 4,
  memSizeMiB: 8192,
  mounts: [
    { disk: overture, path: "/mnt/overture", readOnly: true },
    { disk: workspace, path: "/mnt/work" },
  ],
});

await sandbox.files.uploadFile(await readFile("setup.sh"), "/root/setup.sh");
const setup = await sandbox.exec("bash /root/setup.sh", { timeoutSeconds: 600 });
if (setup.exitCode !== 0) throw new Error(setup.stderr);
console.log(setup.stdout);

import archil

overture = archil.create_disk(
    name="overture-maps",
    mounts=[archil.S3Mount(bucket_name="overturemaps-us-west-2", bucket_prefix="release/2026-09-23.0/")],
).disk
workspace = archil.create_disk(name="duckdb-workspace").disk

sandbox = archil.create_sandbox(
    name="duckdb",
    base_image="python:3.13",
    vcpu_count=4,
    mem_size_mib=8192,
    mounts=[
        archil.SandboxMountSpec(disk=overture, path="/mnt/overture", read_only=True),
        archil.SandboxMountSpec(disk=workspace, path="/mnt/work"),
    ],
)

sandbox.files.upload_file("setup.sh", "/root/setup.sh")
setup = sandbox.exec("bash /root/setup.sh", timeout_seconds=600)
if setup.exit_code != 0:
    raise RuntimeError(setup.stderr)
print(setup.stdout)

The places theme is 16 Parquet files:

16
11G	/mnt/overture/theme=places/type=place
v1.5.6 (Variegata) 069cc9f9b5

Query the bucket in place

Every Overture row has a bbox column with its bounding box, and the files store min/max statistics for it. When a query filters on bbox, DuckDB uses those statistics to skip the row groups that can’t match, so it only reads a small part of the 11 GB. This script finds the sandbox by name and runs two statements. The first breaks down the places in Seattle by category, straight from the bucket. The second saves every place in Washington state as a table in places.duckdb on the workspace disk.

import * as archil from "disk";

const sandbox = (await archil.listSandboxes()).find((s) => s.name === "duckdb")!;
const duckdb = async (sql: string) => {
  const res = await sandbox.exec(`bin/duckdb places.duckdb -c "${sql}"`, { cwd: "/mnt/work" });
  console.log(res.stdout, res.stderr);
};

await duckdb(`
  SELECT basic_category AS category, count(*) AS places
  FROM '/mnt/overture/theme=places/type=place/*.parquet'
  WHERE bbox.xmin BETWEEN -122.44 AND -122.24 AND bbox.ymin BETWEEN 47.49 AND 47.74
  GROUP BY 1 ORDER BY 2 DESC LIMIT 5`);

await duckdb(`
  CREATE OR REPLACE TABLE places AS
  SELECT id, names.primary AS name, basic_category AS category, addresses[1].locality AS city,
         bbox.xmin AS lon, bbox.ymin AS lat
  FROM '/mnt/overture/theme=places/type=place/*.parquet'
  WHERE bbox.xmin BETWEEN -124.8 AND -116.9 AND bbox.ymin BETWEEN 45.5 AND 49.0
    AND addresses[1].region = 'WA';
  SELECT count(*) AS places FROM places`);

import archil

sandbox = next(s for s in archil.list_sandboxes() if s.name == "duckdb")

def duckdb(sql):
    res = sandbox.exec(f"""bin/duckdb places.duckdb -c "{sql}" """, cwd="/mnt/work")
    print(res.stdout, res.stderr)

duckdb("""
  SELECT basic_category AS category, count(*) AS places
  FROM '/mnt/overture/theme=places/type=place/*.parquet'
  WHERE bbox.xmin BETWEEN -122.44 AND -122.24 AND bbox.ymin BETWEEN 47.49 AND 47.74
  GROUP BY 1 ORDER BY 2 DESC LIMIT 5""")

duckdb("""
  CREATE OR REPLACE TABLE places AS
  SELECT id, names.primary AS name, basic_category AS category, addresses[1].locality AS city,
         bbox.xmin AS lon, bbox.ymin AS lat
  FROM '/mnt/overture/theme=places/type=place/*.parquet'
  WHERE bbox.xmin BETWEEN -124.8 AND -116.9 AND bbox.ymin BETWEEN 45.5 AND 49.0
    AND addresses[1].region = 'WA';
  SELECT count(*) AS places FROM places""")

┌──────────────────────┬────────┐
│       category       │ places │
│       varchar        │ int64  │
├──────────────────────┼────────┤
│ restaurant           │   3745 │
│ home_service         │   2791 │
│ real_estate_service  │   2635 │
│ professional_service │   2238 │
│ financial_service    │   2232 │
└──────────────────────┴────────┘

┌────────┐
│ places │
│ int64  │
├────────┤
│ 382501 │
└────────┘

In our runs, the Seattle query took under a second the first time, while DuckDB fetched the row groups it needed from the bucket, and about 0.15 seconds on repeat runs, which Archil serves from its cache. Saving the 382,501 Washington places took under a second. places.duckdb is an ordinary file on the workspace disk, so it survives the sandbox stopping, and anything that mounts the workspace can query it.

Serve queries over HTTPS

This server opens places.duckdb read-only and runs the SQL in each POST body. External file access is turned off and the setting is locked, so a query can read the places table but not other files in the sandbox or on the disks.

server.py

import json
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer

import duckdb

db = duckdb.connect(
    "/mnt/work/places.duckdb",
    read_only=True,
    config={"enable_external_access": False, "lock_configuration": True},
)


class QueryHandler(BaseHTTPRequestHandler):
    def do_POST(self):
        sql = self.rfile.read(int(self.headers["Content-Length"])).decode()
        try:
            result = db.cursor().execute(sql)
            body = {"columns": [c[0] for c in result.description], "rows": result.fetchall()}
            status = 200
        except duckdb.Error as e:
            body, status = {"error": str(e)}, 400
        payload = json.dumps(body, default=str).encode()
        self.send_response(status)
        self.send_header("Content-Type", "application/json")
        self.send_header("Content-Length", str(len(payload)))
        self.end_headers()
        self.wfile.write(payload)


ThreadingHTTPServer(("0.0.0.0", 8080), QueryHandler).serve_forever()

Upload it, run it as a supervised service so it restarts on failure and comes back when the sandbox reboots, and create a port token. Sandbox ports are private by default: a request without a valid token in the X-Archil-Token header gets a 401, so you don’t need to build authentication into the server.

import * as archil from "disk";
import { readFile } from "node:fs/promises";

const sandbox = (await archil.listSandboxes()).find((s) => s.name === "duckdb")!;
await sandbox.files.uploadFile(await readFile("server.py"), "/root/server.py");
await sandbox.exec("archil-sandbox services create duckdb-api --tcp-port 8080 -- python3 /root/server.py");

const { hostname, token } = await sandbox.createPortToken(8080, { ttl: "1h" });
console.log(`export DUCKDB_URL=https://${hostname}`);
console.log(`export DUCKDB_TOKEN=${token}`);

import archil

sandbox = next(s for s in archil.list_sandboxes() if s.name == "duckdb")
sandbox.files.upload_file("server.py", "/root/server.py")
sandbox.exec("archil-sandbox services create duckdb-api --tcp-port 8080 -- python3 /root/server.py")

port_token = sandbox.create_port_token(8080, ttl="1h")
print(f"export DUCKDB_URL=https://{port_token.hostname}")
print(f"export DUCKDB_TOKEN={port_token.token}")

Run the two export lines the script prints, then send a query from your machine:

curl -s "$DUCKDB_URL" -H "X-Archil-Token: $DUCKDB_TOKEN" \
  --data "SELECT city, count(*) AS places, count(*) FILTER (WHERE category = 'coffee_shop') AS coffee_shops
          FROM places GROUP BY 1 ORDER BY 2 DESC LIMIT 5"

{"columns": ["city", "places", "coffee_shops"], "rows": [["Seattle", 57689, 592], ["Spokane", 16961, 190], ["Vancouver", 16671, 147], ["Tacoma", 15690, 133], ["Bellevue", 15158, 76]]}

Each request took about 0.2 seconds end to end from our machine, including the TLS handshake. The same request without the header returns 401. Hand the URL and token to a dashboard, a notebook, or an agent tool, and create a new token when this one expires.

Run a one-off query with serverless execution

For a quick question, you don’t have to go through the sandbox. Serverless execution runs a command in a short-lived VM, and archil.exec can mount several disks at once, each under /mnt/archil/<name>, which is also the working directory. This command mounts both disks read-only and runs the DuckDB binary from the workspace against the bucket, this time for Paris:

import * as archil from "disk";

const [overture] = await archil.listDisks({ name: "overture-maps" });
const [workspace] = await archil.listDisks({ name: "duckdb-workspace" });

const { stdout, timing } = await archil.exec({
  disks: {
    overture: { disk: overture, readOnly: true },
    work: { disk: workspace, readOnly: true },
  },
  command: `work/bin/duckdb -c "SELECT basic_category AS category, count(*) AS places
    FROM 'overture/theme=places/type=place/*.parquet'
    WHERE bbox.xmin BETWEEN 2.25 AND 2.42 AND bbox.ymin BETWEEN 48.81 AND 48.91
    GROUP BY 1 ORDER BY 2 DESC LIMIT 5"`,
});
console.log(stdout, `${timing.totalMs} ms`);

import archil

overture = archil.list_disks(name="overture-maps")[0]
workspace = archil.list_disks(name="duckdb-workspace")[0]

res = archil.exec(
    disks={
        "overture": archil.ExecMountSpec(overture, read_only=True),
        "work": archil.ExecMountSpec(workspace, read_only=True),
    },
    command="""work/bin/duckdb -c "SELECT basic_category AS category, count(*) AS places
    FROM 'overture/theme=places/type=place/*.parquet'
    WHERE bbox.xmin BETWEEN 2.25 AND 2.42 AND bbox.ymin BETWEEN 48.81 AND 48.91
    GROUP BY 1 ORDER BY 2 DESC LIMIT 5" """,
)
print(res.stdout, f"{res.timing.total_ms} ms")

┌────────────────────────────┬────────┐
│          category          │ places │
│          varchar           │ int64  │
├────────────────────────────┼────────┤
│ restaurant                 │  17286 │
│ fashion_and_apparel_store  │   9977 │
│ professional_service       │   7466 │
│ personal_or_beauty_service │   5253 │
│ food_and_beverage_store    │   4384 │
└────────────────────────────┴────────┘
 1245 ms

totalMs is measured by the server, from receiving the request to returning the result. In our runs the first Paris query took about 1.2 seconds, including starting the VM, mounting both disks, and reading the row groups it needed from the bucket. Repeat runs took under a second. Each serverless command is limited to 5 minutes, so use the sandbox for long-running work.

Clean up

Stop and delete the sandbox, then delete both disks. Deleting overture-maps doesn’t affect the public bucket.

import * as archil from "disk";

const sandbox = (await archil.listSandboxes()).find((s) => s.name === "duckdb")!;
await sandbox.stop();
await sandbox.delete();
for (const name of ["overture-maps", "duckdb-workspace"]) {
  const [disk] = await archil.listDisks({ name });
  await disk.delete();
}

import archil

sandbox = next(s for s in archil.list_sandboxes() if s.name == "duckdb")
sandbox.stop()
sandbox.delete()
for name in ["overture-maps", "duckdb-workspace"]:
    archil.list_disks(name=name)[0].delete()

Next steps

S3 Object Storage — attach your own buckets with read-write access
Persistent sandboxes — services, port tokens, and stopping a sandbox between sessions
Serverless execution — run commands against several disks at once
Search logs across sandboxes — another analytics workflow on Archil disks

Agents

Analytics

Training

Apps & databases

Use Archil on other platforms

Analytics with DuckDB

Prerequisites

Create the disks and a sandbox

Query the bucket in place

Serve queries over HTTPS

Run a one-off query with serverless execution

Clean up

Next steps

​Prerequisites

​Create the disks and a sandbox

​Query the bucket in place

​Serve queries over HTTPS

​Run a one-off query with serverless execution

​Clean up

​Next steps

Prerequisites

Create the disks and a sandbox

Query the bucket in place

Serve queries over HTTPS

Run a one-off query with serverless execution

Clean up

Next steps