A data warehouse data construction and management method and terminal

By obtaining and processing the original data tables to be entered into the database and creating standard data tables, the problems of data redundancy and low governance efficiency are solved, efficient governance and standardization of data warehouses are achieved, and costs are reduced.

CN114328759BActive Publication Date: 2024-12-03XIAMEN MEIYA PICO INFORMATION CO LTD
View PDF 1 Cites 0 Cited by

Patent Information

Application Number
CN202111622479.9
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2021-12-28
Publication Date
2024-12-03
Estimated Expiration
2041-12-28

AI Technical Summary

Technical Problem

The existing data governance technology has problems such as data redundancy, low governance efficiency and high cost. Especially in the process of asset data governance, there is a lack of unified data standards and data models, resulting in low data extraction efficiency and poor quality.

Method used

By obtaining the original data table to be entered into, judging and creating standard data tables, filtering, deduplication, format conversion and UDF function processing, reducing data redundancy, realizing rapid data governance and entry into the database, using data construction and management methods of data warehouses and terminals, including memory and processors, execute computer programs for data processing.

Benefits of technology

It realizes high data standardization, reduces redundancy, improves data extraction efficiency and governance costs, and is suitable for various data access, governance and query.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN114328759B_ABST
    Figure CN114328759B_ABST
Patent Text Reader

Abstract

The present invention discloses a data construction and management method and a terminal for a data warehouse. After obtaining an original data table to be stored in the warehouse, the original data table to be stored in the warehouse generates corresponding standard data and then maps the data with a standard field table to obtain a standard table, or directly creates the corresponding standard data based on the original data table to be stored in the warehouse, thereby not needing to store both the original data table and the generated standard data in the database, reducing data redundancy in the database and realizing rapid data management and storage; at the same time, the field data in the original data table is filtered, deduplicated, format converted, and processed by UDF functions, so that the stored data is highly standardized and the redundancy of the stored data is reduced, thereby improving data extraction efficiency and reducing data management costs.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of data processing, and in particular to a data warehouse data construction and management method and terminal. Background Art

[0002] With the rapid development and widespread application of computer network and database technology, information management in all walks of life has entered a new era. Against the backdrop of the current rapid increase in data volume, governments and enterprises are in urgent need of data services and data governance. Among them, in the process of asset data governance, there are various problems such as chaotic asset data, disordered data, incomplete data, etc., which makes the data governance process complicated. In addition, it is difficult for data governance to form a standardized pipeline processing, which makes the governance effect low and the quality of asset data after governance is poor. At the same time, after the governance process is programmed, it is still necessary to check the corresponding program to know the process steps of governance. Therefore, the current asset data governance can no longer meet the requirements of today's data asset governance.

[0003] At the same time, early databases were mainly independent databases. These databases were applied to various aspects of data processing, with multiple systems scattered and no standardized and unified data standards and data models. Due to the lack of unified data planning, reliable data sources and data standards, the data could not be standardized, so there was a certain amount of redundancy. In addition, there was a problem of human intervention in the data processing process. The cost of establishing a data warehouse in the presence of a large amount of messy data would cause unpredictable risks relative to small and medium-sized enterprises.

[0004] Therefore, the current data governance has problems such as scattered business knowledge systems, complex data warehouse structures, and multiple data extraction procedures, which have led to increased data warehouse R&D costs and low data extraction efficiency. It is unable to provide small and medium-sized teams with a method to quickly and efficiently extract data, convert data, clean and encrypt data, and store data in multiple dimensions. Summary of the invention

[0005] The technical problem to be solved by the present invention is to provide a data warehouse data construction and management method and terminal, so as to improve the data management efficiency of the data warehouse and reduce the cost.

[0006] In order to solve the above technical problems, the technical solution adopted by the present invention is:

[0007] A data warehouse data construction and management method, comprising:

[0008] Get the original data table to be stored;

[0009] Determine whether there is an original data table corresponding to the original data table to be stored in the data warehouse;

[0010] If it exists, obtaining corresponding field data according to the access table;

[0011] The field data is filtered, deduplicated, formatted and processed by UDF functions to obtain standard data;

[0012] Storing the standard data in a data warehouse;

[0013] If it does not exist, the corresponding standard data is created based on the original data table to be stored.

[0014] In order to solve the above technical problems, another technical solution adopted by the present invention is:

