CN111563094A

CN111563094A - Data query method and device, electronic equipment and computer-readable storage medium

Info

Publication number: CN111563094A
Application number: CN202010249037.3A
Authority: CN
Inventors: 秦占明; 康林; 段效晨; 付元宝; 王玉东; 赵艳杰; 罗廷方
Original assignee: Beijing QIYI Century Science and Technology Co Ltd
Current assignee: Beijing QIYI Century Science and Technology Co Ltd
Priority date: 2020-03-31
Filing date: 2020-03-31
Publication date: 2020-08-21

Abstract

The embodiment of the invention provides a data query method and device, electronic equipment and a computer readable storage medium. The data query method comprises the following steps: acquiring a query condition and an output field input by a user; writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement; writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement; generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence; and executing the SQL sentence to be executed to obtain a query result. The invention automatically generates SQL sentences for query and output as required through query conditions and output fields respectively. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Description

Data query method and device, electronic equipment and computer-readable storage medium

Technical Field

The present invention relates to the field of data query, and in particular, to a data query method and apparatus, an electronic device, and a computer-readable storage medium.

Background

Data statistics and analysis, which is generally data statistics requirements set forth by products or operators. Because the data in the database is stored in a specific manner, a professional developer is required to obtain the data. Therefore, in order to meet the above requirement, a developer needs to manually write SQL (Structured query language) statements for statistics and analysis according to a database entity. The data in the database is subjected to data statistics and analysis, namely, the data statistics and analysis are used for inquiring the related data in the database.

However, the way of writing SQL statements manually has high requirements for operators. Products or operation related personnel do not understand programming and cannot finish the work of writing the SQL sentences, and the work of writing the SQL sentences needs to be distributed to software developers, so that the query efficiency is undoubtedly reduced.

Disclosure of Invention

In view of the above problems, embodiments of the present invention provide a data query method and apparatus, an electronic device, and a computer-readable storage medium, so as to solve the problems in the prior art that query efficiency is low when an SQL statement is manually written to query a database, and requirements for operators are high.

In a first aspect of the present invention, there is provided a data query method, including:

acquiring a query condition and an output field input by a user;

writing the query condition into a first preset position of a pre-created first structured query language query statement to obtain a first structured query language statement;

writing the output field into a second preset position of a pre-established second structured query language query statement to obtain a second structured query language statement;

generating a structured query language statement to be executed according to the first structured query language statement and the second structured query language statement;

and executing the structured query language statement to be executed to obtain a query result.

Optionally, the step of writing the query condition into a first preset position of a first structured query language query statement created in advance to obtain the first structured query language statement includes:

querying a preset database, and determining a structured query language representation corresponding to the query condition;

and writing the structured query language representation into a first preset position of the first structured query language query statement to obtain a first structured query language statement.

Optionally, the query condition includes: at least one query field and a screening value corresponding to each query field;

writing the structured query language representation to a first preset location of the first structured query language query statement comprises:

writing the structured query language representation corresponding to the query field into a SELECT clause of the first structured query language query statement;

writing the structured query language representation corresponding to the screening value into a WHERE clause of the first structured query language query statement.

Optionally, writing the output field into a second preset position of a second structured query language query statement created in advance, and obtaining the second structured query language statement includes:

querying a preset database, and determining a structured query language representation corresponding to the output field;

and writing the structured query language representation into a second preset position of the second structured query language query statement to obtain a second structured query language statement.

Optionally, the step of writing the structured query language representation to a second preset location of the second structured query language query statement comprises:

and writing the structural query language representation corresponding to the query field into the LEFT JOIN clause of the second structural query language query sentence.

Optionally, the output field includes: a plurality of first output fields indicating intrinsic properties and a plurality of second output fields indicating extrinsic properties;

writing the structured query language representation to a second predetermined location of the second structured query language query statement comprises:

writing the structured query language representation corresponding to each first output field into different LEFT JOIN clauses of the second structured query language query statement respectively;

writing the structured query language representations corresponding to ALL second output fields into the same LEFT JOIN clause of the second structured query language query sentence in a UNION ALL association mode; and writing the structured query language representation corresponding to the first output field or the structured query language representation corresponding to the second output field into the same LEFT JOIN clause.

Optionally, the step of executing the to-be-executed structured query language statement to obtain a query result includes:

acquiring the data calculation amount of the structured query language statement to be executed through an EXPLAIN function;

