ApsaraDB for ClickHouse：FAQ

ApsaraDB for ClickHouse primarily fixes stability bugs found in the community version and provides resource queues that allow you to configure resource usage priorities at the user role level.

You can use one of the following solutions:

• Check the network connection. You can run the ping command to check network connectivity. You can also run the telnet command to check whether database ports 3306 and 8123 are open.

• Check whether a whitelist is configured for ClickHouse. For more information, see Configure a whitelist.

• Check whether the client IP address is correct. The IP addresses of computers in a corporate network often change, so the displayed IP address may be incorrect. You can use a professional IP address lookup service to determine the originating IP address. For more information, see whatsmyip.

Versions 20.3 and 20.8 automatically verify the connection when you create an external table. If the table is created, it indicates that the network is connected. If the table cannot be created, the common causes are as follows:

• The destination endpoint and the ClickHouse cluster are not in the same VPC, which causes a network disconnection.

• The MySQL endpoint has whitelist settings configured. You must add the IP address of the ClickHouse cluster to the whitelist of the MySQL endpoint.

The common causes and solutions are as follows:

• Cause 1: The VPC or public network environment is not correctly configured. You can use an internal network connection if the program and the cluster are in the same VPC. If they are not in the same VPC, you must enable a public endpoint to connect.

  Solution: For more information about how to apply for a public endpoint, see Apply for and release a public endpoint.

• Cause 2: A whitelist is not configured for the cluster.

  Solution: For more information about how to configure a whitelist, see Configure a whitelist.

• Cause 3: The required ports are not open in the ECS security group.

  Solution: For more information about how to configure a security group, see Security group operations.

• Cause 4: A corporate network firewall is blocking the connection.

  Solution: Modify the firewall rules.

• Cause 5: The password in the connection string contains special characters, such as !@#$%^&*()_+=. These special characters may not be recognized during the connection and can cause the connection to fail.

  Solution: You must escape the special characters in the connection string. The escape rules are as follows:

  ! : %21
  @ : %40
  # : %23
  $ : %24
  % : %25
  ^ : %5e
  & : %26
  * : %2a
  ( : %28
  ) : %29
  _ : %5f
  + : %2b
  = : %3d

  Example: If the password is ab@#c, you must escape the special characters in the connection string. The password becomes ab%40%23c.

• Cause 6: By default, ApsaraDB for ClickHouse mounts an SLB instance, which is a pay-as-you-go service. If your account has an overdue payment, your ApsaraDB for ClickHouse instance may become inaccessible.

  Solution: Check whether your Alibaba Cloud account has an overdue payment. If it does, pay the outstanding balance promptly.

You can use one of the following solutions:

• Split the files on OSS into smaller files before you import them.

• Upgrade the memory of your instance. For more information about how to upgrade the memory, see Vertical scaling and horizontal scaling for Community-Compatible Edition clusters.

The common causes and solutions are as follows:

• Cause 1: Improper parameter settings are configured. ClickHouse is suitable for write operations that use large batches and a few concurrent processes. In most cases, the batch size can be as high as tens of thousands or even hundreds of thousands. The batch size depends on your row size. You can estimate the row size at 100 bytes per row, but you need to calculate it based on your actual data characteristics.

  Solution: We recommend that the number of concurrent processes does not exceed 10. You can try adjusting different parameters.

• Cause 2: The ECS instance of the exclusive resource group for DataWorks has low specifications. For example, if the CPU and memory of the exclusive resource are too small, the number of concurrent processes and the outbound bandwidth are limited. If the batch size is too large and the memory is too small, Java garbage collection (GC) may be triggered in the DataWorks process.

  Solution: Check the ECS instance specifications in the DataWorks output logs.

• Cause 3: Data is read slowly from the data source.

  Solution: Search for `totalWaitReaderTime` and `totalWaitWriterTime` in the DataWorks output logs. If `totalWaitReaderTime` is significantly greater than `totalWaitWriterTime`, the bottleneck is at the read end, not the write end.

• Cause 4: A public endpoint is used. The bandwidth of public endpoints is limited and cannot support high-performance data import and export.

  Solution: Replace the public endpoint with a VPC endpoint.

