Database field automatic detection and conversion method, equipment and medium

Abstract

The embodiment of the invention discloses a database field automatic detection and conversion method and device and a medium, belongs to the technical field of databases, and solves the problem that a large number of null but non-null special field states are formed in a database table due to manual execution of field attribute conversion. Comprising the following steps: extracting user tables meeting a preset screening condition from a database, and carrying out field filtering on each user table to obtain fields to be counted; obtaining a record number corresponding to a user table where the to-be-counted field is located, and determining a corresponding table capacity judgment strategy based on a comparison relationship between a preset number threshold and the record number; determining a null value proportion corresponding to the user table where the to-be-counted field is located through different table capacity judgment strategies; and matching a corresponding field conversion strategy based on a comparison relationship between a preset proportion threshold value and the null value proportion so as to perform corresponding conversion on the null field through the field conversion strategy.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a database field automatic detection and conversion method, device and medium. BACKGROUND

[0002] In the field of backend application development, as a core data storage carrier, the table structure of the database needs to be continuously evolved with the iteration of business requirements. In the scenario of adding new business-related fields, in order to avoid triggering the database lock table during the field adding process and affecting the normal operation of the business in the production environment, the industry generally adopts a step-by-step migration operation: first, mark the newly added field as a nullable attribute to ensure that the field adding operation is quickly completed and does not block the business, and after the related logic of the backend application program is updated and the historical data is supplemented through the data backfill mechanism, adjust the attribute of the field from nullable to not null to meet the requirement of the business for the mandatory filling of the field.

[0003] However, in the actual operation process of the production environment, manual execution of the field attribute conversion is usually required, and there may be a situation of missing nullable to not null, which leads to a large number of special field states of nullable but not null in the database table, that is, the field still maintains the nullable identifier in the schema definition of the database, but there is no null value in the actually stored data, thereby causing the mismatch between the schema and the data of the field. SUMMARY

[0004] The embodiments of the present application provide a database field automatic detection and conversion method, device and medium, which are used to solve the technical problem that manual execution of the field attribute conversion usually leads to a situation of missing nullable to not null, which leads to a large number of special field states of nullable but not null in the database table, thereby causing the mismatch between the schema and the data of the field.

[0005] The embodiments of the present application adopt the following technical solutions: The embodiments of the present application provide a database field automatic detection and conversion method. The method comprises the following steps: extracting user tables that meet a preset filtering condition from a database, and performing field filtering on each user table to obtain a to-be-counted field; obtaining a record quantity corresponding to the user table in which the to-be-counted field is located, and determining a table capacity determination strategy based on a comparison relationship between a preset quantity threshold and the record quantity; wherein the table capacity determination strategy at least includes one of a numerical calculation strategy and a sampling estimation strategy; determining a null value proportion corresponding to the user table in which the to-be-counted field is located through different table capacity determination strategies; and matching a corresponding field conversion strategy based on a comparison relationship between a preset proportion threshold and the null value proportion, so as to perform corresponding conversion on the nullable field through the field conversion strategy.

[0006] In an implementation manner of the present application, the user table meeting the preset filtering condition is extracted from the database, and field filtering is performed on each user table to obtain the field to be counted, specifically including: reading the database system metadata, extracting the user table meeting the preset filtering condition from the system metadata, and generating a table to be scanned list; wherein the preset filtering condition at least includes one of the structure change operation or the data read-write operation corresponding to the user table within the preset time range; based on the preset column filtering condition, column elimination processing is performed on each user table in the table to be scanned list to obtain the field to be counted; wherein the preset column filtering condition at least includes one of the system built-in column, the specified data type column and the declared non-empty constraint column.

[0007] In an implementation manner of the present application, the proportion of null values corresponding to the user table where the field to be counted is located is determined through different table capacity determination strategies, specifically including: in the case where the table capacity determination strategy is determined as the numerical calculation strategy, triggering the preset query statement set to query the user table where the field to be counted is located, obtaining the number of null values and the total number of records; obtaining the proportion of null values based on the ratio between the number of null values and the total number of records.

[0008] In an implementation manner of the present application, the proportion of null values corresponding to the user table where the field to be counted is located is determined through different table capacity determination strategies, specifically including: in the case where the table capacity determination strategy is determined as the sampling estimation strategy, extracting reference business dimensions based on the current business characteristics; performing mutual information processing on each reference business dimension to filter out the associated dimensions in the reference business dimensions; performing hierarchical division on each associated dimension based on the dimension value, performing density clustering processing within each level, and performing sampling weight allocation on different clustering clusters; based on the sampling weight, sample extraction is performed within the level of each associated dimension, and the sample null value proportion corresponding to each level is determined; the record proportion of each level in the whole table is taken as the weight to perform weighted summation on the sample null value proportion, so as to obtain the null value proportion corresponding to the user table where the field to be counted is located.

