CN119271750A - Data processing method, device, equipment, medium and program product for data warehouse - Google Patents

Abstract

The present invention relates to the field of big data technology, and provides a data processing method, device, equipment, medium and program product for a data warehouse, including: in response to a business update instruction, obtaining each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtaining the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table; constructing an entity relationship model based on the association relationship and the dependency relationship, and delivering the modeling information of the entity relationship model to the data warehouse layer by layer. The present invention achieves the purpose of intuitively displaying the data warehouse model information by automatically synchronizing the business source table at the ODS layer in the data warehouse, automatically constructing the entity relationship model and delivering the modeling information to the lower layer, without the need for human participation, ensuring that the modeling information in the data warehouse is more efficient and comprehensive, facilitating users to fully understand data assets, and improving data query efficiency and data usage efficiency.

Description

Data processing method, device, equipment, medium and program product for data warehouse

Technical Field

The present invention relates to the field of big data technology, and in particular to a data processing method, device, equipment, medium and program product for a data warehouse.

Background Art

At present, data warehouses are widely used because of their powerful data integration, storage and analysis capabilities. However, due to lax specifications or other historical problems such as loss of model design information, there are a large number of data tables with missing modeling information in the data warehouse, which makes it more difficult for users or data engineers to query and use data. In order to fill the missing modeling information, data engineers usually sort out the missing modeling information based on their own business knowledge and past experience to redesign the missing modeling information; the entire data maintenance process is not only labor-intensive and difficult to maintain continuously, but also the design standards are subjective and fixed, and easily confused with the flow of data engineers, thus failing to achieve efficient and continuous improvement of data warehouse modeling information.

Summary of the invention

The present invention aims to solve at least one of the technical problems existing in the related art. To this end, the present invention proposes a data processing method for a data warehouse, which can intuitively display model information such as association relationships and dependency relationships of all tables in the data warehouse. The entire data maintenance process does not require human participation, ensuring that the modeling information in the data warehouse is more efficient and comprehensive, and facilitating users to fully understand data assets, thereby greatly improving data query efficiency and data usage efficiency.

The present invention also provides a data processing device for a data warehouse.

The invention also provides an electronic device.

The present invention also provides a non-transitory computer-readable storage medium.

The present invention also provides a computer program product.

A data processing method for a data warehouse according to a first embodiment of the present invention includes:

In response to the business update instruction, each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table are obtained, and the association relationship between the business source tables and the dependency relationship between the business source tables and the data warehouse target table are obtained;

An entity relationship model is constructed based on the association relationship and the dependency relationship, and modeling information of the entity relationship model is transmitted layer by layer to the data warehouse.

According to the data processing method of the data warehouse of the embodiment of the present invention, when responding to the business update instruction, first obtain each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtain the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table, and then further build an entity relationship model based on the association relationship and dependency relationship, and pass the modeling information of the entity relationship model to the data warehouse layer by layer. In this way, when the business data in the business system changes, the business source table is automatically synchronized by the ODS layer in the data warehouse, and the entity relationship model is automatically built and the modeling information is passed to the lower layer, so as to realize the intuitive display of the model information such as the association relationship and dependency relationship of all tables in the data warehouse, and the entire data maintenance process does not require human participation, ensuring that the modeling information in the data warehouse is improved more efficiently and comprehensively, and it is convenient for users to understand data assets in an all-round way, thereby greatly improving the efficiency of data query and data use.

According to an embodiment of the present invention, the step of constructing an entity relationship model based on the association relationship and the dependency relationship includes:

Obtaining the data granularity and subject domain of each of the business source tables;

The entity relationship model is constructed based on the association relationship and the dependency relationship, as well as each data granularity and each subject domain.

According to an embodiment of the present invention, constructing the entity relationship model based on the association relationship and the dependency relationship, as well as each data granularity and each subject domain includes:

Determine a first table set, wherein the first table set includes a plurality of ODS tables determined based on the business source tables, each of the ODS tables respectively identifying a corresponding subject domain and data granularity and a corresponding association relationship and/or a corresponding dependency relationship;

Remove the ODS table from the first table set, and determine all subsequent target tables with the ODS table as a source table;

Determine all predecessor source tables that use the successor target table as the target table in all the successor target tables, and determine the target entity relationship model based on the query results of the cross-connection relationships between each two in all the predecessor source tables and the entity relationship model;

The first table set is filled based on the successor target table, and then the next successor target table is selected from all the successor target tables, and the step of determining all the predecessor source tables with the successor target table in all the successor target tables as the target table is returned to execute; until all the successor target tables are traversed, the corresponding filled first table set is used as the new first table set, and the corresponding target entity relationship model obtained when all the successor target tables are traversed is used as the new entity relationship model, and the above process is repeated; until the first table set is empty, the corresponding target entity relationship model is determined as the constructed entity relationship model.

According to one embodiment of the present invention, determining all predecessor source tables that use the successor target table in all the successor target tables as the target table includes:

When the ODS table in the first table set is moved out to the second table set, if the successor target table does not exist in both the first table set and the second table set, all predecessor source tables with the successor target table as the target table are determined from the entity relationship model.

According to an embodiment of the present invention, determining all predecessor source tables with the successor target table as the target table from the entity relationship model includes:

In the case where the ODS table in the entity relationship model is the primary table of the successor target table, the data granularity and the subject domain of the ODS table are copied to the successor target table, and all predecessor source tables with the successor target table as the target table are determined based on the copy result; in the case where the ODS table is a non-primary table of the successor target table, all predecessor source tables with the successor target table as the target table are determined.

According to one embodiment of the present invention, the method further includes:

In the case where the ODS table is the master table of the subsequent target table, the data granularity and the subject domain of the ODS table, as well as the business classification information of the ODS table are copied to the subsequent target table.

