ETL scheduling method based on conch data platform, computer and storage medium

Abstract

The invention provides an ETL scheduling method based on a conch data platform, a computer and a storage medium, and belongs to the technical field of data scheduling, specifically, the conch data platform establishes data connection with a source system database, selects a data integration mode according to the actual situation of a source end table of the database, and extracts a source system database table; designing a data architecture based on the conch data platform; carrying out ETL script development and ETL job scheduling based on a conch data platform; and the conch data platform carries out logic packaging on the data through an API interface form, and provides an access address for a user. Under the condition that the data volume of the source end is large, the problem of data missing integrated to the destination end is reduced, and the integration efficiency is improved.

Description

Technical Field

[0001] This invention belongs to the field of data scheduling technology. Specifically, this invention relates to an ETL scheduling method, computer, and storage medium based on the Conch Data Platform. Background Technology

[0002] As enterprises face increasingly stringent requirements for production, operation, and management, the need for cross-business and cross-regional analysis is constantly growing. The need for unified data standards across various intelligent systems, enabling business-related analysis and production optimization is becoming increasingly urgent. To address issues such as inconsistent data standards, breaking down horizontal data barriers, and improving data quality, the Group, using the Conch Data Platform as its foundation, is comprehensively enhancing its capabilities in data integration, information sharing, and data standardization through data governance.

[0003] Traditional data governance methods mainly involve modules such as data integration, data architecture, data development, data quality, data security, and data services. ETL scheduling, however, is a core module in the data governance process, involving the extraction, transformation, and loading of data from business systems.

[0004] In traditional ETL scheduling processes, the data integration method is relatively simple. When faced with a large amount of data from the source end, it often leads to problems such as missing data and low integration efficiency at the destination end. Summary of the Invention

[0005] The present invention aims to provide an ETL scheduling method, computer, and storage medium based on the Conch Data Platform, so as to reduce the problem of missing data when integrating to the destination and improve integration efficiency when the amount of data at the source is large.

[0006] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0007] This invention provides an ETL scheduling method based on the Conch Data Platform:

[0008] Step 1: The Conch Data Platform establishes a data connection with the source system database, selects the data integration method based on the actual situation of the source database tables, and extracts the source system database tables;

[0009] Step 2: Design the data architecture based on the Conch Data Platform;

[0010] Step 3: Develop ETL scripts and schedule ETL jobs based on the Conch Data Platform;

[0011] Step 4: The Conch Data Platform logically encapsulates the data through API interfaces and provides users with access addresses.

[0012] In step one, when the source table has a small amount of data or does not support data source change detection, select full integration as the data integration method; when the source table has a large amount of data and supports data source change detection, select incremental integration as the data integration method; when the source table has a large amount of data but does not support change detection for deleted or modified data, select the full-increment-periodic full integration method.

[0013] In step one, when the Conch Data Platform integrates large amounts of data, it adopts a condition-based and batch-based approach to data integration.

[0014] Step two, data architecture design includes theme design, model design, and indicator design.

[0015] In step three, the ETL script development process is divided into four levels: source layer, integration layer, reporting layer, and marketplace layer.

[0016] In step three, during ETL job scheduling, the Conch Data Platform determines how many databases are in the source end of the business system. If there is only one, scheduling is terminated when the job fails; if there are multiple databases, a job scheduling policy is configured for each database to continue executing the task even if the job fails.

[0017] In step four, the Conch Data Platform creates a new API interface, fills in some basic information, selects an authentication method, and obtains API interface data; writes the data retrieval logic; conducts data testing, and after successful testing, publishes and approves the API interface, generating corresponding interface information; creates APP authentication, creating corresponding APP authentication based on the API interface usage requirements; creates flow control policies to limit the number of times the API can be accessed within a unit of time; and binds the API interface with the APP authentication method, flow control policies, etc.

[0018] The present invention provides a computer, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above-described method.

[0019] The present invention provides a storage medium storing a computer program, which, when executed by a processor, implements the steps of the above-described method.

[0020] The technical effects of this invention are as follows:

[0021] (1) This invention uses the Conch Data Platform as its foundation to complete the ETL lifecycle process for data governance. By analyzing the current business situation and adopting different methods for data integration, problems such as data loss and low execution efficiency can be solved. By splitting business database table jobs, the access pressure on the source database can be effectively reduced and the execution time of a single task can be shortened. By configuring the job scheduling strategy according to the number of source databases, the coupling between various jobs can be reduced, the mutual influence can be reduced, and the overall data quality can be improved.

[0022] (2) This invention solves the inherent defects of traditional full integration and incremental integration by using a dynamically adaptable integration strategy selection mechanism. Based on the characteristics of the source table data volume and change detection capabilities, the optimal integration method is matched: for tables with small data volumes or no change detection capabilities, full integration with a second-level response ensures data integrity; for tables with large data volumes and support for change detection, incremental integration controls data synchronization latency to the minute level; for scenarios with large data volumes but limited change detection, an innovative hybrid mode of full-incremental-periodic full integration is adopted, which maintains high-frequency updates through incremental synchronization and eliminates accumulated data errors through periodic full verification.

[0023] (3) This invention innovatively proposes a conditional sharding parallel integration architecture, which splits tasks by business dimension and sets priority execution strategies. The hour-level execution time of a single job is decomposed into minute-level processing of multiple sub-jobs, thereby shortening the continuous pressure time on the source database.

[0024] (4) This invention achieves standardization and visualization of data governance. Subject domain division improves the efficiency of business data catalog management, and a unified data standard system enhances the consistency of table structure, eliminating field ambiguity issues caused by traditional decentralized modeling. The hierarchical data model enables specialized division of labor in data processing, with each layer focusing on specific processing tasks, thus shortening the time for locating data problems.

[0025] (5) This invention employs full lifecycle management of API interfaces to construct a data security barrier, which reduces the risk of data leakage compared to traditional database connection string methods. Strong verification of access identity is achieved through APP authentication, and combined with multi-dimensional flow control strategies, the interception rate of abnormal API calls is improved. Attached Figure Description

[0026] This manual includes the following figures, which illustrate the following:

[0027] Figure 1 This is a flowchart illustrating the ETL scheduling method, computer, and storage medium based on the Conch Data Platform of the present invention.

[0028] Figure 2 This is a schematic diagram illustrating the ETL scheduling method, computer, and storage medium based on the Conch Data Platform of the present invention.

[0029] Figure 3 This is a schematic diagram illustrating the ETL scheduling method based on the Conch Data Platform of the present invention, and the conditional and batch-based data integration of the computer and storage medium.

[0030] Figure 4 This is a job configuration diagram of the ETL scheduling method, computer, and storage medium based on the Conch Data Platform of this invention;

[0031] Figure 5 This is a data architecture design diagram of the ETL scheduling method, computer, and storage medium based on the Conch Data Platform of this invention.

[0032] Figure 6 This is a flowchart illustrating the ETL scheduling method, computer, and storage medium based on the Conch Data Platform of this invention, as well as the ETL script development process.

[0033] Figure 7 This is a flowchart illustrating the ETL job scheduling method, computer, and storage medium based on the Conch Data Platform of this invention. Detailed Implementation

[0034] The specific embodiments of the present invention will be further described in detail below with reference to the accompanying drawings, in order to help those skilled in the art to have a more complete, accurate and in-depth understanding of the inventive concept and technical solution of the present invention, and to facilitate its implementation.

[0035] This invention provides an ETL scheduling method based on the Conch Data Platform:

[0036] Step 1: The Conch Data Platform establishes a data connection with the source system database, selects the data integration method based on the actual situation of the source database tables, and extracts the source system database tables;

[0037] Step 2: Design the data architecture based on the Conch Data Platform;

[0038] Step 3: Develop ETL scripts and schedule ETL jobs based on the Conch Data Platform;

[0039] Step 4: The Conch Data Platform logically encapsulates the data through API interfaces and provides users with access addresses.

