ETL data modeling method, device and equipment and computer program product

Abstract

The invention discloses an ETL data modeling method, device and equipment and a computer program product, and the method comprises the steps: collecting and preprocessing multi-source metadata, and obtaining structured multi-source metadata; determining an ETL data modeling scheme according to the ETL data modeling demand information; according to the structured multi-source metadata, a pre-constructed domain term library and an ETL data modeling scheme, automatic table establishment is carried out, and an ETL data script is generated; and post-processing the ETL data script to obtain a final ETL data script. According to the method, rich and diversified data sources in the financial field are comprehensively integrated, the problems of heterogeneous data source adaptation, semantic understanding and the like are effectively solved by utilizing the pre-constructed field term library, context sensing and other technologies, ETL full-link automatic processing is achieved, the modeling efficiency and accuracy are improved, and the method is suitable for large-scale popularization and application. An efficient, intelligent and reliable ETL modeling solution is provided for data engineering in the financial field, and banks are assisted to better carry out data analysis and business decision making.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data engineering, and in particular to an ETL data modeling method, device and equipment, and a computer program product. BACKGROUND

[0002] In today's data-driven era, the data ecosystem presents an explosive evolution trend, which brings many modern challenges to traditional ETL (Extract-Transform-Load) modeling, thereby triggering a series of ETL modeling problems.

[0003] For example, in the traditional scenario, the data source is mainly a single structured database (RDBMS), but the modern data source has developed into a mixed source, including API, log, NoSQL, and streaming data, etc. This makes the original ETL model unable to automatically adapt across sources, because different structures (structured / semi-structured) of data require customized ETL logic development, greatly increasing the complexity and workload of modeling. Traditional business requirements change on a monthly basis, while modern businesses require daily iterations, such as A / B testing metrics that need to be online in real time. However, manual modeling methods cannot keep up with such a fast business rhythm, resulting in ETL modeling becoming a bottleneck for business development. Traditional scenarios are mostly single-language data, while today we face the situation of coexistence of metadata in multiple languages such as Chinese, English, and Japanese. This causes field naming confusion and makes it difficult to reuse models, because different language metadata has differences in understanding and processing, and it is difficult for humans to manage uniformly. Traditional quality requirements are mostly post-checking, while modern requirements require pre-positioning of compliance due to GDPR / CCPA and other compliance requirements. However, manual safeguards have many loopholes and cannot meet the strict pre-positioning compliance requirements.

[0004] The existing ETL modeling process is usually to manually design a model, then manually write ETL code, then manually test, and finally deploy and run, and problems are only exposed during the running process, forming a vicious cycle. For example, primary key conflicts and other errors are often discovered only in the running stage, and fixing these problems requires starting over from the model layer, which takes a long time on average, seriously affecting the efficiency and stability of data engineering.

[0005] To this end, the existing technology proposes some improvement schemes, such as using templated ETL tools, code generation frameworks, or AI-assisted modeling prototypes, etc. However, these methods still have problems such as high dependence on manual work, inaccurate data understanding and processing, and being detached from actual application scenarios.

[0006] In summary, the existing ETL modeling technology has many shortcomings in dealing with the challenges of modern data ecosystems, and a new solution is urgently needed to solve at least some of the above technical problems and achieve intelligent closed-loop automated data engineering. SUMMARY

[0007] The embodiment of the present application provides an ETL data modeling method, device and equipment, and a computer program product, to realize automatic data modeling and improve modeling efficiency and accuracy.

[0008] The embodiment of the present application adopts the following technical scheme:

[0009] In a first aspect, the embodiment of the present application provides an ETL data modeling method, which comprises:

[0010] Collecting multi-source metadata and preprocessing to obtain structured multi-source metadata;

[0011] Obtaining ETL data modeling requirement information, and determining an ETL data modeling scheme according to the ETL data modeling requirement information;

[0012] According to the structured multi-source metadata, a pre-constructed domain term library and the ETL data modeling scheme, automatically creating a table and generating an ETL data script;

[0013] Post-processing the ETL data script to obtain a final ETL data script.

[0014] Optionally, the collecting multi-source metadata and preprocessing to obtain structured multi-source metadata comprises:

[0015] Collecting multi-source metadata, wherein the multi-source metadata comprises at least one of a structured data source, a semi-structured data source and a stream data source;

[0016] Adaptively matching a corresponding preprocessing rule according to the type of the multi-source metadata;

[0017] Preprocessing the multi-source metadata by using the corresponding preprocessing rule of the multi-source metadata to obtain structured multi-source metadata.

[0018] Optionally, the ETL data modeling requirement information comprises user-defined modeling configuration information, and the obtaining ETL data modeling requirement information and determining an ETL data modeling scheme according to the ETL data modeling requirement information comprises:

[0019] Determining whether the user-defined modeling configuration information can be obtained;

[0020] If yes, determining the ETL data modeling scheme according to the user-defined modeling configuration information;

[0021] If no, determining the ETL data modeling scheme by using a schema adaptive generation engine.

[0022] Optionally, the schema adaptive generation engine comprises a model paradigm dynamic decision strategy and an entity relationship reconstruction strategy, and the determining the ETL data modeling scheme by using the schema adaptive generation engine comprises:

[0023] determining a target data model by using the model paradigm dynamic decision strategy;

[0024] reconstructing an entity relationship by using the entity relationship reconstruction strategy to obtain a reconstructed entity dimension table.

[0025] Optionally, the post-processing the ETL data script to obtain the final ETL data script comprises:

[0026] generating an automated test case;

[0027] testing the ETL data script by using the automated test case.

[0028] Optionally, the post-processing the ETL data script to obtain the final ETL data script comprises:

[0029] checking the ETL data script by using a preset multi-dimension checking mechanism to obtain a multi-dimension checking result, the preset multi-dimension checking mechanism comprising at least one of a performance check, a security check, and a syntax check;

[0030] optimizing the ETL data script according to the multi-dimension checking result to obtain the final ETL data script.

