Data association query method and device, equipment and storage medium

Abstract

Embodiments of the present application provide a data association query method and device, equipment and a storage medium. The method comprises: parsing a data query statement to obtain association query information; the association query information comprises query key information of a first association table; determining a query strategy according to the query key information; wherein the query strategy comprises a partition filtering strategy, a side scanning side filtering strategy and a first scanning then filtering strategy; and querying data in a second association table based on the query strategy. The data association query method provided by the embodiments of the present application dynamically determines a query strategy according to query key information, and filters data sources when querying data based on the query strategy, so that all data in the data sources does not need to be scanned, the amount of data processed during association query can be reduced, and the efficiency of association query can be improved.

Description

TECHNICAL FIELD

[0001] Embodiments of the present application relate to the technical field of data query, and in particular, to a data correlation query method and device, equipment and a storage medium. BACKGROUND

[0002] Data query is a common operation in big data processing, and the underlying call is a distributed data processing engine. In a common database model, data tables are generally divided into fact tables and dimension tables. The fact table records actual data information, and the data volume increases continuously over time, while the dimension table is generally fixed and records basic transaction information. Correlation query is a query operation on two or more tables.

[0003] In related technologies, when data is correlated and queried, resource cost and time cost are consumed. SUMMARY

[0004] Embodiments of the present application provide a data correlation query method, device, equipment and storage medium, which can reduce the amount of data processed during correlation query and improve the efficiency of correlation query.

[0005] To achieve the above object, the embodiments of the present application provide a data correlation query method, comprising:

[0006] The data query statement is parsed to obtain correlation query information; the correlation query information includes query key information of a first correlation table;

[0007] The query strategy is determined according to the query key information;

[0008] The data in a second correlation table is queried based on the query strategy.

[0009] To achieve the above object, the embodiments of the present application provide a data correlation query device, comprising:

[0010] A correlation query information acquisition module is configured to parse a data query statement to obtain correlation query information; the correlation query information includes query key information of a first correlation table;

[0011] A query strategy determination module is configured to determine a query strategy according to the query key information;

[0012] A data query module is configured to query data in a second correlation table based on the query strategy.

[0013] To achieve the above object, the embodiments of the present application provide a computer device, comprising 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 control method of data correlation query as described in the embodiments of the present application.

[0014] To achieve the above object, the embodiment of the present application provides a computer readable storage medium, which stores a computer program, and the program is executed by a processor to realize the data correlation query method as described in the embodiment of the present application.

[0015] The data correlation query method, device, equipment and storage medium are provided in the embodiment of the present application. First, the data query statement is parsed to obtain correlation query information; the correlation query information includes query key information of a first correlation table; then, a query strategy is determined according to the query key information; wherein, the query strategy includes a partition filtering strategy, a scanning-while-filtering strategy and a scanning-then-filtering strategy; finally, data in a second correlation table is queried based on the query strategy. The data correlation query method provided in the embodiment of the present application dynamically determines the query strategy according to the query key information, and when the data is queried based on the query strategy, the data source is filtered, without the need to scan all data in the data source, so that the amount of data processed during the correlation query can be reduced, and the efficiency of the correlation query can be improved. BRIEF DESCRIPTION OF DRAWINGS

[0016] Figure 1 is a flowchart of a data correlation query method provided in the embodiment of the present application;

[0017] Figure 2 is a schematic diagram of a correlation table provided in the embodiment of the present application;

[0018] Figure 3 is a structural schematic diagram of a data correlation query device provided in the embodiment of the present application;

[0019] Figure 4 is a structural schematic diagram of a computer device provided in the embodiment of the present application. DETAILED DESCRIPTION

[0020] To make the purpose, technical scheme and advantages of the present application more clear, the embodiments of the present application will be described in detail below with reference to the drawings. It should be noted that, in the case of no conflict, the embodiments in the present application and the features in the embodiments can be combined with each other at will.

[0021] It should be understood that the specific embodiments described herein are merely intended to explain the present application, and are not intended to limit the present application.

