ClickHouse DB::Exception: FINAL not supported for this table engine

The "DB::Exception: FINAL not supported for this table engine" error in ClickHouse occurs when you use the FINAL modifier in a SELECT query against a table whose engine does not support it. The error code is ILLEGAL_FINAL. The FINAL modifier is designed for MergeTree family engines that handle row deduplication or collapsing during merges, such as ReplacingMergeTree, CollapsingMergeTree, and VersionedCollapsingMergeTree.

Impact

The query fails immediately and no results are returned. This is a syntax/semantic error rather than a runtime failure, so it does not affect other queries or server stability. It simply means the query needs to be rewritten to remove the FINAL clause or the table engine needs to be changed.

Common Causes

1. Using FINAL on a plain MergeTree table that does not perform deduplication or collapsing.
2. Using FINAL on non-MergeTree engines such as Memory, Log, TinyLog, or Distributed.
3. Migrating a query from one table to another without realizing the target table uses a different engine.
4. Copy-pasting a query pattern that includes FINAL without checking whether it applies to the target table.
5. Using FINAL on a Distributed table directly, rather than on the underlying local tables.

Troubleshooting and Resolution Steps

1. Check the engine of the table you are querying:

   SELECT engine FROM system.tables WHERE database = 'your_db' AND name = 'your_table';
   

2. If the table uses an engine that does not support FINAL, remove the clause from your query:

   -- Instead of:
   -- SELECT * FROM your_table FINAL;
   
   -- Use:
   SELECT * FROM your_table;
   

3. If you need deduplication behavior, consider whether the table should use ReplacingMergeTree instead:

   CREATE TABLE your_table (
       id UInt64,
       data String,
       version UInt64
   ) ENGINE = ReplacingMergeTree(version)
   ORDER BY id;
   

4. If you are querying a Distributed table and the underlying local tables support FINAL, you can use it in a subquery or adjust the distributed query:

   SELECT * FROM your_distributed_table
   SETTINGS final = 1;
   

5. For cases where you need deduplication on a plain MergeTree table, you can achieve similar results using GROUP BY with aggregate functions:

   SELECT id, argMax(data, version) AS data
   FROM your_table
   GROUP BY id;
   

Best Practices

• Only use FINAL with table engines that support it: ReplacingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, and AggregatingMergeTree.
• Be aware that FINAL can be resource-intensive as it forces merge-time deduplication at query time. Use it judiciously on large tables.
• Consider using the do_not_merge_across_partitions_select_final setting for better FINAL performance on partitioned tables.
• When designing tables that need row-level updates or deduplication, choose ReplacingMergeTree from the start.

Frequently Asked Questions

Q: Which table engines support the FINAL modifier?
A: ReplacingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, and AggregatingMergeTree support FINAL. Plain MergeTree, SummingMergeTree, and non-MergeTree engines do not.

Q: Is there a performance penalty for using FINAL?
A: Yes. FINAL forces ClickHouse to merge and deduplicate data at query time, which can be significantly slower than a regular query. For large tables, consider alternative approaches like GROUP BY with argMax or periodic OPTIMIZE TABLE FINAL operations.

Q: Can I use FINAL with a Distributed table?
A: You can use the SETTINGS final = 1 approach, which pushes the FINAL processing to the local tables on each shard. Using FINAL directly in the FROM clause of a Distributed table may not work as expected.

Q: What is the alternative to FINAL for deduplication?
A: You can use GROUP BY on the primary key columns combined with argMax() to select the latest version of each row. This approach gives you more control and can sometimes be more efficient than FINAL.