一种基于hive的数据增量采集方法
技术领域：
：1.本发明涉及大数据采集与数据清洗治理
技术领域：
：，具体为一种基于hive的数据增量采集方法。
背景技术：
：：2.数据中台需要采集汇聚各个系统数据表至中台的hive数据库，在表数据量较小的情况下，为保证数据准确性和与源库的一致性，可以进行每天周期全量的，即全量采集覆盖更新，但是表为数据量较大的业务表的情况下，单表数据量在数亿甚至数十亿或单表数据存储量在数百g甚至数t时，就不适合每天采集全量，需要增量采集，理论上只需要按照数据表中的新增时间和更新时间，每日抽取前一日所新增或者更新的数据，这样可以减轻数据中台及源库的采集压力。3.在进行数据中台的表数据增量采集时，会出现以下几种问题：一，数据更新时，在源库只会在当前这条数据进行修改更新，并同时更新更新时间，但是数据采集按照更新时间采集后，hive库中会将这同一条数据保留两遍，一个是根据之前的新增时间或者更新时间在数据更新之前采集的，另一个是根据更新时间更新后采集的，出现了同一个主键下出现两条数据的情况；二，源数据库会在一定的时间段内出现删除数据的情况，正常抽取无法检测到删除数据这个操作；四，源数据库会将一定时间内删除的数据进行修改又重新加回源库，正常抽取也不会检测到这个操作，鉴于此，我们设计了一种基于hive的数据增量采集方法。技术实现要素：4.(一)解决的技术问题5.针对现有技术的不足，本发明提供了一种基于hive的数据增量采集方法，解决了上述
背景技术：
：提到的问题。6.(二)技术方案7.为实现以上目的，本发明通过以下技术方案予以实现：一种基于hive的数据增量采集方法，包括以下几个参数：8.${partdate}：代表昨天的日期，格式为年月日，需要根据采集工具自身具体对于hive分区采集时的设定参数进行填写；9.${ods_table_name}：采集至hive的ods表名；10.${only_id}：确定表唯一的主键的组合值11.${rec_create_time}：时间字段，创建时间；12.${rec_revise_time}：时间字段，更新时间；13.${check_days}：源库会删除数据的时间段，与第二步中采集源库的时间段保持一致，比如源库会删除30天内的数据，这里的值会设定为30；14.上述具体实施步骤如下：15.s1：初始化采集全量数据，适用于初始化采集或者需要重跑全量数据；16.s2：定时抽取源库在删除时间之内的数据至ods表；17.s3：对ods表进行sql脚本治理，且治理频率设置为每天，同时定时执行之间设定在第二步执行完毕之后；18.s4：定时永久存储30210100分区数据。19.可选的，在进行所述初始化采集全量数据时，第一个是普通任务，抽取全量，放入${partdate}分区；20.第二个是sql脚本任务，删除${partdate}之前所有分区,并将最新全量数据放入_f表，具体脚本如下：21.首先，抽取全量后删除之前的数据：22.altertable${ods_table_name}droppartition(pt《${partdate})；23.altertable${ods_table_name}droppartition(pt＝'30210100')；24.其次，重新抽数，删除表也要重新算数据：25.truncatetable${ods_table_name}_d；26.最后，将全量数据放入_f表：27.sethive.support.quoted.identifiers＝none；28.insertoverwritetable${ods_table_name}_f29.select*from${ods_table_name}。30.可选的，在抽取所述源库在删除时间之内的数据至ods表时，分区名为pt＝’30210100’，设定定时频率为每天抽取，定时时间根据业务需求时间确定,抽取源库表的sql可以根据源库表的时间字段来确定，假如只有一个时间字段，可以写为：31.select*fromtablewherecreation_date32.》＝to_char(sysdate-30,'yyyymmdd')33.andcreation_date《to_char(sysdate,'yyyymmdd')；34.可选的，所述源库表的时间字段为两个时，其脚本可以写为：35.select*fromtablewhere(creation_date36.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')37.andcreation_date38.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))39.or(last_update_date40.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')41.andlast_update_date42.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))。43.可选的，在对所述ods表进行sql脚本治理时，创建带有合并主键的temp表，简化对比主键的过程，且所述temp表的表结构和表内容与ods表相同：44.createtableifnotexists${ods_table_name}_tempasselect*,”45.asonly_idfrom${ods_table_name}limit0；46.insertoverwritetable${ods_table_name}_temp47.select*,concat(${only_id})asonly_idfrom${ods_table_name}。48.可选的，将所述temp表中最新分区，也就是pt＝30210100的数据,即最新30天数据，temp表中的30天数据左查询最新30天数据，找出pt＝30210100中为null的数据，即为源库被删除数据，将其插入ods_d删除表中在所述源库需要一个时间字段增量采集时：49.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；50.insertinto${ods_table_name}_d51.selecta.*from${ods_table_name}_tempa52.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b53.onconcat(a.only_id)＝concat(b.only_id)54.wherea.${rec_create_time}》＝regexp_replace(substr(date_sub55.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)56.and57.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and58.b.only_idisnull。59.可选的，在所述源库需要两个时间字段采集时：60.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；61.insertinto${ods_table_name}_d62.selecta.*from${ods_table_name}_tempa63.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b64.onconcat(a.only_id)＝concat(b.only_id)65.where(a.${rec_create_time}》＝regexp_replace(substr(date_sub66.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)67.and68.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and69.b.only_idisnull)70.or(a.${rec_revise_time}》＝regexp_replace(substr(date_sub71.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)72.and73.a.${rec_revise_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and74.b.only_idisnull)。75.可选的，创建与所述temp表相同表结构的tempf临时表，将去掉删除数据后的数据插入tempf表中：76.createtableifnotexists${ods_table_name}_tempfasselect*from${ods_table_name}_templimit0；77.insertoverwritetable${ods_table_name}_tempf78.selecta.*from${ods_table_name}_tempawherenotexists79.(select*from(selectdistinctonly_idfrom${ods_table_name}_d)bwherea.only_id＝b.only_id)。80.可选的，创建与所述ods表的表结构相同的_f表，对tempf表进行去重，取最新修改后的数据，去除联合主键，得到与源库一致的ods_f表：81.createtableifnotexists${ods_table_name}_fasselect*from${ods_table_name}limit0；82.sethive.support.quoted.identifiers＝none；83.insertoverwritetable${ods_table_name}_f84.select`(only_id)？ . `from85.(selectdistinctb.*86.from(selectonly_id,87.pt,88.row_number()89.over(partitionbyonly_idorderbyptdesc)asrow_num90.from${ods_table_name}_tempf)a91.leftjoin${ods_table_name}_tempfb92.ona.only_id＝b.only_id93.anda.pt＝b.pt94.wherea.row_num＝1)c。95.可选的，将当日所述第二步采集至30210100分区的数据存入ods表的前一天分区，永久储存数据，任务执行频率根据源库删除数据的天数设定，例如会删除30天之内的数据，则每30天执行一次，定时时间为在当日第二步采集完之后：96.sethive.support.quoted.identifiers＝none；97.insertoverwritetable${ods_table_name}partition(pt＝${partdate})98.select`(pt)？ . `from${ods_table_name}wherept＝'30210100'。99.(三)有益效果100.本发明提供了一种基于hive的数据增量采集方法。具备以下有益效果：101.(1)、该基于hive的数据增量采集方法在使用时，只需要配置相关的采集任务和sql脚本任务，将sql脚本放入可执行带参数sql脚本的sql执行工具中，只暴露出表名，创建时间，更新时间，主键，原表可能会删除数据的时间段n，用户只需填入这几个关键信息进行sql执行任务配置，即可统一解决以上问题，保证数据的一致性。102.(2)、该基于hive的数据增量采集方法在使用时，sql脚本处理流程复用性强，形式灵活，操作简单，将sql脚本放入执行工具，只需填入相关参数，即可进行各类治理，适用于hive库内所有类型的表及表内不同格式的时间字段的情况。103.(3)、该基于hive的数据增量采集方法在使用时，本脚本是按问题最多的情况下进行的处理流程，在只有其中几项问题时，可酌情只保留解决问题的相关sql脚本，使得本采集方法使用更为灵活，便捷。附图说明104.图1为本发明基于hive的数据增量采集方法流程示意图。具体实施方式105.下面将结合本发明实施例中的附图，对本发明实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本发明一部分实施例，而不是全部的实施例。基于本发明中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都属于本发明保护的范围。106.请参阅图1，本发明提供一种技术方案：基于hive的数据增量采集方法，在进行数据采集时包括以下几个参数，${partdate}：代表昨天的日期，格式为年月日(20210101)，需要根据采集工具自身具体对于hive分区采集时的设定参数进行填写。107.${ods_table_name}：采集至hive的ods表名。108.${only_id}：确定表唯一的主键的组合值。109.${rec_create_time}：时间字段，创建时间。110.${rec_revise_time}：时间字段，更新时间。111.${check_days}：源库会删除数据的时间段，与第二步中采集源库的时间段保持一致，比如源库会删除30天内的数据，这里的值会设定为30。112.上述具体实施步骤如下：113.s1：初始化采集全量数据，适用于初始化采集或者需要重跑全量数据，在进行所述初始化采集全量数据时，第一个是普通任务，抽取全量，放入${partdate}分区。114.第二个是sql脚本任务，删除${partdate}之前所有分区,并将最新全量数据放入_f表，具体脚本如下：115.首先，抽取全量后删除之前的数据：116.altertable${ods_table_name}droppartition(pt《${partdate})；117.altertable${ods_table_name}droppartition(pt＝'30210100')；118.其次，重新抽数，删除表也要重新算数据：119.truncatetable${ods_table_name}_d；120.最后，将全量数据放入_f表：121.sethive.support.quoted.identifiers＝none；122.insertoverwritetable${ods_table_name}_f123.select*from${ods_table_name}。124.s2：定时抽取源库在删除时间之内的数据至ods表，在抽取所述源库在删除时间之内的数据至ods表时，分区名为pt＝’30210100’，设定定时频率为每天抽取，定时时间根据业务需求时间确定,抽取源库表的sql可以根据源库表的时间字段来确定，假如只有一个时间字段，可以写为：125.select*fromtablewherecreation_date126.》＝to_char(sysdate-30,'yyyymmdd')127.andcreation_date《to_char(sysdate,'yyyymmdd')；128.所述源库表的时间字段为两个时，其脚本可以写为：129.select*fromtablewhere(creation_date130.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')131.andcreation_date132.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))133.or(last_update_date134.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')135.andlast_update_date136.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))。137.s3：对ods表进行sql脚本治理，且治理频率设置为每天，同时定时执行之间设定在第二步执行完毕之后，第一步，创建带有合并主键的temp表，简化对比主键的过程，且所述temp表的表结构和表内容与ods表相同：138.createtableifnotexists${ods_table_name}_tempasselect*,”asonly_idfrom${ods_table_name}limit0；139.insertoverwritetable${ods_table_name}_temp140.select*,concat(${only_id})asonly_idfrom${ods_table_name}；141.第二步，将所述temp表中最新分区，也就是pt＝30210100的数据,即最新30天数据，temp表中的30天数据左查询最新30天数据，找出pt＝30210100中为null的数据，即为源库被删除数据，将其插入ods_d删除表中，在所述源库需要一个时间字段增量采集时：142.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；143.insertinto${ods_table_name}_d144.selecta.*from${ods_table_name}_tempa145.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b146.onconcat(a.only_id)＝concat(b.only_id)147.wherea.${rec_create_time}》＝regexp_replace(substr(date_sub148.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)149.and150.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull；151.第三步，在所述源库需要两个时间字段采集时：152.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；153.insertinto${ods_table_name}_d154.selecta.*from${ods_table_name}_tempa155.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b156.onconcat(a.only_id)＝concat(b.only_id)157.where(a.${rec_create_time}》＝regexp_replace(substr(date_sub158.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)159.and160.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull)161.or(a.${rec_revise_time}》＝regexp_replace(substr(date_sub162.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)163.and164.a.${rec_revise_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull)；165.第四步，创建与所述temp表相同表结构的tempf临时表，将去掉删除数据后的数据插入tempf表中：166.createtableifnotexists${ods_table_name}_tempfasselect*from${ods_table_name}_templimit0；167.insertoverwritetable${ods_table_name}_tempf168.selecta.*from${ods_table_name}_tempawherenotexists169.(select*from(selectdistinctonly_idfrom${ods_table_name}_d)bwherea.only_id＝b.only_id)；170.第五步，创建与所述ods表的表结构相同的_f表，对tempf表进行去重，取最新修改后的数据，去除联合主键，得到与源库一致的ods_f表：171.createtableifnotexists${ods_table_name}_fasselect*from${ods_table_name}limit0；172.sethive.support.quoted.identifiers＝none；173.insertoverwritetable${ods_table_name}_f174.select`(only_id)？ . `from175.(selectdistinctb.*176.from(selectonly_id,177.pt,178.row_number()179.over(partitionbyonly_idorderbyptdesc)asrow_num180.from${ods_table_name}_tempf)a181.leftjoin${ods_table_name}_tempfb182.ona.only_id＝b.only_id183.anda.pt＝b.pt184.wherea.row_num＝1)c；185.s4：定时永久存储30210100分区数据，将当日所述第二步采集至30210100分区的数据存入ods表的前一天分区，永久储存数据，(因为30210100分区抽取的30天的数据每天会覆盖掉)，任务执行频率根据源库删除数据的天数设定，例如会删除30天之内的数据，则每30天执行一次，定时时间为在当日第二步采集完之后：186.sethive.support.quoted.identifiers＝none；187.insertoverwritetable${ods_table_name}partition(pt＝${partdate})188.select`(pt)？ . `from${ods_table_name}wherept＝'30210100'。189.工作原理：当基于hive的数据增量采集方法在使用时，首先，进行数据的初始化采集，将源库表全量数据(或根据需求采集部分数据)采集至带分区的hive表，这里我们定义的分区为string类型的字段，命名为pt,分区值为年月日格式，内容为昨天的日期，例如今天是20210102，全量数据采集至的分区为pt＝20210101中。此时的表我们称为ods表，存储初步从源库采集来的数据，其次，了解源库表可能会删除的时间段，比如了解到源库表有可能会在30天之内删除数据，我们就每天采集大于等于30天之前，小于今天的数据放入内容为一个较大的日期的分区，因为这个分区内要放最新的数据，去重排序时需要将pt倒序排列，把这部分数据排在前面作为最新数据取出，后面的步骤会有详细的sql语句,例如放入pt＝30210100中，每天覆盖更新此分区数据至ods表中，之后，对ods表，进行sql脚本的检测被删除数据、存储被删除数据至ods_d表中(表结构与ods表相同)、去除删除数据、去除重复数据的处理，最终得到和源库截止至前一天的数据一致的数据，每天覆盖更新此数据至ods_f表中，ods_f表既为最终治理好的表，最后，定时将单日采集的30天之内的数据，也就是pt＝30210100中的数据，放入内容为昨天日期的变量分区之中，永久地存入ods表中，使得ods表存在连续而完整的源库数据(为保证数据完整，采集时间可适当交叉，最后会有去重步骤)，将这部分数据每日进行第三步的治理，从而得到最终数据表，定时时间可根据源库删除数据的时间段确定，例如本例中可定时为每30天执行一次，使得源库数据更新后，hive目标库一条主键下数据对应出现新旧两条数据，同时，源数据库会在一定的时间段内出现删除数据的情况，正常抽取无法检测到删除这个操作；源数据库会将一定时间内删除的数据进行修改又重新加回源库，正常抽取也不会检测到这个操作，且这是一套可复用的sql脚本流程，可提取出参数，使用时只需要关注数据表的主键，时间字段，与具体业务表会删除数据的时间段(例如30天之内)这几个参数，不需关注sql逻辑，直接配置sql脚本任务即可，节约了人力物力和财力，统一了数据采集后初步清洗治理数据步骤，使寻常人都可以方便的使用此方法提高工作效率。190.尽管已经示出和描述了本发明的实施例，对于本领域的普通技术人员而言，可以理解在不脱离本发明的原理和精神的情况下可以对这些实施例进行多种变化、修改、替换和变型，本发明的范围由所附权利要求及其等同物限定。当前第1页12当前第1页12
技术领域：
：1.本发明涉及大数据采集与数据清洗治理
技术领域：
：，具体为一种基于hive的数据增量采集方法。
背景技术：
：：2.数据中台需要采集汇聚各个系统数据表至中台的hive数据库，在表数据量较小的情况下，为保证数据准确性和与源库的一致性，可以进行每天周期全量的，即全量采集覆盖更新，但是表为数据量较大的业务表的情况下，单表数据量在数亿甚至数十亿或单表数据存储量在数百g甚至数t时，就不适合每天采集全量，需要增量采集，理论上只需要按照数据表中的新增时间和更新时间，每日抽取前一日所新增或者更新的数据，这样可以减轻数据中台及源库的采集压力。3.在进行数据中台的表数据增量采集时，会出现以下几种问题：一，数据更新时，在源库只会在当前这条数据进行修改更新，并同时更新更新时间，但是数据采集按照更新时间采集后，hive库中会将这同一条数据保留两遍，一个是根据之前的新增时间或者更新时间在数据更新之前采集的，另一个是根据更新时间更新后采集的，出现了同一个主键下出现两条数据的情况；二，源数据库会在一定的时间段内出现删除数据的情况，正常抽取无法检测到删除数据这个操作；四，源数据库会将一定时间内删除的数据进行修改又重新加回源库，正常抽取也不会检测到这个操作，鉴于此，我们设计了一种基于hive的数据增量采集方法。技术实现要素：4.(一)解决的技术问题5.针对现有技术的不足，本发明提供了一种基于hive的数据增量采集方法，解决了上述
背景技术：
：提到的问题。6.(二)技术方案7.为实现以上目的，本发明通过以下技术方案予以实现：一种基于hive的数据增量采集方法，包括以下几个参数：8.${partdate}：代表昨天的日期，格式为年月日，需要根据采集工具自身具体对于hive分区采集时的设定参数进行填写；9.${ods_table_name}：采集至hive的ods表名；10.${only_id}：确定表唯一的主键的组合值11.${rec_create_time}：时间字段，创建时间；12.${rec_revise_time}：时间字段，更新时间；13.${check_days}：源库会删除数据的时间段，与第二步中采集源库的时间段保持一致，比如源库会删除30天内的数据，这里的值会设定为30；14.上述具体实施步骤如下：15.s1：初始化采集全量数据，适用于初始化采集或者需要重跑全量数据；16.s2：定时抽取源库在删除时间之内的数据至ods表；17.s3：对ods表进行sql脚本治理，且治理频率设置为每天，同时定时执行之间设定在第二步执行完毕之后；18.s4：定时永久存储30210100分区数据。19.可选的，在进行所述初始化采集全量数据时，第一个是普通任务，抽取全量，放入${partdate}分区；20.第二个是sql脚本任务，删除${partdate}之前所有分区,并将最新全量数据放入_f表，具体脚本如下：21.首先，抽取全量后删除之前的数据：22.altertable${ods_table_name}droppartition(pt《${partdate})；23.altertable${ods_table_name}droppartition(pt＝'30210100')；24.其次，重新抽数，删除表也要重新算数据：25.truncatetable${ods_table_name}_d；26.最后，将全量数据放入_f表：27.sethive.support.quoted.identifiers＝none；28.insertoverwritetable${ods_table_name}_f29.select*from${ods_table_name}。30.可选的，在抽取所述源库在删除时间之内的数据至ods表时，分区名为pt＝’30210100’，设定定时频率为每天抽取，定时时间根据业务需求时间确定,抽取源库表的sql可以根据源库表的时间字段来确定，假如只有一个时间字段，可以写为：31.select*fromtablewherecreation_date32.》＝to_char(sysdate-30,'yyyymmdd')33.andcreation_date《to_char(sysdate,'yyyymmdd')；34.可选的，所述源库表的时间字段为两个时，其脚本可以写为：35.select*fromtablewhere(creation_date36.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')37.andcreation_date38.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))39.or(last_update_date40.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')41.andlast_update_date42.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))。43.可选的，在对所述ods表进行sql脚本治理时，创建带有合并主键的temp表，简化对比主键的过程，且所述temp表的表结构和表内容与ods表相同：44.createtableifnotexists${ods_table_name}_tempasselect*,”45.asonly_idfrom${ods_table_name}limit0；46.insertoverwritetable${ods_table_name}_temp47.select*,concat(${only_id})asonly_idfrom${ods_table_name}。48.可选的，将所述temp表中最新分区，也就是pt＝30210100的数据,即最新30天数据，temp表中的30天数据左查询最新30天数据，找出pt＝30210100中为null的数据，即为源库被删除数据，将其插入ods_d删除表中在所述源库需要一个时间字段增量采集时：49.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；50.insertinto${ods_table_name}_d51.selecta.*from${ods_table_name}_tempa52.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b53.onconcat(a.only_id)＝concat(b.only_id)54.wherea.${rec_create_time}》＝regexp_replace(substr(date_sub55.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)56.and57.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and58.b.only_idisnull。59.可选的，在所述源库需要两个时间字段采集时：60.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；61.insertinto${ods_table_name}_d62.selecta.*from${ods_table_name}_tempa63.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b64.onconcat(a.only_id)＝concat(b.only_id)65.where(a.${rec_create_time}》＝regexp_replace(substr(date_sub66.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)67.and68.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and69.b.only_idisnull)70.or(a.${rec_revise_time}》＝regexp_replace(substr(date_sub71.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)72.and73.a.${rec_revise_time}《from_unixtime(unix_timestamp(),'yyyymmdd')and74.b.only_idisnull)。75.可选的，创建与所述temp表相同表结构的tempf临时表，将去掉删除数据后的数据插入tempf表中：76.createtableifnotexists${ods_table_name}_tempfasselect*from${ods_table_name}_templimit0；77.insertoverwritetable${ods_table_name}_tempf78.selecta.*from${ods_table_name}_tempawherenotexists79.(select*from(selectdistinctonly_idfrom${ods_table_name}_d)bwherea.only_id＝b.only_id)。80.可选的，创建与所述ods表的表结构相同的_f表，对tempf表进行去重，取最新修改后的数据，去除联合主键，得到与源库一致的ods_f表：81.createtableifnotexists${ods_table_name}_fasselect*from${ods_table_name}limit0；82.sethive.support.quoted.identifiers＝none；83.insertoverwritetable${ods_table_name}_f84.select`(only_id)？ . `from85.(selectdistinctb.*86.from(selectonly_id,87.pt,88.row_number()89.over(partitionbyonly_idorderbyptdesc)asrow_num90.from${ods_table_name}_tempf)a91.leftjoin${ods_table_name}_tempfb92.ona.only_id＝b.only_id93.anda.pt＝b.pt94.wherea.row_num＝1)c。95.可选的，将当日所述第二步采集至30210100分区的数据存入ods表的前一天分区，永久储存数据，任务执行频率根据源库删除数据的天数设定，例如会删除30天之内的数据，则每30天执行一次，定时时间为在当日第二步采集完之后：96.sethive.support.quoted.identifiers＝none；97.insertoverwritetable${ods_table_name}partition(pt＝${partdate})98.select`(pt)？ . `from${ods_table_name}wherept＝'30210100'。99.(三)有益效果100.本发明提供了一种基于hive的数据增量采集方法。具备以下有益效果：101.(1)、该基于hive的数据增量采集方法在使用时，只需要配置相关的采集任务和sql脚本任务，将sql脚本放入可执行带参数sql脚本的sql执行工具中，只暴露出表名，创建时间，更新时间，主键，原表可能会删除数据的时间段n，用户只需填入这几个关键信息进行sql执行任务配置，即可统一解决以上问题，保证数据的一致性。102.(2)、该基于hive的数据增量采集方法在使用时，sql脚本处理流程复用性强，形式灵活，操作简单，将sql脚本放入执行工具，只需填入相关参数，即可进行各类治理，适用于hive库内所有类型的表及表内不同格式的时间字段的情况。103.(3)、该基于hive的数据增量采集方法在使用时，本脚本是按问题最多的情况下进行的处理流程，在只有其中几项问题时，可酌情只保留解决问题的相关sql脚本，使得本采集方法使用更为灵活，便捷。附图说明104.图1为本发明基于hive的数据增量采集方法流程示意图。具体实施方式105.下面将结合本发明实施例中的附图，对本发明实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本发明一部分实施例，而不是全部的实施例。基于本发明中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都属于本发明保护的范围。106.请参阅图1，本发明提供一种技术方案：基于hive的数据增量采集方法，在进行数据采集时包括以下几个参数，${partdate}：代表昨天的日期，格式为年月日(20210101)，需要根据采集工具自身具体对于hive分区采集时的设定参数进行填写。107.${ods_table_name}：采集至hive的ods表名。108.${only_id}：确定表唯一的主键的组合值。109.${rec_create_time}：时间字段，创建时间。110.${rec_revise_time}：时间字段，更新时间。111.${check_days}：源库会删除数据的时间段，与第二步中采集源库的时间段保持一致，比如源库会删除30天内的数据，这里的值会设定为30。112.上述具体实施步骤如下：113.s1：初始化采集全量数据，适用于初始化采集或者需要重跑全量数据，在进行所述初始化采集全量数据时，第一个是普通任务，抽取全量，放入${partdate}分区。114.第二个是sql脚本任务，删除${partdate}之前所有分区,并将最新全量数据放入_f表，具体脚本如下：115.首先，抽取全量后删除之前的数据：116.altertable${ods_table_name}droppartition(pt《${partdate})；117.altertable${ods_table_name}droppartition(pt＝'30210100')；118.其次，重新抽数，删除表也要重新算数据：119.truncatetable${ods_table_name}_d；120.最后，将全量数据放入_f表：121.sethive.support.quoted.identifiers＝none；122.insertoverwritetable${ods_table_name}_f123.select*from${ods_table_name}。124.s2：定时抽取源库在删除时间之内的数据至ods表，在抽取所述源库在删除时间之内的数据至ods表时，分区名为pt＝’30210100’，设定定时频率为每天抽取，定时时间根据业务需求时间确定,抽取源库表的sql可以根据源库表的时间字段来确定，假如只有一个时间字段，可以写为：125.select*fromtablewherecreation_date126.》＝to_char(sysdate-30,'yyyymmdd')127.andcreation_date《to_char(sysdate,'yyyymmdd')；128.所述源库表的时间字段为两个时，其脚本可以写为：129.select*fromtablewhere(creation_date130.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')131.andcreation_date132.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))133.or(last_update_date134.》＝to_date(to_char(sysdate-30,'yyyy-mm-dd'),'yyyy-mm-dd')135.andlast_update_date136.《to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))。137.s3：对ods表进行sql脚本治理，且治理频率设置为每天，同时定时执行之间设定在第二步执行完毕之后，第一步，创建带有合并主键的temp表，简化对比主键的过程，且所述temp表的表结构和表内容与ods表相同：138.createtableifnotexists${ods_table_name}_tempasselect*,”asonly_idfrom${ods_table_name}limit0；139.insertoverwritetable${ods_table_name}_temp140.select*,concat(${only_id})asonly_idfrom${ods_table_name}；141.第二步，将所述temp表中最新分区，也就是pt＝30210100的数据,即最新30天数据，temp表中的30天数据左查询最新30天数据，找出pt＝30210100中为null的数据，即为源库被删除数据，将其插入ods_d删除表中，在所述源库需要一个时间字段增量采集时：142.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；143.insertinto${ods_table_name}_d144.selecta.*from${ods_table_name}_tempa145.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b146.onconcat(a.only_id)＝concat(b.only_id)147.wherea.${rec_create_time}》＝regexp_replace(substr(date_sub148.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)149.and150.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull；151.第三步，在所述源库需要两个时间字段采集时：152.createtableifnotexists${ods_table_name}_dasselect*from${ods_table_name}_templimit0；153.insertinto${ods_table_name}_d154.selecta.*from${ods_table_name}_tempa155.leftjoin(select*from${ods_table_name}_tempwherept＝'30210100')b156.onconcat(a.only_id)＝concat(b.only_id)157.where(a.${rec_create_time}》＝regexp_replace(substr(date_sub158.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)159.and160.a.${rec_create_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull)161.or(a.${rec_revise_time}》＝regexp_replace(substr(date_sub162.(from_unixtime(unix_timestamp()),${check_days}),1,10),'-',”)163.and164.a.${rec_revise_time}《from_unixtime(unix_timestamp(),'yyyymmdd')andb.only_idisnull)；165.第四步，创建与所述temp表相同表结构的tempf临时表，将去掉删除数据后的数据插入tempf表中：166.createtableifnotexists${ods_table_name}_tempfasselect*from${ods_table_name}_templimit0；167.insertoverwritetable${ods_table_name}_tempf168.selecta.*from${ods_table_name}_tempawherenotexists169.(select*from(selectdistinctonly_idfrom${ods_table_name}_d)bwherea.only_id＝b.only_id)；170.第五步，创建与所述ods表的表结构相同的_f表，对tempf表进行去重，取最新修改后的数据，去除联合主键，得到与源库一致的ods_f表：171.createtableifnotexists${ods_table_name}_fasselect*from${ods_table_name}limit0；172.sethive.support.quoted.identifiers＝none；173.insertoverwritetable${ods_table_name}_f174.select`(only_id)？ . `from175.(selectdistinctb.*176.from(selectonly_id,177.pt,178.row_number()179.over(partitionbyonly_idorderbyptdesc)asrow_num180.from${ods_table_name}_tempf)a181.leftjoin${ods_table_name}_tempfb182.ona.only_id＝b.only_id183.anda.pt＝b.pt184.wherea.row_num＝1)c；185.s4：定时永久存储30210100分区数据，将当日所述第二步采集至30210100分区的数据存入ods表的前一天分区，永久储存数据，(因为30210100分区抽取的30天的数据每天会覆盖掉)，任务执行频率根据源库删除数据的天数设定，例如会删除30天之内的数据，则每30天执行一次，定时时间为在当日第二步采集完之后：186.sethive.support.quoted.identifiers＝none；187.insertoverwritetable${ods_table_name}partition(pt＝${partdate})188.select`(pt)？ . `from${ods_table_name}wherept＝'30210100'。189.工作原理：当基于hive的数据增量采集方法在使用时，首先，进行数据的初始化采集，将源库表全量数据(或根据需求采集部分数据)采集至带分区的hive表，这里我们定义的分区为string类型的字段，命名为pt,分区值为年月日格式，内容为昨天的日期，例如今天是20210102，全量数据采集至的分区为pt＝20210101中。此时的表我们称为ods表，存储初步从源库采集来的数据，其次，了解源库表可能会删除的时间段，比如了解到源库表有可能会在30天之内删除数据，我们就每天采集大于等于30天之前，小于今天的数据放入内容为一个较大的日期的分区，因为这个分区内要放最新的数据，去重排序时需要将pt倒序排列，把这部分数据排在前面作为最新数据取出，后面的步骤会有详细的sql语句,例如放入pt＝30210100中，每天覆盖更新此分区数据至ods表中，之后，对ods表，进行sql脚本的检测被删除数据、存储被删除数据至ods_d表中(表结构与ods表相同)、去除删除数据、去除重复数据的处理，最终得到和源库截止至前一天的数据一致的数据，每天覆盖更新此数据至ods_f表中，ods_f表既为最终治理好的表，最后，定时将单日采集的30天之内的数据，也就是pt＝30210100中的数据，放入内容为昨天日期的变量分区之中，永久地存入ods表中，使得ods表存在连续而完整的源库数据(为保证数据完整，采集时间可适当交叉，最后会有去重步骤)，将这部分数据每日进行第三步的治理，从而得到最终数据表，定时时间可根据源库删除数据的时间段确定，例如本例中可定时为每30天执行一次，使得源库数据更新后，hive目标库一条主键下数据对应出现新旧两条数据，同时，源数据库会在一定的时间段内出现删除数据的情况，正常抽取无法检测到删除这个操作；源数据库会将一定时间内删除的数据进行修改又重新加回源库，正常抽取也不会检测到这个操作，且这是一套可复用的sql脚本流程，可提取出参数，使用时只需要关注数据表的主键，时间字段，与具体业务表会删除数据的时间段(例如30天之内)这几个参数，不需关注sql逻辑，直接配置sql脚本任务即可，节约了人力物力和财力，统一了数据采集后初步清洗治理数据步骤，使寻常人都可以方便的使用此方法提高工作效率。190.尽管已经示出和描述了本发明的实施例，对于本领域的普通技术人员而言，可以理解在不脱离本发明的原理和精神的情况下可以对这些实施例进行多种变化、修改、替换和变型，本发明的范围由所附权利要求及其等同物限定。当前第1页12当前第1页12
再多了解一些
本文用于企业家、创业者技术爱好者查询，结果仅供参考。
