Update snapshots on cluster with replica

[ikeniborn]

When I used in snapshots engine = engine = "ReplicatedMergeTree ('/clickhouse/tables/{shard}/{database}/{table}','{replica}')" first time, all good. But when i try run second time, i get error

Database Error in snapshot snap_dim_fund (snapshots/snap_dim_fund.sql) 10:09:35 Code: 253. 10:09:35 DB::Exception: Replica /clickhouse/tables/s1/snapshots/snap_dim_fund/replicas/r1 already exists. Stack trace: 10:09:35
10:09:35 0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000e91fc37 in /usr/bin/clickhouse 10:09:35 1. ? @ 0x000000000930c64d in /usr/bin/clickhouse 10:09:35 2. DB::StorageReplicatedMergeTree::createReplica(std::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0x00000000148c2e57 in /usr/bin/clickhouse 10:09:35 3. DB::StorageReplicatedMergeTree::StorageReplicatedMergeTree(String const&, String const&, bool, DB::StorageID const&, String const&, DB::StorageInMemoryMetadata const&, std::shared_ptrDB::Context, String const&, DB::MergeTreeData::MergingParams const&, std::unique_ptr<DB::MergeTreeSettings, std::_deleteDB::MergeTreeSettings>, bool, DB::RenamingRestrictions, bool) @ 0x00000000148b1bbf in /usr/bin/clickhouse 10:09:35 4. ? @ 0x00000000150e6dd8 in /usr/bin/clickhouse 10:09:35 5. DB::StorageFactory::get(DB::ASTCreateQuery const&, String const&, std::shared_ptrDB::Context, std::shared_ptrDB::Context, DB::ColumnsDescription const&, DB::ConstraintsDescription const&, bool) const @ 0x00000000147d765b in /usr/bin/clickhouse 10:09:35 6. DB::InterpreterCreateQuery::doCreateTable(DB::ASTCreateQuery&, DB::InterpreterCreateQuery::TableProperties const&, std::unique_ptr<DB::DDLGuard, std::_deleteDB::DDLGuard>&) @ 0x0000000013e7a84c in /usr/bin/clickhouse 10:09:35 7. DB::InterpreterCreateQuery::createTable(DB::ASTCreateQuery&) @ 0x0000000013e73699 in /usr/bin/clickhouse 10:09:35 8. DB::InterpreterCreateQuery::execute() @ 0x0000000013e7fd20 in /usr/bin/clickhouse 10:09:35 9. ? @ 0x000000001442b109 in /usr/bin/clickhouse 10:09:35 10. DB::executeQuery(String const&, std::shared_ptrDB::Context, bool, DB::QueryProcessingStage::Enum) @ 0x00000000144267ce in /usr/bin/clickhouse 10:09:35 11. DB::TCPHandler::runImpl() @ 0x0000000015278ac4 in /usr/bin/clickhouse 10:09:35 12. DB::TCPHandler::run() @ 0x000000001528f879 in /usr/bin/clickhouse 10:09:35 13. Poco::Net::TCPServerConnection::start() @ 0x0000000018294134 in /usr/bin/clickhouse 10:09:35 14. Poco::Net::TCPServerDispatcher::run() @ 0x0000000018295351 in /usr/bin/clickhouse 10:09:35 15. Poco::PooledThread::run() @ 0x000000001841e567 in /usr/bin/clickhouse 10:09:35 16. Poco::ThreadImpl::runnableEntry(void*) @ 0x000000001841bf9c in /usr/bin/clickhouse 10:09:35 17. ? @ 0x00007fa1ad69f802 in ? 10:09:35 18. ? @ 0x00007fa1ad63f450 in ?

How i can used snapshot on cluster with replica?

ikeni@ikeni-Ideapad-Z570:~/Documents/Git/dataset/clickhouse$ dbt --version Core:

• installed: 1.5.5
• latest: 1.6.0 - Update available!

Your version of dbt-core is out of date! You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

Plugins:

• clickhouse: 1.4.7 - Not compatible!

At least one plugin is out of date or incompatible with dbt-core. You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

ClickHouse 23.7.4.5

[lksv]

I guess it's necessary to change this line from:

create table if not exists {{ upsert }} as {{ target }}

to

create table if not exists {{ upsert }} {{ on_cluster_clause(target)}} as {{ target }}

Furthermore I use engine="ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}/{uuid}','{replica}')" where uuid is added to avoid collision with the keeper path - but not sure if really needed.

[BentsiLeviav]

We added support for snapshot creation on a cluster in 1.9. Feel free to reopen/comment if you have questions.