CN111782489A - Data table monitoring method and device - Google Patents
Data table monitoring method and device Download PDFInfo
- Publication number
- CN111782489A CN111782489A CN202010684130.7A CN202010684130A CN111782489A CN 111782489 A CN111782489 A CN 111782489A CN 202010684130 A CN202010684130 A CN 202010684130A CN 111782489 A CN111782489 A CN 111782489A
- Authority
- CN
- China
- Prior art keywords
- data
- monitoring
- expansion
- data table
- inclination
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000012544 monitoring process Methods 0.000 title claims abstract description 129
- 238000000034 method Methods 0.000 title claims abstract description 53
- 238000013515 script Methods 0.000 claims abstract description 103
- 238000001514 detection method Methods 0.000 claims abstract description 78
- 238000004458 analytical method Methods 0.000 claims description 8
- 238000007781 pre-processing Methods 0.000 claims description 8
- 238000012806 monitoring device Methods 0.000 abstract 1
- 238000003860 storage Methods 0.000 description 17
- 238000012423 maintenance Methods 0.000 description 11
- 238000004590 computer program Methods 0.000 description 8
- 230000008569 process Effects 0.000 description 8
- 230000009286 beneficial effect Effects 0.000 description 5
- 238000004140 cleaning Methods 0.000 description 5
- 238000010586 diagram Methods 0.000 description 5
- 238000004364 calculation method Methods 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 230000003287 optical effect Effects 0.000 description 3
- 230000005540 biological transmission Effects 0.000 description 2
- 230000008859 change Effects 0.000 description 2
- 238000009826 distribution Methods 0.000 description 2
- 239000013307 optical fiber Substances 0.000 description 2
- 230000000644 propagated effect Effects 0.000 description 2
- 238000009825 accumulation Methods 0.000 description 1
- 238000003491 array Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000010339 dilation Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000005457 optimization Methods 0.000 description 1
- 230000002093 peripheral effect Effects 0.000 description 1
- 239000004065 semiconductor Substances 0.000 description 1
- 230000007480 spreading Effects 0.000 description 1
- 238000003892 spreading Methods 0.000 description 1
- 230000001960 triggered effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/32—Monitoring with visual or acoustical indication of the functioning of the machine
- G06F11/324—Display of status information
- G06F11/327—Alarm or error message display
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
- G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2358—Change logging, detection, and notification
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
- G06F2201/80—Database-specific techniques
Abstract
The application provides a data table monitoring method and a data table monitoring device, wherein the method comprises the following steps: executing the monitoring script; if the monitoring script is executed successfully, the monitoring script is made to execute the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result; and if the execution of the monitoring script fails, generating first prompt information of the execution failure and sending the first prompt information to a data sending interface so that the data sending interface sends the first prompt information to the terminal equipment of the user. The monitoring script is used for performing table expansion and data inclination detection on the data warehouse, so that automatic monitoring on the data warehouse is realized, a user is informed in time when the execution of the monitoring script fails, the user can manually check the condition of the data warehouse, and the reason of the execution failure of the script can be checked.
Description
Technical Field
The present application relates to information processing technologies in the field of data warehouses, and in particular, to a method and an apparatus for monitoring a data table, an electronic device, and a computer-readable storage medium.
Background
Currently, in the field of warehouse construction, most enterprises use a scheduling system to complete automatic updating of data and storage processes. However, in the actual development process, because the experience and level of the developer are different, sometimes the developer will delete the data in a delete mode; or the distribution keys of the watch are not reasonably arranged, so that the two conditions of table expansion and table inclination are easily generated respectively. In both cases, the performance of the bins is not significantly affected at an early stage, and as the expansion/inclination ratio becomes higher, the performance of the bins drops sharply, resulting in accumulation and delay of the operation of the entire data warehouse. Based on this, there is a need to develop a tool to monitor this situation.
Disclosure of Invention
The application aims to provide a data table monitoring method and device, electronic equipment and a computer readable storage medium, and solves the problem of monitoring table expansion and table inclination in a data warehouse.
The purpose of the application is realized by adopting the following technical scheme:
in a first aspect, the present application provides a method for monitoring a data table, the method including: executing the monitoring script; if the monitoring script is executed successfully, the monitoring script is made to execute the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result; and if the execution of the monitoring script fails, generating first prompt information of the execution failure and sending the first prompt information to a data sending interface so that the data sending interface sends the first prompt information to the terminal equipment of the user. The technical scheme has the advantages that the monitoring script is used for performing table expansion and data inclination detection on the data warehouse, so that automatic monitoring on the data warehouse is realized, a user is timely notified when the monitoring script fails to execute, the user can manually check the condition of the data warehouse, and the reason for the script failure can be checked.
In some possible implementations, the monitoring script includes a first monitoring script and a second monitoring script; the first monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion exists in the plurality of data tables or not to obtain a first detection result; the second monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with data inclination exists in the plurality of data tables or not, and acquiring a second detection result; the method further comprises the following steps: and triggering the first monitoring script through a first timing task, triggering the second monitoring script through a second timing task, wherein the interval duration of the adjacent tasks of the first timing task is less than that of the second timing task. The data slope monitoring script is short in execution time and small in occupied resource, the table expansion monitoring script is long in execution time and large in occupied resource, the technical scheme has the beneficial effects that different timing tasks are set to trigger the table expansion and data slope monitoring scripts respectively, on one hand, the condition of data slope is found in time, and on the other hand, the table expansion monitoring scripts are prevented from occupying a large amount of resources. For example, data skewing may be set to perform a check every five minutes, while table inflation may be set to perform a check once a week.
In some possible implementations, the detecting whether there is a data table with table inflation in the plurality of data tables to obtain a first detection result includes: detecting whether the actual occupied space of the data table exceeds the preset occupied space of the data table or not aiming at each data table in the plurality of data tables; and if the actual occupied space of at least one data table exceeds the preset occupied space of the data table, determining that the first detection result is the data table with the occurrence of table expansion. The technical scheme has the beneficial effects that the actual occupied space of the data table is compared with the preset occupied space of the data table, so that whether the data table expands or not is determined.
In some possible implementations, the detecting whether there is a data table with table inflation in the plurality of data tables to obtain a first detection result includes: and executing a vacuum analysis command on the plurality of data tables for preprocessing, detecting whether the data tables with table expansion exist or not, and acquiring the first detection result. The technical scheme has the beneficial effect that the data table with table expansion is detected by using the vacuum analysis command.
In some possible implementations, the method further includes: and executing a vacuum analysis command on the data table with the table expansion to automatically clean. The technical scheme has the beneficial effect that the data table of the expansion of the generation table is automatically cleaned by using a va cuum analyze command.
In some possible implementations, the detecting whether there is a data table with data skew in the plurality of data tables to obtain a second detection result includes: for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition, the predetermined condition including at least one of: the inclination rate is greater than the preset inclination rate, and the data volume is greater than the preset byte number; and if at least one data table meeting the preset condition exists, determining that the second detection result is the data table with the data inclination. The technical scheme has the beneficial effect that whether the data table is inclined or not is detected through the inclination rate and/or the data quantity.
In some possible implementations, if there is at least one data table that satisfies the predetermined condition, the determining that the second detection result is the data table with data skew includes: if the data table meets the preset condition, putting the data table into a result table; detecting whether the result table is empty; and if not, determining that the second detection result is that the data table with the data inclination exists. The technical scheme has the advantages that the data table with the data inclination is stored through the result table, and whether the result table is empty or not is detected to determine whether the data inclination condition exists or not.
In some possible implementations, the method further includes: if the detection result is that the data table with the table expansion and/or the data inclination exists, second prompt information of the data warehouse with the table expansion and/or the data inclination is generated; and sending the second prompt message to the data sending interface so that the data sending interface sends the second prompt message to the terminal equipment of the user. The technical scheme has the advantages that after the condition that the table expansion and/or the data inclination occur to the data warehouse is detected, the prompt information is sent through the data sending interface, the user is timely reminded, developers can know the current table expansion and the data inclination condition of the data warehouse more timely, the developers can conveniently process the data table which expands and the table which inclines in the data warehouse in the first time, the problem of low performance is timely solved, and the data warehouse is guaranteed to operate stably and efficiently.
In some possible implementations, if the detection result is that there is a data table with table expansion and/or data skew, generating second prompt information of the data warehouse with table expansion and/or data skew includes: if the detection result is that the data table with table expansion exists, second prompt information of the data warehouse with table expansion is generated, wherein the second prompt information comprises the data table with table expansion; if the detection result is that the data table with the data inclination exists, generating second prompt information of the data inclination of the data warehouse, wherein the second prompt information comprises the data table with the data inclination; and if the detection result is that the data table with the table expansion and the data inclination exists, generating second prompt information of the data warehouse with the table expansion and the data inclination, wherein the second prompt information comprises the data table with the table expansion and the data table with the data inclination. The technical scheme has the advantages that the three prompt messages are used for prompting the user corresponding to the three conditions of table expansion and/or data inclination, and the prompt messages contain the data table information of the table expansion and/or the data inclination, so that the user can intuitively know which data table occurs which condition through the prompt messages.
In a second aspect, the present application provides a data table monitoring apparatus, the apparatus comprising: the script execution module is used for executing the monitoring script; an execution success module, configured to, if the monitoring script is successfully executed, enable the monitoring script to perform the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result; and the execution failure module is used for generating first prompt information of execution failure and sending the first prompt information to a data sending interface if the execution of the monitoring script fails, so that the data sending interface sends the first prompt information to the terminal equipment of the user.
In some possible implementations, the monitoring script includes a first monitoring script and a second monitoring script; the first monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion exists in the plurality of data tables or not to obtain a first detection result; the second monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with data inclination exists in the plurality of data tables or not, and acquiring a second detection result; the device further comprises a timing trigger module, wherein the timing trigger module is used for triggering the first monitoring script through a first timing task and triggering the second monitoring script through a second timing task, and the interval duration of adjacent tasks of the first timing task is shorter than that of the second timing task.
In some possible implementations, the execution success module is further configured to: detecting whether the actual occupied space of the data table exceeds the preset occupied space of the data table or not aiming at each data table in the plurality of data tables; and if the actual occupied space of at least one data table exceeds the preset occupied space of the data table, determining that the first detection result is the data table with the occurrence of table expansion.
In some possible implementation manners, the execution success module is further configured to perform preprocessing on the plurality of data tables to execute the vacuum analyze command, detect whether there is a data table with table expansion, and obtain the first detection result.
In some possible implementations, the execution success module is further configured to execute a vacuum analyze command on the data table with the table expansion, so as to perform automatic cleaning.
In some possible implementations, the execution success module is further configured to: for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition, the predetermined condition including at least one of: the inclination rate is greater than the preset inclination rate, and the data volume is greater than the preset byte number; and if at least one data table meeting the preset condition exists, determining that the second detection result is the data table with the data inclination.
In some possible implementations, the execution success module is further configured to: if the data table meets the preset condition, putting the data table into a result table; detecting whether the result table is empty; and if not, determining that the second detection result is that the data table with the data inclination exists.
In some possible implementations, the execution success module is further configured to: if the detection result is that the data table with the table expansion and/or the data inclination exists, second prompt information of the data warehouse with the table expansion and/or the data inclination is generated; and sending the second prompt message to the data sending interface so that the data sending interface sends the second prompt message to the terminal equipment of the user.
In some possible implementations, the execution success module is further configured to: if the detection result is that the data table with table expansion exists, second prompt information of the data warehouse with table expansion is generated, wherein the second prompt information comprises the data table with table expansion; if the detection result is that the data table with the data inclination exists, generating second prompt information of the data inclination of the data warehouse, wherein the second prompt information comprises the data table with the data inclination; and if the detection result is that the data table with the table expansion and the data inclination exists, generating second prompt information of the data warehouse with the table expansion and the data inclination, wherein the second prompt information comprises the data table with the table expansion and the data table with the data inclination.
In a third aspect, the present application provides an electronic device comprising a memory and a processor, the memory storing a computer program, the processor implementing the steps of any of the above methods when executing the computer program.
In a fourth aspect, the present application provides a computer-readable storage medium storing a computer program which, when executed by a processor, implements the steps of any of the methods described above.
Drawings
The present application is further described below with reference to the drawings and examples.
FIG. 1 is a schematic flow chart diagram illustrating a method for monitoring a data table according to an embodiment of the present disclosure;
FIG. 2 is a schematic flow chart illustrating a method for detecting data skew according to an embodiment of the present disclosure;
FIG. 3 is a schematic flow chart illustrating a process for prompting a user for table inflation and/or data skew according to an embodiment of the present disclosure;
fig. 4 is a schematic flowchart of generating a second prompt message according to an embodiment of the present application;
FIG. 5 is a schematic flow chart diagram illustrating a method for monitoring a data table according to an embodiment of the present application;
FIG. 6 is a schematic structural diagram of a data table monitoring apparatus according to an embodiment of the present application;
fig. 7 is a schematic structural diagram of an electronic device according to an embodiment of the present application;
fig. 8 is a schematic structural diagram of a program product for implementing a data table monitoring method according to an embodiment of the present application.
Detailed Description
In the following description, for purposes of explanation and not limitation, specific details are set forth, such as particular system structures, techniques, etc. in order to provide a thorough understanding of the present application. It will be apparent, however, to one skilled in the art that the present invention may be practiced in other embodiments that depart from these specific details. In other instances, detailed descriptions of well-known systems, devices, circuits, and methods are omitted so as not to obscure the description of the present invention with unnecessary detail.
In order to explain the technical means of the present invention, the following description will be given by way of specific examples.
The table expansion and data skewing are first explained as follows.
Table inflation means that the space of the file system occupied by the data and the index of the table is increasing without a large change in the amount of effective data. A direct trigger for table inflation is a large change in the data in the table, such as a large range of UPDATEs, a large number of DELETE operations, and so forth.
Before introducing the data skew, the concept of nodes is first introduced. The nodes are divided into main nodes and sub-nodes. The main node is a control center and an external access point of the whole system and is responsible for receiving a user SQL request, generating a query plan by the SQL and carrying out parallel processing optimization, then distributing the query plan to all the sub-nodes for parallel processing, coordinating and organizing each sub-node to carry out parallel processing step by step according to the query plan, finally obtaining a calculation result of the sub-node and returning the calculation result to the client; the subnodes are operation nodes for executing parallel tasks in the data warehouse, receive the instructions of the main nodes and perform calculation, so the sum of the calculation performance of all the subnodes is the performance of the whole cluster, and the processing performance and the storage capacity of the cluster can be increased by adding the subnodes.
For distributed bins, the data should be evenly distributed across the nodes, thus spreading the original centralized I/O pressure across multiple nodes to improve performance by a factor of two. If the data is not uniformly distributed to each node but is concentrated on a certain node, the I/O pressure is concentrated on the certain node, the performance is sharply reduced, and the condition that the data is not uniformly distributed is called data inclination. Data skew is generally caused by a mis-selection of distribution keys. The slope rate refers to the maximum child node data amount/average node data amount.
Referring to fig. 1, an embodiment of the present application provides a data table monitoring method, which is applied to the field of data warehouses, such as greenply databases or other databases. The method includes steps S101 to S103.
Step S101: the monitoring script is executed. Wherein the monitoring script may include a first monitoring script and a second monitoring script. The first monitoring script is used for monitoring table inflation, and the second monitoring script is used for monitoring data inclination.
The first monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; and detecting whether a data table with table expansion exists in the plurality of data tables to obtain a first detection result.
The second monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; and detecting whether a data table with data inclination exists in the plurality of data tables or not, and acquiring a second detection result.
The execution time of the data inclination monitoring script is short, the occupied resources are less, the execution time of the table expansion monitoring script is long, the occupied resources are more, in some possible implementation modes, different timing tasks can be set respectively to trigger the monitoring scripts of the table expansion and the data inclination, on one hand, the condition of the data inclination is found in time, and on the other hand, the table expansion monitoring script is prevented from occupying a large number of resources. For example, data skewing may be set to perform a check every five minutes, while table inflation may be set to perform a check once a week. The method further comprises the following steps: and triggering the first monitoring script through a first timing task, triggering the second monitoring script through a second timing task, wherein the interval duration of the adjacent tasks of the first timing task is less than that of the second timing task. In a specific implementation, the second monitoring script may be set to be executed periodically once every monday and night.
Step S102: if the monitoring script is executed successfully, the monitoring script is made to execute the following operations: capturing a plurality of data tables according to the data dictionary; and detecting whether the data tables with table expansion and/or data inclination exist in the plurality of data tables to obtain a detection result.
Step S103: and if the execution of the monitoring script fails, generating first prompt information of the execution failure and sending the first prompt information to a data sending interface so that the data sending interface sends the first prompt information to the terminal equipment of the user. If the execution of the monitoring script fails, directly throwing out the message of the early warning script execution failure, pushing the prompt information to developers, and directly finishing the execution.
According to the embodiment of the application, the monitoring script is used for performing table expansion and data inclination detection on the data warehouse, so that automatic monitoring on the data warehouse is realized, a user is timely notified when the execution of the monitoring script fails, the user can manually check the condition of the data warehouse, and the reason for the execution failure of the script can be checked.
In some possible implementations, the actual occupancy of the data table may be compared to the predetermined occupancy of the data table, thereby determining whether table inflation of the data table has occurred. The step of detecting whether there is a data table with table expansion in the plurality of data tables to obtain a first detection result may include: detecting whether the actual occupied space of the data table exceeds the preset occupied space of the data table or not aiming at each data table in the plurality of data tables; and if the actual occupied space of at least one data table exceeds the preset occupied space of the data table, determining that the first detection result is the data table with the occurrence of table expansion. And configuring a preset occupation space of each data table, wherein the preset occupation space is a preset occupation space.
In some possible implementations, the vacuum analyze command may be used to detect a data table where table inflation occurs. The step of detecting whether there is a data table with table expansion in the plurality of data tables to obtain a first detection result may include: and executing a vacuum analysis command on the plurality of data tables for preprocessing, detecting whether the data tables with table expansion exist or not, and acquiring the first detection result. The function of the vacuum analyze command is to update the statistics so that the optimizer can select a better solution to execute the sql statement. Statistics collection and updating is important to system performance, usually by way of manual or timed tasks.
In some possible implementations, the data table generating the table inflation may be automatically cleared using the vacuum analyze command. The method may further comprise: and executing a vacuu m analyze command on the data table with the table expansion to automatically clean.
In some possible implementations, whether a data table is skewed may be detected by a skew rate and/or a data amount. The step of detecting whether there is a data table with data skew in the plurality of data tables to obtain a second detection result may include: for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition, the predetermined condition including at least one of: the inclination rate is greater than the preset inclination rate, and the data volume is greater than the preset byte number; and if at least one data table meeting the preset condition exists, determining that the second detection result is the data table with the data inclination. The predetermined slope rate is, for example, 3, and the predetermined number of bytes is, for example, 100M.
In some possible implementation manners, a data table in which data skew occurs may be stored through a result table, and whether the result table is empty is detected to determine whether a situation in which data skew occurs exists. The step of determining that the second detection result is the presence of the data table with data skew if at least one data table meets the predetermined condition may include: if the data table meets the preset condition, putting the data table into a result table; detecting whether the result table is empty; and if not, determining that the second detection result is that the data table with the data inclination exists. If data exists in the result table, the situation that the data is inclined is judged. The data written in the result table may include, for example, a table name, a tilt rate, and a data amount.
In a specific implementation, referring to fig. 2, the step of detecting whether there is a data table with data skew in the plurality of data tables and acquiring the second detection result may include steps S201 to S204.
Step S201: for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition.
Step S202: and if the data table meets the preset condition, putting the data table into a result table.
Step S203: and detecting whether the result table is empty or not.
Step S204: and if not, determining that the second detection result is that the data table with the data inclination exists.
In some possible implementation modes, after the condition that the table expansion and/or the data inclination occur to the data warehouse is detected, the prompt information can be sent through the data sending interface to prompt the user in time, so that the developer can know the current table expansion and the data inclination condition of the data warehouse more in time, the developer can process the data table which expands and the table which inclines in time, the problem of low performance is solved in time, and the stable and efficient operation of the data warehouse is ensured. Specifically, referring to fig. 3, the method may further include steps S104 to S105.
Step S104: and if the detection result is that the data table with the table expansion and/or the data inclination exists, generating second prompt information of the table expansion and/or the data inclination of the data warehouse.
In some possible implementation manners, the user may be prompted by using three types of prompting information corresponding to three types of situations where table expansion and/or data inclination occur, and the prompting information includes data table information where table expansion and/or data inclination occur, so that the user can intuitively know which data table occurs which situation through the prompting information. Specifically, referring to fig. 4, the step S104 may include steps S301 to S303.
Step S301: and if the detection result is that the data table with the table expansion exists, generating second prompt information of the data warehouse with the table expansion, wherein the second prompt information comprises the data table with the table expansion. The second prompt message is, for example: "prompt information: the details of the expansion table are shown in view gp _ toolkit. gp _ blob _ diag for the xxxxxx data warehouse data dictionary preprocessing vacuum analyze. When the table expansion condition exists in the number bin, the command of vacuum analysis can be executed to automatically clean the expansion table, and the prompt information is pushed to developers after the cleaning is finished.
Step S302: and if the detection result is that the data table with the data inclination exists, generating second prompt information of the data inclination of the data warehouse, wherein the second prompt information comprises the data table with the data inclination. The second prompt message is, for example: "prompt information: xxxxxx data warehouse data dictionary data tilt table details are as follows: table a, slope ratio 5, data volume 200M. "
Step S303: and if the detection result is that the data table with the table expansion and the data inclination exists, generating second prompt information of the data warehouse with the table expansion and the data inclination, wherein the second prompt information comprises the data table with the table expansion and the data table with the data inclination. The second prompt message is, for example: "prompt information: the xxx data warehouse data dictionary preprocess vacuum analyze. inflation table details see view g p _ toolkit. gp _ blob _ diag; xxxxxx data warehouse data dictionary data tilt table details are as follows: table a, slope ratio 5, data volume 200M. "
And if the monitoring script runs successfully but no data in the bins is inclined or the table is expanded, not sending the prompt message.
Step S105: and sending the second prompt message to the data sending interface so that the data sending interface sends the second prompt message to the terminal equipment of the user. And pushing the prompt information to a specified data sending interface, such as a WeChat interface, and pushing the data sheet with the sheet expansion and the data sheet with the data inclination to developers through the operation and maintenance public number. Therefore, relevant developers can receive the relevant information of expansion or data inclination of the data dictionary table of the data warehouse through the operation and maintenance public number and timely process the problems, for example, the developers can manually perform data redistribution operation on the data table with the data inclination.
In a specific implementation, an embodiment of the present application further provides a data table monitoring method, where a monitoring script is triggered by a timing task on a certain day.
When the execution of the monitoring script is successful, the following four cases are divided.
In the first case, the data skew occurs in table A and table B in the bin.
And the monitoring script acquires data dictionary information and captures the inclination/expansion information of the A table and the B table.
And the monitoring script is successfully executed, corresponding prompt information is generated and written into a method, and the prompt information is pushed to the operation and maintenance WeChat interface.
The operation and maintenance public number pushes the information to the developer WeChat to prompt the warning information.
In the second case, there is a table in the bin with data skew and no table expansion.
And the monitoring script acquires the data dictionary information and captures the inclination information generated by the A table.
The monitoring script is executed successfully to generate a "there is data tilt condition in the current bin, please process in time! The prompt information is written into a method, and the prompt information is pushed to the operation and maintenance WeChat interface.
The operation and maintenance public number pushes the information to the developer WeChat to prompt the warning information.
In the third case, there is a table expansion of B table in the bin, and no table data skew.
And the monitoring script acquires data dictionary information and captures the expansion information generated by the B table.
The monitoring script is successfully executed, the expansion table is automatically cleaned, and a prompt message is thrown out after the cleaning is finished: "prompt information: preprocessing a vacuum analysis and expansion table detail view gp _ toolkit. gp _ blob _ diag in a XXXXX data warehouse data dictionary, writing the information into a method, and pushing the prompt information to an operation and maintenance WeChat interface.
The operation and maintenance public number pushes the information to the developer WeChat to prompt the warning information.
In the fourth case, no table expansion/data skew occurs in the bins.
And the monitoring script acquires data dictionary information and judges the condition that no data table has data inclination or table expansion at present.
And the monitoring script is successfully executed, no information is prompted, and the execution is finished.
When the monitoring script fails to execute, no matter whether the table expansion/data inclination condition exists in the current data bin or not, a' table expansion inclination monitoring script fails to operate, please process in time! The prompt information is written into a method and pushed to an operation and maintenance WeChat interface; the operation and maintenance public number pushes the information to the developer WeChat to prompt the alarm information.
Referring to fig. 5, an embodiment of the present application further provides a data table monitoring method, where the method includes steps S401 to S416.
Step S401: and starting.
Step S402: and detecting whether the monitoring script is successfully executed, if so, executing step S403, otherwise, executing step S415.
Step S403: data dictionary information is acquired, and steps S404 and S409 are performed.
Step S404: data tilt detection is performed.
Step S405: and detecting whether data are inclined or not, if so, executing the step S406, otherwise, executing the step S408.
Step S406: and prompting that the table specified by the current bin has a data tilt condition.
Step S407: and pushing the information to a specified WeChat interface.
Step S408: no information is prompted.
Step S409: data dilation detection is performed.
Step S410: and detecting whether table expansion exists, if so, executing the step S411, otherwise, executing the step S413.
Step S411: and performing pretreatment and automatically cleaning the expansion table.
Step S412: and generating prompt information: the details of expansion table of GP courier 38 data dictionary preprocessing vacuum analyze see view GP _ toolkit. Where GP courier 38 is a database identifier.
Step S413: no information is prompted.
Step S414: and sending the information to the developer through the data sending interface. The data transmission interface is, for example, a data transmission interface configured by the operation and maintenance management unit.
Step S415: and sending the information of the execution failure of the monitoring script to the developer.
Step S416: and (6) ending.
Referring to fig. 6, an embodiment of the present application further provides a data table monitoring apparatus, where the apparatus includes: a script execution module 101, configured to execute a monitoring script; an execution success module 102, configured to, if the monitoring script is successfully executed, cause the monitoring script to perform the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result; and the execution failure module 103 is configured to generate first prompt information of the execution failure and send the first prompt information to a data sending interface if the execution of the monitoring script fails, so that the data sending interface sends the first prompt information to a terminal device of a user.
In some possible implementations, the monitoring script includes a first monitoring script and a second monitoring script; the first monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion exists in the plurality of data tables or not to obtain a first detection result; the second monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with data inclination exists in the plurality of data tables or not, and acquiring a second detection result; the device further comprises a timing trigger module 104, wherein the timing trigger module 104 is configured to trigger the first monitoring script through a first timing task and trigger the second monitoring script through a second timing task, and an interval duration of adjacent tasks of the first timing task is shorter than that of the second timing task.
In some possible implementations, the execution success module 102 is further configured to: detecting whether the actual occupied space of the data table exceeds the preset occupied space of the data table or not aiming at each data table in the plurality of data tables; and if the actual occupied space of at least one data table exceeds the preset occupied space of the data table, determining that the first detection result is the data table with the occurrence of table expansion.
In some possible implementations, the execution success module 102 is further configured to perform preprocessing on the plurality of data tables to execute the vacuum analyze command, detect whether there is a data table with table expansion, and obtain the first detection result.
In some possible implementations, the execution success module 102 is further configured to execute a vacuum analyze command on the data table with the table expansion, so as to perform automatic cleaning.
In some possible implementations, the execution success module 102 is further configured to: for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition, the predetermined condition including at least one of: the inclination rate is greater than the preset inclination rate, and the data volume is greater than the preset byte number; and if at least one data table meeting the preset condition exists, determining that the second detection result is the data table with the data inclination.
In some possible implementations, the execution success module 102 is further configured to: if the data table meets the preset condition, putting the data table into a result table; detecting whether the result table is empty; and if not, determining that the second detection result is that the data table with the data inclination exists.
In some possible implementations, the execution success module 102 is further configured to: if the detection result is that the data table with the table expansion and/or the data inclination exists, second prompt information of the data warehouse with the table expansion and/or the data inclination is generated; and sending the second prompt message to the data sending interface so that the data sending interface sends the second prompt message to the terminal equipment of the user.
In some possible implementations, the execution success module 102 is further configured to: if the detection result is that the data table with table expansion exists, second prompt information of the data warehouse with table expansion is generated, wherein the second prompt information comprises the data table with table expansion; if the detection result is that the data table with the data inclination exists, generating second prompt information of the data inclination of the data warehouse, wherein the second prompt information comprises the data table with the data inclination; and if the detection result is that the data table with the table expansion and the data inclination exists, generating second prompt information of the data warehouse with the table expansion and the data inclination, wherein the second prompt information comprises the data table with the table expansion and the data table with the data inclination.
Referring to fig. 7, an embodiment of the present application further provides an electronic device 200, where the electronic device 200 includes at least one memory 210, at least one processor 220, and a bus 230 connecting different platform systems.
The memory 210 may include readable media in the form of volatile memory, such as random access memory (pram) 211 and/or cache memory 212, and may further include Read Only Memory (ROM) 213.
The memory 210 further stores a computer program, and the computer program can be executed by the processor 220, so that the processor 220 executes the steps of the data table monitoring method in the embodiment of the present application (as shown in fig. 1). Memory 210 may also include a program/utility 214 having a set (at least one) of program modules 215, including but not limited to: an operating system, one or more application programs, other program modules, and program data, each of which, or some combination thereof, may comprise an implementation of a network environment.
Accordingly, processor 220 may execute the computer programs described above, as well as may execute programs/utilities 214.
The electronic device 200 may also communicate with one or more external devices 240, such as a keyboard, pointing device, Bluetooth device, etc., and may also communicate with one or more devices capable of interacting with the electronic device 200, and/or with any devices (e.g., routers, modems, etc.) that enable the electronic device 200 to communicate with one or more other computing devices. Such communication may occur via an input/output (I/O) interface 250. Also, the electronic device 200 may communicate with one or more networks (e.g., a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network such as the Internet) via the network adapter 260. The network adapter 260 may communicate with other modules of the electronic device 200 via the bus 230. It should be appreciated that although not shown in the figures, other hardware and/or software modules may be used in conjunction with the electronic device 200, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID systems, tape drives, and data backup storage platforms, to name a few.
The embodiment of the present application further provides a computer-readable storage medium, which is used for storing a computer program, and when the computer program is executed, the steps of the data table monitoring method in the embodiment of the present application are implemented (as shown in fig. 1). Fig. 8 shows a program product 300 provided by the present embodiment for implementing the method, which may employ a portable compact disc read only memory (CD-ROM) and include program codes, and may be run on a terminal device, such as a personal computer. However, the program product 300 of the present invention is not so limited, and in this document, a readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. Program product 300 may employ any combination of one or more readable media. The readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples (a non-exhaustive list) of the readable storage medium include: an electrical connection having one or more wires, a portable disk, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
A computer readable storage medium may include a propagated data signal with readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A readable storage medium may also be any readable medium that is not a readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a readable storage medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing. Program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computing device, partly on the user's device, as a stand-alone software package, partly on the user's computing device and partly on a remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device may be connected to the user computing device through any kind of network, including a Local Area Network (LAN) or a wide area network (wan), or may be connected to AN external computing device (e.g., through the internet using AN internet service provider).
The foregoing description and drawings are only for purposes of illustrating the preferred embodiments of the present application and are not intended to limit the present application, which is, therefore, to the contrary, the intention is to cover all modifications, equivalents, and alternatives falling within the spirit and scope of the present application.
Claims (10)
1. A method for data table monitoring, the method comprising:
executing the monitoring script;
if the monitoring script is executed successfully, the monitoring script is made to execute the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result;
and if the execution of the monitoring script fails, generating first prompt information of the execution failure and sending the first prompt information to a data sending interface so that the data sending interface sends the first prompt information to the terminal equipment of the user.
2. The spreadsheet monitoring method of claim 1, wherein the monitoring script comprises a first monitoring script and a second monitoring script;
the first monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion exists in the plurality of data tables or not to obtain a first detection result;
the second monitoring script is used for executing the following operations after the execution is successful: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with data inclination exists in the plurality of data tables or not, and acquiring a second detection result;
the method further comprises the following steps:
and triggering the first monitoring script through a first timing task, triggering the second monitoring script through a second timing task, wherein the interval duration of the adjacent tasks of the first timing task is less than that of the second timing task.
3. The method for monitoring the data sheet according to claim 2, wherein the detecting whether the data sheet with the sheet expansion exists in the plurality of data sheets to obtain a first detection result comprises:
detecting whether the actual occupied space of the data table exceeds the preset occupied space of the data table or not aiming at each data table in the plurality of data tables;
and if the actual occupied space of at least one data table exceeds the preset occupied space of the data table, determining that the first detection result is the data table with the occurrence of table expansion.
4. The method for monitoring the data sheet according to claim 2, wherein the detecting whether the data sheet with the sheet expansion exists in the plurality of data sheets to obtain a first detection result comprises:
and executing a vacuum analysis command on the plurality of data tables for preprocessing, detecting whether the data tables with table expansion exist or not, and acquiring the first detection result.
5. The method of data table monitoring of claim 4, further comprising:
and executing a vacuum analysis command on the data table with the table expansion to automatically clean.
6. The method for monitoring the data table according to claim 2, wherein the detecting whether the data table with the data skew exists in the plurality of data tables to obtain a second detection result comprises:
for each of the plurality of data tables, detecting whether the data table satisfies a predetermined condition, the predetermined condition including at least one of: the inclination rate is greater than the preset inclination rate, and the data volume is greater than the preset byte number;
and if at least one data table meeting the preset condition exists, determining that the second detection result is the data table with the data inclination.
7. The method for monitoring data table according to claim 6, wherein the determining that the second detection result is the presence of the data table with data skew if at least one data table meets the predetermined condition comprises:
if the data table meets the preset condition, putting the data table into a result table;
detecting whether the result table is empty;
and if not, determining that the second detection result is that the data table with the data inclination exists.
8. The method of data table monitoring of claim 1, further comprising:
if the detection result is that the data table with the table expansion and/or the data inclination exists, second prompt information of the data warehouse with the table expansion and/or the data inclination is generated;
and sending the second prompt message to the data sending interface so that the data sending interface sends the second prompt message to the terminal equipment of the user.
9. The method for monitoring the data table according to claim 8, wherein if the detection result is that the data table with the table expansion and/or the data inclination occurs, generating a second prompt message of the table expansion and/or the data inclination of the data warehouse, including:
if the detection result is that the data table with table expansion exists, second prompt information of the data warehouse with table expansion is generated, wherein the second prompt information comprises the data table with table expansion;
if the detection result is that the data table with the data inclination exists, generating second prompt information of the data inclination of the data warehouse, wherein the second prompt information comprises the data table with the data inclination;
and if the detection result is that the data table with the table expansion and the data inclination exists, generating second prompt information of the data warehouse with the table expansion and the data inclination, wherein the second prompt information comprises the data table with the table expansion and the data table with the data inclination.
10. A data table monitoring apparatus, the apparatus comprising:
the script execution module is used for executing the monitoring script;
an execution success module, configured to, if the monitoring script is successfully executed, enable the monitoring script to perform the following operations: capturing a plurality of data tables according to the data dictionary; detecting whether a data table with table expansion and/or data inclination exists in the plurality of data tables to obtain a detection result;
and the execution failure module is used for generating first prompt information of execution failure and sending the first prompt information to a data sending interface if the execution of the monitoring script fails, so that the data sending interface sends the first prompt information to the terminal equipment of the user.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010684130.7A CN111782489B (en) | 2020-07-16 | Data table monitoring method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010684130.7A CN111782489B (en) | 2020-07-16 | Data table monitoring method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111782489A true CN111782489A (en) | 2020-10-16 |
| CN111782489B CN111782489B (en) | 2024-05-10 |
Family
ID=
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113312354A (en) * | 2021-06-10 | 2021-08-27 | 北京百度网讯科技有限公司 | Data table identification method, device, equipment and storage medium |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104636483A (en) * | 2015-02-16 | 2015-05-20 | 广东省公安厅 | Data monitoring method |
| JP2016071398A (en) * | 2014-09-26 | 2016-05-09 | 株式会社野村総合研究所 | Test execution device, test execution method, and computer program |
| CN105824870A (en) * | 2016-01-15 | 2016-08-03 | 优品财富管理有限公司 | Classification and quality inspection method and system based on verification rules |
| CN108874644A (en) * | 2018-06-06 | 2018-11-23 | 平安科技（深圳）有限公司 | Data monitoring method, device, computer equipment and storage medium |
| CN110764998A (en) * | 2019-09-06 | 2020-02-07 | 平安健康保险股份有限公司 | Data comparison method, device and equipment based on Django framework and storage medium |
| CN111124832A (en) * | 2019-12-25 | 2020-05-08 | 青梧桐有限责任公司 | Data monitoring method and device, electronic equipment and storage medium |
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JP2016071398A (en) * | 2014-09-26 | 2016-05-09 | 株式会社野村総合研究所 | Test execution device, test execution method, and computer program |
| CN104636483A (en) * | 2015-02-16 | 2015-05-20 | 广东省公安厅 | Data monitoring method |
| CN105824870A (en) * | 2016-01-15 | 2016-08-03 | 优品财富管理有限公司 | Classification and quality inspection method and system based on verification rules |
| CN108874644A (en) * | 2018-06-06 | 2018-11-23 | 平安科技（深圳）有限公司 | Data monitoring method, device, computer equipment and storage medium |
| CN110764998A (en) * | 2019-09-06 | 2020-02-07 | 平安健康保险股份有限公司 | Data comparison method, device and equipment based on Django framework and storage medium |
| CN111124832A (en) * | 2019-12-25 | 2020-05-08 | 青梧桐有限责任公司 | Data monitoring method and device, electronic equipment and storage medium |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113312354A (en) * | 2021-06-10 | 2021-08-27 | 北京百度网讯科技有限公司 | Data table identification method, device, equipment and storage medium |
| CN113312354B (en) * | 2021-06-10 | 2023-07-28 | 北京百度网讯科技有限公司 | Data table identification method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107506451B (en) | Abnormal information monitoring method and device for data interaction | |
| CN101542445B (en) | Apparatus and methods of a zero single point of failure load balancer | |
| CN109710615B (en) | Database access management method, system, electronic device and storage medium | |
| CN100570607C (en) | The method and system that is used for the data aggregate of multiprocessing environment | |
| US20130080630A1 (en) | Flexible event data content management for relevant event and alert analysis within a distributed processing system | |
| US9940598B2 (en) | Apparatus and method for controlling execution workflows | |
| US20120331485A1 (en) | Flexible Event Data Content Management For Relevant Event And Alert Analysis Within A Distributed Processing System | |
| JP4506520B2 (en) | Management server, message extraction method, and program | |
| CN111314125A (en) | System and method for fault tolerant communication | |
| JP6217644B2 (en) | Rule distribution server, event processing system, method and program | |
| CN110309108A (en) | Data acquisition and storage method, device, electronic equipment, storage medium | |
| EP3671461A1 (en) | Systems and methods of monitoring software application processes | |
| CN111314158B (en) | Big data platform monitoring method, device, equipment and medium | |
| CN111327685A (en) | Data processing method, device and equipment of distributed storage system and storage medium | |
| US10938633B2 (en) | Facilitating resilient and fault tolerant asynchronous messaging | |
| CN111338834B (en) | Data storage method and device | |
| CN111782672A (en) | Multi-field data management method and related device | |
| CN113515317A (en) | Data recovery method and device | |
| CN112783447A (en) | Method, apparatus, device, medium, and article of manufacture for processing snapshots | |
| CN111782489A (en) | Data table monitoring method and device | |
| CN111782489B (en) | Data table monitoring method and device | |
| CN110389876A (en) | Basic resource capacity monitoring and managing method, device, equipment and storage medium | |
| CN112181942A (en) | Time sequence database system and data processing method and device | |
| CN116521664A (en) | Data monitoring method and device for data warehouse, computing equipment and storage medium | |
| CN110855484A (en) | Method, system, electronic device and storage medium for automatically detecting traffic change |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant |