CN114281819A

CN114281819A - Data query method, device, equipment and storage medium

Info

Publication number: CN114281819A
Application number: CN202111401806.8A
Authority: CN
Inventors: 孙娟; 孙钰; 赵雪寒; 张溯; 吴国防; 杨明
Original assignee: China United Network Communications Group Co Ltd
Current assignee: China United Network Communications Group Co Ltd
Priority date: 2021-11-19
Filing date: 2021-11-19
Publication date: 2022-04-05

Abstract

The application provides a data query method, a data query device, data query equipment and a storage medium. The method comprises the following steps: generating a first query statement corresponding to a first query task according to the first query task from a client; if it is determined that the cache does not have the first query result of the first query task according to the first query statement, determining whether the first query task exists in a preprocessing task information configuration table in a first database according to the first query statement, wherein the preprocessing task information configuration table records at least one preprocessed query task; if the first query task exists in the preprocessing task information configuration table, the first query statement is used for acquiring the first query result from a preprocessing result table corresponding to the first query task; and sending the first query result to the client. The method can improve the query efficiency.

Description

Data query method, device, equipment and storage medium

Technical Field

The present application relates to the field of data query technologies, and in particular, to a data query method, apparatus, device, and storage medium.

Background

The data query is a one-door query technology which queries specified information from a database according to user requirements and a certain algorithm and feeds the information back to a user. Data query systems are functionally divided and typically include a user layer, a service layer, and a data store layer. The user layer comprises a client side positioned at a user side and is used for receiving a query request of a user and providing queried data for the user; the service layer is used for executing query operation of data; the data storage layer is used for storing data.

In the prior art, after a user layer of a data query system receives a query request from a client, a service layer converts the request into an SQL statement corresponding to the request, so as to search a database in a data storage layer for a query result required by the request. The data storage layer is provided with a MySQL database and a hive database, data occupying a small storage space are stored in the MySQL database, data occupying a large storage space are stored in the hive database, and therefore query can be conducted according to the size of the storage space occupied by the data.

With the increase and accumulation of mass data, more and more high-time-consumption data query tasks are performed, which results in that the data query system spends longer and longer time for performing the data query task, and even the phenomenon of query timeout occurs, thereby resulting in that the query efficiency of the data query system is lower and lower.

Disclosure of Invention

The application provides a data query method, a data query device, data query equipment and a storage medium, which are used for solving the problem that the query efficiency of a data query system is lower and lower.

In a first aspect, the present application provides a data query method, including:

generating a first query statement corresponding to a first query task according to the first query task from a client;

if it is determined that the cache does not have the first query result of the first query task according to the first query statement, determining whether the first query task exists in a preprocessing task information configuration table in a first database according to the first query statement, wherein the preprocessing task information configuration table records at least one preprocessed query task;

if the first query task exists in the preprocessing task information configuration table, the first query statement is used for acquiring the first query result from a preprocessing result table corresponding to the first query task;

and sending the first query result to the client.

Optionally, after determining whether the first query task exists in the preprocessing task information configuration table in the first database, the method further includes:

and if the first query task does not exist in the preprocessing task information configuration table, using the first query statement to acquire the first query result from a source data table.

Optionally, the source data table is located in a second database.

Optionally, after generating the first query statement corresponding to the first query task, the method further includes:

and if the first query result is determined to be in the cache according to the first query statement, using the first query statement to obtain the first query result from the cache.

Optionally, the generating a first query statement corresponding to a first query task according to the first query task from a client includes:

and splicing and converting the plurality of query conditions included in the first query task to obtain the first query statement.

Optionally, the method further comprises:

and storing the first query result to the cache according to the first query statement.

Optionally, the method further comprises:

storing first query information corresponding to the first query task to a historical query information record table of the first database, wherein the first query information comprises: the first query statement and the query time sub-information of the first query task.

Optionally, before determining that there is no first query result of the first query task in the cache according to the first query statement, the method further includes:

determining whether the historical query information record table has the first query task;

if the historical query information record table is determined to have the first query task, determining whether a first query result of the first query task exists in the cache or not according to the first query statement;

alternatively, the first and second electrodes may be,

if the historical query information record table is determined to have no first query task, the first query statement is used to obtain the first query result from a source data table;

and sending the first query result to the client.

Optionally, the method further comprises:

screening at least one second query task to be preprocessed from the historical query information record table according to a preset first screening condition; the preset first screening condition comprises the following steps: the accumulated query times of the query tasks in the first historical preset time are greater than or equal to the preset times, and/or the single query time is greater than or equal to the preset time;

adding the at least one second query task to be preprocessed to the preprocessing task information configuration table;

and preprocessing the second query task in the preprocessing task information configuration table.

Optionally, the preprocessing the second query task in the preprocessing task information configuration table includes:

aiming at any second query task in the preprocessing task information configuration table, determining whether a parent task corresponding to the second query task is executed successfully or not according to a parent task state table of the first database; the parent task state table records execution information of at least one parent task, and the parent task is used for updating data in a source data table to which a query result corresponding to the second query task belongs;

if the execution of the parent task corresponding to the second query task is determined to be successful, acquiring a second query result of the second query task from a data table corresponding to the parent task by using a query statement corresponding to the second query task;

taking the second query result as a preprocessing result of the second query task, and generating a preprocessing result table corresponding to the second query task;

and storing a preprocessing result table corresponding to the second query task.

Optionally, the storing the pre-processing result table corresponding to the second query task includes:

and storing the preprocessing result table corresponding to the second query task into a second database.

Optionally, after storing the preprocessing result table corresponding to the second query task, the method further includes:

adding execution information of the second query task to a preprocessing task state table in a first database, the execution information comprising: the identifier of the preprocessing result table corresponding to the second query task, the storage location of the preprocessing result table corresponding to the second query task, the latest update time of the preprocessing result of the second query task, and whether to continue updating.

Optionally, the method further comprises:

