A wide table updating method, device, equipment and computer readable storage medium

Abstract

The application discloses a wide table updating method and device, equipment and a computer readable storage medium, the method comprises the following steps: obtaining the running information and sql statements of each wide table computing task, and screening the target wide table computing task to be transformed according to the running information and sql statements; according to the sql statements of the target wide table computing task, the current source information referred to by the target wide table computing task is determined, and the historical source information is obtained; wherein, the current source information comprises a current source table and a current field; by comparing the current source information with the historical source information, the difference source information is determined, and the current source information in the sql statements is replaced by the difference source information to obtain new sql statements; according to the new sql statements, the wide table is updated. By using the method of the application, the wide table computing task needing to be transformed can be identified, and the rewriting of the sql statements of the wide table computing task can also be automatically completed, so that unnecessary calculation is effectively reduced, and the operation efficiency and performance of the data warehouse are improved.

Description

Technical Field

[0001] This invention relates to the field of computer software technology, and more specifically to a wide table update method, apparatus, device, and computer-readable storage medium. Background Technology

[0002] In the process of synchronizing data from the business database to the data warehouse, it is often necessary to update a large number of wide tables. Existing technologies typically use a scheme to recalculate the entire historical data for wide table updates, which consumes a great deal of computing resources and time. In addition, there is also a scheme for incremental updates based on the data update time in the existing technology. However, the synchronized data may still have the following problems, which may cause the incremental update scheme to fail to retrieve the incremental data correctly, resulting in errors in wide table updates or still requiring a large amount of computing resources and time: (1) the update time is null; (2) the business system does not synchronize the update time when changing the data; (3) when the business system changes the data in a certain column, it modifies the update time of all data in that column to the same time; (4) the business system hard deletes data. Summary of the Invention

[0003] This invention provides a wide table update method, apparatus, device, and computer-readable storage medium to solve the problems of excessive computational resource consumption and insufficient data update accuracy in existing wide table calculation schemes.

[0004] To address the aforementioned technical problems, in a first aspect, the present invention provides a wide table update method, the method comprising:

[0005] Obtain the running information and SQL statements of each wide table calculation task, and filter the target wide table calculation tasks to be modified based on the running information and SQL statements;

[0006] Based on the SQL statement of the target wide table calculation task, determine the current source information referenced by the target wide table calculation task, and obtain historical source information; wherein, the current source information includes the current source table and the current field;

[0007] By comparing the current source information and the historical source information, the difference source information is determined, and the current source information in the SQL statement is replaced with the difference source information to obtain a new SQL statement;

[0008] Based on the new SQL statement, perform a wide table update and use the current source information as the new historical source information.

[0009] As an optional implementation, the step of filtering the target wide table computation tasks to be modified based on the runtime information and SQL statements includes:

[0010] For each wide table computation task, based on the current wide table computation task's runtime information and SQL statements, and according to the computation value parameter calculation formula, the computation value parameter of the current wide table computation task is calculated; wherein, the computation value parameter calculation formula is:

[0011]

[0012] Where v represents the transformation value parameter of the current wide table computing task, and A i B represents the row number of the i-th current source table referenced by the current wide table computation task. i Let C represent the total number of rows in the i-th current source table referenced by the current wide table computation task, C represent the execution time of the current wide table computation task, D represent the number of CPUs used by the current wide table computation task, E represent the average CPU utilization of the current wide table computation task, F represent the average memory usage of the current wide table computation task, G represent the maximum memory usage of the current wide table computation task, n represent the total number of current source tables referenced by the current wide table computation task, and r represent the total number of rows in the current wide table computation task. i This represents the ratio of the number of current fields in the i-th current source table referenced by the current wide table calculation task to the total number of all fields.

[0013] The transformation value parameter of the current wide table computing task is compared with a preset threshold. If the transformation value parameter of the current wide table computing task is greater than the preset threshold, the current wide table computing task is determined to be the target wide table computing task.

[0014] As an optional implementation, determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task includes:

[0015] The SQL statement of the target wide table calculation task is parsed to extract the current source table and current field referenced by the target wide table calculation task;

[0016] For each current source table, a corresponding current signature table is generated as the current source information; wherein, the current signature table includes the primary key of the current source table and the signature of the current field.

[0017] As an optional implementation, generating a corresponding current signature table for each current source table includes:

[0018] For each of the current source tables, a corresponding initial current signature table is created; the initial current signature table includes the primary key of the current source table;

[0019] Arrange the current fields referenced by the target wide table calculation task in alphabetical order and concatenate them into a string;

[0020] The string is transformed using the MD5 message digest algorithm. The first N characters of the MD5 value are selected and a prefix is ​​added as the field name. The MD5 value is then used as the field value to obtain the signature of the current field. Here, N is a positive integer.

[0021] The signature of the current field is inserted into the initial current signature table to generate the corresponding current signature table.

[0022] As an optional implementation, after determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, the method further includes:

[0023] Determine the end position of the last WITH clause as the insertion position of the WITH clause generated when the current source information in the SQL statement is replaced with the difference source information.

[0024] As an optional implementation, after filtering the target wide table calculation tasks to be modified based on the running information and SQL statements, the method further includes:

