Method and apparatus for constructing a data warehouse ziplist, data warehouse system

Abstract

The application relates to the technical field of big data, and discloses a method and device for constructing a data warehouse zip list, and a data warehouse system. The method comprises the following steps: judging field changes according to the demand of a user to obtain a field list file; uploading the field list file to the data warehouse system; inputting an original table name, a primary key field and a proxy key field into the data warehouse system; obtaining a zip list structure and zip list processing logic generated by the data warehouse system to develop a data warehouse zip list by using the zip list structure and the zip list processing logic. The method uses an automatic process driven by metadata, omits the step of manually writing complex processing logic and repeatedly debugging and verifying, greatly reduces the development threshold of the zip list, significantly improves the development efficiency of the data warehouse zip list, avoids logical omissions caused by manual coding, and guarantees the standardization and consistency of the construction of the zip list.

Description

Technical Field

[0001] This application relates to the field of big data technology, such as a method and apparatus for constructing a data warehouse zipper table, and a data warehouse system. Background Technology

[0002] Currently, there is an urgent need in the field of data warehouse construction for technologies that enable low-cost, high-efficiency, and high-quality construction of zipper tables to record the full historical changes of data. As the core carrier for tracing the historical trajectory of data in a data warehouse, zipper tables are widely used in key business scenarios such as business intelligence analysis and data audit backtracking. The efficiency and accuracy of their construction directly determine the application value of the data warehouse.

[0003] To address this need, a manual development method for data warehouse linked lists has been disclosed. This method includes: first, filtering incremental data based on incremental judgment fields in the business system; or having developers write custom SQL logic to compare field differences one by one; or concatenating all field strings to generate an MD5 hash value, and comparing the hash values ​​of new and old data to determine data changes; then, manually writing code according to the standard design specifications for linked lists to implement the opening of new data and the closing of changed historical data; finally, completing the logical verification of the linked list through sampling checks and full comparisons to ensure data consistency.

[0004] In the process of implementing the embodiments of this disclosure, it was found that the related technology has at least the following problems: Although the related technology can realize the basic function of building a zipper table and meet the basic requirements of data history, it has a strong dependence on the incremental judgment field of the original data table. When the original table lacks such a field or the field update rule is not standardized, it is easy to cause problems such as missing or duplicate data in the zipper table. When facing a multi-field data table, the amount of code for manually writing field comparison logic is large and highly repetitive. It not only consumes a lot of development time, but is also prone to logical errors due to human oversight.

[0005] It should be noted that the information disclosed in the background section above is only used to enhance the understanding of the background of this application, and therefore may include information that does not constitute prior art known to those skilled in the art. Summary of the Invention

[0006] To provide a basic understanding of some aspects of the disclosed embodiments, a brief summary is given below. This summary is not intended as a general commentary, nor is it intended to identify key / important components or describe the scope of protection of these embodiments, but rather as a prelude to the detailed description that follows.

[0007] This disclosure provides a method and apparatus for constructing a data warehouse zipper table, as well as a data warehouse system, to improve the efficiency of developing data warehouse zipper tables.

[0008] In some embodiments, the method for constructing a data warehouse zipper table includes: determining field changes based on user requirements and obtaining a field list file; uploading the field list file to the data warehouse system; inputting the original table name, primary key field, and surrogate key field into the data warehouse system; obtaining the zipper table structure and zipper table processing logic generated by the data warehouse system, so as to develop a data warehouse zipper table using the zipper table structure and zipper table processing logic.

[0009] Optionally, based on user needs, field change judgment can be performed to obtain a field list file, including: downloading the original table field list file; determining whether it is necessary to judge all field changes in the original table field list file based on user needs; if it is necessary to judge all field changes, perform field change judgment on all fields to obtain a field list file; if it is not necessary to judge all field changes, edit the whitelist or blacklist and then perform field change judgment to obtain a field list file.

[0010] Optionally, the whitelist or blacklist can be edited, including: adding a column with a white header to the original table field list file and marking the fields that need to be whitelisted as 'y' in the corresponding rows; or, adding a column with a black header to the original table field list file and marking the fields that need to be blacklisted as 'y' in the corresponding rows; wherein the fields in the whitelist are the fields that need to be judged for changes, and the fields in the blacklist are the fields that do not need to be judged for changes.

