A method for automating the Clickhouse data migration process

Abstract

This invention discloses a method for automating the ClickHouse data migration process. The method includes retrieving all source tables from the source ClickHouse database; inputting the name of the destination ClickHouse database and the names of the source tables into a database creation script; executing the script to generate the destination ClickHouse database and tables at a specified path; and migrating data from the source ClickHouse database tables to the destination ClickHouse database tables using a data migration script based on the ClickHouse-Copier migration configuration file in ZooKeeper. This invention utilizes Shell scripting and Java programming languages, breaking down the high cost and complex configuration of ClickHouse-Copier into scripts to automate the data migration process. During the data migration, only the database name or table name and different logical identifier parameters need to be input to complete the migration. Furthermore, deep learning can be used to accurately pinpoint the cause of migration failures, enabling rapid troubleshooting of data migration problems.

Description

Technical Field

[0001] This invention relates to the field of data migration technology, and more specifically, to a method for automating the Clickhouse data migration process. Background Technology

[0002] The commonly used ClickHouse data migration method is to use the official tool ClickHouse-client to export the table creation SQL of the database and tables on the data source side, then execute the table creation SQL in the destination environment to create the database and tables to be migrated, and finally migrate the data to the destination ClickHouse cluster in the following three ways.

[0003] 1. The remote function connects two databases; the data source executes a query SQL and then inserts it into the destination database.

[0004] 2. Export the data from the data source as a CSV file, and then import the CSV file into the destination.

[0005] 3. Redirect the data retrieved from the data source to the destination via Linux pipeline.

[0006] You can use any of the above methods to migrate data from the ClickHouse data table on the data source side to the ClickHouse data table on the destination side.

[0007] The three commonly used migration methods currently have the advantages of being easy to operate, simple to configure, and having a low learning cost. However, they also have the following disadvantages:

[0008] 1. Single-process operation

[0009] 2. Limited configurable parameters at runtime

[0010] 3. Small amount of data transferred during the migration process

[0011] 4. Data cannot be resumed after interruption.

[0012] The tools mentioned above cannot adequately meet the current migration requirements for massive amounts of data that are stable, reliable, highly concurrent, and have high throughput.

[0013] ClickHouse's official data migration tool, ClickHouse-Copier, features multi-process, distributed tables, data sharding, and ZooKeeper to record migration status, which can meet the needs of stable, reliable, high-concurrency, and high-throughput data migration. However, it has shortcomings such as general ease of use, cumbersome configuration, and insufficient official reference materials, resulting in low efficiency in troubleshooting during the data migration process. Summary of the Invention

[0014] The purpose of this invention is to provide a method for automating the Clickhouse data migration process, thereby solving the aforementioned problems in the prior art.

[0015] The application is as follows:

[0016] A method for automating the ClickHouse data migration process, the method comprising running a Shell script to migrate data from a source ClickHouse database to a destination ClickHouse database;

[0017] The process of running a Shell script to migrate data from the source ClickHouse database to the destination ClickHouse database includes the following steps:

[0018] S1: Retrieve all source data tables from the source ClickHouse database, input the name of the destination ClickHouse database and the name of the source data table into the database creation script, execute the database creation script, and generate the destination ClickHouse database and data tables in the specified path;

[0019] S2: Pass the SQL file path of the data table under the destination ClickHouse database and the file name of the ClickHouse-Copier migration configuration template to the ClickHouse-Copier migration configuration file script through input parameters, and generate ClickHouse-Copier migration configuration files named after the destination ClickHouse data table in sequence. All ClickHouse-Copier migration configuration files are stored in the migration configuration file directory.

[0020] S3: Upload the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script;

[0021] S4: Based on the ClickHouse-Copier migration configuration file in ZooKeeper, the data in the source ClickHouse database tables is migrated to the destination ClickHouse database tables by executing the data migration script.

[0022] Furthermore, in step S2, the specific implementation process of the SQL file path of the data table under the destination ClickHouse database is as follows:

[0023] First, connect to the source ClickHouse database using clickhouse-client, execute the SQL statements used to view the database creation statements, and export the results to a target database file with the .sql extension. Then, use the sed command to remove newline characters and backslashes (\\) from this file. Next, connect to the source ClickHouse database again using clickhouse-client, export a list of all tables in the database to the target database file, and use a depth-first search algorithm to iterate through the exported table list file to check if all tables have been exported. If the table list file is not completely exported, iterate through the process using clickhouse-client to export the SQL statements used to create each table to the target table file. After exporting each table, use the sed command to remove newline characters and backslashes from the target table file until the table list file is completely exported. If the table list file is completely exported, generate the SQL file path for the tables.

[0024] Further, in step S2, the specific implementation process of the ClickHouse-Copier migration configuration file script is as follows:

[0025] The ClickHouse-Copier migration configuration script includes a ClickHouse SQL table file module, a ClickHouse-Copier migration configuration template file, and a ClickHouse-Copier migration configuration file generation JAR package. First, a script tool is developed using the Java programming language to read the target data table file and the ClickHouse-Copier migration configuration template file. This script tool reads the target data table file and the ClickHouse-Copier migration configuration template file into memory. Next, the script tool extracts various parameters from the data tables in the target data table file and fills these parameters into the ClickHouse-Copier migration configuration template file. Once the filling is complete, the script... This tool generates a ClickHouse-Copier migration configuration file named after the ClickHouse data table. Then, the script tool uses a shell to traverse the SQL files of all tables under the SQL file path of the target ClickHouse data table, filtering out tables without the ENGINE engine using the grep command. A JAR package is run in the JVM virtual machine, passing the SQL file path of the ClickHouse data table and the ClickHouse-Copier migration configuration template file name as input parameters. This sequentially generates the ClickHouse-Copier migration configuration file named after the ClickHouse data table, and simultaneously generates the ClickHouse-Copier migration configuration file script.

