Intelligent data classification and grading method and system based on SQL script analysis

Abstract

The present invention belongs to the field of data analysis technology, and discloses an intelligent data classification and grading method and system based on SQL script parsing: generating a data table to be analyzed and a blood relationship table based on an SQL script, classifying target fields in the data table to be analyzed based on the blood relationship table, defining annotation information corresponding to unclassified target fields as annotations to be analyzed, classifying the annotations to be analyzed using a preset model, obtaining a certainty index value of the classification result of each annotation to be analyzed, and when the certainty index value of any annotation to be analyzed is greater than or equal to a first preset value, using the data classification information in the classification result as the data classification result of any annotation to be analyzed; otherwise, performing word segmentation processing on any annotation to be analyzed, calculating the cumulative sum of its word segmentation scores in any data classification, and using the data classification corresponding to the maximum value of the cumulative sum as its data classification result. The technical solution of the present invention can improve the efficiency of data classification and data integration capabilities.

Description

Technical Field

[0001] The present invention belongs to the technical field of data analysis, and in particular relates to an intelligent data classification and grading method and system based on SQL script parsing. Background Art

[0002] Data has become a strategic resource in our time, significantly impacting various sectors, including the economy, society, and politics. Data not only reflects various real-world conditions but also, through in-depth mining and integrated applications, provides a scientific basis for decision-making. Therefore, how to effectively manage and utilize data has become a major global issue. As data volumes increase, data security and privacy protection become increasingly prominent. To ensure data security and personal privacy, a scientific data classification and grading system is necessary, with differentiated protection measures for different types and levels of data.

[0003] Similar prior art includes Chinese patent application publication number CN116975167A, which discloses a metadata grading method and system based on weighted Jaccard coefficients. The method comprises obtaining metadata to be graded, performing word segmentation on the metadata to be graded to generate a comprehensive word set comprising multiple subsets, performing word segmentation on the grading standards corresponding to the metadata to be graded to obtain an enterprise grading standard word set comprising multiple subsets, obtaining a weighted Jaccard coefficient between the comprehensive word set and subsets of the same type within the enterprise grading standard word set, and obtaining the grade of the metadata to be graded based on the Jaccard coefficient. Another Chinese patent application publication number CN110263155A discloses a data classification method, a data classification model training method, and a system. The method comprises obtaining a database operation statement for processing a data field to be classified, parsing the database operation statement to obtain operation parameters for processing the data field to be classified, performing feature extraction on the operation parameters to obtain corresponding operation features, and calling the trained data classification model to classify the operation features to obtain a classification result for the data field to be classified.

[0004] The above-mentioned existing technologies only consider the characteristics of the data itself when classifying data, and fail to consider the classification and grading of the parent or subclass of data assets, resulting in poor data asset integration capabilities. Therefore, it is an urgent problem to provide an intelligent data classification and grading method and system based on SQL script parsing to improve the efficiency and accuracy of data classification and improve data asset integration capabilities. Summary of the Invention

[0005] In response to the above-mentioned technical problems, the present invention provides an intelligent data classification and grading method and system based on SQL script parsing.

[0006] In a first aspect, the present invention provides an intelligent data classification and grading method based on SQL script parsing, the method comprising the following steps:

[0007] Step 1: Collect SQL scripts from enterprise data assets;

[0008] Step 2: Extract key SQL statements from the SQL script, and extract target fields and annotation information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding annotation information in correspondence, and generate a data table to be analyzed;

[0009] Step 3: Parse the SQL script, analyze the relationship between data assets, and record the relationship in the relationship table;

[0010] Step 4: Classify and grade the target fields in the data table to be analyzed based on the blood relationship table and the learning database, where the learning database stores the graded and classified data asset information, including metadata table name, data asset field, annotation, category, and level;

[0011] Step 5: Define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed;

[0012] Step 6: Input all the annotations to be analyzed into the preset model for classification processing to obtain the classification results of each annotation to be analyzed, wherein the classification results include data classification information and corresponding certainty index values, and the data classification information includes category and level;

[0013] Step 7: Extract any annotation to be analyzed and determine whether the certainty index value of any annotation to be analyzed is greater than or equal to a first preset value. If so, use the data classification information of any annotation to be analyzed as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed. If not, proceed to step 8.

[0014] Step 8. Perform word segmentation on any annotation to be analyzed. Based on the word segmentation results and the score of any word segmentation in any data classification, calculate the cumulative sum of all word segmentation scores, and use the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0015] Specifically, in step 2, extracting annotation information corresponding to any target field includes:

[0016] Step 21: extract any target field and determine whether any target field has corresponding annotation information. If yes, define any target field as a first target field, and store the first target field and the annotation information corresponding to the first target field in the data table to be analyzed accordingly. If no, define any target field as a second target field, and then proceed to step 22.

[0017] Step 22: perform word segmentation on each target field using specific symbols as intervals, and perform word segmentation on the annotation information corresponding to any first target field;

[0018] Step 23: extract any second target field, define the word contained in any second target field as the first word, extract any first word, determine whether any first word is a complete English word, and if so, directly translate it to obtain the Chinese interpretation. If not, traverse all the word segments of the first target field. If any first target field contains any first word, use the Chinese interpretation of any first word in the annotation information of any first target field as the Chinese interpretation of any first word in any second target field, and repeat step 23 until all second target fields are traversed.

[0019] Step 24: Extract any second target field and determine whether the field annotation is completed. If so, update the data table to be analyzed. If not, obtain the first annotation corresponding to any second target field, where the first annotation includes the second participle and the third participle. The second participle is the Chinese interpretation of the translated first participle, and the third participle is the untranslated first participle.