[0009] In an implementation manner of the present application, based on the comparison relationship between the preset proportion threshold and the proportion of null values, the corresponding field conversion strategy is matched to perform corresponding conversion on the nullable field through the field conversion strategy, specifically including: in the case where the proportion of null values is 0, performing instant field conversion or window period field conversion on the field according to the business type associated with the field; in the case where the proportion of null values is greater than 0 and not greater than the proportion threshold, determining the field completion mode in the preset completion SQL template based on the field type, and performing field conversion on the field after field completion; in the case where the proportion of null values is greater than the proportion threshold, determining the generation time of the field null value, determining the exception type based on the generation time, and performing re-detection or generating a fault work order on the user table where the field to be counted is located based on the exception type.

[0010] In an implementation form of the present application, the field conversion is performed on the field after the field completion, specifically comprising: performing simulation conversion on the completed field to obtain conversion information; wherein the conversion information at least includes one of an execution plan corresponding to the conversion instruction, resource consumption data corresponding to the execution plan, and transaction lock occupation time length; based on the real-time running parameters of the user table where the field is located and the conversion information corresponding to the simulation conversion, the predicted lock table time length is obtained; the predicted lock table time length is compared with the preset lock time threshold value, if the predicted lock table time length is not greater than the preset lock time threshold value, the field conversion instruction is directly executed; if the predicted lock table time length is greater than the preset lock time threshold value, the batch conversion strategy is enabled.

[0011] In an implementation form of the present application, the batch conversion strategy is enabled, specifically comprising: obtaining the identification field of the user table where the field is located, and dividing the value range of the identification field into a plurality of continuous data segments based on the preset batch data amount; in the order of the identification field value of the data segment, the field conversion instruction is executed on each conversion batch in turn; the transaction lock is applied to the records in the data segment corresponding to the current conversion batch, the transaction lock resource is released after the current conversion batch is completed, and the next conversion batch is executed after a preset time interval; wherein the preset time interval is related to the database lock resource release efficiency and the associated business transaction cycle time length; the actual lock table time length and the real-time database load parameter corresponding to each conversion batch are collected in real time, and the batch data amount and the preset time interval of the next batch are dynamically adjusted in the case that the actual lock table time length and / or the real-time database load parameter do not meet the threshold condition.

[0012] In an implementation form of the present application, after matching the corresponding field conversion strategy based on the comparison relationship between the preset proportion threshold value and the null value proportion, the method further comprises: obtaining the associated attributes corresponding to each field respectively, and quantitatively assigning the associated attributes; wherein the associated attributes at least include one of the field association relationship, the default value configuration state and the historical null value record; obtaining historical risk judgment data, based on the historical error information and the business feedback factor, the dynamic weight value corresponding to each associated attribute is determined; based on the quantitative assignment result and the dynamic weight value corresponding to each associated attribute, the risk value corresponding to each field is obtained; based on the difference between the risk value and the preset risk threshold value, the conversion risk level is determined.

[0013] The embodiment of the application provides a database field automatic detection and conversion device, comprising: at least one processor; and a memory in communication connection with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: extract user tables meeting preset filtering conditions in a database, and perform field filtering on each user table to obtain a field to be counted; obtain a record quantity corresponding to a user table where the field to be counted is located, determine a corresponding table capacity determination strategy based on a comparison relationship between a preset quantity threshold and the record quantity; wherein the table capacity determination strategy at least includes one of a numerical calculation strategy and a sampling estimation strategy; determine a null value proportion corresponding to the user table where the field to be counted is located through different table capacity determination strategies; and based on a comparison relationship between a preset proportion threshold and the null value proportion, match a corresponding field conversion strategy to perform corresponding conversion on a nullable field through the field conversion strategy.

[0014] The embodiment of the application provides a nonvolatile computer storage medium, which stores computer executable instructions, and the computer executable instructions are configured to: extract user tables meeting preset filtering conditions in a database, and perform field filtering on each user table to obtain a field to be counted; obtain a record quantity corresponding to a user table where the field to be counted is located, determine a corresponding table capacity determination strategy based on a comparison relationship between a preset quantity threshold and the record quantity; wherein the table capacity determination strategy at least includes one of a numerical calculation strategy and a sampling estimation strategy; determine a null value proportion corresponding to the user table where the field to be counted is located through different table capacity determination strategies; and based on a comparison relationship between a preset proportion threshold and the null value proportion, match a corresponding field conversion strategy to perform corresponding conversion on a nullable field through the field conversion strategy.