[0022] In the subsequent description, suffixes such as "module", "component" or "unit" used to represent elements are only for the convenience of description of the present application, and have no special meaning. Therefore, "module", "component" or "unit" can be mixedly used.

[0023] In one embodiment, Figure 1A flowchart of a data correlation query method provided by an embodiment of the present application. The method can be applicable to the case of correlation query on data. The method can be executed by a data correlation query device. As shown in Figure 1 S110-S130.

[0024] S110, parse the data query statement to obtain correlation query information.

[0025] The correlation query information includes query key information of a first correlation table. The correlation query information also includes the first correlation table and at least one second correlation table having a correlation relationship with the first correlation table. In this embodiment, the first correlation table can be understood as a dimension table for recording basic characteristic information of data; the second correlation table can be understood as a fact table for recording actual data. The query key information of the first correlation table and the second correlation table is the same, so when the query key information of the first correlation table is obtained, the query key information of the second correlation table is also obtained. For example, Figure 2 A schematic diagram of the correlation table in the embodiment of the present application, as shown in Figure 2 includes a dimension table Table1 and a fact table Table2. The dimension table 1 only records basic characteristic information of key values and names and other data, and the fact table records actual data.

[0026] The data query statement can be a structured query language (Structured Query Language, SQL).

[0027] In one embodiment, the way of parsing the data query statement to obtain the correlation query information can be: generating a query tree based on the statement logic of the data query statement; extracting a correlation query node in the query tree; and obtaining the correlation query information corresponding to the query node.

[0028] Specifically, the data query statement is cut according to the contained statement to obtain a plurality of nodes, and a query tree is generated based on the cut nodes. Then, an associated query node, such as an Equi-Join node, that meets an associated query condition is extracted in the query tree. Finally, associated query information corresponding to the associated query node is obtained. For example, taking an SQL statement as an example: "select * from table1, table2 where table1.key1 = table2.key2 and table1.key1 = 100", the associated query information extracted from the SQL statement is "table1.key1 = table2.key2 and table1.key1 = 100", and from the associated query information, it can be obtained that the first associated table is table1, the second associated table is table2, and the query key information is table1.key1 = 100, that is, the data with the key value of 100 is to be queried.

[0029] S120, determining a query strategy according to the query key information.

[0030] The query strategy can include a partition filtering strategy, an edge scanning and filtering strategy, and a scanning first and filtering later strategy.

[0031] In an embodiment, the manner of determining the query strategy according to the query key information can be: determining a statement format of the query key information; and determining the query strategy according to the statement format.

[0032] The statement format can include a partition column format and a non-partition column format. The partition column can be understood as data stored in a partition column according to certain attributes, for example, stored in a partition column according to time.

[0033] In an embodiment, if the statement format is the partition column format, the query strategy is the partition filtering strategy. If the statement format is the non-partition column format, a storage format of data in the second associated table is determined, and the query strategy is determined according to the storage format.

[0034] The storage format can include a column storage format and a non-column storage format. Specifically, if the storage format is the column storage format, the query strategy is the edge scanning and filtering strategy. If the storage format is the non-column storage format, the query strategy is the scanning first and filtering later strategy.

[0035] The embodiment can dynamically adjust the manner of data associated query based on the query key information to determine the query strategy.

[0036] S130, querying data in the second associated table based on the query strategy.

[0037] In this embodiment, since the real data records are in the second association table, it is necessary to query the data in the second association table.

[0038] In one embodiment, when the query strategy is the partition filtering strategy, the way of querying the data in the second association table based on the query strategy can be: filtering out the partitions in the second association table that do not match the query key information; and querying the data in the filtered partitions according to the query key information. For example, assuming that the query key information is table1.key1=100, the partitions in the second association table whose key values are not 100 are filtered out, the partition whose key value is 100 is retained, and the data is queried in the retained partition according to the query key information.

[0039] In one embodiment, if the query key information only includes the partition key value, the data in the retained partition is the target data. If the query key information also includes the related statistical information (such as min / max, etc.), the data in the retained partition is further scanned to obtain the target data according to the related statistical information.

