CN113986933A

CN113986933A - Materialized view creating method and device, storage medium and electronic equipment

Info

Publication number: CN113986933A
Application number: CN202111032832.8A
Authority: CN
Inventors: 张友军; 郭俊; 杨诗旻
Original assignee: Beijing Volcano Engine Technology Co Ltd
Current assignee: Beijing Volcano Engine Technology Co Ltd
Priority date: 2021-09-03
Filing date: 2021-09-03
Publication date: 2022-01-28
Also published as: WO2023029855A1

Abstract

The disclosure relates to a method and a device for creating a materialized view, a storage medium and an electronic device, wherein the method comprises the following steps: extracting original sub-queries in the historical query statement; generating a candidate query set according to a preset rule according to the original sub-query; determining a target query from the candidate query set, wherein the target query is a query with a query profit greater than a preset profit threshold; creating a materialized view corresponding to the target query, wherein the materialized view is used for processing the corresponding query, and the target query is a query with a query income greater than a preset income threshold in the candidate query set, so that the hit rate of the materialized view can be improved; in addition, the materialized view is established only based on the target query, and the calculation cost and the storage cost of the materialized view can be reduced.

Description

Materialized view creating method and device, storage medium and electronic equipment

Technical Field

The present disclosure relates to the field of computer technologies, and in particular, to a method and an apparatus for creating a materialized view, a storage medium, and an electronic device.

Background

In the field of data analysis, On-Line Analytical Processing (mol) plays a very important role, and it greatly shortens the response time of data analysis and reduces the influence of data scale On query efficiency by using a precomputation-based data query technique.

However, the application of online analytical processing techniques needs to rely on materialized views. The materialized view is a set of the pre-calculation results, and when the query corresponding to the materialized view is encountered, the materialized view can be directly multiplexed, the result of the materialized view is read, and repeated calculation is avoided, so that the purpose of improving the query efficiency is achieved. In addition, building a materialized view based on a query requires corresponding computation cost and storage cost, and therefore, how to select an appropriate query to build a corresponding materialized view is crucial.

Disclosure of Invention

This summary is provided to introduce a selection of concepts in a simplified form that are further described below in the detailed description. This summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

In a first aspect, the present disclosure provides a method for creating a materialized view, including:

extracting original sub-queries in the historical query statement;

generating a candidate query set according to a preset rule according to the original sub-query;

determining a target query from the candidate query set, wherein the target query is a query with a query profit greater than a preset profit threshold;

creating a materialized view corresponding to the target query, wherein the materialized view is used for processing the corresponding query.

In a second aspect, the present disclosure provides an apparatus for creating a materialized view, comprising:

the extraction module is used for extracting original sub-queries in the historical query statement;

the generating module is used for generating a candidate query set according to the original sub-query and a preset rule;

a determining module, configured to determine a target query from the candidate query set, where the target query is a query whose query profit is greater than a preset profit threshold;

a creation module to create a materialized view corresponding to the target query, wherein the materialized view is to process a corresponding query.

In a third aspect, the present disclosure provides a computer-readable medium having stored thereon a computer program which, when executed by a processing apparatus, performs the steps of the method described in the first aspect above.

In a fourth aspect, the present disclosure provides an electronic device comprising:

a storage device having a computer program stored thereon;

processing means for executing the computer program in the storage means to implement the steps of the method of the first aspect described above.

According to the technical scheme, the original sub-queries extracted from the historical query sentences are used for constructing the candidate query set, the target queries are determined in the constructed candidate query set, and the materialized views corresponding to the target queries are created, so that the hit rate of the materialized views can be improved as the target queries are queries with query benefits in the candidate query set larger than a preset benefit threshold value; in addition, the materialized view is established only based on the target query, and the calculation cost and the storage cost of the materialized view can be reduced.

Additional features and advantages of the disclosure will be set forth in the detailed description which follows.

Drawings

The above and other features, advantages and aspects of various embodiments of the present disclosure will become more apparent from the following detailed description when taken in conjunction with the accompanying drawings. Throughout the drawings, the same or similar reference numbers refer to the same or similar elements. It should be understood that the drawings are schematic and that elements and features are not necessarily drawn to scale. In the drawings:

FIG. 1 is a flow chart illustrating a method of creation of a materialized view in accordance with an exemplary embodiment of the present disclosure.

FIG. 2 is a flow diagram illustrating a method for generating a set of candidate queries according to an exemplary embodiment of the present disclosure.

FIG. 3 is a block diagram illustrating an apparatus for creating a materialized view in accordance with an exemplary embodiment of the present disclosure.

Fig. 4 is a schematic structural diagram of an electronic device according to an exemplary embodiment of the present disclosure.

Detailed Description

Embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. While certain embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be embodied in various forms and should not be construed as limited to the embodiments set forth herein, but rather are provided for a more thorough and complete understanding of the present disclosure. It should be understood that the drawings and examples of the disclosure are for illustration purposes only and are not intended to limit the scope of the disclosure.

It should be understood that the various steps recited in the method embodiments of the present disclosure may be performed in a different order, and/or performed in parallel. Moreover, method embodiments may include additional steps and/or omit performing the illustrated steps. The scope of the present disclosure is not limited in this respect.

