A data table switching method, device and equipment

Abstract

The application provides a data table switching method, device and equipment. The method comprises the following steps: obtaining an SQL feature corresponding to an initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure comprises a plurality of initial table creation strategies; determining an effect score corresponding to each initial table creation strategy based on the SQL feature; wherein the effect score is used to indicate whether the initial table creation strategy needs to be optimized; determining a switching score value based on the effect score corresponding to each initial table creation strategy; if it is determined that data table switching is needed based on the switching score value, determining a target data table structure based on the initial data table structure, wherein the target data table structure comprises a plurality of target table creation strategies corresponding to the plurality of initial table creation strategies; and generating a target data table of the target data table structure, and replacing the initial data table with the target data table. Through the technical solution of the application, the data writing speed can be improved, and the data query time can be reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data storage, in particular to a data table switching method, device and equipment. BACKGROUND

[0002] An analytic database is a real-time high-concurrency online analysis system for massive data, which performs multi-dimensional analysis and business exploration on massive data, adopts distributed computing, has strong real-time computing capability, can support a higher concurrent query volume, and ensures high system availability through multi-copy data storage technology.

[0003] A storage mode, also known as an internal mode, is a description of a data table structure (also referred to as a data physical structure) and a storage method, which can include but is not limited to a storage method (such as sequential storage, B-tree structure storage, hash storage, etc.), an index organization method, whether data is compressed, whether data is encrypted, etc.

[0004] A large number of data tables can be established in an analytic database to store data. When each data table is established, a user can select a storage mode for the data table according to a business scenario, and establish a data table matching the storage mode. However, if the user selects an unreasonable storage mode for the data table, the unreasonable storage mode will affect the data write speed and data query performance, that is, there are problems such as slow data write speed, long data query time, and poor data query effect. SUMMARY

[0005] The present application provides a data table switching method, which comprises:

[0006] Obtaining SQL features corresponding to an initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure comprises a plurality of initial table creation strategies;

[0007] Determining an effect score corresponding to each initial table creation strategy based on the SQL features; wherein the effect score is used to indicate whether the initial table creation strategy needs to be optimized;

[0008] Determining a switching score value based on the effect score corresponding to each initial table creation strategy;

[0009] If it is determined based on the switching score value that data table switching is needed, determining a target data table structure based on the initial data table structure, wherein the target data table structure comprises a plurality of target table creation strategies corresponding to the plurality of initial table creation strategies; generating a target data table of the target data table structure, and replacing the initial data table with the target data table.

[0010] The application provides a data table switching device, which comprises:

[0011] An acquisition module is configured to acquire SQL features corresponding to an initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure comprises a plurality of initial table creation strategies;

[0012] A determination module is configured to determine an effect score corresponding to each initial table creation strategy based on the SQL features; wherein the effect score is used to indicate whether the initial table creation strategy needs to be optimized;

[0013] A switching score value is determined based on the effect score corresponding to each initial table creation strategy.

[0014] A generation module is configured to determine a target data table structure based on the initial data table structure if it is determined that data table switching is needed based on the switching score value, wherein the target data table structure comprises a plurality of target table creation strategies corresponding to the plurality of initial table creation strategies; and generate a target data table of the target data table structure, and replace the initial data table with the target data table.

[0015] The application provides a data table switching device, which comprises a processor and a machine readable storage medium, wherein the machine readable storage medium stores machine executable instructions capable of being executed by the processor; and the processor is configured to execute the machine executable instructions to implement the data table switching method disclosed in the above examples of the application.

[0016] As can be seen from the above technical solutions, in the embodiments of the application, after an initial data table structure of an initial data table is established, if it is determined that data table switching of the initial data table is needed, a target data table of a target data table structure can be established, and the initial data table is replaced with the target data table, that is, after an initial data table structure is selected for a data table according to a business scenario, and an initial data table matching the initial data table structure is established, if the initial data table structure is unreasonable, the unreasonable initial data table structure can be adjusted, so that the adjusted target data table structure is reasonable, thereby improving the data writing speed, reducing the data query time, improving the data query effect, and improving the data query performance. BRIEF DESCRIPTION OF DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the application or the prior art, the drawings needed in the description of the embodiments of the application or the prior art will be briefly introduced. Obviously, the drawings in the following description are only some embodiments described in the application, and other drawings can also be obtained by those skilled in the art based on these drawings.

[0018] Figure 1is a flowchart of a data table switching method in an embodiment of the present application;

[0019] Figure 2 is a schematic diagram of a data table switching method in an embodiment of the present application;

[0020] Figure 3 is a schematic diagram of an SQL feature storage procedure in an embodiment of the present application;

[0021] Figure 4 is a schematic diagram of a data table switching procedure in an embodiment of the present application;

[0022] Figure 5 is a schematic diagram of a data writing procedure in an embodiment of the present application;

[0023] Figure 6 is a structural schematic diagram of a data table switching device in an embodiment of the present application;

[0024] Figure 7 is a hardware structural diagram of a data table switching device in an embodiment of the present application. DETAILED DESCRIPTION

[0025] The terminology used in the embodiments of the present application is merely for the purpose of describing particular embodiments and is not intended to be limiting of the present application. As used in the present application and the claims, the singular forms "a", "an" and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It will be further understood that the terms "comprises" and / or "comprising," when used in this specification, specify the presence of stated features, integers, steps, operations, elements, and / or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and / or groups thereof.

[0026] It should be understood that, although the terms first, second, third, etc. can be used herein to describe various information, these terms are not intended to denote a particular order. These terms are used only to distinguish one type of information from another. For example, without departing from the scope of the present application, first information can be referred to as second information, and similarly, second information can be referred to as first information. Depending on the context, the word "if' can be interpreted to mean "when" or "upon" or "in response to determining."

[0027] An embodiment of the present application proposes a data table switching method, referring to Figure 1 As shown, the method can include:

[0028] In step 101, an SQL feature corresponding to an initial data table is acquired; wherein the initial data table adopts an initial data table structure, and the initial data table structure can include a plurality of initial table creation strategies.

[0029] Exemplarily, the SQL feature corresponding to the initial data table can be acquired from the feature database when a switching evaluation condition of the initial data table is met; a process of maintaining the feature database can include but is not limited to: determining a data table corresponding to a SQL statement each time the SQL statement is received; acquiring a SQL feature corresponding to the data table from the SQL statement; and recording the SQL feature in a data field corresponding to the data table in the feature database. As described above, the SQL feature corresponding to each data table can be maintained in the feature database, and thus the SQL feature corresponding to the initial data table can be acquired from the feature database.

[0030] Exemplarily, the SQL feature corresponding to the data table can be acquired from the SQL statement, which can include but is not limited to: acquiring an operation type and operation data for the data table from the SQL statement; wherein: if the operation type is a join type, the operation data can include but is not limited to at least one of the following: a column field in the data table; whether the column field has a function operation, the function operation indicating that the column field needs to be converted by a function; and a distribution mode of the data table, the distribution mode being a hash distribution mode or a broadcast distribution mode; if the operation type is a column pruning type, a filtering type, an aggregation type, or a sorting type, the operation data can include but is not limited to at least one of the following: a column field in the data table; and whether the column field has a function operation.

[0031] Exemplarily, the switching evaluation condition of the initial data table can be met, which can include but is not limited to: if an establishment duration corresponding to the initial data table is greater than a preset duration threshold, and / or a number of SQL features corresponding to the initial data table is greater than a preset number threshold, it is determined that the switching evaluation condition of the initial data table is met.

[0032] Step 102, determining an effect score corresponding to each initial table creation strategy based on the SQL feature; wherein the effect score is used to indicate whether the initial table creation strategy needs to be optimized.

[0033] Exemplarily, for each initial table creation strategy, a target feature corresponding to the initial table creation strategy is determined based on the SQL feature, and whether the initial table creation strategy needs to be optimized is determined based on the target feature; if yes, it is determined that the effect score corresponding to the initial table creation strategy is a first score value; if no, it is determined that the effect score corresponding to the initial table creation strategy is a second score value; wherein the first score value is greater than the second score value.

[0034] Exemplarily, the target feature corresponding to the initial table creation strategy is determined based on the SQL feature, and whether the initial table creation strategy needs to be optimized is determined based on the target feature, which can include but is not limited to:

[0035] If the initial table building strategy is the derived field strategy, for each column field in the initial data table, the number of times that the column field exists in a function operation and the total number of times that the column field exists are counted based on the SQL characteristics, and the ratio of the number of times to the total number of times is determined; if the ratio corresponding to a column field is greater than a preset ratio threshold, it is determined that the derived field strategy needs to be optimized; otherwise, it is determined that the derived field strategy does not need to be optimized.

[0036] If the initial table building strategy is the in-memory table strategy, the first number of times that the operation type is a connection type and the second number of times that the distribution mode of the initial data table is a broadcast distribution mode are counted based on the SQL characteristics; if the first number of times is greater than a first number of times threshold and the second number of times is greater than a second number of times threshold, it can be determined that the in-memory table strategy needs to be optimized; otherwise, it can be determined that the in-memory table strategy does not need to be optimized.

[0037] If the initial table building strategy is the storage format strategy, the number of column fields of which the operation type is a column pruning type is counted based on the SQL characteristics; the target storage format is determined based on the ratio of the number of column fields to the total number of column fields of the initial data table; if the target storage format is different from the initial storage format of the initial data table, it can be determined that the storage format strategy needs to be optimized; otherwise, it can be determined that the storage format strategy does not need to be optimized; wherein, if the ratio is less than a first ratio threshold, the target storage format can be column storage; if the ratio is greater than a second ratio threshold, the target storage format can be row storage.

[0038] If the initial table building strategy is the sorting key strategy, the total number of times that each column field of which the operation type is a sorting type exists is counted based on the SQL characteristics; if the total number of times of a column field is greater than a preset number of times threshold, it is determined that the sorting key strategy needs to be optimized; otherwise, it is determined that the sorting key strategy does not need to be optimized.

