ClickHouse Cheat Sheet 2024

Posted on by jonesy

For the past 4 months, ClickHouse has been my life, full time. I’ve been vetting it for production use and learning all about it in the process. Since my memory has always been notoriously poor, I take a lot of notes (in fact, that was the original reason for this blog’s existence).

So, while I’d love to have time to do some longer-form writing about ClickHouse, what I have now in terms of notes could be helpful to probably a lot of people as ClickHouse gains in popularity by the minute.

If there are specific topics within the realm of ClickHouse that you want to see covered, you can certainly let me know, but you should know that, operationally, ClickHouse was complex enough that we just this week opened a ClickHouse Cloud account, so my knowledge of it operationally will probably start to atrophy starting with the 24.x releases (up to this week, I built and ran various testing clusters myself, in AWS EC2, and EKS).

So, without further ado, here’s the Cheat Sheet.

NUMBER_OF_COLUMNS_DOESNT_MATCH

If you get seemingly inexplicable ‘NUMBER_OF_COLUMNS_DOESNT_MATCH’ errors whenever you’re doing a select (by itself or as part of an INSERT…SELECT, or whatever), and it seems like the column numbers do match, remove the parentheses around whatever follows SELECT. So, instead of SELECT (x, y, z) FROM foo it should be SELECT x,y,z FROM foo.

Remote and RemoteSecure Table Functions

If you have an ClickHouse server you manage, or an instance in their cloud offering, you can use clickhouse local and the remote table function to move data into your server from some other source.

For example, I have a cloud instance. I also have an event warehouse in S3, where events are stored in Parquet files. I can use the following command to do an INSERT...SELECT into my (remote) cloud database from the data in the Parquet files in S3:

clickhouse local --verbose --query "INSERT INTO TABLE FUNCTION remoteSecure('my-cloud-host:9440', 'mydb.mytable', 'myclouduser', 'mycloudpassword') (col1, col2, col3) SELECT col1, col2, col3 FROM s3('https://s3-endpoint/events/some-event/2023/**/*.parquet', 'Parquet');"

As a bonus trick, note the wildcard use in the s3 function. The supported wildcards are similar to those used in the bash shell (and maybe zsh – I’m not as familiar). Read all about those here.

Also, note that remoteSecure is just a secure version of the remote function! The syntax is the same! On cloud instances I believe only remoteSecure is supported, which makes sense.

ClickHouse Can’t Parse Some Date Formats

I’ve had issues with:

  • Dates with a timezone offset going into a DateTime column, and
  • Dates with microsecond precision going into a DateTime column

There are multiple solutions to this, but one I’ve used that could work depending on your requirements is to wrap the column with the parseDateTimeBestEffortOrZero function, like this:

INSERT INTO events (source, timestamp) SELECT source, parseDateTimeBestEffortOrZero(timestamp) FROM mytable

By default, ClickHouse has a fast, cheap, and simplified time parsing algorithm. The ‘BestEffort’ functions support a wide array of formats, including ISO8601 and RFC 822, and more. See here.

Here’s how it deals with a couple of formats I had to deal with:

SELECT parseDateTimeBestEffortOrZero('2011-11-04 00:05:23.283+00:00') AS time

┌────────────────time─┐
│ 2011-11-04 00:05:23 │
└─────────────────────┘

SELECT parseDateTimeBestEffortOrZero('2023-10-26T10:11:18.964768') AS time

┌────────────────time─┐
│ 2023-10-26 10:11:18 │
└─────────────────────┘

In my case, I didn’t require the additional information lost by using the function, so it worked fine. If you need to preserve it, there are more options than I can cover here, but look at DateTime64 datatype for storing subsecond precision, and look at all of the other datetime parsing functions available on this page!

My ClickHouse Server Is Out of Disk!

AFAIK this will only apply to self-hosted instances. If your server’s disk is full, you have a couple of options:

  • EBS volumes can be extended & filesystem resized without data loss or downtime (source)
  • A new, bigger disk can be attached to the EC2 instance. Then, add the old and new disks to a new storage policy in the clickhouse server’s config.xml file. Then, move all of the data to the new disk. Once done, the old disk can be removed from the storage policy. (source)

Making Wide Tables Without Using NULLable Columns

NULLable columns can impact performance in clickhouse and their best practices docs explicitly recommend avoiding them. Instead, you can create default values. This is how I was able to combine what were separate tables for each event type in our system into a single ‘events’ table, even though these events don’t all follow the exact same schema. Here’s an example excerpted from a CREATE TABLE statement:

    `headers` Map(String, String) DEFAULT map('00000', '00000'),
    `client_id` UUID DEFAULT '00000000-0000-0000-0000-000000000000',
    `destination` String DEFAULT '-',

The Infamous “TOO MANY PARTS” Error

If you get a “TOO MANY PARTS” error either in the server log or in a client when doing an insert query to the server, run this query. It’ll tell you, for each table, how many parts were created, and the average rows per part, over the last 10 minutes.

SELECT  toStartOfTenMinutes(event_time) AS time,
  concat(database, '.', table) as table,
  count() AS new_parts,
  rount(avg(rows)) AS avg_rows_per_part 
