Covering type generation zipper table updating method, system, equipment and medium

Abstract

The invention relates to a covering type zipper table updating method, system and device and a medium, and the method comprises the following steps: obtaining full data of a source pasting layer in a data warehouse, and initializing the full data into an original zipper table; creating an increment table, collecting the newly-added data and the updated data as increment data, and adding the increment data into the increment table; creating a temporary table, combining the original zipper table and the increment table, setting original data in the original zipper table corresponding to the updated data to be in a closed chain state, and adding the original data into the temporary table; and writing the data in the temporary table into the original zipper table in a covering manner by adopting a rewriting operation so as to realize the updating of the zipper table. Compared with the prior art, the method has the advantages of high re-running efficiency, low resource consumption and the like.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouse, and in particular to a covering zipper table updating method, system, device and medium. Background Art

[0002] A zipper is information that records all changes of a thing from its beginning to its current state. A zipper table is a logical concept that is defined for the table storage method in data warehouse design. A zipper table has the start date and end date of the data and records the life cycle of the data. It can not only reflect the historical state of the data, but also save storage space to the maximum extent.

[0003] In data warehouse model design, the following design scenarios of this type of table are often encountered:

[0004] 1. Some tables have a large amount of data, such as a user table with about 1 billion records and 50 fields. Even with ORC compression, the storage of a single table will exceed 100G. If double or triple backup is used in HDFS, the storage will be even larger.

[0005] 2. Some fields in the table will be updated, such as user contact information, product description information, policy status, etc.

[0006] 3. Need to view historical snapshot information at a certain point in time or time period, for example, to view the status of a certain insurance policy at a certain point in history

[0007] 4. The proportion and frequency of record changes in the table are not very large. For example, there are 1 billion users in total, and about 2 million new users and changes are added every day, so the proportion of changes is very small.

[0008] For this type of table, there are several options:

[0009] Solution 1: Only keep the latest copy every day, for example, extract the latest full data into Hive every day. Advantages: save space, some common uses are also very convenient, no need to add a time partition when selecting a table. Disadvantages: no historical data, if you want to look up the old account, you can only use other methods, such as extracting from the flow table.

[0010] Solution 2: Keep a full slice of data every day. Advantages: A full slice every day is a relatively safe solution, and historical data is also available. Disadvantages: The storage space is too large. If a full slice is kept for this table every day, a lot of unchanged information will be saved in each full slice, which is a huge waste of storage. Of course, some trade-offs can be made, such as only keeping data for the past month. However, the life cycle of data cannot be completely controlled by human factors.

[0011] Solution 3: Use a zipper table. Use time to mark the time period of each state of the data that has changed. Advantages: It makes a trade-off in terms of space. Although it does not occupy as little space as Solution 1, its daily increment may be only one thousandth or even one ten-thousandth of Solution 2. It can obtain the latest data, and can also add filtering conditions to obtain historical data.

[0012] The above scenario is suitable for using zipper tables. In big data scenarios, most users will choose to use HDFS distributed file systems to store data, but HDFS file systems are not suitable for data update operations. Therefore, an update method is needed to solve the data update problem of HDFS file systems. In addition, there is also the problem of complex re-running. Once a problem occurs in the data and the full zipper table needs to be refreshed, it is necessary to start from the earliest data and continuously run the zipper logic of the previous step, which doubles the overall time and resources consumed. Summary of the invention

[0013] The purpose of the present invention is to provide a covering zipper table update method, system, device and medium suitable for updating zipper data of HDFS file system.

[0014] The purpose of the present invention can be achieved by the following technical solutions:

[0015] A covering zipper table updating method comprises the following steps:

[0016] Get all the data of the source layer in the data warehouse and initialize it as the original zipper table;

[0017] Create an incremental table, collect new data and updated data as incremental data, and add them to the incremental table;

[0018] Create a temporary table, merge the original zipper table and the incremental table, set the original data in the original zipper table corresponding to the updated data to a closed chain state, and add it to the temporary table;

