A Visual Chinese SQL System and a Method for Constructing Queries

Abstract

The present invention discloses a visual Chinese SQL system and a query construction method, including a data source connection information module, a metadata collection module, a metadata maintenance system, a statement escape module, a query engine, and a Chinese SQL platform; registering and providing connection information of the data source through the data source connection information module; adding new collection tasks, configuring the collection period, and table information to be collected through the metadata collection module; adding Chinese descriptions according to business requirements for those without Chinese descriptions; displaying in a tree structure in the metadata maintenance system; submitting the generated SQL statement to the query engine module to execute the query plan; performing conversion between JSON format and SQL statements; generating Chinese SQL statements and query result sets in the metadata maintenance system. The present invention, by Chinese-ifying database table fields and SQL statements, can quickly complete data statistics and analysis for temporary and real-time data, reducing the workload and time of system program development.

Description

Technical Field

[0001] The present invention relates to the field of database queries, and particularly to a visual Chinese SQL system and a query construction method. Background Art

[0002] With the rapid development of the Internet information age, database technology has been more and more widely applied in enterprises. From small transaction processing systems to large information systems, from data statistics to data analysis, none of them can do without a database. When it comes to databases, SQL queries have to be mentioned.

[0003] The SQL language has a history of more than 40 years and is almost everywhere since its application. It is mainly used for programming database software or maintaining database data, and performing operations such as querying, summarizing, writing, deleting, and modifying.

[0004] During the software development process, R & D personnel will write various SQL statements based on data according to business requirements, and through code layer conversion, to achieve the final effect required by the business. At the same time, business personnel will also perform a series of subsequent operations based on the data queried in the system, such as data export, data calculation, etc.

[0005] In some business scenarios, such as when business personnel form a data analysis report on the data imported externally by the company temporarily, they need to perform aggregated association queries on data with different data sources. Business personnel do not understand SQL statements and perform statistical and sorting operations on data from different dimensions. The business system development for these database tables has not been developed and implemented. These data are stored in the database with English fields, which is not conducive to business personnel's understanding and report display. Summary of the Invention

[0006] Based on the above technical problems, the purpose of the present invention is to provide a visual Chinese SQL system and a query construction method, which have the functions of being able to quickly complete the statistics and analysis of temporary and real-time data, and reducing the workload and time of system program development.

[0007] To achieve the above purpose, the present invention provides the following technical solutions:

[0008] A visual Chinese SQL system includes a data source connection information module, a metadata collection module, a metadata maintenance system, a statement escape module, a query engine, and a Chinese SQL platform;

[0009] The data source connection information module is used to store the connection parameters and connection drivers of different types of data sources; the metadata collection module is used to create collection tasks, and the metadata collection module periodically obtains the attribute information of the corresponding database tables and fields according to the configuration information of the data source connection information; the metadata maintenance system is used to maintain the database metadata information obtained by the metadata collection program; the statement escape module is used to escape the results of user operations, and the query engine is used to obtain SQL objects to generate query plans.

[0010] The data source connection information module is connected to the metadata collection module, the metadata maintenance system is respectively connected to the data source connection information module and the metadata collection module, the metadata maintenance system is connected to the Chinese SQL platform, the statement escape module is connected to the query engine, and the Chinese SQL platform is respectively connected to the statement escape module and the query engine.

[0011] Preferably, the data source connection information module for storing the connection parameters and connection drivers of different types of data sources includes relational databases such as Mysql, Postgresql, Oracle, and non-relational databases such as ElasticSearch and Redis.

[0012] Preferably, the metadata maintenance system manages and edits database tables and fields according to business requirements, supports batch import, field version change, supports classification and grading, and tagging. The information displayed in the query view of the Chinese SQL platform all comes from the metadata maintenance system.

[0013] A method for constructing a query with visual Chinese SQL includes the following steps:

[0014] S1. Build a visual Chinese SQL system;

[0015] S2. Register and provide the connection information of the data source through the data source connection information module, including the connection address, username, password, and JDBC driver;

[0016] S3. Add a collection task, configure the collection period, and the table information to be collected through the metadata collection module according to the connection information of the data source in step S1;

[0017] S4. Edit and maintain the collected table and field attribute information through the metadata maintenance system, and add Chinese descriptions according to business requirements for those without Chinese descriptions;