[0039] If the initial table building strategy is the bucketing key strategy, the total number of times that each column field of which the operation type is a connection type or an aggregation type exists is counted based on the SQL characteristics; if the total number of times of a column field is greater than a preset number of times threshold, the column field is determined as a new bucketing key, and it is determined that the bucketing key strategy needs to be optimized; otherwise, it can be determined that the bucketing key strategy does not need to be optimized.

[0040] Step 103, determining a switching score value based on the effect score corresponding to each initial table building strategy.

[0041] For example, the effect score corresponding to each initial table building strategy and the weight value corresponding to the initial table building strategy can be weighted to obtain the switching score value corresponding to the initial data table.

[0042] In step 104, if it is determined that the data table switching is needed based on the switching score value, a target data table structure is determined based on the initial data table structure, the target data table structure including a plurality of target table building strategies corresponding to a plurality of initial table building strategies; a target data table of the target data table structure is generated, and the initial data table is replaced by the target data table. For example, the data table structure of the target data table can be different from the data table structure of the initial data table, and the data content of the target data table can be the same as the data content of the initial data table.

[0043] For example, determining that the data table switching is needed based on the switching score value can include, but is not limited to, determining a switching generation value based on the data amount size corresponding to the initial data table and the time consumption of a unit data amount; if the remaining storage space of the disk is greater than the data amount size corresponding to the initial data table, and the ratio of the switching score value to the switching generation value is greater than a ratio threshold, it is determined that the data table switching is needed.

[0044] For example, determining the target data table structure based on the initial data table structure can include, but is not limited to, for each initial table building strategy in the initial data table structure, if the initial table building strategy needs to be optimized, the initial table building strategy is optimized based on the target feature corresponding to the initial table building strategy to obtain an optimized target table building strategy; if the initial table building strategy does not need to be optimized, the initial table building strategy is determined as a target table building strategy; on this basis, the target data table structure can be determined based on a plurality of target table building strategies corresponding to a plurality of initial table building strategies, that is, the target data table structure includes a plurality of target table building strategies.

[0045] For example, generating the target data table of the target data table structure can include, but is not limited to, allowing writing data for the initial data table into the cache, prohibiting writing data in the cache to the initial data table, and on this basis, generating the target data table of the target data table structure based on the initial data table. Or, prohibiting writing data for the initial data table into the cache, prohibiting writing data in the cache to the initial data table, and on this basis, generating the target data table of the target data table structure based on the initial data table.

[0046] Exemplarily, replacing the initial data table by the target data table can include, but is not limited to: obtaining a plurality of SQL statements corresponding to the initial data table; executing the plurality of SQL statements based on the initial data table to obtain a first query time length (i.e., a sum of query time lengths of all the SQL statements or an average of the query time lengths of all the SQL statements); executing the plurality of SQL statements based on the target data table to obtain a second query time length (i.e., a sum of query time lengths of all the SQL statements or an average of the query time lengths of all the SQL statements). On this basis, if the second query time length is less than the first query time length, the initial data table can be replaced by the target data table, and the initial data table can be deleted from the database. Alternatively, if the second query time length is greater than the first query time length, the initial data table can be retained, and the target data table can be deleted from the database.

[0047] As can be seen from the above technical solutions, in the embodiments of the present application, after the initial data table with the initial data table structure is established, if it is determined that data table switching needs to be performed on the initial data table, the target data table with the target data table structure can be established, and the initial data table can be replaced by the target data table. That is to say, after the initial data table structure is selected for the data table according to the business scenario, and the initial data table matching the initial data table structure is established, if the initial data table structure is unreasonable, the unreasonable initial data table structure can be adjusted, so that the adjusted target data table structure is reasonable, thereby the data write speed can be improved, the data query time can be reduced, the data query effect can be improved, and the data query performance can be improved.

[0048] The technical solutions of the embodiments of the present application will be described below in combination with specific application scenarios.

[0049] When the analytical database establishes a data table, a user can select a storage mode for the data table according to a business scenario, and establish a data table matching the storage mode. However, if the storage mode selected by the user for the data table is unreasonable, the unreasonable storage mode will affect the data write speed and the data query performance, and there are problems such as slow data write speed, long data query time, and poor data query effect.

[0050] Exemplarily, the storage mode is a description of the data table structure and the storage mode, which can include, but is not limited to, the data table structure, the storage mode (such as sequential storage, B-tree structure storage, hash storage, etc.), the index organization mode, whether the data is compressed, whether the data is encrypted, the data storage record structure, etc. The data table structure includes a plurality of table creation strategies, such as a derived field strategy, an in-memory table strategy, a storage format strategy, a sorting key strategy, and a bucket key (distribution key) strategy. Of course, the above are only a few examples, and the table creation strategies are not limited thereto.

[0051] The derived field strategy refers to: a new field B is calculated according to the original field A, that is, B = Fun(A), when Fun(A) is queried, the value of the B field is directly obtained. The memory table strategy refers to: the data table set as a memory table, the data of which will reside in memory and does not need to read the disk. The storage format strategy refers to: the storage format is divided into row storage and column storage, row storage means that all columns of a row record are stored continuously, and column storage means that each column of data is stored separately. The sorting key strategy refers to: when data is written, the data is sorted and stored according to the sorting key. The bucket key strategy refers to: according to the bucket key field value, the row data is stored in the corresponding data bucket.

[0052] In view of the above finding, in the embodiments of the present application, the initial data table of the initial data table structure can be adjusted to obtain the target data table of the target data table structure, so that the unreasonable initial data table structure can be adjusted, so that the adjusted target data table structure is reasonable, thereby improving the data writing speed, reducing the data query time, improving the data query effect, and improving the data query performance.

[0053] Referring to Figure 2 As shown in FIG. 1, it is a schematic diagram of a data table switching method, which can include a SQL feature storage process and a data table switching process based on SQL features. In the SQL feature storage process, SQL features can be obtained from the SQL statement each time the SQL statement is received, and the SQL features can be stored in the feature database. In the data table switching process based on SQL features, the SQL features can be obtained from the feature database, and it is determined whether to switch the data table based on the SQL features.

[0054] The SQL feature storage process and the data table switching process based on SQL features are described below.

[0055] Referring to Figure 3 As shown in FIG. 2, it is a schematic diagram of a SQL feature storage process, which can include:

[0056] Step 301, receiving the SQL statement sent by the client, hereinafter referred to as SQL statement A.

[0057] Step 302, optimizing the SQL statement A to obtain the optimized SQL statement B.

[0058] Step 303, executing the SQL statement B, that is, operating the data table based on the SQL statement B.

[0059] Step 304, extracting the SQL features based on the SQL statement B, that is, extracting the SQL features from the SQL statement B and storing the SQL features in the feature database.

[0060] Exemplarily, steps 301-304 can be performed for each received SQL statement, or steps 301-303 can be performed for each received SQL statement, and step 304 can be performed for part of the received SQL statement, without limitation. Steps 301-303 are the execution process of the SQL statement, which is the normal SQL query process. Step 304 is a new step added by the present application, which is used to extract the SQL feature. The process of extracting the SQL feature can be a background process, which does not block the operation of SQL optimization (step 302) and SQL execution (step 303). That is, step 304 and step 303 have no sequence relationship. Step 304 can also be executed at the same time as step 303 in the process of executing step 303.

[0061] Exemplarily, in the process of optimizing the SQL statement A to obtain the SQL statement B, the optimization method of the SQL statement can include but is not limited to: unifying the format of the SQL statement; avoiding full table scanning as much as possible; simplifying the SQL statement; considering using a temporary table to temporarily store intermediate results; avoiding large transaction operations as much as possible; and avoiding returning large amounts of data to the client. Of course, the above are only a few examples of optimization methods, without limitation.

[0062] Exemplarily, for step 304, when the SQL feature is extracted from the SQL statement B, the data table corresponding to the SQL statement B can be determined, and the operation type and operation data for the data table can be obtained from the SQL statement B. The operation type and operation data are the SQL features for the data table.

[0063] For example, if the SQL statement B is used to implement a join operation, the operation type can be a join type, and the operation data can include but is not limited to at least one of the following: a column field in the data table; whether the column field has a function operation, the function operation being used to indicate that the column field needs to be converted by a function; and a distribution manner of the data table, which can be a hash distribution manner or a broadcast distribution manner. It should be noted that the distribution manner of the data table in the present embodiment refers to the distribution manner of the data table in the join operation.

[0064] If the join operation is used to perform a join operation on the column field T11 in the data table T1 and the column field T21 in the data table T2, the data tables corresponding to the SQL statement B are the data table T1 and the data table T2.

[0065] The operation type of the data table T1 is a join type, and the operation data includes a column field T11, whether the column field T11 exists a function operation, and a distribution manner of the data table T1 in the join operation. When the join operation is performed, if a new field needs to be calculated based on the column field T11, and the join operation is performed based on the new field, the column field T11 exists a function operation (such as a conversion of a field type). If the new field does not need to be calculated based on the column field T11, and the join operation is directly performed based on the column field T11, the column field T11 does not exist a function operation. When the join operation is performed on the data table T1, if the data corresponding to the data table T1 is transmitted in a broadcast manner, the distribution manner of the data table T1 in the join operation is a broadcast distribution manner. If the data corresponding to the data table T1 is transmitted in a hash manner, the distribution manner of the data table T1 in the join operation is a hash distribution manner.

[0066] The operation type of the data table T2 is a join type, and the operation data includes a column field T21, whether the column field T21 exists a function operation, and a distribution manner of the data table T2 in the join operation.

[0067] For example, if the SQL statement B is used to implement a select operation, the operation type is a column pruning type, and the operation data includes at least one of the following: a column field in a data table; and whether the column field exists a function operation, which is used to indicate that a function conversion needs to be performed on the column field. If the select operation is used to perform a select operation on the column field T11 in the data table T1, the data table corresponding to the SQL statement B is the data table T1, the operation type of the data table T1 is the column pruning type, and the operation data includes the column field T11 and whether the column field T11 exists a function operation such as a cast, a not null, a case when, or the like.