screening out a third query task needing to continuously execute preprocessing according to the preprocessing task state table; the third query task meets a preset second screening condition, and a parent task corresponding to the third query task is updated regularly; the preset second screening condition comprises the following steps: the accumulated query times of the historical query tasks in the second historical preset time length are greater than or equal to the preset times;

acquiring the next preprocessing time of the third query task;

when the next preprocessing time is reached, preprocessing the third query task, and updating a preprocessing result table corresponding to the third query task according to a preprocessing result of the third query task;

and updating the execution information of the third query task in the preprocessing task state table.

Optionally, the method further comprises:

deleting a fourth query task which does not execute preprocessing any more from the preprocessing task state table; and the fourth query task does not meet the preset second screening condition.

In a second aspect, the present application provides a data query apparatus, including:

the first processing module is used for generating a first query statement corresponding to a first query task according to the first query task from a client;

the second processing module is used for determining whether a first query task exists in a preprocessing task information configuration table in the first database according to a first query statement after determining that the cache does not have the first query result of the first query task according to the first query statement, and acquiring the first query result from a preprocessing result table corresponding to the first query task by using the first query statement when the first query task exists in the preprocessing task information configuration table; the preprocessing task information configuration table records at least one preprocessing query task;

and the sending module is used for sending the first query result to the client.

Optionally, the second processing module is further configured to, after determining whether the first query task exists in a pre-processing task information configuration table in a first database, use the first query statement to obtain the first query result from a source data table when the first query task does not exist in the pre-processing task information configuration table.

Optionally, the source data table is located in a second database.

Optionally, the second processing module is further configured to, after the first processing module generates a first query statement corresponding to the first query task, use the first query statement to obtain the first query result from the cache when it is determined that the first query result exists in the cache according to the first query statement.

Optionally, the first processing module is specifically configured to splice and convert the plurality of query conditions included in the first query task to obtain the first query statement.

Optionally, the apparatus further comprises: and the storage module is used for storing the first query result to the cache according to the first query statement.

Optionally, the storage module is configured to store first query information corresponding to the first query task in a historical query information record table of the first database, where the first query information includes: the first query statement and the query time sub-information of the first query task.

Optionally, the second processing module is further configured to determine whether the historical query information record table has the first query task before determining that the cache does not have the first query result of the first query task according to the first query statement, and determine whether the cache has the first query result of the first query task according to the first query statement when determining that the historical query information record table has the first query task; or when the historical query information record table is determined to have no first query task, the first query statement is used for acquiring the first query result from a source data table.

Optionally, the second processing module is further configured to screen at least one second query task to be preprocessed from the historical query information record table according to a preset first screening condition; adding the at least one second query task to be preprocessed to the preprocessing task information configuration table; preprocessing a second query task in the preprocessing task information configuration table; wherein the preset first screening condition comprises: the accumulated query times of the historical query tasks in the first historical preset time are larger than or equal to the preset times, and/or the single query time is larger than or equal to the preset time.

Optionally, the second processing module is specifically configured to determine, for any second query task in the pre-processing task information configuration table, whether a parent task corresponding to the second query task is successfully executed according to the parent task state table of the first database; if the execution of the parent task corresponding to the second query task is determined to be successful, acquiring a second query result of the second query task from a data table corresponding to the parent task by using a query statement corresponding to the second query task; taking the second query result as a preprocessing result of the second query task, and generating and storing a preprocessing result table corresponding to the second query task; the parent task state table records execution information of at least one parent task, and the parent task is used for updating data in a source data table to which a query result corresponding to the second query task belongs.

The storage module is further configured to store a preprocessing result table corresponding to the second query task.

Optionally, the storage module is further configured to store the preprocessing result table corresponding to the second query task in a second database.

Optionally, the second processing module is further configured to add, after the storage module stores the preprocessing result table corresponding to the second query task, execution information of the second query task to a preprocessing task state table in a first database, where the execution information includes: the identifier of the preprocessing result table corresponding to the second query task, the storage location of the preprocessing result table corresponding to the second query task, the latest update time of the preprocessing result of the second query task, and whether to continue updating.

Optionally, the second processing module is further configured to screen out a third query task that needs to continue to execute the preprocessing according to the preprocessing task state table; acquiring the next preprocessing time of the third query task; when the next preprocessing time is reached, preprocessing the third query task, and updating a preprocessing result table corresponding to the third query task according to a preprocessing result of the third query task; updating the execution information of the third query task in the preprocessing task state table; the third query task meets preset second screening conditions, and a parent task corresponding to the third query task is updated regularly, wherein the preset second screening conditions include: and the accumulated query times of the query task in the second historical preset time length are greater than or equal to the preset times.

Optionally, the second processing module is further configured to delete, from the preprocessing task state table, a fourth query task that is no longer preprocessed; and the fourth query task does not meet the preset second screening condition.

In a third aspect, an embodiment of the present application provides an electronic device, including:

the memory stores computer-executable instructions;

the processor executes computer-executable instructions stored by the memory to implement any of the method steps described above.

In a fourth aspect, embodiments of the present application provide a computer-readable storage medium having stored therein computer-executable instructions, which when executed by a processor, are configured to implement any of the method steps described above.

According to the data query method, the data query device, the data query equipment and the data query storage medium, at least one query task is preprocessed, and the result corresponding to the query task is obtained, so that the result can be directly returned to the client side when the query task which is the same as the query task and is sent by the client side is received, the query result does not need to be obtained in a mode of executing the query task, and the query efficiency is improved. When the query task is a query task consuming longer time, the query efficiency of the data query system for querying a task consuming high time can be improved.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and together with the description, serve to explain the principles of the application.

FIG. 1 is a schematic diagram of a data query system in the prior art;

FIG. 2 is a schematic structural diagram of a data query system according to an embodiment of the present application;

fig. 3 is a schematic flowchart of a data query method according to an embodiment of the present application;

fig. 4 is a schematic flowchart of another method for querying data according to an embodiment of the present application;

fig. 5 is a schematic flowchart of another method for querying data according to an embodiment of the present application;

