---
name: dremio
description: Working with Dremio Cloud, including ingesting, transforming, and querying data. Use when the user asks about data, tables, schemas, SQL queries, datasets, analytics, or anything involving structured data.
user-invocable: false
---

# Dremio Cloud

A project contains **namespaces** (managed Iceberg storage, full DDL/DML) and **sources** (connections to external systems). Sources can be object storage (S3, ADLS), databases (Oracle, Postgres, MongoDB), or other catalogs (Unity Catalog, Glue). Tables in SQL: `"Namespace"."table"` or `"Namespace"."Folder"."table"`.

## REST API

Use the REST API for all Dremio interactions. Base URL: `https://api.dremio.cloud/v0/projects/${DREMIO_PROJECT_ID}`.

**Auth setup:** `DREMIO_PAT` and `DREMIO_PROJECT_ID` must be set. The `.env` file should use `export` and single quotes (PATs often contain `+`, `/`, `=` which can be mangled by the shell):

```
export DREMIO_PAT='your_pat_here'
export DREMIO_PROJECT_ID='your_project_id_here'
```

If the `.env` doesn't exist, ask the user to create one with their PAT and project ID.

**zsh + special characters:** PATs often contain `+`, `/`, `=` which zsh can silently corrupt. Either regenerate the PAT until you get one without `+`, or wrap REST API calls in `bash -c '.  /path/to/.env && curl ...'`.

Use `jq` (not python) to parse JSON responses.

### Search API

Find tables, views, and other entities by keyword:

```bash
curl -s -X POST "https://api.dremio.cloud/v0/projects/${DREMIO_PROJECT_ID}/search" \
  -H "Authorization: Bearer $DREMIO_PAT" -H "Content-Type: application/json" \
  -d '{"query": "search terms", "filter": "category in [\"TABLE\", \"VIEW\"]", "maxResults": 10}'
```

Filter categories: `TABLE`, `VIEW`, `FOLDER`, `SPACE`, `SOURCE`, `REFLECTION`, `UDF`, `SCRIPT`, `JOB`. Results include paths, columns, wiki descriptions, and labels.

**Important — project name in search paths:** The Search API sometimes includes the project name (e.g., `"lakehouse-project"`) as the first element of the `path` array. This is a Dremio Cloud bug. **Never use the project name in SQL paths.** When constructing SQL from search results, strip the first path element if it matches a project name rather than a namespace or source. For example, if the search returns `["my-project", "MyNamespace", "MyTable"]`, the correct SQL reference is `MyNamespace."MyTable"`, not `"my-project".MyNamespace."MyTable"`.

## Workflow

1. **Find tables.** Use the Search API with keywords. Fallback: `INFORMATION_SCHEMA."TABLES"` with `TABLE_TYPE != 'SYSTEM_TABLE'`.
2. **Understand schema.** Search API returns columns inline. For more detail, query `INFORMATION_SCHEMA."COLUMNS"`.
3. **Query.** Use the SQL API for all queries (SELECT, DDL, DML).
4. **Present results** as markdown tables. Summarize findings. Suggest follow-ups.

## Data Ingestion

- **CTAS** (`CREATE TABLE AS SELECT`) — one step, but only works on data already queryable in Dremio.
- **COPY INTO** — loads external files (CSV, JSON, Parquet) into a namespace table. **Table must exist first.**

For new data in S3: prefer CREATE TABLE + COPY INTO over promoting raw files (Iceberg is more efficient).

```sql
-- CTAS (from existing data)
CREATE TABLE Namespace.summary AS SELECT state, SUM(amount) FROM Namespace.raw GROUP BY state;

-- COPY INTO (from external files — two steps)
CREATE TABLE Namespace.my_table (id INT, name VARCHAR, created_at TIMESTAMP);
COPY INTO Namespace.my_table FROM '@source/path/to/data/' FILE_FORMAT 'parquet';
```

### COPY INTO — Critical Syntax Rules

**Path format:** Always use `@source` with **forward slashes** for paths. NEVER use dot-notation.

```sql
-- CORRECT
COPY INTO my_table FROM '@datasets/bucket-name/folder/' FILE_FORMAT 'parquet';

-- WRONG — dot-notation causes "invalid source type" errors
COPY INTO my_table FROM '@datasets."bucket"."folder"' FILE_FORMAT 'parquet';
COPY INTO my_table FROM '@source."path"."to"' FILE_FORMAT 'parquet';
```

