Query and ingest data directly from S3 (or GCS) into/from ClickHouse using the s3 table function, including credentials, glob patterns, and schema inference.

Documented steps

1. Basic syntax: `s3(url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [, format] [, structure] [, compression_method])` — or `s3(named_collection[, option=value, ...])` for reusable credentials.
2. Query with schema inference and no explicit structure: `SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv', 'CSVWithNames') LIMIT 5;` — ClickHouse can also infer the format from the URL's file extension and auto-detects the compression method from the extension (e.g. .csv.gz is auto-decompressed).
3. Query a private/non-public bucket by passing static credentials: `SELECT count() FROM s3('https://bucket.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<your-access-key-id>', '<your-secret-key>', 'TSVWithNames')`. For long-term production use, prefer a named collection: `CREATE NAMED COLLECTION creds AS access_key_id='<from-env>', secret_access_key='<from-env>'; SELECT count() FROM s3(creds, url='https://s3-object-url.csv')`.
4. For public buckets that return 403 due to ClickHouse's default credential resolution, force unsigned requests with the NOSIGN keyword: `SELECT * FROM s3('https://.../aapl_stock.csv', NOSIGN, 'CSVWithNames') LIMIT 5;`.
5. Use glob patterns to read many files at once: `*`, `**` (recursive), `?`, `{abc,def}`, and `{N..M}` numeric ranges, e.g. `SELECT count(*) FROM s3('https://.../file-{000..999}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')`.
6. Ingest S3 data into a ClickHouse table by selecting from the function: `INSERT INTO my_table SELECT * FROM s3('https://.../*.csv', 'CSVWithNames')`. Conversely, write query results out to S3: `INSERT INTO FUNCTION s3('https://bucket.../test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip') SELECT name, value FROM existing_table;`.
7. On ClickHouse Cloud, prefer role-based access over static keys for non-public buckets by passing an extra_credentials(role_arn='arn:aws:iam::...:role/...') parameter instead of embedding access_key_id/secret_access_key.
8. Reference: https://clickhouse.com/docs/sql-reference/table-functions/s3