ApsaraDB for ClickHouse:Vertical and horizontal scaling for Community-Compatible Edition clusters

Step 1: Record and remove Kafka/RabbitMQ tables

The scaling feature in the console cannot directly migrate Kafka and RabbitMQ engine tables. Before you scale the cluster, you must record the DDL statements for these tables and remove them from the cluster to avoid interfering with the scaling task.

1. Log on to the cluster and run the following statement to query all Kafka and RabbitMQ engine tables and their downstream dependencies.

   /*
   create_table_query: The table definition statement.
   dependencies_database: The database that depends on the table.
   dependencies_table: The name of the table that depends on the table.
   You can use dependencies_database and dependencies_table to identify materialized views that depend on Kafka/RabbitMQ tables.
   */
   SELECT * FROM system.tables WHERE engine IN ('RabbitMQ', 'Kafka');

2. View the materialized view definition to check if its target table is an implicit table.

   /*
   View the materialized view definition.
   If the target table of the materialized view is an implicit table, pay special attention to the following:
   When you delete the materialized view, the implicit table is also deleted, resulting in data loss.
   Example: If the TO clause is not specified in CREATE MATERIALIZED VIEW [db.]table_name [TO[db.]name],
   the system automatically creates an implicit table in the format '.inner_id.<TABLE_UUID>' or '.inner.<TABLE>'.
   */
   SELECT * FROM system.tables WHERE database='<DATABASE>' AND name = '<MATERIALIZED_VIEW_NAME>';

3. If a materialized view was created without a TO clause, the system automatically generates an implicit target table prefixed with .inner or .inner_id. These implicit tables are migrated to the new nodes during scaling, but due to internal naming mechanisms, directly re-creating the original materialized view may cause naming conflicts. Therefore, you must first rename the implicit target tables to regular table names.

   -- Rename the implicit target table to a regular table name (execute on all nodes).
   RENAME TABLE <database>.`.inner_id.<uuid>` TO <database>.<new_target_table_name> ON CLUSTER default;

4. Remove the materialized views and Kafka/RabbitMQ engine tables.

   Important

   You must remove the materialized views that reference the Kafka/RabbitMQ tables before you remove the Kafka/RabbitMQ engine tables. Otherwise, the scaling operation fails.

   -- Remove the materialized views first.
   DROP TABLE <database>.<materialized_view_name> ON CLUSTER default;
   
   -- Then, remove the Kafka/RabbitMQ engine tables.
   DROP TABLE <database>.<kafka_or_rabbitmq_table_name> ON CLUSTER default;

Step 2: Back up data of non-MergeTree tables

1. Log on to the cluster and run the following statement to identify the non-MergeTree tables that require data migration.

   SELECT
       `database` AS database_name,
       `name` AS table_name,
       `engine`
   FROM `system`.`tables`
   WHERE (`engine` NOT LIKE '%MergeTree%') AND (`engine` != 'Distributed') AND (`engine` != 'MaterializedView') AND (`engine` NOT IN ('Kafka', 'RabbitMQ')) AND (`database` NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')) AND (`database` NOT IN (
       SELECT `name`
       FROM `system`.`databases`
       WHERE `engine` IN ('MySQL', 'MaterializedMySQL', 'MaterializeMySQL', 'Lazy', 'PostgreSQL', 'MaterializedPostgreSQL', 'SQLite')
   ))

2. Back up the data.

   Back up the business data in the non-MergeTree tables. For more information, see Back up data to OSS.

Step 4: Re-create Kafka/RabbitMQ tables

When the scaling task enters the Data Migration stage (after the table schema migration is complete), use the DDL statements that you saved to re-create the Kafka/RabbitMQ engine tables and their downstream materialized views. After re-creation, incremental data resumes flowing and automatically synchronizes to the new cluster nodes.

-- Re-create Kafka/RabbitMQ engine tables.
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

-- Re-create the materialized view (use the TO clause to point to the renamed target table).
CREATE MATERIALIZED VIEW <database>.<materialized_view_name> TO <database>.<new_target_table_name>
AS SELECT ... FROM <database>.<kafka_or_rabbitmq_table_name>;

Step 5: Remove Kafka/RabbitMQ tables before write suspension

Before the configured write suspension window starts, remove the Kafka/RabbitMQ engine tables and their downstream materialized views to stop incremental data writes and ensure final data synchronization consistency.

-- Remove materialized views first.
DROP TABLE <database>.<materialized_view_name> ON CLUSTER default;

-- Then, remove Kafka/RabbitMQ engine tables.
DROP TABLE <database>.<kafka_or_rabbitmq_table_name> ON CLUSTER default;

Step 6: Re-create Kafka/RabbitMQ tables after scaling

After the scaling task is complete, use the DDL statements that you saved to re-create the Kafka/RabbitMQ engine tables and their downstream materialized views on the scaled cluster to restore the incremental data consumption pipeline.

-- Re-create Kafka/RabbitMQ engine tables.
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

-- Re-create the materialized view (use the TO clause to point to the renamed target table).
CREATE MATERIALIZED VIEW <database>.<materialized_view_name> TO <database>.<new_target_table_name>
AS SELECT ... FROM <database>.<kafka_or_rabbitmq_table_name>;

Step 7: Migrate data of non-MergeTree tables

Log on to the cluster and use OSS to migrate the data that you backed up in Step 2. For more information, see Import data from OSS.