[0040] In one embodiment, if the query strategy is the scanning-while-filtering strategy, the process of querying the data in the second association table based on the query strategy can be: sequentially scanning the columnar data in the second association table, when the data in the current column is scanned, matching the key value of the current column with the query key information; if the matching is successful, the data in the current column is determined as the target data; if the matching is unsuccessful, the current column is skipped, and the next column is continued to be scanned until the target data is scanned. The scanning-while-filtering of the data in the second association table can reduce the amount of data scanning, thereby further improving the efficiency of the association query.

[0041] In one embodiment, if the query strategy is the scanning-then-filtering strategy, the way of querying the data in the second association table based on the query strategy can be: scanning all the data in the second association table, obtaining the key values of the data; and filtering out the data whose key values do not match the query key information to obtain the target data. In this way, the amount of data that continues to participate in the association query in the later stage can be reduced.

[0042] The technical scheme of the embodiment of the present application first analyzes the data query statement to obtain the association query information; the association query information includes the query key information of the first association table; then determines the query strategy according to the query key information; and finally queries the data in the second association table based on the query strategy. The data association query method provided by the embodiment of the present application dynamically determines the query strategy according to the query key information, and filters the data source when querying the data based on the query strategy, without the need to scan all the data in the data source, which can reduce the amount of data processed during the association query and improve the efficiency of the association query.

[0043] In addition, as big data is applied more and more widely in actual production, processing components related to big data are also emerging in an endless stream. For example, Apache Spark is a unified analysis engine for large-scale data processing. Compared with the classic Apache Hadoop MapReduce distributed processing, Spark has the following advantages: compared with the cumbersome code of MapReduce, the distributed processing code in Spark is relatively simple; MapReduce can only support map and reduce methods, and it is cumbersome to develop operations such as join and group by; Spark also supports multiple iterative operations and interactive learning in different scenarios. Since there are multiple analysis engine components related to big data, such as Spark, Flink, Hive, Impala, etc., the underlying SQL engine execution process is the same when performing data correlation query operations, so the embodiments of the present application can be applied to the related scenarios of the big data SQL engine performing correlation query operations without loss of generality.

[0044] In one embodiment, it is assumed that a user submits a SQL correlation query statement: select t, p from table1 join table2 on table1.key1 = table2.key2. The correlation query is performed on the first correlation table table1 and the second correlation table table2, and the correlation fields, i.e., the correlation query information, are the key1 field of table1 and the key2 field of table2. The specific correlation query process is as follows:

[0045] Step one, first generate a query tree based on the statement logic of the data query statement, that is, the SQL engine will first generate a query tree (logical plan) for the SQL statement, then extract the correlation query node in the query tree, that is, find the Equi-Join node that meets the condition, and finally obtain the correlation query information corresponding to the query node, which includes the query key information of the first correlation table, that is, obtain the correlation field information of the first correlation table table1 in the correlation Join operation. The correlation field information is the query key information.

[0046] Step two, determine the statement format of the query key information.

[0047] The specific method is: inserting new filter node information, i.e. the association field information of the first association table table1, into the second association table table2, in the form of Filter(seq(table1.key1), table2.key2). The key1 information is used to perform data filtering operation on table2. Since there can be multiple association fields between the first association table table1 and the second association table table2, seq is used to store the related field information. The purpose is to filter some data of the second association table according to the inserted filter node information. It is judged whether the association field information key2 of the second association table table2 is in the partition column format. In this embodiment, the association field information key2 of the second association table is the same as the association field key1 of the first association table.

[0048] Step three, if the statement format is the partition column format, the query strategy is the partition filtering strategy. That is, if the association field information key2 of the second association table table2 is in the partition column format, all data in the association field information key1 of table1 need to be collected. When collecting the key1 data, an SQL needs to be executed: select key1 from table1 group by key1, and then the collected data is put into a Seq.

[0049] Step four, filtering the partitions in the second association table that do not match the query key information. That is, the query key information in the Seq is obtained to perform partition filtering according to the query key information.