[0015] A data warehouse data construction and management terminal includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, each step in the data warehouse data construction and management method described above is implemented.

[0016] The beneficial effects of the present invention are as follows: after obtaining the original data table to be stored in the warehouse, the corresponding standard data is generated from the original data table to be stored in the warehouse and then mapped with the standard field table to obtain a standard table, or the corresponding standard data is directly created based on the original data table to be stored in the warehouse, so that there is no need to store both the original data table of the warehouse and the generated standard data in the database, which reduces data redundancy in the database and realizes rapid data management and storage; at the same time, the field data in the original data table is filtered, deduplicated, format converted, and processed by UDF functions, so that the stored data is highly standardized while reducing the redundancy of the stored data, thereby improving data extraction efficiency and reducing data management costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 A flowchart of a method for building and managing data in a data warehouse according to an embodiment of the present invention;

[0018] Figure 2 Another step flow chart of a method for building and managing data in a data warehouse according to an embodiment of the present invention;

[0019] Figure 3 A schematic diagram of the structure of a data warehouse data construction and management terminal according to an embodiment of the present invention.

[0020] Description of symbols DETAILED DESCRIPTION

[0021] In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.

[0022] Please refer to Figure 1 , a data warehouse data construction and management method, comprising:

[0023] Get the original data table to be stored;

[0024] Determine whether there is an access table corresponding to the original data table to be stored in the data warehouse;

[0025] If it exists, obtaining corresponding field data according to the access table;

[0026] The field data is filtered, deduplicated, formatted and processed by UDF functions to obtain standard data;

[0027] Storing the standard data in a data warehouse;

[0028] If it does not exist, the corresponding standard data is created based on the original data table to be stored.

[0029] From the above description, it can be seen that the beneficial effects of the present invention are: after obtaining the original data table to be stored in the warehouse, the corresponding standard data is generated from the original data table to be stored in the warehouse and then mapped with the standard field table to obtain a standard table, or the corresponding standard data is directly created based on the original data table to be stored in the warehouse, so that there is no need to store both the original data table of the warehouse and the generated standard data in the database, which reduces data redundancy in the database and realizes rapid data management and storage; at the same time, the field data in the original data table is filtered, deduplicated, format converted, and processed by UDF functions, so that the stored data is highly standardized while reducing the redundancy of the stored data, thereby improving data extraction efficiency and reducing data management costs.

[0030] Further, the standard table includes a view table and a physical table;

[0031] The step of determining whether there is a standard table corresponding to the original data table to be stored in the data warehouse comprises:

[0032] Determine the type of the standard table corresponding to the original data table to be stored. If it is a physical table, determine whether the original data table to be stored has input standards. If so, it means that there is a standard table corresponding to the original data packet to be stored.

[0033] If it is a view table, it is determined whether there is a corresponding access configuration for the original data table to be stored. If so, it means that there is a standard table corresponding to the original data packet to be stored.

[0034] From the above description, it can be seen that by adopting different judgment criteria for different types of original data tables to be stored, physical tables and view tables can be distinguished and different forms of judgment can be performed, thereby improving the ability to distinguish the original data to be stored.

[0035] Furthermore, if the standard table does not exist, creating a standard table corresponding to the original data table to be stored includes:

[0036] If the original data table to be stored is a physical table and does not have an input standard, a corresponding standard table is created according to a preset standard field table.

[0037] It can be seen from the above description that when the original data table to be stored does not have a corresponding input standard, the physical table is automatically generated into a standard table directly according to the standard field table, so that the data can be quickly stored in the standard model.

[0038] Furthermore, if the standard table does not exist, creating a standard table corresponding to the original data table to be stored includes:

[0039] If the original data table to be stored is a view table and there is no corresponding access configuration, the corresponding data input layer and configuration fields are set according to the original data table to be stored, and the corresponding configuration is generated.

[0040] From the above description, it can be seen that when there is no corresponding access configuration for the original data table to be stored in the warehouse, the corresponding data input layer, configuration fields and corresponding configuration are set according to the original data table to be stored in the warehouse, that is, after the corresponding access configuration is generated, the corresponding steps of obtaining the corresponding field data according to the access table are executed, so as to facilitate the access to the original data table in the warehouse.

[0041] Furthermore, before storing the standard data in the data warehouse, the process further includes:

[0042] If the type of the standard table corresponding to the standard data is a physical table, the standard data is mapped according to the standard table.

