Implementation method for flexibly changing fields of Hive large-scale partition table

Abstract

The invention provides an implementation method for flexibly changing fields of a Hive large-scale partition table, which comprises the following steps: creating an independent new data table for each year for an original large-scale partition table on the basis of a divide-and-conquer principle; a Hive view is newly built, new data tables of all years after segmentation are reintegrated into a logic unit, the table name of an original large-scale partition table is reserved, and the original large-scale partition table has the same data architecture as the original large-scale partition table; field adding operation only needs to be executed for a new data table of the latest year, and the same field positions of other years in the Hive view are marked as null values; and the field deletion and modification operation only needs to operate the Hive view, so that the flexibility and timeliness of the table structure are greatly improved. According to the method, the capability of quickly and flexibly adjusting the table structure when the data warehouse processes mass data is remarkably improved, the non-perception of query operation of an upper-layer user is ensured, and the field change process with the minimum influence is realized.

Description

Technical Field

[0001] The present invention belongs to the technical field of big data processing, and in particular relates to a method for realizing flexible field change of a Hive large-scale partition table. Background Art

[0002] In the current Hive environment, the processing of massive data is particularly important. Especially for partitioned tables, the amount of data can reach a huge scale, which poses a considerable challenge to field changes. Generally speaking, for tables with up to hundreds of thousands of partitions, adding or modifying fields not only takes dozens of hours, but also often requires reprocessing historical data, which is obviously unacceptable in real applications. The root cause of this problem is that the design principle of Hive is more used to optimize data reading operations rather than dynamically adjusting its structure. In the context of massive data, any adjustment of the table structure, especially adding or deleting fields, means that all historical data must be reset and processed according to the new data structure, which leads to a huge consumption of processing time and resources. Therefore, flexible and efficient field changes for such a large-scale Hive partition table have become a technical problem that the industry urgently needs to solve. The solution to this problem can not only significantly improve data processing efficiency, but also save a large amount of computing resources, bring economic benefits, and enhance the flexibility of data processing. Summary of the invention

[0003] In view of the defects of the prior art, the present invention provides a method for implementing flexible field changes in a large-scale Hive partition table, which can effectively solve the above problems.

[0004] The technical solution adopted by the present invention is as follows:

[0005] The present invention provides a method for implementing flexible field change of a Hive large-scale partition table, comprising the following steps:

[0006] Step 1: Based on the divide-and-conquer principle, for the existing large-scale partitioned table, a strategy of partitioning by year is adopted to create an independent new data table for each year. The naming method of the new data table is related to the original table name of the original large-scale partitioned table. Specifically, the table name of the new data table of the corresponding year is obtained based on the original table name followed by an underscore and the corresponding year.

[0007] Step 2: Migrate the data in the original large-scale partition table to the new data table of the corresponding year according to the year. Then, update and repair the metadata of the new data table of each year to ensure the integration and maintenance of metadata and the integrity and consistency of the data after migration. The new data tables of each year are integrated together to form an overall Hive table.

[0008] Step 3: After the data migration and metadata repair are completed, the original large-scale partition table is deleted;

[0009] Step 4: Create a new Hive view. The Hive view integrates the new data tables of each year after the segmentation into a logical unit by integrating the union all operation and the precise time filter condition, and logically rebuilds the original large-scale partition table. The Hive view retains the table name of the original large-scale partition table and has the same data architecture as the original large-scale partition table.

[0010] Step 5, add fields to the Hive table:

[0011] Step 5.1, for each new data table obtained for each year, first locate the new data table for the latest year, and add a new field to the new data table for the latest year;

[0012] Step 5.2, after adding the new fields of the new data table of the latest year, by fine-tuning the Hive view, insert null values ​​in the corresponding field positions for the new data tables of other years in the Hive view, so as to add new fields to the virtual overall Hive table at the view level, so that the newly added fields are integrated into the overall architecture;

[0013] Step 6: When deleting fields from the Hive table, you only need to make corresponding adjustments in the Hive view, modify the Hive view definition, and mask the fields to be deleted.

[0014] Step 7: To modify the field name in the Hive table, simply rename the target field to the new field name using the AS keyword in the Hive view.

[0015] Step 8: To change the field type in the Hive table, adjust the field type in the Hive view.

[0016] Preferably, in step 7, when modifying the field name in the Hive table, the field type needs to remain compatible during the conversion process and ensure that the data logic is reasonable.

[0017] The method for implementing flexible field change of a Hive large-scale partition table provided by the present invention has the following advantages:

[0018] The present invention provides a method for implementing flexible field changes in a Hive large-scale partition table, which greatly simplifies the complexity of field changes in a large-scale partition table and improves the efficiency of the operation. It is particularly suitable for scenarios where field changes need to be implemented urgently but do not want to interfere with existing business operations. At the same time, users can query historical data like operating a single large table, enjoy the convenience of not having to perform large-scale rewrite operations on historical data, and effectively achieve the flexibility of field changes and data queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 A flowchart of a method for implementing flexible field changes in a Hive large-scale partition table provided by the present invention. DETAILED DESCRIPTION

[0020] In order to make the technical problems, technical solutions and beneficial effects solved by the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0021] The present invention provides a method for implementing flexible field changes in a Hive large-scale partition table, which greatly simplifies the complexity of field changes in a large-scale partition table and improves the efficiency of the operation. It is particularly suitable for scenarios where field changes need to be implemented urgently but do not want to interfere with existing business operations. At the same time, users can query historical data like operating a single large table, enjoy the convenience of not having to perform large-scale rewrite operations on historical data, and effectively achieve the flexibility of field changes and data queries.

[0022] The present invention provides a method for implementing flexible field changes in Hive large-scale partition tables, referring to Figure 1 , including the following steps:

[0023] 1. A method for implementing flexible field changes in a large-scale Hive partition table, comprising the following steps:

[0024] Step 1: Based on the divide-and-conquer principle, for the existing large-scale partitioned table, a strategy of partitioning by year is adopted to create an independent new data table for each year. The naming method of the new data table is related to the original table name of the original large-scale partitioned table. Specifically, the table name of the new data table of the corresponding year is obtained based on the original table name followed by an underscore and the corresponding year.

[0025] Step 2: Migrate the data in the original large-scale partition table to the new data table of the corresponding year according to the year. Then, update and repair the metadata of the new data table of each year to ensure the integration and maintenance of metadata and the integrity and consistency of the data after migration. The new data tables of each year are integrated together to form an overall Hive table.

[0026] Step 3: After the data migration and metadata repair are completed, the original large-scale partition table is deleted;

[0027] Step 4: Create a new Hive view. The Hive view integrates the new data tables of each year after the segmentation into a logical unit by integrating the union all operation and the precise time filter condition, and logically rebuilds the original large-scale partition table. The Hive view retains the table name of the original large-scale partition table and has the same data architecture as the original large-scale partition table.

[0028] Step 5, add fields to the Hive table:

[0029] Step 5.1, for each new data table obtained for each year, first locate the new data table for the latest year, and add a new field to the new data table for the latest year;

[0030] Step 5.2, after adding the new fields of the new data table of the latest year, by fine-tuning the Hive view, insert null values ​​in the corresponding field positions for the new data tables of other years in the Hive view, so as to add new fields to the virtual overall Hive table at the view level, so that the newly added fields are integrated into the overall architecture;

[0031] Step 6: When deleting fields from the Hive table, you only need to make corresponding adjustments in the Hive view, modify the Hive view definition, and mask the fields to be deleted.

[0032] Step 7: To modify the field name in the Hive table, simply rename the target field to the new field name using the AS keyword in the Hive view.

[0033] Step 8: To change the field type in the Hive table, adjust the field type in the Hive view.

[0034] An example is listed below:

[0035] Step 1: Based on the divide-and-conquer strategy, for the existing large-scale partitioned table, a year-based partitioning strategy is adopted to create independent new data tables for each year. The naming of these new data tables is relevant and consistent with the original table names of the original large-scale partitioned table. Specifically, the table name of the new data table of the corresponding year is obtained based on the original table name followed by an underscore and the corresponding year. This strategy makes data management more refined and facilitates subsequent operations.

[0036] For example, take a user behavior fact table of an App as an example. The original table name of the original large-scale partition table is "app_event". The partitions are organized in the form of (date, event name), which contains all user behavior data from 2019 to 2024. Now it is divided by year. First, a new data table is created for the data of 2019, named "app_event_2019", and then a new data table is created for the data of 2020, named "app_event_2020", and so on, until a new data table is created for the data of 2024, named "app_event_2024".

[0037] This naming method not only preserves the information related to the original table, but also allows you to clearly see the data year corresponding to each new data table, providing convenience for subsequent operations.

[0038] Step 2: Migrate the data in the original large-scale partition table to the corresponding new data table according to the year. At the same time, carefully update and repair the metadata of the new data table of each year to ensure the integrity and consistency of the data after migration. The new data tables of each year are integrated together to form an overall Hive table.

[0039] For example, for each year of the new data table created previously, use the following steps:

[0040] First, migrate all the data in 2019 in the original large-scale partitioned table "app_event". By using the HDFS mv command, all the data in 2019 are successfully transferred to a new data table named "app_event_2019".

[0041] Afterwards, using the same method, the data of each year can be flexibly migrated to the new data table of the corresponding year until all the data has been migrated.

[0042] Next, update and repair the metadata of the new data table: For each year's new data table, execute the msck repair table command to perform elastic partition synchronization to ensure smooth metadata integration and maintenance, thereby ensuring the integrity and consistency of the data after migration.