[0026] Furthermore, the specific implementation process of uploading the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script in step S3 is as follows:

[0027] The upload script uploads the ClickHouse-Copier migration configuration file using four methods, specifically including a first upload method, a second upload method, a third upload method, and a fourth upload method.

[0028] The first upload method represents the creation or update of a single file:

[0029] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a single file creation or update process.

[0030] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create it first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0031] If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command in ZooKeeper;

[0032] The second upload method indicates the creation of multiple files:

[0033] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a process to create multiple files.

[0034] First, check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory. If it exists, create the ClickHouse data table directory. Then, iterate through all migration configuration files in the migration configuration file directory and create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0035] The third upload method refers to the creation or updating of multiple files in a partition:

[0036] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes multiple partition-based data migration creation or update processes.

[0037] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, iterate through all the configuration files in the partition migration configuration file directory, create the ClickHouse data table directory, and then use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0038] If it is an update operation, the contents of the description file in the ClickHouse data table directory will be reset using the set command in ZooKeeper;

[0039] The fourth upload method refers to the creation or updating of a single file in a partition:

[0040] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes a single partition-based data migration creation or update process.

[0041] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0042] If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command of ZooKeeper.

[0043] Further, in step S4, the specific implementation process of migrating data from the source ClickHouse database tables to the destination ClickHouse database tables by executing a data migration script based on the ClickHouse-Copier migration configuration file in ZooKeeper is as follows:

[0044] The data migration script supports three modes, namely, the first mode, the second mode, and the third mode.

[0045] The first mode is used for full migration of a single table, and the specific implementation method is as follows:

[0046] The data migration script takes two parameters: the ClickHouse database name and the ClickHouse table name. These two parameters are concatenated to form the data migration configuration file path, which represents the path in ZooKeeper. The data migration process then starts, migrating data according to the configuration file path. If the data migration is complete, the clickhouse-client queries the source and destination ClickHouse databases for the total number of records in the migrated tables. If the total number of records matches, the script indicates successful data migration; otherwise, it indicates data migration failure. The script then investigates the cause of the failure and attempts to migrate again.

[0047] The second mode is used for full migration of multiple tables, and the specific implementation method is as follows:

[0048] The data migration script takes one Clickhouse database name as a parameter.

[0049] Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables;

[0050] The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log.

[0051] The data migration configuration file path is constructed by concatenating the input ClickHouse database name and the ClickHouse table names to be traversed. This path represents the path in ZooKeeper. Then, the data migration process is started to begin migrating data. If the data migration is complete, the clickhouse-client queries the source and destination ClickHouse databases for the total number of data tables being migrated. If the total number of data tables matches, a message indicating successful data migration is displayed. If the total number of data tables does not match, a message indicating data migration failure is displayed. The cause of the data migration failure is investigated, and the migration is restarted. If the number of ClickHouse tables traversed by the data migration script equals the preset total number of ClickHouse tables, the process ends.

[0052] The third mode is used for full migration of multiple tables by partition. The specific implementation method is as follows:

[0053] The data migration script takes one Clickhouse database name as a parameter.

[0054] Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables;

[0055] The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log.

[0056] Iterate through the migration configuration files of all ClickHouse tables in the increase_partition_copier_tasks directory under the data partition migration configuration file directory, get the name of the ClickHouse table, and print information such as which table is being migrated and how many tables are left to be migrated to the data migration script execution log.

[0057] Read the partition difference list file, query the partition data of this table in the destination ClickHouse database through clickhouse-client. If the total number of data in the data table is greater than 0 and the number of partitions is equal to 1, then drop the partition data of this table in the destination ClickHouse database; if the total number of data in the data table is greater than 0 and the number of partitions is equal to 2, then find the second partition through the first partition and drop the second partition.

[0058] After data is deleted from the partitions of the data tables in the destination ClickHouse database, the data migration configuration file path is constructed by concatenating the input ClickHouse database name and the names of the ClickHouse data tables to be traversed. This data migration configuration file path represents the path in ZooKeeper. Then, the data migration process is started to migrate the data. If the data migration is complete, clickhouse-client queries the total number of data tables being migrated in both the source and destination ClickHouse databases. If the total number of data tables matches, a message indicating successful data migration is displayed. If the total number of data tables does not match, a message indicating data migration failure is displayed. The cause of the data migration failure is investigated, and the migration is restarted. If the number of ClickHouse data tables traversed by the data migration script equals the preset total number of ClickHouse data tables, the process ends.

[0059] Furthermore, the specific implementation process for investigating the reasons for data migration failure is as follows:

[0060] Collect historical migration failure data, including script input parameter error data, database account password error data, script log error data, and network error data;

[0061] Natural language processing techniques are used to extract normalized features from the historical migration failure data to generate migration failure feature data. Part of the migration failure feature data is used for the training set and part is used for the validation set.

[0062] A deep learning model for transfer failure errors is constructed, and the model is trained using training set data. Training stops when the training of the deep learning model for transfer failure errors meets a preset recognition rate. The input of the deep learning model for transfer failure errors is transfer failure feature data, and the output of the deep learning model for transfer failure errors is the transfer error type.