According to an embodiment of the present invention, determining the target entity relationship model based on the query results of the pairwise cross-connection relationships in all the predecessor and successor source tables and the entity relationship model includes:

When the pairwise cross-connection relationship is found in all the predecessor and successor source tables, the entity relationship model is updated based on the pairwise cross-connection relationship to obtain the target entity relationship model;

When the pairwise cross-connection relationship is not found in all the predecessor source tables, the entity relationship model is determined as the target entity relationship model.

A data processing device for a data warehouse according to an embodiment of the second aspect of the present invention includes:

A relationship acquisition unit, configured to acquire, in response to a business update instruction, each business source table synchronized to the ODS layer in the data warehouse and a data warehouse target table corresponding to each business source table, and acquire an association relationship between each business source table and a dependency relationship between each business source table and the data warehouse target table;

A model building unit is used to build an entity relationship model based on the association relationship and the dependency relationship, and transmit modeling information of the entity relationship model to the data warehouse layer by layer.

According to the data processing device of the data warehouse of the embodiment of the present invention, when responding to the business update instruction, it first obtains each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtains the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table, and then further builds an entity relationship model based on the association relationship and dependency relationship, and transmits the modeling information of the entity relationship model to the data warehouse layer by layer. In this way, when the business data in the business system changes, the business source table is automatically synchronized by the ODS layer in the data warehouse, and the entity relationship model is automatically constructed and the modeling information is transmitted to the lower layer, so as to realize the intuitive display of the model information such as the association relationship and dependency relationship of all tables in the data warehouse. The entire data maintenance process does not require human participation, ensuring that the modeling information in the data warehouse is improved more efficiently and comprehensively, and it is convenient for users to understand data assets in an all-round way, thereby greatly improving the efficiency of data query and data use.

The above one or more technical solutions in the embodiments of the present invention have at least one of the following technical effects: when responding to a business update instruction, first obtain each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtain the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table, and then further build an entity relationship model based on the association relationship and dependency relationship, and pass the modeling information of the entity relationship model to the data warehouse layer by layer. In this way, when the business data in the business system changes, the business source table is automatically synchronized by the ODS layer in the data warehouse, and the entity relationship model is automatically built and the modeling information is passed to the lower layer, so as to realize the intuitive display of the model information such as the association relationship and dependency relationship of all tables in the data warehouse. The entire data maintenance process does not require human participation, ensuring that the modeling information in the data warehouse is more efficient and comprehensive, and it is convenient for users to understand data assets in all aspects, thereby greatly improving data query efficiency and data usage efficiency.

Furthermore, the subject and data granularity information in the data warehouse can be improved by automatically dividing the subject domain and automatically identifying the granularity of the data warehouse table. This can not only effectively supplement the data model information, but also play a role in checking the degree of standardization of the data warehouse model. It is automatically executed by the program to avoid standard confusion caused by differences in experience among modelers. It can be widely used in fields such as data warehouses and analytical mining.

Furthermore, by automatically identifying the business domain and data granularity of the data warehouse table, it is convenient for business personnel to quickly locate the required data according to the business domain, helping developers reduce the workload of exploring data granularity.

Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention.

BRIEF DESCRIPTION OF THE DRAWINGS

In order to more clearly illustrate the technical solutions in the embodiments of the present invention or related technologies, the drawings required for use in the embodiments or related technical descriptions are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

FIG. 1 is a flowchart of a data processing method for a data warehouse provided by an embodiment of the present invention.

FIG. 2 is a second flowchart of the data processing method for a data warehouse provided in an embodiment of the present invention.

FIG. 3 is a third flowchart of the data processing method for a data warehouse provided in an embodiment of the present invention.

FIG. 4 is a schematic diagram of the structure of a data processing device for a data warehouse provided in an embodiment of the present invention.

FIG. 5 is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention.

DETAILED DESCRIPTION

In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

In the embodiments of the present invention, "at least one" refers to one or more, and "more than one" refers to two or more. "And/or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and/or B may represent: A exists alone, A and B exist at the same time, and B exists alone, where A and B may be singular or plural. In the textual description of the present invention, the character "/" generally indicates that the previous and next associated objects are in an "or" relationship. In addition, it should be noted that the serial numbers themselves, such as "first", "second", etc., used to distinguish the objects described in the present invention are only used to distinguish the objects described, and do not have any order or technical meaning.

The following is an explanation of the relevant professional terms involved in the present invention:

Data model: used to describe the structure and relationship of data in the data warehouse, including entity attributes, associations and dependencies of tables, etc.

Data domain: A data set that is divided by summarizing and abstracting the business process from the bottom up from the perspective of business data.

Subject domain: An abstract concept that integrates, summarizes, and analyzes the data in the business system from top to bottom from the perspective of data analysis application. There are many ways to divide it. If it is divided according to the business system, it has the same meaning as the data domain.

Data granularity: The degree of detail or scope of the phenomenon or event described by the data.

ODS (Operational Data Store) is a temporary area used by the data warehouse to store business operational data. Its table fields are isomorphic to the business side and are the most upstream of the data warehouse table dependency hierarchy (i.e., all are starting tables).

Schema: represents the structure in the database, including descriptions of tables, columns, data types, and constraint information such as primary keys and unique keys.

sqoop.job.data.publish.class: A hook plug-in of the Sqoop component, which is triggered when business data such as MySQL/Oracle is extracted to the data warehouse, and can obtain the business source table of the synchronized data and the data warehouse ODS target table information.

hive.exec.post.hooks: A hook plug-in for Hive components. It is triggered after the ETL script is successfully executed. It can be used to obtain information such as the table association and dependency of Hive SQL.

spark.sql.queryExecutionListeners: A hook plug-in for Spark components. It is triggered after the ETL script is successfully executed, from which information such as Spark SQL table associations and dependencies can be obtained.