**Source names:** Do NOT quote the source name after `@`. Quoting causes "non-existent source" errors.

```sql
-- CORRECT
COPY INTO my_table FROM '@datasets/path/to/data/' FILE_FORMAT 'parquet';

-- WRONG — quoted source name fails
COPY INTO my_table FROM '@"datasets"/path/to/data/' FILE_FORMAT 'parquet';
```

**FILE_FORMAT:** Just the format name in quotes. No `'type' =` prefix.

```sql
-- CORRECT
FILE_FORMAT 'parquet'
FILE_FORMAT 'csv'
FILE_FORMAT 'json'

-- WRONG
FILE_FORMAT 'type' = 'parquet'    -- parser error
```

**Mixed schemas in a folder:** If a folder contains files with different schemas, point to the specific file — otherwise COPY INTO will fail when it encounters a file whose columns don't match the target table:

```sql
-- Folder has sales.parquet and customers.parquet with different schemas
-- WRONG — will fail on schema mismatch
COPY INTO my_sales FROM '@datasets/bucket/data/' FILE_FORMAT 'parquet';

-- CORRECT — point to the specific file
COPY INTO my_sales FROM '@datasets/bucket/data/sales.parquet' FILE_FORMAT 'parquet';
```

## Namespace & Folder Management

**To create a new namespace** (top-level container for Iceberg tables and views), use `CREATE FOLDER` via the REST SQL API. Do NOT use the catalog REST API to create spaces — spaces cannot hold Iceberg tables.

```sql
CREATE FOLDER my_namespace                    -- create a new namespace
CREATE FOLDER my_namespace.staging            -- subfolder within a namespace
CREATE FOLDER my_namespace.staging.temp       -- nested subfolder
CREATE FOLDER IF NOT EXISTS my_namespace      -- idempotent create
DROP FOLDER my_namespace.staging.temp         -- must drop children first
DROP FOLDER my_namespace.staging              -- cannot drop non-empty folders
```

`DROP FOLDER IF EXISTS` is **not supported** — omit `IF EXISTS`.

**Important:** Never use `AT BRANCH` syntax with CREATE/DROP FOLDER — it is not supported and will cause a parser error.

## Dremio SQL Reference

### Reserved Keywords (must quote as aliases)

`day`, `month`, `year`, `hour`, `minute`, `second`, `count`, `value`, `rows`, `timestamp`, `date`, `time`, `covar_pop`, `table`, `order`, `group`, `key`, `type`, `user`, `name`, `status`

Use `AS "day"` not `AS day`.

### General Rules

- Fully qualify table names: `"Namespace"."table"` or `"Namespace"."Folder"."table"`
- LIMIT results to 100 by default unless aggregating
- Handle NULLs with COALESCE or IS NOT NULL
- Use aliases for computed columns

### Function Syntax (Dremio-specific)

| Function | Correct Syntax | Common Mistake |
|:---------|:---------------|:---------------|
| ILIKE | `ILIKE(col, pattern)` | `col ILIKE pattern` |
| PERCENTILE_CONT | `PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY col)` | Other variants |
| DATEDIFF | `DATEDIFF(start, end)` — 2 args, returns days | Unit argument |
| TIMESTAMPDIFF | `TIMESTAMPDIFF(unit, start, end)` | DATEDIFF with units |
| TIMESTAMPADD | `TIMESTAMPADD(unit, count, ts)` | |
| EXTRACT | `EXTRACT(DAY FROM ts)` | |
| JSON access | `CONVERT_FROM(col, 'JSON')."field"` | `JSON_EXTRACT_SCALAR`, `->>`  |
| STRUCT access | `struct_col."field"` | `col['field']` |
| ANY_VALUE | Not supported — use `MIN(col)` or `MAX(col)` | `ANY_VALUE(col)` |
| FLATTEN | `FLATTEN(col)` or `CROSS JOIN UNNEST(col)` | `LATERAL FLATTEN` |
| ARRAY_AGG | Not supported for STRUCT/MAP/LIST; 32KB limit | |
| Intervals | `INTERVAL '1' HOUR` (unquoted unit) | Quoting the unit |
| Large intervals | `TIMESTAMPADD(DAY, -365, CURRENT_DATE)` | `INTERVAL '365' DAY` |

## Unstructured Data & AI_GENERATE

Use `AI_GENERATE` + `LIST_FILES` to extract structured data from images, PDFs, and documents in object storage.

### Basic pattern