FIG. 6 is a schematic structural diagram of a data query device according to an embodiment of the present application;

fig. 7 is a schematic structural diagram of an electronic device according to an embodiment of the present application.

With the above figures, there are shown specific embodiments of the present application, which will be described in more detail below. These drawings and written description are not intended to limit the scope of the inventive concepts in any manner, but rather to illustrate the inventive concepts to those skilled in the art by reference to specific embodiments.

Detailed Description

Reference will now be made in detail to the exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, like numbers in different drawings represent the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present application, as detailed in the appended claims.

The data query system is a system which collects information from a database by using a specific computer program according to a certain strategy, provides query service for a user after organizing and processing the information, and displays the queried related information to the user.

Fig. 1 is a schematic structural diagram of a data query system in the prior art, and as shown in fig. 1, the data query system in the prior art includes: a user layer, a service layer and a data storage layer.

The query task includes a query condition, for example, a data query system for storing user data of the operator, and the query condition may be, for example: location, time, terminal equipment, area code, internet surfing time, call times, history information record list, call record and the like.

The service layer is usually implemented by a server cluster, and is configured to convert the received query task into a query statement, and after determining that there is no query result of the query task in the cache by using the query statement, query the data storage layer by using the query statement to obtain a query result, and feed the query result back to the client. Wherein the translated query statement matches a query statement supported by the database. Such as SQL statements.

The service layer is also provided with a cache for storing the query results corresponding to the query tasks which are executed recently, and the specific storage quantity is related to the size of the cache and the size of the query results corresponding to the query tasks.

The data storage layer is provided with a MySQL database and a hive database and is used for storing a source data table of the data query system. Each source data table includes at least one data. The data in the source data tables are the data stored by the data query system. The MySQL database has smaller storage capacity and is used for storing data occupying smaller storage space, and the hive database has larger storage capacity and is used for storing data occupying larger storage space.

Currently, when a service layer executes data query, based on a received query task, when a query result is not found in a cache, the service layer also searches a data storage layer and sends the query result to a client. However, with the arrival of the big data era, the data generation speed is increased explosively, more and more time-consuming data query tasks are performed, the query time consumption is longer and longer by adopting the data query mode, and even the query is overtime, so that the query efficiency of the data query system is lower and lower.

In view of the above, the present application provides a data query method, which may pre-process at least one query task to obtain a query result of the at least one query task. Therefore, when the query task from the client is received, the query result can be directly returned to the client, a database does not need to be queried to obtain the query result, and the data query efficiency is improved. The pre-processing query task may be, for example, a highly time-consuming query task, a high-frequency query task, a query task determined based on a user's demand, or the like.

For ease of understanding, the following description will first exemplify an application scenario of the present application:

fig. 2 is a schematic structural diagram of the data query system provided in the present application, and as shown in fig. 2, the database may include: a user layer, a service layer and a data storage layer.

In the application, the user layer refers to a client located at a user side, and is used for receiving a query task triggered by a user and sending the query task to the service layer. The query task is a query task comprising a single query condition, or a query task comprising a plurality of query conditions.

The difference from the conventional data query system shown in fig. 1 is that the data storage layer stores a preprocessing task information configuration table, a preprocessing result table, and a source data table. At least one preprocessed query task is recorded in the preprocessing task information configuration table. The preprocessing result table is used for storing the query result of at least one preprocessing query task. The pre-processed query task is a pre-processed query task, that is, a query task that has been executed in advance when the query task sent by the client is not received.

The data store layer may include one or more databases. When the data storage layer comprises a plurality of databases, the preprocessing task information configuration table and the preprocessing result table can be stored in the first database, or the preprocessing task information configuration table is stored in the first database, and the preprocessing result table is stored in the second database. The second database referred to herein is any database other than the first database.

It should be understood that the data store layer including the plurality of databases may also have a function of storing the source data tables, or that the first database is used only for storing the configuration tables and the remaining databases have a function of storing the source data tables. When the second database stores both the preprocessing result table and the source data table, the storage area of the preprocessing result table and the storage area of the source data table are independent of each other, i.e., located in different partitions of the second database.

Fig. 2 is a schematic diagram illustrating an example in which the data storage layer includes two databases (a first database and a second database, respectively). When the data store layer comprises two databases, the first database may be a MySQL database and the second database may be, for example, a ClickHouse database. The MySQL database has small storage capacity and stable performance, and stores data in a form of a table, so that the table occupying a small storage space is convenient to store and query, such as a preprocessing task information configuration table. The ClickHouse database has larger data storage capacity and higher query speed, and the preprocessing result table and the source data table which occupy larger storage space are stored in the ClickHouse database, so that the data query efficiency can be improved.

After receiving the query task from the user layer, the service layer may determine whether there is a first query task in the pre-processing task information configuration table in the first database based on the query statement when it is determined that there is no query result in the cache according to the query statement corresponding to the query task. When the query task exists, the service layer can directly obtain the query result from the preprocessing result table corresponding to the query task without querying a database, and the query efficiency is improved.

It should be understood that the functions of the service layer may be implemented by one server or by any server in a server cluster.

Alternatively, the processing operation for the service layer may be implemented by a server or a server cluster of the service layer. Alternatively, a server or a server cluster is also provided in the data storage layer, and the data is transferred to the server or the server cluster of the data storage layer through the server or the server cluster of the service layer, so that the data is queried.

The following describes the technical solutions of the present application and how to solve the above technical problems with specific embodiments. The following several specific embodiments may be combined with each other, and details of the same or similar concepts or processes may not be repeated in some embodiments. Embodiments of the present application will be described below with reference to the accompanying drawings.

Fig. 3 is a schematic flowchart of a data query method provided in the present application. The execution subject of the method is a server of a service layer, as shown in fig. 3, the method includes the following steps:

s301, according to the first query task from the client, generating a first query statement corresponding to the first query task.

In one possible implementation, the first query task may be composed of a single query condition, and the service layer converts the first query task into a corresponding first query statement.