when the data calculation amount is larger than or equal to a preset threshold value, sending the structured query language statement to be executed to a first platform, and when the data calculation amount is smaller than the preset threshold value, sending the structured query language statement to be executed to a second platform;

and receiving a query result obtained by the first platform or the second platform executing the to-be-executed structured query language statement.

Optionally, after the step of obtaining the query condition input by the user and the output field, the method further includes:

creating a query task and adding task attributes;

the step of executing the structured query language statement to be executed comprises;

and executing the structured query language statement to be executed according to the task attribute of the query task.

In a second aspect of the present invention, there is also provided a data query apparatus, including:

the acquisition module is used for acquiring the query conditions and the output fields input by the user;

the first generation module is used for writing the query condition into a first preset position of a pre-created first structured query language query statement to obtain the first structured query language statement;

the second generation module is used for writing the output field into a second preset position of a second structured query language query statement which is created in advance to obtain the second structured query language statement;

a third generation module, configured to generate a structured query language statement to be executed according to the first structured query language statement and the second structured query language statement;

and the execution module is used for executing the structured query language statement to be executed to obtain a query result.

Optionally, the first generating module comprises:

the first determining unit is used for querying a preset database and determining a structured query language representation corresponding to the query condition;

and the first generating unit is used for writing the structured query language representation into a first preset position of the first structured query language query statement to obtain the first structured query language statement.

the first generating unit is specifically configured to write the structured query language representation corresponding to the query field into a SELECT clause of the first structured query language query statement; writing the structured query language representation corresponding to the screening value into a WHERE clause of the first structured query language query statement.

Optionally, the second generating module includes:

the second determining unit is used for querying a preset database and determining the structured query language representation corresponding to the output field;

and the second generation unit is used for writing the structured query language representation into a second preset position of the second structured query language query statement to obtain the second structured query language statement.

Optionally, the second generating unit is specifically configured to write the structured query language representation corresponding to the query field into a LEFT JOIN clause of the second structured query language query statement.

the second generating unit is specifically configured to write the structured query language representation corresponding to each of the first output fields into different LEFT JOIN clauses of the second structured query language query statement; writing the structured query language representations corresponding to ALL second output fields into the same LEFT JOIN clause of the second structured query language query sentence in a UNION ALL association mode; and writing the structured query language representation corresponding to the first output field or the structured query language representation corresponding to the second output field into the same LEFT JOIN clause.

Optionally, the execution module includes:

the computing unit is used for acquiring the data computation amount of the structured query language statement to be executed through an EXPLAIN function;

the execution unit is used for sending the structured query language statement to be executed to a first platform when the data calculation amount is greater than or equal to a preset threshold value, and sending the structured query language statement to be executed to a second platform when the data calculation amount is less than the preset threshold value;

a receiving unit, configured to receive a query result obtained by the first platform or the second platform executing the to-be-executed structured query language statement.

Optionally, the apparatus further comprises:

the task module is used for creating a query task and adding task attributes;

the execution module is specifically configured to execute the structured query language statement to be executed according to the task attribute of the query task.

In a third aspect of the present invention, there is also provided an electronic device, including a processor, a communication interface, a memory, and a communication bus, where the processor, the communication interface, and the memory complete communication with each other through the communication bus;

a memory for storing a computer program;

and the processor is used for realizing the steps of the data query method when executing the program stored in the memory.

In a fourth aspect implemented by the present invention, there is also provided a computer-readable storage medium on which a computer program is stored, the computer program, when executed by a processor, implementing the data query method according to any one of the first aspect.

In a fifth aspect of the present invention, there is also provided a computer program product containing instructions which, when run on a computer, cause the computer to perform the above-mentioned data query method.

Aiming at the prior art, the invention has the following advantages:

the data query method provided by the invention can acquire the query conditions and the output fields input by the user; and writing the query condition into a first preset position of a pre-created first structured query language query statement to obtain the first structured query language statement. The first structured query language statement that screens data according to the query conditions is automatically generated by writing the query conditions into the first structured query language query statement that conforms to the structured query language specification. And writing the output field into a second preset position of a pre-established second structured query language query statement to obtain the second structured query language statement. Automatically generating a second structured query language statement that outputs data in the output field by writing the output field to the second structured query language query statement that conforms to the structured query language specification. Generating a structured query language statement to be executed according to the first structured query language statement and the second structured query language statement; and executing the structured query language sentence to be executed to obtain a query result. The invention generates two sections of structured query language sentences through query conditions and output fields respectively. And then spliced into a structured query language statement to be executed. Thus, automatic generation of structured query language statements for querying and outputting on demand is achieved. Query results can be obtained by executing automatically generated structured query language statements to be executed. Compared with the process of manually compiling the structured query language sentence for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Drawings

