A method and related device for bulk import of a CSV file

Abstract

The application discloses a batch import method of CSV files and related equipment, and the method comprises the following steps: obtaining file information of a plurality of CSV files to be imported; determining a target node corresponding to each CSV file according to the file information; grouping the CSV files according to the file information to obtain a plurality of plan lists; according to a preset traversal termination rule, the CSV files in each plan list are circularly traversed, and when one CSV file is traversed, the CSV file is sent to the corresponding target node until the traversal is completed, and a target table corresponding to the CSV file is obtained. The application can greatly improve the rate of batch importing CSV files into a database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, in particular to a batch import method of CSV file and related equipment. BACKGROUND

[0002] When a new project of layout part is applied on a database, the data set to be analyzed is distributed on different nodes, and the nodes where the data set to be analyzed is located may have problems such as old, heterogeneous, unpopular, etc., and the data volume of the data to be analyzed may be large. Therefore, in order to facilitate the acquisition of the data set to be analyzed, the manager generally adopts the way of batch exporting CSV file. On the contrary, when the project is implemented, the batch exported CSV file needs to be imported into the database.

[0003] On a high-performance physical server cluster, such as an MPP type database, MPP is to distribute tasks in parallel to multiple servers and nodes, and after the calculation is completed on each node, the results of each part are summarized together to get the final result. When importing the CSV file into the database, it is necessary to first read and parse the CSV data file, then assemble and insert the CSV data file into the SQL statement, and finally send it to the database of the project implementation. The reading and insertion of the CSV file on such a server cluster is serial, so the utilization rate of hardware resources such as disk IO, multi-core CPU, network, etc. is low, and when the data volume is large, the assembly and insertion of the SQL statement in the early stage and the parsing of the SQL statement all need to consume a long time, so the way of batch inserting CSV is low in efficiency. SUMMARY

[0004] The technical problem to be solved by the present application is that the current way of batch inserting CSV file is low in efficiency, and in view of the shortcomings of the prior art, a batch import method of CSV file and related equipment are provided.

[0005] In order to solve the above technical problems, the technical solutions adopted by the present application are as follows:

[0006] A batch import method of CSV file, the method comprises:

[0007] Obtaining file information of a plurality of CSV files to be imported;

[0008] According to the file information, determining the target node corresponding to each of the CSV files; and,

[0009] According to the file information, grouping the CSV files to obtain a plurality of plan lists;

[0010] According to a preset traversal termination rule, for each of the plan list, the CSV files in the plan list are circularly traversed, and when one of the CSV files is traversed, the CSV file is sent to a corresponding target node until the traversal ends, and a target table corresponding to the CSV file is obtained.

[0011] The batch import method of the CSV files, wherein the file information comprises a file size of each of the CSV files, and the determining of the target node corresponding to each of the CSV files according to the file information comprises:

[0012] According to the file size, the CSV files are sorted to obtain a file sequence list;

[0013] According to the file sequence list, the target node corresponding to each of the CSV files is determined in turn in a descending order.

[0014] The batch import method of the CSV files, wherein the file information comprises a disk space of each node, and the determining of the target node corresponding to each of the CSV files according to the file information comprises:

[0015] For each of the CSV files, the candidate nodes in the nodes are determined according to the disk space;

[0016] For each of the candidate nodes, a residual space corresponding to the candidate node is calculated according to the disk space of the candidate node and the size of the CSV file corresponding to the candidate node;

[0017] The target node corresponding to the CSV file is determined according to the residual space, and the disk space corresponding to the target node is updated.

[0018] The batch import method of the CSV files, wherein the sending of the CSV file to the target node when one of the CSV files is traversed comprises:

[0019] When one of the CSV files is traversed, a sending resource corresponding to the CSV file is acquired based on a preset resource pool, wherein the resource pool comprises a sending resource corresponding to each of the target nodes;

[0020] When the acquisition is successful, the CSV file is sent to the target node based on the sending resource;

[0021] When a sending success instruction for the CSV file is detected, the sending resource is returned to the resource pool.

[0022] The batch import method of the CSV file, wherein the sending the CSV file to the corresponding target node until the end of the traversal is performed when the CSV file is traversed, and the target table corresponding to the CSV file is obtained, including:

[0023] A plurality of temporary tables are set for each target node;

