Data query method and device, equipment and storage medium

Abstract

The invention discloses a data query method, device and equipment and a storage medium, and relates to the technical field of data processing.The method comprises the steps that a data query instruction and a target query database corresponding to a current data query task are determined according to a data query request; detecting whether the data query instruction accords with a database grammar specification corresponding to the target query database; if not, performing grammar conversion on the data query instruction based on a preset grammar tree analysis mode to obtain a target data query instruction; and performing data query based on the target data query instruction. According to the method, when the data query instruction does not conform to the grammar specification of the target database, grammar conversion can be performed on the data query instruction, and data query is performed based on the target data query instruction obtained after conversion; therefore, the technical problems that in the prior art, during cross-database query, query scripts need to be frequently modified to adapt to grammar differences of different database engines, time is consumed, and errors are prone to occurring are solved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, and particularly relates to a data query method and device, equipment and a storage medium. BACKGROUND

[0002] With the rapid development of information technology, enterprises and organizations are increasingly dependent on data. Data query, as a core link of data management and analysis, directly affects the timeliness and accuracy of business decisions.

[0003] Currently, modern enterprises usually use multiple database engines to meet different business needs, such as relational databases (such as MySQL, PostgreSQL), big data processing engines (such as Hive, Impala), and other professional databases (such as GAUSS). However, these database engines have significant differences in syntax and functions, making users need to frequently modify query scripts when querying across databases, which is time-consuming and prone to errors. SUMMARY

[0004] The main purpose of the present application is to provide a data query method, device, equipment and storage medium, which aims to solve the technical problems that the query script needs to be frequently modified to adapt to the syntax differences of different database engines when querying across databases in the prior art, which is time-consuming and prone to errors.

[0005] To achieve the above purpose, the present application provides a data query method, which comprises: In response to a data query request triggered by a user, and according to the data query request, determining a data query instruction and a target query database corresponding to a current data query task; Detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database; If not, performing syntax conversion on the data query instruction based on a preset syntax tree analysis method to obtain a target data query instruction, the target data query instruction being adapted to the target query database; Based on the target data query instruction, performing data query to obtain a data query result of the current data query task.

[0006] In an embodiment, before the step of detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database, the method further comprises: Obtaining a target database connection corresponding to the data query instruction based on a preset database connection pool; The step of performing data query based on the target data query instruction to obtain a data query result of the current data query task comprises: sending the target data query instruction to the target query database through the target database connection; executing the target data query instruction through the target query database to obtain a data query result of the current data query task.

[0007] In an embodiment, the step of obtaining the target database connection corresponding to the data query instruction based on the preset database connection pool comprises: judging whether the target database connection corresponding to the data query instruction exists in the preset database connection pool; if the target database connection exists, directly obtaining the target database connection from the preset database connection pool; or, if the target database connection does not exist, creating the target database connection in the preset database connection pool.

[0008] In an embodiment, the step of performing syntax conversion on the data query instruction based on the preset syntax tree parsing manner to obtain a target data query instruction comprises: parsing the data query instruction based on the preset syntax tree parsing manner to generate a syntax tree structure corresponding to the data query instruction; adjusting the syntax tree structure according to the database syntax specification to obtain an adjusted syntax tree structure; generating a target data query instruction adapted to the target query database based on the adjusted syntax tree structure.

[0009] In an embodiment, the step of adjusting the syntax tree structure according to the database syntax specification to obtain an adjusted syntax tree structure comprises: determining a structure adjustment strategy corresponding to the syntax tree structure according to the database syntax specification; if the structure adjustment strategy is a parameter adjustment strategy, determining a to-be-adjusted parameter and a target adjustment parameter of the syntax tree structure; replacing the to-be-adjusted parameter with the target adjustment parameter to obtain an adjusted syntax tree structure.

[0010] In an embodiment, before the step of responding to a data query request triggered by a user and determining a data query instruction and a target query database corresponding to a current data query task according to the data query request, the method further comprises: loading pre-configured data source information from a background database; generating a data source list according to the data source information and displaying the data source list on a front-end page; determining target data source information selected by the user from the data source list; generate a data query instruction corresponding to the current data query task based on the target data source information.

[0011] In an embodiment, the step of detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database comprises: detecting whether the data query instruction conforms to a syntax rule defined in the database syntax specification corresponding to the target query database; if so, obtaining target metadata information corresponding to the target query database; matching the metadata information in the data query instruction with the target metadata information; detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database according to the matching result.

[0012] In addition, to achieve the above object, the present application further provides a data query device, which comprises: an instruction determination module configured to determine a data query instruction and a target query database corresponding to a current data query task in response to a data query request triggered by a user; a syntax detection module configured to detect whether the data query instruction conforms to a database syntax specification corresponding to the target query database; a syntax conversion module configured to, if not, perform syntax conversion on the data query instruction based on a preset syntax tree analysis manner to obtain a target data query instruction, wherein the target data query instruction is adapted to the target query database; a data query module configured to perform data query based on the target data query instruction to obtain a data query result of the current data query task.

[0013] In addition, to achieve the above object, the present application further provides a data query device, which comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data query method as described above.

[0014] In addition, to achieve the above object, the present application further provides a storage medium, which is a computer readable storage medium, and a computer program is stored on the storage medium, wherein the computer program is executed by a processor to implement the steps of the data query method as described above.

