Data volume evaluation method based on confidence interval

Through the data volume evaluation method based on confidence intervals, the volume abnormalities of the data table are automatically monitored and judged, which solves the problem of manual judgment dependence in the prior art, and improves the accuracy and reliability of data analysis.

CN119088792APending Publication Date: 2024-12-06WUHAN SOUTH SAGITTARIUS INTEGRATION CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411134459.0
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-19
Publication Date
2024-12-06

AI Technical Summary

Technical Problem

The existing data volume evaluation methods rely on manual judgment, making it difficult to effectively monitor abnormal volume in data tables, especially when there are many types of data tables, which makes it difficult to guarantee accuracy and reliability.

Method used

Using the data volume evaluation method based on confidence intervals, the data volume statistics table Q and the data quality detection table W are created, and the volume data of the data table is obtained and monitored, and the confidence interval is calculated to determine whether the data volume is abnormal. Alarm the user based on the monitoring results.

Benefits of technology

It realizes automated data volume monitoring, can effectively judge abnormal data volume, simplify data problem positioning, and improves the accuracy and reliability of data analysis.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119088792A_ABST
    Figure CN119088792A_ABST
Patent Text Reader

Abstract

A data volume evaluation method based on a confidence interval is characterized by comprising the following steps: determining a data table T which needs to be subjected to data volume monitoring; according to the data table T, creating a data table volume statistical information table Q and a data quality detection table W; obtaining volume data of the data table T in a preset time and a preset partition according to a first preset rule, and storing the volume data in the volume statistical information table Q; monitoring volume data of the volume statistical information table Q according to a second preset rule, and returning a monitoring result to the data quality detection table W; and according to a monitoring result in the data quality detection table W, giving an alarm to a user. The line number and the data byte number are used for comprehensively judging whether the data volume is normal or not, data problem positioning can be given, and the method is simple and easy to understand and implement.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data quality management in data management, and in particular to a data volume assessment method based on confidence intervals. Background Art

[0002] In data analysis and data management, the correctness of data volume is crucial to ensure the accuracy and reliability of analysis results. However, existing methods mainly rely on the experience and intuition of data analysts to determine whether the data volume is abnormal. This method is extremely dependent on the data analyst's familiarity with the data. When there are many categories of data tables, manual verification is not advisable. Therefore, a data volume assessment method and system based on confidence intervals is needed to solve this problem. Summary of the invention

[0003] In view of the above problems, the present invention is proposed to provide a data volume assessment method based on confidence intervals that overcomes the above problems or at least partially solves the above problems.

[0004] In order to solve the above technical problems, the embodiments of the present application disclose the following technical solutions:

[0005] In a first aspect, an embodiment of the present invention discloses a data volume assessment method based on a confidence interval, comprising:

[0006] S100. Determine the data table T that needs to monitor the data volume;

[0007] S200. Based on the data table T, create a data table volume statistics information table Q and a data quality detection table W;

[0008] S300. Obtaining the volume data of the data table T at a preset time and a preset partition according to a first preset rule, and storing the volume data in the volume statistical information table Q;

[0009] S400. Monitor the volume data of the volume statistical information table Q according to the second preset rule, and return the monitoring result to the data quality detection table W;

[0010] S500. Alert the user based on the monitoring results in the data quality detection table W.

[0011] Furthermore, in S200, the fields contained in the volume statistics information table Q at least include database instance name instance, database name db, table name table, partition pt, number of rows len, storage size in bytes size and record date day.

[0012] Furthermore, in S200, the fields contained in the quality detection table W at least include database instance name instance, database name db, table name table, partition pt, quality type q_type, result result and record date day.

[0013] Further, in S300, the volume data of the data table T at a preset time and a preset partition is obtained according to the first preset rule. The specific method includes: determining the time range and partition range t to be obtained for the data table T, obtaining the volume data of pt(partition)=t of the data table T on day t, and storing the volume data in the Q table. The parameters obtained by the first preset rule are the full table name and partition, and the return parameter is collection success or collection failure.

[0014] Furthermore, in S400, the volume data of the volume statistical information table Q is monitored according to a second preset rule. The specific method includes: calculating the volume reference value of the data table T on day t according to the volume statistical information table Q Calculate the standard deviation σ of the number of bytes in the data table T in the past seven days; based on the volume benchmark value The confidence interval CI of the data volume is obtained by combining the standard deviation of the number of bytes σ, and the data volume size of the day is monitored according to the confidence interval CI.

[0015] Furthermore, the volume reference value The calculation formula is:

[0016]

[0017]

[0018]

[0019] in, represents the estimated number of rows on day t, represents the estimated number of bytes on day t, Δ represents the average number of bytes per row in table T over the past seven days, and S i Indicates the number of bytes, L i Indicates the number of rows; t-1 means the day before day t, t-2 means the two days before day t, and t-7 means the seven days before day t.

[0020] Furthermore, the calculation formula of the byte number standard deviation σ is:

[0021]

[0022]

[0023] Among them, t-1 means the day before t, t-7 means the 7 days before t, S irepresents the number of bytes, μ represents the average value of 7-day data bytes, and σ represents the standard deviation of the number of bytes.

[0024] Furthermore, the confidence interval of the data volume If the data volume size of the day falls within the CI interval, the data volume detection for that day is considered to have passed. It is considered that the data volume for that day has overflowed; if the data volume It is considered that the data volume for that day overflows.

[0025] Furthermore, in S500, an alarm is issued to the user based on the monitoring result in the data quality detection table W; the specific method includes: when the monitoring result in the data quality detection table W is overflow, it is determined that the data table T for data volume monitoring contains a large number of null values ​​or the data source access data is incomplete, and an alarm is issued to the user; when the monitoring result in the data quality detection table W is overflow, it is determined that duplicate data exists, and an alarm is issued to the user.

[0026] In a second aspect, an embodiment of the present invention discloses an electronic device, including:

[0027] one or more processors;

[0028] A memory for storing one or more programs;

[0029] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for evaluating the data volume.

[0030] The beneficial effects of the above technical solution provided by the embodiment of the present invention include at least:

[0031] The present invention discloses a data volume assessment method based on confidence intervals, which is characterized by comprising: determining a data table T that needs to monitor data volume; creating a new data table volume statistics information table Q and a data quality detection table W according to the data table T; obtaining the volume data of the data table T at a preset time and a preset partition according to a first preset rule, and storing the volume data in the volume statistics information table Q; monitoring the volume data of the volume statistics information table Q according to a second preset rule, and returning the monitoring result to the data quality detection table W; and alerting the user according to the monitoring result in the data quality detection table W. The present invention uses the number of rows and the number of data bytes to comprehensively determine whether the data volume is normal, and can also provide data problem location. The method is simple and easy to understand and implement.

[0032] The technical solution of the present invention is further described in detail below through the accompanying drawings and embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] The accompanying drawings are used to provide a further understanding of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the present invention and do not constitute a limitation of the present invention. In the accompanying drawings:

[0034] Figure 1 This is a flow chart of a data volume assessment method based on confidence interval in Example 1 of the present invention;

[0035] Figure 2 This is a schematic diagram of the structure of an electronic device in Embodiment 2 of the present invention. DETAILED DESCRIPTION

[0036] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0037] In order to solve the problems existing in the prior art, an embodiment of the present invention provides a data volume assessment method based on confidence interval.

[0038] Example 1

[0039] The present invention discloses a data volume assessment method based on confidence intervals, such as Figure 1 ,include:

[0040] S100. Determine the data table T that needs to monitor the data volume; specifically, a data table is a structure used to store and organize data in a database management system (DBMS). In database design, data tables are the basis for building a database. They store data in the form of rows and columns, where each column represents a field (Field) that defines the type of data (such as integer, string, date, etc.), and each row represents a record (Record), which contains the specific data of a specific entity. In this embodiment, the data table T that needs to monitor the data volume corresponds to the ETL task Task-T in the task flow scheduling system (such as Apache Dophinscheduler);

[0041] S200. Create a data table volume statistics information table Q and a data quality detection table W based on the data table T; in S200 of this embodiment, the fields included in the volume statistics information table Q include at least the database instance name instance, database name db, table name table, partition pt, number of rows len, storage size byte size and recording date day. The fields included in the quality detection table W include at least the database instance name instance, database name db, table name table, partition pt, quality type q_type, result result and recording date day.

[0042] S300. Obtain the volume data of the data table T at the preset time and preset partition according to the first preset rule, and store the volume data in the volume statistical information table Q; Specifically, determine the preset time and preset partition: clarify the time range you need to query and the specific definition of the partition (such as partition by date, region, department, etc.). Prepare the volume statistical information table: Make sure there is already a table for storing statistical information, and the table should contain enough fields to record time, partition identifier and corresponding volume data.