[0020] Step 25: extracting a second participle having a specific relationship with the third participle from the first annotation, defining it as an associated participle; traversing the first database based on the third participle and the associated participle to obtain relevant sentences containing the associated participle; and extracting words to be screened having a specific relationship with the associated participle from the relevant sentences;

[0021] Step 26: Identify the Chinese meaning of the third participle from the vocabulary to be screened, and after traversing all the third participles in any second target field, update the data table to be analyzed;

[0022] Step 27: Repeat steps 24 to 26 until all the second target fields are annotated.

[0023] Specifically, step 25 includes:

[0024] Step 251: Search the segmentation table based on the third segmentation, obtain the Chinese interpretation corresponding to the third segmentation, and define it as the fourth segmentation;

[0025] Step 252: traverse the second database based on the third participle, the associated participle, and / or the fourth participle, identify a first sentence containing the third participle, the associated participle, and / or the fourth participle, and obtain a file identifier corresponding to the first sentence;

[0026] Step 253: extract any text identifier, extract text data corresponding to any file identifier from the first database, traverse the text data based on the associated participles, and determine whether there is a sentence containing the associated participles in the text data. If so, define it as a related sentence;

[0027] Step 254: After traversing all file identifiers, obtain all relevant sentences corresponding to the associated participles.

[0028] Specifically, in step 26, identifying the Chinese meaning of the third word from the vocabulary to be screened includes:

[0029] Step 261: extract any word to be screened, search the word segmentation table based on any word to be screened, and obtain the English word corresponding to any word to be screened;

[0030] Step 262: extract any English word and obtain a specific descriptive word corresponding to the English word;

[0031] Step 263: After traversing all English words, determine whether all specific descriptors contain the third participle. If so, define any word to be screened as the first word.

[0032] Step 264: After traversing all the words to be screened, extract any first word, define the relevant sentence corresponding to any first word as a first relevant sentence, perform word segmentation on the first relevant sentence, calculate the first distance between the associated word and any first word, and determine whether the first distance is less than or equal to a second preset value. If so, use any first word as the Chinese interpretation of the third word.

[0033] Specifically, step 3 includes:

[0034] Step 31: extract key SQL statements, perform preliminary formatting on the key SQL statements, remove all comment information in the key SQL statements, and obtain standard key SQL statements;

[0035] Step 32: Obtain target table information and target field information from the target portion of the standard key SQL statement, and assign a first code to each target field information in sequence according to the field order;

[0036] Step 33: Obtain all source information from the first-level select statement portion of the standard key SQL statement, and assign a second code to each source field information in sequence according to the field order;

[0037] Step 34: associate and identify the table information in the "from" portion of the standard key SQL statement with all source field information, and analyze the actual relationship between any source field and the second code;

[0038] Step 35: Associate and identify the second codes corresponding to all source fields with the first codes corresponding to all target fields, identify the blood relationship between data assets, and record them in the blood relationship table.

[0039] Specifically, in step 6, the training method of the preset model is:

[0040] Step 61: Use training data to train the neural network model to generate a first model, wherein the training data includes annotation information and classification information;

[0041] Step 62: Use the test data to test the first model and obtain the test results of each test data, where the test results include data classification information and corresponding certainty index values;

[0042] Step 63: Set a certainty index interval, analyze the test results of all test data, obtain a first number of test results within any certainty index interval, calculate the accuracy corresponding to any certainty index interval, and then generate a model test result based on the first number and the accuracy;

[0043] Step 64: Output and display the model test results, and then determine whether the interval adjustment instruction is received. If so, return to step 63; if not, proceed to step 65;

[0044] Step 65: Determine whether the model test result meets the preset conditions. If so, proceed to step 66; if not, return to step 61;

[0045] Step 66: Define the first model as a preset model.

[0046] Specifically, the steps before step 8 include:

[0047] Step 81: extract any data classification, obtain learning data belonging to any data classification, and perform word segmentation processing on all learning data;

[0048] Step 82: extract any learning data, extract feature words from the word segmentation of any learning data, perform statistical analysis on all feature words after traversing all learning data, set a score for each feature word, store any feature word and the score corresponding to the feature word in correspondence, and generate a feature word configuration table for any data classification;

[0049] Step 83: After traversing all data categories, extract any learning data, and calculate the cumulative sum of any learning data for each data category based on all feature word configuration tables;

[0050] Step 84: Calculate the difference between the maximum value and the second largest value of the accumulated sum, and determine whether the difference is greater than or equal to a third preset value. If not, return to step 82.

[0051] Step 85: After traversing all learning data, store the feature word configuration table.

[0052] Specifically, in step 8, based on the word segmentation results and the score of any word segmentation in any data classification, the cumulative sum of all word segmentation scores is calculated, including: defining the feature word configuration table of any data classification as a first feature word configuration table, extracting the first feature word from the word segmentation of any annotation to be analyzed based on the first feature word configuration table, and obtaining the first score of each first feature word in the first feature word configuration table, accumulating and summing all the first scores to obtain the cumulative sum.

[0053] In a second aspect, the present invention further provides an intelligent data classification and grading system based on SQL script parsing, the system comprising: a data acquisition module, a table generation module, a lineage analysis module, a lineage inheritance module, a table update module, a first self-learning module and a second self-learning module;

[0054] Data collection module, used to collect SQL scripts from enterprise data assets;

[0055] The data table generation module is used to extract key SQL statements from the SQL script, and extract the target fields and the annotation information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding annotation information in correspondence, and generate the data table to be analyzed;

[0056] The lineage analysis module is used to parse SQL scripts, analyze the lineage relationships between data assets, and record the lineage relationships in the lineage relationship table;