[0015] The application provides a data query method, and discloses responding to a data query request triggered by a user, determining a data query instruction corresponding to a current data query task and a target query database according to the data query request, detecting whether the data query instruction conforms to a database syntax specification corresponding to the target query database, performing syntax conversion on the data query instruction based on a preset syntax tree analysis mode if the data query instruction does not conform to the database syntax specification, obtaining a target data query instruction, the target data query instruction being adapted to the target query database, performing data query based on the target data query instruction, and obtaining a data query result of the current data query task. Compared with the prior art, the database engine performing data query has significant differences in syntax and function, so that the user needs to frequently modify a query script when performing cross-database query. The application can perform syntax conversion on the data query instruction if the data query instruction does not conform to the syntax specification of the target database, and perform data query based on the target data query instruction obtained after conversion, thereby solving the technical problem that the query script needs to be frequently modified to adapt to the syntax difference of different database engines when performing cross-database query in the prior art, and improving the data query efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0016] The accompanying drawings, which are incorporated into and constitute a part of the specification, illustrate embodiments consistent with the application and, together with the specification, serve to explain the principles of the application.

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the application or the prior art, the accompanying drawings needed in the embodiments or the prior art description will be briefly introduced. Obviously, for those skilled in the art, other drawings can also be obtained based on these drawings without creative labor.

[0018] Figure 1 A flowchart provided by the data query method embodiment of the application; Figure 2 A system architecture diagram of the data query method of the application; Figure 3 A system physical deployment diagram of the data query method of the application; Figure 4 A flowchart of data configuration in the data query method of the application; Figure 5 A flowchart of data query in the data query method of the application; Figure 6 A query connection maintenance diagram in the data query method of the application; Figure 7 A flowchart of data export in the data query method of the application; Figure 8A flowchart provided for the second embodiment of the data query method of the present application is shown in FIG. 1. Figure 9 A flowchart provided for the third embodiment of the data query method of the present application is shown in FIG. 2. Figure 10 A flowchart provided for the SQL statement conversion of the data query method of the present application is shown in FIG. 3. Figure 11 A module structure diagram of the data query device of the embodiment of the present application is shown in FIG. 4. Figure 12 A device structure diagram of the hardware running environment involved in the data query method of the embodiment of the present application is shown in FIG. 5.

[0019] The object, function features and advantages of the present application will be further explained with reference to the embodiments and the accompanying drawings. DETAILED DESCRIPTION

[0020] It should be understood that the specific embodiments described herein are merely intended to explain the technical solutions of the present application, and are not intended to limit the present application.

[0021] In order to better understand the technical solutions of the present application, the following will be described in detail with reference to the drawings and specific embodiments.

[0022] The main solution of the embodiment of the present application is: in response to a data query request triggered by a user, and according to the data query request, determining a data query instruction and a target query database corresponding to a current data query task; detecting whether the data query instruction conforms to a database syntax specification corresponding to the target query database; if not, performing syntax conversion on the data query instruction based on a preset syntax tree analysis manner to obtain a target data query instruction, the target data query instruction being adapted to the target query database; and performing data query based on the target data query instruction to obtain a data query result of the current data query task.

[0023] Due to the significant differences in syntax and function of the database engines for data query in the prior art, the user needs to frequently modify the query script when cross-database query, which is time-consuming and prone to errors.

[0024] The present application provides a solution, which can perform syntax conversion on the data query instruction when the data query instruction does not conform to the syntax specification of the target database, and perform data query based on the target data query instruction obtained after conversion, thereby solving the technical problem that the user needs to frequently modify the query script to adapt to the syntax difference of different database engines when cross-database query in the prior art, which is time-consuming and prone to errors, and thereby improving the data query efficiency.

[0025] It should be noted that the execution subject of the embodiment can be a computing service device with data processing, network communication and program running functions, such as a tablet computer, a personal computer, a mobile phone, etc., or an electronic device capable of realizing the above functions, a data query device, or a data query system containing the data query device, etc. The following takes the data query system as an example (hereinafter referred to as the system) to illustrate the embodiment and the following embodiments.

[0026] Based on this, the embodiment of the present application provides a data query method, which refers to Figure 1 , Figure 1 The flowchart provided for the data query method embodiment one of the present application.

[0027] In the embodiment, the data query method includes steps S10-S40: Step S10: in response to a data query request triggered by a user, and according to the data query request, determining a data query instruction and a target query database corresponding to a current data query task.

[0028] It should be noted that the data query method refers to Figure 2 , Figure 2 The system architecture diagram of the data query method of the present application. As Figure 2As shown, the system in the present application can adopt a three-layer architecture design to realize complete decoupling of the presentation layer, the business logic layer and the data access layer, and communication between layers can be carried out through explicit interfaces, and independent deployment and horizontal expansion are supported. Among them, the presentation layer (front page) can be used as a visual interface for direct interaction of users, and this layer can provide a complete SQL editing and data operation environment, which includes a data source connection module, a metadata display module, a data batch import and export module, an SQL operation module, a result display module, a data source management module, a user configuration management module, a script management and sharing module, a historical SQL query module and an SQL syntax conversion module. In practical applications, the data source connection module is responsible for establishing and maintaining TCP long connection with the underlying database, and it can work cooperatively with the backend service through WebSocket to manage the life cycle of the connection; the metadata display module can send query commands to the background service through WebSocket, and display the metadata relationship of the database in a clear tree structure (such as library→table→column), so as to facilitate user to write SQL, in addition, the module can asynchronously obtain and render metadata to improve the interface response speed; the data batch import and export module supports users to import and export data in batch through formats such as CSV and TXT, and can customize configurations such as separator, at the same time, the module can adopt a hybrid transmission strategy (such as exporting to HDFS first) to balance performance and reliability; the SQL operation module can provide a SQL code editor with syntax highlighting and intelligent prompts to improve the efficiency and accuracy of users writing complex scripts. The result display module can diversely present the SQL execution result, and support to display query data in table form, structured text to display execution plan, and clear error information prompt; the data source management module can display the data source list pre-configured by the administrator in the background, so as to realize centralized management of connection configuration, at the same time, the module supports users to directly select the configured data source within the permission range without repeating the input of connection account and password; the script management and sharing module allows users to save the edited SQL script to object storage, and can generate a sharing link for others to view or use, realizing collaboration; the historical SQL query module can record and display the user's personal SQL execution history, facilitating traceability and reuse; the SQL syntax conversion module can provide one-key conversion function between Hive syntax and Impala syntax, which can realize accurate conversion of syntax based on syntax tree parsing technology. In addition, the presentation layer can also realize user login authentication to verify user identity and check access rights, ensuring that users can only operate authorized database resources, thereby realizing the association mapping of user accounts and queryable databases.

