Section monitoring method and system for data ETL process, terminal and medium
By defining and monitoring three points in the ETL process, obtaining and analyzing monitoring logs, the problem that traditional ETL monitoring methods cannot be monitored and optimized in real time is solved, and efficient monitoring and optimization of the ETL process is achieved.
Patent Information
- Application Number
- CN202411777259.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-05
- Publication Date
- 2025-04-11
AI Technical Summary
Traditional ETL monitoring methods cannot record and analyze the performance data of the ETL process in real time, resulting in inefficient processing and difficulty in positioning problems.
By defining three monitoring tangents for data acquisition, transformation and loading, the indicators and time-consuming of each tangent are obtained and recorded, a monitoring log is formed, and aggregation analysis and visual presentation is performed through standardized log printing and acquisition.
Real-time monitoring and optimization of the ETL process is realized, data extraction efficiency is improved, monitoring and positioning accuracy is ensured, resource consumption is reduced, and data real-time is ensured.
Smart Images
Figure CN119806953A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data processing, and in particular to a section monitoring method, system, terminal and medium for a data ETL process. Background Art
[0002] In data warehouse and big data processing, the ETL (collection, transformation, loading) process is one of the key links. As a widely used database management system, SQL Server requires efficient monitoring and optimization methods for its ETL process. However, traditional ETL monitoring methods often cannot record and analyze the performance data of the ETL process in real time, resulting in low processing efficiency and difficulty in problem location. Therefore, how to monitor and optimize the data ETL process of SQL Server in real time is urgently needed. Summary of the invention
[0003] In order to solve the technical problems of low data extraction efficiency and difficult monitoring in the existing data ETL process, the present invention provides a cross-sectional monitoring method, system, terminal and medium for the data ETL process.
[0004] To achieve the above object, the present invention provides the following technical solutions:
[0005] The present invention discloses a section monitoring method for a data ETL process, comprising the following steps:
[0006] S1. Define three monitoring cut-points, namely, data collection cut-point, data conversion cut-point and data loading cut-point;
[0007] S2. Obtain indicators according to each monitoring cut-off point and record the time consumed; wherein, in the process of obtaining indicators corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches;
[0008] S3. Summarize the indicators and time consumption corresponding to each monitoring cut-off point, and connect them in series according to a single ETL task, so as to print out the monitoring log corresponding to each monitoring cut-off point;
[0009] S4. Collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
[0010] As a further improvement of the above solution, in step S2, the method for forming the monitoring log corresponding to the data collection cut-off point includes the following specific steps:
[0011] S211. Start the first timer and obtain the query target data table and query method;
[0012] S212. Obtain query parameters and assemble the complete query structured language;
[0013] S213. Determine whether the query parameter exceeds a predetermined number threshold, if yes, execute step S214, otherwise execute step S215;
[0014] S214. Using the predetermined number threshold as a segmentation scale, the query parameters are segmented into multiple batches, and multiple queries are performed using a thread pool;
[0015] S215. After querying the query parameters, stop the first timer and record the query status, and use the start and stop interval of the first timer as the data collection cut-off time consumption.
[0016] As a further improvement of the above solution, in step S2, the method for forming the monitoring log corresponding to the data loading cut-off point includes the following specific steps:
[0017] S231. Start the third timer and obtain the data loading target name;
[0018] S232. Get the primary key of adding data, modifying data, and deleting data;
[0019] S233. Get the amount and byte size of the loaded data;
[0020] S234. Stop the third timer, and use the start and stop interval of the third timer as the time consumption of the data loading cut-off point.
[0021] As a further improvement of the above solution, in step S2, the method for forming the monitoring log corresponding to the data conversion cut-off point includes the following specific steps:
[0022] S221. Start the second timer and obtain the name of the data conversion method, then stop the third timer, and use the start and stop interval of the third timer as the data conversion cut-off time.
[0023] As a further improvement of the above scheme, in step S3, the query status and the data collection cut-off time are combined to form a monitoring log corresponding to the data collection cut-off; the parameters obtained in steps S231 to S233 and the data loading cut-off time are combined to form a monitoring log corresponding to the data loading cut-off; the method name of the data conversion and the data conversion cut-off time are combined to form a monitoring log corresponding to the data conversion cut-off.
[0024] As a further improvement of the above solution, step S4 includes the following specific steps:
[0025] S41. Collect the monitoring logs corresponding to all monitoring points into the OLAP database through FileBeat;
[0026] S42. Perform ETL log aggregation analysis and statistics on OLAP database;
[0027] S43. Visualize and analyze monitoring indicators.
[0028] As a further improvement of the above solution, step S1 includes the following specific steps:
[0029] S11. Through standardized sub-packaging, the database operation method is defined as the data collection cut-off point;
[0030] S12. Provide custom annotations for users to add to the complex conversion logic that needs to be monitored, and define data conversion cutoff points based on each custom annotation;
[0031] S13. Define the top-level interface, unify the sink abstract method in the top-level interface, and define the data loading point based on the sink abstract method.
[0032] The present invention also discloses a section monitoring system for data ETL process, which applies the section monitoring method for data ETL process as described above; the section monitoring system includes: a section point definition module, an indicator acquisition module, a log printing module and an analysis module.
[0033] The cut-point definition module is used to define three types of monitoring cut-points, namely data collection cut-points, data conversion cut-points and data loading cut-points;
[0034] The indicator acquisition module is used to acquire indicators according to each monitoring cut-off point and record the time consumed; wherein, in the indicator acquisition process corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches;
[0035] The log printing module is used to summarize the indicators and time consumption corresponding to each monitoring cut-off point, and connect them in series according to a single ETL task, so as to print the monitoring log corresponding to each monitoring cut-off point;
[0036] The analysis module is used to collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
[0037] The present invention also discloses a computer terminal, comprising a memory and a processor, wherein the processor is used to store a computer program, and the processor is used to implement the above-mentioned aspect monitoring method for a data ETL process when executing the computer program.
[0038] The present invention also discloses a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the aspect monitoring method for a data ETL process as described above is implemented.
[0039] Compared with the prior art, the present invention has the following beneficial effects:
[0040] 1. The aspect monitoring method for the data ETL process disclosed in the present invention can unify the operations scattered throughout the project through a standardized log printing method, decouple them from the business process, reduce the complexity of the development process, and make the project structure clearer and more readable, thereby improving data extraction efficiency and ensuring the accuracy of monitoring positioning.
[0041] 2. In the data extraction process, this method divides the SQL Server parameters according to the threshold based on the aspect method, and uses the thread pool for concurrent processing to avoid the need for additional processing of this logic in all ETL processes. Similarly, this idea is applicable to other similar general processing extractions.
[0042] 3. This method is based on standardized log printing and collection, aggregates and analyzes the time-consuming logs of operations during data extraction, conversion, and loading, and quickly locates abnormal operations for troubleshooting and optimization. It can improve ETL efficiency, reduce unnecessary resource consumption, and ensure the real-time nature of data.
[0043] 4. The aspect monitoring system, computer terminal and computer-readable storage medium for data ETL process disclosed in the present invention can produce the same beneficial effects as the above method by applying the above method, which will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS
[0044] Figure 1 The flowchart of the aspect monitoring method for data ETL process according to an embodiment of the present invention.
[0045] Figure 2 It is a structural diagram of a slice monitoring system for a data ETL process according to an embodiment of the present invention.
[0046] Figure 3 4 is a structural diagram of a computer terminal according to an embodiment of the present invention. DETAILED DESCRIPTION
[0047] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0048] See also Figure 1 , this embodiment provides a section monitoring method for a data ETL process, comprising the following steps, namely S1 to S4.
[0049] S1. Define three monitoring cut-points, namely data collection cut-point, data conversion cut-point and data loading cut-point.
[0050] Step S1 includes the following specific steps, namely S11 to S13.
[0051] S11. Through standardized sub-packaging processing, the database operation method is defined as the data collection cut-off point.
[0052] S12. Provide custom annotations for users to add to the complex conversion logic that needs to be monitored, and define data conversion pointcuts based on each custom annotation.
[0053] Defining data conversion pointcuts based on each custom annotation can be understood as: using custom annotations to define data conversion pointcuts, the program can be added to any conversion method, at which time the method will be defined as a data conversion pointcut.
[0054] S13. Define the top-level interface, unify the sink abstract method in the top-level interface, implement different goals based on strategies, and define the data loading point based on the sink abstract method.
[0055] Among them, sink is a data loading method that uses abstract ideas.
[0056] S2. Obtain indicators according to each monitoring cut-off point and record the time consumed; wherein, in the process of obtaining indicators corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches.
[0057] It should be noted that the indicators in step S2 refer to the key information of each cut point, such as target name, method, data volume, etc., which are mainly used for analysis.
[0058] In step S2, the method for forming the monitoring log corresponding to the data collection cut-point includes the following specific steps, namely S211 to S215.
[0059] S211. Start the first timer and obtain the query target data table and the query method.
[0060] S212. Obtain query parameters and assemble a complete query structured language (ie, SQL, Structured Query Language).
[0061] S213. Determine whether the query parameter exceeds a predetermined quantity threshold, if yes, execute step S214, otherwise execute step S215.
[0062] In this embodiment, the size of the predetermined number threshold is set to 2000. In other embodiments, it can also be set to other sizes according to the parameter restrictions of SqlServer.
[0063] S214. Use 2000 as the segmentation scale, segment the query parameters into multiple batches, and use the thread pool to perform multiple queries.
[0064] For example, when the number of query parameters is 2100, it is divided into two batches; when the number of query parameters is 5200, it is divided into three batches.
[0065] S215. After querying the query parameters, stop the first timer and record the query status, and use the start and stop interval of the first timer as the data collection cut-off time consumption.
[0066] The method for forming the monitoring log corresponding to the data conversion cut-off point includes the following specific steps, namely S221.
[0067] S221. Start the second timer and obtain the name of the data conversion method, then stop the third timer, and use the start and stop interval of the third timer as the data conversion cut-off time.
[0068] It should be noted that, unlike the other two types of monitoring logs, the data conversion process is an intermediate process. The monitoring log corresponding to the data conversion point only needs to obtain the method name of the data conversion and record the time consumed.
[0069] The method for forming the monitoring log corresponding to the data loading cut-point includes the following specific steps, namely S231 to S234.
[0070] S231. Start the third timer and obtain the data loading target name.
[0071] S232. Obtain the primary key for adding new data, modifying data, and deleting data.
[0072] S233. Obtain the data volume and byte size of the loaded data.
[0073] S234. Stop the third timer, and use the start and stop interval of the third timer as the time consumption of the data loading cut-off point.
[0074] It should be noted that the first timer, the second timer and the third timer are respectively started when the program is executed to the corresponding cut-off point, so the start times are different.
[0075] S3. Summarize the indicators and time consumption corresponding to each monitoring cut-point, and connect them in series according to a single ETL task, so as to print out the monitoring log corresponding to each monitoring cut-point.
[0076] In step S3, the query status and the data collection cut-off time are combined to form a monitoring log corresponding to the data collection cut-off; the parameters obtained in steps S231 to S233 and the data loading cut-off time are combined to form a monitoring log corresponding to the data loading cut-off; the method name of the data conversion and the data conversion cut-off time are combined to form a monitoring log corresponding to the data conversion cut-off.
[0077] S4. Collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
[0078] Step S4 includes the following specific steps, namely S41 to S43.
[0079] S41. Collect the monitoring logs corresponding to all monitoring cut-points into the OLAP (Online Analytical Processing) database through FileBeat.
[0080] Among them, FileBeat is an open source lightweight log data collector provided by Elasticsearch. OLAP database, also known as analytical database, is a type of database system specially designed to support complex analytical processing of large-scale data.
[0081] S42. Perform ETL log aggregation analysis and statistics on the OLAP database.
[0082] The above three types of monitoring logs will be collected into the OLAP database. This step is to perform some query aggregation analysis on the above three types of logs.
[0083] S43. Visualize and analyze monitoring indicators.
[0084] The above sectioning method has the following advantages:
[0085] Unified ETL process log printing: Through standardized log printing methods, operations scattered throughout the project can be processed in a unified manner and decoupled from business processes, which not only reduces the complexity of the development process, but also makes the project structure clearer and more readable.
[0086] Decoupling of general processing: In the data extraction process, SQL Server parameters are segmented according to thresholds based on aspects, and concurrent processing is performed using thread pools to avoid the need for additional processing of this logic in all ETL processes. Similarly, this idea is applicable to other similar general processing extractions.
[0087] ETL efficiency monitoring and optimization: Based on standardized log printing and collection, aggregate analysis is performed on the time-consuming logs of operations during data extraction, conversion, and loading, and abnormal operations are quickly located for troubleshooting and optimization. This can improve ETL efficiency, reduce unnecessary resource consumption, and ensure the real-time nature of data.
[0088] See also Figure 2Another embodiment of the present invention further provides a section monitoring system 10 for a data ETL process, which applies the section monitoring method for a data ETL process as described above; the section monitoring system 10 includes: a section point definition module 11, an indicator acquisition module 12, a log printing module 13 and an analysis module 14.
[0089] The cut-point definition module 11 is used to define three monitoring cut-points, namely, data collection cut-point, data conversion cut-point and data loading cut-point;
[0090] The indicator acquisition module 12 is used to acquire indicators according to each monitoring cut-off point and record the time consumed; wherein, in the indicator acquisition process corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches;
[0091] The log printing module 13 is used to summarize the indicators and time consumption corresponding to each monitoring cut-off point, and connect them in series according to a single ETL task, so as to print out the monitoring log corresponding to each monitoring cut-off point;
[0092] The analysis module 14 is used to collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
[0093] like Figure 3 As shown, another embodiment of the present invention further provides a computer terminal, the computer terminal comprising: at least one processor 101, and a memory 102 connected to the at least one processor 101. In this embodiment, the specific connection medium between the processor 101 and the memory 102 is not limited. Figure 3 In the example, the processor 101 and the memory 102 are connected via the bus 100. The bus 100 is connected to the memory 102 via the bus 100. Figure 3 The connections between other components are shown by thick lines, which are only schematic and not limiting. The bus 100 can be divided into an address bus, a data bus, a control bus, etc. For convenience of representation, Figure 3 In the figure, only one thick line is used, but it does not mean that there is only one bus or one type of bus. Alternatively, the processor 101 can also be called a controller, and there is no limitation on the name.
[0094] In this embodiment, the memory 102 stores instructions that can be executed by at least one processor 101. The at least one processor 101 can execute the aspect monitoring method for the data ETL process discussed above by executing the instructions stored in the memory 102. The processor 101 can implement Figure 2 The functions of each module in the device shown.
[0095] Among them, the processor 101 is the control center of the device, and can use various interfaces and lines to connect the various parts of the entire control device. By running or executing instructions stored in the memory 102 and calling data stored in the memory 102, the various functions of the device and process data, the device can be monitored as a whole.
[0096] In one possible design, the processor 101 may include one or more processing units, and the processor 101 may integrate an application processor and a modem processor, wherein the application processor mainly processes an operating system, a user interface, and application programs, and the modem processor mainly processes wireless communications. It is understandable that the modem processor may not be integrated into the processor 101. In some embodiments, the processor 101 and the memory 102 may be implemented on the same chip, and in some embodiments, they may also be implemented separately on separate chips.
[0097] The processor 101 may be a general-purpose processor, such as a central processing unit (CPU), a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, or a discrete hardware component, and may implement or execute the methods, steps, and logic block diagrams disclosed in this embodiment. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the aspect monitoring method for the data ETL process disclosed in this embodiment may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor 101.
[0098] The memory 102 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer executable programs and modules. The memory 102 may include at least one type of storage medium, such as a flash memory, a hard disk, a multimedia card, a card-type memory, a random access memory (Random Access Memory, RAM), a static random access memory (Static Random Access Memory, SRAM), a programmable read-only memory (Programmable Read Only Memory, PROM), a read-only memory (Read Only Memory, ROM), an electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, EEPROM), a magnetic memory, a disk, an optical disk, etc. The memory 102 is any other medium that can be used to carry or store a desired program code in the form of an instruction or data structure and can be accessed by a computer, but is not limited thereto. The memory 102 in this embodiment can also be a circuit or any other device that can realize a storage function, for storing program instructions and / or data.
[0099] By programming the processor 101, the code corresponding to the section monitoring method of the computer terminal described in the above embodiment can be fixed into the chip, so that the chip can execute the code when running. Figure 1 The steps of the aspect monitoring method for data ETL process in the embodiment shown are as follows: How to design and program the processor 101 is a technology well known to those skilled in the art and will not be described in detail here.
[0100] Yet another embodiment of the present invention provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the monitoring method as described above is implemented.
[0101] In some possible implementations, various aspects of the aspect monitoring method for a data ETL process provided by the present invention may also be implemented in the form of a program product, which includes a program code. When the program product is run on an apparatus, the program code is used to enable the control device to execute the steps of the aspect monitoring method for a data ETL process according to various exemplary embodiments of the present invention described above in this specification.
[0102] Those skilled in the art will appreciate that the present embodiment may be provided as a method, system, or computer program product. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0103] The computer-readable storage medium provided in this embodiment and the aspect monitoring method for the data ETL process can produce substantially the same technical effects, which will not be described in detail here.
[0104] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can make equivalent replacements or changes according to the technical scheme and inventive concept of the present invention within the technical scope disclosed by the present invention, which should be covered by the protection scope of the present invention.
Claims
1. A method for monitoring the aspect of a data ETL process, characterized in that: The following steps are involved: S1. Define three monitoring cut-points, namely, data collection cut-point, data conversion cut-point and data loading cut-point; S2. Obtain indicators according to each monitoring cut-off point and record the time consumed; wherein, in the process of obtaining indicators corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches; S3. Summarize the indicators and time consumption corresponding to each monitoring cut-off point, and connect them in series according to a single ETL task, so as to print out the monitoring log corresponding to each monitoring cut-off point; S4. Collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
2. A method for monitoring a data ETL process according to claim 1, characterized in that: In step S2, the method for forming the monitoring log corresponding to the data collection cut-off point includes the following specific steps: S211. Start the first timer and obtain the query target data table and query method; S212. Obtain query parameters and assemble the complete query structured language; S213. Determine whether the query parameter exceeds a predetermined number threshold, if yes, execute step S214, otherwise execute step S215; S214. Using the predetermined number threshold as a segmentation scale, the query parameters are segmented into multiple batches, and multiple queries are performed using a thread pool; S215. After querying the query parameters, stop the first timer and record the query status, and use the start and stop interval of the first timer as the data collection cut-off time consumption.
3. A method for monitoring a data ETL process according to claim 2, characterized in that: In step S2, the method for forming the monitoring log corresponding to the data loading cut-off point includes the following specific steps: S231. Start the third timer and obtain the data loading target name; S232. Get the primary key of adding data, modifying data, and deleting data; S233. Get the amount and byte size of the loaded data; S234. Stop the third timer, and use the start and stop interval of the third timer as the time consumption of the data loading cut-off point.
4. A method for monitoring a data ETL process according to claim 3, characterized in that: In step S2, the method for forming the monitoring log corresponding to the data conversion cut-off point includes the following specific steps: S221. Start the second timer and obtain the name of the data conversion method, then stop the third timer, and use the start and stop interval of the third timer as the data conversion cut-off time.
5. A method for monitoring a data ETL process according to claim 4, characterized in that: In step S3, the query status and the data collection cut-off time are combined to form a monitoring log corresponding to the data collection cut-off; the method name of the data conversion and the data conversion cut-off time are combined to form a monitoring log corresponding to the data conversion cut-off; the parameters obtained in steps S231 to S233 and the data loading cut-off time are combined to form a monitoring log corresponding to the data loading cut-off.
6. A method for monitoring the ETL process according to claim 1, characterized in that: Step S4 includes the following specific steps: S41. Collect the monitoring logs corresponding to all monitoring points into the OLAP database through FileBeat; S42. Perform ETL log aggregation analysis and statistics on OLAP database; S43. Visually display and analyze the data collection cut-off time, the data conversion cut-off time and the data loading cut-off time.
7. A method for monitoring a data ETL process according to claim 1, characterized in that: Step S1 includes the following specific steps: S11. Through standardized sub-packaging, the database operation method is defined as the data collection cut-off point; S12. Provide custom annotations for users to add to the complex conversion logic that needs to be monitored, and define data conversion cutoff points based on each custom annotation; S13. Define the top-level interface, unify the sink abstract method in the top-level interface, and define the data loading point based on the sink abstract method.
8. A facet monitoring system for data ETL process, characterized in that: The aspect monitoring method for data ETL process according to any one of claims 1 to 7 is applied; the aspect monitoring system comprises: The cut-point definition module is used to define three types of monitoring cut-points, namely data collection cut-point, data conversion cut-point and data loading cut-point; The indicator acquisition module is used to acquire indicators according to each monitoring cut-off point and record the time consumed; wherein, in the indicator acquisition process corresponding to the data collection cut-off point, the data collection process parameters are also processed in batches; The log printing module is used to summarize the indicators and time consumption corresponding to each monitoring cut-off point, and connect them in series according to a single ETL task, so as to print the monitoring log corresponding to each monitoring cut-off point; The analysis module is used to collect, analyze and visualize the monitoring logs corresponding to all monitoring points.
9. A computer terminal, characterized in that: It comprises a memory and a processor, wherein the processor is used to store a computer program, and the processor is used to implement the aspect monitoring method for a data ETL process as described in any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, characterized in that: The storage medium stores a computer program, and when the computer program is executed by a processor, the aspect monitoring method for a data ETL process as described in any one of claims 1 to 7 is implemented.