[0063] The trained deep learning model for migration failure errors is used to analyze the reasons for data migration failure in real time and output the migration error type.

[0064] Furthermore, the network error data includes errors caused by network connection bandwidth, latency, and packet loss.

[0065] Furthermore, the feature extraction of the script log error data also includes:

[0066] Spatial features of script log error data are extracted using a convolutional neural network (CNN), and the temporal order relationship of script log error data is captured by a long short-term memory (LSTM) network to generate migration failure feature data.

[0067] Compared with the prior art, the embodiments of the present invention achieve the following beneficial effects:

[0068] This invention provides a method to retrieve all source data tables from a source ClickHouse database, input the name of the destination ClickHouse database and the names of the source data tables into a database creation script, execute the script to generate the destination ClickHouse database and data tables in a specified path, and then input the SQL file path of the data tables in the destination ClickHouse database and the ClickHouse-Copier migration configuration file name into a ClickHouse-Copier migration configuration file script as input parameters to generate ClickHouse-Copier migration configuration files named after the destination ClickHouse data tables. These ClickHouse-Copier migration configuration files are stored in a migration configuration file directory. The method then executes an upload script. This invention uploads the ClickHouse-Copier migration configuration file to ZooKeeper. Based on the ClickHouse-Copier migration configuration file in ZooKeeper, a data migration script is executed to migrate data from the source ClickHouse database tables to the destination ClickHouse database tables. This invention uses Shell scripting language and Java programming language to break down the high cost and cumbersome configuration of ClickHouse-Copier into a process and encapsulate it into a script, thereby automating the data migration process. When executing the data migration process, only the database name or table name and different logical identifier parameters need to be entered to complete the data migration. At the same time, deep learning can be used to accurately locate the cause of data migration failure, enabling rapid troubleshooting of data migration problems. Attached Figure Description

[0069] Figure 1 This is a flowchart illustrating a method for automating the Clickhouse data migration process according to an embodiment of the present invention;

[0070] Figure 2 This is a flowchart of the data table creation statement export method provided by an embodiment of the present invention for automating the Clickhouse data migration process;

[0071] Figure 3 This is a flowchart of a method for automating the Clickhouse data migration process, provided by an embodiment of the present invention, to generate a data migration configuration file.

[0072] Figure 4 This is a flowchart of the data migration configuration file upload process provided by an embodiment of the present invention to automate the Clickhouse data migration process;

[0073] Figure 5 This is a data migration execution flowchart provided by an embodiment of the present invention for automating the Clickhouse data migration process. Detailed Implementation

[0074] The present invention will now be described in detail with reference to the accompanying drawings.

[0075] Example 1

[0076] This invention provides a method for automating the ClickHouse data migration process, the method including running a Shell script to migrate data from a source ClickHouse database to a destination ClickHouse database;

[0077] The shell script is run to migrate data from the source ClickHouse database to the destination ClickHouse database, such as... Figure 1 As shown, it includes the following steps:

[0078] S1: Retrieve all source data tables from the source ClickHouse database, input the name of the destination ClickHouse database and the name of the source data table into the database creation script, execute the database creation script, and generate the destination ClickHouse database and data tables in the specified path;

[0079] S2: Pass the SQL file path of the data table under the destination ClickHouse database and the file name of the ClickHouse-Copier migration configuration template to the ClickHouse-Copier migration configuration file script through input parameters, and generate ClickHouse-Copier migration configuration files named after the destination ClickHouse data table in sequence. All ClickHouse-Copier migration configuration files are stored in the migration configuration file directory.

[0080] S3: Upload the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script;

[0081] S4: Based on the ClickHouse-Copier migration configuration file in ZooKeeper, the data in the source ClickHouse database tables is migrated to the destination ClickHouse database tables by executing the data migration script.

[0082] Furthermore, in step S2, the specific implementation process of the SQL file path of the data table under the destination ClickHouse database is as follows:

[0083] First, connect to the source ClickHouse database using clickhouse-client, execute the SQL statements used to view the database creation statements, and export the results to a target database file with the .sql extension. Then, use the sed command to remove newline characters and backslashes (\\) from this file. Next, connect to the source ClickHouse database again using clickhouse-client, export a list of all tables in the database to the target database file, and use a depth-first search algorithm to iterate through the exported table list file to check if all tables have been exported. If the table list file is not completely exported, iterate through the process using clickhouse-client to export the SQL statements used to create each table to the target table file. After exporting each table, use the sed command to remove newline characters and backslashes from the target table file until the table list file is completely exported. If the table list file is completely exported, generate the SQL file path for the tables.

[0084] Further, in step S2, the specific implementation process of the ClickHouse-Copier migration configuration file script is as follows:

[0085] The ClickHouse-Copier migration configuration script includes a ClickHouse SQL table file module, a ClickHouse-Copier migration configuration template file, and a ClickHouse-Copier migration configuration file generation JAR package. First, a script tool is developed using the Java programming language to read the target data table file and the ClickHouse-Copier migration configuration template file. This script tool reads the target data table file and the ClickHouse-Copier migration configuration template file into memory. Next, the script tool extracts various parameters from the data tables in the target data table file and fills these parameters into the ClickHouse-Copier migration configuration template file. Once the filling is complete, the script... This tool generates a ClickHouse-Copier migration configuration file named after the ClickHouse data table. Then, the script tool uses a shell to traverse the SQL files of all tables under the SQL file path of the target ClickHouse data table, filtering out tables without the ENGINE engine using the grep command. A JAR package is run in the JVM virtual machine, passing the SQL file path of the ClickHouse data table and the ClickHouse-Copier migration configuration template file name as input parameters. This sequentially generates the ClickHouse-Copier migration configuration file named after the ClickHouse data table, and simultaneously generates the ClickHouse-Copier migration configuration file script.

