Database-based data warehousing method and system

Abstract

The application provides a database-based data warehousing method and system, the method comprising: in response to a received special data warehousing request, a coordination node initiates internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes, the special computing nodes send data request instructions to a data server; the data server sends corresponding data to the corresponding special computing nodes; all special computing nodes process the data received by each according to a preset algorithm; after collecting the transaction status of all special computing node replies, the coordination node sets the global visibility of the data. The application accelerates the data warehousing process, shortens the data visibility time, and improves the efficiency of instant queries by users on the basis of logically distinguishing the functions of the computing nodes, and has a relatively significant performance improvement in the Internet of Things and small-batch data loading scenarios.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database technology, and in particular to a database-based data warehousing method and system. BACKGROUND

[0002] The current MPP (Massively Parallel Processing) architecture database cluster data warehouse uses external tables to achieve parallel data loading and achieve the purpose of mass data warehousing.

[0003] The schematic diagram is shown in Figure 1 It should be noted that the prerequisite for this part of the content is that the table structure and the corresponding external table structure have been created in the data warehouse, and the external table structure contains the address of the data server and other information.

[0004] The specific process is as follows:

[0005] S1, connect the coordination node (master) of the data warehouse, execute the insert external table operation, insert into table_name select * from external_table;

[0006] S2, the coordination node (master) will automatically start transaction state control and send internal execution instructions to all computing nodes (segment) under it;

[0007] S3, after the computing node (segment) receives the execution instruction, the computing node will send a data request instruction to the data server, and the order of sending the request by the computing node is random based on the network situation;

[0008] S4, after the data server receives the request instruction, it will send the data to the computing node (segment) according to the protocol content (currently GPFDIST protocol) in the order of receiving the request, and the computing node (segment) that processes the data ensures that the data is correctly distributed on all computing nodes;

[0009] S5, when the data server has no new data to send, the data server will send a state control instruction (EOF) to all computing nodes (segment) to reply that the data transmission is complete.

[0010] S6, all computing nodes (segment) process the data according to their respective received data, and after receiving the state control instruction (EOF), they will reply the current transaction state to the coordination node (master).

[0011] S7, after collecting the transaction status responses from all compute nodes (segments), the coordinating node (master) sets the global visibility of the data as appropriate.

[0012] This completes a full data entry process.

[0013] As can be seen from the above process, the basis for global data visibility after a single data loading is completed is that the coordinating node (master) can receive the transaction status of all compute nodes (segments). The prerequisite for a compute node (segment) to reply with its transaction status is that it has finished processing the data and received an End-of-Flight (EOF) response from the data server.

[0014] The above data ingestion process is highly effective when the data server has a large amount of data to ingest. During this process, all computing nodes (segments) will have actual data processing activities.

[0015] There is a current user scenario where the scale of computing nodes (segments) is relatively large, but the amount of data to be inserted into the database each time is relatively small, meaning there is an increasing demand for small-batch data insertion. The focus is on the timely insertion of a certain amount of data (relative to a single large amount of data), and the data insertion process must occur continuously, meaning that the insertion calls to the coordinating node (master) must be performed continuously in a loop.

[0016] At this point, due to the large number of computing nodes (segments) and the relatively limited amount of data loaded at one time, some computing nodes (segments) may not actually process data during this data loading process, meaning there is a period of idle time in the process. However, they participate in the processing related to the final state control instruction (EOF), thus completing the overall data loading and making the data visible.

[0017] To make it easier to understand, here is an example:

[0018] There are 20 compute nodes (segments), and 50GB of data needs to be loaded at once. The data server sends 1MB of data to each compute node (segment) at a time. Therefore, during this data loading, all compute nodes have actual data processing activities, and not just once. Only after the 50GB of data has been loaded is the data made globally visible, and the time for the data to become visible depends on the overall loading time.

[0019] Example of current data loading behavior:

[0020] There are 256 compute nodes (segments), and the total amount of data to be loaded is 50GB. However, this 50GB of data is stored separately, and the data server sends 1MB of data to each compute node (segment) at a time. Because the data is stored separately, if the amount of data loaded at one time is less than 256MB, some compute nodes (segments) will not receive any data for processing during that data loading operation. However, compute nodes (segments) that haven't processed any data cannot predict whether they will receive data. Therefore, according to the normal process, these compute nodes (segments) will also send data requests to the data server, receive an Expiration of Forecast (EOF) instruction, and then report their status to the coordinating node (master). This results in compute nodes (segments) running idle.