[0018] S5. Display the tables and field information published in step S3 in a tree structure in the metadata maintenance system;

[0019] S6. Screen the tables and fields to be queried in the metadata maintenance system, and perform aggregation operations such as maximum and minimum values on the fields; submit the generated SQL statement to the query engine module to execute the query plan;

[0020] S7. Generate a JSON message for the selected tables and fields and perform the conversion between JSON format and SQL statement through the statement escape module;

[0021] S8. Generate Chinese SQL statements and query result sets in the metadata maintenance system;

[0022] S9. Save the current Chinese SQL statement, export data to generate a report file or query statement;

[0023] S10. The process ends.

[0024] Preferably, in step S4, when editing the collected table and field attribute information, the original table library information is not changed, only the modified information is published, and a corresponding version is generated for each modification release. The latest version of the modified information is imported into step S5.

[0025] Preferably, in step S6, relational queries can also be performed. When relational queries are required, relevant tables need to be selected and the associated fields between the tables need to be specified.

[0026] Preferably, the query engine in step S6 will verify the legality of the SQL according to the rule configuration and determine whether it is a multi-data source, and finally submit the query plan and return the query data result. The returned fields will be replaced according to the information in the metadata maintenance system and displayed on the front-end page.

[0027] Preferably, the conversion between JSON format and SQL statement in step S7 includes: performing a total of two escapes on the results of user operations;

[0028] The first escape is completed on the front end, converting the query table object, query field, query condition, associated object table object, associated field, aggregation field, and sorting field information into a JSON format object, and passing the JSON object to the back-end program;

[0029] The second escape is executed by the back-end program, converting the obtained JSON object into an SQL object, and performing replacement according to the information in the metadata maintenance system and passing it to the query engine.

[0030] Preferably, the specific embodiment of the conversion logic is: after parsing the JSON fields specifying different data formats, generate their respective corresponding statements and splice them into a complete SQL field.

[0031] Compared with the prior art, the beneficial effects of the present invention are:

[0032] 1. By sinicizing the database table fields and SQL statements, compared with the main process of data statistical analysis in the previous report form, a series of tasks such as R & D personnel completing the connection to the data source, creating table entities, developing SQL statements, interfaces, and front-end pages are required. It takes a certain period from function development to business use. With this invention, business personnel in the company can also achieve independent queries, quickly complete data statistics and analysis for temporary and real-time data, and reduce the workload and time of system program development.

[0033] 2. For some temporary data that only requires queries and whose data formats often change and are uncertain, once modified, it means that the business system also needs to be modified accordingly, deepening the deep binding with the business system and making it impossible to achieve flexibility. With this invention, the data can be decoupled from the business system, without caring about the data format, and it can be retrieved and used at any time.

[0034] 3. When aggregating and integrating data from different data sources in this invention, it is often found that the naming is not standardized and the Chinese annotations are unreasonable. It also raises the standardization requirements for the Chinese definitions of database fields, gradually achieving the effect and purpose of being easy to understand. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following will briefly introduce the drawings required for use in the description of the specific embodiments or the prior art. Obviously, the following drawings are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0036] Figure 1 It is a schematic structural diagram of the visual Chinese SQL system of the present invention;

[0037] Figure 2 It is a flowchart of the method for constructing queries in the visual Chinese SQL of the present invention;

[0038] Figure 3 It is a flowchart for generating SQL fields in the present invention;

[0039] Figure 4 It is a specific field definition rule table at level Ⅰ of the present invention;

[0040] Figure 5 It is a specific field definition rule table for other levels of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0041] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0042] Please refer to Figures 1 to 5 , the present invention provides a technical solution:

[0043] A visual Chinese SQL system, as Figure 1 shown, includes a data source connection information module 10, a metadata collection module, a metadata maintenance system 12, a statement escape module 14, a query engine 15, and a Chinese SQL platform 13;

[0044] The data source connection information module 10 is used to store connection parameters and connection drivers of different types of data sources. The data source connection information module 10 for storing connection parameters and connection drivers of different types of data sources includes relational Mysql, Postgresql, Oracle, and non-relational ElasticSearch, Redis databases;