[0086] Furthermore, the specific implementation process of uploading the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script in step S3 is as follows:

[0087] The upload script uploads the ClickHouse-Copier migration configuration file using four methods, specifically including a first upload method, a second upload method, a third upload method, and a fourth upload method.

[0088] The first upload method represents the creation or update of a single file:

[0089] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a single file creation or update process.

[0090] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create it first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0091] If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command in ZooKeeper;

[0092] The second upload method indicates the creation of multiple files:

[0093] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a process to create multiple files.

[0094] First, check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory. If it exists, create the ClickHouse data table directory. Then, iterate through all migration configuration files in the migration configuration file directory and create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0095] The third upload method refers to the creation or updating of multiple files in a partition:

[0096] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes multiple partition-based data migration creation or update processes.

[0097] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, iterate through all the configuration files in the partition migration configuration file directory, create the ClickHouse data table directory, and then use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0098] If it is an update operation, the contents of the description file in the ClickHouse data table directory will be reset using the set command in ZooKeeper;

[0099] The fourth upload method refers to the creation or updating of a single file in a partition:

[0100] The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes a single partition-based data migration creation or update process.

[0101] If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory.

[0102] If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command of ZooKeeper.

[0103] Further, in step S4, the specific implementation process of migrating data from the source ClickHouse database tables to the destination ClickHouse database tables by executing a data migration script based on the ClickHouse-Copier migration configuration file in ZooKeeper is as follows:

[0104] The data migration script supports three modes, namely, the first mode, the second mode, and the third mode.

[0105] The first mode is used for full migration of a single table, and the specific implementation method is as follows:

[0106] The data migration script takes two parameters: the ClickHouse database name and the ClickHouse table name. These two parameters are concatenated to form the data migration configuration file path, which represents the path in ZooKeeper. The data migration process then starts, migrating data according to the configuration file path. If the data migration is complete, the clickhouse-client queries the source and destination ClickHouse databases for the total number of records in the migrated tables. If the total number of records matches, the script indicates successful data migration; otherwise, it indicates data migration failure. The script then investigates the cause of the failure and attempts to migrate again.

[0107] The second mode is used for full migration of multiple tables, and the specific implementation method is as follows:

[0108] The data migration script takes one Clickhouse database name as a parameter.

[0109] Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables;

[0110] The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log.

[0111] The data migration configuration file path is constructed by concatenating the input ClickHouse database name and the ClickHouse table names to be traversed. This path represents the path in ZooKeeper. Then, the data migration process is started to begin migrating data. If the data migration is complete, the clickhouse-client queries the source and destination ClickHouse databases for the total number of data tables being migrated. If the total number of data tables matches, a message indicating successful data migration is displayed. If the total number of data tables does not match, a message indicating data migration failure is displayed. The cause of the data migration failure is investigated, and the migration is restarted. If the number of ClickHouse tables traversed by the data migration script equals the preset total number of ClickHouse tables, the process ends.

[0112] The third mode is used for full migration of multiple tables by partition. The specific implementation method is as follows:

[0113] The data migration script takes one Clickhouse database name as a parameter.

[0114] Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables;

[0115] The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log.

[0116] Iterate through the migration configuration files of all ClickHouse tables in the increase_partition_copier_tasks directory under the data partition migration configuration file directory, get the name of the ClickHouse table, and print information such as which table is being migrated and how many tables are left to be migrated to the data migration script execution log.

[0117] Read the partition difference list file, query the partition data of this table in the destination ClickHouse database through clickhouse-client. If the total number of data in the data table is greater than 0 and the number of partitions is equal to 1, then drop the partition data of this table in the destination ClickHouse database; if the total number of data in the data table is greater than 0 and the number of partitions is equal to 2, then find the second partition through the first partition and drop the second partition.

[0118] After data is deleted from the partitions of the data tables in the destination ClickHouse database, the data migration configuration file path is constructed by concatenating the input ClickHouse database name and the names of the ClickHouse data tables to be traversed. This data migration configuration file path represents the path in ZooKeeper. Then, the data migration process is started to migrate the data. If the data migration is complete, clickhouse-client queries the total number of data tables being migrated in both the source and destination ClickHouse databases. If the total number of data tables matches, a message indicating successful data migration is displayed. If the total number of data tables does not match, a message indicating data migration failure is displayed. The cause of the data migration failure is investigated, and the migration is restarted. If the number of ClickHouse data tables traversed by the data migration script equals the preset total number of ClickHouse data tables, the process ends.

[0119] Furthermore, the specific implementation process for investigating the reasons for data migration failure is as follows:

[0120] Collect historical migration failure data, including script input parameter error data, database account password error data, script log error data, and network error data;

[0121] Natural language processing techniques are used to extract normalized features from the historical migration failure data to generate migration failure feature data. Part of the migration failure feature data is used for the training set and part is used for the validation set.

[0122] A deep learning model for transfer failure errors is constructed, and the model is trained using training set data. Training stops when the training of the deep learning model for transfer failure errors meets a preset recognition rate. The input of the deep learning model for transfer failure errors is transfer failure feature data, and the output of the deep learning model for transfer failure errors is the transfer error type.

