Data extraction method and device based on relationship graph

Abstract

The application provides a data extraction method and device based on a relationship graph, and relates to the technical field of data processing.The method comprises the following steps: querying a relationship graph according to a previously received data extraction request to obtain query data; wherein the relationship graph is constructed based on at least one index and dimension corresponding to a query condition; and generating a query statement according to the query data and a preset language generation rule, and executing the query statement to obtain a query result.The application can automatically generate a query statement and execute the query statement according to the query data obtained by querying the relationship graph according to the data extraction request, so as to realize data extraction of a temporary and non-fixed complex data extraction request and realize efficient and accurate data extraction.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a data extraction method and apparatus based on relation graphs. Background Technology

[0002] Data providers often request dimensional metrics that cannot be obtained from existing reports. Currently, data extraction is mostly carried out using the following two methods:

[0003] One approach is to extract data based on personalized data extraction services, where IT professionals write code to extract data from the backend and then transfer it to the business department via email or other means. Another approach is to prepare the tables involved in the personalized data extraction by the business department in advance in a star schema, and the business personnel can select and operate through the interface. The system will automatically generate simple SQL query statements and return the data to the business personnel.

[0004] However, in the first approach mentioned above, personalized data extraction services consume a significant amount of IT staff's working time, and these staff need to be familiar with various table structures and relationships, incurring learning costs. Different IT staff may have different approaches to handling special data, resulting in discrepancies in the data received by the business department due to extraction by different personnel. In the second approach, business needs are often unpredictable, making it difficult to prepare all the tables involved in personalized needs in advance. Furthermore, the prepared data consumes a large amount of storage space and takes a long time to process. If the prepared data does not cover the business's temporary needs, resources are wasted. Summary of the Invention

[0005] This invention provides a data extraction method and apparatus based on relational graphs to address the shortcomings of existing technologies where the accuracy and efficiency of data extraction are poor due to the temporality and complexity of data extraction requests. It satisfies temporary, non-fixed, and complex data extraction requests, and achieves efficient and accurate data extraction.

[0006] This invention provides a data extraction method based on a relationship graph, comprising: querying a relationship graph to obtain query data according to a previously received data extraction request; wherein the relationship graph is constructed in advance based on indicators and dimensions corresponding to at least one query condition; and generating and executing a query statement based on the query data and a preset language generation rule to obtain query results.

[0007] According to a data extraction method based on a relationship graph provided by the present invention, the step of querying the relationship graph to obtain query data based on a previously received data extraction request includes: querying the relationship graph to obtain a corresponding table query set based on the query conditions in the previously received data extraction request, wherein the table query set includes the indicators and dimensions corresponding to the query conditions; and obtaining the query data based on the table connectivity relationship between the table query set and the relationship graph.

[0008] According to a data extraction method based on a relational graph provided by the present invention, query data is obtained based on the table query set and the table connectivity relationships of the relational graph, including: finding the shortest connectivity path based on the table query set to obtain the corresponding table entity; and obtaining the query data by combining the table entity with the table query set and the table connectivity relationships of the relational graph.

[0009] According to a data extraction method based on a relational graph provided by the present invention, query data is obtained by combining the table entity and the table query set, and combining the table connectivity relationships of the relational graph. The method includes: determining whether the table entity and the table query set are connected according to the table connectivity relationships of the relational graph; combining the table entity and the table query set based on the connection between the table entity and the table query set to obtain query data; otherwise, creating a corresponding entity table based on the table entity, and combining the entity table with the table query set to obtain query data.

[0010] According to a data extraction method based on a relational graph provided by the present invention, a query statement is generated based on the query data and in conjunction with preset language generation rules. The method includes: selecting tables from the query data according to preset table selection rules, and generating corresponding sub-query statements in conjunction with preset language generation rules; wherein the preset table selection rules are used to limit the selection order and number of table query sets and corresponding table entities in the query data; generating an intermediate temporary table, and selecting tables from the remaining tables in the query data according to preset table selection rules, associating the selected tables with the intermediate temporary table, and generating corresponding sub-query statements in conjunction with preset language generation rules; continuing to generate intermediate temporary tables, and selecting tables from the remaining tables in the query data according to preset table selection rules, associating the selected tables with the intermediate temporary table, and generating corresponding sub-query statements in conjunction with preset language generation rules, until all tables in the query data are associated; and obtaining a query statement based on all generated sub-query statements.