In another possible implementation manner, the first query task may be composed of a plurality of query conditions, and the service layer analyzes and concatenates the first query task into the corresponding first query statement. Each query condition in the first query task is converted into a query statement, and then the query statements are spliced according to a preset sequence or format to obtain a first query statement. The first query statement obtained through splicing conversion can simultaneously query a plurality of options, so that the query condition can be selected independently and flexibly based on the query requirement of a user, and the query flexibility is improved.

The query statement referred to in the present application is specifically determined by the query language supported by the database of the data query system, and may be, for example, an SQL statement.

S302, whether a first query result of the first query task exists in the cache is determined according to the first query statement.

According to a possible implementation mode, when the service layer sequentially stores the historical query results corresponding to the historical query tasks into the cache according to the time sequence, the historical query result corresponding to each historical query task can be used as a cache file, and the historical query statement is mapped into the file name of the file through a hash algorithm according to the historical query statement corresponding to the historical query tasks, so that the query results of different historical query tasks are stored. Therefore, when judging whether the first query result of the first query task exists in the cache, the above method of storing in the cache may also be adopted, and the first query statement is converted into the corresponding file name through the hash algorithm and then compared with the file names of the historical query tasks in the cache, so as to determine whether the first query result of the first query task exists in the cache.

If the same file name as the file name corresponding to the first query statement does not exist in the cache, it is indicated that there is no first query result of the first query task in the cache, which indicates that the first query task may not have been queried before, so there is no first query result corresponding to the first query task in the cache, or the first query task may have been queried before, but since the data in the cache is updated, that is, the first query task is covered or deleted, there is no first query result corresponding to the first query task in the cache, at this time, a search needs to be performed in the data storage layer, that is, step S303 is performed.

Optionally, if a file name identical to the file name corresponding to the first query statement exists in the cache, it indicates that the first query result of the first query task exists in the cache, that is, the first query result indicates that the query task is queried before and is recorded in the cache, and at this time, the query result may be directly obtained from the cache, so as to improve the data query efficiency, that is, step S307 is executed.

S303, determining whether a first query task exists in the preprocessing task information configuration table in the first database according to the first query statement.

It should be understood that the pre-processing task information configuration table stores query information corresponding to the pre-processing query task, where the query information may include: query statements of the pre-processed query task, and query time sub-information of the pre-processed query task.

It should be appreciated that the query time sub-information of the pre-processed query task may include: the query task starts and ends the query time to record the time required for executing the query task. Alternatively, the query time sub-information of the preprocessed query task may include: the length of time it takes to perform the query task.

For example, taking the query time sub-information of the preprocessed query task as the starting query time and the ending query time of the query result query task, the preprocessing task information configuration table may be shown in the following table 1, for example:

TABLE 1

Query statement	Time to start query	End query time
			Query statement 1	9:00	9:02
Query statement 2	10:10	10:15
			Query statement 3	19:00	19:03
…	…	…

It should be understood that the query statement 1, the query statement 2, and the query statement 3 represent three different query tasks, the pre-processing task information configuration table may store query information of all pre-processing query tasks, and the pre-processing task information configuration table records at least one pre-processing query task.

Optionally, the query information corresponding to the preprocessed query task stored in the preprocessed task information configuration table includes, but is not limited to, a query statement of the preprocessed query task, query time sub-information of the preprocessed query task, and may further include other information, for example, a result amount of a query result corresponding to the query task, a group by field, and the like. The result quantity refers to the number of query results which can be queried by the query task, and the group by field refers to the query condition in the query statement.

Optionally, the service layer may compare the first query statement corresponding to the first query task with the query statements in the preprocessing task information configuration table one by one to determine whether the first query task exists.

If it is determined that the first query task exists in the pre-processing task information configuration table in the first database according to the first query statement, indicating that the first query task is a pre-processing query task, the query result of the first query task may be directly obtained, that is, step S304 is performed.

Optionally, it is determined according to the first query statement that the first query task does not exist in the pre-processing task information configuration table in the first database, which indicates that the first query task is not pre-processed, and it is required to perform query in the source data table storing all data information, that is, step S306 is performed.

S304, a first query statement is used for obtaining a first query result from the preprocessing result table corresponding to the first query task.

Illustratively, the pretreatment results table may be, for example, as shown in table 2 below:

TABLE 2

Query statement	Results of pretreatment
		Query statement 1	Pretreatment results 1
Query statement 2	Pretreatment results 2
		Query statement 3	Pretreatment results 3
…	…

It should be understood that the query statement 1, the query statement 2, and the query statement 3 represent three different query tasks, the pre-processing result table may store query results of all query tasks that need to be pre-processed, and at least one pre-processed query task is recorded in the pre-processing result table.

Alternatively, when the first query result is obtained from the pre-processing result table, the first query result corresponding to the first query statement may be obtained by successively comparing the first query statement with the query statements of the pre-processing result table.

S305, sending the first query result to the client.

Optionally, after the first query result is obtained, the first query result may be stored in a cache according to the first query statement, so that when the same query task exists subsequently, the query result may be directly obtained in the cache, thereby saving query time and further improving query efficiency.

S306, using the first query statement, obtaining a first query result from the source data table.

After step S306 is executed, step S305 is executed.

It should be understood that the source data table is used to store all of the data stored by the data query system.

For how to obtain the first query result from the source data table by using the first query statement, see in particular the implementation manner of obtaining the query result from the source data table by using the query statement in the prior art. For example, the query condition included in the query statement corresponding to the first query task may be compared with the original data of the data in the source data table, so as to screen out the data satisfying the query condition as the query result.

S307, using the first query statement, obtaining a first query result from the cache.

Optionally, after determining that the cache has the first query result of the first query task according to the first query statement, the file may be directly read from the cache according to the file name, so as to read the first query result from the file. After step S306 is executed, step S305 is executed.

