Improved Method for Automatically Creating and Updating the Structure of a Data Synchronization Wide Table

Abstract

The present invention discloses an improved method for automatically creating and updating the structure of a data synchronization wide table, including the first step of creating and updating the wide table structure of multiple storage bodies by configuring the schema metadata of table configuration and its associated information in a database; the second step of automatically generating the required wide table fields according to best practice rules; the third step of reducing redundant configuration; the fourth step of customizing the types of specific fields in the output stream; the fifth step of adding a table custom attribute table; the sixth step of associating the table information table involved in the task wide table; and the seventh step of configuring the directory name and database name to which the table belongs. The present invention efficiently and automatically creates and updates the wide table structure of multiple storage bodies by configuring the schema metadata of table configuration and its associated information in the database, while automatically generating the required wide table fields, standardizing the setting rules of wide table fields, reducing redundant configuration, improving configuration efficiency, and reducing inconsistencies.

Description

Technical Field

[0001] The present invention relates to the field of big data real-time stream dimension table queries, and particularly to an improved method for automatically creating and updating the structure of a data synchronization wide table. Background Art

[0002] A big data real-time data warehouse is a data warehouse system that can process and analyze large-scale data in real time. It combines big data technology and real-time data processing technology, can meet rapidly changing business needs, and supports real-time data analysis and decision-making.

[0003] However, at present, the synchronization of table fields is limited to single tables and cannot adapt to wide tables generated by multi-table associations. It is necessary to manually correspond to each field one by one or only perform selection operations on the web interface, without automatically associating fields according to certain rules. Moreover, in existing databases, media and storage bodies are both classified as storage bodies, resulting in a lot of redundant configurations of the same things. In addition, the partition and bucket configurations of multi-key value type fields are redundant. Through such repeated and redundant configurations, the configuration efficiency is reduced and inconsistency is increased. Summary of the Invention

[0004] The purpose of the present invention is to provide an improved method for automatically creating and updating the structure of a data synchronization wide table to solve the problems raised in the above background art.

[0005] To achieve the above purpose, the present invention provides the following technical solutions: an improved method for automatically creating and updating the structure of a data synchronization wide table, the improved method comprising the following steps;

[0006] The first step is to create and update the wide table structures of multiple storage bodies by configuring the schema metadata of table configurations and their associated information in the database.

[0007] The second step is to automatically generate the required wide table fields according to best practice rules.

[0008] The third step is to reduce redundant configurations by splitting media and storage bodies into a storage body media table and a metadata storage body table, and then adding a metadata information table to form the core base.

[0009] The fourth step is to customize the types of specific fields in the output stream by adding a Flink task wide table specific field type conversion table to define the fields to be converted and the types to be converted, decoupling the strong association between the field types of the output stream and the input stream.

[0010] The fifth step is to add a table custom attribute table, and support attribute custom configuration, attribute encryption / decryption, and replacement.

[0011] Step 6: Split the table association information table involved in the task wide table into the table association information involved in the task wide table and the metadata storage body, and parse the corresponding wide table of the task and its association relationship;

[0012] Step 7: Configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage body table.

[0013] Preferably, the storage body in the first step includes Flink, Hudi, and Doris.

[0014] Preferably, after reducing the redundant configuration operations in the third step, when other tables are distinguished at three progressive levels of metadata, storage body, and medium, use the primary key of one of the corresponding tables as a foreign key for targeted configuration.

[0015] Preferably, after the progressive levels are distinguished, improve the associated tables, including splitting the medium classification from the storage body classification, ensuring that only the configuration of the table template attributes, table template fields, and table custom attributes and the association with the storage body medium table need to be configured according to different media.

[0016] Preferably, the reduction of redundant configuration also includes reducing the redundancy of partition and bucket configuration, and moving the key fields and key types of the metadata storage body table to the new metadata key value table.

[0017] Preferably, in the fifth step, the attribute custom configuration is to split the table template attributes from the table template and add a table custom attribute table to separate the field and attribute configurations.

[0018] Preferably, in the sixth step, parsing the corresponding wide table of the task and its association relationship is to query the metadata information of the wide table and the association relationship of its associated tables, as well as the information of the corresponding associated tables, and the storage body, medium, corresponding template, key value, custom fields, and attribute information to which the wide table needs to be synchronized.

[0019] Preferably, the acquisition of the field information required by the wide table includes the following steps;

[0020] A1. Obtain the field name information required by the wide table based on the wide table and its association relationship and the information of the corresponding associated tables;

[0021] A2. Unify the field formats of the fact tables;

[0022] A3. Perform addition and subtraction processing on specific fields;