[0031] Optionally, the ETL data modeling method further comprises:

[0032] receiving a calling request of an ETL data script;

[0033] running the final ETL data script according to the calling request of the ETL data script to obtain a running result of the ETL data script;

[0034] monitoring the running result of the ETL data script by using a preset monitoring index to obtain a running monitoring result of the ETL data script;

[0035] iteratively optimizing the final ETL data script according to the running monitoring result of the ETL data script.

[0036] In a second aspect, an ETL data modeling device is also provided, which comprises:

[0037] a collection unit configured to collect and pre-process multi-source metadata to obtain structured multi-source metadata;

[0038] determining unit configured to acquire ETL data modeling requirement information and determine an ETL data modeling scheme according to the ETL data modeling requirement information;

[0039] generating unit configured to automatically generate a table and generate an ETL data script according to the structured multi-source metadata, a pre-built domain term library, and the ETL data modeling scheme;

[0040] post-processing unit configured to post-process the ETL data script to obtain a final ETL data script.

[0041] In a third aspect, an apparatus is also provided, including:

[0042] a processor; and a memory arranged to store computer-executable instructions that, when executed, cause the processor to perform any of the foregoing ETL data modeling methods.

[0043] In a fourth aspect, a computer program product is also provided, including computer programs / instructions that, when executed by a processor, implement any of the foregoing ETL data modeling methods.

[0044] The above at least one technical solution adopted by the embodiments of the present application can achieve the following beneficial effects: The ETL data modeling method of the embodiments of the present application first collects multi-source metadata and pre-processes the same to obtain structured multi-source metadata; then acquires ETL data modeling requirement information and determines an ETL data modeling scheme according to the ETL data modeling requirement information; then automatically generates a table and generates an ETL data script according to the structured multi-source metadata, a pre-built domain term library, and the ETL data modeling scheme; and finally post-processes the ETL data script to obtain a final ETL data script. The ETL data modeling method of the embodiments of the present application can comprehensively integrate rich and diverse data in the financial field by collecting multi-source metadata and structurally processing the same; determines a modeling scheme according to requirements, making the ETL modeling more targeted and flexible; utilizes a pre-built domain term library and context awareness, etc., to realize automatic table generation and ETL script generation, effectively solving problems such as heterogeneous data source adaptation and semantic understanding, and improving modeling efficiency and accuracy; the post-processing process further guarantees the quality and performance of the script, providing an efficient, intelligent, and reliable ETL modeling solution for data engineering in the financial field, and helping banks better perform data analysis and business decision-making. BRIEF DESCRIPTION OF DRAWINGS

[0045] The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the present application and together with the description serve to explain the present application. In the drawings:

[0046] Figure 1 A flowchart of an ETL data modeling method in an embodiment of the present application;

[0047] Figure 2 A flowchart of a multi-source metadata collection and processing method in an embodiment of the present application;

[0048] Figure 3 A flowchart of an ETL data script checking method in an embodiment of the present application;

[0049] Figure 4 A structural diagram of an ETL data modeling device in an embodiment of the present application;

[0050] Figure 5 A structural diagram of an apparatus in an embodiment of the present application. DETAILED DESCRIPTION

[0051] In order to make the objects, technical solutions and advantages of the present application clearer, the technical solutions of the present application will be described below in conjunction with specific embodiments of the present application and corresponding drawings. Obviously, the described embodiments are only some 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 of ordinary skill in the art without creative work fall within the scope of protection of the present application.

[0052] The technical solutions provided by the embodiments of the present application will be described in detail below with reference to the drawings.

[0053] The solutions proposed in the prior art mainly include:

[0054] (1) Template-based ETL tool

[0055] Table 1

[0056]

[0057]

[0058] (2) Code generation framework

[0059] Table 2

[0060] Functionality Technical principles Limitations analysis Modular SQL development dbt Jinja templates Still requires engineers to write base SQL models Lineage tracking SQLMesh DAG Cannot automatically infer undeclared field associations Test syntax dbt test Requires manual writing of assert not null

[0061] (3) AI-assisted modeling prototype (academic research)

[0062] Table 3

[0063] Project Institution Limitations NL2SQL Stanford + Google Only supports simple queries, cannot generate models Semantic type detection (Sherlock) MIT Identifies field types but cannot construct relationships Automatic feature engineering (FeatureTools) FeatureLabs Requires pre-defined entity relationships

[0064] The technical problems that the above existing solutions have not solved mainly include:

[0065] (1) Semantic discontinuity

[0066] The conversion of business language (such as "user retention rate") to physical model (user_retention_rate FLOAT) requires manual translation, and for cross-language mapping such as Chinese commodity number and English item_id, it cannot be automatically associated, which leads to a large number of semantic understanding barriers in the data modeling process.

[0067] (2) Quality discontinuity

[0068] The model is separated from the ETL code, and the engineers may use LEFT JOIN incorrectly in the script, which may cause data inflation, but there is no static checking mechanism to find and avoid such problems in the modeling stage.

[0069] (3) Adaptation discontinuity

[0070] When new Internet of Things device data is added, the parsing logic needs to be rewritten, and the model cannot be dynamically expanded, making it difficult for ETL modeling to adapt to changing data sources.

[0071] (4) Compliance discontinuity

[0072] Manual work may miss sensitive field desensitization, such as writing unmasked ID cards to the data warehouse, which cannot meet the increasingly stringent compliance requirements.

[0073] Based on this, the embodiments of the present application provide an ETL data modeling method, as shown in Figure 1 The flowchart of the ETL data modeling method provided by the embodiments of the present application is shown in the figure, and the ETL data modeling method at least includes the following steps S110 to S140:

[0074] Step S110, collect multi-source metadata and pre-process to obtain structured multi-source metadata.