[0123] The trained deep learning model for migration failure errors is used to analyze the reasons for data migration failure in real time and output the migration error type.

[0124] Furthermore, network error data includes errors caused by network connection bandwidth, latency, and packet loss.

[0125] Furthermore, the feature extraction of the script log error data also includes:

[0126] Spatial features of script log error data are extracted using a convolutional neural network (CNN), and the temporal order relationship of script log error data is captured by a long short-term memory (LSTM) network to generate migration failure feature data.

[0127] Example 2, as Figure 2 It includes the following steps:

[0128] 1. Execute the script using the sh command in the Linux server terminal, and pass the ClickHouse database name (the name of the table creation statement to be exported) to the end of the script;

[0129] 2. Check if the directories ck_databases and ck_tables exist. If they exist, clear the files in both directories; otherwise, create the two directories.

[0130] 3. Export the database creation statements to a file named with the .sql extension using clickhouse-client, and save the file to the ck_databases directory;

[0131] 4. Remove invalid characters from the database creation statement;

[0132] 5. Export a list of tables from the database using ClickHouse-Client;

[0133] 6. Traverse the list of data tables, export the table creation statements for each data table to a file named after the data table with the suffix "sql", and save the file to the ck_tables directory;

[0134] 7. Remove invalid characters from the database creation statement;

[0135] 8. End the process.

[0136] Example 3, as Figure 3 It includes the following steps:

[0137] 1. Execute the script using the sh command in the Linux server terminal, and pass the name of the database from which the data migration configuration file is to be generated after the script;

[0138] 2. Check if the copier_tasks directory exists. If it exists, clear the files in the directory; otherwise, create the directory.

[0139] 3. Traverse all data table creation files under the database directory in the ck_tables directory, and check if there is an ENGINE in the creation statement. If there is, proceed to the data migration configuration file generation process; otherwise, print a prompt message.

[0140] 4. Pass the data table creation statement file and the data migration configuration file template name to the data migration configuration file to create the data table. After successful creation, write the contents of the configuration file to a file named after the data table with the .xml extension and save it to the copier_tasks directory.

[0141] 5. End the process.

[0142] Example 4, as Figure 4 It includes the following steps:

[0143] 1. Execute the script using the sh command in the Linux server terminal, and pass the operation type parameter 1, create or update, and the ClickHouse data table name to the script. At this time, the process of uploading or updating the data migration configuration file for a single data table will be executed.

[0144] 2. After the ZooKeeper client connects to ZooKeeper, it checks whether a directory named after the database table exists in ZooKeeper; if not, it creates it.

[0145] 3. If the second parameter is create, the ZooKeeper client creates a directory named after the data table in the database directory and writes the contents of the data migration configuration file with the same name as the data table in the copier_tasks directory into a file named description in the data table directory;

[0146] 4. If the second parameter is update, the ZooKeeper client will use the set command to reset the contents of the description file in the database / table directory to the contents of the data migration configuration file in the copier_tasks directory with the same name as the table.

[0147] 5. End the process.

[0148] Example 5, as Figure 4 It includes the following steps:

[0149] 1. Execute the script in the Linux server terminal using the sh command, and pass the operation type parameter 2, create, and the ClickHouse database name to the script. At this time, the process of migrating and uploading all data configuration files in the copier_tasks directory will be executed.

[0150] 2. After the ZooKeeper client connects to ZooKeeper, it checks whether a directory named after the database table exists in ZooKeeper; if not, it creates it.

[0151] 3. Traverse all data migration configuration files in the copier_tasks directory. The ZooKeeper client creates directories named after the data tables in the database directory in turn, and writes the contents of the data migration configuration files with the same names as the data tables in the copier_tasks directory into a file named description in the data table directory.

[0152] 4. End the process.

[0153] Example 6, as Figure 4 The usage method of this embodiment includes the following steps:

[0154] 1. Execute the script in the Linux server terminal using the sh command, and pass the operation type parameter 3, create or update, and the ClickHouse database name to the script. At this time, the process of uploading or updating all data migration configuration files in the increase_copier_tasks directory will be executed.

[0155] 2. After the ZooKeeper client connects to ZooKeeper, it checks whether a directory named after the database table exists in ZooKeeper; if not, it creates it.

[0156] 3. If the second parameter is create, iterate through all data migration configuration files in the increase_copier_tasks directory. The ZooKeeper client creates directories named after the data tables in the database directory in turn, and writes the contents of the data migration configuration files with the same names as the data tables in the increase_copier_tasks directory into a file named description in the data table directory.

[0157] 4. If the second parameter is update, iterate through all data migration configuration files in the increase_copier_tasks directory. The ZooKeeper client will then reset the description file content in the database / table directory to the content of the data migration configuration file with the same name as the table in the increase_copier_tasks directory.

[0158] 5. The example run has ended.

[0159] Example 7, as Figure 4 The usage method of this embodiment includes the following steps:

[0160] 1. Execute the script in the Linux server terminal using the sh command, and pass the operation type parameter 4, create or update, and the ClickHouse data table name to the script. At this time, the process of uploading or updating the data migration configuration file with the same name as the data table in the increase_copier_tasks directory will be executed.

[0161] 2. After the ZooKeeper client connects to ZooKeeper, it checks whether a directory named after the database table exists in ZooKeeper; if not, it creates it.