[0015] The above at least one technical scheme adopted by the embodiment of the application can achieve the following beneficial effects: the embodiment of the application can accurately focus on target objects by extracting user tables meeting conditions and filtering to obtain fields to be counted, avoids irrelevant tables and fields, and further filters out redundant fields that do not need to be counted, thereby reducing subsequent calculation amount and improving process efficiency. Secondly, the table capacity determination strategy is determined by comparing the record quantity with the preset threshold, differential processing is realized, the statistical accuracy and performance are balanced, the statistical process is adapted to different data sizes, and efficiency and accuracy are taken into account. In addition, the null value proportion is determined through different strategies, the null value statistical result is ensured to be reliable and efficient, finally, the conversion strategy is matched based on the null value proportion and the preset threshold, the conversion is executed, the field conversion automation is realized, the conversion scheme is dynamically selected according to the null value proportion, the risk of blind conversion is avoided, the manual operation cost is reduced, and the consistency of the database schema and the data state is ensured. BRIEF DESCRIPTION OF DRAWINGS

[0016] In order to make the technical solutions in the application or the prior art clearer, the accompanying drawings needed in the embodiments or prior art description will be briefly introduced. Obviously, the accompanying drawings in the following description are only some embodiments described in the application, and other drawings can be obtained by those skilled in the art without creative labor. In the drawings: Figure 1 A database field automatic detection and conversion method flow chart is provided for the embodiments of the application. Figure 2 A structural schematic diagram of a database field automatic detection and conversion device is provided for the embodiments of the application.

[0017] Reference signs: 200: database field automatic detection and conversion device, 201: processor, 202: memory. DETAILED DESCRIPTION

[0018] The embodiments of the application provide a database field automatic detection and conversion method, device and medium.

[0019] In order to make those skilled in the art better understand the technical solutions in the application, the technical solutions in the embodiments of the application will be described clearly and completely in the following with reference to the accompanying drawings of the embodiments of the application. Obviously, the described embodiments are only some of the embodiments of the application, not all. Based on the embodiments of the description, all other embodiments obtained by those skilled in the art without creative labor should be within the scope of protection of the application.

[0020] The technical solutions of the embodiments of the application will be described in detail in the following with reference to the accompanying drawings. Figure 1 A database field automatic detection and conversion method flow chart is provided for the embodiments of the application. As shown in Figure 1 The database field automatic detection and conversion method comprises the following steps: Step 101, extracting user tables meeting preset filtering conditions from a database, and filtering fields of each user table to obtain to-be-counted fields.

[0021] In an implementation manner of the application, for the current detected database, first, the CPU utilization, I / O throughput and database connection idle ratio corresponding to the database are periodically collected. If all of the three are lower than a preset threshold or the current time falls into a maintenance window, the subsequent statistical process is triggered.

[0022] In an implementation form of the present application, system metadata is read, user tables meeting preset filtering conditions are extracted from the system metadata, and a table list to be scanned is generated; wherein the preset filtering conditions at least include one of a structure change operation or a data read-write operation corresponding to the user tables within a preset time range. Based on preset column filtering conditions, column elimination processing is performed on each user table in the table list to be scanned, and a field to be counted is obtained; wherein the preset column filtering conditions at least include one of a system built-in column, a specified data type column, and a declared non-empty constraint column.

[0023] Specifically, when it is determined that the target database is PostgreSQL, its core directory table is located and accessed, specifically, pg_class (storing table basic information), pg_stat_all_tables (recording table statistical data), pg_depend (reflecting object dependency relationship), pg_attribute (storing field attribute), and pg_stat_user_tables (user table statistical information) are read to obtain key information such as structure change records and data read-write frequency of user tables. Secondly, the user tables are screened, the operation records of the user tables are extracted from the integrated system metadata, and it is judged whether each user table meets the structure change operation or the data read-write operation condition, wherein the structure change operation includes a DDL (Data Definition Language) operation occurring within a preset time range such as 30 days, and the data read-write operation includes a large number of writes or updates within a preset time range. The user tables meeting any condition are screened out to generate a table list to be scanned.