[0075] In the financial scenario, the data sources are diverse, which can include structured data sources, semi-structured data sources, stream data sources, etc. The structured data sources can include, for example, the traditional relational databases of banks, such as databases storing customer basic information (name, ID number, contact information, etc.), account information (account number, account type, balance, etc.). The semi-structured data sources can be various log files of banks, such as server logs recording the running state of the system, user login logs, etc., which are usually stored in a specific format but have relatively flexible structures. The stream data sources can be, for example, real-time transaction data streams of banks, such as real-time transaction information of customers on ATMs, real-time transfer data of online banks, etc.

[0076] Different collection rules are adopted for different data sources. For structured data sources, data extraction can be directly performed through the interface of the database; for semi-structured data sources such as log files, parsing is performed according to the specific format of the log (such as the field separator of each record line, the timestamp format, etc.); for stream data sources, a stream data processing framework (such as Apache Kafka, etc.) is used for real-time collection. In the parsing process, the data in different formats is converted into structured metadata in a unified format, for example, the customer name in various data sources is uniformly parsed into the "customer_name" field, and the data type, source, etc. are recorded.

[0077] In step S120, ETL data modeling requirement information is obtained, and an ETL data modeling scheme is determined according to the ETL data modeling requirement information.

[0078] The ETL data modeling requirement information can be obtained from multiple aspects. On the one hand, it can be defined according to specific business requirements, for example, in order to perform customer risk assessment, customer credit records, transaction frequency, etc. Data needs to be collected, and corresponding data processing logic is defined. On the other hand, it is automatically generated according to the needs of different business scenarios of banks, such as when a new financial product is opened, the corresponding ETL data modeling requirements are generated according to the product characteristics and related business rules.

[0079] According to the obtained requirement information, an ETL data modeling scheme is determined, which serves as an upper framework for generating ETL logic. For example, if the requirement is to summarize and analyze the transaction data of customers, the modeling scheme can include determining the frequency of data extraction (such as once a day), the rules of data conversion (such as classifying and summarizing the amounts of different transaction types), the target table structure of data loading, etc.

[0080] In step S130, automatic table creation is performed and ETL data scripts are generated according to the structured multi-source metadata, the pre-built domain term library, and the ETL data modeling scheme.

[0081] The pre-training domain-specific BERT model is based on the industry knowledge graph FIBO in the financial field. Through the rich financial terms, concepts and their relationships provided by FIBO, the BERT model is pre-trained to enable it to understand the semantics in the financial field. The trained BERT model is used to extract terms from multi-source metadata to build a domain term library, such as establishing the mapping relationship between English and Chinese terms in the financial field, such as "customer number" corresponding to "castID", etc.

[0082] For input Chinese-English mixed metadata, such as "user table (Chinese) + OrderTable (English)", the mapping rules of the pre-constructed domain term library are processed, and the domain term library is continuously improved through term vector training and artificial feedback mechanism. The same entity represented in different languages can be uniformly mapped to a standard model, such as the output unified model "dim_user".

[0083] In the process of automatic table building and ETL script generation, the context awareness is used to eliminate ambiguity, such as table name, adjacent field, etc. For example, when encountering the "address" field, if the table where the field is located is the user table, it is mapped to "register_address" in combination with the context; if it is in the order table, it may be mapped to "delivery_address".

[0084] In step S140, the ETL data script is post-processed to obtain the final ETL data script.

[0085] The post-processing stage mainly tests and optimizes the generated ETL data script. The test includes functional test, which checks whether the script can accurately extract data from multi-source data, correctly convert data and successfully load into the target table as expected; performance test, which evaluates the execution efficiency of the script when processing large amounts of data, such as data extraction and loading speed, resource occupation, etc. According to the test results, the script is optimized, such as optimizing the query statement of data extraction, adjusting the algorithm of data conversion, etc., to finally obtain the final ETL data script that meets the application requirements of the financial field.

[0086] The ETL data modeling method of the embodiments of the present application can comprehensively integrate the rich and diverse data in the financial field by collecting multi-source metadata and structuring the data; the modeling scheme is determined according to the requirements, making the ETL modeling more targeted and flexible; the pre-constructed domain term library and context awareness technology are used to realize automatic table building and ETL script generation, effectively solving the problems of heterogeneous data source adaptation, semantic understanding, etc., improving the modeling efficiency and accuracy; the post-processing process further guarantees the quality and performance of the script, providing an efficient, intelligent and reliable ETL modeling solution for data engineering in the financial field, helping banks to better analyze data and make business decisions.

[0087] In some embodiments of the present application, the collection of multi-source metadata and preprocessing to obtain structured multi-source metadata comprises: collecting multi-source metadata, the multi-source metadata comprising at least one of a structured data source, a semi-structured data source and a stream data source; adaptively matching a corresponding preprocessing rule according to the type of the multi-source metadata; and preprocessing the multi-source metadata using the corresponding preprocessing rule of the multi-source metadata to obtain structured multi-source metadata.

[0088] In combination Figure 2 , a multi-source metadata collection and processing flowchart is provided in embodiments of the present application. The embodiments of the present application can collect various types of metadata, covering structured data sources, semi-structured data sources and stream data sources, etc. Structured data sources can usually be accessed through JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity), such as common relational databases like MySQL, Oracle, etc. The data in these databases has a strict structure and Schema definition. Semi-structured data sources have data with a certain structure but not completely regular, such as JSON or XML format data files, which will be collected by the system in a targeted manner. Stream data sources may be real-time data streams, such as data transmitted through Kafka message queues, which will be sensed and collected by the system using Kafka topic sniffing, etc.

[0089] According to the specific type of the collected multi-source metadata, a corresponding preprocessing rule is adaptively matched. For structured data sources, since they have a clear Schema, a Schema parser is used to parse their structure information to extract table structure, field type and other metadata. For semi-structured data sources such as JSON / XML format data, a JSON / XML parsing method is used to construct a path tree structure of the data through a path tree generator to better understand and process the hierarchical structure of semi-structured data. For stream data sources, sample data sampling is performed to obtain the characteristics and patterns of the data.

