> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oleander.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Lake

> Query the lake, explore catalogs, register catalogs, and launch a DuckDB terminal from the CLI.

Run SQL queries, browse your catalogs and tables, register S3 Tables catalogs, and open a DuckDB terminal connected to your lake from the command line. For a full overview of the lake, see the [Lake documentation](/platform/lake/query).

## Query

`oleander query` sends SQL through the [query router](/platform/query-routing): oleander parses it, estimates how much data the referenced tables hold, and picks the engine and machine size to match. There is nothing to size by hand.

```bash theme={null}
oleander query "<sql>"
```

**Example:**

```bash theme={null}
oleander query "SELECT * FROM oleander.default.flowers LIMIT 10"
```

Results are printed as a formatted table with row counts and execution time, under a block naming the engine that ran the query, the sandbox or cluster shape, the estimated input, and the router's reasons.

`oleander query` is read-only. Anything that changes data - a `SELECT` you want written to a table, or an `INSERT`, `UPDATE`, `DELETE`, `MERGE`, or DDL statement - goes to [`oleander query submit`](#write-results-with-query-submit). Mutating SQL is refused before the request leaves your machine.

### Explain before you run

`--explain` prints the engine, estimated input size, and reason trail without executing anything or spending compute. Worth doing before a query you expect to be large.

```bash theme={null}
oleander query "SELECT * FROM oleander.default.events" --explain
```

```
✔ Planned (nothing ran, no compute spent)

Engine: bloom (interactive) · 4 vCPU / 8 GB · ~3.4 GiB input
Tables: oleander.default.events
  • Estimated input 3.4 GiB from 1/1 Iceberg table snapshot(s).
  • Medium input: scaling the Bloom sandbox instead of switching engines.
  • Sandbox sized to 4 vCPU / 8 GB.
```

The same block prints under every result, so the engine choice is visible where the query ran.

### Pick an engine yourself

Leave the engine on `auto` unless you have a reason. `--engine` accepts `auto`, `duckdb`, `polars`, or `bloom`; Spark is not an option here because it never returns rows.

```bash theme={null}
oleander query "SELECT * FROM oleander.default.flowers LIMIT 10" --engine duckdb
```

An engine that cannot serve the query returns a capability error rather than rerouting.

### Polars scripts

Pass `--script` instead of SQL to run a Python [Polars](/platform/polars) script that assigns `result`, declaring the tables it reads with `--table`. This forces the Polars engine.

```bash theme={null}
oleander query --script ./job.py --table events=oleander.default.events
```

### Write results with `query submit`

`oleander query submit` takes everything that changes data: a `SELECT` plus `--destination`, or a statement that names its own target.

```bash theme={null}
# Write a result set to a table
oleander query submit "SELECT * FROM oleander.default.flowers" \
  --destination default.flower_sample

# Append instead of replacing, and block until the run finishes
oleander query submit "SELECT * FROM oleander.default.flowers" \
  --destination default.flower_sample \
  --write-mode append \
  --wait

# A statement that writes on its own needs no destination
oleander query submit "DELETE FROM oleander.default.flower_sample WHERE species IS NULL"
```

<Warning>
  `--write-mode` defaults to `overwrite`, which replaces the destination table's contents.
</Warning>

Whether a write finishes on the call is the router's choice, not yours, so read the state it reports:

| Output           | Meaning                                            |
| ---------------- | -------------------------------------------------- |
| `Write complete` | The write already landed. Nothing to poll.         |
| A run id         | A job is running and the write is not visible yet. |

Add `--wait` to block on a submitted run until it reaches a terminal state; `--poll-interval` (default 10s) and `--timeout` (default 600s) control the polling. A run that fails or is aborted exits non-zero.

### Options

| Option                      | Applies to | Description                                                             |
| --------------------------- | ---------- | ----------------------------------------------------------------------- |
| `--engine <engine>`         | both       | `auto` (default), `duckdb`, `polars`, `bloom`; `spark` on `submit` only |
| `--explain`                 | both       | Print the routing decision without running or submitting anything       |
| `--json`                    | both       | Print the raw API response as JSON                                      |
| `--script <file>`           | both       | Polars script that assigns `result`, run instead of SQL                 |
| `--table <alias=ns.table>`  | both       | Table a `--script` reads. Repeatable.                                   |
| `--destination <ns.table>`  | `submit`   | Table to write the result to                                            |
| `--write-mode <mode>`       | `submit`   | `overwrite` (default) or `append`                                       |
| `--wait`                    | `submit`   | Block until an asynchronous run reaches a terminal state                |
| `--poll-interval <seconds>` | `submit`   | Seconds between `--wait` polls (default `10`)                           |
| `--timeout <seconds>`       | `submit`   | Seconds to wait before giving up (default `600`)                        |

<Note>
  A 402 or 403 on a query is a billing decision, not a transient failure. The CLI prints what it asks for - settle billing, upgrade the plan, add a card, or run a read on `--engine duckdb`. Retrying the same query fails identically.
</Note>

<Note>
  `oleander query --save` and `--table-name` are gone: a read no longer writes a table. Use `oleander query submit --destination` instead.
</Note>

## Catalogs

### List catalogs

List all available catalogs in your organization:

```bash theme={null}
oleander catalogs list
```

### Register an S3 Tables catalog

Register a catalog backed by S3 Tables:

```bash theme={null}
oleander catalogs register <catalog_name> \
  --region <region> \
  --account-id <aws_account_id> \
  --bucket <bucket_name> \
  --role-arn <role_arn>
```

`--type` is optional and defaults to `s3tables`.

### Get catalog credentials

Retrieve short-lived credentials for a registered catalog:

```bash theme={null}
oleander catalogs credentials <catalog_name>
```

Pass `--json` to get raw JSON output (useful for scripting):

```bash theme={null}
oleander catalogs credentials my-catalog --json
```

### List namespaces

```bash theme={null}
oleander catalogs namespaces list --catalog <catalog_name>
```

Omit `--catalog` to use the `oleander` default catalog. Pass `--json` for JSON output.

### Create a namespace

```bash theme={null}
oleander catalogs namespaces create <namespace> --catalog <catalog_name>
```

`--catalog` defaults to `oleander`.

### List tables

List tables in a catalog namespace:

```bash theme={null}
oleander catalogs tables list --catalog <catalog_name> --namespace <namespace>
```

If you omit `--catalog` or `--namespace`, the CLI prompts you interactively.

### Describe a table

View the schema of a table:

```bash theme={null}
oleander catalogs tables describe \
  --catalog <catalog_name> \
  --namespace <namespace> \
  --table <table_name>
```

If you omit any of `--catalog`, `--namespace`, or `--table`, the CLI prompts you interactively.

### Create a table

Create a new Iceberg table from a JSON schema definition:

```bash theme={null}
oleander catalogs tables create <table_name> \
  --catalog <catalog_name> \
  --namespace <namespace> \
  --schema '{"type":"struct","fields":[{"id":1,"name":"id","required":true,"type":"long"},{"id":2,"name":"name","required":false,"type":"string"}]}'
```

Pass a file path prefixed with `@` or just a path to load the schema from a file:

```bash theme={null}
oleander catalogs tables create events \
  --catalog oleander \
  --namespace default \
  --schema ./schema.json
```

Optionally include a partition spec:

```bash theme={null}
oleander catalogs tables create events \
  --schema ./schema.json \
  --partition-spec ./partition.json
```

`--catalog` defaults to `oleander`. `--namespace` defaults to `default`.

### Delete a table

```bash theme={null}
oleander catalogs tables delete <table_name> \
  --catalog <catalog_name> \
  --namespace <namespace>
```

`--catalog` defaults to `oleander`. `--namespace` defaults to `default`.

### Load data into a table

Create a new table and populate it from a local file or a remote URI. The source can be a local Parquet, CSV, or JSON file, or an S3/HTTPS URI.

```bash theme={null}
oleander catalogs tables load \
  --table <table_name> \
  --source <path_or_uri> \
  --file-type parquet
```

**Local file example:**

```bash theme={null}
oleander catalogs tables load \
  --table flowers \
  --source ./data/flowers.parquet \
  --file-type parquet
```

**Remote URI example:**

```bash theme={null}
oleander catalogs tables load \
  --table flowers \
  --source s3://my-bucket/data/flowers.parquet \
  --file-type parquet
```

| Flag                   | Description                              |
| ---------------------- | ---------------------------------------- |
| `--table <name>`       | Destination table name (required)        |
| `--source <path\|uri>` | Local file path or remote URI (required) |
| `--file-type <type>`   | `parquet`, `csv`, or `json` (required)   |
| `--catalog <name>`     | Catalog name (defaults to `oleander`)    |
| `--namespace <name>`   | Namespace (defaults to `default`)        |

### Schema evolution

Add, rename, or drop columns on an existing table. Pass the operation as a JSON body - either inline or from a file.

**Add columns:**

```bash theme={null}
oleander catalogs tables schema add \
  --catalog oleander \
  --namespace default \
  --table events \
  --input '{"columns":[{"id":3,"name":"created_at","required":false,"type":"timestamptz"}]}'
```

**Rename a column:**

```bash theme={null}
oleander catalogs tables schema rename \
  --catalog oleander \
  --namespace default \
  --table events \
  --input '{"from":"created_at","to":"event_time"}'
```

**Drop columns:**

```bash theme={null}
oleander catalogs tables schema drop \
  --catalog oleander \
  --namespace default \
  --table events \
  --input '{"columns":["event_time"]}'
```

All three schema commands accept `--input` as either an inline JSON string or a path to a JSON file.

## DuckDB terminal

Launch a DuckDB terminal pre-configured with your registered catalogs:

```bash theme={null}
oleander duckdb
```

The CLI loads registered `lakekeeper` and `s3tables` catalogs into DuckDB before opening the interactive shell.

```sql theme={null}
SHOW ALL TABLES;
```

<Note>
  The `duckdb` command requires [DuckDB](https://duckdb.org/docs/installation) to be installed on your machine.
</Note>