[0057] A lineage inheritance module is used to classify and grade target fields in the data table to be analyzed based on the lineage relationship table and the learning database, wherein the learning database stores the graded and classified data asset information, which includes metadata table names, data asset fields, comments, categories, and levels;

[0058] A data table update module is used to define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed;

[0059] The first self-learning module is used to input all the annotations to be analyzed into a preset model for classification processing, obtain the classification result of each annotation to be analyzed, wherein the classification result includes data classification information and a corresponding certainty index value, and the data classification information includes a category and a level. Extract any annotation to be analyzed, and determine whether the certainty index value of any annotation to be analyzed is greater than or equal to a first preset value. If so, the data classification information of any annotation to be analyzed is used as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and the data table to be analyzed is updated. If not, enter the second self-learning module;

[0060] The second self-learning module is used to perform word segmentation on any annotation to be analyzed, calculate the cumulative sum of all word segmentation scores based on the word segmentation results and the score of any word segmentation in any data classification, and use the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0061] The present invention discloses an intelligent data classification and grading method and system based on SQL script parsing. Compared with the prior art, the beneficial effects are at least as follows:

[0062] 1. By intelligently parsing SQL scripts, extracting key SQL statements, target fields, and corresponding annotation information, the system automatically generates data tables to be analyzed and blood relationship tables, providing an accurate data basis for subsequent classification and grading work. At the same time, by utilizing the graded and classified data asset information in the learning database and combining it with the blood relationship table, the target fields in the data table to be analyzed can be more accurately classified and graded, greatly improving the accuracy and efficiency of classification and grading, and achieving high execution efficiency.

[0063] 2. Analyzing the blood relationship between data assets and recording the blood relationship in the blood relationship table can better understand the flow and dependency of data, break down data silos, promote data sharing and collaboration, enhance comprehensive data asset integration capabilities, integrate and comprehensively analyze single data assets in series, and improve the accuracy of data classification and grading.

[0064] 3. When digital assets cannot be classified and graded based on blood relationships, the annotations to be analyzed are classified and processed through preset models and self-learning, and the data classification results of unidentified fields are automatically determined based on the deterministic indicator value and the cumulative sum of word segmentations. This enhances the adaptability and flexibility of data classification and grading, improves the accuracy of classification results, and can adapt to different data types and classification requirements. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are merely embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0066] Figure 1 This is a flow chart of the intelligent data classification and grading method based on SQL script parsing of the present invention;

[0067] Figure 2 This is a modular schematic diagram of the intelligent data classification and grading system based on SQL script parsing of the present invention. DETAILED DESCRIPTION

[0068] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below with reference to the accompanying drawings and embodiments. Obviously, the specific embodiments described herein are only used to explain the present invention and are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0069] It should be noted that if there are descriptions involving "first", "second", etc. in the embodiments of the present invention, the descriptions of "first", "second", etc. are only for descriptive purposes and cannot be understood as indicating or implying their relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of such features. In addition, the technical solutions between the various embodiments can be combined with each other, but they must be based on the ability of ordinary technicians in this field to implement them. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such a combination of technical solutions does not exist and is not within the scope of protection required by the present invention.

[0070] Figure 1 FIG. 1 is a flow chart of an embodiment of an intelligent data classification and grading method based on SQL script parsing provided by the present invention, which specifically includes the following steps:

[0071] Step 1: Collect SQL scripts from enterprise data assets.

[0072] Data assets can be acquired through JDBC or FTP. This includes metadata (tables, views) and SQL scripts to be categorized and graded. Data can be collected from various relational databases, including Hive, Oracle, MySQL, SqlServer, DM, and Oceanbase.

[0073] Step 2: Extract key SQL statements from the SQL script, and extract target fields and comment information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding comment information correspondingly, and generate a data table to be analyzed.

[0074] Extract key SQL statements, extracting all insert or update statements from the full text of the SQL script. These statements cannot contain virtual tables, such as the system.dual table (such tables have no practical application value for kinship relationships). The extracted key SQL statements are initially formatted, removing all comments, such as "--" to the end of the line and all text and symbols from " / *" to "* / ". Then, all content in the key SQL statements is converted to uppercase, and the target fields are extracted from the converted key SQL statements.

[0075] The key SQL statements are shown in Table 1, and the data table to be analyzed is shown in Table 2.

[0076] Table 1

[0077]

[0078]

[0079] Table 2

[0080]

[0081] Specifically, in step 2, extracting annotation information corresponding to any target field includes:

[0082] Step 21: extract any target field and determine whether any target field has corresponding annotation information. If so, define any target field as the first target field, and store the first target field and the annotation information corresponding to the first target field in the data table to be analyzed. If not, define any target field as the second target field, and then enter step 22.

[0083] Step 22: perform word segmentation on each target field using specific symbols as intervals, and perform word segmentation on the annotation information corresponding to any first target field.

[0084] Step 23: extract any second target field, define the word segmentation contained in any second target field as the first word segmentation, extract any first word segmentation, determine whether any first word segmentation is a complete English word, if so, directly translate it to obtain the Chinese interpretation, if not, traverse all the word segmentations of the first target field, if any first target field contains any first word segmentation, use the Chinese interpretation of any first word segmentation in the annotation information of any first target field as the Chinese interpretation of any first word segmentation in any second target field, and repeat step 23 until all second target fields are traversed.

[0085] Step 24: Extract any second target field and determine whether the field annotation is completed. If so, update the data table to be analyzed. If not, obtain the first annotation corresponding to any second target field, where the first annotation includes the second participle and the third participle. The second participle is the Chinese interpretation of the translated first participle, and the third participle is the untranslated first participle.