[0068] For example, if the SQL statement B is used to implement a where operation, the operation type is a filtering type, and the operation data includes at least one of the following: a column field in a data table; and whether the column field exists a function operation, which is used to indicate that a function conversion needs to be performed on the column field. For example, if the where operation is used to perform a where operation (such as =, between, in, like, or the like) on the column field T11 in the data table T1, the data table corresponding to the SQL statement B is the data table T1, the operation type of the data table T1 is the filtering type, and the operation data includes the column field T11 and whether the column field T11 exists a function operation (such as a conversion of a field type).

[0069] For example, if the SQL statement B is used to implement a group operation, the operation type is an aggregation type, and the operation data includes, but is not limited to, at least one of the following: a column field in the data table; and whether the column field has a function operation. For example, if the group operation is used to perform a group operation on the column field T11 in the data table T1, the data table corresponding to the SQL statement B is the data table T1, the operation type of the data table T1 is the aggregation type, and the operation data includes the column field T11 and whether the column field T11 has a function operation.

[0070] For example, if the SQL statement B is used to implement a sort operation, the operation type can be a sort type, and the operation data can include, but is not limited to, at least one of the following: a column field in the data table; and whether the column field has a function operation. For example, if the sort type is used to perform a sort operation on the column field T11 in the data table T1, the data table corresponding to the SQL statement B is the data table T1, the operation type of the data table T1 is the sort type, and the operation data can include the column field T11 and whether the column field T11 has a function operation.

[0071] In a possible implementation, a feature database can be maintained, the feature database being used to record the SQL features corresponding to each data table. Each table entry of the data table can be referred to as a data field, and a data field is used to record a SQL feature. Table 1 shows an example of the feature database.

[0072] Table 1

[0073]

[0074] Of course, Table 1 is only an example, and no limitation is made thereto. After a SQL feature is extracted from a SQL statement each time, the SQL feature can be recorded in the feature database shown in Table 1. For example, if the SQL feature corresponding to the data table T1 is obtained, a table entry (i.e., a data field) corresponding to the data table T1 can be added, and the SQL feature corresponding to the data table T1 can be recorded in the data field.

[0075] As described above, the SQL feature storage process can be completed, and thus the SQL features corresponding to each data table can be stored in the feature database. That is, the feature database can include the SQL features corresponding to each data table.

[0076] Referring to Figure 4 Fig. 1 shows a schematic diagram of a data table switching process, which can include the following steps.

[0077] Step 401, for each data table (i.e. initial data table), determine whether the switching evaluation condition of the data table is met. If yes, execute step 402, if not, wait for a preset time length, and then re-determine whether the switching evaluation condition of the data table is met, and so on until the switching evaluation condition of the data table is met.

[0078] For example, the data table switching process can be triggered at a fixed time, that is, it can be periodically determined whether the switching evaluation condition of the data table is met. For example, for data table T1, it is determined whether the switching evaluation condition of data table T1 is met at time A1, if not, it is re-determined whether the switching evaluation condition of data table T1 is met after a preset time interval, and so on until the switching evaluation condition of data table T1 is met, and step 402 is executed. For data table T2, it is determined whether the switching evaluation condition of data table T2 is met at time A2 (time A2 can be different from or the same as time A1), and so on.

[0079] For example, when the data volume of the SQL feature corresponding to the data table meets a certain condition, it is considered that the switching evaluation condition of the data table is met. For example, when there are enough time SQL features as reference data of the data table, and / or there are enough times of SQL features as reference data of the data table, it is considered that the switching evaluation condition of the data table is met, otherwise, it is determined that the switching evaluation condition of the data table is not met.

[0080] For example, for each data table, taking data table T1 as an example, if the establishment time length corresponding to data table T1 is greater than a preset time length threshold, it means that there are enough time SQL features as reference data of data table T1, therefore, it is determined that the switching evaluation condition of data table T1 is met, if the establishment time length corresponding to data table T1 is not greater than the preset time length threshold, it is determined that the switching evaluation condition of data table T1 is not met.

[0081] For another example, if the number of SQL features corresponding to data table T1 is greater than a preset number threshold, it means that there are enough times of SQL features as reference data of data table T1, therefore, it is determined that the switching evaluation condition of data table T1 is met, if the number of SQL features corresponding to data table T1 is not greater than the preset number threshold, it is determined that the switching evaluation condition of data table T1 is not met. Wherein, referring to Table 1, the feature database includes the SQL features corresponding to data table T1, that is, each record corresponding to data table T1 is a SQL feature, therefore, the number of SQL features corresponding to data table T1 can be counted.

[0082] For example, if the establishment duration corresponding to the data table T1 is greater than the preset duration threshold, and the number of SQL features corresponding to the data table T1 is greater than the preset number threshold, it is determined that the switching evaluation condition of the data table T1 is met. If the establishment duration corresponding to the data table T1 is not greater than the preset duration threshold, and / or the number of SQL features corresponding to the data table T1 is not greater than the preset number threshold, it is determined that the switching evaluation condition of the data table T1 is not met.

[0083] In the above embodiment, the preset duration threshold and the preset number threshold can be configured according to experience.

[0084] Step 402, for each data table, when the switching evaluation condition of the data table is met, the SQL feature corresponding to the data table is obtained from the feature database. For example, taking the data table T1 as an example, referring to Table 1, the feature database includes the SQL feature corresponding to the data table T1, so when the switching evaluation condition of the data table T1 is met, the SQL feature corresponding to the data table T1 can be obtained from the feature database.

[0085] In a possible implementation, for each data table, the data table structure of the data table can be referred to as an initial data table structure, the initial data table structure can include a plurality of table creation strategies, and each table creation strategy in the initial data table structure can be referred to as an initial table creation strategy. Therefore, the initial data table structure can include a plurality of initial table creation strategies. For example, the initial data table structure can include at least one of the following initial table creation strategies: a derived field strategy, a memory table strategy, a storage format strategy, a sorting key strategy, and a bucket key strategy. Of course, the above are only a few examples, and the initial table creation strategy is not limited thereto.

[0086] For example, when the data table is established, the table creation strategy of the data table needs to meet the initial table creation strategies such as the derived field strategy, the memory table strategy, the storage format strategy, the sorting key strategy, and the bucket key strategy.

[0087] Step 403, for each initial table creation strategy in the initial data table structure of the data table (hereinafter referred to as the data table T1), a target feature corresponding to the initial table creation strategy is determined based on the SQL feature corresponding to the data table T1, and whether the initial table creation strategy needs to be optimized is determined based on the target feature.

[0088] In a possible implementation, taking the initial data table structure including the derived field strategy, the memory table strategy, the storage format strategy, the sorting key strategy, and the bucket key strategy as an example, for each initial table creation strategy, whether the initial table creation strategy needs to be optimized can be determined in step 403 as follows.

[0089] Case 1, if the initial table building strategy is the derived field strategy, for each column field in the data table T1, based on the SQL features, the number of times that the column field exists function operation and the total number of times that the column field appears are counted, the ratio between the number of times and the total number of times is determined, if the ratio corresponding to a column field is greater than a preset ratio threshold, it is determined that the derived field strategy needs to be optimized, otherwise, it is determined that the derived field strategy does not need to be optimized.

[0090] For example, if the initial table building strategy is the derived field strategy, it needs to be considered whether a derived field needs to be added, for example, by traversing the function operation features of each column field in the data table T1, it is found whether there is a column field with a high application function ratio (above a threshold), if so, it is determined that a derived field needs to be added, that is, a function derived field (col->fun(col)) is added.

[0091] For example, referring to Table 1, the feature database includes SQL features corresponding to the data table T1, each column field in the data table T1 can be traversed, such as column field T11, column field T12, and the like, taking column field T11 as an example. All SQL features corresponding to the data table T1 can be traversed in turn, for each SQL feature traversed, if the column field T11 appears in the SQL feature, the total number of times that the column field T11 appears is incremented by 1, if the column field T11 does not appear in the SQL feature, the total number of times that the column field T11 appears is not incremented by 1, after traversing all SQL features corresponding to the data table T1, the total number of times that the column field T11 appears can be determined.

[0092] In addition, all SQL features corresponding to the data table T1 can be traversed in turn, for each SQL feature traversed, if the column field T11 appears in the SQL feature and the column field T11 exists function operation, the number of times that the column field T11 exists function operation can be incremented by 1. If the column field T11 does not appear in the SQL feature, or although the column field T11 appears in the SQL feature, the column field T11 does not exist function operation, the number of times that the column field T11 exists function operation can not be incremented by 1, in this way, after traversing all SQL features corresponding to the data table T1, the number of times that the column field T11 exists function operation can be determined.

[0093] Then, the ratio between the number of times that the column field T11 exists function operation and the total number of times that the column field T11 appears can be determined, if the ratio is greater than a preset ratio threshold (the preset ratio threshold can be configured according to experience, such as 50%, 60%, and the like), it indicates that a derived field of function operation needs to be added for the column field T11, in this case, it is determined that the derived field strategy needs to be optimized. If the ratio is not greater than the preset ratio threshold, it indicates that a derived field of function operation does not need to be added for the column field T11.

[0094] After the above processing is performed on each column field in the data table T1, the proportion corresponding to each column field in the data table T1 can be obtained. If the proportion corresponding to a column field (i.e., at least one column field) is greater than the preset proportion threshold, it is determined that the derivation field strategy needs to be optimized. If the proportions corresponding to all column fields are not greater than the preset proportion threshold, it is determined that the derivation field strategy does not need to be optimized.

[0095] Case 2, if the initial table building strategy is the in-memory table strategy, the first number of operation types that are the connection type based on the SQL feature statistics, the second number of distribution manners of the data table T1 that are the broadcast distribution manner; if the first number is greater than the first number threshold, and the second number is greater than the second number threshold, it can be determined that the in-memory table strategy needs to be optimized; otherwise, it can be determined that the in-memory table strategy does not need to be optimized.