[0040] In step one, when the source table has a small amount of data or does not support data source change detection, select full integration as the data integration method; when the source table has a large amount of data and supports data source change detection, select incremental integration as the data integration method; when the source table has a large amount of data but does not support change detection for deleted or modified data, select the full-increment-periodic full integration method.

[0041] In step one, when the Conch Data Platform integrates large amounts of data, it adopts a condition-based and batch-based approach to data integration.

[0042] Step two, data architecture design includes theme design, model design, and indicator design.

[0043] In step three, the ETL script development process is divided into four levels: source layer, integration layer, reporting layer, and marketplace layer.

[0044] In step three, during ETL job scheduling, the Conch Data Platform determines how many databases are in the source end of the business system. If there is only one, scheduling is terminated when the job fails; if there are multiple databases, a job scheduling policy is configured for each database to continue executing the task even if the job fails.

[0045] In step four, the Conch Data Platform creates a new API interface, fills in some basic information, selects an authentication method, and obtains API interface data; writes the data retrieval logic; conducts data testing, and after successful testing, publishes and approves the API interface, generating corresponding interface information; creates APP authentication, creating corresponding APP authentication based on the API interface usage requirements; creates flow control policies to limit the number of times the API can be accessed within a unit of time; and binds the API interface with the APP authentication method, flow control policies, etc.

[0046] The present invention provides a computer, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above-described method.

[0047] The present invention provides a storage medium storing a computer program, which, when executed by a processor, implements the steps of the above-described method.

[0048] The ETL scheduling method based on the Conch Data Platform of the present invention is described in detail below.

[0049] In traditional data governance, data integration is mainly divided into two types: full integration and incremental integration. Full integration involves copying all data from the required tables in the source system at a 1:1 ratio, regardless of whether the data has changed; a full extraction is performed each time. This method is relatively simple to implement, does not require tracking changes to the data source, ensures comprehensive data updates, and avoids data gaps, but has low execution efficiency and high resource consumption. Incremental integration only extracts data that has changed in the tables since the last integration, including data additions, modifications, and deletions. This method has high execution efficiency and can be executed frequently to maintain data timeliness. However, it requires tracking data changes, has relatively complex implementation logic, and may result in data gaps.

[0050] The Conch Data Platform supports full and incremental integration of common databases such as MySQL, Oracle, PostgreSQL, and SQL Server. Since both full and incremental integration have their advantages and disadvantages, in practice, the integration method is usually chosen based on different situations to ensure that the data maintains its integrity, validity, and reliability.

[0051] First, the Conch Data Platform establishes a connection with the source system database to facilitate data integration. Second, business analysis is conducted based on the actual situation of the source tables, generally falling into three categories: First, if the source table has a small data volume or does not support data source change detection, and full data extraction can be completed in seconds, then full integration is prioritized. Second, if the source table has a large data volume and supports data source change detection, then incremental integration is prioritized. Third, if the source table has a large data volume but does not support change detection for deleted or modified data, a full-incremental-periodic full integration approach is generally chosen. This ensures data integrity while preventing high-frequency access, avoiding excessive pressure on the business system database and potential server crashes. Finally, the data integration method is determined, and the relevant database tables are extracted to the Conch Data Platform.

[0052] For tables with large amounts of data, even with regular full data extraction, the excessively long job execution time can put significant pressure on the business system database, potentially causing server crashes or data extraction job failures due to network fluctuations. To address these issues, this invention employs a condition-based, batch-based data integration approach when using the Conch Data Platform for large-scale data extraction. Specifically, for tables with large amounts of data, conditions are added to integrate the data into a single table through multiple sequential jobs.

[0053] like Figure 3 As shown, taking a data table from a business system as an example, the table contains tens of millions of records. When performing full data integration, using a single scheduled job would take hours to execute. Firstly, this can easily put prolonged pressure on the business system's database. Secondly, if connection interruptions occur due to network fluctuations, related tasks must be re-executed, wasting considerable time. Therefore, the entire table can be split according to different conditions to reduce the number of jobs extracted and the time required, avoiding or reducing the frequency of the aforementioned problems.