[0086] Step 25: Extract a second participle that has a specific relationship with the third participle from the first annotation, define it as an associated participle, traverse the first database based on the third participle and the associated participle, obtain relevant sentences containing the associated participle, and extract words to be screened that have a specific relationship with the associated participle from the relevant sentences.

[0087] Step 26: Identify the Chinese meaning of the third word from the vocabulary to be screened, and after traversing all the third words in any second target field, update the data table to be analyzed.

[0088] Step 27: Repeat steps 24 to 26 until all the second target fields are annotated.

[0089] The annotation information of the target field is an important basis for the classification of the target field. When the annotation information of the target field is missing, the annotation information of the target field is supplemented.

[0090] For example, the specific symbols are “_”, “.”, etc.

[0091] When programmers write SQL scripts, the same specific descriptor often corresponds to the same Chinese interpretation. Therefore, first searching for the Chinese interpretation of target fields without annotations through comparison can improve the speed and accuracy of adding annotations to target fields. For example, if the second target field is "SRC_SYS_NO," the second target field is segmented into "SRC," "SYS," and "NO." If the segmented words "SRC" and "NO" have the same segmented words in the first target field "SRC_CUST_NO," the interpretations of "SRC" and "NO" in the first target field are used as the Chinese interpretations. The first annotation "Source SYS Number" for the second target field "SRC_SYS_NO" is then retrieved.

[0092] For example, if two or more morphemes (words or word parts) are combined to form a new word, and this new word can be understood semantically as a whole, then the two or more morphemes are considered to have a specific relationship. For the first annotation "source SYS number" above, if "source" and "SYS," "source" and "number" and "SYS," or "number" and "SYS" can be understood as a whole, then "source" and / or "number" are considered as associated participles of "SYS."

[0093] Preferably, in step 26, while updating the data table to be analyzed, the Chinese interpretations of the same third characteristic words in other second target fields are updated based on the Chinese interpretations of the identified third characteristic words.

[0094] Specifically, step 25 includes:

[0095] Step 251: Search the word segmentation comparison table based on the third word segmentation, obtain the Chinese interpretation corresponding to the third word segmentation, and define it as the fourth word segmentation.

[0096] Step 252: traverse the second database based on the third participle, the associated participle and / or the fourth participle, identify a first sentence containing the third participle, the associated participle and / or the fourth participle, and obtain a file identifier corresponding to the first sentence.

[0097] Step 253: extract any text identifier, extract text data corresponding to any file identifier from the first database, traverse the text data based on the associated participles, and determine whether there is a sentence containing the associated participles in the text data. If so, define it as a related sentence.

[0098] Step 254: After traversing all file identifiers, obtain all relevant sentences corresponding to the associated participles.

[0099] The word segmentation table is pre-stored and set based on actual application conditions. It stores English words, Chinese definitions, and specific descriptors, such as (system, (S, SYS)). The third word segmentation may have multiple Chinese definitions.

[0100] The second database stores summaries, abstracts, or titles of various text data items in the first database. If the second database contains third participles, associated participles, and / or fourth participles, there is a greater probability of retrieving sentences containing third participles and associated participles from the first database. By filtering text data containing third participles and associated participles in the first database using the second database, the text data related to these third participles and associated participles can be more accurately located, reducing the amount of text to be processed and improving search accuracy and efficiency.

[0101] Specifically, in step 26, identifying the Chinese meaning of the third word from the vocabulary to be screened includes:

[0102] Step 261: extract any word to be screened, search the word segmentation table based on any word to be screened, and obtain the English word corresponding to any word to be screened.

[0103] Step 262: extract any English word and obtain a specific description word corresponding to the English word.

[0104] Step 263: After traversing all English words, determine whether all specific descriptors contain the third participle. If so, define any word to be screened as the first word.

[0105] Step 264: After traversing all the words to be screened, extract any first word, define the relevant sentence corresponding to any first word as a first relevant sentence, perform word segmentation on the first relevant sentence, calculate the first distance between the associated word and any first word, and determine whether the first distance is less than or equal to a second preset value. If so, use any first word as the Chinese interpretation of the third word.

[0106] The second preset value is set according to the experience of those skilled in the art or according to actual application scenarios, and is not limited in the embodiments of the present application.

[0107] For example, for the third participle "SYS" and the associated participle "number", obtain the relevant sentence containing "number" "When we insert a new record into the database, the system will automatically assign a unique system number to the new record", extract the "system" of the to-be-screened vocabulary that has a specific relationship with the associated participle "number", search the participle comparison table, obtain the English word "system" corresponding to "system", the specific descriptive words corresponding to "system" are S and SYS, containing the third participle "SYS", and the distance between the first vocabulary "system" and the associated participle "number" is 0, then take "system" as the Chinese interpretation of the third participle "SYS".

[0108] According to the technical solution of the present invention, key information can be quickly extracted from large amounts of text data, and the Chinese meanings of unrecognized word segments can be accurately identified, thereby improving the accuracy, efficiency and automation level of data processing, enhancing the comprehensibility of data assets, and providing support for subsequent solutions.

[0109] Step 3: Parse the SQL script, analyze the relationship between data assets, and record the relationship in the relationship table.

[0110] Specifically, step 3 includes:

[0111] Step 31: extract key SQL statements, perform preliminary formatting on the key SQL statements, remove all comment information in the key SQL statements, and obtain standard key SQL statements.

[0112] Step 32: Acquire target table information and target field information from the target part of the standard key SQL statement, and assign a first code to each target field information in sequence according to the field order.

[0113] Step 33: Obtain all source information from the first-level select statement portion of the standard key SQL statement, and assign a second code to each source field information in sequence according to the field order.