[0025] Rewrite the HiveSQL syntax file according to the preset syntax file rules;

[0026] The preset syntax file rules include:

[0027] Variable names support the insertion of {$xxx.xxx}; and / or,

[0028] Remove fine-grained statement parsing under functions and expressions.

[0029] As an optional implementation, before performing the wide table update based on the new SQL statement, the method further includes:

[0030] Based on the new SQL statement, create and run a temporary task to obtain a temporary result table;

[0031] The temporary result table is compared with the correct result table. If the comparison results are correct for M consecutive times, the new SQL statement is determined to have passed the validation.

[0032] In a second aspect, the present invention provides a wide table updating apparatus, the apparatus comprising:

[0033] The target filtering unit is used to obtain the running information and SQL statements of each wide table calculation task, and to filter the target wide table calculation tasks to be modified based on the running information and SQL statements.

[0034] The information determination unit is used to determine the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, and to obtain historical source information; wherein, the current source information includes the current source table and the current field;

[0035] The difference replacement unit is used to compare the current source information and the historical source information, determine the difference source information, and replace the current source information in the SQL statement with the difference source information to obtain a new SQL statement.

[0036] The wide table update unit is used to update the wide table based on the new SQL statement and use the current source information as the new historical source information.

[0037] Thirdly, the present invention provides a wide table update device, including a memory and a processor, wherein:

[0038] The memory is used to store computer programs;

[0039] The processor is used to read the computer program in the memory and execute the steps of the wide table update method as described in the first aspect.

[0040] Fourthly, the present invention provides a computer-readable storage medium having a readable computer program stored thereon, which, when executed by a processor, implements the steps of the wide table update method as described in the first aspect.

[0041] Compared with existing technologies, this invention provides a method, apparatus, device, and computer-readable storage medium for updating wide tables. This method can identify wide table computation tasks requiring modification, avoiding manual analysis of massive wide table computation tasks. It can also automatically rewrite the SQL statements for wide table computation tasks, automatically compare the underlying data used, and filter out records with updates in the fields used. This effectively reduces unnecessary calculations and improves the operational efficiency and performance of the data warehouse. By using the method provided by this invention, intelligent modification of wide table computations in data warehouses can be achieved, reducing resource waste and increasing data processing speed. Furthermore, the method provided by this invention has high practicality and versatility, and can be widely applied to data warehouse management of various sizes and fields, helping enterprises save costs and improve competitiveness. Attached Figure Description

[0042] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention, and not all embodiments. For those skilled in the art, other drawings obtained from these drawings without creative effort are all within the scope of protection of this application.

[0043] Figure 1 This is a flowchart of a wide table update method provided in an embodiment of the present invention;

[0044] Figure 2 This is a schematic diagram illustrating the relationship between a wide table and a source table provided in an embodiment of the present invention;

[0045] Figure 3 This is a flowchart illustrating a process for generating a new SQL statement, provided by an embodiment of the present invention.

[0046] Figure 4 This is a schematic diagram of the structure of a wide table updating device provided in an embodiment of the present invention;

[0047] Figure 5 This is a schematic diagram of the structure of a wide table update device provided in an embodiment of the present invention;

[0048] Figure 6 This is a schematic diagram of the structure of a computer-readable storage medium provided in an embodiment of the present invention. Detailed Implementation

[0049] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and specific embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0050] To make the description of this disclosure more detailed and complete, illustrative descriptions of embodiments and specific examples of the present invention are provided below; however, these are not the only forms of implementing or utilizing the specific embodiments of the present invention. The embodiments cover features of multiple specific embodiments and the methods, steps, and their order for constructing and operating these specific embodiments. However, other specific embodiments may also be used to achieve the same or equivalent functions and step sequences. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without inventive effort are within the scope of protection of this application.

[0051] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in sequences other than those illustrated or described herein.

[0052] In the description of the embodiments of the present invention, unless otherwise stated, " / " means "or". For example, A / B can mean A or B. The word "and / or" in the text is merely a description of the relationship between related objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist simultaneously, and B exists alone. In addition, in the description of the embodiments of this application, "multiple" means two or more. Other quantifiers should be understood similarly. The preferred embodiments described herein are only used to illustrate and explain the present invention and are not intended to limit the present invention. Furthermore, the embodiments and features in the embodiments of this application can be combined with each other without conflict.

[0053] Example 1

[0054] like Figure 1 The flowchart described above illustrates a wide table update method provided in an embodiment of the present invention, which includes the following steps.

[0055] Step S101: Obtain the running information and SQL statements of each wide table calculation task, and filter the target wide table calculation tasks to be modified based on the running information and SQL statements.

[0056] It should be noted that the above runtime information refers to the parameter information of each wide table calculation task when it runs in the computing engine, including the information of the source tables it references.

[0057] The above source table is the data source for the current wide table calculation task.

[0058] like Figure 2 The diagram shown illustrates the relationship between a wide table and a source table according to an embodiment of the present invention.

[0059] In the above Figure 2 In this table, the wide table references field 2 of business table 1 to form field 1 of the wide table, and references field 1 and field 2 of business table 2 to form field 2 and field 3 of the wide table, respectively. The aforementioned business table 1 and business table 2 are the source tables of the wide table.