[0011] According to the data extraction method based on a relationship graph provided by the present invention, before querying the relationship graph according to a previously received data extraction request to obtain the query data, the method includes: establishing a set of corresponding tables based on all indicators and dimensions corresponding to preset query conditions; and associating the set of corresponding tables with preset relationships to obtain the relationship graph.

[0012] According to the data extraction method based on relational graphs provided by the present invention, after obtaining the query results, the method includes: sending the query results to the user terminal for display.

[0013] The present invention also provides a data extraction device based on a relationship graph, comprising: a first query module, which queries the relationship graph according to a previously received data extraction request to obtain query data; wherein the relationship graph is constructed in advance based on indicators and dimensions corresponding to at least one query condition; and a second query module, which generates and executes a query statement according to the query data and a preset language generation rule to obtain query results.

[0014] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of any of the above-described relation graph-based data extraction methods.

[0015] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the data extraction method based on relational graphs as described above.

[0016] The present invention also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of any of the above-described relation graph-based data extraction methods.

[0017] The data extraction method and apparatus based on relation graphs provided by this invention query the relation graph according to the data extraction request, and automatically generate and execute query statements based on the query data obtained from the query, so as to realize the data extraction of temporary and non-fixed complex data extraction requests, and achieve efficient and accurate data extraction. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0019] Figure 1This is a flowchart illustrating the data extraction method based on relational graphs provided by the present invention;

[0020] Figure 2 This is a schematic diagram of the data extraction device based on relation graphs provided by the present invention;

[0021] Figure 3 This is a schematic diagram of the structure of the electronic device provided by the present invention. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0023] Figure 1 This invention illustrates a flowchart of a data extraction method based on a relational graph, comprising:

[0024] S11, based on the previously received data extraction request, query the relationship graph to obtain the query data; the relationship graph was previously constructed based on the indicators and dimensions corresponding to at least one query condition;

[0025] S12: Based on the query data and the preset language generation rules, generate a query statement and execute it to obtain the query results.

[0026] It should be noted that the step number "S1N" in this specification does not represent the order of the data extraction methods based on relation graphs. The data extraction method based on relation graphs of the present invention will be described in detail below.

[0027] Step S11: Based on the previously received data extraction request, query the relationship graph to obtain the query data; wherein, the relationship graph is constructed in advance based on the indicators and dimensions corresponding to at least one query condition.

[0028] In one optional embodiment, before querying the relationship graph and obtaining the query data based on the previously received data extraction request, the process includes: establishing a set of corresponding tables based on all indicators and dimensions corresponding to preset query conditions; and associating the set of corresponding tables with preset relationships to obtain the relationship graph.

[0029] Furthermore, based on all metrics and dimensions corresponding to the preset query conditions, a corresponding set of tables is established, including: obtaining all dimensions and metrics information involved in the personalized data service requests of data service users; determining the relationship between dimensions, metrics, and table fields according to the business system and data warehouse table structure; and creating the corresponding table structure according to the determined relationship between dimensions, metrics, and table fields and the data warehouse table structure.

[0030] It should be noted that indicators include indicator code, indicator name, table name, field name, statistical period field, and constraints. For example: 1001, premium income, policy master table (prpcmain), premium, the greater of start date and underwriting date (max(startdate, undrtdate)), and claims approval flag is 1 or 3 (undrtflag in('1','3')), etc.; dimensions include dimension code, dimension name, table name, and field name. For example: 2001, policy start date, policy master table (prpcmain), and start date (startdate), etc.

[0031] Alternatively, a graph database can be used to establish relationships between tables. Tables are entities in the graph, and the relationships between tables are the relationships between entities in the graph. Any table with a relationship is considered a configured relationship. The configuration information is as follows: Table 1, Table 2, relationship conditions, and relationship characteristics. For example, a policy master table (prpcmain), a policy public information table (prpcmain_common), and a 1:1 relationship based on the policy number (policyno = policyno).

[0032] In this embodiment, querying the relationship graph to obtain query data based on the previously received data extraction request includes: querying the relationship graph to obtain a corresponding table query set based on the query conditions in the previously received data extraction request, wherein the table query set includes the indicators and dimensions corresponding to the query conditions; and obtaining the query data based on the table connectivity relationship between the table query set and the relationship graph.