[0029] In the present application, the business logic layer (background service) is the core of the system, which provides various business capabilities in the form of microservices, ensures architecture decoupling and cloud native deployment, which can include database operation service, configuration management service, script management service and syntax conversion service. In practical applications, the database operation service can encapsulate all database operations (such as database connection, SQL execution, metadata acquisition, data import and export) into a unified WebSocket interface, and it can use WebSocket long connection to maintain database session state to meet the ACID characteristics of transactions, at the same time, this module can also be responsible for executing traffic control to prevent resource overload. The configuration management service can encrypt and persistently store user personalized configurations (such as interface theme, query settings) to ensure that the configuration can be synchronized and take effect when the user logs in on different browsers or devices. The script management service can manage the SQL scripts saved by the user, including script storage, reading, permission control (sharing and unsharing), etc. The syntax conversion service can provide a core SQL syntax conversion engine, which can parse and refactor SQL statements based on tools such as ANTLR4. In addition, the data access layer in the present application is the data persistence end of the system, which can include various data storage resources such as GAUSS, Hive, Impala, Mysql, TdSQL, etc.

[0030] It should be noted that the above description refers to Figure 3 , Figure 3 the system physical deployment diagram of the data query method of the present application. As Figure 3As shown, the system in the present application can include a front-end access layer, a back-end service layer and a data infrastructure layer, wherein a plurality of front-end instances can be deployed in the front-end access layer, which are usually run on web servers such as Nginx, Apache, etc., and are responsible for providing static resources such as HTML, CSS, JavaScript, etc. The back-end service layer can include database operation services, configuration management services, script management services, syntax conversion services and a plurality of independent microservices, each of which can focus on a specific business capability, for example, the database operation service is responsible for maintaining the WebSocket long connection with the database and processing all SQL execution requests; the configuration management service is responsible for the persistence and synchronization of user settings. At the same time, the microservices can communicate with each other through a service registration and discovery mechanism, support dynamic expansion and contraction, for example, when the SQL execution pressure increases, the number of instances of the database operation service can be increased independently. The data infrastructure layer is provided with an OLAP database cluster, a system configuration database and a script object storage, wherein the OLAP database cluster (such as Hive, Impala, GaussDB, etc.) is the data engine for user actual query and analysis; the system configuration database is usually a relational database (such as MySQL) or a key-value storage (such as Redis), which is used to store system metadata such as user configuration, data source information, etc.; the script object storage (such as HDFS-based storage or object storage) is used to save the SQL script files shared and archived by the user.

[0031] It should be understood that the above data query request can be an instruction initiated by the user through the front-end interface, instructing the system to perform a specific data query operation. In the present embodiment, the user can trigger the data query request by inputting a SQL query statement in the front-end page, selecting a query condition or other interactive ways.

[0032] It can be understood that the above current data query task can be a specific query task instance that needs to be executed at present, which can contain all relevant information of the query request, such as query instruction, target database, user context, etc., which is not limited in the present embodiment.

[0033] It should be noted that the above data query instruction can be an instruction for data query in the database, that is, a specific expression of the operation to be performed in the target database subsequently, wherein the target query database can be a specific database instance that needs to execute the query instruction, which can be determined by the data source selected by the user in the front-end interface.

[0034] In actual application, a user can input a query statement in a SQL edit box of a front-end interface, select a target data source, and click an "execute" button, at which time a data query request can be triggered. Then, a front-end page in the system can capture and analyze the data query request triggered by the user, and send the query instruction carried in the request and the data source information selected by the user to a back-end service. After receiving the query request, the back-end service can create a current data query task, which can record the following information: query instruction: the SQL query statement input by the user; target data source: the data source information selected by the user; user context: user identity and permission information, and the like. In this embodiment, the system can determine the data query instruction and the target query database corresponding to the current data query task by analyzing the data query request.

[0035] Further, before the step S10, the method further includes: loading pre-configured data source information from a background database; generating a data source list according to the data source information, and displaying the data source list on a front-end page; determining target data source information selected by the user from the data source list; and generating a data query instruction corresponding to the current data query task based on the target data source information.

[0036] It should be understood that the above background database can be a database for storing and managing all configuration information, such as MySQL or PostgreSQL, in which data source information, user permissions, query history, and the like can be stored, thereby providing data support for the back-end service of the system.

[0037] It can be understood that the above data source information can be detailed parameters required for connecting to a target database, such as database type, host address, port number, username, password, and the like. Correspondingly, the above data source list can be a collection containing all available data source information, which is usually displayed in a list form on a front-end page for user selection. The front-end page can be an interface for user interaction with the system, through which the user can perform operations such as inputting a query instruction, selecting a data source, and viewing a query result.

[0038] It should be noted that the above target data source information can be detailed information of a data source selected by the user from the data source list, which can be used to establish a database connection.

