A method for analyzing log data of power system
By analyzing database log data and building a life cycle map, the problem of being unable to analyze the purpose of database tables in the existing technology is solved, and detailed analysis and optimization of database tables and fields is realized, and optimization suggestions for power operation and maintenance are provided.
Patent Information
- Application Number
- CN202111392139.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-11-19
- Publication Date
- 2024-11-26
- Estimated Expiration
- 2041-11-19
AI Technical Summary
The existing technology cannot accurately analyze the uses of database SQL scripts and tables, and lacks statistics on the number and frequency of new additions, modifications, queries, and deletion operations of database tables, resulting in the inability to accurately judge the use of database tables.
By collecting database log data, using the SQL parsing engine to parse SQL script information, traverse the abstract syntax tree to judge the attributes and operation types of data fields, combine the dictionary table to obtain the life cycle, activity trajectory and operation frequency of the database table, build a life cycle map, and analyze The purpose of database tables and optimize queries if necessary.
It realizes the use division of database tables and fields, provides power operation and maintenance optimization suggestions through log analysis, and improves SQL query efficiency.
Smart Images

Figure CN114328573B_ABST
Abstract
Description
Technical Field
[0001] The invention relates to the technical field of databases, and in particular to a method for analyzing log data of an electric power system. Background Art
[0002] At present, the log analysis of the power system relies on native database audit and review records, and there is no detailed analysis of the database SQL scripts executed historically. For example, for SQL statements, it is impossible to accurately define whether they belong to the data query language (DQL), data manipulation language (DML) or data definition language (DDL). There is also a lack of statistics on the number of executions of adding, modifying, querying and deleting database tables, whether the fields are used as results or filtering conditions, the number of executions of adding, modifying, querying and deleting fields, the execution frequency, etc., and it is even more impossible to accurately derive the purpose of the database table. Summary of the invention
[0003] The purpose of the present invention is to overcome the shortcomings of the prior art and provide a method for analyzing log data of an electric power system.
[0004] The purpose of the present invention is achieved through the following technical solutions:
[0005] A method for analyzing power system log data comprises the following steps:
[0006] Step 1: Collect database log data to obtain SQL script execution information and database execution log information;
[0007] Step 2: parse the SQL script information and database execution log information through the SQL parsing engine to obtain the language type to which the database table and data field belong;
[0008] Step 3, by traversing the abstract syntax tree, combined with the language type to which the database table and data field belong, summarize the operation type of the database table and data field, and at the same time determine the attributes of the data field, the attributes of the data field are conditions or results;
[0009] Step 4, obtain the life cycle of the database table, the activity track of the database table and the operation frequency of the database table through the set dictionary table, the database table, the language type to which the data field belongs and the attributes of the data field, and determine the purpose of the database table.
[0010] Preferably, the language type to which the database table and data field belong is DQL, DML or DDL.
[0011] Preferably, the operation types of the database tables and data fields include adding, modifying, querying and deleting.
[0012] Preferably, in step 3, the abstract syntax tree is obtained by manually defining a vistor tool class in the druid parsing engine.
[0013] Preferably, in step 4, the life cycle of the database table, the activity track of the database table and the operation frequency of the database table are obtained through the set dictionary table, the database table, the language type to which the data field belongs and the attributes of the data field, and the data information of the database table is also determined, and a life cycle map is constructed through the data information.
[0014] Preferably, the step 4 also records characteristic data of the life cycle map. For discrete characteristic data, the data whose difference from the average value exceeds a set threshold is recorded; for continuous characteristic data, the inflection point of the continuous data is recorded.
[0015] Preferably, for continuous characteristic data, changes in values within a set period of the continuous data are also recorded. If the changes in values within the set period exceed a set threshold, the set period value is judged to be abnormal, and the set period is recorded as an abnormal period.
[0016] Preferably, after determining the purpose of the database table in step 4, the database table is analyzed and optimized. The analysis and optimization specifically includes: obtaining the time for executing the SQL query of the database table. If the time for executing the SQL query exceeds the set time, first obtaining the fields of the SQL query, and then adding an index to the queried fields.
[0017] The beneficial effects of the present invention are as follows: the analysis method of the power system log data parses SQL, can divide the uses of database tables and data fields, and through log analysis of the application program that executes SQL, execution time, execution times, memory read times, etc., can mine various SQL related details, and provide suggestions and directions for power operation and maintenance optimization. BRIEF DESCRIPTION OF THE DRAWINGS
[0018] Figure 1 is a flow chart of the present invention;
[0019] Figure 2 It is the first life cycle map of the present invention;
[0020] Figure 3 It is the second life cycle diagram of the present invention;
[0021] Figure 4 It is the third life cycle diagram of the present invention;
[0022] Figure 5 This is the fourth life cycle diagram of the present invention. DETAILED DESCRIPTION
[0023] The present invention is further described below in conjunction with the accompanying drawings and embodiments.
[0024] Example:
[0025] A method for analyzing power system log data, such as Figure 1 As shown, the following steps are included:
[0026] Step 1: Collect database log data to obtain SQL script execution information and database execution log information;
[0027] Step 2: parse the SQL script information and database execution log information through the SQL parsing engine to obtain the language type to which the database table and data field belong;
[0028] Step 3, by traversing the abstract syntax tree, combined with the language type to which the database table and data field belong, summarize the operation type of the database table and data field, and at the same time determine the attributes of the data field, the attributes of the data field are conditions or results;
[0029] For example, when traversing SQLColumnDefinition objects, its parent object is SQLCreateTableStatement, and the getName() method obtains the content EMPLOYEE_ID. Then I can conclude that EMPLOYEE_ID is used as a table creation field in the DDL language, the operation is to add a new field, and the usage type is result.
[0030] For example: traverse to the SQLPropertyExpr object, its parent object is SQLUpdateSetItem, getName() method obtains the content PHONE_NUM, we conclude that the PHONE_NUM phone number field is DML language, modify data operations, the purpose type is result; its parent object is SQLInsertStatement, which is a new data field for DML; its parent object is SQLAlterTableDropColumnItem, which is a deleted field for DDL operations; its parent object is the field purpose in SQLInListExpr as a condition.
[0031] Step 4, obtain the life cycle of the database table, the activity track of the database table and the operation frequency of the database table through the set dictionary table, the database table, the language type to which the data field belongs and the attributes of the data field, and determine the purpose of the database table.
[0032] The database tables and data fields belong to the language type of DQL, DML or DDL. The operation types of the database tables and data fields include adding, modifying, querying and deleting.
[0033] In the step 3, the abstract syntax tree is obtained by manually defining a vistor tool class in the druid parsing engine.
[0034] Get the SQLStatement object and use the instanceof method to find out which subclass instance the SQLStatement object belongs to. The subclass instances include SQLUpdateStatement, SQLDeleteStatement, SQLInsertStatement, SQLMergeStatement, SQLSelectStatement, SQLCreateStatement, SQLAlterStatement, SQLDropStatement, and SQLDDLStatement. You can then know which of the three languages, DQL, DML, and DDL, the SQL script belongs to.
[0035] For example, a sql to create a statement:
[0036] create table C##TEST2.EMPLOYEE_TEST(
[0037] EMPLOYEE_ID NUMBER(6,0)NOT NULL ENABLE,
[0038] EMPLOYEE_NAME VARCHAR2(20BYTE),
[0039] PHONE_NUM VARCHAR2(20BYTE) )
[0041] How to use: List <sqlstatement>stmtList=SQLUtils.parseStatements(sql,"ORACLE");
[0042] SQLStatement sqlStatement=stmtList.get(0);
[0043] if(sqlStatement instanceof SQLCreateStatement){
[0044] / / This SQL is the data definition language DDL language
[0045] In step 4, the life cycle of the database table, the activity track of the database table and the operation frequency of the database table are obtained through the set dictionary table, the database table, the language type to which the data field belongs and the attributes of the data field, and the data information of the database table is also determined, and a life cycle map is constructed through the data information.
[0046] For example, for the EMPLOYEE_TEST table, Figure 2 As shown in the figure, we can see the life cycle of the table and the activity of table modification. Figure 3 As shown in the figure, we find that the number of executions of T_LOG is consistent within a certain period of time. We can infer that this table is a scheduled task.
[0047] like Figure 4 As shown in the figure, observing the operations involved in the department table DEPARTMENT, it is found that most of the DQL queries involved in this table are combined with other tables for query. After the early log DML operations, there are DQL operations afterwards, and the number of queries is much greater than that of other tables.
[0048] Select
[0049] e.EMPLOYEE_ID,e.EMPLOYEE_NAME,e.department_id,d.DEPARTMENT_NAME
[0050] from EMPLOYEE e LEFT OUTER JOIN DEPARTMENT d ON
[0051] d.department_id=e.department_id
[0052] The DML operation modifies the usage type of the data log statistics field, which can help you discover business concerns, understand which business attributes will be modified, and which business attributes are often used as filtering conditions.
[0053] For example: Analyze the following modification statement
[0054] a)update EMPLOYEE_TEST set PHONE_NUM='13523456782'
[0055] where EMPLOYEE_ID = '12';
[0056] b)update EMPLOYEE_TEST set email='hanhuan@dameng.com'
[0057] where EMPLOYEE_ID = '3122';
[0058] c)update EMPLOYEE_TEST set salary=16436
[0059] where EMPLOYEE_ID = '3122';
[0060]
[0061]
[0062] Observe the above data, the EMPLOYEE_TEST employee information table changes data, often the primary key or unique identifier field as the modification condition, the change field - mobile phone number, email address, salary often business scenarios need to pay attention to the change point, other fields IDENTITY_CARD identity card number has no modification operation record. Let's look at their execution count statistics, such as Figure 5 As shown, the number of executions for the employee number is greater than or equal to the number of other executions, and the salary modification time points are very concentrated, which may be an overall salary adjustment of the company.
[0063] Track the operation logs of some master data tables to see in which time period the table and field operations are concentrated. If the time period is relatively concentrated, consider adding resources during the concentrated time. Categorize which tables are relatively frequently operated. These tables are likely to be master data tables. You can also trace back some slow query tables to assist in analyzing the causes of slow queries and optimize queries. For example, adding indexes to the conditional fields of slow queries can improve query efficiency.
[0064] The step 4 also records the characteristic data of the life cycle map. For discrete characteristic data, the data whose difference from the average value exceeds a set threshold is recorded; for continuous characteristic data, the inflection point of the continuous data is recorded.
[0065] For continuous feature data, the change of the value within the set period of the continuous data is also recorded. If the change of the value within the set period exceeds the set threshold, the set period value is judged to be abnormal, and the set period is recorded as an abnormal period.
[0066] After determining the purpose of the database table in step 4, the database table is analyzed and optimized. The analysis and optimization specifically includes: obtaining the time for executing the SQL query of the database table. If the time for executing the SQL query exceeds the set time, first obtaining the field of the SQL query, and then adding an index to the queried field.
[0067] For example, during the analysis process, we found that the execution time ELAPSED_TIME of a certain SQL query statement was very long and took more than 5 minutes. We can consider it a slow query. After parsing it, we find that the IDENTITY_CARD field is the condition field for the query. We recommend adding a unique index to the IDENTITY_CARD ID card number to improve efficiency.
[0068] select e.employee_id,e.employee_name
[0069] ,e.job_id,e.salary,b.job_title,b.min_salary,b.max_salary
[0070] from employee
[0071] left join job b on e.job_id=b.job_id
[0072] where IDENTITY_CARD='111111199210121234'
[0073] The above-described embodiment is only a preferred solution of the present invention and does not limit the present invention in any form. There are other variations and modifications without exceeding the technical solution described in the claims.< / sqlstatement>
Claims
1. A method for analyzing power system log data, characterized in that: The following steps are involved: Step 1: Collect database log data to obtain SQL script execution information and database execution log information; Step 2: parse the SQL script information and database execution log information through the SQL parsing engine to obtain the language type to which the database table and data field belong; Step 3, by traversing the abstract syntax tree, combined with the language type to which the database table and data field belong, summarize the operation type of the database table and data field, and at the same time determine the attributes of the data field, the attributes of the data field are conditions or results; Step 4, obtaining the life cycle of the database table, the activity track of the database table and the operation frequency of the database table through the set dictionary table, the database table, the language type to which the data field belongs and the attributes of the data field, and determining the purpose of the database table; In the step 4, the life cycle of the database table, the activity track of the database table and the operation frequency of the database table are obtained through the set dictionary table, the database table, the language type to which the data field belongs and the attribute of the data field, and the data information of the database table is also determined, and the life cycle map is constructed through the data information; In the step 4, the characteristic data of the life cycle map is also recorded. For discrete characteristic data, the data whose difference from the average value exceeds the set threshold is recorded; for continuous characteristic data, the inflection point of the continuous data is recorded; For continuous feature data, the change of the value within the set period of the continuous data is also recorded. If the change of the value within the set period exceeds the set threshold, the set period value is judged to be abnormal, and the set period is recorded as an abnormal period.
2. The method for analyzing power system log data according to claim 1, characterized in that: The database tables and data fields belong to the language type of DQL, DML or DDL.
3. The method for analyzing power system log data according to claim 1, characterized in that: The types of operations on the database tables and data fields include adding, modifying, querying and deleting.
4. The method for analyzing power system log data according to claim 1, characterized in that: In the step 3, the abstract syntax tree is obtained by manually defining a vistor tool class in the druid parsing engine.
5. The method for analyzing power system log data according to claim 1, characterized in that: After determining the purpose of the database table in step 4, the database table is analyzed and optimized. The analysis and optimization specifically includes: obtaining the time for executing the SQL query of the database table. If the time for executing the SQL query exceeds the set time, first obtaining the field of the SQL query, and then adding an index to the queried field.