[0096] For example, if the initial table building strategy is the in-memory table strategy, it needs to be considered whether to increase the in-memory table that is broadcast, that is, whether a certain data table is broadcasted and distributed as an in-memory table. For example, if the data amount of a certain data table is small (such as not greater than 5% of the single-node memory), and the data table is often used for join operations, and the data table often belongs to the broadcast distribution manner, the data table can be distributed to each node as an in-memory table, that is, the data table is broadcasted and distributed as an in-memory table.

[0097] For example, the feature database includes the SQL features corresponding to the data table T1, and all the SQL features corresponding to the data table T1 can be traversed in sequence. For each SQL feature traversed, if the operation type in the SQL feature is the connection type, the first number of operation types that are the connection type is increased by 1, and if the operation type is not the connection type, the first number of operation types that are the connection type is not increased by 1. After all the SQL features corresponding to the data table T1 are traversed, the first number of operation types that are the connection type can be determined.

[0098] Based on all the SQL features whose operation types are the connection type, each SQL feature can be traversed in sequence, if the distribution manner of the data table T1 in the SQL feature is the broadcast distribution manner, the second number of distribution manners of the data table T1 that are the broadcast distribution manner is increased by 1, otherwise, the second number of distribution manners of the data table T1 that are the broadcast distribution manner is not increased by 1. After all the SQL features whose operation types are the connection type are traversed, the second number of distribution manners of the data table T1 that are the broadcast distribution manner can be determined.

[0099] On this basis, if the first number of times is greater than a first number of times threshold (which can be configured according to experience) and the second number of times is greater than a second number of times threshold (which can be configured according to experience), it is determined that the memory table strategy needs to be optimized, that is, the data table T1 needs to be broadcast and distributed as a memory table. Otherwise, if the first number of times is not greater than the first number of times threshold, and / or the second number of times is not greater than the second number of times threshold, it is determined that the memory table strategy does not need to be optimized, that is, the data table T1 does not need to be broadcast and distributed as a memory table.

[0100] Case 3, if the initial table building strategy is the storage format strategy, the number of column fields of the column pruning type is counted based on the SQL feature statistics operation type; the target storage format is determined based on the proportion of the number of column fields to the total number of column fields of the data table T1; if the target storage format is different from the initial storage format of the data table T1, it can be determined that the storage format strategy needs to be optimized; otherwise, it can be determined that the storage format strategy does not need to be optimized; wherein, if the proportion is less than a first proportion threshold, the target storage format can be column storage; if the proportion is greater than a second proportion threshold, the target storage format can be row storage.

[0101] For example, if the initial table building strategy is the storage format strategy, it is necessary to consider whether to optimize the storage format of the initial data table structure, for example, to optimize the row storage storage format to the column storage storage format, or to optimize the column storage storage format to the row storage storage format. For example, if the number of column fields participating in the query (except for column fields with very low query frequency) is less than a certain threshold compared to the total number of fields of the data table, column storage is considered; when it is greater than a certain threshold, row storage is considered.

[0102] For example, the feature database includes the SQL features corresponding to the data table T1, and all the SQL features corresponding to the data table T1 can be traversed in turn. For each SQL feature traversed, if the operation type in the SQL feature is the column pruning type, the column fields in the SQL feature, such as column field T11, are extracted, and if the operation type in the SQL feature is not the column pruning type, the column fields in the SQL feature do not need to be extracted. After traversing all the SQL features corresponding to the data table T1, all the column fields of the column pruning type (there can be duplicate column fields) can be obtained, and after removing the duplicate column fields, the number of remaining non-duplicate column fields, i.e., the number of column fields of the column pruning type, is counted.

[0103] Then, a ratio between the column field quantity and a total quantity of column fields of the data table T1 is calculated. If the ratio is less than a first ratio threshold (which can be configured according to experience), it is determined that the target storage format corresponding to the data table T1 is column storage. If the ratio is greater than a second ratio threshold (which can be configured according to experience), it is determined that the target storage format corresponding to the data table T1 is row storage. The second ratio threshold can be the same as the first ratio threshold, or the second ratio threshold can be greater than the first ratio threshold, which is not limited.

[0104] The initial storage format corresponding to the data table T1 can also be determined. For example, the initial data table structure of the data table T1 includes a storage format policy, which is used to indicate the initial storage format corresponding to the data table T1. The initial storage format can be column storage or row storage, which is not limited.

[0105] If the target storage format is different from the initial storage format, it is determined that the storage format policy needs to be optimized. For example, if the initial storage format is column storage and the target storage format is row storage, the storage format policy needs to be optimized to row storage. For example, if the initial storage format is row storage and the target storage format is column storage, the storage format policy needs to be optimized to column storage. Alternatively, if the target storage format is the same as the initial storage format, it is determined that the storage format policy does not need to be optimized. For example, if the initial storage format is column storage and the target storage format is column storage, the storage format policy does not need to be optimized. If the initial storage format is row storage and the target storage format is row storage, the storage format policy does not need to be optimized.

[0106] Case 4, if the initial table building strategy is the sorting key strategy, the total number of occurrences of each column field of the sorting type is counted based on the SQL feature statistics operation type. If the total number of occurrences of a column field is greater than a preset number threshold, it is determined that the sorting key strategy needs to be optimized. Otherwise, it is determined that the sorting key strategy does not need to be optimized.

[0107] For example, if the initial table building strategy is the sorting key strategy, it needs to be considered whether to select a sorting field to sort the data table or modify the sorting field of the data table, that is, whether to use a column field as the sorting field of the data table. For example, if one or more column fields are often used for sorting (occupying a proportion greater than a certain threshold), it is considered to sort the data table by using the column field as the sorting key.

[0108] For example, the feature database includes SQL features corresponding to the data table T1, and all SQL features corresponding to the data table T1 can be traversed in sequence. For each SQL feature traversed, if the operation type in the SQL feature is the sorting type, the column field in the SQL feature, such as the column field T11, is extracted, and if the operation type in the SQL feature is not the sorting type, the column field in the SQL feature does not need to be extracted. After traversing all SQL features corresponding to the data table T1, all column fields of the sorting type (there can be duplicate column fields) can be obtained, and the total number of occurrences of each column field of the sorting type is counted. For example, based on all column fields of the sorting type, if the column field T11 appears 10 times in total in the column fields, the total number of occurrences of the column field T11 can be 10.

[0109] For all column fields of the sorting type, if the total number of occurrences of a column field (that is, at least one column field) is greater than a preset number threshold (the preset number threshold can be configured according to experience), it can be determined that the sorting key strategy needs to be optimized, that is, the column field with the total number of occurrences greater than the preset number threshold can be used as the sorting key to sort the data table, otherwise, if the total number of occurrences of all column fields is not greater than the preset number threshold, it can be determined that the sorting key strategy does not need to be optimized.

[0110] Case 5, if the initial table building strategy is the bucket key strategy, the total number of occurrences of each column field of the connection type or the aggregation type is counted based on the SQL feature; if the total number of occurrences of a column field is greater than a preset number threshold, the column field can be determined as a new bucket key, and it can be determined that the bucket key strategy needs to be optimized; otherwise, it can be determined that the bucket key strategy does not need to be optimized.

[0111] For example, if the initial table building strategy is the bucket key strategy, it needs to be considered whether to perform bucketing on the data table or modify the bucketing manner of the data table, that is, whether to use a column field as the bucket key of the data table to perform bucketing on the data table based on the bucket key. For example, if one or more column fields participate in the join operation and the group operation with a high proportion, the column field is considered as the bucket key.

[0112] For example, the feature database includes SQL features corresponding to the data table T1, and all the SQL features corresponding to the data table T1 can be traversed in sequence. For each SQL feature traversed, if the operation type in the SQL feature is a connection type or an aggregation type, the column field in the SQL feature, such as the column field T11, is extracted, and if the operation type in the SQL feature is neither the connection type nor the aggregation type, the column field in the SQL feature does not need to be extracted. After all the SQL features corresponding to the data table T1 are traversed, all the column fields of the connection type or the aggregation type (there can be repeated column fields among the column fields) can be obtained, and the total number of occurrences of each column field of the connection type or the aggregation type is counted. For example, based on all the column fields of the connection type or the aggregation type, if the column field T11 appears 10 times in total among the column fields, the total number of occurrences of the column field T11 can be 10.

[0113] For all the column fields of the connection type or the aggregation type, if the total number of occurrences of a column field (that is, at least one column field) is greater than a preset number threshold (the preset number threshold can be configured according to experience), the column field can be determined as a new bucket key, and it is determined that the bucket key strategy needs to be optimized, that is, the column field with the total number of occurrences greater than the preset number threshold can be used as the new bucket key, so that the data table is bucketed based on the new bucket key. Otherwise, if the total number of occurrences of all the column fields is not greater than the preset number threshold, it is determined that the bucket key strategy does not need to be optimized.

[0114] Step 404, for each initial table creation strategy in the initial data table structure of the data table T1, an effect score corresponding to the initial table creation strategy is determined, and the effect score is used to indicate whether the initial table creation strategy needs to be optimized. For example, if the initial table creation strategy needs to be optimized, the effect score corresponding to the initial table creation strategy is determined as a first score value, and the first score value indicates that the initial table creation strategy needs to be optimized. If the initial table creation strategy does not need to be optimized, the effect score corresponding to the initial table creation strategy is determined as a second score value, and the second score value indicates that the initial table creation strategy does not need to be optimized.

[0115] For example, the first score value can be greater than the second score value, and the first score value and the second score value are not limited in this regard as long as the first score value is greater than the second score value. For example, the second score value can be 0, and the first score value can be any score value greater than 0 and less than or equal to 100.

[0116] In a possible implementation, the second score value can be 0, and the first score value can be a fixed score value, which can be configured according to experience, such as 100, 80, 60, 40, etc., and is not limited in this regard.

