# Native SQL ↔ S3

> How do I stream SQL query results to S3 and read S3 files as parameters from native SQL scripts in Windmill?

Native SQL scripts (PostgreSQL, MSSQL, MySQL, BigQuery, Snowflake) can both stream their results to [workspace S3 storage](../38_object_storage_in_windmill/index.mdx) and consume S3 files as parameters. Both flows require workspace storage to be configured.

## Streaming results to S3

Use the `-- s3` flag when a query would otherwise blow past the 10 000 row in-memory limit:

```sql
-- s3 prefix=datalake format=csv
SELECT id, created_at FROM users
```

This returns a path string to the generated file, named after the job id — for example `"datalake/0196c8e6-1fd4-0d05-d31c-c7eae9a12b1a.csv"`. A downstream flow step can pick it up by reference.

All `-- s3` parameters are optional:

- `prefix`: object-key prefix for the generated file. Default: `wmill_datalake/path/to/job/id`
- `format`: `json` (default), `csv`, or `parquet`
- `storage`: name of the secondary workspace storage to use

## Reading S3 files as parameters

Declare an `(s3object)` argument and the worker downloads the file, decodes it (auto-detected from the extension: `.json` / `.jsonl` / `.parquet` / `.csv`), and binds it as a JSON-text parameter. Your SQL then reads it with the dialect's JSON-table function. This is the symmetric path to `-- s3`: a previous flow step (in any language) can write its result to S3 and the next native SQL step picks it up directly.

| Dialect | Argument declaration | JSON-consumption pattern |
|---|---|---|
| PostgreSQL | `-- $1 input_file (s3object)` | `SELECT * FROM jsonb_to_recordset($1::jsonb) AS x(id INT, name TEXT);` |
| MSSQL | `-- @P1 input_file (s3object)` | `SELECT * FROM OPENJSON(@P1) WITH (id INT '$.id', name NVARCHAR(255) '$.name');` |
| MySQL | `-- :input_file (s3object)` | `SELECT * FROM JSON_TABLE(:input_file, '$[*]' COLUMNS (id INT PATH '$.id', name VARCHAR(255) PATH '$.name')) AS x;` |
| BigQuery | `-- @input_file (s3object)` | `SELECT JSON_VALUE(row, '$.id') AS id, JSON_VALUE(row, '$.name') AS name FROM UNNEST(JSON_QUERY_ARRAY(@input_file)) AS row;` |
| Snowflake | `-- ? input_file (s3object)` | `SELECT v.value:id::int AS id, v.value:name::string AS name FROM TABLE(FLATTEN(input => PARSE_JSON(?))) v;` |

The `input_file` argument shows up in the run form as the standard S3 object picker.

### Format detection

The decoder picks a path from the object key's extension:

- `.parquet` — decoded via Apache Arrow's Parquet reader and re-serialized to a JSON array.
- `.csv` — decoded via Arrow's CSV reader (first row used as headers, types inferred).
- `.json`, `.jsonl`, `.ndjson`, no extension — passed through; JSONL inputs are repackaged as a JSON array so the user SQL can uniformly assume an array shape.

### Size limits

Despite the name of this page, an `(s3object)` argument is **not** streamed: the file is downloaded and decoded in full, then bound as a *single* SQL parameter. It sits in the worker's memory once and in the database driver's buffer once, so plan for worker memory several times the payload size.

What matters is the size of the **decoded JSON**, not the size of the file in S3. Parquet is compressed and columnar, so it inflates substantially on decode: a 15 MB Parquet file of 3 million short rows decodes to roughly 156 MB of JSON.

Two separate ceilings apply.

**The download has 20 seconds to complete.** The worker fetches the object over an HTTP client with a 20 second total request timeout, so an input that cannot transfer in that window fails with a timeout error regardless of its size on disk. How many MB that allows depends entirely on your network.

**Each dialect caps how large a single parameter may be**, and that ceiling is the database's, not Windmill's:

- **PostgreSQL** rejects a `jsonb` value whose array elements exceed 256 MB in total, with `total size of jsonb array elements exceeds the maximum of 268435455 bytes`. Past roughly 1 GB the connection is dropped outright (`connection closed`). In practice this means Parquet inputs of more than a few tens of MB will not go through `jsonb_to_recordset`.
- **MySQL** is bounded by `max_allowed_packet`, which is far smaller than these limits on a default server.
- **BigQuery** and **Snowflake** bound the size of a query request and its bind variables.

These are hard limits: no amount of worker memory or timeout will get a large file through this path. For anything beyond a modest payload, keep the data in S3 and read it with the database engine's own loader (`COPY FROM`, `BULK INSERT`, BigQuery load job, Snowflake `PUT`/`COPY`), or use [DuckDB](#duckdb), which reads Parquet from S3 natively and is the recommended option for large-file transformation pipelines.

### DuckDB

DuckDB has its own `(s3object)` path: the engine reads from S3 directly, so the worker just substitutes a `s3://...` URI. See the [DuckDB section of the SQL quickstart](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#duckdb) for the read_parquet/read_csv/read_json patterns.