Metadata-driven integrated data warehouse and governance platform construction method and system

Abstract

The invention provides a metadata-driven integrated data warehouse and governance platform construction method and system, and the method comprises the steps: collecting full-quantity metadata in a data source through a metadata automatic collection adapter, extracting a full-link blood relationship map and an enterprise data map, and constructing a target data set; executing a data table one-key synchronization operation by adopting a control table and parameterized pipeline mode, and generating an ODS target table and an ETL synchronization task; constructing a DDL table establishment statement according to the ODS target table and the ETL synchronization task; on the basis of DDL table establishment statements, dividing models according to business subject domains, and combining data magnitude prediction to automatically recommend a bucket dividing strategy to generate a data warehouse model; a table structure of a data warehouse model, a full-link blood relationship graph and a partitioning strategy are adopted to construct a one-stop data governance platform. A data warehouse table model can be quickly constructed, data can be automatically synchronized, and full-link data development, treatment and operation capabilities are realized.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of big data processing, in particular to a metadata-driven integrated data warehouse and governance platform construction method and system. BACKGROUND

[0002] The construction of a data warehouse and a data governance platform is a core link for an enterprise to realize data asset integration and value mining. Based on metadata management, automatic collection and value discovery of metadata become a key direction for constructing a high-performance data processing system. Metadata can connect source data (such as a business system), a data warehouse, and data applications, and completely record the whole life cycle process of data from generation to consumption, thereby providing reliable tool support for an enterprise to establish a metadata management system. In the prior art, graphically displaying metadata dependency, analyzing data lineage, and managing metadata changes and versions have become important means of metadata management. Based on metadata, data tables can be collected by one key, automatically synchronized to a data warehouse, and a high-performance, standardized data warehouse can be constructed, and ETL (Extract-Transform-Load) synchronization tasks can be automatically created, which can effectively improve the development efficiency of the data warehouse and provide support for data exploration and report system construction.

[0003] The prior art has realized synchronization of business table data to a data warehouse such as Hive (a Hadoop distributed data warehouse tool) through a one-key synchronization function, so as to quickly query table data. For the subject catalog and table metadata of metadata, the existing scheme connects the metadata system and the data development scheduling system to realize rapid construction of a data warehouse table model and automatic synchronization of data. For example, a T+1 full-amount synchronization one-key warehouse construction technology configures a data source connection and a target data warehouse, the system can automatically synchronize the data source data to the target warehouse at a set time, and create a table model similar to the source table structure, complete data analysis, data warehouse model construction, and ETL task development based on the target warehouse, and initially realize the automation of the data processing flow.

[0004] However, with the continuous growth of business data, the limitations of the prior art are gradually highlighted: first, the construction of a data warehouse highly depends on big data engineers to manually construct ETL tasks, JOB task configuration is complicated, and the operation process is complex, resulting in high labor cost and low efficiency, and a single manual task configuration takes several hours to several days, and the quality is difficult to guarantee, affecting the system stability; second, the creation speed of a data warehouse table model is slow, and it is difficult to ensure the standardization and consistency of the table model through manual operation, resulting in difficulties in standardization landing; third, the degree of automation of manual operation is insufficient when facing massive data, which cannot meet the real-time demand of data processing, and seriously restricts the efficiency and quality of data application. SUMMARY

[0005] In view of this, the present application provides a metadata-driven integrated data warehouse and governance platform construction method and system, which solves the technical problems of low automation, high development cost and difficulty in standardization landing of the platform constructed due to the dependence on manual operation to construct data processing processes in the prior art.

[0006] The metadata-driven integrated data warehouse and governance platform construction method provided in the first aspect of the present application comprises the following steps:

[0007] Step S1, collecting full metadata in a data source through a metadata automatic collection adapter, extracting a full-link blood relationship graph and an enterprise data map, and constructing a target data set;

[0008] Step S2, performing a data table one-key synchronization operation based on the target data set in a control table and parameterized pipeline manner, generating an ODS target table and an ETL synchronization task;

[0009] Step S3, constructing a DDL table creation statement according to the ODS target table and the ETL synchronization task;

[0010] Step S4, dividing a model according to a business theme domain based on the DDL table creation statement, and combining a data volume level prediction to automatically recommend a bucketing strategy, and generating a data warehouse model;

[0011] Step S5, constructing a one-stop data governance platform using a table structure, a full-link blood relationship graph and a partitioning strategy of the data warehouse model.

[0012] Optionally, the specific steps of step S1 are as follows:

[0013] Step S11, collecting full metadata of a data source through a metadata automatic collection adapter;

[0014] Step S12, extracting data entity dependency relationships and conversion rules from the full metadata to generate a full-link blood relationship graph;

[0015] Step S13, visualizing data entities, flow paths and processing relationships in the full metadata to generate an enterprise data map;

[0016] Step S14, constructing a target data set using the full metadata, the full-link blood relationship graph and the enterprise data map.

[0017] Optionally, the specific steps of step S2 are as follows:

[0018] S21, select the source table basic parameters corresponding to the target data set according to the table object name input by the user, and store the table object name in the control table;

[0019] S22, read the table object data in the control table, and configure a synchronization application using the source table basic parameters to generate synchronization application data;

[0020] S23, copy the ODS layer table structure and inject the data warehouse standard field to generate the ODS target table;

[0021] S24, using the full-link blood relationship graph in the target data set, analyze the field mapping relationship between the ODS target table and the business system table in the data source, and generate a correlation relationship mapping table;

[0022] S25, identify the foreign key field in the ODS target table, and match the corresponding associated table metadata in the target data set to generate a foreign key field list;

[0023] S26, according to the correlation relationship mapping table and the foreign key field list, perform dimension modeling optimization to generate a data warehouse hierarchical table structure and a table interrelation list;

[0024] S27, based on the data warehouse hierarchical table structure and the table interrelation list, integrate data conversion rules to generate an ETL synchronization task.

[0025] Optionally, the specific steps of step S26 are:

[0026] Step S261, according to the correlation relationship mapping table, the ODS target table is divided into a DWD table and a DWS table;

[0027] Step S262, add the foreign key field of the foreign key field list, the business tag field and the data warehouse standard field to the DWD table, and clean the business field in the table to generate a DWD table structure;

[0028] Step S263, according to the associated table dimension, the foreign key field list and the business cycle, set the partitioning strategy;

[0029] Step S264, using the DWD table, the DWS table, the DWD table structure and the partitioning strategy, construct a data warehouse hierarchical table structure and a table interrelation list.

[0030] Optionally, the specific steps of step S27 are:

[0031] Step S271, extract the business demand parameters from the synchronization application data, and parse the ODS layer table business domain label to generate a task configuration file;

[0032] Step S272: Using the data warehouse hierarchical table structure and the inter-table relationship list, construct a task data flow path;

[0033] Step S273: extracting field conversion logic from the conversion rule library in the target data set to generate a task script fragment;

[0034] Step S274: Construct an ETL synchronization task script using synchronization strategy parameters, the task configuration file, the task data flow path, and the task script fragment;

[0035] Step S275: Associating the ETL synchronization task script with the data warehouse table structure and performing quality verification to generate an ETL synchronization task.

[0036] Optionally, the specific steps of step S3 are:

[0037] S31, extracting table structure data from the ODS target table and generating an ODS layer DDL framework;

[0038] S32. Analyze the field conversion rules in the ETL synchronization task and convert the field conversion rules into field constraints;

[0039] S33: Constructing the initial DDL statements for the DWD layer and the initial DDL statements for the DWS layer using the foreign key fields, cleaning rules, and preset partitioning strategies in the data warehouse hierarchical table structure;

[0040] S34, extracting foreign key association data from the inter-table association relationship list;

[0041] S35: Add the constraint definition corresponding to the foreign key associated data to the DWD layer initial DDL statement and the DWS layer initial DDL statement to generate a DWD layer target DDL statement and a DWS layer target DDL statement;

