Method, system and electronic equipment for realizing supply data storage configuration of data warehouse

Abstract

The invention provides a data warehouse supply data storage configuration implementation method and system and electronic equipment. The method comprises the steps that S1, an underlying database uses kudu to create a customer dimension large-width table; s2, the front end uses a vue framework to create supply data storage configuration newly-added and modified pages; s3, the system administrator configures information of names, types and modes of the transmission data on the front-end configuration page and then completes storage; s4, the background logic completes information corresponding files and message fields configured at the front end, and a client table insertion record in the kudu; and S5, after the data warehouse supplies data, the kafka message is received and automatically and correspondingly stored in the background database, or the file is automatically and correspondingly stored in the background database after being analyzed. According to the method disclosed by the invention, aiming at the real-time kafka message sent by the data warehouse and the generated T-1 data file, data storage is directly completed through a configuration management interface function in an application service system, so that repeated and time-consuming development work is omitted.

Description

Technical Field

[0001] The present invention relates to the technical field of computer systems, and more specifically, to a method, system and electronic equipment for implementing data warehouse data storage configuration. Background Art

[0002] In current system development, data from the upstream system needs to be extracted from the data warehouse and transmitted to the downstream system in some way. However, no matter which method is used, the business first puts forward a requirement, and then the technology completes a development task. The codes are independent and not reusable. There are a large number of identical or similar codes in different business processing logics, which are seriously coupled, making maintenance difficult and the development cycle long.

[0003] Currently, commonly used data transmission methods include file transfer, real-time message transmission, direct database query, etc., but none of them have been configured. Summary of the invention

[0004] The present invention aims at the technical problems existing in the prior art and provides a method, system and electronic device for realizing the configuration of data storage for data warehouse. The purpose of the present invention is to directly complete the data storage through the configuration management interface function in the application business system for the real-time Kafka messages sent by the data warehouse and the generated T-1 data files, thereby omitting repetitive and time-consuming development work.

[0005] According to a first aspect of the present invention, a method for implementing data warehouse data storage configuration is provided, comprising the following steps:

[0006] Step S1. The underlying database uses Kudu to create a large wide table of customer dimensions;

[0007] Step S2. The front end uses the vue framework to create a new and modified page for data storage configuration;

[0008] Step S3. The system administrator configures the name, type, and mode of the transmitted data on the front-end configuration page and saves the configuration;

[0009] Step S4. The background logic completes the information corresponding to the files and message fields configured on the front end, and inserts records into the customer table in Kudu;

[0010] Step S5. After the data warehouse provides data, the Kafka message is automatically stored in the backend database, or the file is automatically stored in the backend database after parsing.

[0011] Based on the above technical solution, the present invention can also make the following improvements.

[0012] Optionally, the underlying database uses Kudu to establish a data storage configuration module for the front-end page and back-end management functions, and creates a large wide table of customer dimension with the primary key: customer id, which is unique to each customer. If not found, insert; if found, update.

[0013] Optionally, the front end uses the Vue framework to create a new and modified page for data entry configuration, and the page configuration information items include: data mode, field identifier, field name, field type, message subject, message scenario, file name, and file field order.

[0014] Optionally, after the front-end configuration is completed, the system administrator configures the supply data related fields at the front end, saves them, completes the addition and modification of the supply data information items, and inserts records other than the primary key into the customer dimension wide table in Kudu.

[0015] Optionally, the background logic completes the corresponding files and message fields of the data information items configured in the foreground, that is, the real-time messages and T-1 file fields correspond to the add, delete, modify and query functions of the table fields in the database.

[0016] Optionally, the receiving of the Kafka message and automatically storing it in the backend database includes:

[0017] The general code for receiving real-time messages from Kafka is encapsulated in the background. After the key in the Kafka message and the physical name of the field in the customer dimension wide table are completely matched, the value corresponding to the key in the Kafka message is inserted and updated.

[0018] Optionally, the file is automatically mapped to the backend database after parsing, including:

[0019] The general code for downloading and parsing T-1 files is encapsulated in the background. After the fields in the file are matched one by one according to the order of the fields configured in the customer dimension wide table, the data is inserted and updated.

