A method for quickly cleaning and importing data into Hive

Abstract

The present invention discloses a method for quickly cleaning and importing data into Hive. In the process of data serialization and deserialization, the original data is read row by row, and the legality of the data is determined according to the "data verification rule". For illegal data, a new separator "^|" and an error mark "EE" need to be added after the data. In this way, the correct data and the wrong data are distinguished by marking the wrong data with "EE". The correct and wrong data are classified again according to the distinguished correct and wrong data. The original data is first classified according to the date, and then the interface ID is distinguished and classified under the date to form the cleaned data finally provided to the outside. The present invention omits the inspection step when the subsequent data is used by quickly and automatically marking and cleaning the original data, thereby improving the data processing efficiency.

Description

Technical Field

[0001] The present invention relates to the field of software technology, and in particular to a method for quickly cleaning and importing data into Hive. Background Art

[0002] Hive is a distributed, fault-tolerant data warehouse system that enables large-scale analysis. The data warehouse centrally stores information that you can easily analyze to make informed, data-driven decisions. Hive lets users read, write, and manage PB-level data using SQL.

[0003] Hive is built on Apache Hadoop, an open source framework that can be used to efficiently store and process large data sets. As a result, Hive is tightly integrated with Hadoop and is designed to quickly operate on petabytes of data. What makes Hive different is that it uses Apache Tez or MapReduce to query large data sets through a SQL-like interface.

[0004] Existing Hive architecture (such as Figure 1 As shown) as follows:

[0005] 1. Hive's underlying storage

[0006] Hive data is stored on HDFS. The libraries and tables in Hive are considered as a mapping of the data on HDFS. Therefore, Hive must be run on a Hadoop cluster.

[0007] 2. Execution process of Hive statements

[0008] The executor in Hive puts the MapReduce program to be executed on YARN in the form of a series of jobs.

[0009] 3. Hive metadata storage

[0010] Hive metadata is generally stored in a relational database such as MySQL. Hive and MySQL interact through the MetaStore service.

[0011] 4. Partition table (partioned)

[0012] Sometimes data is organized, such as by date / type, and when querying data, we often only care about part of the data. For example, I only want to query November 12, 2021. At this time, create a partition. When querying data for a specific day, there is no need to scan all directories, so performance will be significantly optimized. A Hive table has a corresponding directory on HDFS to store data. The data of ordinary tables is directly stored in this directory, and when partitioned table data is stored, it is divided into subdirectories for storage. Use partitioned by (xxx) to create table partitions (such as Figure 2 shown).

[0013] 5. How to import data into Hive

[0014] There are two ways to import raw data into a Hive table, differentiated by the location of the raw data.

[0015] If the original data is in the system directory:

[0016] load data local inpath′xxx.txt′into table xxx;

[0017] If the original data is in HDFS:

[0018] load data inpath′ / home / xxx / add.txt′into table xxx;

[0019] 6. Hive Serde

[0020] SerDe is the abbreviation of Serializer / Deserializer. Hive uses the SerDe interface to complete IO operations, that is, reading and writing data. Hive itself does not store data, it uses files stored on HDFS. Serialization is required when interacting with HDFS files for reading and writing.

[0021] Hive Serd is used for serialization and deserialization, and is built between the data storage and execution engine to decouple the two. SerDe allows Hive to read data from the table and then write the data back to HDFS in any custom format. Anyone can write their own SerDe for their own data format.

[0022] So serialization is to convert the imported data into Hadoop's Writable format, and deserialization is to import the data on HDFS into memory to form a row object. The data reading and writing process is as follows (such as Figure 3 shown):

[0023] Read HDFS files->InputFileFormat-><key, value>->Deserializer->Rowobject

[0024] Write Row object->Serializer-><key, value>->OutputFileFormat->HDFSfiles

[0025] It should be noted that the key part will be ignored when reading and is a constant when writing. Usually the Row object is stored in the value. Summary of the invention

[0026] The present invention solves the problem that invalid (erroneous) data cannot be distinguished during the process of loading data into a Hive table, and further data cleaning is required through subsequent operations. The method realizes data cleaning during the loading process and automatically classifies the erroneous data into a partition specifically for storing erroneous data, thereby simplifying the data cleaning process and greatly improving the data cleaning efficiency.

[0027] The present invention achieves the above-mentioned purpose through the following technical solutions:

[0028] Hive partitions based on fields, but this field is a "pseudo column" (query, no actual value is stored). A table has one or more partitions, and a partition has one or more partitions under it. According to the partition characteristics, the wrong data is placed in a special "EE" partition for easy distinction and query.