Table associations and dependencies: The relationships between data warehouse tables extracted from the ETL code. Associations represent JOIN relationships between ETL source tables, and dependencies represent blood relationships between source tables and target tables, such as ETL logic.

For example, the ETL logic is: INSERT OVERWRITE TABLE t SELECT s1.field,s2.col,s3.value FROM s1 JOIN s2 ON s1.id=s2.key LEFT JOIN s3 ON s2.fk=s3.name; the extracted dependencies are: (s1,t), (s2,t), (s3,t), where s1, s2, s3 are source tables and t1 is the target table; the association relationships are: <s1,JOIN:id=key,s2> and <s2,LEFT JOIN:fk=name,s3>, where d=key and fk=name are association keys.

In the field of big data technology, as the amount of business data in enterprises continues to increase, the use of data warehouses, a system dedicated to integrating, storing and managing various types of enterprise data, can ensure that users can easily perform complex data queries and data analysis operations. However, data warehouses also have historical problems in the specific application process. For example, due to the lack of strict specifications or other historical problems such as the loss of model design information, there are a large number of data tables with missing modeling information in the data warehouse, which makes it more difficult for users or data engineers to query and use data.

In order to fill in the missing modeling information, data engineers usually sort out the information based on their own business knowledge and past experience to redesign the missing modeling information; the entire data maintenance process is not only labor-intensive and difficult to maintain continuously and iteratively, but also the design standards are subjective and fixed, and easily confused with the flow of data engineers, thus failing to achieve efficient and continuous modeling of data warehouse information. Therefore, how to efficiently maintain various types of data in the data warehouse is particularly important.

In order to solve the above technical problems, the present invention provides a data processing method, device, equipment, medium and program product for a data warehouse. The data processing method, device, equipment, medium and program product for a data warehouse provided by the present invention are described below in conjunction with Figures 1 to 5, wherein the execution subject of the data processing method for a data warehouse is a data warehouse. Furthermore, the data processing method can also be applied to a data processing device arranged in a data warehouse, and the data processing device can be implemented by software, hardware or a combination of the two. The data processing method is described below by taking the execution subject of the data processing method as a data warehouse as an example.

In order to facilitate understanding of the data processing method of the data warehouse provided by the embodiment of the present invention, the data processing method of the data warehouse provided by the present invention will be described in detail below through the following exemplary embodiments. It can be understood that the following exemplary embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments.

1 , which is a flow chart of a data processing method for a data warehouse provided in an embodiment of the present invention, as shown in FIG1 , the data processing method includes the following steps 110 and 120 .

Step 110, in response to the business update instruction, obtain each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtain the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table.

The business update instruction is an instruction automatically generated when the business data in a business system changes. The number of association relationships and dependency relationships is at least 1.

Each association relationship can specifically represent that at least two business source tables in multiple business source tables have an association relationship; each dependency relationship can specifically represent that a data warehouse target table has a dependency relationship with at least two corresponding business source tables in multiple business source tables, such as generating a corresponding data warehouse target table after processing, cleaning and other processing such as at least two corresponding business source tables in multiple business source tables.

Enterprises can usually set up multiple business systems according to their specific needs and business scope. Multiple business systems may include but are not limited to human resource management systems, financial management systems, supply chain management systems, customer relationship management systems, sales and marketing systems, credit management systems and other businesses.

The data warehouse includes the operational data store (ODS) layer, data detail (DWD) layer, subject summary (DWS) layer and data application (ADM) layer from top to bottom. The ODS layer is a temporary area used to store business operational data in the data warehouse. Its table fields are isomorphic with the business side and are the upstream of the data warehouse table dependency hierarchy (i.e., they are all starting tables).

Specifically, a big data synchronization plug-in is pre-set in the data warehouse. This big data synchronization plug-in is used to monitor the data synchronization events of the business data changed in multiple business systems and synchronize them to the data warehouse; when the data warehouse monitors the data synchronization events by using the data synchronization plug-in, it can be considered that the business update instruction is automatically generated and responded to. At this time, the data warehouse can obtain the changed business data (that is, business update data) from the data synchronization event based on the information acquisition method pre-configured in the data synchronization plug-in and synchronize it to each business source table of the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table. The big data synchronization plug-in is a component that extracts business data to the data warehouse, such as Sqoop, SeaTunnel and DataX.

It should be noted that the present invention can parse each association relationship and each dependency relationship through the code of the data warehouse ETL engine (such as Hive SQL or Spark SQL). ETL (Extract-Transform-Load) is a process of extracting, transforming, and loading from the source end to the target end. Its calculation logic is generally defined by Hive SQL or SparkSQL. Except for the ODS layer, the data of other layers in the data warehouse are basically obtained through ETL processing.

For example, to develop the ETL process listener shown in Figure 2, take Hive SQL as the ETL execution engine as an example, first expand:

org.apache.hadoop.hive.ql.hooks.ExecuteWithHookContext interface, listen to data warehouse ETL events in the run method, obtain data synchronization events synchronized to the data warehouse from the HookContext parameter, obtain the Hive SQL execution plan from the db.connect.string parameter, and parse the dependency between the business source table and the data warehouse target table in the HiveSQL script from the Hive SQL execution plan, the association key between the left table and the right table in each business source table (that is, the association relationship between the left table and the right table, such as JOIN ON), and the aggregation key (GROUP BY) of the subquery block; in this way, the collection function of the collection module in Figure 2 is completed, that is, the collection module can not only obtain the metadata such as each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table through the data synchronization hook plug-in, but also obtain the association key, aggregation key and table dependency between all ODS tables reaching the ODS layer through the data ETL process listener.

