ApsaraDB for ClickHouse:FAQ

Multiple bugs of open source ClickHouse are fixed in ApsaraDB for ClickHouse to ensure stability. ApsaraDB for ClickHouse provides the resource queue feature for you to assign priorities to the resource queues that are bound to users.

To resolve this issue, perform the following operations:

• Check whether the network connection is established. You can run the ping command to check the status of the network connection. You can also run the telnet command to check whether port 3306 and port 8123 of the ApsaraDB for ClickHouse cluster are enabled.

• Check whether a whitelist is configured for the ApsaraDB for ClickHouse cluster to which you want to connect. For more information about how to configure a whitelist for an ApsaraDB for ClickHouse cluster, see Configure a whitelist.

• Check whether the actual IP address of the client is used. The IP addresses of machines that are connected over the internal networks of enterprises are frequently changed. This way, the client IP address that you obtain may not be the actual IP address of the client. You can use an IP address checking tool such as WhatsMyIP to obtain the actual IP address of your client. For more information, see WhatsMyIP.

When you create an external table in an ApsaraDB for ClickHouse cluster of V20.3 or V20.8, the system automatically verifies the network connection between the ApsaraDB for ClickHouse cluster and the external platform. If the external table is created, the network connection is established. The external table may fail to be created due to one of the following causes:

• The table that you want to connect to is deployed in a cluster that is not in the same VPC as the ApsaraDB for ClickHouse cluster. In this case, the network connection fails to be established.

• A whitelist is configured for the MySQL instance to which you want to connect. The CIDR block of the VPC to which the ApsaraDB for ClickHouse cluster is connected is not included in the whitelist. In this case, add the CIDR block to the whitelist.

For this issue, the following sections provide the common causes and solutions:

• Cause 1: The VPC settings or Internet access settings are improper. If your application and the cluster are deployed in the same VPC, you can connect your application to the cluster over an internal network. If your application and the cluster are deployed in different VPCs, apply for a public endpoint for the cluster.

  Solution: Apply for a public endpoint for the cluster if your application and the cluster are deployed in different VPCs. For more information, see Apply for and release a public endpoint.

• Cause 2: The IP address of your application is not added to the whitelist of the cluster.

  Solution: Add the IP address of your application to the whitelist of the cluster. For more information, see Configure a whitelist.

• Cause 3: The security group of the Elastic Compute Service (ECS) instance on which the cluster runs denies access traffic.

  Solution: Enable the security group of the Elastic Compute Service (ECS) instance on which the cluster runs to allow access traffic. For more information, see Work with security groups.

• Cause 4: Your enterprise uses a network firewall.

  Solution: Modify the firewall rules.

• Cause 5: The account password in the connection string contains any of the following special characters: !@#$%^&*()_+=. These special characters cannot be recognized during a connection. As a result, the application fails to connect to the ApsaraDB for ClickHouse cluster.

  Solution: Escape special characters in the connection string by using the following escape rules:

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

  For example, if the password is ab@#c, the escaped characters of the password are ab%40%23c in the connection string.

• Cause 6: The ApsaraDB for ClickHouse cluster is attached to a Classic Load Balancer (CLB) instance by default. The CLB instance is charged by using the pay-as-you-go billing method. If your Alibaba Cloud account has an overdue payment, your ApsaraDB for ClickHouse cluster may be inaccessible.

  Solution: Check whether your Alibaba Cloud account has an overdue payment. If an overdue payment exists, pay off the overdue payment at your earliest opportunity.

To resolve this issue, perform the following operations:

• Split the OSS object into multiple objects. Then, import the data from these objects to the external table in the ApsaraDB for ClickHouse cluster.

• Scale up the memory capacity of the ApsaraDB for ClickHouse cluster. For more information, see Modify the configurations of an ApsaraDB for ClickHouse Community-compatible Edition cluster.

For this issue, the following sections provide the common causes and solutions:

• Cause 1: The specified parameter values are not suitable for the scenario. In ApsaraDB for ClickHouse, we recommend that you write data in multiple batches. Make sure that each batch contains a large volume of data and that a small number of concurrent data writing tasks are run at the same time. In most cases, a batch of data can contain tens of thousands of or even hundreds of thousands of rows. This depends on the size of a single row. In most cases, the estimated size of a row is 100 bytes. You must estimate a row size and calculate the number of rows based on the row size.

  Solution: Set the maximum number of concurrent write requests to 10. You can modify the settings of parameters and try again.