The term "include" and variations thereof as used herein are open-ended, i.e., "including but not limited to". The term "based on" is "based, at least in part, on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". Relevant definitions for other terms will be given in the following description.

It should be noted that the terms "first", "second", and the like in the present disclosure are only used for distinguishing different devices, modules or units, and are not used for limiting the order or interdependence relationship of the functions performed by the devices, modules or units.

It is noted that references to "a", "an", and "the" modifications in this disclosure are intended to be illustrative rather than limiting, and that those skilled in the art will recognize that "one or more" may be used unless the context clearly dictates otherwise.

The names of messages or information exchanged between devices in the embodiments of the present disclosure are for illustrative purposes only, and are not intended to limit the scope of the messages or information.

It will be appreciated that creating a materialized view requires computational and storage costs, and that creating a materialized view with a low hit rate results in a low query benefit for the materialized view, where the query benefit is determined by the cost (computational and storage costs) and the hit rate. For example, the cost of creating the first materialized view is the same as the cost of the second materialized view, but the number of hits for the first materialized view is 1 and the number of hits for the second materialized view is 10, the query benefit of the first materialized view is lower than the query benefit of the second materialized view.

In the related art, if a corresponding materialized view is created for all sub-queries of a query statement, the cost of the materialized view is high because there are many sub-queries but the hit rate of the materialized view corresponding to some sub-queries is reduced. Therefore, it is crucial how to determine queries that can encompass most high frequency query statements and thus build high hit rate materialized views.

In view of this, the embodiment of the present disclosure provides a method for creating a materialized view, where a candidate query set is created by using an original sub-query extracted from a historical query statement, a target query is determined in the candidate query set, and a materialized view corresponding to the target query is created, and since the target query is a query in which a query profit in the candidate query set is greater than a preset profit threshold, a hit rate of the materialized view can be increased; in addition, the materialized view is established only based on the target query, and the calculation cost and the storage cost of the materialized view can be reduced.

Before the embodiments of the present disclosure are explained in detail, a possible application scenario in the embodiments of the present disclosure is explained in order to help understand a creation method of a materialized view in the embodiments of the present disclosure.

In some possible application scenarios, when the same query statement needs to be calculated for multiple times, a materialized view corresponding to the query statement may be created for such queries, so that a result may be obtained directly by querying the materialized view when a query corresponding to the query statement is subsequently performed.

FIG. 1 is a flow chart illustrating a method of creation of a materialized view in accordance with an exemplary embodiment of the present disclosure. Referring to fig. 1, the creation method of the materialized view includes:

step 101, extracting original sub-queries in historical query statements.

It can be understood that before the original sub-query is extracted, a history query statement is required to be obtained, and the query statement is a statement with a query condition, so that the range of the query required by the user is determined.

It should be noted that the historical query statement is a historical structured query statement, such as an SQL statement, before the materialized view is created. The following explains the present embodiment by using a history query statement as a history SQL statement.

In some embodiments, the syntax parsing may be performed on the historical SQL statement to obtain a syntax tree, and then, based on the syntax tree, the sub-queries in the syntax tree are extracted as the original sub-queries. The syntax tree is a graphic representation of a sentence structure, represents the derivation result of the sentence and is beneficial to understanding the hierarchy of the sentence syntax structure. Therefore, it is convenient to extract all the sub-queries in the SQL statement by processing the SQL statement into a syntax tree.

And 102, generating a candidate query set according to the original sub-query and a preset rule.

The preset rule is used for generating a query different from the original sub-query on the basis of the original sub-query.

In some embodiments, two original sub-queries may be combined to generate a query that distinguishes the query conditions corresponding to the two sub-queries, thereby increasing the diversity of the query conditions and further increasing the number of queries.

It should be noted that queries that can cover multiple types of query conditions can be generated through the original sub-queries, and if a materialized view is established by using such queries, the hit rate of the materialized view can be increased.

And 103, determining a target query from the candidate query set, wherein the target query is a query with a query profit larger than a preset profit threshold value.

It should be noted that the target query represents a query range corresponding to the data that the user cares about.

In some embodiments, the query profit may be determined based on the number of queries, the size of the base table, and the parameters of the column in the base table being queried. Wherein the parameter may be, for example, a convergence ratio. The convergence ratio can reflect the storage cost of the materialized view, and the higher the convergence ratio is, the lower the cost of the corresponding materialized view is.

Step 104, creating a materialized view corresponding to the target query, wherein the materialized view is used for processing the corresponding query.

It should be noted that, for a certain query, the query result corresponding to the query may be extracted from the materialized view, and the query is the corresponding query of the materialized view.

In some embodiments, the materialized view may be applied in the following manner. When a new query is received, the Oracle database can automatically judge whether a query result can be obtained by querying the materialized view, if so, the new query is subjected to query rewriting, the rewritten query statement is executed, and the rewritten query statement directly reads data from the calculated materialized view so as to obtain the query result corresponding to the new query from the corresponding materialized view, thereby avoiding aggregation or connection operation.