[0054] For tables that are split into multiple sub-jobs for data integration, the following two points should be noted: First, when configuring the destination job, all sub-jobs need to write data into the same table; second, there needs to be a priority between the execution of each sub-job, and the first sub-job needs to choose to clear the data in the table when configuring the destination job, while other sub-jobs do not need to clear the data in the table.

[0055] The data architecture module, supported by relational and dimensional modeling theories, enables the development of standardized, visualized, and standardized data models. It guides developers in the ETL scheduling process development phase of data governance. Its main focus is on three aspects: first, building a unified data classification system for cataloging and managing all business data, facilitating data categorization, retrieval, and use; second, building a unified data standard system to standardize tables, data, and fields, improving data quality and usability; and third, building a unified data model system, constructing a top-down hierarchical system for enterprise data through standardized definitions and data modeling, facilitating data flow, sharing, and interaction, improving data utilization efficiency, and reducing redundant information.

[0056] like Figure 5 As shown, data architecture design is a preliminary step in the ETL scheduling process. Business personnel design the relevant architecture based on actual business needs. The main steps are as follows:

[0057] Step 1: Theme design. Design the theme content according to business needs, including theme domain grouping, theme domain, business objects, entities, attributes, etc.

[0058] Step 2: Model design, mainly including relational modeling and dimensional modeling. Relational modeling primarily involves merging and aligning data from various business systems according to themes from a group perspective, requiring compliance with the third normal form of the database. Dimensional modeling mainly constructs a multi-dimensional model from an analytical decision-making perspective, focusing on how users can quickly complete requirements analysis.

[0059] Step 3: Indicator Design, mainly including atomic indicators, derived indicators, and composite indicators. Atomic indicators are directly derived from the most basic measurements of the business process, with the smallest granularity and indivisible, such as the electricity consumption and output data of a cement plant's production line. Derived indicators are simple calculations of atomic indicators or modified by adding calibers and dimensions, such as the total electricity consumption and output data of a cement plant on a certain day. Composite indicators are calculated by superimposing multiple atomic or derived indicators, such as the growth rate of a cement plant's electricity consumption and output in a certain month compared to the previous month. The three together constitute a complete indicator management system.

[0060] After completing all the design requirements for the data architecture, developers can then carry out relevant ETL development work based on the architecture design and actual business needs.

[0061] Data development is a one-stop big data development platform and a key module in the entire ETL scheduling process of the Conch Data Platform. It boasts a rich set of data development types, such as SQL, Shell, and Python. It supports multi-user online collaborative work and the management of various big data cloud services, offering fully managed job scheduling and operation and maintenance monitoring capabilities to help enterprises quickly build big data processing centers. It mainly includes two modules: ETL script development and ETL job scheduling.

[0062] ETL script development is the implementation of actual business needs. The entire development process is divided into four layers: Source Data Integration (SDI), Data Warehouse Integration (DWI), Data Warehouse Report (DWR), and Data Mart (DWS). The main development steps are as follows:

[0063] Step 1: Source Layer. This layer is a simple landing of data from the source system. It establishes the connection between the source database and the Conch Data Platform, creates data integration tasks according to business needs, integrates the required data on a 1:1 basis or filters it based on conditions, and integrates the data into the Conch Data Platform.

[0064] Step 2: Integration Layer. This layer's main function is to integrate, filter, and clean the source system data integrated from the source layer. For potential issues such as null values, empty values, and dirty data in the source data, the integration layer performs simple processing and filtering on these problematic data.

[0065] Step 3: Reporting layer. This layer mainly focuses on implementing actual business needs. Based on the results of the preliminary business research, the data in the integration layer is logically processed, manipulated, and calculated from different dimensions and perspectives to complete the development of corresponding indicators.

[0066] Step 4: The Marketplace Layer. This layer is mainly used to summarize the processed and calculated data. By adding statistical dimensions and other information, the data is classified and stored in a hierarchical manner, making the data more intuitive and clear. It also provides the primary data source for subsequent data services.

[0067] Data is processed layer by layer at four levels, each serving different scenarios, forming a complete development process from raw data to business applications. The entire process is as follows: Figure 6 As shown.