[0043] In S300 of this embodiment, the volume data of the data table T at a preset time and a preset partition is obtained according to the first preset rule. The specific method includes: determining the time range and partition range t to be obtained for the data table T, obtaining the volume data of pt(partition)=t of the data table T on day t, and storing the volume data in the Q table. The parameters obtained by the first preset rule are the full table name and the partition, and the return parameter is whether the collection is successful or failed.

[0044] In the actual operation process, by writing a data volume collection script A, the function of A is to collect the t partition volume data of T on day t, and store this volume data in the Q table. The input parameter of script A is (full table name, partition), here is (T, t), and the return parameter is collection success or collection failure. Specifically, write a data volume collection script A. The function of A is to collect the pt(partition)=t partition volume data of T on day t, and store this volume data in the Q table. The input parameter of script A is (full table name, partition), here is (T, t), and the return parameter is collection success or collection failure.

[0045] S400. Monitor the volume data of the volume statistical information table Q according to the second preset rule, and return the monitoring result to the data quality detection table W;

[0046] In S400 of this embodiment, the volume data of the volume statistical information table Q is monitored according to the second preset rule. The specific method includes: calculating the volume reference value of the data table T on day t according to the volume statistical information table Q Calculate the standard deviation σ of the number of bytes in the data table T in the past seven days; based on the volume benchmark value The confidence interval CI of the data volume is obtained by combining the standard deviation of the number of bytes σ, and the data volume size of the day is monitored according to the confidence interval CI.

[0047] Among them, the volume reference value The calculation formula is:

[0048]

[0049]

[0050]

[0051] in, represents the estimated number of rows on day t, represents the estimated number of bytes on day t, Δ represents the average number of bytes per row in table T over the past seven days, and S i Indicates the number of bytes, L i Indicates the number of rows; t-1 means the day before day t, t-2 means the two days before day t, and t-7 means the seven days before day t.

[0052] The calculation formula for the standard deviation σ of the number of bytes is:

[0053]

[0054] Among them, t-1 means the day before t, t-7 means the 7 days before t, S i represents the number of bytes, μ represents the average value of 7-day data bytes, and σ represents the standard deviation of the number of bytes.

[0055] Confidence interval for the data volume If the data volume size of the day falls within the CI interval, the data volume detection for that day is considered to have passed. It is considered that the data volume for that day has overflowed; if the data volume It is considered that the data volume for that day overflows.

[0056] In the actual operation process, write a data volume monitoring script B. The role of B is to implement formula (1)-formula (6) and combine with Q table to monitor whether there is a problem with the data volume of partition t in table T. The calculation process parameters and calculation results are stored in the data quality detection table W for subsequent verification and algorithm optimization. The input parameter of script B is (full table name, partition), and the return value is an enumeration value [success, overflow, underflow]. It should be noted that the data in the same table and the same partition in table Q may have duplicate values. At this time, if the record date day is different, the latest value is taken according to day. If the day is also the same, it means that there is a bug in the data volume collection script A. At this time, you can only clear the abnormal data and re-run the data volume collection script A.

[0057] After writing scripts A and B, configure scripts A and B to the task flow scheduling system. The corresponding task names are Task-A and Task-B, and the task dependencies are Task-T->Task-A->Task-B, that is, Task-B depends on Task-A, and task-A depends on Task-B. When the downstream task fails, the downstream task is blocked. It should be noted that the dependency of Task-T->Task-A->Task-B is a separate branch and should not affect the original downstream dependency of Task-T. For example, when Task-A and Task-B are not configured, there is a dependency of Task-T->Task-N. After Task-A and Task-B are configured, the dependency becomes Task-T->Task-N.

[0058] \->Task-A->Task-B.

[0059] S500. Alert the user based on the monitoring results in the data quality detection table W. In S500 of this embodiment, alert the user based on the monitoring results in the data quality detection table W; the specific method includes: when the monitoring result in the data quality detection table W is overflow, it is determined that the data table T for data volume monitoring has a large number of null values ​​or the data source access data is incomplete, and the user is alerted; when the monitoring result in the data quality detection table W is overflow, it is determined that there is duplicate data, and the user is alerted.

[0060] Specifically, the alarm service reads the data in the W table and issues real-time alarms based on the result status and day generation time. The alarm results should be translated accordingly. For example, if an underflow corresponds to an abnormal data, the detection result is an underflow, and the initial judgment is that there are a large number of null values ​​or the data source access data is incomplete. If an overflow corresponds to an abnormal data, the detection result is an overflow, and the initial judgment is that there are duplicate data. The alarm results are sent to the data manager or operation and maintenance personnel through email, DingTalk, etc.