[0023] A4. Obtain the complete field information of the types and lengths of the fields corresponding to the field names required by the wide table.

[0024] Preferably, after obtaining the required field information of the wide table, the wide table structure is set, and the structures corresponding to different storage bodies of the wide table are created.

[0025] Preferably, after the wide table structure is set, create and update statement settings and execute them. The operations performed include the following steps:

[0026] B1. Set table attributes, key values, and partition and bucket information;

[0027] B2. Determine whether to create or update, including checking if there is a table in the corresponding storage body. If the table does not exist, prepare to create the table. If it exists, obtain the old table structure;

[0028] B3. Create a data table operation statement, including determining whether to create, setting table attributes, finding the corresponding table template, and replacing the placeholders inside to create the corresponding table creation and update statements;

[0029] B4. Execute the statement.

[0030] The technical effects and advantages of the present invention:

[0031] The present invention efficiently and automatically creates and updates the wide table structures of multiple storage bodies by configuring the schema metadata of the table configuration and its associated information in the database. At the same time, this method can automatically generate the required wide table fields, standardize the wide table field setting rules, thereby reducing redundant configurations, improving configuration efficiency, and reducing inconsistencies; at the same time, the settings of this method support custom configuration of attributes, attribute encryption and decryption, and replacement, thereby ensuring data field consistency and correct and fast operation processes. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a flowchart of the operation of the improved method of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0033] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without making creative efforts shall fall within the protection scope of the present invention.

[0034] The present invention provides an improved method for automatically creating and updating the data synchronization wide table structure as Figure 1 shown, and the improved method includes the following steps:

[0035] The first step is to create and update the wide table structures of multiple storage bodies by configuring the schema metadata of the table configuration and its associated information in the database;

[0036] Specifically, the storage body in the first step includes Flink, Hudi, and Doris.

[0037] In the second step, according to the best practice rules, automatically generate the required wide table fields;

[0038] In the third step, reduce redundant configurations, split the medium and storage body into a storage body medium table and a metadata storage body table, and then add a metadata information table to form the core base;

[0039] Specifically, after the operation of reducing redundant configurations in the third step, when other tables distinguish among the three progressive levels of metadata, storage body, and medium, use the primary key of one of the corresponding tables as a foreign key for targeted configuration.

[0040] Furthermore, after distinguishing the progressive levels, improve the associated tables, including splitting the medium classification from the storage body classification, and ensuring that only the configurations related to the table template attributes, table template fields, and table custom attributes associated with the storage body medium table need to be configured according to different media.

[0041] Even further, reducing redundant configurations also includes reducing the redundancy of partition and bucket configurations, and moving the key fields and key types of the metadata storage body table to a new metadata key value table.

[0042] In the fourth step, customize the types of specific fields in the output stream. By adding a Flink task wide table specific field type conversion table, define the fields whose types are to be converted and the converted types, decoupling the strong association between the field types of the output stream and the input stream;

[0043] In the fifth step, add a table custom attribute table, and support attribute custom configuration, attribute encryption / decryption, and replacement;

[0044] Specifically, in the fifth step, the attribute custom configuration is to split the table template attributes from the table template and add a table custom attribute table to separate the field and attribute configurations.

[0045] In the sixth step, split the table association information table related to the task wide table into the table association information related to the task wide table and the metadata storage body, and parse the corresponding wide table of the task and its association relationship;

[0046] Specifically, in the sixth step, parsing the corresponding wide table of the task and its association relationship is to query the metadata information of the wide table and the association relationship of its associated tables, as well as the information of the corresponding associated tables through the task name, and the storage body, medium, corresponding template, key value, custom fields, and attribute information to which the wide table needs to be synchronized.

[0047] In the seventh step, configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage body table.

[0048] Specifically, the acquisition of the required field information for the wide table includes the following steps;

[0049] A1. Obtain the required field name information for the wide table based on the wide table, its association relationships, and the information of the corresponding associated table;

[0050] Furthermore, determine whether the field name information is a key value, and if so, retain it. If the addition mode of the associated table involved in the wide table is the supplement mode, the required fields for the wide table are all fields of this associated table. If the update mode of the associated table involved in the wide table is the supplement mode, the required fields for the wide table are the combination of the columns with immutable values in this associated table and the set of key fields recorded in the corresponding metadata storage body information table, after deduplication.

[0051] A2. Standardize the field formats of the fact table to standardize the storage of fields with non-standardized field names into the table, facilitating the finding of duplicate fields, and convert the fields in the fact table from camel case to underscores.

[0052] A3. Perform addition and deletion processing on specific fields;