[0060] In some embodiments, the above runtime information includes the row number A of the i-th current source table read data referenced by the current wide table computation task. i The total number of rows B in the i-th current source table referenced by the current wide table computation task. i, i is the total number of current source tables referenced by the current wide table computation task; n is the execution time of the current wide table computation task; C is the number of CPUs (Central Processing Units) used by the current wide table computation task; D is the average CPU utilization of the current wide table computation task; E is the average memory usage of the current wide table computation task; F is the average memory usage of the current wide table computation task; G is the maximum memory usage of the current wide table computation task.

[0061] The SQL statement above is in Structured Query Language (SQL), a database query and programming language used to manipulate databases, access data, and query, update, and manage relational database systems.

[0062] The above SQL statement is the SQL statement executed by the wide table calculation task mentioned above.

[0063] In some embodiments, the format of the above SQL statement is as follows:

[0064] insert into wide table

[0065] Select from source table 1

[0066] leftjoin source table 2

[0067] left source table 3

[0068] In some embodiments, the input and output of the above SQL statement parsing function are, for example:

[0069] insert overwrite table zz

[0070] SELECT a.a1, a.a2, b.b2, c.c2

[0071] from a

[0072] leftjoin(selectb1,b2 from source1

[0073] where update_time>'xxx'and is_valid=1)b on a.a1=b.b1

[0074] leftjoin c on a.a1=c.c1

[0075] The above SQL statement means that the source table source_tables is ['a','source1','c'], the target table target_table is "zz", and the source table field is {'a':['a1','a2'],'b':['b1','b2'],'c':['c1','c2']}.

[0076] In some embodiments, the above-mentioned filtering of target wide table calculation tasks to be modified based on SQL statements can be achieved by parsing the ratio of the number of current fields to the total number of fields in the current source table referenced by the current wide table calculation task based on the SQL statements, and then filtering the target wide table calculation tasks to be modified based on the ratio.

[0077] It should be noted that the method of updating the wide table based on the fields used in the source table adopted in this embodiment of the invention can shorten the calculation time when the amount of read data is large, the amount of write data is large, and the proportion of fields used in the source table is small. However, in other cases, the calculation time will increase due to the complexity of the operation. Therefore, this embodiment of the invention filters the target wide table calculation tasks to be modified based on the running information and SQL statements, and only performs the following step S102 and subsequent processing steps on the target wide table calculation tasks that pass the filtering.

[0078] In some embodiments, for wide table calculation tasks that fail the screening, any implementation method can be used for updating, such as: (1) extracting new records according to the update time, then merging the new records with the latest record table in the data warehouse to remove duplicates, obtaining the latest record table, and using the full data of the latest record table to calculate the wide table; (2) extracting new records according to the update time, then merging the new records with the latest record table in the data warehouse to remove duplicates, obtaining the latest record table, and then filtering out records after the specified date threshold by specifying a date threshold, and calculating the wide table; (3) using manual methods to identify, modify, and update the wide table; (4) data lake scheme, etc., the embodiments of the present invention do not limit this in any way.

[0079] Step S102: Based on the SQL statement of the target wide table calculation task, determine the current source information referenced by the target wide table calculation task, and obtain historical source information; wherein, the current source information includes the current source table and the current field;

[0080] In step S102 above, the SQL statements of several selected target wide table calculation tasks are deeply analyzed to provide the necessary foundation for subsequent replacement of source tables and removal of conditions.

[0081] The aforementioned deep parsing refers to parsing the current source information referenced by the target wide table calculation task from the aforementioned SQL statement. The current source information includes all current fields referenced by the target wide table calculation task and the corresponding current source table.

[0082] Step S103: Compare the current source information and the historical source information to determine the difference source information, and replace the current source information in the SQL statement with the difference source information to obtain a new SQL statement;

[0083] In step S103 above, the SQL statements of several selected target wide table calculation tasks are replaced and reconstructed in order to replace the source table and remove the conditions.

[0084] By comparing the current source information and the historical source information, records in inconsistent source tables are filtered out as difference source information and replaced. The aforementioned difference source information is then used as the result of the source table.

[0085] Step S104: Based on the new SQL statement, perform a wide table update and use the current source information as the new historical source information.

[0086] Finally, based on the new SQL statement above, perform a wide table update.

[0087] The aforementioned historical source information refers to the source information used during the last wide table update.

[0088] In some embodiments, for step S101 above, based on the running information and SQL statements of each wide table calculation task obtained above, the corresponding transformation value parameters are calculated for each wide table calculation task, and further compared with a preset threshold based on the transformation value parameters, and the wide table calculation task that needs to be transformed is selected, namely the target wide table calculation task.

[0089] As an optional implementation, the step of filtering the target wide table computation tasks to be modified based on the runtime information and SQL statements includes:

[0090] For each wide table computation task, based on the current wide table computation task's runtime information and SQL statements, and according to the computation value parameter calculation formula, the computation value parameter of the current wide table computation task is calculated; wherein, the computation value parameter calculation formula is:

[0091]

