CN113568923A

CN113568923A - Method and device for querying data in database, storage medium and electronic equipment

Info

Publication number: CN113568923A
Application number: CN202110091205.5A
Authority: CN
Inventors: 袁建伟
Original assignee: Tencent Technology Shenzhen Co Ltd
Current assignee: Tencent Technology Shenzhen Co Ltd
Priority date: 2021-01-22
Filing date: 2021-01-22
Publication date: 2021-10-29

Abstract

The invention discloses a method and a device for querying data in a database, a storage medium and electronic equipment. Wherein, the method comprises the following steps: obtaining a target query item determined from a plurality of query items, wherein each query item in the plurality of query items corresponds to a field in a database; generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from a database; converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under a target field corresponding to a target query item is located; and executing the first query statement in the target query engine to obtain a query result corresponding to the target query item. The invention solves the technical problem of low data efficiency in database query.

Description

Method and device for querying data in database, storage medium and electronic equipment

Technical Field

The invention relates to the field of computers, in particular to a method and a device for querying data in a database, a storage medium and electronic equipment.

Background

In the prior art, when a user needs to query data in a database, because query engines of different databases are different, query statements used are also different. For different query engines, a user needs to correspondingly input a query statement corresponding to the query engine to query the data in the corresponding database, and the query efficiency is low.

In view of the above problems, no effective solution has been proposed.

Disclosure of Invention

The embodiment of the invention provides a method and a device for querying data in a database, a storage medium and electronic equipment, which are used for at least solving the technical problem of low efficiency of querying the data in the database.

According to an aspect of the embodiments of the present invention, there is provided a method for querying data in a database, including: acquiring a target query item determined from a plurality of query items, wherein each query item in the plurality of query items corresponds to a field in a database; generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from the database; converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under the target field corresponding to the target query item is located; and executing the first query statement in the target query engine to obtain a query result corresponding to the target query item.

According to another aspect of the embodiments of the present invention, there is also provided an apparatus for querying data in a database, including: the system comprises a first acquisition unit, a second acquisition unit and a third acquisition unit, wherein the first acquisition unit is used for acquiring a target query item determined from a plurality of query items, and each query item in the plurality of query items corresponds to a field in a database; a generating unit, configured to generate a target query statement according to the target query term, where the target query statement is used to query the database for data in a target field corresponding to the target query term; a conversion unit, configured to convert the target query statement into a first query statement matched with a target query engine, where the target query engine is a query engine used by a target database in which data in the target field corresponding to the target query term is located; and the execution unit is used for executing the first query statement in the target query engine to obtain a query result corresponding to the target query item.

As an alternative example, the generating the target query statement according to the target query term includes: acquiring the identifier of the target query item; determining a query field corresponding to the identifier from a corresponding table of the identifier and the query field; and inputting the query field to a target position of an original query statement to obtain the target query statement, wherein the original query statement is the target query statement without the query field.

As an alternative example, the inputting the query field into the target location of the original query statement to obtain the target query statement includes: in the case that the target query item includes a plurality of items, obtaining a plurality of relationships between the target query items, wherein the relationships include at least one of: mutually exclusive, and, or; acquiring the original query statement according to the relationship; and inputting the query field into the target position of the original query statement to obtain the target query statement.

As an alternative example, the executing the first query statement in the target query engine to obtain the query result corresponding to the target query term includes: determining the first query statement as a target query task in a query task queue; and distributing the target query task to a target thread in a plurality of threads in a distributed query system, and executing the target query task by the target thread to obtain the query result.

According to another aspect of the embodiments of the present invention, there is also provided a computer-readable storage medium, in which a computer program is stored, where the computer program is configured to execute the method for querying data in the database when the computer program runs.

According to another aspect of the embodiments of the present invention, there is also provided an electronic device, including a memory and a processor, where the memory stores a computer program, and the processor is configured to execute the method for querying data in the database through the computer program.

In the embodiment of the invention, a target query item determined from a plurality of query items is obtained, wherein each query item in the plurality of query items corresponds to a field in a database; generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from the database; converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under the target field corresponding to the target query item is located; in the method, after the target query statement is obtained, the target query statement can be automatically converted into the first query statement used by the target query engine according to the target query engine corresponding to the target database to be queried, and then the query result is obtained by executing the first query statement, so that the efficiency of querying data in the database is improved, and the technical problem of low data efficiency in querying the database is solved.

Drawings

The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this application, illustrate embodiment(s) of the invention and together with the description serve to explain the invention without limiting the invention. In the drawings:

FIG. 1 is a diagram illustrating an application environment of an alternative method for querying data in a database, according to an embodiment of the present invention;

FIG. 2 is a schematic diagram of an application environment of an alternative method for querying data in a database, according to an embodiment of the invention;

FIG. 3 is a schematic diagram illustrating a flow of an alternative method for querying data in a database, according to an embodiment of the present invention;

FIG. 4 is a diagram of a query page of an alternative method for querying data in a database, according to an embodiment of the invention;

FIG. 5 is a diagram illustrating query results of an alternative method for querying data in a database, in accordance with an embodiment of the present invention;