[0042] S36 , associating the ODS layer DDL framework, the DWD layer target DDL statement, the DWS layer target DDL statement with the scheduling parameters of the ETL synchronization task to generate a DDL table creation statement.

[0043] Optionally, the specific steps of step S4 are:

[0044] S41. Divide the subject domains according to the preset business subject domains and generate a classification list;

[0045] S42, mapping the table structure corresponding to each layer of DDL statements in the DDL table creation statement to the corresponding level to build a model framework;

[0046] S43, extracting the partitioning strategy and storage format from the DDL table creation statement to generate a physical storage configuration solution;

[0047] S44, a bucketing strategy is selected according to the data magnitude in the DDL table creation statement, and a physical design optimization scheme is generated;

[0048] S45, a hotspot is identified and managed based on the inter-table association relationship in the DDL table creation statement, and a model optimization suggestion list is generated;

[0049] S46, the model is constructed by using the classification list, the model framework, the physical storage configuration scheme, the physical design optimization scheme and the model optimization suggestion list, and a data warehouse model is generated.

[0050] Optionally, the specific steps of step S5 are:

[0051] S51, a preset number of quality rule templates are bound based on the table structure of the data warehouse model, and a data quality detection task is generated;

[0052] S52, the data quality detection task and the full-link blood relationship graph corresponding to the data warehouse model are used to calculate the health score of each partition table, and a management list is generated;

[0053] S53, the detection abnormal data corresponding to the data quality detection task, the partition positioning information of the data warehouse model, the responsibility person information, the quality score state and the rectification requirement of the management list are constructed to build a data quality problem work order;

[0054] S54, the partition and hierarchical alarm push and rectification tracking are performed according to the data quality problem work order, and a rectification record is generated;

[0055] S55, the rectification record, the quality rule template and the management list are used to build a module, and a full-process data quality monitoring module is generated;

[0056] S56, based on the DDL table creation statement, the business theme domain, the table structure of the data warehouse model, the full-link blood relationship graph and the partition strategy, a data governance system is constructed;

[0057] S57, the data quality problem work order, the rectification record, the full-process data quality monitoring module and the data governance system are used to build a one-stop data governance platform.

[0058] Optionally, the specific steps of step S56 are:

[0059] S561, based on the table structure, the full-link blood relationship graph and the partition strategy, the data physical storage location and the partition flow path are sorted out, and a CRUD matrix and a visual flow graph are generated;

[0060] S562, audit the partition strategy compliance in the DDL table creation statement by adopting the partition strategy in the DDL table creation statement and the model framework corresponding to the data warehouse model, and generate a partition compliance report;

[0061] S563, integrate the CRUD matrix, the visual flow graph and the partition compliance report into the scheduling system of the ETL synchronization task, and generate a target scheduling system;

[0062] S564, set task parallelism and abnormal retry logic in the target scheduling system according to the partition strategy corresponding to the partition compliance report, and generate a scheduling process with partition optimization;

[0063] S565, sample and verify the key business theme domain of the data warehouse model, and generate a quality verification report containing partition dimensions in combination with built-in dimensions;

[0064] S566, adopt the CRUD matrix, the visual flow graph, the partition compliance report, the target scheduling system, the scheduling process and the quality verification report to build a data governance system.

[0065] The second aspect of the present application provides a metadata-driven integrated data warehouse and governance platform construction system, comprising:

[0066] A target data set construction module is configured to collect full metadata in a data source, extract a full-link blood relationship graph and an enterprise data map, and construct a target data set through a metadata automatic collection adapter.

[0067] A target table and synchronization task generation module is configured to perform a data table one-key synchronization operation by adopting a control table and a parameterized pipeline based on the target data set, and generate an ODS target table and an ETL synchronization task.

[0068] A DDL table creation statement construction module is configured to construct a DDL table creation statement according to the ODS target table and the ETL synchronization task.

[0069] A data warehouse model generation module is configured to divide a model according to a business theme domain based on the DDL table creation statement, and automatically recommend a bucketing strategy in combination with data volume level prediction, and generate a data warehouse model.

[0070] A data governance platform construction module is configured to adopt a table structure, a full-link blood relationship graph and a partition strategy of the data warehouse model to construct a one-stop data governance platform.

[0071] Compared with the prior art, the present application has the following advantages:

[0072] 1. Cost reduction: drive ETL development, guide ETL work through metadata, improve ETL development efficiency, and reduce labor cost. One-key, efficient data synchronization is achieved through metadata driving. The operation steps of data synchronization are simplified, and the table building, task configuration, and release operation related to data synchronization are realized one-key processing, so that the data demand side can more conveniently obtain and use data.

[0073] 2. Quality of JOB task is guaranteed: one-key data synchronization realizes automatic creation of JOB task, reduces the skill threshold of data synchronization, guarantees the quality of JOB task, and improves the stability of the system.

[0074] 3. Quality of data warehouse is improved: based on the DDL table building statement, the model is divided according to the business theme domain, and the automatic recommendation of the bucket strategy is combined with the data level prediction to generate the data warehouse model. The one-key synchronization driven by metadata realizes the standardization and rapid creation of the table model, and improves the quality of the data warehouse.

[0075] 4. Drive business growth: using the table structure, full-link blood relationship graph, and partition strategy of the data warehouse model, a one-stop data governance platform is constructed to realize full-link data development, governance and operation ability, and help enterprises to drive business growth with data. BRIEF DESCRIPTION OF DRAWINGS

[0076] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced. Obviously, the drawings in the following description are only preferred embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0077] Figure 1 The flowchart of the metadata-driven integrated data warehouse and governance platform construction method of the present application;

[0078] Figure 2 The metadata-driven data development and scheduling system architecture diagram of the present application;

[0079] Figure 3 The synchronization table application and management interface diagram of the present application;

[0080] Figure 4 The synchronization table parameter configuration detail interface diagram of the present application;

[0081] Figure 5 The architecture diagram of the one-stop data governance platform of the present application;

[0082] Figure 6 The data governance process architecture diagram of the one-stop data governance platform of the present application;

[0083] Figure 7 The structural diagram of the metadata-driven integrated data warehouse and governance platform construction system of the present application. DETAILED DESCRIPTION

[0084] The embodiment of the present application provides a metadata-driven integrated data warehouse and governance platform construction method and system, which is used for connecting metadata management systems and data development scheduling systems, realizing rapid construction of data warehouse table models and automatic synchronization of data, realizing one-key collection of data tables based on metadata, automatic synchronization of tables to data warehouses, and automatic creation of ETL synchronization tasks, and improving the development efficiency of data warehouses.

[0085] In order to make the application purpose, features and advantages of the present application more obvious and easy to understand, the technical solutions in the embodiments of the present application will be described clearly and completely in combination with the drawings in the embodiments of the present application. Obviously, the following described embodiments are only part of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the protection scope of the present application.

[0086] Please refer to Figures 1 to 6 , Figure 1 The metadata-driven integrated data warehouse and governance platform construction method provided by the present application.

[0087] In the embodiment of the present application, as shown in Figure 2 and Figure 5 , the data source includes low-altitude home, flight control platform and other systems. The source data includes image data, environmental detection data, video monitoring data, flight trajectory data, buried point data and reporting data. The collection mode of the metadata automatic collection adapter includes offline data collection and real-time data collection. Specifically, the metadata automatic collection adapter automatically completes the collection of metadata, creates a data connection, and after a new data source is created in the workspace, the platform can provide metadata collection capability for the data source. After starting metadata collection in the metadata system, the system will collect the inventory metadata in one time, and collect the incremental metadata every day, and then collect them to the metadata system to obtain the full amount of metadata. The full amount of metadata includes data entity description, processing logic and blood relationship. The data entity description includes the description of system, library, table and field. The processing logic includes the inter-table foreign key relationship and the JOIN operation in the ETL task. The blood relationship includes the field mapping path from the source table to the ODS table (Operational Data Store, operational data storage table) (such as the link in Figure 2 from the source table to the metadata system to the data warehouse).