[0011] Optionally, the method for constructing a data warehouse zipper table further includes: when both a whitelist and a blacklist exist, selecting the whitelist to take effect.

[0012] Optionally, the method for constructing a data warehouse zipper table further includes: using a double hash operation for data comparison when judging field changes.

[0013] Optionally, when performing field change judgment, a double hash operation is used for data comparison, including: performing a hash once on each column that needs to be judged as having changed; concatenating all columns that have been hashed once, performing a double hash on the concatenated column to generate a 32-character string; and comparing the data using the 32-character string.

[0014] Optionally, the zipper table processing logic includes surrogate key generation logic, which includes: obtaining the maximum value of the surrogate key in the current zipper table, and setting the maximum value to 0 if there is no data in the current zipper table; performing a sorting operation on the newly opened data to generate an integer sequence m starting from 1; and setting the surrogate key value of the newly opened data to the maximum value of the current surrogate key plus m.

[0015] In some embodiments, the apparatus for constructing a data warehouse zipper table includes: a file acquisition module configured to determine field changes based on user requirements and obtain a field list file; a file upload module configured to upload the field list file to the data warehouse system; a data input module configured to input the original table name, primary key field, and surrogate key field into the data warehouse system; and a zipper table development module configured to acquire the zipper table structure and zipper table processing logic generated by the data warehouse system, so as to develop a data warehouse zipper table using the zipper table structure and zipper table processing logic.

[0016] In some embodiments, the apparatus for constructing a data warehouse zipper table includes a processor and a memory storing program instructions, the processor being configured to perform the method for constructing a data warehouse zipper table as described above when the program instructions are executed.

[0017] In some embodiments, the data warehouse system includes: a data warehouse system body; and an apparatus for constructing a data warehouse zipper table, as described above, installed on the data warehouse system body.

[0018] The method, apparatus, and data warehouse system for constructing a data warehouse zipper table provided in this disclosure can achieve the following technical effects: In this embodiment, field changes are first determined based on user requirements to obtain a field list file. This field list file is the core metadata foundation for subsequent chain table construction, covering key information such as field names, field types, and field comments of the original table, as well as field change judgment rules determined by the user based on business requirements. Next, the field list file is uploaded to the data warehouse system to provide data input for subsequent automated processing. Then, the original table name, primary key field, and surrogate key field are input into the data warehouse system. The primary key field is used to determine the uniqueness of the data, and the surrogate key field is used to optimize the indexing and association efficiency of the chain table. The system can automatically process duplicate data based on the primary key field. Finally, the chain table structure and chain table processing logic generated by the data warehouse system are obtained, and the data warehouse chain table is developed directly using this structure and logic.

[0019] By using metadata-driven automated processes, the need for manually writing complex processing logic and repeatedly debugging and verifying is eliminated, significantly lowering the development threshold of zipper tables and greatly improving the development efficiency of data warehouse zipper tables. At the same time, it avoids logical omissions caused by manual coding and ensures the standardization and consistency of zipper table construction.

[0020] The above general description and the description below are exemplary and illustrative only and are not intended to limit this application. Attached Figure Description

[0021] One or more embodiments are illustrated by way of example with reference to the accompanying drawings. These illustrations and drawings do not constitute a limitation on the embodiments. Elements having the same reference numerals in the drawings are shown as similar elements. The drawings are not to be scaled. And wherein: Figure 1 This is a schematic diagram of a method for constructing a data warehouse zipper table provided in an embodiment of this disclosure; Figure 2 This is a schematic diagram of another method for constructing a data warehouse zipper table provided in an embodiment of this disclosure; Figure 3 This is a schematic diagram of a double hashing example provided in an embodiment of this disclosure; Figure 4 This is a schematic diagram of an apparatus for constructing a data warehouse zipper table provided in an embodiment of this disclosure; Figure 5 This is a schematic diagram of another apparatus for constructing a data warehouse zipper table provided in an embodiment of this disclosure. Detailed Implementation

