A method for cross-cluster migration of ClickHosue

Abstract

This invention discloses a method for cross-cluster migration of ClickHosue databases, including: obtaining information on all source databases in the source cluster and returning a list of source databases to be migrated; connecting to the target cluster, looping through the list of source databases to be migrated, obtaining each source database to be migrated, and creating a corresponding target database in the target cluster; looping through the list of source databases to be migrated and obtaining the data table information of each source database to be migrated; based on the difference in the number of nodes in the source cluster and the target cluster, remotely copying the data of the obtained data tables to the target cluster; repeating the remote copy until all data tables of the source databases in the source cluster have been copied. This invention utilizes a lightweight development language combined with ClickHosue's own functions to achieve data synchronization between the two clusters. This invention also provides a solution to the problem of data imbalance when the number of nodes in the target cluster and the source cluster is inconsistent, facilitating the distribution of corresponding data across different nodes.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of computer, and particularly relates to a clickhosue cross-cluster migration method. BACKGROUND

[0002] In the field of big data, clickhosue is a commonly used OALP computing engine. In the development process of a big data platform, the demand for clickhosue cluster migration may be encountered. At present, there is a lack of a systematic migration method in the field of operation and maintenance. For a server cluster of hundreds of days and thousands of servers, the migration technology is very difficult, which has become a technical problem to be solved in the technical field.

[0003] Therefore, the present application provides a clickhosue cross-cluster migration method to at least solve part of the above technical problems. SUMMARY

[0004] The present application solves the technical problem of providing a clickhosue cross-cluster migration method to at least solve part of the above technical problems.

[0005] To achieve the above purpose, the technical solution adopted by the present application is as follows:

[0006] A clickhosue cross-cluster migration method, comprising the following steps:

[0007] Step 1, obtaining all source database information of a source cluster, excluding internal system libraries and returning to a list of source databases to be migrated;

[0008] Step 2, connecting a target cluster, and cycling the list of source databases to be migrated to obtain each source database to be migrated, judging whether the source database exists in the target cluster, and if not, creating a corresponding target database in the target cluster;

[0009] Step 3, cycling the list of source databases to be migrated to obtain data table information of each source database to be migrated;

[0010] Step 4, based on the difference in the number of source cluster nodes and target cluster nodes, remotely copying the data of the data table obtained in step 3 to the target cluster;

[0011] Step 5, repeating step 4 until the data tables of all source databases of the source cluster are copied.

[0012] Further, the step 4 comprises: step 41, judging whether the target database of the target cluster has the same data table as the data table of the source database to be migrated; if yes, deleting the data table of the target cluster, and if not, creating a distributed table corresponding to the data table of the source database in the target database; step 42, judging whether the data table of the source database to be migrated is a distributed table; if yes, extracting the name of the local table corresponding to the distributed table, creating a data table corresponding to the local table in one node of the target cluster, and then automatically creating the data tables of the remaining nodes of the target cluster based on the distributed table created in step 41; if not, extracting the name of the local table, and creating a corresponding data table in each node of the target cluster; step 43, using the name of the distributed table corresponding to the local table or the local table, and then assembling the sql statement for remote calling to remotely copy the local table data to the data table of the corresponding node.

[0013] Further, in the step 43, if the number of nodes of the source cluster is the same as that of the target cluster, the local table data is copied to the corresponding node; if the number of nodes of the source cluster is different from that of the target cluster, the local table data is partitioned, and the partitioned data is remotely copied to the corresponding node.

[0014] Further, in the step 43, the partitioning copy logic for the nodes with different numbers comprises: calculating the remainder of the value of the primary key field of the local table divided by the number of nodes of the target cluster, and transmitting the partitioned data of the local table to the data table of the node equal to the remainder.

[0015] Further, in the step 43, the data is mod-partitioned by the primary key of the data table.

[0016] Further, in the step 41, the jdbc interface provided by clickhouse is called to create the distributed table corresponding to the data table in the target cluster.

[0017] Further, in the step 42, the data table of the source database to be migrated is a distributed table, the engine information of the distributed table is obtained, and the name of the local table corresponding to the distributed table is extracted through the field content of the returned engine information.

[0018] Further, in the step 1, the database viewing function of the clickhosue field is used to exclude internal system libraries and return to the list of the source database to be migrated.

[0019] Further, in the step 2, the jdbc interface provided by clickhouse is used to create the target database corresponding to the source database to be migrated in the target cluster.

[0020] Compared with the prior art, the present application has the following beneficial effects:

[0021] In the data migration mode of the traditional clickhosue, data is generally read from the source end by using the spark technology, and then written in the target end, which depends on the external big data cluster. The application only needs to use the light python development language combined with the sql function of clickhosue itself, so as to realize the data synchronization of two clusters. And the application also provides a solution to automatically balance the data when the number of target clusters and source clusters is inconsistent, so that the corresponding data can be scattered to different nodes. BRIEF DESCRIPTION OF DRAWINGS