• Cause 5: Dirty data is present. Without dirty data, data is written in batches. However, if dirty data is encountered, the current batch write operation fails and falls back to row-by-row writing. This generates many data parts and significantly reduces the write speed.

  You can use one of the following methods to check for dirty data:

  • Check the error message. If the returned message contains Cannot parse, dirty data exists.

    The code is as follows:

    SELECT written_rows, written_bytes, query_duration_ms, event_time, exception
    FROM system.query_log
    WHERE event_time BETWEEN '2021-11-22 22:00:00' AND '2021-11-22 23:00:00' AND lowerUTF8(query) LIKE '%insert into <table_name>%' and type != 'QueryStart' and exception_code != 0
    ORDER BY event_time DESC LIMIT 30;

  • Check the number of rows in the batch. If the number of rows is 1, dirty data exists.

    The code is as follows:

    SELECT written_rows, written_bytes, query_duration_ms, event_time
    FROM system.query_log
    WHERE event_time BETWEEN '2021-11-22 22:00:00' AND '2021-11-22 23:00:00' AND lowerUTF8(query) LIKE '%insert into <table_name>%' and type != 'QueryStart'
    ORDER BY event_time DESC LIMIT 30;

  Solution: Delete or modify the dirty data in the data source.

You can use the following methods to troubleshoot the issue:

1. Check the `query_log` system table to see if any errors occurred during the import. If errors occurred, data loss may have happened.

2. Determine whether the table engine supports deduplication. For example, if you use the ReplacingMergeTree engine, the row count in ClickHouse may be smaller than in Hive.

3. Reconfirm the accuracy of the row count in Hive. The source row count may have been determined incorrectly.

You can use the following methods to troubleshoot the issue:

1. Check the `query_log` system table to see if any errors occurred during the import. If errors occurred, data loss may have happened.

2. Determine whether the table engine supports deduplication. For example, if you use the ReplacingMergeTree engine, the row count in ClickHouse may be smaller than in Kafka.

3. Check whether the `kafka_skip_broken_messages` parameter is configured for the Kafka external table. If this parameter is present, Kafka messages that fail to be parsed may be skipped. This causes the total number of rows in ClickHouse to be smaller than in Kafka.

You can use one of the following solutions:

• Migrate data by exporting files using the ClickHouse client. For more information, see Migrate data from a self-managed ClickHouse instance to an ApsaraDB for ClickHouse Community-Compatible Edition instance.

• Migrate data using the `remote` function.

  INSERT INTO <destination_table> SELECT * FROM remote('<connection_string>', '<database>', '<table>', '<username>', '<password>');

Solution: Resynchronize the MySQL data. To do so, perform the following steps:

1. Delete the table that stopped synchronizing.

   DROP TABLE <table_name> ON cluster default;

   Note

   table_name is the name of the table that stopped synchronizing. If the table has a distributed table, you must delete both the local table and the distributed table.

2. Restart the synchronization process.

   ALTER database <database_name> ON cluster default MODIFY SETTING skip_unsupported_tables = 1;

   Note

   <database_name> is the database in ApsaraDB for ClickHouse that is being synchronized.

The common causes and solutions are as follows:

• Cause 1: High memory usage.

  Solution: Adjust the `max_insert_threads` parameter to reduce potential memory usage.

• Cause 2: You are using an INSERT INTO SELECT statement to import data from one ClickHouse cluster to another.

  Solution: Migrate data by importing files. For more information, see Migrate data from a self-managed ClickHouse instance to an ApsaraDB for ClickHouse Community-Compatible Edition instance.

The ClickHouse server has a memory tracker for each query thread. The trackers for all threads in the same query report to a `memory tracker for query`. Above that is a `memory tracker for total`. You can use one of the following solutions based on your situation:

• If you encounter a Memory limit (for query) error, the query is using too much memory (70% of the instance's total memory) and has failed. In this case, you can perform a vertical upgrade to increase the instance's memory.

• If you encounter a Memory limit (for total) error, the total memory usage of the instance has exceeded the limit (90% of the instance's total memory). In this case, you can try to reduce query concurrency. If the issue persists, background asynchronous tasks may be using a large amount of memory. This is often the case for primary key merge tasks after a write. You need to perform a vertical upgrade to increase the instance's memory.

The default maximum query concurrency for the server is 100. You can modify this in the console. To modify the running parameter value, perform the following steps:

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Cluster List page, select List of Community Edition Instances, and click the target cluster ID.

3. In the navigation pane on the left, click Parameter Configuration.

4. On the Parameter Configuration page, click the edit button next to Operating parameter value for the max_concurrent_queries parameter.

5. Enter the target value in the pop-up box and click Confirm.

6. Click Submit parameters.

7. Click Submit parameters.

You can use one of the following solutions:

• Check whether it is a multi-node cluster. For multi-node clusters, you must create distributed tables, write data to them, and query them to obtain consistent results. Otherwise, each query accesses data from different shards, which results in inconsistent results. For more information about how to create a distributed table, see Create a distributed table.

• Check whether it is a master-replica cluster. Master-replica clusters require tables with Replicated series table engines to synchronize data between replicas. Otherwise, each query accesses a different replica, which results in inconsistent results. For more information about how to create a table with a Replicated series table engine, see Table engines.

The common causes and solutions are as follows:

• Cause 1: Issues with the table creation process. A distributed ClickHouse cluster does not have native distributed DDL semantics. If you use create table to create a table in a self-managed ClickHouse cluster, the query may return a success message, but the table is created only on the server to which you are currently connected. If you reconnect to a different server, you will not see this table.

  Solution:

  1. When you create a table, use the create table <table_name> on cluster default statement. The on cluster default declaration broadcasts this statement to all nodes in the default cluster for execution. The following code provides an example:

     CREATE TABLE test ON cluster default (a UInt64) Engine = MergeTree() ORDER BY tuple();

  2. Create a distributed table engine on the `test` table. The table creation statement is as follows:

     CREATE TABLE test_dis ON cluster default AS test Engine = Distributed(default, default, test, cityHash64(a));

• Cause 2: Issues with the ReplicatedMergeTree storage table configuration. The ReplicatedMergeTree table engine is an enhanced version of the MergeTree table engine with master-replica synchronization. Single-replica instances can only create MergeTree tables. Master-replica instances can only create ReplicatedMergeTree tables.

  Solution: When you create a table on a master-replica instance, use ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') or ReplicatedMergeTree() to configure the ReplicatedMergeTree table engine. The ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') configuration is fixed and does not require modification.

To ensure that data is correctly merged by primary key, the following two prerequisites must be met:

• The `PARTITION BY` field defined in the storage table must be included in the ORDER BY clause. Data from different partitions is not merged by primary key.

• The hash algorithm field defined in the distributed table must be included in the ORDER BY clause. Data from different nodes is not merged by primary key.

Common OPTIMIZE commands and their descriptions are as follows:

The common causes and solutions are as follows:

• Cause 1: Data TTL expiration is handled during the primary key merge phase. If data parts are not merged by primary key for a long time, expired data cannot be removed.

  Solution:

  • Manually trigger merge tasks using OPTIMIZE FINAL or OPTIMIZE PARTITION.

  • Set parameters such as `merge_with_ttl_timeout` and `ttl_only_drop_parts` when you create a table to increase the merge frequency of data parts that contain expired data.

• Cause 2: The table's TTL has been modified or added, and existing data parts lack TTL information or have incorrect information. This can also prevent expired data from being removed.

  Solution:

  • Regenerate TTL information using the ALTER TABLE ... MATERIALIZE TTL command.

  • Update TTL information using OPTIMIZE PARTITION.

Modifications to local tables can be run directly. To modify a distributed table, the procedure varies based on the situation:

• If no data is being written, you can modify the local table first, and then modify the distributed table.

• If data is being written, you must handle different types of operations differently:

  Type	Procedure
  Add a `Nullable` column	Modify the local table. Modify the distributed table.
  Modify a column's data type (if the types are convertible)
  Delete a `Nullable` column	Modify the distributed table. Modify the local table.
  Add a non-`Nullable` column	Stop data writing. Run `SYSTEM FLUSH DISTRIBUTED` on the distributed table. Modify the local table. Modify the distributed table. Resume data writing.
  Delete a non-`Nullable` column
  Modify a column name

You can use one of the following solutions:

• Wait for the operation to complete.

• Try to terminate the query in the console.

The common causes and solutions are as follows:

• Cause 1: The ClickHouse client parses syntax, and set global on cluster default is syntax added by the server. If the client has not been updated to a version that is aligned with the server, this syntax is blocked by the client.

  Solution:

  • Use tools that do not parse syntax on the client side, such as a JDBC driver, DataGrip, or DBeaver.

  • Write a JDBC program to run the statement.

• Cause 2: In set global on cluster default key = value;, `value` is a string, but the quotation marks are missing.

  Solution: Add quotation marks on both sides of the string-type value.

You can use the following code to view the disk space occupied by each table:

SELECT table, formatReadableSize(sum(bytes)) as size, min(min_date) as min_date, max(max_date) as max_date FROM system.parts WHERE active GROUP BY table; 

The following is sample code:

SELECT * FROM system.disks;

The following is sample code:

SELECT * FROM system.parts WHERE disk_name = 'cold_disk';

The following is sample code:

ALTER TABLE table_name MOVE PARTITION partition_expr TO DISK 'cold_disk';

The common causes are as follows:

• A query triggered an OOM error.

• A configuration change triggered a restart.

• The instance was restarted after an upgrade or downgrade.

The common system tables and their functions are as follows:

User-level parameters correspond to some configuration items in the `users.xml` file. You can run the following sample statement:

SET global ON cluster default ${key}=${value};

You can add it to the settings when you run a statement. The following is sample code:

settings max_memory_usage = XXX;

If your cluster is a master-replica cluster or a single-replica multi-node cluster, the CPU and memory utilization of the write node will be higher than other nodes when you perform many write operations. After data is synchronized to other nodes, the CPU and memory utilization will become balanced.

• Problem description:

  How to view detailed system log information to troubleshoot errors or identify potential issues.

• Solution:

  1. Check the `text_log.level` parameter of the cluster and perform the following operations:

     1. If `text_log.level` is empty, `text_log` is not enabled. You can set `text_log.level` to enable `text_log`.

     2. If `text_log.level` is not empty, check whether the `text_log` level meets your current requirements. If not, you can modify this parameter to set the `text_log` level.

     For more information about how to view and modify the `text_log.level` parameter, see Configure config.xml parameters.

  2. Log on to the destination database. For more information, see Connect to a database.

  3. Run the following statement for analysis:

     SELECT * FROM system.text_log;

If the target cluster and the data source use the same VPC and are in the same region, check whether they have added each other's IP addresses to their whitelists. If not, add the IP addresses to the whitelists.

• For more information about how to add an IP address to a whitelist in ClickHouse, see Configure a whitelist.

• For more information about how to add an IP address to a whitelist for other data sources, see the documentation for the respective product.

If the target cluster and the data source do not meet the preceding conditions, you can choose an appropriate network solution to resolve the network issue. Then, add each other's IP addresses to their whitelists.

Yes, you can migrate an ApsaraDB for ClickHouse Community-Compatible Edition cluster to an Enterprise Edition cluster.

There are two main methods for migrating data between Enterprise Edition and Community-Compatible Edition clusters: using the `remote` function or using file export and import. For more information, see Migrate data from a self-managed ClickHouse instance to an ApsaraDB for ClickHouse Community-Compatible Edition instance.

New Enterprise Edition instances of version 24.5 or later use the new analyzer as the default query engine. The new analyzer offers better query performance but may be incompatible with some older SQL syntax, which can cause parsing errors. If you encounter this error, you can run the following statement to revert to the old analyzer. For more information about the new analyzer, see New analyzer enabled by default.

SET allow_experimental_analyzer = 0;

The pause feature is not supported for ClickHouse Community Edition clusters, but it is available for Enterprise Edition clusters. To pause an Enterprise Edition cluster, go to the Enterprise Edition cluster list page. In the upper-left corner, select the destination region, find the target cluster, and click >Pause in the Actions column of the target cluster.