Data query method, device, electronic device and storage medium

Abstract

The present application relates to the field of database technology, and discloses a data query method, device, electronic device, and storage medium, which do not require the development and maintenance of complex data sustainable update algorithm strategies, reduce the implementation complexity of data query, and do not require the local storage of data in the view table, saving storage resources. The method comprises: obtaining a first query statement for indicating to query specified data from a view table, the view table is generated based on an original query statement, and the original query statement is used to indicate to query data from a specified database; based on the original query statement, modifying the first query statement to obtain a second query statement for indicating to query specified data from a specified database; based on the second query statement, obtaining the specified data.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a data query method, device, electronic device and storage medium. Background Art

[0002] When developing products and systems related to data visualization and analysis, the data queried based on the SQL (Structured Query Language) input by the user is often used as the data source of the visualization report, and the view table is generated and stored locally, so that users can use new SQL statements to directly query data from the locally stored view table, and implement secondary processing of the data in the view table, such as querying, filtering, grouping, sorting, etc., to obtain better data analysis results. However, once the data in the original database changes, the data in the view table needs to be updated synchronously. Therefore, the solution of querying data from the locally stored view table requires relevant algorithm strategies for continuous data update, which is more complicated to implement. Summary of the invention

[0003] The embodiments of the present application provide a data query method, device, electronic device and storage medium, which do not require the development and maintenance of complex data continuous update algorithm strategies, reduce the implementation complexity of data query, and do not require the data in the view table to be stored locally, thus saving storage resources.

[0004] On the one hand, an embodiment of the present application provides a data query method, including:

[0005] Obtaining a first query statement for instructing to query specified data from a view table, wherein the view table is generated based on an original query statement, and the original query statement is used to instruct to query data from a specified database;

[0006] Based on the original query statement, modify the first query statement to obtain a second query statement for instructing to query the specified data from the specified database;

[0007] Based on the second query statement, the specified data is obtained.

[0008] On the one hand, an embodiment of the present application provides a data query device, including:

[0009] An acquisition module, configured to acquire a first query statement for instructing to query specified data from a view table, wherein the view table is generated based on an original query statement, and the original query statement is used to instruct to query data from a specified database;

[0010] A modification module, used for modifying the first query statement based on the original query statement to obtain a second query statement for instructing to query the specified data from the specified database;

[0011] A query module is used to obtain the specified data based on the second query statement.

[0012] Optionally, the modification module is specifically used to:

[0013] According to the correspondence between the original field and the alias field obtained from the original query statement, the alias field in the first query statement is replaced with the corresponding original field;

[0014] Replacing the view table in the first query statement with the specified database; and

[0015] The query condition in the original query statement is added to the conditional clause of the first query statement.

[0016] Optionally, the device further includes a parsing module, configured to obtain the corresponding relationship by the following method:

[0017] Based on the query field separator, the query field clause is divided into multiple query fields;

[0018] For any query field containing alias definition characters, an original field and an alias field having a corresponding relationship are obtained from the any query field, wherein the alias definition characters are characters used to define a corresponding relationship between the original field and the alias field.

[0019] Optionally, the parsing module is specifically used to:

[0020] If the alias definition character contained in any query field is AS, the field before AS in any query field is determined as the original field, and the field after AS in any query field is determined as the alias field corresponding to the original field;

[0021] If the alias definition character contained in any query field is CASE, the field between CASE and END in any query field is determined as the original field, and the field after END in any query field is determined as the alias field corresponding to the original field;

[0022] If any of the query fields does not contain AS and CASE, and any of the query fields contains a space character, the field before the space character in any of the query fields is determined as the original field, and the field after the space character in any of the query fields is determined as the alias field corresponding to the original field.

[0023] Optionally, the parsing module is specifically used to:

[0024] Replace the symbol in the brackets in the query field clause that is the same as the query field separator with a preset special character;

[0025] Based on the query field separator, the query field after the replacement symbol is divided into multiple query fields;

[0026] Special characters in the multiple query fields are replaced with the query field separator.

[0027] Optionally, the device further comprises a marking module, configured to:

[0028] If the original field corresponding to the alias field has used an aggregate function, the alias field is marked as a field that cannot use an aggregate function;

[0029] When you use an aggregate function on a field that is marked as not being able to use an aggregate function, a prompt message is displayed indicating that the aggregate function cannot be used.

[0030] Optionally, the modification module is specifically configured to: add the query condition in the original query statement before the query condition in the condition clause of the first query statement.

[0031] Optionally, when the conditional clause of the original query statement contains a placeholder corresponding to a custom condition, the modification module is further used to, if it is determined that the first query statement contains the custom query condition, delete the custom query condition in the first query statement and replace the placeholder added to the first query statement with the custom query condition.

[0032] On the one hand, an embodiment of the present application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of any of the above methods when executing the computer program.

[0033] On the one hand, an embodiment of the present application provides a computer-readable storage medium having computer program instructions stored thereon, and the computer program instructions implement the steps of any of the above methods when executed by a processor.

[0034] On the one hand, an embodiment of the present application provides a computer program product, which includes a computer program stored on a computer-readable storage medium, and the computer program includes program instructions, which implement the steps of any of the above methods when executed by a processor.