[0033] It should be noted that data service requests are made by data service users through a visual interface based on the selection of the required dimensions, indicators, and query conditions; in addition, data service users can also choose the format and display method of the returned results.

[0034] Specifically, based on the query conditions in the previously received data extraction request, the relationship graph is queried to obtain the corresponding table query set, including: querying the relationship graph based on the data extraction request to obtain the corresponding data table; and determining the indicators and dimensions in the data table based on the query conditions to obtain the table query set.

[0035] In addition, based on the table query set and the table connectivity relationships in the relational graph, the query data is obtained, including: finding the shortest connectivity path based on the table query set to obtain the corresponding table entity; and combining the table entity with the table query set, and combining the table connectivity relationships in the relational graph to obtain the query data.

[0036] To elaborate further, based on the combination of table entities and table query sets, and combined with the table connectivity relationships in the relational graph, the query data is obtained, including: determining whether the table entities and table query sets are connected based on the table connectivity relationships in the relational graph; combining the table entities and table query sets based on their connectivity to obtain the query data; otherwise, creating corresponding entity tables based on the table entities, and combining the entity tables with the table query sets to obtain the query data.

[0037] It should be added that after creating the corresponding entity table based on the table entity, this includes associating the entity table with the table query set and other tables connected to the table query set to ensure that all tables in the query data are connected.

[0038] Step S12: Based on the query data and the preset language generation rules, generate a query statement and execute it to obtain the query results.

[0039] In this embodiment, a query statement is generated based on the query data and a preset language generation rule, including: selecting tables from the query data according to a preset table selection rule, and generating corresponding subquery statements according to the preset language generation rule; wherein, the preset table selection rule is used to limit the selection order and number of table query sets and corresponding table entities in the query data; generating an intermediate temporary table, selecting tables from the remaining tables in the query data according to the preset table selection rule, associating the selected tables with the intermediate temporary table, and generating corresponding subquery statements according to the preset language generation rule; continuing to generate intermediate temporary tables, selecting tables from the remaining tables in the query data according to the preset table selection rule, associating the selected tables with the intermediate temporary table, and generating corresponding subquery statements according to the preset language generation rule, until all tables in the query data are associated; and obtaining the query statement based on all generated subquery statements.

[0040] In one alternative embodiment, after generating the query statement, the process includes: generating a query engine based on the query statement.

[0041] In one optional embodiment, after obtaining the query results, the method includes: sending the query results to the user terminal for display, so that the user can view the execution progress and download the execution results on the visual interface.

[0042] It should be noted that asynchronous execution is possible, meaning that while the current query results are being sent to the client for display, the next data retrieval request can continue to be processed. Additionally, users can pre-select the return format and display effect of the query results based on the visual interface.

[0043] In summary, the embodiments of the present invention query the relationship graph according to the data extraction request, and automatically generate and execute query statements based on the query data obtained from the query, so as to realize the data extraction of temporary and non-fixed complex data extraction requests, and achieve efficient and accurate data extraction.

[0044] The following describes the data extraction device based on relational graphs provided by the present invention. The data extraction device based on relational graphs described below and the data extraction method based on relational graphs described above can be referred to in correspondence with each other.

[0045] Figure 2 A schematic diagram of a data extraction device based on relational graphs is shown. The device includes:

[0046] The first query module 21 queries the relationship graph based on the previously received data extraction request to obtain the query data; wherein, the relationship graph is constructed in advance based on the indicators and dimensions corresponding to at least one query condition;

[0047] The second query module 22 generates and executes query statements based on the query data and preset language generation rules to obtain query results.

[0048] In an optional embodiment, the apparatus further includes: a table creation module, which, before querying the relationship graph according to a previously received data extraction request and obtaining the query data, creates a set of corresponding tables based on all indicators and dimensions corresponding to preset query conditions; and a graph generation module, which associates the set of corresponding tables with preset relationships to obtain a relationship graph.

