ClickHouse® and S3 Compatible Object Storage

ClickHouse is a polyglot database that can talk to many external systems using dedicated engines or table functions. In modern cloud systems, the most important external system is object storage. First, it can hold raw data to import from or export to other systems (aka a data lake). Second, it can offer cheap and highly durable storage for table data. ClickHouse now supports both of these uses for S3 compatible object storage.

The first attempts to marry ClickHouse and object storage were merged more than a year ago. Since then object storage support has evolved considerably. In addition to the basic import/export functionality, ClickHouse can use object storage for MergeTree table data. While this functionality is still experimental, it has already attracted a lot of attention at meetups and webinars. In this article, we will explain how the integration works.

S3 table function

ClickHouse has a powerful method to integrate with external systems called ‘table functions’. Table functions allow users to export/import data into other sources, and there are plenty of sources available, e.g. MySQL Server, ODBC or JDBC connection, file, url, and, lately, S3-compatible storage. At the time of writing the s3 table function is not in the official list, but it should be fixed soon. The basic syntax is the following:

s3(path, [aws_access_key_id, aws_secret_access_key,] format, structure, [compression])

Input parameters

• path | bucket url. Path to file. Supports following wildcards in readonly mode: *, ?, {abc,def} and {N..M} where N, M | numbers, `’abc’, ‘def’ | strings.

• format | The format of the data.

• structure | Structure of the table. Format ‘column1_name column1_type, column2_name column2_type, …’.

• compression parameter is optional, currently ‘gzip’ is the only option but other methods are being added.

Note the wildcards! They allow you to import multiple files in a single function call. For example, our favorite NYC taxi trips dataset that is stored in one file per month can be imported with a single SQL command:

On an Altinity.Cloud ClickHouse instance it takes me less than 4 minutes to import a 1.3B rows dataset!

A few important hints:

• As of the 20.10 ClickHouse version, wildcard paths do not work properly with ‘generic’ S3 bucket URLs, region specific one is required. So we have to use: “https://s3.us-east-1.amazonaws.com/altinity-clickhouse-data/”,instead of “https://altinity-clickhouse-data.s3.amazonaws.com/”

• On the other hand, single file download can use convenient bucket URLs.

• The S3 import performance heavily depends on the level of client side parallelism. In glob mode multiple files can be processed in parallel. The example above used 32 insert threads. If you have a smaller server and VM try setting higher values of max_insert_threads setting. It can be done by a ‘SET’ command, for example:set max_threads=32, max_insert_threads=32;

set max_threads=32, max_insert_threads=32;

• From the other side, ‘input_format_parallel_parsing’ setting may result in overcommitting the memory, so better to turn it off.

S3 table function can be used not only for imports but for exports as well! This is how an ‘ontime’ dataset can be uploaded to S3.

Uploading is pretty slow because we can not benefit from the parallelism in this case. ClickHouse can not automatically split the data into multiple files, so only one file can be uploaded at a time. There is a feature request to enable automatic partitioning when inserting to an external table function. That would make export more efficient and convenient.

Also it is a bit annoying that ClickHouse requires table structure to be supplied to the S3 table function. This is going to be improved in the future releases as well.

ClickHouse storage architecture

S3 table function is a convenient tool for exporting or importing data but it can not be used in real insert/select workloads. The closer integration with ClickHouse storage system is required. Let’s look at ClickHouse storage architecture in more detail.

We have already discussed storage several times earlier in the blog, for example in Amplifying ClickHouse Capacity with Multi-Volume Storage (Part 1). Let’s do a short recap. ClickHouse provides several abstraction layers from top to the bottom:

• Storage policies define what volumes can be used, and how data migrates from volume to volume;

• Volumes allow to organize multiple ‘disk’ devices together;

• Disk represents the physical device or mount point.

When this storage design was implemented in early 2019, ClickHouse supported only one type of disk that maps to OS mount points. A few months later the ClickHouse development team added an extra abstraction layer inside the disk itself, that allows to plug in different disk types. As one can probably guess the rationale for this was object storage integration. The new disk type ‘S3’ was added shortly after. It encapsulated the specifics of communicating with S3-compatible object storage. Now we can configure S3 ‘disks’ in ClickHouse, and store all or some data on the object storage.

Object storage configuration

Disks, volumes, and storage policies can be defined in the main ClickHouse configuration file config.xml or, better, in the custom file inside /etc/clickhouse-server/config.d folder. Let’s define the disk S3 first:

config.d/storage.xml:

This is a very basic configuration, ClickHouse supports quite a lot of different options here; we will discuss some of them later.

Once the S3 disk is configured, it can be used in volume and storage policy configuration. We can setup several policies for different use cases:

• S3 volume in a policy next to other volumes. It can be used for TTL or manual moves of table partitions.

• S3 volume in a policy with no other volumes. This is an S3-only approach.

Now let’s try to create some tables and move data around.

Inserting data

We will be using an ‘ontime’ dataset for this example. You can get it from ClickHouse Tutorial, or download from an Altinity S3 bucket. The table has 193M rows and 109 columns, that’s why it is interesting to see how it performs with S3, where file operations are expensive. The reference table name is ‘ontime_ref’ and it uses default EBS volume. We can now use it as a template for experiments with S3.

‘ontime_tiered’ table is configured to store a full 3 years of data on block storage, and move earlier data to S3. ‘ontime_s3’ is the S3-only table.

Now, let’s insert some data. Our reference table has data up to March 31st, 2020.

That was almost instant. The data still goes to the normal disk. What about the S3 table?

Same amount of rows takes 25 times more to insert!

Once data lands on S3 insert performance degrades quite a lot. This is certainly not desirable for a tiered table, so there is a special volume level setting that disables TTL moves on insert completely, and runs it in the background only. Here is how it can be configured:

With such a setting insert goes always to the first disk in the storage policy. TTL moves to the corresponding volume are executed in the background. Let’s clean the ‘ontime_tiered’ table and perform a full table insert (side note: truncate takes a long time).

This was fast, since all the data was inserted to the fast disk. We can check how data is located on the storage using this query:

So the data was already moved to S3 by a background process. Only 10% of the data is stored on a local file system, and everything else has been moved to the object storage. This looks to be the right way to deal with S3 disks, so we will be using ‘ontime_tiered’ later on.

Note the ‘part_type’ column. ClickHouse MergeTree table can store data parts in different formats. ‘Wide’ format is the default; it is optimized for query performance. It requires, however, at least two files per column. The ‘ontime’ table has 109 columns, which results in 227 files for every part. This is the main reason for slow S3 performance on inserts and deletes.

On the other hand, ‘compact’ parts store all data in a single file, so inserts to ‘compact’ parts are much faster (we tested that), but query performance degrades. Therefore, ClickHouse uses ‘compact’ parts only for small parts. The default threshold is 10MB (see ‘min_bytes_for_wide_part’ and ‘min_rows_for_wide_part’ merge tree settings).

Checking query performance

In order to test query performance we will run several benchmark queries for ‘ontime_tiered’ and ‘ontime_ref’ tables that query historical data, so the tiered table will be using S3 storage. We will also run a mixed range query to confirm that S3 and non-S3 data can be used together, and compare results with the reference table. This is not going to be thoroughly tested, but it should give us a general idea of performance differences. Only 4 representative queries have been selected from the benchmark. Please refer to the full list in ClickHouse Tutorial.

This query runs in 0.015s for ‘ontime_ref’ and 0.318s for ‘ontime_tiered’. Second run completes in 0.142s.

This query runs in 0.063 sec for ‘ontime_ref’ and 0.766/0.518 for ‘ontime_tiered’.

This query runs in 0.319s for ‘ontime_ref’, and 1.016/0.988 for ‘ontime_tiered’.

This query runs in 0.436s for ‘ontime_ref’ and 2.493/2.241s for ‘ontime_tiered’. This time both block and object storage were used in a single query for the tiered table.

So, query performance with S3 disk definitely degrades, but it is still fast enough for interactive queries. Note the performance improvement on the second run. While Linux page cache can not be used for S3 data, ClickHouse caches index and mark files for S3 storage locally, that gives a notable boost when analyzing where conditions and fetching the data from S3.

Trying a bigger dataset

Let’s try to compare the query performance of the bigger NYC taxi trips dataset as well. We used it recently in order to compare against Amazon RedShift. The dataset contains 1.3 billion rows. As noted above, it can be loaded from S3 using the S3 table function. First, we create the tiered table the same way:

And insert the data:

That was almost instant, thanks to EBS storage performance. Now let’s look into the data placement:

Apparently, the dataset end date is 31 December 2016, so all our data goes to S3. You can see quite a lot of parts — it will take some time for ClickHouse to merge it. If we check the same query 10 minutes later, the number of parts reduces to 3-4 per partition. In order to see not only the S3 performance but also the effect of number of parts, we run benchmark queries twice: first with 441 parts in the S3 table, and second with an optimized table that contains only 96 parts after OPTIMIZE FINAL. Note, OPTIMIZE FINAL is very slow on the S3 table, it took around an hour to complete in our setup.

The chart below compares the best result of 3 runs for 5 test queries:

As you can see, the query performance difference between EBS and S3 MergeTree is not that substantial anymore compared to smaller ontime dataset and it reduces when query complexity increases. Also table optimization helps to reduce the gap even more.

Under the hood

ClickHouse was not originally designed for object storage. Therefore it uses some block storage specific features like hard links a lot. How does it work for the S3 storage then? Let’s look into the ClickHouse data directory to figure out.

For non-S3 tables ClickHouse stores data parts in /var/lib/clickhouse/data/<database>/<table>. For S3 tables you won’t file the data at this location, instead something similar is located in /var/lib/clickhouse/disks/s3/data/<database>/<table>. (This location can be configured on the disk level). Let’s look into contents though:

This is not the data, but the reference to an S3 file instead. We can find corresponding S3 object looking into AWS console:

ClickHouse generates unique files for every column with hashed names and stores references in the local file system. Merges, mutations and rename operations that require hard links in block storage are implemented on the reference level, while S3 data is not touched at all. This definitely solves a lot of problems but creates another one: all files for all columns of all tables are stored with a single prefix.

Issues and limitations

S3 storage for MergeTree tables is still experimental, and it has a few loose ends. One evident limitation is replication. Object storage is supposed to be replicated by the cloud provider already, so there is no need to use ClickHouse replication and keep multiple copies of the data. ClickHouse needs to be smart enough not to replicate S3 tables. It gets even more sophisticated when a table uses tiered storage.

Another drawback is insert and merge performance. Some optimizations like parallel multipart uploads have been already implemented. Tiered tables can be used in order to have fast local inserts, but we can not change the laws of physics — merges may be quite slow. In real use cases though ClickHouse will do most of the merges on fast disks before data goes to object storage. There is also a setting to disable merges on object storage completely, in order to protect historical data from unneeded changes.

The structure of the data on object storage also needs to be improved. In particular, if every table had a separate prefix, it would be possible to move tables between locations. Adding metadata would allow you to restore the table from an object storage copy if everything else was lost.

Another issue is related to security. In examples provided above we had to supply AWS access keys in SQL or ClickHouse storage configuration. This is definitely not convenient, let alone secure. There are two options that make users’ lives easier. First, it is possible to supply credentials or the authorization header globally on a server configuration level, for example:

Second, IAM role support is already in development. Once implemented it delegates access control to AWS account administrators.

All those limitations are taken into account in the current development efforts, and we plan to improve MergeTree S3 implementation in the next few months.

Conclusion

ClickHouse constantly adapts to user needs. Many ClickHouse features are driven by community feedback. Object storage support is not an exception. Frequently demanded by community users it has been largely contributed by developers from Yandex.Cloud and Altinity.Cloud teams. While imperfect at the moment it extends ClickHouse capabilities a lot already. The development is still going on; every new feature and improvement in this area pushes ClickHouse one step further to the effective cloud operation. ClickHouse does not slow down! Stay tuned.