[0043] Step 3: After the data migration and metadata repair are completed, the original large-scale partition table is deleted.

[0044] For example, after the above data migration and metadata repair steps, all the data in the original large-scale partition table "app_event" has been completely migrated to the new data table divided by year. Since the data in the original table is no longer used, deleting it can avoid unnecessary data confusion and redundant storage usage.

[0045] In Hive, you can directly execute the "Drop table app_event" command, so that the original large-scale partition table "app_event" can be safely removed. After this step, Hive only retains the newly created and filled data table divided by year, thus laying the foundation for the subsequent field change work.

[0046] Step 4: Create a new Hive view. The Hive view integrates the new data tables of each year after the segmentation into a logical unit by integrating the union all operation and the precise time filter condition, and logically rebuilds the original large-scale partition table. The Hive view retains the table name of the original large-scale partition table and has the same data architecture as the original large-scale partition table.

[0047] Specifically, in order to logically rebuild the original large-scale partitioned table and ensure seamless continuity of data access, a Hive view is created. This Hive view uses the union all operation combined with precise time filtering conditions to re-integrate the new data tables of different years after the split into a logical unit and retain the naming of the original table, so as not to affect the existing data access mode.

[0048] For example: Based on the new data tables previously split by year, create a new Hive view. The name of this Hive view is the same as the original table, which is still "app_event". The creation method is as follows: In the creation statement, use the "create view" syntax followed by "app_event", and then use the as keyword to give it specific content. The specific expression is: create view app_event as select field from app_event_2020where datekey>='20200101'and datekey<='20201231'union all select field from app_event_2021wheredatekey>='20210101'and datekey<='20211231'union all....... Although at the physical storage level, the data is divided and placed in new data tables of different years, at the logical level, the original "app_event" table is reorganized through this new Hive view. That is to say, when querying and operating data, the Hive view "app_event" has the same data architecture as the original table "app_event", which undoubtedly provides great convenience for subsequent data query and operation, making data management and use more flexible and efficient.

[0049] Step 5, add fields to the Hive table:

[0050] Step 5.1, for each new data table obtained for each year, first locate the new data table for the latest year, and add a new field to the new data table for the latest year;

[0051] Specifically, adding new fields to the new data table of the latest year is much simpler and faster than directly operating the historical data table, because the size of the new data table after segmentation is relatively small, and the adding operation can usually be completed within a few minutes.

[0052] For example, if you need to add a new field to the "app_event" table, such as the user device information field "device_info". Since the original table has been finely divided by year, the data scale of the new data table "app_event_2024" is relatively small. Therefore, by executing the command: ALTER TABLE app_event_2024ADDCOLUMNS(device_info string), you can quickly and efficiently add a new field to the new data table. Due to the small data scale, this process can usually be completed smoothly within a few minutes, greatly improving efficiency.

[0053] Step 5.2, after adding the new fields of the new data table of the latest year, by fine-tuning the Hive view, insert null values ​​in the corresponding field positions for the new data tables of other years in the Hive view, so as to add new fields to the virtual overall Hive table at the view level, so that the newly added fields are integrated into the overall architecture;

[0054] Specifically, after adding the new fields of the new data table of the latest year, by fine-tuning the Hive view, null values ​​are inserted in the corresponding field positions for the new data tables of other years, so as to add new fields to the virtual overall Hive table and perfectly integrate the new field operations into the overall architecture.

[0055] For example, a new field "device_info" has been added to the new data table "app_event_2024". Next, you need to modify the Hive view to synchronize this new field in the new data tables of other years. Since there is no actual "device_info" information for the data from 2019 to 2023, insert a "null" value at the corresponding position in the Hive view. Specifically, you can use the following SQL to add a null value as the "device_info" field for the new data tables of other years in the union query:

[0056] CREATE OR REPLACE VIEW app_event AS SELECT*,null as device_info FROMapp_event_2019UNION ALL SELECT*,null as device_info FROM app_event_2020UNIONALL SELECT*,null as device_info FROM app_event_2021UNION ALL......SELECT*,device_info FROM app_event_2024. After this operation, when users query the Hive view "app_event", they can see that all new data tables have the "device_info" field. Although this field is actually null in the new data tables from 2019 to 2023, the field structure can be kept consistent in the overall table (Hive view) no matter which year's data is queried.

[0057] Step 6: When deleting fields from the Hive table, you only need to make corresponding adjustments in the Hive view, modify the Hive view definition, and mask the fields to be deleted.

[0058] For example, suppose you need to delete the "device_info" field mentioned earlier. Since this field is added at the view level, you only need to adjust it in the Hive view. The specific operation is to modify the view definition and delete or comment out the part involving the "device_info" field. In this way, you can achieve the effect of deleting the field in the entire Hive table, while avoiding the operation of the physical table, greatly improving work efficiency.