Optionally, if the first query result is obtained from the preprocessing result table or obtained from the source data table by querying, after the first query result is obtained, the first query result may be further stored in the cache according to the first query statement, so that when the same query task exists subsequently, the query result may be directly obtained in the cache, thereby saving the query time and further improving the query efficiency.

Optionally, the data storage layer of the data query system may further store a historical query information recording table, where the table is used to record query information of a recently received historical query task from the client. The number of the historical query tasks recorded in the historical query information record table is related to the size of the historical query information record table.

The query information of the historical query task may include: historical query statements corresponding to the historical query tasks and query time sub-information of the historical query tasks. When the data storage layer includes a plurality of databases, the history query information recording table may be stored in the first database, or may be stored in the second database, which is not limited herein.

Optionally, after the first query result of the first query task is obtained in the method flow, the first query information corresponding to the first query task may be stored in the historical query information record table of the first database, so as to count the query times of the historical query task. The specific execution time of the historical query information record table for storing the first query information corresponding to the first query task in the first database is not limited, and the specific execution time can be executed after the first query result is obtained.

In this implementation manner, optionally, before executing step S302, the following steps may also be included:

it is determined whether the historical query information record table has a first query task. If it is determined that the historical query information record table has the first query task, it indicates that the first query result corresponding to the first query task is searched before the query, and may be stored in a cache or a pre-processing result table, so that step S302 may be continuously performed. If it is determined that the historical query information record table has no first query task, it indicates that the first query result corresponding to the first query task may not be searched before the query, and may not exist in the cache or the pre-processing result table, so that the step of querying the query result of the query task from the cache and pre-processing result table may be skipped, and step S306 may be directly performed to perform the search from the source database, so as to improve the query efficiency.

For example, the first query statement may be successively compared with query statements of the historical query information log to determine whether there is a first query task in the historical query information log.

According to the method and the device, at least one query task is preprocessed, and the result corresponding to the query task is obtained, so that the result can be directly returned to the client when the query task which is sent by the client and is the same as the query task is received, the query result does not need to be obtained in a mode of executing the query task, and the query efficiency is improved. When the query task is a query task consuming longer time, the query efficiency of the data query system for querying a task consuming high time can be improved.

The foregoing describes how to use the preprocessing task information configuration table, and the following description is directed to how to generate the preprocessing task information configuration table.

Fig. 4 is a schematic flowchart of another method for querying data provided in the present application, and as shown in fig. 4, the method includes the following steps:

s401, screening at least one second query task to be preprocessed from the historical query information record table according to a preset first screening condition.

Optionally, the preset first filtering condition may be specifically determined according to a requirement of a user.

Taking the preprocessed query task as the high-frequency task as an example, the presetting of the first filtering condition may include: the accumulated query times of the historical query tasks in the first historical preset time are greater than or equal to the preset times. It should be understood that the accumulated query times within the first historical preset time duration refers to the number of query information with the same query statement stored in the historical query information record table, where the first historical preset time duration may be set according to a requirement, and is not limited herein.

Taking the preprocessed query task as a high time-consuming task as an example, the presetting of the first filtering condition may include: the single query duration of the historical query task is greater than or equal to the preset duration.

Taking the preprocessed query task as a high-frequency and high-time-consuming task as an example, the presetting of the first screening condition may further include: the accumulated query times of the historical query tasks in the first historical preset time are larger than or equal to the preset times, and the single query time is larger than or equal to the preset time.

Optionally, the presetting of the first screening condition may further include: and (4) measuring the result.

It should be appreciated that the second query task is the query task that is screened out and needs to be preprocessed. After the preset first screening condition is met, the query tasks can be preprocessed so as to be stored in a preprocessing result table.

S402, adding at least one second query task to be preprocessed to a preprocessing task information configuration table.

Alternatively, the query tasks in the preprocessing task information configuration table may be obtained by filtering from the historical query tasks recorded in the historical query information recording table according to a preset first filtering condition.

For example, the adding of the second query task to the preprocessing task information configuration table may be adding the query information of the second query task in the historical query information record table to the preprocessing task information configuration table, so as to subsequently determine whether the preprocessing query task needs to continue the preprocessing operation.

It should be understood that, for the second query task, the query information of the second query task in the preprocessing task information configuration table and the query information of the second query task recorded in the historical query information record table may be the same, or a subset or a whole set of the information in the historical query information record table. For example, for the same query task, the historical query information record table includes query information of the query task, which includes a query statement and query time sub-information, and the preprocessing task information configuration table may include the query statement of the query task, or include the query statement and the query time sub-information.

And S403, aiming at any second query task in the preprocessing task information configuration table, determining that the parent task of the second query task is successfully executed according to the parent task state table of the first database.

It should be understood that a parent task refers to a task in the data query system for periodically updating the source data table. The parent task state table records the execution condition of the parent task in the data query system. The execution condition may reflect whether the parent task was executed successfully, i.e., whether the update operation on the source data table was completed.

Optionally, the parent task state table may include: parent task execution time and execution state. The execution time of the parent task further includes a start time and an end time of the execution of the parent task, and the execution state may include the start of the execution and the end of the execution during the execution. When the data storage layer includes a plurality of databases, the parent task state table may be stored in a first database, or may be stored in a second database, which is not limited herein.

It should be understood that, if the parent task corresponding to the second query task is not successfully executed, the execution information is stored in the parent task state table, and the service layer sends alarm information to the terminal device held by the staff responsible for maintaining the data query system according to the execution state information in the parent task state table, where the alarm information is used to remind the staff to query the reason why the parent task is not successfully executed.

S404, using the query statement corresponding to the second query task, and obtaining a second query result of the second query task from the data table corresponding to the parent task.

And the data table corresponding to the parent task is the updated source data table of the parent task.

For how to obtain the second query result from the source data table updated by the parent task using the second query statement, see in particular the implementation manner in the prior art that uses the query statement to obtain the query result from the source data table. For example, the query condition included in the query statement corresponding to the second query task may be compared with the original data of the data in the source data table updated by the parent task, so as to filter out the data meeting the query condition as the query result.

