Eumeration value treatment method based on handling of data in data warehouse
Through the use of data dictionary and UDF functions, the enumeration value mapping and monitoring are automated, and the process complexity and consistency problems caused by the change of enumeration value in data warehouse development are solved, and the automatic management and monitoring of enumeration values are realized, ensuring data Consistency and credibility.
Patent Information
- Application Number
- CN202410971583.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-19
- Publication Date
- 2024-11-08
AI Technical Summary
During the development of data warehouses, changes in the enumeration value of the business system require manual notification and modification of the data warehouse system, resulting in complex processes and increased risks, and may lead to inconsistent mapping logic, affecting data consistency and credibility.
By developing and maintaining data dictionaries, generating enum value mapping tables, creating UDF functions for enum value mapping, and building monitoring configuration tables to automate the mapping and monitoring of enum values to ensure consistency of enum values.
The automatic mapping and monitoring of enum values is realized, which reduces development costs, avoids the problem of inconsistent enum values, ensures the consistency of enum values between the data warehouse and the business system, and reduces the risk of human error.
Smart Images

Figure CN118916365A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, and in particular to an enumeration value management method based on data warehouse data consistency. Background Art
[0002] Data consistency in a data warehouse is an important aspect of quality assurance. If data consistency cannot be guaranteed, the results users see in multiple places will be inconsistent, which will lead to doubts about the data. The data will lose credibility and become meaningless.
[0003] It is a common technique to define enumeration values during the development of business systems. First, enumeration values can be used to replace judgment constants, unify definitions, and reduce the occurrence of hard-coded constants. Secondly, enumeration value definitions and database fields are unified to control the occurrence of invalid value transmission events in business codes and ensure the legitimacy of database fields.
[0004] Because the business system and the data warehouse system are two independent systems, during the development of data warehouse computing tasks, it is inevitable to convert the enumeration values in the business system into corresponding Chinese for display due to the various code values stored in the business system. The common conversion method in the data warehouse is to hard-code the Chinese mapping of the enumeration value at that time in the code during task development. Each subsequent change in the business system needs to be notified to the data warehouse system, and the relevant R&D department will modify the code logic and go online at the same time.
[0005] Defects and shortcomings of the existing technology:
[0006] The problems with hard-coding mappings in the code during data warehouse development are:
[0007] 1. Every time the business system changes, the scheduled tasks in the data warehouse system need to be modified and put online. The complicated process increases the risk during the launch process.
[0008] 2. Every time a business system is changed, the data warehouse system R&D personnel need to be manually notified to make modifications. This link cannot guarantee that every modification will be notified, and the human-run process is not rigorous.
[0009] 3. In the data warehouse system, multiple tasks use this business table and business enumeration value. The mapping logic needs to be written once in different task codes. It is possible that the mapping logic of different tasks is different, such as mapping errors or missing mappings.
[0010] If the enumeration value of the business system changes, the downstream tasks involved in the enumeration value in the data warehouse need to be modified, which requires manual code reading and manual judgment, making it difficult to analyze downstream usage. Therefore, a method for enumeration value governance based on the consistency of warehouse data is proposed. Summary of the invention
[0011] In view of this, an embodiment of the present invention hopes to provide an enumeration value management method based on the data warehouse data consistency to solve or alleviate the technical problems existing in the prior art and at least provide a beneficial option.
[0012] The technical solution of the embodiment of the present invention is implemented as follows: A method for managing enumeration values based on data warehouse data consistency includes the following steps:
[0013] S1. Develop and maintain the data dictionary and obtain the result table A;
[0014] S2. Perform ETL processing based on the data dictionary result table A, save each enumeration value name into a record, and obtain the result table B;
[0015] S3. Develop UDF functions based on data dictionary result table B;
[0016] S4. Use UDF functions in data warehouse systems;
[0017] S5. Configure enumeration value monitoring according to data dictionary result table A, data dictionary result table B and configuration table C for building monitoring.
[0018] In some embodiments, in S1, the data dictionary can maintain a mapping of CODE and Chinese corresponding to the used enumeration values in the data warehouse system (which needs to be aligned with the business system during initialization). This method can be implemented more quickly to obtain the result table A.
[0019] In some embodiments, the result A table includes an enumeration value globally unique Key, an enumeration value name, an enumeration value CODE, and an enumeration value Chinese field.
[0020] In some embodiments, in S2, the result table B includes the enumeration value globally unique Key, the enumeration value name and the enumeration value mapping relationship.
[0021] In some embodiments, in S3, the UDF function logic is to read all enumeration value unique keys and mapping relationships of the data dictionary result table B;
[0022] Build Json data structure;
[0023] {"Unique Index":{"code1":"value1","code2":"value2"..},...} format;
[0024] According to the unique index Key parameter passed in by UDF, obtain the mapping of the corresponding unique index code and value in Json;
[0025] Based on this mapping relationship, according to the parameter code value passed in by the UDF, the corresponding enumeration value in Chinese is returned.
[0026] In some embodiments, in S4, the computing task in the data warehouse system replaces the hard-coded mapping relationship of the stock with the UDF function mapping, and directly uses the UDF function mapping for the increment.
[0027] In some embodiments, in the S5, a configuration table C that needs to be monitored is constructed to store the data warehouse source layer table (table name, enumeration value field name, existing known enumeration value list) that needs to be monitored, and the configuration table is traversed to generate and execute sql to execute the enumeration value field of the monitoring table, and compared with the existing known enumeration value list in the configuration table. If there is a difference, an alarm is sent to the R&D side through a group robot, DingTalk or email, and the R&D intervenes to determine whether to modify the data dictionary result tables A and B and the known enumeration value list of the monitoring configuration table C.
[0028] In some embodiments, the monitoring table configuration principle is that quasi-real-time storage or binlog storage is used first, and if not, the table for T+1 storage is monitored.
[0029] The embodiment of the present invention has the following advantages due to the adoption of the above technical solution:
[0030] 1. The present invention uses the system to implement a governance and guarantee solution for the consistency of enumeration values, ensuring that the Chinese enumeration values seen by users on each page are the same, and there will be no ambiguity in understanding. The mapping of enumeration values between various business systems is ensured to be consistent through technical means such as data dictionary, general enumeration value mapping UDF, and enumeration value change monitoring. The data dictionary capability ensures that the Chinese enumeration values between the data warehouse and the business, and between businesses remain consistent. The general enumeration value mapping UDF function can manually make the enumeration values in the data warehouse consistent, ensuring that the data enumeration values provided by the data warehouse seen by users are consistent.
[0031] 2. The present invention uses this system to ensure that every time the business system is changed, the data warehouse system does not need to be modified and put online, and can automatically perform mapping, thereby saving development and launch costs.
[0032] 3. The present invention can effectively avoid the problem of data errors caused by business system changes not being notified to the downstream data warehouse system through the monitoring capability in the system.
[0033] 4. The present invention uses the same enumeration value for multiple tasks in the data warehouse system of the system. It only needs to use the UDF function to pass the globally unique key of the enumeration value and the field to be mapped, thereby reducing the development workload and avoiding the occurrence of different mapping logics. If there is a problem with the enumeration value of the upstream business system, the downstream usage and scenarios can be checked. It only needs to remember the code search and search the globally unique key of the enumeration value to find all downstream tasks that use this enumeration value.
[0034] The above summary is for illustrative purposes only and is not intended to be limiting in any way. In addition to the illustrative aspects, embodiments and features described above, further aspects, embodiments and features of the present invention will be readily apparent by reference to the accompanying drawings and the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS
[0035] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0036] Figure 1 is a schematic diagram of the present invention;
[0037] Figure 2 It is a flow chart of the present invention. DETAILED DESCRIPTION
[0038] In the following, only some exemplary embodiments are briefly described. As those skilled in the art will appreciate, the described embodiments may be modified in various ways without departing from the spirit or scope of the present invention. Therefore, the drawings and descriptions are considered to be exemplary and non-restrictive in nature.
[0039] It should be noted that the terms "first", "second", "symmetrical", "array", etc. are only used to distinguish between description and position description purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the definition of "first", "symmetrical", etc. can explicitly or implicitly include one or more of these features; similarly, when the quantity of certain features is not limited in the form of words such as "two" or "three", it should be noted that this feature also explicitly or implicitly includes one or more feature quantities;
[0040] In the present invention, unless otherwise clearly specified and limited, the terms such as "installation", "connection", "fixation" and the like should be understood in a broad sense; for example, it can be a fixed connection, a detachable connection, or an integral molding; it can be a mechanical connection, a direct connection, welding, or an indirect connection through an intermediate medium, or it can be the internal connection of two elements or the interaction relationship between two elements. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood based on the description and drawings combined with specific circumstances.
[0041] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.
[0042] like Figure 1-Figure 2 As shown, an embodiment of the present invention provides an enumeration value management method based on data warehouse data consistency, including the following steps:
[0043] S1. Develop and maintain the data dictionary and obtain the result table A;
[0044] S2. Perform ETL processing based on the data dictionary result table A, save each enumeration value name into a record, and obtain the result table B;
[0045] S3. Develop UDF functions based on data dictionary result table B;
[0046] S4. Use UDF functions in data warehouse systems;
[0047] S5. Configure enumeration value monitoring according to data dictionary result table A, data dictionary result table B and configuration table C for building monitoring.
[0048] In this embodiment, specifically, in S1, the data dictionary can maintain a mapping of CODE and Chinese corresponding to the used enumeration values in the data warehouse system (which needs to be aligned with the business system during initialization). This method can be implemented more quickly to obtain the result table A.
[0049] In this embodiment, specifically, the result table A contains the globally unique key of the enumeration value, the name of the enumeration value, the code of the enumeration value, and the Chinese field of the enumeration value. The globally unique key of the enumeration value is usually set to set a globally unique identifier for the value defined by the enumeration type in software development. In order to ensure the accuracy and consistency of data between different systems and applications, the key of the enumeration value needs to be unique, so as to avoid confusion and errors;
[0050] In some programming languages, such as Java, enumerations can be associated with unique identifiers in the database, so that when the enumeration values are serialized into the database, each enumeration value will have a unique key to correspond to it. This mechanism is conducive to maintaining the integrity and accuracy of data during data storage and retrieval;
[0051] A globally unique key can also be used to identify each enumeration value in a distributed system, especially in a microservice architecture where each service may need to access and operate a shared database. In this case, a globally unique key can ensure that data between different services does not conflict.
[0052] In addition, globally unique keys can also enhance the readability and maintainability of the code, because when you see an enumeration value, you can directly identify its meaning through the key without looking at a lot of context information;
[0053] When implementing a globally unique key, you usually need to follow some best practices, such as:
[0054] Use standardized naming conventions to make key names clear and semantic;
[0055] Avoid using values that may change as keys, such as timestamps or system version numbers;
[0056] Whenever possible, the key should be short and meaningful so that people can understand and remember it.
[0057] Globally unique keys are an important means of ensuring data consistency and accuracy in software development, especially in application scenarios where data needs to be shared across systems and services;
[0058] The enumeration value name refers to the name given to each enumeration member when defining the enumeration type in programming. The enumeration value name usually has the following meanings:
[0059] Descriptive: The name of the enumeration value should be descriptive and clearly indicate the meaning or function of the enumeration value;
[0060] Semantic: The name of the enumeration value should contain enough semantic information so that other developers can understand the general function of each enumeration value through the name without having to look at the definition details of the enumeration;
[0061] Simplicity: Although the name needs to be descriptive, it should also be as concise as possible and avoid lengthy and complex expressions;
[0062] Consistency: In the same project or application, the naming of enumeration values should be kept consistent. This means that if an enumeration represents a "state", then all enumeration values representing the state should use a similar naming pattern;
[0063] Internationalization: If the application needs to support multiple languages, the names of enumeration values should avoid using words that may be translated incorrectly or have different meanings in different languages;
[0064] Avoid conflicts: The names of enumeration values should avoid conflicts with keywords, class names, method names, or other enumeration value names in the programming language to prevent naming ambiguity and errors;
[0065] Type safety: In some programming languages, the name of the enumeration value can be mapped to the database column name or the actual value. At this time, the choice of name directly affects the data type safety and data consistency;
[0066] Enumeration values are often called enumeration members or enumeration constants. When we use an enumeration type to define a fixed set of values, each value is an enumeration member. These members are usually given a name for easy identification and reference. In some contexts, enumeration values may be called "CODE", which usually means:
[0067] Encoding: Enumeration values can be considered as a set of predefined encodings that represent different states, options, or situations. Each encoding has a corresponding name for easy understanding and use;
[0068] Code: Enumeration values can be codes used to represent specific concepts or entities. For example, in the enumeration representing order status, there may be the following enumeration values: PEND I NG, SH IPPED, DEL IVERED. These values are used as codes to quickly identify the current status of an order;
[0069] Constants: Enumeration values are often treated as constants in programming languages, which means that their values are fixed while the program is running. This helps ensure consistency and predictability in your code;
[0070] Identifier: Enumeration values can be used as identifiers to identify specific categories or types of data. Enumeration values can be used as keywords or tags during data storage and transmission;
[0071] Symbol: In some cases, enumeration values can be considered as symbols, which represent a set of predefined concepts or options. These symbols are used in program logic to represent different situations or choices;
[0072] The meanings of the Chinese fields of the enumeration value as enumeration values include:
[0073] Readability: For scenarios that require Chinese descriptions, using Chinese fields can improve the readability of the code, making it easier to understand and maintain;
[0074] Localization: In applications that support multiple languages, using Chinese fields can help localize the application so that enumeration values can adapt to different language environments;
[0075] Contextual relevance: Chinese fields can better reflect concepts or entities in context, especially in enumerations related to specific domains. Using domain-specific Chinese names can increase the contextual relevance of the code.
[0076] Avoid misunderstandings: In some cases, using English as enumeration values may cause misunderstandings or confusion, especially for developers who are not familiar with English. Using Chinese fields can avoid this situation;
[0077] Coding consistency: Even if the enumeration value uses Chinese fields, the coding style should be consistent, such as using unified naming rules, to ensure the neatness and consistency of the code.
[0078] In this embodiment, specifically, in S2, the result table B includes the globally unique key of the enumeration value, the name of the enumeration value and the mapping relationship of the enumeration value. The above setting of the enumeration value mapping relationship refers to the relationship in programming that associates each member of the enumeration value with a specific data, function, resource or behavior. This mapping relationship can be implemented in various ways, including associating the enumeration value with a database record, mapping the enumeration value to a specific business logic process, associating the enumeration value with the setting in the configuration file, etc.;
[0079] The meaning of the enumeration value mapping relationship includes:
[0080] Data association: Each member of the enumeration value can be mapped to a record in the database or a row in the table, so that the data in the database can be accessed or manipulated through the enumeration value;
[0081] Function association: Each member of the enumeration value can be associated with a specific function or method, so that the corresponding function can be called through the enumeration value in the program to achieve different functions;
[0082] Resource association: The members of the enumeration value can be mapped to different resources in the system, such as files, network services, etc. Through the enumeration value, these resources can be easily accessed or managed;
[0083] Behavior association: Each member of the enumeration value can represent a behavior or state. For example, different game states (start, in progress, end) in the game can be represented by enumeration values, and each state can trigger different game logic;
[0084] Configuration association: The members of the enumeration value can be mapped to different settings in the configuration file, so that these settings can be read or modified through the enumeration value when the program is running.
[0085] In this embodiment, specifically, in S3, the UDF function logic is to read all the enumeration value unique keys and mapping relationships in the data dictionary result table B;
[0086] Build Json data structure;
[0087] {"Unique Index":{"code1":"value1","code2":"value2"..},...} format;
[0088] According to the unique index Key parameter passed in by UDF, obtain the mapping of the corresponding unique index code and value in Json;
[0089] Based on this mapping relationship, according to the parameter code value passed in by the UDF, the corresponding enumeration value in Chinese is returned.
[0090] In this embodiment, specifically, in S4, the computing task in the data warehouse system replaces the hard-coded mapping relationship of the stock with the UDF function mapping, and directly uses the UDF function mapping for the increment.
[0091] In this embodiment, specifically, in S5, a configuration table C that needs to be monitored is constructed to store the data warehouse source layer table (table name, enumeration value field name, existing known enumeration value list) that needs to be monitored, and the configuration table is traversed to generate and execute sql to execute the enumeration value field of the monitoring table, and compared with the existing known enumeration value list in the configuration table. If there is a difference, an alarm is sent to the R&D side through a group robot, DingTalk or email, and the R&D intervenes to determine whether to modify the data dictionary result tables A and B and the known enumeration value list of the monitoring configuration table C.
[0092] In this embodiment, specifically, the monitoring table configuration principle is that quasi-real-time storage or binlog storage is used first, and if not, the T+1 storage table is monitored.
[0093] In this embodiment, specifically, the enumeration value is a mapping relationship between the code and Chinese that is redefined in the business system, such as order status and order channel, etc. The business system generally stores the code value in order to save storage and convenience during storage;
[0094] The data dictionary defines a mapping relationship between code values and Chinese values in business, and this relationship is usually stored in the database;
[0095] UDF function is a user-defined calculation processing logic in the data warehouse. The function can be referenced in SQL task development to perform data logic processing calculations.
[0096] In this embodiment, specifically, the order status includes placing an order, receiving an order, confirming an order, and paying.
[0097] In this embodiment, specifically, the order placement channels include a client and a mini program.
[0098] In this embodiment, specifically, the traditional A / B test can only target a single test object (including driver ID, order ID, etc.), and map the test object to two groups of strategies, test group A and test group B, at the same time point, to ensure that all users participating in the test can only enter one of the test strategies at the same time point;
[0099] Time slice rotation test: By evenly dividing the time dimension, different time slices are mapped to the strategies of test group A and test group B in turn, ensuring that all users participating in the test maintain consistent test strategies at the same time and in the same space.
[0100] In the present embodiment, specifically, for those skilled in the art, it can be further realized that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to the function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.
[0101] At the same time, those skilled in the art can understand that all or part of the processes in all the above-mentioned embodiments can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media provided in this application and used in the embodiments may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (SSRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), RAMbus direct RAM (RDRAM), direct RAMbus dynamic RAM (DRDRAM), and RAMbus dynamic RAM (RDRAM), etc.
[0102] In this embodiment, specifically, it can be implemented in whole or in part by software, hardware, firmware or any other combination. When implemented by software, the above embodiments can be implemented in whole or in part in the form of computer program goods;
[0103] When loading or executing a computer instruction or computer program on a computer, a process or function according to an embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. Computer instructions can be stored in a computer-readable storage medium, or transmitted from a computer-readable storage medium to another computer-readable storage medium, for example, a computer instruction can be transmitted from a website site, a computer, a server, or a data center by wired or wireless (such as infrared, wireless, microwave, etc.) mode to another website site, computer, server, or data center. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server, a data center, etc. that contains one or more available media sets. Available media can be magnetic media (for example, floppy disks, hard disks, tapes), optical media (for example, DVDs), or semiconductor media. The semiconductor medium can be a solid-state hard disk.
[0104] It should be understood that in the various embodiments of the present application, the size of the serial numbers of the above-mentioned processes does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.
[0105] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example 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 performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.
[0106] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the system described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.
[0107] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of software goods. The computer software goods are stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc., various media that can store program codes.
[0108] When the present invention is working: the data dictionary of the system is used to maintain the business enumeration values commonly used by various business systems in the whole company, or each business line has its own enumeration values of business attributes for maintenance, and the data dictionary service provides an interface query capability to query the mapping relationship between the code of a certain enumeration value and Chinese;
[0109] All business systems can call this service for query, which is equivalent to providing a unified enumeration value maintenance for each business to use. This is a company-wide common capability.
[0110] This capability ensures that the business enumeration value mapping of each business line is consistent at the company level.
[0111] Based on the unified enumeration value mapping relationship provided by the data dictionary capability, this part of the data is processed by ETL and stored in a dimension table of a data warehouse. Each enumeration value defines a global unique key.
[0112] Develop a UDF function to read the mapping relationship data in the dimension table, and directly return the mapped Chinese text based on the global unique key and business code value passed in by the ETL processing task;
[0113] Each data warehouse task uses this UDF function and the globally unique key of the enumeration value for mapping, which can ensure the consistency of the mapping logic of each task in the data warehouse. In addition, each change only requires the business to modify the data dictionary, and no adaptation is required in the data warehouse, and the change is automatic.
[0114] The original tables collected from the business system in the purpose monitoring data warehouse (generally divided into T+1 collection and quasi-real-time collection). Maintain a configuration table to record the list of tables and fields in the data warehouse source layer corresponding to the business table to be monitored and the existing enumeration values. Develop a scheduled task to read the configuration table, assemble SQL to traverse and execute the query of the actual enumeration value CODE in the field data of the table, and compare it with the existing enumeration value CODE under maintenance. If inconsistency is found, send a DingTalk group robot, WeChat group robot, or email alarm to notify the corresponding R&D personnel to handle it;
[0115] This technical solution can effectively avoid upstream and downstream data inconsistencies caused by adding or modifying enumeration values in the business system without notifying the downstream data warehouse system, and the situation where enumeration values in the data warehouse system are mapped to null values.
[0116] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of various changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.
Claims
1. A method for managing enumeration values based on the consistency of data warehouse data, characterized in that: The following steps are involved: S1. Develop and maintain the data dictionary and obtain the result table A; S2. Perform ETL processing based on the data dictionary result table A, save each enumeration value name into a record, and obtain the result table B; S3. Develop UDF functions based on data dictionary result table B; S4. Use UDF functions in data warehouse systems; S5. Configure enumeration value monitoring according to data dictionary result table A, data dictionary result table B and configuration table C for building monitoring.
2. The enumeration value management method based on data warehouse data consistency according to claim 1 is characterized by: In S1, the data dictionary can maintain a mapping of CODE and Chinese corresponding to the enumeration values used in the data warehouse system (which needs to be aligned with the business system during initialization). This method can be implemented more quickly to obtain the result table A.
3. The enumeration value management method based on data warehouse data consistency according to claim 2 is characterized by: As a result, table A contains the enumeration value globally unique Key, enumeration value name, enumeration value CODE, and enumeration value Chinese fields.
4. The enumeration value management method based on data warehouse data consistency according to claim 1 is characterized by: In S2, the result table B includes the enumeration value globally unique Key, the enumeration value name and the enumeration value mapping relationship.
5. The enumeration value management method based on data warehouse data consistency according to claim 1 is characterized by: In the S3, the UDF function logic is to read all the enumeration value unique keys and mapping relationships in the data dictionary result table B; Build Json data structure; {"Unique Index":{"code1":"value1","code2":"value2"..},...} format; According to the unique index Key parameter passed in by UDF, obtain the mapping of the corresponding unique index code and value in Json; Based on this mapping relationship, according to the parameter code value passed in by the UDF, the corresponding enumeration value in Chinese is returned.
6. The method for managing enumeration values based on data warehouse data consistency according to claim 1 is characterized in that: In S4, the computing tasks in the data warehouse system replace the hard-coded mapping relationship of the stock with the UDF function mapping, and directly use the UDF function mapping for the increment.
7. The method for managing enumeration values based on data warehouse data consistency according to claim 1 is characterized in that: In the S5, a configuration table C that needs to be monitored is constructed to store the data warehouse source layer table (table name, enumeration value field name, existing known enumeration value list) that needs to be monitored, and the configuration table is traversed to generate and execute SQL to execute the enumeration value field of the monitoring table, and compared with the existing known enumeration value list in the configuration table. If there is a difference, an alarm is sent to the R&D side through a group robot, DingTalk or email, and the R&D intervenes to determine whether to modify the data dictionary result tables A and B and the known enumeration value list of the monitoring configuration table C.
8. The method for managing enumeration values based on data warehouse data consistency according to claim 7 is characterized in that: The monitoring table configuration principle is to give priority to quasi-real-time or binlog storage. If not, monitor the table with T+1 storage.
Citation Information
Cited By
Enumerated data processing method and device, electronic equipment and storage medium
CN120179942A