[0043] It can be seen from the above description that when the type of the standard table is a physical table, the standard data is mapped according to the standard field table corresponding to the standard table, thereby achieving standardization of the original data table to be stored.

[0044] Furthermore, after mapping the standard data, the method further includes:

[0045] Determine whether a secondary governance instruction is received, and if not, transfer the standard data to the doris operator and store it in the data warehouse;

[0046] If so, the standard data is input into a preset management terminal; and different management operators are sequentially obtained to manage the standard data to obtain management data.

[0047] From the above description, it can be seen that by judging whether to process the standard data, when the standard data is simple and small in quantity, it is directly put into the warehouse; when the standard data is highly complex and large in quantity, the standard data is processed and supplemented for the second time before being put into the warehouse, so that the standard data can meet the needs of different businesses and improve the practicality of the data warehouse.

[0048] Furthermore, obtaining the governance data further includes:

[0049] Get the data group statistics calculator;

[0050] The management data is grouped according to the data grouping system calculator to form a business wide table.

[0051] From the above description, it can be seen that by using data grouping operators to uniformly classify and group governance data, and combining different operators to quickly clean data topics, the original data tables to be stored are cleaned and stored, which improves the uniformity of the data and reduces data redundancy.

[0052] Furthermore, the governance operator includes a data filtering operator, a data deduplication operator, a data association operator, a data format conversion operator, a UDF function operator, a window operator, a business code operator and a sink operator.

[0053] From the above description, we can see that based on Flink's multiple operators and the combination of operators, the full process of plasticizer cleaning is realized, and data cleaning is streamlined.

[0054] As can be seen from the above description, the process of obtaining the original data table to be stored includes:

[0055] The data source type of the original data table to be stored is determined. If it is a heterogeneous data source, the heterogeneous data source is synchronized through dataXWeb.

[0056] From the above description, we can see that by accessing other heterogeneous data sources through dataXWeb, we can achieve full and incremental synchronization of data, and efficiently synchronize data to the doris data warehouse; and we can synchronize different heterogeneous data sources at the same time on multiple devices, further improving the efficiency of data access.

[0057] Please refer to Figure 2 The present invention also provides a data warehouse data construction and management terminal, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements each step in the above-mentioned data warehouse data construction and management method when executing the computer program.

[0058] The data construction and management method and terminal of the data warehouse described above in the present invention can be applied to various types of data access, management and query, such as data based on protocols such as JSM, kafka, MQTT, data based on files such as CSV, EXCEL, JSON, XML, data based on protocols such as HTTP, REST, etc., which are described below through specific implementation methods:

[0059] Embodiment 1

[0060] Please refer to Figure 1 , a data warehouse data construction and management method, comprising:

[0061] S1. Obtain the original data table to be stored;

[0062] S2. Determine whether there is a standard table corresponding to the original data table to be stored in the data warehouse; specifically: the standard table includes a view table and a physical table; determine the type of the standard table corresponding to the original data table to be stored, if it is a physical table, determine whether there is an input standard for the original data table to be stored, if so, it means that there is a standard table corresponding to the original data to be stored; if it is a view table, determine whether there is a corresponding access configuration for the original data table to be stored, if so, it means that there is a standard table corresponding to the original data to be stored;

[0063] If it does not exist, execute S3, create a standard table corresponding to the original data table to be stored, and then execute step S4; specifically: if the original data table to be stored is a physical table and there is no input standard, execute S31, create a corresponding standard table according to a preset standard field table; if the original data table to be stored is a view table and there is no corresponding access configuration, execute S32, set the corresponding data input layer and configuration field according to the original data table to be stored, and generate the corresponding configuration;

[0064] If yes, execute S4, obtain the field data corresponding to the standard table in the original data table according to the standard table;

[0065] S5, filtering, deduplication, format conversion and UDF function processing are performed on the field data in sequence to obtain standard data;

[0066] S6. If the type of the standard table corresponding to the standard data is a physical table, mapping the standard data according to the standard table;

[0067] S7, storing the standard data in a data warehouse;

[0068] This embodiment provides a specific application scenario. The data warehouse includes a data detail layer (DWD, DataWarehouse Detail), a data dimension theme table layer (DWS, Data WareHouse Servce), and a resource library layer; the DWD layer is further divided into a view layer and a physical layer; the automatic table creation, extraction, conversion, and loading are realized through the DWD layer and based on kettle; the details are as follows:

[0069] When JSM, kafka, MQTT and other protocol data are used as the data input layer;

[0070] S1. Create a transformation task to determine whether the original data table exists, and call it based on the job, that is, to obtain the original data table;

[0071] S2. Based on the term standard table (table_standard), determine whether the standard table corresponding to the original data table is a physical table or a view table. If it is a physical table, determine whether the corresponding physical table exists by determining whether its table name exists, such as whether in_standard exists. If not, execute S31. Based on the standard field table (table_standard_field), create the corresponding field and table name to obtain a standard table; if a physical table exists, execute S4; if it is a view table, determine whether the configured original data table exists. If not, execute S32, and then execute step S4. If it exists, execute S4;

[0072] Among them, S32 includes:

[0073] S321. In Kettle, use JSM, Kafka, MQTT protocol and other data as the data input layer, configure fields, set the read batch to 1,000, and set the configuration not to submit the offset configuration displacement mechanism, that is, to match the field with table_standard_field; not submitting means that the 1,000 data in this batch have no consumption access governance, but are only used to obtain fields and corresponding field types. The offset displacement will be submitted in the next consumption;

[0074] S322, kettle string operation, operation key removes leading and trailing spaces and special characters;

[0075] S323, read the 1000 rows of data through a preset Java script, obtain all fields and field types, obtain the maximum value of the field by judging the value of the field, and make a judgment on the type (for example, if the value of the field is 1, the type is int type, and if this value is greater than int type, it is considered to be Long type), generate a standard creation statement, and generate the standard specified md5 field and timestamp field; create a table partitioning strategy based on the standard customized md5 field and timestamp field, and configure how many partition segments to delete in front; the md5 field is used for later deduplication, deduplication operator filtering, and selection of all fields, etc. If it is based on a specified field, deduplication is performed through the specified field; that is, read the data of the specified topic in Kafka based on Java, parse record.value() and convert it into json data after reading, and then read the key in json;

[0076] S324, create table script: call doris data source to execute the create statement in S323;

[0077] S4. Configure JSM, Kafka, and MQTT protocol connection information in the kettle according to the standard table, configure the field data and type, consume from the most open offset, and automatically submit the offset position; after completion, determine whether the entire data packet is empty. If it is empty, do not execute further, that is, if all data in the current data packet is empty, or the data packet is empty, terminate the process;

[0078] S5, filtering, deduplication, format conversion and UDF function processing are performed on the field data in sequence to obtain standard data;

[0079] The specific steps include:

[0080] S51, data filtering: filter all fields with empty values ​​and remove leading and trailing spaces;

[0081] S52, data deduplication: deduplication judgment is performed on all fields of this batch of data, and a unique md5 field is generated;

[0082] S53: Data format conversion: perform data type conversion such as string to date, string to integer, etc., convert the general date format into YYYY-MM-DD, convert the general time format into HH-MM-SS type, and insert the timestamp field;

[0083] S54: UDF function processing: converting the corresponding fields of the Java online code UDF function, such as field completion, field encryption and decryption, etc.

[0084] S6. If the type of the standard table corresponding to the standard data is a physical table, the standard data is mapped according to the standard table; specifically, if the standard table is a view table, no mapping is required; if the standard table is a physical table, the access domain name is directly converted into a standard field and a corresponding standard field type through a standard field table (table_standard_field) mapping;

[0085] S7, storing the standard data in a data warehouse;

[0086] In another optional implementation, if the file protocol data such as CSV, EXCEL, JSON, XML, etc. is used as the data input layer; then step S321 is changed to S321a: read the file (i.e., the original data table) according to the standard table, configure the fields, write a java script based on kettle to uniformly transcode the file into the UTF-8 protocol, and assemble each json format for data delivery;

[0087] At the same time, step S4 is changed to S4a: determine whether the entire data packet is empty, and if so, do not proceed further, that is, if all data in the current data packet is empty, or the data packet is empty, terminate the process.

[0088] Embodiment 2

[0089] The difference between this embodiment and the first embodiment is that the protocol data such as HTTP and REST is used as the data input layer;

[0090] Change step S321 to S321b:

[0091] S3211. Get the request header and various parameters of the request header based on the kettle configuration;

[0092] S3212, configure the splicing interface request body content;

