A method for incremental data collection based on HIVE

Abstract

The present invention discloses a data incremental collection method based on HIVE, and relates to the technical field of big data collection and data cleaning and management. When using the data incremental collection method based on HIVE, it only needs to configure relevant collection tasks and sql script tasks, put the sql script into a sql execution tool that can execute sql scripts with parameters, and only expose the table name, creation time, update time, primary key, and the time period n when the original table may delete data. The user only needs to fill in these key information to configure the sql execution task, and the above problems can be solved uniformly to ensure data consistency; the sql script processing flow has strong reusability, flexible form, and simple operation. Put the sql script into the execution tool, and only need to fill in the relevant parameters to perform various types of management, which is suitable for all types of tables in the hive library and time fields of different formats in the table.

Description

Technical Field

[0001] The present invention relates to the technical field of big data collection and data cleaning management, and in particular to a HIVE-based data incremental collection method. Background Art

[0002] The data center needs to collect and aggregate data tables from various systems to the hive database of the center. When the amount of data in the table is small, in order to ensure data accuracy and consistency with the source database, a full collection can be performed on a daily basis, that is, full collection covers updates. However, when the table is a business table with a large amount of data, the amount of data in a single table is in the hundreds of millions or even billions, or the data storage capacity of a single table is in the hundreds of GB or even several TB, it is not suitable to collect the full amount every day, and incremental collection is required. In theory, it is only necessary to extract the data that was added or updated on the previous day every day according to the time of addition and update in the data table. This can reduce the collection pressure on the data center and the source database.

[0003] When performing incremental data collection of tables in the data center, the following problems may occur: First, when data is updated, the source database will only modify and update the current data, and update the update time at the same time. However, after data is collected according to the update time, the hive database will retain the same data twice, one is collected before the data is updated based on the previous addition time or update time, and the other is collected after the update time, resulting in two data under the same primary key; second, the source database will delete data within a certain period of time, and normal extraction cannot detect the deletion of data; fourth, the source database will modify the data deleted within a certain period of time and add it back to the source database, and normal extraction will not detect this operation. In view of this, we have designed a HIVE-based incremental data collection method. Summary of the invention

[0004] 1. Technical issues to be resolved

[0005] In view of the deficiencies in the prior art, the present invention provides a HIVE-based data incremental collection method, which solves the problems mentioned in the above background technology.

[0006] (II) Technical solution

[0007] To achieve the above objectives, the present invention is implemented through the following technical solutions: a data incremental collection method based on HIVE, including the following parameters:

[0008] ${PartDate}: represents yesterday's date in the format of year, month, and day. You need to fill it in according to the specific parameters set by the collection tool when collecting hive partitions.

[0009] ${ods_table_name}: the name of the ODS table collected to Hive;

[0010] ${only_id}: The composite value that determines the unique primary key of the table

[0011] ${REC_CREATE_TIME}: time field, creation time;

[0012] ${REC_REVISE_TIME}: time field, update time;

[0013] ${check_days}: the time period during which the source database deletes data. This should be consistent with the time period during which the source database is collected in step 2. For example, if the source database deletes data within 30 days, the value here will be set to 30.

[0014] The specific implementation steps are as follows:

[0015] S1: Initialize the collection of full data, suitable for initial collection or when full data needs to be rerun;

[0016] S2: extract the data in the source database within the deletion time to the ods table regularly;

[0017] S3: Perform SQL script management on the ods table, and set the management frequency to every day. At the same time, the scheduled execution interval is set after the second step is completed.

[0018] S4: Timely and permanent storage of 30210100 partition data.

[0019] Optionally, when performing the initialization to collect the full amount of data, the first task is a common task, which extracts the full amount and puts it into the ${PartDate} partition;

[0020] The second is a SQL script task, which deletes all partitions before ${PartDate} and puts the latest full data into the _f table. The specific script is as follows:

[0021] First, extract the full amount and delete the previous data:

[0022] alter table${ods_table_name}drop partition(pt<${PartDate});

[0023] alter table${ods_table_name}drop partition(pt='30210100');

[0024] Secondly, re-draw the data and recalculate the data if the table is deleted:

[0025] truncate table${ods_table_name}_d;

[0026] Finally, put all the data into the _f table:

[0027] SET hive.support.quoted.identifiers=None;