[0090] The multi-source metadata is processed using the matched preprocessing rule. After Schema parsing, the metadata information of the structured data is extracted and arranged; semi-structured data is generated by a path tree generator to generate a unified structure representation; after sample data sampling, stream data is also converted into a metadata form with a certain structure. Finally, these metadata processed by different preprocessing rules are all converted into structured metadata in a unified format, providing a standardized data basis for subsequent ETL data modeling.

[0091] By adaptively matching different types of multi-source metadata preprocessing rules, metadata from structured, semi-structured and streaming data sources can be efficiently and comprehensively collected and processed, and converted into a structured form. This process effectively solves the differences in format and structure of multi-source heterogeneous data, provides a unified and standardized data input for subsequent ETL data modeling, improves the accuracy and efficiency of data processing, and helps to build a more complete and accurate data model, providing a reliable data foundation for data analysis and decision support.

[0092] In some embodiments of the present application, the ETL data modeling requirement information includes user-defined modeling configuration information, and the obtaining ETL data modeling requirement information and determining an ETL data modeling scheme according to the ETL data modeling requirement information includes: determining whether user-defined modeling configuration information can be obtained; if so, determining the ETL data modeling scheme according to the user-defined modeling configuration information; and if not, determining the ETL data modeling scheme by using a schema adaptive generation engine.

[0093] In determining the ETL data modeling scheme, it is first determined whether user-defined modeling configuration information can be obtained. User-defined modeling configuration information has great flexibility. For example, a user can configure modeling requirement information according to his own understanding of the data model and business requirements. For example, the user can choose a star model or a wide table model as the model of the generated data table. The star model is usually composed of one fact table and multiple dimension tables, and is suitable for scenarios where data analysis and query are frequent. The wide table model is to concentrate multiple related fields into one table, which can simplify the query logic in some cases.

[0094] If user-defined modeling configuration information can be obtained, the ETL data modeling scheme will be determined directly according to these information. This means that the modeling scheme will fully follow the user's individual needs and follow the user-specified model type and other configuration requirements for subsequent ETL process design.

[0095] If user-defined modeling configuration information cannot be obtained, a schema adaptive generation engine will be used to determine the ETL data modeling scheme. The schema adaptive generation engine can dynamically decide to use a suitable table model according to the characteristics of multi-source metadata, potential requirements of business scenarios and other factors. For example, according to the degree of data association, the complexity of query and other factors, a star model, a snowflake model or other suitable model structure is automatically selected to ensure that the generated modeling scheme can adapt to the actual situation of data and business.

[0096] In an embodiment of the present application, the user is allowed to customize the modeling configuration information, thereby meeting the personalized business requirements of the user and enabling the user to design a data model according to the understanding of the user for the data and the specific requirements of the user. On the other hand, when the user does not provide the customized configuration, the schema adaptive generation engine can dynamically generate a suitable modeling scheme according to the actual situation of the data and the business, thereby improving the automation and intelligent level of the system. This flexible mode ensures that the ETL data modeling scheme can not only meet the user requirements but also adapt to the complex and changeable data and business environment, thereby laying a good foundation for subsequent automatic table creation and ETL data script generation and improving the efficiency and accuracy of the entire data engineering process.

[0097] In some embodiments of the present application, the schema adaptive generation engine includes a model paradigm dynamic decision strategy and an entity relationship reconstruction strategy, and the determination of the ETL data modeling scheme by the schema adaptive generation engine includes: determining a target data model by using the model paradigm dynamic decision strategy; and reconstructing an entity relationship by using the entity relationship reconstruction strategy to obtain a reconstructed entity dimension table.

[0098] In an embodiment of the present application, the schema adaptive generation engine mainly uses a model paradigm dynamic decision strategy and an entity relationship reconstruction algorithm when determining the ETL data modeling scheme.

[0099] (1) Model paradigm dynamic decision strategy

[0100] The strategy determines the target data model by using a decision matrix. The decision matrix comprehensively considers multiple example indicators, such as the number of associated tables, query delay requirements, and update frequency. For example, when it is detected that the number of associated tables is greater than 3, the query delay requirement is greater than or equal to 100 ms, and the update frequency is a high-frequency update at the minute level, it is automatically determined to select a wide table model. In actual application, each indicator under the current data and business scenario is evaluated, and the most suitable target data model is selected from different model paradigms such as a star model and a wide table model according to a preset decision rule. For example, in an e-commerce business scenario, if there are multiple related business tables, there is a certain requirement for query speed, and the data is updated frequently, a wide table model can be automatically generated as the target data model according to the decision matrix.

[0101] (2) Entity relationship reconstruction strategy

[0102] This strategy aims to reconstruct the entity relationship, and the core idea is to reduce the association between data by separately constructing the table of entity dimension. For example, customer information can be extracted separately to construct a customer information table, and only the customer number is stored in other related business tables, and the customer number is associated with the customer information table. For example, the order time, payment time and delivery time are combined into a time dimension table, so that in the subsequent data query and processing process, the JOIN operation can be greatly reduced, and the data processing efficiency can be improved. The entity relationship reconstruction algorithm can be used to reconstruct the entity relationship according to the internal logic of the data and the business requirements, and a more reasonable and efficient entity dimension table structure is formed.

[0103] The mode adaptive generation engine of the embodiments of the present application dynamically determines the strategy and entity relationship reconstruction strategy through the model paradigm, and can intelligently determine the appropriate target data model according to different data characteristics and business requirements, and optimize and reconstruct the entity relationship. This not only improves the automation degree of data modeling and reduces manual intervention, but also generates a data model that is more in line with the actual business scenario and data characteristics, effectively improves the efficiency and performance of data processing, provides a high-quality modeling basis for subsequent automatic table building and ETL data script generation, and helps to realize a more efficient and accurate data engineering process.

[0104] In some embodiments of the present application, the automatic table building and ETL data script generation according to the structured multi-source metadata and pre-built domain term library and the ETL data modeling scheme comprises: automatically building a table and generating an initial ETL data script according to the structured multi-source metadata and pre-built domain term library and the ETL data modeling scheme; and optimizing the initial ETL data script by using an ETL data script optimization strategy to obtain an optimized ETL data script.