[0049] Furthermore, the table creation module includes: a data acquisition unit, which acquires all dimensions and indicator information involved in the personalized data service requests of data service users; a relationship determination unit, which determines the relationship between dimensions, indicators, and table fields based on the business system and data warehouse table structure; and a table structure creation unit, which creates the corresponding table structure according to the determined relationship between dimensions, indicators, and table fields and the data warehouse table structure.

[0050] In this embodiment, the first query module 21 includes: a first query unit, which queries the relationship graph according to the query conditions in the previously received data extraction request to obtain a corresponding table query set, wherein the table query set includes the indicators and dimensions corresponding to the query conditions; and a second query unit, which obtains the query data according to the table connectivity relationship between the table query set and the relationship graph.

[0051] Specifically, the first query unit includes: a data table query subunit, which queries the relationship graph according to the data extraction request to obtain the corresponding data table; and a first query subunit, which determines the indicators and dimensions in the data table according to the query conditions to obtain the table query set.

[0052] In addition, the second query unit includes: an entity query subunit, which finds the shortest connected path based on the table query set to obtain the corresponding table entity; and a data query subunit, which obtains the query data based on the combination of the table entity and the table query set, combined with the table connectivity relationships in the relation graph.

[0053] To elaborate further, the data query subunit is used to: determine whether a table entity and a table query set are connected based on the table connectivity relationships in the relational graph; combine the table entity and the table query set to obtain the query data based on the connectivity between the table entity and the table query set; otherwise, create a corresponding entity table based on the table entity and combine the entity table and the table query set to obtain the query data.

[0054] It should be added that the data query subunit is also used to: after creating the corresponding entity table based on the table entity, associate the entity table with the table query set and other tables connected to the table query set, so as to ensure that all tables in the query data are connected.

[0055] The second query module 22 includes a statement generation unit, used for: selecting tables from the query data according to preset table selection rules, and generating corresponding subquery statements based on preset language generation rules; wherein, the preset table selection rules are used to limit the selection order and number of table query sets and corresponding table entities in the query data; generating intermediate temporary tables, and selecting tables from the remaining tables in the query data according to preset table selection rules, associating the selected tables with the intermediate temporary tables, and generating corresponding subquery statements based on preset language generation rules; continuing to generate intermediate temporary tables, and selecting tables from the remaining tables in the query data according to preset table selection rules, associating the selected tables with the intermediate temporary tables, and generating corresponding subquery statements based on preset language generation rules, until all tables in the query data are associated; and obtaining a query statement based on all generated subquery statements.

[0056] In an optional embodiment, the apparatus further includes an engine generation module that generates a query engine based on the query statement after the query statement is generated.

[0057] In an optional embodiment, the device further includes a result sending module, which, after obtaining the query results, sends the query results to the user terminal for display, thereby facilitating the user to view the execution progress and download the execution results on a visual interface.

[0058] In summary, the embodiments of the present invention utilize a first query module to query a relationship graph based on a data extraction request, and a second query module to automatically generate and execute a query statement based on the query data obtained from the query, thereby enabling data extraction for temporary and non-fixed complex data extraction requests and achieving efficient and accurate data extraction.

[0059] Figure 3 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 3 As shown, the electronic device may include a processor 31, a communications interface 32, a memory 33, and a communication bus 34. The processor 31, communications interface 32, and memory 33 communicate with each other via the communication bus 34. The processor 31 can call logical instructions in the memory 33 to execute a data extraction method based on a relational graph. This method includes: querying the relational graph according to a previously received data extraction request to obtain query data; wherein the relational graph is constructed based on indicators and dimensions corresponding to at least one query condition; and generating and executing a query statement based on the query data and pre-defined language generation rules to obtain query results.

[0060] Furthermore, the logical instructions in the aforementioned memory 33 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, essentially, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0061] On the other hand, the present invention also provides a computer program product, which includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the data extraction method based on the relational graph provided by the above methods. The method includes: querying the relational graph according to a previously received data extraction request to obtain query data; wherein the relational graph is constructed in advance based on indicators and dimensions corresponding to at least one query condition; generating a query statement based on the query data and combining it with preset language generation rules, and executing it to obtain query results.

[0062] In another aspect, the present invention also provides a non-transitory computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the data extraction method based on the relational graph provided by the above methods. The method includes: querying the relational graph according to a previously received data extraction request to obtain query data; wherein the relational graph is constructed in advance based on indicators and dimensions corresponding to at least one query condition; generating a query statement based on the query data and combining it with preset language generation rules, and executing it to obtain query results.