[0092] In the formula, v represents the transformation value parameter of the current wide table calculation task, and A i B represents the row number of the i-th current source table referenced by the current wide table computation task. iLet C represent the total number of rows in the i-th current source table referenced by the current wide table computation task, C represent the execution time of the current wide table computation task, D represent the number of CPUs used by the current wide table computation task, E represent the average CPU utilization of the current wide table computation task, F represent the average memory usage of the current wide table computation task, G represent the maximum memory usage of the current wide table computation task, n represent the total number of current source tables referenced by the current wide table computation task, and r represent the total number of rows in the current wide table computation task. i This represents the ratio of the number of current fields in the i-th current source table referenced by the current wide table calculation task to the total number of all fields.

[0093] The transformation value parameter of the current wide table computing task is compared with a preset threshold. If the transformation value parameter of the current wide table computing task is greater than the preset threshold, the current wide table computing task is determined to be the target wide table computing task.

[0094] When selecting target wide table calculation tasks to be modified, data acquisition is carried out in two streams: the above-mentioned running information and the above-mentioned SQL statements. The acquired data is then aggregated to calculate the above-mentioned modification value parameters.

[0095] It should be noted that the calculation method for the above-mentioned preset threshold can be determined according to the actual data distribution, and the embodiments of the present invention do not impose any limitations on it.

[0096] In some embodiments, the absolute value corresponding to any quantile of the transformation value parameter, such as the absolute value corresponding to the 75th quantile, can be selected as the aforementioned preset threshold. This means that only tasks whose transformation value parameter is in the top 25% can be selected as target wide table calculation tasks to be transformed.

[0097] In some embodiments, the absolute value corresponding to the 94th percentile, such as 128, can be selected as the preset threshold.

[0098] It should be noted that absolute values, rather than quantiles, should be used as the preset threshold. This is because as the transformation process progresses, the overall distribution of wide table computation tasks will change, and the number of wide table computation tasks that need to be transformed will decrease. If quantiles are used as the preset threshold, the filtering will not be accurate.

[0099] In some embodiments, cluster analysis can be performed on the above-mentioned modification value parameters to determine the dividing point, and the dividing point can be used as the above-mentioned preset threshold.

[0100] The clustering analysis described above can use the DBSCAN clustering algorithm (Density-Based Spatial Clustering of Applications with Noise), which defines clusters by density and can identify noise points that do not belong to any cluster.

[0101] In addition, in some embodiments, after calculating the modification value parameters of all wide table computing tasks, specific wide table computing tasks can be extracted at a 2% quantile point, and then the extracted wide table computing tasks can be analyzed to determine whether they need to be modified.

[0102] As an optional implementation, after filtering the target wide table calculation tasks to be modified based on the running information and SQL statements, the method further includes:

[0103] Rewrite the HiveSQL syntax file according to the preset syntax file rules;

[0104] The preset syntax file rules include:

[0105] Variable names support the insertion of {$xxx.xxx}; and / or,

[0106] Remove fine-grained statement parsing under functions and expressions.

[0107] To adapt to SparkSQL and PrestoSQL, and to accommodate scenarios with custom SQL parameters, the HiveSQL syntax file needs to be rewritten.

[0108] In some embodiments, the HiveSQL file is rewritten based on publicly available documentation.

[0109] The aforementioned preset syntax file rules may also include:

[0110] Variable names can be inserted using {$xxx.xxx};

[0111] It supports SQL column-to-row statements compatible with multiple mainstream computing engines, such as (hive)lateral view explode xxx and (presto)crossjson unnest.

[0112] It integrates special statements from multiple mainstream computing engines to avoid parsing errors;

[0113] Remove fine-grained statement parsing under functions and expressions.

[0114] By rewriting the HiveSQL syntax file according to the preset syntax file rules, it can not only be applied to the SQL syntax of multiple engines, but also avoid calling the platform's preprocessing service.

[0115] It should be noted that the above-mentioned operation of rewriting the HiveSQL syntax file according to the preset syntax file rules can be performed in the first step of step S102 above.

[0116] Regarding step S102 above, as an optional implementation, determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task includes:

[0117] The SQL statement of the target wide table calculation task is parsed to extract the current source table and current field referenced by the target wide table calculation task;

[0118] For each current source table, a corresponding current signature table is generated as the current source information; wherein, the current signature table includes the primary key of the current source table and the signature of the current field.

[0119] Create a backup table for each current source table referenced by the aforementioned target wide table calculation task. Each row of the backup table includes the primary key of the aforementioned current source table and the signature of the value of the current field referenced by the aforementioned target wide table calculation task.

[0120] Signing the fields used in the source table used by the wide table can speed up comparisons and reduce the memory required at runtime.

[0121] As an optional implementation, generating a corresponding current signature table for each current source table includes:

[0122] For each of the current source tables, a corresponding initial current signature table is created; the initial current signature table includes the primary key of the current source table;

[0123] Arrange the current fields referenced by the target wide table calculation task in alphabetical order and concatenate them into a string;

[0124] The string is transformed using the MD5 message digest algorithm. The first N characters of the MD5 value are selected and a prefix is ​​added as the field name. The MD5 value is then used as the field value to obtain the signature of the current field. Here, N is a positive integer.

