Source data processing methods and apparatus, storage media and electronic devices
By calculating the similarity between source data fields and filtering, standard dimensions are automatically determined, solving the problem of difficulty in unifying data table dimensions in data warehouses and achieving efficient dimension standardization.
Patent Information
- Application Number
- CN202311575125.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2023-11-23
- Publication Date
- 2026-11-17
- Estimated Expiration
- 2043-11-23
AI Technical Summary
Unifying the dimensions of data tables in a data warehouse requires manual calibration, which is difficult and lacks an effective solution in current technology.
By calculating the similarity between each field in the source data, fields with similarity greater than a threshold are statistically analyzed and filtered out to determine the standard dimension and then standardize the dimension.
Automatically determining standard dimensions reduces the need for manual calibration and improves the efficiency and accuracy of data table dimension unification.
Smart Images

Figure CN117573941B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of smart home technology, and more specifically, to a method and apparatus for processing source data, a storage medium, and an electronic device. Background Technology
[0002] In the era of big data, organizations are placing great emphasis on building data warehouses and promoting data consumption within their organizations. However, in specific business processes, maintaining dimensional consistency across data tables within a data warehouse is challenging due to factors such as numerous departments, business systems, long timeframes, and rapid organizational restructuring. Yet, for a data warehouse, dimensional consistency is fundamental to data consumption, and its importance is self-evident. To maintain dimensional consistency, data governance teams manage data warehouses by issuing unified dimensional standards, constructing slowly changing dimensions, and manually verifying dimensional consistency. Simultaneously, data governance teams need to conduct dimensional governance work on historical data.
[0003] In response to the challenges of unifying dimensions in data warehouse tables, such as the need for data governance teams to manually calibrate dimensional consistency, which is difficult to achieve, no effective solution has yet been proposed. Summary of the Invention
[0004] This invention provides a method and apparatus for processing source data, a storage medium, and an electronic device to at least solve the problem that data governance teams need to manually calibrate the dimension uniformity of data tables in a data warehouse, which is difficult to achieve.
[0005] According to one embodiment of the present invention, a method for processing source data is provided, comprising: calculating a first similarity between every two first fields based on the data content contained in each first field in the source data, and counting the number of second similarities greater than a first threshold among the multiple first similarities corresponding to each first field; sorting the multiple first fields according to the number of second similarities, and filtering the sorted multiple first fields to obtain multiple second fields; determining the multiple second fields and the data content contained in the multiple second fields as standard dimensions, and performing dimensional standardization on the source data according to the standard dimensions.
[0006] In an exemplary embodiment, before calculating the first similarity between every two first fields based on the data content contained in each first field in the source data, the method further includes: deduplicating the data content contained in a plurality of third fields contained in the source data, wherein the plurality of third fields includes the first field; determining the data category of the data content contained in each third field and a first quantity of the data category based on the deduplication result; and deleting the target third field corresponding to the target first quantity from the plurality of third fields if a target first quantity is greater than a second threshold among the plurality of first quantities, thereby obtaining the first field.
[0007] In an exemplary embodiment, calculating a first similarity between every two first fields based on the data content contained in each first field in the source data includes: determining the data type of the data content contained in each first field; if it is determined that the data types corresponding to two fourth fields among a plurality of first fields are different, determining the first similarity of the two fourth fields as a third threshold, wherein the third threshold is less than the first threshold; if it is determined that the data types corresponding to two fifth fields among a plurality of first fields are the same, using a formula... Calculate the first similarity between the two fifth fields, where Sim A,B The first similarity of the two fifth fields is represented by A and B, respectively. Na is used to indicate the second number of data categories contained in A, Nb is used to indicate the third number of data categories contained in B, and Nc is used to indicate the fourth number of data categories that are the intersection of A and B.
[0008] In an exemplary embodiment, after determining the data category contained in each third field and the first quantity of the data category based on the deduplication result, the method further includes: determining a fifth quantity of data content for each data category, wherein the fifth quantity is the quantity of the data content before deduplication; when the data type corresponding to the sixth field in the first field is a character type, calculating a third similarity between every two data contents among the multiple data contents contained in the sixth field using an edit distance algorithm, wherein the data categories of the multiple data contents are all different; when there is a target third similarity greater than a fourth threshold among the multiple third similarities, deleting the target data content in the two data contents corresponding to the target third similarity to obtain an updated sixth field, wherein the fifth quantity of the target data content is the minimum value among the fifth quantities of the two data contents; and calculating a first similarity between every two first fields based on the updated sixth field.
[0009] In one exemplary embodiment, sorting a plurality of first fields according to the number of second similarities and filtering the sorted plurality of first fields to obtain a plurality of second fields includes: sorting the plurality of first fields according to the number of second similarities, wherein the first field with a larger number of second similarities is sorted first; deleting the second-ranked first field from the two first fields indicated by each second similarity to obtain the plurality of second fields.
[0010] In an exemplary embodiment, dimensional standardization of the source data according to the standard dimension includes: calculating the fourth similarity between the seventh field in the source data and the plurality of second fields to obtain a plurality of fourth similarities; if a target fourth similarity is greater than a fifth threshold among the plurality of fourth similarities, storing the seventh field and the target second field corresponding to the target fourth similarity in an early warning list; and sending the early warning list to a first object to instruct the first object to standardize the data content of the seventh field according to the target second field.
[0011] In an exemplary embodiment, dimensional standardization of the source data according to the standard dimension includes: calculating the fifth similarity between multiple eighth fields in the source data and the second field to obtain multiple fifth similarities; if multiple sixth similarities are greater than a sixth threshold among the multiple fifth similarities, storing the field names of multiple target eighth fields corresponding to the multiple sixth similarities and the field name of the second field into a field name list; determining the target field name with the most occurrences in the field name list as the standard dimension name, and standardizing the field names of the multiple target eighth fields and the second field according to the standard dimension name.
[0012] According to another embodiment of the present invention, a source data processing apparatus is also provided, comprising: a calculation module, configured to calculate a first similarity between every two first fields based on the data content contained in each first field in the source data, and to count the number of second similarities greater than a first threshold among the multiple first similarities corresponding to each first field; a sorting module, configured to sort the multiple first fields according to the number of second similarities, and to filter the sorted multiple first fields to obtain multiple second fields; and a standardization module, configured to determine the multiple second fields and the data content contained in the multiple second fields as standard dimensions, and to perform dimensional standardization on the source data according to the standard dimensions.
[0013] According to another aspect of the present invention, a computer-readable storage medium is also provided, wherein a computer program is stored in the computer program, and the computer program is configured to execute the above-described source data processing method at runtime.
[0014] According to another aspect of the present invention, an electronic device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the above-described source data processing method through the computer program.
[0015] In this embodiment, the first similarity between each pair of first fields is calculated based on the data content contained in each first field in the source data, and the number of second similarities for each first field is counted. The second similarity is the similarity between the first similarities of the first field and other first fields that is greater than a first threshold. Then, the multiple first fields are sorted according to the number of second similarities corresponding to each first field, and the sorted multiple first fields are deduplicated to obtain multiple second fields. These multiple second fields and the data content contained in these multiple second fields are determined as standard dimensions, and the source data is then dimensionally standardized according to the standard dimensions. By adopting the above scheme, standard dimensions are automatically determined from the tables in the database (i.e., the source data), and data with non-standard dimensions is dimensionally standardized, thereby solving the problem in the prior art that if data tables in the data warehouse need to be dimensionally unified, the data governance team needs to manually verify the dimension uniformity, which is difficult. Attached Figure Description
[0016] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.
[0017] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0018] Figure 1 This is a schematic diagram of the hardware environment for an optional source data processing method according to an embodiment of this application;
[0019] Figure 2 This is a flowchart of an optional source data processing method according to an embodiment of the present invention;
[0020] Figure 3 This is a schematic diagram of an optional data warehouse dimension standardization process according to an embodiment of the present invention;
[0021] Figure 4 This is a structural block diagram (a) of an optional source data processing device according to an embodiment of the present invention;
[0022] Figure 5 This is a structural block diagram (II) of an optional source data processing device according to an embodiment of the present invention. Detailed Implementation
[0023] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.
[0024] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0025] According to one aspect of the embodiments of this application, a method for processing source data is provided. This source data processing method is widely applicable to whole-house intelligent digital control application scenarios such as smart homes, smart home ecosystems, and intelligence house ecosystems. Optionally, in this embodiment, the above-mentioned source data processing method can be applied to, for example... Figure 1 The hardware environment shown consists of terminal device 102 and server 104. For example... Figure 1 As shown, server 104 is connected to terminal device 102 via a network and can be used to provide services (such as application services) to the terminal or clients installed on the terminal. A database can be set up on the server or independently of the server to provide data storage services for server 104. Cloud computing and / or edge computing services can be configured on the server or independently of the server to provide data processing services for server 104.
[0026] The aforementioned network may include, but is not limited to, at least one of the following: wired network, wireless network. The aforementioned wired network may include, but is not limited to, at least one of the following: wide area network, metropolitan area network, local area network. The aforementioned wireless network may include, but is not limited to, at least one of the following: Wi-Fi (Wireless Fidelity), Bluetooth. Terminal device 102 may not be limited to PC, mobile phone, tablet computer, smart air conditioner, smart range hood, refrigerator, smart oven, smart stove, smart washing machine, smart water heater, smart washing equipment, smart dishwasher, smart projector, smart TV, smart clothes rack, smart curtains, smart audio-visual equipment, smart socket, smart speaker, smart speaker box, smart fresh air equipment, smart kitchen and bathroom equipment, smart bathroom equipment, smart robot vacuum cleaner, smart window cleaning robot, smart mopping robot, smart air purifier, smart steam oven, smart microwave oven, smart water heater, smart air purifier, smart water dispenser, smart door lock, etc.
[0027] This embodiment provides a method for processing source data, applied to a computer terminal. Figure 2 This is a flowchart of an optional source data processing method according to an embodiment of the present invention, the process including the following steps:
[0028] Step S202: Calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field;
[0029] It should be noted that the source data mentioned above can be understood as all the data stored in the data warehouse. The data warehouse is a collection of multiple databases and numerous data tables. The data warehouse φ has an inclusion relationship with the database S, and the database S has an inclusion relationship with the data table T. The data table T contains each field C (equivalent to the first field mentioned above).
[0030] The symbols are represented as:
[0031] φ={S1, S2, S3, S4,...S a};
[0032] S a ={T a,1 T a,2 T a,3 T a,4 , ...T a,b};
[0033] Step S204: Sort the multiple first fields according to the number of second similarities, and filter the sorted multiple first fields to obtain multiple second fields;
[0034] Step S206: Determine the plurality of second fields and the data content contained in the plurality of second fields as standard dimensions, and perform dimension standardization on the source data according to the standard dimensions.
[0035] In this embodiment, the first similarity between each pair of first fields is calculated based on the data content contained in each first field in the source data, and the number of second similarities for each first field is counted. The second similarity is the similarity between the first similarities of the first field and other first fields that is greater than a first threshold. Then, the multiple first fields are sorted according to the number of second similarities corresponding to each first field, and the sorted multiple first fields are deduplicated to obtain multiple second fields. These multiple second fields and the data content contained in these multiple second fields are determined as standard dimensions, and the source data is then dimensionally standardized according to the standard dimensions. By adopting the above scheme, standard dimensions are automatically determined from the tables in the database (i.e., the source data), and data with non-standard dimensions is dimensionally standardized, thereby solving the problem in the prior art that if data tables in the data warehouse need to be dimensionally unified, the data governance team needs to manually verify the dimension uniformity, which is difficult.
[0036] Optionally, before performing step S202 above: calculating the first similarity between every two first fields based on the data content contained in each first field in the source data, the method further includes: deduplicating the data content contained in a plurality of third fields contained in the source data, wherein the plurality of third fields includes the first field; determining the data category of the data content contained in each third field and the first quantity of the data category based on the deduplication result; and deleting the target third field corresponding to the target first quantity from the plurality of third fields if there is a target first quantity greater than a second threshold among the plurality of first quantities, thereby obtaining the first field.
[0037] Before calculating the first similarity between any two first fields, it is necessary to deduplicate the data content in each field. For each third field, a deduplication query must be performed first. For example, if the third field includes a, a, b, b, b, c, the deduplication result indicates that the data content contained in the third field includes the data categories a, b, and c, and the first number of the corresponding data categories is 3, that is, the third field contains three data categories. If, after deduplicating all the third fields, it is found that the first number of a certain third field is greater than the second threshold, it means that the data content contained in the third field has too many data categories, and the third field cannot be used as a standard data dimension. Therefore, the corresponding target third field is deleted. After deleting all target third fields, the first field is obtained.
[0038] Optionally, step S202 above: calculating the first similarity between every two first fields based on the data content contained in each first field in the source data, can be achieved through the following steps: determining the data type of the data content contained in each first field; when it is determined that the data types of two fourth fields among multiple first fields are different, determining the first similarity of the two fourth fields as a third threshold, wherein the third threshold is less than the first threshold; when it is determined that the data types of two fifth fields among multiple first fields are the same, using a formula... Calculate the first similarity between the two fifth fields, where Sim A,B The first similarity of the two fifth fields is represented by A and B, respectively. Na is used to indicate the second number of data categories contained in A, Nb is used to indicate the third number of data categories contained in B, and Nc is used to indicate the fourth number of data categories that are the intersection of A and B.
[0039] The data types contained in each first field are the same, specifically categorized as character, numeric, and date types. Date types are not included in the similarity calculation. If two fourth fields have different data types (one character and one numeric), their first similarity is determined to be 0. If two fifth fields have the same data type, the similarity is determined using the formula... To calculate the first similarity between these two fifth fields, where Sim A,B The first similarity of the two fifth fields is represented by A and B, respectively. Na is used to indicate the second number of data categories contained in A, Nb is used to indicate the third number of data categories contained in B, and Nc is used to indicate the fourth number of data categories that are the intersection of A and B.
[0040] Based on the above steps, the following steps are performed: after determining the data category contained in each third field and the first quantity of the data category according to the deduplication result, the method further includes: determining the fifth quantity of data content in each data category, wherein the fifth quantity is the quantity of data content before deduplication; when the data type corresponding to the sixth field in the first field is character type, calculating the third similarity of every two data contents among the multiple data contents contained in the sixth field using the edit distance algorithm, wherein the data categories of the multiple data contents are all different; when there is a target third similarity greater than a fourth threshold among the multiple third similarities, deleting the target data content in the two data contents corresponding to the target third similarity to obtain the updated sixth field, wherein the fifth quantity of the target data content is the minimum value of the fifth quantities of the two data contents; calculating the first similarity between every two first fields based on the updated sixth field.
[0041] Before deduplicating the data content of each first field, the multiple data categories and the first quantity of each data category are determined. At this time, the quantity of data content in each data category is also determined, which is the fifth quantity mentioned above. Before calculating the first similarity, the character data in each first field of the character type needs to be deduplicated. First, the third similarity between two data contents of different data categories in the sixth field of the character type is calculated using the edit distance algorithm. If the third similarity is greater than the fourth threshold, it means that the similarity between the two character data is high enough. Then, the character data with the smaller fifth quantity is deleted, and only the string with the largest quantity in the field is kept. Then, the character type fields in the first field are replaced according to the updated sixth field, and the first similarity between each pair of first fields is calculated according to the updated sixth field.
[0042] Optionally, the above step S204 is performed: sorting the multiple first fields according to the number of second similarities, and filtering the sorted multiple first fields to obtain multiple second fields, including the following steps: sorting the multiple first fields according to the number of second similarities, wherein the first field with a larger number of second similarities is sorted first; deleting the first field that is sorted later in the two first fields indicated by each second similarity to obtain the multiple second fields.
[0043] The multiple first fields are sorted in reverse order based on the number of second similarities corresponding to each first field. That is, if the similarity of a first field to the other N first fields is greater than a first threshold, then the number of second similarities corresponding to that first field is N. Then, the fields are sorted in reverse order according to the N value of each first field. Then, the other N first fields corresponding to each first field are deleted in order according to the sorting, resulting in multiple second fields. For example, A, B, C, D, and E are the first fields sorted from 1 to 5. The number of second similarities corresponding to A is 2, and the first fields with similarity greater than the first threshold to A are C and D. The number of second similarities corresponding to B is 1, and the first field with similarity greater than the first threshold to B is E. Therefore, for A, C and D are deleted, and for B, E is deleted.
[0044] Optionally, step S206 above: performing dimensional standardization on the source data according to the standard dimension includes: calculating the fourth similarity between the seventh field in the source data and the plurality of second fields to obtain a plurality of fourth similarities; if there is a target fourth similarity greater than a fifth threshold among the plurality of fourth similarities, storing the seventh field and the target second field corresponding to the target fourth similarity in an early warning list; sending the early warning list to a first object to instruct the first object to standardize the data content of the seventh field according to the target second field.
[0045] Calculate the fourth similarity between the seventh field (excluding the multiple second fields) and the multiple second fields in the source data. For each seventh field, obtain multiple fourth similarities. If there is a target fourth similarity greater than the fifth threshold, it means that the seventh field is highly similar to the target second field, but the categories of the data content contained in the field are still different. Therefore, the seventh field and the target second field are stored in the warning list and sent to the first object, instructing the first object to standardize the data content of the seventh field according to the warning list.
[0046] Optionally, step S206 above is performed: dimensional standardization of the source data according to the standard dimension, including: calculating the fifth similarity between multiple eighth fields and the second field in the source data to obtain multiple fifth similarities; if multiple sixth similarities are greater than the sixth threshold among the multiple fifth similarities, storing the field names of multiple target eighth fields and the field names of the second field corresponding to the multiple sixth similarities into a field name list; determining the target field name with the most occurrences in the field name list as the standard dimension name, and standardizing the field names of the multiple target eighth fields and the second field according to the standard dimension name.
[0047] Calculate the fifth similarity between multiple eighth fields in the source data and a second field. If there is a sixth similarity among the fifth similarities that is greater than the first threshold, store the field names of the target eighth fields and the field names of the second fields corresponding to all the sixth similarities in a field name list. Then, determine the target field name that appears most frequently in the field name list as the standard field name. Then, standardize the field names of these multiple target eighth fields and second fields according to the standard field name.
[0048] This application also provides an optional embodiment, proposing an optional data warehouse dimension standardization scheme, such as... Figure 3 As shown, it includes the following steps:
[0049] Step 1: Automatically scan all table fields in the data warehouse using a pre-set script, and store the database, data table, fields, and field attributes in the specified data table in field format;
[0050] The system connects to and reads the contents of all databases, tables, and fields in the data warehouse using a pre-configured script and with access permissions. The read data is then stored in the specified table `Table_init`, as shown in Table 1.
[0051] Table 1
[0052] Id Schema Table Columns Column_name Parameters 1 <![CDATA[S1]]> <![CDATA[T 1,1 ]]> <![CDATA[C 1,1,1 ]]> <![CDATA[name 1,1,1 ]]> <![CDATA[p 1,1,1 ]]> 2 <![CDATA[S1]]> <![CDATA[T 1,1 ]]> <![CDATA[C 1,1,2 ]]> <![CDATA[name 1,1,2 ]]> <![CDATA[p 1,1,2 ]]> 3 <![CDATA[S1]]> <![CDATA[T 2,1 ]]> <![CDATA[C 2,1,1 ]]> <![CDATA[name 2,1,1 ]]> <![CDATA[p 2,1,1 ]]> …… …… …… …… …… ……
[0053] Among them, Parameters are field attributes. For structured databases, most of them are stored as three types: date, character, and numeric. Other types are not recorded (equivalent to the above data types).
[0054] Step 2: Analyze the field content and record the relevant attributes of the field content;
[0055] The maximum number of dimensions is preset to a threshold N_ref (equivalent to the second threshold mentioned above); the fields of Table_init are queried in a loop, and the fields are deduplicated. The number of non-duplicate records N (equivalent to the first number mentioned above) is recorded, and the content of the non-duplicate records is recorded.
[0056] If N is less than or equal to N_ref and is not 0, then record the content and corresponding quantity of the non-repeating record. List 1 records the content of the non-repeating record, and List 2 records the quantity of the content of the non-repeating record. Considering that the amount of non-repeating content data is large, store the key-value pair C_key. The content of the non-repeating record is stored in an unstructured database, and the key-value pair is represented as (C_key, [Content_list, Content_count]).
[0057] If N is greater than N_ref, then the record is ignored (that is, the third field of the target is deleted).
[0058] Example of recording mode:
[0059] The field content in a certain table is [a, a, a, b, b, b, c, c, d, d], and the field content after deduplication is [a, b, c, d], where N=4. It is determined that N < N_ref. Then the non-duplicate record [a, b, c, d] and the quantity of the non-duplicate record content [3, 3, 2, 2] (equivalent to the aforementioned fifth quantity) are recorded, with N being 4.
[0060] Step 3: Using the field content to calculate the similarity between respective fields;
[0061] For the non-duplicate record content obtained in step 2, perform different processing according to different formats, calculate the similarity between fields, and record the results in the form of key-value pairs, Con_list 1,1,1 and Con_list 1,1,2 has a similarity of Sim 1,2 , which is expressed as
[0062] The method for calculating the similarity (equivalent to the aforementioned first similarity) is as follows:
[0063] (1) Numeric type
[0064] The Tanimoto similarity method is used; this method is essentially a method for calculating the ratio of the intersection to the union. For example, to calculate the similarity between the two arrays A and B:
[0065] A = [1, 2, 3, 4] ........ List length: 4.
[0066] B = [1, 2, 7] ........ List length: 3.
[0067] C = A&B = [1, 2] ........ List length: 2.
[0068]
[0069] (2) Character type
[0070] a. Set a string similarity threshold Sim_ref_content (equivalent to the aforementioned fourth threshold);
[0071] b. Read the string list cyclically, use the edit distance algorithm to calculate the similarity of different strings in the string list. If the similarity (equivalent to the aforementioned third similarity) is higher than the similarity threshold Sim_ref_content, only the string with the largest quantity in the list is retained.
[0072] Examples are as follows:
[0073] A = [a, b, c, d], where Sim a,b >Sim_ref_content and Count a >Count b ;
[0074] A_new = [a, c, d];
[0075] c. Combine the above list of strings into text, and segment it into words according to the bag-of-words model;
[0076] A = [Europe, Americas, Time, Space] --→ string_list =
[0077] Europe, regions, Americas, time, space;
[0078] d. After word segmentation, use the Tanimoto similarity method to compare the similarity between the two fields.
[0079] (3) Date type fields are not included in the similarity calculation.
[0080] Step 4: Obtain the recommendation dimension scheme through similarity;
[0081] (1) Set the field similarity threshold Sim_ref_columns (equivalent to the first threshold mentioned above), and count the number of key-value pairs with similarity exceeding Sim_ref_columns for each field;
[0082] (2) Sort Table 1 in descending order based on the count, and retrieve the column list of fields that appear first in the sorted list. For example, Column_list = [Con_list] 1,1,1 Con_list 2,1,1 Con_list 3,1,1 ];
[0083] (3) Perform pairwise comparisons on the contents of Column_list. If the similarity (equivalent to the first similarity mentioned above) is greater than the field similarity threshold Sim_ref_columns, remove the field with the lower ranking. The final non-duplicate record content corresponding to Column_list_new is the final recommendation dimension scheme, denoted as recommend_dim_list. The final scheme is displayed in list form: recommend_dim_list = [Con_list] 1,1,1 Con_list 1,1,2 ...].
[0084] Step 5: Use the dimension recommendation scheme to automatically issue warnings for non-standard dimension data;
[0085] Key-value pairs that measure the similarity between the content of non-duplicate records for each field. Process it; if one of the fields belongs to the standard dimension recommendation scheme recommendation_dim_list, and If the value is high (equivalent to being greater than the fifth threshold mentioned above), the field is recorded to form an early warning list; this early warning list generates an early warning report to notify the data warehouse administrator (equivalent to the first object mentioned above) to conduct an investigation.
[0086] Step 6: Develop a recommended scheme for standard dimension field names;
[0087] Key-value pairs that measure the similarity between the content of non-duplicate records for each field. Process accordingly. If one of the fields belongs to the standard dimension recommendation scheme command_dim_list, and If the similarity exceeds the field similarity threshold (equivalent to the sixth threshold mentioned above), then the field name is recorded and added to the dim_init table as a list Name_list, for example Name_list = [Name1, Name2, ...];
[0088] Analyze each element in Name_list. Name_list = [name1, name2, ...]. Find the element with the most occurrences in the list and use it as the recommended dimension name scheme Name_command (equivalent to the standard dimension name mentioned above), and record it in the dim_init table.
[0089] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods according to the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods of the various embodiments of the present invention.
[0090] This embodiment also provides a source data processing device for implementing the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that performs a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0091] Figure 4 A structural block diagram of an optional source data processing apparatus according to an embodiment of the present invention; as follows: Figure 4 As shown, it includes:
[0092] The calculation module 42 is used to calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and to count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field;
[0093] The sorting module 44 is used to sort multiple first fields according to the number of second similarities, and to filter the sorted multiple first fields to obtain multiple second fields;
[0094] The standardization module 46 is used to determine the plurality of second fields and the data content contained in the plurality of second fields as standard dimensions, and to perform dimensional standardization on the source data according to the standard dimensions.
[0095] Using the aforementioned device, the first similarity between any two first fields is calculated based on the data content contained in each first field in the source data, and the number of second similarities for each first field is counted. The second similarity is the similarity between the first similarities of the first field and other first fields that is greater than a first threshold. Then, the multiple first fields are sorted according to the number of second similarities corresponding to each first field, and the sorted multiple first fields are deduplicated to obtain multiple second fields. These multiple second fields and the data content contained in these multiple second fields are determined as standard dimensions, and then the source data is dimension-standardized according to the standard dimensions. By adopting the above scheme, standard dimensions are automatically determined from the tables in the database (i.e., the source data), and dimension standardization is performed on data with non-standard dimensions. This solves the problem in the existing technology that if data tables in the data warehouse need to be dimension-unified, the data governance team needs to manually verify the dimension uniformity, which is difficult.
[0096] Optionally, the source data processing device further includes a deduplication module 48, such as... Figure 5As shown, the deduplication module 48 is used to deduplicate the data content contained in multiple third fields of the source data, wherein the multiple third fields include the first field; determine the data category of the data content contained in each third field and the first quantity of the data category based on the deduplication result; if there is a target first quantity among the multiple first quantities that is greater than a second threshold, delete the target third field corresponding to the target first quantity from the multiple third fields to obtain the first field.
[0097] Before calculating the first similarity between any two first fields, it is necessary to deduplicate the data content in each field. For each third field, a deduplication query must be performed first. For example, if the third field includes a, a, b, b, b, c, the deduplication result indicates that the data content contained in the third field includes the data categories a, b, and c, and the first number of the corresponding data categories is 3, that is, the third field contains three data categories. If, after deduplicating all the third fields, it is found that the first number of a certain third field is greater than the second threshold, it means that the data content contained in the third field has too many data categories, and the third field cannot be used as a standard data dimension. Therefore, the corresponding target third field is deleted. After deleting all target third fields, the first field is obtained.
[0098] Optionally, the calculation module 42 is further configured to determine the data type of the data content contained in each first field; if it is determined that the data types corresponding to two fourth fields among a plurality of first fields are different, determine the first similarity of the two fourth fields as a third threshold, wherein the third threshold is less than the first threshold; if it is determined that the data types corresponding to two fifth fields among a plurality of first fields are the same, use a formula Calculate the first similarity between the two fifth fields, where Sim A,B The first similarity of the two fifth fields is represented by A and B, respectively. Na is used to indicate the second number of data categories contained in A, Nb is used to indicate the third number of data categories contained in B, and Nc is used to indicate the fourth number of data categories that are the intersection of A and B.
[0099] The data types contained in each first field are the same, specifically categorized as character, numeric, and date types. Date types are not included in the similarity calculation. If two fourth fields have different data types (one character and one numeric), their first similarity is determined to be 0. If two fifth fields have the same data type, the similarity is determined using the formula... To calculate the first similarity between these two fifth fields, where Sim A,BThe first similarity of the two fifth fields is represented by A and B, respectively. Na is used to indicate the second number of data categories contained in A, Nb is used to indicate the third number of data categories contained in B, and Nc is used to indicate the fourth number of data categories that are the intersection of A and B.
[0100] Optionally, the calculation module 42 is further configured to determine the fifth quantity of data content for each data category, wherein the fifth quantity is the quantity of data content before deduplication; when the data type corresponding to the sixth field in the first field is character type, the third similarity of every two data contents among the multiple data contents contained in the sixth field is calculated by the edit distance algorithm, wherein the data categories of the multiple data contents are all different; when there is a target third similarity greater than a fourth threshold among the multiple third similarities, the target data content in the two data contents corresponding to the target third similarity is deleted to obtain the updated sixth field, wherein the fifth quantity of the target data content is the minimum value among the fifth quantities of the two data contents; and the first similarity between every two first fields is calculated based on the updated sixth field.
[0101] Before deduplicating the data content of each first field, the multiple data categories and the first quantity of each data category are determined. At this time, the quantity of data content in each data category is also determined, which is the fifth quantity mentioned above. Before calculating the first similarity, the character data in each first field of the character type needs to be deduplicated. First, the third similarity between two data contents of different data categories in the sixth field of the character type is calculated using the edit distance algorithm. If the third similarity is greater than the fourth threshold, it means that the similarity between the two character data is high enough. Then, the character data with the smaller fifth quantity is deleted, and only the string with the largest quantity in the field is kept. Then, the character type fields in the first field are replaced according to the updated sixth field, and the first similarity between each pair of first fields is calculated according to the updated sixth field.
[0102] Optionally, the sorting module 44 is further configured to sort the plurality of first fields according to the number of second similarities, wherein the first field with a larger number of second similarities is sorted first; and to delete the second field from the two first fields indicated by each second similarity to obtain the plurality of second fields.
[0103] The multiple first fields are sorted in reverse order based on the number of second similarities corresponding to each first field. That is, if the similarity of a first field to the other N first fields is greater than a first threshold, then the number of second similarities corresponding to that first field is N. Then, the fields are sorted in reverse order according to the N value of each first field. Then, the other N first fields corresponding to each first field are deleted in order according to the sorting, resulting in multiple second fields. For example, A, B, C, D, and E are the first fields sorted from 1 to 5. The number of second similarities corresponding to A is 2, and the first fields with similarity greater than the first threshold to A are C and D. The number of second similarities corresponding to B is 1, and the first field with similarity greater than the first threshold to B is E. Therefore, for A, C and D are deleted, and for B, E is deleted.
[0104] Optionally, the standardization module 46 is further configured to calculate the fourth similarity between the seventh field in the source data and the plurality of second fields to obtain a plurality of fourth similarities; if there is a target fourth similarity greater than a fifth threshold among the plurality of fourth similarities, the seventh field and the target second field corresponding to the target fourth similarity are stored in an early warning list; the early warning list is sent to a first object to instruct the first object to standardize the data content of the seventh field according to the target second field.
[0105] Calculate the fourth similarity between the seventh field (excluding the multiple second fields) and the multiple second fields in the source data. For each seventh field, obtain multiple fourth similarities. If there is a target fourth similarity greater than the fifth threshold, it means that the seventh field is highly similar to the target second field, but the categories of the data content contained in the field are still different. Therefore, the seventh field and the target second field are stored in the warning list and sent to the first object, instructing the first object to standardize the data content of the seventh field according to the warning list.
[0106] Optionally, the standardization module 46 is further configured to calculate the fifth similarity between multiple eighth fields and the second field in the source data to obtain multiple fifth similarities; if multiple sixth similarities are greater than the sixth threshold among the multiple fifth similarities, the field names of multiple target eighth fields and the field names of the second field corresponding to the multiple sixth similarities are stored in a field name list; the target field name with the most occurrences in the field name list is determined as the standard dimension name, and the field names of the multiple target eighth fields and the second field are standardized according to the standard dimension name.
[0107] Calculate the fifth similarity between multiple eighth fields in the source data and a second field. If there is a sixth similarity among the fifth similarities that is greater than the first threshold, store the field names of the target eighth fields and the field names of the second fields corresponding to all the sixth similarities in a field name list. Then, determine the target field name that appears most frequently in the field name list as the standard field name. Then, standardize the field names of these multiple target eighth fields and second fields according to the standard field name.
[0108] Embodiments of the present invention also provide a storage medium comprising a stored program, wherein the program, when executed, performs any of the methods described above.
[0109] Optionally, in this embodiment, the storage medium may be configured to store program code for performing the following steps:
[0110] S1, calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field;
[0111] S2, sort the multiple first fields according to the number of second similarities, and filter the sorted multiple first fields to obtain multiple second fields;
[0112] S3, determine the plurality of second fields and the data content contained in the plurality of second fields as standard dimensions, and perform dimensional standardization on the source data according to the standard dimensions.
[0113] Embodiments of the present invention also provide an electronic device including a memory and a processor, the memory storing a computer program and the processor being configured to run the computer program to perform the steps in any of the above method embodiments.
[0114] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor and the input / output device is connected to the processor.
[0115] Optionally, in this embodiment, the processor can be configured to perform the following steps via a computer program:
[0116] S1, calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field;
[0117] S2, sort the multiple first fields according to the number of second similarities, and filter the sorted multiple first fields to obtain multiple second fields;
[0118] S3, determine the plurality of second fields and the data content contained in the plurality of second fields as standard dimensions, and perform dimensional standardization on the source data according to the standard dimensions.
[0119] Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.
[0120] Optionally, specific examples in this embodiment can refer to the examples described in the above embodiments and optional implementations, and will not be repeated here.
[0121] It is obvious to those skilled in the art that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Optionally, they can be implemented using computer-executable program code, thereby storing them in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those presented herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.
[0122] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the principles of the present invention should be included within the scope of protection of the present invention.
Claims
1. A method of processing source data, characterized by, include: Calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field; The first fields are sorted according to the number of second similarities, and the sorted first fields are filtered to obtain multiple second fields; The plurality of second fields and the data content contained in the plurality of second fields are determined as standard dimensions, and the source data is dimensionally standardized according to the standard dimensions; Before calculating the first similarity between any two first fields based on the data content contained in each first field in the source data, the method further includes: The data content contained in the multiple third fields of the source data is deduplicated, wherein the multiple third fields include the first field; Based on the deduplication results, determine the data category of the data content contained in each third field and the first quantity of the data category; If, among the plurality of first quantities, there exists a target first quantity greater than a second threshold, the target third field corresponding to the target first quantity is deleted from the plurality of third fields to obtain the first field.
2. The source data processing method according to claim 1, characterized in that, Calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, including: Determine the data type of the data contained in each first field; If it is determined that two fourth fields among a plurality of first fields correspond to different data types, the first similarity of the two fourth fields is determined as a third threshold, wherein the third threshold is less than the first threshold; When it is determined that two fifth fields among multiple first fields have the same data type, the formula is used. Calculate the first similarity between the two fifth fields, where, The first similarity of the two fifth fields is represented by A and B, which are used to indicate the two fifth fields respectively. A second quantity used to indicate the number of data categories contained in A A third quantity used to indicate the number of data categories contained in B. The fourth quantity of data categories used to indicate the intersection of A and B.
3. The source data processing method according to claim 2, characterized in that, After determining the data category contained in each third field and the first quantity of each data category based on the deduplication results, the method further includes: Determine the fifth quantity of data content for each data category, wherein the fifth quantity is the quantity of data content before deduplication; When the data type corresponding to the sixth field in the first field is character, the third similarity between every two data contents in the sixth field is calculated by the edit distance algorithm, wherein the data categories of the multiple data contents are all different; If, among multiple third similarities, there exists a target third similarity greater than a fourth threshold, the target data content in the two data contents corresponding to the target third similarity is deleted to obtain an updated sixth field, wherein the fifth quantity of the target data content is the minimum value among the fifth quantities of the two data contents; The first similarity between each pair of first fields is calculated based on the updated sixth field.
4. The method for processing source data according to claim 1, characterized in that, The first fields are sorted according to the number of second similarities, and the sorted first fields are then filtered to obtain multiple second fields, including: The plurality of first fields are sorted according to the number of second similarities, wherein the first field with a larger number of second similarities is sorted first. The second similarity indicator is obtained by deleting the last sorted first field from the two first fields.
5. The method for processing source data according to claim 1, characterized in that, The source data is dimensionally standardized according to the aforementioned standard dimensions, including: Calculate the fourth similarity between the seventh field in the source data and the plurality of second fields to obtain multiple fourth similarities; If, among the plurality of fourth similarities, there is a target fourth similarity greater than the fifth threshold, the seventh field and the target second field corresponding to the target fourth similarity are stored in the warning list; The warning list is sent to the first object to instruct the first object to standardize the data content of the seventh field according to the target second field.
6. The method for processing source data according to claim 1, characterized in that, The source data is dimensionally standardized according to the aforementioned standard dimensions, including: Calculate the fifth similarity between multiple eighth fields in the source data and the second field to obtain multiple fifth similarity scores; If multiple sixth similarities are greater than the sixth threshold among the multiple fifth similarities, the field names of the multiple target eighth fields corresponding to the multiple sixth similarities and the field names of the second field are stored in the field name list; The target field name with the most occurrences in the field name list is determined as the standard dimension name, and the field names of the multiple target eighth field and the second field are standardized according to the standard dimension name.
7. A source data processing apparatus, characterized in that, include: The calculation module is used to calculate the first similarity between every two first fields based on the data content contained in each first field in the source data, and to count the number of second similarities greater than the first threshold among the multiple first similarities corresponding to each first field; The sorting module is used to sort multiple first fields according to the number of second similarities, and to filter the sorted multiple first fields to obtain multiple second fields; The standardization module is used to determine the plurality of second fields and the data content contained in the plurality of second fields as standard dimensions, and to perform dimensional standardization on the source data according to the standard dimensions; The calculation module is further configured to deduplicate the data content contained in multiple third fields of the source data, wherein the multiple third fields include the first field; determine the data category of the data content contained in each third field and the first quantity of the data category based on the deduplication result; and delete the target third field corresponding to the target first quantity from the multiple third fields if there is a target first quantity greater than a second threshold among the multiple first quantities, thereby obtaining the first field.
8. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored program, wherein the program, when executed, performs the method according to any one of claims 1 to 6.
9. An electronic device comprising a memory and a processor, characterized in that, The memory stores a computer program, and the processor is configured to execute the method described in any one of claims 1 to 6 through the computer program.
Citation Information
Patent Citations
Data deduplication method and device based on field analysis and computer equipment
CN111797082A