[0053] Furthermore, obtain the specific field addition and deletion table for the task-wide table corresponding to the task according to the task name, and obtain the records of the fields to be deleted.

[0054] According to the records, add, delete, and rename the required field names for the wide table. Renaming is generally used to handle fields with the same name.

[0055] A4. Obtain the complete field information of the types and lengths of the fields corresponding to the required field names for the wide table.

[0056] Furthermore, according to the wide table, its association relationships, and the name of the corresponding associated table, obtain the complete field information from the catalog table structure in Flink;

[0057] If the information is incomplete, then connect to the data source where the associated table is located to obtain the complete field information;

[0058] Find the fields to be converted according to the specific field type conversion table for the task-wide table. If they exist, convert them to the corresponding field types.

[0059] Furthermore, after obtaining the required field information for the wide table, set the wide table structure and create the structures of the wide table corresponding to different storage bodies.

[0060] It should be noted that creating the structures of the wide table corresponding to different storage bodies (such as Hudi, Doris, etc.) and media (such as Flink, Spark, etc.) includes:

[0061] Set fields, set the complete field information into the corresponding table structure, and the field type corresponds according to the table storage body;

[0062] Set the key and partition type, and obtain the required key-value fields according to the metadata storage body and the metadata key-value table records, such as the id of the metadata information table, the storage body type, the key field, the key type, the type of table partition, and the type information of table bucketing.

[0063] Furthermore, after the wide table structure is set up, create and update statements are set and executed, and the operations performed include the following steps;

[0064] B1, set the table attributes, key values, and partition and bucketing information;

[0065] B2, determine whether to create or update, including checking whether there is a table in the corresponding storage body. If the table does not exist, prepare to create a table. If it exists, obtain the old table structure;

[0066] Specifically, compare the new table structure to see if there are any field changes. If there are changes, record the field structure information that needs to be changed and prepare for an update; if there are no changes, do nothing.

[0067] B3, create the data table operation statement, including determining whether to create, setting the table attributes, finding the corresponding table template, and replacing the placeholders inside to create the corresponding table creation and update statements;

[0068] It should be noted that when setting the table attributes, find the corresponding template and custom configuration from the table template attributes and the table custom attribute table. According to the custom attributes, change the corresponding template attributes. If there are placeholders in the attributes, replace them with the attribute values in the configuration file. If there is an encryption and decryption configuration, perform encryption and decryption according to the rules and then replace.

[0069] B4, execute the statement.

[0070] It should be noted that the purpose of the data table:

[0071] Metadata Information Table: The required relevant information of the table in the data warehouse. Metadata Association Information Table: The association relationship information between tables. Task Wide Table Association Information Table: The corresponding relationship between tasks and wide tables. Table Association Information Table Involved in Task Wide Table: The metadata association information involved in the wide table corresponding to the task. Task Wide Table Specific Field Increase / Decrease Table: Tasks for deleting, adding, and renaming specific fields based on general rules, such as deleting and renaming fields with the same name in associated tables. Task Wide Table Specific Field Type Conversion Table: Decouple the strong association between the field types of the output stream and the input stream, and customize the types of specific fields in the output stream. Table Custom Attribute Table: Support custom configuration of table attributes in different media, and support sorting and encryption / decryption configuration. Metadata Storage Body Information Table: Used to set the structure and configuration information specific to the storage body for different storage bodies. Storage Body Medium Table: Define the combination relationship between the storage body and the medium. Metadata Key-Value Table: Define the key-value fields, types, and orders of the table corresponding to the storage body. Table Template Table: Used to record the create / update table templates for different media. Table Template Attribute Table: Used to record the table attribute templates for different media, and support sorting and encryption / decryption configuration.

[0072] Among them, the specific table structure information is as follows:

[0073] Metadata Information Table: Primary key, original table name, and data warehouse layer type, 1: ods table, 2: dwd wide table, addition mode, 0: append, 1: upsert, columns with immutable values;

[0074] Metadata Association Information Table: Primary key, foreign key, associated data fields, relationship model, id of the Metadata Information Table, id of the associated Metadata Information Table;

[0075] Task Wide Table Association Information Table: Primary key, task name, id of the wide table (corresponding to the id of the Metadata Information Table);

[0076] Table Association Information Table Involved in Task Wide Table: Primary key, id of the Flink task wide table association information table, id of the involved metadata table information table, whether it is a time temporary association, and sorting number;

[0077] Task Wide Table Specific Field Increase / Decrease Table: Primary key, id of the Flink task wide table association information table, id of the involved metadata association information table, field set to be excluded, field set to be retained, field set to be renamed;

