00:00
好,那接下来我们看退款实时表啊,那它也是一个累积型快照时表啊,那实际上退款这个业务与支付的业务呢,很相似啊,只不过支付业务呢是用户付款给商家,而退款业务呢,是由商家付款给用户啊,那所以说这两张表啊,他从表结构上去看,那从数据装载的逻辑上去看,都是很相似的啊好,那现在我们就来看一下这张表的表结构啊,那首先我们先来明确一下啊,那这张表它的每行数据指来的是什么?那指代的是不是就是一次退款记录啊。没错吧,啊OK,那接下来我们再继续往下进行,接下来我们分析一下它的列都有哪些,也就是字段有哪些。那同样的是两部分啊,一部分是维度外建,一部分呢是度量值,那在咱现在呢,先来看一下它的维度外建啊,那这里边有谁有user ID,有SID,有province ID,没错吧?那除了这个用户商品地区,那还有啥?还有时间对吧?那时间字段呢,这同样也是有两个,一个是create time,一个是call back time time呢诶就是创建时间,Call back time呢,就是回调时间,在这儿呢,我们可以把回调时间当成什么呀?当成退款成功的时间对吧?啊OK,那这两个字段呢,都可以作为时间维度表的外建啊OK,那这就是它的维度外建啊,那接下来呢,我们再来看一下它的度量值啊,那度量值呢,在这主要就是谁呀?是就是这个refund amount呀,对吧,也就是退款金额啊OK,那这就是退款时表呢,哎,这个字段啊,也是它的列啊好,那接下来我们再来看一下它的分区规划,我们往下看啊。
01:41
好,那它的分区规划呢,与其他的累积型快照时表也是一样的啊,这里边儿有一个9999分区啊,还有一个呢,每日分区,没错吧,那9999分区当中存放的就是所有的未完成的退款记录啊,而每日的分区当中存放的就是当日完成的退款记录啊,OK,那这就是退款时值表的表结构。
02:06
那紧接着我们来看一下这张表的数据装载啊,那同样的我们还是先来看一下它的这个装载的时候的数据走向啊,来点开这个PPT。好,大家来看一下啊,那上边指的就是ODI层与退款相关的业务表,下边呢,指的就是退款实值表啊,来看我们第一天能拿到什么数据啊,第一天咱能拿到的是不是全量的退款记录啊,对吧?那同样那这个退款记录当中包括完成的,也包括未完成的,没错吧?那完成的需要放到完成的那一天所在的分区,没错吧?那未完成的需要放到999分区,那所以说第一天我们需要来进行一个动态分区啊OK,那接下来我们再看第二天我们要做的操作是什么。好,那第二天呢,我们从ods层只能拿到当天的新增及变化的退款记录,对吧?跟之前一样啊,这里边有新增有变化,那新增里边呢,有完成,也有可能有未完成的,对吧?那所以说可能有一部分数据需要写到九九游分区,那一部分数据呢,写需要写到这个完成的分区当中啊,那除了新增是不是还有变化呀,对吧?变化指的是什么呀?变化指的是原来产生的啊,或者叫之前产生的啊,这个退款记录啊,今天发生变化了,对吧?那今天发生变化的这些退款记录啊,它一定位于这个事实表的哪个分区啊。
03:24
是不是一定位于999分区啊,为啥呀?啊,因为咱们提到了啊,完成的记录是不是不会再发生变化了,对吧,那变化的记录肯定是未完成的啊,那所以说那咱们变化的这些记录呢,肯定都在999分区。啊,OK,那未完成的记录发生变化了之后,是不是可能就是完成的状态了呀,对吧?那所以说999分区的数据呢,可能有一部分呢,还要写到诶完成的分区当中,OK,那这就是我们每日装载的时候呢,这个数据走向啊,那后续每天诶都跟这个是一样的啊OK,那这就是退款市时表呢,数据装载的时候呢,它的这个走向啊那接下来我们就开始编写这张表的装载语句啊啊那现在打开吧,那这张表呢,同样分为首日装载和每日装载啊那现在我们先看首日啊,假定今天就是6月14号啊OK,那接下来咱们开始编写这个SQ语句啊,那首先我们先明确一下咱们首载的具体的逻辑啊OK,咱们需怎么呀,我们需要从OS对这个相关的当到四号这个分区当中的全量数据对吧,拿到之后呢,我们需要使用动态分区,将数据分别写入对应的分区当中啊OK,那现在咱们开始,那首先我们先从OD。
04:40
S层的payment,诶应该是refund啊,这个是退款了对吧?Refund payment这张表当中啊,它的十四二的分区里边啊,拿到我们所需的全量数据啊,OK,那在这呢,我们需要拿到所有的字段啊,现在这个字段呢,咱们需要处理一下啊,那在这儿呢,我已经提前处理好了啊再拿过来来CTRLC。啊,放在这个位置啊,CTRLV啊,那现在我们注意观察一下啊,这里边呢,有两个字段,咱们从这张表当中是拿不到的,对吧?一个是user ID,一个是province ID啊,那去哪张表拿呢?啊,没错,还是older这张表啊好,那现在我们再写一个查询select,不让ODI层的older对吧?个分区是不是也是2020杠零六杠幺四对吧,也是这个分区。好在这里边呢,我们需要拿三个字段啊,一个是ID,也就是order ID,用来做关联的对吧?那还有一个呢,是呃,User ID,那再有一个呢,就是这个province ID啊好,那上面这俩字段呢,咱就可以去掉了啊,给它删除UID也去掉好,那接下来我们需要将这两个紫查询来join到一起,对吧?啊,OK,那现在我们分别起一个别名啊来。
05:48
CTRLV。第一个子察觉呢,我给它命名为啊,Refund payment命名为RP对吧?那下边这个字子察觉呢,还是O啊,Order in。
06:01
好,OA,好,那它俩应该做的呢,是left对吧?RFTG,好,那下边我们写一个连接条件啊,RP应该是什么?Rp.o.ID等于呃,oi.id对吧?啊好,那接下来呢,我们继续往下进行,现在我们就可以从中选择咱所需的所有字段了,对吧?啊,我们去拿一下啊,CTRLC来拿过来。放在这哪位好了,那咱所有字段就已经拿到了,那同样这个ID呢,是有歧义的啊,在这儿呢,应该是谁呀?应该是refund payment这张表当中的对吧?好,那这样一来我们就拿到所有字段了,最后我们需要使用动态分区啊,将这个数据呢,写入到目标表对应的分区当中,对吧?应该是insertright table dwd refund payment后边写一个partition,要注意啊,Partition当中我们只写DT这个字段就够了,对吧?啊,因为是动态分区吧,那当然了,最后我们还得再多选择一个字段作为动态分区的字段的值,对吧?那这个值应该怎么写呢?其实和我们前面写的那个支付时表当中的那个,诶分区字段的值是一样的啊,待会咱们写是不是直接NVL就可以了,对吧?然后呢,对这个call back做一个格式化啊,把call back time放进来,后边来一个YYYY,然后杠MM-DD,那完之后呢,我们再把9999放在里边啊,9999杠九九杠九九啊OK,那这就是咱们动态分区这个字段的值啊好了,那。
07:25
到现在为止,我们就完成了首日装载的circle语句了啊好,那咱们紧接着再来看一下它的每日装载啊,来,往下翻。那假定我们现在的日期呢,就是2020年6月15号啊好,那先下来我们看一下每日装载的逻辑应该是什么样的啊,是不是跟前面所讲的累积型会计时表也是一样的呀,对吧?咱首先需要先拿到对应的事实表的原来9999分区的数据,对吧,再拿到啊ODI层对应业务表的今天的新增变化,那这两部分数据呢,说要做一个外联。
08:02
没错吧,那求爱链之后呢,我们需要拿到这些所有记录的最新状态啊,其实就是这一部分,然后再加上这部分,那怎么拿呢?是不是使用NVR就可以了,对吧?那拿到之后呢,我们需要将这两部分数据啊,给它写回到这个实时表当中,没错吧,但是大家要注意啊,那最新状态的这部分数据呢?哎,它是不是有可能是完成的状态,也有可能是未完成的状态啊,对吧?那所以说往回写的时候呢,这数据呢,可能要写到两个分区当中啊,那所以在这儿呢,我们也需要使用动态分区啊,就是这样的一个呃,装载思路啊OK,那接下来我们再把这个思路呢,再给它实现一下啊好,首先我们先获取咱需要的两部分数据啊,那一部分呢,应该是来自于DW refund payment对吧?哪个分区应该是9999分区对吧?9999,然后杠九九杠九九啊好,那现在我们需要拿到这张表当中的所有字段啊,CTRLC我们获取一下。来往前对一下,好,第一问这个数据呢,咱们就已经拿到了,那这部分数据呢,实际上就是我们那个所谓的O的子查询,对吧?来CTRLV,那咱们现在先给它起个名old。
09:09
好,那O的拿到之后呢,我们再去获取那个new对吧?啊,New怎么办呢?New是不是应该是来自于ODI层的refund pay的表啊,对吧?那VRDT等于注意哪个分区,2020杠零六杠幺五对吧?啊好,那在这呢,我们也要拿到咱所需的所有字段啊,CTRLC,咱去拿一下CTRLV,好注意观察一下,那这里边仍然有两个字段是拿不到的对吧,一个user ID,一个pro ID,那这个是不是又得去从older info表里拿了呀,对吧?Select from ods层的啊o in four OK,那现在我们过滤一下这个分区啊,2020杠零六杠幺五没错吧?在这里边呢,我们需要拿到ad,需要拿到user ID,需要拿到province ID啊,没错吧,那他们俩呢,需要做一个关联,然后呢,组成咱那个所谓的的子查询啊,那现在我们把上面的两个字段清理一下。
10:01
一个去掉,那这个呢,咱也给它去掉啊,好了,那现在我们把这俩子查询,先给它照一下,来回说这个我给它命名为呃,RP refund payment,下面呢,命名为oio。来给它往前推一下,好,那这个呢,叫做OA啊好,我们先把它俩join唤起来啊,它俩做一个什么召唤,是不是做一个呃,Left join就可以啊go啊,An啊好,那它俩的关联条件呢,应该是Rp.o的ID等于呃oi.id对吧?啊好,那接下来我们继续,那现在我们从这两个子查询当中选择咱所需的这些所有的字段啊,CTRLC拿过来。拿来之后呢,放在这儿啊,CTRLV,好,我们往前对一下,那同理这个ID呢,是有歧义的,我们应该是阳是RP对吧?啊好了,那这个其实就是咱们所谓的new对吧,那咱把它放到一个括号当中。
11:01
来CTRLV,好,这个最后的分号就不要了啊,给它去掉来往前推一下。OK,那这就是那个所谓的啊,那现在我们需要怎么呢?将这两部分的数做一个对吧?好,那完事之后呢,写一下连接条件应该是on old的点ID等于new.id对吧?好,那现在我们再从这里边选择咱所需的数据啊,我们需要拿到所有记录的最新状态对吧?啊,咱s slide from,那怎么拿呢?是不是又是一大堆的NVL啊对吧?在这我们就不再手写了,我们去拿一下文档当中的结果啊,OK,好,那这就是咱们寻找的NVL啊,CTRLC。来,放到笔记当中。好,大家看一下啊,那这样一来我们就能拿到这些记录的最新状态了啊,好,都拿到之后,我们就可以把数据往回写了,对吧?咱们在insert overri table dwd refund payment后边是不是也是partition,对吧?咱们这也要动态分区对吧?所以说只写一个DT就够了,那这时候呢,我们后边这儿是不是还得再写上一个动态分区的值啊,对吧?
12:07
那这个值实际上跟我们首入装载的逻辑是相同的,哎,咱们把这个拿过来啊,拿来之后呢,其实只只需要改一个地方就行了,对吧,改哪啊,是不是里边这个call backtime,我们应该用NVL返回的结果呀,对吧,咱们应该用最新的call backtime作为它动态分区的值啊,OK,行,那到现在为止,那我们这个每日装载的circle也就写完了。
我来说两句