FIG. 6 is a diagram illustrating a query task of an alternative method for querying data in a database, according to an embodiment of the present invention;

FIG. 7 is a system framework diagram of an alternative method for querying data in a database, in accordance with embodiments of the present invention;

FIG. 8 is a diagram illustrating the mapping of query terms and ids for an alternative method of querying data in a database, in accordance with an embodiment of the present invention;

FIG. 9 is a multi-threaded diagram of an alternative method of querying data in a database, in accordance with embodiments of the present invention;

FIG. 10 is a flow diagram illustrating preemption of task locks for an alternative method of querying data in a database, in accordance with an embodiment of the present invention;

FIG. 11 is a flow chart illustrating user permission verification of an alternative method for querying data in a database according to an embodiment of the present invention;

FIG. 12 is a flowchart illustrating tasks performed by an alternative method for querying data in a database, according to an embodiment of the present invention;

FIG. 13 is a diagram illustrating query data for an alternative method of querying data in a database, in accordance with an embodiment of the present invention;

FIG. 14 is a flow chart illustrating a query result mapping of an alternative method for querying data in a database, according to an embodiment of the present invention;

FIG. 15 is a schematic structural diagram of an alternative apparatus for querying data in a database according to an embodiment of the present invention;

fig. 16 is a schematic structural diagram of an alternative electronic device according to an embodiment of the invention.

Detailed Description

In order to make the technical solutions of the present invention better understood, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

It should be noted that the terms "first," "second," and the like in the description and claims of the present invention and in the drawings described above are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the invention described herein are capable of operation in sequences other than those illustrated or described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

According to an aspect of the embodiments of the present invention, a method for querying data in a database is provided, and optionally, as an optional implementation manner, the method for querying data in a database may be, but is not limited to be, applied to an environment as shown in fig. 1.

As shown in fig. 1, the terminal device 102 includes a memory 104 for storing various data generated during the operation of the terminal device 102, a processor 106 for processing and operating the various data, and a display 108 for displaying query terms. Terminal device 102 may interact with server 112 via network 110. The server 112 includes a database 114 for storing various data and a processing engine 116 for querying the data. Step S102 to step S106, the terminal device 102 sends the target query statement to the server 112, and the server converts the target query statement into the first query statement and executes the first query statement to obtain a query result.

As an alternative embodiment, the above method for querying data in a database can be applied, but not limited to, the environment shown in fig. 2.

As shown in fig. 2, the server 202 includes a database 204 for storing various data items, a query engine 206 for querying the data items, and a display 208 external to the server for displaying query items and query results. The server 202 may execute steps S202 to S208, display the query term and obtain the target query term, thereby determining the first query statement and executing the first query statement to obtain the query result.

Optionally, in this embodiment, the terminal device may be a terminal device configured with a client, and may include, but is not limited to, at least one of the following: mobile phones (such as Android phones, iOS phones, etc.), notebook computers, tablet computers, palm computers, MID (Mobile Internet Devices), PAD, desktop computers, smart televisions, etc. The client may be a video client, an instant messaging client, a browser client, an educational client, etc. Such networks may include, but are not limited to: a wired network, a wireless network, wherein the wired network comprises: a local area network, a metropolitan area network, and a wide area network, the wireless network comprising: bluetooth, WIFI, and other networks that enable wireless communication. The server may be a single server, a server cluster composed of a plurality of servers, or a cloud server. The above is merely an example, and this is not limited in this embodiment.

The server may also be an independent physical server, a server cluster or a distributed system formed by a plurality of physical servers, or a cloud server providing basic cloud computing services such as cloud service, a cloud database, cloud computing, a cloud function, cloud storage, network service, cloud communication, middleware service, domain name service, security service, CDN, and a big data and artificial intelligence platform. The terminal may be, but is not limited to, a smart phone, a tablet computer, a laptop computer, a desktop computer, a smart speaker, a smart watch, and the like. The terminal and the server may be directly or indirectly connected through wired or wireless communication, and the application is not limited herein.

Optionally, as an optional implementation manner, as shown in fig. 3, the method for querying data in the database includes:

s302, obtaining a target query item determined from a plurality of query items, wherein each query item in the plurality of query items corresponds to a field in a database;

s304, generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from a database;

s306, converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under a target field corresponding to a target query item is located;

s308, executing the first query statement in the target query engine to obtain a query result corresponding to the target query item.

Alternatively, the method for querying data in a database may be applied to a process of querying data in a database. When data is queried, query terms can be displayed on a display interface, a user can select a target query term from the query terms, then a target query statement is generated, the target query statement is converted into a first query statement, the first query statement is operated to query the data from a database, and a query result is obtained. When the data in the database is queried, the target query statement can be automatically converted into the first query statement used by the target query engine, so that the efficiency of querying the data from the database is improved.

Alternatively, the query statement in this embodiment may be an SQL statement for querying data from a database. The method comprises the steps of displaying query items on a page, selecting target query items from the query items, generating corresponding SQL sentences according to the target query items, and querying data in a database by using the SQL sentences.