[0021] As the number of compute nodes (segments) increases and the amount of data loaded each time decreases, the above situation becomes more pronounced, which in turn prolongs the time for the visibility of this data load. Summary of the Invention

[0022] The technical problem to be solved by this invention is how to shorten the time for data loading visibility when the data volume is small; in view of this, this invention provides a database-based data import method and system.

[0023] The technical solution adopted in this invention is a data import method based on a database, comprising:

[0024] Step S1: In response to the received special data entry request, the coordinating node initiates internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes. The coordinating node has multiple computing nodes deployed under it, including the special computing nodes and regular computing nodes.

[0025] Step S2: After receiving the execution instruction, the special computing node sends a data request instruction to the data server.

[0026] Step S3: After receiving the request instruction, the data server sends the corresponding data to the corresponding special computing node in the order in which the request instruction was received. When the data server has no new data to send, it replies to all the special computing nodes with a status control instruction indicating that the data transmission is complete.

[0027] Step S4: All the special computing nodes process the data they receive according to a preset algorithm, wherein when the status control instruction is received, the current transaction status is replied to the coordination node.

[0028] Step S5: After collecting the transaction status responses from all the special computing nodes, the coordination node sets the global visibility of the special data as needed.

[0029] In one implementation, step S1 further includes:

[0030] The number of special computing nodes and regular computing nodes in the computing nodes will be dynamically adjusted.

[0031] In one embodiment, dynamically adjusting the number of special computing nodes and regular computing nodes in the computing nodes further includes:

[0032] Modify the special computing node to the regular computing node; or

[0033] The conventional computing node is modified into the special computing node.

[0034] In one embodiment, the method further includes:

[0035] The coordinating node is configured with a list of read nodes, wherein the list of read nodes includes only information about the special computing nodes, and is used for the interaction between the coordinating node and all special computing nodes.

[0036] In one embodiment, prior to step S1, the method further includes:

[0037] By configuring the external statement, an identifier is added to the special data to be entered into the database, so that when the coordinating node receives the special data entry request, it can call the list of reading nodes based on the identifier.

[0038] Another aspect of the present invention provides a database-based data import system, comprising:

[0039] A coordinating node is configured to initiate internal communication with a pre-configured number of special computing nodes in response to a received special data ingestion request, so as to send the special data and execution instructions to the special computing nodes.

[0040] The computing nodes include special computing nodes and regular computing nodes. The special computing nodes are configured to send a data request instruction to the data server after receiving the execution instruction.

[0041] The data server is configured to send the corresponding data to the corresponding special computing node in the order in which the request instructions are received after receiving the request instructions. When the data server has no new data to send, it replies to all the special computing nodes with a status control instruction indicating that the data transmission is complete.

[0042] The special computing nodes are further configured to process the data they receive according to a preset algorithm, wherein, upon receiving the status control instruction, they reply to the coordination node with the current transaction status.

[0043] The coordinating node is further configured to set global visibility of special data as needed after collecting transaction status responses from all the special computing nodes.

[0044] Another aspect of the present invention provides an electronic device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of the database-based data entry method as described in any of the preceding claims.

[0045] Another aspect of the present invention provides a computer storage medium storing a computer program, which, when executed by a processor, implements the steps of the database-based data entry method as described in any of the preceding claims.

[0046] Compared with the prior art, the present invention has at least the following advantages:

[0047] The method provided by this invention, while logically distinguishing the functions of computing nodes, does not intrusively affect normal query functions, accelerates the data entry process, shortens data visibility time, and improves user efficiency in real-time queries. It demonstrates significant performance improvements in IoT and small-batch data loading scenarios. Attached Figure Description

[0048] Figure 1 A schematic diagram of a data entry system in the existing technology;

[0049] Figure 2 This is a flowchart illustrating a database-based data import method according to an embodiment of the present invention.

[0050] Figure 3 This is a schematic diagram of the structure of a database-based data import system according to an embodiment of the present invention;

[0051] Figure 4 This is a schematic diagram of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0052] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments.

[0053] In the accompanying drawings, the thickness, size, and shape of the objects have been slightly exaggerated for ease of illustration. The drawings are for illustrative purposes only and are not drawn to scale.