[0019] The rewrite operation is adopted to overwrite the data in the temporary table into the original zipper table, so as to implement the update of the zipper table.

[0020] Furthermore, the zipper table includes a business primary key, a data field, a current status, a start date, and an end date, and the business primary key serves as a unique identifier for each piece of data.

[0021] Furthermore, the incremental table includes a business primary key, a data field, a current status, a start date, an end date, and a change status of the incremental data, and the change status of the incremental data includes update and addition.

[0022] Furthermore, the date when the incremental data is collected is used as the start date of the incremental table.

[0023] Furthermore, the frequency of collecting the incremental data is set according to business needs, including 12 hours, day, month, and year.

[0024] Furthermore, the temporary table includes a business primary key, a data field, a current status, a start date, an end date, and a change status of each piece of data, and the change status of each piece of data includes update, addition, and closed link.

[0025] Furthermore, the deadline of the original data in the closed-link state in the temporary table is set to the last data collection date.

[0026] The present invention also provides a covering zipper table updating system, comprising:

[0027] Initialization module: obtains the full amount of data corresponding to the business requirements and initializes it into the original zipper table;

[0028] Incremental table module: creates an incremental table, collects new data and updated data as incremental data, and adds them to the incremental table;

[0029] Temporary table module: creates a temporary table, merges the original zipper table and the incremental table, sets the original data in the original zipper table corresponding to the updated data to a closed chain state, and adds it to the temporary table;

[0030] Rewrite module: adopts rewrite operation to overwrite the data in the temporary table into the original zipper table to implement zipper table update.

[0031] The present invention also provides an electronic device, comprising: one or more processors; a memory; and one or more programs stored in the memory, wherein the one or more programs include instructions for executing the overlay zipper table update method as described above.

[0032] The present invention also provides a computer-readable storage medium, comprising one or more programs for execution by one or more processors of an electronic device, wherein the one or more programs include instructions for executing the overlay zipper table update method as described above.

[0033] Compared with the prior art, the present invention has the following beneficial effects:

[0034] (1) The present invention adopts an overwriting operation to replace the previous update operation. By collecting incremental data, new data records and update records are obtained. The incremental data and the original zipper data are merged and the original zipper table is overwritten. Not only the latest data but also the historical data are retained, thereby realizing the update of zipper data in the HDFS file system.

[0035] (2) If the zipper table of the present invention is to be re-run to regenerate the zipper table, it only needs to execute the agent once and run it once to complete it. However, the prior art often needs to start from the first day and execute all subsequent date data once, that is, execute the code many times, which has a great difference in resource consumption and execution time.

[0036] (3) The updated zipper table of the method of the present invention does not need to be compared with the zipper table before the update. The newly added data and the updated data are processed separately, and all of them can be processed directly at one time. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 It is a schematic diagram of the method flow of the present invention. DETAILED DESCRIPTION

[0038] The present invention is described in detail below in conjunction with the accompanying drawings and specific embodiments. This embodiment is implemented based on the technical solution of the present invention, and provides a detailed implementation method and specific operation process, but the protection scope of the present invention is not limited to the following embodiments.

[0039] Example 1

[0040] This embodiment provides a covering zipper table update method, such as Figure 1 As shown, the method comprises the following steps:

[0041] S1. Obtain the full amount of data of the source layer in the data warehouse and initialize it to the original zipper table.

[0042] Get all the data from the source layer in the data warehouse and initialize the zipper table. The zipper table includes the business primary key, data field, current status, start date, and end date. The business primary key is used as the unique identifier for each piece of data.

[0043] As shown in Table 1 below, the user policy data as of 2020-01-01 is initialized as zipper table data. The policy number in the table is used as the business primary key, the insured is used as the data field, the policy status is used as the current status of the user policy (including ineffective, valid, and expired), the start date is used as the recording date of the user policy information data, and the end date is used as the invalid date of the user policy information data.