[0093] S3213. Configure the splicing interface request body and all parameter contents of the request header to be output to the log for later testing and verification;

[0094] S3214, configure HTTP access component, configure URL address, request protocol POST\GET protocol, configure domain name for obtaining result return;

[0095] S3215. Write a java script to convert the returned data format into the common json format. The format after conversion is as follows:

[0096] {"page":"1","pageSize":"100","data":[{"keyField":"",…}]}

[0097] Among them, "page":"1" means: the current page number starts from 1;

[0098] "pageSize": "100" means: the number of paging items is 100;

[0099] “data”:[{“keyField”:””,…}] means: the returned data type, where keyField is the returned field;

[0100] Then, the returned fields are used as row data and steps S322-S324 are executed;

[0101] After completing step S324, execute step S325: S3211-S3215;

[0102] Step S326: parallelize and separate the js script to obtain the current page, and pass the current page + 1 into the above steps to perform paging execution S3211-S3215.

[0103] Embodiment 3

[0104] This embodiment differs from the first or second embodiment in that data based on file storage, column storage, relational database, memory database, full-text search, document database, etc. is used as the data input layer;

[0105] Step S3 is changed to S3a, comprising the steps:

[0106] S31: Execute the SQL script to display the corresponding field header and type;

[0107] S32: Write a table creation statement based on the Java script to generate a standard creation statement, and generate a standard specified md5 field and a timestamp field; perform a partitioning strategy based on the standard customized md5 field and timestamp field;

[0108] S33: Create table script: call doris data source to execute creation statement;

[0109] Changing step S4 to S4b comprises the steps of:

[0110] S41: Query the total number of data, and obtain the total number for the next step of paging to obtain data;

[0111] S42: Setting query parameters: setting the total number of queries to a constant, setting the number of pages, the current page number, and assembling a dynamic query statement;

[0112] S43: Determine whether the entire data packet is empty. If it is empty, do not proceed further. That is, if all data in the current data packet is empty, or the data packet is empty, terminate the process; and execute steps S5-S7;

[0113] After completing step S7, the method further includes step S8:

[0114] S8. Get the current page number, and add a judgment statement through the js script: judge whether the number of current queries is greater than or equal to the total number. If it is greater, no paging extraction is performed. If it is less, continue to loop and execute S42-S8.

[0115] Embodiment 4: This embodiment differs from Embodiment 1, 2 or 3 in that the access data is a heterogeneous data source;

[0116] When data is accessed to the warehouse, it is often synchronized from other heterogeneous data sources to the doris warehouse; these heterogeneous data sources often have large data volumes, and the fields and types are consistent; to meet the full synchronization and incremental synchronization, DataXWeb is used to migrate and synchronize data to the doris warehouse; if the access layer and the DWD layer only perform data migration and synchronization functions, or the standard view table of the DWD layer is changed to a physical table, such as migrating the original data table (in_table) to the standard physical table (in_standard), through DataX Web users can select the data source on the page to create a data synchronization task; support RDBMS, Hive, HBase, ClickHouse, MongoDB, oltp and other data sources, RDBMS data sources can batch create data synchronization tasks, support real-time viewing of data synchronization progress and logs, and provide synchronization termination function;

[0117] Please refer to Figure 2 , determine the data source type of the original data table to be stored, if it is a heterogeneous data source, synchronize the heterogeneous data source through DataXWeb:

[0118] A1. Create data source: create source database and target database;

[0119] A2. Create a task template: Build a task synchronization process, select the target table, and automatically create the table structure of the target table if it does not exist;

[0120] A3. Build a JSON script: Select field mapping, select full or incremental synchronization fields, add internal standard full md5 fields and insertion timestamp fields, generate mapping json format synchronization content, synchronize to DataX to generate a python file, perform scheduled updates through cluster distribution or call DataX once for data synchronization;

[0121] A4. Determine whether the data migration is complete and compare the numbers in the migration database. If they are consistent or the numbers in the new database are greater than or equal to the historical database table (original data table or business table), the migration is successful. Use the standard field table to determine whether the table is an original data table and the standard table of the original data table is a view table. If so, directly delete the original data table data and the corresponding original data table structure; otherwise, do nothing.

[0122] Data flow configuration based on DataXWeb has the following beneficial effects:

[0123] 1. It can monitor the source data to the target data, check the number of successful data, the number of failed data, and log information; 2. It can realize full and incremental synchronization of data, and realize efficient data synchronization to Doris; 3. It can synchronize different heterogeneous data sources on multiple devices at the same time; 4. It can delete data from the original data table associated with the DWD physical layer historical view table, reduce input redundancy, reduce disk space, and provide data robustness; 5. When the target table does not exist, it can automatically create the target table structure based on the field type of the original data table.

[0124] Embodiment 5

[0125] The difference between this embodiment and any one of the embodiments 1 to 4 is that the data is secondary managed before being stored in the database;

[0126] After mapping the standard data in step S6, the following further comprises:

[0127] Determine whether a secondary governance instruction is received, and if not, transfer the standard data flow to the doris operand and store it in the data warehouse;

[0128] If yes, the standard data is input into the default management terminal; and different management operation numbers are obtained in sequence to manage the standard data to obtain management data; after obtaining the management data, the following steps are performed:

[0129] Get the number of data grouping statistics operations;

[0130] The management data are grouped according to the data grouping statistical operation number to form a business wide table;

[0131] The governance operands include data filtering operands, data deduplication operands, data association operands, data format conversion operators, UDF function operands, window operands, business code operands, and sink operands.

[0132] Based on the DWD layer data governance, select the corresponding data layer for secondary data governance to make up for the defects in kettle performance, or some customized requirements need to be written by Flink, specifically:

[0133] B1. Drag and drop the Kafka operator of the source end source on the canvas, configure the data source information of Kafka based on the data source distributed to Kafka through Kettle access, enter the consumption address of Kafka, topic name, and select the displacement of consuming Kafka: consume from the specified position, consume from the starting position, and consume from the latest position; at this time, the data of Kafka can be sent from Kettle or from other ends; this step is mutually exclusive with step B2;

[0134] B2. Select the database operator of the source end by dragging on the canvas, configure the database connection information, database account password, database, table name, etc., and write the query SQL statement; this step is mutually exclusive with step B3;

[0135] B3. Drag and drop on the canvas to select the next step of standard table field mapping. Obtain the table name through the table field in the streaming pipeline data acquisition. If the table name is the original data table name, such as in_table, replace the table name with a standard table, such as in_standard, through standard table field mapping, and replace the corresponding access fields with standard fields. The remaining unmatched fields are retained.

[0136] B4. Drag and drop the next data filter operator node on the canvas. Enter the name of the filter field key and the value corresponding to the filter field key, and select the conditions that are met (greater than, equal to, not equal to, less than, greater than or equal to, less than or equal to, regular, empty, not empty, contain, fuzzy match, start to contain, end). Select the conditions that are met. Multiple AND and OR groups of conditions are supported. Values ​​that meet the regular conditions will be filtered and will not flow to the next node. For example, if the current key is name, and name = Zhang San, then the value that starts to contain name starts to contain "Zhang" to indicate the start of inclusion. For example, if the current key type is length, and length = 10, the full condition is greater than 10, less than 10, etc., which can be set according to specific needs.

[0137] B5. Drag and drop on the canvas to select the next node for data deduplication calculation. Enter the names of one or more filter fields, and set whether to ignore the case of the key field. The values ​​corresponding to the names of the same key field will be filtered and will not flow to the next node.

[0138] B6. Drag and drop on the canvas to select the next data association operator node. Enter a group of key or multiple groups of key field names, enter the key field name combination group corresponding to the input resource library layer corresponding table name binding equality conditions and backfill the values ​​corresponding to the key field names. The data that meets the conditions will be supplemented based on the data values ​​of the resource library layer. The example is as follows:

[0139] Select the Streaming field;

[0140] sexCode=1, sexValue is empty;

[0141] The table of the binding resource layer is t_sex, t_code=1, t_sexValue=male;

[0142] Then directly complete sexValue to male;

[0143] B7. Drag and drop the next data format conversion operator node on the canvas, and enter the corresponding key field name, the corresponding data type to be converted, such as string to Date, and the corresponding format of the converted data, such as YYYY-MM-DD.

[0144] B8. Drag and drop the Java online UDF function operator node on the canvas, input the Java code, compile it at runtime, obtain the compiled bytecode, and then use the custom class loader to define these classes at runtime; realize business conversion of the corresponding fields of the data;