Optionally, in this embodiment, before the query page is displayed, the query item may be set according to data stored in the database. The data in the database may contain multiple tables, each of which in turn contains multiple fields. Non-repeated fields in the database can be used, each field correspondingly generates a query item, and the query item can be the name of the field or the name used for representing the content of the field. Query terms can be categorized into different categories according to data type or data association.

When the query page is displayed and the query items are displayed, the query items may be displayed in different categories, all the query items may be displayed or a part of the query items may be displayed according to settings, and the query items to be displayed may be searched and displayed. The display quantity and the display content of the query items can be added or deleted according to the setting of the user, and can also be set through the authority account. The setting operation of the user can be reduced by setting the authority account, and the user can set the authority account by himself to realize individuation. After the query terms are displayed, a target query term may be determined from the query terms. Generating a target query statement, such as an SQL statement, corresponding to the target query term according to the corresponding relation between the query term and the query statement, converting the SQL statement into the SQL statement used in the target query engine, namely a first query statement, and searching data from a database by operating the first query statement to obtain a query result.

Optionally, in this embodiment of the present application, there may be a plurality of methods for converting the target query statement into the first query statement.

For example, a name or a flag of a target database of the target query engine may be added to the target query statement, so as to mark the target database corresponding to the target query engine, so as to obtain the first query statement. The target query statement may be a statement including a target query term of the query, and then, in the statement, a name of the target database is added and combined to obtain the first query statement. The name of the target database may be appended to the target query term in the query statement before the target query term, with a character. For example, the character may be "."/", etc.

Or, when the target query statement is converted into the first query statement, the target database may be determined, then, the corresponding field name of the target query term in the target query statement in the target database is determined, and then, the target query term in the target query statement is converted into the corresponding field name in the target database, so as to obtain the first query statement. For example, the time field is represented differently in the database where the target query statement is located than in the target database. Thus, through the conversion of the target query term, the target query statement may be converted into the first query statement. Of course, in this embodiment, after the target query item is obtained, the target query statement is generated first, and the target query statement is converted into the target database to generate the first query statement. If the target database to be queried is known in advance, the field under the target database can be directly generated by using the target query item, and then the first query statement is generated, so that the efficiency of generating the first query statement can be improved.

As another example, in this embodiment of the application, for a target query statement, there may be a plurality of different target databases corresponding to the target query statement, the target query statement may be split into a plurality of target query statements, each target query statement is used for querying one or more target query terms, then, each split target query statement generates a first query statement, and the first query statement is used to query data in the corresponding target database. For example, there are three target query terms, and the generated target query statement is split into two statements, one for querying one target query term, and the other for querying the other two target query terms. And then converting the split target query statement into a first query statement under a corresponding target database according to the target database where the target query item to be queried is located.

Optionally, after the first query statement is obtained and the first query statement is executed to obtain the query result, the query result may be displayed, or the query result may be automatically sent to the target receiving account.

The present embodiment proposes an example of displaying a query page. Fig. 4 is a schematic diagram of an alternative query page, as shown in fig. 4. A plurality of query items such as advertiser id and agent id are displayed in the query page, and the query items are displayed in a classified mode according to flow correlation, advertiser correlation and advertisement correlation. The query term is preceded by two square selection boxes, either of which is selected to indicate that the selectable item is selected as the target selectable item. Of the two square selection boxes, the first selection box is selected to indicate that the relationship between the query terms is a sum, and the second selection box is selected to indicate that the relationship between the query terms is a mutual exclusion. Further selection boxes may also be displayed, as in the case of a third selection box being displayed, selection of the third selection box may indicate a relationship between query terms of "or". Through the query page, not only the target query items but also the relationships among the target query items can be obtained. The task name is the identifier of the task queried at this time and is used for distinguishing different tasks. The target receiving account may be a receiving account of the queried query result. The enumeration value feedback mode may be set for the language of the content in the query result. Chinese and English can be output simultaneously. And the system acquires the target query item by clicking a submit task button to submit the task.

After a target query item is selected in the query page, a first query statement is generated and executed, and a query result is obtained. The query result can jump to a display page or a newly-built display page for display, or pop up a floating layer on the current page (the query page or other pages) to display the query result. The popped floating layer can shield the current page, and the popped floating layer can be closed. For example, as shown in fig. 5, taking date, resource site, ad slot name and customer name as target query terms as an example, the query result is obtained through query, and the query result is popped up in a floating layer manner. The float may be closed by clicking on the float blank or clicking on the blank of the query page.

With the method in the above embodiment, first, a plurality of query items may be displayed on the display interface of the foreground, and then the target query item is selected by the user. The user may select the target query term directly from the plurality of query terms. After the system acquires the target query item, a target query statement can be generated according to a field corresponding to the target query item. The target query statement may have one or more sentences, and may be divided according to the data type of the target query term. For example, a target query statement for querying one type of target query term is divided into one target query statement. Of course, multiple types of target query terms may also be queried using one target query statement. The present embodiment is not limited. Next, the system converts the target query statement into a first query statement. During conversion, a user does not need to participate, the system can determine a target database where a target query item to be queried by a target query statement is located, and then a name or a mark of the target database is added to the target query statement so as to convert the target query statement into a first query statement used under the target database. Data of a target query term in a target database is queried using a first query statement. In the process, the user can wait for checking the query result only after selecting the target query item without excessive participation, so that the query efficiency of querying the data in the database is greatly improved.