[0050] Step five, performing data query in the filtered partitions according to the query key information. That is, data query is performed in the filtered partitions.

[0051] In this embodiment, partition filtering according to the association field can greatly reduce the scanning amount of data.

[0052] In one embodiment, it is assumed that a user submits an SQL association query statement: select t, p from table1 join table2 on table1.key1 = table2.key2, and performs association query on the first association table table1 and the second association table table2. The association field, i.e. the association query information, is the key1 field of the first association table table1 and the key2 field of table2. The specific association query process is as follows:

[0053] Step one, first based on the statement logic of the data query statement to generate a query tree, that is, the SQL engine will first generate a query tree (logical plan) for the SQL statement, then extract the join query node in the query tree, that is, find the Equi-Join node that meets the conditions, and finally obtain the associated query information corresponding to the query node, which includes the query key information of the first associated table, that is, the associated field information of the first associated table table1 in the associated join operation. Among them, the associated field information is the query key information.

[0054] Step two, determine the statement format of the query key information.

[0055] The specific method is: insert new filter node information, that is, the associated field information of the first associated table table1, into the second associated table table2, such as Filter(seq(table1.key1),table2.key2). To filter the data of table2 using key1 information, since there may be multiple associated fields between the first associated table table1 and the second associated table table2, seq is used to store the relevant field information. In order to achieve the purpose of filtering some data of the second associated table according to the inserted filter node information. Determine whether the associated field information key2 of the second associated table table2 is in the partition column format. In this embodiment, the associated field information key2 of the second associated table is the same as the associated field key1 of the first associated table.

[0056] Step five, if the statement format is not a partition column format, obtain the storage format of the data in the second associated table; determine the query strategy according to the storage format.

[0057] Specifically, if the associated field information segment key2 of the second associated table table2 is not in the partition column format, the min / max statistical information in the associated field information segment key1 in the first associated table table1 needs to be collected. When collecting key1 statistical information, an SQL needs to be executed: select min(key1) / max(key1)from table1, and the collected data is placed in a Seq. Then obtain the storage format of the second associated table table2 to determine the query strategy according to the storage format.

[0058] Step six, if the storage format is a columnar storage format, the query strategy is a scan-while-filtering strategy. That is, if the underlying file of the second associated table data storage is in a columnar storage format, the data source is scanned and filtered according to the relevant filter information (min / max, etc.) in Seq.

[0059] Step 7, return the result of the association query.

[0060] Step 7, return the result of the association query.

[0061] In one embodiment, Figure 3 A structural schematic diagram of a data association query device provided by an embodiment of the present application is shown in FIG. 2. As shown in the figure, the device comprises an association query information acquisition module 210, a query strategy determination module 220 and a data query module 230. Figure 3

[0062] The association query information acquisition module 210 is configured to parse the data query statement and obtain association query information. The association query information comprises query key information of the first association table.

[0063] The query strategy determination module 220 is configured to determine a query strategy according to the query key information. The query strategy comprises a partition filtering strategy, a scanning-while-filtering strategy and a scanning-then-filtering strategy.

[0064] The data query module 230 is configured to query data in the second association table based on the query strategy.

[0065] In one embodiment, the association query information acquisition module 210 is further configured to:

[0066] generate a query tree based on the statement logic of the data query statement;

[0067] extract an association query node in the query tree;

[0068] obtain association query information corresponding to the query node.

[0069] In one embodiment, the query strategy determination module 220 is further configured to:

[0070] determine a statement format of the query key information;

[0071] determine the query strategy according to the statement format.

[0072] In one embodiment, the query strategy determination module 220 is further configured to:

[0073] if the statement format is a partition column format, the query strategy is a partition filtering;

[0074] In one embodiment, the data query module 230 is further configured to:

[0075] ​Filter out the partitions in the second association table that do not match the query key information;

[0076] According to the query key information, data is queried in the filtered partitions.

[0077] In one embodiment, the query strategy determination module 220 is further configured to:

[0078] If the statement format is a non-partition column format, determine the storage format of the data in the second association table;