[0125] The signature of the current field is inserted into the initial current signature table to generate the corresponding current signature table.

[0126] The above signature method involves concatenating field values ​​and then calculating the MD5 hash.

[0127] Additionally, it should be noted that the format of the current signature table can be as shown in Table 1 below:

[0128] (Original table fields...) Signature Algorithm ({Field Combination 1}) Signature Algorithm ({Field Combinations n}) ...

[0129] Table 1

[0130] The aforementioned signature algorithm is as follows: several current fields referenced by the target wide table calculation task are arranged in alphabetical order, then concatenated into a string using '+', and finally converted using the MD5 hash algorithm, with the first 12 characters plus the prefix 'SIGN' used as the field name of the backup table.

[0131] The aforementioned Message-Digest Algorithm (MD5) is a widely used cryptographic hash function that produces a 128-bit, or 16-byte, hash value to ensure the integrity and consistency of transmitted information.

[0132] When multiple wide tables use the same source table, a field combination signature is calculated based on the combination of fields used. If the signature does not exist, a new column is created.

[0133] Using the corresponding current signature table from the source table as a field combination as the signature can avoid duplicate signature calculations caused by the same field combination in different wide table calculation tasks, thereby shortening the calculation time and saving computing resources.

[0134] As an optional implementation, after determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, the method further includes:

[0135] Determine the end position of the last WITH clause as the insertion position of the WITH clause generated when the current source information in the SQL statement is replaced with the difference source information.

[0136] In some embodiments, the underlying implementation of step S102 above can be:

[0137] First, the SQL statement of the wide table calculation task is parsed, and the target table, source table, and related field information are extracted from it.

[0138] Specifically:

[0139] (1) Rewrite the HiveSQL syntax file according to the preset syntax file rules.

[0140] Please refer to the foregoing content; it will not be repeated here.

[0141] (2) Use the ANTLR4 parser generation tool library to generate a parser and a lexical analyzer.

[0142] (3) Perform lexical analysis, that is, use the above lexical analyzer to split the SQL statement into a series of tokens.

[0143] Each tag represents a keyword, identifier, or symbol.

[0144] (4) Construct an abstract syntax tree (AST), that is, use the parser described above to convert the result of lexical analysis into an abstract syntax tree.

[0145] The AST mentioned above is a tree-like data structure that represents the logical structure of the source code.

[0146] Each node in the above AST represents a construct, such as an expression, operator, or variable.

[0147] (5) Traversing the abstract syntax tree:

[0148] Traverse the AST using a custom Visitor class.

[0149] The Visitor pattern is a behavioral design pattern that is primarily used to represent operations applied to a set of object structures. It allows new operations to be defined without modifying the classes of these object structures.

[0150] When accessing each node, check the node type and extract relevant information.

[0151] Specifically, the target table, source table, their aliases, and related fields are extracted, and the position information of the source table in the syntax tree is marked.

[0152] Specifically:

[0153] Create a class named sqlVisitor that inherits from TsqlListener.

[0154] The TsqlListener class is the basic listener class generated by ANTLR4, providing an empty "enter" and "exit" method for each syntax rule. In this case, only the "enter" method is relevant, as it allows information to be extracted when accessing AST nodes.

[0155] Override the enterTable_name method, which will be called when a table name node is accessed.

[0156] Its function is to distinguish between the source table and the target table, and add them to the source_tables and target_table attributes respectively.

[0157] The underlying logic of the method is as follows: First, the table name of the current node is obtained by calling `ctx.getText()` and converted to lowercase. Then, the rule index of the parent context of the current node is checked. Here, the parent context refers to the level above the current node in the AST. The rule index of the parent context is obtained by `ctx.parentCtx.getRuleIndex()`. If the rule index is equal to `TsqlParser.RULE_from_clause`, it means that the current table name is a source table, and it is added to the `source_tables` attribute. If the rule index is equal to `TsqlParser.RULE_ddl_object`, it means that the current table name is a target table, and it is added to the `target_table` attribute.

[0158] Override the `enterColumn_name` method, which is called when a column name node is accessed. Its function is to extract the column name and the name of the table it belongs to, and add them to the `fields` property.

[0159] The underlying logic of the method is as follows: First, the column name of the current node is obtained by calling `ctx.getText()`. Then, the table name to which the column name belongs is found. This can be done by calling `ctx.parentCtx` multiple times to find the context node containing the table name. Finally, the table name is obtained by calling `getText()` and converting it to lowercase. Next, it checks if the current table name already exists in the `fields` property. If it doesn't exist, a new key-value pair is added, with the table name as the key and an empty list as the value. Finally, the current column name is appended to the list corresponding to the table name in the `fields` property.

[0160] Instantiate the sqlVisitor class, call its visit method, and pass the abstract syntax tree (AST) as a parameter. The visit method traverses the entire AST, and when it encounters a relevant node, it calls the corresponding enter method.

[0161] (6) Extract the future with insertion position information, that is, find the end position of the last with clause by traversing the AST. If there is no with clause, return 0.

[0162] Existing SQL parsing techniques do not locate the end of the WITH clause; instead, they repeatedly perform numerous replacements at the locations in the source table that need to be modified. In contrast, this invention pre-locates the location of the code to be inserted into the generated WITH clause.