In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings needed to be used in the description of the embodiments of the present invention will be briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other drawings can be obtained according to these drawings without inventive labor.

FIG. 1 is a flowchart illustrating steps of a data query method according to an embodiment of the present invention;

FIG. 2 is a second flowchart illustrating steps of a data query method according to an embodiment of the present invention;

FIG. 3 is a third flowchart illustrating steps of a data query method according to an embodiment of the present invention;

FIG. 4 is a flowchart illustrating a fourth step of a data query method according to an embodiment of the present invention;

FIG. 5 is a flow chart illustrating a fifth step of a data query method according to an embodiment of the present invention;

FIG. 6 is a diagram illustrating an application interface of a data query method according to an embodiment of the present invention;

FIG. 7 is an application architecture diagram of a data query method according to an embodiment of the present invention;

FIG. 8 is a block diagram of a data query device according to an embodiment of the present invention;

fig. 9 is a second block diagram of a data query apparatus according to an embodiment of the present invention;

fig. 10 is a third block diagram of a data query apparatus according to an embodiment of the present invention;

fig. 11 is a block diagram of an electronic device according to an embodiment of the present invention.

Detailed Description

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 some, not all, embodiments of the present invention. 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 appreciated that reference throughout this specification to "one embodiment" or "an embodiment" means that a particular feature, structure or characteristic described in connection with the embodiment is included in at least one embodiment of the present invention. Thus, the appearances of the phrases "in one embodiment" or "in an embodiment" in various places throughout this specification are not necessarily all referring to the same embodiment. Furthermore, the particular features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.

In various embodiments of the present invention, it should be understood that the sequence numbers of the following processes do not mean the execution sequence, and the execution sequence of each process should be determined by its function and inherent logic, and should not constitute any limitation to the implementation process of the embodiments of the present invention.

Referring to fig. 1, an embodiment of the present invention provides a data query method, where the method includes:

step 101, obtaining a query condition and an output field input by a user.

It should be noted that the query condition is information in a fixed format composed of a keyword, a filter value, and an operator. For example, the query page includes a first region and a second region; wherein the first region contains a plurality of keywords; the second area is an input box. The user drags the keywords in the first area to the second area in a dragging mode, and then adds the screening values and the operation symbols related to the keywords in a selecting mode, so that the query conditions are generated. The query page may further include a third area, which is an input box for adding an output field. The keywords in the first area can be dragged to the third area in a dragging mode, and therefore the output field is formed.

Step 102, writing the query condition into a first preset position of a pre-created first SQL (structured query Language) query statement to obtain a first SQL statement.

It should be noted that the first SQL query statement is a statement with query function that conforms to the SQL language specification. When the first SQL statement is executed, data meeting the query condition in the preset database may be screened out.

And 103, writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement.

It should be noted that the second SQL query statement is a statement with query function that conforms to the SQL language specification. When the second SQL statement is executed, data that meets the query condition may be output according to the output field.

And 104, generating an SQL statement to be executed according to the first SQL statement and the second SQL statement.

It should be noted that, the first SQL statement and the second SQL statement are added to a preset SQL statement template in a splicing manner, so as to generate an to-be-executed SQL statement, where the to-be-executed SQL statement is a segment of executable SQL code.

And 105, executing the SQL sentence to be executed to obtain a query result.

It should be noted that the query result is information that matches the output field in the data searched in the preset database according to the query condition.

In the embodiment of the invention, the query condition and the output field input by the user can be obtained; and writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement. And writing the query condition into a first SQL query statement conforming to the SQL specification, thereby automatically generating a first SQL statement for screening data according to the query condition. And writing the output field into a second preset position of a second SQL query statement which is created in advance to obtain the second SQL statement. And writing the output field into a second SQL query statement conforming to the SQL specification, so as to automatically generate a second SQL statement outputting data according to the output field. Generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence; and executing the SQL sentence to be executed to obtain a query result. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. By executing the automatically generated SQL statement to be executed, a query result can be obtained. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Fig. 2 is a flowchart of steps of another data query method provided in an embodiment of the present invention, as shown in fig. 2, the method includes:

step 201, obtaining the query condition and the output field input by the user.

It should be noted that the query condition is information in a fixed format composed of a keyword, a filter value, and an operator. For example, including a first region and a second region in a query page; wherein the first region contains a plurality of keywords; the second area is an input box. The user drags the keywords in the first area to the second area in a dragging mode, and then adds the screening values and the operation symbols related to the keywords in a selecting mode, so that the query conditions are generated. The query page may further include a third area, which is an input box for adding an output field. The keywords in the first area can be dragged to the third area in a dragging mode, and therefore the output field is formed.

Step 202, querying a preset database, and determining an SQL representation corresponding to the query condition.

It should be noted that the preset database stores data in the form of a table. Preferably, a model can be created to abstract different data tables into different entities, for example, for a video website, a video table for storing video related data is one entity, and a user table for storing website user related data is another entity. The inherent attributes of an entity are abstracted into dimensions, which may be, for example, user nicknames, user registration times, etc., that do not change over time. The time-interval attribute of the entity is abstracted into indexes, such as the playing amount of the user, the playing time of the user and the like, which can change along with the time.

The name of the entity, the name of each dimension and the name of each index respectively correspond to different SQL expressions, and all the SQL expressions are stored in the database in the same way. The query condition is query information relating to a dimension and/or an index. The SQL expression corresponding to the query condition is the SQL expression corresponding to the dimension and/or index in the query condition.

Step 203, writing the SQL representation into a first preset position of the first SQL query statement to obtain the first SQL statement.

It should be noted that the query conditions include: at least one query field and a screening value corresponding to each query field; the query field is an index or dimension. Thus, the step of writing the SQL representation to the first predetermined location of the first SQL query statement comprises:

writing the SQL expression corresponding to the query field into a SELECT clause of the first SQL query statement; and writing the SQL expression corresponding to the screening value into a WHERE clause of the first SQL query statement.

For example, the query condition is that the uid includes data of 1, 2, and 3; the SQL corresponding to the query field is represented as uid, and the screening value is 1, 2, 3; the generated first SQL statement is:

of course, the number of the query fields may be multiple, that is, the query condition may be formed by multiple indexes and/or dimensions. And the multiple query fields are related by a JOIN mode. For example, the query condition is that the uid includes data of 1, 2, and 3; and the user category is movie or documentary; the generated first SQL statement is:

and 204, writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement.

Step 205, generating an SQL statement to be executed according to the first SQL statement and the second SQL statement.

And step 206, executing the SQL sentence to be executed to obtain a query result.

In the embodiment of the invention, the query condition and the output field input by the user can be obtained; querying a preset database, and determining SQL expression corresponding to query conditions; and writing the SQL expression into a first preset position of the first SQL query statement to obtain the first SQL statement. Because the SQL statement needs to adopt SQL expression which accords with SQL writing specification, a professional programmer is needed to convert the query condition into SQL expression. By storing the SQL expression corresponding to the query condition in the database, when generating the SQL statement, the user does not need to know the SQL writing specification, and only needs to input the query condition corresponding to the SQL expression, thereby being convenient for automatically generating the first SQL statement, and simultaneously reducing the requirement on the user. And writing the output field into a second preset position of a second SQL query statement which is created in advance to obtain the second SQL statement. And writing the output field into a second SQL query statement conforming to the SQL specification, so as to automatically generate a second SQL statement outputting data according to the output field. Generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence; and executing the SQL sentence to be executed to obtain a query result. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. By executing the automatically generated SQL statement to be executed, a query result can be obtained. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Fig. 3 is a flowchart of steps of another data query method provided in an embodiment of the present invention, as shown in fig. 3, the method includes:

step 301, obtaining a query condition and an output field input by a user.

Step 302, writing the query condition into a first preset position of a first SQL query statement created in advance, so as to obtain the first SQL statement.

Step 303, querying a preset database, and determining the SQL representation corresponding to the output field.

The name of the entity, the name of each dimension and the name of each index respectively correspond to different SQL expressions, and all the SQL expressions are stored in the database in the same way. The output field is a dimension or index.

And 304, writing the SQL expression into a second preset position of the second SQL query statement to obtain a second SQL statement.

It should be noted that the SQL representation corresponding to the query field is written into the LEFT JOIN clause of the second SQL query statement.

For example, if the output field is uid, the generated second SQL statement is:

of course the number of output fields may be multiple. Preferably, the output field includes: a plurality of first output fields indicating intrinsic properties and a plurality of second output fields indicating extrinsic properties. I.e. the output field comprises a plurality of dimensions and a plurality of metrics. And generating a section of SQL codes for each output field, and then splicing the generated sections of SQL codes to form a second SQL statement.