[0044] Table 1 Original zipper table

[0045] policy_no (policy number) applicant_name (insured person) policy_status (policy status) start_date(start date) end_date (deadline date) hmb00xxx001 Wang Yi Not effective 2020-01-01 9999-12-31 hmb00xxx002 Li Er efficient 2020-01-01 9999-12-31 hmb00xxx003 Zhang San Invalidation 2020-01-01 9999-12-31 hmb00xxx004 Zhong Si efficient 2020-01-01 9999-12-31 hmb00xxx005 Zhang Wu efficient 2020-01-01 9999-12-31 hmb00xxx006 Li Liu Expiration 2020-01-01 9999-12-31

[0046] S2. Create an incremental table, collect new data and updated data as incremental data, and add them to the incremental table.

[0047] This step requires an incremental table. Compared with the original zipper table, the incremental table also includes a column for the change status of the incremental data, which is used to record the change status of the data, including addition and update. Set the data collection frequency, which can be set to 12 hours, days, months, years, etc. according to business needs. Collect new and updated data according to the collection frequency and add it to the created incremental table.

[0048] As shown in Table 2, the collection frequency is set to 1 day, and new and updated records are extracted every day. The extraction is performed on 2020-01-02, and the incremental table of that day is obtained. The start date of each data in the table is 2020-01-02, that is, the collection date, and the last column indicates whether the data is a new record or an updated record.

[0049] Table 2 Increment table

[0050]

[0051] S3. Create a temporary table, merge the original zipper table and the incremental table, set the original data in the original zipper table corresponding to the updated data to a closed chain state, and add it to the temporary table.

[0052] First, create a temporary table. Compared with the original zipper table, the temporary table also includes a column for the change status of the incremental data, which is used to record the change status of the data, including addition, update, and closed link. The original zipper table and the incremental table are merged by directly union all the incremental data mentioned above, and added to the created temporary table. After the merger, there are multiple data with the same business primary key in the temporary table, including not only the original data in the original zipper table, but also the incremental data in the incremental table. Because the incremental table contains the latest data, the deadline of the corresponding original data is modified, set to the date of the last data collection, and closed.

[0053] As shown in Table 3, the original zipper table data of 2020-01-01 and the incremental table data of 2020-01-02 are merged, and the updated data end_date in the valid data of the original zipper table is set to 2020-01-02 to close the chain.

[0054] Table 3 Temporary table

[0055]

[0056] S4. Use a rewrite operation to overwrite the data in the temporary table into the original zipper table to update the zipper table.

[0057] Use the overwrite operation to overwrite the data in the temporary table into the zipper table to update the zipper table.

[0058] The core SQL code implemented by the above method is as follows:

[0059]

[0060] Apply the above method to other business scenarios, specifically:

[0061] Taking the user information table t_user_info in Table 4 below as an example, the following is the stock data.

[0062] On 20210108, two incremental data came in. The data in the user information incremental table t_user_info_delta is as shown in Table 5 below. In the record where user_id=1, the addess field changes, and the incremental extraction obtains the change; the record where user_id=3 is a new record.

[0063] Table 4 User information table

[0064] user_id phone adress create_time modified_time 1 1xxxxxxxxxx Pudong New Area, Shanghai 2021 / 1 / 1 2021 / 1 / 1 2 1xxxxxxxxxx Fengtai District, Beijing 2021 / 1 / 1 2021 / 1 / 1

[0065] Table 5 User information increment table t_user_info_delta

[0066]

[0067] After the incremental and full data are merged, the zipper table shown in Table 6 is generated.

[0068] Table 6 User information zipper table

[0069]

[0070] The code processing logic is as follows

[0071]

[0072]

[0073] The above method is highly efficient in rerunning and consumes less resources. If the table has been online for one year and has 365 partitions of stock and incremental data, to rerun the full data and regenerate the zipper, you only need to execute the above code based on all the incremental and stock data, and it can be completed by running it once. The existing method needs to execute all date data from the first day onwards, and the code is executed 364 times in total, which is more than 300 times different in resource consumption and execution time.

