A distributed statistical method and system for power grid operation data supporting custom functions

Abstract

The present invention discloses a distributed statistical method and system for power grid operation data that supports custom functions. The method comprises developing corresponding UDF custom functions based on data statistical business rules; initializing configuration information such as SQL templates and UDF functions; registering statistical task-related information through the front end of a task scheduling center; the scheduling center triggers task scheduling on schedule based on the Cron configuration of the statistical task and sends a scheduling request to an executor via RPC; the executor receives and parses task parameters and creates a corresponding Hive mapping table for an HBase measurement table based on the table name information; the executor dynamically generates SQL statistical statements based on the task parameter information, completes system variable settings, creates a statistical result table, and then submits the SQL statistical statements to the Yarn resource management framework to execute the corresponding statistical task; and the data statistical results are written to the HBase result table. The present invention solves the pain points of existing Kettle-based data statistical systems, such as RDB database freezing, slow statistical task execution, and difficulty in scalability of data operation operators.

Description

Technical Field

[0001] The present invention belongs to the field of big data technology, and in particular relates to a distributed statistical method and system for power grid operation data supporting user-defined functions. Background Art

[0002] Enterprise-level big data platforms are prone to a wide range of business-related requirements requiring statistical analysis of data. These services often require statistical analysis operations such as aggregation, accumulation, and finding extreme values and the time corresponding to these extreme values, all based on specific business rules. For example, in the power grid sector, statistical analysis of characteristic values of containers and devices includes metrics such as maximum value, minimum value, maximum time, minimum time, average value, and integrated power consumption. For these business scenarios, a range of solutions currently exist in the industry, such as big data ETL processing tools like Kettle and Informatica. These tools offer excellent scalability, user-friendly GUI interfaces, and support for a variety of data source types, making them a common choice for data analysis and processing on enterprise-level big data platforms.

[0003] Kettle is a visual programming ETL tool. This data processing tool is simple to operate and easy to use, making it a popular open-source ETL tool. Kettle's data processing features include the ability to write transformation scripts to perform basic data conversions, write job scripts to control the entire workflow, and utilize a scheduling function to control the periodic execution of data statistical analysis tasks. While Kettle provides data analysis and processing methods to a certain extent, it only supports simple data analysis operations and is not suitable for scenarios with large data volumes.

[0004] While some excellent ETL data processing tools exist, they commonly face the following issues in actual production environments: They only support simple data analysis operations, such as sort, group by, and aggregator. Customizable operators are not available for specialized business processing scenarios. Furthermore, they are slow to process data, leading to slow or even stuck tasks when dealing with large data volumes. This makes them unsuitable for business scenarios involving massive amounts of data. However, large data volumes and complex operations are precisely the hallmarks of data analysis in industries such as power grids, banking, and insurance. Currently, no effective solutions have been proposed to address these issues. Summary of the Invention

[0005] The purpose of the present invention is to address the problems existing in the prior art and provide a distributed statistical method and system for power grid operation data that supports custom functions, so as to solve the pain points such as the lack of data analysis operation operators that cannot meet statistical business needs, slow data statistical performance in scenarios with large data volumes, and task jams.

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

[0007] A distributed statistical method for power grid operation data supporting user-defined functions includes the following steps:

[0008] S1, develop corresponding UDF functions according to the functional requirements of power grid operation data statistics business;

[0009] S2: Initialize SQL templates and UDF function creation statements corresponding to various business types;

[0010] S3, complete the registration of statistical task information through the front-end interface of the task scheduling center;

[0011] S4: The scheduling center triggers the task scheduling according to the Cron configuration information of the task and sends a task scheduling request to the executor through RPC;

[0012] S5: The executor receives and parses the task parameters and creates a corresponding Hive mapping table for the HBase measurement table based on the table name information.

[0013] S6: The executor dynamically generates the statistical SQL statement corresponding to the statistical task based on the task parameters, executes the UDF function to create the statement, and then submits the statistical SQL statement to the Yarn resource scheduling framework to execute the statistical task;

[0014] S7: The power grid operation data statistics are written into the HBase result table.