[0035] The data query method, device, electronic device and storage medium provided in the embodiments of the present application, when processing data based on a view table, modify the first query statement for the view table based on the original query statement corresponding to the view table, obtain the second query statement that can directly query data from the specified database of the original query statement, and directly obtain data from the specified database based on the second query statement. Therefore, there is no need to store the data in the view table locally, and there is no need to update the changed data in the specified database to the view table, and there is no need to develop and maintain complex data continuous update algorithm strategies. It can also save a lot of local storage resources, reduce the complexity of the data analysis client and server, and has strong portability. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments of the present application will be briefly introduced below. Obviously, the drawings introduced below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0037] Figure 1 A schematic diagram of an application scenario of the data query method provided in an embodiment of the present application;

[0038] Figure 2 A flowchart of a data query method provided in one embodiment of the present application;

[0039] Figure 3A A schematic diagram of the interface of a data table management page provided in an embodiment of the present application;

[0040] Figure 3B A schematic diagram of an interface for creating a new data table page provided in an embodiment of the present application;

[0041] Figure 3C A schematic diagram of an interface of an editing page corresponding to a view table provided in an embodiment of the present application;

[0042] Figure 4 A schematic diagram of a tree structure of an abstract syntax tree provided in an embodiment of the present application;

[0043] Figure 5 A schematic diagram of a process for obtaining a corresponding relationship from an original query statement provided in an embodiment of the present application;

[0044] Figure 6 A schematic diagram of the structure of a data query device provided in one embodiment of the present application;

[0045] Figure 7 A schematic diagram of the structure of an electronic device provided in one embodiment of the present application. DETAILED DESCRIPTION

[0046] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application.

[0047] For ease of understanding, the terms involved in the embodiments of the present application are explained below:

[0048] Cloud technology refers to a hosting technology that unifies hardware, software, network and other resources in a wide area network or local area network to achieve data computing, storage, processing and sharing. Cloud technology is a general term for network technology, information technology, integration technology, management platform technology, application technology, etc. based on the cloud computing business model. It can form a resource pool, which is used on demand and is flexible and convenient. Cloud computing technology will become an important support. The background services of technical network systems require a large amount of computing and storage resources, such as video websites, picture websites and more portal websites. With the high development and application of the Internet industry, in the future, each item may have its own identification mark, and all need to be transmitted to the background system for logical processing. Data of different levels will be processed separately. All kinds of industry data require strong system backing support, which can only be achieved through cloud computing.

[0049] In short, a database can be seen as an electronic filing cabinet - a place where electronic files are stored, and users can add, query, update, delete, and other operations on the data in the files. The so-called "database" is a collection of data that is stored together in a certain way, can be shared with multiple users, has as little redundancy as possible, and is independent of the application.

[0050] A database management system (DBMS) is a computer software system designed for managing databases. It generally has basic functions such as storage, retrieval, security, and backup. Database management systems can be classified according to the database model they support, such as relational, XML (Extensible Markup Language); or according to the type of computer they support, such as server clusters, mobile phones; or according to the query language used, such as SQL (Structured Query Language), XQuery; or according to performance focus, such as maximum scale, maximum operating speed; or other classification methods. Regardless of the classification method used, some DBMS can cross categories, for example, supporting multiple query languages ​​at the same time.

[0051] SQL (Structured Query Language) is a special-purpose programming language, a database query and programming language used to access data and query, update, and manage relational database systems. Common SQL statements include mysql, postgresql, oracle, sqlserver, etc.

[0052] Query statement: The data query statement in SQL statement, whose core instruction is SELECT. In order to perform detailed query, various auxiliary instructions are added, including FROM, WHERE, GROUP BY and ORDER BY.

[0053] Field: Each row in a table is called a "record". Each record contains all the information in this row, just like all the information about a person in an address book database. However, records in the database do not have a specific record name, and the row number is often used to indicate which record it is. A field is a smaller unit than a record. A set of fields constitutes a record. Each field describes a feature of a document, namely a data item, and has a unique field identifier for computer recognition.

[0054] Alias: When displaying query results, the database will display the output columns specified after each SELECT. In some cases, the names of the displayed columns will be very long or not intuitive enough. In this case, you can specify a column alias.

[0055] Regular Expression: Also known as regular expression. (English: Regular Expression, often abbreviated as regex, regexp or RE in code), a concept in computer science. Regular expressions are usually used to retrieve and replace text that conforms to a certain pattern (rule).

[0056] View: A view in a computer database is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and rows of data. However, a view does not exist as a stored set of data values ​​in the database. The row and column data come from the table referenced by the query that defines the view, and are generated dynamically when the view is referenced.

[0057] Client, or user end, refers to a program that corresponds to a server and provides local services to clients. Except for some applications that only run locally, they are generally installed on ordinary client computers and need to work with the server end. After the development of the Internet, the more commonly used user ends include web browsers used for the World Wide Web, email clients for sending and receiving emails, and client software for instant messaging. For this type of application, there needs to be a corresponding server and service program in the network to provide the corresponding services, such as database services, email services, etc. In this way, a specific communication connection needs to be established between the client and the server to ensure the normal operation of the application.

[0058] Any number of elements in the drawings is for illustration and not limitation, and any naming is for distinction only and does not have any limiting meaning.

[0059] In actual practice, the data obtained through SQL query based on user input is often used as the data source of the visualization report, and the view table is generated and stored locally, so that users can use new SQL statements to directly query data from the locally stored view table, and implement secondary processing of the data in the view table, such as querying, filtering, grouping, sorting, etc., to obtain better data analysis results. However, once the data in the original database changes, the data in the view table needs to be updated synchronously. Therefore, the solution of querying data from the locally stored view table requires relevant algorithm strategies for continuous data update, which is more complicated to implement, and local storage requires a large amount of storage resources, all of which lead to bloated projects and poor portability.