[0039] In specific implementation, reference can be made to Figure 4 , Figure 4 a flowchart of a data configuration process in the data query method of the present application. As shown in Figure 4As shown, in the present application, a user can access a front-end instance of a SQL editor through a browser and make query configurations in a configuration editing box, such as configuring query maximum row number, query timeout, query encoding, user-defined query environment variables, etc. When the user clicks the "save" button, the front-end application can encapsulate the collected configuration parameters and send them to the configuration management service of the back-end service layer through an HTTP API request to implement database configuration. When the system starts, the back-end service can load all data source information from the background database and display the loaded data source information in the form of a list on the front-end page, so that the user can select a data source from the data source list displayed on the front-end page. At this time, the front-end page can capture the user's selection event and send the data source ID selected by the user to the back-end service, so that the back-end service can obtain the corresponding data source detailed information from the memory according to the data source ID to determine the target data source information. Thereafter, the system can integrate the query statement input by the user in the SQL editing box on the front-end page and the target data source information into a data query instruction corresponding to the current data query task.

[0040] Step S20: detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database.

[0041] It should be noted that the above database syntax specification can be the SQL syntax and semantic rules supported by the engine (such as MySQL, Hive, Impala, etc.) corresponding to the target query database. In actual application, each database engine has its unique syntax and function, and these rules define the keywords, syntax structure and statement format of its legal query statement, etc.

[0042] Further, the step S20 includes: detecting whether the data query instruction conforms to the syntax rules defined in the database syntax specification corresponding to the target query database; if so, obtaining the target metadata information corresponding to the target query database; matching the metadata information in the data query instruction with the target metadata information; and detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database according to the matching result.

[0043] It can be understood that the syntax rules defined in the database syntax specification are the structure and format specifications of the SQL query statement supported by the target database, and these rules can define which keywords, syntax structure and statement format are legal. For example, the syntax rules of MySQL: support SELECT, INSERT, UPDATE, DELETE and other standard SQL statements; the syntax rules of Hive: support SQL-like syntax, but have some specific extensions, such as INSERT OVERWRITE.

[0044] It should be understood that the above target metadata information can be detailed information about the database structure stored in the target database, including table name, field name, field type, index, etc., which can be used to verify whether the objects in the query instruction exist and meet the semantic requirements. Correspondingly, the metadata information in the data query instruction can be the database objects (such as table name, field name, etc.) referenced in the query statement input by the user. The embodiment can match these information with the metadata information of the target database to verify its legality.

[0045] In practical applications, the system can load the syntax rules of the target database, and use a syntax parsing tool to parse the data query instruction input by the user to generate a syntax tree, and then verify whether the generated syntax tree is legal according to the syntax rules of the target database. For example, if there is a node in the syntax tree that does not conform to the syntax rules, it is determined that the query instruction does not conform to the syntax specification. Thereafter, the system can match the metadata information of the target database, such as table name, field name, etc., with the metadata information in the query instruction to verify whether the table name and field name exist, and whether the field type matches, etc. Finally, the results of syntax parsing and metadata matching are combined to determine whether the query instruction completely conforms to the syntax rules and semantic requirements of the target database. If the data query instruction does not meet the requirements, specific error information can be fed back to the user to help the user correct the query instruction.

[0046] Step S30: If not, syntax conversion is performed on the data query instruction based on a preset syntax tree parsing method to obtain a target data query instruction, which is adapted to the target query database.

[0047] It should be noted that the above preset syntax tree parsing method can be a method for parsing and converting SQL query statements. Correspondingly, the above target data query instruction can be a query statement that is converted by syntax and adapted to the syntax specification of the target query database. In this embodiment, by parsing the SQL query statement through the syntax tree, the query statement can be decomposed into a structured syntax tree, so that the syntax tree can be adjusted and converted according to the syntax specification of the target database. For example, if the query statement input by the user is for Hive, and the target database is Impala, since Impala does not support the INSERT OVERWRITE syntax in the query statement, the query statement needs to be parsed through the syntax tree, and the INSERT OVERWRITE syntax therein needs to be adjusted to obtain the target data query instruction adapted to the target query database.

[0048] In practical applications, the system can load the syntax specification of the target database and use a syntax parsing tool (such as ANTLR4) to parse the query command input by the user, generate a syntax tree, and then verify the validity of the generated syntax tree according to the syntax specification of the target database. If there are nodes in the syntax tree that do not conform to the syntax of the target database, it is determined that the query command does not conform to the syntax specification. At this time, it is necessary to perform syntax conversion on the data query command to obtain the target data query statement that conforms to the syntax specification of the target query database.

[0049] Step S40: Perform a data query based on the target data query instruction to obtain the data query result of the current data query task.

[0050] In the specific implementation, refer to Figure 5 , Figure 5 This is a flowchart illustrating the data query process in the data query method of this application. For example... Figure 5 As shown, users can first write or enter SQL statements in the SQL editor on the DWS_WEB main page and click the execute button to trigger a data query request. Then, the front-end application in the system can send the SQL statement to the back-end database operation service via a WebSocket long connection. After receiving the SQL query request from the front-end, the database operation service can send the data query instruction to the target database via a JDBC connection, at which point the target database can execute the SQL statement. During SQL execution, the front-end can first initiate an SQL partitioning request to the server, allowing the server to divide the SQL into several sub-SQLs and store the partitioned sub-SQLs in a set of SQLs to be executed. To avoid loading massive amounts of data at once and causing memory overflow on the server or front-end, the front-end can initiate SQL execution line by line, allowing the database operation service to retrieve data query results from the database in batches until the batch number is reached or SQL parsing is complete. After obtaining the result set data for each batch, the server can push it to the front-end in real time, batch by batch, via the same WebSocket connection. After receiving each batch of data, the front-end can cache the query results for the current batch and, after all batches have been received, merge the batch results to generate the final data query result. During the query process, users can click the "Stop" or "Interrupt" button at any time. At this time, the front end will call the breakSQL interface to send a query termination command to the back end. After receiving the command, the back end service can terminate the query task running on the database.