[0029] First, during the data serialization and deserialization process, the original data is read row by row, and the "data verification rules" are used to determine whether the data is legal. For legal data, a new separator "^|" and the interface ID number corresponding to the data are added after the original data (replaced by the letter Z), such as "Z^|A02" (where Z is the original data and A02 is the interface ID number of the record). For illegal data, a new separator "^|" and an error mark "EE" are added after the data, such as "Z^|EE". In this way, the correct data and the wrong data are distinguished by marking the wrong data with "EE".

[0030] Then, the correct and incorrect data are distinguished and reclassified. The original data is first classified by date, and then classified by interface ID under the date to form the cleaned data that is finally provided to the outside.

[0031] The beneficial effects of the present invention are:

[0032] The method for quickly cleaning and importing data into Hive of the present invention saves the verification step when the data is used subsequently, thereby improving the data processing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, 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 some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0034] Figure 1 : Hive architecture diagram;

[0035] Figure 2 : Data storage structure after Hive data table partitioning;

[0036] Figure 3 : The process of reading / writing Hive data to HDFS;

[0037] Figure 4 : Flow chart of the method of the present invention;

[0038] Figure 5 : T_LOG_FILE table storage structure;

[0039] Figure 6 : The actual storage structure after the original data is imported into the T_LOG_FILE table;

[0040] Figure 7 : T_LOG_INTERFACE table storage structure;

[0041] Figure 8 : The actual storage structure after importing data from the T_LOG_FILE table into the T_LOG_INTERFACE table. DETAILED DESCRIPTION

[0042] To make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be described in detail below. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other implementation methods obtained by ordinary technicians in this field without creative work belong to the scope of protection of the present invention.

[0043] In any embodiment, if Figure 4As shown, a method for quickly cleaning and importing data into Hive of the present invention first marks the data. Assume that there is an original data named Z, and its format is as follows: "2021-09-09 09:50:36|02|ams|30|A02|504980|504980|chiq1|imei|192.168.1.4|keyword=Crazy Bird 2773|kyw=2|recodnum=2". The entire data is separated by "|", and each two vertical lines represent a field. A1 represents the first field "2021-09-09 09:50:36", A2 represents the second field "02", A3 represents the third field "ams", and so on. A5 code field "A02", A13 represents the last field "recodnum=2". From Figure 3 It is known that in the process of importing data into Hive and persisting it to HDFS, the data needs to be serialized and deserialized. When implementing data serialization and deserialization, each data in the shape of Z is read and split according to the vertical line to form 13 fields from A1 to A13. The "verification rules" are as follows: A1 must be a date in the form of "YYYY-MM-dd HH:mm:ss", A2 is a two-digit number, and the high digits are filled with 0 if there are not enough two digits, A3 is one of "ams" and "sws", A4 is a two-digit number (such as A2), A5 starts with the letter A, followed by two digits and can only be one from 01 to 09, A6 can only be a number, A7 can only be a number, A8 can only be a combination of letters and numbers, A9 must be "imei", A10 is of ip type, A11 is of key=value type and value is a number, A12 is of key=value type, and by verifying each field according to the corresponding rules, as long as one field fails the verification, If it passes the verification of the above rules, then the content of the A5 (interface type field) field is added to the end of the array Z, in the form of "Z^|A02", where "Z" is the original data, "^|" is the field separator and distinguishes it from the field separator "|" in the original data, and "A02" is the interface ID of this data, that is, the content of the A5 field.

[0044] Through the above method, the original data is labeled, and then a Hive table T_LOG_FILES is created. Two fields "LOG_CONTENT" and "LOG_INTERFACE_ID" are defined in the table. The "LOG_CONTENT" field stores the original data, and the "LOG_INTERFACE_ID" field stores the tag field. The correct data and the wrong data are distinguished in Hive. The HQL statement "select log_content from t_log_files where log_interface_id='A02'" provided by Hive is used to filter out all the correct data with the interface ID "A02". At this point, the correct data and the wrong data are distinguished by marking the wrong data with "EE".