[0015] S8, the executor completes the data statistics task and returns the running results.

[0016] Furthermore, according to the specific needs of running data statistics business, it is judged whether the built-in functions of the existing data warehouse meet the data statistics business needs. If not, it is necessary to develop corresponding UDF functions according to the statistical business logic to meet the statistical business needs.

[0017] Furthermore, the initialization of configuration information corresponding to various business types also includes: creating an SQL template for a statistical result table corresponding to a data statistical task.

[0018] Furthermore, the task information includes a table name of data statistics, a business type, a task type, and task parameters.

[0019] Furthermore, the task scheduling center and the executor implement the transmission of task parameter information and the calling of task execution functions through RPC.

[0020] Furthermore, the task scheduling center uses a fragmented broadcast routing strategy to achieve distributed collaborative execution of data statistics tasks.

[0021] Furthermore, the Hive mapping table is a Hive external table created for a data table in HBase, and is used to implement a function of statistically analyzing massive data in HBase using the OLAP feature of Hive SQL.

[0022] Furthermore, the UDF function is a user-defined function developed to meet specific business requirements of the power grid. Before the statistical task is executed, the UDF function corresponding to the task needs to be dynamically created first.

[0023] Furthermore, the process of writing the statistical results of power grid operation data into the HBase result table is as follows: first, a Hive external table corresponding to the HBase result table is created, and then the statistical result data is stored in HBase using the Hive external table.

[0024] Accordingly, a distributed statistical system for power grid operation data supporting custom functions includes: a task scheduling center and a plurality of executors, wherein the plurality of executors form an executor cluster; the scheduling center and the executor cluster communicate remotely via RPC, and when an executor starts, it registers its address information with the task scheduling center; the task scheduling center is used to register data statistical task information and periodically trigger the scheduling and execution of tasks based on the Cron configuration of the task; the executor is used to receive parameter information of the data statistical task, dynamically generate the statistical SQL corresponding to the task according to the task parameter information, dynamically create a UDF function, and then submit the statistical SQL corresponding to the task to Yarn to run the statistical SQL; the data in the HBase table is read through the Hive external table;

[0025] The task scheduling center includes: a task management module, a log management module, an executor management module and an operation report management module. The task management module is used to provide task registration services and task scheduling services, the log management module is used to provide task log query services, and the executor management module is used to provide executor registration information management services. The task scheduling center also provides an operation report management module to provide query and display functions for scheduling reports, operation reports, and success ratio charts.

[0026] Compared with the existing technology, the present invention has the following advantages: it can customize and extend the data operation operators in big data analysis operations to meet the personalized operation requirements of data aggregation, conversion, decomposition, and other specific business scenarios. At the same time, based on the task scheduling execution method of MapReduce on Yarn, the unified resource management and scheduling framework YARN can be horizontally expanded to ensure the efficient execution of big data analysis tasks and avoid system performance issues caused by resource constraints. This data statistics method ensures the high availability and high performance of big data analysis and processing tasks, solving the problems of data processing operator shortage and big data analysis task performance bottlenecks that cannot be handled by existing technical solutions. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 This is a flow chart of a distributed statistical method for power grid operation data supporting user-defined functions according to the present invention;

[0028] Figure 2 This is a specific flow chart for implementing a distributed statistical method for power grid operation data supporting custom functions according to the present invention;

[0029] Figure 3 This is a system architecture diagram of a distributed statistical system for power grid operation data that supports custom functions according to the present invention. DETAILED DESCRIPTION

[0030] The following will clearly and completely describe the technical solution of the present invention in conjunction with the accompanying drawings. Obviously, the embodiments described are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0031] like Figure 1 、 2 As shown, this embodiment provides a distributed statistical method for power grid operation data supporting custom functions, including the following steps:

[0032] S1: Deploy dependent services such as the task scheduling center, executors, and CDH clusters. Identify the UDF functions that need to be expanded for data statistics services. Develop, package, and upload the UDF functions to the node where the Hive data warehouse service resides.

[0033] S2: Initialize configuration information such as SQL templates, UDF function creation statements, and statistical result table creation SQL templates corresponding to various business types;