Wherein the query rewrite is to rewrite the query statement. In addition, the related process of judging whether the query result can be obtained by querying the materialized view and the related process of query rewriting may refer to related technologies, which are not described herein in detail in the embodiments of the present disclosure.

According to the scheme, the original sub-queries extracted from the historical query statement are used for constructing the candidate query set, the target queries are determined in the constructed candidate query set, the materialized views corresponding to the target queries are created, and the target queries are queries with query benefits larger than a preset benefit threshold value in the candidate query set, so that the hit rate of the materialized views can be improved; in addition, compared with a scheme of establishing a corresponding materialized view based on each seed query, the method can reduce the calculation cost and the storage cost of the materialized view, and the performance of the system tends to be stable on the premise of reducing the calculation cost and the storage cost.

In order to make the creation method of the materialized view provided by the present disclosure more understandable to those skilled in the art, the above steps are exemplified in detail below.

In some embodiments, where the original sub-query includes a table field, step 102 may be implemented as follows: dividing original sub-queries with the same table fields into the same group according to the table fields of the original sub-queries to obtain a plurality of original sub-query sets; and aiming at each original sub-query set, generating a candidate query set according to a preset rule according to the original sub-queries in the original sub-query set.

It should be noted that the table field of the original sub-query represents the base table corresponding to the data that the original sub-query wants to query. The base table includes a dimension column, an index column, and the like. In some application scenarios, the dimension column may be, for example, employee identification, date of transaction, etc., the index column may be salary, etc., and correspondingly, the original sub-query may be, for example, month data for which the salary of query employee 1 is greater than 10000.

In some embodiments, the original sub-query further comprises: a filter condition field, a dimension column field, an index column field, and a number of queries field. The query condition field, the dimension column field and the index column field represent the query condition of the original sub-query; the query times of the original sub-query represent the number of times the original sub-query has been historically queried.

As shown in table 1, including the extracted original sub-queries and the field descriptions related to the original sub-queries, the divided original sub-query set includes two original sub-queries according to each original sub-query and the table field corresponding to each original sub-query shown in table 1. One original sub-query set is Querpattern 1, Querpattern 2, Querpattern 3, Querpattern 4, and Querpattern 5 that include only the Table fields A. Another set of original sub-queries is QueryAttern 6 and QueryAttern 7, which include only the Table fields A and B.

TABLE 1

By the method, the original sub-queries are grouped based on the table fields of the original sub-queries, and due to the fact that the original sub-queries corresponding to the same table field are high in correlation, selection of the target query is optimized, and further the optimization effect of the materialized view set is improved.

In some embodiments, the original sub-query set determined in table 1 includes, for example, Querypattern1, Querypattern2, Querypattern3, Querypattern4, and Querypattern5, and the generation process of the candidate query set is explained in conjunction with a flowchart for generating the candidate query set shown in fig. 2. For each original sub-query set, a candidate query set may be generated by the steps in the flow chart shown in fig. 2, with reference to fig. 2, including the steps of:

step 201, the original sub-query set is used as the current sub-query set.

Illustratively, the original sub-query set includes Querpypattern 1, Querpypattern 2, Querpypattern 3, Querpypattern 4, and Querpattern 5, and the current sub-query set includes Querpattern 1, Querpattern 2, Querpattern 3, Querpattern 4, and Querpattern 5.

Step 202, generating a candidate query set according to a preset merging rule according to the queries in the current sub-query set.

It should be noted that the preset merge rule merges queries in the current sub-query set, for example, a new query may be generated by merging queries in the Querypattern1 and the Querypattern 2.

In some embodiments, the queries in the candidate query set include a filter condition field, a dimension column field, an index column field, and a query number field, and the candidate query set may be generated according to the queries in the current sub-query set and according to a preset merge rule in the following manner: aiming at every two queries in the current sub-query set, merging the two queries according to the filtering condition field, the dimension column field, the index column field and the query frequency field of the two queries; and generating a candidate query set according to the combined result and the query included in the current sub-query set.

Illustratively, the set of candidate queries generated for QueryAttern 1, QueryAttern 2, QueryAttern 3, QueryAttern 4, and QueryAttern 5 are shown in Table 2 below.

Index	Filter condition field	Dimension column field	Index column field	Number of queries
					1	d>0,e<10	a,b	f,g	10
2	d>0	a,b	f,h	5
					3	e<10	a	f	15
4	d>0	b,c	f,h,i	20
					5	e>0	a	f,i	20
6(1&2)	d>0	a,b	f,g,h	15(10+5)
					7(1&3)	e<10	a,b	f,g	25(10+15)
8(1&4)	d>0	a,b c	f,g,h,i	30(10+20)
					9(1&5)	e>0	a,b	f,g,i	30(10+20)
10(2&3)	/	a,b,d,e	f,h	20(5+15)
					11(2&4)	d>0	a,b c	f,h,i	25(5+20)
12(2&5)	/	a,b,d,e	f,h,i	25(5+20)
					13(3&4)	/	a,b,c,d,e	f,h,i	35(15+20)