[0022] Figure 1 The method flowchart of the application. DETAILED DESCRIPTION

[0023] In order to make the purpose, technical scheme and advantages of the application more clear and obvious, the following will combine the attached drawings to further describe the application. Figure 1 The application is further described. Obviously, the described embodiments are only some of the embodiments of the application, not all. Based on the embodiments in the application, all other embodiments obtained by those skilled in the art without creative labor are within the protection scope of the application.

[0024] The application provides a clickhosue cross-cluster migration method, comprising the following steps:

[0025] Step 1, obtaining all source database information of the source cluster, excluding internal system library and returning to the list of source databases to be migrated;

[0026] Step 2, connecting the target cluster, cycling the list of source databases to be migrated, obtaining each source database to be migrated, judging whether the source database exists in the target cluster, if not, creating a corresponding target database in the target cluster;

[0027]

[0027] Step 3, cycling the list of source databases to be migrated, obtaining the data table information of each source database to be migrated;

[0028] Step 4, based on the number difference between the source cluster node and the target cluster node, remotely copying the data of the data table obtained in step 3 to the target cluster;

[0029] Step 5, repeating step 4 until the data table of all source databases of the source cluster is copied.

[0030] The step 1 is to obtain all source data information of the source cluster, preferably using the database view function (show database) of the clickhosue field to exclude the internal databases initialized by the system, information_schema, system, default database, etc., and then return to the list of source databases to be migrated.

[0031] The step 2 is to connect the target cluster, and preferably use the jdbc interface provided by clickhouse to create the target database corresponding to the source database to be migrated in the target cluster, so as to facilitate subsequent data migration.

[0032] The step 3 is to obtain the data table information of each source database to be migrated, by looping the list of source databases to be migrated, switching each source database, executing the function (use database show tables) of the clickhosue field to display all tables in the database, and returning the data table information under the current source database.

[0033] The step 4 is based on the processing of the data table of the source database to migrate the data.

[0034] The step 4 includes:

[0035] Step 41, judge whether there is a data table identical to the data table of the source database to be migrated in the target database of the target cluster: if yes, delete the data table of the target cluster, and the delete operation is also executed by the distributed delete syntax provided by clickhouse; if not, call the jdbc interface provided by clickhouse to create a corresponding distributed table in the target cluster, and the statement for creating the table uses the distributed table creation statement of clickhosue;

[0036] Step 42, judge whether the data table of the source database to be migrated is a distributed table, and the judgment logic of the distributed table is to execute the statement SELECT engine FROM system.tables WHERE database = '{db}' AND name = '{tb}' in the source clickhosue, wherein system.tables is a table for managing data table metadata in the source clickhosue cluster, if the value of the engine_full field returned by the above statement is Distributed table, then the table is a distributed table, otherwise it is a local table of a non-distributed table;

[0037] If it is a distributed table, a select engine_full from system.tables where name='distributed_name' statement is executed, the name of the local table corresponding to the distributed table is extracted through the content of the returned engine_full field, and then a data table corresponding to the local table is created on one node of the target cluster, and then based on the distributed table newly created in step 41, data tables of the remaining nodes of the target cluster are automatically created; if it is a non-distributed local table, the name of the local table is directly extracted, and corresponding data tables are created on each node of the target cluster;

[0038] Step 43, using the name of the distributed table corresponding local table or non-distributed local table, then assembling the sql statement for remote call, remotely copying the local table data to the data table of the corresponding node.

[0039] The number of source cluster nodes is the same as that of target cluster nodes, and the name of the non-distributed local table or the name of the non-distributed local table is used to assemble the sql statement for remote call, and the data is remotely copied to the data table of the corresponding node;

[0040] The sql statement is as follows: insert into lw.dwd_lw_attendance_detail SELECT * FROM remote ('{remote_addr}:9000', {local_db}, {local_tb}, '{remote_user}', '{remote_pass}')', which is to migrate the data of the data table local_tb to the data table lw.dwd_lw_attendance_detail of the corresponding node in the target cluster. Wherein lw.dwd_lw_attendance_detail is a data table created in the target cluster, remote_addr is the server address of the data to be migrated, local_db is the database name of the data to be migrated, local_tb is the data table name of the data to be migrated, and emote_user and remote_pass are the username and password of the data server to be migrated respectively.

[0041] If the number of source cluster nodes is different from that of target cluster nodes, the local table data is partitioned, the name of the non-distributed local table or the name of the non-distributed local table is used to assemble the sql statement for remote call, and the partitioned data is remotely copied to the data table of the corresponding node;