FROM system.part_log
WHERE (event_date >= today()) AND (event_type = 'NewPart')
GROUP BY time, table
ORDER BY time ASC, table ASC;

If you see thousands of parts created but the average row size is like, say, 4, then that’s unhealthy. Remember that ClickHouse is optimized to perform fewer, bigger inserts, and performs poorly when doing a high volume of very small inserts, because all of the inserts create parts that have to be merged, and merges are resource intensive. The fewer inserts, the fewer parts, the fewer merges, the happier the server is.

Moving Internal Data Off The Root Volume

ClickHouse stores internal log tables, tables from the system database, and metadata, all under /var/lib/clickhouse by default. Also by default, Amazon Linux puts /var/ on the root volume. I wanted it off of the root volume, and thought it made sense to put it on the volume where all of the other data was stored.

This is possible, but it’s not a documented process and it took me a little debugging to get it done. Here’s what I did:

First, In config.xml (or wherever you keep the storage config), at the top level, set the following settings to whatever path you want (I used /data/lib/clickhouse): 

<path>/data/lib/clickhouse/</path>
<tmp_path>/data/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/data/lib/clickhouse/user_files/</user_files_path>
<format_schema_path>/data/lib/clickhouse/format_schemas/</format_schema_path>

<user_directories>
    <local_directory>
        <path>/data/lib/clickhouse/access/</path>
    </local_directory>
</user_directories>

Also in config.xml, make sure there isn’t a metadata path associated with an existing disk in your <storage_configuration>. It would likely be pointing at /var/lib by default.

At this point I copied everything to the new location: cp -R /var/lib/clickhouse /data/lib/.

In addition, you need to make 100% sure that the clickhouse user has ownership and full permissions over everything it had access to before that you’ve now moved. I did this: cd /data/lib; chown -R clickhouse:clickhouse clickhouse and that worked fine for me.

ClickHouse Silently Fails To Start

There have been plenty of times while learning about ClickHouse that it failed to start, but it was always very good about putting an error in the error log (by default, /var/log/clickhouse-server/clickhouse-server.err.log). One time, it failed to start, and there was no error in the log, and nothing to see using journalctl or systemctl status either.

If this happens, you should go find the command systemd (or your init script) uses to start it, copy it, and run it verbatim yourself, and you’ll likely see the missing error. In my case, ClickHouse failed to start because it was unable to access its pid file. That’s weird, because ClickHouse creates the pid file itself.

After some spelunking around I figured out that there was, in fact, a permissions issue (it’s always permissions or DNS folks!). What’s weird is that the permissions issue was in a directory on another completely separate physical disk, but it was causing this issue. So if this happens to you, go look at permissions under your /var/lib/clickhouse directory. The user that ClickHouse runs as should own everything recursively underneath /var/lib/clickhouse.

Age-based Tiered Storage In ClickHouse

When I first read about tiered storage in ClickHouse, the example they used seemed really strange to me: they configured ClickHouse to move data to another volume when the first volume was 80% full. When I think of tiered storage I immediately think of hot/cold storage volumes, and aging out data to cold storage after some specific period of time. You can totally do that with ClickHouse.

ClickHouse uses ‘storage policies’ to let you inform clickhouse of the disks available to it & the volumes those disks make up. Using a storage policy (and the also-cool TTL clause), you can configure multiple volumes, ‘hot’ and ‘cold’, for example, and then ClickHouse will migrate the data between volumes for you. It can also recompress data or do other operations at the same time. Here’s what I did:

First, set up a <storage_configuration> w/ the hot & cold disks:

     <disks>
        <hot>
            <type>local</type>
            <path>/data/</path>
        </hot>
        <cold>
           <type>s3</type>
           <endpoint>https://mys3endpoint</endpoint>
        </cold>
    </disks>

Now, add the hot/cold policy:

<policies>
    <tiered_storage> <!-- policy name --> 
        <volumes>
            <hot_volume> <!-- volume name -->
                <disk>hot</disk>
            </hot_volume>
            <cold_volume> <!-- volume name -->
                 <disk>cold</disk>
            </cold_volume>
        </volumes>
    </tiered_storage>
</policies>

Now, when you create a new table, you can reference the tiered_storage policy in the SETTINGS, and use a TTL clause to tell ClickHouse what to move and when to move it. For example:

CREATE TABLE default.foo
(
   source_id UUID,
   timestamp DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
TTL timestamp + toIntervalDay(45) TO VOLUME 'cold'
SETTINGS storage_policy = 'tiered_storage';

ClickHouse will use the order of the volumes in the storage policy to determine priority, so by default, because we defined the ‘hot’ volume first, newly-inserted rows will go there. However, after timestamp + toIntervalDay(45) it’ll be moved to the ‘cold’ volume.

Hope This Helps!

ClickHouse is a very deep product, and each individual feature has a lot of nuance to it, as does the overall behavior of the service in general. This is obviously not the totality of what I learned, but if you’re just starting out with ClickHouse, I hope this saves you some of the hours I spent getting this knowledge. Good luck!