[0020] Optionally, no matter how many new topics are added to the data warehouse or how many message scenarios are added to the existing topics, as long as the corresponding message fields are configured in the backend in advance, the real-time message data can be configured to be entered into the warehouse; similarly, no matter how many new T-1 data files are added, as long as the corresponding field order is configured in advance, the T-1 file data can be configured to be entered into the warehouse.

[0021] According to a second aspect of the present invention, a data warehouse data supply and storage configuration implementation system is provided, comprising:

[0022] The front-end page configuration module is used to create a large wide table of customer dimensions using Kudu in the underlying database and to create a new and modified page for data entry configuration using the Vue framework in the front-end;

[0023] The backend information field storage module is used by the system administrator to save the name, type, and mode of the transmitted data after configuring it on the frontend configuration page;

[0024] Kafka message receiving module, which is used to automatically store Kafka message reception in the backend database after the data warehouse provides data;

[0025] The file parsing module is used for the background logic to implement the information corresponding to the files and message fields configured on the front end, and insert records into the customer table in Kudu;

[0026] The Kafka message and file corresponding matching storage module is used for data warehouse data supply, and the files are automatically mapped to the backend database after parsing.

[0027] According to a third aspect of the present invention, there is provided an electronic device comprising a memory and a processor, wherein the processor is configured to implement the steps of a method for configuring data warehouse data supply and storage when executing a computer program stored in the memory.

[0028] Technical effects and advantages of the present invention:

[0029] The present invention provides a data warehouse data supply storage configuration implementation method, system and electronic device. By developing a data supply storage configuration function in a back-end management system, firstly, a data supply configuration interface is added to the back-end management, and data mode, field identifier, field name, field type, message topic, message scenario, file name, file field order, etc. are configured on the page, and then the background database inserts the configured related fields, and the background java code writes the general kafka message receiving and file parsing logic, so as to realize the function of matching the data items configured on the page with the data transmitted by the data warehouse one by one and storing them in the warehouse. The duplication of the code is avoided, the maintenance difficulty is reduced, and the development cycle is shortened.

[0030] Other features and advantages of the present invention will be described in the following description, and partly become apparent from the description, or understood by practicing the present invention. The purpose and other advantages of the present invention can be realized and obtained by the structures pointed out in the description, claims and drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] Figure 1 A flow chart of a method for implementing data warehouse data storage configuration provided by an embodiment of the present invention;

[0032] Figure 2 A structural block diagram of a data warehouse for data entry configuration provided by an embodiment of the present invention. DETAILED DESCRIPTION

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

[0034] It should be noted that the technology stack used in the embodiment of the present invention includes but is not limited to: Vue, Spring, Springboot, Kudu, Mysql, Redis, and Kafka.

[0035] It is understandable that, based on the defects in the background technology, the embodiment of the present invention proposes a data warehouse data storage configuration implementation method, specifically as follows Figure 1 As shown, the method comprises the following steps:

[0036] Step S1: The underlying database uses Kudu to create a large wide table of customer dimensions;

[0037] In this embodiment, the underlying database uses kudu to create a customer dimension wide table, specifically including: the underlying database uses kudu database design to create a customer dimension wide table t_wide_cust, with the primary key: cust_id (customer id), which is unique to the customer. If not present, insert; if present, update (the table is created first and can be expanded later, and different business requirement tables can be created directly through the configuration function).

[0038] Step S2, the front end uses the vue framework to create a new and modified page for data storage configuration;

[0039] The system backend has added a data field configuration function, that is, the real-time message and T-1 file fields correspond to the add, delete, modify and query functions of the table fields in the database.

[0040] The front-end uses the vue framework to create a configurable new addition and modification page for data entry. The configurable fields on the page are: data mode (real-time message / T-1 file, required, multiple selections are allowed, that is, the same field can be provided by both real-time message and T-1 file at the same time, and the data is updated and overwritten in order), field identifier, field name, field type (string / enumeration / number / date), message topic (required when "real-time message" is selected), message scenario (required when "real-time message" is selected), file name (required when "T-1 file" is selected), and file field order (required when "T-1 file" is selected).

[0041] Step S3: The system administrator configures the name, type, and mode of the transmission data on the front-end configuration page and saves the configuration;