[0088] Further, the metadata automatic collection adapter is built-in with Oracle (Oracle database), MySQL, Hive, and other 10+ collection adapters (equivalent to Figure 2 The "metadata collection" module in the middle automatically identifies the data source type and loads the corresponding driver through the connection pool and protocol parsing engine. End-to-end automatic collection realizes centralized management of metadata, full-link blood relationship extraction, quick clarification of enterprise data resources, understanding of data sources and destinations, construction of enterprise data map, visual display of metadata information, and provides basic support for data standard planning and data model construction. Specifically, during the first full collection, the table structure, field definition, view relationship, and other full metadata of the source database are scanned, and the changed data is updated through incremental collection. After collection, the full-link blood relationship graph is generated by parsing the inter-table foreign key relationship and the JOIN operation in the ETL task. The data entities, flow paths, and processing relationships in the full metadata are visualized to generate an enterprise data map, as shown in Figure 2 The data flow path from the source table (such as the low-altitude home business system table) to the metadata system and then to the data warehouse is clearly displayed, and the graphical display of metadata dependency relationships (implemented by the "metadata search / analysis" module on the right) is supported. Figure 2 Finally, the target data set is constructed using full metadata, full-link blood relationship graph, and enterprise data map.

[0089] After completing the metadata collection, one-key synchronization operation can be performed based on the control table (storing the list of source table names) and the parameterized pipeline (reading the control table to generate tasks) to synchronize the data into the data warehouse. The metadata-driven data synchronization method mainly relies on the use of control tables and parameterized pipelines. This method inputs the list of object names containing the required replication behavior into a control table, then uses the parameterized pipeline to read these objects from the control table, and applies them accordingly to the job. In this way, the list of objects to be replicated can be easily maintained (e.g., added / deleted) by updating the object names in the control table without the need to redeploy the pipeline. This method simplifies the generation process of large-scale data replication pipelines, making data synchronization more efficient and convenient.

[0090] One-key synchronization has the ability to automatically create tables and automatically create ETL tasks to quickly build target data tables in synchronization, thereby improving the efficiency of data link usage. According to the target data set, the one-key synchronization operation of the data table is performed in a controlled table and parameterized pipeline manner to obtain the ODS target table and the ETL synchronization task. (3) Select the connection of the source database and the table name of the source table, and then input the table name of the target table. According to the needs, full loading or incremental loading mode can be selected, in this way, the data table is quickly created, thereby realizing one-key data synchronization operation, making the table data synchronization more simple and reliable. One-key synchronization provides one-key table creation, batch table creation and other automatic table creation modes, and this function is suitable for the stock of the source end, and quickly builds the target table similar to the structure of the source table.

[0091] Intelligent DDL, that is, DDL table creation statement generation, strictly follows the data warehouse hierarchical specification (ODS→DWD (Data Warehouse Detail, detail data layer)→DWS (Data Warehouse Summary, summary data layer)→ADS (Application Data Store, application data layer)), and automatically adds partition strategy (according to time / business key), storage format (Parquet / ORC) according to the ODS target table and the ETL synchronization task.

[0092] Based on the DDL table creation statement, the preset business theme domain is divided (the preset business theme domain includes commodity analysis, task analysis, airport analysis, pilot analysis, user analysis, airspace analysis, job analysis, video analysis, market analysis, and equipment analysis, such as Figure 5 as shown in the data warehouse part), and the automatic recommendation of the bucketing strategy and the index type is performed according to the data magnitude prediction (such as daily incremental 1 TB). The high-frequency access table, the redundant table, and the table without a primary key are automatically identified to manage hotspots, and the basis for model optimization is provided, and a data warehouse model with better, more robust, and more standard performance is generated.

[0093] Based on the table structure of the data warehouse model, the full-link blood relationship graph, and the partition strategy, a one-stop data management platform is constructed, and the automatic mechanism also guarantees the data quality and the ETL task quality. The automatically generated DDL conforms to strict naming specifications, constraint definitions, and partition strategies. The automatically generated ETL code has built-in error handling, log recording, and retry mechanisms. The management platform has 20+ kinds of quality rule templates (such as uniqueness verification, value range checking, and timeliness monitoring) that can be used out of the box, supports custom SQL rules, and also automatically generates data quality detection tasks and basic data quality verification steps (such as record number comparison and key field non-empty checking). The audit process locates the person responsible for the quality problem (such as Figure 6The governance platform also has a data quality scoring system built in, which automatically calculates table health scores based on rule violation rates, data freshness, blood relationship integrity, etc., to drive governance priorities. The data quality scoring audit mechanism automatically monitors data quality, thereby achieving pre-event, in-event and post-event monitoring of data. Problem closed-loop management automatically generates data quality problem work orders, associates blood link to locate responsible persons, tracks rectification status, and pushes real-time alerts to Dingding. The present application improves the efficiency of ETL development and reduces labor costs by using metadata to guide ETL work. Through metadata-driven, one-key, efficient data synchronization is achieved. The operation steps of data synchronization are simplified, and one-key processing of table building, task configuration and release operation related to data synchronization is realized, making it more convenient for data demanders to obtain and use data. A one-stop data governance platform (data integration, data development, metadata management) is built to realize full-link data development, governance and operation capabilities, and help enterprises drive business growth with data. The technical problems of low automation and high development cost caused by relying on manual operation to build data processing flow in the prior art are solved, and the platform built has the problem of difficulty in standardization landing.

[0094] Preferably, the specific steps of step S1 are:

[0095] Step S11, automatically collecting adapters through metadata to collect full metadata of data sources;

[0096] Step S12, extracting data entity dependency relationships and conversion rules from the full metadata to generate a full-link blood relationship graph;

[0097] Step S13, visualizing data entities, flow paths and processing relationships in the full metadata to generate an enterprise data map;

[0098] Step S14, using full metadata, full-link blood relationship graph and enterprise data map to build a target data set.

[0099] In the embodiment of the present application, the metadata automatic collection adapter can be used for heterogeneous environment data collection, supporting the collection of full metadata from data generation systems to data processing systems to data application report systems in traditional relational databases and big data platforms. It includes data entities (descriptions of systems, databases, tables and fields) and logic in the data entity processing process. After the user maintains the data source connection information, the metadata synchronization period can be set according to the update frequency of the data source, and the metadata information can be automatically parsed, obtained and updated regularly to ensure that the platform metadata information is timely and effective. The metadata automatic collection adapter supports ordinary tables (single tables) and split database and split tables.

[0100] The processing logic in the full metadata is analyzed, and the dependency relationship and conversion rule between data entities are extracted. The full-link blood relationship graph is constructed by combining the update frequency of the data source and the blood link. The full-link blood relationship graph displays the dependency relationship and conversion rule between data entities in the form of a graph (such as A table→B table→C table), clearly showing the data source and processing logic, and solving the “data black box” problem in traditional data governance. Based on the data entities, flow paths, and processing relationships in the full metadata, the data entities, flow paths, and processing relationships are displayed and visualized through a graphical tool, and an enterprise data map is generated. The target data set is constructed by using the full metadata, the full-link blood relationship graph, and the enterprise data map.

[0101] Preferably, the specific steps of step S2 are as follows:

[0102] S21, according to the table object name input by the user, selecting the corresponding source table basic parameters in the target data set, and storing the table object name in the control table;

[0103] S22, reading the table object data in the control table, and using the source table basic parameter configuration synchronization application to generate synchronization application data;

[0104] S23, copying the ODS layer table structure and injecting the data warehouse standard field to generate the ODS target table;

[0105] S24, using the full-link blood relationship graph in the target data set to analyze the field mapping relationship between the ODS target table and the business system table in the data source, and generating a correlation relationship mapping table;

[0106] S25, identifying the foreign key field in the ODS target table, and matching the corresponding correlation table metadata in the target data set to generate a foreign key field list;

[0107] S26, performing dimension modeling optimization according to the correlation relationship mapping table and the foreign key field list to generate a data warehouse hierarchical table structure and a table interrelation relationship list;

[0108] S27, based on the data warehouse hierarchical table structure and the table interrelation relationship list, integrating data conversion rules to generate an ETL synchronization task.

[0109] Further, the specific steps of step S26 are as follows:

[0110] Step S261, according to the correlation relationship mapping table, the ODS target table is split into a DWD table and a DWS table;

[0111] Step S262, adding the foreign key field, business tag field, and data warehouse standard field of the foreign key field list to the DWD table, and cleaning the business fields in the table to generate a DWD table structure;

[0112] Step S263, setting a partition strategy according to the association table dimension, the foreign key field list and the business cycle;

[0113] Step S264, constructing a data warehouse hierarchical table structure and a table interrelation list by using the DWD table, the DWS table, the DWD table structure and the partition strategy.

[0114] Further, the specific steps of step S27 are as follows:

[0115] Step S271, extracting a business demand parameter from the synchronization application data, and parsing an ODS layer table business domain label to generate a task configuration file;

[0116] Step S272, constructing a task data flow path by using the data warehouse hierarchical table structure and the table interrelation list;

[0117] Step S273, extracting a field conversion logic from a conversion rule library in the target data set to generate a task script segment;

[0118] Step S274, constructing an ETL synchronization task script by using the synchronization strategy parameter, the task configuration file, the task data flow path and the task script segment;

[0119] Step S275, associating the ETL synchronization task script with the data warehouse table structure and performing quality verification to generate an ETL synchronization task.

[0120] In the embodiment of the application, for the case that a table model is complex and needs business customization, a user can add a table field according to business characteristics to improve the development efficiency of a synchronization task. This function can automatically complete the construction of a source-to-target table according to a plurality of table objects specified by a source end, and complete the creation of an ODS table and a DWD / DWS table model at one time. The ODS table creation strategy is to automatically copy a source table structure and inject a data warehouse standard field (such as createtime and datasource). The DWD / DWS table creation strategy is to automatically identify an associated table based on a blood relationship, perform dimension modeling optimization, and the user can dynamically add a business label field (such as is_svip) on the automatically generated table structure.

[0121] Enter a metadata query list page (as shown in Figure 3 ), select a table to be synchronized, and execute the operation "Apply synchronization table to HIVE". In an editing page (as shown in Figure 4 ), select a table to be synchronized, and execute the operation "Apply synchronization table to HIVE".As shown in Table 1, the default configuration data source, synchronization table name and table annotation, and the configuration parameter "synchronization mode" are incremental synchronization / full synchronization, and the default is incremental synchronization. Specifically, the synchronization operation is performed, and "Synchronize" is clicked. In the first step, the source table information is configured by default, and the source table and source library information are brought in by default, as shown in Table 1. The data dictionary name of the source table and the business library name of the source library are automatically configured. In the second step, the target information is set, and the target information is configured, including the target library, target table name and table description, as shown in Table 2. Finally, the synchronization strategy is configured, and the configuration data of the synchronization strategy includes the synchronization field and the partition configuration, as shown in Table 3. The synchronization field supports configuration of imported partial or all fields, and the default is all fields. The partition field of the partition configuration is PT partition by default, and the configuration parameter is supported.

[0122] Table 1: Configure source table information

[0123] Serial number Parameter Description 1 Source library Default business library name 2 Source table Default data dictionary name

[0124] Table 2: Configure target table information

[0125] Serial number Parameter Description 1 Target library Database name of HIVE 2 Target table name Format: ODS + source table name \ DWD + source table name 3 Table description Description information of the table

[0126] Table 3: Configure synchronization strategy

[0127] Serial number Parameter Description 1 Synchronization field Configure import part or all fields, default all fields 2 Partition configuration Partition field defaults to PT partition, supports configuration parameters

[0128] Specifically, the synchronization operation is performed, and "Synchronize" is clicked. In the first step, the source table information is brought in by default, and the source table basic information (table English name, Chinese name, business domain, file storage type) is automatically configured. In the second step, the target information is set, and the target library and target table parameters are configured. Finally, the synchronization strategy is configured. The synchronization field supports configuration of all fields or partial fields, and the default is all fields. The configuration data table column name, field type and field annotation are supported. When the synchronization mode is "incremental synchronization", at least one partition field is configured, the PT partition is default, and the update configuration parameter is supported. The preview synchronization result table task SQL script information is supported. According to the synchronization configuration, the SQL statement of the current synchronization task is automatically parsed (the generated SQL script cannot be edited, and is limited to understanding the logic of the synchronization task). The ETL synchronization task is automatically generated in the background, and the creation of the data warehouse table structure and the one-key synchronization of the table data are automatically completed (as shown in Table 4). Figure 4

[0129] By selecting the corresponding source table basic parameters in the target data set according to the table object name input by the user, and storing the table object name in the control table, that is, after the user selects the source table in the metadata query list page, inputs the source table and target table information, and the system stores the table object name in the control table. The table object in the control table is read by the parameterized pipeline, and the source table basic parameters are used to execute the synchronization application configuration operation to generate the synchronization application data. As shown in Table 4. Figure 4 ​As shown, the parameterized pipeline reads the control table data through a Python script to dynamically generate synchronization tasks. The pipeline uses a template engine to generate configuration scripts based on the source table base parameters, including data source connection information, target table parameters, synchronization strategy, etc. Synchronization application data includes source table information, target table configuration, synchronization mode (full / incremental), etc., which is used to drive subsequent table building and ETL task generation.

[0130] Based on the source table base parameters (such as field name, type) in the synchronization application data, the source table structure is automatically copied and the standard fields of the data warehouse are injected to generate the ODS target table, ensuring the standardization of the table structure. Based on the full-link blood relationship graph in the target dataset, the field mapping relationship between the ODS target table and the business system table is analyzed to generate a correlation relationship mapping table, recording the correspondence between the source table fields and the target table fields. Through the blood relationship graph, the foreign key fields in the ODS target table are identified, and the metadata of the associated tables in the target dataset is matched to generate a foreign key field list, recording the relationship between the foreign key fields and the associated tables.

[0131] The dimension modeling optimization process splits the ODS target table into DWD tables and DWS tables based on the correlation relationship mapping table (field mapping relationship) and the foreign key field list (foreign key associated table). Then add the data warehouse standard fields (such as is_svip) and clean the business fields to generate the DWD table structure. According to the associated table dimensions, foreign key field list, and business cycle, set the partitioning strategy. By combining the partitioning strategy (such as time partitioning) to define the associated fields of the aggregation table, the data warehouse hierarchical table structure is generated. And record the associated fields between tables to generate the inter-table association relationship list.

[0132] Extract business requirement parameters (such as synchronization mode, partition field) from synchronization application data, parse ODS layer table business domain tags, and generate task configuration files. According to the data warehouse hierarchical table structure and the inter-table association relationship list, construct the task data flow path. Extract field conversion logic from the conversion rule library (such as null value processing, format conversion) in the target dataset to generate task script fragments. Use synchronization strategy parameters, task configuration files, task data flow paths, and task script fragments to construct ETL synchronization task scripts. And associate the ETL synchronization task scripts with the data warehouse table structure and perform quality verification to generate ETL synchronization tasks. That is, after submitting the synchronization application, the task recipient is automatically notified in WeChat or WeChat, reminding the task recipient to handle it. You can view and manage the pending synchronization tasks in "My Workbench" (such as Figure 3The target table and synchronization task creation are completed, data in the business library (from the library) table is read and loaded into the EDW library table, and after detecting changes in the data volume of the data warehouse table, the data quality score is automatically started. Through the built-in quality inspection rules, through the data consistency, validity and integrity check, all the detection passes, and the task receiver confirms again, evaluates the data quality monitoring, and the quality score is "good" and is recommended to pass the audit directly, the quality score is "qualified", and the monitoring process needs to be evaluated. After confirmation, it can pass the audit, and the data quality evaluation exists alarm, and the alarm problem needs to be handled, and the data is synchronized again until the quality score is qualified and can pass the audit, and after the "audit" operation is completed, the data is formally put online in the production system, and the WeChat or Feishu notifies the application user table that the synchronization is completed. Through the automatic generation of data quality verification tasks, data quality verification rules and data verification steps, the technical problems of low automation, high development cost and difficulty in standardization landing of the platform constructed by relying on manual operation to build data processing processes in the prior art are solved.

[0133] Without coding, fast data table model and ETL synchronization task development, the development cycle is shortened, the development efficiency is improved, and the development process is optimized, the table structure and table data are synchronized in one key, and the data application and data analysis of the business side are quickly responded. The data table model is realized by the model designer and the ETL task automatic generation capability, the ETL work between the traditional database and the HIVE data warehouse is completed, and the development cycle is greatly saved and the development difficulty is reduced.

[0134] Through the "one-key automation" intelligentization, the user only needs to minimize the input (for example: source table information, target table, loading mode), and the system can complete all the tedious, error-prone and professional knowledge required background work, so as to realize the maximum automation construction, for example: DDL generation, complex SQL / code generation, scheduling configuration, dependency management, release deployment, which all need professional knowledge decision (DDL design, ETL logic generation, task optimization), and the system improves the intelligentization. The JOB configuration error rate is greatly reduced, and the table model standardization compliance rate is also improved. And the conversion rule library in the metadata is used, and necessary conversions (such as null value processing, format conversion) are automatically added in the ETL logic generation.

[0135] The complexity of "one-key automation" and the complex technical processes hidden behind "one-key" (such as the above-mentioned DDL generation, code generation, configuration generation, and intelligent inference). If these processes are completed manually, they need to be completed by a senior big data developer, which requires a lot of experience and professional technical knowledge. Through the automatic encapsulation of the present application, new employees can independently complete the data synchronization task after a few hours of training, and the original manual configuration process of several hours / day can be shortened to 30 minutes. Through the highly automated one-key operation of the present application, one-key and efficient data synchronization is realized, the operation steps of data synchronization are simplified, and one-key processing of table building, ETL task configuration, and release operation related to data synchronization is realized, thereby reducing the skill threshold of data synchronization.

[0136] Preferably, the specific steps of step S3 are as follows:

[0137] S31, extracting table structure data from the ODS target table to generate an ODS layer DDL framework;

[0138] S32, parsing the field conversion rules in the ETL synchronization task and converting the field conversion rules into field constraint conditions;

[0139] S33, using the foreign key fields, cleaning rules, and preset partitioning strategies in the data warehouse hierarchical table structure to construct initial DDL statements for the DWD layer and the DWS layer;

[0140] S34, extracting foreign key association data from the inter-table association relationship list;

[0141] S35, adding constraint definitions corresponding to the foreign key association data to the initial DDL statements for the DWD layer and the DWS layer to generate target DDL statements for the DWD layer and the DWS layer;

[0142] S36, associating the target DDL statements for the DWD layer and the DWS layer with the scheduling parameters of the ETL synchronization task to generate DDL table building statements.

[0143] In the embodiment of the present application, table structure data (such as field name, type, and annotation) is extracted from the ODS target table, and an ODS layer DDL framework is generated in strict accordance with the data warehouse hierarchical specification (ODS→DWD→DWS→ADS). Figure 6 The verification process of the implicit field constraint in the quality inspection process is ensured to be consistent with the DDL. The ETL synchronization task contains field conversion rules (such as null value processing and format conversion), and step S32 converts these rules into field constraint conditions in the DDL to ensure the standardization of data entry. Figure 2The inter-table association module displays the foreign key association relationship with arrows, consistent with the association data structure. The inter-table association relationship list records the foreign key association conditions of each layer table, and step S34 extracts these data therefrom to generate foreign key association data for adding foreign key constraints. The DDL statement needs to include the foreign key constraint definition to ensure data consistency. Step S35 adds the foreign key association data of step S34 to the initial DDL to generate complete target detail layer and summary layer DDL statements.

[0144] Figure 2 The data development scheduling system module displays the association process of the DDL and the scheduling parameters to ensure consistency between table generation and task execution. The constraint definition corresponding to the foreign key association data is added to the initial DDL statement in the DWD layer and the initial DDL statement in the DWS layer to generate the target DDL statement in the DWD layer and the target DDL statement in the DWS layer. The target DDL statement in the DWD layer, the target DDL statement in the DWS layer, and the scheduling parameters of the ETL synchronization task are associated to generate the DDL table creation statement. The automatically generated DDL table creation statement will strictly comply with the table model standardization specification, and the DDL will comply with strict table naming specifications and field naming specifications. By using table names, field names, types, data provenance relationships, automatically generated DDL table creation statements that meet the standards, automatically generated ETL logic, automatically configured task scheduling dependency relationships, and more importantly, through metadata application, more optimal (better performance, more robust, more standard) configurations and codes are generated, which cannot be achieved by simply concatenating steps.

[0145] Preferably, the specific steps of step S4 are as follows:

[0146] S41, dividing the subject domain according to the preset business subject domain to generate a classification list;

[0147] S42, mapping the table structure corresponding to each layer DDL statement in the DDL table creation statement to the corresponding level to construct a model framework;

[0148] S43, extracting the partitioning strategy and storage format from the DDL table creation statement to generate a physical storage configuration scheme;

[0149] S44, selecting a bucketing strategy according to the data magnitude prediction in the DDL table creation statement to generate a physical design optimization scheme;

[0150] S45, identifying and managing hotspots based on the inter-table association relationship in the DDL table creation statement to generate a model optimization suggestion list;

[0151] S46, using the classification list, the model framework, the physical storage configuration scheme, the physical design optimization scheme, and the model optimization suggestion list to construct the model to generate a data warehouse model.

[0152] In the embodiment of the present application, as Figure 5As shown, based on the table structure data (such as table name, field annotation) in the DDL table creation statement, the data warehouse theme domain is divided according to the preset business theme domain (such as empty domain analysis, equipment analysis, flying hand analysis, video analysis, job analysis, etc.), and a classification list containing the theme domain to which each table belongs is generated. Due to the different functional positioning of each data layer, the corresponding data organization (model design strategy) is also different, and a suitable model design strategy and storage period are formulated. Specifically, by mapping the table structure corresponding to each layer DDL statement in the DDL table creation statement to the corresponding level, a model framework is constructed. Then the partition strategy and storage format are extracted from the DDL table creation statement to generate a physical storage configuration scheme. Then according to the data magnitude prediction selection of the DDL table creation statement, a physical design optimization scheme is generated. And based on the inter-table association relationship in the DDL table creation statement, hot spots are identified and managed, and a model optimization suggestion list is generated. The classification list, model framework, physical storage configuration scheme, physical design optimization scheme and model optimization suggestion list are used for model construction to generate a data warehouse model. The model standardization specification includes table, field naming specification and field type specification.

[0153] Preferably, the specific steps of step S5 are:

[0154] S51, binding a preset number of quality rule templates based on the table structure of the data warehouse model to generate a data quality detection task;

[0155] S52, using the data quality detection task, the full-link blood relationship graph corresponding to the data warehouse model, calculating the health score of each partition table, and generating a management list;

[0156] S53, constructing a data quality problem work order by using the detection abnormal data corresponding to the data quality detection task, the partition positioning information of the data warehouse model, the responsibility person information, the quality score state and the rectification requirement of the management list;

[0157] S54, performing partition-level alarm pushing and rectification tracking according to the data quality problem work order to generate a rectification record;

[0158] S55, using the rectification record, the quality rule template and the management list to construct a module to generate a full-process data quality monitoring module;

[0159] S56, constructing a data governance system based on the DDL table creation statement, the business theme domain, the table structure of the data warehouse model, the full-link blood relationship graph and the partition strategy;

[0160] S57, using the data quality problem work order, the rectification record, the full-process data quality monitoring module and the data governance system to construct a one-stop data governance platform.

[0161] Further, the specific steps of step S56 are:

[0162] S561, combing data physical storage location and partition flow path based on table structure, full-link blood relationship graph and partition strategy, generating CRUD matrix and visual flow graph;

[0163] S562, auditing the compliance of the partition strategy in the DDL table creation statement based on the partition strategy in the DDL table creation statement and the model framework corresponding to the data warehouse model, and generating a partition compliance report;

[0164] S563, integrating the CRUD matrix, the visual flow graph and the partition compliance report into the scheduling system of the ETL synchronization task to generate a target scheduling system;

[0165] S564, setting task parallelism and abnormal retry logic in the target scheduling system according to the partition strategy corresponding to the partition compliance report to generate a scheduling process with partition optimization;

[0166] S565, sampling and verifying the key business theme domain of the data warehouse model, and generating a quality verification report containing partition dimensions combined with built-in dimensions;

[0167] S566, using the CRUD matrix, the visual flow graph, the partition compliance report, the target scheduling system, the scheduling process and the quality verification report to build a data governance system.

[0168] In the embodiment of the application, the table structure of the data warehouse model is bound to a preset number of quality rule templates to obtain a data quality detection task. The health score of each partition table is calculated by using the data quality detection task and the full-link blood relationship graph corresponding to the data warehouse model, and a governance list is obtained. The data quality score audit mechanism automatically monitors the data quality, thereby realizing the pre-event, in-event and post-event monitoring of the data. The detection of abnormal data corresponding to the data quality detection task, the partition positioning information of the data warehouse model, the information of the person in charge, and the quality score state and rectification requirements of the governance list are used to build a data quality problem work order. The partition-level alarm push and rectification tracking are performed according to the data quality problem work order, and a rectification record is generated. The rectification record, the quality rule template and the governance list are used to build a full-process data quality monitoring module. Then, the table structure, the full-link blood relationship graph and the partition strategy are used to comb the data physical storage location and the partition flow path, and the CRUD matrix and the visual flow graph are generated. The partition compliance report is generated by auditing the compliance of the partition strategy in the DDL table creation statement based on the partition strategy in the DDL table creation statement and the model framework corresponding to the data warehouse model.

[0169] The CRUD matrix, visual flow diagram, and partition compliance report are integrated into the scheduling system of the ETL synchronization task to generate a target scheduling system. In the target scheduling system, the task parallelism and abnormal retry logic are set according to the partition strategy corresponding to the partition compliance report, and a scheduling process with partition optimization is generated. The key business theme domains of the data warehouse model are sampled and verified, and combined with built-in dimensions, a quality verification report containing partition dimensions is generated. The CRUD matrix, visual flow diagram, partition compliance report, target scheduling system, scheduling process, and quality verification report are used to build a data governance system. Finally, a one-stop data governance platform is built by using data quality problem tickets, rectification records, full-process data quality monitoring modules, and the data governance system. The generated one-stop data governance platform has a standardized data governance system:

[0170] 1. Data distribution CRUD matrix: the physical storage location, flow path, and operations (Create, Read, Update, Delete) of data in the entire processing flow (from the source to the application).

[0171] 2. Data model audit: covering all-dimension metadata such as device information (physical model deployment compliance) and interface specifications (upstream and downstream system interface consistency) to ensure the compliance and consistency of data models in the design, development, and operation life cycle.

[0172] 3. Data audit process scheduling: seamlessly integrating data audit processes into the entire scheduling system to achieve active and automatic quality monitoring. The audit task is embedded as a priority rule in the scheduling dependency chain, and quality audit is injected through the full-link rule of "detection → collection → processing → release" to ensure that only trusted data can flow to the final application.

[0173] Global key data sampling audit: full-process verification of timeliness, completeness, legality, accuracy, consistency, and uniqueness.

[0174] Specifically, Figure 5The overall business coverage and hierarchical design of the platform are depicted. The integrated data warehouse and data governance platform, namely the one-stop data governance platform, comprises a data source (basic data source), data collection (data acquisition method), data warehouse (core analysis module), data service (functional service output), data application (terminal application scenario), and data asset and governance (management and quality control). The data source comprises image data, environmental detection data, video monitoring data, flight trajectory data, buried point data, and reported data. The data collection comprises offline data collection and real-time data collection. The data warehouse comprises commodity analysis, task analysis, airport analysis, pilot analysis, user analysis, airspace analysis, operation analysis, video analysis, market analysis, and equipment analysis. The data service comprises data query service, algorithm service, and label service. The data application comprises data large screen, intelligent operation platform, and unmanned aerial vehicle data platform. The data asset comprises data asset and data governance. The data asset focuses on asset management and comprises asset analysis (asset inventory and asset label). The data governance ensures data quality and specification, and comprises model management and data quality.

[0175] Figure 6 The whole process and quality control system of data governance are explained. The data whole life cycle processing and governance process of the one-stop data governance platform is as follows:

[0176] I. Core data and processing actions of each link

[0177] 1. Data source (basic input): covering the original data / demands of six business scenarios, comprising:

[0178] (1) Production and processing: data collection, coding mapping, data processing rules, and unified view demand;

[0179] (2) Metadata: equipment information, interface specification, coding standard, hardware parameter, entity object definition, and index standard;

[0180] (3) Operation scheduling: file transmission instruction, collection / warehouse / market flow scheduling strategy;

[0181] (4) Quality audit: data source self-checking rule, file / record auditing logic, hierarchical auditing standard, and index auditing requirement;

[0182] (5) Problem processing: retransmission / rework work order, and abnormal problems of each link (data source / processing / index);

[0183] (6) Operation supervision: data collection monitoring index, timeliness / completeness assessment standard, and processing / release monitoring rule.

[0184] 2. Data collection (preliminary regularization): performing “acquisition-cleaning-scheduling” on the data source:

[0185] Action: File acquisition, merging, decompression, warehousing; encoding mapping (unified format); interface specification verification; collection process scheduling;

[0186] Objective: Preliminary filtering of invalid data, ensuring controllable collection process, providing "clean raw materials" for the warehouse layer.

[0187] 3. Data warehouse (structured conversion): Perform "format-encoding-model" triple conversion to create a standardized data layer:

[0188] Action: Format conversion (adapt to warehouse storage), encoding conversion (unified encoding system), model conversion (build data relationships);

[0189] Core processing: Data distribution (follow CDI standards, embed data audit), warehouse data processing flow scheduling, data model construction (support upper-layer applications).

[0190] 4. Data mart (business theme refinement): Focus on specific business scenarios and perform "thematic sedimentation" on warehouse data:

[0191] Action: Cube sedimentation (aggregate business indicators), unified view processing (simplify business queries), mart process scheduling;

[0192] Objective: Refine "ready-to-use data sets" that can directly support applications and reduce business usage costs.

[0193] 5. Application (value landing): Data ultimately serves business, outputting application release (such as data reports, business system functions), business management (process control, decision support), application release control (ensuring compliance with release).

[0194] II. Data governance (full-process escort) Governance runs through the entire process of collection-warehouse-mart-application, focusing on three dimensions:

[0195] (1) Quality control: Through data source pre-self-check, file / record audit, hierarchical data audit, and index audit, covering "timeliness, completeness, legality" and other multi-dimensional verification.

[0196] (2) Problem closed loop: Relying on retransmission / rework work orders and exception problem work orders (data source / processed data / indicators) at each stage, realize the closed loop of problem discovery→processing→verification.

[0197] (3) Operation monitoring: Monitor data collection / processing process, assess data timeliness / completeness, and warn of abnormalities to ensure efficient and stable process.

[0198] Therefore, the one-stop data governance platform constructed by the application can provide data integration, data development, metadata management and other full-link data development, governance and operation capabilities, help improve development efficiency, help data teams efficiently build enterprise-level data warehouses, more efficiently explore data and provide data support for subsequent data application construction, and help enterprises drive business growth with data.

[0199] Further, please refer to Figure 7 , Figure 7 The structural diagram of the metadata-driven integrated data warehouse and governance platform construction system of the application.

[0200] A metadata-driven integrated data warehouse and governance platform construction system comprises:

[0201] A target data set construction module 701 is configured to collect full metadata in a data source through a metadata automatic collection adapter, extract a full-link blood relationship graph and an enterprise data map, and construct a target data set.

[0202] A target table and synchronization task generation module 702 is configured to perform a data table one-key synchronization operation based on the target data set in a control table and parameterized pipeline manner, and generate an ODS target table and an ETL synchronization task.

[0203] A DDL table construction statement construction module 703 is configured to construct a DDL table construction statement according to the ODS target table and the ETL synchronization task.

[0204] A data warehouse model generation module 704 is configured to divide a model according to a business theme domain based on the DDL table construction statement, and automatically recommend a bucketing strategy in combination with a data volume level prediction, and generate a data warehouse model.

[0205] A data governance platform construction module 705 is configured to construct a one-stop data governance platform by using a table structure of the data warehouse model, a full-link blood relationship graph and a partitioning strategy.

[0206] Further, the target data set construction module 701 can perform the following steps:

[0207] Step S11, collect full metadata of a data source through a metadata automatic collection adapter.

[0208] Step S12, extract data entity dependency relationships and conversion rules from the full metadata, and generate a full-link blood relationship graph.

[0209] Step S13, visualize data entities, flow paths and processing relationships in the full metadata, and generate an enterprise data map.

[0210] Step S14, using full metadata, full link blood relationship graph and enterprise data map, the target data set is constructed.

[0211] Further, the target table and synchronization task generation module 702 can perform the following steps:

[0212] S21, according to the table object name input by the user, the corresponding source table basic parameters in the target data set are selected, and the table object name is stored in the control table;

[0213] S22, read the table object data in the control table, and use the source table basic parameter configuration synchronization application to generate synchronization application data;

[0214] S23, copy the ODS layer table structure and inject the data warehouse standard field to generate the ODS target table;

[0215] S24, using the full link blood relationship graph in the target data set, the field mapping relationship between the ODS target table and the business system table in the data source is analyzed to generate a correlation relationship mapping table;

[0216] S25, identify the foreign key field in the ODS target table, and match the corresponding correlation table metadata in the target data set to generate a foreign key field list;

[0217] S26, according to the correlation relationship mapping table and the foreign key field list, the dimension modeling optimization is performed to generate the data warehouse hierarchical table structure and the table interrelation list;

[0218] S27, based on the data warehouse hierarchical table structure and the table interrelation list, the data conversion rules are integrated to generate the ETL synchronization task.

[0219] Further, the target table and synchronization task generation module 702 can perform the following steps:

[0220] Step S261, according to the correlation relationship mapping table, the ODS target table is split into DWD table and DWS table;

[0221] Step S262, the foreign key field of the foreign key field list, the business tag field and the data warehouse standard field are added to the DWD table, and the business field in the table is cleaned to generate the DWD table structure;

[0222] Step S263, according to the correlation table dimension, the foreign key field list and the business cycle, the partitioning strategy is set;

[0223] Step S264, using the DWD table, the DWS table, the DWD table structure and the partitioning strategy, the data warehouse hierarchical table structure and the table interrelation list are constructed.

[0224] Further, the target table and synchronization task generation module 702 can further perform the following steps:

[0225] Step S271, extracting the business requirement parameters from the synchronization application data and parsing the ODS layer table business domain label to generate a task configuration file;

[0226] Step S272, using the data warehouse hierarchical table structure and the inter-table association relationship list to construct a task data flow path;

[0227] Step S273, extracting the field conversion logic from the conversion rule library in the target data set to generate a task script fragment;

[0228] Step S274, using the synchronization strategy parameters, the task configuration file, the task data flow path, and the task script fragment to construct an ETL synchronization task script;

[0229] Step S275, associating the ETL synchronization task script with the data warehouse table structure and performing quality verification to generate an ETL synchronization task.

[0230] Further, the DDL table creation statement construction module 703 can perform the following steps:

[0231] S31, extracting table structure data from the ODS target table to generate an ODS layer DDL framework;

[0232] S32, parsing the field conversion rules in the ETL synchronization task and converting the field conversion rules into field constraint conditions;

[0233] S33, using the foreign key fields in the data warehouse hierarchical table structure, the cleaning rules, and the preset partitioning strategy to construct DWD layer initial DDL statements and DWS layer initial DDL statements;

[0234] S34, extracting foreign key association data from the inter-table association relationship list;

[0235] S35, adding constraint definitions corresponding to the foreign key association data to the DWD layer initial DDL statements and the DWS layer initial DDL statements to generate DWD layer target DDL statements and DWS layer target DDL statements;

[0236] S36, associating the ODS layer DDL framework, the DWD layer target DDL statements, the DWS layer target DDL statements, and the scheduling parameters of the ETL synchronization task to generate DDL table creation statements.

[0237] Further, the data warehouse model generation module 704 can perform the following steps:

[0238] S41, dividing the subject domains according to the preset business subject domains to generate a classification list;

[0239] S42, map the table structure corresponding to each layer DDL statement in the DDL table creation statement to the corresponding level, and construct a model framework;

[0240] S43, extract the partition strategy and storage format from the DDL table creation statement, and generate a physical storage configuration scheme;

[0241] S44, select a bucketing strategy according to the data magnitude prediction in the DDL table creation statement, and generate a physical design optimization scheme;

[0242] S45, identify and manage hotspots based on the inter-table association relationship in the DDL table creation statement, and generate a model optimization suggestion list;

[0243] S46, use the classification list, model framework, physical storage configuration scheme, physical design optimization scheme and model optimization suggestion list to build a model, and generate a data warehouse model.

[0244] Further, the data governance platform construction module 705 can perform the following steps:

[0245] S51, bind a preset number of quality rule templates to the table structure of the data warehouse model, and generate a data quality detection task;

[0246] S52, use the data quality detection task and the full-link blood relationship graph corresponding to the data warehouse model to calculate the health score of each partition table, and generate a governance list;

[0247] S53, construct a data quality problem work order based on the detection abnormal data corresponding to the data quality detection task, the partition positioning information of the data warehouse model, the person in charge information, the quality score state of the governance list and the rectification requirements;

[0248] S54, execute partition-level alarm pushing and rectification tracking according to the data quality problem work order, and generate a rectification record;

[0249] S55, use the rectification record, quality rule template and governance list to build a module, and generate a full-process data quality monitoring module;

[0250] S56, based on the DDL table creation statement, business theme domain, table structure of the data warehouse model, full-link blood relationship graph and partition strategy, construct a data governance system;

[0251] S57, use the data quality problem work order, rectification record, full-process data quality monitoring module and data governance system to build a one-stop data governance platform.

[0252] Further, the data governance platform construction module 705 can also perform the following steps:

[0253] S561, based on the table structure, full-link blood relationship map and partition strategy, comb data physical storage location and partition flow path, generate CRUD matrix and visual flow map;

[0254] S562, adopt the partition strategy in the DDL table creation statement and the model framework corresponding to the data warehouse model to audit the partition strategy compliance in the DDL table creation statement, and generate a partition compliance report;

[0255] S563, integrate the CRUD matrix, the visual flow map and the partition compliance report into the scheduling system of the ETL synchronization task to generate a target scheduling system;

[0256] S564, set task parallelism and abnormal retry logic in the target scheduling system according to the partition strategy corresponding to the partition compliance report to generate a scheduling process with partition optimization;

[0257] S565, sample and verify the key business theme domain of the data warehouse model, and generate a quality verification report containing partition dimensions in combination with built-in dimensions;

[0258] S566, adopt the CRUD matrix, the visual flow map, the partition compliance report, the target scheduling system, the scheduling process and the quality verification report to build a data governance system.

[0259] The above is only a preferred embodiment of the present application, and is not intended to limit the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A metadata-driven integrated data warehouse and governance platform construction method, characterized by: The following steps are involved: Step S1: Use the metadata automatic collection adapter to collect all metadata from the data source, extract the full-link blood relationship map and enterprise data map, and build the target data set; Step S2: Based on the target data set, a one-click synchronization operation of the data table is performed using a control table and a parameterized pipeline to generate an ODS target table and an ETL synchronization task; Step S3: Construct a DDL table creation statement according to the ODS target table and the ETL synchronization task; Step S4: Based on the DDL table creation statement, the model is divided by business subject domain, and a bucketing strategy is automatically recommended in combination with data level prediction to generate a data warehouse model; Step S5: Use the table structure, full-link blood relationship map and partitioning strategy of the data warehouse model to build a one-stop data governance platform.

2. The method according to claim 1, characterized in that The specific steps of step S1 are: Step S11: Collect the full metadata of the data source through the metadata automatic collection adapter; Step S12: Extract data entity dependencies and conversion rules from the full metadata to generate a full-link lineage relationship graph; Step S13: Visualize the data entities, flow paths, and processing relationships in the full metadata to generate an enterprise data map; Step S14: Use the full metadata, the full-link blood relationship map and the enterprise data map to construct a target data set.

3. The method according to claim 1, characterized in that The specific steps of step S2 are: S21. According to the table object name input by the user, select the corresponding source table basic parameters in the target data set, and store the table object name in the control table; S22. Read the table object data in the control table, and configure the synchronization application using the basic parameters of the source table to generate synchronization application data; S23. Copy the ODS layer table structure and inject the data warehouse standard fields to generate the ODS target table; S24. Using the full-link blood relationship graph in the target data set, analyze the field mapping relationship between the ODS target table and the business system table in the data source to generate an association relationship mapping table; S25, identifying the foreign key fields in the ODS target table, matching the corresponding associated table metadata in the target dataset, and generating a foreign key field list; S26. Perform dimensional modeling optimization based on the association relationship mapping table and the foreign key field list to generate a data warehouse hierarchical table structure and a list of association relationships between tables; S27: Based on the data warehouse hierarchical table structure and the list of relationships between tables, integrate data conversion rules and generate ETL synchronization tasks.

4. The method according to claim 3, characterized in that The specific steps of step S26 are: Step S261: Split the ODS target table into a DWD table and a DWS table according to the association relationship mapping table; Step S262: Add the foreign key fields, added business tag fields, and data warehouse standard fields in the foreign key field list to the DWD table, clean the business fields in the table, and generate a DWD table structure. Step S263: Set a partitioning strategy based on the associated table dimensions, the foreign key field list, and the business cycle; Step S264: Use the DWD table, the DWS table, the DWD table structure, and the partitioning strategy to construct a data warehouse hierarchical table structure and a list of associations between tables.

5. The method according to claim 3, characterized in that The specific steps of step S27 are: Step S271: extracting business requirement parameters from the synchronization application data, parsing the business domain tags of the ODS layer table, and generating a task configuration file; Step S272: Using the data warehouse hierarchical table structure and the inter-table relationship list, construct a task data flow path; Step S273: extracting field conversion logic from the conversion rule library in the target data set to generate a task script fragment; Step S274: Construct an ETL synchronization task script using synchronization strategy parameters, the task configuration file, the task data flow path, and the task script fragment; Step S275: Associating the ETL synchronization task script with the data warehouse table structure and performing quality verification to generate an ETL synchronization task.

6. The method according to claim 3, characterized in that The specific steps of step S3 are: S31, extracting table structure data from the ODS target table and generating an ODS layer DDL framework; S32. Analyze the field conversion rules in the ETL synchronization task and convert the field conversion rules into field constraints; S33: Constructing the initial DDL statements for the DWD layer and the initial DDL statements for the DWS layer using the foreign key fields, cleaning rules, and preset partitioning strategies in the data warehouse hierarchical table structure; S34, extracting foreign key association data from the inter-table association relationship list; S35: Add the constraint definition corresponding to the foreign key associated data to the DWD layer initial DDL statement and the DWS layer initial DDL statement to generate a DWD layer target DDL statement and a DWS layer target DDL statement; S36 , associating the ODS layer DDL framework, the DWD layer target DDL statement, the DWS layer target DDL statement with the scheduling parameters of the ETL synchronization task to generate a DDL table creation statement.

7. The method according to claim 1, characterized in that The specific steps of step S4 are: S41. Divide the subject domains according to the preset business subject domains and generate a classification list; S42, mapping the table structure corresponding to each layer of DDL statements in the DDL table creation statement to the corresponding level to build a model framework; S43, extracting the partitioning strategy and storage format from the DDL table creation statement to generate a physical storage configuration solution; S44. Select a bucketing strategy based on the data level prediction in the DDL table creation statement to generate a physical design optimization solution; S45. Identify governance hotspots based on the inter-table association relationships in the DDL table creation statements and generate a list of model optimization suggestions; S46: Build a model using the classification list, the model framework, the physical storage configuration solution, the physical design optimization solution, and the model optimization suggestion list to generate a data warehouse model.

8. The method according to claim 1, characterized in that The specific steps of step S5 are: S51, binding a preset number of quality rule templates based on the table structure of the data warehouse model to generate a data quality detection task; S52: Calculate the health score of each partition table using the data quality detection task and the full-link lineage relationship map corresponding to the data warehouse model, and generate a governance list; S53: Construct a data quality problem work order based on the abnormal detection data corresponding to the data quality detection task, the partition location information of the data warehouse model, the responsible person information, the quality score status of the governance checklist, and the rectification requirements; S54: Execute zone-by-zone and graded alarm push and rectification tracking according to the data quality issue work order, and generate rectification records; S55. Build a module using the rectification record, the quality rule template, and the governance checklist to generate a full-process data quality monitoring module; S56. Build a data governance system based on the DDL table creation statement, the business subject domain, the table structure of the data warehouse model, the full-link lineage relationship map, and the partitioning strategy; S57. Use the data quality problem work order, the rectification record, the full-process data quality monitoring module and the data governance system to build a one-stop data governance platform.

9. The method according to claim 8, characterized in that The specific steps of step S56 are: S561. Based on the table structure, the full-link blood relationship map, and the partition strategy, sort out the data physical storage location and partition flow path, and generate a CRUD matrix and a visual flow map; S562: Using the partitioning strategy in the DDL table creation statement and the model framework corresponding to the data warehouse model, audit the compliance of the partitioning strategy in the DDL table creation statement and generate a partitioning compliance report; S563: Integrate the CRUD matrix, the visual flow map, and the partition compliance report into the scheduling system of the ETL synchronization task to generate a target scheduling system; S564: Setting task parallelism and exception retry logic in the target scheduling system according to the partition strategy corresponding to the partition compliance report, and generating a scheduling process with partition optimization; S565. Sampling and verifying the key business subject domains of the data warehouse model, and generating a quality verification report including partition dimensions in combination with built-in dimensions; S566. Build a data governance system using the CRUD matrix, the visual flow map, the partition compliance report, the target scheduling system, the scheduling process, and the quality verification report.

10. Metadata-driven integrated data warehouse and governance platform construction system, characterized by: include: The target dataset construction module is used to collect all metadata from the data source through the metadata automatic collection adapter, extract the full-link lineage relationship map and enterprise data map, and build the target dataset; A target table and synchronization task generation module is used to perform a one-click synchronization operation on the data table based on the target data set using a control table and parameterized pipeline to generate an ODS target table and an ETL synchronization task; A DDL table creation statement construction module is used to construct a DDL table creation statement according to the ODS target table and the ETL synchronization task; A data warehouse model generation module is used to divide the model by business subject domain based on the DDL table creation statement, and automatically recommend bucketing strategies in combination with data level prediction to generate a data warehouse model; The data governance platform construction module is used to build a one-stop data governance platform by adopting the table structure, full-link lineage relationship map and partitioning strategy of the data warehouse model.

Images