[0105] Based on the structured multi-source metadata and the domain term library, the table structure definition is automatically completed. For example, input the Chinese table name "customer table" and the field "age", and map the standard field name "age" through the term library, and automatically add the value domain constraint (such as CHECK (age BETWEEN 0 AND 120)) and the partitioning strategy (such as PARTITION BY RANGE (age)). The table name is unified to dim_customer through the Chinese-English mixed processing chain, the field annotation is generated synchronously, and the physical model is consistent with the business semantics.

[0106] In combination with the ETL modeling scheme, a full-process script including data extraction, conversion, and loading is automatically generated. For example, when extracting data from the "User Table (Chinese)" and "OrderTable (English)", the ambiguity is eliminated through context awareness (table name + adjacent field), the "address" field is mapped to register_address, and JOIN logic is automatically generated to associate customer numbers with dimension tables, while embedding desensitization rules (such as automatic desensitization of mobile phone fields).

[0107] For the generated initial ETL script, further optimization strategies are adopted for optimization. For example, through static analysis to detect the field null rate, if it is found that order_amount has 30% null values, COALESCE(order_amount, 0) logic is automatically inserted to avoid subsequent calculation errors. For example, for high-cardinality fields (such as "province"), partition optimization is automatically triggered to generate the DISTRIBUTE BY province statement to realize province-based partitioning, reduce data skew, and improve query efficiency.

[0108] Dynamic DDL generation example:

[0109] / * Input: Chinese table name 'Customer Table', field 'age' * /

[0110] CREATE TABLE dim_customer(

[0111] customer_id BIGINT PRIMARY KEY COMMENT 'Customer ID',

[0112] age SMALLINT COMMENT 'Age' CHECK(age BETWEEN 0 AND 120) -- Automatically add value domain constraints

[0113] ) PARTITION BY RANGE(age); -- Automatically partition based on data distribution

[0114] Through automatic table creation and ETL script generation, combined with dynamic optimization strategies, end-to-end intelligent processing from multi-source metadata to target models is realized. On the one hand, the domain terminology library and context awareness technology ensure semantic consistency and eliminate ambiguity in Chinese-English mixed field mapping. On the other hand, optimization strategies automatically repair common problems such as null values, high cardinality, and JOIN expansion, improving script robustness and execution efficiency. Ultimately, a reusable and adaptive ETL modeling system is formed, significantly reducing the cost of manual intervention and shortening the business response cycle, providing high-quality, efficient, and intelligent automated solutions for data engineering.

[0115] In some embodiments of the present application, the post-processing of the ETL data script to obtain a final ETL data script includes generating an automated test case; and testing the ETL data script using the automated test case.

[0116] In embodiments of the present application, the post-processing of the ETL data script can include generating an automated test case and testing the ETL data script using the automated test case. The generation of the automated test case is based on explicit rule derivation logic, and corresponding test rules and corresponding test codes can be automatically generated according to different characteristics of the fields. As shown in Table 4 below, the rule derivation logic for centralized automated generation of test cases is listed:

[0117] Table 4

[0118]

[0119] For fields with the field name containing "amount", a test rule with non-negative values will be automatically generated. In the test code, assert df['amount'].min() >= 0 is used to verify whether all values of the field are greater than or equal to 0. This ensures the reasonableness of the amount-related data in the business logic, avoiding the occurrence of negative amounts and other data that do not conform to the actual situation.

[0120] When the data type of a field is date, a test rule conforming to the ISO 8601 standard will be generated. The test code assert df['date'].str.match(r'\d{4}-\d{2}-\d{2}').all() will check whether all values of the date field conform to the specified date format, ensuring the consistency and standardization of the date data.

[0121] For primary key fields, a test rule without duplication is generated. Using the test code assert df['id'].nunique() == len(df), it can be verified whether the values of the primary key field are unique, preventing the occurrence of primary key conflicts and ensuring the uniqueness and integrity of the data.

[0122] After generating the automated test case, the ETL data script is comprehensively tested using these test cases. By executing the test code, it can automatically check whether the ETL data script meets various rule requirements during data processing, and timely discover potential problems such as data conversion errors, data format problems, and primary key conflicts in the script.

[0123] By generating automated test cases and testing ETL data scripts, the quality control of the ETL process is realized. The rule derivation logic automatically generates targeted test rules according to field characteristics, which can comprehensively cover key checkpoints such as data legality, format specification, and uniqueness, effectively avoiding the omissions and subjectivity of manual testing. This not only improves testing efficiency and accuracy, but also discovers and solves problems in the early stages of data engineering, ensuring that the final generated ETL data script can run stably and reliably, providing high-quality data support for subsequent data analysis and decision-making, and overall improving the reliability and automation level of data engineering.

[0124] In some embodiments of the present application, the post-processing of the ETL data script to obtain the final ETL data script includes: checking the ETL data script using a preset multi-dimensional checking mechanism to obtain a multi-dimensional checking result, the preset multi-dimensional checking mechanism including at least one of performance checking, security checking, and syntax checking; and optimizing the ETL data script according to the multi-dimensional checking result to obtain the final ETL data script.

[0125] In combination Figure 3 , a schematic diagram of an ETL data script checking process is provided in an embodiment of the present application. The multi-dimensional checking mechanism preset in the embodiment of the present application covers performance checking, security checking, and syntax checking, etc.

[0126] (1) Performance checking: detecting the performance of the ETL data script. For example, detecting full table JOIN operations, because full table JOIN can cause performance degradation when the data volume is large. Once full table JOIN is detected, it is rewritten as a partition query, which reduces the amount of data scanning by only operating on related partition data, thereby improving query performance.

[0127] (2) Security checking: responsible for discovering security problems in the ETL data script. For example, checking whether there are sensitive fields that have not been desensitized. If an unsensitized field is found, a MASK function is injected to mask sensitive information, ensuring the security of data during processing and preventing sensitive data leakage.