Through the method, the target query statement can be automatically converted into the first query statement used by the target query engine according to the target query engine corresponding to the target database to be queried, and then the query result is obtained by executing the first query statement, so that the effect of improving the efficiency of querying the data in the database is achieved.

As an alternative example, prior to converting the target query statement to the first query statement that matches the target query engine, the method further comprises:

determining a first data type of data under a target field corresponding to a target query item;

determining a target database from the plurality of databases according to the first data type, wherein the second data type of the data in the target database is consistent with the first data type;

and determining a query engine used for querying the target database as a target query engine.

Alternatively, in this embodiment, the data in the database may have different types, and the different types of data may be stored in different tables or different databases. After the target query statement is obtained, the corresponding target database can be automatically matched according to the first data type of the data to be queried by the target query statement, and the matching means is that the second data type of the data stored in the target database is consistent with the first data type. And after the matching is successful, the query engine used by the target database is the target query engine. And querying data in the target database by using the target query engine to obtain a query result. During automatic matching, the first data type of the data stored in each database can be obtained, and the first data type of the data to be queried can be obtained from the target query statement, so that matching is completed.

As an alternative embodiment, converting the target query statement into a first query statement that matches the target query engine comprises:

inquiring a target conversion rule from an inquiry statement conversion table according to the type of a target inquiry engine, wherein the inquiry statement conversion table records the conversion rules of the target inquiry statement and the inquiry statements used by each inquiry engine;

the target query statement is converted to a first query statement for use by the target query engine using the target conversion rules.

Optionally, in this embodiment, when the target query engine is used to query corresponding data in the target database, the statement of the target query statement may be converted into the first query statement used by the target query engine. The purpose of converting the target query statement into the first query statement is to convert the standard target query statement into the first query statement used by a specific query engine, thereby improving the query efficiency.

At the time of the specific conversion, the conversion may be performed by a predetermined conversion relationship. If the conversion rule of the target query statement to each query engine is preset, then the first query statement is obtained through conversion according to the conversion rule.

As an alternative embodiment, executing the first query statement in the target query engine to obtain a query result corresponding to the target query term includes:

under the condition that a plurality of target query engines are available, executing a first query statement corresponding to each target query engine by using each target query engine in the plurality of target query engines to obtain a plurality of first results;

and summarizing a plurality of first results into a query result.

Optionally, if the data to be queried is distributed in different target servers after the first data type of the data to be queried by the target query statement is determined, the data in the respective target servers are respectively queried by using target query engines corresponding to the different target servers, so as to implement parallel querying. And after the query is finished, obtaining first results of the plurality of target databases, and summarizing the first results into query results.

During the summarization, the data of the same field can be summarized under one field according to the name of the field, and different fields are sorted according to the name of the field.

Of course, in query, the target query statement needs to be converted into the first query statement used by different target query engines respectively.

As an alternative embodiment, generating the target query statement from the target query term includes:

acquiring the identification of a target query item;

determining a query field corresponding to the identifier from a corresponding table of the identifier and the query field;

and inputting the query field into a target position of the original query statement to obtain a target query statement, wherein the original query statement is the target query statement without the query field.

Optionally, in this embodiment, a unique identifier may be generated for each query term, and by using the unique identifier, it may be determined which field of the database the query term corresponds to. Or, in this embodiment, the corresponding relationship between the query item and the query field may be pre-established, and after the query item is obtained, the corresponding query field may be automatically matched according to the corresponding relationship. The query fields may be the fields used by the query in the SQL statement. After the query field is obtained, the query field may be merged with the original query statement into a target query statement. When merging, the query field may be input to the target location of the original query statement, or the query field may be input to the target location of the original query statement after data processing. The original query statement may be a target query statement that does not include the query field, the original query statement may include a fixed SQL statement used for the query, and the data corresponding to the query field may be queried through combination with the query field.

As an alternative example, inputting the query field into the target location of the original query statement, and obtaining the target query statement comprises:

in the case that the target query item comprises a plurality of items, obtaining a relationship between the plurality of target query items, wherein the relationship comprises at least one of the following: mutually exclusive, and, or;

obtaining an original query statement according to the relation;

and inputting the query field to the target position of the original query statement to obtain the target query statement.

That is, since the purpose of the query is different when querying the data in the database, it may be necessary to view the relationship between the target query items after selecting the target query items, for example, after determining the target query items as the name of the advertiser and the advertisement playing time. If the two relations are sum, the original query statement used for querying the intersection of the two fields is obtained, and combined with the advertiser name and the advertisement playing time to obtain a target query statement, and the intersection of the advertiser name and the advertisement playing time is obtained through query. If the relationship between the advertiser name and the advertisement playing time is yes, an original query statement for querying any one of the advertiser name and the advertisement playing time is obtained, the original query statement and the advertiser name and the advertisement playing time are combined to obtain a target query statement, and a union set of the advertiser name and the advertisement playing time is obtained through query.

