Data warehouse SQL query method and device, medium and product
By introducing a large language model into the data warehouse to generate or correct SQL statements, the problem of non-professional users writing complex queries is solved, and an efficient and secure data query process is achieved.
Patent Information
- Application Number
- CN202411426509.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-12
- Publication Date
- 2025-01-10
AI Technical Summary
In data warehouses, non-professional users find it difficult to write efficient and accurate SQL queries, resulting in low query efficiency and wasted system resources. Furthermore, complex queries require the assistance of professional developers, leading to cumbersome communication processes.
By introducing a large language model and combining it with metadata to generate or correct SQL statements, the barrier to entry is lowered, enabling non-professional users to write complex queries and execute them through the data query engine.
It improves the accuracy and efficiency of SQL queries in the data warehouse, reduces the query error rate, reduces the consumption of system resources, and enhances the security of data processing.
Smart Images
Figure CN119293066A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of data query, and in particular to a data warehouse SQL query method, device, medium and product. Background Art
[0002] Data warehouse (DW) is a centralized enterprise data storage collection. Data warehouse is used to integrate and store structured and semi-structured data from different sources. The integrated data can provide support for enterprise insight and decision-making. After completing the data integration of multiple data sources in the data warehouse, data cleaning can be performed to facilitate subsequent data analysis, mining and visualization. Data query is an important step to obtain data from the data warehouse for subsequent analysis, mining and visualization. It can simply directly capture data subsets or perform complex calculations and data conversions. Structured Query Language (SQL) is the standard query language for relational data warehouses. In addition to query, it can also complete a series of functions such as data warehouse model creation, data insertion and modification in data tables, data warehouse security integrity definition and control, and plays an important role in data management and analysis. Professional data developers can skillfully write concise SQL language to complete related development or query requirements. However, business personnel who are not familiar with SQL find it difficult to use SQL to query accurately on their own, or are prone to problems such as low efficiency of the SQL queries they write. When the data warehouse structure is complex, the SQL statement will become more complex and may contain multiple subqueries, join operations or nested statements. In the current situation, business personnel can only rely on data developers to complete data retrieval needs, which may lead to long demand scheduling and complicated communication processes. In addition, due to lack of understanding of SQL language, repeated trial and error may occur, resulting in inefficient queries and occupation of system resources.
[0003] In summary, in order to overcome the problem of difficulty and slowness in data retrieval caused by the inability to efficiently master SQL statements for data query in data warehouses, it is urgent to provide a data warehouse SQL query method or device. Summary of the invention
[0004] The purpose of this application is to provide a data warehouse SQL query method, device, medium and product, which can improve the accuracy and efficiency of data warehouse SQL query.
[0005] To achieve the above objectives, this application provides the following solutions:
[0006] In a first aspect, the present application provides a data warehouse SQL query method, the data warehouse SQL query method comprising:
[0007] The master data in the data source is stored to obtain storage data;
[0008] Determine the execution object of the SQL language; the execution object includes: data source type, data source and data warehouse;
[0009] Get the metadata of all data tables in the execution object;
[0010] The SQL requirements, metadata and prompts expressed in natural language are combined, and a large language model is used to obtain an output result; the output result includes: a generated SQL statement or a corrected SQL statement;
[0011] According to the output results of the large language model, a data query engine is used to perform data query from the stored data.
[0012] Optionally, the step of obtaining metadata of all data tables in the execution object further includes:
[0013] The metadata is stored.
[0014] Optionally, the metadata includes: data table name, data table type, data table description, field name, field type and field description.
[0015] Optionally, the SQL requirements, metadata and prompts expressed in natural language are combined and a large language model is used to obtain an output result, and then the following steps are further included:
[0016] Adjust and check the output results.
[0017] Optionally, the step of using a data query engine to perform data query from stored data based on the output result of the large language model specifically includes:
[0018] Determine the data query engine;
[0019] According to the output results, a data query engine is used to perform data query from the stored data.
[0020] Optionally, the method uses a data query engine to perform data query from the stored data according to the output result of the large language model, and then further includes:
[0021] Determine whether the query result meets the requirements; if not, return to the step of combining the SQL requirements, metadata and prompts expressed in natural language, and using a large language model to obtain the output result; if satisfied, output the query result.
[0022] In a second aspect, the present application provides a data warehouse SQL query device, the data warehouse SQL query device comprising:
[0023] A data storage module is used to store the master data in the data source to obtain storage data;
[0024] An execution object determination module is used to determine the execution object of the SQL language; the execution object includes: data source type, data source and data warehouse;
[0025] The metadata module is used to obtain the metadata of all data tables in the execution object;
[0026] The large language model module is used to combine the SQL requirements, metadata and prompts expressed in natural language, and use the large language model to obtain output results; the output results include: generated SQL statements or corrected SQL statements;
[0027] The data query module is used to query data from the data storage module using a data query engine according to the output results of the large language model.
[0028] In a third aspect, the present application provides a computer device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the data warehouse SQL query method.
[0029] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the data warehouse SQL query method when executed by a processor.
[0030] In a fifth aspect, the present application provides a computer program product, including a computer program, which implements the data warehouse SQL query method when executed by a processor.
[0031] According to the specific embodiments provided in this application, this application discloses the following technical effects:
[0032] The present application provides a data warehouse SQL query method, device, medium and product. By introducing a large language model when querying SQL in the data warehouse, the generation and error correction of SQL statements by the large language model (LLM) are performed to lower the threshold for using the SQL language, which can make it easier for non-professional users or beginners to write and execute complex SQL queries; and reduce query trial and error, and improve the efficiency and accuracy of data processing; in addition, when using the large language model to generate SQL statements or to correct SQL statements, the metadata of the data table is introduced, thereby improving the quality of SQL statements generated by the large language model. At the same time, the metadata does not involve the master data in the data warehouse, and the purpose of isolating the large language model from the actual data (master data) in the data warehouse is achieved, which can further avoid the data leakage problem that may be caused by the large language model and improve the security of the data. BRIEF DESCRIPTION OF THE DRAWINGS
[0033] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0034] Figure 1 This is a flow chart of a data warehouse SQL query method in one embodiment of the present application;
[0035] Figure 2 This is a schematic diagram of the overall process of a data warehouse SQL query method in one embodiment of the present application;
[0036] Figure 3 This is a structural diagram of a data warehouse SQL query device in one embodiment of the present application. DETAILED DESCRIPTION
[0037] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0038] In order to make the above-mentioned objects, features and advantages of the present application more obvious and easy to understand, the present application is further described in detail below with reference to the accompanying drawings and specific implementation methods.
[0039] In an exemplary embodiment, Figure 1As shown, a data warehouse SQL query method is provided, which includes the following S101 to S105. Among them:
[0040] S101, store the master data in the data source to obtain the stored data. The storage of the master data in the data source is based on the Hadoop ecosystem, which is the cornerstone of the data warehouse and supports the collection and storage of structured and semi-structured data from a variety of sources, including raw data and processed clean data; the Hadoop ecosystem is a distributed cluster that deploys two Hadoop core components, HDFS and YARN, and uses MapReduce to provide distributed storage and big data processing functions, supports Hive data warehouses and supports access to streaming data such as Kafka, and uses Flink for data stream batch processing.
[0041] S102, determining an execution object of the SQL language; the execution object includes: data source type, data source and data warehouse;
[0042] S103, obtain metadata of all data tables in the execution object; and store the metadata; the metadata includes: data table name, data table type, data table description, field name, field type and field description. The metadata is used to provide data information reference for the large language model to reduce the hallucination effect of the large language model.
[0043] S104, combining the SQL requirements, metadata and prompts expressed in natural language, and using a large language model to obtain an output result; the output result includes: a generated SQL statement or a corrected SQL statement; the large language model uses an open source model, such as ChatGLM2.
[0044] In a specific embodiment, the SQL requirement may be: analyzing the total sales of different commodities in the last month;
[0045] After that, confirm the debugged Text2SQL and SQL error correction prompts;
[0046] The corresponding prompts, extracted metadata and SQL requirements are combined, and a large language model is used to generate SQL statements or correct SQL statements.
[0047] S104 and later also include:
[0048] Adjust and check the output results.
[0049] S105: Based on the output result of the large language model, a data query engine is used to perform data query from the stored data.
[0050] S105 specifically includes:
[0051] Determine the data query engine; the data query engine can be MySQL or Hive SQL;
[0052] According to the output results, a data query engine is used to perform data query from the stored data.
[0053] like Figure 2 As shown, after S105, it also includes:
[0054] Determine whether the query result meets the requirements; if not, return to the step of combining the SQL requirements, metadata and prompts expressed in natural language, and using a large language model to obtain the output result; if satisfied, output the query result.
[0055] Based on the same inventive concept, the embodiment of the present application also provides a data warehouse SQL query device for implementing the data warehouse SQL query method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in one or more data warehouse SQL query device embodiments provided below can refer to the limitations on the data warehouse SQL query method above, and will not be repeated here.
[0056] In an exemplary embodiment, Figure 3 As shown, a data warehouse SQL query device is provided, including:
[0057] A data storage module is used to store the master data in the data source to obtain storage data;
[0058] An execution object determination module is used to determine the execution object of the SQL language; the execution object includes: data source type, data source and data warehouse;
[0059] The metadata module is used to obtain the metadata of all data tables in the execution object;
[0060] The large language model module is used to combine the SQL requirements, metadata and prompts expressed in natural language, and use the large language model to obtain output results; the output results include: generated SQL statements or corrected SQL statements;
[0061] The data query module is used to query data from the data storage module using a data query engine according to the output results of the large language model.
[0062] In an exemplary embodiment, a computer device is provided, which may be a server or a terminal. The computer device includes a processor, a memory, an input / output interface (I / O for short) and a communication interface. The processor, the memory and the input / output interface are connected via a system bus, and the communication interface is connected to the system bus via the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a data warehouse SQL query method is implemented.
[0063] In an exemplary embodiment, a computer-readable storage medium is provided, storing a computer program, and when the computer program is executed by a processor, the steps in the above method embodiments are implemented.
[0064] In an exemplary embodiment, a computer program product is provided, including a computer program, and when the computer program is executed by a processor, the steps in the above method embodiments are implemented.
[0065] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.
[0066] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM may be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM).
[0067] The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. The non-relational database may include a distributed database based on blockchain, etc., but is not limited thereto. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, etc., but is not limited thereto.
[0068] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0069] This article uses specific examples to illustrate the principles and implementation methods of this application. The description of the above embodiments is only used to help understand the method and core ideas of this application. At the same time, for those skilled in the art, according to the ideas of this application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting this application.
Claims
1. A data warehouse SQL query method, characterized in that: The data warehouse SQL query method includes: The master data in the data source is stored to obtain storage data; Determine the execution object of the SQL language; the execution object includes: data source type, data source and data warehouse; Get the metadata of all data tables in the execution object; The SQL requirements, metadata and prompts expressed in natural language are combined, and a large language model is used to obtain an output result; the output result includes: a generated SQL statement or a corrected SQL statement; According to the output results of the large language model, a data query engine is used to perform data query from the stored data.
2. The data warehouse SQL query method according to claim 1, characterized in that: The step of obtaining metadata of all data tables in the execution object further includes: The metadata is stored.
3. The data warehouse SQL query method according to claim 1, characterized in that: The metadata includes: data table name, data table type, data table description, field name, field type and field description.
4. The data warehouse SQL query method according to claim 1, characterized in that: The SQL requirements, metadata and prompts expressed in natural language are combined and a large language model is used to obtain output results, and then the following is also included: Adjust and check the output results.
5. The data warehouse SQL query method according to claim 1, characterized in that: According to the output result of the large language model, a data query engine is used to perform data query from the stored data, specifically including: Determine the data query engine; According to the output results, a data query engine is used to perform data query from the stored data.
6. The data warehouse SQL query method according to claim 1, characterized in that: According to the output result of the large language model, a data query engine is used to query data from the stored data, and then the following is further included: Determine whether the query result meets the requirements; if not, return to the step of combining the SQL requirements, metadata and prompts expressed in natural language, and using a large language model to obtain the output result; if satisfied, output the query result.
7. A data warehouse SQL query device, characterized in that: The data warehouse SQL query device includes: A data storage module is used to store the master data in the data source to obtain storage data; An execution object determination module is used to determine the execution object of the SQL language; the execution object includes: data source type, data source and data warehouse; The metadata module is used to obtain the metadata of all data tables in the execution object; The large language model module is used to combine the SQL requirements, metadata and prompts expressed in natural language, and use the large language model to obtain output results; the output results include: generated SQL statements or corrected SQL statements; The data query module is used to query data from the data storage module using a data query engine according to the output results of the large language model.
8. A computer device comprising: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the data warehouse SQL query method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the data warehouse SQL query method described in any one of claims 1 to 6 is implemented.
10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the data warehouse SQL query method described in any one of claims 1 to 6 is implemented.