At this time, the data warehouse saves the association keys of the tables involved in the ETL process and the dependencies between the tables into the metadata database, so that the modeling module in the data warehouse can build an entity relationship diagram, where the table association relationships in the data warehouse are shown in Table 1, and the table dependencies in the data warehouse are shown in Table 2; this completes the modeling function of the modeling module in Figure 2, that is, the modeling module is used to continuously drive the subsequent target table of the business source table to generate modeling information mainly including subject domains and data granularity, as well as ER diagrams, based on the data table dependencies, starting from the ODS table.

Table 1

Table 2

For example, when the ETL computing engine of the data ETL process listener shown in FIG. 2 is other non-Hive SQL components, it can be replaced with a plug-in of the corresponding component. For example, when Spark SQL is used, the spark.sql.queryExecutionListeners plug-in can be developed:

Extend the org.apache.spark.sql.util.QueryExecutionListener interface, listen to data warehouse ETL events in the onSuccess method, obtain the Spark SQL execution plan from the QueryExecution parameter, and parse the source and target table dependencies, the association keys of the left and right tables (such as JOIN ON), and the aggregation keys of the subquery block (such as GROUP BY) in the SQL from the execution plan.

It should be noted that the number of data warehouse target tables is at least one, and the business data in each data warehouse target table is generated after processing and/or cleaning of the business data of at least two interrelated business source tables in multiple business source tables; by analyzing the relationship between all business source tables, at least one association relationship between the business source tables is obtained; by analyzing the relationship between all business source tables and at least one data warehouse target table, at least one dependency relationship between each business source table and the corresponding data warehouse target table is obtained.

Step 120: construct an entity relationship model based on the association relationship and the dependency relationship, and transfer the modeling information of the entity relationship model to the data warehouse layer by layer.

Specifically, the data warehouse can use each business source table and data warehouse target table synchronized to the ODS layer as entity points, each business source table and data warehouse target table's respective different levels of subject and preset key field information as attributes, all the associations between the business source tables and the dependencies between the business source tables and the corresponding data warehouse target tables as relationship edges to build an entity relationship model, that is, to build an entity relationship (Entity-Relationship, ER).

Exemplarily, the subject domains and data granularity of other layers in the data warehouse can be transferred from the predecessor source table to the successor target table according to the dependency relationship. When transferred to the current target table, the association relationships of all corresponding source tables are constructed into relationship edge triples, and the entity relationship model is updated with the constructed relationship edge triples; thereby achieving the purpose of transferring the modeling information of the entity relationship model to the data warehouse layer by layer.

The data processing method of the data warehouse provided by the embodiment of the present invention, when the data warehouse responds to the business update instruction, first obtains each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtains the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table, and then further builds an entity relationship model based on the association relationship and dependency relationship, and transmits the modeling information of the entity relationship model to the data warehouse layer by layer. In this way, when the business data in the business system changes, the business source table is automatically synchronized by the ODS layer in the data warehouse, and the entity relationship model is automatically constructed and the modeling information is transmitted to the lower layer, so as to realize the intuitive display of the model information such as the association relationship and dependency relationship of all tables in the data warehouse, and the entire data maintenance process does not require human participation, ensuring that the modeling information in the data warehouse is improved more efficiently and comprehensively, and it is convenient for users to understand data assets in an all-round way, thereby greatly improving the efficiency of data query and data use.

Based on the data processing method shown in FIG. 1 , in an exemplary embodiment, constructing an entity relationship model based on association relationships and dependency relationships in step 120 can be implemented through the following steps.

First, the data granularity and subject domain of each business source table are obtained; then, based on the association and dependency relationships, as well as the data granularity and subject domain, an entity relationship model is constructed.

Specifically, the data warehouse can obtain the data granularity and subject domain of each business source table while obtaining the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table.

Each subject domain can be a bottom-up summary and abstraction of the business process from the perspective of the business data of the corresponding business source table. The resulting data subject set can usually be used to divide the enterprise's business data into regions based on multiple dimensions such as business category, data source, and data usage, and classify the same type of data separately to obtain a closely related data subject set.

Each data granularity may be a specific degree or scope of a phenomenon or event described by the data in the corresponding business source table, such as a primary key or a unique key of the corresponding business source table.

The data warehouse can use each business source table and data warehouse target table synchronized to the ODS layer as entity points, the data granularity and subject domain of each business source table as attributes, all associations between the business source tables, and the dependency relationships between each business source table and the corresponding data warehouse target table as relationship edges to build an entity relationship model.

It should be noted that the present invention can parse out each association relationship and each dependency relationship through the code or hook plug-in of the data warehouse ETL engine (such as Hive SQL or Spark SQL). ETL (Extract-Transform-Load): the process of extracting, transforming, and loading from the source end to the target end. Its calculation logic is generally defined by HiveSQL or Spark SQL. Except for the ODS layer, the data of other layers in the data warehouse are basically obtained through ETL processing.

Exemplarily, a data synchronization hook plug-in as shown in Figure 2 is developed. Taking the big data synchronization tool Sqoop as an example, the org.apache.sqoop.SqoopJobDataPublisher interface is first extended, and the data synchronization events of business update data synchronization to the data warehouse are listened to in the publish method. The source system business library jdbc connection string is obtained from the db.connect.string parameter, the initial business source table of the synchronized business update data is extracted from the db.query parameter, and the library name and table name of each ODS table in the ODS layer of the data warehouse are obtained from the hive.database.name and hive.table.name parameters. At this time, the data warehouse can query the business system name or database transcoding name to which it belongs based on the source system business library jdbc connection string and the name of each initial business source table, as the first-level subject of the corresponding ODS table in the ODS layer of the data warehouse, and the business source table comments of each initial business source table are used as the second-level subject of the corresponding ODS table, and the primary key or unique key of each initial business source table is used as the data granularity of the corresponding ODS table. Here, all ODS tables in the ODS layer of the data warehouse include business update data synchronized to each business source table of the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table.