[0022] To provide a more detailed understanding of the features and technical content of the embodiments of this disclosure, the implementation of the embodiments of this disclosure will be described in detail below with reference to the accompanying drawings. The accompanying drawings are for illustrative purposes only and are not intended to limit the embodiments of this disclosure. In the following technical description, for ease of explanation, several details are used to provide a full understanding of the disclosed embodiments. However, one or more embodiments may still be implemented without these details. In other cases, well-known structures and devices may be simplified in their depiction to simplify the drawings.

[0023] The terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this disclosure are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this disclosure described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion.

[0024] Unless otherwise stated, the term "multiple" means two or more.

[0025] In this embodiment of the disclosure, the character " / " indicates that the objects before and after it are in an "or" relationship. For example, A / B means: A or B.

[0026] The term "and / or" describes an association between objects, indicating that three relationships can exist. For example, A and / or B means: A or B, or A and B.

[0027] The term "correspondence" can refer to an association or binding relationship. The correspondence between A and B means that there is an association or binding relationship between A and B.

[0028] Data warehouse: A data warehouse is a subject-oriented, integrated, non-volatile, time-varying collection of data. It obtains data from an enterprise's business systems, cleans and transforms it to support business intelligence activities. The "time-varying" characteristic means that it records the historical changes of the data.

[0029] Zipper table: A zipper table is a table design method used in data warehouses to record historical changes in data. Typically, it has fields for "effective date" and "expiration date" to represent the effective date range of the data. These two fields are also commonly referred to as "open date" and "close date".

[0030] Currently, developing data warehouse zipper tables has a high development threshold, making it difficult for junior developers to create correct zipper table logic. The development and verification cycle is long, and when developing zipper tables requires judging too many fields of data changes, the processing logic contains a large number of field processing and judgment logics, making it prone to errors when manually writing code. Poor code consistency also results in different developers implementing the same logic with different approaches, leading to poor code readability.

[0031] When developing zipper tables, surrogate keys are usually not handled. The generated zipper table must use a composite primary key to ensure data uniqueness. However, adding surrogate key logic still faces problems such as high development threshold and easy error.

[0032] Combination Figure 1 As shown in the embodiments of this disclosure, a method for constructing a data warehouse zipper table is provided, including: S101, based on user requirements, performs field change judgment and obtains a field list file.

[0033] S102, Upload the field list file to the data warehouse system.

[0034] S103. Input the original table name, primary key field, and surrogate key field into the data warehouse system.

[0035] S104, Obtain the zipper table structure and zipper table processing logic generated by the data warehouse system, and use the zipper table structure and zipper table processing logic to develop data warehouse zipper tables.

[0036] The method provided in this disclosure first determines field changes based on user requirements, thereby obtaining a field list file. This field list file is the core metadata foundation for subsequent chain table construction, covering key information such as field names, field types, and field comments of the original table, as well as field change judgment rules determined by the user based on business needs. Next, the field list file is uploaded to the data warehouse system to provide data input for subsequent automated processing. Then, the original table name, primary key field, and surrogate key field are input into the data warehouse system. The primary key field is used to determine the uniqueness of the data, and the surrogate key field is used to optimize the indexing and association efficiency of the chain table. The system can automatically process duplicate data based on the primary key field. Finally, the chain table structure and chain table processing logic generated by the data warehouse system are obtained, and the data warehouse chain table is developed directly using this structure and logic.

[0037] By using metadata-driven automated processes, the need for manually writing complex processing logic and repeatedly debugging and verifying is eliminated, significantly lowering the development threshold of zipper tables and greatly improving the development efficiency of data warehouse zipper tables. At the same time, it avoids logical omissions caused by manual coding and ensures the standardization and consistency of zipper table construction.

[0038] Optionally, based on user needs, field change judgment can be performed to obtain a field list file, including: downloading the original table field list file; determining whether it is necessary to judge all field changes in the original table field list file based on user needs; if it is necessary to judge all field changes, perform field change judgment on all fields to obtain a field list file; if it is not necessary to judge all field changes, edit the whitelist or blacklist and then perform field change judgment to obtain a field list file.