[0045] The metadata collection module 11 is used to create collection tasks. The metadata collection module 11 periodically obtains attribute information of corresponding database tables and fields according to the configuration information of the data source connection information; the metadata maintenance system 12 is used to maintain the database metadata information obtained by the metadata collection program; the statement escape module 14 is used to escape the results of user operations, and the query engine 15 is used to obtain a query plan by generating an SQL object; the metadata maintenance system 12 manages and edits database tables and fields according to business requirements, supports batch import, field version change, supports classification and grading, and tagging. The information displayed in the query view of the Chinese SQL platform 13 all comes from the metadata maintenance system 12.

[0046] The data source connection information module 10 is connected to the metadata collection module 11. The metadata maintenance system 12 is respectively connected to the data source connection information module 10 and the metadata collection module 11. The metadata maintenance system 12 is connected to the Chinese SQL platform 13. The statement escape module 14 is connected to the query engine 15. The Chinese SQL platform 13 is respectively connected to the statement escape module and the query engine 15.

[0047] A method for constructing a query for a visual Chinese SQL, specifically referring to Figure 2 , includes the following steps:

[0048] S1. Build a visual Chinese SQL system;

[0049] S2. Register and provide the connection information of the data source through the data source connection information module 10, including the connection address, username, password, and JDBC driver;

[0050] S3. Add a collection task, configure the collection period, and the table information to be collected according to the connection information of the data source in step S1 and through the metadata collection module 11;

[0051] S4. Edit and maintain the collected tables and field attribute information through the metadata maintenance system 12, and add Chinese descriptions according to business requirements for those without Chinese descriptions; during the editing of the collected tables and field attribute information, the original table library information is not changed, and only the modified information is published. Each modification release will generate a corresponding version, and the latest version of the modified information is imported into step S5.

[0052] S5. Display the tree structure of the tables and fields published in step S3 in the metadata maintenance system 12;

[0053] S6. Filter the tables and fields to be queried in the metadata maintenance system 12, and perform aggregation operations such as maximum and minimum values on the fields; submit the generated SQL statement to the query engine 15 module to execute the query plan; relational queries can also be performed. When a relational query is required, the relevant tables need to be selected, and the associated fields between the tables need to be specified. The query engine 15 will verify the legality of the SQL according to the rule configuration and determine whether it is a multi-data source, and finally submit the query plan and return the query data result. The returned fields will be replaced and displayed on the front-end page according to the information in the metadata maintenance system 12.

[0054] S7. Generate a JSON message for the selected tables and fields and perform the conversion between JSON format and SQL statement through the statement escape module 14; the conversion between JSON format and SQL statement in step S7 includes: performing a total of two escapes on the results of the user's operations;

[0055] The first escape is completed on the front end, converting the query table object, query field, query condition, associated object table object, associated field, aggregation field, and sorting field information into a JSON format object, and passing the JSON object to the back-end program;

[0056] The second escape is executed by the back-end program, converting the obtained JSON object into an SQL object, and performing replacement according to the information in the metadata maintenance system 12 and passing it to the query engine 15.

[0057] S8. Generate Chinese SQL statements and query result sets in the metadata maintenance system 12;

[0058] S9. Save the current Chinese SQL statement, export data to generate a report file or a query statement;

[0059] S10. The process ends.

[0060] In this embodiment, specifically refer to Figure 3 In step S7, the specific embodiment of the conversion logic between the JSON format and the SQL statement is as follows: after parsing the JSON fields that define different data formats, the specific field definition rules refer to Figure 4 and Figure 5 to generate their respective corresponding statements and concatenate them into a complete SQL field.

[0061] Through the Chineseization of the database table fields and SQL statements, compared with the previous form of report-based data statistical analysis, the main process requires R & D personnel to complete a series of tasks such as connecting to the data source, creating table entities, developing SQL statements, interfaces, and front-end pages. It takes a certain period from function development to business use. With this invention, the company's business personnel can also achieve autonomous queries, quickly complete data statistics and analysis for temporary and real-time data, and reduce the workload and time of system program development.