[0024] Further, for the user tables in the table list to be scanned, the present application has preset column filtering conditions, including system built-in columns, fields without business meaning, specified data type columns, and declared non-empty constraint columns. The fields without business meaning include oid, ctid, etc.; the specified data type columns include JSONB type, TEXT type, and binary object type columns; and the declared non-empty constraint columns refer to fields in the table structure that have been explicitly marked as not null. Column elimination processing is performed on the user tables in the table list to be scanned one by one, for each user table, the attribute information of all fields thereof is determined, the system built-in columns, the specified data type columns, and the declared non-empty constraint columns are eliminated in turn by comparing with the preset column filtering conditions, only the ordinary business columns are retained, and the fields remaining after processing each user table are summarized to form the field to be counted corresponding to the user table.

[0025] Step 102, the number of records corresponding to the user table where the field to be counted is located is obtained, and based on the comparison relationship between the preset number threshold and the number of records, a corresponding table capacity determination strategy is determined.

[0026] In an implementation of the present application, the number of table rows corresponding to the user table in which the field to be counted is located is obtained, and if the number of table rows is less than a preset number threshold, it is determined that the table capacity determination strategy is the numerical calculation strategy, and if the number of table rows is not less than the preset number threshold, it is determined that the table capacity determination strategy is the sampling estimation strategy.

[0027] In step 103, the proportion of null values corresponding to the user table in which the field to be counted is located is determined by different table capacity determination strategies.

[0028] In an implementation of the present application, in the case where the table capacity determination strategy is determined to be the numerical calculation strategy, a preset query statement set is triggered to query the user table in which the field to be counted is located, to obtain the number of null values and the total number of records, and the proportion of null values is obtained based on the ratio between the number of null values and the total number of records.

[0029] Specifically, a total number of records query statement is called from the preset query statement set, a full query is performed on the user table in which the field to be counted is located, the total number of records of the user table is obtained by traversing all records in the table and counting, and then a non-null record number query statement in the preset query statement set is called, only the full scan is performed on the current field to be counted, and the number of records of all non-null values in the field is counted. The number of null values of the field to be counted is obtained by subtracting the number of non-null records from the total number of records, and then the ratio is calculated with the number of null values as the numerator and the total number of records as the denominator to obtain the proportion of null values.

[0030] In an implementation of the present application, in the case where the table capacity determination strategy is determined to be the sampling estimation strategy, reference business dimensions are extracted based on the current business characteristics, mutual information processing is performed on each reference business dimension to filter out associated dimensions in the reference business dimensions. Each associated dimension is hierarchically divided based on the dimension value, density clustering processing is performed within each level, and different clustering clusters are allocated with sampling weights, samples are extracted within each level of the associated dimensions based on the sampling weights, and the sample null value proportion corresponding to each level is determined. The record proportion of each level in the whole table is used as a weight to perform weighted summation on the sample null value proportion, to obtain the proportion of null values corresponding to the user table in which the field to be counted is located.

[0031] Specifically, in the case where the table capacity determination strategy is determined as the sampling estimation strategy, first, the business dimensions associated with the field to be counted are extracted from the business dictionary, table association relationship and historical operation log of the PostgreSQL database, such as the user level, registration area, activity level layer and other dimensions strongly related to business logic in the user table. For each reference business dimension extracted, the mutual information value of the dimension and the null state of the field to be counted is calculated to determine the close degree of association between the two. The embodiment of the present application has a mutual information threshold, and the reference business dimension with a mutual information value not less than the mutual information threshold is screened as an associated dimension. For each screened associated dimension, according to the specific value range or business classification rule of the dimension, it is divided into multiple continuous and non-overlapping levels. For example, the registration area dimension is divided into levels according to the North China, East China, South China and other regions, each level corresponds to a specific value interval of the associated dimension, ensuring that the level division conforms to the business cognition and can reflect the difference in data distribution.

[0032] For different levels of each associated dimension, a density clustering algorithm is used to cluster the records in the level to identify core clusters with dense null value distribution and non-core clusters with sparse null value distribution. The core cluster with dense null values is given a higher sampling weight to ensure that the core cluster can be fully sampled to reflect the real distribution of null values, and the non-core cluster is given a lower sampling weight to control the total amount of sampling while considering data representativeness. According to the sampling weight of different clustering clusters in each level, a corresponding number of samples are randomly extracted from each clustering cluster to ensure that the number of samples is proportional to the weight. After extraction, the number of null values and the total number of samples in each level are counted, and the sample null value ratio corresponding to each level is calculated.

[0033] Finally, the record proportion of each level of each associated dimension in the whole table is obtained by querying the PostgreSQL system table, and the proportion is used as a weight to perform weighted calculation with the sample null value ratio of the corresponding level. By accumulating the results of "level record proportion x sample null value ratio" of all levels, the final null value ratio of the user table where the field to be counted is located is obtained.