It can be understood that the schema of the corresponding business table is queried according to the database connection information of the business source table, including: business primary key and unique key (as data granularity), comments or opinions (that is, the Chinese name of the business table, as the secondary data field of the data), and the business system to which the business source table belongs (the system number is converted into the Chinese name) as the primary data field.

What needs to be explained about the hook plug-in is that the hook plug-in provided by the present invention can automatically integrate the system/library, table/field, primary key/unique key and other information of the business data synchronized to the data warehouse into the corresponding data warehouse table as the original data for data domain division and granularity identification, thereby improving the relevance of the data domain and the business field and the accuracy of data granularity identification.

At this time, the data warehouse can save the subject domain and data granularity of all ODS tables in the ODS layer of the data warehouse into the metadata database to facilitate the modeling module in the data warehouse to pass model information to subsequent tables, where the subject domain and data granularity of the ODS table in the data warehouse can be shown in Table 3.

Table 3

It should be noted that if the data integration tool of the data synchronization hook plug-in shown in Figure 2 is other non-Sqoop components, it can be replaced with the hook plug-in of the corresponding component. For example, when using DataX synchronization service to update data, you can develop the com.alibaba.datax.common.spi.Hook plug-in; when using SeaTunnel to synchronize data, you can develop a Spark listener or a custom plug-in to parse the source and sink configurations.

The data processing method for data warehouse provided by the present invention improves the subject and data granularity information in the data warehouse by automatically dividing the subject domain and automatically identifying the granularity of the data warehouse table. It can not only effectively supplement the data model information, but also play a role in checking the standardization degree of the data warehouse model. It is automatically executed by the program to avoid standard confusion caused by differences in experience of modeling personnel. It can be widely used in fields such as data warehouses and analysis and mining.

Based on the data processing method shown in FIG. 1 , in an exemplary embodiment, the data warehouse constructs an entity relationship model based on association relationships and dependency relationships, as well as various data granularities and various subject domains, which can be specifically implemented through the following steps.

First, determine the first table set, which includes multiple ODS tables determined based on each business source table, each ODS table identifies the corresponding subject domain and data granularity and the corresponding association relationship and/or the corresponding dependency relationship; then, remove the ODS table in the first table set, and determine all the successor target tables with the ODS table as the source table; further determine all the predecessor source tables with the successor target tables in all the successor target tables as the target table, and determine the target entity relationship model based on the query results of the cross-connection relationships between the two predecessor source tables and the entity relationship model; finally, based on The successor target table fills the first table set, then selects the next successor target table from all the successor target tables, and returns to execute the step of determining all the predecessor source tables with the successor target table as the target table; until all the successor target tables are traversed, the corresponding filled first table set is used as the new first table set, and the corresponding target entity relationship model obtained when all the successor target tables are traversed is used as the new entity relationship model, and the above process is repeated; until the first table set is empty, the corresponding target entity relationship model is determined as the constructed entity relationship model.

Among them, all entity points in the entity-relationship model are business source tables and data warehouse target tables. Here, each business source table is used as the source table of the data warehouse, that is, it represents that all business source tables are synchronized to the ODS layer of the data warehouse. At this time, they are all used as ODS tables. Each ODS table can be isomorphic to the business data table in the corresponding business system. The subject domain and data granularity of each ODS table follow the subject domain and data granularity of the business data table in the corresponding business system, and the required data can be extracted through the above Table 3.

It is understandable that since the data warehouse ODS table remains isomorphic to the business data without any conversion, the subject domain and data granularity of the data warehouse ODS table remain consistent with the business source table. At this point, all starting tables of the data warehouse have obtained modeling information such as subject domain and data granularity.

The modeling information of the entity relationship model includes association relationships and dependency relationships, as well as the corresponding data granularity and subject domain identified by each ODS table.

Specifically, referring to the second flowchart of the data processing method shown in FIG. 3 , as shown in FIG. 3 , steps 100 to 107 may be implemented through the following steps.

Step 100, the data warehouse puts all ODS tables in its ODS layer into the first table set, which can be recorded as the TODO set. The TODO set is used to store the table list that has been marked with the subject domain and data granularity, which needs to be transferred to the subsequent target table in the next step, and constructs all source table entity relationships on which its target table depends; go to step 200.

Step 200, determine whether the TODO set is empty; when the TODO set is empty, it means that all ODS tables have been marked with subject domains and data granularity and there are no subsequent target tables to be processed, and the data processing flow ends; when the TODO set is not empty, it means that there are ODS tables that have not been modeled, and go to step 110 (step 110 is not shown in Figure 3).

Step 110 , remove the kth ODS table _Sk from the TODO set, and determine all subsequent target tables with the kth ODS table _Sk as the source table; then go to step 120 (step 120 is not shown in FIG. 3 ).

Step 120 : Determine the target entity relationship model based on the query results of the pairwise cross-connection relationships in all predecessor and successor source tables and the entity relationship model; and go to step 107 .

Step 107, fill the first table set based on the successor target table _Ti , that is, put the successor target table _Ti into the TODO set. So far, the successor target table _Ti corresponding to the source table _Sk has been marked with the subject domain and data granularity, and the association relationship of all the predecessor source tables associated with the successor target table _Ti has been updated to the entity relationship diagram. At this time, the next successor target table is selected from all the successor target tables, and the process returns to step 120 to continue processing other unprocessed successor target tables of the source table _Sk ; until all the successor target tables are processed, the corresponding filled TODO set is used as a new TODO set, and the corresponding target entity relationship model is used as a new entity relationship model when all the successor target tables are processed, and the above process is repeated; until the first table set is empty, the modeling information to be transferred to the next layer is generated.

The data processing method for the data warehouse provided by the present invention automatically identifies the business domain and data granularity of the data warehouse table, so as to facilitate business personnel to quickly locate required data according to the business domain and help developers reduce the workload of exploring data granularity.