[0128] (3) Syntax checking: mainly identifies syntax problems in the ETL data script, especially dialect differences. Different databases may have slight differences in syntax. By identifying these differences and replacing equivalent syntax, the script can be correctly executed in the target database, ensuring the universality and compatibility of the script.

[0129] According to the multi-dimensional checking result, the ETL data script is optimized, and finally an ETL data script that meets all requirements is obtained.

[0130] The preset multi-dimensional checking mechanism is used for comprehensive checking and optimization of the ETL data script, so as to effectively improve the quality and reliability of the script. The performance checking optimizes the execution efficiency of the script, reduces the data processing time, the security checking guarantees the safety of the data in the processing process, and avoids the risk of sensitive information leakage, and the syntax checking ensures the correct execution of the script in different database environments. Overall, the technical scheme realizes the fine management and optimization of the ETL data script, improves the stability and efficiency of the data engineering, and provides strong support for efficient and safe processing of data.

[0131] In some embodiments of the present application, the ETL data modeling method further comprises: receiving a calling request of an ETL data script; running the final ETL data script according to the calling request of the ETL data script to obtain a running result of the ETL data script; monitoring the running result of the ETL data script by using preset monitoring indicators to obtain a running monitoring result of the ETL data script; and iteratively optimizing the final ETL data script according to the running monitoring result of the ETL data script.

[0132] In the embodiments of the present application, the ETL data modeling method further comprises a continuous optimization feedback loop to realize runtime-driven model iteration. First, a calling request of an ETL data script is received, the final optimized ETL data script is run according to the request, and a corresponding running result is obtained. Then, the running result is monitored by using preset monitoring indicators. These monitoring indicators and corresponding thresholds and optimization actions may, for example, include:

[0133] (1) Null rate: when the null rate is greater than the null rate threshold, such as 30%, a default value filling logic is automatically added. For example, when processing order data, if the null rate of the “coupon amount” field is too high, a default value of 0 is automatically filled to ensure data integrity and accuracy of subsequent calculations.

[0134] (2) Data skew: if the data skew is greater than the skew threshold, such as 0.8, a pre-aggregation table is generated. For example, when performing aggregation analysis on user behavior data, the data volume of some popular commodities is too large, causing data skew. By generating a pre-aggregation table, data distribution can be balanced and processing efficiency can be improved.

[0135] (3) Query delay P90: when the query delay P90 is greater than the delay threshold, such as 5s, a composite index is added. For example, in a scenario where users frequently query customer order information, adding a composite index can speed up the query and improve user experience.

[0136] In addition, an incremental update mechanism is also provided. When partial changes occur in the data, such as the addition of a new field "coupon_code", only the changed part needs to be recompiled, without the need for full data refresh, greatly improving processing efficiency.

[0137] According to the monitoring results of the monitoring indicators of each dimension, the final ETL data script is iteratively optimized to continuously improve the script performance and data processing capability.

[0138] Through the construction of a continuous optimization feedback loop, dynamic optimization of the ETL data script is achieved. Real-time monitoring of the running results is performed using preset monitoring indicators, and targeted optimization actions are taken according to the monitoring results, which can effectively solve problems such as high null rate, data skew, query delay, etc. that occur in the data processing process, and improve the accuracy, efficiency and stability of data processing. At the same time, the incremental update mechanism avoids the resource waste and time consumption caused by full data refresh, significantly improves the data processing efficiency, and makes the ETL data modeling method better adapt to the changing business requirements and data environment, providing a more intelligent and efficient solution for data engineering.

[0139] In summary, the ETL data modeling method of the embodiments of the present application at least achieves the following technical effects:

[0140] (1) In terms of semantic understanding, it breaks through the limitations of traditional simple regular matching, uses context-aware vector disambiguation technology to accurately distinguish the meanings of the same terms in different scenarios, greatly improving the depth and accuracy of semantic understanding.

[0141] (2) The implementation of the engineering closed loop covers the "modeling-development-testing-deployment-monitoring" full-link automated process, effectively eliminating manual intervention, greatly improving data processing efficiency, and reducing the risk of human error.

[0142] (3) Dynamic adaptability enables the model to automatically evolve according to changes in data distribution, such as seamlessly switching from a star model to a wide table, better adapting to complex and changing data environments.

[0143] (4) The security design is originally designed through sensitive field DNA identification technology, combined with naming and content features, to intercept 98% of compliance risks in advance, providing reliable security protection for data processing.

[0144] In summary, the present application performs outstandingly in improving data processing efficiency, accuracy, adaptability and security, and provides a comprehensive, intelligent and efficient solution for data engineering.

[0145] The embodiments of the present application also provide an ETL data modeling device 400, as shown in Figure 4As shown, a structural schematic diagram of an ETL data modeling device in an embodiment of the present application is provided, and the ETL data modeling device 400 includes a collection unit 410, a determination unit 420, a generation unit 430, and a post-processing unit 440, wherein:

[0146] The collection unit 410 is configured to collect and pre-process multi-source metadata to obtain structured multi-source metadata.

[0147] The determination unit 420 is configured to obtain ETL data modeling requirement information, and determine an ETL data modeling scheme according to the ETL data modeling requirement information.

[0148] The generation unit 430 is configured to automatically build a table and generate an ETL data script according to the structured multi-source metadata, a pre-constructed domain term library, and the ETL data modeling scheme.

[0149] The post-processing unit 440 is configured to post-process the ETL data script to obtain a final ETL data script.

[0150] In some embodiments of the present application, the collection unit 410 is specifically configured to collect multi-source metadata, the multi-source metadata including at least one of a structured data source, a semi-structured data source, and a stream data source; adaptively match a corresponding pre-processing rule according to a type of the multi-source metadata; and pre-process the multi-source metadata by using the corresponding pre-processing rule of the multi-source metadata to obtain structured multi-source metadata.

[0151] In some embodiments of the present application, the ETL data modeling requirement information includes user-defined modeling configuration information, and the determination unit 420 is specifically configured to determine whether the user-defined modeling configuration information can be obtained; if yes, determine the ETL data modeling scheme according to the user-defined modeling configuration information; and if not, determine the ETL data modeling scheme by using a schema adaptive generation engine.

