Improved method for automatically creating and updating data synchronization wide table structure

Abstract

The present application discloses an improved method for automatically creating and updating a data synchronization wide table structure, comprising: step 1, creating and updating a wide table structure of a plurality of memory banks by configuring, in a database, schema metadata of table configuration and an associated information configuration table thereof; step 2, automatically generating a required wide table field on the basis of an optimal practice rule; step 3, reducing redundant configuration; step 4, customizing the type of a specific field of an output stream; step 5, adding a table custom attribute table; step 6, splitting a table associated information table involved in a task wide table into table associated information and metadata memory banks involved in the task wide table, and parsing a wide table corresponding to a task and an associated task thereof; and step 7, configuring a directory name and a database name to which the table belongs. The present application efficiently and automatically creates and updates the wide table structure of the plurality of memory banks by configuring, in the database, the schema metadata of the table configuration and the associated information configuration table thereof, and at the same time, automatically generates the required wide table field, standardizes a wide table field configuration rule, reduces the redundant configuration, improves configuration efficiency, and reduces inconsistency.

Description

Improved method for automatically creating and updating data synchronization wide table structure

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application claims priority to the Chinese patent application filed with the China Patent Office on December 14, 2023, with application number 202311722358.0 and invention name “Improved Method for Automated Creation and Update of Data Synchronization Wide Table Structure”, the entire contents of which are incorporated by reference into this application. Technical Field

[0003] The present application relates to the field of real-time streaming dimension table query for big data, and in particular to an improved method for automatically creating and updating a data synchronization wide table structure. Background Art

[0004] A big data real-time data warehouse is a data warehouse system capable of processing and analyzing large amounts of data in real time. Combining big data technologies with real-time data processing techniques, it can meet rapidly changing business needs and support real-time data analysis and decision-making.

[0005] However, the current table field synchronization is limited to a single table and cannot adapt to the wide table generated by multi-table association. It requires manual matching of each field one by one or is limited to selection operations in the web interface. There are no specific rules for automatically associating fields. In addition, existing databases classify media and storage bodies as storage bodies, resulting in many identical items requiring repeated configuration. In addition, the partitioning and bucketing configurations of multi-key value type fields are redundant. This repeated and redundant configuration reduces configuration efficiency and increases inconsistency. Summary of the Invention

[0006] The purpose of this application is to provide an improved method for automatically creating and updating a data synchronization wide table structure to solve the problems raised in the above background technology.

[0007] To achieve the above objectives, the present application provides the following technical solutions: an improved method for automatically creating and updating a data synchronization wide table structure, the improved method comprising the following steps:

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

[0009] The second step is to automatically generate the required wide table fields based on best practice rules.

[0010] The third step is to reduce redundant configurations by splitting media and storage into a storage medium table and a metadata storage table, and then adding a metadata information table to form the core foundation.

[0011] 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, you can define the fields to be converted and the conversion types, thereby decoupling the strong association between the field types of the output stream and the input stream.

[0012] Step 5: Add a custom attribute table, and support custom attribute configuration, attribute encryption, decryption, and replacement;

[0013] Step 6: Split the table association information table involved in the task wide table into the table association information and metadata storage volume involved in the task wide table, and parse the wide tables corresponding to the task and their associations.

[0014] The seventh step is to configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage table.

[0015] Optionally, the storage in the first step includes flink, hudi, and doris.

[0016] Optionally, after the redundancy reduction configuration operation in the third step, when distinguishing other tables at the three progressive levels of metadata, storage body and medium, the primary key of one of the corresponding tables is used as a foreign key for targeted configuration.

[0017] Optionally, after the progressive level is differentiated, the association table is improved, including splitting the media classification from the storage body classification to ensure that only the table template attributes, table template fields and table custom attribute configurations are associated with the storage body media table and need to be configured according to different media.

[0018] Optionally, reducing redundant configuration further includes reducing partition and bucket configuration redundancy, and moving key fields and key types of the metadata storage body table to a new metadata key-value table.

[0019] Optionally, the attribute customization configuration in the fifth step is to separate the table template attributes from the table template and add a table customization attribute table to separate the field and attribute configuration.