[0078] Task Wide Table Specific Field Type Conversion Table: Primary key, id of the Flink task wide table association information table, id of the metadata storage body table, field whose type is to be converted, converted type;

[0079] Table Custom Attribute Table: Primary key, ID of the metadata storage table, ID of the storage medium table, operation type, 1: upsert 2: delete, key of the attribute value, value of the attribute value, encryption type, sorting number;

[0080] Metadata Storage Body Information Table: Primary key, ID of the metadata information table, storage body type, 0: mysql, 1: hudi, 2: doris, directory name, database name, type of table partitioning, 0: non-partitioned, 1: field value, 2: RANGE range, 3: List list, type of table bucketing, 0: non-bucketed, 1: HASH, 2: RANDOM;

[0081] Storage Medium Table: Primary key, storage body type, media type, 0: storage body, 1: flink, 2: spark;

[0082] Metadata Key-Value Table: Primary key, ID of the metadata storage table, key field, key type, 0: unique primary key, 1: duplicate primary key, 2: partition key, 3: bucket key, sorting number;

[0083] Table Template Table: Primary key, ID of the storage medium table, table statement template, template type: 1: create table, 2: add field, 3: delete field; 4: modify field;

[0084] Table Template Table: Primary key, ID of the storage medium table, key of the attribute value, value of the attribute value, encryption type, sorting number.

[0085] Finally, it should be noted that the above are only the preferred embodiments of the present invention and are not used to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions recorded in the foregoing embodiments or perform equivalent replacements for some of the technical features. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. An improved method for automatically creating and updating the structure of a data synchronization wide table, characterized in that, The improved method includes the following steps; In the first step, create and update the wide table structure of multiple storage bodies by configuring the schema metadata and its associated information table configured in the database; In the second step, automatically generate the required wide table fields according to the best practice rules; In the third step, reduce redundant configurations, split the medium and storage body into a storage body medium table and a metadata storage body table, and then add a metadata information table to form the core base; In the fourth step, customize the types of specific fields in the output stream. By adding a wide table specific field type conversion table for the flink task, define the fields to be converted and the converted types, decoupling the strong association between the field types of the output stream and the input stream; In the fifth step, add a table custom attribute table, and support attribute custom configuration, attribute encryption and decryption, and replacement; In the sixth step, split the table association information table involved in the task wide table into the table association information and metadata storage body involved in the task wide table, and parse the corresponding wide table and its association relationship of the task; In the seventh step, configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage body table.

2. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 1, wherein The storage bodies in the first step include flink, hudi, and doris.

3. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 1, wherein After the redundant configuration operation in the third step, when other tables are distinguished at three progressive levels of metadata, storage body, and medium, use the primary key of one of the corresponding tables as a foreign key for targeted configuration.

4. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 3, characterized in that, After the progressive levels are distinguished, improve the associated tables, including splitting the medium classification from the storage body classification, ensuring that only the configurations related to the table template attributes, table template fields, and table custom attributes and the storage body medium table need to be configured according to different media.

5. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 4, characterized in that, The reduction of redundant configuration also includes reducing the redundancy of partition and bucket configuration, and moving the key fields and key types of the metadata storage body table to the new metadata key value table.

6. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 1, characterized in that, The attribute custom configuration in the fifth step is to split the table template attributes from the table template and add a table custom attribute table to separate the field and attribute configurations.

7. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 1, characterized in that, The parsing of the corresponding wide table and its association relationship in the sixth step is to query the metadata information of the wide table and the association relationship of the associated tables, as well as the information of the corresponding associated tables through the task name, and the storage body, medium, corresponding template, key value, custom field, and attribute information to which the wide table needs to be synchronized.

8. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 7, characterized in that, The acquisition of the required field information for the wide table includes the following steps; A1, obtain the required field name information for the wide table according to the wide table and its association relationship and the information of the corresponding associated tables; A2, unify the field formats of the fact tables; A3, perform addition and subtraction processing on specific fields; A4, obtain the complete field information of the types and lengths of the fields corresponding to the required field names of the wide table.

9. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 8, characterized in that, After the required field information of the wide table is obtained, set the wide table structure, and create the structures of the wide table corresponding to different storage bodies.

10. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 9, characterized in that, After the wide table structure setting is completed, create and update the statement settings and execute them. The operations performed include the following steps; B1, set the table attributes, key values, and partition and bucket information; B2. Determine whether to create or update, including checking whether there is a table in the corresponding storage. If the table does not exist, prepare to create the table. If it exists, obtain the old table structure; B3. Create a data table operation statement, including determining whether to create, setting table attributes, finding the corresponding table template, and replacing placeholders in it to create the corresponding table creation and update statements; B4. Execute the statement.

Images