[0163] Secondly, for each source table source_i, a signature backup table source_i_sign is created to store the signatures of the original table's primary key and the set of fields referenced by the wide table calculation task. At the same time, the same field combination is mapped to one signature to avoid duplicate calculations.

[0164] Specifically:

[0165] (1) Initialize the source_i_sign table so that it contains only the primary key field of source_i.

[0166] (2) Analyze the wide table calculation task, determine the set of fields referenced by the wide table calculation task, and arrange these fields in alphabetical order.

[0167] (3) Calculate the field combination signature. Concatenate the sorted field combinations into a string `fields_concat` using '+'. Then, use the MD5 hash algorithm to transform it, take the first 12 characters, add the prefix 'SIGN' as the field name `fields_sign`, and use `fields_concat` as the field comment. If the signature exists, skip this step; otherwise, add the field to the backup table.

[0168] (4) Calculate the table contents. Every day, a specific scheduling task concatenates the values ​​of the fields in the field description of source_i_sign according to the field combination, and then calculates the MD5 value. These MD5 values ​​will be used as the values ​​of the corresponding field combinations in the signature backup table, and inserted into the signature backup table along with the value of the primary key field to complete the data update of the current source_i_sign.

[0169] (5) Compare table records for updates. After updating the current source_i_sign content in the previous step, compare it with the previous source_i_sign content. Records with inconsistent signatures are marked as 1 in the {fields_sign}_diff field, otherwise it is 0.

[0170] In some embodiments, the underlying implementation of step S103 above can be:

[0171] In the SQL statement, replace the original source table with records whose reference values ​​have changed on the current day.

[0172] Specifically:

[0173] (1) To implement this replacement, first parse each subquery in the SQL statement and replace the original source table alias source_i with the signed source table alias source_i_diff.

[0174] (2) For each source table record, calculate the signature of the combination of referenced field names and find the corresponding {fields_sign}_diff field.

[0175] (3) Extract the primary key with the {fields_sign}_diff field of 1 from the signature backup table source_i_sign, and then perform an inner join operation with the source table. The resulting record is used as the result set to be updated.

[0176] (4) To avoid creating additional temporary tables, subqueries on the source table in the SQL statement are converted into WITH clauses so that the results of these subqueries can be reused throughout the query process.

[0177] This improves query performance while maintaining the readability and maintainability of SQL statements.

[0178] (5) When replacing the original source table, remove the judgment conditions related to the update_time field in the SQL statement.

[0179] Specifically, locate the source table in the SQL statement's syntax tree, find the WHERE clause in the same level statement; locate the node `node_x` that contains the smallest condition `update_time`; remove the `node_x` node and the previous token node (or the next token if none exists) from the syntax tree. If there are no nodes at the original level after removal, remove the WHERE clause.

[0180] like Figure 3 The diagram shown is a flowchart illustrating a process for generating new SQL statements according to an embodiment of the present invention.

[0181] First, the original SQL statement is parsed to obtain the fields used in the upper-level DWD table and their source tables. Then, the source tables are iterated to check if completion is complete. If so, the final SQL statement (the new SQL statement mentioned above) is generated; otherwise, a signature backup name is generated, and it is checked whether a corresponding signature backup table exists. If the signature backup table exists, the set of fields referenced by the target wide table calculation task is directly determined; otherwise, the signature backup table needs to be initialized first. Then, the field combination signature is calculated based on the field set, again checking if the field combination exists. If it exists, a syntax tree for query statements of records with inconsistent signatures in the signature backup table is generated; otherwise, the signature backup table fields and their comments are added, and then the aforementioned syntax tree is generated.

[0182] During the addition process, a special scheduling task is used to calculate the signature field of the signature backup table, then compare the table records for updates, and finally mark the records with inconsistent table signatures.

[0183] After generating the syntax tree, the syntax tree for the statements that perform inner join operations with the source table is generated and converted into with clauses and inserted into the original syntax tree. Then, the conditional statements related to update_time are removed.

[0184] Then continue iterating through the source table to see if it is complete, repeating this process until the source table is complete.

[0185] As an optional implementation, before performing the wide table update based on the new SQL statement, the method further includes:

[0186] Based on the new SQL statement, create and run a temporary task to obtain a temporary result table;

[0187] The temporary result table is compared with the correct result table. If the comparison results are correct for M consecutive times, the new SQL statement is determined to have passed the validation.

[0188] In this embodiment of the invention, after the SQL statement of the target wide table calculation task is modified, i.e., after obtaining the new SQL statement, a temporary task is created, and the result table name is replaced with a temporary table name. Then, the temporary task is run to obtain a temporary result table. Based on the data comparison between the temporary result table and the online result table, it is determined whether the modification is correct. Furthermore, the new SQL statement is confirmed to pass the verification based on M consecutive verifications. That is, if the verification test results are correct for M consecutive days, then the modification verification is determined to be successful; if the verification test results are not correct for M consecutive days, then the modification verification is determined to be unsuccessful.

[0189] As an optional implementation, after determining that the new SQL statement passes the validation, the method further includes:

[0190] For temporary tasks that have passed the modification verification, modify the temporary result table to the official result table, then replace the SQL statement of the target wide table calculation task with the new SQL statement, and then initiate a release request based on the new SQL statement.

[0191] Compared with existing technologies, this invention provides a method, apparatus, device, and computer-readable storage medium for updating wide tables. This method can identify wide table computation tasks requiring modification, avoiding manual analysis of massive wide table computation tasks. It can also automatically rewrite the SQL statements for wide table computation tasks, automatically compare the underlying data used, and filter out records with updates in the fields used. This effectively reduces unnecessary calculations and improves the operational efficiency and performance of the data warehouse. By using the method provided by this invention, intelligent modification of wide table computations in data warehouses can be achieved, reducing resource waste and increasing data processing speed. Furthermore, the method provided by this invention has high practicality and versatility, and can be widely applied to data warehouse management of various sizes and fields, helping enterprises save costs and improve competitiveness.

[0192] Meanwhile, the embodiments of the present invention do not rely on update time, and can also avoid update errors caused by update time, such as update time being null, business system not updating time synchronously when changing data, business system modifying the update time of all data in a column to the same time when refreshing a column of data, and business system hard deleting data, etc.

[0193] Example 2

[0194] Based on the above-described wide table update method, this embodiment of the invention provides a schematic diagram of the structure of a wide table update device, as shown below. Figure 4 As shown, the wide table update device 400 includes:

[0195] The target filtering unit 401 is used to obtain the running information and SQL statements of each wide table calculation task, and to filter the target wide table calculation tasks to be modified based on the running information and SQL statements.

[0196] The information determination unit 402 is used to determine the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, and to obtain historical source information; wherein, the current source information includes the current source table and the current field;

[0197] The difference replacement unit 403 is used to compare the current source information and the historical source information, determine the difference source information, and replace the current source information in the SQL statement with the difference source information to obtain a new SQL statement;

[0198] The wide table update unit 404 is used to update the wide table according to the new SQL statement and use the current source information as the new historical source information.

[0199] For other details regarding the implementation of the above technical solution by each module in the above wide table update device, please refer to the description in the wide table update method provided in the above embodiments of the invention, which will not be repeated here.

[0200] Based on the wide table update method described above, such as Figure 5 As shown in the diagram, this embodiment of the invention also provides a schematic diagram of a wide table update device 500, which includes a processor 501 and a memory 502 coupled to the processor 501. The memory 502 stores a computer program, which, when executed by the processor 501, causes the processor 501 to perform the steps of the wide table update method in the above embodiment.

[0201] For other details regarding the implementation of the above technical solution by the processor 501 in the wide table update device, please refer to the description in the wide table update method provided in the above embodiments of the invention, which will not be repeated here.

[0202] The processor 501 can also be called a CPU (Central Processing Unit). The processor 501 may be an integrated circuit chip with signal processing capabilities. The processor 501 may also be a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. The general-purpose processor may be a microprocessor, or the processor 501 may be any conventional processor.

[0203] like Figure 6 As shown in the diagram, this embodiment of the invention also provides a schematic diagram of a computer-readable storage medium 600, on which a readable computer program 601 is stored. The computer program 601 can be stored in the storage medium 600 in the form of a software product, including several instructions to cause a wide table update device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods described in various embodiments of the invention. The aforementioned storage medium 600 includes various media capable of storing program code, such as a USB flash drive, portable hard drive, magnetic disk or optical disk, ROM (Read-Only Memory), RAM (Random Access Memory), or terminal devices such as computers, servers, mobile phones, and tablets.

[0204] In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of modules is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be indirect coupling or communication connection through some interfaces, apparatuses, or modules, and may be electrical, mechanical, or other forms.

[0205] The modules described as separate components may or may not be physically separate. The components shown as modules may or may not be physical modules; that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0206] Furthermore, the functional modules in the various embodiments of this application can be integrated into one processing module, or each module can exist physically separately, or two or more modules can be integrated into one module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can be stored in a computer-readable storage medium.

[0207] In the above embodiments, implementation can be achieved, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, it can be implemented, in whole or in part, as a computer program product.

[0208] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions may be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can store or a data storage device such as a server or data center that integrates one or more available media. The available medium may be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid-state disk (SSD)).

[0209] The technical solutions provided in this application have been described in detail above. Specific examples have been used in this application to illustrate the principles and implementation methods of this application. The description of the above embodiments is only for the purpose of helping to understand the method and core ideas of this application. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