[0079] According to the storage format, determine the query strategy.

[0080] In one embodiment, the query strategy determination module 220 is further configured to:

[0081] If the storage format is a columnar storage format, the query strategy is scan-while-filtering;

[0082] In one embodiment, the data query module 230 is further configured to:

[0083] Scan the columnar data in the second association table in sequence, and when the data of the current column is scanned, match the key value of the current column with the query key information;

[0084] If the match is successful, determine the data of the current column as the target data;

[0085] If the match is not successful, skip the current column and continue to scan the next column until the target data is scanned.

[0086] In one embodiment, the query strategy determination module 220 is further configured to:

[0087] If the storage format is a non-columnar storage format, the query strategy is scan-then-filter;

[0088] In one embodiment, the data query module 230 is further configured to:

[0089] Scan all the data in the second association table, and obtain the key value of the data;

[0090] Filter out the data whose key value does not match the query key information, and obtain the target data.

[0091] In one embodiment, Figure 4 is a structural schematic diagram of a computer device provided by an embodiment of the present application. As shown in Figure 4 The device provided by the present application includes a processor 310 and a memory 320. The number of processors 310 in the device can be one or more, Figure 4 The number of processors 310 in the device can be one or more, Figure 4The memory 320 is taken as an example. The processor 310 and the memory 320 of the device can be connected through a bus or other means, Figure 4 The device is taken as a computer device in the embodiment.

[0092] The memory 320 is a computer readable storage medium, which can be configured to store software programs, computer executable programs and modules, such as program instructions / modules of the device according to any embodiment of the present application (for example, the encoding module and the first sending module in the data transmission device). The memory 320 can include a program storage area and a data storage area, wherein the program storage area can store an operating system and application programs required by at least one function; the data storage area can store data created according to the use of the device, etc. In addition, the memory 320 can include a high-speed random access memory, and can also include a non-volatile memory, such as at least one magnetic disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some examples, the memory 320 can further include a memory remotely arranged with respect to the processor 310, which can be connected to the device through a network. Examples of the above network include but are not limited to the Internet, an intranet, a local area network, a mobile communication network, and a combination thereof.

[0093] The device provided above can be configured to execute the method for associated query of data provided by any embodiment above, and has corresponding functions and effects.

[0094] The program stored in the memory 320 can be program instructions / modules corresponding to the method for associated query of data provided by the embodiments of the present application. The processor 310 executes the software programs, instructions and modules stored in the memory 320, so as to execute one or more functions of the computer device and data processing, that is, to implement the method for associated query of data in the above method embodiment. It can be understood that when the above device is a receiving end, the method for associated query of data provided by any embodiment of the present application can be executed, and has corresponding functions and effects.

[0095] The embodiments of the present application also provide a storage medium containing computer executable instructions, which are used to execute a method for associated query of data when executed by a computer processor. The method includes: parsing a data query statement to obtain associated query information; the associated query information includes query key information of a first associated table; determining a query strategy according to the query key information; and querying data in a second associated table based on the query strategy.

[0096] Those skilled in the art should understand that the term user equipment covers any suitable type of wireless user equipment, such as a mobile phone, a portable data processing apparatus, a portable web browser or a vehicle-mounted mobile station.

[0097] In general, the various embodiments of the application can be implemented in hardware or special purpose circuits, software, logic or any combination thereof. For example, some aspects can be implemented in hardware, while other aspects can be implemented in

[0098] Embodiments of the application can be implemented by computer program instructions stored on a machine-readable medium executed by a data processing apparatus of a mobile device, for example within a processor entity, or by hardware, or by a combination of software and hardware. The computer program instructions can be in the form of assembly language, Instruction Set Architecture (ISA) instructions, machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, or in any combination of one or more programming languages, executed on one or more general purpose processors.