Specifically, the step of writing the SQL representation corresponding to the query field into the LEFT JOIN clause of the second SQL query statement includes:

respectively writing the SQL expression corresponding to each first output field into different LEFTJOIN clauses of the second SQL query statement; writing SQL representations corresponding to ALL second output fields into the same LEFT JOIN clause of the second SQL query statement in a UNION ALL correlation mode; and only the SQL expression corresponding to the first output field or the SQL expression corresponding to the second output field is written into the same LEFT JOIN clause. Since the second output field is used to indicate extrinsic properties, i.e. indicators that vary with time, such as play volume, number of praise, etc. The plurality of second output fields should have a uniform time range, i.e. the data indicated by the second output fields should be counted within the same time range. In order to reduce the code amount, the SQL representations corresponding to ALL the second output fields may be associated first by using the association method of UNION ALL. And then writing the associated result into the same LEFT JOIN clause of the second SQL query statement, so that each second output field does not need to be written into different LEFT JOIN clauses, and the code amount of the second SQL statement is reduced.

For example, if the output field includes the uid and the registration time, the generated second SQL statement is:

step 305, generating an SQL statement to be executed according to the first SQL statement and the second SQL statement.

And step 306, executing the SQL sentence to be executed to obtain a query result.

In the embodiment of the invention, the query condition and the output field input by the user can be obtained; and writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement. And writing the query condition into a first SQL query statement conforming to the SQL specification, thereby automatically generating a first SQL statement for screening data according to the query condition. Querying a preset database, and determining SQL expression corresponding to an output field; and writing the SQL expression into a second preset position of the second SQL query statement to obtain a second SQL statement. Because the SQL statement needs to adopt SQL expression which accords with SQL writing specification, a professional programmer is needed to convert the query condition into SQL expression. By storing the SQL expression corresponding to the output field in the database, when the SQL statement is generated, a user does not need to know the SQL writing specification, and only needs to input the query condition corresponding to the SQL expression, so that the second SQL statement can be generated conveniently and automatically, and the requirement on the user is reduced. Generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence; and executing the SQL sentence to be executed to obtain a query result. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. By executing the automatically generated SQL statement to be executed, a query result can be obtained. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Fig. 4 is a flowchart of steps of another data query method provided in an embodiment of the present invention, as shown in fig. 4, the method includes:

step 401, obtaining a query condition and an output field input by a user.

Step 402, writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement.

And 403, writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement.

And step 404, generating an SQL statement to be executed according to the first SQL statement and the second SQL statement.

And step 405, acquiring the data calculation amount of the SQL statement to be executed through the EXPLAIN function.

Step 406, when the data calculation amount is greater than or equal to a preset threshold value, sending the SQL statement to be executed to the first platform; and when the data calculation amount is smaller than a preset threshold value, sending the SQL sentence to be executed to a second platform.

It should be noted that the first platform and the second platform are third party platforms. The third-party platform is a platform with the function of executing SQL statements, and is provided with a database. When the SQL statement is executed, data is screened in the database. The third party platform may be a HIVE (data warehouse tool) or an Impala, and then the first platform is a HIVE and the second platform is an Impala. If the calculated data amount exceeds a preset threshold value, sending an SQL statement to be executed to the HIVE; and if the data calculation amount does not exceed the preset threshold, sending the SQL sentence to be executed to the Impala.

Step 407, receiving a query result obtained by the first platform or the second platform executing the SQL statement to be executed.

It should be noted that the query result usually contains a large amount of data, and preferably, a preset amount of result data can be selected after the query result is received. Of course, a statement for limiting the number of query results may be added to the generated SQL statement to be executed.

In the embodiment of the invention, the query condition and the output field input by the user can be obtained; and writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement. And writing the query condition into a first SQL query statement conforming to the SQL specification, thereby automatically generating a first SQL statement for screening data according to the query condition. And writing the output field into a second preset position of a second SQL query statement which is created in advance to obtain the second SQL statement. And writing the output field into a second SQL query statement conforming to the SQL specification, so as to automatically generate a second SQL statement outputting data according to the output field. And acquiring the data calculation amount of the SQL statement to be executed through the EXPLAIN function. When the data calculation amount is larger than or equal to a preset threshold value, sending the SQL sentence to be executed to a first platform; and when the data calculation amount is smaller than a preset threshold value, sending the SQL sentence to be executed to a second platform. The third-party platform is used for processing, so that the workload of data development can be reduced. And according to the data calculation amount and the advantages and disadvantages of different third-party platforms, a proper third-party platform is flexibly selected to execute the SQL sentence to be executed, and the query result can be timely and effectively obtained from the third-party platform. And receiving a query result obtained by the first platform or the second platform executing the SQL sentence to be executed. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. And executing the automatically generated SQL sentence to be executed by the third-party platform to quickly obtain a query result. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Fig. 5 is a flowchart of steps of another data query method provided in an embodiment of the present invention, as shown in fig. 5, the method includes:

step 501, obtaining a query condition and an output field input by a user.

Step 502, create query tasks and add task attributes.

It should be noted that the task attribute includes single execution and timed execution.

Step 503, writing the query condition into a first preset position of a first SQL query statement created in advance, to obtain the first SQL statement.

And 504, writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement.

And 505, generating an SQL statement to be executed according to the first SQL statement and the second SQL statement.

Step 506, according to the task attribute of the query task, executing the SQL sentence to be executed to obtain a query result.

It should be noted that, when creating the query task, the task attribute selected by the user is executed once, and the to-be-executed SQL statement is executed only once. When the query task is created, if the task attribute selected by the user is executed at regular time, the SQL statement to be executed is executed periodically at a certain time every day or every week.

In the embodiment of the invention, the query condition and the output field input by the user can be obtained; a query task is created and task attributes are added. And writing the query condition into a first preset position of a first SQL query statement created in advance to obtain the first SQL statement. And writing the query condition into a first SQL query statement conforming to the SQL specification, thereby automatically generating a first SQL statement for screening data according to the query condition. And writing the output field into a second preset position of a second SQL query statement which is created in advance to obtain the second SQL statement. And writing the output field into a second SQL query statement conforming to the SQL specification, so as to automatically generate a second SQL statement outputting data according to the output field. Generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence; and executing the SQL sentence to be executed according to the query task to obtain a query result. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. The query result can be flexibly obtained by executing the automatically generated SQL statement to be executed in a single time or in a timing mode through the task attribute of the query task. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time, improves the query efficiency and reduces the requirements on operators; meanwhile, more choices are provided for the user, and the time for obtaining the query result can be selected according to the user requirements.

As shown in fig. 6, it is an application interface display diagram of the data query method provided in the embodiment of the present invention; for example, for a video type website, different data tables are abstracted into different entities according to the established model, that is, a video table for storing video related data is one entity, and a user table for storing website user related data is another entity. The inherent attributes of an entity are abstracted into dimensions, which may be, for example, user nicknames, user registration times, etc., that do not change over time. The time-interval attribute of the entity is abstracted into indexes, such as the playing amount of the user, the playing time of the user and the like, which can change along with the time.

The left area in fig. 6 shows the dimensions and the index information. Such as production, vermicelli, distribution, display, and evaluated quantity. The filtering condition and the data in the output field area may be formed by a user dragging each dimension and each index in the left area. Tasks can also be established according to query actions, such as the task with the task name of demonstration-1 in fig. 6, and task attributes are added to the tasks, and the tasks can be executed in a single time or in a timing mode. The number of rows may also be limited, as shown in FIG. 6, to 1000; the resulting query results only display 1000 pieces of data.

The screening conditions in FIG. 6 are those in which the uid contains 1, 2, 3; and the user comments are movies or documentaries; and the volume of the positive film playing exceeds 10000 during 2020-02-10 to 2020-02-16. The output field is uid, authentication time, and feature play amount.

The first SQL statement generated according to the screening condition is:

according to the output field, the generated second SQL statement is:

the SQL sentences to be executed generated according to the first SQL sentences and the second SQL sentences are as follows:

in the embodiment of the invention, two sections of SQL sentences are generated respectively through the query conditions and the output fields. And then the SQL sentences are spliced into the SQL sentences to be executed. Therefore, the SQL statement can be automatically generated for query and output as required. By executing the automatically generated SQL statement to be executed, a query result can be obtained. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

As shown in fig. 7, the application architecture diagram of the data query method provided in the embodiment of the present invention is shown, in which a UI (User Interface) is used for a User to input query conditions and output fields. And the task generator is used for generating a query task according to the input of the user and storing the query task to the MYSQL database. And the SQL generator generates HIVE SQL to be executed according to the query condition and the output field in the query task. Specifically, a data range t _ filter is defined firstly, and inquired data is stored in a temporary table t _ filter according to inquiry conditions; determining dimension output and index output according to the output field; SQL optimization, which is to remove the repeated data in the temporary table; and (5) associating the output with the temporary table t _ filter, and finally generating the HIVESQL to be executed.

