Method and device for dynamically reducing retrieval data and computer equipment

Abstract

The invention discloses a method and device for dynamically reducing retrieval data and computer equipment, and the method comprises the steps: receiving a screening condition sent by a front end, and extracting a column name array and a condition array in the screening condition; performing hash operation on the column name array according to an MD5 encryption algorithm, and generating a table name of the dynamic materialized view; the table name, the condition array and the column name array are converted into SQL condition fragments according to a preset rule, the SQL condition fragments are spliced into a pre-written SQL template, and therefore a complete SQL table building statement is generated; generating a materialized view according to the SQL table establishment statement; and returning data meeting conditions to the front end according to the materialized view. According to the method, the columns needing to be queried can be determined through the screening conditions, the columns are extracted to automatically generate the materialized view, scanning of a large amount of data and pre-defining of the materialized view are reduced, the flexibility when the materialized view is used for querying data is improved, and storage resources and computing resources are utilized to the maximum extent.

Description

Technical Field

[0001] This application relates to the technical field of data retrieval, and in particular to a method, device and computer device for dynamically reducing retrieved data. Background Art

[0002] In recent years, with the rapid growth of the amount of data, the retrieval and processing of massive data have become one of the core challenges of data retrieval systems. In the big data environment, data retrieval systems usually face the following technical problems:

[0003] (1) Excessive data volume: The data grows exponentially, and it takes a long time to fully traverse all the data, resulting in a decline in retrieval efficiency, especially in real-time scenarios with multiple conditions and a large time span, which is most obvious.

[0004] (2) Diversified user needs: Different users have different focuses when retrieving data, resulting in the need for multi-dimensional storage of data retrieval.

[0005] (3) Higher real-time requirements: In many scenarios, higher requirements for real-time response are put forward, and traditional full-volume retrieval is difficult to meet the low-latency requirements.

[0006] In big data processing scenarios, materialized views, as a common data optimization method, are often used to accelerate queries. However, traditional materialized views are usually predefined by developers and generated during the initialization of the database system, which has the following problems:

[0007] (1) Lack of flexibility: Predefined materialized views cannot meet the query requirements under different conditions.

[0008] (2) Computational and storage costs: Creating too many materialized views will occupy a large amount of computing resources and storage resources. Summary of the Invention

[0009] Therefore, this application provides a method, device and computer device for dynamically reducing retrieved data to solve the problems of lack of flexibility and occupying a large amount of computing resources and storage resources when using materialized views to query data in the prior art.

[0010] To achieve the above object, this application provides the following technical solutions:

[0011] In a first aspect, a method for dynamically reducing retrieved data includes:

[0012] Step 1: Receive a query request sent by the front end; the query request contains multiple filtering conditions;

[0013] Step 2: Parse the screening conditions, and extract the column name array and condition array in the screening conditions; the column name array contains all the field names that need to participate in screening or display, and the condition array contains the specific filtering logic or values corresponding to each column.

[0014] Step 3: Perform a hash operation on the column name array according to the MD5 encryption algorithm, and generate the table name of the dynamic materialized view.

[0015] Step 4: Convert the table name, the condition array, and the column name array into SQL condition fragments according to a predetermined rule, and splice the SQL condition fragments into a pre-written SQL template to generate a complete SQL table creation statement.

[0016] Step 5: Generate a materialized view according to the SQL table creation statement.

[0017] Step 6: Return the data that meets the conditions to the front end according to the materialized view.

[0018] Preferably, in step 1, the query request is represented by a map[string]any parameter.

[0019] Preferably, in step 2, the condition array includes range conditions, equal value conditions, and fuzzy matching conditions.

[0020] Preferably, in step 3, if the column name arrays in different query conditions are the same, the same MD5 value is generated.

[0021] Preferably, in step 4, the SQL condition fragment is spliced into the WHERE clause of a pre-written SQL template.

[0022] Preferably, in step 4, the column name array can obtain the Condition required by the SQL template; the condition array can obtain the Columns, GroupBy, and OrderBy required by the SQL template.

[0023] Preferably, in step 4, when splicing the SQL condition fragment into a pre-written SQL template, the native Template library of Golang is used.

[0024] Preferably, in step 5, a third-party library SQLX is used to generate a materialized view according to the SQL table creation statement.

[0025] In a second aspect, a device for dynamically reducing retrieved data includes:

[0026] A query request receiving module, configured to receive a query request sent by the front end; the query request contains multiple screening conditions.

[0027] A column name and condition stripping module for parsing the filtering conditions and extracting an array of column names and an array of conditions from the filtering conditions; the array of column names contains all the field names that need to participate in filtering or display, and the array of conditions contains the specific filtering logic or values corresponding to each column;

[0028] A table name generation module for performing a hash operation on the array of column names according to the MD5 encryption algorithm and generating the table name of the dynamic materialized view;