Based on the data processing method shown in Figure 1 above, in an exemplary embodiment, the process of the data warehouse determining all predecessor source tables with the successor target table as the target table in all successor target tables includes: when the ODS table in the first table set is moved out to the second table set, if the successor target table does not exist in both the first table set and the second table set, then determining all predecessor source tables with the successor target table as the target table from the entity relationship model.

Exemplarily, the aforementioned step 110 may be specifically implemented through steps 101 to 202 in FIG. 3 .

Step 101, remove the kth ODS table S _k from the TODO set and move it into the second table set, where the value range of k is 1 to G, and G represents the total number of ODS tables in the TODO set; and the second table set here is specifically the DONE set, and the set DONE stores the data tables for which modeling has been completed; moving the kth ODS table S _k from the TODO set to the DONE set indicates that the kth ODS table S _k can be modeled after being processed in subsequent steps; go to step 102.

Step 102, take out all the successor target tables with the kth ODS table _Sk as the source table and put them into the third table set, where the third table set can be set T, and set T stores a list of all target tables with the source table _Sk . As shown in Table 2, set T stores a list of all target_tables with source_table Sk; go to step 201.

Step 201, determine whether the set T is empty; if the set T is empty, it means that the source table _Sk has no successor target table (that is, _Sk is an isolated point, has no successor and is not associated with other tables). Since the subject domain and data granularity have been marked when the source table _Sk is placed in the TODO set, and it has also been placed in the DONE set in step 101, the source table _Sk has been modeled, so the subsequent steps are skipped and the process directly returns to step 200 to continue iterating the next ODS table from the TODO set; otherwise, go to step 103.

Step 103 , when the set T is a non-empty set, remove a successor target table from the set T, where the removed successor target table is recorded as the successor target table _Ti ; go to step 202 .

Step 202, determine whether the successor target table _Ti exists in the TODO set or the DONE set, that is, determine whether the successor target table _Ti corresponding to the source table _Sk has been processed, and when it is determined that the successor target table _Ti does not exist in both the TODO set and the DONE set, go to step 210 (step 210 is not shown in Figure 3); on the contrary, if it is determined that the successor target table _Ti exists in the TODO set or the DONE set, it means that the successor target table _Ti has been marked with the subject domain and data granularity, and the association relationships of all the associated predecessor source tables of the successor target table _Ti have been updated in the entity relationship model. At this time, return to step 201 and continue to process the next successor target table in the set T.

Step 210: Determine all predecessor source tables that take the successor target table _Ti as the target table from the entity relationship model.

Based on the data processing method shown in FIG1 , in an exemplary embodiment, the process of the data warehouse determining all predecessor source tables of the target table with the successor target table as the target table from the entity relationship model (i.e., step 210) includes: when the ODS table is the master table of the successor target table in the entity relationship model, copying the data granularity and subject domain of the ODS table to the successor target table, and determining all predecessor source tables of the target table with the successor target table as the target table based on the copy result; when the ODS table is a non-master table of the successor target table, determining all predecessor source tables of the target table with the successor target table as the target table.

Exemplarily, step 210 may be implemented specifically through step 203 and step 105 in FIG. 3 .

Step 203, determine whether the source table _Sk is the master table of the subsequent target table _Ti ; if the source table _Sk is the master table of the subsequent target table _Ti , go to step 104; otherwise, the subsequent target table _Ti does not inherit the subject domain of the source table _Sk , skip step 104 and go directly to step 105.

Step 104: copy the data granularity and subject domain of the source table _Sk to the subsequent target table _Ti , and if the copy is successful, go to step 105.

Step 105: Take out all predecessor source tables with successor target table _Ti as target table in the entity relationship model and put them into set S. For example, take out all source_tables with target_table as _Ti from Table 2 and put them into set S.

Based on the data processing method shown in FIG. 1 above, in an exemplary embodiment, the data processing method of the data warehouse provided by the present invention may further include:

In the case where the ODS table is the master table of the subsequent target table, the data granularity and subject domain of the ODS table, as well as the business classification information of the ODS table are copied to the subsequent target table.

Specifically, the business classification information of the ODS table is determined using a generative artificial intelligence (AIGC) model, such as by providing the AIGC model with prompts such as role names, comments on the ODS table, a table field list (and its comments), and data samples, to obtain the business domain and subject domain output by the AIGC model; the business domain here may include but is not limited to business classification information, or the business domain is the business classification information.

As for the method of determining the subject domain of the ODS table, text classification can also be performed on the comments of the ODS table and its fields, and the classification result is used as the subject domain of the ODS table.

As for the method of determining different topics contained in the subject domain of the ODS table, the comments of the ODS table and its fields can also be clustered, and from all the clustered tables, the topic of the table with the closest text similarity is used as the topic of the ODS table.

It should be noted that when the ODS table uses its source table (that is, a business data table in the business system), the subject probability can be calculated using formula (1).

(1).

In formula (1), Indicates the total number of fields. Represents an ODS table, Indicates the field index of the ODS table.

In addition, the degree of model standardization in the data warehouse can also be checked based on the modeling information filled in by the present invention: if the data granularity field aggregation is non-unique, it means that there are irregularities such as data divergence or the finest granularity table is not the main table; the smaller the subject difference between the target table and the source table, the more the data table complies with the "high cohesion" specification and the more reasonable the boundary division.

Based on the data processing method shown in FIG. 1 , in an exemplary embodiment, the process of determining the target entity relationship model (i.e., step 120) by the data warehouse based on the query results of the pairwise cross-connection relationships in all predecessor source tables and the entity relationship model includes: when the pairwise cross-connection relationships are found in all predecessor source tables, the entity relationship model is updated based on the pairwise cross-connection relationships to obtain the target entity relationship model; when the pairwise cross-connection relationships are not found in all predecessor source tables, the entity relationship model is determined as the target entity relationship model.