[0059] Step 7: To modify the field name in the Hive table, simply rename the target field to the new field name using the AS keyword in the Hive view.

[0060] For example, if you need to change the name of the "device_info" field in "app_event" to "device_detail", you can modify it in the Hive view without operating the original partition table. That is, execute the following command in the Hive view: ALTER VIEW app_event AS SELECT...,device_info AS device_detail,...; this will enable us to modify the field name at the view level, and the original "device_info" field name will be changed to the "display_detail" field, thereby realizing the update of the field name.

[0061] Step 8: To change the field type in the Hive table, adjust the field type in the Hive view.

[0062] Specifically, when changing the field type in a Hive table, you can adjust the field type by applying cast (field AS new type) in the Hive view. This requires that the field type remains compatible during the conversion process and ensures that the data logic is reasonable.

[0063] For example, suppose you need to change an integer type field "event_id" in the "app_event" view to a string type. First, you need to confirm that this type conversion is logically reasonable, whether from the usage scenario or from the data content itself, and that the value of "event_id" can be reasonably interpreted as a string. After confirming that it is correct, apply the cast operation to the "event_id" field in the Hive view. The specific method is to use the following HQL statement: ALTER VIEW app_eventSELECT other_columns, cast (event_id as string) AS event_id,... In this way, the change of the "event_id" field type is implemented at the view level, which not only ensures the logical consistency of the field, but also does not require any processing of the underlying data storage, which is both simple and efficient.

[0064] The present invention provides a method for implementing flexible field changes in a large-scale Hive partition table, the idea is:

[0065] Based on the divide-and-conquer principle, the massive Hive partitioned data tables are divided according to the year dimension. Each year corresponds to a separate new data table, and the naming rule follows the format of the original table name followed by an underscore and the corresponding year.

[0066] To achieve unified access to data for each year, a Hive view is created to logically connect the new data tables after each split using the union all operation and time filter condition, and the original table name is given as the view name.

[0067] The field addition operation only needs to be performed on the new data table of the most recent year, and the same field position in other years is marked as a null value in the Hive view; field deletion and modification operations only need to be performed on the Hive view, which greatly improves the flexibility and timeliness of the table structure.

[0068] The present invention provides a method for implementing flexible field changes in Hive large-scale partitioned data tables, which not only significantly improves the ability of data warehouses to quickly and flexibly adjust table structures when processing massive data, but also ensures the imperceptibility of upper-level user query operations, thereby achieving a field change process with minimal impact.

[0069] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principle of the present invention. These improvements and modifications should also be considered as the scope of protection of the present invention.

Claims

1. A method for implementing flexible field changes in a large-scale Hive partition table, characterized in that: The following steps are involved: Step 1: Based on the divide-and-conquer principle, for the existing large-scale partition table, adopt the strategy of dividing by year and create an independent new data table for each year; The naming method of the new data table is related to the original table name of the original large-scale partitioned table. Specifically, the table name of the new data table of the corresponding year is obtained based on the original table name followed by an underscore and the corresponding year. Step 2: Migrate the data in the original large-scale partition table to the new data table of the corresponding year according to the year. Then, update and repair the metadata of the new data table of each year to ensure the integration and maintenance of metadata and the integrity and consistency of the data after migration. The new data tables of each year are integrated together to form an overall Hive table. Step 3: After the data migration and metadata repair are completed, the original large-scale partition table is deleted; Step 4: Create a new Hive view. The Hive view integrates the new data tables of each year after the segmentation into a logical unit by integrating the union all operation and the precise time filter condition, and logically rebuilds the original large-scale partition table. The Hive view retains the table name of the original large-scale partition table and has the same data architecture as the original large-scale partition table. Step 5: Add fields to the Hive table: Step 5.1, for each new data table obtained for each year, first locate the new data table for the latest year, and add a new field to the new data table for the latest year; Step 5.2, after adding the new fields of the new data table of the latest year, by fine-tuning the Hive view, insert null values ​​in the corresponding field positions for the new data tables of other years in the Hive view, so as to add new fields to the virtual overall Hive table at the view level, so that the newly added fields are integrated into the overall architecture; Step 6: When deleting fields from the Hive table, you only need to make corresponding adjustments in the Hive view, modify the Hive view definition, and mask the fields to be deleted. Step 7: To modify the field name in the Hive table, simply rename the target field to the new field name using the AS keyword in the Hive view. Step 8: To change the field type in the Hive table, adjust the field type in the Hive view.

2. According to claim 1, a method for implementing flexible field changes in a Hive large-scale partition table is characterized in that: In step 7, when modifying the field name in the Hive table, the field type must remain compatible during the conversion process and ensure that the data logic is reasonable.

Images