[0029] An SQL table creation statement generation module for converting the table name, the array of conditions, and the array of column names into an SQL condition fragment according to a predetermined rule, and splicing the SQL condition fragment into a pre-written SQL template to generate a complete SQL table creation statement;

[0030] A materialized view generation module for generating a materialized view according to the SQL table creation statement;

[0031] A data sending module for returning data that meets the conditions to the front end according to the materialized view.

[0032] In a third aspect, a computer device includes a memory and a processor, the memory stores a computer program, and when the processor executes the computer program, the steps of a method for dynamically reducing retrieved data are implemented.

[0033] Compared with the prior art, the present application has at least the following beneficial effects:

[0034] The present application provides a method, device and computer device for dynamically reducing retrieved data, which receives filtering conditions sent by the front end, and extracts an array of column names and an array of conditions from the filtering conditions; performs a hash operation on the array of column names according to the MD5 encryption algorithm, and generates the table name of the dynamic materialized view; converts the table name, the array of conditions, and the array of column names into an SQL condition fragment according to a predetermined rule, and splices the SQL condition fragment into a pre-written SQL template to generate a complete SQL table creation statement; generates a materialized view according to the SQL table creation statement; returns data that meets the conditions to the front end according to the materialized view. Through the transmitted filtering conditions, the present application can determine the columns to be queried, extract these columns and automatically generate a materialized view, so as to achieve the purpose of reducing a large amount of data scanning and reducing predefined materialized views, improve the flexibility of querying data using the materialized view, and maximize the utilization of storage resources and computing resources. Description of the Drawings

[0035] To more intuitively illustrate the prior art and the present application, exemplary drawings are given below. It should be understood that the specific shapes and structures shown in the drawings generally should not be regarded as limiting conditions when implementing the present application; for example, those skilled in the art are capable of making routine adjustments or further optimizations to the addition / deletion / attribution division, specific shapes, positional relationships, connection methods, dimensional proportional relationships, etc. of certain units (components) based on the technical concepts disclosed in the present application and the exemplary drawings.

[0036] Figure 1 It is a flowchart of a method for dynamically reducing retrieved data provided in the first embodiment of the present application;

[0037] Figure 2 It is a schematic structural diagram of a method for dynamically reducing retrieved data provided in the first embodiment of the present application. Detailed implementation manners

[0038] The present application will be further described in detail below with reference to the drawings through specific embodiments.

[0039] In the description of the present application: Unless otherwise specified, "a plurality of" means two or more. The terms "first", "second", "third", etc. in the present application are intended to distinguish the objects being referred to, and do not have special significance in terms of technical connotations (for example, it should not be understood as emphasizing the importance level or order, etc.). Expressions such as "including", "comprising", "having", etc. also mean "not limited to" (certain units, components, materials, steps, etc.).

[0040] The terms such as "upper", "lower", "left", "right", "middle", etc. cited in the present application are usually indications of the general relative positional relationship for the convenience of intuitively understanding with reference to the drawings, and are not absolute limitations on the positional relationship in the actual product.

[0041] Embodiment 1

[0042] Please refer to Figure 1 and Figure 2 , this embodiment provides a method for dynamically reducing retrieved data. By extracting the column names in the filtering conditions, encrypting these column names through MD5 to obtain a unique identifier as the table name, and then dynamically generating columns and filtering conditions through a pre-written SQL template, data clipping and aggregation are achieved without affecting the original data. The method includes:

[0043] S1: Receive a query request sent by the front end; the query request contains a plurality of filtering conditions;

[0044] Specifically, in this embodiment, the query request is represented by the map[string]any parameter. map[string]any is a data structure type in the Go language. Here, map represents a mapping type, which is usually used to store key-value pairs. string represents that the type of the key is a string, and any represents that the type of the value is any, that is, it can be of any type. In web development, query parameters usually appear in the form of key-value pairs, where the key is a string and the value can be a string, an integer, a boolean value, etc. Therefore, using map[string]any can flexibly store these parameters.

[0045] In this step, the query requests submitted by the user are first received from the front end through an HTTP request. These requests contain multiple filtering conditions, such as the fields the user wishes to query, filtering conditions, etc. Receiving these conditions through the interface allows for flexible handling of different combinations of query requirements in subsequent processes.

[0046] It should be noted that in this step, receiving the condition format passed in from the front end through the map type enables the interface to adapt to various complex query requirements and lays a foundation for dynamically generating materialized views later.

[0047] S2: Parse the filtering conditions and extract the column name array and condition array in the filtering conditions; the column name array contains all the field names that need to participate in the filtering or display, and the condition array contains the specific filtering logic or values corresponding to each column;

[0048] Specifically, in this step, the filtering conditions passed in from the front end are parsed, and the data is divided into two parts: the column name array and the condition array:

[0049] Column name array: Contains all the field names that need to participate in the filtering or display. The column name array determines the structure of the finally queried data;

[0050] Condition array: Contains the specific filtering logic or values corresponding to each column, such as range, equality, fuzzy matching, etc.

[0051] This step structurally splits the filtering conditions into two parts: the column name array and the condition array, which can more finely control the query logic and provide a clear data basis for subsequent dynamic naming and SQL splicing.

[0052] S3: Perform a hash operation on the column name array according to the MD5 encryption algorithm and generate the table name of the dynamic materialized view;

[0053] Specifically, in this step, the MD5 encryption algorithm is used to perform a hash operation on the column name array to generate a unique identifier with a fixed length, which serves as the table name of the dynamic materialized view. If the column name arrays in different query conditions are the same, the same MD5 value is generated, facilitating the reuse of existing materialized views and avoiding repeated calculations. At the same time, MD5 encryption can ensure that the length and format of the table name comply with the database naming specifications, preventing exceptions caused by overly long names or special characters.

[0054] This step uses MD5 encryption to dynamically generate the table name, which not only achieves uniqueness and reusability but also effectively maps complex column combinations to simple and efficient identifiers, greatly improving the processing efficiency of repeated queries.

[0055] S4: Convert the table name, condition array, and column name array into SQL condition fragments according to a predefined rule, and splice the SQL condition fragments into a pre-written SQL template to generate a complete SQL table creation statement;

[0056] Specifically, in this embodiment, a set of SQL templates is pre-designed, and the SQL templates contain placeholders for inserting dynamic conditions. The SQL template is as follows:

[0057] CREATE MATERIALIZED VIEW IF NOT EXISTS

[0058] {{.DbName}}.dynamic_{{.TableName}}

[0059] ENGINE = SummingMergeTree()

[0060] PARTITION BY (qtime) ORDER BY (qtime, {{.OrderBy}}) POPULATE AS SELECT

[0061] {{range.Columns}}

[0062] {{ifeq."rdata"}}

[0063] arrayElement(arraySort(rdata), 1) as rdata

[0064] {{else}}

[0065] {{.}}

[0066] {{end}}

[0067] {{end}}

[0068] count() as count

[0069] FROM query_logs

[0070] WHERE

[0071] {{template "Condition"}}

[0072] GROUP BY qtime, {{.GroupBy}}{{end}}

[0073] In this step, the extracted condition array and column name array are converted into SQL condition fragments according to the predefined rules, and then these fragments are safely spliced into the WHERE clause of the SQL template to generate a complete query statement. Attention should be paid to preventing SQL injection during this process. Therefore, parameterized queries or other security mechanisms are usually adopted during splicing.

[0074] More specifically, in this step, the Columns, GroupBy, and OrderBy required by the SQL template can be obtained according to the column name array, and the Condition required by the SQL template can be obtained according to the condition array. Columns with special processing during the generation process can also be processed through if in the template. By using the native Template library in Golang, the column name array and condition array are filled into the placeholders in the SQL template. For example, {{.DbName}} in the SQL template represents the placeholder for the database name. After filling the parameters in the SQL template, a complete SQL table creation statement is obtained.

[0075] In this step, by pre-writing the SQL template, the dual advantages of dynamic splicing and standardized execution are achieved. It can not only flexibly handle variable query conditions but also ensure the security and efficiency of SQL execution at the system level, thus significantly reducing the unnecessary data scanning range.

[0076] S5: Generate a materialized view according to the SQL table creation statement;

[0077] Specifically, in this step, the third-party library SQLX is used to generate a materialized view according to the SQL table creation statement. This view has pre-screened and stored the data according to specific conditions.

[0078] S6: Return the data that meets the conditions to the front end according to the materialized view.

[0079] Specifically, in this step, the view is queried to quickly return the data that meets the conditions to the front-end users. The use of the materialized view avoids the need to re-scan and calculate a large amount of data every time a query is made, greatly improving the response speed and system throughput. When a query request with the same conditions appears again, the existing materialized view can be directly reused to further optimize the query performance.

[0080] The introduction of the dynamic materialized view technology enables efficient response to user queries even in the face of large amounts of data. By pre-computing and caching query results, it not only reduces the database load but also improves the query efficiency. At the same time, it supports condition reuse and automatic update strategies, realizing an intelligent method for reducing query data.

[0081] A method for dynamically reducing retrieved data provided in this embodiment generates a materialized view in advance according to filtering conditions before the query data, avoiding scanning a large amount of data in the original table directly. The advantage of this method is that it does not modify or trim the original data, but derives an additional table based on the original data, and does not pre-create in advance, saving system resources.

[0082] A method for dynamically reducing retrieved data provided in this embodiment has the following advantages:

[0083] (1) High efficiency: By dynamically generating materialized views, a large number of unnecessary view generations and maintenance are reduced;

[0084] (2) Real-time performance: According to the query conditions, the scanned columns and the data meeting the conditions are accurately determined, reducing the data volume scanned and greatly improving the response speed;

[0085] (3) Resource optimization: Through the expiration management of materialized views, storage resources and computing resources are maximally utilized;

[0086] (4) Flexibility: It can meet different-dimensional queries of customers in different scenarios;

[0087] (5) Reusability: When filtering with the same screening conditions, the existing materialized views can be directly reused without repeated creation.

[0088] Embodiment 2

[0089] This embodiment provides a device for dynamically reducing retrieved data, including:

[0090] A query request receiving module, configured to receive a query request sent by the front end; the query request contains multiple screening conditions;

[0091] A column name and condition stripping module, configured to parse the screening conditions and extract the column name array and condition array in the screening conditions; the column name array contains all field names that need to participate in screening or display, and the condition array contains the specific filtering logic or values corresponding to each column;

[0092] A table name generating module, configured to perform a hash operation on the column name array according to the MD5 encryption algorithm and generate the table name of the dynamic materialized view;

[0093] The SQL table creation statement generation module is used to convert the table name, the condition array, and the column name array into an SQL condition fragment according to a predetermined rule, and splice the SQL condition fragment into a pre-written SQL template to generate a complete SQL table creation statement;

[0094] The materialized view generation module is used to generate a materialized view according to the SQL table creation statement;

[0095] The data sending module is used to return data that meets the conditions to the front end according to the materialized view.

[0096] For the specific implementation content of each module in a device for dynamically reducing retrieved data, reference can be made to the limitations on a method for dynamically reducing retrieved data in the foregoing text, and details are not described herein again.

[0097] Embodiment III

[0098] This embodiment provides a computer device, including a memory and a processor. The memory stores a computer program, and when the processor executes the computer program, the steps of a method for dynamically reducing retrieved data are implemented.

[0099] The technical features of the above embodiments can be combined arbitrarily (as long as there is no contradiction in the combination of these technical features). For the sake of brief description, not all possible combinations of the technical features in the above embodiments are described; these embodiments that are not explicitly written out should also be considered to be within the scope described in this specification.

Claims

1. A method for dynamically reducing retrieval data, characterized in that: include: Step 1: Receive the query request sent by the front end; The query request includes multiple screening conditions; Step 2: Parse the filtering conditions and extract the column name array and condition array in the filtering conditions; the column name array contains all the field names that need to be filtered or displayed, and the condition array contains the specific filtering logic or value corresponding to each column; Step 3: Perform a hash operation on the column name array according to the MD5 encryption algorithm, and generate a table name of the dynamic materialized view; Step 4: convert the table name, the condition array and the column name array into SQL condition fragments according to a predetermined rule, and splice the SQL condition fragments into a pre-written SQL template to generate a complete SQL table creation statement; Step 5: Generate a materialized view according to the SQL table creation statement; Step 6: Return the data that meets the conditions to the front end according to the materialized view.

2. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 1, the query request is represented by a map[string]any parameter.

3. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 2, the condition array includes range conditions, equal value conditions and fuzzy matching conditions.

4. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 3, if the column name arrays in different query conditions are the same, the same MD5 value is generated.

5. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 4, the SQL condition fragment is spliced ​​into the WHERE clause of the pre-written SQL template.

6. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 4, the column name array can obtain the Condition required by the SQL template; the condition array can obtain the Columns, GroupBy and OrderBy required by the SQL template.

7. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 4, the Golang native Template library is used to splice the SQL conditional fragment into the pre-written SQL template.

8. The method for dynamically reducing retrieval data according to claim 1, characterized in that: In step 5, a materialized view is generated according to the SQL table creation statement using a third-party library SQLX.

9. A device for dynamically reducing search data, characterized in that: include: A query request receiving module, used to receive the query request sent by the front end; The query request includes multiple screening conditions; The column name and condition stripping module is used to parse the filtering conditions and extract the column name array and condition array in the filtering conditions; the column name array contains all the field names that need to be screened or displayed, and the condition array contains the specific filtering logic or value corresponding to each column; A table name generation module, used for performing a hash operation on the column name array according to the MD5 encryption algorithm, and generating a table name of the dynamic materialized view; An SQL table creation statement generation module, used for converting the table name, the condition array and the column name array into SQL condition fragments according to a predetermined rule, and splicing the SQL condition fragments into a pre-written SQL template, thereby generating a complete SQL table creation statement; A materialized view generation module, used to generate a materialized view according to the SQL table creation statement; The data sending module is used to return the data that meets the conditions to the front end according to the materialized view.

10. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 8 are implemented.

Images