• Cause 2: In an exclusive resource group that is created in DataWorks, ECS instances have low specifications. In an exclusive resource group, the low specifications of CPU cores or memory can impose limits on the request concurrency or outbound bandwidth. If you specify a large batch size but have insufficient memory capacity, garbage collection can occur in DataWorks Java processes.

  Solution: Check the specifications of the ECS instances based on the output logs of DataWorks.

• Cause 3: A long period of time is required to perform a read operation in a data source.

  Solution: Search for totalWaitReaderTime and totalWaitWriterTime in the output logs of DataWorks. If the value of totalWaitReaderTime is much greater than the value of totalWaitWriterTime, a longer period of time is required to perform the read operation than the period of time required to perform the write operation.

• Cause 4: A public endpoint is used. A public endpoint provides a limited bandwidth and cannot ensure high-performance data import or export.

  Solution: Replace the public endpoint with a VPC endpoint.

• Cause 5: A data source contains dirty data. In normal cases, data is written in batches. If dirty data is read from the data source, the batch of data that is being written fails and a row of data is written each time. In addition, a large number of data parts are generated. This slows down the write operation.

  You can use the following methods to check whether dirty data is written:

  • Query data and check the error message that is returned. If the message contains "Cannot parse", dirty data is written.

    You can run the following sample code:

    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 after a batch of data is written. If the number of rows is 1, dirty data is written.

    You can run the following sample code:

    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.

To identify the cause of the inconsistency in the number of rows between the tables, perform the following steps:

1. Check whether an error is reported during data synchronization based on a system table named query_log. If an error is reported, data loss may occur.

2. Check whether the table engine that you use allows you to remove duplicates. If you use ReplacingMergeTree, the external table in the ApsaraDB for ClickHouse cluster may have a smaller number of rows compared with the table in the Hive instance.

3. Check whether the number of rows in the table that is stored in the Hive instance is correct.

To identify the cause of the inconsistency in the number of rows between the tables, perform the following steps:

1. Check whether an error is reported during data synchronization based on a system table named query_log. If an error is reported, data loss may occur.

2. Check whether the table engine that you use allows you to remove duplicates. If you use ReplacingMergeTree, the external table in the ApsaraDB for ClickHouse cluster may have a smaller number of rows compared with the table in the Kafka instance.

3. Check whether the kafka_skip_broken_messages parameter is set for the external table. If this parameter is set, ApsaraDB for ClickHouse may skip the Kafka messages that fail to be parsed. As a result, the external table in the ApsaraDB for ClickHouse cluster may have a smaller number of rows than the table in the Kafka instance.

To resolve this issue, perform the following operations:

• Use clickhouse-client to migrate data by exporting a file. For more information, see Migrate data from a self-managed ClickHouse cluster to an ApsaraDB for ClickHouse cluster.

• Execute the following statement to migrate data by using the remote function:

  INSERT INTO <Destination table> SELECT * FROM remote('<Connection string>', '<Database>', '<Table>', '<Username>', '<Password>');

Solution: Perform the following steps to execute another synchronization of MySQL data:

1. Delete the table on which data synchronization stopped.

   DROP TABLE <table_name> ON cluster default;

   Note

   The table_name parameter specifies the name of the table on which data synchronization stopped. If the table on which data synchronization stopped is a distributed table, the local table and the distributed table must be deleted.

2. Restart data synchronization.

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

   Note

   The <database_name> parameter specifies the name of the database that uses the MaterializeMySQL engine in ApsaraDB for ClickHouse.

For this issue, the following sections provide the common causes and solutions:

• Cause 1: The memory usage is high.

  Solution: Modify the settings of the max_insert_threads parameter to reduce the memory usage.

• Cause 2: You executed the INSERT INTO SELECT statement to import data from one ApsaraDB for ClickHouse cluster to another cluster.

  Solution: Migrate data by importing a file to the destination cluster. For more information, see Migrate data from a self-managed ClickHouse cluster to an ApsaraDB for ClickHouse cluster.

The ApsaraDB for ClickHouse server provides a memory tracker for each query thread. The memory trackers of all threads for each query report the memory usage to a memory tracker named memory tracker for query. Then, memory tracker for query reports the obtained information to the upper-layer memory tracker that is named memory tracker for total. To resolve the issue, perform the following operations based on your scenario:

• If the "Memory limit (for query)" message is returned, more than 70% of the cluster memory is consumed by queries. We recommend that you scale up the memory specifications.

• If the "Memory limit (for total)" message is returned, more than 90% of the cluster memory is consumed. We recommend that you reduce the number of concurrent queries. If the issue persists after you perform this operation, a large amount of memory may be consumed by asynchronous tasks in the background. In most cases, the deduplication of the records that contain the same primary key value after data is written consumes a large amount of memory. We recommend that you scale up the memory specifications.

By default, the maximum number of concurrent queries on the ApsaraDB for ClickHouse server is 100. You can change the value in the ApsaraDB for ClickHouse console. To change the maximum number of concurrent queries, perform the following steps:

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Clusters page, click the Default Instances tab and click the ID of the cluster that you want to manage.

3. In the left-side navigation pane of the cluster details page, click Parameter Configuration.

4. Change the value of the max_concurrent_queries parameter. Enter a valid value and click OK.

5. Click Submit Parameters.

6. Click OK.

To resolve this issue, perform the following operations:

• Check whether your ApsaraDB for ClickHouse cluster is a multi-node cluster. If your ApsaraDB for ClickHouse cluster has multiple nodes, you must create distributed tables on the nodes. Then, you can write data to the distributed tables and query data from the distributed tables. This way, the same query result is returned each time you execute the statement to query data from the distributed tables. If you do not use distributed tables, the data of a different shard may be queried each time you execute the statement. As a result, different query results are returned for the same statement. For more information about how to create a distributed table, see Create a table.

• Check whether your ApsaraDB for ClickHouse cluster is of Double-replica Edition. If you use an ApsaraDB for ClickHouse cluster of Double-replica Edition, create replicated tables on nodes. This ensures that your data is consistent between the replicas of a node. If you do not create replicated tables, the query result may vary based on the replica that is queried. For more information about how to create a replicated table, see Table engines.

For this issue, the following sections provide the common causes and solutions:

• Cause 1: An invalid DDL statement is used to create a table in an ApsaraDB for ClickHouse cluster. To create a table in a distributed ApsaraDB for ClickHouse cluster, you must include the ON CLUSTER default clause in the CREATE TABLE statement. If you execute only the CREATE TABLE statement, the table is created only on the server to which your client is connected. In this case, you can query data only from this table. If you connect your client to another server, you cannot find the table.

  Solution:

  1. When you create a table, execute the CREATE TABLE <table_name> ON CLUSTER default statement. Due to the ON CLUSTER default clause, the statement is executed on all nodes in the default cluster. The following sample statement provides an example:

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

  2. Create a distributed table engine on the table named test. You can execute the following statement to create a distributed table named test_dis:

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

• Cause 2: A ReplicatedMergeTree table is incorrectly configured. The ReplicatedMergeTree table engine is developed based on the MergeTree table engine and can be used to synchronize data between replicated tables. Only MergeTree table engines can be created in an ApsaraDB for ClickHouse cluster of Single-replica Edition. Only ReplicatedMergeTree table engines can be created in an ApsaraDB for ClickHouse cluster of Double-replica Edition.

  Solution: When you create a table in an ApsaraDB for ClickHouse cluster of Double-replica Edition, use ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') or ReplicatedMergeTree() to configure the ReplicatedMergeTree table engine. ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') is a fixed configuration and does not need to be modified.

Before you execute the OPTIMIZE statement, make sure that the following conditions are met. This ensures that the logic used to deduplicate the records that contain the same primary key value is valid.

• When you define a table to store data, the fields specified in the PARTITION BY clause must be included in the fields specified in the order by clause. If two records in different partitions contain the same primary key value, the records are not deduplicated.

• When you define a distributed table, the fields that you want to hash must be included in the fields specified in the order by clause. If two records on different nodes contain the same primary key value, the records are not deduplicated.

The following table describes common OPTIMIZE statements.

For this issue, the following sections provide the common causes and solutions:

• Cause 1: Eviction based on the specified TTL value occurs when the records that contain the same primary key value are being deduplicated. If the records that contain the same primary key value in data parts are not deduplicated for a long period of time, the expired records cannot be removed.

  Solution:

  • You can execute the OPTIMIZE TABLE statement by using the FINAL or PARTITION clause to trigger a task that deduplicates the records that contain the same primary key value.

  • When you create a table, you can set parameters such as merge_with_ttl_timeout and ttl_only_drop_parts to increase the deduplication frequency of the records that contain the same primary key value in the data parts that contain the expired data.

• Cause 2: The TTL for a table is specified or changed. As a result, TTL information may be incomplete or incorrect in existing data parts, and expired data may not be deleted.

  Solution:

  • You can execute the ALTER TABLE materialize ttl statement to reconfigure the TTL.

  • You can execute the OPTIMIZE TABLE test PARTITION tuple(); statement to update the TTL.

For a local table, you can execute a DDL statement to create, delete, or modify a column. For a distributed table, you can use either of the following methods to create, delete, or modify a column in different scenarios:

• If no data is being written to the distributed table, you can create, delete, or modify a column in the local table and then in the distributed table.

• If data is being written to the distributed table, you can use one of the following procedures to perform different operations.

  Type	Operation
  Create a nullable column.	Create or modify a column in the local table. Create or modify a column in the distributed table.
  Change the data type of a column if data type conversion is supported.
  Delete a nullable column.	Create or modify a column in the distributed table. Create or modify a column in the local table.
  Create a non-nullable column.	Stop writing data. Execute the SYSTEM FLUSH DISTRIBUTED statement. Create or modify a column in the local table. Create or modify a column in the distributed table. Resume writing data to the distributed table.
  Delete a non-nullable column.
  Change the name of a column.

To resolve this issue, perform the following operations:

• Wait until all DDL statements are executed.

• Stop executing DDL statements in the ApsaraDB for ClickHouse console.

• In the ApsaraDB for ClickHouse console, go to the cluster details page. In the left-side navigation pane, click Parameter Configuration. On the page that appears, change the value of a parameter and retain the previous value of the parameter. Then, click Submit Parameters.

  Note

  For more information about how to change the values of parameters, see What do I do if the number of concurrent queries exceeds the upper limit?

For this issue, the following sections provide the common causes and solutions:

• Cause 1: The ApsaraDB for ClickHouse client parses the syntax of statements. However, the SET GLOBAL ON CLUSTER default statement is valid only on the ApsaraDB for ClickHouse server. If the client version does not match the server version, the client reports a syntax error when you execute the SET GLOBAL ON CLUSTER default statement.

  Solution:

  • Use tools that do not parse the syntax of statements on a client. For example, you can use DataGrip or DBeaver with a JDBC driver.

  • You can also write a JDBC program to execute the SET GLOBAL ON CLUSTER default statement.

• Cause 2: You specified a string value that is not enclosed in single quotation marks (') in the SET GLOBAL ON CLUSTER default statement.

  Solution: Enclose the string value in the SET GLOBAL ON CLUSTER default statement in single quotation marks (' ').

To check the disk space that is used by each table, you can execute the following sample statement:

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; 

To query the size of cold data, you can execute the following sample statement:

SELECT * FROM system.disks;

To check which data is in cold storage, you can execute the following sample statement:

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

To dump data in a partition to cold storage, you can execute the following sample statement:

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

This issue occurs due to one of the following causes:

• When you query data, an OOM error is reported.

• The configuration of your cluster is modified. This causes your cluster to restart.

• The specifications of your cluster are scaled up or scaled down. This causes your cluster to restart.

The following table describes the common system tables.

The users.xml file contains information about user settings. You can modify the settings of user parameters in the file. To modify the settings of a user parameter, execute the following sample statement:

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

You can use the SETTINGS statement to change a resource quota. To change a resource quota, execute the following sample statement:

settings max_memory_usage = XXX;

If the destination cluster and the data source are deployed in the same region and use the same VPC, check whether the IP address of the destination cluster is added to the whitelist of the data source and whether the IP address of the data source is added to the whitelist of the destination cluster. If the IP addresses are not added to the whitelists, configure whitelists.

• For more information about how to configure a whitelist for an ApsaraDB for ClickHouse cluster, see Configure a whitelist.

• For more information about how to configure a whitelist for the data source, see the corresponding service document.

Otherwise, select an appropriate network solution to resolve the network connection issue and then add IP addresses to the whitelists.