[0045] Reclassify the correct and incorrect data according to the distinction, and create a new Hive table T_LOG_INTERFACE to store the final data classified by date and interface ID, and the correct data and incorrect data are placed in different partitions. This table has only one field LOG_CONTENT, which stores the original data, and two partitions P_LOG_DATE and P_INTERFACE_ID. The former represents the date partition and the latter represents the interface partition. The table creation statement is "CREATE TABLE T_LOG_INTERFACE(LOG_CONTENT string) PARTITIONED BY(P_LOG_DATE STRING, P_INTERFACE_ID STRING)". Then, through the HQL statement provided by Hive, such as "INSERT OVERWRITE TABLE T_LOG_INTERFACE PARTITION (P_LOG_DATE, P_INTERFACE_ID) SELETE L.LOG_CONTENT, L.P_LOG_DATE, L.LOG_INTERFACE_ID FROM T_LOG_FILES L WHERE L.P_LOG_DATE = '2021-09-09'", all the data on September 9, 2021 in the table T_LOG_FILES are imported into the table T_LOG_INTERFACE. In the above HQL statement, the fields LOG_CONTENT, P_LOG_DATE, and P_LOG_INTERFACE after the insert table (T_LOG_INTERFACE) and the fields L.LOG_CONTENT, L.P_LOG_DATE, and L.LOG_INTERFACE_ID before the select table (T_LOG_FILES) correspond. Thus, data cleaning is achieved. At this point, the original data is first classified by date, and then classified by interface ID under the date to form the cleaned data that is finally provided to the outside.

[0046] After the original data passes through the above cleaning process, the HQL statement "SELECT * FROM T_LOG_FILE WHEREP_LOG_DATE = "2021-11-01" AND P_INTERFACE_ID = "A01"" is used to directly obtain all the correct original data reported on November 1, 2021 with interface type A01, eliminating the subsequent data cleaning work and improving data processing efficiency.

[0047] In a specific embodiment, a method for quickly cleaning and importing data into Hive of the present invention is implemented when the Hadoop cluster has been deployed, the Hive configuration is complete, and the network and basic environment can work normally.

[0048] The original data is as follows:

[0049] 2021-09-09 04:23:38|02|ams|30|A01|1427064|1427064|chiq|imei|192.168.1.9|devicemodel=gw301|OS=chOS|osversion=2.0|ossdkversion=01|resolving=700*800|teleope ratorid=CMCC|devicevendor=ch|devicebrand=ch|clientlang=CN|deviceidtype=0|clientversion=3.0|clientsource=01|lat=E104°05|lon=N30°39|sysvesion=2.3

[0050] 2021-09-09 09:50:36|02|ams|30|A02|504980|504980|chiq1|imei|192.168.1.4|keyword=crazybird2773|kyw=2|recodnum=2

[0051] 2021-09-09 13:23:14|02|ams|30|A03|16583|16583|chiq3|imei|192.168.1.7|apptypeid=4|apptypename=game4

[0052] The process is as follows:

[0053] 1. Enter the hive command to enter the hive cli interaction;

[0054] 2. Add the ch-hive-ext.jar package through the add command (addjar ch-hive-ext.jar);

[0055] This extension package implements the serialization and deserialization process of customized data when loading into Hive table.

[0056] The following functions are implemented during the serialization and deserialization process:

[0057] 1) Split the data according to the field separator "|" to form a field array, and take out the fifth element in the array, which is the interface type number field of the data. According to the above original data, there are three types of data interface types in the original data: A01, A02, and A03 (there are nine types of data interface types defined in actual production). The number of data fields of each interface data type is different. The interface and field number definition rules are A01 / 25, A02 / 12, and A03 / 12 respectively. If the size of the array after segmentation is larger than the size of the data defined in the rules, then it means that there is a problem with this data and it is not a legal data format (the data in some fields contains the specified field separators), and it is marked as wrong data (add separators "^|" and "EE" after the data).

[0058] 2) For legal data (the size of the array is consistent with the number of fields defined in the interface rules), take out each field in the array, first remove the spaces that may exist at the head and tail of the field through trim, and then check the legality of the field. The check rule takes the A03 interface as an example. The first field can only be numbers in time format, the second field can only be numbers, the third field can only be lowercase letters, and if it is an uppercase letter, it will be converted to lowercase letters. The fourth field can only be numbers, the fifth field can only be A03, the sixth and seventh fields can only be numbers, the eighth field can only be a combination of letters and numbers, the ninth field is a letter, the tenth field is an ip type, the eleventh field is a key=value type and the value is a number, and the twelfth field is a key=value type. If any of the above fields is not legal, it will be marked as an error (add separators "^|" and "EE" after the data). If all the above checks are legal, add the separator "^|" and the original "interface ID" of the data after the data.

[0059] Through the above processing, automatic data cleaning can be achieved (error data is marked as EE, correct data is marked as its original interface ID, and some data fields are formatted).

[0060] 3. Create the T_LOG_FILES table. This table is used to establish the relationship between the original data and Hive. The first field LOG_CONTENT of the table represents a record in the log. The second field LOG_INTERFACE_ID represents the interface number of this record. The error data is EE. The table is date partitioned and uses a custom SerDe.

