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

Abstract

The invention belongs to the technical field of data analysis, and discloses an intelligent data classification and grading method and system based on SQL script analysis. A to-be-analyzed data table and a blood relationship table are generated based on an SQL script, and target fields in the to-be-analyzed data table are classified based on the blood relationship table; the annotation information corresponding to the unclassified target fields is defined as to-be-analyzed annotations, the to-be-analyzed annotations are classified through a preset model, the deterministic index value of the classification result of each to-be-analyzed annotation is obtained, and when the deterministic index value of any to-be-analyzed annotation is larger than or equal to a first preset value, the to-be-analyzed annotations are classified. And taking data classification information in the classification result as a data classification result of any to-be-analyzed annotation, otherwise, carrying out word segmentation processing on any to-be-analyzed annotation, calculating the cumulative sum of scores of segmented words in any data classification, and taking the data classification corresponding to the maximum value of the cumulative sum as a data classification result of the data classification. According to the technical scheme, the data classification efficiency and the data integration capability can be improved.

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 analysis. Background Art

[0002] Data has become a strategic resource in the contemporary world, and has a significant impact on various fields such as economy, society, and politics. Data can not only reflect various situations in the real world, but also provide a scientific basis for decision-making through deep mining and integrated applications. Therefore, how to effectively manage and utilize data has become a major global issue. With the increase in the amount of data, data security and privacy protection issues have become increasingly prominent. In order to ensure data security and personal privacy, it is necessary to establish a scientific data classification and grading system and take different protection measures for different types and levels of data.

[0003] Similar prior art includes a Chinese patent application with publication number CN116975167A, which discloses a metadata classification method and system based on weighted Jaccard coefficient, obtaining metadata to be classified; performing word segmentation processing on the metadata to be classified to generate a comprehensive word set including multiple subsets, performing word segmentation processing on the corresponding classification standards of the metadata to be classified to obtain an enterprise classification standard word set including multiple subsets, obtaining the weighted Jaccard coefficient between the comprehensive word set and the same type of subsets in the enterprise classification standard word set, and obtaining the level of the metadata to be classified based on the Jaccard coefficient. There is also a Chinese patent application with publication number CN110263155A, which discloses a data classification method, a data classification model training method and system, obtaining a database operation statement for processing a data field to be classified; parsing the database operation statement to obtain an operation parameter for processing the data field to be classified; performing feature extraction on the operation parameter to obtain a corresponding operation feature; calling the trained data classification model to classify the operation feature to obtain a classification result of 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 the data asset, and the data asset integration capability is poor. 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 the data asset integration capability. 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, extract target fields from the target part of the key SQL statements, and annotation information corresponding to any target field, store the target fields and the corresponding annotation information correspondingly, and generate a data table to be analyzed;

[0009] Step 3: Parse the SQL script, analyze the blood relationship between data assets, and record the blood relationship in the blood 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, wherein the learning database stores the graded and classified data asset information, and the data asset information includes the 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, and obtain the classification result of each annotation to be analyzed, wherein the classification result includes data classification information and corresponding certainty index value, and the data classification information includes category and level;

[0013] 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 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;

[0014] Step 8: Perform word segmentation on any annotation to be analyzed. Based on the word segmentation result and the score of any word segmentation in any data classification, calculate the cumulative sum of all word segmentation scores, take the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unrecognized 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, determine whether any target field has corresponding annotation information, if yes, 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 accordingly, if no, define any target field as the second target field, and then enter step 22;

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

[0018] 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, then 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, repeat step 23 until all second target fields are traversed;

[0019] Step 24: extract any second target field, 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, wherein 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 the second participle as an associated participle, traversing the first database based on the third participle and the associated participle, obtaining 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 word from the words to be screened, and after traversing all the third words 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 comparison 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 participle, determine whether there is a sentence containing the associated participle in the text data, and 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 comparison 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 description word corresponding to the English word;

[0031] Step 263: after traversing all English words, determine whether all specific descriptive words contain the third participle, and 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 the first relevant sentence, perform word segmentation processing on the first relevant sentence, calculate the first distance between the associated word segmentation and any first word, and determine whether the first distance is less than or equal to the second preset value. If so, use any first word as the Chinese interpretation of the third word segmentation.

