ClickHouse - Query files stored in OVHcloud Object Storage

Objective

ClickHouse can query files stored in an S3¹-compatible bucket without loading them first: you can read them, write query results to them, join them with your tables or load them into a table. Your data stays in your own OVHcloud Object Storage bucket.

This guide explains how to query files stored in OVHcloud Object Storage from a Public Cloud Analytics with ClickHouse cluster, and how to give other users access to these files without sharing your S3 credentials.

Requirements

• A Public Cloud project in your OVHcloud account.
• A ClickHouse cluster running on OVHcloud Public Cloud and accepting incoming connections. See How to create a ClickHouse cluster and How to connect to a ClickHouse cluster.
• The password of the avnadmin user of the cluster, or of a user with the privileges described in this guide.
• An OVHcloud Object Storage bucket and an S3 user with read and write access to it, with its access key and secret key. See Object Storage - Getting started and Object Storage - Identity and access management.

Instructions

Step 1 - Building the URL of your files

ClickHouse addresses a file with the URL of the bucket endpoint, followed by the bucket name and the path of the file (path-style URL):

https://s3.<region>.io.cloud.ovh.net/<bucket>/<path>/<file>

• <region> is the region of your bucket in lowercase, for example gra or eu-west-par. The endpoint of each region is listed in Object Storage - Endpoints and geoavailability.
• <bucket> is the name of your bucket.
• <path>/<file> is the key of the object, for example sales/2026/orders.parquet.

Step 2 - Storing your S3 credentials in a named collection

You could write your access key and secret key in each query, but they would then appear in your scripts and in the query history. Store them once in a named collection instead: ClickHouse keeps them encrypted and never displays them.

Connected as avnadmin, run:

CREATE NAMED COLLECTION ovh_bucket AS
    url = 'https://s3.<region>.io.cloud.ovh.net/<bucket>/',
    access_key_id = '<access_key>',
    secret_access_key = '<secret_key>';

The values are hidden when you list the named collections:

SELECT name, collection FROM system.named_collections;

ovh_bucket   {'access_key_id':'[HIDDEN]','secret_access_key':'[HIDDEN]','url':'[HIDDEN]'}

In the next steps, filename is the path of the file relative to the url of the collection.

Step 3 - Reading files

Read a Parquet file with the s3 table function:

SELECT count(), sum(id)
FROM s3(ovh_bucket, filename = 'numbers.parquet', format = 'Parquet');

The structure of Parquet files is detected automatically.

The s3 table function also accepts the full URL of the file, the access key, the secret key and the format. For CSV files with a header line, use the CSVWithNames format:

SELECT *
FROM s3('https://s3.<region>.io.cloud.ovh.net/<bucket>/exports/customers.csv', '<access_key>', '<secret_key>', 'CSVWithNames');

To read several files at once, use a wildcard (*) in the path. The virtual column _file gives the name of the file each row comes from:

SELECT _file, count()
FROM s3('https://s3.<region>.io.cloud.ovh.net/<bucket>/sales/*.parquet', '<access_key>', '<secret_key>', 'Parquet')
GROUP BY _file;

Step 4 - Creating a table on your files

To query the same files regularly, create a table with the S3 engine. The data stays in your bucket and is read at each query:

CREATE TABLE numbers_s3 (id UInt64, label String)
ENGINE = S3(ovh_bucket, filename = 'numbers.parquet', format = 'Parquet');

SELECT count() FROM numbers_s3;

Step 5 - Joining files with your tables, or loading them

You can join files with the tables of your cluster in the same query:

SELECT o.customer_id, sum(o.amount)
FROM orders AS o
INNER JOIN s3(ovh_bucket, filename = 'referential/customers.parquet', format = 'Parquet') AS c
    ON o.customer_id = c.id
GROUP BY o.customer_id;

To load the content of files into a table of your cluster, use INSERT … SELECT:

INSERT INTO orders_history
SELECT * FROM s3(ovh_bucket, filename = 'archive/orders_2025.parquet', format = 'Parquet');

Step 6 - Exporting query results to your bucket

Write the result of a query to a file with INSERT INTO FUNCTION:

INSERT INTO FUNCTION s3(ovh_bucket, filename = 'exports/orders_2026.parquet', format = 'Parquet')
SELECT * FROM orders WHERE toYear(order_date) = 2026;

To replace an existing file, add SETTINGS s3_truncate_on_insert = 1 at the end of the query.

Step 7 - Giving other users access to the files

Connected as avnadmin, grant other users the rights they need with SQL. For a user who only reads the files through the ovh_bucket named collection, without knowing the S3 credentials:

GRANT READ ON S3 TO <user>;
GRANT NAMED COLLECTION ON ovh_bucket TO <user>;
GRANT CREATE TEMPORARY TABLE ON *.* TO <user>;
GRANT SELECT ON <database>.* TO <user>;

• READ ON S3 allows reading from S3.
• NAMED COLLECTION ON ovh_bucket allows using the named collection, without seeing its values.
• CREATE TEMPORARY TABLE is required to use the s3 table function.
• SELECT on the database is required to query tables created with the S3 engine.

Troubleshooting

Go further

• ClickHouse documentation - s3 table function
• ClickHouse documentation - S3 table engine
• ClickHouse documentation - Named collections
• Capabilities and Limitations of Public Cloud Analytics with ClickHouse

We would love to help answer questions and appreciate any feedback you may have.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Are you on Discord? Connect to our channel at https://discord.gg/ovhcloud and interact directly with the team that builds our Analytics service!

Join our community of users.

¹: S3 is a trademark of Amazon Technologies, Inc. OVHcloud's service is not sponsored by, endorsed by, or otherwise affiliated with Amazon Technologies, Inc.