[0024] The CSV file is imported into a preset initial table in a loop until a sending termination instruction is detected, and the target table is obtained, wherein the importing the CSV file into the preset initial table in the loop includes:

[0025] The CSV file is sequentially imported into the temporary table until the number of CSV files in the temporary table is equal to a preset data threshold, wherein each target node is provided with a plurality of temporary tables;

[0026] The CSV file in the temporary table is imported into the initial table.

[0027] The batch import method of the CSV file, wherein when the sending termination instruction is detected, for each target node, a target table corresponding to the target node is generated according to a loading queue corresponding to the target node, and the method further includes:

[0028] A redistribution table in the target table is determined according to a preset redistribution rule;

[0029] The target node corresponding to the redistribution table is expanded to obtain an expanded node, and a transfer table corresponding to the expanded node is created;

[0030] The data in the redistribution table is redistributed to obtain redistributed data, and the redistributed data is imported into the transfer table to obtain an update table corresponding to each expanded node.

[0031] The batch import method of the CSV file, wherein the determining the redistribution table in the target table according to the preset redistribution rule includes:

[0032] When the target table is a non-hash table, the redistribution table in all target tables is determined according to a preset determination rule and the data quantity of the target table;

[0033] When the target table is a hash table, all target tables are determined as redistribution tables.

[0034] The batch import method of the CSV file, wherein the determining the redistribution table in all target tables according to the preset determination rule and the data quantity of the target table includes:

[0035] According to the data amount of all the target tables, an average data amount is calculated;

[0036] A product of a preset multiple and the average data amount is calculated to obtain a data amount threshold corresponding to the target table;

[0037] For each target table, when the data amount of the target table is greater than the data amount threshold, the target table is determined as a redistribution table.

[0038] A computer readable storage medium stores one or more programs, which can be executed by one or more processors to implement the steps in the CSV file bulk import method.

[0039] A terminal device includes a processor, a memory and a communication bus; the memory stores a computer readable program which can be executed by the processor;

[0040] The communication bus realizes the connection communication between the processor and the memory;

[0041] The processor realizes the steps in the CSV file bulk import method when executing the computer readable program.

[0042] Advantages: the CSV file bulk import method and related device provided by the application, in the method, first, the CSV file to be imported is acquired, then according to the file information of the CSV file, the target node to which the CSV file is to be sent subsequently is determined, at the same time, the CSV file is grouped to obtain a plurality of plan lists. Afterwards, the CSV file in each plan list is distributed in units of plan list, and each distribution is recorded in a loading queue until the distribution is completed. In terms of the target node, when the distribution of the content of the plan list is completed, the loading queue corresponding to the target node is merged to generate a target table. In the method, the CSV file is split into a plurality of plan lists for distribution, and a plurality of CSV files can be sent to the target node at the same time, so that the distribution efficiency is improved. BRIEF DESCRIPTION OF DRAWINGS

[0043] Figure 1 The flowchart of the CSV file bulk import method provided by the application.

[0044] Figure 2 The overall flowchart of the CSV file bulk import method provided by the application.

[0045] Figure 3 The flowchart of the CSV file import by the target node in the CSV file bulk import method provided by the application.

[0046] Figure 4 The flow chart of importing the CSV file by the target node in the batch import method of the CSV file provided by the present application.

[0047] Figure 5 The schematic diagram of redistribution in the batch import method of the CSV file provided by the present application.

[0048] Figure 6 The processing schematic diagram of the master and slave nodes in the redistribution in the batch import method of the CSV file provided by the present application.

[0049] Figure 7 The structure schematic diagram of the terminal device provided by the present application. DETAILED DESCRIPTION

[0050] The present application provides a batch import method of CSV file, in order to make the purpose, technical scheme and effect of the present application more clear and definite, the present application is further explained in detail below with reference to the drawings and examples. It should be understood that the specific examples described herein are only used to explain the present application and are not used to limit the present application.

[0051] Those skilled in the art can understand that, unless specifically stated otherwise, the singular form "a", "an", "said" and "the" used herein can also include the plural form. It should be further understood that the phrase "comprising" used in the specification of the present application means that the features, integers, steps, operations, elements and / or components exist, but does not exclude the existence or addition of one or more other features, integers, steps, operations, elements, components and / or their groups. It should be understood that when we say that an element is "connected" or "coupled" to another element, it can be directly connected or coupled to other elements, or there can be intermediate elements. In addition, the "connection" or "coupling" used herein can include wireless connection or wireless coupling. The phrase "and / or" used herein includes all or any single unit and all combinations of the associated listed items.