[0034] Step 104, based on the comparison relationship between the preset proportion threshold and the null value ratio, the corresponding field conversion strategy is matched to perform corresponding conversion on the nullable field through the field conversion strategy.

[0035] In an implementation of the present application, when the null value proportion is 0, the field is converted in real time or in a window period according to the business type associated with the field. When the null value proportion is greater than 0 and not greater than a proportion threshold, the field completion method is determined in the preset completion SQL template based on the field type, and the field is converted after field completion. When the null value proportion is greater than the proportion threshold, the generation time of the field null value is determined, the abnormal type is determined based on the generation time, and the user table where the field to be counted is located is re-detected or a fault work order is generated based on the abnormal type.

[0036] Specifically, when the null value proportion is 0, the business type associated with the field to be counted is extracted from the database business dictionary and the associated table metadata, such as core transaction type, normal query type, and log recording type. If the associated business type is a normal query type, real-time field conversion is triggered, and non-null constraint addition operation is directly executed; if the associated business type is a core transaction type, i.e. there is a high-frequency write or update operation, it is determined that window period conversion is needed, and the database maintenance window calendar is automatically queried to perform field conversion in the preset maintenance period, avoiding the influence of conversion operation on business continuity.

[0037] Further, when the null value proportion is greater than 0 and not greater than the proportion threshold, the data type of the field to be counted is first identified, and the completion method of the corresponding type is matched from the preset completion SQL template library. Among them, the numerical value type field matches the filling business default value template, the date type field matches the filling recent effective date template, and the character type field matches the filling standard default text template. Field non-null constraint conversion is performed within the maintenance period.

[0038] Further, when the null value proportion is greater than the proportion threshold, the generation time of all null value records of the field to be counted is extracted from the PostgreSQL transaction log and table creation or update record, the number of null value generation is summarized according to time dimension, and the null value generation time distribution curve is formed. If the null values are concentrated in a certain period and no new null values are generated subsequently, it is determined that it is a one-time exception, and the re-detection process is triggered, and the null value proportion is counted again after excluding the influence of the fault. If the null values are evenly distributed in multiple periods and continue to be generated, it is determined that it is a persistent exception, a fault work order is generated, the null value generation time distribution, associated business module and other information are included in the work order, and the work order is pushed to the development team work order system.

[0039] In an implementation of the present application, when field conversion is performed after field completion, the completed field is simulated to obtain conversion information; wherein the conversion information includes at least one of the execution plan corresponding to the conversion instruction, the resource consumption data corresponding to the execution plan, and the transaction lock occupation time. Based on the real-time running parameters of the user table where the field is located and the conversion information corresponding to the simulation conversion, the predicted lock table time is obtained. The predicted lock table time is compared with the preset lock time threshold. If the predicted lock table time is not greater than the preset lock time threshold, the field conversion instruction is directly executed. If the predicted lock table time is greater than the preset lock time threshold, the batch conversion strategy is enabled.

[0040] Specifically, after completing the data completion operation of the field to be counted and confirming that there is no empty value left, the simulation conversion mechanism is triggered to simulate the execution of the field non-empty conversion instruction to be executed. This process only generates conversion-related information without actually modifying the field attribute, avoiding affecting the production data, while ensuring that the key technical parameters during the execution of the conversion instruction can be obtained completely. In the simulation conversion process, the following conversion information is obtained: first, the execution plan corresponding to the conversion instruction, including the table scanning mode, index usage, and associated object processing logic involved in the execution process; second, the resource consumption data corresponding to the execution plan, including the estimated CPU occupancy rate, memory usage, and I / O operation times; third, the transaction lock occupation time, which is recorded by simulating the process of applying and releasing the lock, including the occupation period of the lock resource and the potential lock waiting situation. Through the system monitoring view of PostgreSQL, the running state data of the user table where the field to be counted is located is collected in real time, mainly including the current active transaction number of the table, the data page cache hit rate, the overall I / O throughput rate of the database, and the real-time change trend of the table data volume. These parameters can reflect the actual running load of the current database, providing real-time environmental dimension reference for the lock table time prediction.

