> ## Documentation Index > Fetch the complete documentation index at: https://docs.archil.com/llms.txt > Use this file to discover all available pages before exploring further. # Analytics with DuckDB > Query 81 million places from the Overture Maps public bucket with DuckDB, without copying the data, and serve results over HTTPS. [Overture Maps](https://overturemaps.org) 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](https://duckdb.org), with no download step and no AWS credentials. You'll run ad-hoc SQL against the bucket from a [persistent sandbox](/compute/sandboxes/introduction), 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](/compute/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. ```bash theme={null} export ARCHIL_API_KEY=key-... export ARCHIL_REGION=aws-us-west-2 ``` ```bash TypeScript theme={null} npm init -y && npm pkg set type=module npm install disk tsx ``` ```bash Python theme={null} pip install archil ``` Save each script in this guide in your project directory. Run TypeScript scripts with `npx tsx .ts` and Python scripts with `python .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. ```bash setup.sh theme={null} 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: ```typescript setup.ts theme={null} 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); ``` ```python setup.py theme={null} 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. ```typescript explore.ts theme={null} 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`); ``` ```python explore.py theme={null} 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. ```python server.py theme={null} 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. ```typescript serve.ts theme={null} 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}`); ``` ```python serve.py theme={null} 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: ```bash theme={null} 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" ``` ```json theme={null} {"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](/compute/serverless-execution) runs a command in a short-lived VM, and `archil.exec` can mount several disks at once, each under `/mnt/archil/`, 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: ```typescript oneoff.ts theme={null} 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`); ``` ```python oneoff.py theme={null} 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. ```typescript cleanup.ts theme={null} 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(); } ``` ```python cleanup.py theme={null} 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](/data-sources/s3-object-storage) — attach your own buckets with read-write access * [Persistent sandboxes](/compute/sandboxes/introduction) — services, port tokens, and stopping a sandbox between sessions * [Serverless execution](/compute/serverless-execution) — run commands against several disks at once * [Search logs across sandboxes](/guides/analytics/log-search) — another analytics workflow on Archil disks This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.