[0052] Those skilled in the art can understand that, unless otherwise defined, all terms (including technical terms and scientific terms) used herein have the same meaning as that generally understood by those skilled in the art to which the present application belongs. It should also be understood that terms such as those defined in general dictionaries should be understood as having meanings consistent with those in the prior art, and unless specifically defined as here, should not be interpreted with idealized or overly formal meanings.

[0053] As Figure 1 and Figure 2As shown, the embodiment provides a batch import method of CSV file, for the convenience of description, the common server is taken as the execution main body to describe, the server here can be replaced by the tablet, the computer and other devices with data processing function, the batch import method of CSV file includes the following steps:

[0054] S10, acquiring file information of a plurality of CSV files to be imported.

[0055] Specifically, on the physical server cluster, usually a plurality of servers or nodes are distributed with data and tasks, and the final result is obtained after the calculation structure of each node is summarized.

[0056] Firstly, the file information of the CSV file to be imported is acquired, and the file information refers to the size, original storage path, such as source host and source path, and other information related to the attributes of the CSV file.

[0057] S20a, determining the target node corresponding to each of the CSV files according to the file information.

[0058] Specifically, after the file information is obtained, the target node corresponding to the CSV file can be determined.

[0059] In one implementation mode, the target node corresponding to the CSV file is determined in a random allocation manner. For example, the remaining space of the target node is 100 M, and therefore the CSV file to be imported with a total size less than 100 M is selected. This determination mode is very random, and it is easy to cause uneven distribution of data among the target nodes.

[0060] In the second implementation mode, the larger CSV file is planned first, and then the smaller CSV file is planned, so as to make the data among the target nodes evenly distributed. Firstly, the CSV files are sorted according to the file size, and a file sequence list is obtained. In the file sequence list, the CSV files can be arranged in descending order or ascending order. Then, according to the file sequence list, the target node corresponding to each CSV file is determined in descending order. Through this planning in descending order, the uniformity of data distribution can be improved.

[0061] However, the disk space of the target node is not the same, and after the corresponding CSV file is arranged, the actual space available for storage is limited, and therefore, in the third implementation mode, the target node corresponding to each CSV file is determined in the following manner:

[0062] A10, for each of the CSV files, determining a candidate node in the node according to the disk space.

[0063] Specifically, firstly, based on the disk space of each node, candidate nodes are determined. Candidate nodes are those whose planned capacity can meet the storage requirements of CSV files.

[0064] It's important to note that the disk space here includes not only the node's current initial space value but also the theoretical consumption value calculated according to the plan. Before the target node corresponding to the CSV file is determined, the theoretical consumption value is 0.

[0065] For example, if a database contains nodes A and B, before the target node is determined, node A has an initial space of 40 MB and a theoretical space of 0 MB, while node B has an initial space of 50 MB and a theoretical consumption of 0 MB. After the target nodes for the two CSV files are determined, the initial space values ​​of nodes A and B remain unchanged, but the theoretical consumption of node A remains 0 MB, and the theoretical consumption of node B remains 20 MB. This means that when the CSV file is subsequently allocated, before the CSV file is distributed, the first two CSV files will occupy 20 MB of node B, leaving only 30 MB of theoretically remaining space available for the CSV file (50 MB - 20 MB = 30 MB).

[0066] Therefore, based on the disk space of each node, the node that can store the CSV file is selected as the candidate node.

[0067] A20. For each candidate node, calculate the remaining space corresponding to the candidate node based on the disk space of the candidate node and the size of the CSV file corresponding to the candidate node.

[0068] Specifically, for example, if the size of the CSV file is 1M, and both node A and node B are candidate nodes, the remaining space corresponding to node A is 39M (40 – 1=39M), and the remaining space corresponding to node B is 29M (30 – 1=29M).

[0069] A30. Based on the remaining space, determine the target node corresponding to the CSV file, and update the disk space corresponding to the target node.

[0070] Specifically, based on the remaining space, the node with the largest remaining space is selected as the target node for the CSV file. Simultaneously, the disk space corresponding to this target node also needs to be updated; for example, the theoretical disk space consumption for node A is updated to 1 MB.