[0060] To this end, the present application provides a data query method, which generates a corresponding view table based on the original query statement input by the user; when the user needs to query, filter, group, sort, etc. on the data in the view table, the user can input a first query statement for indicating to query the specified data from the view table, modify the first query statement based on the original query statement, and obtain a second query statement for indicating to query the specified data from the specified database, that is, modify the data source of this query to the specified database in the original query statement, and then query the data directly from the specified database. Due to the above data query method, when processing data based on the view table, data can be queried directly from the specified database. Therefore, there is no need to synchronously update the data in the original database and the view table, and there is no need to develop and maintain complex data continuous update algorithm strategies, and there is no need to store the data in the view table locally, saving storage resources. The data query system implemented based on the above data query method has low implementation complexity and strong portability.

[0061] After introducing the design ideas of the embodiments of the present application, the following briefly introduces the application scenarios to which the technical solutions of the embodiments of the present application can be applied. It should be noted that the application scenarios introduced below are only used to illustrate the embodiments of the present application and are not limited. In specific implementation, the technical solutions provided by the embodiments of the present application can be flexibly applied according to actual needs.

[0062] refer to Figure 1 , which is a schematic diagram of an application scenario of the data query method provided in an embodiment of the present application. The application scenario includes a terminal device 101, a server 102, and multiple databases 103. Among them, the terminal device 101 and the server 102, the server 102 and each database 103 can be connected through a wireless or wired network, and the terminal device 101 includes but is not limited to electronic devices such as smart phones, laptops, desktop computers, tablet computers, media players, smart wearable devices, smart speakers, smart TVs, etc. The server 102 and the database 103 can both be independent physical servers, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms.

[0063] The terminal device 101 is installed with a data analysis client. The user can edit a custom query statement through the data analysis client. The query statement may include query fields, databases, query conditions, etc., and send the edited query statement to the server 102. The server 102 obtains data related to the query field and meeting the query conditions from the corresponding database 103 based on the query statement, and returns the queried data to the terminal device 101. The data analysis client in the terminal device 101 displays the queried data in a certain way. The user can also use the data analysis client to perform a secondary query based on the queried data, or filter, group, sort, etc. the queried data to obtain more targeted and refined data analysis results.

[0064] The user can also log in to the data analysis website through the terminal device 101 and obtain the data analysis service through the web page. The specific implementation method of the web page is similar to that of the data analysis client and will not be repeated here.

[0065] Of course, the method provided in the embodiment of the present application is not limited to Figure 1 The application scenarios shown can also be used in other possible application scenarios, and the embodiments of the present application are not limited thereto. Figure 1 The functions that can be implemented by each device in the application scenario shown will be described in the subsequent method embodiments, and will not be described in detail here.

[0066] To further illustrate the technical solution provided by the embodiment of the present application, this is described in detail below in conjunction with the accompanying drawings and specific implementation methods. Although the embodiment of the present application provides the method operation steps shown in the following embodiments or drawings, more or fewer operation steps may be included in the method based on routine or no creative labor. In the steps where there is no necessary causal relationship logically, the execution order of these steps is not limited to the execution order provided by the embodiment of the present application.

[0067] The data query method of the embodiment of the present application can be applied to the data analysis client in the terminal device, and can also be applied to the web page. Figure 1 The application scenario shown uses the application in a data analysis client as an example to illustrate the technical solution provided in the embodiment of the present application.

[0068] refer to Figure 2 The present invention provides a data query method, which specifically includes the following steps:

[0069] S201. Obtain a first query statement for instructing to query specified data from a view table, where the view table is generated based on an original query statement, where the original query statement is for instructing to query data from a specified database.

[0070] The query statement in the embodiment of the present application includes a SELECT clause (i.e., a query field clause), a FROM clause (i.e., a data source clause), a WHERE clause (i.e., a conditional clause), a GROUP BY clause, and an ORDER BY clause, etc. The embodiment of the present application mainly involves a query field clause, a data source clause, and a conditional clause. The query field clause is the string between the keywords SELECT and FROM in the query statement, the data source clause is the string between the keywords FROM and WHERE, and the conditional clause is the string after the keyword WHER. The query field clause includes at least one query field, the data source clause includes the database used when querying data, and the conditional clause includes at least one query condition. After the server receives the query statement sent by the data analysis client, it queries the data corresponding to the query field from the specified database in the data source clause, and filters the queried data based on the query condition, and returns the data that meets the query condition to the data analysis client. Of course, the user can also not set the query condition. At this time, the server directly returns the data corresponding to the query field to the data analysis client.

[0071] The following is an example of a query statement:

[0072]

[0073] In some cases, the field names of the data displayed in the data table are very long or not intuitive enough. In order to make the data table more concise and readable, you can set a corresponding alias field for the field name, display the alias field in the data table, and set the field name corresponding to the alias field to the original field. For example, if the original field is date_format(create_time,'%Y-%m-%d'), the corresponding alias field can be set to create_date. Therefore, these query fields in the query field clause can be divided into two categories, one of which contains the original field and the alias field, such as the original field in "date_format(create_time,'%Y-%m-%d')AS create_date" is date_format(create_time,'%Y-%m-%d'), the alias field is create_date, and the alias field displayed in the data table is create_date; the other is the query field without an alias, that is, only the original field, such as api_name, page_id, etc. in the above query statement example, and the original field is directly displayed in the data table.

[0074] In the specific implementation, before executing step S201, the user can open the data analysis client and enter the data table management page. The interface diagram of the data table management page can be referred to as follows: Figure 3A , click the new data table button 301 in the data table management page 30, and the following is displayed Figure 3B The new data table page 302 is shown. After the user enters the original query statement in the SQL statement bar and clicks the Parse button 303, the data analysis client will parse the alias fields in the SELECT clause and the original fields that do not contain the alias fields from the original query statement. The user can check the fields that need to be used in subsequent processing or choose to select all. In addition, you can also choose to view the detailed information of each parsed field and set the corresponding Chinese name, field type, remarks, format and other information. For fields with Chinese names, the corresponding Chinese names are displayed during subsequent processing to facilitate user operations and improve the readability of the generated data table. After the user confirms that the parsed fields are correct and sets the corresponding detailed information of each field, click the OK button 304 to trigger the data analysis client to create a new view table for the original query statement, and associate and store the original query statement, the fields obtained by parsing, the Chinese names corresponding to the fields, the correspondence between the original fields and the alias fields, and other information. Reference Figure 3B The user can also set a folder corresponding to the view table in the new data table page, and store the above-mentioned various types of information that need to be associated and stored in the folder. When the user processes the view table, the data analysis client can obtain relevant information from the folder corresponding to the view table.

[0075] refer to Figure 3C This is an editing page corresponding to a view table. The left column 305 displays the fields parsed from the original query statement corresponding to the view table, such as "uv", "creation date", "api_name", etc. If the user sets the corresponding Chinese name for the field during parsing, the corresponding Chinese name can be displayed in the left column, such as "creation date" is the Chinese name of the field "create_date". The user can select the fields to be used when drawing the data table from the left column, and add the selected fields to the dimension column 306, the value column 307 or the condition column 308. Among them, the dimension column 306 contains the fields corresponding to the horizontal axis of the data table. The fields added by the user to the dimension column 306 will appear in the GROUP BY clause in the first query statement. The value column 307 contains the fields corresponding to the vertical axis of the data table. One or more fields can be added to the value column 307. The user can also set the aggregation function corresponding to the field in the value column 307. Based on the aggregation function, the data corresponding to the field is subjected to relevant operations to obtain the corresponding value. The aggregation function includes but is not limited to: sum function (Sum), average function (Average), maximum function (Max), minimum function (Min), count function (Count), etc. The condition column 308 contains the filtering conditions when filtering data. One or more filtering conditions can be set in the condition column 308. By adding fields in the condition column 308, the WHERE clause in the first query statement can be edited. For example, the field "creation date" is added to the condition column 308, and the filtering condition set is that the creation date is from April 1, 2020 to May 30, 2020. Then, only the data with the creation date from April 1, 2020 to May 30, 2020 is obtained, and the data table 309 is drawn based on these data. Of course, the filtering condition can also be not set in actual applications. After the user has set the dimension column 306, the value column 307 and the condition column 308, he clicks the Finish button 310, which triggers the data analysis client to generate a first query statement based on the fields in the dimension column 306, the value column 307 and the condition column 308. The data source in the first query statement is the view table, the query field in the SELECT clause is the field selected from the left column 305, and the query condition in the WHERE clause is the filter condition set in the condition column 308. Therefore, the first query statement is used to indicate the specified data corresponding to the query field in the view table and meeting the filter condition, and the data table is drawn based on the obtained specified data. The drawn data table 310 is displayed in the editing page.

[0076] S202: Based on the original query statement, modify the first query statement to obtain a second query statement for instructing to query specified data from a specified database.

[0077] In specific implementation, the data analysis client extracts the name of the view table from the data source clause in the first query statement, obtains the original query statement corresponding to the view table, extracts the data source (i.e., the specified database) in the original query statement, modifies the data source in the first query statement to the specified database extracted from the original query statement, and uses the modified first query statement as the second query statement. Since the data source in the second query statement is the specified database, the specified data can be directly obtained from the specified database based on the second query statement without storing the data corresponding to the view table, and there is no need to update the data in the view table.

[0078] S203: Based on the second query statement, obtain specified data.

[0079] In specific implementation, the data analysis client sends the second query statement to the server, and the server obtains the specified data from the specified database based on the second query statement, and sends the obtained specified data to the data analysis client. The data analysis client draws and displays the corresponding data table based on the specified data returned by the server. Figure 3C An example of a data table is given in which the horizontal axis of the data table is the creation date, and the vertical axis includes uv (deduplication count value) and pv (count value) obtained based on the queried data.

[0080] A data query method provided in an embodiment of the present application, when performing data processing based on a view table, modifies a first query statement for the view table based on an original query statement corresponding to the view table, obtains a second query statement that can directly query data from a specified database of the original query statement, and directly obtains data from the specified database based on the second query statement. Therefore, there is no need to store the data in the view table locally, and there is no need to update the changed data in the specified database to the view table, and there is no need to develop and maintain complex data continuous update algorithm strategies. A large amount of local storage resources can also be saved, reducing the complexity of the data analysis client and server, and the method has strong portability.

[0081] In actual application, if the original query statement already contains GROUP BY clause, ORDER BY clause, with clause, union clause having clause, and when the first query statement is generated based on the user's operation, the GROUP BY clause is added, which will change the result set and data order of the final query result. Therefore, before executing step S201, it is also necessary to confirm whether the original query statement contains GROUP BY clause, ORDER BY clause, with clause, union clause having clause. If it does not contain the above clauses, step S201 is executed. If it contains any of the above clauses, a subquery method is used to perform data query. Among them, the subquery method is to first obtain data from the specified database based on the original query statement, and then query the specified data from the obtained data. For example, the original query statement is: SELECT a, sum(b), sum(c) FROM table GROUP BY a, and the subquery statement is: SELECTA, B FROM (select a as A, sum(b) as B, sum(c) as CFROM table) as table_alias GROUP BY A ORDER BY B; and the second query statement obtained by the method of the embodiment of the present application is: SELECT a as A, sum(b) as B FROM table GROUP BY a ORDER BY sum(b).

[0082] In order to make the final generated data table more concise and readable, the user can set a corresponding alias field for the original field in the original query statement. The subsequent data analysis client will replace the original field with the alias field of the original field. For example, the alias field corresponding to the original field is displayed, and the query field in the first query statement generated based on the user operation is also the alias field corresponding to the original field. However, the alias field is user-defined, and the alias field does not exist in the specified database. The corresponding data cannot be queried based on the alias field. For this reason, it is necessary to replace the alias field in the first query statement with the corresponding original field. Specifically, step S202 includes: according to the corresponding relationship between the original field and the alias field obtained from the original query statement, the alias field in the first query statement is replaced with the corresponding original field; the view table in the first query statement is replaced with the specified database; and the query condition in the original query statement is added to the conditional clause of the first query statement. The first query statement modified in the above manner is the required second query statement.

[0083] In specific implementation, the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause generally contain alias fields. It is necessary to identify the alias fields in the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause in the first query statement, and then replace the identified alias fields with the corresponding original fields. The data source in the first query statement is a view table, and the data source in the first query statement needs to be modified to the specified database in the original query statement.

[0084] Since the first query statement is a query statement for the data contained in the view table, it is equivalent to first filtering out the target data based on the query conditions in the original query statement, and then filtering out the specified data from the target data based on the query conditions in the first query statement. Therefore, the filtering conditions in the original query statement need to be added to the conditional clause of the first query statement.

[0085] In specific implementation, SQL is parsed on the query statement to obtain query keywords such as SELECT, FROM, WHERE, GROUP BY, and ORDER BY, and based on these query keywords, the SELECT clause, FROM clause, WHERE clause, GROUP BY clause, and ORDER BY clause are divided.

[0086] SQL parsing includes lexical analysis and grammatical / semantic analysis. Lexical analysis mainly converts query statements into multiple tokens. Tokens can be keywords (also called symbols) or non-keywords. For example, by performing lexical analysis on the query statement "SELECTusername FROM userinfo", four tokens can be obtained, of which the keyword tokens are SELECT and FROM. Syntactic analysis is based on lexical analysis to determine whether the words in the query statement conform to grammatical logic. For example, "SELECT FOO+100FROM POKES" is a grammatically correct sentence, while "SELECT FOO+100FROM" is an illegal statement because a table name must follow FROM. The grammatical analyzer will report an error at this time. Lexical analysis and grammatical analysis are existing technologies and will not be described in detail. After lexical analysis and grammatical analysis, the following can be obtained: Figure 4The abstract syntax tree shown in the figure is a tree structure representation of the query statement. Each node on the abstract syntax tree corresponds to a word. The structure of the abstract syntax tree reflects the grammar. The abstract syntax tree is constructed along the process of syntax analysis. When the syntax analysis is completed normally, the syntax analyzer will output an abstract syntax tree. The structure and content of the query statement and the abstract syntax tree are one-to-one corresponding. For example, the node SELECT corresponds to the keyword SELECT, the node Fields corresponds to the query field clause, and the query field clause can be further divided into multiple nodes, such as username and ismale, the node FROM corresponds to the keyword FROM, the node Tables corresponds to the data source clause, and the data source clause can be divided into the node userinfo, the node WHERE corresponds to the keyword WHERE, the node Conditions corresponds to the conditional clause, and the conditional clause can be further divided into multiple nodes.

[0087] When implementing it, refer to Figure 5 , the corresponding relationship can be obtained by the following method:

[0088] S501: Based on the query field separator, divide the query field clause into multiple query fields.

[0089] Specifically, a subtree portion of the node Fields in the abstract syntax tree is obtained, and the subtree portion of the node Fields is merged to obtain a query field clause.

[0090] The query field separator is the character used to separate query fields in the query field clause. Generally, the query field separator is a comma. For example, in "SELECT create_user AS count_create_user,api_name,page_id", the query fields "create_user AS count_create_user" and "api_name" are separated by a comma.

[0091] In actual applications, the SELECT clause often contains some functions, which contain the same symbols as the query field separators, such as "date_format(create_time,'%Y-%m-%d')AS create_date", and these commas usually appear in brackets. Therefore, before splitting the query field clause into multiple query fields, you need to replace the symbols in the brackets in the query field clause that are the same as the query field separator with preset special characters, and then split the query field after the replacement symbols into multiple query fields based on the query field separator. Finally, replace the special characters in multiple query fields with the query field separator, so as to avoid erroneous splitting.

[0092] S502. For any query field containing alias definition characters, obtain the original field and alias field having a corresponding relationship from any query field, wherein the alias definition characters are characters used to define a corresponding relationship between the original field and the alias field.

[0093] The alias definition character is determined by the SQL statement, and the alias definition character can be AS, CASE, or a space character. The alias definition character contained in the query field can be identified by regular matching.

[0094] In specific implementation, step S502 specifically includes the following steps:

[0095] If the alias definition character contained in any query field is AS, the field before AS in any query field is determined as the original field, and the field after AS in any query field is determined as the alias field corresponding to the original field;