```sql
CREATE TABLE Namespace.Results AS
WITH
  files AS (
    SELECT file, file['path'] AS file_path
    FROM TABLE(LIST_FILES('@source/path/to/files/'))
  ),
  extracted AS (
    SELECT file_path,
      AI_GENERATE('model-name',   -- e.g. 'FieldOrg', 'claude.claude-sonnet-4-6'
        ('Your extraction prompt.', file)
        WITH SCHEMA ROW(field1 VARCHAR, field2 DOUBLE)
      ) AS data
    FROM files
  )
SELECT data['field1'] AS field1, data['field2'] AS field2, file_path
FROM extracted;
```

### Variable-length extraction (JSON array → FLATTEN)

For documents with variable numbers of items (e.g., invoice line items):

1. Extract with `line_items_json VARCHAR` in the schema — prompt the LLM to return a JSON array
2. Parse and flatten:

```sql
CREATE VIEW Namespace.LineItems AS
WITH parsed AS (
  SELECT *, TRY_CONVERT_FROM(
    line_items_json AS ARRAY(ROW(field_a VARCHAR, amount DOUBLE))
  ) AS items
  FROM Namespace.Documents
),
flattened AS (
  SELECT doc_id, FLATTEN(items) AS item FROM parsed
)
SELECT doc_id, item['field_a'] AS field_a, item['amount'] AS amount
FROM flattened;
```

### Key points
- Pass `file` column from `LIST_FILES` as second tuple element for images/PDFs
- Access fields: `data['field_name']`
- **Use CREATE TABLE** (not view) to materialize AI results — avoids re-running LLM on each query
- For variable-length data: extract as VARCHAR JSON array, then `TRY_CONVERT_FROM` + `FLATTEN`

## Job Status API

When submitting DDL/DML via the REST API (CTAS, COPY INTO, CREATE FOLDER, etc.), the response returns a job ID. Poll for completion:

```bash
# Submit a SQL job
JOB_ID=$(curl -s -X POST "https://api.dremio.cloud/v0/projects/${DREMIO_PROJECT_ID}/sql" \
  -H "Authorization: Bearer $DREMIO_PAT" -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE ..."}' | jq -r '.id')

# Poll job status
curl -s "https://api.dremio.cloud/v0/projects/${DREMIO_PROJECT_ID}/job/${JOB_ID}" \
  -H "Authorization: Bearer ${DREMIO_PAT}" | jq '{jobState, rowCount, errorMessage}'
```

**Job states:** `PENDING`, `METADATA_RETRIEVAL`, `PLANNING`, `QUEUED`, `ENGINE_START`, `RUNNING`, `COMPLETED`, `FAILED`, `CANCELED`

**Polling tips:**
- CTAS on large tables (100M+ rows) can take 1-3 minutes
- COPY INTO from parquet is typically faster than CTAS
- CREATE FOLDER / CREATE TABLE (empty) complete in seconds
- Check `errorMessage` on `FAILED` — common causes: syntax errors, schema mismatches, source permissions

## Discovering Data & System Analysis

```sql
-- Browse tables/views
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA."TABLES"
WHERE TABLE_TYPE != 'SYSTEM_TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME

-- Recent jobs
SELECT * FROM sys.project.jobs_recent ORDER BY submitted_ts DESC LIMIT 20

-- Engine status / Org users
SELECT * FROM sys.project.engines
SELECT * FROM sys.organization.users
```

## Documentation

Dremio Cloud docs: https://docs.dremio.com/dremio-cloud/

Look up syntax with WebFetch when unsure:

| Topic | URL |
|:------|:----|
| SQL Functions | https://docs.dremio.com/dremio-cloud/sql/sql-functions/functions/ |
| SQL Reference | https://docs.dremio.com/dremio-cloud/sql/ |
| COPY INTO | https://docs.dremio.com/dremio-cloud/sql/commands/copy-into-table/ |
| CREATE TABLE | https://docs.dremio.com/dremio-cloud/sql/commands/tables/create-table/ |
| Data Types | https://docs.dremio.com/dremio-cloud/sql/data-types/ |
| REST API | https://docs.dremio.com/dremio-cloud/api/ |

## Best Practices

- If ambiguous which table to use, show what's available and ask.
- On query errors, check: unquoted reserved words, wrong table path, column typos.
- Aggregate first, then drill down. Don't dump hundreds of raw rows.
- Verify column types match before joins.
- Suggest follow-up queries based on results.