[0042] The sql statement is: insert into lw.dwd_lw_attendance_detail SELECT * FROM remote ('{remote_addr}:9000', {local_db}, {local_tb}, '{remote_user}', '{remote_pass}') where primaryKey % 8=1, which is to migrate the data of the data table local_tb of the data to be migrated to the data table lw.dwd_lw_attendance_detail of the node 1 in the target cluster. Most of the parameters are explained as above, and where primaryKey % 8=1 indicates that the value (for example, 12345) of the primary key field primaryKey in the local table is divided by the number of nodes (for example, 8) of the target cluster, and the remainder is 1, and the current data is transmitted to the node 1 of the target cluster, in particular, the node number of the target cluster starts from 0. And here the data is mod partitioned by the primary key of the data table.

[0043] Suppose the source cluster has only 6 nodes, but the target cluster has 8 nodes, and the data of the 6 nodes needs to be distributed to the 8 nodes. The data of the source nodes can be redistributed by mod partitioning, and the partitioning mode is through the unique primary key id % target cluster node quantity, "%" is a mathematical symbol for mod in the mathematical field, for example, 5 % 2 = 1 indicates that the remainder of 5 divided by 2 is 1. The data partitioning by mod partitioning can scatter the data of the distributed table during data migration, and scatter the corresponding data to different nodes to make the data balanced.

[0044] Finally, it should be noted that: the above embodiments are only the preferred embodiments of the present application for explaining the technical solutions of the present application, but not limiting them, and of course, not limiting the patent scope of the present application; although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that: it can still modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement for part or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present application; that is, any modification or polishing without substantial significance in the main design idea and spirit of the present application, and the technical problems solved are still consistent with the present application, and should be included in the protection scope of the present application; in addition, the technical solutions of the present application are directly or indirectly applied to other related technical fields, which are also included in the patent protection scope of the present application.

Claims

1. A method for clickhouse cross-cluster migration, characterized in that, The method comprises the following steps: Step 1, obtaining all source database information of a source cluster, excluding internal system libraries and returning a list of source databases to be migrated; Step 2, connecting a target cluster, and cycling through the list of source databases to be migrated to obtain each source database to be migrated, and judging whether the source database exists in the target cluster, if not, creating a corresponding target database in the target cluster; Step 3, cycling through the list of source databases to be migrated to obtain the data table information of each source database to be migrated; In step 3, cycling through the list of source databases to be migrated, switching each source database, executing the switch database display all tables function of the clickhosue field, and returning the data table information under the current source database; Step 4, based on the difference between the number of source cluster nodes and the number of target cluster nodes, remotely copying the data table data obtained in step 3 to the target cluster; Step 5, repeating step 4 until all source database data tables of the source cluster are copied; Step 4 comprises: step 41, judging whether there is a data table identical to the data table of the source database to be migrated in the target database of the target cluster: if there is, deleting the data table of the target cluster, if there is not, creating a distributed table corresponding to the data table of the source database in the target database; step 42, judging whether the data table of the source database to be migrated is a distributed table: if it is a distributed table, extracting the name of the local table corresponding to the distributed table, creating a data table corresponding to the local table in one node of the target cluster, and then based on the distributed table newly created in step 41, automatically creating data tables of the remaining nodes of the target cluster; if it is a non-distributed local table, extracting the name of the local table, and creating a corresponding data table in each node of the target cluster; step 43, using the name of the distributed table corresponding local table or the non-distributed local table, and then assembling the sql statement for remote calling, and remotely copying the local table data to the data table of the corresponding node; In step 42, the data table of the source database to be migrated is a distributed table, the engine information of the distributed table is obtained, and the name of the local table corresponding to the distributed table is extracted through the field content of the returned engine information; In step 43, if the number of source cluster nodes is the same as the number of target cluster nodes, the local table data is copied to the corresponding node; if the number of source cluster nodes is different from the number of target cluster nodes, the local table data is partitioned, and the partitioned data is remotely copied to the corresponding node; The partitioning copying logic of different node numbers is: calculating the remainder of the value of the primary key field of the local table divided by the number of target cluster nodes, and transmitting the partitioned data of the local table to the data table of the node equal to the remainder.

2. The method of claim 1, wherein, In step 43, the data is mod partitioned through the primary key of the data table.

3. The method of claim 1, wherein, In step 41, the jdbc interface provided by clickhouse is called to create a distributed table corresponding to the data table in the target cluster.

4. The method of claim 1, wherein, In step 1, the database viewing function of the clickhosue field is used to exclude internal system libraries and return a list of source databases to be migrated.

5. The method of claim 1, wherein, In the step 2, the jdbc interface provided by clickhouse is used to create the target database corresponding to the source database to be migrated in the target cluster.

Images