[0068] In the ETL development process, the source layer and integration layer are based on MapReduce, while the reporting layer and data mart layer are based on data warehouse services. Because the data is stored in different locations, the data span between the integration layer and the reporting layer needs to be transferred via data integration.

[0069] The main function of ETL job scheduling is to connect the entire operation process of ETL job development. It supports graphical design and allows for the rapid construction of data processing workflows through drag-and-drop. It supports single-time scheduling, periodic scheduling, and event-driven scheduling. The main purpose is to test or periodically update data through different scheduling methods, ensuring that the latest business data is regularly synchronized to the Conch Data Platform.

[0070] The entire job scheduling process is mainly divided into five levels, with four levels corresponding to the data development process. Each level requires a separate scheduling job. Finally, a master job is created to link the jobs from the four development levels together. During periodic scheduling, only the master job needs to be scheduled.

[0071] Traditional ETL job scheduling suffers from the following problem: for the same type of business system with multiple source databases, all database tables' data is concentrated in a single scheduling job, resulting in strong coupling between the tables. If one database fails, other data cannot be updated. Therefore, to address this issue, the Conch Data Platform fully considers the actual situation and uses different configuration strategies for ETL scheduling. Specifically, it determines how many source databases the business system has. If there's only one, the scheduling task is terminated upon job failure. If multiple databases exist, a separate scheduling strategy is configured for each database to continue execution even if the job fails. This method allows jobs to run in parallel across databases without interfering with each other. Even if a database fails, only the data in that database table is affected—it cannot be updated—but other database tables remain unaffected, and the entire ETL scheduling job can continue. This method effectively reduces the risk of data loss caused by mutual interference between jobs.

[0072] After the data ETL scheduling process is completed, the data needs to be made available for use to fully realize its value. The Conch Data Platform has a dedicated data service module that provides the ability to quickly generate data API interfaces from database tables. It covers the entire lifecycle management of API publishing, management, operation and maintenance, and usage, and is characterized by simplicity, speed, low cost, and low risk.

[0073] The data service primarily offers two data provision methods. One is to directly provide database connection strings, granting access to database tables and databases via usernames and passwords. This method is straightforward, directly exposing database tables and data, resulting in poor privacy and significant security risks; it is generally not considered. The other method is through API interfaces, logically encapsulating the data and providing users with access addresses. Furthermore, mechanisms such as authentication, interface auditing, and access policies can enhance data security. This method is typically preferred when providing data services. The specific steps are as follows:

[0074] Step 1: Create a new API and fill in some basic information, such as API name, directory, access path, request method, etc.

[0075] Step 2: Security level authentication method selection, divided into three methods: APP authentication, IAM authentication, and no authentication. Among them, no authentication has the lowest security level, and APP authentication has the highest security level. To ensure data security, APP authentication is usually selected, and interface data is obtained through AppKey and AppSecret;

[0076] Step 3: Write the data retrieval logic. Based on actual business needs, retrieve data from the database tables through script configuration or by writing code. Generally, writing code is preferred because it offers greater flexibility and can better meet business requirements.

[0077] Step 4: Data Testing and Deployment. Successful testing indicates that the data retrieval logic is written correctly. The API interface will then be deployed and reviewed, generating the corresponding interface information.

[0078] Step 5: Create App Authentication. Based on the API usage requirements, create the corresponding App authentication. You can bind one API to multiple App authentications, or bind one App authentication to multiple APIs.

[0079] Step 6: Create a flow control policy to limit the number of times the API is accessed within a unit of time, preventing high-frequency API calls from putting pressure on the database and causing server crashes. Flow control restrictions can be implemented from different dimensions such as user, application, and time period.

[0080] Step 7: Bind the API and APP authentication methods, flow control policies, etc. Developers need to obtain the AppKey, AppSecret, and interface URL at the same time to complete the data access and ensure the security and reliability of the data.

[0081] The technical effects of the present invention are described in detail below.