[0117] In another possible implementation, the second score value can be 0, and the first score value can dynamically change according to the target feature corresponding to the initial table building strategy, that is, the first score value is a dynamically changing score value, as long as the first score value is greater than 0. For example, the first score value corresponding to the initial table building strategy can be determined in the following manner.

[0118] For example, if the initial table building strategy is the derived field strategy, the first score value corresponding to the derived field strategy can be determined based on the target feature corresponding to the derived field strategy. Referring to case 1 described above, when the ratio between the number of times of function operations on the column field T11 and the total number of occurrences of the column field T11 is greater than the preset ratio threshold, it indicates that the derived field strategy needs to be optimized, and the first score value corresponding to the derived field strategy can be determined based on the ratio. For example, the greater the ratio, the greater the first score value, and the smaller the ratio, the smaller the first score value. For example, when the ratio is greater than 60% and less than or equal to 70%, the first score value is 40, when the ratio is greater than 70% and less than or equal to 80%, the first score value is 60, when the ratio is greater than 80% and less than or equal to 90%, the first score value is 80, and when the ratio is greater than 90%, the first score value is 100.

[0119] For example, if the initial table building strategy is the memory table strategy, the first score value corresponding to the memory table strategy can be determined based on the target feature corresponding to the memory table strategy. Referring to case 2 described above, when the first number of operation types being the join type is greater than the first number threshold, and the second number of the distribution mode of the data table T1 being the broadcast distribution mode is greater than the second number threshold, it indicates that the memory table strategy needs to be optimized, and the first score value corresponding to the memory table strategy can be determined based on the second number. For example, the greater the second number, the greater the first score value, and the smaller the second number, the smaller the first score value. Of course, the first score value corresponding to the memory table strategy can also be determined based on the first number. For example, the greater the first number, the greater the first score value, and this is not limited. For example, when the second number is greater than 10 and less than or equal to 20, the first score value is 40, when the second number is greater than 20 and less than or equal to 30, the first score value is 60, when the second number is greater than 30 and less than or equal to 40, the first score value is 80, and when the second number is greater than 40, the first score value is 100.

[0120] For example, if the initial table building strategy is the storage format strategy, a first score value corresponding to the storage format strategy can be determined based on a target feature corresponding to the storage format strategy. Referring to case 3 described above, a ratio between the number of column fields of the column pruning type and the total number of column fields of the data table T1 can be calculated, and it is determined that the storage format strategy needs to be optimized based on the ratio. Therefore, the first score value corresponding to the storage format strategy can be determined based on the ratio. For example, if the storage format strategy needs to be optimized when the ratio is less than a first ratio threshold, the greater the ratio, the smaller the first score value, and the smaller the ratio, the greater the first score value. Alternatively, if the storage format strategy needs to be optimized when the ratio is greater than a second ratio threshold, the greater the ratio, the greater the first score value, and the smaller the ratio, the smaller the first score value.

[0121] For example, if the initial table building strategy is the sorting key strategy, a first score value corresponding to the sorting key strategy can be determined based on a target feature corresponding to the sorting key strategy. Referring to case 4 described above, when the total number of occurrences of the column field of the sorting type is greater than a preset number threshold, it indicates that the sorting key strategy needs to be optimized, and the first score value corresponding to the sorting key strategy can be determined based on the total number of occurrences of the column field, such as the greater the total number of occurrences, the greater the first score value, and the smaller the total number of occurrences, the smaller the first score value.

[0122] For example, if the initial table building strategy is the bucketing key strategy, a first score value corresponding to the bucketing key strategy can be determined based on a target feature corresponding to the bucketing key strategy. Referring to case 5 described above, when the total number of occurrences of the column field of the join type or the aggregation type is greater than a preset number threshold, it indicates that the bucketing key strategy needs to be optimized, and the first score value corresponding to the bucketing key strategy can be determined based on the total number of occurrences of the column field, such as the greater the total number of occurrences, the greater the first score value, and the smaller the total number of occurrences, the smaller the first score value.

[0123] In a possible implementation, if the initial table building strategy is the bucket key strategy, the first score value corresponding to the bucket key strategy can also be determined in the following manner: when the total number of occurrences of the column field T13 of the operation type being the connection type or the aggregation type is greater than a preset number threshold, the column field T13 is taken as a new bucket key, in addition, the column field T12 is taken as an old bucket key in the initial data table structure, based on all data in a target statistical period (such as the last few days), the data can be bucketed based on the new bucket key (the column field T13), the data write hotspot information 1 and / or the data distribution uniformity information 1 under this bucketing manner are determined, and the data can be bucketed based on the old bucket key (the column field T12), the data write hotspot information 2 and / or the data distribution uniformity information 2 under this bucketing manner are determined. On this basis, the first score value corresponding to the bucket key strategy can be determined based on the data write hotspot information 1 and / or the data distribution uniformity information 1, and the data write hotspot information 2 and / or the data distribution uniformity information 2.

[0124] If the data write hotspot information 1 indicates that there is a data hotspot problem, and the data write hotspot information 2 indicates that there is a data hotspot problem, that is, the effects of the data hotspot are both poor, the first score value is low (such as less than a threshold). If the data write hotspot information 1 indicates that there is no data hotspot problem, and the data write hotspot information 2 indicates that there is no data hotspot problem, that is, the effects of the data hotspot are both good, the first score value is low. If the data write hotspot information 1 indicates that there is no data hotspot problem, and the data write hotspot information 2 indicates that there is a data hotspot problem, that is, the effect of the new bucket key is better, the first score value is high (such as greater than a threshold). If the data write hotspot information 1 indicates that there is a data hotspot problem, and the data write hotspot information 2 indicates that there is no data hotspot problem, that is, the effect of the old bucket key is better, the first score value is low.

[0125] If the data distribution uniformity information 1 indicates that there is a data uniformity problem, and the data distribution uniformity information 2 indicates that there is a data uniformity problem, that is, the effects of the data uniformity are both poor, the first score value is low. If the data distribution uniformity information 1 indicates that there is no data uniformity problem, and the data distribution uniformity information 2 indicates that there is no data uniformity problem, that is, the effects of the data uniformity are both good, the first score value is low. If the data distribution uniformity information 1 indicates that there is no data uniformity problem, and the data distribution uniformity information 2 indicates that there is a data uniformity problem, that is, the effect of the new bucket key is better, the first score value is high. If the data distribution uniformity information 1 indicates that there is a data uniformity problem, and the data distribution uniformity information 2 indicates that there is no data uniformity problem, that is, the effect of the old bucket key is better, the first score value is low.

[0126] Step 405, determine the switching score value based on the effect score corresponding to each initial table building strategy in the initial table structure of the data table T1. For example, perform weighted operation on the effect score corresponding to each initial table building strategy and the weight value corresponding to the initial table building strategy to obtain the switching score value corresponding to the data table T1.

[0127] For example, the switching score value corresponding to the data table T1 can be determined by using the following formula:

[0128] YG=Y1*W1+Y2*W2+Y3*W3+Y4*W4+Y5*W5

[0129] In the above formula, YG represents the switching score value corresponding to the data table T1, Y1 represents the effect score corresponding to the derived field strategy, W1 represents the weight value corresponding to the derived field strategy, Y2 represents the effect score corresponding to the memory table strategy, W2 represents the weight value corresponding to the memory table strategy, Y3 represents the effect score corresponding to the storage format strategy, W3 represents the weight value corresponding to the storage format strategy, Y4 represents the effect score corresponding to the sorting key strategy, W4 represents the weight value corresponding to the sorting key strategy, Y5 represents the effect score corresponding to the bucket key strategy, and W5 represents the weight value corresponding to the bucket key strategy. Of course, the above formula is only an example of determining the switching score value based on five initial table building strategies, and the determination manner of the switching score value is not limited.

[0130] For example, W1, W2, W3, W4 and W5 can be configured according to experience, and the sum of W1, W2, W3, W4 and W5 can be 1 or other values, which is not limited.

[0131] Step 406, determine whether data table switching is needed based on the switching score value corresponding to the data table T1, if yes, execute step 407, and if not, end the flow and do not switch the data table T1.

[0132] For example, the following steps can be used to determine whether data table switching is needed:

[0133] Step 4061, determine the switching cost value based on the data amount size corresponding to the data table T1 and the time consumption of unit data amount. For example, the switching cost value YC can be determined by using the following formula: YC=N*T. In the above formula, YC is used to represent the switching cost value, N is used to represent the data amount size corresponding to the data table T1, and T is used to represent the time consumption of unit data amount, which is a fixed value and can be pre-configured according to experience.

[0134] Step 4062, determine whether the remaining storage space of the disk is greater than the data amount size corresponding to the data table T1, if yes, execute step 4063, and if not, determine that the data table T1 does not need data table switching.

[0135] For example, when switching data table T1, a target data table needs to be generated first (see the following embodiments for the generation process). The data size of the target data table is the same as that of data table T1. Therefore, the target data table can only be generated normally if the remaining disk storage space is greater than the data size of data table T1. If the remaining disk storage space is not greater than the data size of data table T1, that is, if the disk space is insufficient, the target data table cannot be generated normally, and the switching process is abandoned.

[0136] Step 4063: Determine whether the ratio of the switching score to the switching cost is greater than the ratio threshold. If yes, proceed to step 4064. If no, determine that data table T1 will not be switched.

[0137] Step 4064: Determine that a data table switch is required for data table T1.

[0138] For example, a higher switching score indicates that table T1 should be switched, while a higher switching cost indicates that table T1 should not be switched. Therefore, the decision to switch table T1 can be determined based on the ratio of the switching score to the switching cost. For instance, if the ratio is greater than a threshold, the switching score has a greater impact, and table T1 should be switched. Conversely, if the ratio is less than the threshold, the switching cost has a greater impact, and table T1 should not be switched.

[0139] Step 407: Generate a target data table T1' (i.e., the target data table) with the target data table structure for data table T1.