[0071] Further, in the process of determining the candidate nodes, the nodes with theoretical residual spaces greater than the CSV file can be directly selected as the candidate nodes. However, if the CSV file is too small, the number of candidate nodes is large, and the number of residual spaces to be calculated is large, which prolongs the calculation time. Therefore, in the embodiment, a space multiple value is preset, and the space multiple value refers to a multiple of the theoretical residual space greater than the CSV file. For example, the preset space multiple value is 2, and the CSV file is 4 M. The nodes with theoretical residual spaces greater than 8 M (2 x 4 = 8 M) are selected as the candidate nodes.

[0072] In addition, in order to improve the processing efficiency, the master node and the slave node are arranged in the server. If the CSV file needs to be imported into the master-slave node, for example, the duplicate parameter value is MASTER_SLAVE, the theoretical residual spaces of the master node and the slave node are compared with the size of the CSV file to ensure that the master node and the slave node can be imported in the subsequent process.

[0073] Further, the above-mentioned manners can be combined to improve the effect of determining the target node. For example, the implementation mode two and the implementation mode three are combined. First, the CSV file is sorted according to the file size to obtain a file sequence table. According to the file sequence table, the CSV files are selected in descending order. For the selected CSV file, the candidate nodes in the node are determined according to the disk space, and for each candidate node, the residual space corresponding to the candidate node is calculated according to the disk space of the candidate node and the size of the CSV file corresponding to the candidate node. Finally, the target node corresponding to the CSV file is determined according to the residual space, and the disk space corresponding to the target node is updated.

[0074] It is worth noting that if the file encoding of the CSV file is utf8-bom, the start sending offset of the file import plan is recorded as 3, otherwise it is recorded as 0. The fast import of the data node does not support the CSV file with encoding of utf8-bom, and the difference between two files with the same content and different encodings is only that the utf8-bom encoding has three fixed bytes as a label compared with the utf8 encoding. The present scheme can solve this problem by sending the file from the fourth byte when sending the file with utf8-bom encoding.

[0075] S20b, grouping the CSV files according to the file information to obtain a plurality of plan lists.

[0076] Specifically, in order to be able to send multiple CSV files in parallel, after obtaining the file information, the CSV files are also grouped, and each group is taken as a plan list. The plan list is the basis for subsequent CSV file distribution, and each plan list contains several CSV files.

[0077] Further, the grouping can be performed according to various parameters, such as the size of the CSV file, the storage path of the CSV file. If splitting is performed according to the storage path of the CSV file, the address accessed by the sending of the plan list is uniform during subsequent sending, which can improve the sending efficiency, and therefore in the embodiment, it is preferred to group the CSV files according to the storage path of the CSV file, such as the source host, the source path, the combination of the source host and the source path, and the like.

[0078] S30, according to a preset traversal termination rule, for each of the plan lists, the CSV files in the plan list are traversed in a loop, and when a CSV file is traversed, the CSV file is sent to the corresponding target node until the traversal ends, and a target table corresponding to the CSV file is obtained.

[0079] Specifically, a traversal termination rule is set in advance, which is used to limit when the subsequent traversal is terminated. Since the present scheme is to import all the CSV files into the database, the traversal termination rule can be set to include stopping the traversal when all the CSV files in the plan list are sent. In addition, the traversal can also be terminated when an error occurs in the file sending link during the traversal.

[0080] After obtaining the plan list, the traversal is performed in a loop with any CSV file in the plan list as the starting point, and generally the first row of the CSV file in the table in the list is taken as the starting point. When each CSV file is traversed, since the target node corresponding to the CSV file has been determined in advance, the CSV file can be sent to the target node. As shown in Figure 3 In order to better implement the loop traversal for each plan list, a sending thread can be set for each plan list, which is dedicated to traversing, sending, and importing the CSV file.

[0081] After importing all the CSV files in the plan list into the corresponding target node, the target node can generate a target table corresponding to the batch-imported CSV file according to the imported target node.