[0162] 3. If the second parameter is create, the ZooKeeper client writes the contents of the data migration configuration file with the same name as the data table in the increase_copier_tasks directory to a file named description in the data table directory;

[0163] 4. If the second parameter is update, the ZooKeeper client will reset the contents of the description file in the data table directory to the contents of the data migration configuration file in the increase_copier_tasks directory with the same name as the data table;

[0164] 5. The example run has ended.

[0165] Example 8, as Figure 5 The usage method of this embodiment includes the following steps:

[0166] 1. Execute the script using the sh command in the Linux server terminal, and pass the operation type parameter 1 and the ClickHouse data table name to the script. At this time, the process of migrating a single data table will be executed.

[0167] 2. clickhouse-copier obtains the data migration configuration file information from ZooKeeper using the ZooKeeper configuration file zookeeper.xml and the passed-in ClickHouse data table name, starts the data migration process, and terminates the service process after the data migration is completed.

[0168] 3. ClickHouse-client connects to both the data source cluster and the data destination cluster, counts the total number of data tables being migrated, and checks if the total number of data tables on both ends is consistent. If the total number of data tables is consistent, it prints a message indicating successful data migration; if they are inconsistent, it prints a message indicating data migration failure; if the total number of data tables on the data source side is 0, it prompts the user to manually create the tables on the data destination side.

[0169] 4. The example run has ended.

[0170] Example 9, as Figure 5 The usage method of this embodiment includes the following steps:

[0171] 1. Execute the script using the sh command in the Linux server terminal, and pass the operation type parameter 2 after the script. At this time, the process of migrating multiple data tables will be executed.

[0172] 2. Traverse all data table migration configuration files under the copier_tasks directory, obtain the data table names, and pass them to clickhouse-copier in turn, then execute the following steps;

[0173] 3. clickhouse-copier obtains the data migration configuration file information from ZooKeeper using the ZooKeeper configuration file zookeeper.xml and the passed-in ClickHouse data table name, starts the data migration process, and terminates the service process after the data migration is completed.

[0174] 4. ClickHouse-client connects to both the data source cluster and the data destination cluster, counts the total number of tables being migrated, and checks if the total number of tables on both ends is consistent. If the total number of tables is consistent, it prints a message indicating successful data migration; otherwise, it prints a message indicating data migration failure; if the total number of tables on the data source side is 0, it prompts the user to manually create the tables on the data destination side.

[0175] 5. After one loop ends, continue from step 3 to step 4 until all data table migration configuration files under the copier_tasks directory have been traversed;

[0176] 6. This embodiment is now complete.

[0177] Example 10, as Figure 5 The usage method of this embodiment includes the following steps:

[0178] 1. Execute the script using the sh command in the Linux server terminal, and pass the operation type parameter 4 after the script. At this time, the process of migrating multiple data tables by partition will be executed.

[0179] 2. Traverse all data table migration configuration files under the increase_copier_tasks directory to obtain the data table names;

[0180] 3. Enter the partition data difference directory, read the file named after the data table, and then store the number of partitions and the amount of data in each partition into a variable;

[0181] 4. If the total number of partition data is greater than 0 and there is only one partition, delete the partition using clickhouse-client; if the total number of partition data is greater than 0 and there are two partitions, delete both partitions using clickhouse-client.

[0182] 5. If the total number of data in the partition is 0, no action is required; proceed directly to the next step.

[0183] 7. clickhouse-copier obtains the data migration configuration file information from ZooKeeper using the ZooKeeper configuration file zookeeper.xml and the passed-in ClickHouse data table name, starts the data migration process, and terminates the service process after the data migration is completed.

[0184] 8. ClickHouse-Client connects to both the data source cluster and the data destination cluster, counts the total number of data tables being migrated, and checks if the total number of data tables on both ends is consistent. If the total number of data tables is consistent, it prints a message indicating successful data migration; if they are inconsistent, it prints a message indicating data migration failure; if the total number of data tables on the data source side is 0, it prompts the user to manually create the tables on the data destination side.

[0185] 9. After one loop ends, continue from step 3 to step 8 until all data table migration configuration files under the increase_copier_tasks directory have been traversed.

[0186] The example run has ended.

[0187] The algorithms and displays provided herein are not inherently related to any particular computer, virtual system, or other device. Various general-purpose systems can also be used in conjunction with the teachings herein. The required structure for constructing such systems is apparent from the above description. Furthermore, this invention is not directed to any particular programming language. It should be understood that the contents of the invention described herein can be implemented using various programming languages, and the above description of specific languages ​​is for the purpose of disclosing the best mode of implementation of the invention.

[0188] Numerous specific details are set forth in the specification provided herein. However, it will be understood that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures, and techniques have not been shown in detail so as not to obscure the understanding of this specification.

[0189] Similarly, it should be understood that, in order to simplify this disclosure and aid in understanding one or more of the various aspects of the invention, in the above description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together in a single embodiment, figure, or description thereof. However, this method of disclosure should not be construed as reflecting an intention that the claimed invention requires more features than are expressly recited in each claim. Rather, as reflected in the following claims, inventive aspects lie in fewer than all features of a single foregoing disclosed embodiment. Therefore, the claims following the detailed description are hereby expressly incorporated into this detailed description, wherein each claim itself is a separate embodiment of the invention.

