set odps.sql.allow.fullscan=true;
insert overwrite table
select ,
,
from(
select row_number() over(partition by t.
order by record_id desc, after_flag desc) as row_number, record_id, operation_flag, after_flag, , ,
from(
select incr.record_id, incr.operation_flag, incr.after_flag, incr., incr.,incr.
from incr
where utc_timestamp<
union all
select 0 as record_id, 'I' as operation_flag, 'Y' as after_flag, base., base.,base.
from base) t) gt
where record_num=1
and after_flag='Y'
说明
:存储全量merge结果集的表名。
//:同步表中的列名。
:同步表中的主键列名。
:增量日志表名。
:全量基线表名。
:需要获取全量数据的时间点。
合并数据表,获取customer表在1565944878时间点的全量数据,示例如下:set odps.sql.allow.fullscan=true;
insert overwrite table customer_1565944878
select id,
register_time,
address
from(
select row_number() over(partition by t.id
order by record_id desc, after_flag desc) as row_number, record_id, operation_flag, after_flag, id, register_time, address
from(
select incr.record_id, incr.operation_flag, incr.after_flag, incr.id, incr.register_time, incr.address
from customer_log incr
where utc_timestamp< 1565944878
union all
select 0 as record_id, 'I' as operation_flag, 'Y' as after_flag, base.id, base.register_time, base.address
from customer_base base) t) gt
where gt.row_number= 1
and gt.after_flag= 'Y';