[0082] Further, since the sending of the CSV files is performed simultaneously with multiple plan lists, and there are multiple plan lists to be imported, it is possible that some target nodes need to receive CSV files from multiple different plan lists at the same time, and some target nodes may not receive the CSV files at the moment. Therefore, in order to avoid this situation, a resource pool is set in advance before distribution, which is used to send the CSV files to the target nodes and needs to call the sending resources such as CPU. The sending method based on the resource pool includes the following steps:

[0083] B10, when a CSV file is traversed, the sending resource corresponding to the CSV file is obtained based on the preset resource pool.

[0084] Specifically, when a CSV file is traversed, since the target node corresponding to the CSV file has been determined in advance, the sending resource for sending the CSV file to the target node can be obtained from the resource pool. Multiple sending resources can be set in the resource pool, for example, for CSV file 1, the target node corresponding to the CSV file is node A, and the number of sending resources corresponding to node A is set to 2, so that at most two CSV files are sent to node A at the same time.

[0085] B20, when the acquisition is successful, the CSV file is sent to the target node based on the sending resource.

[0086] Specifically, when the acquisition is successful, it means that the number of CSV files currently sent to node A is 0 or 1, and node A is not congested yet. Therefore, the CSV file is sent to the target node based on the sending resource.

[0087] Further, if the acquisition fails, it means that the number of CSV files currently sent to node A is 2, which has reached the upper limit of the number of files sent to node A at the same time. Therefore, the sending of the CSV file is stopped. At this time, taking the sending thread as an example, a first waiting threshold value, for example, 10 ms, can be set in advance, that is, after 10 ms, the sending thread will try to acquire the sending resource corresponding to the target node in the resource pool. In addition, the sending thread can also choose not to send CSV file 1, but to select the next CSV file in the plan list with CSV file 1, and try to acquire the sending resource corresponding to the next CSV file in the resource pool. If the next CSV file, for example, CSV file 2, acquires the sending resource successfully, then CSV file 2 is sent, and the next CSV file of CSV file 2, for example, CSV file 3, is tried to acquire, until the traversal of the entire plan list is completed, and returns to CSV file 1.

[0088] B30, when a sending success instruction for the CSV file is detected, the sending resource is returned to the resource pool.

[0089] Specifically, after the sending thread sends the CSV file, if the CSV file is successfully sent to the target node, a sending success instruction for the CSV file is generated to let the sending thread send the next CSV file. When the sending success instruction is detected, since the target nodes corresponding to the two CSV files before and after may be different, the target node corresponding to the previous CSV file is idle, and therefore the sending thread also needs to return the sending resource to the resource pool to facilitate the use of the sending thread corresponding to the other plan list.

[0090] In addition, in order to avoid duplication, the sending thread can delete the CSV file from the plan list at this time. If the CSV file fails to be sent, for example, the sending success instruction is not detected within the preset detection time, the sending thread can choose to resend, or send the next CSV file of the CSV file first, and retry in the subsequent loop traversal.

[0091] Through the above method of setting the resource pool, the CSV files processed by different target nodes at the same time are relatively uniform, and there is no overcrowded target node, nor is there a target node that does not need to receive a CSV file at the moment, which improves the efficiency of resource allocation and the speed of batch import.

[0092] On the other hand, since there are multiple plan lists, and for a certain target node, multiple CSV files may be imported at the same time, which is prone to import errors. Therefore, in order to reduce errors during import and ensure data accuracy, in the embodiment, the process of sending the CSV file to the target node includes: cyclically importing the CSV file into a preset initial table until a sending termination instruction is detected, and obtaining a target table. The cyclically importing the CSV file into the preset initial table includes:

[0093] C10, sequentially importing the CSV file into the temporary table until the number of CSV files in the temporary table is equal to a preset data threshold.

[0094] Specifically, in order to facilitate the import of the CSV file into the temporary table, a loading queue is generally provided to sequentially load the CSV file to be imported into the loading queue. Therefore, the embodiment adopts the loading queue as the basis for importing the temporary table. The target node receives each CSV file, and the CSV file is inserted into the loading queue. As shown in Figure 4 A merging thread can also be provided to facilitate task execution.

[0095] For each temporary table, the CSV file in the loading queue is inserted into the temporary table first. Since the loading queue inserts data in chronological order, the first piece of information is obtained from the loading queue corresponding to the target node. If the acquisition is successful, data import is performed, the CSV file in the loading queue is imported into the temporary table, and the data in the loading queue is deleted to avoid repeated import.