[0054] It should also be understood that the terms "comprising," "including," "having," "containing," and / or "comprising," when used in this specification, indicate the presence of the stated features, integrals, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components, and / or combinations thereof. Furthermore, when expressions such as "at least one of..." appear after a list of listed features, they modify the entire listed feature, not individual elements in the list. Additionally, when describing embodiments of this application, the word "may" is used to mean "one or more embodiments of this application." And the term "exemplary" is intended to refer to an example or illustration.

[0055] As used herein, the terms “basically,” “approximately,” and similar terms are used as terms of approximation rather than terms of degree, and are intended to describe inherent biases in measured or calculated values ​​that will be recognized by those skilled in the art.

[0056] Unless otherwise specified, all terms used herein (including technical and scientific terms) shall have the same meaning as commonly understood by one of ordinary skill in the art to which this application pertains. It should also be understood that terms (e.g., those defined in common dictionaries) shall be interpreted as having the meaning consistent with their meaning in the context of the relevant art and shall not be interpreted in an idealized or overly formal sense unless expressly so specified herein.

[0057] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0058] The steps described in the specification and the flowcharts in the accompanying drawings of this invention are not necessarily to be strictly followed according to the step numbers; the execution order of the steps can be changed. Furthermore, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be broken down into multiple steps.

[0059] The first embodiment of the present invention provides a database-based data import method, such as... Figure 2 As shown, the specific steps include the following:

[0060] Step S1: In response to the received special data entry request, the coordinating node initiates internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes. The coordinating node has multiple computing nodes deployed under it, including the special computing nodes and regular computing nodes.

[0061] Step S2: After receiving the execution instruction, the special computing node sends a data request instruction to the data server.

[0062] Step S3: After receiving the request instruction, the data server sends the corresponding data to the corresponding special computing node in the order in which the request instruction was received. When the data server has no new data to send, it replies to all the special computing nodes with a status control instruction indicating that the data transmission is complete.

[0063] Step S4: All the special computing nodes process the data they receive according to a preset algorithm, wherein when the status control instruction is received, the current transaction status is replied to the coordination node.

[0064] Step S5: After collecting the transaction status responses from all the special computing nodes, the coordination node sets the global visibility of the special data as needed.

[0065] The method in this embodiment will be described in detail below.

[0066] For reference Figure 3 As can be seen from the figure, in this embodiment, n computing nodes are divided into m special computing nodes and corresponding nm regular computing nodes.

[0067] In this embodiment, a list of read nodes needs to be configured on the coordinating node. The list of read nodes only includes information about the special computing nodes and is used for the interaction between the coordinating node and all special computing nodes.

[0068] In other words, there are currently 1 to n computing nodes, a total of n. Some of these nodes (1 to m) are logically divided into data loading nodes (i.e. special computing nodes), and their information is stored in the system table load_table (read node list) and the corresponding auxiliary tables. The content stored in the tables comes from other system tables on the coordinating node.

[0069] In summary, the work involved in coordinating nodes may include:

[0070] 1) Add a table load_table to record information about special computing nodes 1 to m. The information comes from the system table and is used to record information about special computing nodes that participate in data loading.

[0071] 2) After receiving a special data loading instruction, the coordinating node initiates internal communication with the compute nodes based on the contents of the load_table, and only sends the instruction to the preset compute nodes.

[0072] 3) When the coordinating node is handling the transaction status of special data loading instructions, it only collects the status returned by the compute nodes in the load_table table to complete the data visibility settings.

[0073] In this embodiment, the number of special computing nodes and regular computing nodes in the computing nodes can also be dynamically adjusted. Specifically, special computing nodes can be modified into regular computing nodes, or regular computing nodes can be modified into special computing nodes.

[0074] In other words, the data in the above list of read nodes can be dynamically adjusted, and users can adjust it according to their current needs. However, the dynamically adjusted result will be applied to the next data entry operation, and ongoing data entry operations will not be affected.

[0075] In some specific implementation methods, the dynamic adjustment of the data entry participation strategy can further include the following points:

[0076] 1) The default participation strategy is that all compute nodes participate in data ingestion;

[0077] 2) Provide a user API to manually adjust the contents of the load_table table and the corresponding auxiliary tables, thereby indirectly adjusting the participation strategy manually;