[0140] For example, see Figure 5 The diagram illustrates the data writing process. Typically, a two-stage writing method is supported: first, data is written to the cache; then, when the amount of data in the cache reaches a preset threshold, the cached data is written to the data table. Based on this data writing principle, in step 407, when generating data table T1' for data table T1, it is permissible to write data for data table T1 to the cache while prohibiting the writing of cached data to data table T1. This process of allowing data for data table T1 to the cache and prohibiting the writing of cached data to data table T1 is used to generate data table T1' for data table T1. This method can be called a seamless switching method.

[0141] Alternatively, when generating the data table T1' of the data table T1, writing data of the data table T1 into the cache can be prohibited, and writing data in the cache into the data table T1 can be prohibited, and the data table T1' of the data table T1 is generated on the basis of prohibiting writing data of the data table T1 into the cache and prohibiting writing data in the cache into the data table T1. The above manner can be referred to as a table stop switching manner.

[0142] In a possible implementation, during data writing, a one-stage writing manner can also be supported, that is, data is directly written into a data table. In this case, writing data into the data table T1 can be prohibited, and the data table T1' of the data table T1 is generated on the basis of prohibiting writing data into the data table T1.

[0143] In a possible implementation, during data writing, a partition writing manner can also be supported, that is, the data table T1 is divided into multiple partitions, and data writing is performed for each partition. That is, the above operation can specify an operation on a certain partition, and the non-sensing switching manner and the table stop switching manner are both for a certain partition.

[0144] For example, when generating the data table T1' of the data table T1 in the target data table structure, for each initial table building strategy in the initial data table structure of the data table T1, if the initial table building strategy needs to be optimized, the initial table building strategy is optimized based on the target feature corresponding to the initial table building strategy, to obtain an optimized target table building strategy; if the initial table building strategy does not need to be optimized, the initial table building strategy is determined as the target table building strategy. As described above, the target table building strategy corresponding to each initial table building strategy can be obtained, and the target data table structure can be generated based on the multiple target table building strategies corresponding to the multiple initial table building strategies, that is, the target data table structure includes multiple target table building strategies. After obtaining the target data table structure, the data table T1' of the target data table structure can be generated. For example, the data table T1 in the initial data table structure is converted into the data table T1' in the target data table structure. Compared with the data table T1, the data in the data table T1' does not change, only the data table structure changes, which is not limited.

[0145] In a possible implementation, the initial table building strategy is optimized based on the target feature corresponding to the initial table building strategy to obtain the optimized target table building strategy, which can include but is not limited to:

[0146] If the initial table building strategy is the derived field strategy, when the derived field strategy needs to be optimized, referring to case 1 described above, if the ratio between the number of times of function operations on the column field T11 and the total number of times of appearance of the column field T11 is greater than a preset ratio threshold, it is determined that the derived field strategy needs to be optimized, and a derived field with function operations needs to be added to the column field T11. Therefore, the target table building strategy can include the derived field strategy, and the derived field strategy is the derived field with function operations added to the column field T11.

[0147] If the initial table building strategy is the in-memory table strategy, when the in-memory table strategy needs to be optimized, referring to case 2 described above, if the first number of operation types is a connection type is greater than a first number threshold, and the second number of distribution modes of the data table T1 is a broadcast distribution mode is greater than a second number threshold, it is determined that the in-memory table strategy needs to be optimized, and the data table T1 needs to be broadcast distributed as an in-memory table. Therefore, the target table building strategy can include the in-memory table strategy, and the in-memory table strategy is the broadcast distribution of the data table T1 as an in-memory table.

[0148] If the initial table building strategy is the storage format strategy, when the storage format strategy needs to be optimized, referring to case 3 described above, if it is determined that the storage format strategy needs to be optimized based on the ratio between the number of column fields of the operation type being a column pruning type and the total number of column fields of the data table T1, and it is determined that the target storage format is column storage based on the ratio, then the target table building strategy can include the storage format strategy, and the storage format strategy is column storage. Alternatively, if it is determined that the target storage format is row storage based on the ratio, then the target table building strategy can include the storage format strategy, and the storage format strategy is row storage.

[0149] If the initial table building strategy is the sorting key strategy, when the sorting key strategy needs to be optimized, referring to case 4 described above, when the total number of times of appearance of the column field (taking the column field T12 as an example) of the operation type being a sorting type is greater than a preset number threshold, it can be determined that the sorting key strategy needs to be optimized, and the column field T12 needs to be used as a sorting key to sort the data table. Therefore, the target table building strategy can include the sorting key strategy, and the sorting key strategy is to use the column field T12 as a sorting key to sort the data table.

[0150] If the initial table building strategy is the bucketing key strategy, when the bucketing key strategy needs to be optimized, referring to case 5 described above, when the total number of times of appearance of the column field (taking the column field T12 as an example) of the operation type being a connection type or an aggregation type is greater than a preset number threshold, it can be determined that the bucketing key strategy needs to be optimized, and the column field T12 needs to be used as a new bucketing key. Therefore, the target table building strategy can include the bucketing key strategy, and the bucketing key strategy is to use the column field T12 as a new bucketing key to bucket the data table based on the new bucketing key.

[0151] Step 408, after the data table T1' of the target data table structure is generated, it is determined whether the data table T1' is effective, if yes, step 409 can be executed, if no, step 410 can be executed.

[0152] Step 409, the data table T1 is replaced by the data table T1', and the data table T1 is deleted.

[0153] Step 410, the data table T1 is retained, and the data table T1' is deleted.

[0154] For example, after the data table T1' is generated, since there are two data tables, the data table T1 and the data table T1', it is necessary to consider whether to retain the data table T1 or the data table T1', therefore, in the embodiment, a plurality of SQL statements corresponding to the data table T1 can be acquired. The plurality of SQL statements are executed based on the data table T1, and a first query time (i.e. the sum of the query times of all SQL statements or the average of the query times of all SQL statements) is obtained. The plurality of SQL statements are executed based on the data table T1', and a second query time (i.e. the sum of the query times of all SQL statements or the average of the query times of all SQL statements) is obtained. On this basis, if the second query time is less than the first query time, the data table T1 is replaced by the data table T1', and the data table T1 is deleted from the database. Or, if the second query time is greater than the first query time, the data table T1 can be retained, and the data table T1' is deleted from the database.

[0155] In the above embodiment, the plurality of SQL statements corresponding to the data table T1 can be part of the SQL statements corresponding to the data table T1 sampled from all historical SQL statements, which is not limited.

[0156] In the above embodiment, when the data table T1 is replaced by the data table T1', data consistency verification can also be performed, such as checking the total number, sampling (full amount) checking single data content consistency, and then replacing the data table T1 by the data table T1', otherwise, the data table T1 needs to be retained.

[0157] From the above technical solutions, in the embodiment of the application, after the initial data table of the initial data table structure is established, if it is determined that the initial data table needs to be switched, a target data table of a target data table structure can be established, and the initial data table is replaced by the target data table, that is, after the initial data table structure is selected for the data table according to the business scenario, and the initial data table matching the initial data table structure is established, if the initial data table structure is unreasonable, the unreasonable initial data table structure can be adjusted, so that the adjusted target data table structure is reasonable, thereby improving the data writing speed, reducing the data query time, improving the data query effect, and improving the data query performance. Whether each data table needs to be switched to the data table structure can be evaluated by disassembling the SQL statement, if the switching cost value is calculated, if the switching cost value is compared with the switching benefit value, the switching data table structure operation is performed, after the generation of the new data table of the new data table is completed, the effect evaluation is performed, whether the expected effect is improved, if so, the pointing of the data table is modified, and the new data table is switched. The data storage can be optimized from the bottom layer, thereby improving the data storage and query performance. Based on the optimization evaluation scheme of the SQL feature, whether the underlying data storage mode needs to be changed is evaluated. An algorithm for calculating the optimization scheme effect is proposed, which considers optimization selection, optimization cost, and optimization effect.

[0158] Based on the same application concept as the above method, in the embodiment of the application, a data table switching device is provided, as shown in Figure 6 The device can include:

[0159] The acquisition module 61 is configured to acquire the SQL feature corresponding to the initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure includes a plurality of initial table creation strategies;

[0160] The determination module 62 is configured to determine an effect score corresponding to each initial table creation strategy based on the SQL feature; wherein the effect score is used to indicate whether the initial table creation strategy needs to be optimized;

[0161] And, based on the effect score corresponding to each initial table creation strategy, a switching score value is determined;

[0162] The generation module 63 is configured to, if it is determined based on the switching score value that data table switching is needed, determine a target data table structure based on the initial data table structure, the target data table structure including a plurality of target table creation strategies corresponding to the plurality of initial table creation strategies; generate a target data table of the target data table structure, and replace the initial data table by the target data table.

[0163] The acquisition module 61 acquires the SQL features corresponding to the initial data table, for example, by: acquiring, from a feature database, the SQL features corresponding to the initial data table when a switching evaluation condition of the initial data table is met; and performing the following maintenance process on the feature database: determining a data table corresponding to a SQL statement each time the SQL statement is received; acquiring, from the SQL statement, the SQL features corresponding to the data table; and recording the SQL features in a data field corresponding to the data table in the feature database.

[0164] The acquisition module 61 acquires the SQL features corresponding to the data table from the SQL statement, for example, by: acquiring, from the SQL statement, an operation type and operation data for the data table; and wherein: if the operation type is a join type, the operation data includes at least one of the following: a column field in the data table; whether the column field has a function operation, the function operation indicating that the column field needs to be converted by a function; and a distribution manner of the data table, the distribution manner being a hash distribution manner or a broadcast distribution manner; and if the operation type is a column pruning type, a filtering type, an aggregation type, or a sorting type, the operation data includes at least one of the following: a column field in the data table; and whether the column field has a function operation.

[0165] The acquisition module 61 determines that the switching evaluation condition of the initial data table is met, for example, by: determining that the switching evaluation condition of the initial data table is met if an establishment time corresponding to the initial data table is greater than a preset time threshold, and / or a number of SQL features corresponding to the initial data table is greater than a preset number threshold.