[0096] A quantity threshold is set in advance, which is used to limit the number of bits and data in the temporary table to avoid accumulation of too many CSV files in the temporary table.

[0097] C20, importing the CSV file in the temporary table into the preset initial table.

[0098] Specifically, as shown in Figure 5 When the number of CSV files in the temporary table reaches the quantity threshold, the CSV files in the temporary table are imported into the preset initial table. The preset initial table is an empty table, and as the CSV files in the temporary table are continuously imported, the number of CSV files in the initial table is gradually increased until all the CSV files in the loading queue are imported into the initial table to obtain the target table.

[0099] After the data in the temporary table is imported into the initial table, the temporary table can be deleted, a new temporary table is created, and the CSV files in the loading queue are continuously imported, or the data in the temporary table is deleted, and all the CSV files in the loading queue are directly imported into the temporary table.

[0100] Further, when the data in the temporary table is imported into the initial table, too many temporary tables are imported at the same time, which can also cause storage errors, so in this embodiment, a merging rule is set in advance before the CSV files in the temporary table are imported into the initial table. The merging rule can specify whether the temporary table can import the CSV file into the initial table at this time. For example, in the merging rule, it is specified that only one CSV file of one temporary table can be imported at a time, the number of temporary tables waiting to be imported is greater than a preset first waiting threshold, and the CSV file is continuously imported into the temporary table, and the like. The following is an example.

[0101] When the merging rule specifies that only one temporary table is imported at a time, it is detected whether the target node is in the import state. If it is in the import state, it means that a temporary table is importing data into the initial table. At this time, the temporary table can be selected to wait for the previous temporary table to import, or the CSV file can be continuously imported into the temporary table.

[0102] The first waiting threshold refers to the number of temporary tables waiting to import the initial table. If the number of temporary tables waiting to import the initial table is too large, continuing to wait will reduce the overall import efficiency. Therefore, the first waiting threshold can be set in the merging rule. When the number of temporary tables waiting to import is greater than the first waiting threshold, the CSV file is continued to be imported into the subsequent temporary table that reaches the number threshold. The first waiting threshold can be half of the total number of set temporary tables.

[0103] In addition, if there are multiple temporary tables waiting to import the CSV file in the loading queue, a part of the temporary tables waiting can be selected for CSV file import. For example, a second waiting threshold is set in advance, which is used to limit the number of temporary tables waiting to import the CSV file in the loading queue. For example, the second waiting threshold is 2. When there are two temporary tables in the loading queue, if there is a CSV file in the third temporary table, the CSV file in the third temporary table is preferably imported into the initial table; if there is no CSV file in the third temporary table, it is likely that the number of set temporary tables is too large, and the third temporary table can be selected to be deleted.

[0104] Further, after generating the target table, since the target node corresponding to the CSV file is determined by the size of the CSV file, the number distribution can not be uniform, for example, the number of CSV files in a target node is much larger than that in another target node.

[0105] Therefore, in order to balance the number of CSV files in each target node, after all CSV files are imported, the CSV files in the target node need to be redistributed. A redistribution rule is set in advance, which is used to determine the tables in the target table that need to be redistributed, i.e. the redistribution table.

[0106] At the same time, the target node of the redistribution table is expanded to obtain an expanded node. A transfer table corresponding to the expanded node is created. The transfer table can be created by "creat like". Finally, the data in the redistribution table is redistributed to obtain a plurality of redistribution data, and the redistribution data is imported into the transfer table to obtain an update table corresponding to each expanded node. In the data redistribution process, redis can be used to construct a command instruction for redistribution.

[0107] In addition, if the target table is a partition table, since the supported statements of the partition table and the non-partition table are different, different options can be added in the redis statement according to whether the target table is a partition table, so as to obtain a command statement for redistribution. Taking the Tianyunxing database as an example, if the target table is a partition table, the use rdc option can be added in the redis statement; if not, the use file option is added.

[0108] In addition, if the target node-duplicate value of the redistribution table is MASTER_SLAVE, the master and slave nodes need to be processed during redistribution, and the backup properties of the master or slave node can affect the redistribution. Therefore, in the embodiment, the following method can be used to realize the redistribution of the master and slave nodes.