[0074] Example 2

[0075] This embodiment provides a covering zipper table updating system, including:

[0076] Initialization module: obtains the full amount of data corresponding to the business requirements and initializes it into the original zipper table;

[0077] Incremental table module: creates an incremental table, collects new data and updated data as incremental data, and adds them to the incremental table;

[0078] Temporary table module: creates a temporary table, merges the original zipper table and the incremental table, sets the original data in the original zipper table corresponding to the updated data to a closed chain state, and adds it to the temporary table;

[0079] Rewrite module: adopts rewrite operation to overwrite the data in the temporary table into the original zipper table to implement zipper table update.

[0080] The rest is the same as in Example 1.

[0081] Example 3

[0082] The present invention also provides an electronic device, comprising: one or more processors; a memory; and one or more programs stored in the memory, wherein the one or more programs include instructions for executing the covering zipper table update method as described in the above-mentioned embodiment 1.

[0083] If the above functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program code.

[0084] It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes. The schemes in the embodiments of the present invention may be implemented in various computer languages, for example, object-oriented programming language Java and literal scripting language JavaScript, etc.

[0085] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0086] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0087] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0088] Although the preferred embodiments of the present invention have been described, those skilled in the art may make other changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the present invention.

[0089] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention is also intended to include these modifications and variations.

Claims

1. A covering zipper table updating method, characterized in that: The following steps are involved: Get all the data of the source layer in the data warehouse and initialize it as the original zipper table; Create an incremental table, collect new data and updated data as incremental data, and add them to the incremental table; Create a temporary table, merge the original zipper table and the incremental table, set the original data in the original zipper table corresponding to the updated data to a closed chain state, and add it to the temporary table; The rewrite operation is adopted to overwrite the data in the temporary table into the original zipper table, so as to implement the update of the zipper table.

2. A covering zipper table updating method according to claim 1, characterized in that: The zipper table includes a business primary key, a data field, a current status, a start date, and an end date, and the business primary key serves as a unique identifier for each piece of data.

3. The method for updating a covered zipper table according to claim 1, characterized in that: The incremental table includes a business primary key, a data field, a current status, a start date, an end date, and a change status of the incremental data, where the change status of the incremental data includes update and addition.

4. The method for updating a covered zipper table according to claim 3, characterized in that: The date when the incremental data is collected is used as the start date of the incremental table.

5. The method for updating a covered zipper table according to claim 1, characterized in that: The frequency of collecting the incremental data is set according to business needs, including 12 hours, day, month, and year.

6. The method for updating a covered zipper table according to claim 1, characterized in that: The temporary table includes a business primary key, a data field, a current status, a start date, an end date, and a change status of each piece of data, where the change status of each piece of data includes update, addition, and closed link.

7. A covering zipper table updating method according to claim 6, characterized in that: The deadline of the original data in the closed-link state in the temporary table is set to the last data collection date.

8. A covering zipper watch updating system, characterized in that: include: Initialization module: obtains the full amount of data corresponding to the business requirements and initializes it into the original zipper table; Incremental table module: creates an incremental table, collects new data and updated data as incremental data, and adds them to the incremental table; Temporary table module: creates a temporary table, merges the original zipper table and the incremental table, sets the original data in the original zipper table corresponding to the updated data to a closed chain state, and adds it to the temporary table; Rewrite module: adopts rewrite operation to overwrite the data in the temporary table into the original zipper table to implement zipper table update.

9. An electronic device, characterized in that: include: one or more processors; Memory; and One or more programs stored in a memory, wherein the one or more programs include instructions for executing the covering zipper table updating method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that: The method comprises one or more programs executed by one or more processors of an electronic device, wherein the one or more programs comprise instructions for executing the covering zipper table updating method as claimed in any one of claims 1 to 7.

Images