As an alternative example, executing the first query statement in the target query engine to obtain a query result corresponding to the target query term includes:

determining the first query statement as a target query task in a query task queue;

and distributing the target query task to a target thread in a plurality of threads in the distributed query system, and executing the target query task by the target thread to obtain a query result.

Optionally, in this embodiment, after the target query item is selected, whether one or more target query items are selected, whether the relationship between the target query items is selected or not, as long as the currently selected target query item is determined, a query task may be generated. The query task is distributed to one thread of a plurality of threads in a distributed query system for query, and the distributed query system comprises a plurality of threads so as to realize parallel query of the plurality of query tasks. For example, as shown in FIG. 6, FIG. 6 is a plurality of query tasks. The query task can be divided into a common task and a periodic task, and the periodic task queries once every period. The task may be queried through its information. The display may display a part of the tasks or all the tasks, and when the display is not complete, the display is performed in pages. Each task may be previewed, downloaded, copied, etc., the general task may be periodized, and the periodized task may be set as the general task. Incomplete tasks may be stopped. Each task may also be previewed, viewed in a query statement, i.e., SQL statement, or viewed in a click condition, i.e., target query term, etc.

As an alternative example, before executing the first query statement by the target query engine, resulting in a query result corresponding to the target query term, the method further comprises: acquiring an account identifier of a target receiving account for receiving a query result; after executing the first query statement by the target query engine, resulting in a query result corresponding to the target query term, the method further comprises: and sending the query result to a target receiving account.

Optionally, when the target query item is obtained, the account id of the target receiving account may also be obtained, that is, in this embodiment, after the query result is obtained, the query result may be automatically sent to the target receiving account, and the query result may be displayed or not displayed. The query result sent to the target receiving account can be viewed by the target receiving account, and other accounts can be set without viewing permission.

Explained in connection with a specific example. In this embodiment, a query page as shown in fig. 4 may be displayed, in which query terms are displayed. The user may click on any of the query terms to obtain the target query term. The user can input the task name of the query, perform subsequent tracking and operation, input the account identification of the target receiving account, and automatically send the query result to the target receiving account after the query is completed. Fig. 5 and 6 are schematic diagrams for displaying query results and displaying query tasks.

Fig. 7 is a system framework diagram of the present embodiment. The user clicks a target query item to be queried on the front-end interface to obtain a query task, the front-end subsystem stores task information of the query task into a database, the background subsystem asynchronously obtains the task from the database, the query is carried out after analysis and calculation, a query result is returned, and finally the front-end subsystem displays the query result.

Firstly, the method comprises the following steps: front subsystem

The foreground subsystem is responsible for interaction of users, the users can select the data model and various target query items on the visual interface, and after the users submit query tasks, the front-end module converts the target query items visually selected by the users into a uniform query protocol. The front-end subsystem divides the target query items into different categories such as client-related, advertisement-related and flow-related, the target query items of different categories can realize mutual exclusion selection, and on the other hand, the front-end subsystem is also responsible for mapping identity documents (id) of the target query items to names or names to id and also supports parameter input of various customized target query items. The front mapping of id and Chinese name is shown in FIG. 8.

In fig. 8, the mapping relationship between the name and id of the target query term is displayed by the floating layer. The float layer may be closed.

The unified query protocol for front-end and back-end interaction is shown in the following figure and mainly comprises screening items, aggregation items, time items, custom items and the like. As shown in the following table:

the following is an exemplary code:

II, secondly: background subsystem

The background subsystem is mainly responsible for acquiring the query task submitted by the front end, generating an sql statement which can be run by a calculation engine after the query task is analyzed and the authority is checked, and returning a query result after calculation and execution feedback. Background service is realized by adopting distributed multithreading, each thread independently processes one query task, the threads are mutually independent and are not aware, and the whole system has high availability and better horizontal expansion capability by adopting distributed scheduling management (zookeeper, zk for short). The structure is shown in fig. 9.

The DB is mainly provided with a task interface table, a field mapping meta table, an entity mapping table, a data partition table, a field name table and the like. The task interface table is designed as follows:

lock robbing analysis module

The lock acquisition analysis module is mainly responsible for acquiring the tasks submitted by the front end, acquiring the task locks, analyzing and checking whether the syntax and the content of the tasks are legal or not, whether the related data sources are ready or not and the like. The main flow is as shown in steps S1012 to S1020 of fig. 10: when starting a task with the authority, mysql acquires the task, then judges whether zk successfully preempts the task, if yes, modifies the flag state of the database, and checks whether the task is legal, if yes, checks whether the data source is ready, and if yes, continues executing the task. In the process, if zk fails to preempt the task lock, the sleep is carried out for 1 second, and the step of mysql acquiring task is returned. And if the checking task is illegal, modifying the BD state to be failure, releasing the zk lock, and returning to the mysql to obtain the task. If the brief data source is not ready, marking the next running time, releasing zk place, and returning to the step of mysql acquiring task.

Permission verification module