[0114] Step 34: associate and identify the table information in the from portion of the standard key SQL statement with all source field information, and analyze the actual relationship between any source field and the second code.

[0115] Step 35: Associate and identify the second codes corresponding to all source fields with the first codes corresponding to all target fields, identify the blood relationship between data assets, and record them in the blood relationship table.

[0116] Taking Tables 1 to 6 as examples, the technical solution of the present invention is described.

[0117] 1. Obtain target table information and target field information from the insert portion of the key SQL statement shown in Table 1, and mark them with identifiers 0 to n (first encoding), as shown in Table 3.

[0118] Table 3

[0119]

[0120]

[0121] 2. Analyze the source information from the first-level select statement of the key SQL and mark it with 0 to n (second code), as shown in Table 4.

[0122] Table 4

[0123]

[0124] 3. Based on the table information in the "from" section and the field information in the source information, perform association identification and analyze the actual relationship between the source field and the identifier, as shown in Table 5.

[0125] Table 5

[0126]

[0127] 4. Associate and identify the source field 0 to n identifiers with the target field 0 to n identifiers, and finally obtain the blood relationship table, as shown in Table 6.

[0128] Table 6

[0129]

[0130] Step 4: Classify and grade the target fields in the data table to be analyzed based on the blood relationship table and the learning database, where the learning database stores the graded and classified data asset information, which includes metadata table name, data asset field, annotation, category and level.

[0131] The data asset information stored in the learning library is shown in Table 7, including three tables: ODS.ODS_CTS_PRODCASHAC, EDW.EDW_T03_ORG_NO_MAPPING, and EDW.EDW_T01_IP_NO_MAPPING.

[0132] Table 7

[0133]

[0134] Based on the blood relationship table and the learning database, the target fields in the data table to be analyzed are classified and graded. The classification and grading results are shown in Table 8.

[0135] Table 8

[0136] SRC_SYS_NO Source system number Unidentified CPTL_ACTNO Asset Account Asset management / transaction management / transaction information SEC_ACTNO Securities Account Asset management / transaction management / transaction information OPNAC_DT Account opening date Asset management / transaction management / transaction information BRN_ORG_NO branches Asset Management / Fund Management / Basic Fund Information SRC_BRN_ORG_NO Source branch Asset Management / Fund Management / Basic Fund Information SRC_CUST_NO Source customer number Asset management / transaction management / transaction information CUST_NO Customer Number Asset management / transaction management / transaction information TRD_ACTNO Trading account Asset management / transaction management / transaction information FND_CO_NO Fund company number Asset management / transaction management / transaction information FND_CD Fund Code Asset management / transaction management / transaction information CASH_MNY_TRG_STAT_CD Cash management trigger status Asset management / transaction management / transaction instruction information CASH_MNY_TRG_AMT Cash investment trigger amount Asset management / transaction management / transaction instruction information CASH_MNY_ACCNT_STAT_CD Cash management account status Asset management / transaction management / transaction instruction information OCC_DT Date of occurrence Asset management / transaction management / transaction information ETL_DATE Loading time Asset Management / Fund Management / Basic Fund Information ETL_LD_TM ETL loading time Unidentified ETL_LD_JOB ETL loading jobs Unidentified

[0137] Step 5. Define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed.

[0138] As shown in SRC_SYS_NO, ETL_LD_TM, and ETL_LD_JOB in Table 8, the classification and grading were not successful and further identification is required.

[0139] Step 6: Input all the annotations to be analyzed into the preset model for classification processing to obtain the classification results of each annotation to be analyzed, wherein the classification results include data classification information and corresponding certainty index values, and the data classification information includes category and level.

[0140] Specifically, in step 6, the training method of the preset model is:

[0141] Step 61: Use training data to train the neural network model to generate a first model, wherein the training data includes annotation information and classification information.

[0142] Step 62: Use the test data to test the first model and obtain the test results of each test data. The test results include data classification information and corresponding certainty index values.

[0143] Step 63: Set a certainty index interval, analyze the test results of all test data, obtain a first number of test results in any certainty index interval, calculate the accuracy corresponding to any certainty index interval, and then generate a model test result based on the first number and the accuracy.

[0144] Step 64 , output and display the model test result, and then determine whether the interval adjustment instruction is received. If so, return to step 63 , if not, enter step 65 .

[0145] Step 65 : Determine whether the model test result meets the preset conditions. If so, proceed to step 66 ; if not, return to step 61 .

[0146] Step 66: Define the first model as a preset model.

[0147] Preferably, the hierarchical information of the data corresponds to the classification information, and the hierarchical information can be obtained if the classification information is known.

[0148] The certainty index value is the confidence level of the model in its prediction results. For example, if the model determines that data A has a 90% probability of belonging to class X, the certainty index value of the model that data A belongs to class X is 0.9.

[0149] For example, the model tests 100 data, and sets the certainty index interval to [0, 0.5), [0.5, 0.8), [0.8, 1]. Analyzing the test results, the certainty index values ​​of the test results of 30 test data are in the interval [0.8, 0.9]. Among them, 28 test results are correctly tested, and the accuracy rate corresponding to this interval is 93.3%. After outputting the model test results, an interval adjustment instruction input by the user is received, and the certainty index interval is adjusted based on the interval adjustment instruction. For example, the certainty index interval is [0, 0.3), [0.3, 0.9), [0.9, 1]. Analyzing the test results, the certainty index values ​​of the test results of 25 test data are in the interval [0.9, 1]. Among them, 24 test results are correctly tested, and the accuracy rate corresponding to this interval is 96%.