[0034] S3, complete the registration of statistical task information through the front-end interface of the task scheduling center;

[0035] S4: The scheduling center triggers the task scheduling according to the Cron configuration information of the task and sends a scheduling request to the executor through RPC;

[0036] S5: The executor receives and parses the task parameters and creates a corresponding Hive mapping table for the HBase measurement table based on the table name information.

[0037] S6: The executor dynamically generates the statistical SQL statement corresponding to the statistical task based on the task parameters, executes the UDF function to create the statement, and then submits the statistical SQL statement to the Yarn resource scheduling framework to execute the specific statistical task;

[0038] S7: The power grid operation data statistics are written into the HBase result table.

[0039] S8, the executor completes the data statistics task and returns the running results.

[0040] Specifically, in step S1, the dependent services include tasks scheduling center, executor, CDH cluster and other services.

[0041] Furthermore, according to the specific needs of running data statistics business, it is judged whether the existing data warehouse operation operators, that is, built-in functions, meet the data statistics business needs. If not, it is necessary to develop corresponding UDF functions according to the statistical business logic to meet the statistical business needs.

[0042] Specifically, in step S2, the configuration information corresponding to various business types is initialized, including: SQL templates corresponding to data statistics tasks, UDF function creation statements, and SQL templates for creating statistical result tables.

[0043] Specifically, in step S3, the task registration information includes parameter information such as the table name of data statistics, business type, task type, task parameters, etc. The statistical task description information can be registered through the front-end interface of the task scheduling center.

[0044] Specifically, in step S4, the task scheduling center and the executor implement the transmission of task parameter information and the calling of task execution function through RPC.

[0045] Furthermore, the task scheduling center uses a shard broadcast routing strategy to achieve distributed collaborative execution of data statistics tasks. The parameter information of the data statistics task is broadcast to all executor nodes. Each executor determines the table range information that needs to be counted by the node based on the hash algorithm, thereby collaboratively completing the entire data statistics task.

[0046] Specifically, in step S5, the Hive mapping table is a Hive external table created for the data table in HBase. The running data information in HBase is read through the Hive external table, realizing the business demand of statistically analyzing the massive data in HBase by using the OLAP feature of Hive SQL.

[0047] Specifically, in step S6, the UDF function is a user-defined function developed to meet the specific business needs of the power grid. Before the statistical task is executed, the UDF function corresponding to the task needs to be dynamically created first, and then the statistical SQL can call the custom function to achieve personalized operation requirements such as data aggregation, conversion, and decomposition in specific businesses.

[0048] Furthermore, the statistical SQL statements are converted into MapReduce programs, which then request container resources from Yarn based on their needs to support their data processing tasks. MapReduce is a distributed data processing framework designed to rapidly meet business needs in big data processing scenarios. MapReduce can be considered a Yarn client, leveraging Yarn's scalable distributed cluster resources to complete its data processing tasks at each stage.

[0049] Specifically, in step S7, the running data statistical results are stored in the HBase data source. First, a Hive external table corresponding to the HBase result table needs to be created, and then the statistical result data is stored in the HBase database using the Hive external table.

[0050] In this embodiment, the data statistics task registration information is to register the task information to be run by operating the Web interface or the built-in database refresh script, wherein the business statistics type of the task must be a business type that already exists in the statistics type definition table.

[0051] like Figure 3As shown, this embodiment also provides a distributed statistical system for power grid operation data that supports custom functions. The distributed statistical system for power grid operation data in this embodiment includes a task scheduling center and an executor. The scheduling center and the executor cluster communicate remotely through RPC. When the executor starts, the address information of the executor will be registered with the task scheduling center; the task scheduling center is used to register data statistical task information and periodically trigger the scheduling execution of tasks based on the Cron configuration of the task; the executor is used to receive parameter information of the data statistical task, dynamically generate the statistical SQL corresponding to the task according to the task parameter information, dynamically create the UDF function, and then submit the statistical SQL corresponding to the task to Yarn to run the statistical SQL; the data in the HBase table is read by Hive external table. This method can give full play to the OLAP characteristics of Hive in data analysis and processing and the OLTP characteristics of HBase in real-time reading and writing of massive data, and can ensure that the system has good horizontal scalability, operation reliability and performance stability.