The current limitation is used to limit the number of tasks to be executed simultaneously, for example, to limit the number of tasks to be executed simultaneously by each user to not more than 5, but not limited thereto. A one-time task is used to ensure that a task is only executed once. The timed task is used to ensure that the task is executed at a timed time. And the task recovery is used for processing task exception, and whether the task is abnormal is judged by monitoring the task state. If the task state is monitored, updating the task state; and if the task state is not monitored, the task is resubmitted. The task running queue is used for submitting tasks to third-party platform hadoops in sequence, SQL sentences in the Hadoop execution tasks acquire query results in an HDFS (Hadoop Distributed File System), and data of a preset number of the query results are written into a MYSQL database.

According to the embodiment of the invention, the SQL statement for query and output as required can be automatically generated according to the query condition and the output field. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

Referring to fig. 8, an embodiment of the present invention further provides a data query apparatus, including:

an obtaining module 81, configured to obtain a query condition and an output field input by a user;

the first generating module 82 is configured to write the query condition into a first preset position of a first SQL query statement created in advance, so as to obtain the first SQL statement;

the second generating module 83 is configured to write the output field into a second preset position of a second SQL query statement created in advance, so as to obtain the second SQL statement;

a third generating module 84, configured to generate an SQL statement to be executed according to the first SQL statement and the second SQL statement;

the execution module 85 is configured to execute the SQL statement to be executed to obtain a query result.

Alternatively, referring to fig. 9, the first generation module 82 includes:

a first determining unit 821, configured to query a preset database and determine an SQL representation corresponding to a query condition;

the first generating unit 822 is configured to write the SQL representation into a first preset location of the first SQL query statement, so as to obtain the first SQL statement.

a first generating unit 822, specifically configured to write the SQL representation corresponding to the query field into a SELECT clause of the first SQL query statement; and writing the SQL expression corresponding to the screening value into a WHERE clause of the first SQL query statement.

Optionally, as shown in fig. 10, the second generating module 83 includes:

a second determining unit 831, configured to query a preset database and determine an SQL representation corresponding to the output field;

the second generating unit 832 is configured to write the SQL representation into a second preset location of the second SQL query statement, so as to obtain a second SQL statement.

Optionally, the second generating unit 832 is specifically configured to write the SQL representation corresponding to the query field into the LEFT JOIN clause of the second SQL query statement.

Optionally, the output field comprises: a plurality of first output fields indicating intrinsic properties and a plurality of second output fields indicating extrinsic properties;

a second generating unit 832, configured to respectively write the SQL representation corresponding to each first output field into different LEFT JOIN clauses of the second SQL query statement; writing the SQL expressions corresponding to ALL the second output fields into the same LEFT JOIN clause of the second SQL query statement in a UNION ALL correlation mode; and only the SQL expression corresponding to the first output field or the SQL expression corresponding to the second output field is written into the same LEFT JOIN clause.

Optionally, the execution module 85 includes:

the calculation unit is used for acquiring the data calculation amount of the SQL statement to be executed through an EXPLAIN function;

the execution unit is used for sending the SQL sentence to be executed to the first platform when the data calculation amount is larger than or equal to a preset threshold value, and sending the SQL sentence to be executed to the second platform when the data calculation amount is smaller than the preset threshold value;

and the receiving unit is used for receiving a query result obtained by the first platform or the second platform executing the SQL sentence to be executed.

Optionally, the apparatus further comprises:

the task module is used for creating a query task and adding task attributes;

and the execution module is specifically used for executing the SQL statement to be executed according to the task attribute of the query task.

The data query device provided in the embodiment of the present invention can implement each process implemented by the data query method in the method embodiments of fig. 1 to fig. 5, and is not described here again to avoid repetition.

In an embodiment of the present invention, a data query apparatus includes: the acquisition module is used for acquiring the query conditions and the output fields input by the user; the first generation module is used for writing the query condition into a first preset position of a pre-created first structured query language query statement to obtain the first structured query language statement; the second generation module is used for writing the output field into a second preset position of a second structured query language query statement which is created in advance to obtain the second structured query language statement; the third generation module is used for generating a structured query language statement to be executed according to the first structured query language statement and the second structured query language statement; and the execution module is used for executing the structured query language statement to be executed to obtain a query result. The invention generates two sections of SQL sentences through query conditions and output fields respectively. And then the SQL sentences to be executed are spliced. Therefore, the SQL statement can be automatically generated for query and output as required. By executing the automatically generated SQL statement to be executed, a query result can be obtained. Compared with the process of manually compiling SQL sentences for data query, the method saves a large amount of time and improves the query efficiency; and simultaneously, the requirement on operators is also reduced.