[0150] Exemplarily, the above preset conditions are that the accuracy corresponding to the optimal certainty index interval ([0.9, 1]) is greater than or equal to a fourth preset value and / or the ratio of the first number to the total number of test data is greater than or equal to a fifth preset value.

[0151] According to the technical solution of the present invention, during model training, the test results are divided into multiple groups based on the deterministic index value, making it easier for users to verify the accuracy of the classification and adjust the display of the classification results as needed. This dynamic adjustment capability enables the model to respond to changes more flexibly. Users can adjust the deterministic index interval according to their needs to adapt to different application scenarios and risk preferences, achieving more flexible data management and decision support. At the same time, by analyzing data in different deterministic index intervals, the performance of the model can be better understood, and the model parameters can be adjusted accordingly to improve the overall classification accuracy. Based on the above solution, it is possible to confirm the accuracy of the classification model's estimation of data classification and the overall deterministic tendency of the data object.

[0152] Step 7: Extract any annotation to be analyzed, and determine whether the certainty index value of any annotation to be analyzed is greater than or equal to the first preset value. If so, use the data classification information of any annotation to be analyzed as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed. If not, proceed to step 8.

[0153] The first preset value is set based on the experience of those skilled in the art or based on actual application scenarios, and is not limited in the embodiments of the present application.

[0154] Data with high certainty index values ​​are more likely to be classified correctly, while data with low certainty index values ​​are more likely to be classified correctly and need further verification.

[0155] Step 8. Perform word segmentation on any annotation to be analyzed. Based on the word segmentation results and the score of any word segmentation in any data classification, calculate the cumulative sum of all word segmentation scores, and use the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0156] Preferably, the cumulative sums of the annotations to be analyzed in different data categories are converted to a common scale, and then the maximum values ​​are compared. For example, the maximum and minimum values ​​of the annotations to be analyzed in different data categories are obtained. For any data category, the first difference between the cumulative sum and the minimum value, as well as the second difference between the maximum and the minimum value, are calculated. The ratio of the first difference to the second difference is used as the score value of the data category. After traversing all data categories, the data category corresponding to the maximum score value is used as the data classification result of the annotation to be analyzed.

[0157] Specifically, the steps before step 8 include:

[0158] Step 81: extract any data classification, obtain the learning data belonging to any data classification, and perform word segmentation processing on all learning data.

[0159] Step 82: extract any learning data, extract feature words from the word segmentation of any learning data, perform statistical analysis on all feature words after traversing all learning data, set a score for each feature word, store any feature word and the score corresponding to any feature word in correspondence, and generate a feature word configuration table for any data classification.

[0160] Step 83: After traversing all data categories, extract any learning data, and calculate the cumulative sum of any learning data for each data category based on all feature word configuration tables.

[0161] Step 84 : Calculate the difference between the maximum value and the second largest value of the accumulated sum, and determine whether the difference is greater than or equal to a third preset value; if not, return to step 82 .

[0162] Step 85: After traversing all learning data, store the feature word configuration table.

[0163] The third preset value is set according to the experience of those skilled in the art or according to actual application scenarios, and is not limited in the embodiments of the present application.

[0164] For example, for the data classification "asset management / transaction management / transaction information", a preset number of learning data belonging to the classification is obtained, the learning data "source customer number" is segmented, and the segmentation result "source / customer / number" or "source / customer number" is obtained, and "customer" "number" or "customer number" is used as the feature word of the learning data. After extracting the feature words of all learning data, the number of each feature word is counted, and a score is set for each feature word based on the number, or the influence of each feature word on the classification result is determined by training a machine learning model (such as a support vector machine SVM, random forest, etc.) to set the score for each feature word, or the score for each feature word is set by manual operation or other methods.

[0165] For example, the feature word configuration table corresponding to data classification B "Asset Management / Fund Management / Fund Basic Information" is ((Branch, 7), (Number, 2)), and the feature word configuration table corresponding to data classification C "Asset Management / Transaction Management / Transaction Information" is ((Customer, 8), (Number, 1)). For the data "Source Branch Number", the cumulative sum for data classification B is 9, and the cumulative sum for data classification C is 1. The cumulative sum corresponding to the correctly classified data classification B is much larger than the cumulative sum of the incorrectly classified data classification C.

[0166] Specifically, in step 8, based on the word segmentation results and the score of any word segmentation in any data classification, the cumulative sum of all word segmentation scores is calculated, including: defining the feature word configuration table of any data classification as a first feature word configuration table, extracting the first feature word from the word segmentation of any annotation to be analyzed based on the first feature word configuration table, and obtaining the first score of each first feature word in the first feature word configuration table, accumulating and summing all the first scores to obtain the cumulative sum.

[0167] Figure 2 FIG. 1 is a schematic diagram of an embodiment of an intelligent data classification and grading system based on SQL script analysis provided by the present invention. Figure 2 As shown, the system includes: a data acquisition module 10, a number table generation module 20, a blood relationship analysis module 30, a blood relationship inheritance module 40, a number table update module 50, a first self-learning module 60 and a second self-learning module 70.

[0168] The data collection module 10 is used to collect SQL scripts in enterprise data assets.

[0169] The data table generation module 20 is used to extract key SQL statements from the SQL script, and extract target fields and comment information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding comment information correspondingly, and generate a data table to be analyzed.

[0170] The lineage analysis module 30 is used to parse the SQL script, analyze the lineage relationship between data assets, and record the lineage relationship in the lineage relationship table.

[0171] The bloodline inheritance module 40 is used to classify and grade the target fields in the data table to be analyzed based on the bloodline relationship table and the learning database, wherein the learning database stores the graded and classified data asset information, and the data asset information includes metadata table name, data asset field, annotation, category and level.