[0061] The present embodiment discloses a data volume assessment method based on confidence intervals, which is characterized by comprising: determining a data table T that needs to monitor data volume; creating a new data table volume statistics information table Q and a data quality detection table W based on the data table T; obtaining the volume data of the data table T at a preset time and a preset partition according to a first preset rule, and storing the volume data in the volume statistics information table Q; monitoring the volume data of the volume statistics information table Q according to a second preset rule, and returning the monitoring results to the data quality detection table W; and alerting the user based on the monitoring results in the data quality detection table W. The present invention uses the number of rows and the number of data bytes to comprehensively determine whether the data volume is normal, and can also provide data problem location. The method is simple and easy to understand and implement.

[0062] Example 2

[0063] Based on the same inventive concept, an embodiment of the present disclosure also provides an electronic device. Figure 2 FIG. 1 is a schematic diagram of the structure of an electronic device according to an embodiment of the present disclosure. Figure 2 As shown, an embodiment of the present disclosure provides an electronic device including: one or more processors 101, a memory 102, and one or more I / O interfaces 103. The memory 102 stores one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement any optimization method in the above embodiments; the one or more I / O interfaces 103 are connected between the processor and the memory, and are configured to implement information interaction between the processor and the memory.

[0064] Among them, the processor 101 is a device with data processing capabilities, including but not limited to a central processing unit (CPU), etc.; the memory 102 is a device with data storage capabilities, including but not limited to random access memory (RAM, more specifically SDRAM, DDR, etc.), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory (FLASH); the I / O interface (read-write interface) 103 is connected between the processor 101 and the memory 102, and can realize information interaction between the processor 101 and the memory 102, including but not limited to a data bus (Bus), etc.

[0065] In some embodiments, the processor 101 , the memory 102 , and the I / O interface 103 are connected to each other via a bus 104 , and further connected to other components of the computing device.

[0066] In some embodiments, the one or more processors 101 include a field programmable gate array.

[0067] According to an embodiment of the present disclosure, a computer-readable medium is further provided, wherein a computer program is stored on the computer-readable medium, wherein when the program is executed by a processor, the steps in any optimization method in the above-mentioned embodiment are implemented.

[0068] It should be understood that the specific order or hierarchy of steps in the disclosed process is an example of an exemplary method. Based on design preferences, it should be understood that the specific order or hierarchy of steps in the process can be rearranged without departing from the scope of protection of the present disclosure. The attached method claims present the elements of the various steps in an exemplary order and are not intended to be limited to the specific order or hierarchy described.

[0069] In the above detailed description, various features are grouped together in a single embodiment to simplify the disclosure. This method of disclosure should not be interpreted as reflecting an intention that the embodiments of the claimed subject matter require more features than are clearly stated in each claim. On the contrary, as reflected in the appended claims, the invention is in a state of having less than all the features of the disclosed individual embodiments. Therefore, the appended claims are hereby expressly incorporated into the detailed description, with each claim standing on its own as a separate preferred embodiment of the invention.

[0070] Those skilled in the art will also appreciate that the various illustrative logic blocks, modules, circuits, and algorithmic steps described in conjunction with the embodiments herein can all be implemented as electronic hardware, computer software, or a combination thereof. In order to clearly illustrate the interchangeability between hardware and software, various illustrative components, blocks, modules, circuits, and steps are generally described above around their functions. Whether such functions are implemented as hardware or software depends on specific applications and the design constraints imposed on the entire system. A skilled person can implement the described functions in an alternative manner for each specific application, but such implementation decisions should not be interpreted as departing from the scope of protection of the present disclosure.

[0071] The steps of the method or algorithm described in conjunction with the embodiments herein may be directly embodied as hardware, a software module executed by a processor, or a combination thereof. The software module may be located in a RAM memory, a flash memory, a ROM memory, an EPROM memory, an EEPROM memory, a register, a hard disk, a mobile disk, a CD-ROM, or any other form of storage medium well known in the art. An exemplary storage medium is connected to the processor so that the processor can read information from the storage medium and can write information to the storage medium. Of course, the storage medium may also be an integral part of the processor. The processor and the storage medium may be located in an ASIC. The ASIC may be located in a user terminal. Of course, the processor and the storage medium may also be present in a user terminal as discrete components.

[0072] For software implementation, the techniques described in this application can be implemented with modules (e.g., procedures, functions, etc.) that perform the functions described in this application. These software codes can be stored in a memory unit and executed by a processor. The memory unit can be implemented within the processor or outside the processor. In the latter case, it is coupled to the processor in a communication manner via various means, which are well known in the art.

[0073] The above description includes examples of one or more embodiments. Of course, it is impossible to describe all possible combinations of components or methods for the purpose of describing the above embodiments, but it should be recognized by those skilled in the art that the various embodiments may be further combined and arranged. Therefore, the embodiments described herein are intended to cover all such changes, modifications and variations that fall within the scope of protection of the appended claims. In addition, with respect to the term "comprising" used in the specification or claims, the word is covered in a manner similar to the term "including", just as "including," is explained as a transitional word in the claims. In addition, any term "or" used in the specification of the claims is intended to mean "non-exclusive or".

Claims

1. A data volume assessment method based on confidence interval, It is characterized in that include: S100. Determine the data table T that needs to monitor the data volume; S200. Based on the data table T, create a data table volume statistics information table Q and a data quality detection table W; S300. Obtaining the volume data of the data table T at a preset time and a preset partition according to a first preset rule, and storing the volume data in the volume statistical information table Q; S400. Monitor the volume data of the volume statistical information table Q according to the second preset rule, and return the monitoring result to the data quality detection table W; S500. Alert the user based on the monitoring results in the data quality detection table W.

2. A data volume assessment method based on confidence interval as claimed in claim 1, It is characterized in that In S200, the volume statistics information table Q includes at least fields including database instance name instance, database name db, table name table, partition pt, number of rows len, storage size in bytes size, and record date day.

3. The data volume assessment method based on confidence interval according to claim 1, It is characterized in that In S200, the fields contained in the quality detection table W at least include database instance name instance, database name db, table name table, partition pt, quality type q_type, result result and record date day.

4. The data volume assessment method based on confidence interval according to claim 1, It is characterized in that In S300, the volume data of the data table T at the preset time and the preset partition is obtained according to the first preset rule. The specific method includes: determining the time range and partition range t of the data table T to be obtained, obtaining the volume data of pt(partition)=t of the data table T on day t, and storing the volume data in the Q table. The parameters obtained by the first preset rule are the full table name and the partition, and the return parameter is collection success or collection failure.

5. The data volume assessment method based on confidence interval according to claim 1, It is characterized in that In S400, the volume data of the volume statistical information table Q is monitored according to the second preset rule. The specific method includes: calculating the volume reference value of the data table T on day t according to the volume statistical information table Q Calculate the standard deviation σ of the number of bytes in the data table T in the past seven days; based on the volume benchmark value The confidence interval CI of the data volume is obtained by combining the standard deviation of the number of bytes σ, and the data volume size of the day is monitored according to the confidence interval CI.

6. A data volume assessment method based on confidence interval as claimed in claim 5, It is characterized in that The volume reference value The calculation formula is: in, represents the estimated number of rows on day t, represents the estimated number of bytes on day t, Δ represents the average number of bytes per row in table T over the past seven days, and S i Indicates the number of bytes, L i Indicates the number of rows; t-1 means the day before day t, t-2 means the two days before day t, and t-7 means the seven days before day t.

7. A data volume assessment method based on confidence interval as claimed in claim 6, It is characterized in that The calculation formula for the standard deviation σ of the number of bytes is: Among them, t-1 means the day before t, t-7 means the 7 days before t, S i represents the number of bytes, μ represents the average value of 7-day data bytes, and σ represents the standard deviation of the number of bytes.

8. A data volume assessment method based on confidence interval as claimed in claim 7, It is characterized in that Confidence interval for the data volume If the data volume size of the day falls within the CI interval, the data volume detection for that day is considered to have passed. It is considered that the data volume for that day has overflowed; if the data volume It is considered that the data volume for that day overflows.

9. A data volume assessment method based on confidence interval as claimed in claim 8, It is characterized in that In S500, based on the monitoring result in the data quality detection table W, an alarm is issued to the user; the specific method includes: when the monitoring result in the data quality detection table W is overflow, it is determined that the data table T for data volume monitoring contains a large number of null values ​​or the data source access data is incomplete, and an alarm is issued to the user; when the monitoring result in the data quality detection table W is overflow, it is determined that duplicate data exists, and an alarm is issued to the user.

10. An electronic device, include: one or more processors; A memory for storing one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method for evaluating the data volume.