[0041] Further, a preset lock table duration prediction model is called to take the collected conversion information and real-time running parameters as input variables, take the I / O throughput demand in the planning complexity and resource consumption data as a basic influence factor, take the active transaction quantity and cache hit rate as dynamic adjustment factors, and obtain a preliminary predicted lock table duration through multivariate weighted calculation. Meanwhile, historical lock table duration data of the same type field conversion are referred to to calibrate the preliminary result, so as to ensure that the predicted value is more suitable for the actual scene and reduce the deviation between theoretical calculation and actual execution. The predicted lock table duration is compared with a preset lock duration threshold value. If the predicted duration is not greater than the threshold value, it is indicated that the influence of the conversion operation on the business is controllable, and the field conversion instruction is directly executed in the current maintenance period. The lock resource usage is monitored in real time during the execution to ensure that there is no abnormal lock waiting. If the predicted duration is greater than the threshold value, the batch conversion strategy is immediately enabled, the current full conversion plan is suspended, and the batch conversion process design stage is entered instead, the lock occupancy time of single conversion is reduced by splitting the data volume, and business blocking caused by long-time lock table is avoided. In an implementation manner of the present application, the batch conversion strategy is enabled, specifically including: obtaining an identification field of a user table where the field is located, and dividing the value range of the identification field into a plurality of continuous data segments based on a preset batch data volume. In order of the identification field value of the data segment, the field conversion instruction is executed for each conversion batch in turn, the transaction lock is applied to the record in the data segment corresponding to the current conversion batch, the transaction lock resource is released after the current conversion batch is completed, and the next conversion batch is executed after a preset duration. The preset duration is related to the database lock resource release efficiency and the associated business transaction cycle duration. The actual lock table duration corresponding to each conversion batch and the real-time load parameter of the database are collected in real time. In the case that the actual lock table duration and / or the real-time load parameter of the database does not meet the threshold condition, the batch data volume and the preset duration of the next batch are dynamically adjusted.

[0042] Specifically, the identification field of the user table where the field to be converted is located is identified, and the primary key field is preferentially selected. If the primary key does not exist, the unique index field is selected as the identification field. According to the preset batch data volume, the value range of the identification field is uniformly divided into a plurality of continuous and non-overlapping data segments in combination with the minimum and maximum values of the identification field. Each segment corresponds to a conversion batch, and it is ensured that there is no repetition and omission between batches. In order of the identification field value of the data segment from small to large, the field conversion instruction of each conversion batch is triggered in turn. Before executing the current batch conversion, it is verified whether the last batch has been completed to avoid batch execution confusion. The conversion instruction is only for the field non-empty attribute adjustment of the record in the data segment corresponding to the current batch, and does not affect other segment data, thereby reducing the resource occupancy of single conversion.

[0043] Further, when performing the current conversion batch, transaction locks are applied to all records in the data segment corresponding to the batch, ensuring that the data is not modified concurrently during the conversion process, guaranteeing data consistency. After the current batch conversion is completed, the transaction lock resources are immediately released, avoiding long-term lock occupation leading to business blocking. Subsequently, a preset time period is waited for, and after the lock resources are completely released and the database load tends to be stable, the next conversion batch is executed, balancing conversion efficiency and business availability. During the execution of each conversion batch, two core indicators are collected in real time through a database monitoring tool: one is the actual lock table duration of the current batch, and the other is the real-time load parameter of the database. The actual lock table duration collected is compared with the preset lock time threshold, and the real-time load parameter of the database is compared with the preset load threshold. If the actual lock table duration exceeds the threshold or the database load parameter is abnormal, the next batch parameters are automatically adjusted: the batch data volume is reduced, and the preset interval time is lengthened, thereby reducing the pressure on the database in the subsequent batch through dynamic adjustment, ensuring that the batch conversion process is stable and controllable.

[0044] In an implementation manner of the present application, after matching the corresponding field conversion strategy based on the comparison relationship between the preset proportion threshold and the null value proportion, the associated attributes corresponding to each field are further obtained, and the associated attributes are quantitatively assigned; wherein the associated attributes at least include one of field association relationship, default value configuration state and historical null value record. The historical risk judgment data is obtained, and based on the historical error information and the business feedback factor, the dynamic weight value corresponding to each associated attribute is determined. Based on the quantitative assignment result and the dynamic weight value corresponding to each associated attribute, the risk value corresponding to each field is obtained, and based on the difference between the risk value and the preset risk threshold, the conversion risk level is determined.

