Data query method and device
By combining the big data computing engine and syntax parser to generate and convert SQL syntax trees, the problem of insufficient data query performance in the existing technology is solved, efficient data management and query across databases is realized, and learning and migration costs are reduced. .
Patent Information
- Application Number
- CN202410923020.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-10
- Publication Date
- 2024-11-08
AI Technical Summary
The data query method based on conventional relational databases in the prior art cannot meet the performance requirements of big data query, and the query method based on the big data computing engine has high learning and migration costs due to the unique structured query language.
Combining the big data computing engine with the syntax parser, by generating and converting the SQL syntax tree, cross-query and management of data between different databases is realized to meet the performance needs of big data.
The SQL that uses commonly used databases to cross-query and manage data of different types of databases is realized, which reduces learning and migration costs and meets the performance requirements of big data.
Smart Images

Figure CN118916373A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, in particular to the field of intelligent search technology, and can also be used in the financial field, and specifically to a data query method and device. Background Art
[0002] With the development of big data, artificial intelligence and machine learning, database query technology is also constantly evolving. There are two main methods for realizing cross-database query management in the existing technology. One is based on a conventional relational database, using a syntax parser to parse the SQL statements of the commonly used database into a syntax tree, and then convert it into the SQL of the corresponding relational database, and finally use the relational database to store data for query; the other is to use big data structured query language as an intermediate language, based on the query method of the big data computing engine.
[0003] However, conventional relational databases are limited by performance bottlenecks and cannot meet the needs of big data queries. Even if there are programs implemented based on big data computing engines, since query SQL can only use the structured query language unique to the big data computing engine, a lot of learning and migration costs are incurred. Summary of the invention
[0004] In order to solve at least one of the above technical problems, the present application proposes a data query method and device, which combines a big data computing engine and a syntax parser to realize the function of cross-query and data management of data in different databases using the SQL of a commonly used database, while meeting the requirements of big data performance.
[0005] According to a first aspect of the present application, a data query method is provided, comprising:
[0006] Receive an original data query statement input by a user on a data query platform, and determine a database type of the original data query statement;
[0007] Generate an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model;
[0008] The original syntax tree is converted into a target query statement required by the data query platform.
[0009] In some optional manners of this embodiment, determining the database type of the original data query statement includes:
[0010] In response to the original data query statement including database connection information, determining a database type of the original data query statement according to the database connection information;
[0011] In response to the original data query statement including a preset keyword, a preset grammar, or a preset function, determining a database type of the original data query statement according to the preset keyword, the preset grammar, or the preset function;
[0012] In response to the original data query statement including a preset system table name and a preset view name, a database type of the original data query statement is determined according to the preset system table name and the preset view name.
[0013] In some optional manners of this embodiment, generating an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model includes:
[0014] Determining, according to the database type and the SQL syntax analysis model, a SQL dialect characteristic corresponding to the database type, wherein the SQL syntax analysis model stores a mapping relationship between the database type and the SQL dialect characteristic, and the SQL dialect characteristic is used to characterize structural features of keywords, operators, identifiers, numbers, and character strings;
[0015] According to the SQL dialect characteristics, an original syntax tree corresponding to the original data query statement is generated.
[0016] In some optional aspects of this embodiment, the SQL syntax analysis model includes a lexical analyzer, a syntax analyzer, and a semantic analyzer, and generating an original syntax tree corresponding to the original data query statement according to the SQL dialect characteristics includes:
[0017] Based on the lexical analyzer, decomposing the original data query statement into a plurality of original data query sub-statements, wherein the original data query sub-statements include keywords, operators, identifiers, numbers and character strings;
[0018] Based on the syntax analyzer and the SQL dialect characteristics, construct a syntax parse tree for the multiple original data query sub-statements;
[0019] Based on the semantic analyzer, perform semantic checking on the grammatical parse tree;
[0020] In response to determining that the semantics of the grammar parse tree is correct, the grammar parse tree is determined to be the original grammar tree.
[0021] In some optional aspects of this embodiment, converting the original syntax tree into a target query statement required by the data query platform includes:
[0022] Determining a preset query statement specification of the data query platform;
[0023] According to the preset query statement specification, converting the original syntax tree into a target syntax tree;
[0024] According to the target syntax tree, a target query statement required by the data query platform is determined.
[0025] In some optional aspects of this embodiment, after converting the original syntax tree into a target query statement required by the data query platform, the method further includes:
[0026] According to the database type, determining a target database;
[0027] Determining whether to perform a real-time query based on the target query statement;
[0028] If yes, query the target database based on the target query statement to obtain a query result;
[0029] If not, determine whether the local data stores the data of the target database, wherein:
[0030] If yes, query the local data based on the target query statement to obtain the query result;
[0031] If not, the target database is queried based on the target query statement to obtain the query result, and the query result is saved to the local database.
[0032] In some optional manners of this embodiment, the number of the target databases is one or more, and in response to the target databases including a first database and a second database, querying the target database based on the target query statement includes:
[0033] Determine a first target query sub-statement in the target query statement for querying the first database;
[0034] Determine a second target query sub-statement in the target query statement for querying the second database;
[0035] querying the first database based on the first target query sub-statement to obtain a first query sub-result;
[0036] Query the second database based on the second target query sub-statement to obtain a second query sub-result;
[0037] Merging the first query sub-result and the second query sub-result to obtain the query result, and sending the query result to the data query platform;
[0038] The database type of the first database is different from the database type of the second database, and both the first database and the second database are configured to establish a connection with the data query platform.
[0039] According to a second aspect of the present application, a data query device is provided, comprising:
[0040] A database type determination module is configured to receive an original data query statement input by a user on a data query platform, and determine the database type of the original data query statement;
[0041] An original syntax tree determination module is configured to generate an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model;
[0042] The target query statement determination module is configured to convert the original syntax tree into a target query statement required by the data query platform.
[0043] According to a third aspect of the present application, an electronic device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the above-mentioned data query method when executing the computer program.
[0044] According to a fourth aspect of the present application, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and the computer program implements the above-mentioned data query method when executed by a processor.
[0045] According to a fifth aspect of the present application, a computer program product is provided, the computer program product comprising a computer program, and the computer program implements the above-mentioned data query method when executed by a processor.
[0046] The data query method and device provided in the embodiment of the present invention combine a big data computing engine and a syntax parser, and realize the function of cross-querying and data management of data in different databases using the SQL of a common database, while meeting the requirements of big data performance. BRIEF DESCRIPTION OF THE DRAWINGS
[0047] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art descriptions. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. In the drawings:
[0048] Figure 1is an exemplary system architecture diagram to which the embodiments of the present invention can be applied;
[0049] Figure 2 One of the exemplary flow charts of the data query method according to the embodiment of the present invention;
[0050] Figure 3 This is a second exemplary flow chart of the data query method according to an embodiment of the present invention;
[0051] Figure 4 This is a third exemplary flow chart of the data query method according to an embodiment of the present invention;
[0052] Figure 5 This is a fourth exemplary flow chart of the data query method according to an embodiment of the present invention;
[0053] Figure 6 This is a fifth exemplary flow chart of the data query method according to an embodiment of the present invention;
[0054] Figure 7 This is a sixth exemplary flow chart of the data query method according to an embodiment of the present invention;
[0055] Figure 8 This is the seventh exemplary flow chart of the data query method according to the embodiment of the present invention;
[0056] Fig. 9 This is an exemplary flowchart of the data query method of an embodiment of the present invention;
[0057] Fig.10 A schematic diagram of the structure of a data query device according to an embodiment of the present invention;
[0058] Fig.11 The present invention is a block diagram of an electronic device for implementing the data query method according to an embodiment of the present invention. DETAILED DESCRIPTION
[0059] To make the purpose, technical solution and advantages of the embodiments of the present invention more clear, the embodiments of the present invention are further described in detail below in conjunction with the accompanying drawings. Here, the exemplary embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention.
[0060] The information collected in the technical solution of this application is information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of relevant data comply with the relevant laws, regulations and standards of relevant countries and regions, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entrances for users to choose to authorize or refuse.
[0061] Specifically, a corresponding operation entrance is provided for users to choose to agree or reject the automated decision-making results; if the user chooses to reject, the expert decision-making process will be entered.
[0062] Figure 1 An exemplary system architecture to which an embodiment of a data query method or a data query device of the present application can be applied is shown.
[0063] like Figure 1 As shown, the system architecture 100 may include terminal devices 101, 102, 103, a network 104 and a server 105. The network 104 is used to provide a medium for communication links between the terminal devices 101, 102, 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.
[0064] Users can use terminal devices 101, 102, 103 to interact with server 105 through network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, 103, such as web browser applications or search applications and other data query platforms.
[0065] Terminal devices 101, 102, 103 can be hardware or software. When terminal devices 101, 102, 103 are hardware, they can be various electronic devices, including but not limited to clients such as smart phones, tablet computers, e-book readers, laptop computers, and desktop computers. When terminal devices 101, 102, 103 are software, they can be installed in the electronic devices listed above. They can be implemented as multiple software or software modules (for example, to provide distributed services), or they can be implemented as a single software or software module. No specific limitation is made here.
[0066] The server 105 may be a server that provides various services, such as a backend server that processes the original data query statements input by the user through the terminal devices 101, 102, and 103. The backend server may analyze and process the acquired original data query statements and other data, and feed back the processing results (such as query results) to the terminal devices 101, 102, and 103.
[0067] It should be noted that the server 105 can be hardware or software. When the server 105 is hardware, it can be implemented as a distributed server cluster consisting of multiple servers, or it can be implemented as a single server. When the server 105 is software, it can be implemented as multiple software or software modules (for example, for providing distributed services), or it can be implemented as a single software or software module. No specific limitation is made here.
[0068] It should be understood that Figure 1The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to the implementation requirements.
[0069] It should be noted that the data query method provided in the embodiment of the present application is generally executed by the server 105. Accordingly, a data query device is generally disposed in the server 105.
[0070] like Figure 2 As shown, the embodiment of the present application provides a data query method, including:
[0071] Step 10: Receive the original data query statement input by the user on the data query platform, and determine the database type of the original data query statement.
[0072] In this step, the user enters the original query statement on the data query platform. The original query statement here can be an SQL statement of a commonly used database type, that is, the query statement entered by the user on the data query platform may not be the preset query statement specification of the data query platform, but any type of SQL statement that the user is familiar with can be entered, thereby greatly reducing the learning and migration costs.
[0073] It should be noted that the commonly used database types include the following and the databases listed below are all open source:
[0074] Relational database management system (RDBMS): A database based on the relational model that uses tables to store data and supports the SQL query language. Examples include MySQL, PostgreSQL, etc.
[0075] Non-relational database (NoSQL): does not use traditional table relationships to store data, but uses documents, key-value pairs, column families, or graphs to organize data. For example: MongoDB (document type), Redis (key-value pair type), Cassandra (column family type), etc.
[0076] Object-oriented database management system (OODBMS): A database system used to store object data models in object-oriented programming. For example: db4o.
[0077] Graph Database: A database designed for graph structures, suitable for storing graph and network data. For example: Neo4j, ArangoDB.
[0078] In-memory Database: Stores data in memory to speed up data access and improve performance. For example: Redis, Memcached.
[0079] Time Series Database: A database specifically used to process time series data, such as sensor data, log data, etc. For example: InfluxDB, Prometheus.
[0080] Columnar Database: Stores data in columns, suitable for analysis and aggregation of large amounts of data. For example: ClickHouse.
[0081] Distributed Database: A database system that stores data in multiple physical or logical locations and supports distributed computing and storage. For example: HBase.
[0082] Furthermore, the execution subject of the data query method ( Figure 1 After receiving the original query statement, the server 105 in the example may also reversely determine the corresponding database type according to the parameters carried by the original query statement. In some optional methods of this embodiment, determining the database type of the original data query statement in step 10 includes:
[0083] In response to the original data query statement including database connection information, a database type of the original data query statement is determined according to the database connection information.
[0084] Specifically, the connection information for accessing the database, such as the database server name or IP address, port number, etc., can be used to determine the type of database to be used.
[0085] In response to the original data query statement including a preset keyword, a preset grammar, or a preset function, the database type of the original data query statement is determined according to the preset keyword, the preset grammar, or the preset function.
[0086] Specifically, a specific database system has its own unique functions and syntax. By checking whether certain functions or syntax specific to a certain database system are used in the original query statement, the type of database used can be inferred; in addition, the keywords and grammatical structure used in the original query statement can be checked. Different database systems may differ in syntax and keywords. For example, MySQL and PostgreSQL may differ in some query functions and keywords, thereby determining the database type corresponding to the original query statement.
[0087] In response to the original data query statement including a preset system table name and a preset view name, a database type of the original data query statement is determined according to the preset system table name and the preset view name.
[0088] Specifically, the naming and structure of system tables and views in different database systems may be different. If the original query statement involves system tables or views, the database type can be inferred based on their naming conventions and structural characteristics.
[0089] Step 20: Generate an original syntax tree corresponding to the original data query statement according to the database type and the pre-built SQL syntax analysis model.
[0090] In this step, the execution subject of the data query method is intended to generate an original syntax tree corresponding to the original data query statement according to the aforementioned determined database type and the pre-built SQL syntax analysis model.
[0091] It should be understood that the syntax tree is a tree data structure generated by the SQL syntax analysis model according to the input original data query statement, which represents the syntax structure of the input text.
[0092] In some optional embodiments of this embodiment, Figure 3 As shown, step 20 further includes:
[0093] Step 201: Determine the SQL dialect characteristics corresponding to the database type according to the database type and the SQL syntax analysis model, wherein the SQL syntax analysis model stores a mapping relationship between the database type and the SQL dialect characteristics, and the SQL dialect characteristics are used to characterize structural features of keywords, operators, identifiers, numbers, and character strings.
[0094] Specifically, in this application, an extensible database management system interface is designed to support access to multiple different types of databases, and for each database, its unique SQL dialect characteristics are defined and mapped to the SQL syntax analysis model, that is, the SQL syntax analysis model stores the mapping relationship between the database type and the SQL dialect characteristics. Therefore, according to the database type and the mapping relationship stored in the SQL syntax analysis model, the SQL dialect characteristics corresponding to the database type can be determined.
[0095] For example, MySQL is an open source relational database management system, and its SQL dialect features include: using LIMIT and OFFSET to paginate result sets; there are some specific functions, such as DATE_FORMAT() for date formatting, IFNULL() for handling null values, etc.; support for specific data types, such as AUTO_INCREMENT for creating auto-increment fields; there are some specific functions, such as DATE_FORMAT() for date formatting, IFNULL() for handling null values, etc.; support for transaction processing, but the auto-commit mode is used by default.
[0096] For another example, PostgreSQL is a powerful open source relational database whose SQL dialect features include: supporting more complex and richer data types, such as arrays, JSON, XML, etc.; providing richer index types and query optimization functions; supporting more advanced SQL standards, such as window functions and common expressions; providing more powerful transaction control, supporting savepoints and more flexible transaction management.
[0097] Step 202: Generate an original syntax tree corresponding to the original data query statement according to the SQL dialect characteristics.
[0098] Specifically, in the present application, an original syntax tree corresponding to the original data query statement is further generated based on the aforementioned determined SQL dialect characteristics, which requires in-depth syntax analysis and understanding, and a detailed understanding of the SQL dialect specifications of the database, and the use of corresponding tools or parsers to analyze the query statement and construct its syntax tree.
[0099] To this end, in some optional embodiments of this embodiment, the SQL syntax analysis model includes a lexical analyzer, a syntax analyzer, and a semantic analyzer, such as Figure 4 As shown, step 202 generates an original syntax tree corresponding to the original data query statement according to the SQL dialect characteristics, including:
[0100] Step 2021: Based on the lexical analyzer, decompose the original data query statement into multiple original data query sub-statements, wherein the original data query sub-statements include keywords, operators, identifiers, numbers, and character strings.
[0101] Specifically, the input original data query statement is decomposed into multiple original data query sub-statements, such as keywords, operators, identifiers, numbers, and strings, based on the lexical analyzer, to ensure that the lexical units in the SQL dialects of various databases can be accurately identified.
[0102] Step 2022: Based on the syntax analyzer and the SQL dialect characteristics, construct a syntax parse tree for the multiple original data query sub-statements.
[0103] Specifically, optionally, a general SQL syntax analysis model is constructed according to the SQL syntax specifications and characteristics of various databases. The model should be able to parse and identify the SQL statement structure, keywords, functions, data types, etc. of different databases. On the basis of lexical analysis, a syntax analyzer is used to construct a syntax parse tree (Parse Tree) according to predefined SQL syntax rules and multiple original data query sub-statements to ensure that the syntax structure of the SQL dialects of different databases can be processed.
[0104] Step 2023: Based on the semantic analyzer, perform semantic checking on the grammatical parse tree.
[0105] Specifically, based on the syntax analysis, a semantic check is performed to verify the semantic correctness of the SQL statement, such as data type matching and function parameter validity.
[0106] Step 2024: In response to determining that the semantics of the grammar parse tree is correct, determine the grammar parse tree as the original grammar tree.
[0107] Specifically, when it is determined that the semantics of the grammatical parse tree is correct, the obtained grammatical parse tree is determined as the original grammatical tree; if the semantics are incorrect, the operations of step 2021 to step 2023 are re-executed until the semantics of the grammatical parse tree is correct, thereby improving the accuracy of the original grammatical tree and the accuracy of data query.
[0108] Step 30: Convert the original syntax tree into a target query statement required by the data query platform.
[0109] In this step, after the original syntax tree is determined, it is also necessary to convert the original syntax tree into a target query statement required by the data query platform, and perform data query based on the target query statement.
[0110] In some optional embodiments of this embodiment, Figure 5 As shown, the converting of the original syntax tree into the target query statement required by the data query platform includes:
[0111] Step 301: Determine a preset query statement specification of the data query platform, wherein the preset query statement specification includes a predetermined grammatical structure, data type, query optimization and index, special functions and extensions, etc.
[0112] Specifically, the data query platform has its own unique SQL dialect and query syntax rules. Generally speaking, the rules of preset query statements involve the following aspects:
[0113] Syntax structure: Determine the SQL statement types (such as SELECT, INSERT, UPDATE, DELETE) and their syntax structures supported by the data query platform. For example, the field selection, table name, and WHERE clause conditions of the SELECT statement.
[0114] Data types: Determine the data types supported by the data query platform, such as integers, strings, dates, etc., and how to use these data types in queries.
[0115] Query optimization and indexing: Determine how the data query platform optimizes query performance, including the creation and use of indexes. Different databases may support different types of indexes, such as B-tree indexes, hash indexes, or column storage indexes.
[0116] Special features and extensions: Determine whether the data query platform can provide specific features or extensions, such as window functions, full-text search, or geospatial query, and understand how to use these features in the data query platform.
[0117] Step 302: Convert the original syntax tree into a target syntax tree according to the preset query statement specification.
[0118] Specifically, each node in the original syntax tree is replaced with a format such as keywords, operators, functions, etc. specified in a preset query statement specification to obtain a target syntax tree.
[0119] Step 303: Determine the target query statement required by the data query platform according to the target syntax tree.
[0120] Specifically, according to the parameters such as keywords, operators or functions of each node on the target syntax tree, the target syntax tree is converted into the target query statement required by the data query platform.
[0121] In some optional embodiments of this embodiment, Figure 6 As shown, after converting the original syntax tree into the target query statement required by the data query platform, it also includes:
[0122] Step 40: Determine the target database according to the database type.
[0123] Specifically, the database type can be determined according to the aforementioned original query statement, so that the query location of the data query platform can be determined, that is, to query data from the target database.
[0124] Step 50: Determine whether to perform a real-time query based on the target query statement.
[0125] Specifically, if real-time data query is required, the target database is queried based on the target query statement to obtain a query result, which is the latest real-time data; if real-time data query is not required, determine whether the local data stores the data of the target database, wherein: if the data of the target database is stored, the local data is queried based on the target query statement to obtain the query result, which is historical data; if the data of the target database is not stored, the target database is queried based on the target query statement to obtain the query result, which is the latest real-time data, and the query result is saved to the local data.
[0126] In this application, local or remote query methods are supported. If the data query speed requirement is high, the data can be queried locally; if the real-time requirement for the data query is high, the query is performed from a remote target database. This application does not limit this.
[0127] In some optional aspects of this embodiment, the number of the target databases may be one or more. In a specific example, Figure 7 As shown, in response to the target database including the first database and the second database, querying the target database based on the target query statement includes:
[0128] Step 501: determine a first target query sub-statement in a target query statement for querying the first database;
[0129] Step 502: determining a second target query sub-statement in the target query statement for querying the second database;
[0130] Step 503: query the first database based on the first target query sub-statement to obtain a first query sub-result;
[0131] Step 504: query the second database based on the second target query sub-statement to obtain a second query sub-result;
[0132] Step 505: merge the first query sub-result and the second query sub-result to obtain the query result, and send the query result to the data query platform;
[0133] The database type of the first database is different from the database type of the second database, and both the first database and the second database are configured to establish a connection with the data query platform.
[0134] Specifically, the data query method based on the present application can also realize cross-database query, taking cross-database query between two databases as an example. The target query statement includes a first target query sub-statement for querying the first database and a second target query sub-statement for querying the second database, and queries the first database based on the first target query sub-statement to obtain a first query sub-result and queries the second database based on the second target query sub-statement to obtain a second query sub-result; finally, the first query sub-result and the second query sub-result are merged to obtain the query result, and the query result is sent to the data query platform for users to view the query results.
[0135] It should be noted that, during cross-database query, the database type of the first database is different from the database type of the second database, and the cross-database query between the above two databases is an example, and may also be a query between more different types of databases.
[0136] Since it is a cross-database type cross-query, it is also necessary to establish connections between multiple databases and the data query platform. Establishing connections between databases usually involves the following steps:
[0137] Determine database type and location: Determine the type (e.g., MySQL, PostgreSQL, MongoDB, etc.) and location (IP address, host name, port number, etc.) of each database.
[0138] Select the appropriate connection method: Select the appropriate connection method based on the database type, such as using a database-specific connection library or ODBC (Open Database Connectivity) driver.
[0139] Configure connection parameters: Provide the required details for the connection such as username, password, database name, etc.
[0140] Write code or configuration files: Write connection code using the programming language of your choice (such as Python, Java, C#, etc.) or configure connection files so that your application can access and operate these databases.
[0141] Test the connection: Write simple test code or command-line queries to ensure that the connection is working and that you can perform basic database operations.
[0142] The present application provides a method of combining a big data computing engine and a syntax parser to implement the function of cross-querying and managing data in different databases using the SQL of a common database, while meeting the requirements of big data performance.
[0143] Next, Figure 8The detailed process of cross-database cross-query in the present invention is summarized, wherein:
[0144] Step S101, establishing database connection: because it is a cross-database query, multiple database connections need to be established;
[0145] Step S102, identifying the database type: the program configuration will record the database connection information, including the database type, and the lexical analysis and syntax analysis of S104 and S105 need to be performed according to the database type;
[0146] Step S103, reading SQL: the SQL statement for cross-database query uses the SQL language syntax of the same database, and the database information, table information, etc. will be converted into SQL specific to the big data platform and then passed to the big data computing engine;
[0147] Step S104, performing lexical analysis on SQL;
[0148] Step S105, perform grammatical analysis on tokens to generate a grammar tree;
[0149] Step S106: convert the syntax tree into SQL specific to the big data engine (data query platform), namely, the target SQL. Specifically, replace each node in the syntax tree with keywords, operators, functions, etc. of the target type SQL, and then convert the syntax tree into the target type SQL;
[0150] Step S107, judge the current configuration, determine whether it is necessary to query real-time data, if it is necessary to query real-time data every time, execute step S108 to use the target SQL to query data from the remote end, if not, execute step S111 to query whether there is corresponding cached data in the local cache, if yes, execute step S112 to execute the target SQL to query data from the local, and return the local cached data, otherwise execute step S121 to use the target SQL to query data from the remote end, and execute step S122 to cache the data locally, and finally execute step S109 to return the result to the user.
[0151] So far, the above technical solutions are summarized. The purpose of the present invention is to use the SQL of common relational databases to call the big data computing platform module to perform cross-database cross-query. Fig. 9 As shown, because the SQL specific to the big data platform is used to achieve the above purpose, the present invention uses the SQL specific to the big data platform as the target language, converts the SQL of the common relational database into a syntax tree, and then converts the syntax tree into the target language SQL, and finally executes the cross-database query in the big data computing engine. Specifically:
[0152] The query SQL (i.e., the original query statement) input during the query is passed through a syntax parser to obtain a syntax tree, and then passed through a language converter to obtain SQL specific to the data query platform. The data query platform queries data results from databases connected to the data query platform, such as type A database and / or type B database.
[0153] A data query device is also provided in the embodiments of the present invention, as described in the following embodiments. Since the principle of solving the problem by the device is similar to that of the data query method, the implementation of the device can refer to the implementation of the data query method, and the repeated parts are not repeated. As used below, the term "unit" or "module" can be a combination of software and / or hardware that implements a predetermined function. Although the system described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware is also possible and conceived.
[0154] like Fig.10 As shown, the data query device includes:
[0155] The database type determination module 601 is configured to receive an original data query statement input by a user on the data query platform, and determine the database type of the original data query statement;
[0156] The original syntax tree determination module 602 is configured to generate an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model;
[0157] The target query statement determination module 603 is configured to convert the original syntax tree into a target query statement required by the data query platform.
[0158] In some optional manners of this embodiment, determining the database type of the original data query statement includes:
[0159] In response to the original data query statement including database connection information, determining a database type of the original data query statement according to the database connection information;
[0160] In response to the original data query statement including a preset keyword, a preset grammar, or a preset function, determining a database type of the original data query statement according to the preset keyword, the preset grammar, or the preset function;
[0161] In response to the original data query statement including a preset system table name and a preset view name, a database type of the original data query statement is determined according to the preset system table name and the preset view name.
[0162] In some optional manners of this embodiment, generating an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model includes:
[0163] Determining, according to the database type and the SQL syntax analysis model, a SQL dialect characteristic corresponding to the database type, wherein the SQL syntax analysis model stores a mapping relationship between the database type and the SQL dialect characteristic, and the SQL dialect characteristic is used to characterize structural features of keywords, operators, identifiers, numbers, and character strings;
[0164] According to the SQL dialect characteristics, an original syntax tree corresponding to the original data query statement is generated.
[0165] In some optional aspects of this embodiment, the SQL syntax analysis model includes a lexical analyzer, a syntax analyzer, and a semantic analyzer, and generating an original syntax tree corresponding to the original data query statement according to the SQL dialect characteristics includes:
[0166] Based on the lexical analyzer, decomposing the original data query statement into a plurality of original data query sub-statements, wherein the original data query sub-statements include keywords, operators, identifiers, numbers and character strings;
[0167] Based on the syntax analyzer and the SQL dialect characteristics, construct a syntax parse tree for the multiple original data query sub-statements;
[0168] Based on the semantic analyzer, perform semantic checking on the grammatical parse tree;
[0169] In response to determining that the semantics of the grammar parse tree is correct, the grammar parse tree is determined to be the original grammar tree.
[0170] In some optional aspects of this embodiment, converting the original syntax tree into a target query statement required by the data query platform includes:
[0171] Determining a preset query statement specification of the data query platform;
[0172] According to the preset query statement specification, converting the original syntax tree into a target syntax tree;
[0173] According to the target syntax tree, a target query statement required by the data query platform is determined.
[0174] In some optional aspects of this embodiment, after converting the original syntax tree into a target query statement required by the data query platform, the method further includes:
[0175] According to the database type, determining a target database;
[0176] Determining whether to perform a real-time query based on the target query statement;
[0177] If yes, query the target database based on the target query statement to obtain a query result;
[0178] If not, determine whether the local data stores the data of the target database, wherein:
[0179] If yes, query the local data based on the target query statement to obtain the query result;
[0180] If not, the target database is queried based on the target query statement to obtain the query result, and the query result is saved to the local database.
[0181] In some optional manners of this embodiment, the number of the target databases is one or more, and in response to the target databases including a first database and a second database, querying the target database based on the target query statement includes:
[0182] Determine a first target query sub-statement in the target query statement for querying the first database;
[0183] Determine a second target query sub-statement in the target query statement for querying the second database;
[0184] querying the first database based on the first target query sub-statement to obtain a first query sub-result;
[0185] Query the second database based on the second target query sub-statement to obtain a second query sub-result;
[0186] Merging the first query sub-result and the second query sub-result to obtain the query result, and sending the query result to the data query platform;
[0187] The database type of the first database is different from the database type of the second database, and both the first database and the second database are configured to establish a connection with the data query platform.
[0188] It should be noted that the data query method and device provided in the embodiments of the present invention can be used in the financial field, and can also be used in any technical field other than the financial field. The embodiments of the present invention do not limit the application field of the query method and device.
[0189] According to an embodiment of the invention, an electronic device, a readable storage medium and a computer program product are also provided.
[0190] An electronic device comprises: at least one processor; and a memory connected to the at least one processor in communication; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the steps of the data query method of the aforementioned embodiment.
[0191] A non-transitory computer-readable storage medium storing computer instructions, wherein the computer instructions are used to enable a computer to execute the steps of the data query method of the aforementioned embodiment.
[0192] A computer program product includes a computer program / instruction, which implements the steps of the data query method of the above embodiment when the computer program / instruction is executed by a processor.
[0193] Fig.11 A schematic block diagram of an example electronic device 900 that can be used to implement an embodiment of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present disclosure described and / or required herein.
[0194] like Fig.11 As shown, the device 900 includes a computing unit 901, which can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 902 or a computer program loaded from a storage unit 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the device 900 can also be stored. The computing unit 901, the ROM 902, and the RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.
[0195] A number of components in the device 900 are connected to the I / O interface 905, including: an input unit 906, such as a keyboard, a mouse, etc.; an output unit 907, such as various types of displays, speakers, etc.; a storage unit 908, such as a disk, an optical disk, etc.; and a communication unit 909, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 909 allows the device 900 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.
[0196] The computing unit 901 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the computing unit 901 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, digital signal processors (DSPs), and any appropriate processors, controllers, microcontrollers, etc. The computing unit 901 performs the various methods and processes described above, such as a data query method.
[0197] For example, in some embodiments, a data query method may be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit 908. In some embodiments, part or all of the computer program may be loaded and / or installed on the device 900 via the ROM 902 and / or the communication unit 909. When the computer program is loaded into the RAM 903 and executed by the computing unit 901, one or more steps of a data query method described above may be performed. Alternatively, in other embodiments, the computing unit 901 may be configured to perform a data query method in any other suitable manner (e.g., by means of firmware).
[0198] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.
[0199] The program code for implementing the method of the present disclosure may be written in any combination of one or more programming languages. These program codes may be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.
[0200] In the context of the present disclosure, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0201] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the computer. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).
[0202] The systems and techniques described herein may be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), and the Internet.
[0203] A computer system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The relationship of client and server is generated by computer programs running on respective computers and having a client-server relationship with each other. The server may be a cloud server, a server of a distributed system, or a server combined with a blockchain.
[0204] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in this disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of this disclosure can be achieved, and this document is not limited here.
[0205] The above specific implementations do not constitute a limitation on the protection scope of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present disclosure shall be included in the protection scope of the present disclosure.
Claims
1. A data query method, characterized in that: include: Receive an original data query statement input by a user on a data query platform, and determine a database type of the original data query statement; Generate an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model; The original syntax tree is converted into a target query statement required by the data query platform.
2. The method according to claim 1, characterized in that The determining the database type of the original data query statement includes: In response to the original data query statement including database connection information, determining a database type of the original data query statement according to the database connection information; In response to the original data query statement including a preset keyword, a preset grammar, or a preset function, determining a database type of the original data query statement according to the preset keyword, the preset grammar, or the preset function; In response to the original data query statement including a preset system table name and a preset view name, a database type of the original data query statement is determined according to the preset system table name and the preset view name.
3. The method according to claim 1, characterized in that: The step of generating an original syntax tree corresponding to the original data query statement according to the database type and the pre-built SQL syntax analysis model includes: Determining, according to the database type and the SQL syntax analysis model, a SQL dialect characteristic corresponding to the database type, wherein the SQL syntax analysis model stores a mapping relationship between the database type and the SQL dialect characteristic, and the SQL dialect characteristic is used to characterize structural features of keywords, operators, identifiers, numbers, and character strings; According to the SQL dialect characteristics, an original syntax tree corresponding to the original data query statement is generated.
4. The method according to claim 3, characterized in that The SQL syntax analysis model includes a lexical analyzer, a syntax analyzer, and a semantic analyzer. The generating of an original syntax tree corresponding to the original data query statement according to the SQL dialect characteristics includes: Based on the lexical analyzer, decomposing the original data query statement into a plurality of original data query sub-statements, wherein the original data query sub-statements include keywords, operators, identifiers, numbers and character strings; Based on the syntax analyzer and the SQL dialect characteristics, construct a syntax parse tree for the multiple original data query sub-statements; Based on the semantic analyzer, perform semantic checking on the grammatical parse tree; In response to determining that the semantics of the grammar parse tree is correct, the grammar parse tree is determined to be the original grammar tree.
5. The method according to claim 1, characterized in that The converting the original syntax tree into a target query statement required by the data query platform includes: Determining a preset query statement specification of the data query platform; According to the preset query statement specification, converting the original syntax tree into a target syntax tree; According to the target syntax tree, a target query statement required by the data query platform is determined.
6. The method according to claim 1, characterized in that After converting the original syntax tree into the target query statement required by the data query platform, the method further includes: According to the database type, determining a target database; Determining whether to perform a real-time query based on the target query statement; If yes, query the target database based on the target query statement to obtain a query result; If not, determine whether the local data stores the data of the target database, wherein: If yes, query the local data based on the target query statement to obtain the query result; If not, the target database is queried based on the target query statement to obtain the query result, and the query result is saved to the local database.
7. The method according to claim 6, characterized in that The number of the target databases is one or more. In response to the target databases including a first database and a second database, querying the target database based on the target query statement includes: Determine a first target query sub-statement in the target query statement for querying the first database; Determine a second target query sub-statement in the target query statement for querying the second database; querying the first database based on the first target query sub-statement to obtain a first query sub-result; Query the second database based on the second target query sub-statement to obtain a second query sub-result; Merging the first query sub-result and the second query sub-result to obtain the query result, and sending the query result to the data query platform; The database type of the first database is different from the database type of the second database, and both the first database and the second database are configured to establish a connection with the data query platform.
8. A data query device, characterized in that: include: A database type determination module is configured to receive an original data query statement input by a user on a data query platform, and determine the database type of the original data query statement; An original syntax tree determination module is configured to generate an original syntax tree corresponding to the original data query statement according to the database type and a pre-built SQL syntax analysis model; The target query statement determination module is configured to convert the original syntax tree into a target query statement required by the data query platform.
9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the data query method according to any one of claims 1 to 7 is implemented.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the data query method according to any one of claims 1 to 7 is implemented.
11. A computer program product, characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the data query method according to any one of claims 1 to 7 is implemented.
Citation Information
Patent Citations
SQL conversion method and device, and storage medium
CN111143403A
Query method and system based on multi-data engine dynamic loading
CN113032423A
Data query method and device based on online analysis processing, medium and equipment
CN116955413A
SQL conversion method and device, computer equipment and storage medium
CN117033348A
Cited By
Enterprise database query statement conversion method and device based on large model, equipment and storage medium
CN121255844A
