Zipper model data aggregation method and system based on HIVE
By adopting the zipper model data aggregation method in the HIVE environment, using the standard interface model to the target result model, data operation is performed based on the primary key, which solves the problem of unfriendly update and delete operations in HIVE, and efficient data storage and low redundancy are achieved, reducing the difficulty of development and operation and maintenance.
Patent Information
- Application Number
- CN202111289420.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-11-02
- Publication Date
- 2025-08-22
- Estimated Expiration
- 2041-11-02
AI Technical Summary
In the Hive environment, data operations, especially update and delete operations, are not friendly, resulting in high data redundancy and difficult development and operation.
Through the data aggregation method of zipper model based on HIVE, the standard interface model is used to associate with the target result model, and data insertion, deletion and update are performed based on the primary key, avoid file-level operations, realize zipper storage of historical data, and reduce data redundancy.
It realizes unlogical development in the HIVE environment, reduces the development workload and operation and maintenance difficulties, and retains all data changes trajectories.
Smart Images
Figure CN114238460B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data aggregation, and in particular to a zipper model data aggregation method based on HIVE and a zipper model data aggregation system based on HIVE. Background Art
[0002] The carrier industry is fully promoting de-O and de-MPP technology scenarios, introducing big data technologies for data storage, computing, and applications. Based on data timeliness and business scenarios, these scenarios are primarily categorized into real-time, near-real-time, and offline. Regardless of the business scenario, data operations must be performed at a certain frequency. In the context of big data, offline business scenarios generally use Hive as a replacement for Oracle as the primary data storage and computing solution. However, Hive is based on HDFS (Hadoop Distributed File System) file storage, which lacks support for updates and deletes. Update and delete operations in Hive are also not recommended. Summary of the Invention
[0003] In response to the above problems, the present invention provides a zipper-type model data aggregation method and system based on HIVE. Through data standardization, according to the association between the standard interface model and the target result model, data insertion, deletion and update operations are performed based on the primary key. Only logic-free development is required based on the model fields and attributes, thereby avoiding file-level update and deletion operations, realizing zipper-type storage of historical data, and being able to retain all data change traces while reducing data redundancy. The templated development method reduces development workload and operation and maintenance difficulty, and solves the data operation problem in the HIVE environment.
[0004] To achieve the above objectives, the present invention provides a zipper model data aggregation method based on HIVE, comprising:
[0005] Connecting to the business system and extracting raw data from the business system;
[0006] Cleaning and standardizing the raw data to determine a standard interface model;
[0007] Obtaining current valid data from the target result model, and associating the valid data with the standard interface model through the primary key of the valid data to determine the data model to be updated in the target result model;
[0008] Left outer join the standard interface model to the data model to be updated, remove duplicate primary key data to generate incremental data, and directly insert the incremental data into the target result model;
[0009] Associating the data model to be updated with the standard interface model, and deleting the data that matches all fields in the original data;
[0010] The data model to be updated is associated with the standard interface model, and the data of the standard interface model is used to update the data model to be updated, so as to achieve data convergence of the target result model.
[0011] In the above technical solution, preferably, the HIVE-based zipper model data aggregation method further includes: after the data update of the data model to be updated is completed, updating the data status date of the data before the update of the same primary key.
[0012] In the above technical solution, preferably, the standard interface model is consistent with the data model in the business system, and the standard interface model is used to copy the original data extracted from the business system to the target result model.
[0013] In the above technical solution, preferably, an ETL (Extract-Transform-Load) tool is used to extract the original data in the business system.
[0014] In the above technical solution, preferably, the cleaning and standardizing of the raw data specifically includes:
[0015] Deleting irrelevant characters in the original data to clean the original data;
[0016] The corresponding field names and field attributes in the original data are kept consistent with the target result model to achieve standardization of the original data.
[0017] In the above technical solution, preferably, determining the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model specifically includes:
[0018] By internally associating the target result model with the standard interface model, the valid data is compared with the primary key data of the standard interface model, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
[0019] The present invention further proposes a HIVE-based zipper model data aggregation system, which applies the HIVE-based zipper model data aggregation method disclosed in any one of the above technical solutions, including:
[0020] A data extraction module is used to connect to the business system and extract raw data from the business system;
[0021] A data processing module, configured to clean and standardize the raw data to determine a standard interface model;
[0022] An update confirmation module is used to obtain current valid data from the target result model, and determine the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model;
[0023] An incremental insertion module, configured to perform a left outer association of the standard interface model with the data model to be updated, remove data with duplicate primary keys to generate incremental data, and directly insert the incremental data into the target result model;
[0024] A data discarding module is used to associate the data model to be updated with the standard interface model and delete the data that matches all fields in the original data;
[0025] The data updating module is used to associate the data model to be updated with the standard interface model, and update the data model to be updated using the data of the standard interface model to achieve data convergence of the target result model.
[0026] In the above technical solution, preferably, the HIVE-based zipper model data aggregation system further includes:
[0027] The date update module is used to update the data status date of the data before the update of the same primary key after the data update of the data model to be updated is completed.
[0028] In the above technical solution, preferably, the data processing module is specifically used to:
[0029] Deleting irrelevant characters in the original data to clean the original data;
[0030] The corresponding field names and field attributes in the original data are kept consistent with the target result model to achieve standardization of the original data.
[0031] In the above technical solution, preferably, the update confirmation module is specifically used to:
[0032] By internally associating the target result model with the standard interface model, the valid data is compared with the primary key data of the standard interface model, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
[0033] Compared with the existing technology, the beneficial effects of the present invention are as follows: through data standardization, according to the association between the standard interface model and the target result model, data insertion, deletion and update operations are performed based on the primary key, and only logic-free development is required based on the model fields and attributes, thereby avoiding file-level update and deletion operations, realizing zipper-style storage of historical data, and being able to retain all data change traces while reducing data redundancy. The templated development method reduces development workload and operation and maintenance difficulty, and solves the data operation problem in the HIVE environment. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] Figure 1 A schematic diagram of a flow chart of a HIVE-based zipper model data aggregation method disclosed in an embodiment of the present invention;
[0035] Figure 2 A schematic diagram of data changes in a zipper model data aggregation method based on HIVE disclosed in an embodiment of the present invention;
[0036] Figure 3 The module diagram of the HIVE-based zipper model data aggregation system disclosed in one embodiment of the present invention is shown.
[0037] In the figure, the corresponding relationship between each component and the reference numeral is as follows:
[0038] 11. Data extraction module, 12. Data processing module, 13. Update confirmation module, 14. Incremental insertion module, 15. Data discard module, 16. Data update module, 17. Date update module. DETAILED DESCRIPTION
[0039] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.
[0040] The present invention is described in further detail below with reference to the accompanying drawings:
[0041] like Figure 1 As shown, a zipper model data aggregation method based on HIVE is provided according to the present invention, comprising:
[0042] Connect to the business system and extract raw data from the business system;
[0043] Clean and standardize the raw data to determine the standard interface model;
[0044] Obtain the current valid data from the target result model, associate it with the standard interface model through the primary key of the valid data, and determine the data model to be updated in the target result model;
[0045] Left outer join the standard interface model with the data model to be updated, remove duplicate primary key data to generate incremental data, and insert the incremental data directly into the target result model;
[0046] Associate the standard interface model with the data model to be updated, and delete all matching data in the original data.
[0047] The data model to be updated is associated with the standard interface model, and the data of the standard interface model is used to update the data model to be updated, so as to achieve data aggregation of the target result model.
[0048] In this embodiment, through data standardization, according to the association between the standard interface model and the target result model, data insertion, deletion and update operations are performed based on the primary key. Only logic-free development is required based on the model fields and attributes, thereby avoiding file-level update and deletion operations, realizing zipper-style storage of historical data, and being able to retain all data change traces while reducing data redundancy. The template-based development method reduces development workload and operation and maintenance difficulty, and solves data operation problems in the HIVE environment.
[0049] Specifically, first, the key processing fields are standardized through model design templates;
[0050] Secondly, through the predefined data processing template, the target data and the data to be processed are mapped to maintain the same field names and attributes;
[0051] Finally, according to the predefined business data processing flow, the data processing process is completed step by step to realize the incremental zipper storage of data.
[0052] The above method mainly solves two problems:
[0053] 1. Solve the problem of unfriendly support for update and delete operations when performing data operations in the Hive environment;
[0054] 2. Reduce the difficulty of data aggregation logic development and operation and maintenance in the Hive environment, and achieve logic-free development, where you only need to worry about model fields and attributes.
[0055] Specifically, in this method, the following technologies are mainly used:
[0056] 1. Use Python to call the underlying database links and functions, as well as business process logic judgment, and encapsulate and isolate specific data processing, provide external interface calls, and pass parameters internally;
[0057] 2. Implement data filtering, cleaning, storage, summary calculation and data sharing through Hive-sql. In this invention, it is mainly used to aggregate data and perform logical operations and storage of data according to predefined compression algorithms and storage formats and business requirements (such as historical zippers);
[0058] 3. Special use is made of database-specific syntax structures and functions such as drop table if exists, create table xxx as select * from aaa, replace, not exists, exists, and rename.
[0059] Specifically, such as Figure 2 As shown, the processing process is as follows:
[0060] 1. Extract new raw data from the business system;
[0061] 1.1. Extract the original data of the business system through ETL tools;
[0062] 1.2. Standardize the extracted data according to the data structure of the target result model His (the main purpose is to make the interface model and the target model have the same field names and field attributes) to obtain the standard interface model Cur.
[0063] 2. Update the target model's data His with the newly added data from the business system. Based on the data model's primary key, data updates are divided into three main types of operations.
[0064] 2.1. If the standard interface model Cur has data with (primary keys), but the target result model His does not have (data with these primary keys), it is considered as completely new data, and such data in Cur is directly inserted into His;
[0065] 2.2. If the standard interface model Cur has it, and the target result model His also has it, but the attribute values of other fields are different, you need to use the attribute values of Cur's fields to update the attribute values of other fields with the same primary key in the target model;
[0066] 2.3. If the standard interface model Cur has it, the target result model His also has it, and all other field attribute values are the same, the primary key data of the standard interface model Cur needs to be discarded, and there is no need to update His.
[0067] 3. The existing models in the target result model His that do not need to be updated will continue to be retained in the new bill period. That is, the existing data that does not need to be updated + the newly added updated data are combined into the complete zipper data of the new bill period.
[0068] The standard interface model, Cur, is completely consistent with the business system. Its primary implementation involves copying the corresponding model structure of the business system, replicating it on the target end, and then using ETL tools to transfer data between the business and target systems. ETL also implements data governance (including cleansing and standardization). This interface approach eliminates dependencies on the business system after data extraction, ensuring data integrity and consistency and achieving decoupling from the business system.
[0069] In the above embodiment, preferably, the HIVE-based zipper model data aggregation method further includes: after the data update of the data model to be updated is completed, updating the data status date of the data before the update of the same primary key.
[0070] In the above embodiment, preferably, cleaning and standardizing the raw data specifically includes:
[0071] Delete irrelevant characters in the original data to clean the original data;
[0072] Keep the corresponding field names and field attributes in the original data consistent with the target result model to achieve standardization of the original data.
[0073] In the above embodiment, preferably, determining the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model specifically includes:
[0074] By internally associating the target result model with the standard interface model, the primary key data of the valid data and the standard interface model are compared, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
[0075] like Figure 3 As shown, the present invention further proposes a HIVE-based zipper model data aggregation system, which applies the HIVE-based zipper model data aggregation method disclosed in any of the above embodiments, including:
[0076] The data extraction module 11 is used to connect to the business system and extract original data from the business system;
[0077] The data processing module 12 is used to clean and standardize the raw data to determine the standard interface model;
[0078] The update confirmation module 13 is used to obtain the current valid data from the target result model, and determine the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model;
[0079] The incremental insert module 14 is used to left outer join the standard interface model with the data model to be updated, remove duplicate primary key data to generate incremental data, and directly insert the incremental data into the target result model;
[0080] The data discarding module 15 is used to associate the data model to be updated in the standard interface model and delete the data that matches all fields in the original data;
[0081] The data updating module 16 is used to associate the data model to be updated with the standard interface model, and update the data model to be updated using the data of the standard interface model, so as to achieve data convergence of the target result model.
[0082] In the above embodiment, preferably, the HIVE-based zipper model data aggregation system further includes:
[0083] The date updating module 17 is configured to update the data status date of the data before the update of the same primary key after the data update of the data model to be updated is completed.
[0084] In the above embodiment, preferably, the data processing module 12 is specifically used to:
[0085] Delete irrelevant characters in the original data to clean the original data;
[0086] Keep the corresponding field names and field attributes in the original data consistent with the target result model to achieve standardization of the original data.
[0087] In the above embodiment, preferably, the update confirmation module 13 is specifically used to:
[0088] By internally associating the target result model with the standard interface model, the primary key data of the valid data and the standard interface model are compared, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
[0089] According to the HIVE-based zipper model data aggregation method disclosed in the above embodiment, specific examples and data are used for specific description below. The specific update data change process and results are as follows.
[0090] Assume that the data of the target model His before being updated is:
[0091] Account Period Primary Key name age income expend etl_dt 20210531 1001 Tom 18 2000 500 2021 / 5 / 30 20210531 1001 Tom 19 4000 1000 3000 / 12 / 31 20210531 1002 Samon 25 10000 4500 3000 / 12 / 31
[0092] The interface data extracted from the production system for the current billing period (for example, 2021 / 6 / 1):
[0093] 1) Raw data
[0094] Primary Key name age income expend ID1001 Tom 20 years old 4500 1200 ID1002 Samon 25 10000 4500 ID1003 Lucky 25 15000 10000
[0095] 2) Clean and standardize the original data
[0096] Primary Key name age income expend 1001 Tom 20 4500 1200 1002 Samon 25 10000 4500 1003 Lucky 25 15000 10000
[0097] Extract from His the data that is still valid as of the last billing period (etl_dt=30001231), that is, the data that is valid in the current state
[0098] Account Period Primary Key name age income expend etl_dt 20210531 1001 Tom 19 4000 1000 3000 / 12 / 31 20210531 1002 Samon 25 10000 4500 3000 / 12 / 31
[0099] His valid data is associated with Cur, and the result is the data model to be updated Pre
[0100] Account Period Primary Key name age income expend etl_dt 20210531 1001 Tom 19 4000 1000 3000 / 12 / 31 20210531 1002 Samon 25 10000 4500 3000 / 12 / 31
[0101] Cur is left outer joined with Pre, eliminating duplicate primary key data and generating incremental data Ins
[0102] Primary Key name age income expend 1003 Lucky 25 15000 10000
[0103] After inserting the above incremental data into His, the result is
[0104] Account Period Primary Key name age income expend etl_dt 20210601 1003 Lucky 25 15000 10000 3000 / 12 / 31
[0105] Associate Pre with Cur and perform full field matching to generate the data that needs to be deleted
[0106] Primary Key name age income expend 1002 Samon 25 10000 4500
[0107] This part of the data needs to be discarded.
[0108] Associate Cur within Pre and update Pre with the value of Cur
[0109] Primary Key name age income expend 1001 Tom 20 4500 1200
[0110] After inserting the above data into the target model His, the result is
[0111] Account Period Primary Key name age income expend etl_dt 20210601 1001 Tom 20 4500 1200 3000 / 12 / 31
[0112] At the same time, the data status date of the original His with the same primary key should be updated
[0113] Account Period Primary Key name age income expend etl_dt 20210531 1001 Tom 20 4500 1200 2021 / 5 / 31
[0114] The data of the final target model His is
[0115]
[0116]
[0117] As can be seen from the above example, the final data of the target result model not only preserves historical change information—the changes of each primary key are like a chain, allowing you to view all changes by date—but also allows you to obtain unique information for each primary key based on the status date, thus ensuring data uniqueness. This is the zipper model.
[0118] The above are merely preferred embodiments of the present invention and are not intended to limit the present invention. Those skilled in the art will readily appreciate that various modifications and variations of the present invention are possible. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present invention shall be included within the scope of protection of the present invention.
Claims
1. A zipper model data aggregation method based on HIVE, characterized in that: include: Connect to the business system and use ETL tools to extract raw data from the business system; Cleaning and standardizing the raw data to determine a standard interface model; Obtaining current valid data from the target result model, and associating the valid data with the standard interface model through the primary key of the valid data to determine the data model to be updated in the target result model; Left outer join the standard interface model to the data model to be updated, remove duplicate primary key data to generate incremental data, and directly insert the incremental data into the target result model; Associating the data model to be updated with the standard interface model, and deleting the data that matches all fields in the original data; Associating the data model to be updated with the standard interface model, and updating the data model to be updated using the data of the standard interface model, thereby achieving data aggregation of the target result model; The standard interface model is consistent with the data model in the business system, and the standard interface model is used to copy the original data extracted from the business system to the target result model.
2. The HIVE-based zipper model data aggregation method according to claim 1, characterized in that: Also includes: After the data update of the data model to be updated is completed, the data status date of the data before the update with the same primary key is updated.
3. The HIVE-based zipper model data aggregation method according to claim 1, characterized in that: The cleaning and standardization of the raw data specifically includes: Deleting irrelevant characters in the original data to clean the original data; The corresponding field names and field attributes in the original data are kept consistent with the target result model to achieve standardization of the original data.
4. The HIVE-based zipper model data aggregation method according to claim 1, characterized in that: Determining the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model specifically includes: By internally associating the target result model with the standard interface model, the valid data is compared with the primary key data of the standard interface model, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
5. A HIVE-based zipper model data aggregation system, applying the HIVE-based zipper model data aggregation method according to any one of claims 1 to 4, characterized in that: include: The data extraction module is used to connect to the business system and extract raw data from the business system using ETL tools; A data processing module, configured to clean and standardize the raw data to determine a standard interface model; An update confirmation module is used to obtain current valid data from the target result model, and determine the data model to be updated in the target result model by associating the primary key of the valid data with the standard interface model; An incremental insertion module, configured to perform a left outer association of the standard interface model with the data model to be updated, remove data with duplicate primary keys to generate incremental data, and directly insert the incremental data into the target result model; A data discarding module is used to associate the data model to be updated with the standard interface model and delete the data that matches all fields in the original data; A data updating module, configured to associate the data model to be updated with the standard interface model, and update the data model to be updated using the data of the standard interface model, thereby achieving data convergence of the target result model; The standard interface model is consistent with the data model in the business system, and the standard interface model is used to copy the original data extracted from the business system to the target result model.
6. The HIVE-based zipper model data aggregation system according to claim 5, characterized in that: Also includes: The date update module is used to update the data status date of the data before the update of the same primary key after the data update of the data model to be updated is completed.
7. The HIVE-based zipper model data aggregation system according to claim 5, characterized in that: The data processing module is specifically used for: Deleting irrelevant characters in the original data to clean the original data; The corresponding field names and field attributes in the original data are kept consistent with the target result model to achieve standardization of the original data.
8. The HIVE-based zipper model data aggregation system according to claim 5, characterized in that: The update confirmation module is specifically used for: By internally associating the target result model with the standard interface model, the valid data is compared with the primary key data of the standard interface model, and the corresponding data with the same primary key in the target result model is used as the data model to be updated.
Citation Information
Patent Citations
Method, device and computer device for updating zipper list in Hive database
CN109299093A