Apply a TTL to system.query_log

ClickHouse can surface query logs in the system.query_log table, making it easy to run queries over your logs for debugging. Over time, especially on busy clusters, this table can grow quite large and take up a lot of disk space.

You might think to apply a TTL to this table using MODIFY TTL, however, this change will be lost upon a server restart.

To permenantly apply a TTL to the system.query_log table, you must modify the conf.xml file.

For example:

    <query_views_log>
        <database>system</database>
        <table>query_views_log</table>
        <partition_by>toStartOfWeek(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <ttl>toStartOfWeek(event_date) + INTERVAL 3 MONTH</ttl>
    </query_views_log>

    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toStartOfWeek(event_date)</partition_by>
        <ttl>toStartOfWeek(event_date) + INTERVAL 3 MONTH</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>

You must restart the ClickHouse server for this config change to be applied.