Cannot read Iceberg table: positional and equality deletes are not supported #66588
Description
Company or project name
Gr8 Tech we are evaluating CH as a backend for serving layer in our internal DWH.
What's wrong
There is no possibility to query iceberg tables after update/delete operations.
Does it reproduce on the most recent release?
yes
How to reproduce
- Create iceberg table using AWS Athena
CREATE TABLE sandbox.test_iceberg_deletes (
id int,
data string,
category string)
PARTITIONED BY (category, bucket(16,id))
LOCATION 's3://<bucket>/dbt/sandbox/test_iceberg_deletes'
TBLPROPERTIES (
'table_type'='ICEBERG',
'format'='parquet',
'write_compression'='snappy',
'optimize_rewrite_delete_file_threshold'='10'
)
- Insert some data into table
INSERT INTO sandbox.test_iceberg_table (id, data, category)
VALUES (1, 'test', 'cat1')
- Create table in CH using Iceberg engine
create table test.test_deletes engine=Iceberg('https://<bucket>.s3.<region>.amazonaws.com/dbt/sandbox/test_iceberg_deletes/');
- Query this table with CH and it works
select * from test.test_deletes;
- Now try to delete data from iceberg table using AWS Athena
delete from sandbox.test_iceberg_deletes where id = 1;
If you query this table from AWS Athena, it works:
select * from sandbox.test_iceberg_deletes;
But when you try to query this table from CH, the query fails with error:
2024.07.16 12:17:57.315253 [ 50 ] {16ba3932-6035-4ee3-a845-0e8cb5faa133} <Error> executeQuery: Code: 1. DB::Exception: Cannot read Iceberg table: positional and equality deletes are not supported. (UNSUPPORTED_METHOD) (version 24.6.2.17 (official build)) (from [::ffff:127.0.0.1]:57306) (in query: select * from test.test_deletes), Stack trace (when copying this message, always include the lines below):
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c3b2a28
1. DB::Exception::Exception(PreformattedMessage&&, int) @ 0x0000000007bb443c
2. DB::Exception::Exception<>(int, FormatStringHelperImpl<>) @ 0x0000000007bc1494
3. DB::IcebergMetadata::getDataFiles() const @ 0x000000000efc4448
4. DB::IStorageDataLake<DB::IcebergMetadata>::updateConfiguration(std::shared_ptr<DB::Context const>) @ 0x000000000e98f718
5. DB::StorageObjectStorage::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x000000000ef674c4
6. DB::(anonymous namespace)::buildQueryPlanForTableExpression(std::shared_ptr<DB::IQueryTreeNode>, DB::SelectQueryInfo const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::PlannerContext>&, bool, bool) @ 0x000000001018cae0
7. DB::buildJoinTreeQueryPlan(std::shared_ptr<DB::IQueryTreeNode> const&, DB::SelectQueryInfo const&, DB::SelectQueryOptions&, std::unordered_set<String, std::hash<String>, std::equal_to<String>, std::allocator<String>> const&, std::shared_ptr<DB::PlannerContext>&) @ 0x0000000010186864
8. DB::Planner::buildPlanForQueryNode() @ 0x000000001016ccc0
9. DB::Planner::buildQueryPlanIfNeeded() @ 0x0000000010169928
10. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000104b2610
11. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, DB::QueryFlags, std::optional<DB::FormatSettings> const&, std::function<void (DB::IOutputFormat&, String const&, std::shared_ptr<DB::Context const> const&, std::optional<DB::FormatSettings> const&)>) @ 0x00000000104b6394
12. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::optional<DB::CurrentThread::QueryScope>&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x0000000011274ee4
- Which ClickHouse server version to use: 24.6.2.17
- Which interface to use, if it matters: does not matter
- Non-default settings, if any
profiles:
default/iceberg_engine_ignore_schema_evolution: 1
Expected behavior
I can query the described iceberg table after delete and update operations as well as before them.
Error message and/or stacktrace
Code: 1. DB::Exception: Cannot read Iceberg table: positional and equality deletes are not supported. (UNSUPPORTED_METHOD) (version 24.6.2.17 (official build))