[0166] The determination module 62 determines the effect score corresponding to each initial table creation strategy based on the SQL features, for example, by: determining a target feature corresponding to the initial table creation strategy based on the SQL features; determining, based on the target feature, whether the initial table creation strategy needs to be optimized; determining that the effect score corresponding to the initial table creation strategy is a first score value if the initial table creation strategy needs to be optimized; determining that the effect score corresponding to the initial table creation strategy is a second score value if the initial table creation strategy does not need to be optimized; and wherein the first score value is greater than the second score value.

[0167] For example, the determining module 62 determines the target feature corresponding to the initial table creation strategy based on the SQL features, and determines whether the initial table creation strategy needs to be optimized based on the target feature, specifically: if the initial table creation strategy is the derived field strategy, for each column field in the initial data table, the number of times that the column field has function operations and the total number of occurrences of the column field are counted based on the SQL features, and the proportion of the number of times to the total number of occurrences is determined; if the proportion corresponding to a column field is greater than a preset proportion threshold, it is determined that the derived field strategy needs to be optimized; otherwise, it is determined that the derived field strategy does not need to be optimized; if the initial table creation strategy is the in-memory table strategy, the first number of operation types that are connection types and the second number of distribution modes of the initial data table that are broadcast distribution modes are counted based on the SQL features; if the first number is greater than a first number threshold and the second number is greater than a second number threshold, it is determined that the in-memory table strategy needs to be optimized; otherwise, it is determined that the in-memory table strategy does not need to be optimized; if the initial table creation strategy is the storage format strategy, the number of column fields of column pruning type is counted based on the SQL features; the target storage format is determined based on the proportion of the number of column fields to the total number of column fields of the initial data table; if the target storage format is different from the initial storage format of the initial data table, it is determined that the storage format strategy needs to be optimized; otherwise, it is determined that the storage format strategy does not need to be optimized; wherein, if the proportion is less than a first proportion threshold, the target storage format is column storage; if the proportion is greater than a second proportion threshold, the target storage format is row storage; if the initial table creation strategy is the sorting key strategy, the total number of occurrences of each column field of sorting type is counted based on the SQL features; if the total number of occurrences of a column field is greater than a preset number threshold, it is determined that the sorting key strategy needs to be optimized; otherwise, it is determined that the sorting key strategy does not need to be optimized; if the initial table creation strategy is the bucketing key strategy, the total number of occurrences of each column field of connection type or aggregation type is counted based on the SQL features; if the total number of occurrences of a column field is greater than a preset number threshold, the column field is determined as a new bucketing key, and it is determined that the bucketing key strategy needs to be optimized; otherwise, it is determined that the bucketing key strategy does not need to be optimized.

[0168] For example, the determining module 62 determines the switching score value based on the effect score corresponding to each initial table creation strategy, specifically: the effect score corresponding to each initial table creation strategy and the weight value corresponding to the initial table creation strategy are subjected to weighted operation to obtain the switching score value corresponding to the initial data table.

[0169] For example, the generation module 63 determines that the data table switching is needed when the switching score value is greater than a switching cost value, and the switching score value is greater than a threshold value. The switching cost value is determined based on the size of the data corresponding to the initial data table and the time cost of the unit data size.

[0170] For example, the generation module 63 determines the target data table structure based on the initial data table structure. For each initial table building strategy in the initial data table structure, if the initial table building strategy needs to be optimized, the initial table building strategy is optimized based on the target feature corresponding to the initial table building strategy to obtain an optimized target table building strategy. If the initial table building strategy does not need to be optimized, the initial table building strategy is determined as the target table building strategy. The target data table structure is determined based on the plurality of target table building strategies corresponding to the plurality of initial table building strategies.

[0171] For example, the generation module 63 generates the target data table of the target data table structure. The data for the initial data table is allowed to be written into the cache, and the data in the cache is prohibited to be written to the initial data table. The target data table of the target data table structure is generated based on the initial data table. Alternatively, the data for the initial data table is prohibited to be written into the cache, and the data in the cache is prohibited to be written to the initial data table. The target data table of the target data table structure is generated based on the initial data table.

[0172] For example, the generation module 63 replaces the initial data table with the target data table. The plurality of SQL statements corresponding to the initial data table are obtained. The plurality of SQL statements are executed based on the initial data table to obtain a first query time. The plurality of SQL statements are executed based on the target data table to obtain a second query time. If the second query time is less than the first query time, the initial data table is replaced with the target data table, and the initial data table is deleted from the database.

[0173] Based on the same application concept as the above method, the embodiment of the present application proposes a data table switching device. As shown in Figure 7 The data table switching device includes a processor 71 and a machine readable storage medium 72. The machine readable storage medium 72 stores machine executable instructions that can be executed by the processor 71. The processor 71 is configured to execute the machine executable instructions to implement the data table switching method disclosed in the above examples of the present application.

[0174] Based on the same application concept as the above method, the embodiment of the present application also provides a machine readable storage medium, wherein the machine readable storage medium stores a plurality of computer instructions, and the computer instructions can realize the data table switching method disclosed in the above examples of the present application when executed by a processor.

[0175] The machine readable storage medium can be any electronic, magnetic, optical, or other physical storage device, and can contain or store information such as executable instructions, data, and the like. For example, the machine readable storage medium can be a RAM (Random Access Memory), a volatile memory, a non-volatile memory, a flash memory, a storage drive (such as a hard drive), a solid state drive, any type of storage disk (such as an optical disk, a DVD, and the like), or similar storage medium, or a combination thereof.

[0176] The system, device, module, or unit illustrated in the above embodiments can be specifically implemented by a computer chip or entity, or by a product with certain functions. A typical implementation device is a computer, and the specific form of the computer can be a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.

[0177] For the convenience of description, the above device is described as various units by function. Of course, the functions of the units can be implemented in the same or multiple software and / or hardware in the implementation of the present application.

[0178] Those skilled in the art should understand that the embodiments of the present application can be provided as a method, a system, or a computer program product. Therefore, the present application can be in the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Moreover, the embodiments of the present application can be in the form of a computer program product implemented on one or more computer usable storage media (including but not limited to a magnetic disk storage, a CD-ROM, an optical storage, and the like) containing computer usable program code.

[0179] The computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart or flowsheet block or blocks. Figure 1 one or more flowsheet or flowsheet blocks. Figure 1 one or more flowsheet or flowsheet blocks.

[0180] Also, these computer program instructions can be stored in a computer readable memory capable of directing a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer readable memory produce an article of manufacture including instructions which implement the function specified in the flowchart or flowsheet block or blocks. Figure 1 one or more flowsheet or flowsheet blocks. Figure 1 one or more flowsheet or flowsheet blocks.

[0181] The computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart or flowsheet block or blocks. Figure 1 one or more flowsheet or flowsheet blocks. Figure 1 one or more flowsheet or flowsheet blocks.

[0182] The embodiments of the present application described above are merely used to illustrate the present application and should not be used to limit the present application. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present application should be included in the scope of protection of the present application.

Claims

1. A method for switching data tables, characterized in that, The method includes: Obtain the SQL characteristics corresponding to the initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure includes multiple initial table creation strategies; The effectiveness score for each initial table creation strategy is determined based on the SQL characteristics; wherein the effectiveness score is used to indicate whether the initial table creation strategy needs to be optimized. The switching score is determined based on the performance score corresponding to each initial table creation strategy. If a data table switch is determined to be required based on the switching score, a target data table structure is determined based on the initial data table structure. The target data table structure includes multiple target table creation strategies corresponding to the multiple initial table creation strategies. A target data table of the target data table structure is generated, and the initial data table is replaced by the target data table. The step of determining the performance score corresponding to each initial table creation strategy based on the SQL features includes: for each initial table creation strategy, determining the target features corresponding to the initial table creation strategy based on the SQL features, and determining whether the initial table creation strategy needs to be optimized based on the target features; if yes, determining the performance score corresponding to the initial table creation strategy as a first score value; if no, determining the performance score corresponding to the initial table creation strategy as a second score value; the first score value is greater than the second score value; The step of determining the target features corresponding to the initial table creation strategy based on the SQL features, and determining whether the initial table creation strategy needs to be optimized based on the target features, includes: If the initial table creation strategy is a derived field strategy, for each column field in the initial data table, based on the SQL characteristics, the number of times the column field has function operations and the total number of times the column field appears are statistically analyzed, and the ratio of the number of operations to the total number of occurrences is determined; if the ratio of any column field is greater than a preset ratio threshold, it is determined that the derived field strategy needs to be optimized; otherwise, it is determined that the derived field strategy does not need to be optimized. If the initial table creation strategy is a memory table strategy, the first count of operation type (connection type) and the second count of initial data table distribution method (broadcast distribution type) are counted based on the SQL characteristics. If the first count is greater than the threshold for the first count and the second count is greater than the threshold for the second count, then the memory table strategy needs to be optimized; otherwise, the memory table strategy does not need to be optimized. If the initial table creation strategy is a storage format strategy, the number of columns with the column pruning type is counted based on the SQL feature statistics. The target storage format is determined based on the ratio of the number of these columns to the total number of columns in the initial data table. If the target storage format differs from the initial storage format of the initial data table, the storage format strategy needs optimization; otherwise, the storage format strategy does not need optimization. If the initial table creation strategy is a sort key strategy, the total number of occurrences of each column with the sorting type is counted based on the SQL feature statistics. If the total number of occurrences of any column exceeds a preset threshold, the sort key strategy needs optimization; otherwise, the sort key strategy does not need optimization. If the initial table creation strategy is a bucketing key strategy, the total number of occurrences of each column field with the operation type of join or aggregation is counted based on the SQL characteristics. If the total number of occurrences of any column field is greater than a preset threshold, then the column field is determined as the new bucketing key, and the bucketing key strategy needs to be optimized; otherwise, the bucketing key strategy does not need to be optimized.