[0145] B9. Drag and drop the window operator node on the canvas, group by selecting the corresponding key, select the window type such as time window or quantity window, enter the corresponding time or quantity to form a collection of data and send it to the downstream;

[0146] B10. Drag and drop the next business code operator node on the canvas, and configure the specified Java package name and the corresponding class name to perform the data cleaning process; for example, the business operator triggers the sending of HTTP to another business system;

[0147] B11. Drag and drop the canvas to select the next step to write the sink operator node. By configuring the connection information of Doris, the standard table is associated based on the table name of the field. If it is a view table, it is directly converted into the original data table and the corresponding fields of the original data table. If it is a physical table, no modification is required. Based on the database query, it is determined whether the record exists. If not, batch combination insertion is performed. If it exists, an update operation is performed;

[0148] B12. Generate an XML flowchart based on the above operator, send it to the corresponding jar package program of Flink, execute the flowchart, and finally perform data management and processing;

[0149] Based on DWS subject layer governance, select the corresponding data layer to perform subject statistics on data and form the corresponding business wide table;

[0150] Specific:

[0151] C1. Select the governance layer through the canvas, select the data source and data connection information of the DWD layer or DWS layer, fill in the standard SQL statement for query, and convert it into the corresponding doris or mongo database through Apache Calcite;

[0152] C2, selectively drag the canvas and execute steps B4-B12;

[0153] C3. Select the data grouping calculator on the canvas, group the key fields by specifying the key fields, and perform Reduce calculations on the fields to form the fields corresponding to the wide table.

[0154] C4. Select the mongo data table in the target sink through the canvas, and configure the incoming DWS layer to update or insert data into the incoming mongo database.

[0155] C5. Generate an XML flow chart based on the above operator and send it to the corresponding jar package program of Flink to generate DWS topic wide table data. The topic wide table data is regularized by MapReduce based on the business data operator to form business topic data.

[0156] The above data governance can achieve the following beneficial effects: 1. Visualize the flink process configuration based on the canvas to grasp and understand the data flow; 2. Implement full-process data cleaning based on a combination of multiple flink operators to streamline data cleaning; 3. Specify that the DWD layer is based on standard tables without having to care about the underlying table names and table fields, so as to achieve universality of the DWD layer, alleviate the inconsistency of field data and data types pushed from different cities, unify through standard tables, and enable business departments to only care about standard tables with less data awareness.

[0157] Embodiment 6

[0158] The difference between this embodiment and any one of the embodiments 1 to 5 is that the stored data is queried;

[0159] The DWD layer business data query rules query standard tables, not limited to single-table or multi-table associations; by parsing a single or multiple standard tables, determine whether the standard table is a view table or a physical table through the table_standard table. If it is a view table, SQL parsing is performed to replace the fields and table name of the table in the query, and replace them with the original data table name and field; thus, not only can the upper-level query be imperceptible, business queries can be performed through standard tables, and query standardization can be achieved (converting the corresponding physical table or logical table through the standard table). The business does not care about the actual table name and table field, reducing the problem of inconsistent fields and table names during data access, and being compatible with the problem of inconsistent fields and types of data sources in different cities.

[0160] Embodiment 7

[0161] A data warehouse data construction and management terminal comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, each step in a data warehouse data construction and management method as described in any one of embodiments one to seven is implemented.

[0162] the term:

[0163] in_table original data table

[0164]

[0165] T1: DWD standard view table

[0166] table_standard Standard view layer table

[0167]

[0168] table_standard_field standard view field table

[0169]

[0170]

[0171] table_standard_relation original data table and standard view table mapping configuration

[0172]

[0173] T2: DWD standard physical table:

[0174] When in_table, in_table2 and in_standard view tables have n:1,

[0175] For example, the information of dishonest persons subject to execution in various cities across the country, including the information of dishonest persons subject to execution on the Chengdu Public Data Open Platform and the information of dishonest persons subject to execution on the Sichuan Provincial Public Data Open Platform, is connected to the DWD physical layer. At this time, n (n=2): 1, because the data structure of dishonest persons subject to execution in Sichuan Province is inconsistent with the data field type and data field of dishonest persons subject to execution in Chengdu, there are n: pairs of DWD layer physical tables; forming n original data tables and 1 DWD physical layer table.

[0176] At this time, the table=in_standard type in table_standard changes the physical table to 2, and directly creates the in_standard table and corresponding fields.