[0039] In this embodiment, the original table field list file is first downloaded from the data warehouse system. This file is in Excel format and contains three core columns: field name, field type, and field comment, clearly presenting the basic field information of the original table. Then, based on the user's actual business needs, it is determined whether it is necessary to judge all field changes in the original table field list file. If it is necessary to judge all field changes, the field change judgment is directly performed on all fields in the original table field list without additional configuration, quickly generating a field list file containing all field judgment rules. If it is not necessary to judge all field changes, the fields that need to be judged or not need to be judged are filtered by editing the whitelist or blacklist, and then the change judgment is performed based on the filtered fields to generate a field list file that meets the user's needs.

[0040] It enables flexible configuration of the field change judgment scope, supporting both comprehensive monitoring of all fields and precise filtering of specific fields. This meets the differentiated needs of zipper table data tracking in different business scenarios, avoids invalid judgments on fields that do not need to be monitored, reduces the amount of data processing in the system, and improves the targeting and accuracy of field change judgment, laying the foundation for the accurate construction of subsequent zipper tables.

[0041] Optionally, the whitelist or blacklist can be edited, including: adding a column with a white header to the original table field list file and marking the fields that need to be whitelisted as 'y' in the corresponding rows; or, adding a column with a black header to the original table field list file and marking the fields that need to be blacklisted as 'y' in the corresponding rows; wherein the fields in the whitelist are the fields that need to be judged for changes, and the fields in the blacklist are the fields that do not need to be judged for changes.

[0042] In this embodiment of the disclosure, a new column is added to the downloaded original table field list file. If a whitelist is configured, the column header is set to "white", and the fields for which the user needs to perform field change judgment are marked as "y" in the corresponding rows. In this case, the system will only track data changes for the fields marked as "y". If a blacklist is configured, the new column header is set to "black", and the fields for which the user does not need to perform field change judgment are marked as "y" in the corresponding rows. In this case, the system will exclude the fields marked as "y" and only perform data change judgment on the remaining fields.

[0043] The core function of the whitelist is to clearly define the range of fields that require focused monitoring, while the core function of the blacklist is to filter out fields that do not require attention. This disclosure provides a simple, intuitive, and easy-to-operate method for configuring field filtering. It eliminates the need for writing complex filtering code; the field judgment range can be set simply by adding annotations in an Excel file, reducing the difficulty of configuration. This clear annotation method also facilitates subsequent maintenance and modification, ensuring the clarity and readability of the field filtering rules, effectively avoiding judgment errors caused by ambiguous filtering rules, and improving the accuracy of field change judgment.

[0044] Optionally, the method for constructing a data warehouse zipper table further includes: when both a whitelist and a blacklist exist, selecting the whitelist to take effect.

[0045] In this embodiment of the disclosure, if, during user operation, due to misoperation or change of requirements, both a whitelist column and a blacklist column are configured in the original table field list file, that is, if two columns with headers of "white" and "black" appear simultaneously and both have fields marked "y", the system will default to selecting the whitelist to take effect and will only judge data changes based on the fields marked in the whitelist, while the configuration of the blacklist will automatically become invalid.

[0046] This approach resolves the conflict between whitelist and blacklist configurations, clarifies priority processing rules, and prevents system processing anomalies or logical inconsistencies caused by rule conflicts, ensuring the stability and reliability of the field filtering process. Furthermore, this priority rule better aligns with actual business needs, as whitelists typically represent core fields that users explicitly require monitoring. Prioritizing whitelist rules ensures that changes to key fields are accurately captured, preventing the omission of core data changes due to blacklist interference, and further improving the accuracy of the linked list construction.

[0047] Optionally, the method for constructing a data warehouse zipper table further includes: using a double hash operation for data comparison when judging field changes.

[0048] Combination Figure 2 As shown in the embodiments of this disclosure, another method for constructing a data warehouse zipper table is provided, including: S201, based on user requirements, performs field change judgment and obtains a field list file.

[0049] S202, when performing field change judgment, a double hash operation is used for data comparison.

[0050] S203, Upload the field list file to the data warehouse system.

