00:00
好,跟大家说一下啊,咱们这个所谓的合并变动信息,这个咱们得从哪一天开始才需要合并变动信息啊,第二天开始了啊,假如说我们出仓第一天就是六月14开始的,咱们就是六月14开始的,对不对,那六月14我们这一天数据怎么过来,是不是咱们执行一个这个全量同步就行了呀,啊全量同步就好就OK了,那我们去获取变动信息,然后合并变动信息是被从6月15号开始了呀,对不对,所以说这个呢,我们也是6月15号开始。啊,这个咱们先明确啊,当然咱们现在数仓里边,我们现在还没有15号的数据啊,没关系啊,这个后续我们开发完之后呢,我们还会再造一天数据,到时候咱们再去看这个效果就行啊好,那咱们现在不影响咱写circle啊,咱们该写写就行啊好,那既然要合并这个数据了,我们得回顾一下咱们这个数据到底怎么合并来着。来我们往上看啊,打开这个表,打这个PPT,这PPT来咱怎么合并来着,是不是这就是咱们所谓的这个合并的工作呀,对吧?那咱们基本上这个方案是这么定的啊,当时需要先拿到新增级变化,然后加上开始和结束日期,然后呢,找到这个老数据,把老数据里边呢,这个需要修改的九九,九九改成昨天的日期,完了之后呢,这两部分数据直接进行什么优念合并,是不是就得到最终结果了呀,这是我们要做的,那所以说咱们现在呢,就分这样的三步去做,第一步处理新数据,第二步处理老数据,第三步union,最后呢,插入就行了啊就是这样的三步啊好,那开始写词课了啊,那首先我们就要先获取我们这个新数据啊,对新数据进行处理,那我新数据在哪放着?
01:38
是不是在ods层的O,呃,不是O是U啊u ino里边放着呀啊,那当然我们这个得获取哪一天呢?啊,不是14了,咱们从15才开始获取这个变动,对吧,那就零六杠幺五呗,啊零杠五啊把这个先拿到,那当然了,咱们这儿呢,需要拿所有字段啊,这个所有字段呢,我就呃从那拿吧,是一样的啊。
02:03
那CTRLV好,那拿回来之后呢,我们对它的处理方式应该是什么?是不是加俩字段呀,一个开始一个结束对不对,那开始日期应该是哪天2020杠零几。零六杠幺几啊?11415啊。幺五今天是15号了,对不对,你15号获取到的最新状态,那开始日期是不是就是15号了,对不对,那所以说这个得搞清楚啊,那接下来往下走,那结束日期就是全是9999啊九九杠九九啊杠九九这个没错吧,那新数据处理好了,那接下来我们处理这个老数据,就是老数据,你要处理的话就是就是这么多,就是去找一下,看看哪条数据它的9999需要去修改对不对,要改的给他改了。那然后是不是把这两部分数据可以合并到一起了呀,就这样一个逻辑嘛,来看看怎么合并来我们select select from from哪张表。还是这个咱们这张维度这个这张那个拉链表吧,对吧,那从DWD,然后呢,DS。
03:08
应该是user in for case,是不是这是咱们那个拉链表啊,那从这里边去获取我们这个所需的这个今天的这个数据,当然这里边它的字段比较多啊,字段比较多,那有什么呢?有这些。这是他最开始那个CTRL,喂,这些字段呢,直接拿就行,这些其实是不用改的吧,其实咱们要改只改哪个字段呀。只改那个999END date对吧,只有end date需要修改,所以前面的根本就不用判断,直接拿就行,包括那个starlight starlight是不是也不用改啊,那关键就是这个end date end date这怎么写?啊,怎么写,这时候咱们就得考虑一个问题了啊,我们是这张表当中的所有数据的n date都要改吗?不是只改某些数据对不对,那那咱假如说我现在把这张表的数据啊,全部拿回来了,就放在这儿了,那你怎么去找哪些要改,哪些不用改呀。
04:08
哪些要改,哪些不改。哎,又有同学想到了,是不是跟咱们今天的新增级变化可以做一个对比啊,对不对,跟今天的新增级变化做一个对比,那怎么对比,是不是还通过join的方式去对比啊,那OK,我把今天的新增级变化拿过来放在这儿了啊,是不是放在这了,那放下之后呢?那他们还是这样的一个对应关系,有重合的,也有各自独有的,这就是重合的部分,对不对啊?那当然join的时候得得用什用这个UID去join对吧?有重合的,也有这个各自独有的,那对于各自独有这部分呢,这是不用改的吧。对不对,那这部分呢。是需要改的,两边都有,是不是正好需要修改的呀?那这部分的是什么,是新增的啊,新增的啊,那问一下大家啊,那你说我们现在这两张表比完之后,咱们要返回的结果应该是哪部分数据是都要啊,还是只要其中一部分呢?
05:01
是都要吗?还是只要一部分。是都要吗?其实只要一部分就够了啊,为啥呀,只要哪部分。只要旧的是不是就行。为啥只要旧的啊,为什么,因为新的是不是在上面呢?是不是新的呀,对新的我是不是处理完了,然后跟旧的,咱们旧的你看啊,你旧的只干了什么事,你看那个PPT。旧的,我们只是把这里边儿的某些数据给它改一下就行,这个函数会发生变化吗?不会的,对不对,那所以说咱们只要谁只要旧的就就可以了啊,这是新旧啊,那我就说我这个,呃,这个照完之后呢,我只要这一部分就行了,然后新的跟上面是不是直接进行一个union就行了呀,就是这么一个过程嘛,好,那咱们既然要要的是这部分数据,那你说他俩进行对比的时候呢,你说我们应该用哪种招引方式。Left run呗,对不对,我要返回是不是这部分返回这部分,那就left run呗,没错吧,啊没错啊,好,那left run,嗯,行,那这个我们先把这一点明确啊,那接下来呢,我们为了去修改这个数据啊,那为了去判断哪些要改,哪些不改,我们需要跟今天的新增级变化做一个left join啊,那所以说咱们从这呢就得往下写了啊,From这张表,然后呢,让他去和谁去和我们那个新增级编号做一个left run,那所以说这下边咱是不是需要有一个子查询啊,咱们需要来一个子查询啊。
06:32
来,把这个拿过来。下那select select啊这呢,我就先写一个星啊,先一个星select星,然后from d。OS啊,ODS,然后呢,User in for啊,OS user for,然后where dt等于2020杠零六杠幺四啊幺几幺幺,我今天15号了,15号了,好,那给它起一个别名,这个呢,我管它叫做哎,U in for UI嘛,啊那上面呢,给它起个别名叫做his,诶,咱们叫old和new吧,对吧?这是不是old,这个叫new对吧?那这个old和new比较熟悉嘛啊好,那它俩需要做什么,需要做一个left draw对吧?那left draw完之后呢,它的连接条件应该是on,哎,old.id等于new.id是不是照上了啊,没错,那照上之后呢,接下来是不是就可以去写这个字段了呀,对不对?那咱们来判断呢,什么样的改,什么样不改啊,这是old的,这是U。
07:33
啊,咱们要的是这部分数据吧,这是咱们返回的数据,对不对啊,是咱们返回的数据。OK,那这块咱们再看看怎么去写啊,呃,首先咱们得明确啊,这两张表做了一个draw之后,Left draw之后返回的虚表是哪部分,是这部分吧,啊,有几个字段。是不是这里边有这么多字段,这个里边是不是也有这么多字段呀,对不对,那你说我们要取的应该是哪些字段呀?上面这一大堆应该都来自于谁O来自于old才对吧?你都得拿old的对不对?你拿new是不是这块全是no啊,对吧?你不能拿new,得拿old的,也就是上边这一大串全都得来自于old,一会儿这个咱们得改一下啊,都来自于old啊,那接下来呢,就是这个end date了,End date看看是怎么整啊。
08:18
是不是这一部分的n date需要改对不对,那这一部分跟上面这部分有啥区别呀,怎么给他们区分开呀,上面的不改,下边的改对吧?啊怎怎么区分。是不是就看。右边是不是闹啊。对不对,你看啊,这个new如果是闹,那这块相当于就不用改。右不是不为呢,那这不是需要改的呀,是不是就是这个道理,那所以说那这时候咱们就应该就知道怎么写,是不做一个if判断if new的是不是ID,哎,等于now,那我就不改,不改就保留原来的值。对不对,如果说你有的ID不为闹不为闹,那我就改,改成什么,改成昨天的日期对不对,就这样一个逻辑,那咱们先写一下啊,那上边这些东西全部得都得改成,改成啥,改成O的点啊oledd点好,那接下来接着往下走,那下边咱们写啥。
09:12
应该是if判断呗,啊if啊,If咱们就得判断,哎,new.id is no,哎,如果为闹啊,那咱们写为闹还是不为闹呢?呃,不为闹的要改,为闹的不用改,对吧,那咱们写闹闹吧,这个好理解一点啊,如果不为闹不为闹,那我是不是得改,那改成多少。改成昨天日期嘛,哎,怎么会取昨天日期啊。怎么获取作业日期?当天日期是多少?当天是不是6月15号啊,怎么会前一天。是不是用它减一对吧,怎么对日期减一啊。直接减行吗?这个不行,我们得借助一个函数,这函数呢叫做date a或者叫date sub,你看啊,咱们俩函数一个是date a,一个是date sub,它就是加subb就是减,但其实这俩函数呢,呃,用一个就够了啊呃,这个函数怎么用啊,它俩都是一样的,传俩参数,一个参数日期,一个参数呢,是一个int值啊,当然你这个int值呢,是可正可负的对不对,既然可正可负,那是不是就是其实它俩用一个就够了呀,对不对啊,就一样啊,那咱们这是不是要获取昨昨天的呀,昨天呢,比如说我用呃dated data的话呢,那你就把今天的日期放进来,2020杠零几零六杠幺五,那OK,减一。
10:30
这是不是就是昨天日期啊,那如果用sub呢,就是正一呗,那就这个道理啊啊,那如果说是部委呢,部委呢,要改要改就改成昨天,否则的话呢,改成是不是保留原来的日期,那就old的点类呗。哎,是不是就搞定了。完事了吧,感觉。啊,诶,那这么写它对不对呢?有没有什么漏洞呢。好像没啥问题对不对,感觉被我忽悠的是不是感觉,诶没问题是吧,但是里边有没有问题啊。
11:00
有问题啊,有问题我一直在忽悠你们啊,这个确实有问题,有什么问题呢?你你们仔细思考一下啊,关键是忽悠半天没没有人能反应过来啊来,为什么有问题啊,咱们分析一下。来这个是old的对吧?啊,这个是new,咱们这old和new的召唤条件是什么,是不是只有一个ID啊,对不对,那你要知道啊,在old当中。啊,咱们一行数据是一个什么?是一个状态拉链表对吧?一行数据是一个状态,那也就是我一个用户是不可能有多个状态,对不对啊,那也就是说,假如说啊,我一个用户他的ID是多少?ID是一,ID是一,假如说啊,那我的状态呢,比如说有三个,哎,状态一,状态二,状态三,那他的ID是不是都是一。那假如这个用户啊,我今天状态发生变化了,他是不是会出现在咱们右边的什么里边。是不是出现在咱们右边的这个新新能级变化里边呢,对吧,那这个ID是不是一。那是不是一个一能够跟这三个一照着上。
12:00
对不对,那照上之后,那你这直接判断啊,只要不为闹,那他们仨这边是不是都不为闹。这三条数据对应的这个右边是不是都不给呢,都照上了吧,那都照上了,那你是不是都给它改成昨天日期了。对吗?不对,你只改谁呀,只改n date等于9999的那一条对不对,原来那些状态你不能给他把结束日期改了吧,啊你是不能改的,那所以说这块呢,咱们是有问题的啊,这个问题怎么怎么解决一下。怎么解决,只需要在这儿,是不是再加一点就行啊,再加一点或者是在哪加也行。是在状语条件上也加,加一头也行啊,也行,其实一样的道理啊,咱们加在上边更好理解一些啊,那加上边吧,那加在这,那就是and啊,什么东西?呃,End date等于。9999。9999,然后杠九九杠九九,哎这回才是哎这个最完整的一个结果啊,那这样一来就不会出问题了啊,这就是啊,咱们这提到的这个,呃,这个新的数据,这个旧的数据咱们就改完了啊,当然新旧改完之后,他俩需要做一个什么东西,所1UNION对吧?哎,那问一下大家,这个妖念我是分两种,一个奥,一个UN念奥一个妖念对吧?那有什么区别。
13:15
一个去虫,一个不去虫,哎,Union all是去还是不去啊?Union是不去啊,Union是去啊啊怎么怎么记啊,这个东西union all all是不是所有啊,对不对,不管你重复不重复,所有东西全给你合并在一起,那这就是不驱虫啊,就带all的是不去虫的,Union是驱虫的,这个应该挺好记的啊,那咱这有驱虫的这个驱虫吗?有没有没有没有,咱是不是就用union all啊对不对,那我问一下啊,这个union的语法大家熟悉吗?英语法熟悉吗?妖念肯定是两个东西进行妖念,对不对,两个什么进行妖念呀。是两张表进邮件行吗?不行,你要记住啊,我们have当中啊,Union啊,能够进行union连接的东西只能是啥?
14:03
只能是select,紫纱裙啊,就必须得是select什么什么from什么union or或union select什么什么from什么能理解吧,只能是它啊,你比如说我现在想让有两种表,一个表A,一个表B哈,假如是这么写的,那select,呃,然后比如说from表A,又念表B,这样行吗?不行啊,这个是不对的啊,只能是两个子查询进行优念,那咱这是不是就正好是俩子查询一个新的一个旧的对吧,直接他俩进行一个是不是优念奥就行,哎UN奥就行,诶搞定,那再问一下啊,那UN金奥之后,那这个东西你怎么处理它呀。妖精到一起之后怎么处理它?来再记住一个原则,你这个union你就会用了啊,不管是几个c select子查询union到一起,不管是两个也好,还是三个也好,只要using到一起之后呢,你就把它当一个什么处理就行了。就当成一个select子查询就可以了。
15:02
啊,你就对一个select你是怎么对待的,那对多个select进行union之后的结果就怎么对待就行了啊,能理解吧,那相当于咱们这一大串是不是就是一个select子查询呀,对不对,那这个合并之后,咱们需要给它放到哪,是不是放到咱们一个所谓的临时的拉链表里边啊,对不对?那咱们临时拉链表在哪呢?我先把临时拉链表建一下啊,当然这个临时拉表其实咱们也分析过了,不用也行是吧?啊,不用也行,咱这就不改了啊,咱们就也给他整一个临临临时表吧,临时表在哪,在这呢啊。啊,临时表CTRLC临时表的字段啊,哎,跟咱们真正的拉链表字段是一样的啊,唯一的区别呢,就是它的表明后边有一个tmp啊啊,那接下来咱们要做的是就是把这个合并之后的结果放到这个拉链表当中啊,临时表当中怎么放啊,是不是加一个insert,是不是直接加insert就行,因为下边这一大堆东西啊,相当于是一个什么。相对是一个select吗?那咱们的语法不就是insert加select吗?那所以这个直接写就行了,Insert然后加什么呢?加上一个,呃,这个overwe啊,咱们这个table,那DWD啊,闭母啊,User in four和three TB哎,就搞定了啊,那这就是咱们这个circle啊,当然呢,这块咱们没有分区,直接直接覆盖就行啊,这是这一步啊啊然后咱们看一下文档,文档上可能要比我写的要多一步啊,看一下多哪一步了。
16:25
他是不是并没有把这个合并之后的结果直接插入到咱们这张表里啊,他干什么了,是不是把咱们合并之后的结果又做了一个子查询。然后下边加了一个什么order by,它按照哎咱们这个哎用户的ID以及开始日期做了一个排序,对吧,那大家说这块有必要排序吗。有没有必要排序啊?有必要吗?没必要,完全没必要啊,这个排序只是为了让数据啊,你看着能好看一点,但是你要知道拉链表咱们用的时候怎么用啊,我们是不是N的date等于9999,或者是什么这个开始日期啊,这个小于等于某一天结束日期大于等于某一天,是不是这么去使用它呀,你这个需要用到它排序吗?
17:12
不需要,所以说这个排序很一点必要没有一点用没有,而且你加了这个排序之后呢,会导致你这个拉链表啊。就是每天更新的时候,这个时间会比较长,为啥呀,因为拉链表本身数据量比较大,对吧?完你这相当于做了一个啥呀,全局排序。而且你要发现这是不是没有limit米呀,对不对?全局排序,你要对这么大的数据量全局排序,那你要知道啊,用order如果不加limit米,那我要想得到一个全局有序的结果,那你只能怎么做才能得到全局有序的呀?把所有的数据是不是最终写到一个reduce里边,我才能得到一个全局游戏的结果呀,对吧,所以无论如何最终你这些数据肯定都会交给一个reduce处理,但这一个reduce压力会比较大啊,比较大啊,那这个相当于也是有这个就是啊,这个相当于也叫数据倾斜吧,这个不能叫做,其实叫数据倾斜不太好啊,就相当于就是有某某一台节点,它的这个数据量比较大啊,因为所有数据都跑这一台节点的嘛,所以这个不好啊,咱这个排序咱就不要了啊,排序就不要了,反正加呢也没啥用啊,而加了之后呢,还会影响咱们的性能啊,咱就不加它了,好,那这个就是咱们最终这个拉链秒的这个circle,到这一步呢,其实核心的东西就完事了啊,当然最后一步呢,还有咱们需要把临时表的数据时,还得放到咱们真正的答案表啊,那就写一个insert,哎,Overri table,那DW。
18:34
啊,加一个Di user in for his啊,然后后边呢,加一个select星from WD层的。DWDWD层的啊,这个tmp从咱们这个user info的tmp里边去选是不是就可以了啊,那这个是不是就是咱们这个拉链表所有的这个操作呀?啊,就完事了啊,嗯,给大家说一下,你说咱们这个后续这个拉链表的这个装载的语句是不是也要写到脚本当中啊,那写到脚本当中的内容应该是哪部分,咱们写了这么多,从上到下这么多,哪部分需要写到脚本当中啊?
19:11
哪部分?其实就是这部分。然后呢,再加谁再加这一部分是不是就行了。对不对,这是相当于是每天把新增级变化进行合并啊,对不对,跟跟那个老老的老的数据进行合并,完了之后呢,再把这个临时的表的数据呢,放到真正的表的这个数据里,那这个真正的这个表里是不是就完事了,就是主要就是这两步D相当于步骤二和步骤步骤三啊,是我们每天重复做的啊好,那这个就完事了啊,我把视频录一下啊。
我来说两句