[0020] Optionally, in the sixth step, the wide table corresponding to the task and its associated relationship are parsed by looking up the metadata information of the wide table and its associated table's associated relationship through the task name, as well as the information of the corresponding associated table, and the storage body, medium, corresponding template, key value, custom field and attribute information to which the wide table needs to be synchronized.

[0021] Optionally, obtaining the field information required for the wide table includes the following steps:

[0022] A1 obtains the required field names for the wide table based on the wide table, its relationships, and the information of the corresponding associated tables.

[0023] A2: Standardize the format of fact table fields;

[0024] A3, adds or subtracts specific fields;

[0025] A4 obtains complete field information, including the type and length of the field names required by the wide table.

[0026] Optionally, after the field information required for the wide table is obtained, the wide table structure is set, and a structure in which the wide table corresponds to different storage bodies is created.

[0027] Optionally, after the wide table structure is set, create and update statements are set and executed, and the execution operation includes the following steps:

[0028] B1: Set table properties, key values, and partition and bucket information.

[0029] 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 the table. If it does exist, obtain the old table structure;

[0030] B3: Create data table operation statements, including determining whether to create, setting table properties, searching for the corresponding table template, and replacing placeholders in the template to create the corresponding table creation and update statements.

[0031] B4, execute the statement.

[0032] The technical effects and advantages of this application are:

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

[0034] FIG1 is an operational flow chart of the improved method of the present application. DETAILED DESCRIPTION

[0035] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0036] This application provides an improved method for automatically creating and updating a data synchronization wide table structure as shown in FIG1 , and the improved method includes the following steps:

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

[0038] Specifically, the storage bodies in the first step include flink, hudi, and doris.

[0039] The second step is to automatically generate the required wide table fields based on best practice rules.

[0040] The third step is to reduce redundant configurations by splitting media and storage into a storage medium table and a metadata storage table, and then adding a metadata information table to form the core foundation.

[0041] Specifically, after the redundancy reduction configuration operation in the third step, when distinguishing other tables at the three progressive levels of metadata, storage body, and media, the primary key of one of the corresponding tables is used as a foreign key for targeted configuration.

[0042] Furthermore, after differentiation at the progressive level, the association table is improved, including separating the media classification from the storage body classification, ensuring that only the configuration of table template attributes, table template fields and table custom attribute configurations associated with the storage body media table need to be configured according to different media.

[0043] Furthermore, reducing redundant configuration also includes reducing partition and bucket configuration redundancy and moving the key fields and key types of the metadata storage table to a new metadata key-value table.

[0044] 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, you can define the fields to be converted and the conversion types, thereby decoupling the strong association between the field types of the output stream and the input stream.

[0045] Step 5: Add a custom attribute table, and support custom attribute configuration, attribute encryption, decryption, and replacement;

[0046] Specifically, the attribute customization configuration in the fifth step is to separate the table template attributes from the table template and add a table customization attribute table to separate the field and attribute configuration.

[0047] Step 6: Split the table association information table involved in the task wide table into the table association information and metadata storage volume involved in the task wide table, and parse the wide tables corresponding to the task and their associations.

[0048] Specifically, in the sixth step, the wide table corresponding to the task and its associated relationships are parsed by looking up the metadata information of the wide table and its associated relationships through the task name, as well as the information of the corresponding associated tables, and the storage body, media, corresponding templates, key values, custom fields, and attribute information that the wide table needs to be synchronized to.

[0049] The seventh step is to configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage table.

[0050] Specifically, obtaining the required field information for a wide table includes the following steps:

[0051] A1 obtains the required field names for the wide table based on the wide table, its relationships, and the information of the corresponding associated tables.

[0052] Optionally, determine whether the field name information is a key value. If it is, retain it. If the wide table's associated table is added in append mode, the required field names for the wide table are all fields in the associated table. If the wide table's associated table is updated in append mode, the required field names for the wide table are the columns in the associated table whose values ​​are immutable, and the key field set of the corresponding metadata storage information table record, after deduplication.