[0051] S204. Input the original table name, primary key field, and surrogate key field into the data warehouse system.

[0052] S205, Obtain the zipper table structure and zipper table processing logic generated by the data warehouse system, and use the zipper table structure and zipper table processing logic to develop data warehouse zipper tables.

[0053] In this embodiment, when performing data change judgment, the system no longer uses the traditional field-by-field comparison or single hash comparison method, but instead uses a double hash operation for data comparison. This effectively solves the hash collision risk inherent in traditional single hash comparison. Traditional single hashing concatenates all fields and hashes directly, which may result in the same hash value after concatenation of different field combinations, leading to missed data change detection. The double hash operation, through staged processing, can significantly reduce the probability of hash collisions and improve the accuracy of data change judgment. At the same time, compared with the field-by-field comparison method, the double hash operation converts field data into a fixed-length hash string, reducing the complexity of data comparison and improving the system's processing efficiency. It is especially suitable for complex data tables with a large number of fields, balancing accuracy and efficiency in judgment.

[0054] Optionally, when performing field change judgment, a double hash operation is used for data comparison, including: performing a hash once on each column that needs to be judged as having changed; concatenating all columns that have been hashed once, performing a double hash on the concatenated column to generate a 32-character string; and comparing the data using the 32-character string.

[0055] like Figure 3 As shown in this embodiment, a hash operation is first performed on each column of data that needs to be judged for field changes. In this scheme, the MD5 hash algorithm is used. After one hash, the data of each column is converted into a hash string of 32 characters, realizing the standardized processing of field data of different types and lengths. Then, the hash strings of all columns that have undergone one hash processing are concatenated according to the field order to form a long string with a length of 32 times the number of fields. Finally, a second MD5 hash operation is performed on the concatenated long string to generate a final hash string of a fixed length of 32 characters. The system determines whether the data has changed by comparing whether the final hash strings of the old and new data are consistent.

[0056] By employing phased hashing, the collision problem inherent in traditional single-hash comparisons is completely resolved. For instance, when two different fields are combined and concatenated to form identical strings, a single hash can lead to misjudgments. However, a two-stage hashing approach, by hashing each field individually before concatenation, effectively distinguishes such cases, ensuring the accuracy of data change detection. Furthermore, the standardized 32-character hash string facilitates system storage and comparison, improving data processing efficiency and consistency while reducing system storage pressure.

[0057] Optionally, the zipper table processing logic includes surrogate key generation logic, which includes: obtaining the maximum value of the surrogate key in the current zipper table, and setting the maximum value to 0 if there is no data in the current zipper table; performing a sorting operation on the newly opened data to generate an integer sequence m starting from 1; and setting the surrogate key value of the newly opened data to the maximum value of the current surrogate key plus m.

[0058] In this embodiment, the system first queries the existing zipper table to obtain the maximum value of the surrogate key. If there is no data in the current zipper table, i.e., it is in the initial construction state, the system will set the maximum value of the surrogate key to 0 by default. Next, for the data that needs to be added to the open chain, the system performs a sorting operation using the database's built-in sorting function to generate a continuous integer sequence m starting from 1. The length of this sequence is the same as the number of new open chain data entries. Finally, the surrogate key value of each new open chain data entry is set to the sum of the currently obtained maximum value of the surrogate key and the corresponding value in the sequence m, thereby generating a unique and continuous surrogate key.

[0059] This approach automates and standardizes surrogate key generation, resolving the difficulties in handling surrogate keys in traditional linked list development. It eliminates the need for manually writing complex surrogate key generation code, lowering the development threshold and reducing the probability of errors. Simultaneously, this generation method ensures the uniqueness and continuity of surrogate keys, optimizes the linked list's index structure, improves the efficiency of querying and joining data, avoids the inefficiency of traditional composite primary key queries, and enhances the performance of linked lists in data warehouse applications.