[0078] 3) Periodically obtain resource usage information for all computing nodes via APIs provided by third-party monitoring functions, including but not limited to CPU, MEMORY, and IO. Based on the resource usage of participating data ingestion nodes, use a decision tree to dynamically adjust the contents of the load_table table and corresponding auxiliary tables, thereby achieving the goal of automatically adjusting the participation strategy.

[0079] In this embodiment, before step S1, the method further includes:

[0080] By configuring the external statement, an identifier is added to the special data to be entered into the database, so that when the coordinating node receives the special data entry request, it can call the list of reading nodes based on the identifier.

[0081] In other words, the term "special data" in this article refers only to data containing the aforementioned identifiers, and does not imply that any aspect of that data is "special" compared to other data.

[0082] Specifically, after receiving a special data loading request, the coordinating node's query parsing module processes the special data loading identifier. This identifier is used to distinguish it from the original data loading behavior and determine the different processing branches. The special data loading request here is an identifier added to the basic insertto table statement. This identifier, along with subsequent configuration options and the contents of the load_table, determines the specific data loading participation strategy.

[0083] Based on the specific transaction processing (data visibility) process, it is necessary to update some execution strategies of the coordinating node. That is, the query processing module will apply the information of the load_table table to the current processing flow at the appropriate location. At this time, the coordinating node can determine some behaviors of this data entry, that is, which nodes are involved in this data entry behavior and which computing nodes to send internal control instructions to. The subsequent internal communication part will follow the current processing flow.

[0084] According to the data ingestion strategy, the transaction processing module of the coordinating node also needs to add special handling, namely, determining which compute nodes to wait for status control responses based on which compute nodes the internal control instructions were sent to. After collecting all status responses, the coordinating node will decide whether to make the data ingested this time globally visible based on the current status.

[0085] The result will allow data to be stored in the database in a shorter time than the original scheme, and the latency for data visibility will be reduced. Furthermore, it will not affect the current normal query process; that is, when the coordinating node performs data queries, it will use the original query strategy and utilize the computing resources of compute nodes 1 to n.

[0086] The coordinating node in the cluster has a parameter configuration to determine whether to enable the data ingestion optimization feature. This parameter is disabled by default during cluster initialization. Users can enable the configuration when needed, reloading the configuration file or restarting the cluster for the changes to take effect. Changing the configuration from enabled to disabled will affect subsequent data ingestion operations.

[0087] The status of the `load_table` table and its corresponding auxiliary tables also affects some behaviors in the data import process. For example, if the auxiliary table shows that some nodes participating in data loading are unavailable, the data import request will be rejected, and the error code returned to the client will remain the same as the original code. If the `load_table` table has no content, the default participation strategy will be used, i.e., all nodes participate in data import.

[0088] In summary, compared to existing technologies, this embodiment, while logically distinguishing the functions of computing nodes, does not intrusively affect normal query functions, accelerates the data entry process, shortens data visibility time, and improves user efficiency in real-time queries. It offers significant performance improvements in IoT and small-batch data loading scenarios.

[0089] The second embodiment of the present invention is a database-based data import system based on the above embodiments, such as... Figure 3 As shown, it specifically includes:

[0090] A coordinating node is configured to initiate internal communication with a pre-configured number of special computing nodes in response to a received special data ingestion request, so as to send the special data and execution instructions to the special computing nodes.

[0091] The computing nodes include special computing nodes and regular computing nodes. The special computing nodes are configured to send a data request instruction to the data server after receiving the execution instruction.

[0092] The data server is configured to send the corresponding data to the corresponding special computing node in the order in which the request instructions are received after receiving the request instructions. When the data server has no new data to send, it replies to all the special computing nodes with a status control instruction indicating that the data transmission is complete.

[0093] Special computing nodes are further configured to process the data they receive according to a preset algorithm, wherein, upon receiving the status control instruction, they reply to the coordination node with the current transaction status;

[0094] The coordinating node is further configured to set global visibility of special data as needed after collecting transaction status responses from all the special computing nodes.

[0095] A third embodiment of the present invention provides an electronic device, such as... Figure 4 As shown, it can be understood as a physical device, including a processor and a memory storing processor-executable instructions. When the instructions are executed by the processor, the following operations are performed:

[0096] Step S1: In response to the received special data entry request, the coordinating node initiates internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes. The coordinating node has multiple computing nodes deployed under it, including the special computing nodes and regular computing nodes.