[0096] If the alias definition character contained in any query field is CASE, the field between CASE and END in any query field is determined as the original field, and the field after END in any query field is determined as the alias field corresponding to the original field;

[0097] If any query field does not contain AS and CASE, and any query field contains a space character, the field before the space character in any query field is determined as the original field, and the field after the space character in any query field is determined as the alias field corresponding to the original field.

[0098] For example, in the query field "Sum(C+2)AS c", the original field is "Sum(C+2)" and the alias field is "c". The original field in the query field "CASE WHEN substr(E,9,2)<'06'THEN'Midnight snack'WHEN substr(E,9,2)<'10'THEN'Breakfast'WHEN substr(E,9,2)<'15'THEN'Lunch'WHEN substr(E,9,2)<'20'THEN'Dinner'ELSE'Midnight snack'AS e" is "WHEN substr(E,9,2)<'06'THEN'Midnight snack'WHEN substr(E,9,2)<'10'THEN'Breakfast'WHEN substr(E,9,2)<'15'THEN'Lunch'WHEN substr(E,9,2)<'20'THEN'Dinner'ELSE'Midnight snack'", and the alias field is "e". There is a space between B and b in the query field "B b", so the original field is "B" and the alias field is "b".

[0099] Based on any of the above-mentioned implementation modes, the data query method of the embodiment of the present application also includes the following steps: if the original field corresponding to the alias field has used an aggregation function, the alias field is marked as a field that cannot use an aggregation function; when an aggregation function is used on a field marked as a field that cannot use an aggregation function, a prompt message that the aggregation function cannot be used is output.

[0100] For example, in "Sum(C+2)AS c", the original field Sum(C+2) corresponding to the alias field c has already used the aggregate function Sum, so c is actually a numeric value and the aggregate function cannot be used on this numeric value. Therefore, the data analysis client will mark the alias field c as a field that cannot use aggregate functions. When the user wants to add the alias field c to the numeric column, a prompt message will be generated that the alias field cannot use aggregate functions to prevent errors caused by secondary aggregation.

[0101] Based on any of the above implementations, the query condition in the original query statement may be added to the condition clause of the first query statement in the following manner: before the query condition in the condition clause of the first query statement, the query condition in the original query statement is added.

[0102] For example, if the query condition in the original query statement is: A>10and B<1, and the condition clause of the first query statement is: WHERE C>2, then the condition clause of the second query statement is: WHERE A>10and B<1and C>2. In this way, the query condition in the original query statement will be executed first, and then the query condition in the first query statement will be executed, which improves the index hit rate.

[0103] Based on any of the above implementations, the user can also add a placeholder representing a custom condition in the condition clause of the original query statement, so that subsequent users can customize the query condition. Figure 3C , users can enter custom query conditions through the custom condition input box in the condition bar.

[0104] Based on this, the method of the embodiment of the present application also includes the following steps: if it is determined that the first query statement contains a custom query condition, the custom query condition in the first query statement is deleted, and the placeholder added to the first query statement is replaced with the custom query condition.

[0105] For example, the conditional clause of the original query statement is: WHERE A>100and D{D}, where D{D} is a placeholder for a custom condition. When the user enters a filter condition named D, such as D>100, D{D} will be replaced with D>100. For example, the user enters the condition: WHERE C>2and D>100. First, the query condition of the original query statement is added to the first query statement, and the obtained conditional clause is: WHERE A>100and D{D}and C>2and D>100. Then, D>100 in the above conditional sentence is deleted, and D{D} is replaced as a whole with D>100. Finally, the conditional clause of the second query statement is: WHERE A>100and D>100and C>2. If the user does not enter a custom query condition, the data analysis client will replace D{D} as a whole with 1=1. Since the condition 1=1 is true, the server executes 1=1, which does not affect the query results and query performance.

[0106] In actual applications, a first-level view table is generated based on the original query statement Q0 input by the user. The original query statement Q0 is used to instruct to query data from a specified database. The user can also perform query operations on the first-level view table. At this time, the data analysis client will generate a new second-level view table based on the query statement Q1 input for the first-level view table. When the user performs a query operation on the second-level view table, the data analysis client obtains the query statement Q2 for instructing to query the specified data from the second-level view table, modifies the query statement Q2 based on the query statement Q1, and obtains the query statement Q'2 for instructing to query the specified data from the first-level view table; then, the query statement Q'2 is modified based on the original query statement Q0 to obtain the query statement Q"2 for instructing to query the specified data from the specified database; based on the query statement Q"2, the specified data is obtained from the specified database.

[0107] Among them, the method of modifying query statement Q2 based on query statement Q1 can refer to the specific implementation method of modifying the first query statement based on the original query statement provided in the embodiment of the present application, for example, according to the correspondence between the original field and the alias field obtained from query statement Q1, the alias field in query statement Q2 is replaced with the corresponding original field; the view table in query statement Q2 is replaced with the data source (i.e., the first-level view table) in query statement Q1; and the query condition in query statement Q1 is added to the conditional clause of query statement Q2. Among them, reference can be made to Figure 5 In the manner shown, the corresponding relationship between the original field and the alias field is obtained from the query statement Q1.

[0108] The specific implementation of modifying the query statement Q'2 based on the original query statement Q0 may refer to the specific implementation of modifying the first query statement based on the original query statement provided in the embodiment of the present application, and will not be described in detail.

[0109] Of course, users can also create a third-level view table based on the second-level view table, and a fourth-level view table based on the third-level view table, and so on. When querying data, after obtaining the query statement Q for instructing to query the specified data from the N-level view table, N When the query statement Q corresponding to the N-1 level view table is N-1 , modify the query statement Q N , obtain the query statement Q' used to instruct to query the specified data from the N-1 level view table N ; Query statement Q corresponding to the N-2 level view table N-2 , modify the query statement Q' N , obtain the query statement Q used to instruct to query the specified data from the N-2 level view table" N Similarly, by recursively optimizing query statements level by level, query statements for N-level view tables can be eventually converted into query statements that directly query data from specified data, that is, query statements that do not contain subqueries, thereby improving query efficiency and optimizing query performance.

[0110] like Figure 6 As shown, based on the same inventive concept as the above-mentioned data query method, the embodiment of the present application further provides a data query device 60 , including an acquisition module 601 , a modification module 602 and a query module 603 .

[0111] An acquisition module 601 is used to acquire a first query statement for instructing to query specified data from a view table, where the view table is generated based on an original query statement, and the original query statement is used to instruct to query data from a specified database;

[0112] A modification module 602 is used to modify the first query statement based on the original query statement to obtain a second query statement for instructing to query specified data from a specified database;

[0113] The query module 603 is used to obtain specified data based on the second query statement.

[0114] Optionally, the modification module 602 is specifically used to: replace the alias field in the first query statement with the corresponding original field based on the correspondence between the original field and the alias field obtained from the original query statement; replace the view table in the first query statement with the specified database; and add the query condition in the original query statement to the conditional clause of the first query statement.

[0115] Optionally, the data query device 60 further includes a parsing module 604, which is used to obtain the corresponding relationship by the following method:

[0116] Based on the query field separator, the query field clause is divided into multiple query fields;

[0117] For any query field containing alias definition characters, an original field and an alias field having a corresponding relationship are obtained from any query field, wherein the alias definition characters are characters used to define a corresponding relationship between the original field and the alias field.

[0118] Optionally, the parsing module 604 is specifically configured to:

[0119] If the alias definition character contained in any query field is AS, the field before AS in any query field is determined as the original field, and the field after AS in any query field is determined as the alias field corresponding to the original field;

[0120] If the alias definition character contained in any query field is CASE, the field between CASE and END in any query field is determined as the original field, and the field after END in any query field is determined as the alias field corresponding to the original field;

[0121] If any query field does not contain AS and CASE, and any query field contains a space character, the field before the space character in any query field is determined as the original field, and the field after the space character in any query field is determined as the alias field corresponding to the original field.

[0122] Optionally, the parsing module 604 is specifically configured to:

[0123] Replace the symbols in the brackets in the query field clause that are the same as the query field separator with the preset special characters;

[0124] Based on the query field separator, the query field after the replacement symbol is divided into multiple query fields;

[0125] Replace special characters in multiple query fields with query field separators.

[0126] Optionally, the data query device 60 further includes a marking module, which is used to:

[0127] If the original field corresponding to the alias field has used an aggregate function, the alias field will be marked as a field that cannot use an aggregate function;

[0128] When you use an aggregate function on a field that is marked as not being able to use an aggregate function, a prompt message is displayed indicating that the aggregate function cannot be used.

[0129] Optionally, the modifying module 602 is specifically configured to: add the query condition in the original query statement before the query condition in the condition clause of the first query statement.

[0130] Optionally, when the condition clause of the original query statement contains a placeholder corresponding to the custom condition, the modification module 602 is further used to delete the custom query condition in the first query statement if it is determined that the first query statement contains the custom query condition, and replace the placeholder added to the first query statement with the custom query condition.

[0131] The data query device provided in the embodiment of the present application adopts the same inventive concept as the above-mentioned data query method and can achieve the same beneficial effects, which will not be described in detail here.

[0132] Based on the same inventive concept as the above data query method, the embodiment of the present application also provides an electronic device, which can be a desktop computer, a portable computer, a smart phone, a tablet computer, a personal digital assistant (PDA), a server, etc. Figure 7 As shown, the electronic device 70 may include a processor 701 and a memory 702 .

[0133] Processor 701 can be a general-purpose processor, such as a central processing unit (CPU), a digital signal processor (DigitalSignal Processor, DSP), an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC), a field programmable gate array (Field Programmable Gate Array, FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, and can implement or execute the various methods, steps and logic block diagrams disclosed in the embodiments of the present application. A general-purpose processor can be a microprocessor or any conventional processor, etc. The steps of the method disclosed in conjunction with the embodiments of the present application can be directly embodied as a hardware processor to be executed, or a combination of hardware and software modules in the processor can be executed.

[0134] The memory 702 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer executable programs and modules. The memory may include at least one type of storage medium, such as a flash memory, a hard disk, a multimedia card, a card-type memory, a random access memory (Random Access Memory, RAM), a static random access memory (Static Random Access Memory, SRAM), a programmable read-only memory (Programmable Read Only Memory, PROM), a read-only memory (Read Only Memory, ROM), an electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, EEPROM), a magnetic memory, a disk, an optical disk, etc. The memory is any other medium that can be used to carry or store a desired program code in the form of an instruction or data structure and can be accessed by a computer, but is not limited thereto. The memory 702 in the embodiment of the present application can also be a circuit or any other device that can realize a storage function, for storing program instructions and / or data.

[0135] An embodiment of the present application provides a computer-readable storage medium for storing computer program instructions used for the above-mentioned electronic device, which includes a program for executing the above-mentioned data query method.