[0062] Finally, it should be noted that: the above embodiments are only used to illustrate the technical solutions of the present invention, not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A visual Chinese SQL system, characterized in that, It includes a data source connection information module (10), a metadata collection module (11), a metadata maintenance system (12), a statement escape module (14), a query engine (15), and a Chinese SQL platform (13); The data source connection information module (10) is used to store connection parameters and connection drivers for different types of data sources; the metadata collection module (11) is used to create collection tasks, and the metadata collection module (11) periodically obtains attribute information of corresponding database tables and fields according to the configuration information of the data source connection information; the metadata maintenance system (12) is used to maintain the database metadata information obtained by the metadata collection program; The statement escape module (14) is used to escape the results of user operations, and the query engine (15) is used to obtain SQL objects to generate query plans; The data source connection information module (10) is connected to the metadata collection module (11), the metadata maintenance system (12) is respectively connected to the data source connection information module (10) and the metadata collection module (11), the metadata maintenance system (12) is connected to the Chinese SQL platform (13), the statement escape module (14) is connected to the query engine (15), and the Chinese SQL platform (13) is respectively connected to the statement escape module and the query engine (15).

2. The visual Chinese SQL system according to claim 1, wherein The data source connection information module (10) for storing connection parameters and connection drivers of different types of data sources includes relational databases such as Mysql, Postgresql, Oracle, and non-relational databases such as ElasticSearch and Redis.

3. The visual Chinese SQL system according to claim 1, characterized in that The metadata maintenance system (12) manages and edits database tables and fields according to business requirements, supports batch import, field version change, classification and grading, and tagging. The information displayed in the query view of the Chinese SQL platform (13) all comes from the metadata maintenance system (12).

4. A method for visualizing Chinese SQL construction queries, adapted to a visual Chinese SQL system as described in claim 1, characterized in that, It includes the following steps: S1. Build a visual Chinese SQL system; S2. Register and provide connection information of the data source through the data source connection information module (10), including connection address, username, password, and JDBC driver; S3. Add a collection task, configure the collection period, and the table information to be collected through the metadata collection module (11) according to the connection information of the data source in step S1; S4. Edit and maintain the collected table and field attribute information through the metadata maintenance system (12), and add Chinese descriptions according to business requirements for those without Chinese descriptions; S5. Display the table and field information published in step S3 in a tree structure in the metadata maintenance system (12); S6. Screen the tables and fields to be queried in the metadata maintenance system (12), and perform aggregation operations such as maximum value and minimum value on the fields; submit the generated SQL statement to the query engine (15) module to execute the query plan; S7. Generate a JSON message for the selected tables and fields and perform conversion between JSON format and SQL statement through the statement escape module (14); S8. Generate Chinese SQL statements and query result sets in the metadata maintenance system (12); S9. Save the current Chinese SQL statements, export data to generate report files or query statements; S10. The process ends.

5. A method for constructing a query of visual Chinese SQL according to claim 4, characterized in that, In step S4, when editing the collected tables and field attribute information, the original table library information is not changed, and only the modified information is published. Each modification release generates a corresponding version, and the latest version of the modified information is imported into step S5.

6. The visual Chinese SQL construction query method according to claim 4, characterized in that, In step S6, relational queries can also be performed. When a relational query is required, relevant tables need to be selected, and the associated fields between the tables need to be specified.

7. A method for constructing a query for visual Chinese SQL according to claim 4, characterized in that The query engine (15) in step S6 will verify the legality of the SQL according to the rule configuration and determine whether it is a multi-data source, and finally submit the query plan and return the query data result. The returned fields will be replaced according to the information in the metadata maintenance system (12) and displayed on the front-end page.

8. A method for constructing a query for visual Chinese SQL according to claim 4, characterized in that In step S7, the conversion between the JSON format and the SQL statement includes: performing a total of two escapes on the results of the user's operations; The first escape is completed on the front end, converting the query table object, query fields, query conditions, associated object table objects, associated fields, aggregation fields, and sorting field information into JSON format objects, and passing the JSON objects to the back-end program; The second escape is executed by the back-end program, converting the obtained JSON objects into SQL objects, replacing them according to the information in the metadata maintenance system (12), and passing them to the query engine (15).

9. A method for constructing a query for visualizing Chinese SQL according to claim 8, characterized in that, The specific manifestation of the conversion logic is: after parsing the JSON fields that define different data formats, generating their respective corresponding statements and concatenating them into a complete SQL field.

Images