[0172] The data table update module 50 is used to define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed.

[0173] The first self-learning module 60 is used to input all the annotations to be analyzed into the preset model for classification processing, and obtain the classification result of each annotation to be analyzed, wherein the classification result includes data classification information and the corresponding certainty index value, and the data classification information includes category and level. Any annotation to be analyzed is extracted, and it is determined whether the certainty index value of any annotation to be analyzed is greater than or equal to the first preset value. If so, the data classification information of any annotation to be analyzed is used as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and the data table to be analyzed is updated. If not, the second self-learning module 70 is entered.

[0174] The second self-learning module 70 is used to perform word segmentation processing on any annotation to be analyzed, calculate the cumulative sum of all word segmentation scores based on the word segmentation results and the score of any word segmentation in any data classification, take the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0175] It should be understood that, although the various steps in the flow chart of each embodiment of the present invention are shown in sequence according to the indication of the arrows, these steps are not necessarily performed in sequence according to the order indicated by the arrows. Unless otherwise specified herein, the execution of these steps is not strictly limited in order, and these steps can be performed in other orders. Moreover, at least a portion of the steps in each embodiment may include a plurality of sub-steps or a plurality of stages, and these sub-steps or stages are not necessarily performed at the same time, but can be performed at different times, and the execution order of these sub-steps or stages is not necessarily performed in sequence, but can be performed in turn or alternately with at least a portion of other steps or sub-steps or stages of other steps.

[0176] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program. The above-mentioned program can be stored in a non-volatile computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0177] The above embodiments merely represent preferred implementations of the present invention. While the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present invention. It should be noted that a person skilled in the art would be able to make numerous variations and improvements without departing from the spirit of the present invention, all of which fall within the scope of protection of the present invention.

Claims

1. An intelligent data classification and grading method based on SQL script analysis, characterized in that: The steps include: Step 1: Collect SQL scripts from enterprise data assets; Step 2: extract key SQL statements from the SQL script, and extract target fields and annotation information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding annotation information in correspondence with each other, and generate a data table to be analyzed; Step 3: Parse the SQL script, analyze the blood relationship between data assets, and record the blood relationship in the blood relationship table; Step 4: Classify and grade the target fields in the data table to be analyzed based on the blood relationship table and the learning database, wherein the learning database stores graded and classified data asset information, and the data asset information includes metadata table name, data asset field, annotation, category, and level; Step 5: Define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed; Step 6: Input all the annotations to be analyzed into the preset model for classification processing to obtain the classification results of each annotation to be analyzed, wherein the classification results include data classification information and corresponding certainty index values, and the data classification information includes category and level; Step 7: Extract any annotation to be analyzed, determine whether the certainty index value of any annotation to be analyzed is greater than or equal to a first preset value, if so, use the data classification information of any annotation to be analyzed as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed; if not, proceed to step 8; Step 8: Perform word segmentation on any of the annotations to be analyzed, and based on the word segmentation results and the score of any word segmentation in any data classification, calculate the cumulative sum of the scores of all word segmentations, and use the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any of the annotations to be analyzed, and update the data table to be analyzed.

2. The method according to claim 1, characterized in that In step 2, extracting annotation information corresponding to any target field includes: Step 21: extract any of the target fields, determine whether any of the target fields has corresponding annotation information, if yes, define any of the target fields as a first target field, and store the first target field and the annotation information corresponding to the first target field in the data table to be analyzed accordingly; if no, define any of the target fields as a second target field, and then proceed to step 22; Step 22: perform word segmentation on each target field using specific symbols as intervals, and perform word segmentation on the annotation information corresponding to any first target field; Step 23: extract any second target field, define the segmentation contained in any second target field as a first segmentation, extract any first segmentation, determine whether any first segmentation is a complete English word, and if so, directly translate it to obtain a Chinese interpretation; if not, traverse all segmentations of the first target field, and if any first target field contains any first segmentation, use the Chinese interpretation of any first segmentation in the annotation information of any first target field as the Chinese interpretation of any first segmentation in any second target field, and repeat step 23 until all second target fields are traversed; Step 24: extract any second target field, determine whether the field annotation is completed, and if so, update the data table to be analyzed; if not, obtain the first annotation corresponding to any second target field, wherein the first annotation includes a second participle and a third participle, the second participle being the Chinese interpretation of the translated first participle, and the third participle being the untranslated first participle; Step 25: extracting the second participle that has a specific relationship with the third participle from the first annotation, defining it as an associated participle; traversing the first database based on the third participle and the associated participle to obtain relevant sentences containing the associated participle; and extracting, from the relevant sentences, words to be screened that have the specific relationship with the associated participle; Step 26: Identify the Chinese meaning of the third word from the vocabulary to be screened, and after traversing all the third words in any of the second target fields, update the table of data to be analyzed; Step 27: Repeat steps 24 to 26 until all the second target fields are annotated.

3. The method according to claim 2, characterized in that The step 25 comprises: Step 251: Search a segmentation comparison table based on the third segmentation, obtain the Chinese interpretation corresponding to the third segmentation, and define it as the fourth segmentation; Step 252: traverse the second database based on the third participle, the associated participle, and / or the fourth participle, identify a first sentence containing the third participle, the associated participle, and / or the fourth participle, and obtain a file identifier corresponding to the first sentence; Step 253: extract any text identifier, extract text data corresponding to any file identifier from the first database, traverse the text data based on the associated participle, determine whether there is a sentence containing the associated participle in the text data, and if so, define it as the relevant sentence; Step 254: After traversing all file identifiers, obtain all relevant sentences corresponding to the associated participles.

