无法设置ClickHouse log_queries=1，如何开启system.query_log查询日志？

Got it, let's work through this step by step. The key issue here is that system.settings is a read-only system table—you can't run UPDATE on it directly. Here are the two reliable ways to enable query logging properly:

1. Temporary Enable (Session-Only, Resets on Restart) #

If you just want to test query logging for your current session, run this command right in your ClickHouse client:

SET log_queries = 1;

Verify it worked with:

SELECT value FROM system.settings WHERE name = 'log_queries';

After enabling this, the system.query_log table will become accessible. Note: It might not show up until you run your first test query (like SELECT 1;)—the table is populated once the first logged query completes.

2. Permanent Global Enable (Applies to All Sessions, Survives Restarts) #

To make this setting stick even after ClickHouse restarts, you need to edit the configuration files:

• Step 1: Locate your ClickHouse config directory
  On Linux, this is usually /etc/clickhouse-server/. On Windows, it's typically in your ClickHouse installation folder (e.g., C:\Program Files\ClickHouse\config).

• Step 2: Update the profile configuration
  You have two options here:

  • Global for all users: Open config.xml, find the <profiles> section, and add the setting to the <default> profile (applied to all users by default):

    <profiles>
        <default>
            <!-- Keep existing settings here -->
            <log_queries>1</log_queries>
        </default>
    </profiles>
    

  • For a specific user: Open users.xml, find the target user, and either add the setting to their assigned profile or create a custom profile for them:

    <users>
        <your_target_user>
            <!-- Existing user settings -->
            <profile>default</profile> <!-- Or your custom profile name -->
        </your_target_user>
    </users>
    <!-- If using a custom profile, add it to the <profiles> section like above -->
    

• Step 3: Restart ClickHouse
  On Linux, run:

  sudo systemctl restart clickhouse-server
  

  On Windows, restart the ClickHouse service via the Windows Services manager.

• Step 4: Verify the permanent setting
  Open a new ClickHouse session (old sessions won't pick up the new config) and run:

  SELECT value FROM system.settings WHERE name = 'log_queries';
  

  It should return 1, and system.query_log will now be available permanently.

Quick Troubleshooting Note #

If system.query_log still doesn't appear after enabling log_queries, double-check your config for the <query_log> section. By default, ClickHouse uses a MergeTree engine for this table, so your config should include something like:

<query_log>
    <database>system</database>
    <table>query_log</table>
    <engine>MergeTree</engine>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

内容的提问来源于stack exchange，提问作者Tomyhill