[0210] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0211] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0212] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0213] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0214] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A method for updating a wide table, characterized in that, include: Obtain the running information and SQL statements of each wide table calculation task, and filter the target wide table calculation tasks to be modified based on the running information and SQL statements; Based on the SQL statement of the target wide table calculation task, determine the current source information referenced by the target wide table calculation task, and obtain historical source information; wherein, the current source information includes the current source table and the current field; By comparing the current source information and the historical source information, the difference source information is determined, and the current source information in the SQL statement is replaced with the difference source information to obtain a new SQL statement; Based on the new SQL statement, perform a wide table update and use the current source information as the new historical source information; The step of filtering the target wide table calculation tasks to be modified based on the running information and SQL statements includes: For each wide table computation task, based on the current wide table computation task's runtime information and SQL statements, and according to the computation value parameter calculation formula, the computation value parameter of the current wide table computation task is calculated; wherein, the computation value parameter calculation formula is: Where v represents the transformation value parameter of the current wide table computing task, and A i B represents the row number of the i-th current source table referenced by the current wide table computation task. i Let C represent the total number of rows in the i-th current source table referenced by the current wide table computation task, C represent the execution time of the current wide table computation task, D represent the number of CPUs used by the current wide table computation task, E represent the average CPU utilization of the current wide table computation task, F represent the average memory usage of the current wide table computation task, G represent the maximum memory usage of the current wide table computation task, n represent the total number of current source tables referenced by the current wide table computation task, and r represent the total number of rows in the current wide table computation task. i This represents the ratio of the number of current fields in the i-th current source table referenced by the current wide table calculation task to the total number of all fields. The transformation value parameter of the current wide table computing task is compared with a preset threshold. If the transformation value parameter of the current wide table computing task is greater than the preset threshold, the current wide table computing task is determined to be the target wide table computing task.

2. The wide table update method according to claim 1, characterized in that, The step of determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task includes: The SQL statement of the target wide table calculation task is parsed to extract the current source table and current field referenced by the target wide table calculation task; For each current source table, a corresponding current signature table is generated as the current source information; wherein, the current signature table includes the primary key of the current source table and the signature of the current field.

3. The wide table update method according to claim 2, characterized in that, The step of generating a corresponding current signature table for each current source table includes: For each of the current source tables, a corresponding initial current signature table is created; the initial current signature table includes the primary key of the current source table; Arrange the current fields referenced by the target wide table calculation task in alphabetical order and concatenate them into a string; The string is transformed using the MD5 message digest algorithm. The first N characters of the MD5 value are selected and a prefix is ​​added as the field name. The MD5 value is then used as the field value to obtain the signature of the current field. Here, N is a positive integer. The signature of the current field is inserted into the initial current signature table to generate the corresponding current signature table.

4. The wide table update method according to claim 1, characterized in that, After determining the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, the method further includes: Determine the end position of the last WITH clause as the insertion position of the WITH clause generated when the current source information in the SQL statement is replaced with the difference source information.

5. The wide table update method according to claim 1, characterized in that, After filtering the target wide table calculation tasks to be modified based on the runtime information and SQL statements, the process also includes: Rewrite the HiveSQL syntax file according to the preset syntax file rules; The preset syntax file rules include: Variable names support the insertion of {$xxx.xxx}; and / or, Remove fine-grained statement parsing under functions and expressions.

6. The wide table update method according to claim 1, characterized in that, Before performing the wide table update based on the new SQL statement, the process also includes: Based on the new SQL statement, create and run a temporary task to obtain a temporary result table; The temporary result table is compared with the correct result table. If the comparison results are correct for M consecutive times, the new SQL statement is determined to have passed the validation.

7. A wide table updating device, characterized in that, include: The target filtering unit is used to obtain the running information and SQL statements of each wide table calculation task, and to filter the target wide table calculation tasks to be modified based on the running information and SQL statements. The step of filtering the target wide table calculation tasks to be modified based on the running information and SQL statements includes: For each wide table computation task, based on the current wide table computation task's runtime information and SQL statements, and according to the computation value parameter calculation formula, the computation value parameter of the current wide table computation task is calculated; wherein, the computation value parameter calculation formula is: Where v represents the transformation value parameter of the current wide table computing task, and A i B represents the row number of the i-th current source table referenced by the current wide table computation task. i Let C represent the total number of rows in the i-th current source table referenced by the current wide table computation task, C represent the execution time of the current wide table computation task, D represent the number of CPUs used by the current wide table computation task, E represent the average CPU utilization of the current wide table computation task, F represent the average memory usage of the current wide table computation task, G represent the maximum memory usage of the current wide table computation task, n represent the total number of current source tables referenced by the current wide table computation task, and r represent the total number of rows in the current wide table computation task. i This represents the ratio of the number of current fields in the i-th current source table referenced by the current wide table calculation task to the total number of all fields. The transformation value parameter of the current wide table computing task is compared with a preset threshold. If the transformation value parameter of the current wide table computing task is greater than the preset threshold, the current wide table computing task is determined to be the target wide table computing task. The information determination unit is used to determine the current source information referenced by the target wide table calculation task based on the SQL statement of the target wide table calculation task, and to obtain historical source information; wherein, the current source information includes the current source table and the current field; The difference replacement unit is used to compare the current source information and the historical source information, determine the difference source information, and replace the current source information in the SQL statement with the difference source information to obtain a new SQL statement. The wide table update unit is used to update the wide table based on the new SQL statement and use the current source information as the new historical source information.

8. A wide table update device, characterized in that, Includes memory and processor, wherein: The memory is used to store computer programs; The processor is used to read the computer program in the memory and execute the steps of any of the wide table update methods as described in claims 1 to 6.

9. A computer-readable storage medium, characterized in that, It stores a readable computer program that, when executed by a processor, implements the steps of any of the wide table update methods as described in claims 1 to 6.

Images