[0177]

[0178]

[0179] a: The newly connected in_table2 table is mapped with the original data table in_table2 in table_standard_relation and directly stored in the standard physical table in_standard.

[0180] b: The historical records of the old in_table will be stored in the standard physical table in_standard in the form of field mapping configuration through DataxWeb, and the data and table structure of the in_table table will be deleted.

[0181] c: The old in_table real-time data will be stored in the standard physical table in_standard, converted to the physical table fields and table names through the custom operator standard table field mapping of kettle, and converted to the physical table fields and table names through the custom operator standard table field mapping of flink for storage.

[0182] table_standard_relation original data table and standard table mapping configuration:

[0183]

[0184] In summary, the present invention provides a visualization of the process specified based on kettle, so that the data flow is clear at a glance, and data can be quickly managed and stored in the warehouse, and the data flow configuration can be dynamically configured. At the same time, access to multiple data protocols can be achieved through kettle without the need for hard-coded access data streams in the form of code; and after obtaining the original data table to be stored in the warehouse, the corresponding standard data is generated from the original data table to be stored in the warehouse and then mapped with the standard field table to obtain a standard table, or the corresponding standard data is directly created based on the original data table to be stored in the warehouse, so that the original data table of the warehouse and the generated standard data do not need to be stored in the database, which reduces data redundancy in the database and realizes rapid data management and storage; at the same time, the field data in the original data table is filtered, deduplicated, format converted, and processed by UDF functions, so that the stored data is highly standardized while reducing the redundancy of the stored data, thereby improving data extraction efficiency and reducing data management costs.

[0185] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A data warehouse data construction and management method, It is characterized in that include: Get the original data table to be stored; Determine whether there is a standard table corresponding to the original data table to be stored in the data warehouse; If it exists, obtaining the field data corresponding to the standard table in the original data table according to the standard table; The field data is filtered, deduplicated, formatted and processed by UDF functions to obtain standard data; Storing the standard data in a data warehouse; If it does not exist, create a standard table corresponding to the original data table to be stored; The standard table includes a view table and a physical table; The step of determining whether there is a standard table corresponding to the original data table to be stored in the data warehouse comprises: Determine the type of the standard table corresponding to the original data table to be stored. If it is a physical table, determine whether there is an input standard corresponding to the original data table to be stored. If so, it means that there is a standard table corresponding to the original data table to be stored. If it is a view table, determine whether the original data table to be stored has a corresponding access configuration. If so, it means that there is a standard table corresponding to the original data packet to be stored; Before storing the standard data in the data warehouse, the following steps are also included: If the type of the standard table corresponding to the standard data is a physical table, mapping the standard data according to the standard table; After mapping the standard data, the method further includes: Determine whether a secondary governance instruction is received, and if not, transfer the standard data to the doris operator and store it in the data warehouse; If yes, the standard data is input into a preset management terminal; and different management operators are sequentially obtained to manage the standard data to obtain management data; The obtaining of the governance data further includes: Get the data group statistics calculator; The management data is grouped according to the data grouping system calculator to form a business wide table.

2. A data warehouse data construction and management method according to claim 1, It is characterized in that If it does not exist, creating a standard table corresponding to the original data table to be stored includes: If the original data table to be stored is a physical table and does not have an input standard, a corresponding standard table is created according to a preset standard field table.

3. The data warehouse data construction and management method according to claim 1, It is characterized in that If it does not exist, creating a standard table corresponding to the original data table to be stored includes: If the original data table to be stored is a view table and there is no corresponding access configuration, the corresponding data input layer and configuration fields are set according to the original data table to be stored, and the corresponding configuration is generated.

4. The data warehouse data construction and management method according to claim 1, It is characterized in that The governance operators include data filtering operators, data deduplication operators, data association operators, data format conversion operators, UDF function operators, window operators, business code operators and sink operators.

5. The data warehouse data construction and management method according to claim 1, It is characterized in that The step of obtaining the original data table to be stored includes: The data source type of the original data table to be stored is determined. If it is a heterogeneous data source, the heterogeneous data source is synchronized through dataXWeb.

6. A data warehouse data construction and management terminal, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the computer program, the processor implements each step of the data construction and management method of a data warehouse as described in any one of claims 1 to 5.

Citation Information

Patent Citations

  • Method and system for constructing data warehouse with dependency between data streams

    CN111767327A