[0042] This embodiment uses the latest account entry information of a customer in an actual scenario as an example for explanation.

[0043] After the front-end configuration is completed, the system administrator saves the data and completes the addition and modification of the supply information items. The following records are inserted into the customer dimension wide table t_wide_cust in Kudu except for the primary key cust_id:

[0044]

[0045] Step S4: The backend logic completes the data warehouse file and message field corresponding to the information configured on the front end, and inserts records into the customer table in Kudu;

[0046] The background logic completes the front-end configuration information corresponding to the data warehouse file and message fields, and corresponds the configured fields to the data items in the following two data supply modes of the data warehouse:

[0047] Real-time message, the message fields sent by the data warehouse are as follows:

[0048] topic=topic_a_001,vaule={"type_id":"deposit","cust_id":"1000000001","cu st_name":"Zhang San","cust_phone":"13412345678","deposit_time":"2024-10-1017:30:25","deposit_amt":"5000.00"}

[0049] Among them, topic_a_no_001 is the topic in the backend configuration, type_id is the message scenario, the configured value is deposit, and the key and value of the remaining fields need to match one by one, that is, the configuration field name is cust_name, the field type is string; the field name is cust_phone, the field type is string; the field name is deposit_time, the field type is date; the field name is deposit_amt, the field type is number;

[0050] T-1 file, the file fields generated by the data warehouse are as follows:

[0051] 1000000002^13412345670^2024-10-09 16:30:25^10000.00^V2

[0052] Among them, file_deposit_yyyyMMdd.txt is the file name in the backend configuration, the configuration field name is cust_phone, the field type is string, the field name is deposit_time, the field type is date, the field name is deposit_amt, the field type is number, the field name is cust_level, the field type is string, and the file field order is 2;

[0053] Step S5: After the data warehouse provides data, the Kafka message is automatically stored in the backend database, or the file is automatically stored in the backend database after parsing.

[0054] This implementation step is explained in two modes: "real-time message" and "T-1 file":

[0055] The automatic storage of Kafka message reception in the backend database includes: the backend encapsulates and writes the general code for receiving Kafka real-time messages. After the key in the Kafka message and the physical name of the field in the customer dimension wide table t_wide_cust are completely matched, the value corresponding to the key in the Kafka message is inserted and updated;

[0056] A Kafka message is received. In addition to the topic and message scenario, the message keys of other fields correspond to the physical names of the fields in k_wide_cust. The value corresponding to the message key is the record value that needs to be inserted and updated in k_wide_cust.

[0057] After the file is parsed, it is automatically mapped to the backend database, including: the backend encapsulates and writes the general code for downloading and parsing the T-1 file, and after the fields in the file are matched one by one according to the field sequence configured in the customer dimension wide table t_wide_cust, the data is inserted and updated.

[0058] After the file is retrieved and generated, it is downloaded and parsed. After the fields in the file match the field order configured in the backend one by one, they can be batch inserted and updated into k_wide_cust.

[0059] After the above real-time messages and T-1 files are stored in the database, the data stored in the customer dimension wide table t_wide_cust is as follows:

[0060] cust_id cust_name cust_phone deposit_time deposit_amt cust_level 1000000001 Zhang San 13412345678 2024-10-10 17:30:25 5000.00 1000000002 13412345670 2024-10-09 16:30:25 10000.00 V2

[0061] As can be seen from the above, through the data warehouse data storage configuration implementation method described in the embodiment of the present invention, no matter how many new topics are added to the data warehouse or how many new message scenarios are added under the existing topics, as long as the corresponding message fields are configured in the backend in advance, real-time message data storage configuration can be realized. No matter how many T-1 data files are added, as long as the corresponding field order is configured in advance, T-1 file data storage configuration can be realized.

[0062] According to a second aspect of the present invention, a data warehouse data storage configuration implementation system is provided, referring to Figure 2 As shown, the system comprises:

[0063] The front-end page configuration module is used to create a large wide table of customer dimensions using Kudu in the underlying database and to create a new and modified page for data entry configuration using the Vue framework in the front-end;

[0064] The backend information field storage module is used by the system administrator to save the name, type, and mode of the transmitted data after configuring it on the frontend configuration page;

[0065] Kafka message receiving module, which is used to automatically store Kafka message reception in the backend database after the data warehouse provides data;