[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: 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;

[0036] Step 33: Obtain all source information from the first-level select statement part 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 of the from part 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 result of each test data, where the test result includes data classification information and a corresponding certainty index value;

[0042] 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 rate corresponding to any certainty index interval, and then generate a model test result based on the first number and the accuracy rate;

[0043] 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;

[0044] Step 65, determine whether the model test result meets the preset conditions, if yes, proceed to step 66, if no, 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 any 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, calculating the difference between the maximum value and the second largest value of the accumulated sum, and determining whether the difference is greater than or equal to a third preset value, if not, returning 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 any word 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.

[0053] In a second aspect, the present invention also provides an intelligent data classification and grading system based on SQL script analysis, the system comprising: a data acquisition module, a table generation module, a blood relationship analysis module, a blood relationship 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 in enterprise data assets;

[0055] A data table generation module is used to extract key SQL statements from SQL scripts, and extract target fields and annotation information corresponding to any target field from the target part of the key SQL statements, store the target fields and the corresponding annotation information correspondingly, and generate a 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, and the data asset information includes metadata table name, data asset field, annotation, category and level;

[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, and 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, take the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unrecognized 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 analysis. Compared with the prior art, the beneficial effects are at least as follows:

[0062] 1. Through intelligent parsing of 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 classified data asset information in the learning database and combining 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. Analyze the blood relationship between data assets and record the blood relationship in the blood relationship table, so as to better understand the flow and dependency of data, break down data silos, promote data sharing and collaboration, enhance the overall 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 through blood relationship, 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, which 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 drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.

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

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

[0068] In order to make the purpose, technical scheme and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with 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 of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work belong to 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 used 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 the 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 the 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 flowchart of an embodiment of an intelligent data classification and grading method based on SQL script parsing provided by the present invention, and the flowchart specifically includes the following steps:

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

[0072] Metadata can be collected through JDBC or FTP to obtain data assets, including metadata information (tables, views) and SQL script information to be classified and graded. The database types for data collection support various relational databases such as Hive, Oracle, Mysql, SqlServer, DAMO, Oceanbase, etc.

[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 field and the corresponding comment information correspondingly, and generate a data table to be analyzed.

[0074] Extract key SQL statements and extract all insert or update statements from the full text of the SQL script. The statement cannot contain virtual tables, such as the system.dual table (this type of table has no practical application value for blood relations). Perform preliminary formatting on the extracted key SQL statements, remove all comment information in the statements, such as all text and symbols from "--" to the end of the line and from " / *" to "* / ", and then convert all the contents in the key SQL statements to uppercase, and then extract the target field 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, determine whether any target field has corresponding annotation information, if yes, 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 accordingly, if not, define any target field as the second target field, and then enter step 22.

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

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

[0085] Step 24, extract any second target field, 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, wherein 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 having 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 words 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] Exemplarily, the specific symbols are “_”, “.”, etc.

[0091] When programmers write SQL scripts, the same specific descriptive word will correspond to the same Chinese interpretation. Therefore, for target fields without annotation information, first find the Chinese interpretation by comparison to improve the speed and accuracy of adding annotations to target fields. For example, the second target field is "SRC_SYS_NO", and the second target field is segmented into "SRC", "SYS" and "NO". For the segmented words "SRC" and "NO", there are the same segmented words in the first target field "SRC_CUST_NO", then the interpretations of "SRC" and "NO" in the first target field are used as their Chinese interpretations, and then the first annotation "Source SYS Number" of the second target field "SRC_SYS_NO" is obtained.

[0092] For example, two or more morphemes (words or parts of words) can be combined to form a new word, and this new word can be understood as a whole in terms of semantics, and it is considered that there is a specific relationship between the two or more morphemes. For the first annotation "source SYS number" above, "source" and "SYS", "source" and "number" and "SYS", or "number" and "SYS" can be understood as a whole, and "source" and / or "number" are taken as associated participles of "SYS".

[0093] Preferably, in step 26, while updating the to-be-analyzed data table, 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 comparison table is pre-stored and set based on actual application conditions, and stores English words, Chinese definitions, and specific descriptive words, such as (system, system, (S, SYS)). The third word segmentation may correspond to multiple Chinese definitions.

[0100] The second database stores the overview, abstract or title of each text data in the first database. When the second database contains the third participle, the associated participle and / or the fourth participle, the probability of obtaining the sentence containing the third participle and the associated participle from the first database is relatively high. By filtering the text data containing the third participle and the associated participle in the first database through the second database, the text data related to these third participle and the associated participle can be more accurately located, reducing the amount of text to be processed and improving the 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 comparison 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 descriptive words 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 the first relevant sentence, perform word segmentation processing on the first relevant sentence, calculate the first distance between the associated word segmentation and any first word, and determine whether the first distance is less than or equal to the second preset value. If so, use any first word as the Chinese interpretation of the third word segmentation.

[0106] The second preset value is set according to the experience of those skilled in the art or according to an actual application scenario, and the embodiment of the present application does not limit this.

[0107] Exemplarily, for the third participle "SYS" and the associated participle "number", the relevant sentence containing "number" is obtained, "When we insert a new record into the database, the system will automatically assign a unique system number to the new record", and the word "system" to be screened that has a specific relationship with the associated participle "number" is extracted, and the participle comparison table is searched to obtain the English word "system" corresponding to "system". The specific descriptive words corresponding to "system" are S and SYS, which contain the third participle "SYS", and the distance between the first word "system" and the associated participle "number" is 0, then "system" is used 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 a large amount of text data, and the Chinese meaning 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 blood relationship between data assets, and record the blood relationship in the blood 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 part 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 from part table information 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 the target table information and target field information from the insert part of the key SQL statement shown in Table 1, and mark them with 0 to n identifiers (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~n (second code), as shown in Table 4.

[0122] Table 4

[0123]

[0124] 3. According to the table information in the from part and the field information in the source information, the actual relationship between the source field and the identifier is analyzed, as shown in Table 5.

[0125] Table 5

[0126]

[0127] 4. Associate and identify the source field 0~n identifiers with the target field 0~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, 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.

[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 Unrecognized 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 Branch Offices 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 Unrecognized ETL_LD_JOB ETL loading jobs Unrecognized

[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 successfully performed 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 result of each annotation to be analyzed, wherein the classification result includes data classification information and corresponding certainty index value, 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 result of each test data, where the test result includes data classification information and a corresponding certainty index value.

[0143] Step 63: Set a deterministic index interval, analyze the test results of all test data, obtain a first number of test results in any deterministic index interval, calculate the accuracy corresponding to any deterministic 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 results, 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 value of the model's confidence level in its prediction results. For example, if the model determines that data A has a 90% chance of belonging to class X, then the model's certainty index value for data A belonging to class X is 0.9.

[0149] Exemplarily, the model tests 100 data, and sets the certainty index interval to [0, 0.5), [0.5, 0.8), [0.8, 1]. The test results are analyzed, and 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 the model test results are output, the 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]. The test results are analyzed, and 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, when the model is trained, the test results are divided into multiple groups based on the deterministic index value, so that users can more easily 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, and achieve more flexible data management and decision support. At the same time, by analyzing the data of 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 according to the experience of those skilled in the art or according to the actual application scenario, and the embodiment of the present application does not limit this. Exemplarily, the first preset value is 0.85.

[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 result and the score of any word segmentation in any data classification, calculate the cumulative sum of all word segmentation scores, take the data classification corresponding to the maximum value of the cumulative sum as the data classification result of the unrecognized field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0156] Preferably, the cumulative sum of the annotations to be analyzed in different data classifications is 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 classifications are obtained, and for any data classification, the first difference between the cumulative sum and the minimum value of the data classification and the second difference between the maximum value and the minimum value are calculated, and the ratio of the first difference to the second difference is used as the score value of the data classification. After traversing all data classifications, the data classification corresponding to the maximum score value is used as the data classification result of the annotations 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 the learning data.

[0159] Step 82, extract any learning data, extract feature words from the word segmentation of any learning data, after traversing all the learning data, perform statistical analysis on all feature words, set scores 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 the actual application scenario, and the embodiment of the present application is not limited to this.

[0164] Exemplarily, for the data classification "asset management / transaction management / transaction information", a preset number of learning data belonging to the classification is obtained, and the learning data "source customer number" is segmented to obtain the segmentation result "source / customer / number" or "source / customer number", 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 support vector machine SVM, random forest, etc.) to set the score of each feature word, or the score of each feature word is set by manual operation or other methods.

[0165] Exemplarily, the feature word configuration table corresponding to data classification B "Asset Management / Fund Management / Basic Fund Information" is ((Branch, 7), (Number, 2)), and the feature word configuration table corresponding to data classification C "Asset Management / Trading 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 any word 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.

[0167] Figure 2 FIG. 1 is a schematic diagram of a structure 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 table generation module 20, a blood relationship analysis module 30, a blood relationship inheritance module 40, a 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 annotation information corresponding to any target field from the target part of the key SQL statement, store the target field and the corresponding annotation 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 names, data asset fields, comments, categories and levels.

[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 a 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 a corresponding certainty index value, and the data classification information includes a category and a 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 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, 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 unrecognized field corresponding to any annotation to be analyzed, and update the data table to be analyzed.

[0175] It should be understood that, although each step in the flow chart of each embodiment of the present invention is shown in sequence according to the indication of the arrow, these steps are not necessarily performed in sequence according to the order indicated by the arrow. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, 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 can understand that all or part of the processes in the above-mentioned embodiment methods can be completed 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 may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in various 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 only express the preferred implementation mode of the present invention, and the description is relatively specific and detailed, but it cannot be understood as limiting the scope of the present invention. It should be pointed out that for ordinary technicians in this field, several modifications and improvements can be made without departing from the concept of the present invention, which all belong to the protection scope 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, extract target fields from the target part of the key SQL statements, and annotation information corresponding to any target field, store the target fields and the corresponding annotation information correspondingly, 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 the 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 field that has been successfully classified and graded as an identified field, update the data table to be analyzed based on the category information and level information of the identified field, define the target field that has not been successfully classified and graded as an unidentified field, and define the annotation information corresponding to the unidentified field as an annotation to be analyzed; Step 6: 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 corresponding certainty index value, 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 processing 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 unrecognized 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 of the target fields 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 to-be-analyzed data table 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 processing on each target field at intervals of specific symbols, and perform word segmentation processing on the annotation information corresponding to any first target field; 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 segments 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, repeat step 23 until all second target fields are traversed; Step 24: extract any of the second target fields, 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 of the second target fields, wherein the first annotation includes a second participle and a third participle, the second participle is the Chinese interpretation of the translated first participle, and the third participle is the untranslated first participle; Step 25: extracting the 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, acquiring related sentences containing the associated participle, and extracting words to be screened having the specific relationship with the associated participle from the related sentences; Step 26: Identify the Chinese meaning of the third word from the words 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 step 24 to step 26 until the annotation of all the second target fields is completed.

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 related 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 to-be-screened vocabulary includes: Step 261: extract any word to be screened, search the word segmentation comparison table based on any word to be screened, and obtain the 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, and 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 a related sentence corresponding to any of the first words as a first related sentence, perform word segmentation processing on the first related sentence, calculate a 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, characterized in that 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: acquiring target table information and target field information from the target part of the standard key SQL statement, and assigning 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 part 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: associate and identify the from part table information of the standard key SQL statement with all source field information, and analyze 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: Use training data to train the 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 of 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 in any certainty index interval, calculate the accuracy rate corresponding to any certainty index interval, and then generate a model test result based on the first number and the accuracy rate; 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; Step 65, determining whether the model test result meets the preset conditions, if so, proceeding to step 66, if not, returning 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 classifications, obtain learning data belonging to any of the data classifications, 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 of the learning data, perform statistical analysis on all feature words after traversing all of the learning data, set the score for each feature word, store any feature word and the score corresponding to any of the feature words in correspondence, and generate a feature word configuration table for any of the data classifications; 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, calculating the difference between the maximum value and the second largest value of the accumulated sum, and determining whether the difference is greater than or equal to a third preset value, if not, returning 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 the step 8, the calculation of the cumulative sum of the scores of all the segmented words based on the segmentation results and the score of any segmented word in any data classification includes: defining the feature word configuration table of any of the data classifications as a first feature word configuration table, extracting the first feature word from any of the segmented words 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, and 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 collection module, table generation module, blood relationship analysis module, blood relationship inheritance module, 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 field and the corresponding annotation information correspondingly, 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 a blood relationship table; The bloodline inheritance module 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; The data table updating module is used to define the target field that has been successfully classified and graded as an identified field, update the data table to be analyzed based on the category information and level information of the identified field, define the target field that has not been successfully classified and graded as an unidentified field, and define the annotation information corresponding to the unidentified field as an annotation 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, and 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, the second self-learning module is entered; The second self-learning module is used to perform word segmentation processing 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, 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