[0063] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0064] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0065] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data extraction method based on relational graphs, characterized in that, include: Based on the previously received data extraction request, the relationship graph is queried to obtain the query data; wherein, the relationship graph is constructed in advance based on the indicators and dimensions corresponding to at least one query condition; Based on the query data, and in conjunction with preset language generation rules, a query statement is generated and executed to obtain the query results; Based on the query data and in accordance with preset language generation rules, a query statement is generated, including: Based on the query data, tables are selected from the query data according to preset table selection rules, and corresponding subquery statements are generated in combination with preset language generation rules; wherein, the preset table selection rules are used to limit the selection order and number of table query sets and corresponding table entities in the query data; An intermediate temporary table is generated, and a table is selected from the remaining tables in the query data according to the preset table selection rules. The selected table is associated with the intermediate temporary table, and a corresponding subquery statement is generated in combination with the preset language generation rules. Continue generating intermediate temporary tables, and select tables from the remaining tables in the query data according to preset table selection rules. Associate the selected tables with the intermediate temporary tables, and generate corresponding subquery statements in combination with preset language generation rules, until all tables in the query data are associated. The query statement is obtained based on all the generated subqueries.

2. The data extraction method based on relational graphs according to claim 1, characterized in that, The step of querying the relationship graph and obtaining the query data based on the previously received data extraction request includes: Based on the query conditions in the previously received data extraction request, the relationship graph is queried to obtain the corresponding table query set, which includes the indicators and dimensions corresponding to the query conditions. The query data is obtained based on the table query set and the table connectivity relationships in the relation graph.

3. The data extraction method based on relational graphs according to claim 2, characterized in that, Based on the table query set and the table connectivity relationships in the relation graph, the query data is obtained, including: Based on the table query set, find the shortest connected path and obtain the corresponding table entity; The query data is obtained by combining the table entity with the table query set and the table connectivity relationships in the relation graph.

4. The data extraction method based on relational graphs according to claim 3, characterized in that, Based on the combination of the table entity and the table query set, and in conjunction with the table connectivity relationships in the relational graph, the query data is obtained, including: Based on the table connectivity relationships in the relation graph, determine whether the table entity is connected to the table query set; Based on the connection between the table entity and the table query set, the table entity and the table query set are combined to obtain query data; Otherwise, based on the table entity, a corresponding entity table is created, and the entity table is combined with the table query set to obtain the query data.

5. The data extraction method based on relational graphs according to claim 1, characterized in that, Before retrieving the query data by querying the relationship graph based on the previously received data extraction request, the process includes: Based on all the indicators and dimensions corresponding to the preset query conditions, a set of corresponding tables is created; Based on the preset association relationships, the corresponding table sets are associated to obtain the relationship graph.

6. The data extraction method based on relational graphs according to claim 1, characterized in that, After obtaining the query results, the following will be included: The query results are sent to the user's device for display.

7. A data extraction device based on relational graphs, characterized in that, include: The first query module queries the relationship graph based on the previously received data extraction request to obtain the query data; wherein the relationship graph is constructed in advance based on the indicators and dimensions corresponding to at least one query condition; The second query module generates and executes a query statement based on the query data and a preset language generation rule to obtain the query result. The second query module includes a statement generation unit, used for: Based on the query data, tables are selected from the query data according to preset table selection rules, and corresponding subquery statements are generated in combination with preset language generation rules; wherein, the preset table selection rules are used to limit the selection order and number of table query sets and corresponding table entities in the query data; An intermediate temporary table is generated, and a table is selected from the remaining tables in the query data according to the preset table selection rules. The selected table is associated with the intermediate temporary table, and a corresponding subquery statement is generated in combination with the preset language generation rules. Continue generating intermediate temporary tables, and select tables from the remaining tables in the query data according to preset table selection rules. Associate the selected tables with the intermediate temporary tables, and generate corresponding subquery statements in combination with preset language generation rules, until all tables in the query data are associated. The query statement is obtained based on all the generated subqueries.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the data extraction method based on relational graphs as described in any one of claims 1 to 6.

9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the data extraction method based on relational graphs as described in any one of claims 1 to 6.

Images