[0051] In practical applications, after the data query ends, the WebSocket connection is usually not closed immediately, but remains active to support subsequent operations (such as commit or rollBack) that can be in the same transaction context, or new query requests, until the user actively disconnects or the connection times out, to achieve connection multiplexing, so as to avoid the overhead of frequently establishing new connections. Referring to Figure 6 , Figure 6 is a schematic diagram of the query connection in the data query method of the present application. As shown in Figure 6 , the user can select different databases (such as Hive database, Gauss database) that need to be connected through the data source management function in the front-end interface, at which time the front-end application and the database operation service in the back-end can establish a WebSocket long connection, when the user operates different databases through different SQL editor boxes in the front-end, the database operation service in the back-end will receive the instructions sent by the front-end through WebSocket, and create and maintain a JDBC connection to the target database for each window or data source, so that multiple different database connections can be opened through the front-end editor box, and multi-source switching can be achieved.

[0052] In the present embodiment, after the data query ends, the system can implement query data export by using the method of first exporting to HDFS and WebSocket and Http mixed export in the long connection. Referring to Figure 7 , Figure 7 is a flowchart of data export in the data query method of the present application. As shown in Figure 7As shown, the data export process in the present application can be divided into the export instruction initiation and task execution stage and the file generation stage, wherein the export instruction initiation and task execution stage can ensure reliable transmission and execution of the export instruction through WebSocket long connection, and the file generation stage can utilize the advantages of HTTP protocol to realize efficient and stable file download. Specifically, after the user executes the query in the SQL editor and obtains the query result, the user can click the "data export" button on the front-end interface and select the export format (such as CSV), file name, separator, etc. to generate the data export instruction. The front-end page in the system can capture and parse the data export instruction, obtain the data export parameters (including the SQL statement for generating the export file, file format, encoding, etc.), and send the data export instruction to the database operation service in the back-end through the established WebSocket long connection. After receiving the data export instruction, the database operation service can execute the corresponding query SQL, obtain the export result, and directly write the data result set into HDFS (Hadoop Distributed File System) or similar big data file storage. When all the data is written into HDFS, the database operation service can generate a unique file identifier or a temporary download link with access time limit, then the back-end service can return the file identifier or temporary link to the front-end through the WebSocket long connection, and inform "file export success, ready to download", and close the Hive connection and HDFS connection. After receiving the export success notification, the front-end can automatically or through the user clicking the "download" button to initiate a standard HTTP GET request to the back-end, which can contain the file identifier or temporary link obtained from the back-end. After receiving the HTTP request, the back-end service can connect to HDFS according to the HDFS path, read the corresponding file stream from HDFS according to the file identifier, then write the HDFS file into the target file through the HTTP connection, delete the written HDFS file, and close the HDFS file after the HDFS file is written, finally obtain and export the target file.

[0053] The embodiment provides a data query method. The method discloses responding to a data query request triggered by a user, and determining a data query instruction and a target query database corresponding to a current data query task according to the data query request; detecting whether the data query instruction conforms to a database syntax specification corresponding to the target query database; if not, performing syntax conversion on the data query instruction based on a preset syntax tree analysis mode to obtain a target data query instruction, the target data query instruction being adapted to the target query database; performing data query based on the target data query instruction to obtain a data query result of the current data query task; compared with the prior art, since there is a significant difference in syntax and function between database engines performing data query, a user needs to frequently modify a query script when performing cross-database query; since the embodiment can perform syntax conversion on the data query instruction when the data query instruction does not conform to the syntax specification of the target database, and perform data query based on the target data query instruction obtained after conversion, the technical problem that the user needs to frequently modify the query script to adapt to the syntax difference of different database engines when performing cross-database query in the prior art is solved, time is saved, and errors are reduced, and the data query efficiency is improved.

[0054] Based on the first embodiment of the application, in the second embodiment of the application, the same or similar contents as the above-mentioned first embodiment can be referred to the above description, and will not be described hereinafter. On this basis, please refer to Figure 8 , Figure 8 The flowchart of the second embodiment of the data query method of the application is provided.

[0055] In the embodiment, before step S20, the method further includes step S01: Step S01: obtaining a target database connection corresponding to the data query instruction based on a preset database connection pool.

[0056] It should be noted that the above-mentioned preset database connection pool can be a database connection set created and maintained by the system in advance. Correspondingly, the above-mentioned target database connection can be a database connection obtained from the preset database connection pool and used for executing the current data query task. In the embodiment, the database connections in the preset database connection pool can be initialized when the system starts, and reused by multiple query tasks during running, so that the overhead of connection creation and destruction can be reduced, wherein the connection pool is usually maintained by a connection pool manager, which can support the creation, allocation, recovery and destruction of connections.

[0057] Further, the step S01 includes: judging whether the target database connection corresponding to the data query instruction exists in the preset database connection pool; if yes, directly obtaining the target database connection from the preset database connection pool; or if not, creating the target database connection in the preset database connection pool.

[0058] In actual application, the system can determine the data source information selected by the user according to the data query instruction, and determine the connection parameters (such as host address, port number, database name, etc.) of the target database according to the data source information. Then, the system can check whether there is a usable connection matching the target database in the preset database connection pool through the connection pool manager. If there is a usable connection in the connection pool, the system directly obtains the connection from the connection pool. If there is no usable connection matching the target database in the connection pool, the system can create a new database connection according to the data source information, and add the newly created database connection to the connection pool for subsequent reuse. In this embodiment, by reusing the database connections in the connection pool, the overhead of frequently creating and closing connections can be reduced, thereby significantly improving the performance of the system.

[0059] Correspondingly, the step S40 comprises: Step S401: sending the target data query instruction to the target query database through the target database connection.

[0060] Step S402: executing the target data query instruction through the target query database to obtain the data query result of the current data query task.

