ClickHouse

ClickHouse is a column-oriented database built for fast analytical queries over large datasets.

Files.com integrates with ClickHouse through the Files.com S3-Compatible Endpoint. ClickHouse's native S3 functionality treats your Files.com site as an S3-compatible bucket and reads files directly from your Files.com folders, without a separate load step.

ClickHouse offers three ways to do this: the s3() table function for ad hoc queries, the S3 table engine for a persistent table backed by your Files.com folder, and the S3Queue table engine for continuous ingestion of new files as they arrive. All three connect directly to your Files.com S3-Compatible Endpoint. ClickHouse Cloud's managed ClickPipes connector does not currently work with Files.com.

Configuring Files.com

Your Files.com site includes an S3-Compatible Endpoint. Generate an S3-compatible API Key to create the access key ID and secret access key that ClickHouse needs. The API Key must be associated with a user account on your site.

Configure folder permissions for the folder you want ClickHouse to access so that the key's associated user has access. Read permission is the minimum required for ClickHouse to query data. Write permission is required if ClickHouse will also write results back to Files.com using the S3 table engine.

Verify that the user can access the folder and see the expected files before configuring ClickHouse.

Configuring ClickHouse

The bucket name for your Files.com site is your custom subdomain. If your custom subdomain is mysite.files.com, the bucket name is mysite.

Files.com's S3-compatible endpoint supports virtual-hosted-style requests, and ClickHouse's s3() function, S3 table engine, and S3Queue table engine all expect this format: the bucket name is part of the hostname (for example, mysite.s3.files.com), not part of the URL path. Point ClickHouse at https://mysite.s3.files.com/path/to/folder/*.csv, using your access key ID and secret access key for authentication.

Querying Files Directly with the s3() Table Function

Use s3() to query files in a Files.com folder without creating a table first. This is the fastest way to explore data or run a one-off import.

SELECT *
FROM s3('https://mysite.s3.files.com/path/to/folder/*.csv', 'your_access_key_id', 'your_secret_access_key',
  'CSV',
  'order_number String, id String, quantity String, unit_price String');

The wildcard * matches every file in the folder. s3() reads the files at query time; it does not persist a table definition.

Creating a Persistent Table with the S3 Table Engine

Use the S3 table engine to create a table backed by your Files.com folder, so you can query it like any other ClickHouse table without repeating the URL and credentials each time.

CREATE TABLE my_files_dot_com_table
  (order_number String, id String, quantity String, unit_price String)
ENGINE = S3('https://mysite.s3.files.com/path/to/folder/*.csv', 'your_access_key_id', 'your_secret_access_key', 'CSV');

With Write permission on the folder, the S3 table engine can also write results back to Files.com through INSERT statements.

Continuous Ingestion with the S3Queue Table Engine

Use S3Queue when new files land in a Files.com folder on an ongoing basis and you want ClickHouse to pick each one up automatically, without re-reading files it has already processed. S3Queue requires ClickHouse Keeper, which ClickHouse Cloud provides by default.

CREATE TABLE s3queue_files_dot_com
  (order_number String, id String, quantity String, unit_price String)
ENGINE = S3Queue('https://mysite.s3.files.com/path/to/folder/*.csv', 'your_access_key_id', 'your_secret_access_key', 'CSV')
SETTINGS mode = 'unordered';

CREATE TABLE orders (order_number String, id String, quantity String, unit_price String)
ENGINE = MergeTree ORDER BY order_number;

CREATE MATERIALIZED VIEW orders_mv TO orders AS SELECT * FROM s3queue_files_dot_com;

The S3Queue table tracks which files it has already ingested. The materialized view moves each new row into a regular MergeTree table as files arrive. Use mode = 'ordered' instead of 'unordered' when your filenames encode a sequence or timestamp that determines processing order.

ClickHouse Cloud ClickPipes

ClickHouse Cloud's managed ClickPipes S3 connector validates the bucket path against a short list of recognized S3-compatible providers and their expected URL formats before it will create a pipe. Files.com is not currently on that list, so a ClickPipe configured with a Files.com S3-Compatible Endpoint URL fails to save with an invalid bucket path error, even though the identical URL and credentials succeed when used directly in a query.

Use the s3() table function, S3 table engine, or S3Queue table engine documented above instead. All three connect to any S3-compatible endpoint and do not go through ClickPipes' provider validation.

Troubleshooting

Most issues come from incorrect folder permissions or the wrong bucket format. Confirm that the S3-Compatible Endpoint access key ClickHouse is using, and its associated user, have the correct permissions on the target folder.

Invalid Bucket Path

This error occurs when you try to use ClickPipes with an S3-compatible object storage solution that ClickHouse has not integrated with yet, such as Files.com. Use the s3() table function, S3 table engine, or S3Queue table engine instead.

Bucket or Key Name Invalid in S3 URI

ClickHouse could not parse your URL as virtual-hosted-style. Confirm the bucket name (your Files.com custom subdomain) appears in the hostname, for example mysite.s3.files.com, rather than in the path.

Access Denied

The key's associated user lacks permission on the target folder. Confirm Read permission (and Write, for the S3 table engine writing back to Files.com) is set on the folder, and that permissions have not changed since setup.

S3Queue Not Picking Up New Files

S3Queue requires ClickHouse Keeper to track processed files; confirm your ClickHouse deployment has Keeper configured. Check system.s3queue_log for per-file ingestion status and errors.