[0190] Those skilled in the art will understand that modules in the device of the embodiments can be adaptively changed and placed in one or more devices different from that embodiment. Modules, units, or components in the embodiments can be combined into a single module, unit, or component, and further, they can be divided into multiple sub-modules, sub-units, or sub-components. Except where at least some of such features and / or processes or units are mutually exclusive, any combination can be used to combine all features disclosed in this specification (including the accompanying claims, abstract, and drawings) and all processes or units of any method or device so disclosed. Unless expressly stated otherwise, each feature disclosed in this specification (including the accompanying claims, abstract, and drawings) may be replaced by an alternative feature that serves the same, equivalent, or similar purpose.

[0191] Furthermore, those skilled in the art will understand that although some embodiments herein include certain features included in other embodiments but not others, combinations of features from different embodiments are intended to be within the scope of the invention and form different embodiments. For example, in the following claims, any of the claimed embodiments can be used in any combination.

[0192] The various component embodiments of the present invention can be implemented in hardware, or as software modules running on one or more processors, or a combination thereof. Those skilled in the art will understand that microprocessors or digital signal processors (DSPs) can be used in practice to implement some or all of the functions of some or all of the components in the apparatus according to embodiments of the present invention. The present invention can also be implemented as a device or apparatus program (e.g., a computer program and computer program product) for performing part or all of the methods described herein. Such programs implementing the present invention can be stored on a computer-readable medium or can be in the form of one or more signals. Such signals can be downloaded from an Internet website, provided on a carrier signal, or provided in any other form.

Claims

1. A method for implementing Clickhouse data migration process automation, characterized in that, The method includes running a Shell script to migrate data from the source ClickHouse database to the destination ClickHouse database; The process of running a Shell script to migrate data from the source ClickHouse database to the destination ClickHouse database includes the following steps: S1: Retrieve all source data tables from the source ClickHouse database, input the name of the destination ClickHouse database and the name of the source data table into the database creation script, execute the database creation script, and generate the destination ClickHouse database and data tables in the specified path; S2: Pass the SQL file path of the data table under the destination ClickHouse database and the file name of the ClickHouse-Copier migration configuration template to the ClickHouse-Copier migration configuration file script through input parameters, and generate ClickHouse-Copier migration configuration files named after the destination ClickHouse data table in sequence. All ClickHouse-Copier migration configuration files are stored in the migration configuration file directory. S3: Upload the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script; S4: Based on the ClickHouse-Copier migration configuration file in ZooKeeper, migrate the data from the source ClickHouse database tables to the destination ClickHouse database tables by executing the data migration script; In step S2, the specific implementation process of the SQL file path of the data table under the destination ClickHouse database is as follows: First, connect to the source ClickHouse database using clickhouse-client, execute the SQL statements used to view the database creation statements, and export the results to a target database file with the .sql extension. Then, use the sed command to remove newline characters and backslashes from this file. Next, connect to the source ClickHouse database again using clickhouse-client, export a list of all tables in the database to the target database file, and use a depth-first search algorithm to iterate through the exported table list file to check if all tables have been exported. If the table list file has not been completely exported, iterate through the process of exporting the SQL statements used to create each table to the target table file using clickhouse-client. After exporting each table, use the sed command to remove newline characters and backslashes from the target table file until the table list file has been completely traversed. If the export of the data table list file is complete, the SQL file path for the generated data tables will be determined.

2. The method for automating the Clickhouse data migration process according to claim 1, characterized in that, In step S2, the specific implementation process of the ClickHouse-Copier migration configuration file script is as follows: The ClickHouse-Copier migration configuration script includes a ClickHouse SQL file module, a ClickHouse-Copier migration configuration template file, and a ClickHouse-Copier migration configuration file generation JAR package. First, a script tool is developed using the Java programming language to read the target SQL file and the ClickHouse-Copier migration configuration template file. The script tool reads the target SQL file and the ClickHouse-Copier migration configuration template file into memory. Then, the script tool extracts various parameters from the tables in the target SQL file and fills these parameters into the ClickHouse-Copier migration configuration template file. Once the filling is complete, the script... This tool generates a ClickHouse-Copier migration configuration file named after the ClickHouse data table. Then, the script tool uses a shell to traverse the SQL files of all tables under the SQL file path of the target ClickHouse data table, filtering out tables without the ENGINE engine using the grep command. A JAR package is run in the JVM virtual machine, passing the SQL file path of the ClickHouse data table and the ClickHouse-Copier migration configuration template file name as input parameters. This sequentially generates the ClickHouse-Copier migration configuration file named after the ClickHouse data table, and simultaneously generates the ClickHouse-Copier migration configuration file script.

3. The method for automating the Clickhouse data migration process according to claim 2, characterized in that, The specific implementation process of uploading the ClickHouse-Copier migration configuration file to ZooKeeper by executing the upload script in step S3 is as follows: The upload script uploads the ClickHouse-Copier migration configuration file using four methods, specifically including a first upload method, a second upload method, a third upload method, and a fourth upload method. The first upload method represents the creation or update of a single file: The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a single file creation or update process. If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create it first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory. If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command in ZooKeeper; The second upload method indicates the creation of multiple files: The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script then executes a process to create multiple files. First, check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory. If it exists, create the ClickHouse data table directory. Then, iterate through all migration configuration files in the migration configuration file directory and create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory. The third upload method refers to the creation or updating of multiple files in a partition: The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes multiple partition-based data migration creation or update processes. If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, iterate through all the configuration files in the partition migration configuration file directory, create the ClickHouse data table directory, and then use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory. If it is an update operation, the contents of the description file in the ClickHouse data table directory will be reset using the set command in ZooKeeper; The fourth upload method refers to the creation or updating of a single file in a partition: The upload script takes three parameters: the operation type, the ClickHouse database name, and the ClickHouse table name. The upload script executes a single partition-based data migration creation or update process. If it is a creation operation, first check if the ClickHouse database directory exists in ZooKeeper. If it does not exist, create the ClickHouse database directory first. If it exists, create the ClickHouse data table directory. Then, use ZooKeeper's cat command to read the ClickHouse-Copier migration configuration file corresponding to the data table, write it into a file with the description name, and save it in the ClickHouse data table directory. If it is an update operation, the content of the description file in the ClickHouse data table directory is reset using the set command of ZooKeeper.

