> ## 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 [DuckDB](/platform/compute/duckdb) documentation.

## Query

`oleander query` sends SQL through the [query router](/platform/query-routing/overview): 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/compute/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
```

**CSV example:**

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

| 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`) |

**How each file type is read:**

* **CSV**: the first row is used as the column names, and column types are inferred from the values (integers, decimals, timestamps, booleans, and so on) rather than loaded as strings. A quote inside a quoted field is written as two double quotes, per RFC 4180. A CSV without a header row is not supported: its first data row would become the column names.
* **Parquet**: column names and types come from the file's schema.
* **JSON**: newline-delimited JSON (one object per line). Column names come from the object keys and types are inferred.

The load runs as a Spark job. The table is queryable once the run reaches `COMPLETE`; the run appears on the [Pipelines](/platform/observability/overview) page like any other job.

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.