[0060] Combination Figure 4 As shown in the illustration, this disclosure provides an apparatus 400 for constructing a data warehouse zipper table, including a file acquisition module 401, a file upload module 402, a data input module 403, and a zipper table development module 404. The file acquisition module 401 is configured to determine field changes based on user requirements and obtain a field list file; the file upload module 402 is configured to upload the field list file to the data warehouse system; the data input module 403 is configured to input the original table name, primary key field, and surrogate key field into the data warehouse system; and the zipper table development module 404 is configured to acquire the zipper table structure and zipper table processing logic generated by the data warehouse system, so as to develop a data warehouse zipper table using the zipper table structure and zipper table processing logic.

[0061] The apparatus 400 for constructing data warehouse zipper tables provided in this embodiment of the present disclosure eliminates the need for manually writing complex processing logic and repeatedly debugging and verifying through a metadata-driven automated process. This significantly lowers the development threshold of zipper tables, greatly improves the development efficiency of data warehouse zipper tables, avoids logical omissions caused by manual coding, and ensures the standardization and consistency of zipper table construction.

[0062] Combination Figure 5 As shown in the figure, this disclosure provides an apparatus 50 for constructing a data warehouse zipper table, including a processor 500 and a memory 501. Optionally, the apparatus 50 may further include a communication interface 502 and a bus 503. The processor 500, communication interface 502, and memory 501 can communicate with each other via the bus 503. The communication interface 502 can be used for information transmission. The processor 500 can call logical instructions in the memory 501 to execute the method for constructing a data warehouse zipper table as described in the above embodiment.

[0063] Furthermore, the logic instructions in the aforementioned memory 501 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium.

[0064] The memory 501, as a computer-readable storage medium, can be used to store software programs and computer-executable programs, such as program instructions / modules corresponding to the methods in the embodiments of this disclosure. The processor 500 executes functional applications and data processing by running the program instructions / modules stored in the memory 501, that is, it implements the method of constructing a data warehouse zipper table in the above embodiments.

[0065] The memory 501 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created based on the use of the terminal device. Furthermore, the memory 501 may include high-speed random access memory and may also include non-volatile memory.

[0066] This disclosure provides a data warehouse system, including: a data warehouse system body, and the aforementioned apparatus for constructing a data warehouse zipper table. The apparatus for constructing the data warehouse zipper table is installed in the data warehouse system body. The installation relationship described herein is not limited to placement within the data warehouse system body, but also includes installation connections with other components of the data warehouse system, including but not limited to physical connections, electrical connections, or signal transmission connections. Those skilled in the art will understand that the apparatus for constructing the data warehouse zipper table can be adapted to any feasible data warehouse system body, thereby implementing other feasible embodiments.

[0067] The technical solutions of this disclosure can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes one or more instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in this disclosure. The aforementioned storage medium can be a non-transitory storage medium, such as a USB flash drive, external hard drive, read-only memory (ROM), random access memory (RAM), magnetic disk, or optical disk, etc., and other media capable of storing program code.

[0068] The foregoing description and accompanying drawings fully illustrate embodiments of this disclosure to enable those skilled in the art to practice them. Other embodiments may include structural, logical, electrical, procedural, and other changes. The embodiments represent only possible variations. Individual components and functions are optional unless explicitly required, and the order of operation may vary. Parts and features of some embodiments may be included in or replace parts and features of other embodiments. Moreover, the terminology used in this application is for describing embodiments only and is not intended to limit the claims. As used in the description of embodiments and claims, the singular forms “a,” “an,” and “the” are intended to equally include the plural forms unless the context clearly indicates otherwise. Similarly, the term “and / or” as used in this application means including one or more of the associated listed items and all possible combinations thereof. Additionally, when used in this application, the term "comprise" and its variations "comprises" and / or "comprising" refer to the presence of stated features, integrals, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components, and / or groups thereof. Without further limitations, an element defined by the phrase "comprises a..." does not exclude the presence of other identical elements in the process, method, or apparatus that includes said element. In this document, each embodiment may focus on the differences from other embodiments, and similar or identical parts between embodiments can be referred to mutually. For methods, products, etc., disclosed in the embodiments, if they correspond to the method section disclosed in the embodiments, the relevant parts can be referred to the description of the method section.