4. The method for automating the Clickhouse data migration process according to claim 3, characterized in that, In step S4, the specific implementation process of migrating data from the source ClickHouse database tables to the destination ClickHouse database tables by executing a data migration script based on the ClickHouse-Copier migration configuration file in ZooKeeper is as follows: The data migration script supports three modes, namely, the first mode, the second mode, and the third mode. The first mode is used for full migration of a single table, and the specific implementation method is as follows: The data migration script takes two parameters: the Clickhouse database name and the Clickhouse table name. These two parameters are then concatenated to form the data migration configuration file path, which represents the path in ZooKeeper. The data migration process is then started to migrate data based on the data migration configuration file path. If the data migration is complete, clickhouse-client will query the total number of data in the migrated data table in both the source and destination ClickHouse databases. If the total number of data matches, the data migration will be successful. If the total number of data is inconsistent, a data migration failure message will be displayed. After investigating the cause of the data migration failure, the migration will be attempted again. The second mode is used for full migration of multiple tables, and the specific implementation method is as follows: The data migration script takes one Clickhouse database name as a parameter. Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables; The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log. The data migration configuration file path is constructed by concatenating the input ClickHouse database name and the ClickHouse table names traversed. The data migration configuration file path is used to represent the path in ZooKeeper. Then, the data migration process is started to begin migrating the data. If the data migration is complete, clickhouse-client will query the total number of data in the migrated data table in both the source and destination ClickHouse databases. If the total number of data matches, the data migration will be successful. If the total number of data is inconsistent, a data migration failure message will be displayed. After investigating the cause of the data migration failure, the migration will be attempted again. If the number of ClickHouse data tables traversed by the data migration script is equal to the preset total number of ClickHouse data tables, the process ends. The third mode is used for full migration of multiple tables by partition. The specific implementation method is as follows: The data migration script takes one Clickhouse database name as a parameter. Iterate through the migration configuration files of all ClickHouse tables in the copier-tasks directory under the data migration configuration file directory, and get the names of the ClickHouse tables; The data migration script iterates through the ClickHouse tables starting from 0, with a preset total number of ClickHouse tables. It prints information such as which table is being migrated and how many tables remain to be migrated to the data migration script's runtime log. Iterate through the migration configuration files of all ClickHouse tables in the increase_partition_copier_tasks directory under the data partition migration configuration file directory, get the name of the ClickHouse table, and print information such as which table is being migrated and how many tables are left to be migrated to the data migration script execution log. Read the partition difference list file, query the partition data of this table in the destination ClickHouse database through clickhouse-client. If the total number of data in the data table is greater than 0 and the number of partitions is equal to 1, then drop the partition data of this table in the destination ClickHouse database; if the total number of data in the data table is greater than 0 and the number of partitions is equal to 2, then find the second partition through the first partition and drop the second partition. After data is deleted from the data table partition of the destination ClickHouse database, the data migration configuration file path is constructed by concatenating the input ClickHouse database name and the ClickHouse data table names traversed. The data migration configuration file path is used to represent the path in ZooKeeper. Then, the data migration process is started to begin migrating the data. If the data migration is complete, clickhouse-client will query the total number of data in the migrated data table in both the source and destination ClickHouse databases. If the total number of data matches, the data migration will be successful. If the total number of data is inconsistent, a data migration failure message will be displayed. After investigating the cause of the data migration failure, the migration will be attempted again. If the number of ClickHouse tables traversed by the data migration script equals the preset total number of ClickHouse tables, the process ends.

5. A method for automating the Clickhouse data migration process according to claim 4, characterized in that, The specific implementation process for investigating the reasons for data migration failure is as follows: Collect historical migration failure data, including script input parameter error data, database account password error data, script log error data, and network error data; Natural language processing techniques are used to extract normalized features from the historical migration failure data to generate migration failure feature data. Part of the migration failure feature data is used for the training set and part is used for the validation set. A deep learning model for transfer failure errors is constructed, and the model is trained using training set data. Training stops when the training of the deep learning model for transfer failure errors meets a preset recognition rate. The input of the deep learning model for transfer failure errors is transfer failure feature data, and the output of the deep learning model for transfer failure errors is the transfer error type. The trained deep learning model for migration failure errors is used to analyze the reasons for data migration failure in real time and output the migration error type.

6. A method for automating the Clickhouse data migration process according to claim 5, characterized in that, The network error data includes errors caused by network connection bandwidth, latency, and packet loss.

7. A method for automating the Clickhouse data migration process according to claim 6, characterized in that, The feature extraction of the script log error data also includes: Spatial features of script log error data are extracted using a convolutional neural network (CNN), and the temporal order relationship of script log error data is captured by a long short-term memory (LSTM) network to generate migration failure feature data.

Images