S405, taking the second query result as a preprocessing result of the second query task, and generating and storing a preprocessing result table corresponding to the second query task.

It should be understood that the generation of the pre-processing result table corresponding to the second query task means writing the pre-processing result into the pre-processing result table, that is, writing the query statement and the pre-processing result corresponding to the second query task into the pre-processing result table, so as to obtain the query result in the pre-processing result table.

S406, adding the execution information of the second query task to the preprocessing task state table in the first database.

It should be appreciated that the pre-processing task state table records execution information for pre-processing query tasks that have completed pre-processing operations. Wherein the execution information includes: the identification of the preprocessing result table corresponding to the preprocessed query task, the storage location of the preprocessing result table corresponding to the preprocessed query task, the latest update time of the preprocessing result of the preprocessed query task, whether the preprocessing result table needs to be updated continuously, and the like.

The adding of the execution information of the second query task to the preprocessing task state table in the first database may be adding, to the preprocessing task state table, the execution information including an identifier of the preprocessing result table corresponding to the second query task, a storage location of the preprocessing result table corresponding to the second query task, a latest update time of the preprocessing result of the second query task, and whether the preprocessing result table needs to be updated continuously.

Optionally, there may be a source data table that needs to be updated periodically in the data query system, so to ensure accuracy of the query result of the preprocessed query task, after the preprocessing of the query task in the preprocessing task state table is completed, the following operations may be performed.

Fig. 5 is a schematic flowchart of another method for querying data provided in the present application, and as shown in fig. 5, the method includes the following steps:

s501, screening out a third query task needing to continuously execute preprocessing according to the preprocessing task state table.

It should be understood that the third query task is a query task that needs to update the query result along with the data updated regularly.

For example, the screened third query task may satisfy the preset second screening condition, and the parent task corresponding to the third query task is updated regularly. Wherein, the presetting of the second screening condition comprises: and the accumulated query times of the query task in the second historical preset time length are greater than or equal to the preset times.

Optionally, a specific implementation of the screening of the third query task may be as follows: and counting the total times of the second query task executed within the second historical preset time length in the historical query information records according to the identifier of the preprocessing result table corresponding to the second query task in the preprocessing task state table. That is, when the accumulated query number is greater than or equal to the preset number within the second historical preset time, the query task may be used as a candidate third query task.

It should be understood that the second historical preset time period here may be equal to or longer than the first historical preset time period, and may be determined according to actual needs, and is not limited herein.

And then, determining whether the parent task corresponding to the candidate third query task needs to be executed regularly based on the parent task state query table, if the parent task needs to be executed regularly, indicating that the update operation of the parent task for the source data table needs to be followed, and updating the query result of the candidate third query task regularly, so that the candidate third query task is used as the third query task. If the query result does not need to be executed regularly, the query result of the candidate third query task does not need to be updated.

And S502, acquiring the next preprocessing time of the third query task.

It should be understood that the time for the third query task to perform the next preprocessing is determined by the update operation for the source data table that needs to follow the parent task.

Illustratively, for example, the time of the parent task corresponding to the third query task performing the update operation for the source data table next time is 3:00, and the time of the next preprocessing of the third query task may be, for example, a time delayed by a preset time interval on the basis of 3:00, or may be obtained by delaying the time taken by the parent task to perform on the basis of 3: 00.

S503, when the next preprocessing time is reached, preprocessing is carried out on the third query task, and a preprocessing result table corresponding to the third query task is updated according to the preprocessing result of the third query task.

As to how to preprocess the third query task, reference may be made to the operation of preprocessing the second query task in fig. 4, which is not described herein again.

After the preprocessing result of the third query task is obtained, the preprocessing result may be used to replace the preprocessing result of the third query task recorded in the original preprocessing result table, or the two preprocessing results may be compared to update the difference portion to the preprocessing result table corresponding to the third query task.

S504, updating the execution information of the third query task in the preprocessing task state table.

It should be understood that, in a specific implementation manner of step S504, reference may be made to the operation of preprocessing the second query task in fig. 4, and details are not described here again.

It should be noted that the operation shown in fig. 5 may be a loop operation, so as to avoid the problem that the query result of the preprocessed query task is inaccurate due to the query result updated periodically by the source data table.

Optionally, if a fourth query task that does not satisfy the preset second screening condition exists in the preprocessing task state table, deleting the fourth query task that does not execute the preprocessing any more from the preprocessing task state table. The fourth query task may be any query task in the pre-processing task state table.

It should be understood that, for a fourth query task that does not satisfy the preset second filtering condition, it indicates that the fourth query task is no longer a query task that needs to be preprocessed. Taking the high-frequency query task as an example, when the accumulated query times of the third query task in the second historical preset time period is less than the preset times, it indicates that the query task is changed from the high-frequency query task to the low-frequency query task. At this time, the preprocessing operation can be omitted, so as to save the storage space.

It should be understood that the server may execute the above-mentioned action of determining whether the historical query task needs to continue the preprocessing when executing the query operation, or may execute a relationship decoupled from the query operation, that is, asynchronously process the above-mentioned action of determining whether the historical query task needs to continue the preprocessing, regardless of whether the server currently executes the query operation from the client, and when asynchronously execute, the server may be implemented by different threads, that is, one thread is used for executing the query operation from the client, and another thread is used for executing the query task preprocessing operation shown in fig. 4, fig. 5 and the like.

According to the data query method provided by the embodiment of the application, after the query task is determined as the preprocessed query task, the preprocessed query task can be preprocessed, so that when the data is queried again in the following process, the query can be directly performed in the preprocessing result table of the database, and the query speed is improved.

In addition, the operation for each table and the operation for querying data from the source data table may be implemented by a server in the service layer directly accessing a database in the data storage layer, or a server cluster is deployed in the data storage layer, and the server in the service layer interacts with a server in the server cluster in the data storage layer, so that the server in the server cluster in the data storage layer operates on the database, thereby implementing the above functions.