[0109] As shown in Figure 6 The slave node is first set as the execution object, for example, "set slave", and then "stopbackup" is used to prevent the subsequent operation from backing up between the master and slave nodes. Before the execution object is changed, only the slave node will operate according to the command, and the master node will not change the data.

[0110] Then, the backup tables corresponding to the target table in the slave node are all deleted. For example, the table names of the backup tables are "target table_slave" or "target table", and the two tables are deleted. The code statement can be "drop table if exists target table table name_slave, target table table name".

[0111] The master node is then set as the execution object, and the slave node table is generated for storing in the slave node according to the table name of the target node and the update table. Taking the target table table name of the slave node as "target table table name_slave" as an example, "save 'target table' as 'target table_slave'slave" can be used to obtain the target table for storing in the slave node.

[0112] The slave node is then set as the execution object, and the slave node table is saved to the slave node. At this time, the statement "alter table target table_slave rename as target table" can be used to save the update table of the master node to the slave node.

[0113] Further, the target table of the node of the database can use a non-hash table or a hash table. For the two different table types, when the target table is a hash table, all the target tables are determined to be redistribution tables; when the target table is a non-hash table, the redistribution tables in all the target tables are determined according to a preset determination rule and the data volume of the target table. The determination rule refers to a rule for determining the redistribution tables in the non-hash table, which is mainly based on the data volume of the target table to determine whether it is a redistribution table.

[0114] In addition, as shown in Figure 5As shown, after the redistribution table is determined, since the redistribution of the target node requires the re-creation of the table, a queue rule is preset for determining the column value of the subsequently created table to ensure the uniqueness of the data. For example, when the redistribution table is a hash table, the attribute value of the hash table or other special value is used as the column value of the transfer table; when the redistribution table is a non-hash table, the column value can be obtained by extracting the fields of each row in the redistribution table according to a certain selection rule. The selection rule can be freely set, for example, the first, second and middle two fields of each row are selected as the column value.

[0115] The embodiment provides a determination rule. First, the average number is calculated according to the data amount of all target tables. For example, the data amount of the target table 1, that is, the number of CSV files, is 60, the data amount of the target table 2 is 50, and the data amount of the target table 3 is 40, and the average number is 50. A multiple is set in the determination rule, which is used to evaluate the multiple difference between the target tables. For example, the preset multiple is 2 times, the product of the multiple and the average data amount is calculated to obtain the data amount threshold. Finally, for each target table, the size relationship between the data amount of the target table and the data amount threshold is compared. If the data amount is greater than the data amount threshold, the target table is determined as the redistribution table.

[0116] For example, in the previous example, the data amount threshold is 100 when the multiple is 2, and the data amount of none of the target tables is greater than the data amount threshold, so there is no redistribution table in these target tables, and subsequent redistribution is not required. For another example, if the data amount of the target table 1 is 10, the data amount of the target table 2 is 70, the data amount of the target table 3 is 10, the multiple is 2, the data amount threshold is 60, and the data amount of the target table 2 exceeds the data amount threshold, so the target table 2 is the redistribution table.

[0117] Based on the batch import method of the CSV file, the application further provides a terminal device, as shown in the figure, which comprises at least one processor 20, a display screen 21, and a memory 22, and can further comprise a communications interface 23 and a bus 24. The processor 20, the display screen 21, the memory 22 and the communications interface 23 can communicate with each other through the bus 24. The display screen 21 is configured to display the user guide interface preset in the initial setting mode. The communications interface 23 can transmit information. The processor 20 can call the logic command in the memory 22 to execute the method in the above embodiment. Figure 7

[0118] In addition, the logic command in the memory 22 described above can be realized in the form of a software function unit and sold or used as an independent product when used, and can be stored in a computer readable computer readable storage medium.​

[0119] The memory 22, as a computer readable storage medium, can be configured to store software programs, computer executable programs, such as program commands or modules corresponding to the method in the embodiments of the present disclosure. The processor 20 executes the functions of the application and data processing by running the software programs, commands or modules stored in the memory 22, that is, implements the method in the above embodiments.

[0120] The memory 22 can include a program storage area and a data storage area, wherein the program storage area can store an operating system and at least one application required by a function; the data storage area can store data created according to the use of the terminal device, etc. In addition, the memory 22 can include a high-speed random access memory, and can also include a non-volatile memory. For example, a variety of media that can store program codes, such as a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, etc. can also be a transitory computer readable storage medium.