[0099] The block diagrams of any logical flow of the application in the accompanying drawings can represent program steps, or can represent interconnecting logic circuit, modules, and functions, or can represent a combination of program steps and logic circuit, modules, and functions. The computer program can be stored on a memory. The memory can be of any type suitable to the local technical environment and can be implemented using any suitable data storage technology, such as, but not limited to, random access memory (RAM), read-only memory (ROM), optical storage devices, and the like. The computer readable media can include non-transitory storage media. The data processing apparatus can be of any type suitable to the local technical environment, and can include one or more of general purpose computers, special purpose computers, microprocessors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field- programmable gate arrays (FPGAs), and processors of multi-core processor architectures, as examples.

[0100] The above-described embodiments of the application are merely descriptive of its

[0101] Embodiments of the application can be implemented by computer program instructions on a mobile device's data processor, for example in the processor entity, or by hardware, or by a combination of software and hardware. Computer program instructions can be in the form of assemblies, instructions set architecture (ISA), machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, or in any combination of one or more programming languages, executed on one or more of a set of volatile and / or non-volatile computer memory modules.

[0102] By way of example, and not limitation, such computer program instructions can be loaded onto a computer, other programmable apparatuses, or computer processing apparatuses to produce a machine, such that the computer processing apparatus implements a procedure that operates on input data to generate output. The computer program instructions, when executed on the computer processing apparatus, enable the computing system(s) to implement the operations described herein. In some embodiments, for example, the computer program instructions which enable a computer processing apparatus to operate in one or more particular modes are stored, in whole or in part, in a memory of the computer processing apparatus.

Claims

1. A data association query method, characterized in that: include: Parse the data query statement to obtain related query information; The associated query information includes query key information of the first associated table; Determine a query strategy based on the query key information; wherein the query strategy includes: a partition filtering strategy, a scanning and filtering strategy, and a scanning-first-then-filtering strategy; Querying the data in the second association table based on the query strategy to obtain target data; The step of determining a query strategy based on the query key information includes: Determine the sentence format of the query key information; If the statement format is partition column format, the query strategy is partition filtering strategy; If the statement format is a non-partition column format, obtaining the storage format of the data in the second association table; If the storage format is columnar, the query strategy is a scanning and filtering strategy; If the storage format is non-columnar, the query strategy is to scan first and then filter.

2. The method according to claim 1, characterized in that Parse the data query statement to obtain related query information, including: Generate a query tree based on the statement logic of the data query statement; extracting associated query nodes in the query tree; Obtain associated query information corresponding to the query node.

3. The method according to claim 1, characterized in that In the case where the query strategy is a partition filtering strategy, querying data in the second association table based on the query strategy of the first association table includes: Filtering partitions in the second association table that do not match the query key information; Data query is performed in the filtered partitions according to the query key information.

4. The method according to claim 1, wherein In a case where the query strategy is a scanning and filtering strategy, querying the data in the second association table based on the query strategy includes: Scanning the column data in the second association table in sequence, and when data in the current column is scanned, matching the key value of the current column with the query key information; If the match is successful, the data in the current column is determined as the target data; If the match is unsuccessful, skip the current column and continue scanning the next column until the target data is found.

5. The method according to claim 1, wherein In a case where the query strategy is a scan-first-filter-later strategy, querying the data in the second association table based on the query strategy includes: Scan all data in the second associated table and obtain the key value of the data; Filter data whose key values ​​do not match the query key information to obtain target data.

6. A data association query device, characterized in that: include: The associated query information acquisition module is used to parse the data query statement and obtain the associated query information; The associated query information includes query key information of the first associated table; A query strategy determination module, configured to determine a query strategy based on the query key information; wherein the query strategy includes: a partition filtering strategy, a scanning and filtering strategy, and a scanning-first-then-filtering strategy; A data query module, configured to query the data in the second association table based on the query strategy; The query strategy determination module is further configured to: Determining the sentence format of the query key information; If the statement format is partition column format, the query strategy is partition filtering strategy; If the statement format is a non-partition column format, obtaining the storage format of the data in the second association table; If the storage format is columnar, the query strategy is a scanning and filtering strategy; If the storage format is non-columnar, the query strategy is to scan first and then filter.

7. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the control method for associated query of data as described in any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the data association query method according to any one of claims 1 to 5 is implemented.

Images