[0028] insert overwrite table${ods_table_name}_f

[0029] select*from${ods_table_name}.

[0030] Optionally, when extracting the data of the source database within the deletion time to the ods table, the partition name is pt = '30210100', and the timing frequency is set to extract every day. The timing time is determined according to the business demand time. The SQL for extracting the source database table can be determined according to the time field of the source database table. If there is only one time field, it can be written as:

[0031] select*from table where CREATION_DATE

[0032] >=to_char(sysdate-30,'yyyymmdd')

[0033] AND CREATION_DATE <to_char(sysdate,'yyyymmdd');

[0034] Optionally, when the source database table has two time fields, the script can be written as:

[0035] select*from table where(CREATION_DATE

[0036] >=to_date(to_char(sysdate-30,'YYYY-MM-DD'),'YYYY-MM-DD')

[0037] AND CREATION_DATE

[0038] <to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD'))

[0039] OR(LAST_UPDATE_DATE

[0040] >=to_date(to_char(sysdate-30,'YYYY-MM-DD'),'YYYY-MM-DD')

[0041] AND LAST_UPDATE_DATE

[0042] <to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD'))。

[0043] Optionally, when performing SQL script management on the ODS table, a temp table with a merged primary key is created to simplify the process of comparing primary keys, and the table structure and table content of the temp table are the same as those of the ODS table:

[0044] create table if not exists${ods_table_name}_temp as select*,"

[0045] as only_id from${ods_table_name}limit 0;

[0046] insert overwrite table${ods_table_name}_temp

[0047] select*,concat(${only_id})as only_id from${ods_table_name}.

[0048] Optionally, the latest partition in the temp table, that is, the data of pt=30210100, that is, the latest 30-day data, is queried for the latest 30-day data in the temp table, and the data that is null in pt=30210100 is found, which is the deleted data in the source database, and is inserted into the ods_d deletion table. When the source database requires a time field incremental collection:

[0049] create table if not exists${ods_table_name}_d as select*from${ods_table_name}_temp limit 0;

[0050] insert into${ods_table_name}_d

[0051] select a.*from${ods_table_name}_temp a

[0052] left join (select * from ${ods_table_name}_temp where pt = '30210100') b

[0053] on concat(a.only_id) = concat(b.only_id)

[0054] where a.${REC_CREATE_TIME} >= regexp_replace(substr(date_sub

[0055] (FROM_UNIXTIME(UNIX_TIMESTAMP()), ${check_days}), 1, 10), '-', '')

[0056] and

[0057] a.${REC_CREATE_TIME} < from_unixtime(unix_timestamp(), 'yyyyMMdd') and

[0058] b.only_id is null。

[0059] Optionally, when two time fields need to be collected in the source library:

[0060] create table if not exists ${ods_table_name}_d as select * from ${ods_table_name}_temp limit 0;

[0061] insert into ${ods_table_name}_d

[0062] select a.* from ${ods_table_name}_temp a

[0063] left join (select * from ${ods_table_name}_temp where pt = '30210100') b

[0064] on concat(a.only_id) = concat(b.only_id)

[0065] where (a.${REC_CREATE_TIME} >= regexp_replace(substr(date_sub

[0066] (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',")

[0067] and

[0068] a.${REC_CREATE_TIME} <from_unixtime(unix_timestamp(),'yyyyMMdd')and

[0069] b.only_id is null)

[0070] or(a.${REC_REVISE_TIME}>=regexp_replace(substr(date_sub

[0071] (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',")

[0072] and

[0073] a.${REC_REVISE_TIME} <from_unixtime(unix_timestamp(),'yyyyMMdd')and

[0074] b.only_id is null).

[0075] Optionally, create a temporary table tempf with the same table structure as the temp table, and insert the data after removing the deleted data into the tempf table:

[0076] create table if not exists${ods_table_name}_tempf as select*from${ods_table_name}_temp limit 0;

[0077] insert overwrite table${ods_table_name}_tempf

[0078] select a.*from${ods_table_name}_temp a where not exists

[0079] (select*from(select distinct only_id from${ods_table_name}_d)b wherea.only_id=b.only_id).

[0080] Optionally, create an _f table with the same structure as the ods table, deduplicate the tempf table, obtain the latest modified data, remove the joint primary key, and obtain an ods_f table that is consistent with the source database:

[0081] create table if not exists${ods_table_name}_f as select*from${ods_table_name}limit 0;

[0082] SET hive.support.quoted.identifiers=None;

[0083] insert overwrite table${ods_table_name}_f

[0084] select `(only_id)? +.+` from

[0085] (select distinct b.*

[0086] FROM(SELECT only_id,

[0087] pt,

[0088] row_number()

[0089] over(PARTITION BY only_id ORDER BY pt desc)AS row_num

[0090] FROM${ods_table_name}_tempf)a

[0091] left join${ods_table_name}_tempf b

[0092] on a.only_id=b.only_id

[0093] and a.pt=b.pt

[0094] WHERE a.row_num=1)c.

[0095] Optionally, the data collected in the second step of the day to the 30210100 partition is stored in the partition of the previous day in the ODS table to store the data permanently. The task execution frequency is set according to the number of days the source database deletes data. For example, if data within 30 days is deleted, it is executed every 30 days, and the timing time is after the second step of the day is collected:

[0096] SET hive.support.quoted.identifiers=None;

[0097] insert overwrite table${ods_table_name}partition(pt=${PartDate})

[0098] select`(pt)? +.+`from${ods_table_name}where pt='30210100'.

[0099] (III) Beneficial effects

[0100] The present invention provides a data incremental collection method based on HIVE. It has the following beneficial effects:

[0101] (1) When using this HIVE-based data incremental collection method, you only need to configure the relevant collection tasks and SQL script tasks, put the SQL script into the SQL execution tool that can execute SQL scripts with parameters, and only expose the table name, creation time, update time, primary key, and the time period n when the original table may delete data. The user only needs to fill in these key information to configure the SQL execution task, which can uniformly solve the above problems and ensure data consistency.

[0102] (2) When using this HIVE-based data incremental collection method, the SQL script processing flow is highly reusable, flexible in form, and simple to operate. By putting the SQL script into the execution tool, you only need to fill in the relevant parameters to perform various types of governance. It is suitable for all types of tables in the hive library and time fields in different formats in the table.

[0103] (3) When using the HIVE-based data incremental collection method, this script is a processing flow based on the case with the most problems. When there are only a few problems, only the relevant SQL scripts that solve the problems can be retained as appropriate, making the use of this collection method more flexible and convenient. BRIEF DESCRIPTION OF THE DRAWINGS

[0104] Figure 1 The figure is a flow chart of the incremental data collection method based on HIVE of the present invention. DETAILED DESCRIPTION

[0105] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0106] See also Figure 1 ,The present invention provides a technical solution: a data incremental collection method based on HIVE, which includes the following parameters when collecting data, ${PartDate}: represents yesterday's date, in the format of year-month-day (20210101), and needs to be filled in according to the specific setting parameters of the collection tool itself for hive partition collection.

[0107] ${ods_table_name}: the name of the ODS table collected to Hive.

[0108] ${only_id}: The composite value of the primary key that determines the uniqueness of the table.

[0109] ${REC_CREATE_TIME}: time field, creation time.

[0110] ${REC_REVISE_TIME}: time field, update time.

[0111] ${check_days}: The time period during which the source database will delete data. This should be consistent with the time period during which the source database is collected in the second step. For example, if the source database deletes data within 30 days, the value here will be set to 30.

[0112] The specific implementation steps are as follows:

[0113] S1: Initialize the collection of full data. This is applicable to initializing the collection or re-running the full data. When performing the initialization of the collection of full data, the first task is a common task, which extracts the full data and puts it into the ${PartDate} partition.

[0114] The second is a SQL script task, which deletes all partitions before ${PartDate} and puts the latest full data into the _f table. The specific script is as follows:

[0115] First, extract the full amount and delete the previous data:

[0116] alter table${ods_table_name}drop partition(pt<${PartDate});

[0117] alter table${ods_table_name}drop partition(pt='30210100');

[0118] Secondly, re-draw the data and recalculate the data if the table is deleted:

[0119] truncate table${ods_table_name}_d;

[0120] Finally, put all the data into the _f table:

[0121] SET hive.support.quoted.identifiers=None;

[0122] insert overwrite table${ods_table_name}_f

[0123] select*from${ods_table_name}.

[0124] S2: extract the data of the source database within the deletion time to the ods table at regular intervals. When extracting the data of the source database within the deletion time to the ods table, the partition name is pt = '30210100', and the regular frequency is set to extract every day. The regular time is determined according to the business demand time. The SQL for extracting the source database table can be determined according to the time field of the source database table. If there is only one time field, it can be written as:

[0125] select*from table where CREATION_DATE

[0126] >=to_char(sysdate-30,'yyyymmdd')

[0127] AND CREATION_DATE <to_char(sysdate,'yyyymmdd');

[0128] When the source database table has two time fields, the script can be written as:

[0129] select*from table where(CREATION_DATE

[0130] >=to_date(to_char(sysdate-30,'YYYY-MM-DD'),'YYYY-MM-DD')

[0131] AND CREATION_DATE

[0132] <to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD'))

[0133] OR(LAST_UPDATE_DATE

[0134] >=to_date(to_char(sysdate-30,'YYYY-MM-DD'),'YYYY-MM-DD')

[0135] AND LAST_UPDATE_DATE

[0136] <to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD'))。

[0137] S3: Perform SQL script management on the ods table, and set the management frequency to every day. At the same time, set the scheduled execution time after the second step is completed. In the first step, create a temp table with a merged primary key to simplify the process of comparing primary keys. The table structure and content of the temp table are the same as those of the ods table:

[0138] create table if not exists${ods_table_name}_temp as select*,” as only_id from${ods_table_name}limit 0;

[0139] insert overwrite table${ods_table_name}_temp

[0140] select*,concat(${only_id})as only_id from${ods_table_name};

[0141] The second step is to query the latest partition in the temp table, that is, the data of pt=30210100, that is, the latest 30 days of data, and find out the data that is null in pt=30210100, which is the deleted data in the source database, and insert it into the ods_d deletion table. When the source database needs a time field incremental collection:

[0142] create table if not exists${ods_table_name}_d as select*from${ods_table_name}_temp limit 0;

[0143] insert into${ods_table_name}_d

[0144] select a.*from${ods_table_name}_temp a

[0145] left join(select*from${ods_table_name}_temp where pt='30210100')b

[0146] on concat(a.only_id)=concat(b.only_id)

[0147] where a.${REC_CREATE_TIME}>=regexp_replace(substr(date_sub

[0148] (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',”)

[0149] And

[0150] a.${REC_CREATE_TIME}<from_unixtime(unix_timestamp(),'yyyyMMdd')andb.only_id is null;

[0151] The third step, when two time fields need to be collected in the source library:

[0152] create table if not exists${ods_table_name}_d as select*from${ods_table_name}_temp limit 0;

[0153] insert into${ods_table_name}_d

[0154] select a.*from${ods_table_name}_temp a

[0155] left join(select*from${ods_table_name}_temp where pt='30210100')b

[0156] on concat(a.only_id)=concat(b.only_id)

[0157] where(a.${REC_CREATE_TIME}>=regexp_replace(substr(date_sub

[0158] (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',")

[0159] And

[0160] a.${REC_CREATE_TIME} <from_unixtime(unix_timestamp(),'yyyyMMdd')andb.only_id is null)

[0161] or(a.${REC_REVISE_TIME}>=regexp_replace(substr(date_sub

[0162] (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',")

[0163] and

[0164] a.${REC_REVISE_TIME} <from_unixtime(unix_timestamp(),'yyyyMMdd')andb.only_id is null);

[0165] Step 4: Create a temporary table tempf with the same table structure as the temp table, and insert the data after removing the deleted data into the tempf table:

[0166] create table if not exists${ods_table_name}_tempf as select*from${ods_table_name}_temp limit 0;

[0167] insert overwrite table${ods_table_name}_tempf

[0168] select a.*from${ods_table_name}_temp a where not exists

[0169] (select*from(select distinct only_id from${ods_table_name}_d)b wherea.only_id=b.only_id);

[0170] Step 5: Create the _f table with the same structure as the ods table, deduplicate the tempf table, take the latest modified data, remove the joint primary key, and obtain the ods_f table that is consistent with the source database:

[0171] create table if not exists${ods_table_name}_f as select*from${ods_table_name}limit 0;

[0172] SET hive.support.quoted.identifiers=None;

[0173] insert overwrite table${ods_table_name}_f

[0174] select `(only_id)? +.+` from

[0175] (select distinct b.*

[0176] FROM(SELECT only_id,

[0177] pt,

[0178] row_number()

[0179] over(PARTITION BY only_id ORDER BY pt desc)AS row_num

[0180] FROM${ods_table_name}_tempf)a

[0181] left join${ods_table_name}_tempf b

[0182] on a.only_id=b.only_id

[0183] and a.pt=b.pt

[0184] WHERE a.row_num=1)c;

[0185] S4: Store the data of partition 30210100 permanently on a scheduled basis. Store the data collected in the second step of the day to partition 30210100 into the partition of the previous day in the ods table to store the data permanently (because the 30-day data extracted from partition 30210100 will be overwritten every day). The task execution frequency is set according to the number of days the source database deletes data. For example, if data within 30 days is deleted, the task is executed every 30 days. The scheduled time is after the second step of the day is collected:

[0186] SET hive.support.quoted.identifiers=None;

[0187] insert overwrite table${ods_table_name}partition(pt=${PartDate})

[0188] select`(pt)? +.+`from${ods_table_name}where pt='30210100'.

[0189] Working principle: When the HIVE-based data incremental collection method is in use, first, perform initial data collection, and collect all the data of the source database table (or collect part of the data as needed) into a partitioned hive table. Here we define the partition as a string type field, named pt, and the partition value is in the year-month-day format. The content is yesterday's date. For example, today is 20210102, and the partition where the full data is collected is pt=20210101. At this time, we call the table an ods table, which stores the data initially collected from the source database. Secondly, understand the time period when the source database table may be deleted. For example, if we know that the source database table may delete data within 30 days, we collect data greater than or equal to 30 days ago and less than today every day and put it into a partition with a larger date content. Because the latest data is to be placed in this partition, pt needs to be sorted in reverse order when reordering, and this part of the data needs to be put in front as the latest data. There will be detailed SQL statements in the following steps, such as putting it in pt=30210100, and covering more every day. The partition data is newly added to the ods table. After that, the ods table is subjected to the SQL script to detect the deleted data, store the deleted data in the ods_d table (the table structure is the same as the ods table), remove the deleted data, and remove the duplicate data. Finally, the data consistent with the data of the source database as of the previous day is obtained. The data is overwritten and updated to the ods_f table every day. The ods_f table is the final managed table. Finally, the data collected within 30 days of a single day, that is, the data in pt=30210100, is regularly put into the variable partition with the content of yesterday's date, and it is always The data is stored in the ODS table for a long time, so that the ODS table contains continuous and complete source database data (to ensure data integrity, the collection time can be appropriately crossed, and there will be a deduplication step at the end). This part of the data is managed in the third step every day to obtain the final data table. The timing time can be determined according to the time period when the source database deletes data. For example, in this example, it can be scheduled to be executed every 30 days, so that after the source database data is updated, two new and old data appear corresponding to the data under a primary key in the hive target database. At the same time, the source database will delete data within a certain period of time, and normal extraction cannot detect this deletion operation; the source database will modify the data deleted within a certain period of time and add it back to the source database, and normal extraction will not detect this operation. This is a reusable SQL script process that can extract parameters. When using it, you only need to pay attention to the primary key of the data table, the time field, and the time period (for example, within 30 days) when the specific business table will delete data. You don’t need to pay attention to the SQL logic. You can directly configure the SQL script task, which saves manpower, material and financial resources, unifies the initial cleaning and management steps after data collection, and enables ordinary people to easily use this method to improve work efficiency.

[0190] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for incremental data collection based on HIVE, characterized by: Includes the following parameters: ${PartDate}: represents yesterday's date in the format of year, month, and day. It is filled in according to the specific parameters set by the collection tool when collecting hive partitions. ${ods_table_name}: the name of the ODS table collected to Hive; ${only_id}: The composite value that determines the unique primary key of the table ${REC_CREATE_TIME}: time field, creation time; ${REC_REVISE_TIME}: time field, update time; ${check_days}: The time period during which the source database deletes data. This period is consistent with the time period during which the source database is collected in step 2. The source database deletes data within 30 days. The value here is set to 30. The specific implementation steps are as follows: S1: Initialize the collection of full data, suitable for initial collection or when full data needs to be rerun; S2: extract the data in the source database within the deletion time to the ods table regularly; S3: Perform SQL script management on the ods table, and set the management frequency to every day. At the same time, the scheduled execution interval is set after the second step is completed. S4: Timely and permanent storage of 30210100 partition data; When performing the initialization to collect the full amount of data in step S1, the first one is a common task, extracting the full amount and putting it into the ${PartDate} partition; The second is a SQL script task, which deletes all partitions before ${PartDate} and puts the latest full data into the _f table. The specific script is as follows: First, extract the full amount and delete the previous data: alter table${ods_table_name}drop partition(pt<${PartDate}); alter table${ods_table_name}drop partition(pt='30210100'); Secondly, re-draw the data and recalculate the data if the table is deleted: truncate table${ods_table_name}_d; Finally, put all the data into the _f table: SET hive.support.quoted.identifiers=None; insert overwrite table${ods_table_name}_f select*from${ods_table_name}.

2. The method for incremental data collection based on HIVE according to claim 1, characterized in that: When extracting the data of the source database within the deletion time to the ods table, the partition name is pt = '30210100', and the timing frequency is set to extract every day. The timing time is determined according to the business demand time. The SQL for extracting the source database table is determined according to the time field of the source database table. If there is only one time field, it can be written as: select*from table where CREATION_DATE >=to_char(sysdate-30,'yyyymmdd') AND CREATION_DATE <to_char(sysdate,'yyyymmdd')。 3. The method for incremental data collection based on HIVE according to claim 2, characterized in that: When the source database table has two time fields, the script is written as follows:

4. The method for incremental data collection based on HIVE according to claim 1, characterized in that: When the ods table is managed with SQL scripts, a temp table with a merged primary key is created to simplify the process of comparing primary keys, and the table structure and table content of the temp table are the same as those of the ods table: create table if not exists${ods_table_name}_temp as select*,”as only_idfrom${ods_table_name}limit 0; insert overwrite table${ods_table_name}_temp select*,concat(${only_id})as only_id from${ods_table_name}.

5. The method for incremental data collection based on HIVE according to claim 4, characterized in that: The latest partition in the temp table, that is, the data of pt=30210100, that is, the latest 30-day data, the 30-day data in the temp table, query the latest 30-day data, find out the data that is null in pt=30210100, that is, the deleted data in the source database, and insert it into the ods_d deletion table. When the source database needs a time field incremental collection: create table if not exists${ods_table_name}_d as select*from${ods_table_name}_temp limit 0; insert into${ods_table_name}_d select a.*from${ods_table_name}_temp a left join(select*from${ods_table_name}_temp where pt='30210100')b on concat(a.only_id)=concat(b.only_id) where a.${REC_CREATE_TIME}>=regexp_replace(substr(date_sub (FROM_UNIXTIME(UNIX_TIMESTAMP()),${check_days}),1,10),'-',") and a.${REC_CREATE_TIME} <from_unixtime(unix_timestamp(),'yyyyMMdd')and b.only_id is null.

6. The method for incremental data collection based on HIVE according to claim 5, characterized in that: When the source library requires two time fields to be collected:

7. The method for incremental data collection based on HIVE according to claim 4, characterized in that: Create a temporary table tempf with the same table structure as the temp table, and insert the data after removing the deleted data into the tempf table: create table if not exists${ods_table_name}_tempf as select*from${ods_table_name}_temp limit 0; insert overwrite table${ods_table_name}_tempf select a.*from${ods_table_name}_temp a where not exists (select*from(select distinct only_id from${ods_table_name}_d)b wherea.only_id=b.only_id).

8. The method for incremental data collection based on HIVE according to claim 1, characterized in that: Create an _f table with the same structure as the ods table, deduplicate the tempf table, take the latest modified data, remove the joint primary key, and obtain an ods_f table that is consistent with the source database:

9. The method for incremental data collection based on HIVE according to claim 1, characterized in that: The data collected in the second step of the day to the 30210100 partition is stored in the partition of the previous day in the ODS table to store the data permanently. The task execution frequency is set according to the number of days the source database deletes data. If data within 30 days is deleted, it will be executed every 30 days. The timing time is after the second step of the day is collected: SET hive.support.quoted.identifiers=None; insert overwrite table${ods_table_name}partition(pt=${PartDate}) select`(pt)? +.+`from${ods_table_name}where pt='30210100'.

Images