[0053] A2: Standardize the format of fact table fields to store non-standardized field names in the table in a standardized manner, making it easier to find duplicate fields. Also, convert camel case to underscores in fact table fields.

[0054] A3, adds or subtracts specific fields;

[0055] Optionally, obtain the task wide table specific field increase and decrease table corresponding to the task based on the task name, and obtain the records of the fields that need to be deleted.

[0056] According to the records, add or delete the field names required for the wide table. Renaming is generally used to process fields with the same name.

[0057] A4 obtains complete field information, including the type and length of the field names required by the wide table.

[0058] Optionally, based on the wide table, its associations, and the names of the corresponding associated tables, obtain complete field information from the Flink catalog table structure.

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

[0060] Find the field to be converted according to the task wide table specific field type conversion table. If it exists, convert it to the corresponding field type.

[0061] Furthermore, after the field information required for the wide table is obtained, the wide table structure is set, and structures corresponding to different storage bodies of the wide table are created.

[0062] It should be noted that the structures for creating wide tables corresponding to different storage bodies (such as Hudi, Doris, etc.) and media (such as Flink, Spark, etc.) include:

[0063] Set the field, set the complete field information to the corresponding table structure, and the field type corresponds to the table storage body;

[0064] Set the key and partition type, and obtain the required key-value fields based on the metadata storage body and metadata key-value table records, such as the metadata information table ID, storage body type, key field, key type, table partition type, and table bucket type information.

[0065] Furthermore, after the wide table structure is set up, create and update statements are set up and executed. The execution includes the following steps:

[0066] B1: Set table properties, key values, and partition and bucket information.

[0067] 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 the table. If it does exist, obtain the old table structure;

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

[0069] B3: Create data table operation statements, including determining whether to create, setting table properties, searching for the corresponding table template, and replacing placeholders in the template to create the corresponding table creation and update statements.

[0070] It should be noted that to set table properties, find the corresponding template and custom configuration from the table template properties and table custom properties table, and change the corresponding template properties according to the custom properties. If there are placeholders in the properties, replace them with the property values ​​in the configuration file. If there are encryption and decryption configurations, encrypt and decrypt them according to the rules before replacing them.

[0071] B4, execute the statement.

[0072] It should be noted that the data table is used for:

[0073] Metadata Information Table: Information required for tables in the data warehouse. Metadata Association Information Table: Information on associations between tables. Task-Wide Table Association Information Table: Information on the correspondence between tasks and wide tables. Table Association Information Table Related to Task-Wide Tables: Information on metadata associations related to the wide tables corresponding to tasks. Task-Wide Table Specific Field Addition and Deletion Table: This table performs tasks such as deleting, adding, subtracting, and renaming specific fields based on general rules, such as deleting and renaming fields with the same name in related tables. Task-Wide Table Specific Field Type Conversion Table: This table decouples the strong association between the field types of the output stream and the input stream, allowing customization of the types of specific fields in the output stream. Table Custom Attribute Table: This table supports custom configuration of table attributes for different media, including sorting and encryption / decryption configuration. Metadata Storage Body Information Table: This table is used to set storage-specific structures and configuration information for different storage bodies. Storage Body Medium Table: This table defines the association between storage bodies and media. Metadata Key Value Table: This table defines the key value fields, types, and order of the storage bodies corresponding to the tables. Table Template Table: This table records the creation and update of table templates for different media. Table template attribute table: used to record table attribute templates for different media, supporting sorting and encryption and decryption configuration.

[0074] The specific table structure information is as follows:

[0075] Metadata information table: primary key, original table name, and data warehouse layer type, 1: ods table, 2: dwd wide table, add mode, 0: append, 1: upsert, columns with immutable values;

[0076] Metadata association information table: primary key, foreign key and associated data fields, relational model, metadata information table ID, and associated metadata information table ID;

[0077] Task wide table association information table: primary key, task name, wide table ID (corresponding to the metadata information table ID);

[0078] Table association information table involved in the task wide table: primary key, ID of the Flink task wide table association information table, ID of the metadata table involved, whether it is a temporary time association, and sort number;