[0061] In actual application, after the system obtains the database connection matching the target database from the preset database connection pool, the system can use the obtained database connection to create a database operation object to execute the SQL query instruction, and send the target data query instruction to the target database through the database operation object. After the target database receives the target data query instruction, the target database can execute the target data query instruction and return the query result.

[0062] In this embodiment, the target database connection corresponding to the data query instruction is obtained based on the preset database connection pool, the target data query instruction is sent to the target query database through the target database connection, and the target data query instruction is executed through the target query database to obtain the data query result of the current data query task. Since this embodiment can execute the query instruction based on the target database connection corresponding to the data query instruction obtained based on the preset database connection pool, the reuse of the database connections in the connection pool can be implemented, thereby reducing the overhead of frequently creating and closing connections, and facilitating the improvement of the performance and data query efficiency of the system.

[0063] Based on the first embodiment and / or the second embodiment of the present application, the same or similar contents as the above embodiments can be referred to the above description, and will not be described hereinafter. On this basis, please refer to Figure 9 , Figure 9 The flowchart provided for the third embodiment of the data query method of the present application.

[0064] In this embodiment, step S30 includes steps S301-S303: Step S301: parsing the data query instruction based on a preset syntax tree parsing manner to generate a syntax tree structure corresponding to the data query instruction.

[0065] It should be understood that the above syntax tree structure can be a tree-shaped data structure for representing the syntax components of the query instruction and their hierarchical relationship. Each node in the structure can represent a syntax element (such as a keyword, table name, field name, operator, etc.) in the data query instruction, and the structure of the tree can reflect the syntax relationship between these elements.

[0066] Step S302: adjusting the syntax tree structure according to the database syntax specification to obtain an adjusted syntax tree structure.

[0067] It can be understood that the above adjusted syntax tree structure can be a structure obtained by adjusting the original syntax tree structure according to the syntax specification of the target database, and the adjustment can include replacing keywords, modifying function names, adjusting syntax structures, etc., which are not limited in this embodiment. In this embodiment, by adjusting the syntax tree structure, it can be ensured that the generated query instruction conforms to the syntax requirements of the target database.

[0068] Further, the step S302 includes: determining a structure adjustment strategy corresponding to the syntax tree structure according to the database syntax specification; if the structure adjustment strategy is a parameter adjustment strategy, determining a to-be-adjusted parameter and a target adjustment parameter of the syntax tree structure; replacing the to-be-adjusted parameter based on the target adjustment parameter to obtain an adjusted syntax tree structure.

[0069] It should be noted that the above structure adjustment strategy can be a strategy adopted to adjust the syntax tree structure to meet the requirements of the target database, wherein the syntax elements (such as keywords, functions, syntax structures, etc.) that need to be adjusted and the specific adjustment method are defined. Correspondingly, the above parameter adjustment strategy can be a strategy for adjusting parameters (such as field names, table names, function parameters, etc.) in the syntax tree, such as a field name adjustment strategy and a function parameter adjustment strategy, wherein the field name adjustment strategy refers to a strategy for adjusting the field name from the naming specification of one database to the naming specification of another database; the function parameter adjustment strategy refers to a strategy for adjusting the format or type of the function parameter to adapt to the requirements of the target database.

[0070] It should be understood that the above to-be-adjusted parameter can be a parameter in the original abstract syntax tree that does not conform to the syntax specification of the target database; correspondingly, the above target adjusted parameter can be a parameter conforming to the syntax specification of the target database. In this embodiment, through the parameter adjustment strategy, the to-be-adjusted parameter can be replaced or modified into the target adjusted parameter, and the adjusted parameter is updated to the syntax tree, so as to obtain an adjusted syntax tree structure.

[0071] Step S303: generating a target data query instruction adapted to the target query database based on the adjusted syntax tree structure.

[0072] In actual application, the system can load a predefined syntax parser and pass the data query instruction input by the user to the syntax parser to generate a syntax tree structure corresponding to the data query instruction, where the syntax parser can be constructed based on the syntax specification of the target database. Then, the system can traverse the generated syntax tree to check whether each node in the syntax tree conforms to the syntax specification of the target database, and if not, adjust the node in the syntax tree according to the syntax specification of the target database, for example, replace an unsupported keyword (such as replacing INSERT OVERWRITE with INSERT INTO), adjust a function name (such as replacing DATE_FORMAT of Hive with TO_DATE of Impala), modify a syntax structure (such as adjusting the order of clauses), and the like. Thereafter, the system can regenerate a query instruction conforming to the syntax specification of the target database according to the adjusted syntax tree structure, and perform syntax verification on the generated query instruction to ensure that it conforms to the requirements of the target database.

[0073] In specific implementation, refer to Figure 10 , Figure 10 FIG. 1 is a flowchart of the SQL statement conversion in the data query method of the present application. Figure 10The document illustrates the implementation process of automatically converting Hive SQL to Impala SQL. Specifically, the system first retrieves the original SQL statement to be converted from the user's SQL edit box and performs preliminary segmentation based on keywords and clause boundaries. This breaks down the complex SQL statement into more manageable logical units, improving the accuracy and efficiency of parsing. Then, the system uses a syntax parser to perform lexical and syntactic analysis on the SQL, converting it into a structured abstract syntax tree (AST). This AST can include root nodes (statement context), clause nodes (clause context), expression nodes (expression context), and terminal nodes. Subsequently, the system can traverse the AST using a custom accessor. When a specific node is accessed, it can query a regenerated rule base to convert the SQL based on the rules set in that regenerated rule base. For example, when the visitor accesses an expression node, it can enter the overridden expression context access (visitExprContext) method. If the function call node DATE_FORMAT is identified at this time, the rule base is queried to find its corresponding Impala target function TO_DATE, and the SQL can be transformed based on this target function. When the visitor accesses a clause node, it can enter the overridden clause context access (visitClauseContext) method. If the expression size(attributes)>0 in WhereClause is identified at this time, the rule base may instruct it to be transformed into CARDINALITY(attributes)>0 supported by Impala. This process can be based on the visitor design pattern, allowing transformation logic to be added by overriding the access methods of various types of nodes (such as statement context access, clause context access, and terminal node access) without modifying the node structure of the abstract syntax tree. During the traversal of the syntax tree, the system needs to manage scope information to ensure that the transformation process understands the SQL context and avoids erroneous transformations out of context. ProjectScope manages information in select and conditional statements to ensure correct column names and types after transformation; JoinScope or ScanScope manages table join and scan information, handling syntax differences between Hive and Impala in table joins, partitioned queries, etc. After traversing and modifying all nodes requiring transformation, the system can regenerate standard SQL text according to the modified syntax tree structure and Impala syntax rules, returning the generated Impala SQL to the front-end editor for user use. Simultaneously, a corresponding transformation log is generated so users can trace modifications and quickly troubleshoot problems if the transformation result does not meet expectations.