[0121] In addition, the specific processes of the above computer readable storage medium and the plurality of command processors in the terminal device load and execute have been described in detail in the above method, and will not be repeated here.

[0122] Finally, it should be pointed out that: the above embodiments are only used to illustrate the technical solutions of the present application, and not to limit them; 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 of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for batch importing CSV files, characterized in that, The method includes: Retrieve file information for several CSV files to be imported; Based on the file information, determine the target node corresponding to each of the CSV files; and, Based on the file information, the CSV file is grouped to obtain several plan lists; According to the preset traversal termination rule, for each of the plan lists, the CSV files in the plan list are traversed in a loop. When a CSV file is traversed, the CSV file is sent to the corresponding target node until the traversal is completed, and the target table corresponding to the CSV file is obtained. Specifically, when traversing a CSV file, sending the CSV file to the corresponding target node until the traversal is complete, to obtain the target table corresponding to the CSV file, includes: Set up several temporary tables for each of the target nodes; The CSV file is imported into a preset initial table in a loop until a termination command is detected, resulting in a target table. The loop of importing the CSV file into the preset initial table includes: The CSV files are sequentially imported into the temporary table until the number of CSV files in the temporary table equals a preset data threshold. Each target node has several temporary tables. Import the CSV file from the temporary table into the initial table.

2. The batch import method for CSV files according to claim 1, characterized in that, The file information includes the file size of each CSV file, and determining the target node corresponding to each CSV file based on the file information includes: The CSV files are sorted according to their sizes to obtain a file sequence list; Based on the file sequence list, the target node corresponding to each CSV file is determined sequentially in descending order.

3. The batch import method for CSV files according to claim 1 or 2, characterized in that, The file information includes the disk space of each node, and determining the target node corresponding to each CSV file based on the file information includes: For each of the CSV files, candidate nodes are determined based on the disk space. For each candidate node, the remaining space corresponding to the candidate node is calculated based on the disk space of the candidate node and the size of the CSV file corresponding to the candidate node; Based on the remaining space, determine the target node corresponding to the CSV file, and update the disk space corresponding to the target node.

4. The batch import method for CSV files according to claim 1, characterized in that, When traversing a CSV file, sending the CSV file to the corresponding target node includes: When traversing a CSV file, the sending resources corresponding to the CSV file are obtained based on a preset resource pool, wherein the resource pool includes the sending resources corresponding to each target node; Upon successful acquisition, the CSV file is sent to the target node based on the aforementioned sending resources; When a successful transmission command for the CSV file is detected, the transmission resource is returned to the resource pool.

5. The batch import method for CSV files according to claim 1, characterized in that, When the termination command is detected, for each target node, after generating a target table corresponding to that target node based on the loading queue corresponding to that target node, the process further includes: According to the preset redistribution rules, determine the redistribution table in the target table; Expand the target node corresponding to the redistribution table to obtain expanded nodes, and create a transit table corresponding to the expanded nodes; The data in the redistribution table is redistributed to obtain redistributed data, and the redistributed data is imported into the transit table to obtain an update table corresponding to each expansion node.

6. The batch import method for CSV files according to claim 5, characterized in that, The step of determining the redistribution table in the target table according to the preset redistribution rules includes: When the target table is a non-hash table, the redistribution table in all the target tables is determined according to the preset determination rules and the amount of data in the target table; When the target table is a hash table, all target tables are determined to be redistribution tables.

7. The batch import method for CSV files according to claim 6, characterized in that, The step of determining the redistribution tables in all the target tables according to the preset determination rules and the data volume of the target table includes: Calculate the average data volume based on the data volume of all the target tables. Calculate the product of the preset multiple and the average data volume to obtain the data volume threshold corresponding to the target table; For each target table, if the amount of data in the target table is greater than the data amount threshold, the target table is determined to be a redistribution table.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores one or more programs, which can be executed by one or more processors to implement the steps in the batch import method for CSV files as described in any one of claims 1 to 7.

9. A terminal device, characterized in that, include: Processor, memory, and communication bus; The memory stores a computer-readable program that can be executed by the processor; The communication bus enables communication between the processor and the memory; When the processor executes the computer-readable program, it implements the steps in the batch import method for CSV files as described in any one of claims 1 to 7.

Images