The authority checking module is mainly used for checking whether the user has the inquiry authority of the related data or not and limiting the actually inquired data. There are two main types of constraints on query data, a) static constraints, which limit the result data by directly adding a filter condition to the where condition by rewriting sql. b) Dynamic restriction, real-time join-related configuration during each query, and screening for related results. The flow is as shown in steps S1102 to S1108 of fig. 11: and acquiring the user authority according to the user id, checking the user authority, generating an authority limit sql fragment under the condition that the user has the authority, and prompting the user to return a failure message without the authority if the user has no authority.

Generating rewrite modules

The module is mainly responsible for parsing and rewriting tasks into sql that the physical engine can execute. The method mainly comprises the following steps: a) in the multi-table routing, one model may correspond to a plurality of tables, and is mapped to different entity tables according to the difference of index latitudes selected by a user and a routing map in a database (the routing map records which index latitudes exist in each table, and which tables each model can correspond to). b) And field mapping, namely converting the index latitude selected by the front end into an actual sql field of the background, wherein according to a field mapping routing table, one field can be directly obtained from a bottom table, can also be obtained from a dimension table join, and can also be calculated according to complex calculation logic. c) And finally, generating and optimizing the sql, and rewriting the entity table and the sql field into the really executable sql. The flow is as shown in steps S1202 to S1208 of fig. 12.

Task execution module

The task execution module is mainly responsible for splitting the query statement into different sub-queries according to the execution engine and aggregating and returning the results. The method mainly comprises the following steps of a) query splitting, adopting depth-first traversal recursive push-down, splitting into logic sub-splits according to units and joins, and splitting into physical sub-queries according to different execution engines based on the logic sub-queries. b) Query rewriting and distribution, wherein the syntax difference between the original SQL and the target query engine is adapted by using the SQLAlchemy dialect and is distributed to the leaf node query. c) And summarizing the results obtained by the sub-queries by the execution engines, and summarizing and aggregating the results to obtain the final query result.

An example of a query is shown in FIG. 13: 1302 is a global summary node, 1304 is a logical atomic query, 1306 is a physical atomic query, 1308 is a pivot query in a logical atomic query, 1310 is an tdw query in a logical atomic query, 1312 is a draid query in a logical atomic query, and 1314 is an excel query in a logical atomic query.

Task feedback module

The task feedback module is mainly responsible for supplementing the query result with necessary Chinese explanation and finally outputting the query result. Because the original data is usually stored by using English letters or numbers in order to reduce storage and improve query efficiency, if the readability of the letters or numbers is poor, a mapping process from id to Chinese needs to be performed. Firstly, whether a certain column needs to be mapped from id to Chinese is judged through meta information in a database, and if the column needs to be mapped, a relevant mapping relation is obtained from redis for feedback. The main flow is as shown in step S1402 to step S1410 of fig. 14. Through the process, the Chinese and English results can be output because the results are mapped into Chinese results. Of course, the Chinese result or the English result can be output, and the output query result can be determined according to the setting.

The method and the device can greatly reduce the data query and use thresholds, and users can conveniently fetch the data without understanding SQL; the field service logic and data difference of the bottom layer are shielded, and the use is simpler; the user does not need to write complex SQL sentences, so that the workload is greatly reduced, and the working efficiency is improved; specific SQL can be optimized, and execution efficiency is improved; finally, necessary data supplement can be carried out on the query result, so that the readability of the data is greatly improved; the newly added modification fields are simple, maintainability is high, development efficiency is greatly improved, and development cost is reduced. The method and the system can support cross-data source and cross-engine heterogeneous query and association, can realize pb-level big data query, and have the advantages of high query speed and reliability, rich index dimensionality, strong customizability, simple use and support of various result data feedback modes. The greenspan click system realized based on the method and the system provides the ads-rich advertisement data query capability.

It should be noted that, for simplicity of description, the above-mentioned method embodiments are described as a series of acts or combination of acts, but those skilled in the art will recognize that the present invention is not limited by the order of acts, as some steps may occur in other orders or concurrently in accordance with the invention. Further, those skilled in the art should also appreciate that the embodiments described in the specification are preferred embodiments and that the acts and modules referred to are not necessarily required by the invention.

According to another aspect of the embodiment of the present invention, there is also provided a device for querying data in a database, which is used for implementing the method for querying data in a database. As shown in fig. 15, the apparatus includes:

a first obtaining unit 1502, configured to obtain a target query term determined from a plurality of query terms, where each query term in the plurality of query terms corresponds to a field in a database;

a generating unit 1504, configured to generate a target query statement according to the target query term, where the target query statement is used to query, from a database, data in a target field corresponding to the target query term;

a conversion unit 1506, configured to convert the target query statement into a first query statement matched with a target query engine, where the target query engine is a query engine used by a target database in which data in a target field corresponding to the target query term is located;

an executing unit 1508, configured to execute the first query statement in the target query engine, and obtain a query result corresponding to the target query term.

Alternatively, the device for querying data in the database may be applied to a process of querying data in the database. When data is queried, query terms can be displayed on a display interface, a user can select a target query term from the query terms, then a target query statement is generated, the target query statement is converted into a first query statement, the first query statement is operated to query the data from a database, and a query result is obtained. When the data in the database is queried, the target query statement can be automatically converted into the first query statement used by the target query engine, so that the efficiency of querying the data from the database is improved.