[0074] In this embodiment, the data query instruction is parsed based on a preset syntax tree parsing manner to generate a syntax tree structure corresponding to the data query instruction; the syntax tree structure is adjusted according to a database syntax specification to obtain an adjusted syntax tree structure; and a target data query instruction that is adapted to a target query database is generated based on the adjusted syntax tree structure. Through syntax tree parsing and adjustment, the query instruction can be automatically converted into a syntax that is adapted to the target database, so that data query of multiple database engines can be supported, thereby improving the flexibility of data query.

[0075] It should be noted that the above examples are only used for understanding the present application and do not constitute a limitation on the data query method of the present application. More forms of simple transformation based on this technical concept are within the protection scope of the present application.

[0076] The present application also provides a data query device, please refer to Figure 11 The data query device comprises: An instruction determination module 10 is configured to determine a data query instruction and a target query database corresponding to a current data query task in response to a data query request triggered by a user. A syntax detection module 20 is configured to detect whether the data query instruction conforms to a database syntax specification corresponding to the target query database. A syntax conversion module 30 is configured to perform syntax conversion on the data query instruction based on a preset syntax tree parsing manner to obtain a target data query instruction if the data query instruction does not conform to the database syntax specification, wherein the target data query instruction is adapted to the target query database. A data query module 40 is configured to perform data query based on the target data query instruction to obtain a data query result of the current data query task.

[0077] The data query device provided by the present application adopts the data query method in the above embodiments, and can solve the technical problem that in the prior art, query scripts need to be frequently modified to adapt to the syntax difference of different database engines when cross-database query is performed, which is time-consuming and error-prone. Compared with the prior art, the data query device provided by the present application has the same beneficial effects as the data query method provided by the above embodiments, and other technical features in the data query device are the same as the features disclosed in the above embodiments, which will not be repeated here.

[0078] The present application provides a data query device, comprising: at least one processor; and a memory in communication connection with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the data query method in Embodiment I.

[0079] The following is for reference. Figure 12 The diagram illustrates a structural schematic of a data query device suitable for implementing embodiments of this application. The data query device in this application may include, but is not limited to, mobile terminals such as mobile phones, laptops, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Description), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 12 The data query device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0080] like Figure 12 As shown, the data query device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in the read-only memory 1002 or a program loaded from the storage device 1003 into the random access memory 1004. The random access memory 1004 also stores various programs and data required for the operation of the data query device. The processing unit 1001, the read-only memory 1002, and the random access memory 1004 are interconnected via a bus 1005. An input / output interface 1006 is also connected to the bus. Typically, the following systems can be connected to the input / output interface 1006: input devices 1007 including, for example, touchscreens, touchpads, keyboards, mice, image sensors, microphones, accelerometers, gyroscopes, etc.; output devices 1008 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices 1003 including, for example, magnetic tapes, hard disks, etc.; and communication devices 1009. Communication device 1009 allows the data query device to communicate wirelessly or wiredly with other devices to exchange data. Although the figures show data query devices with various systems, it should be understood that it is not required to implement or possess all of the systems shown. More or fewer systems may be implemented alternatively.

[0081] In particular, according to embodiments of the present application, the processes described above with reference to the flowcharts can be implemented as a computer software program. For example, embodiments of the present application include a computer program product comprising a computer program carried on a computer readable medium, the computer program comprising program code for performing the methods illustrated by the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from a storage device 1003, or installed from a read-only memory 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the methods of the embodiments of the present application are performed.

[0082] The data query device provided by the present application adopts the data query method in the above-mentioned embodiments, and can solve the technical problem of data query. Compared with the prior art, the data query device provided by the present application has the same beneficial effects as the data query method provided by the above-mentioned embodiments, and other technical features in the data query device are the same as the features disclosed in the above-mentioned embodiment method, which will not be described here.

[0083] It should be understood that various parts of the present application can be realized by hardware, software, firmware or a combination thereof. In the description of the above-mentioned embodiments, specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0084] The above is merely specific implementation of the present application, but the protection scope of the present application is not limited thereto, any person skilled in the art can easily think of changes or replacements within the technical scope disclosed by the present application, which should be covered within the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

[0085] The present application provides a computer readable storage medium having stored thereon computer readable program instructions (i.e. computer program) for performing the data query method in the above-mentioned embodiments.

[0086] The computer readable storage medium provided in the application may, for example, be a U disk, but is not limited to an electric, magnetic, optical, electromagnetic, infrared, or semiconductor system or device, or any combination of the above. More specific examples of the computer readable storage medium may include, but are not limited to, an electric connection with one or more conductive wires, 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 above. In the embodiment, the computer readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system or device. The program code contained on the computer readable storage medium can be transmitted by any suitable medium, including but not limited to an electric wire, an optical cable, an RF (Radio Frequency), and the like, or any suitable combination of the above.

