00:00
好,那接下来我们看优惠券领用时表的数据装载啊,那这张表的装载逻辑啊,较前面的几张时表要难一些啊,好,那现在我们先来看一下它的数据走向啊,点开这个PPT。好,我们来看一下啊,那上边指的就是ods层与优惠券领用相关的业务表对吧?那当然它就是优惠券领用表的数据来源啊,那下边呢,指来的就是优惠券领用实时表啊好,那现在假定今天就是6月14号对吧?那看一下我们从ODI层能获取什么样的数据。OK,咱是不是能拿到全量的优惠券领用记录啊,对吧,那拿到全量的数据之后,咱们得想一下,这个数据咱需要放到这个优惠券领用市值表的哪个分区当中。啊,放到哪个分区是不是还是得由下边这张实时表的分区规划决定啊,对吧,咱这张表是怎么规划的分区啊。啊,在这儿呢,我们会有一个9999分区,对吧?那还会有一个每日的分区对不对?那9999分区当中存放的是所有的未完成的领用记录,而其余的分析当中呢,存储的就是当天完成的领用记录啊,没错吧,那所以说我们第一天拿到的这个全量数据啊,它里边你说是不是会包含未完成的记录和完成的记录啊,对吧?而且完成咱也不知道它到底是在哪天完成的,那所以说我们第一天进行数据装载的时候呢,那是不是可能会往多个分区里边写数据啊,对吧?那是在这儿呢,咱需要用到动态分区啊,咱需要把数据诶写到九九分区,或者是这个每日的分区当中都有可能,对吧?
01:36
啊,都有可能,OK,这就是我们首日装载的逻辑啊,OK,那第一天过去之后,我们来看第二天我们的装载逻辑是什么样的啊好,那随着时间的推移,我们来到第二天,那注意观察第二天咱们从OD层拿到的数据是什么?是当日的新增及变化的优惠券领用记录啊注意啊,是新及变化啊,因为什么?因为优惠券领用诶市值表它所对应的那个业务表啊,啊采用的同步策略呢,是每日新增及变化,那所以从第二天开始,我们就只能拿到新增级变化的数据了。
02:10
OK,那接下来我们分析一下啊,咱拿到这个新增及变化的优惠券领域记录之后,咱应该怎么做?首先咱们分析一下啊,这个新增变化的数据到底都是什么啊?首先我们先看新增,大家说新增的记录指的是什么?指的是6月15号那产生的这个领用记录对不对?那你说15号产生的领用记录当中有没有可能包含完成的记录?什么意思呢?就是今天领今天用,对吧,有可能对吧?那所以说是不是这里边儿的数据有一部分可能要写到那个完成的分区当中,没错吧,那有没有可能包含未完成的记录呢?也就是今天领,但是没用,有没有可能,有可能对吧?那有可能的话,那咱是不是就可能会把一部分数据写到咱这个9999分区啊。没错吧,那再有,那刚才咱们只分析了新增,那今天我们是不是还有变化的数据啊,对吧,变化数据指的什么样的数据,是不是原来产生的这个领用记录今天发生了变化呀,对吧。
03:07
没错吧,那今天发生变化,那意味着什么?意味着是不是有可能原来的未完成的记录在今天完成了呀,对吧?那所以说呢,是不是9999分区当中是有一部分数据可能会怎么样啊,可能会完成,完成之后是不是得从里边拿出来,放到完成的那一天的分区当中啊。没错吧,那所以说我们每日装载的时候,它的数据走向应该是这样的啊,啊就是新增级变化,那有一部分可能需要写到,诶咱的996分区,那有一部分呢,可能要写到咱这个完成的分区,那当然呢,九九分区当中可能有一部分数据在今天完成了,对不对,那今天完成啊,就要写到今天的分区当中。OK,那这就是我们每日装载的这个逻辑啊,OK,那后续每天我们的装载逻辑都是跟这个一样的,OK,那这就是优惠券领用时表装载时的数据走向。
04:00
那接下来我们就开始编写它的首日装载和每日装载的SQ语句啊好,那现在咱打开date RI啊好,我们先看首日,那假定今天就是6月14号对吧?那接下来我们先明确一点啊,咱们要往这张表当中装载数据啊,咱们的主要的数据来源是哪张表?那是不是应该是ods层的库on柚子对吧?那所以咱select,然后from ods库on,呃,柚子啊好,那完之后呢,需要过滤一下这个分区啊,DT应该是等于2020杠零六杠幺四啊没错吧?好,那现在我们先尝试着获取一下咱所需的所有字段啊,那是不是就这些啊对吧?来,咱CTRLC诶全部拿过来,好,那拿过来之后呢,我们去处理一下啊。在这我其实已经提前处理好了,那咱就把所有的字段呢,都拿过来,CTRLC来放到select语句当中啊,Ctrl v OK,大家注意观察啊,咱这所有的字段是不是都能够获取到啊,能获取到,那在这儿呢,我们就不需要再去照其他的表了啊,也就是我们现在已经拿到了第一天的数据了啊,那接下来咱需要怎么做呢?是不是需要使用动态分区,把数据写到咱这个DWD层的哎,On use这张表啊,对吧?那接下来咱们来一个insert allright table,应该是dwdon use,那后边呢,需要写一个part提的,注意咱这应该是啥,是动态分区对吧?所以只选一个DT就可以了。好,那动态分区我们还有一个地方要注意,就是最后咱是不是得多选择一个字段对吧?用来作为它呃,分区字段的值啊,没错吧,那关键是这个分区字段的值应该怎么写?
05:33
那我们来分析一下啊,那咱这儿获取到的全量数据当中,是不是可能会包含完成的记录,也可能会包含未完成的记录啊,对吧?那我们的装载逻辑应该是完成的记录就写到他完成的那一天的分区当中,未完成的记录写到9999分区当中啊,没错吧?啊OK,那对于优惠券领用这个业务来说,什么叫做完成?啊,用券支付或者是过期是不是算作完成啊,对吧,那所以说那咱的完成时间是不可能是use time,也可能是spare time啊。
06:07
没错吧,那所以最终咱们就能定下来啊,咱这的这个逻辑应该是啥样的?应该是这样的,我们得先判断use time是否为闹,如果它不为闹,那我们就把数据写到use对应日期的分区当中啊,如果它为呢,我们再继续往下判断对吧?判断啥?判断re time是否为闹,如果他不为,那我们就把数据放到re对应日期的分区当中,如果他为,那咱们就把数据写在哪啊,是不是写到9999分啊。没错吧,啊,是这样的啊,这是咱的判断逻辑,当然根据这个逻辑呢,我们可以用什么呀?可以用kiss去做对吧?或者在呢,我们也可以使用这个函数叫做Co啊,Co函数啊,那这个函数呢,相当于是一个增强版的NV,可以这样去理解,大家都知道啊,NV是不是能够接收两个数对吧?NVL的判断逻辑是什么?先判断第一个参数是否为闹,如果不为闹,那就返回第一个参数,如果为闹,再去判断第二一个参数,对吧?那NVL呢?那它其实可以传N个参数的,然后呢,从第一个开始去判断是否为回第一个值啊,这是Co的一个使用逻辑,那我在这儿呢,我们其实就可以用它啊,那具体理论怎么写呀?说白这样写,那我们得用data for m啊,Data for m谁。
07:24
是不是首先先把you time放进来use的time啊,当然了,我们需要给它格式化成年月日的格式,年,然后月日好,那我们先判断它是否为闹对吧啊,如果它不为now,那就返回它,它为now,再继续往下判断,那第二一个是不是咱要写的就是这个exp time啊对吧?诶,CTRLC咱拿过来,诶放在这个位置啊好,那再往这里边咱需要改一下啊,别忘了这里边应该改成time对吧?那再往后呢,是不是就得是999999了呀,对吧,咱把这个999放里边,9999杠九九杠九九。啊,那现在呢,我们这个动态分区的字段的值咱就写好了啊,那它的逻辑呢,就是跟我们刚才所说的是一样的啊,先看它是否为闹啊,不为闹就返回为闹来判断它啊,它如果不为闹就返回为闹的话,那就是9999了,OK,那这就是我们首日装载的搜狗语句。
08:17
好,那接下来我们再看它的每日装载啊,那假定现在日期呢,来到了2020年6月15号啊,对吧?那接下来我们分析一下它每日装载的逻辑啊,这个每日装载呢,要相对复杂一点啊好,我们打开文档啊,咱们先来回顾一下啊,每日装载的时候,它的数据走向是什么样的啊来点开这个PPT。OK,我们来看一下,这首日已经过去了啊,我们看每日。好,拿来看一下,那每日装载的时候呢,我们会拿到新增及变化的优惠券领用记录,对吧?这里边儿包含新增包含变化,没错吧,那我们前面分析过啊,这个新增的里边是不是可能有完成的,也有可能有未完成的呀,对吧?啊,当然完成的呢,我就需要写到完成的分区,未完成的呢,就需要写到九九也有分区。
09:03
没错吧,那这里边儿是不是还有变化的数据啊,对吧,那有变化的数据,那说明什么呀,是不是说明原来产生的零零记录今天发生的变化呀,对吧。没错吧,那当然发生变化的肯定是什么,肯定是前边的未完成的记录,因为完成的就永远都不会再变了,对吧?也就是说原来的9999分区的数据今天有可能会发生变化。没错吧,那你说它变化完之后,是不是就有能会完成,那完成之后,那它的数据是不是也有可能要写到,诶这个完成的分区当中啊是这样的,所以说咱们每天的数据的走向呢,是这样的。啊,OK,那接下来咱们提炼一下啊,咱们把这个每日装载的时候这个数据的呃,数据源和目的地给它提炼出来,好,那我们每日装载的时候数据源有啥呀?其实咱这看箭头就可以,那数据源首先有谁,是不是有这个原来的9999分区,你看是不是从这里边,诶需要往这儿写呀,对吧?OK,那这是一个数据啊,那还有谁,是不是还有ods层的新增变化数据,对吧?这是咱的两个数据啊,一个是原来的9999分区的数据,一个是ods层的新增变化,那OK,最终的目的地有谁呢?
10:10
最终的目的地其实也有两个,一个就是完成的这个分区,一个呢就是啥,就是9999分区。啊,OK,这就是我们的数据源和目的地,好,那这个咱们分析完之后呢,接下来我们再去看一下它这个具体的装载逻辑是什么样的啊好,我们往下翻啊,文档当中还有一个PPT,咱给它点开啊,就是这个啊,咱一起看一下啊。好,我们看一下每日装载的具体的思路是什么样的,那首先呢,我们需要先拿到这两部分数据,大家来看一下啊,这是不是就是我们每日装载的时候它的数据源呀,对吧?一个是优惠券领用时时表原来的9999分区的数据,那这里边存储了什么呀?存储了截至前一日的所有的未完成记录,没错吧?那还有一部分呢,是ods层啊,这个业务表的当日的分区,那里边存储的是当日的新增及变化的领用记录。
11:04
OK,大家来看一下啊,这两部分记录的对应关系是什么样的?来,我们一起来看一下啊,这里边呢,有一部分记录是不是只出现在左边这个数据当中,没错吧,那还有一部分呢,只出现在右边。没错吧,那还有一部分呢,是不是同时出现在左右两边啊啊,那这几部分数据,那到底是什么含义呢?我们来解读一下啊,我们先看这部分。这部分怎么解读?这一部分啊,它是不是只出现在了原来的9999分区,没有出现在今天的新增级变化分区,对吧?那说明啥?说明它是一个原来的未完成的记录,然后今天呢,也没有发生变化,那也就是说到今天为止,它仍然是未完成的状态,对吧?好,那接下来往下看,诶我们看一下这一部分数据,这部分数据你看啊,它是不是同时出现在左右两边了,对吧?那这说明什么呀?是不是说明原来的未完成的记录在今天发生了变化呀,对吧。OK,那我们继续往下进行,那再看第三部分,第三部分只出现在了右边,没有出现在左边,那说明啥?说明这两条数据呢,是今天的新增的领用记录。
12:11
没错吧,OK,好,那我们拿到这两部分数据之后,我们需要做什么操作呢?诶,咱们需要做一个副alter join全外联的操作啊,OK,大家都知道,全外连之后呢,咱这儿是不是会形成一个虚表啊,对吧?那虚表这一部分呢,会补上now,那这一部分是不是也会补上now啊,没错吧,那接下来我们要坐在这儿呢,就是从这个虚表当中去获取咱们所需的诶全部的数据,OK,那接下来看咱们需要获取哪部分数据啊,我们需要获取的是诶这两部分数据。啊,那这两部分数据是什么啊,大家来看一下。这里边儿呢,是所有的记录的最新状态。啊,最近大家来看一下咱们这个最新的状态是哪两部分,具体看一下,OK,看一下这。首先这部分,这部分什么是只出现在左边,没有出现在右边的,对吧?这就是什么?说明他原来未完成,今天没变,那所以说上一个最新状态是不是就他现在的最新状态,那直接拿出来就行了,OK,那接下来看这一部分,那这一部分呢,我们包含两部分啊,那一部分呢,是同时出现在左右两边的,这说明啥?说明原来的未完成的记录今天发生变化了,那所以说这个是不是他截止到今天的最新状态啊,对吧?那还有一部分呢,是今天的,诶新增的记录,那当然就是它的最新状态了,OK,那我们需要把这些数据拿到。
13:27
OK,那当然大家想一想拿到之后啊,那这一部分数据当中是不是仍然有可能包含完成的记录,也有可能包含未完成的记录啊。没错吧,大家来看一下啊,那你像这个这个还有这个啊,还有这个,这是不是都是完整的记录,那剩余的你像他们俩,还有他这是不是都是未完整的记录啊。没错吧,那所以说我们最终这一部分数据呢,需要怎么办呀,是不是需要写到两个分区里边对吧?完成的写到完成的分区当中,未完成的呢,写到9999分区当中啊,那所以说最终咱需要做一个什么操作,是不是一个动态分区的操作,然后把两部分的数据呢,分别写到两个分区当中,OK,那这就是优惠券领用时时表每日装载的具体的思路。
14:13
啊,OK,那这个思路讲完了之后,我们就来一起写一下这个circle啊好,咱接下来打开data GR啊好,那现在我们假定今天的日期就是6月15号了,那首先我们需要先获取到啊,咱所需的两部分数据,那先看第一部分select from应该是哪张表,DWD部分use对吧?那完之后呢,VRDT等于9999杠九九杠九九。没问题对吧,那我们所需的字段呢,就是它里边的所有字段CTRLC,咱们拿过来来,我放在这儿啊。CTR瑞,这个没有问题,对吧?啊,咱们先拿到呃,原来的999分区的数据,好,那接下来我们再去获取今天的新增及变化,是不应该是s select from ods层的库柚子啊,对吧?那当然DT也是这个2020杠零六杠幺五啊,没错吧,那我们需要什么字段呢?仍然是这些,哎,字段好再拿过来,拿了之后呢,我们需要对这两部分数据做一个全外联的操作,对吧?那接下来呢,我们就给它起一个别名来,那同样这个上面的子查询我们起名为old啊。
15:15
来走,那这个叫做old,那下边这个子查询呢,我们就给它命名为new。来多一个零啊,给它删掉,来CTRLV,好,那现在呢,我们给它起一个别名叫做new。那这两部分数据呢,需要进行全外联al。OK,那他们的关联条件应该是啥呀?应该是o.ID等于点ID。啊,OK,那接下来我们就可以从这个装完之后的需表当中去选择咱们所需的字段了,Select from OK,那咱们怎么能拿到我们所需的数据呢?啊来我们去看一下啊来快把这个图点开。啊,那按照我们刚才的分析啊,呃,咱这儿是怎么做呀,需要让这两部分数据做全外联,我们已经全外联了,对吧?那大家都知道全外联之后呢,会形成一个虚表,你像这些位置呢,是不是会补上闹啊,对吧?那咱们怎么能拿到所有记录的最新状态啊,那其实这个逻辑好像跟我们之前讲的那个拉链表的逻辑有点像,对吧?在这咱怎么获取啊?哎,我们的判断逻辑应该是这样的啊,那如果说右边这个子查询,也就是new里边有值,那我们就取new里边的值。
16:25
没头板,因为它是最新的嘛,呃,如果new里边没有值,没有值就说明它是闹对吧,如果为闹那就怎么办呢?诶那我们就取左边这个子查询,也就是原来的值,好,那这样一来我们就能拿到这个全量的最新的状态了,那其实具体的操作就是啥呀,是不是就可以用NVL或者用if去做呀,对吧,在这呢,我们就用NVL来,我们打开这个data gra来,我们先看第一个字段啊,那直接怎么写,是不是直接n v l new.id啊,然后old点。ID呀,对吧?好,那这样一来我们就能拿到一个,哎,最新的ID,那啥意思呢?其实很简单啊,就是说判断一下new是否为呢,为呢,那我们就拿old,不为呢,我们的拿new,对吧?其实很简单,好,那剩余的所有字段呢,在这儿我们都是这样的一个操作,OK,那现在我就不再一个一个去写了,我们拿下文档当中的结果,来,咱们去拿一下。
17:15
哎,主要到这个位置CTRLC,那接下来我给它放在这儿。啊,那现在我们就已经拿到了所有的记录的这个最新状态了啊,那接下来大家要干啥这件事儿就比较简单了,是不是给它呃,写到我们这个对应的分区当中就行了,然后大家都知道啊,那我们得到的这个最新的状态呢?实际上它是什么呀?啊,它包含两个分区的数据对吧?啊,因为有可能有完成的,有可能有未完成的,没错吧,那我们需要把它写到99U分区,或者是这个完成的分区当中,所以在这儿呢,咱们得怎么得用动态分区对吧?那所以insert来overri加一个table dwd柚子后边呢,我们需要写一个partition,里边呢,只写一个DT就行了,对吧?那至于我最后边的这个分区字段应该怎么写啊,那应该怎么写,那它的判断逻辑是不是跟我们首日装载的逻辑是相同的呀,对吧,咱们首日装载逻辑是什么?先判断诶使用支付时间是否为空啊,对吧,如果不为空,那就放到这个支付时间来对应日期的分区当中啊,否则的话呢,我们再去判断exp time。
18:18
没错吧啊,如果exp time不为空,那就把它写到exp time对应日期的分区当中啊,那否则的话呢,就是996分区了,OK啊,只不过这次我们再这个time是NOK,最终呢,诶,我们把这个最终的这个结果拿过来咱们看一下。CTRLZ。放到这个位置。啊,那这就是咱们动分的这个值啊,逻辑跟前的逻辑是一样的,只不过里边的呢,是我们NV之后的这个最新的结果,OK,那就是我们这装载circle。
我来说两句