For other examples of this embodiment, please refer to the above examples, which are not described herein again.

According to another aspect of the embodiment of the present invention, there is also provided an electronic device for implementing the method for querying data in the database, where the electronic device may be a terminal device or a server shown in fig. 16. The present embodiment takes the electronic device as a server as an example for explanation. As shown in fig. 16, the electronic device comprises a memory 1602 and a processor 1604, the memory 1602 having stored therein a computer program, the processor 1604 being arranged to perform the steps of any of the method embodiments described above by means of the computer program.

Optionally, in this embodiment, the electronic device may be located in at least one network device of a plurality of network devices of a computer network.

Optionally, in this embodiment, the processor may be configured to execute the following steps by a computer program:

obtaining a target query item determined from a plurality of query items, wherein each query item in the plurality of query items corresponds to a field in a database;

generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from a database;

converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under a target field corresponding to a target query item is located;

and executing the first query statement in the target query engine to obtain a query result corresponding to the target query item.

Alternatively, it can be understood by those skilled in the art that the structure shown in fig. 16 is only an illustration, and the electronic device may also be a terminal device such as a smart phone (e.g., an Android phone, an iOS phone, etc.), a tablet computer, a palmtop computer, a Mobile Internet Device (MID), a PAD, and the like. Fig. 16 is a diagram illustrating a structure of the electronic device. For example, the electronics may also include more or fewer components (e.g., network interfaces, etc.) than shown in FIG. 16, or have a different configuration than shown in FIG. 16.

The memory 1602 may be configured to store software programs and modules, such as program instructions/modules corresponding to the method and apparatus for querying data in a database in the embodiment of the present invention, and the processor 1604 executes various functional applications and data processing by running the software programs and modules stored in the memory 1602, that is, implements the method for querying data in a database. The memory 1602 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 1602 can further include memory located remotely from the processor 1604, which can be connected to the terminal over a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof. The memory 1602 may be, but not limited to, specifically configured to store information such as data in a database. As an example, as shown in fig. 16, the aforementioned storage 1602 may include, but is not limited to, a first obtaining unit 1502, a generating unit 1504, a converting unit 1506, and an executing unit 1508 in the querying device of the data in the aforementioned database. In addition, the query device may further include, but is not limited to, other module units in the query device for data in the database, which is not described in detail in this example.

Optionally, the transmission device 1606 is configured to receive or transmit data via a network. Examples of the network may include a wired network and a wireless network. In one example, the transmission device 1606 includes a Network adapter (NIC) that can be connected to a router via a Network line to communicate with the internet or a local area Network. In one example, the transmission device 1606 is a Radio Frequency (RF) module, which is used for communicating with the internet in a wireless manner.

In addition, the electronic device further includes: an external display 1608 for displaying the query results; and a connection bus 1610 for connecting respective module components in the above-described electronic apparatus.

In other embodiments, the terminal device or the server may be a node in a distributed system, where the distributed system may be a blockchain system, and the blockchain system may be a distributed system formed by connecting a plurality of nodes through a network communication. Nodes can form a Peer-To-Peer (P2P, Peer To Peer) network, and any type of computing device, such as a server, a terminal, and other electronic devices, can become a node in the blockchain system by joining the Peer-To-Peer network.

According to a further aspect of an embodiment of the present invention, there is also provided a computer-readable storage medium having a computer program stored thereon, wherein the computer program is arranged to perform the steps of any of the above method embodiments when executed.

Alternatively, in the present embodiment, the above-mentioned computer-readable storage medium may be configured to store a computer program for executing the steps of:

Alternatively, in this embodiment, a person skilled in the art may understand that all or part of the steps in the methods of the foregoing embodiments may be implemented by a program instructing hardware associated with the terminal device, where the program may be stored in a computer-readable storage medium, and the storage medium may include: flash disks, Read-Only memories (ROMs), Random Access Memories (RAMs), magnetic or optical disks, and the like.

The above-mentioned serial numbers of the embodiments of the present invention are merely for description and do not represent the merits of the embodiments.

The integrated unit in the above embodiments, if implemented in the form of a software functional unit and sold or used as a separate product, may be stored in the above computer-readable storage medium. Based on such understanding, the technical solution of the present invention may be substantially or partially implemented in the prior art, or all or part of the technical solution may be embodied in the form of a software product stored in a storage medium, and including instructions for causing one or more computer devices (which may be personal computers, servers, or network devices) to execute all or part of the steps of the method according to the embodiments of the present invention.

In the above embodiments of the present invention, the descriptions of the respective embodiments have respective emphasis, and for parts that are not described in detail in a certain embodiment, reference may be made to related descriptions of other embodiments.

In the several embodiments provided in the present application, it should be understood that the disclosed client may be implemented in other manners. The above-described embodiments of the apparatus are merely illustrative, and for example, a division of a unit is merely a division of a logic function, and an actual implementation may have another division, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, units or modules, and may be in an electrical or other form.

Units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.

In addition, functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit.

The foregoing is only a preferred embodiment of the present invention, and it should be noted that, for those skilled in the art, various modifications and decorations can be made without departing from the principle of the present invention, and these modifications and decorations should also be regarded as the protection scope of the present invention.

Claims

1. A method for querying data in a database is characterized by comprising the following steps:

generating a target query statement according to the target query item, wherein the target query statement is used for querying data under a target field corresponding to the target query item from the database;

converting the target query statement into a first query statement matched with a target query engine, wherein the target query engine is a query engine used by a target database in which data under the target field corresponding to the target query item is located;

2. The method of claim 1, wherein prior to converting the target query statement to the first query statement that matches the target query engine, the method further comprises:

determining a first data type of data under the target field corresponding to the target query item;

determining the target database from a plurality of databases according to the first data type, wherein a second data type of data in the target database is consistent with the first data type;

determining a query engine used for querying the target database as the target query engine.

3. The method of claim 1, wherein converting the target query statement into a first query statement that matches a target query engine comprises:

inquiring a target conversion rule from an inquiry statement conversion table according to the type of the target inquiry engine, wherein the inquiry statement conversion table records the conversion rules of the target inquiry statement and the inquiry statements used by each inquiry engine;

converting the target query statement to the first query statement used by the target query engine using the target conversion rule.

4. The method of claim 1, wherein executing the first query statement in the target query engine to obtain a query result corresponding to the target query term comprises:

under the condition that the target query engines are multiple, executing the first query statement corresponding to each target query engine by using each target query engine in the multiple target query engines to obtain multiple first results;

and summarizing the plurality of first results into the query result.

5. The method of claim 1, wherein generating a target query statement from the target query term comprises:

acquiring the identification of the target query item;

and inputting the query field to a target position of an original query statement to obtain the target query statement, wherein the original query statement is the target query statement without the query field.

6. The method of claim 5, wherein the inputting the query field into a target location of an original query statement, resulting in the target query statement comprises:

in the case that the target query item comprises a plurality of items, obtaining a plurality of relations between the target query items, wherein the relations comprise at least one of the following: mutually exclusive, and, or;

and acquiring the original query statement according to the relation.

7. The method of claim 1, wherein executing the first query statement in the target query engine to obtain a query result corresponding to the target query term comprises:

and distributing the target query task to a target thread in a plurality of threads in a distributed query system, and executing the target query task by the target thread to obtain the query result.

8. The method according to any one of claims 1 to 7,

before executing, by the target query engine, the first query statement resulting in a query result corresponding to the target query term, the method further comprises: acquiring an account identifier of a target receiving account for receiving the query result;

after executing the first query statement by the target query engine, resulting in a query result corresponding to the target query term, the method further comprises: and sending the query result to the target receiving account.

9. An apparatus for querying data in a database, comprising:

the device comprises a first acquisition unit, a second acquisition unit and a third acquisition unit, wherein the first acquisition unit is used for acquiring a target query item determined from a plurality of query items, and each query item in the plurality of query items corresponds to a field in a database;

a generating unit, configured to generate a target query statement according to the target query term, where the target query statement is used to query, from the database, data in a target field corresponding to the target query term;

a conversion unit, configured to convert the target query statement into a first query statement matched with a target query engine, where the target query engine is a query engine used by a target database in which data in the target field corresponding to the target query term is located;

and the execution unit is used for executing the first query statement in the target query engine to obtain a query result corresponding to the target query item.

10. The apparatus of claim 9, further comprising:

a first determining unit, configured to determine, before converting the target query statement into a first query statement matched with a target query engine, a first data type of data in the target field corresponding to the target query term;

a second determining unit, configured to determine the target database from multiple databases according to the first data type, where a second data type of data in the target database is consistent with the first data type;

and the third determining unit is used for determining a query engine used for querying the target database as the target query engine.

11. The apparatus of claim 9, wherein the conversion unit comprises:

the query module is used for querying a target conversion rule from a query statement conversion table according to the type of the target query engine, wherein the query statement conversion table records the target query statement and the conversion rule of the query statement used by each query engine;

a conversion module to convert the target query statement to the first query statement for use by the target query engine using the target conversion rule.

12. The apparatus of claim 9, wherein the execution unit comprises:

the execution module is used for executing the first query statement corresponding to each target query engine by using each target query engine in the plurality of target query engines under the condition that the target query engines are multiple to obtain a plurality of first results;

and the summarizing module is used for summarizing the plurality of first results into the query result.

13. The apparatus of any one of claims 9 to 12, further comprising:

a first obtaining unit, configured to obtain an account id of a target receiving account used for receiving the query result before the target query engine executes the first query statement to obtain a query result corresponding to the target query term;

and the sending unit is used for sending the query result to the target receiving account after the target query engine executes the first query statement to obtain the query result corresponding to the target query item.

14. A computer-readable storage medium, in which a computer program is stored, which computer program, when running, performs the method of any one of claims 1 to 8.

15. An electronic device comprising a memory and a processor, characterized in that the memory has stored therein a computer program, the processor being arranged to execute the method of any of claims 1 to 8 by means of the computer program.