[0061] CREATE TABLE T_LOG_FILES(LOG_CONTENT string, LOG_INTERFACE_ID string)PARTITIONED BY(P_LOG_DATE STRING)ROW FORMAT SERDE′com.changhong.hive.ext.LogMappingSerDe′;

[0062] The storage structure of this table is as follows Figure 5 As shown;

[0063] 4. Load the original data into the Hive table using the load command;

[0064] LOAD DATA INPATH′ / data / 2021-09-09 / appstore.log′OVERWRITE INTO TABLET_LOG_FILES PARTITION(P_LOG_DATE=′2021-09-09′);

[0065] This method formally establishes the mapping relationship between the original data and the table in Hive. The actual storage form of the original data loaded into the table T_LOG_FILE is as follows: Figure 6 shown.

[0066] 5. Create the T_LOG_INTERFACE table, which is partitioned by date and interface number (date / interface number) to save records of different interface tables in categories. CREATE TABLE T_LOG_INTERFACE(LOG_CONTENT string) PARTITIONED BY(P_LOG_DATE STRING, P_INTERFACE_ID STRING);

[0067] From the definition of this table, we can see that this table has an additional partition field for the interface id (P_INTERFACE_ID). After loading the data from the original data table into this table, the data will be placed in unused partitions according to the interface id (A01, A02, A03, EE). The specific process is implemented in step 6 below. The storage structure of this table is as follows: Figure 7 shown.

[0068] 6. Execute HQL to import the data in the T_LOG_FILES table into T_LOG_INTERFACE according to the partition fields (system ID / date / interface number), INSERT OVERWRITE TABLE T LOG INTERFACE PARTITION(P_LOG_DATE, P_INTERFACE_ID) SELETE L.LOG_CONTENT, L.P_LOG_DATE, L.LOG_INTERFACE_ID FROM T_LOG_FILES L WHERE L.P_LOG_DATE='2021-09-09'; the error data is saved in the EE partition (date / EE); the storage structure of this table is as follows Figure 8 shown.

[0069] 7. Load the files in each partition directory of the T_LOG_INTERFACE table directory into each interface table respectively, and the entire data cleaning process is completed.

[0070] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of changes or replacements within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims. It should also be noted that the various specific technical features described in the above-mentioned specific embodiments can be combined in any suitable manner without contradiction. In order to avoid unnecessary repetition, the present invention will not further explain various possible combinations. In addition, the various different embodiments of the present invention are also arbitrarily combined, as long as they do not violate the concept of the present invention, they should also be regarded as the contents disclosed by the present invention.

Claims

1. A method for quickly cleaning and importing data into Hive, characterized in that: The following steps are involved: Step 1: Enter the hive command to enter the hive cli interaction; Step 2: Use the add command to add the ch-hive-ext.jar package. This extension package implements the serialization and deserialization process of custom data when it is loaded into the hive table. In step 2, the following functions are implemented in the serialization and deserialization process: 1) Split the data into a field array according to the field separator "|", and take out the fifth element in the array, which is the interface type number field of the data. The number of data fields of each interface data type is different. If the size of the array after segmentation is larger than the size of the data defined in the rule, it means that there is a problem with this data and it is not a legal data format. It is marked as wrong data; 2) For legal data, take out each field in the array, first remove the spaces at the beginning and end of the field through trim, then check the legality of the field. If all checks are legal, add the separator "^|" and the original "interface ID" of the data after the data; Step 3: Create the T_LOG_FILES table to establish the relationship between the original data and Hive. The first field LOG_CONTENT of the table represents a record in the log, and the second field LOG_INTERFACE_ID represents the interface number of this record. The error data is EE. Step 4: Load the original data into the T_LOG_FILES table created in step 3 using the load command; Step 5, create T_LOG_INTERFACE table, partition by date and interface number, and save records of different interface tables in categories; in step 5, after loading the data in the original data table into this table, the data will be placed in unused partitions according to the interface ID (A01, A02, A03, EE); Step 6, execute HQL to import the data in the T_LOG_FILES table into T_LOG_INTERFACE according to the partition field; when importing T_LOG_INTERFACE, the interface number contained in it corresponds to the LOG_INTERFACE_ID in T_LOG_FILES, so that the interface information contains the error data identifier; Step 7: Load the files under each partition directory of the T_LOG_INTERFACE table directory into each interface table respectively, and the entire data cleaning process is completed.

2. The method for quickly cleaning and importing data into Hive according to claim 1, characterized in that: In step 3, the "LOG_CONTENT" field stores the original data, and the "LOG_INTERFACE_ID" field stores the tag field, which completes the distinction between correct data and incorrect data in Hive, and all correct data are filtered out through the HQL statement provided by Hive.

Images