4. The method according to claim 2, characterized in that In step 26, identifying the Chinese meaning of the third word from the vocabulary to be screened includes: Step 261: extract any word to be screened, search a word segmentation table based on any word to be screened, and obtain an English word corresponding to any word to be screened; Step 262: extract any English word and obtain a specific descriptive word corresponding to any of the English words; Step 263: After traversing all English words, determine whether all specific descriptive words contain the third participle. If so, define any of the words to be screened as the first word. Step 264: After traversing all the words to be screened, extract any first word, define the related sentence corresponding to any of the first words as a first related sentence, perform word segmentation processing on the first related sentence, calculate the first distance between the associated word and any of the first words, and determine whether the first distance is less than or equal to a second preset value. If so, use any of the first words as the Chinese interpretation of the third word.

5. The method according to claim 1, wherein The step 3 comprises: Step 31: extract the key SQL statement, perform preliminary formatting on the key SQL statement, remove all comment information in the key SQL statement, and obtain a standard key SQL statement; Step 32: Acquire target table information and target field information from the target portion of the standard key SQL statement, and assign a first code to each target field information in sequence according to the field order; Step 33: Acquire all source information from the first-level select statement portion of the standard key SQL statement, and assign a second code to each source field information in sequence according to the field order; Step 34: Associating and identifying the table information in the "from" portion of the standard key SQL statement with all source field information, and analyzing the actual relationship between any source field and the second code; Step 35: Associate and identify the second codes corresponding to all source fields with the first codes corresponding to all target fields, identify the blood relationship between the data assets, and record them in the blood relationship table.

6. The method according to claim 1, characterized in that In step 6, the training method of the preset model is: Step 61: Using training data to train a neural network model to generate a first model, wherein the training data includes annotation information and classification information; Step 62: Use test data to test the first model and obtain a test result for each test data, wherein the test result includes the data classification information and the corresponding certainty index value; Step 63: Set a certainty index interval, analyze the test results of all test data, obtain a first number of the test results within any certainty index interval, calculate the accuracy corresponding to any certainty index interval, and then generate a model test result based on the first number and the accuracy; Step 64: Output and display the model test result, and then determine whether the interval adjustment instruction is received. If so, return to step 63; if not, go to step 65; Step 65: Determine whether the model test result meets the preset conditions. If so, proceed to step 66; if not, return to step 61; Step 66: Define the first model as the preset model.

7. The method according to claim 1, characterized in that The step 8 includes: Step 81: extract any of the data categories, obtain learning data belonging to any of the data categories, and perform word segmentation processing on all the learning data; Step 82: extract any learning data, extract feature words from the word segmentation of any learning data, perform statistical analysis on all feature words after traversing all the learning data, set the score for each feature word, store any feature word and the score corresponding to any feature word in correspondence, and generate a feature word configuration table for any data classification; Step 83: After traversing all data categories, extract any of the learning data, and calculate the cumulative sum of any of the learning data for each data category based on all feature word configuration tables; Step 84: Calculate the difference between the maximum value and the second largest value of the accumulated sum, and determine whether the difference is greater than or equal to a third preset value. If not, return to step 82. Step 85: After traversing all the learning data, store the feature word configuration table.

8. The method according to claim 7, characterized in that In step 8, the calculation of the cumulative sum of the scores of all segmentations based on the segmentation results and the score of any segmentation in any data classification includes: defining the feature word configuration table of any data classification as a first feature word configuration table, extracting the first feature word from any segmentation of the annotation to be analyzed based on the first feature word configuration table, and obtaining the first score of each first feature word in the first feature word configuration table, accumulating and summing all the first scores to obtain the cumulative sum.

9. An intelligent data classification and grading system based on SQL script analysis, used to implement the method according to any one of claims 1 to 8, characterized in that: include: Data acquisition module, data table generation module, blood relationship analysis module, blood relationship inheritance module, data table update module, first self-learning module and second self-learning module; The data collection module is used to collect SQL scripts in enterprise data assets; The data table generation module is used to extract key SQL statements from the SQL script, and extract target fields and annotation information corresponding to any target field from the target part of the key SQL statement, store the target fields and the corresponding annotation information in correspondence with each other, and generate a data table to be analyzed; The blood relationship analysis module is used to parse the SQL script, analyze the blood relationship between data assets, and record the blood relationship in the blood relationship table; The lineage inheritance module is configured to classify and grade the target fields in the data table to be analyzed based on the lineage relationship table and a learning database, wherein the learning database stores graded and classified data asset information, the data asset information including metadata table names, data asset fields, comments, categories, and levels; The data table updating module is used to define the target fields that have been successfully classified and graded as identified fields, update the data table to be analyzed based on the category information and level information of the identified fields, define the target fields that have not been successfully classified and graded as unidentified fields, and define the annotation information corresponding to the unidentified fields as annotations to be analyzed; The first self-learning module is used to input all the annotations to be analyzed into a preset model for classification processing, obtain the classification result of each annotation to be analyzed, wherein the classification result includes data classification information and a corresponding certainty index value, and the data classification information includes a category and a level, extract any annotation to be analyzed, and determine whether the certainty index value of any annotation to be analyzed is greater than or equal to a first preset value. If so, use the data classification information of any annotation to be analyzed as the data classification result of the unidentified field corresponding to any annotation to be analyzed, and update the data table to be analyzed. If not, enter the second self-learning module; The second self-learning module is used to perform word segmentation processing on any of the annotations to be analyzed, calculate the cumulative sum of the scores of all the word segments based on the word segmentation results and the score of any word segmentation in any data classification, take the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unidentified field corresponding to any of the annotations to be analyzed, and update the data table to be analyzed.

Images