An embodiment of the present invention further provides an electronic device, as shown in fig. 11, including a processor 1101, a communication interface 1102, a memory 1103, and a communication bus 1104, where the processor 1101, the communication interface 1102, and the memory 1103 complete communication with each other through the communication bus 1104;

a memory 1103 for storing a computer program;

the processor 1101 is configured to implement the following steps when executing the program stored in the memory 1103:

acquiring a query condition and an output field input by a user;

writing the query condition into a first preset position of a first SQL query statement created in advance to obtain a first SQL statement;

writing the output field into a second preset position of a second SQL query statement created in advance to obtain a second SQL statement;

generating an SQL sentence to be executed according to the first SQL sentence and the second SQL sentence;

and executing the SQL sentence to be executed to obtain a query result.

The communication bus mentioned in the above terminal may be a Peripheral Component Interconnect (PCI) bus, an Extended Industry Standard Architecture (EISA) bus, or the like. The communication bus may be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one thick line is shown, but this does not mean that there is only one bus or one type of bus.

The communication interface is used for communication between the terminal and other equipment.

The Memory may include a Random Access Memory (RAM) or a non-volatile Memory (non-volatile Memory), such as at least one disk Memory. Optionally, the memory may also be at least one memory device located remotely from the processor.

The Processor may be a general-purpose Processor, and includes a Central Processing Unit (CPU), a Network Processor (NP), and the like; the device can also be a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field Programmable Gate Array (FPGA) or other Programmable logic device, a discrete Gate or transistor logic device, or a discrete hardware component.

In yet another embodiment of the present invention, a computer-readable storage medium is further provided, which has instructions stored therein, and when the instructions are executed on a computer, the instructions cause the computer to execute the data query method in any one of the above embodiments.

In yet another embodiment, a computer program product containing instructions is provided, which when run on a computer, causes the computer to execute the data query method described in the above embodiments.

In the above embodiments, the implementation may be wholly or partially realized by software, hardware, firmware, or any combination thereof. When implemented in software, may be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When loaded and executed on a computer, cause the processes or functions described in accordance with the embodiments of the invention to occur, in whole or in part. The computer may be a general purpose computer, a special purpose computer, a network of computers, or other programmable device. The computer instructions may be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another, for example, from one website site, computer, server, or data center to another website site, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, Digital Subscriber Line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device, such as a server, a data center, etc., that incorporates one or more of the available media. The usable medium may be a magnetic medium (e.g., floppy Disk, hard Disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., Solid State Disk (SSD)), among others.

It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

All the embodiments in the present specification are described in a related manner, and the same and similar parts among the embodiments may be referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

The above description is only for the preferred embodiment of the present invention, and is not intended to limit the scope of the present invention. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention shall fall within the protection scope of the present invention.

Claims

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

acquiring a query condition and an output field input by a user;

2. The method of claim 1, wherein writing the query condition into a first pre-defined location of a pre-created first structured query language query statement to obtain the first structured query language statement comprises:

3. The method of claim 2, wherein the query condition comprises: at least one query field and a screening value corresponding to each query field;

4. The method of claim 1, wherein writing the output field to a second pre-determined location of a pre-created second structured query language query statement, the step of obtaining the second structured query language statement comprises:

5. The method of claim 4, wherein writing the structured query language representation to a second predetermined location of the second structured query language query statement comprises:

6. The method of claim 5, wherein the output field comprises: a plurality of first output fields indicating intrinsic properties and a plurality of second output fields indicating extrinsic properties;

writing the structured query language representation corresponding to the query field into a LEFT JOIN clause of the second structured query language query statement, comprising:

7. The method of claim 1, wherein executing the structured query language statement to be executed to obtain a query result comprises:

8. The method of claim 1, wherein after the step of obtaining the query condition input by the user and the output field, the method further comprises:

creating a query task and adding task attributes;

the step of executing the structured query language statement to be executed comprises:

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

10. An electronic device comprising a processor, a memory and a computer program stored on the memory and executable on the processor, characterized in that the computer program, when executed by the processor, implements the steps of the data query method according to any one of claims 1 to 8.

11. A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the steps of the data query method according to any one of claims 1 to 8.