[0136] The above-mentioned computer storage media can be any available media or data storage devices that can be accessed by a computer, including but not limited to magnetic storage (such as floppy disks, hard disks, magnetic tapes, magneto-optical disks (MO), etc.), optical storage (such as CD, DVD, BD, HVD, etc.), and semiconductor storage (such as ROM, EPROM, EEPROM, non-volatile memory (NAND FLASH), solid-state drive (SSD)), etc.

[0137] The above embodiments are only used to introduce the technical solutions of the present application in detail, but the description of the above embodiments is only used to help understand the methods of the embodiments of the present application and should not be understood as limiting the embodiments of the present application. Any changes or substitutions that can be easily thought of by technicians in this technical field should be included in the protection scope of the embodiments of the present application.

Claims

1. A data query method, characterized in that: Applied to data analysis clients, including: Obtaining a first query statement for instructing to query specified data from a local view table, wherein the view table is generated based on an original query statement, and the original query statement is used to instruct to query data from a specified database; Based on the original query statement, modify the first query statement to obtain a second query statement for instructing to query the specified data from the specified database; Based on the second query statement, obtaining the specified data from the server; The step of modifying the first query statement based on the original query statement specifically includes: According to the correspondence between the original fields and the alias fields obtained from the original query statement, the alias fields in the first query statement are replaced with the corresponding original fields; the view table in the first query statement is replaced with the specified database; and the query conditions in the original query statement are added to the conditional clause of the first query statement.

2. The method according to claim 1, characterized in that The corresponding relationship is obtained by the following method: Based on the query field separator, the query field clause is divided into multiple query fields; For any query field containing alias definition characters, an original field and an alias field having a corresponding relationship are obtained from the any query field, wherein the alias definition characters are characters used to define a corresponding relationship between the original field and the alias field.

3. The method according to claim 2, characterized in that The step of acquiring, for any query field containing alias definition characters, an original field and an alias field having a corresponding relationship from any query field specifically includes: If the alias definition character contained in any query field is AS, the field before AS in any query field is determined as the original field, and the field after AS in any query field is determined as the alias field corresponding to the original field; If the alias definition character contained in any query field is CASE, the field between CASE and END in any query field is determined as the original field, and the field after END in any query field is determined as the alias field corresponding to the original field; If any of the query fields does not contain AS and CASE, and any of the query fields contains a space character, the field before the space character in any of the query fields is determined as the original field, and the field after the space character in any of the query fields is determined as the alias field corresponding to the original field.

4. The method according to claim 2, characterized in that: The step of dividing the query field clause into multiple query fields based on the query field separator specifically includes: Replace the symbol in the brackets in the query field clause that is the same as the query field separator with a preset special character; Based on the query field separator, the query field after the replacement symbol is divided into multiple query fields; Special characters in the multiple query fields are replaced with the query field separator.

5. The method according to any one of claims 1 to 4, characterized in that: The method further comprises: If the original field corresponding to the alias field has used an aggregate function, the alias field is marked as a field that cannot use an aggregate function; When you use an aggregate function on a field that is marked as not being able to use an aggregate function, a prompt message is displayed indicating that the aggregate function cannot be used.

6. The method according to any one of claims 2 to 4, characterized in that: The adding the query condition in the original query statement to the conditional clause of the first query statement specifically includes: The query condition in the original query statement is added before the query condition in the conditional clause of the first query statement.

7. The method according to claim 6, characterized in that When the condition clause of the original query statement contains a placeholder corresponding to the custom condition, the method further includes: If it is determined that the first query statement includes a custom query condition, the custom query condition in the first query statement is deleted, and the placeholder added to the first query statement is replaced with the custom query condition.

8. A data query device, characterized in that: include: An acquisition module, used to acquire a first query statement for instructing to query specified data from a local view table, wherein the view table is generated based on an original query statement, and the original query statement is used to instruct to query data from a specified database; A modification module, used for modifying the first query statement based on the original query statement to obtain a second query statement for instructing to query the specified data from the specified database; A query module, configured to obtain the specified data from a server based on the second query statement; The modification module is specifically used to: replace the alias field in the first query statement with the corresponding original field according to the correspondence between the original field and the alias field obtained from the original query statement; replace the view table in the first query statement with the specified database; and add the query condition in the original query statement to the first query statement.

9. The device according to claim 8, characterized in that The device also includes a parsing module, which is used to obtain the corresponding relationship by the following method: Based on the query field separator, the query field clause is divided into multiple query fields; For any query field containing alias definition characters, an original field and an alias field having a corresponding relationship are obtained from the any query field, wherein the alias definition characters are characters used to define a corresponding relationship between the original field and the alias field.

10. The device according to claim 9, characterized in that The analysis module is specifically used for: If the alias definition character contained in any query field is AS, the field before AS in any query field is determined as the original field, and the field after AS in any query field is determined as the alias field corresponding to the original field; If the alias definition character contained in any query field is CASE, the field between CASE and END in any query field is determined as the original field, and the field after END in any query field is determined as the alias field corresponding to the original field; If any of the query fields does not contain AS and CASE, and any of the query fields contains a space character, the field before the space character in any of the query fields is determined as the original field, and the field after the space character in any of the query fields is determined as the alias field corresponding to the original field.

11. The device according to claim 9, characterized in that The analysis module is specifically used for: Replace the symbol in the brackets in the query field clause that is the same as the query field separator with a preset special character; Based on the query field separator, the query field after the replacement symbol is divided into multiple query fields; Special characters in the multiple query fields are replaced with the query field separator.

12. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 7 are implemented.

13. A computer-readable storage medium having computer program instructions stored thereon, characterized in that: When the computer program instructions are executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images