[0069] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the embodiments of this disclosure. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the systems, devices, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0070] The methods and products disclosed in the embodiments herein (including but not limited to devices and equipment) can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For instance, the division of units may be merely a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. In addition, the mutual coupling or direct coupling or communication connection shown or discussed may be through some interfaces, and the indirect coupling or communication connection of devices or units may be electrical, mechanical, or other forms. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to implement this embodiment according to actual needs. In addition, the functional units in the embodiments of this disclosure may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0071] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to embodiments of this disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions marked in the blocks may occur in a different order than that shown in the drawings. For example, two consecutive blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. In the descriptions corresponding to the flowcharts and block diagrams in the accompanying drawings, the operations or steps corresponding to different blocks may also occur in a different order than disclosed in the description, and sometimes there is no specific order between different operations or steps. For example, two consecutive operations or steps may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. Each block in a block diagram and / or flowchart, and combinations of blocks in a block diagram and / or flowchart, can be implemented using a dedicated hardware-based system that performs the specified function or action, or using a combination of dedicated hardware and computer instructions.

Claims

1. A method for constructing a data warehouse zipper table, characterized in that, Applied to data warehouse systems, including: Based on user requirements, determine field changes and obtain a field list file; Upload the field list file to the data warehouse system; Enter the original table name, primary key field, and surrogate key field into the data warehouse system; Obtain the structure and processing logic of the zipper table generated by the data warehouse system, and use the zipper table structure and processing logic to develop data warehouse zipper tables.

2. The method according to claim 1, characterized in that, Based on user requirements, perform field change detection to obtain a field list file, including: Download the original table field list file; Based on the user's needs, determine whether it is necessary to check for changes in all fields of the original table field list file; If it is necessary to determine changes in all fields, perform field change checks on all fields to obtain a field list file; If it is not necessary to check all field changes, edit the whitelist or blacklist and then check for field changes to obtain a field list file.

3. The method according to claim 2, characterized in that, Editing whitelists or blacklists includes: Add a column with a white header to the original table field list file, and mark the fields that need to be whitelisted as 'y' in the corresponding rows; or, Add a column with a black header to the original table field list file, and mark the fields that need to be blacklisted as y in the corresponding rows; The whitelist consists of fields that require change detection, while the blacklist consists of fields that do not require change detection.

4. The method according to claim 2, characterized in that, Also includes: If both a whitelist and a blacklist exist, the whitelist will take effect.

5. The method according to any one of claims 1 to 4, characterized in that, Also includes: When determining field changes, a double hash operation is used for data comparison.

6. The method according to claim 5, characterized in that, When determining field changes, a double hash operation is used for data comparison, including: Perform a hash operation once for each column whose field needs to be evaluated for changes. Concatenate all columns that have undergone a single hash, perform a double hash on the concatenated column, and generate a 32-character string; Data comparison is performed using a 32-character string.

7. The method according to any one of claims 1 to 4, characterized in that, The zipper table processing logic includes surrogate key generation logic, which includes: Get the maximum value of the proxy key in the current zipper table. If there is no data in the current zipper table, set the maximum value to 0. Perform a sorting operation on the newly opened chained data to generate an integer sequence m starting from 1; Set the proxy key value of the newly opened chain data to the maximum value of the current proxy key plus m.

8. An apparatus for constructing a data warehouse zipper table, characterized in that, include: The file acquisition module is configured to determine field changes based on user needs and obtain a field list file. The file upload module is configured to upload a field list file to the data warehouse system. The data input module is configured to input the original table name, primary key field, and surrogate key field into the data warehouse system. The zipper table development module is configured to obtain the zipper table structure and zipper table processing logic generated by the data warehouse system, so as to develop data warehouse zipper tables using the zipper table structure and zipper table processing logic.

9. An apparatus for constructing a data warehouse zipper table, comprising a processor and a memory storing program instructions, characterized in that, The processor is configured to, when running the program instructions, execute the method for constructing a data warehouse zipper table as described in any one of claims 1 to 7.

10. A data warehouse system, characterized in that, include: The data warehouse system itself; The apparatus for constructing a data warehouse zipper table as described in claim 8 or 9 is installed on the data warehouse system body.

Images