[0079] Task-wide table specific field addition and deletion table: primary key, ID of the Flink task-wide table association information table, ID of the metadata association information table involved, set of fields to be excluded, set of fields to be retained, and set of fields to be renamed;

[0080] 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 table, field to be converted, and conversion type;

[0081] Table custom attribute table: primary key, metadata storage table ID, storage medium table ID, operation type (1: upsert 2: delete), attribute value key, attribute value value, encryption type, sort number;

[0082] Metadata storage body information table: primary key, metadata information table ID, storage body type (0: mysql, 1: hudi, 2: doris), directory name, library name, table partition type (0: no partition, 1: field value, 2: RANGE range, 3: List list), table bucket type (0: no bucket, 1: HASH, 2: RANDOM);

[0083] Storage medium table: primary key, storage type, media type, 0: storage, 1: flink, 2: spark;

[0084] Metadata key-value table: primary key, metadata storage table ID, key field, key type, 0: unique primary key, 1: duplicate primary key, 2: partition key, 3: bucket key, sort number;

[0085] Table template table: primary key, storage medium table ID, table statement template, template type: 1: create table, 2: add field, 3: delete field; 4: modify field;

[0086] Table template table: primary key, storage medium table ID, attribute value key, attribute value value, encryption type, sort number.

[0087] Finally, it should be noted that the above is only a preferred embodiment of the present application and is not intended to limit the present application. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art can still modify the technical solutions described in the aforementioned embodiments or make equivalent replacements for some of the technical features therein. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should be included in the scope of protection of the present application.

Claims

1. An improved method for automatically creating and updating a data synchronization wide table structure, characterized in that: The improved method comprises the following steps: The first step is to create and update the wide table structure of multiple storage bodies by configuring the schema metadata of the table configuration and its associated information configuration table in the database; The second step is to automatically generate the required wide table fields according to best practice rules; The third step is to reduce redundant configurations by splitting the media and storage into a storage medium table and a metadata storage table, and then adding a metadata information table to form the core base; The fourth step is to customize the types of specific fields in the output stream. By adding a new Flink task wide table specific field type conversion table, you can define the fields to be converted and the types of conversions, and decouple the strong association between the field types of the output stream and the input stream. Step 5: Add a table custom attribute table, and support custom attribute configuration, attribute encryption, decryption and replacement; Step 6: Split the table association information table involved in the task wide table into table association information and metadata storage bodies involved in the task wide table, and parse the wide table corresponding to the task and its association relationship; The seventh step is to configure the directory name and database name to which the table belongs, and add the directory name and database name to the metadata storage table.

2. The improved method for automatically creating and updating a data synchronization wide table structure according to claim 1, characterized in that: 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, characterized in that: After the redundancy reduction configuration operation in the third step, when distinguishing other tables at the three progressive levels of metadata, storage body and medium, the primary key of one of the corresponding tables is used 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 level is differentiated, the association table is improved, including splitting the media classification from the storage body classification to ensure that only the table template attributes, table template fields and table custom attribute configurations are associated with the storage body media table and 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 reducing redundant configuration also includes reducing partition and bucket configuration redundancy, and moving the key fields and key types of the metadata storage body table to a 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 separate the table template attributes from the table template and add a table custom attribute table to separate the field and attribute configuration.

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

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 field information required for the wide table includes the following steps: A1, obtains the field name information required for the wide table based on the wide table and its association relationship and the information of the corresponding association table; A2, standardize the format of fact table fields; A3, perform addition and subtraction processing on specific fields; A4, obtains complete field information such as the type and length of the field corresponding to the field name required by 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 field information required for the wide table is obtained, the wide table structure is set, and the structure of the wide table corresponding to different storage bodies is created.

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 is set, create and update statements are set and executed. The execution operation includes the following steps: B1, set 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 body, preparing to create the table if the table does not exist, and obtaining the old table structure if it exists; B3, create data table operation statements, including determining whether to create, setting table properties, searching for the corresponding table template, and replacing the placeholders in the template to create the corresponding table creation and update statements; B4, execute statement.

Images