In the foregoing embodiment, the database in which each table is located in the data query system is also illustrated, and a specific example is used below to illustrate the database in which each table is located in the data query system:

the data query system comprises two databases, wherein the first database is a MySQL database and is used for storing information such as a preprocessing task information configuration table, a historical query information recording table, a preprocessing task state table and a parent task state table. The second database is a ClickHouse database and is used for storing a preprocessing result table and a source data table.

The MySQL database has small storage capacity and stable performance, and stores data in a form of a table, so that the table with small storage space is convenient to store and query. Because the configuration table stores the query information of the query task in the form of a table, different configuration tables are not affected when the query information in the configuration table is added or deleted, and the like, so that the purpose can be achieved only by modifying the corresponding configuration table without modifying codes or restarting application.

The ClickHouse database has larger data storage capacity and faster query speed, the single-table query speed of the ClickHouse database is forced to press various popular large databases, and the result can be fed back to the user quickly. Therefore, the preprocessing result table and the source data table which occupy larger storage space can be stored in the ClickHouse database, and tasks such as common query, multi-table query, time-consuming query and the like stored in the database are preprocessed, so that the query efficiency can be improved.

After the client provides the query task, the service layer splices the query task into an SQL statement matched with the query statement supported by the database, and then searches whether the query information of the query task exists in the historical query information record table according to the SQL statement. The query tasks are spliced into SQL statements, so that a user can randomly select query conditions at a client, such as time granularity, dimensionality deletion conditions, indexes, index screening conditions and other information, so as to flexibly query required data.

If the query information of the query task exists in the historical query information recording table, the query task is queried before, and whether the query result corresponding to the query task exists or not needs to be searched in a cache; if the query result exists in the cache, directly acquiring and sending the query result to the client; if the query result does not exist in the cache, the query result corresponding to the query task needs to be searched in a preprocessing result table in the ClickHouse database to check whether the query task is subjected to preprocessing operation in advance, and if the query result exists in the preprocessing result table, the query task is indicated to have completed the preprocessing operation, so that the query result can be directly obtained, stored in the cache and sent to the client; if the query result does not exist in the preprocessing result table, it is indicated that the query task may not be preprocessed, the query result needs to be searched in the source data table in the clickwouse database, and after the query result is obtained, the query result also needs to be stored in a cache and sent to the client. After the query result of the query task is obtained, the query information of the query task needs to be stored in a preprocessing task information configuration table.

According to the method and the device, different query conditions and query results can be recorded in each step, and when a user queries the same record again, the query results can be quickly responded.

Based on the data query method provided by the above embodiment, the embodiment of the present application further provides an embodiment of an apparatus for implementing each step and method in the above method embodiment.

Fig. 6 is a schematic structural diagram of a data query device according to an embodiment of the present application, as shown in fig. 6. The device includes: a first processing module 601, a second processing module 602, and a sending module 603. Optionally, the apparatus may further include, for example: a storage module 604.

A first processing module 601, configured to generate a first query statement corresponding to a first query task according to the first query task from a client;

a second processing module 602, configured to determine whether a first query task exists in a pre-processing task information configuration table in a first database according to a first query statement after determining that there is no first query result of the first query task in the cache according to the first query statement, and obtain a first query result from a pre-processing result table corresponding to the first query task by using the first query statement when the first query task exists in the pre-processing task information configuration table; the preprocessing task information configuration table records at least one preprocessing query task;

the sending module 603 is configured to send the first query result to the client.

Optionally, the second processing module 602 is further configured to, after determining whether the first query task exists in the pre-processing task information configuration table in the first database, use the first query statement to obtain the first query result from the source data table when there is no first query task in the pre-processing task information configuration table.

Optionally, the source data table is located in a second database.

Optionally, the second processing module 602 is further configured to, after the first processing module 601 generates the first query statement corresponding to the first query task, use the first query statement to obtain the first query result from the cache when it is determined that the first query result exists in the cache according to the first query statement.

Optionally, the first processing module 601 is specifically configured to splice and convert a plurality of query conditions included in the first query task to obtain the first query statement.

Optionally, the apparatus further comprises: the storage module 604 and the storage module 604 are configured to store the first query result in the cache according to the first query statement.

Optionally, the storage module 604 is configured to store first query information corresponding to the first query task in a historical query information record table of the first database, where the first query information includes: the query time sub-information of the first query statement and the first query task.

Optionally, the second processing module 602 is further configured to determine whether the historical query information record table has the first query task before determining that the cache does not have the first query result of the first query task according to the first query statement, and determine whether the cache has the first query result of the first query task according to the first query statement when determining that the historical query information record table has the first query task; or when the historical query information recording table is determined to have no first query task, a first query statement is used for obtaining a first query result from the source data table.

Optionally, the second processing module 602 is further configured to screen, according to a preset first screening condition, at least one second query task to be preprocessed from the historical query information record table; adding at least one second query task to be preprocessed to a preprocessing task information configuration table; preprocessing a second query task in the preprocessing task information configuration table; wherein the first screening condition comprises: the accumulated query times of the historical query tasks in the first historical preset time are larger than or equal to the preset times, and/or the single query time is larger than or equal to the preset time.

Optionally, the second processing module 602 is specifically configured to, for any second query task in the pre-processing task information configuration table, determine whether a parent task corresponding to the second query task is successfully executed according to the parent task state table of the first database; if the execution success of the parent task corresponding to the second query task is determined, acquiring a second query result of the second query task from a data table corresponding to the parent task by using a query statement corresponding to the second query task; taking the second query result as a preprocessing result of the second query task, and generating and storing a preprocessing result table corresponding to the second query task; the parent task state table records execution information of at least one parent task, and the parent task is used for updating data in a source data table to which a query result corresponding to the second query task belongs.

The storage module 604 is further configured to store a preprocessing result table corresponding to the second query task.

Optionally, the storage module 604 is further configured to store the preprocessing result table corresponding to the second query task in the second database.