Exemplarily, step 120 may be implemented specifically through step 106 in FIG. 3 .

Step 106, query the pairwise cross-connection relationship constructed by the pairwise cross-connection of the predecessor source tables in the set S. If a pairwise cross-connection relationship exists, the entity relationship diagram is updated based on the queried pairwise cross-connection relationship, and the updated entity relationship diagram is used as the target entity relationship diagram; conversely, if no pairwise cross-connection relationship exists, the entity relationship diagram is not updated, that is, the entity relationship remains unchanged.

For example, for any two different predecessor source tables ^SL and ^SR in set S, if there are records in Table 1 where left_table is ^SL and rigth_table is ^SR , it can be determined that there is an association relationship K between ^SL and ^SR (where the association type is join_type and the key is join_on), and the pairwise cross-connection relationship is recorded as: < ^SL , K, ^SR >, L≠R, and the entity relationship diagram is updated accordingly; otherwise, it means that there is no association between ^SL and ^SR , and the entity relationship diagram is not updated.

At this point, the modeling information to be transferred to the next layer of the data warehouse is completed through Figure 3. According to the data dependency order of the business source table in the data warehouse, the subject domain and data granularity of each business source table can be transferred to its target table one by one. After completion, the target table is used as the new business source table, and the downstream table with dependency relationship is used as the new target table, and the transfer is continued until the modeling information of all data tables in the data warehouse is completed. In this way, the association relationship of all tables in the data warehouse, the field information of the table, the dependency relationship of the table, the business field and data granularity of the table, and other data model information can be intuitively displayed, which is convenient for users to fully understand data assets and improve the efficiency of data search and use.

The data processing device of the data warehouse provided by the present invention is described below. The data processing device of the data warehouse described below and the data processing method of the data warehouse described above can be referenced to each other.

4 , which is a schematic diagram of the structure of a data processing device for a data warehouse provided in an embodiment of the present invention, as shown in FIG4 , the data processing device 400 for a data warehouse includes: a relationship acquisition unit 410 and a data processing unit 420 .

The relationship acquisition unit 410 is used to respond to the business update instruction, obtain the business source tables synchronized to the ODS layer in the data warehouse and the data warehouse target tables corresponding to the business source tables, and obtain the association relationships between the business source tables and the dependency relationships between the business source tables and the data warehouse target tables.

The data processing unit 420 is used to construct an entity relationship model based on the association relationship and the dependency relationship, and transmit the modeling information of the entity relationship model to the data warehouse layer by layer.

Optionally, the data processing unit 420 is specifically used to obtain the data granularity and subject domain of each business source table; and construct an entity relationship model based on the association relationship and the dependency relationship, as well as each data granularity and each subject domain.

Optionally, the data processing unit 420 is specifically used to determine a first table set, the first table set includes multiple ODS tables determined based on each business source table, each ODS table respectively identifies a corresponding subject domain and data granularity and a corresponding association relationship and/or a corresponding dependency relationship; remove the ODS table in the first table set, and determine all subsequent target tables with the ODS table as the source table; determine all predecessor source tables with the successor target table in all the successor target tables as the target table, and determine the target entity relationship model based on the query results of the cross-connection relationships between the two in all the predecessor source tables and the entity relationship model. ; Fill the first table set based on the successor target table, then select the next successor target table from all the successor target tables, and return to execute the step of determining all the predecessor source tables with the successor target table as the target table; until all the successor target tables are traversed, the corresponding filled first table set is used as the new first table set, and the corresponding target entity relationship model obtained when all the successor target tables are traversed is used as the new entity relationship model, and the above process is repeated; until the first table set is empty, the corresponding target entity relationship model is determined as the constructed entity relationship model.

Optionally, the data processing unit 420 is specifically used to determine all predecessor source tables with the successor target table as the target table from the entity relationship model when the ODS table in the first table set is moved out to the second table set if the successor target table does not exist in both the first table set and the second table set.

Optionally, the data processing unit 420 is specifically used to copy the data granularity and subject domain of the ODS table to the successor target table when the ODS table is the primary table of the successor target table in the entity relationship model, and determine all predecessor source tables with the successor target table as the target table based on the copy result; when the ODS table is a non-primary table of the successor target table, determine all predecessor source tables with the successor target table as the target table.

Optionally, the data processing unit 420 is specifically configured to copy the data granularity and subject domain of the ODS table, as well as the business classification information of the ODS table to the subsequent target table when the ODS table is the main table of the subsequent target table.

Optionally, the data processing unit 420 is specifically used to update the entity relationship model based on the pairwise cross-connection relationship to obtain a target entity relationship model when a pairwise cross-connection relationship is found in all predecessor source tables; and to determine the entity relationship model as the target entity relationship model when no pairwise cross-connection relationship is found in all predecessor source tables.

FIG5 illustrates a schematic diagram of the physical structure of an electronic device. As shown in FIG5 , the electronic device may include: a processor 510, a communication interface 520, a memory 530 and a communication bus 540, wherein the processor 510, the communication interface 520 and the memory 530 communicate with each other through the communication bus 540. The processor 510 may call the logic instructions in the memory 530 to execute the following method: in response to the business update instruction, obtain each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtain the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table; construct an entity relationship model based on the association relationship and the dependency relationship, and transfer the modeling information of the entity relationship model to the data warehouse layer by layer.

In addition, the logic instructions in the above-mentioned memory 530 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on such an understanding, the technical solution of the present invention, or the part that contributes to the relevant technology or the part of the technical solution, can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program codes.