[0152] In some embodiments of the present application, the schema adaptive generation engine includes a model paradigm dynamic decision strategy and an entity relationship reconstruction strategy, and the determination unit 420 is specifically configured to determine a target data model by using the model paradigm dynamic decision strategy; and reconstruct an entity relationship by using the entity relationship reconstruction strategy to obtain a reconstructed entity dimension table.

[0153] In some embodiments of the present application, the post-processing unit 440 is specifically configured to generate an automatic test case; and test the ETL data script by using the automatic test case.

[0154] In some embodiments of the present application, the post-processing unit 440 is specifically configured to: check the ETL data script by using a preset multi-dimension checking mechanism to obtain a multi-dimension checking result, the preset multi-dimension checking mechanism including at least one of performance checking, security checking, and syntax checking; and optimize the ETL data script according to the multi-dimension checking result to obtain the final ETL data script.

[0155] In some embodiments of the present application, the ETL data modeling apparatus 400 further includes: a receiving unit configured to receive a calling request of an ETL data script; a running unit configured to run the final ETL data script according to the calling request of the ETL data script to obtain a running result of the ETL data script; a monitoring unit configured to monitor the running result of the ETL data script by using a preset monitoring index to obtain a running monitoring result of the ETL data script; and an optimization unit configured to iteratively optimize the final ETL data script according to the running monitoring result of the ETL data script.

[0156] It can be understood that the above-mentioned ETL data modeling apparatus can realize each step of the ETL data modeling method provided in the foregoing embodiments, and the related explanations about the ETL data modeling method are all applicable to the ETL data modeling apparatus, which will not be repeated here.

[0157] Figure 5 is a structural schematic diagram of an apparatus in an embodiment of the present application. As shown in Figure 5 The apparatus includes one or more processors (or processing units), can further include one or more memories coupled to the processors, and can further include a communication module coupled to the processors.

[0158] The communication module can be used for communication with other apparatuses or devices, such as transmission or reception of data and / or signals. The communication module can have at least one communication module for communication. The communication module can include any interface necessary for communication with other apparatuses. Illustratively, the communication module can be a transceiver, a circuit, a bus, a module, or other types of communication modules.

[0159] The processor can include, but is not limited to, at least one of the following: a general-purpose computer, a special-purpose computer, a microcontroller, a digital signal controller (Digital Signal Processor, DSP), or one or more of a controller-based multi-core controller architecture. The apparatus can have multiple processors, such as application-specific integrated circuit chips, which are time-dependent on a clock synchronized with the main processor.

[0160] The memory can include one or more non-volatile memories and one or more volatile memories. Examples of non-volatile memories include, but are not limited to, at least one of the following: Read-Only-Memory (ROM), Electrically Programmable Read-Only-Memory (EPROM), flash memory, hard disk, Compact Disc (CD), Digital Video Disk (DVD), or other magnetic and / or optical storage. Examples of volatile memories include, but are not limited to, at least one of the following: Random Access Memory (RAM), or other volatile memory that does not persist in the event of a power duration.

[0161] The computer program includes computer-executable instructions executed by an associated processor. The program can be stored in the ROM. The processor can perform any suitable action and processing by loading the program into the RAM.

[0162] Possible implementations of the present application can be realized by means of a program and therefore the communication device can perform any process as discussed in the preceding embodiments. Possible implementations of the present application can also be realized in hardware or in a combination of software and hardware.

[0163] In some embodiments, the program can be tangibly embodied in a computer-readable storage medium, which can be included in the device, such as in the memory, or other storage device accessible by the device. The program can be loaded from the computer-readable storage medium into the RAM for execution. The computer-readable storage medium can include any type of tangible non-volatile memory, for example, ROM, EPROM, flash memory, hard disk, CD, DVD, and the like.

[0164] The embodiments of the present application further provide a computer readable storage medium having computer instructions or program codes stored thereon, which, when executed by a processor, cause the processor to perform the methods and functions involved in any of the above embodiments. The computer readable medium can be any tangible medium containing or storing a program for or about an instruction execution system, apparatus or device. The computer readable medium can be a computer readable signal medium or a computer readable storage medium. The computer readable medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared or semiconductor system, apparatus or device, or any suitable combination thereof. The computer readable storage medium can be any available medium accessible by a computer or data storage device such as a server, data center, etc. integrated with one or more available media. More detailed examples of the computer readable storage medium include an electrical connection with one or more wires, a magnetic medium (e.g., a disk, a floppy disk, a hard disk, a magnetic tape, a magnetic storage device), an optical medium (e.g., an optical storage device, a DVD), a semiconductor medium (e.g., a solid-state hard disk), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), or any suitable combination thereof, etc.

[0165] In the above embodiments, all or part of the embodiments can be implemented by software, hardware, firmware or any combination thereof. When implemented by software, all or part of the embodiments can be implemented in the form of a computer program product. The embodiments of the present application also provide at least one computer program product tangibly stored on a non-transitory computer readable storage medium. The computer program product includes one or more computer executable instructions, such as instructions included in program modules, which are executed in a device on a real or virtual processor of a target to perform the processes, methods and functions involved in any of the above embodiments. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions according to the embodiments of the present application are generated. The computer can be a general purpose computer, a special purpose computer, a computer network or other programmable device. The computer instructions can be stored in a computer readable storage medium or transferred from one computer readable storage medium to another, for example, the computer instructions can be transferred from one website, computer, server or data center to another by wire (such as coaxial cable, optical fiber, digital subscriber line) or wireless (such as infrared, wireless, microwave, etc.).

[0166] The embodiments of the present application further provide a computer program product, comprising computer programs or instructions, which, when executed on a computer, cause the computer to perform the processes, methods and functions in the above embodiments. Generally, program modules include routines, programs, libraries, objects, classes, components, data structures, etc. that perform particular tasks or implement particular abstract data types. In various embodiments, the functions of program modules can be combined or divided among program modules as desired. Machine-executable instructions for program modules can be executed within a local or distributed device. In a distributed device, program modules can be located in local and remote storage media.

