00:00
好,那接下来我们看每日装载,呃,每日装载的逻辑呢,要相对复杂一点啊,那我们先回顾一下,呃,每日装载的数据走向啊。好,这个是首日装载,我们已经做完了啊,那接下来看下边那每日装载的时候啊,我们从ods层拿到的数据是什么呢?诶,是新增及变化的用户数据啊,从第二年开始,我们用户表采用的同步策略就是新增及变化了,对吧?那所以说我们这儿只能拿到新增及变化的数据啊,OK,那新增及变化的用户数据是不是都是最新状态,没错吧,最新状态我们就需要写到哎,用户维度表,也就是拉链表的9999分区当中。啊,没错吧,啊,那有变化的状态就意味着有些用户的状态已经过时了,对吧,那过时了之后,我们需要把它从9999分区拿出来,然后呢,写入到过期的这个分区当中。OK,那这就是我们每日的数据走向啊好,那现在我们把这个数据走向提炼一下啊,总结一下,那总结什么呢?诶无非就是数据从哪儿来,那最终呢,到哪儿去了,对吧?那我们去进行每日装载的时候,我们数据都从哪儿来呀?
01:15
一个呢,是来自ods层的新增及变化数据,没错吧?啊,那还有一个呢,是来自于呃,用户维度表,也就是拉链表的9999分区啊,这是我们的两个数据来源啊,那数数据最终他去往什么地方了呢?来大家看一下啊,那一部分数据最终又写回了9999分区。对吧,那另一部分数据呢,诶写入到了过期的分区,那这就是我们用户维度表每日装载的数据走向。好,那接下来我们再去看一下具体的装载逻辑啊,来往下。找到这个PPT,那这个PPT呢,它描绘了用户维度表每日装载的具体思路啊,那现在我们一起看一下啊,首先我们需要先拿到呃每日装载所需的两部分数据啊,刚才我们分析过了啊,那一部分呢,就是ods层的当日分区,它里边存储了呃当日的新增及变化的用户信息。
02:15
啊,那还有一部分呢,就是拉链表的9999分区啊,那它里边存储了截至前一天的全量最新的用户信息啊,OK,那现在我们一起来观察一下这两部分数据啊。那这两部分数据当中呢,有一部分用户信息只出现在了左边对吧,那有一部分数据呢,只出现在了右边啊,那还有一部分用户信息呢,同时出现在了左边和右边。啊,那这三部分数据我们应该怎么去理解呢?啊,其实很简单啊,我们先看同时出现在左右两边的用户信息,那这表明什么呀?这是不是表明原来的用户今天的状态发生了变化?没错吧?那只出现在左边的用户表明什么呢?表明原来的用户今天的状态没有发生变化,对吧?那只出现在右边的用户又代表了什么?
03:09
那就表示着这两个用户,他是今天的新增用户。OK,那这个我们首先需要搞清楚好,那完之后呢,我们继续往下进行啊,那大家思考一下啊,就是这些数据啊,我们是不是最终都会需要啊,对吧?那所以说在这儿我们要将这两部分的数据呢,做一个全外联。啊好,那全外联之后呢,首先我们需要先获取到啊,我们截止到当天的全量的最新数据啊,那哪部分数据是截止到当天的全量最新呢?啊大家来看一下啊,是不是就是这两部分呀,对吧?那首先来看一下这部分。这部分数据刚才咱们分析过啊,这是原来的用户今天没有发生变化对吧。对吧,今天没变,那所以说原来的最新状态是不是仍然是最新状态啊,所以这部分我们需要拿到啊,那再来看那这部分呢,那这部分是不是,诶咱们最新的一个状态啊,对吧?那所以说这部分我们也需要拿到,OK,那这两部分数据呢,就组成了啊,我们截止到当天的全量最新数据啊,那这部分数据呢?啊,肯定就需要覆盖之前的那个9999分区,没错吧?啊好,那现在已经拿到了我们最终结果当中的一部分了啊好,那我们再继续往下进行,我们还有一部分数据需要拿哪部分呢?就是过期的数据啊,过期的数据是哪部分呀?
04:30
是不是就这啊,对吧?啊,当然了,我们需要对过期的数据进行一定的处理啊,主要就是把它的结束日期改成前一天的日期,对吧?啊,OK,那这就是我们拿到的过期的数据啊,那过期的数据需要写到哪儿呢?需要写到拉链表过期的分区当中啊,没错吧,那也就是说我们最终得到的结果呢?呃,需要往两个分区里边去写,一个是写到9999分区,一个呢是写入过期分区。啊,那我们要想同时把数据写到多个分区,那这我们就需要用到动态分区,没没错吧,那所以说我们首先需要先将这两部分的结果啊进行union啊之后呢,再使用动态分区,把数据分别写到9999分区和过期的分区。
05:16
那OK,那这就是用户维度表每日装载的具体思路,好,那接下来我们就按照这个思路去编写一下用户维度表的每日装载的circle啊好,现在我们打开data grape啊,现在我们先假定一下啊,假定当天的日期就是2020年6月15号啊OK,那接下来我们开始编译circle,那首先我们先获取咱所需的两部分数据,一部分是蒂m user info诶,9999分区的数据,那一个呢是ods user INF啊,2020杠零六杠幺五分区的数据,对吧?啊,那现在我们逐个的获取一下select from user in four dt啊,等于9999啊,杠九九杠九九啊,那在接下来我们把它所有的字段全部选择出来啊,这个字段呢,需要处理一下啊。
06:07
好,我们给它竖过来。呃,最后一个字段是分区字段啊,这个DT咱不需要把它删除就可以了啊,那现在CTRLC把它粘过来,来放到我们的呃,这个参课当中啊,行,现在我们就已经拿到了咱所需的第一部分数据了啊,那紧接着我们获取第二部分数据啊,Select from,这回应该是ods user info对吧?然后VRDT等于多少?2020杠零六杠幺五啊,没错吧,那咱也把所有字段都选择出来,那选完之后呢,这里边有一些字段我们需要进行相应的处理啊,那首先我们先处理这三个字段。For number email这些是不是都属于啊这个敏感信息啊,对吧?那敏感敏感信息呢,我们需要进行相应的脱敏操作啊,那在这儿呢,就是进行MD5加密,那我们逐个的处理一下MD5啊,把那个放进去,然后后边再起一个别名啊,那phone number我们也是一样的MD5啊,然后呢,把它放进来,后边起一个别名,那email呢,CTRLXMD5。
07:14
然后把它放进去,后边起个别名好了,那这三个敏感信息我们就处理完毕了,那接下来下边还有两个字段需要我们处理啊,那一个就是开始日期,一个就是结束日期啊,首先他俩为什么爆红啊,因为ods user in four当中根本就没有这俩字段对吧?那所以说呃,肯定会爆红的,那接下来我们来思考一下啊,那此处的star date和end date我们应该赋予它什么值呢?那其实这个很简单,大家来想一想啊,我们从ods user info这张表的2020年6月15号的分区当中拿到的是什么?拿的是不是新增变化的数据对吧?新增及变化的数据是不是都是当前最新的状态啊,最新的状态,那它的结束日期是不是应该就是9999杠九九杠九九对吧?那它的开始日期是多少呢?那是不是就是今天,因为它是今天的新增级变化对吧?啊OK,那所以说在这儿我们直接赋值就可以了啊,那这个我们给它写死为2020杠零六杠幺五啊,那后边的staratet就当做它的别名了啊,那再往后呢?呃,这个结束日期我们给它设为9999啊,然后杠。
08:20
九九杠九九啊,OK,那end datet就坐在它的表面啊,也就是现在我们就已经拿到了第二部分数据了啊,两部分数据咱们都拿到了之后,我们需要做什么呀?是不是需要对它俩进行一个全外联的操作,那既然要全外联,那我们就得给他们起别名啊,上面这个呃子查询呢,我们先起个名字啊,叫做O的啊,O的OLD,那下边这个子查询呢,我们命名为new啊,CTRLS。好,给它起名为new下边,呃,咱们接下来呢,要做的事呢,就是对他俩进行全外联啊,来,我们进行全外联UI啊。Join,那再往下呢,需要填一下它们俩的连接条件啊,应该是什么?应该是o.ID等于诶new.id对吧?嗯,好了,那完了之后呢,我们需要从呃,他俩照样的结果当中选择所有的字段啊,因为所有字段我们都可能会用到啊,那现在我们select。
09:17
From,那也就是说我们需要选择old里边的所有字段啊,还要选择new里边的所有字段啊,但是大家注意观察一下啊,这个old和new它们的字段是不是都是一样的呀,对吧?那所以我们在选择的时候呢,需要加别名啊,比如说举个例子,我需要选择哎,Old点。ID啊,那后边呢,还得再选一个,诶6.id。啊,当然那选完之后,我们一会儿还得从这个子查询当中选择咱所需的数据对吧?那所以说我们为了区分这俩ID呢,需要给它们起名啊,那这个呢,就起名为O的下划线ID,那这个呢,就起名为这个a new_ID。啊,OK,就是这样的一个思路啊,那剩余的所有字段我们都需要这样来进行选择啊,此处呢,我就不再一个一个去写了啊,咱们拿一下文档当中的结果啊,往下翻找到文档当中的结果。
10:12
来我们到这个位置啊。啊,CTRLC,那粘到笔记当中啊,CTRLV好了,那现在我们就拿到了我们所需的所有的字段了啊好,那我们把它格式化一下,行,那拿到之后啊,大家来思考一下,现在咱们得到的结果啊,相当于我们前面讲那个思路的时候,哪一部分呀,是不是就相当于这一部分。没错吧,那这部分呢,它会自动的给我们补上一些no值叭,如说这个位置啊,还有这个位置是不是所有的行,所有的列都是nu啊,对吧?哎,这是全外联的特点啊好了,那现在我们接下来要做的是什么呢?接下来咱要做的事儿,是不是就是从这个结果当中分别选择9999分区的数据和过期分区的数据啊,对吧。啊,是这样的啊啊,那既然要从这张表当中选择数据,那所以说在这呢,我们需要给它是不是封装到一个子查询当中啊啊,OK,那现在我们使用VS的语法给它封装一下啊。
11:09
跟着X,那在这呢,我们V那后边起一个别名叫tmp啊艾,那后边呢,需要写一个括号,把刚才这个子查询放进去。OK,把它往前推一下,当然最后这个分号咱就不需要了啊,给它去掉就行了啊,那现在我们就得到了一个tmp了啊,那接下来我们就开始从这个tmp当中获取我们所需的这个两部分数据,好,那接下来我们先去获取截止到今天的全量最新的数据啊,来打开文档,我们看一个图啊,来大家看一下啊呃,首先它是不是相当于我们刚才所写的O的这个查询,那这个呢,相当于我们刚才所写的new的这个子查询。没错吧,啊,那OK,那现在我们已经把old和new做了全外联了,对吧,那做了全外联之后呢,那他们是不是就会形成一个虚表,没错吧,而且这里边有一些位置呢,会被补上闹值,比如说这两部分。
12:03
啊,没错吧,啊,那我们现在是不是就要从这个子查询当中,从这个虚表当中去获取我们所需要的全量最新以及过期数据啊,对吧?啊,那现在我们先看怎么获取全量最新。啊,全量最新的数据呢,是由这样的两部分组成的,这部分还有这部分对吧?啊有这两部分组成啊,那大家会发现啊,这两部分数据呢,有些啊,我们是从O的这个子查询当中查出来的,那有些呢,是从new这个子查询当中查出来的。啊,没错吧,啊,那在这儿我们怎么去判断,那什么情况下我应该取O的这个刹群当中值,那什么情况下我应该去取new这个子差群当中的值呢?其实很简单啊,你观察的仔细一点你就能发现啊,是不是只要new这个子查询当中的字段为now,我们选择的就是O的子查询当中的字段啊?如果拗不为闹,那我们选择的是不是new这个子查询当中字段?
13:00
啊,没错吧,其实这个道理也很简单啊,这个具体怎么理解呢?其实就这样的啊,如果说拗为闹,拗为闹就说明什么,说明这个人今天没有发生变化,对吧?那我们是不是就取他原来的那个最新状态作为他今天的最新状态?没错吧,那如果又不为闹,不为闹就说明他今天发生变化,或者他是今天的一个新增用户,对吧,那今天的状态是不是就是他最新的状态。啊是这样的啊,那所以说这就是我们去选择全量最新的这个逻辑,那最终总结一下啊,也就是我们需要去判断啊,如果new.id不为no啊,那我们就选谁,那就选new里边的字段啊,否则的话呢,我们就选old里边的字段。OK,那现在把这个去落实一下,再往下走。现在我们就可以从把这个tmp当中选择咱们所需的数据了啊,Select from tmp,那现在我们先选择第一个字段啊,第一个字段我们的判断逻辑应该是啥呀?是不是应该就是if对吧?If啊,应该是new ID。
14:03
Is now,那它为now就说明什么,说明他今天没变对吧?没变我们就应该获取old的ID的值,那否则呢,我们就应该获取new ID的值,那同样道理啊,那下边我们也是这样的一些判断逻辑,那继续我们看下一个啊,来if,那下一个是谁?下一个我们上往上看一下啊来第二个字段是谁呀,第二字段呢,是login name对吧?那我们也是这样去判断,那if new log name is not啊那这时候我就怎么办呢?我就获取哎,Old,哎,Login name,否则我就获取new login name。啊,其余的字段呢,哎,都是这样去写,那当然呢,这个if的逻辑在这儿我们也能使用NVL去代替,对吧?比如说我们以这个ID为例啊,后边咱也能怎么写呀,我是不是能这么写,NVL里边一个new ID,一个O的ID啊没错吧,那这个函数现在的逻辑跟这个if函数的逻辑其实是完全一样的,那所以咱这也可以使用NVL啊,在这儿呢,我就不在一个字段一个字段去写了啊,我们拿一下文档当中的结果来往下翻一下。
15:07
再往下翻,往下翻好找到,哎这个位置。拿过来CTRLC来,先到这儿啊。我们先到这儿。CTRLC啊,那现在我们就拿到了咱所需的所有的全量最新的数据了啊,OK,行,全量最新已经拿到了,那接下来我们再去获取过期的数据啊,那过期的数据怎么获取呢?还是打开文档啊,大家注意观察啊,过期的数据指的是这部分对吧?那咱们想一想怎么能拿到它呢?啊,其实思路也不难啊,那首先我们也得从tmp这张表当中去选择对吧?啊,OK,那我们可以先把左边和右边均不为闹的这些数据过滤出来啊,过滤出来之后呢,我们选择它O的子查询当中的字段,那这是不是就是咱的过期数据啊,对吧?当然这里边有一个字段需要注意,咱需要把过期数据的结束日期改成前一天的日期,对吧?啊,那这就是我们获取过期数据的思路,好,那现在我们去实际的操作一下,来往下走啊,那现在我们先把这个子查询助掉啊,来,我们现在继续往下进行,来咱select,然后呢,From哪个表啊,就不是刚才咱写的那个tmp对吧?那这时候我们需要先来一过滤条件啊,刚才咱们的过滤条件是什么?是左和右,也就是old和new均不为now对吧,那就是说,呃,比如说以OLDID作为理条件啊,Old ID is not啊,并且new ID也不能为对吧,那is not。
16:38
OK,那现在咱这个过滤条件就写完了,写完之后,呃,按照我们刚才的这个分析呢,我们应该选择O的这个子查询当中的字段,对吧?那应该选择什么呀?比如说OLDID,那还有什么呀,还有什么old login name等等等等,那咱把所有的old字段选择出来就OK了,那在这呢,我就不再一个一个去写了啊呃,打开文档,我们拿下文档当中的结果。
17:02
再往下翻。找到这个位置。好,那这就是我们选择的过期的数据来放在这儿。好,那大家注意观察一下啊,观察哪儿,观察我们这个最后一个字段,最后一个字段是不是就是那个结束日期啊,对吧?那这里边你看啊,做了两步操作,首先第一步使用data的函数啊,对当前日期啊进行了一个简一的操作,对吧?那也是获取前一天的日期嘛,啊这个是正确的啊,除此之外呢,那是不是还做了一个类型的强转呀,对吧?将这个类型转成了死菌类型。啊,那这是为什么?给大家简单解释一下啊,首先大家得明确data I的函数,它的返回值是什么类型,是date类型的啊,这一定要注意一下啊好,那由于一会儿我们需要把它和上边咱写的这个子查询进行一个union操作,对吧?那union的时候呢,我们上下两个子查它们的字段个数以及字段类型是必须得匹配才可以,没错吧?那上边的结束日期咱是死人类型,那下边的这个结束日期呢?诶如果不是死人类型,那union的时候是不是就会出问题啊对吧?那所以在这儿呢,需要强转一下,然后统一上下两个子查询的类型,哎,这就是主要目的啊好,那这个子查询咱就写完了,那上面这个查询呢,我们也可以放开了啊,那放开之后呢,这两个紫查询我们需要做一个什么操作呀,需要做一个union操作,那union的时候呢,我们有呃union还有union奥对吧,那union会去虫,Union奥不会去虫,那咱这儿没有去虫的需求啊,那所以说我们用union奥就可以了。
18:37
啊,OK,那现在我们就得到了咱所需的两部分数据了,一个呢,是截止到当前的全量的最新的数据,对吧?那一个是过期的数据啊,两部分咱都得到了,而且我们已经把它们优粘在一起了,那接下来我们要做的是啥呀?是不是就是把这两部分数据写入到啊这个拉链表当中啊,没错吧,那怎么写呢?我们往上接着走,那是不是在这儿补上一个insertright语句就可以了,对吧?Insertright后边来一个table table来谁是不是diam user in for后边分区啊,Partition,那关键这个partition怎么写?
19:12
呃,大家要注意啊,那下边的数据是不是最终要写往两个分区对吧?它需要写到9999分区,那它呢,需要写到2020杠零六杠幺四分区,没错吧?诶,那这时候咱的分区应该怎么写。那显然我们就不能写死这个分区了,我们应该使用什么呀?应该使用动态分区对吧?那动态分区在这个位置咱是不是只需要写一个诶分区的字段名就可以了,对吧?那数据到底被写往哪个分区是由什么决定的呀?是不是由我们select当中它的最后一个字段决定的呀,对吧?那当然我们要想用呃动态分区,我们需要在满足呃咱们这个字段的要求的基础值上再多选择一个字段对吧?多选的字段就作为动态分区的值啊OK,那现在我们需要再额外的选择一个字段啊,那这部分数据咱大家都知道它将来会被前往哪个分区啊。
20:04
是不是9999分区没错吧,那现在呢,我们就把这个呃9999给它写在这儿,9999,然后杠九九杠九九啊这个没有问题对吧?那继续往下进行,那这一部分数据会被写往哪个分区啊,是不是会被写完啊呃,哪哪一个分区是不是6月15号前一天的分区,那也就是14对吧,其实就是谁呀,是不是其实就是他。没错吧,那现在咱们CTRLC,诶把这个呢,我再来复制一份。来拿过来啊,由于动态分区的时候呢,我们动态分区的这个字段跟其余的字段不能完全一样啊,那所以在这儿呢,我们给它起一个别名啊,叫做DT啊,否则的话呢,哎,它会出现这个奇,那这个呢,就是用户维度表每日装载的最终的SQL语句了啊,那这个SQL语句呢,我们暂时还不能执行啊,因为我们现在还没有生成6月15号的数据啊,那咱得等6月14号的数据从前到后全部装载完毕,那再去生成15号的数据,那到时候呢,咱再来执行这个circle。
我来说两句