[0066] The file parsing module is used for the background logic to implement the information corresponding to the files and message fields configured on the front end, and insert records into the customer table in Kudu;

[0067] The Kafka message and file corresponding matching storage module is used for data warehouse data supply, and the files are automatically mapped to the backend database after parsing.

[0068] It can be understood that a data warehouse data supply and data entry configuration implementation system provided by the present invention corresponds to a data warehouse data supply and data entry configuration implementation method provided by the aforementioned embodiments. The relevant technical features of a data warehouse data supply and data entry configuration implementation system can refer to the relevant technical features of a data warehouse data supply and data entry configuration implementation method, which will not be repeated here.

[0069] An embodiment of the present invention provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the implementation steps of a method for configuring data storage for data warehouse supply.

[0070] 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.

[0071] 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.

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

Claims

1. A data warehouse data storage configuration implementation method, characterized in that: The following steps are involved: Step S1. The underlying database uses Kudu to create a large wide table of customer dimensions; Step S2. The front end uses the vue framework to create a new and modified page for data storage configuration; Step S3. The system administrator configures the name, type, and mode of the transmitted data on the front-end configuration page and saves the configuration; Step S4. The background logic completes the information corresponding to the files and message fields configured on the front end, and inserts records into the customer table in Kudu; Step S5. After the data warehouse provides data, the Kafka message is automatically stored in the backend database, or the file is automatically stored in the backend database after parsing.

2. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: The underlying database uses Kudu to establish a front-end page and a data storage configuration module for back-end management functions, and creates a large wide table of customer dimension with primary key: customer id, which is unique to each customer. If not found, insert; if found, update.

3. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: The front end uses the Vue framework to create a new and modified page for data entry configuration. The page configuration information items include: data mode, field identifier, field name, field type, message subject, message scenario, file name, and file field order.

4. A data warehouse data storage configuration implementation method according to claim 3, characterized in that: After the front-end configuration is completed, the system administrator configures the supply data related fields at the front end, saves them, completes the addition and modification of supply data information items, and inserts records other than the primary key into the customer dimension wide table in Kudu.

5. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: The background logic completes the corresponding files and message fields of the data information items configured in the front-end, that is, the real-time messages and T-1 file fields correspond to the add, delete, modify and query functions of the table fields in the database.

6. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: The Kafka message receiving and automatically storing in the background database includes: The general code for receiving real-time messages from Kafka is encapsulated in the background. After the key in the Kafka message and the physical name of the field in the customer dimension wide table are completely matched, the value corresponding to the key in the Kafka message is inserted and updated.

7. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: After the file is parsed, it is automatically mapped to the backend database including: The general code for downloading and parsing T-1 files is encapsulated in the background. After the fields in the file are matched one by one according to the order of the fields configured in the customer dimension wide table, the data is inserted and updated.

8. A data warehouse data storage configuration implementation method according to claim 1, characterized in that: No matter how many new topics are added to the data warehouse or how many message scenarios are added to the existing topics, as long as the corresponding message fields are configured in the backend in advance, the real-time message data can be configured to be stored in the warehouse; similarly, no matter how many new T-1 data files are added, as long as the corresponding field order is configured in advance, the T-1 file data can be configured to be stored in the warehouse.

9. A data warehouse data storage configuration implementation system, characterized in that: include: The front-end page configuration module is used to create a large wide table of customer dimensions using Kudu in the underlying database and to create a new and modified page for data entry configuration using the Vue framework in the front-end; The backend information field storage module is used by the system administrator to save the name, type, and mode of the transmitted data after configuring it on the frontend configuration page; Kafka message receiving module, which is used to automatically store Kafka message reception in the backend database after the data warehouse provides data; The file parsing module is used for the background logic to implement the information corresponding to the files and message fields configured on the front end, and insert records into the customer table in Kudu; The Kafka message and file corresponding matching storage module is used for data warehouse data supply, and the files are automatically mapped to the backend database after parsing.

10. An electronic device, characterized in that: It includes a memory and a processor, and the processor is used to implement a data warehouse data supply and storage configuration implementation method as described in any one of claims 1 to 8 when executing a computer program stored in the memory.

Images