[0167] Generally, the various embodiments of the present application can be implemented in hardware or special-purpose circuits, software, logic or any combination thereof. Some aspects can be implemented in hardware, while other aspects can be implemented in firmware or software which can be executed by a controller, microprocessor or other computing device. While various aspects of the embodiments of the present disclosure are illustrated and described as block diagrams, flow charts, or using some other pictorial representation, it is well understood that these blocks, apparatus, systems, techniques or methods described herein can be implemented in, as non-limiting examples, hardware, software, firmware, special-purpose circuits or logic, general purpose hardware or controller or other computing devices, or some combination thereof.

[0168] It should be noted that although the embodiments of the present application are described above respectively in connection with the drawings, the above embodiments are not independent of each other, and they can be combined to obtain other embodiments. The manners, cases, categories and division of embodiments in the embodiments of the present application are only for the convenience of description, and should not constitute special limitation. The features in various manners, categories, cases and embodiments can be combined with each other as long as they are logically consistent. The various embodiments of the present application can be combined arbitrarily to achieve different technical effects. The embodiments of the present application do not list various combinations again.

[0169] In addition, although the operations of the methods of the present disclosure are described in a particular order in the drawings, this does not require or imply that the operations must be performed in that particular order, or that all of the illustrated operations must be performed to achieve the desired results. On the contrary, the steps depicted in the flowcharts can change the order of execution. Additionally or alternatively, some steps can be omitted, combined into one step, and / or decomposed into multiple steps. It should also be noted that the features and functions of two or more devices according to the present disclosure can be embodied in one device. Conversely, the features and functions of one device described above can be further divided into multiple devices.

[0170] It is also to be noted that the terms "comprising", "including", and any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but can include other elements not expressly listed or inherent to such process, method, article, or apparatus. An element proceeded by "comprises a... " does not, without more constraints, exclude the existence of additional identical elements in the process, method, article, or apparatus that comprises the element.

[0171] The above description is merely illustrative of the application, and not restrictive. Various modifications and changes can become apparent to those skilled in the art. Incorporating any modification, equivalent substitution, improvement, etc. within the spirit and principle of the application, shall be included in the scope of the claims of the application.

Claims

1. An ETL data modeling method, characterized in that, The ETL data modeling method comprises: collecting and preprocessing multi-source metadata to obtain structured multi-source metadata; obtaining ETL data modeling requirement information, and determining an ETL data modeling scheme according to the ETL data modeling requirement information; automatically building a table and generating an ETL data script according to the structured multi-source metadata, a pre-constructed domain term library and the ETL data modeling scheme; post-processing the ETL data script to obtain a final ETL data script.

2. The ETL data modeling method of claim 1, wherein, The collecting and preprocessing multi-source metadata to obtain structured multi-source metadata comprises: collecting multi-source metadata, the multi-source metadata comprising at least one of a structured data source, a semi-structured data source and a stream data source; adaptively matching a corresponding preprocessing rule according to the type of the multi-source metadata; preprocessing the multi-source metadata by using the corresponding preprocessing rule of the multi-source metadata to obtain structured multi-source metadata.

3. The ETL data modeling method of claim 1, wherein, The ETL data modeling requirement information comprises user-defined modeling configuration information, and the obtaining ETL data modeling requirement information, and determining an ETL data modeling scheme according to the ETL data modeling requirement information comprises: determining whether the user-defined modeling configuration information can be obtained; if yes, determining the ETL data modeling scheme according to the user-defined modeling configuration information; if no, determining the ETL data modeling scheme by using a schema adaptive generation engine.

4. The ETL data modeling method of claim 3, wherein, The schema adaptive generation engine comprises a model paradigm dynamic decision strategy and an entity relationship reconstruction strategy, and the determining the ETL data modeling scheme by using the schema adaptive generation engine comprises: determining a target data model by using the model paradigm dynamic decision strategy; reconstructing an entity relationship by using the entity relationship reconstruction strategy to obtain a reconstructed entity dimension table.

5. The ETL data modeling method of claim 1, wherein, The post-processing the ETL data script to obtain a final ETL data script comprises: generating an automated test case; testing the ETL data script by using the automated test case.

6. The ETL data modeling method of claim 1, wherein, The post-processing the ETL data script to obtain a final ETL data script comprises: checking the ETL data script by using a preset multi-dimensional checking mechanism to obtain a multi-dimensional checking result, the preset multi-dimensional checking mechanism comprising at least one of a performance check, a security check and a syntax check; optimizing the ETL data script according to the multi-dimensional checking result to obtain the final ETL data script.

7. The ETL data modeling method of any one of claims 1-6, wherein, The ETL data modeling method further comprises: receiving a calling request of an ETL data script; running the final ETL data script according to the calling request of the ETL data script to obtain a running result of the ETL data script; monitoring the running result of the ETL data script by using a preset monitoring index to obtain a running monitoring result of the ETL data script; iteratively optimizing the final ETL data script according to the running monitoring result of the ETL data script.

8. An ETL data modeling apparatus, characterized by, The ETL data modeling device comprises: The collection unit is used for collecting multi-source metadata and preprocessing to obtain structured multi-source metadata; The determination unit is used for obtaining ETL data modeling requirement information and determining an ETL data modeling scheme according to the ETL data modeling requirement information; The generation unit is used for automatically building a table and generating an ETL data script according to the structured multi-source metadata, a pre-constructed domain term library and the ETL data modeling scheme; The post-processing unit is used for post-processing the ETL data script to obtain a final ETL data script.

9. An apparatus comprising: a processor; and a memory arranged to store computer executable instructions that, when executed, cause the processor to perform the ETL data modeling method of any one of claims 1-7.

10. A computer program product comprising computer programs / instructions, characterized in that, The computer program / instructions, when executed by the processor, implement the ETL data modeling method of any one of claims 1-7.

Images