On the other hand, an embodiment of the present invention discloses a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the data processing methods of the data warehouse provided by the above-mentioned method embodiments, for example, including: in response to a business update instruction, obtaining each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtaining the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table; constructing an entity relationship model based on the association relationship and the dependency relationship, and delivering the modeling information of the entity relationship model to the data warehouse layer by layer.

On the other hand, an embodiment of the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, it is implemented to execute the data processing method of the data warehouse provided by the above-mentioned embodiments, for example, including: in response to a business update instruction, obtaining each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtaining the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table; constructing an entity relationship model based on the association relationship and the dependency relationship, and delivering the modeling information of the entity relationship model to the data warehouse layer by layer.

On the other hand, an embodiment of the present invention also provides a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the data processing methods of the data warehouse provided by the above-mentioned method embodiments, for example, including: in response to a business update instruction, obtaining each business source table synchronized to the ODS layer in the data warehouse and the data warehouse target table corresponding to each business source table, and obtaining the association relationship between each business source table and the dependency relationship between each business source table and the data warehouse target table; constructing an entity relationship model based on the association relationship and the dependency relationship, and delivering the modeling information of the entity relationship model to the data warehouse layer by layer.

The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.

Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the relevant technology can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM/RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiment.

Finally, it should be noted that the above embodiments are only used to illustrate the present invention, rather than to limit the present invention. Although the present invention is described in detail with reference to the embodiments, it should be understood by those skilled in the art that various combinations, modifications or equivalent substitutions of the technical solutions of the present invention do not depart from the spirit and scope of the technical solutions of the present invention, and should be included in the scope of the present invention.

Claims (11)

1. A data processing method of a data warehouse, comprising:

responding to a service updating instruction, acquiring each service source table synchronized to an ODS layer in a data warehouse and a data warehouse target table corresponding to each service source table, and acquiring the association relation between each service source table and the dependency relation between each service source table and the data warehouse target table;

And constructing an entity relation model based on the association relation and the dependency relation, and transmitting modeling information of the entity relation model to the data warehouse layer by layer.

2. The data processing method of the data warehouse according to claim 1, wherein the constructing an entity relationship model based on the association relationship and the dependency relationship includes:

acquiring respective data granularity and topic domains of the service source tables;

and constructing the entity relation model based on the association relation, the dependency relation, the data granularity and the subject domains.

3. The data processing method of the data warehouse according to claim 2, wherein the constructing the entity relationship model based on the association relationship and the dependency relationship, and each of the data granularity and each of the subject domains includes:

Determining a first table set, wherein the first table set comprises a plurality of ODS tables determined based on the service source tables, and each ODS table respectively identifies a corresponding subject domain and data granularity, and the corresponding association relationship and/or the corresponding dependency relationship;

shifting out the ODS tables in the first table set, and determining all subsequent target tables taking the ODS tables as source tables;

Determining all the successor source tables taking the successor target tables of all the successor target tables as target tables, and determining a target entity relationship model based on query results of the cross connection relationship in all the successor source tables and the entity relationship model;

Filling the first table set based on the subsequent target tables, selecting the next subsequent target table from all the subsequent target tables, and returning to execute the step of determining all the subsequent source tables taking the subsequent target table in all the subsequent target tables as the target table; repeatedly executing the process by taking the corresponding obtained filled first table set as a new first table set when all the subsequent target tables are traversed and taking the corresponding obtained target entity relation model as a new entity relation model when all the subsequent target tables are traversed; and determining the corresponding obtained target entity relation model as the constructed entity relation model until the first table set is empty.

4. A data processing method in a data warehouse according to claim 3, wherein said determining all successor source tables targeting successor target tables in said all successor target tables comprises:

In the case of shifting out the ODS table in the first table set to a second table set, if the successor target table does not exist in both the first table set and the second table set, determining all successor source tables targeting the successor target table from the entity relationship model.

5. The data processing method of the data warehouse as claimed in claim 4, wherein said determining all successor source tables targeting the successor target table from the entity relationship model comprises:

copying the data granularity and the subject field of the ODS table into the subsequent target table under the condition that the ODS table is a main table of the subsequent target table in the entity relation model, and determining all the subsequent source tables taking the subsequent target table as target tables based on a copying result; in the case where the ODS table is a non-master table of the successor target table, all successor source tables targeting the successor target table are determined.

6. The data processing method of a data warehouse of claim 5, further comprising:

In the case where the ODS table is a main table of the subsequent target table, the data granularity and the subject field of the ODS table, and the traffic class information of the ODS table are copied into the subsequent target table.

7. The method for processing data in a data warehouse according to any one of claims 3 to 6, wherein determining the target entity relationship model based on the query results of the cross-connection relationships in all the successor source tables and the entity relationship model includes:

under the condition that the pairwise cross connection relations are queried in all the previous source tables, updating the entity relation model based on the pairwise cross connection relations to obtain the target entity relation model;

and under the condition that the cross connection relations between every two of the all the previous source tables are not queried, determining the entity relation model as the target entity relation model.

8. A data processing apparatus for a data warehouse, comprising:

The relation acquisition unit is used for responding to the business update instruction, acquiring each business source table synchronized to the ODS layer in the data warehouse and a data warehouse target table corresponding to each business source table, and acquiring the association relation between each business source table and the dependency relation between each business source table and the data warehouse target table;

and the data processing unit is used for constructing an entity relation model based on the association relation and the dependency relation and transmitting modeling information of the entity relation model to the data warehouse layer by layer.

9. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements a data processing method of a data warehouse as claimed in any one of claims 1 to 7 when the program is executed by the processor.

10. A non-transitory computer readable storage medium, on which a computer program is stored, characterized in that the computer program, when executed by a processor, implements a data processing method of a data warehouse as claimed in any one of claims 1 to 7.

11. A computer program product comprising a computer program which, when executed by a processor, implements a data processing method of a data warehouse as claimed in any one of claims 1 to 7.