[0082] This invention uses the Conch Data Platform as its foundation to complete the ETL lifecycle process for data governance. By analyzing the current business situation and adopting different methods for data integration, it can solve problems such as missing data and low execution efficiency. By splitting business database table jobs, it can effectively reduce the access pressure on the source database and shorten the execution time of individual tasks. By customizing the job scheduling strategy based on the number of source databases, it can reduce the coupling between various jobs, reduce mutual influence, and improve the overall data quality.

[0083] This invention addresses the inherent shortcomings of traditional full and incremental integration through a dynamically adaptable integration strategy selection mechanism. It matches the optimal integration method based on the characteristics of the source table, such as data volume and change detection capabilities: for tables with small data volumes or no change detection capabilities, full integration with second-level response ensures data integrity; for tables with large data volumes and change detection support, incremental integration controls data synchronization latency to the minute level; for scenarios with large data volumes but limited change detection, an innovative hybrid mode of full-incremental-periodic full integration is adopted, maintaining high-frequency updates through incremental synchronization while eliminating accumulated data errors through periodic full verification.

[0084] This invention innovatively proposes a conditional sharding parallel integration architecture, which splits tasks by business dimension and sets priority execution strategies. It decomposes the hour-level execution time of a single job into minute-level processing of multiple sub-jobs, thus shortening the continuous pressure period on the source database.

[0085] This invention achieves standardization and visualization of data governance. Subject domain division improves the efficiency of cataloging business data, while a unified data standard system enhances table structure consistency and eliminates field ambiguity issues caused by traditional distributed modeling. The hierarchical data model enables specialized division of data processing, with each layer focusing on specific tasks, thus shortening the time required to locate data problems.

[0086] This invention employs full lifecycle management of API interfaces to build a data security barrier, reducing the risk of data leakage compared to traditional database connection strings. Strong identity verification is achieved through APP authentication, and combined with multi-dimensional flow control strategies, the interception rate of abnormal API calls is improved.

[0087] The present invention has been described above by way of example with reference to the accompanying drawings. Obviously, the specific implementation of the present invention is not limited to the above-described manner. Any non-substantial improvements made using the inventive concept and technical solution; or the direct application of the inventive concept and technical solution to other situations without modification, are all within the protection scope of the present invention.

Claims

1. An ETL scheduling method based on the Conch Data Platform, characterized in that: Step 1: The Conch Data Platform establishes a data connection with the source system database, selects the data integration method based on the actual situation of the source database tables, and extracts the source system database tables; Step 2: Design the data architecture based on the Conch Data Platform; Step 3: Develop ETL scripts and schedule ETL jobs based on the Conch Data Platform; Step 4: The Conch Data Platform logically encapsulates the data through API interfaces and provides users with access addresses.

2. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: In step one, when the source table has a small amount of data or does not support data source change detection, select full integration as the data integration method; when the source table has a large amount of data and supports data source change detection, select incremental integration as the data integration method; when the source table has a large amount of data but does not support change detection for deleted or modified data, select the full-increment-periodic full integration method.

3. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: In step one, when the Conch Data Platform integrates large amounts of data, it adopts a condition-based and batch-based approach to data integration.

4. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: Step two, data architecture design includes theme design, model design, and indicator design.

5. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: In step three, the ETL script development process is divided into four levels: source layer, integration layer, reporting layer, and marketplace layer.

6. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: In step three, during ETL job scheduling, the Conch Data Platform determines how many databases are in the source end of the business system. If there is only one, scheduling is terminated when the job fails; if there are multiple databases, a job scheduling policy is configured for each database to continue executing the task even if the job fails.

7. The ETL scheduling method based on the Conch Data Platform as described in claim 1, characterized in that: In step four, the Conch Data Platform creates a new API interface, fills in some basic information, selects an authentication method, obtains API interface data, writes the data retrieval logic, conducts data testing, and after successful testing, publishes and reviews the API interface to generate corresponding interface information; and creates APP authentication based on the API interface usage requirements. Create a flow control policy to limit the number of times an API can be accessed within a unit of time. Bind API interfaces with APP authentication methods, flow control strategies, etc.

8. A computer comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method as described in any one of claims 1-7.

9. A storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method as described in any one of claims 1-7.

Images