[0052] The task scheduling center is responsible for managing task scheduling information and initiating scheduling requests based on the task scheduling configuration. The scheduling center does not support the operation of business code; the executor is responsible for receiving task requests, then dynamically generating statistical SQL and executing the task logic based on the Yarn resource scheduling framework; the executor cluster carries the operation of specific tasks in a load-sharing manner, which can ensure high availability and high performance of task execution.

[0053] The task scheduling center includes: a task management module, a log management module, an executor management module, an operation report management module and other functions. The task management module is used to provide task registration services and task scheduling services. The log management module is used to provide task log query services. The executor management module is used to provide executor registration information management services. The task scheduling center also provides an operation report management module to provide query and display functions for scheduling reports, operation reports, success ratio charts, etc.

Claims

1. A distributed statistical method for power grid operation data supporting custom functions, characterized in that: The following steps are involved: S1, develop corresponding UDF functions according to the functional requirements of power grid operation data statistics business; S2: Initialize SQL templates and UDF function creation statements corresponding to various business types; S3, complete the registration of statistical task information through the front-end interface of the task scheduling center; S4: The scheduling center triggers the task scheduling according to the Cron configuration information of the task and sends a task scheduling request to the executor through RPC; S5: The executor receives and parses the task parameters and creates a corresponding Hive mapping table for the HBase measurement table based on the table name information. S6: The executor dynamically generates the statistical SQL statement corresponding to the statistical task based on the task parameters, executes the UDF function to create the statement, and then submits the statistical SQL statement to the Yarn resource scheduling framework to execute the statistical task; S7: The power grid operation data statistics are written into the HBase result table. S8, the executor completes the data statistics task and returns the running results.

2. A distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: Based on the specific needs of running data statistics business, determine whether the built-in functions of the existing data warehouse meet the data statistics business needs. If not, you need to develop corresponding UDF functions based on the statistical business logic to meet the statistical business needs.

3. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The initialization of configuration information corresponding to various business types also includes: creating an SQL template for the statistical result table corresponding to the data statistical task.

4. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The task information includes the table name of the data statistics, business type, task type and task parameters.

5. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The task scheduling center and the executor use RPC to transfer task parameter information and call task execution functions.

6. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The task scheduling center uses a fragmented broadcast routing strategy to achieve distributed collaborative execution of data statistics tasks.

7. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The Hive mapping table is a Hive external table created for a data table in HBase, and is used to implement a function of statistically analyzing massive data in HBase using the OLAP feature of Hive SQL.

8. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The UDF function is a user-defined function developed to meet specific business requirements of the power grid. Before the statistical task is executed, the UDF function corresponding to the task needs to be dynamically created first.

9. The distributed statistical method for power grid operation data supporting user-defined functions according to claim 1, characterized in that: The process of writing the statistical results of power grid operation data into the HBase result table is as follows: first, create a Hive external table corresponding to the HBase result table, and then use the Hive external table to implement the function of storing the statistical result data into HBase.

10. A distributed statistical system for power grid operation data supporting custom functions, characterized in that: include: The task scheduling center and several executors form an executor cluster. The scheduling center and the executor cluster communicate remotely via RPC. When an executor starts, it registers its address information with the task scheduling center. The task scheduling center is used to register data statistics task information and periodically trigger the scheduling and execution of tasks based on the task's Cron configuration. The executor is used to receive parameter information of the data statistics task, dynamically generate the statistical SQL corresponding to the task based on the task parameter information, dynamically create a UDF function, and then submit the statistical SQL corresponding to the task to Yarn to run the statistical SQL. Data in the HBase table is read through the Hive external table. The task scheduling center includes: a task management module, a log management module, an executor management module and an operation report management module. The task management module is used to provide task registration services and task scheduling services, the log management module is used to provide task log query services, and the executor management module is used to provide executor registration information management services. The task scheduling center also provides an operation report management module to provide query and display functions for scheduling reports, operation reports, and success ratio charts.

Images