Optionally, the second processing module 602 is further configured to, after the storage module 604 stores the preprocessing result table corresponding to the second query task, add execution information of the second query task to the preprocessing task state table in the first database, where the execution information includes: the identifier of the preprocessing result table corresponding to the second query task, the storage location of the preprocessing result table corresponding to the second query task, the latest update time of the preprocessing result of the second query task, and whether to continue updating.

Optionally, the second processing module 602 is further configured to screen out a third query task that needs to continue to execute the preprocessing according to the preprocessing task state table; acquiring the next preprocessing time of a third query task; when the next preprocessing time is reached, preprocessing the third query task, and updating a preprocessing result table corresponding to the third query task according to a preprocessing result of the third query task; updating the execution information of the third query task in the preprocessing task state table; the third query task meets preset second screening conditions, and a parent task of the third query task is updated regularly, wherein the preset second screening conditions comprise: and the accumulated query times of the query task in the second historical preset time length are greater than or equal to the preset times.

Optionally, the second processing module 602 is further configured to delete the fourth query task that is no longer preprocessed from the preprocessed task state table; the fourth query task does not satisfy the preset second screening condition.

The apparatus of the embodiment of the present application may be configured to execute the technical solutions of the method embodiments shown in fig. 3 to fig. 5, and the implementation principles and technical effects are similar, which are not described herein again. The device may be, for example, a server in the service layer, or may be a chip in the server.

Fig. 7 is a schematic structural diagram of an electronic device provided in the present application. As shown in fig. 7, the electronic device may include: at least one processor 701 and a memory 702.

And a memory 702 for storing programs. In particular, the program may include program code including computer operating instructions.

The Memory 702 may comprise high-speed RAM Memory, and may also include Non-volatile Memory (NVM), such as at least one disk Memory.

The processor 701 is configured to execute computer-executable instructions stored in the memory 702 to implement the data query method described in the foregoing method embodiments. The processor 701 may be a Central Processing Unit (CPU), an Application Specific Integrated Circuit (ASIC), or one or more Integrated circuits configured to implement embodiments of the present Application.

Optionally, the electronic device may further comprise a communication interface. In a specific implementation, if the communication interface, the processor 701 and the memory 702 are implemented independently, the communication interface, the memory 702 and the processor 701 may be connected to each other through a bus and perform communication with each other. The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, an Extended ISA (EISA) bus, or the like. Buses may be classified as address buses, data buses, control buses, etc., but do not represent only one bus or type of bus.

Alternatively, in a specific implementation, if the communication interface, the processor 701 and the memory 702 are integrated into a chip, the communication interface, the processor 701 and the memory 702 may complete communication through an internal interface.

The present application also provides a computer-readable storage medium, which may include: various media capable of storing program codes, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and in particular, the computer-readable storage medium stores program instructions, and the program instructions are used in the method in the foregoing embodiments.

Other embodiments of the present application will be apparent to those skilled in the art from consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of the invention following, in general, the principles of the application and including such departures from the present disclosure as come within known or customary practice within the art to which the invention pertains. It is intended that the specification and examples be considered as exemplary only, with a true scope and spirit of the application being indicated by the following claims.

It will be understood that the present application is not limited to the precise arrangements described above and shown in the drawings and that various modifications and changes may be made without departing from the scope thereof. The scope of the application is limited only by the appended claims.

Claims

1. A method for data query, the method comprising:

and sending the first query result to the client.

2. The method of claim 1, wherein after determining whether the first query task exists in a pre-processing task information configuration table in the first database, further comprising:

3. The method of claim 2, wherein the source data table is located in a second database.

4. The method of claim 1, wherein after generating the first query statement corresponding to the first query task, further comprising:

5. The method of any one of claims 1-4, wherein generating, from a first query task from a client, a first query statement corresponding to the first query task comprises:

6. The method according to any one of claims 1-3, further comprising:

7. The method according to any one of claims 1-4, further comprising:

8. The method of claim 7, wherein prior to determining that there is no first query result for the first query task in the cache from the first query statement, further comprising:

alternatively, the first and second electrodes may be,

and sending the first query result to the client.

9. The method of claim 7, further comprising:

screening at least one second query task to be preprocessed from the historical query information record table according to a preset first screening condition; the preset first screening condition comprises the following steps: the accumulated query times of the historical query tasks in the first historical preset time are greater than or equal to the preset times, and/or the single query time is greater than or equal to the preset time;

10. The method of claim 9, wherein the preprocessing the second query task in the preprocessing task information configuration table comprises:

11. The method according to claim 10, wherein the storing the pre-processing result table corresponding to the second query task comprises:

12. The method according to claim 10, wherein after storing the pre-processing result table corresponding to the second query task, further comprising:

13. The method of claim 12, further comprising:

screening out a third query task needing to continuously execute preprocessing according to the preprocessing task state table; the third query task meets a preset second screening condition, and a parent task corresponding to the third query task is updated regularly; the preset second screening condition comprises the following steps: the accumulated query times of the query task in the second historical preset time are greater than or equal to the preset times;

acquiring the next preprocessing time of the third query task;

14. The method of claim 13, further comprising:

15. A data query apparatus, characterized in that the apparatus comprises:

the second processing module is used for determining whether a first query task exists in a preprocessing task information configuration table in a first database according to a first query statement after determining that the cache does not have the first query result of the first query task according to the first query statement, and acquiring the first query result from a preprocessing result table corresponding to the first query task by using the first query statement when the first query task exists in the preprocessing task information configuration table; the preprocessing task information configuration table records at least one preprocessing query task;

16. An electronic device, characterized in that the electronic device comprises: a processor, and a memory communicatively coupled to the processor;

the memory stores computer-executable instructions;

the processor executes computer-executable instructions stored by the memory to implement the method of any of claims 1 to 14.

17. A computer-readable storage medium having computer-executable instructions stored thereon, which when executed by a processor, are configured to implement the data query method of any one of claims 1 to 14.