2. The method according to claim 1, characterized in that, The process of obtaining the SQL features corresponding to the initial data table includes: When the switching evaluation conditions of the initial data table are met, the SQL features corresponding to the initial data table are obtained from the feature database; wherein, the maintenance process of the feature database includes: Each time an SQL statement is received, the corresponding data table is determined. Obtain the SQL characteristics corresponding to the data table from the SQL statement; The SQL feature is recorded in the data field corresponding to the data table in the feature database.

3. The method according to claim 2, characterized in that, The step of obtaining the SQL features corresponding to the data table from the SQL statement includes: Retrieve the operation type and operation data for the data table from the SQL statement; wherein: If the operation type is a join type, the operation data includes at least one of the following: column fields in the data table; whether the column fields have function operations, wherein the function operations indicate that the column fields need to be transformed by a function; the distribution method of the data table, wherein the distribution method is a hash distribution method or a broadcast distribution method; If the operation type is column trimming, filtering, aggregation, or sorting, the operation data includes at least one of the following: column fields in the data table; and whether the column fields have function operations.

4. The method according to claim 2, characterized in that, The switching evaluation conditions for the initial data table are satisfied as follows: if the establishment duration of the initial data table is greater than a preset duration threshold, and / or the number of SQL features corresponding to the initial data table is greater than a preset number threshold, then the switching evaluation conditions for the initial data table are satisfied.

5. The method according to claim 1, characterized in that, If the ratio is less than the first ratio threshold, the target storage format is column storage; if the ratio is greater than the second ratio threshold, the target storage format is row storage.

6. The method according to claim 1, characterized in that, The process of determining the switching score based on the performance score corresponding to each initial table creation strategy includes: The switching score corresponding to the initial data table is obtained by weighting the effect score and the weight value of each initial table creation strategy.

7. The method according to claim 1, characterized in that, The step of determining the need for a data table switch based on the switching score includes: The switching cost is determined based on the amount of data in the initial data table and the time taken per unit of data. If the remaining disk storage space is greater than the data size corresponding to the initial data table, and the ratio of the switching score to the switching cost is greater than the ratio threshold, then it is determined that a data table switch is required.

8. The method according to claim 1, characterized in that, Determining the target data table structure based on the initial data table structure includes: For each initial table creation strategy in the initial data table structure, if the initial table creation strategy needs to be optimized, then the initial table creation strategy is optimized based on the target features corresponding to the initial table creation strategy to obtain the optimized target table creation strategy; if the initial table creation strategy does not need to be optimized, then the initial table creation strategy is determined as the target table creation strategy. The target data table structure is determined based on the multiple target table creation strategies corresponding to the multiple initial table creation strategies.

9. The method according to claim 1 or 8, characterized in that, The target data table for generating the target data table structure includes: Allow writing data for the initial data table to the cache, prohibit writing data from the cache to the initial data table, and generate a target data table with the target data table structure based on the initial data table; or, Writing data for the initial data table to the cache is prohibited, writing data from the cache to the initial data table is prohibited, and a target data table with the target data table structure is generated based on the initial data table.

10. The method according to claim 1, characterized in that, The step of replacing the initial data table with the target data table includes: Obtain multiple SQL statements corresponding to the initial data table; Based on the initial data table, execute the multiple SQL statements to obtain the first query duration; Based on the target data table, execute the multiple SQL statements to obtain the second query duration; If the second query duration is less than the first query duration, then the initial data table is replaced by the target data table, and the initial data table is deleted from the database.

11. A data table switching device, characterized in that, The device includes: The acquisition module is used to acquire the SQL characteristics corresponding to the initial data table; wherein the initial data table adopts an initial data table structure, and the initial data table structure includes multiple initial table creation strategies. The determination module is used to determine the performance score corresponding to each initial table creation strategy based on the SQL characteristics; wherein the performance score is used to indicate whether the initial table creation strategy needs to be optimized. In addition, the switching score is determined based on the performance score corresponding to each initial table creation strategy; The generation module is used to determine a target data table structure based on the initial data table structure if a data table switch is required based on the switching score. The target data table structure includes multiple target table creation strategies corresponding to the multiple initial table creation strategies. The module generates a target data table based on the target data table structure and replaces the initial data table with the target data table. Specifically, when the determining module determines the performance score corresponding to each initial table creation strategy based on the SQL features, it is used to: determine the target features corresponding to the initial table creation strategy based on the SQL features, and determine whether the initial table creation strategy needs to be optimized based on the target features; if yes, then determine the performance score corresponding to the initial table creation strategy as a first score value; if no, then determine the performance score corresponding to the initial table creation strategy as a second score value; wherein, the first score value is greater than the second score value; Specifically, when the determining module determines the target features corresponding to the initial table creation strategy based on the SQL features, and determines whether the initial table creation strategy needs optimization based on the target features, it is used as follows: If the initial table creation strategy is a derived field strategy, for each column field in the initial data table, based on the SQL features, the number of times the column field has function operations and the total number of times the column field appears are counted, and the ratio of the number of times to the total number of times is determined; if the ratio corresponding to any column field is greater than a preset ratio threshold, then the derived field strategy needs optimization; otherwise, the derived field strategy does not need optimization; if the initial table creation strategy is a memory table strategy, based on the SQL features, the first count of operation type (join type) and the second count of initial data table distribution method (broadcast distribution method) are counted; if the first count is greater than the first count threshold and the second count is greater than the second count threshold, then the memory table strategy needs optimization; otherwise, the memory table strategy does not need optimization; if the initial table creation strategy is a storage format... The strategy involves: 1) Counting the number of columns in the column pruning type based on the SQL feature statistics; 2) Determining the target storage format based on the ratio of this number to the total number of columns in the initial data table; 3) If the target storage format differs from the initial storage format of the initial data table, the storage format strategy needs optimization; otherwise, it does not need optimization. If the initial table creation strategy is a sorting key strategy, the strategy involves counting the total number of occurrences of each column in the sorting type based on the SQL feature statistics; 4) If the total number of occurrences of any column exceeds a preset threshold, the sorting key strategy needs optimization; otherwise, it does not need optimization. 5) If the initial table creation strategy is a bucketing key strategy, the strategy involves counting the total number of occurrences of each column in the join or aggregation type based on the SQL feature statistics; 5) If the total number of occurrences of any column exceeds a preset threshold, this column is designated as a new bucketing key, and the bucketing key strategy needs optimization; otherwise, it does not need optimization.

12. The apparatus according to claim 11, Its features are, Specifically, when the acquisition module acquires the SQL features corresponding to the initial data table, it is used to: acquire the SQL features corresponding to the initial data table from the feature database when the switching evaluation conditions of the initial data table are met; the maintenance process of the feature database includes: determining the data table corresponding to the SQL statement each time an SQL statement is received; acquiring the SQL features corresponding to the data table from the SQL statement; and recording the SQL features in the data field corresponding to the data table in the feature database. Specifically, when the acquisition module retrieves the SQL features corresponding to the data table from the SQL statement, it is used to: retrieve the operation type and operation data for the data table from the SQL statement; wherein: if the operation type is a join type, the operation data includes at least one of the following: column fields in the data table; whether the column fields have function operations, the function operations indicating that the column fields need to be transformed by a function; the distribution method of the data table, the distribution method being a hash distribution method or a broadcast distribution method; if the operation type is a column trimming type, filtering type, aggregation type, or sorting type, the operation data includes at least one of the following: column fields in the data table; whether the column fields have function operations; Specifically, when the acquisition module determines that the switching evaluation conditions of the initial data table are met, it is used to: if the establishment duration of the initial data table is greater than a preset duration threshold, and / or the number of SQL features corresponding to the initial data table is greater than a preset number threshold, then it is determined that the switching evaluation conditions of the initial data table are met. Wherein, if the ratio is less than the first ratio threshold, the target storage format is column storage; if the ratio is greater than the second ratio threshold, the target storage format is row storage. Specifically, when the determining module determines the switching score based on the effect score corresponding to each initial table creation strategy, it performs a weighted calculation on the effect score corresponding to each initial table creation strategy and the weight value corresponding to the initial table creation strategy to obtain the switching score corresponding to the initial data table. Specifically, when the generation module determines that a data table switch is required based on the switching score, it is used to: determine the switching cost based on the data size of the initial data table and the time taken per unit of data; if the remaining disk storage space is greater than the data size of the initial data table, and the ratio of the switching score to the switching cost is greater than a ratio threshold, then it is determined that a data table switch is required. Specifically, when the generation module determines the target data table structure based on the initial data table structure, it performs the following: for each initial table creation strategy in the initial data table structure, if the initial table creation strategy needs to be optimized, it optimizes the initial table creation strategy based on the target features corresponding to the initial table creation strategy to obtain an optimized target table creation strategy; if the initial table creation strategy does not need to be optimized, it determines the initial table creation strategy as the target table creation strategy; and it determines the target data table structure based on the multiple target table creation strategies corresponding to the multiple initial table creation strategies. Specifically, when the generation module generates the target data table of the target data table structure, it is used to: allow writing data for the initial data table to the cache, prohibit writing data in the cache to the initial data table, and generate the target data table of the target data table structure based on the initial data table; or, prohibit writing data for the initial data table to the cache, prohibit writing data in the cache to the initial data table, and generate the target data table of the target data table structure based on the initial data table. Specifically, when the generation module replaces the initial data table with the target data table, it is used to: obtain multiple SQL statements corresponding to the initial data table; execute the multiple SQL statements based on the initial data table to obtain a first query duration; execute the multiple SQL statements based on the target data table to obtain a second query duration; if the second query duration is less than the first query duration, then replace the initial data table with the target data table and delete the initial data table from the database.

13. A data table switching device, characterized in that, include: A processor and a machine-readable storage medium, the machine-readable storage medium storing machine-executable instructions that can be executed by the processor; The processor is configured to execute machine-executable instructions to implement the steps of the method according to any one of claims 1-10.

Images