[0087] The computer readable storage medium described above may be contained in a data query device, or may exist separately without being assembled into the data query device.

[0088] The computer readable storage medium described above carries one or more programs, which, when executed by the data query device, cause the data query device to: in response to a data query request triggered by a user, determine a data query instruction and a target query database corresponding to a current data query task according to the data query request; detect whether the data query instruction conforms to a database syntax specification corresponding to the target query database; if not, perform syntax conversion on the data query instruction based on a preset syntax tree analysis manner to obtain a target data query instruction, the target data query instruction being adapted to the target query database; and perform data query based on the target data query instruction to obtain a data query result of the current data query task.

[0089] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0090] The flow diagrams and the block diagrams in the drawings are illustrations of architectures, functionalities, and operations of possible implementations of systems, methods, and computer program products according to various embodiments of the present application. In this regard, each block in the flow diagrams or block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or the blocks may

[0091] The modules involved in the embodiments of the present application can be implemented in the form of software or in the form of hardware. In some cases, the name of the module does not constitute a limitation on the module itself.

[0092] The readable storage medium provided by the present application is a computer readable storage medium, which stores computer readable program instructions (i.e., a computer program) for executing the above data query method, and can solve the technical problem that in the prior art, the query script needs to be frequently modified to adapt to the syntax difference of different database engines when cross-database query is performed, which is time-consuming and error-prone. Compared with the prior art, the computer readable storage medium provided by the present application has the same beneficial effects as the data query method provided by the above embodiments, and will not be described here.

[0093] The above merely provides part of embodiments of the present application, and does not limit the patent scope of the present application. Any equivalent structure transformation, direct / indirect application in other related technical fields based on the technical concept of the present application, and contents of the present application specification and drawings are included in the patent protection scope of the present application.

Claims

1. A data query method, characterized by, The method comprises: in response to a user triggered data query request, and according to the data query request, determining a data query instruction and a target query database corresponding to a current data query task; detecting whether the data query instruction conforms to a database syntax specification corresponding to the target query database; if not, performing syntax conversion on the data query instruction based on a preset syntax tree parsing manner to obtain a target data query instruction, the target data query instruction being adapted to the target query database; based on the target data query instruction, performing data query to obtain a data query result of the current data query task.

2. The method of claim 1, wherein, Before the step of detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database, the method further comprises: based on a preset database connection pool, obtaining a target database connection corresponding to the data query instruction; the step of based on the target data query instruction, performing data query to obtain a data query result of the current data query task, comprising: sending the target data query instruction to the target query database through the target database connection; executing the target data query instruction through the target query database to obtain the data query result of the current data query task.

3. The method of claim 2, wherein, The step of obtaining the target database connection corresponding to the data query instruction based on the preset database connection pool comprises: judging whether the target database connection corresponding to the data query instruction exists in the preset database connection pool; if so, directly obtaining the target database connection from the preset database connection pool; or, if not, creating the target database connection in the preset database connection pool.

4. The method of claim 1, wherein, The step of performing syntax conversion on the data query instruction based on the preset syntax tree parsing manner to obtain a target data query instruction comprises: based on a preset syntax tree parsing manner, parsing the data query instruction to generate a syntax tree structure corresponding to the data query instruction; adjusting the syntax tree structure according to the database syntax specification to obtain an adjusted syntax tree structure; based on the adjusted syntax tree structure, generating a target data query instruction adapted to the target query database.

5. The method of claim 4, wherein, The step of adjusting the syntax tree structure according to the database syntax specification to obtain an adjusted syntax tree structure comprises: determining a structure adjustment strategy corresponding to the syntax tree structure according to the database syntax specification; if the structure adjustment strategy is a parameter adjustment strategy, determining a to-be-adjusted parameter and a target adjustment parameter of the syntax tree structure; based on the target adjustment parameter, replacing the to-be-adjusted parameter to obtain an adjusted syntax tree structure.

6. The method of any one of claims 1 to 5, wherein, Before the step of in response to a user triggered data query request, and according to the data query request, determining a data query instruction and a target query database corresponding to a current data query task, the method further comprises: loading preconfigured data source information from a background database; generating a data source list according to the data source information, and displaying the data source list on a front-end page; determining target data source information selected by the user from the data source list; Generate a data query instruction corresponding to the current data query task based on the target data source information.

7. The method of any one of claims 1 to 5, wherein, The step of detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database comprises: Detecting whether the data query instruction conforms to the syntax rule defined in the database syntax specification corresponding to the target query database; If it conforms, obtaining target metadata information corresponding to the target query database; Matching the metadata information in the data query instruction with the target metadata information; According to the matching result, detecting whether the data query instruction conforms to the database syntax specification corresponding to the target query database.

8. A data query apparatus, characterized by comprising: The device comprises: An instruction determination module configured to determine a data query instruction and a target query database corresponding to a current data query task in response to a data query request triggered by a user; A syntax detection module configured to detect whether the data query instruction conforms to the database syntax specification corresponding to the target query database; A syntax conversion module configured to, if it does not conform, perform syntax conversion on the data query instruction based on a preset syntax tree analysis manner to obtain a target data query instruction, wherein the target data query instruction is adapted to the target query database; A data query module configured to perform data query based on the target data query instruction to obtain a data query result of the current data query task.

9. A data query device, characterized by The device comprises a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data query method according to any one of claims 1 to 7.

10. A storage medium, characterized by The storage medium is a computer readable storage medium, and the storage medium stores a computer program, wherein the computer program is executed by a processor to implement the steps of the data query method according to any one of claims 1 to 7.

Images