[0097] Step S2: After receiving the execution instruction, the special computing node sends a data request instruction to the data server.

[0098] Step S3: After receiving the request instruction, the data server sends the corresponding data to the corresponding special computing node in the order in which the request instruction was received. When the data server has no new data to send, it replies to all the special computing nodes with a status control instruction indicating that the data transmission is complete.

[0099] Step S4: All the special computing nodes process the data they receive according to a preset algorithm, wherein when the status control instruction is received, the current transaction status is replied to the coordination node.

[0100] Step S5: After collecting the transaction status responses from all the special computing nodes, the coordination node sets the global visibility of the special data as needed.

[0101] The fourth embodiment of the present invention provides a database-based data import method with the same flow as the first, second, or third embodiments. The difference lies in the engineering implementation: this embodiment can be implemented using software plus necessary general-purpose hardware platforms. While hardware can also be used, the former is often a better implementation method. Based on this understanding, the method of the present invention can be embodied in the form of a computer software product stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), including several instructions to cause a device to execute the method described in the embodiments of the present invention.

[0102] Through the description of specific embodiments, a more in-depth and specific understanding should be gained of the technical means and effects adopted by the present invention to achieve the intended purpose. However, the accompanying drawings are only provided for reference and illustration and are not intended to limit the present invention.

Claims

1. A database-based data ingesting method, characterized by, The method comprises: Step S1, in response to a received special data storage request, a coordination node initiates internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes, wherein the coordination node is deployed with a plurality of computing nodes, including the special computing nodes and regular computing nodes; Step S2, after receiving the execution instructions, the special computing nodes send data request instructions to a data server; Step S3, after receiving the request instructions, the data server sends corresponding data to the corresponding special computing nodes in the order in which the request instructions are received, wherein when the data server has no new data to send, it replies to all the special computing nodes with a state control instruction indicating that the data transmission is complete; Step S4, all the special computing nodes process the data they have received according to a pre-set algorithm, wherein when the state control instruction is received, the current transaction state is replied to the coordination node; Step S5, after collecting the transaction states replied by all the special computing nodes, the coordination node sets the global visibility of the special data according to requirements.

2. The database-based data ingesting method of claim 1, wherein, The step S1 further comprises: The number of special computing nodes and regular computing nodes in the computing nodes is dynamically adjusted.

3. The database-based data ingesting method of claim 2, wherein, The dynamically adjusting the number of special computing nodes and regular computing nodes in the computing nodes further comprises: Modifying the special computing nodes to the regular computing nodes; or Modifying the regular computing nodes to the special computing nodes.

4. The database-based data ingesting method of claim 1, wherein, The method further comprises: Configuring a read node list in the coordination node, wherein the read node list only includes information of the special computing nodes, and is used for interaction between the coordination node and all the special computing nodes.

5. The database-based data ingesting method of claim 4, wherein, Before the step S1, the method further comprises: Adding an identifier to the special data to be stored by configuration in an external table statement, so that when the coordination node receives the special data storage request, it calls the read node list based on the identifier.

6. A database-based data warehousing system, characterized by The method comprises: A coordination node configured to, in response to a received special data storage request, initiate internal communication with a pre-configured number of special computing nodes to send the special data and execution instructions to the special computing nodes; Computing nodes, wherein the coordination node is deployed with a plurality of computing nodes, including the special computing nodes and regular computing nodes, and the special computing nodes are configured to, after receiving the execution instructions, send data request instructions to a data server; A data server configured to, after receiving the request instructions, send corresponding data to the corresponding special computing nodes in the order in which the request instructions are received, wherein when the data server has no new data to send, it replies to all the special computing nodes with a state control instruction indicating that the data transmission is complete; The special computing node is further configured to process the received data according to a preset algorithm, and when the state control instruction is received, the current transaction state is returned to the coordination node; The coordination node is further configured to set global visibility of special data according to requirements after collecting all the transaction states returned by the special computing nodes.

7. An electronic device, comprising: A computer readable storage medium, having stored thereon a computer program, which, when executed by a processor, implements the steps of the database-based data warehousing method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, A computer readable storage medium, having stored thereon a computer program, which, when executed by a processor, implements the steps of the database-based data warehousing method according to any one of claims 1 to 5.

Images