[0045] Specifically, after the null ratio calculation and the conversion strategy matching are completed, the associated attributes of each field to be converted are extracted from the database metadata, business dictionary and historical operation logs, including field association relationship, default value configuration state and historical null value record. According to the preset quantification rules, each associated attribute is assigned a value, for example, 10 points are assigned for the existence of core foreign key association, 8 points are assigned for the configuration of fixed default value, and 9 points are assigned for no null value in the past 30 days. Qualitative attributes are converted into quantifiable indicators. The historical risk judgment data set is called, which contains the associated attribute quantification values in the past field conversion cases, the error information after conversion and the business feedback results. By analyzing the correlation between historical error information and each associated attribute, the basic weight is calculated, and the basic weight is dynamically adjusted combined with the business feedback factor to generate real-time dynamic weight values of each associated attribute, ensuring that the weight distribution is consistent with the actual business risk scenario. The weighted summation algorithm is used to multiply and accumulate the associated attribute quantification values of each field and the corresponding dynamic weight values to obtain the original risk value of the field. For example, the associated relationship quantification value of a certain field is 8 points (weight 30%), the default value configuration quantification value is 6 points (weight 25%), and the historical null value record quantification value is 9 points (weight 45%), so the original risk value is 8x0.3+6x0.25+9x0.45=8.25 points. Then the original risk value is normalized to convert it into a standard risk value of 0-100 points, which is convenient for subsequent level judgment.

[0046] Further, the calculated standard risk value is compared with the preset risk threshold value, and the difference between the risk value and the threshold value is calculated to divide the levels: if the risk value is lower than the low risk threshold value, the difference is positive and large, and it is determined as a low risk level; if the risk value is between the low risk threshold value and the medium risk threshold value, the difference is close to zero, and it is determined as a medium risk level; if the risk value is higher than the medium risk threshold value, the difference is negative and the absolute value is large, and it is determined as a high risk level. Different risk levels correspond to different processing strategies, for example, low risk level can automatically execute conversion, high risk level needs manual review before decision making, to ensure that the risk is controllable.

[0047] Figure 2 A structure diagram of a database field automatic detection and conversion device provided by an embodiment of the present application is shown in FIG. 1. As shown in FIG. 1, the device comprises a data preprocessing module 1, a risk judgment module 2, a risk control module 3 and a data output module 4. Figure 2As shown, the construction safety monitoring device 200 of the heat pipe laying comprises: at least one processor 201; and a memory 202 in communication connection with the at least one processor 201; wherein the memory 202 stores instructions executable by the at least one processor 201, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to: extract user tables meeting preset filtering conditions from a database, and perform field filtering on each of the user tables to obtain a field to be counted; obtain a record quantity corresponding to a user table in which the field to be counted is located, determine a corresponding table capacity determination strategy based on a comparison relationship between a preset quantity threshold and the record quantity; wherein the table capacity determination strategy at least includes one of a numerical calculation strategy and a sampling estimation strategy; determine a null value proportion corresponding to the user table in which the field to be counted is located through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison relationship between a preset proportion threshold and the null value proportion, to perform corresponding conversion on a nullable field through the field conversion strategy.

[0048] The non-volatile computer storage medium provided by the embodiments of the present application stores computer executable instructions, and the computer executable instructions are configured to: extract user tables meeting preset filtering conditions from a database, and perform field filtering on each of the user tables to obtain a field to be counted; obtain a record quantity corresponding to a user table in which the field to be counted is located, determine a corresponding table capacity determination strategy based on a comparison relationship between a preset quantity threshold and the record quantity; wherein the table capacity determination strategy at least includes one of a numerical calculation strategy and a sampling estimation strategy; determine a null value proportion corresponding to the user table in which the field to be counted is located through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison relationship between a preset proportion threshold and the null value proportion, to perform corresponding conversion on a nullable field through the field conversion strategy.

[0049] Each of the embodiments in the present application is described in a progressive manner, and the same or similar parts of each of the embodiments can be cross-referenced. Each of the embodiments focuses on the differences from other embodiments. In particular, for the device, equipment and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be cross-referenced with the part of the method embodiment.

[0050] The above only describes the embodiments of the present application and is not intended to limit the present application. The embodiments of the present application can be variously changed and modified by those skilled in the art. Such modifications or replacements do not cause the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for automatic detection and conversion of database fields, characterized in that, The method includes: Extract user tables that meet the preset filtering conditions from the database, and filter the fields of each user table to obtain the fields to be counted; Obtain the number of records corresponding to the user table containing the field to be counted, and determine the corresponding table capacity determination strategy based on the comparison relationship between the preset number threshold and the number of records; wherein, the table capacity determination strategy includes at least one of the numerical calculation strategy and the sampling estimation strategy; By using different table capacity determination strategies, the percentage of null values ​​corresponding to the user table containing the field to be counted is determined. Based on the comparison relationship between the preset ratio threshold and the proportion of null values, a corresponding field conversion strategy is matched to convert nullable fields accordingly.

2. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The step of extracting user tables that meet preset filtering conditions from the database and filtering the fields in each user table to obtain the fields to be counted specifically includes: Read the database system metadata, extract user tables that meet preset filtering conditions from the system metadata, and generate a list of tables to be scanned; wherein, the preset filtering conditions include at least one of the structural change operations or data read / write operations corresponding to the user tables within a preset time range; Based on preset column filtering conditions, each user table in the list of tables to be scanned is subjected to column removal processing to obtain the fields to be counted; wherein, the preset column filtering conditions include at least one of the following: system built-in columns, specified data type columns, and columns with declared non-null constraints.

3. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The step of determining the percentage of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies specifically includes: When the table capacity determination strategy is determined to be a numerical calculation strategy, a preset set of query statements is triggered to query the user table where the field to be counted is located, and the number of null values ​​and the total number of records are obtained. The percentage of null values ​​is obtained based on the ratio between the number of null values ​​and the total number of records.

4. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The step of determining the percentage of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies specifically includes: When the table capacity determination strategy is determined to be the sampling estimation strategy, a reference business dimension is extracted based on the current business characteristics. Mutual information processing is performed on each of the aforementioned reference business dimensions to filter out related dimensions from the reference business dimensions; The related dimensions are hierarchically divided based on the dimension values, density clustering is performed within each level, and sampling weights are assigned to different clusters. Based on the sampling weights, samples are extracted within the levels of each of the associated dimensions, and the proportion of null values ​​in the samples corresponding to each level is determined. Using the proportion of records at each level in the entire table as weights, the proportion of null values ​​in the sample is weighted and summed to obtain the proportion of null values ​​corresponding to the user table where the field to be counted is located.

5. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The comparison between the preset proportional threshold and the proportion of null values, and the matching of corresponding field conversion strategies, to perform corresponding conversions on nullable fields through the field conversion strategies, specifically includes: When the percentage of null values ​​is 0, the field is converted either instantly or within a window period based on the business type that is related to the field. When the proportion of null values ​​is greater than 0 and not greater than the proportion threshold, the field completion method is determined in the preset completion SQL template based on the field type, and the field is converted after the field is completed; If the proportion of null values ​​is greater than the proportion threshold, the generation time of the null values ​​in the field is determined, and the anomaly type is determined based on the generation time. Based on the anomaly type, the user table containing the field to be counted is re-inspected or a fault work order is generated.

6. The method for automatic detection and conversion of database fields according to claim 5, characterized in that, The process of performing field conversion on the field after field completion specifically includes: The completed fields are simulated and transformed to obtain transformation information; wherein, the transformation information includes at least one of the following: the execution plan corresponding to the transformation instruction, the resource consumption data corresponding to the execution plan, and the transaction lock occupancy duration; Based on the real-time running parameters of the user table where the field is located and the conversion information corresponding to the simulated conversion, the predicted table locking duration is obtained; The predicted table lock duration is compared with the preset lock duration threshold. If the predicted table lock duration is not greater than the preset lock duration threshold, the field conversion instruction is executed directly. If the predicted lock duration exceeds the preset lock duration threshold, then the batch conversion strategy is enabled.

7. The method for automatic detection and conversion of database fields according to claim 6, characterized in that, The implementation of the batch conversion strategy specifically includes: Obtain the identifier field of the user table where the field is located, and divide the value range of the identifier field into multiple continuous data segments based on the preset batch data volume; The field conversion instructions are executed sequentially for each conversion batch according to the value order of the identifier field of the data segment; A transaction lock is applied to the records within the data segment corresponding to the current conversion batch. After the current conversion batch is completed, the transaction lock resource is released, and the next conversion batch is executed after a preset interval. The preset interval is related to the database lock resource release efficiency and the cycle length of the associated business transaction. The actual table locking duration and real-time database load parameters corresponding to each conversion batch are collected in real time. If the actual table locking duration and / or the real-time database load parameters do not meet the threshold conditions, the batch data volume and preset duration of the next batch are dynamically adjusted.

8. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, After matching the corresponding field conversion strategy based on the comparison relationship between the preset ratio threshold and the proportion of null values, the method further includes: Obtain the associated attributes corresponding to each field, and assign quantitative values ​​to the associated attributes; wherein, the associated attributes include at least one of the following: field association relationship, default value configuration status, and historical null value record; Obtain historical risk assessment data, and determine the dynamic weight values ​​corresponding to each of the aforementioned related attributes based on historical error information and business feedback factors; Based on the quantitative assignment results and the dynamic weight values ​​corresponding to each of the associated attributes, the risk values ​​corresponding to each of the fields are obtained. The risk level is determined based on the difference between the risk value and the preset risk threshold.

9. A database field automatic detection and conversion device, characterized in that, The device includes a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method described in any one of claims 1-8.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of performing the method described in any one of claims 1-8.

Images