14(3&5	/	a,e	f,i	35(15+20)
					15(4&5)	/	a,b,c,d,e	f,h,i	40(20+20)

TABLE 2

It should be noted that indexes 1 to 5 correspond to Querypattern1, Querypattern2, Querypattern3, Querypattern4, and Querypattern5, respectively. The query corresponding to the index 6-15 is obtained by combining every two Querpattern corresponding to the index 1-5.

For the filter condition field of the merged query, when the filter condition fields of the two queries to be merged intersect, the filter condition field of the merged query is the intersection of the filter condition fields of the two queries, for example, the filter condition field of the query corresponding to the index 6 merged with the query corresponding to the reference index 1 and the reference index 2. In the case where there is no intersection between the filter condition fields of the two queries to be merged, the filter condition field of the query obtained by merging is a superset of the filter condition fields of the two queries, for example, the filter condition field obtained by referring to the query corresponding to the index 10 obtained by merging the queries corresponding to the index 2 and the index 3.

For the dimension column field of the query obtained by merging, the dimension column field of the query obtained by merging is the union of the dimension column fields of the two queries subjected to merging. For example, the dimension column field of the query corresponding to index 8 is obtained by referring to the query corresponding to index 1 and index 4.

For the index column field of the query obtained by merging, the index column field of the query obtained by merging is the union of the index column fields of the two queries subjected to merging. For example, the index column field of the query corresponding to index 8 obtained by merging the queries corresponding to index 1 and index 4 is referred to.

For the query frequency of the merged query, the query frequency of the merged query is the sum of the query frequencies of the two queries subjected to merging. For example, the number of queries for the query corresponding to index 8, which is obtained by combining the queries corresponding to index 1 and index 4, is referred to.

And 203, sequencing the queries in the candidate query set according to a preset sequencing rule, and selecting a preset number of queries from the sequencing results according to the sequencing results to form a new candidate query set.

It should be noted that the preset ordering rule is to arrange all queries in the candidate query set according to a certain rule.

In some embodiments, the preset arrangement rule may be ordered according to the filter condition field, the dimension column field, and the query times field.

Further, in a case where the preset arrangement rule includes a rule set according to the filtering condition field, the dimension column field, and the query number field, in some embodiments, the queries in the candidate query set may be arranged in the following arrangement manner:

the queries in the candidate query set are sorted in an ascending order according to the number of the dimension columns to obtain a first sorting result; on the basis of the first sorting result, performing descending sorting on the first sorting result according to the query times to obtain a second sorting result; and on the basis of the second sorting result, sorting the second sorting result in an ascending order according to the condition number of the filtering condition to obtain a final sorting result. For example, the ranking results obtained by ranking the candidate query set shown in table 2 in this ranking manner are shown in table 3 below.

Index	Filter condition field	Dimension column field	Index column field	Number of queries
					5	e>0	a	f,i	20
3	e<10	a	f	15
					14(3&5	/	a,e	f,i	35(15+20)
9(1&5)	e>0	a,b	f,g,i	30(10+20)
					7(1&3)	e<10	a,b	f,g	25(10+15)
4	d>0	b,c	f,h,i	20
					6(1&2)	d>0	a,b	f,g,h	15(10+5)
1	d>0,e<10	a,b	f,g	10
					2	d>0	a,b	f,h	5
8(1&4)	d>0	a,b c	f,g,h,i	30(10+20)
					11(2&4)	d>0	a,b c	f,h,i	25(5+20)
12(2&5)	/	a,b,d,e	f,h,i	25(5+20)
					10(2&3)	/	a,b,d,e	f,h	20(5+15)
15(4&5)	/	a,b,c,d,e	f,h,i	40(20+20)
					13(3&4)	/	a,b,c,d,e	f,h,i	35(15+20)

TABLE 3

It should be noted that the smaller the number of dimension columns, the higher the expected convergence ratio, the lower the required storage cost; the higher the number of queries, the higher the expected hit rate; the smaller the number of the conditions of the filtering conditions is, the more the query scenes are covered, so that the hit rate can be improved.

Considering that the order determines the importance of different queries requiring the generation of materialized views, the order of each arrangement rule is very important on the premise that various arrangement rules exist. Based on the above, in consideration of the strength of various arrangement rules during arrangement, the arrangement is performed according to the arranged ordering sequence (dimension column-query times-filtering condition), so that the optimal query can be screened to the greatest extent.

In some embodiments, taking the preset number of 5 as an example, according to the sorting result in table 3, the new candidate query set is composed of queries corresponding to index 5, index 3, index 14, index 9, and index 7, respectively.

And step 204, judging whether a preset iteration condition is met. In case it is determined that the preset iteration condition is satisfied, step 205 is executed: the current set of candidate queries is output. In the case where it is determined that the preset iteration condition is not satisfied, step 206 is performed: the new set of candidate queries is treated as a new current set of sub-queries and execution returns to step 202.

In some embodiments, the preset iteration condition may be whether the number of iterations reaches a preset number.

In some embodiments, the number of times step 202 is performed may be counted, and step 204 may be: it is determined whether the execution count of step 202 reaches a predetermined count. And under the condition that the execution times of the step 202 reach the preset times, determining that the preset iteration condition is met; in the case where the number of times of execution of step 202 does not reach the preset number of times, it is determined that the preset iteration condition is not satisfied.

It should be noted that the preset number of times may be set according to actual situations, and the embodiment is not limited herein.

Illustratively, to form the new candidate query set includes queries corresponding to index 5, index 3, index 14, index 9, and index 7, respectively. Under the condition that the preset iteration condition is met, the output current candidate query set is a candidate query set consisting of queries respectively corresponding to an index 5, an index 3, an index 14, an index 9 and an index 7; in the case that it is determined that the preset iteration condition is not satisfied, a candidate query set including queries respectively corresponding to index 5, index 3, index 14, index 9, and index 7 is taken as a current sub-query set, and step 202 is performed.

By the method, the query in the candidate query set is continuously optimized in an iteration mode, so that the hit rate of the materialized view established according to the query in the optimized candidate query set is improved.

In some embodiments, step 103 shown in fig. 1 may be implemented by: calculating index parameters of each query in the candidate query set; and selecting the query of which the index parameter meets a preset parameter condition corresponding to the index parameter as a target query according to the index parameter of each query in the candidate query set.

It should be noted that the target query is selected from the candidate query set generated in the last iteration.

In some embodiments, the index parameters may include the data amount of the base table corresponding to the query in the candidate query set, the query times of the query, and the convergence ratio of the query. Given the low cost of small data table queries, queries corresponding to such tables may not require the creation of materialized views. The query times of a query may reflect the hit rate of the materialized view corresponding to the subsequent query. Thus, queries with higher query times are more suitable for building materialized views.

In some embodiments, the amount of data in the base table may be obtained by reading the storage space occupied by the base table in the database.

In some embodiments, the query number of the query may be obtained by the query number field of the query.

In some embodiments, the convergence ratio may be determined based on the number of rows of the dimension column corresponding to the dimension column field, where the number of rows may be understood as the number of values of the dimension column. For example, the convergence ratio may be a ratio of the number of rows before calculation to the number of rows after calculation. For example, for a query with an index of 5 in table 3, if the number of rows of the a-dimensional column represented by the dimension column field in the base table is 1000 rows (i.e., the number of rows before calculation), that is, there are 1000 values, and the number of rows of the calculated query result corresponding to the query with the index of 5 is 10 rows (i.e., the number of rows after calculation), that is, only 10 values of the a-dimensional column obtained corresponding to the query result are obtained, then the convergence ratio is 10 at this time.

In some embodiments, if the number of dimension columns corresponding to the same query in the candidate query set is multiple, the ratio of the sum of the convergence ratios corresponding to each dimension column to the number of dimension columns may be determined as the convergence ratio corresponding to the query.

In some embodiments, if the index parameters include a plurality of index parameters, the target query is a query in which all the index parameters satisfy the corresponding preset parameter conditions.

For example, taking the example that the index parameter includes the data amount of the base table corresponding to the query, the query times of the query, and the convergence ratio of the query, the query with the data amount of the base table smaller than the preset data amount threshold, the query times larger than the preset query times threshold, and the convergence ratio larger than the preset convergence ratio threshold is the target query.

The preset data volume threshold, the preset query frequency threshold and the preset convergence ratio threshold may be set according to actual conditions. The present embodiment is not limited thereto.

By the method, the target query is selected comprehensively by adopting the multi-dimensional index parameters, the target selection is more comprehensive, the optimization effect of the target query is improved, the quality of the materialized view is improved, and a reliable data basis is provided for the subsequent application based on the materialized view.

In some embodiments, step 104 shown in fig. 1 may be implemented by: setting a data period of a materialized view corresponding to the target query, wherein the materialized view includes data corresponding to the data period in a base table corresponding to the table field of the target query; generating a data definition language according to the target query and the data time interval; a data definition language is executed to create a materialized view corresponding to a target query.

Note that the data period characterizes the data range of the initialization data of the materialized view. For example, the data period may be data satisfying the query condition within 2 days. Based on this, the materialized view created includes two days of data.

It is contemplated that in an application scenario with related materialized views, updates may be performed to the materialized view based on data updates of the base table. Particularly, in a data scene updated along with the date, the data amount of the materialized view is increased for updating the data along with the continuous extension of the date, so that the storage cost of the materialized view is increased, but based on the materialized view, if the query based on the materialized view is biased to query the latest data, the probability that the data stored earlier in the materialized view is not hit is increased. Based on the method, the expired data in the materialized view can be guaranteed to be abandoned by setting the data period, so that the storage cost is reduced.

For example, in an application scenario in which a user prefers to query data within three days, the initially created materialized view includes data with a data period of 3 days (for example, No. 1 to No. 3), and after the data of No. 4 is updated by the base table, the data meeting the filtering condition of the query corresponding to the materialized view in No. 4 is updated into the materialized view in the related art, and at this time, the materialized view includes the data of No. 1 to No. 4. But since the user prefers to query data for the last three days, the hit rate for data number 1 in the materialized view drops. With the continuous extension of dates, the storage cost of the materialized view is greatly increased. However, by adopting the scheme improved by the embodiment of the disclosure, the data belonging to number 1 in the materialized view can be deleted based on the set data period, and the data meeting the screening condition of the query corresponding to the materialized view in number 4 is updated to the materialized view, so that the storage cost of the materialized view is reduced under the condition that the related data of the latest 3 days of the query can be met.

It will be appreciated that the data period may be determined based on a history corresponding to the target query. The history characterizes the period of data required for each query.

For example, an update task may be created at the same time as the materialized view is created for the update of the materialized view.

Illustratively, the data definition language may be an executable SQL statement. Based on the class of statements, creation of a materialized view is implemented.

In some embodiments, the method further comprises: detecting whether the materialized view belongs to a failure view or not; in the event that the materialized view is detected as belonging to the failed view, the materialized view is deleted.

In some embodiments, the failure view may be a materialized view that misses for a preset duration. For example, upon detecting that a materialized view misses within 3 days, the materialized view may be determined to be a failed view.

In some embodiments, the failure view may also be a materialized view that misses a predetermined number of times within a predetermined duration.

The preset time duration can be set according to actual conditions, and the preset time duration can be 1 day or 2 days. The present embodiment is not limited thereto.

In some embodiments, the stale view may be a materialized view in which the corresponding base table has been deleted. For example, a materialized view may include query results that are data in base table A, and when it is detected that base table A is deleted from storage, the materialized view may be determined to be a failed view.

In some embodiments, the failure view may be a materialized view of changes to the corresponding base table data. For example, the materialized view includes query results that are data in the base table A, and when a change in a portion of the data in the base table A is detected, the materialized view is determined to be a failed view. In consideration of ensuring the consistency of data, the materialized view can be deleted when the data in the base table corresponding to the materialized view is changed, so that the condition that the wrong materialized view based on the data is queried to obtain a wrong query result is avoided.

In some embodiments, the foregoing detection may be implemented based on a corresponding monitoring task created for the materialized view.

In some embodiments, deleting a materialized view may be accomplished by generating a corresponding SQL execute statement. In some embodiments, after a materialized view is deleted, tasks created for the materialized view may also be deleted, e.g., update tasks, monitor tasks.

Based on the same inventive concept, the embodiment of the present disclosure provides a creating apparatus of a materialized view, and referring to fig. 3, the creating apparatus 300 includes:

an extraction module 301, configured to extract an original sub-query in a historical query statement;

a generating module 302, configured to generate a candidate query set according to a preset rule according to the original sub-query;

a determining module 303, configured to determine a target query from the candidate query set, where the target query is a query whose query profit is greater than a preset profit threshold;

a creation module 304 to create a materialized view corresponding to the target query, wherein the materialized view is to process a corresponding query.

Optionally, the original sub-query includes a table field, and the generating module 302 includes:

the grouping submodule is used for dividing the original sub-queries with the same table field into the same group according to the table field of the original sub-queries to obtain a plurality of original sub-query sets;

and the generation sub-module is used for generating a candidate query set according to a preset rule according to the original sub-queries in the original sub-query set aiming at each original sub-query set.

Optionally, the generating sub-module includes:

a first determining subunit, configured to use the original sub-query set as a current sub-query set;

a merging subunit, configured to generate a candidate query set according to a preset merging rule according to the query in the current sub-query set;

the sorting subunit is used for sorting the queries in the candidate query set according to a preset sorting rule; selecting a preset number of queries from the sequencing results according to the sequencing results to form a new candidate query set;

and the second determining subunit is used for taking the new candidate query set as a new current sub-query set under the condition that the preset iteration condition is determined not to be met.

Optionally, the queries in the candidate query set include a filtering condition field, a dimension column field, an index column field, and a query frequency field, and the merging subunit is specifically configured to, for each two queries in the current sub-query set, merge the two queries according to the filtering condition field, the dimension column field, the index column field, and the query frequency field of the two queries; and generating a candidate query set according to the combined result and the query included in the current sub-query set.

Optionally, the determining module 303 includes:

the calculation sub-module is used for calculating index parameters of each query in the candidate query set;

and the selection submodule is used for selecting the query of which the index parameter meets the preset parameter condition corresponding to the index parameter as the target query according to the index parameter of each query in the candidate query set.

Optionally, the creating module 304 includes:

a setting sub-module to set a data period of a materialized view corresponding to the target query, wherein the materialized view includes data corresponding to the data period in a base table corresponding to a table field of the target query;

the statement generation submodule is used for generating a data definition language according to the target query and the data time interval;

and the execution sub-module is used for executing the data definition language to create a materialized view corresponding to the target query.

Optionally, the creating apparatus 300 further includes:

the detection module is used for detecting whether the materialized view belongs to a failure view;

a deletion module to delete the materialized view if it is detected that the materialized view belongs to the failed view.

Based on the same inventive concept, the disclosed embodiments provide a computer-readable medium, on which a computer program is stored, which, when executed by a processing apparatus, implements the steps of the creation method described in the method embodiments.

Based on the same inventive concept, an embodiment of the present disclosure provides an electronic device, including:

a storage device having a computer program stored thereon;

processing means for executing said computer program in said storage means to implement the steps of the creation method in the method embodiments.

Referring now to FIG. 4, a block diagram of an electronic device 400 suitable for use in implementing embodiments of the present disclosure is shown. The terminal device in the embodiments of the present disclosure may include, but is not limited to, a mobile terminal such as a mobile phone, a notebook computer, a digital broadcast receiver, a PDA (personal digital assistant), a PAD (tablet computer), a PMP (portable multimedia player), a vehicle terminal (e.g., a car navigation terminal), and the like, and a stationary terminal such as a digital TV, a desktop computer, and the like. The electronic device shown in fig. 4 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present disclosure.

As shown in fig. 4, the electronic device 400 may include a processing means (e.g., a central processing unit, a graphics processor, etc.) 401 that may perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)402 or a program loaded from a storage means 408 into a Random Access Memory (RAM) 403. In the RAM 403, various programs and data necessary for the operation of the electronic apparatus 400 are also stored. The processing device 401, the ROM 402, and the RAM 403 are connected to each other via a bus 404. An input/output (I/O) interface 405 is also connected to bus 404.

Generally, the following devices may be connected to the I/O interface 405: input devices 406 including, for example, a touch screen, touch pad, keyboard, mouse, camera, microphone, accelerometer, gyroscope, etc.; an output device 407 including, for example, a Liquid Crystal Display (LCD), a speaker, a vibrator, and the like; storage 408 including, for example, tape, hard disk, etc.; and a communication device 409. The communication means 409 may allow the electronic device 400 to communicate wirelessly or by wire with other devices to exchange data. While fig. 4 illustrates an electronic device 400 having various means, it is to be understood that not all illustrated means are required to be implemented or provided. More or fewer devices may alternatively be implemented or provided.

In particular, according to an embodiment of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program carried on a non-transitory computer readable medium, the computer program containing program code for performing the method illustrated by the flow chart. In such embodiments, the computer program may be downloaded and installed from a network through the communication device 409, or from the storage device 408, or from the ROM 402. The computer program performs the above-described functions defined in the methods of the embodiments of the present disclosure when executed by the processing device 401.

It should be noted that the computer readable medium mentioned in the present disclosure may be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present disclosure, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In contrast, in the present disclosure, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, either in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: electrical wires, optical cables, RF (radio frequency), etc., or any suitable combination of the foregoing.

In some embodiments, the electronic devices may communicate using any currently known or future developed network Protocol, such as HTTP (HyperText Transfer Protocol), and may interconnect with any form or medium of digital data communication (e.g., a communications network). Examples of communication networks include a local area network ("LAN"), a wide area network ("WAN"), the Internet (e.g., the Internet), and peer-to-peer networks (e.g., ad hoc peer-to-peer networks), as well as any currently known or future developed network.

The computer readable medium may be embodied in the electronic device; or may exist separately without being assembled into the electronic device.

The computer readable medium carries one or more programs which, when executed by the electronic device, cause the electronic device to: extracting original sub-queries in the historical query statement; generating a candidate query set according to a preset rule according to the original sub-query; determining a target query from the candidate query set, wherein the target query is a query with a query profit greater than a preset profit threshold; creating a materialized view corresponding to the target query, wherein the materialized view is used for processing the corresponding query.

Computer program code for carrying out operations for the present disclosure may be written in any combination of one or more programming languages, including but not limited to an object oriented programming language such as Java, Smalltalk, C + +, and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may 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 case of a remote computer, the remote computer may 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 may be made to an external computer (for example, through the Internet using an Internet service provider).

The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or 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 block may 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 sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

The modules described in the embodiments of the present disclosure may be implemented by software or hardware. Where the name of a module does not in some cases constitute a limitation on the module itself, for example, an extraction module may also be described as a "module that extracts the original sub-queries in a historical query statement".

The functions described herein above may be performed, at least in part, by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field Programmable Gate Arrays (FPGAs), Application Specific Integrated Circuits (ASICs), Application Specific Standard Products (ASSPs), systems on a chip (SOCs), Complex Programmable Logic Devices (CPLDs), and the like.

In the context of this disclosure, a machine-readable medium may be a tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. The machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples of a machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer diskette, 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 compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

Example 1 provides, in accordance with one or more embodiments of the present disclosure, a method of creating a materialized view, comprising:

extracting original sub-queries in the historical query statement;

Example 2 provides the method of example 1, the original sub-query including a table field, and the generating a set of candidate queries according to a preset rule from the original sub-query, including:

dividing the original sub-queries with the same table fields into the same group according to the table fields of the original sub-queries to obtain a plurality of original sub-query sets;

and aiming at each original sub-query set, generating a candidate query set according to a preset rule according to the original sub-query in the original sub-query set.

Example 3 provides the method of example 2, wherein, for each of the original sub-query sets, generating a candidate query set according to a preset rule according to an original sub-query in the original sub-query set, the method includes:

for each of the original set of sub-queries, performing the steps of:

taking the original sub-query set as a current sub-query set;

generating a candidate query set according to a preset merging rule according to the query in the current sub-query set;

sorting the queries in the candidate query set according to a preset sorting rule; and the number of the first and second electrodes,

selecting a preset number of queries from the sorting results according to the sorting results to form a new candidate query set;

and under the condition that the preset iteration condition is determined not to be met, taking the new candidate query set as a new current sub-query set, returning to execute the step of generating the candidate query set according to the preset combination rule and the query in the current sub-query set.

Example 4 provides the method of example 3, wherein the queries in the candidate query set include a filter condition field, a dimension column field, an index column field, and a query number field, and the generating the candidate query set according to the queries in the current sub-query set according to a preset merge rule includes:

aiming at each two queries in the current sub-query set, merging the two queries according to the filtering condition field, the dimensionality column field, the index column field and the query frequency field of the two queries;

and generating a candidate query set according to the combined result and the query included in the current sub-query set.

Example 5 provides the method of example 1, the determining a target query from the set of candidate queries, comprising:

calculating index parameters of each query in the candidate query set;

and selecting the query of which the index parameter meets a preset parameter condition corresponding to the index parameter as a target query according to the index parameter of each query in the candidate query set.

Example 6 provides the method of any one of examples 1-5, the creating a materialized view corresponding to the target query, comprising:

setting a data period of a materialized view corresponding to the target query, wherein the materialized view includes data corresponding to the data period in a base table corresponding to a table field of the target query;

generating a data definition language according to the target query and the data time interval;

executing the data definition language to create a materialized view corresponding to the target query.

Example 7 provides the method of any one of examples 1-5, further comprising, in accordance with one or more embodiments of the present disclosure:

detecting whether the materialized view belongs to a failure view;

deleting the materialized view if it is detected that the materialized view belongs to the failed view.

Example 8 provides, in accordance with one or more embodiments of the present disclosure, an apparatus for creating a materialized view, comprising:

Example 9 provides a computer-readable medium, on which a computer program is stored, according to one or more embodiments of the present disclosure, characterized in that the program, when executed by a processing device, implements the steps of the method of any one of examples 1-7.

Example 10 provides, in accordance with one or more embodiments of the present disclosure, an electronic device characterized by comprising:

a storage device having a computer program stored thereon;

processing means for executing the computer program in the storage means to carry out the steps of the method of any of examples 1-7.

The foregoing description is only exemplary of the preferred embodiments of the disclosure and is illustrative of the principles of the technology employed. It will be appreciated by those skilled in the art that the scope of the disclosure herein is not limited to the particular combination of features described above, but also encompasses other embodiments in which any combination of the features described above or their equivalents does not depart from the spirit of the disclosure. For example, the above-mentioned features and the technical features (but not limited to) having similar functions disclosed in the present disclosure are mutually replaced to form the technical solution.

Further, while operations are depicted in a particular order, this should not be understood as requiring that such operations be performed in the particular order shown or in sequential order. Under certain circumstances, multitasking and parallel processing may be advantageous. Likewise, while several specific implementation details are included in the above discussion, these should not be construed as limitations on the scope of the disclosure. Certain features that are described in the context of separate embodiments can also be implemented in combination in a single embodiment. Conversely, various features that are described in the context of a single embodiment can also be implemented in multiple embodiments separately or in any suitable subcombination.

Although the subject matter has been described in language specific to structural features and/or methodological acts, it is to be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or acts described above. Rather, the specific features and acts described above are disclosed as example forms of implementing the claims. With regard to the apparatus in the above-described embodiment, the specific manner in which each module performs the operation has been described in detail in the embodiment related to the method, and will not be elaborated here.

Claims

1. A method of creating a materialized view, comprising:

extracting original sub-queries in the historical query statement;

2. The method of creating according to claim 1, wherein the original sub-query comprises table fields, and wherein generating the set of candidate queries according to a preset rule based on the original sub-query comprises:

3. The creating method according to claim 2, wherein for each original sub-query set, generating a candidate query set according to a preset rule based on original sub-queries in the original sub-query set comprises:

for each of the original set of sub-queries, performing the steps of:

taking the original sub-query set as a current sub-query set;

4. The creating method according to claim 3, wherein the queries in the candidate query set include a filter condition field, a dimension column field, an index column field, and a query frequency field, and the generating the candidate query set according to the queries in the current sub-query set and the preset merge rule includes:

aiming at each two queries in the current sub-query set, merging the two queries according to the filtering condition field, the dimension column field, the index column field and the query frequency field of the two queries;

5. The method of creating as claimed in claim 1, wherein said determining a target query from said set of candidate queries comprises:

calculating index parameters of each query in the candidate query set;

6. The method for creating according to any of claims 1-5, wherein said creating a materialized view corresponding to the target query comprises:

7. The creation method according to any one of claims 1 to 5, characterized in that the method further comprises:

detecting whether the materialized view belongs to a failure view;

8. An apparatus for creating a materialized view, comprising:

9. A computer-readable medium, on which a computer program is stored, characterized in that the program, when being executed by processing means, carries out the steps of the method of any one of claims 1 to 7.

10. An electronic device, comprising:

a storage device having a computer program stored thereon;

processing means for executing the computer program in the storage means to carry out the steps of the method according to any one of claims 1 to 7.