历史拉链表「建议收藏」

历史拉链表「建议收藏」使用UDW创建历史拉链表。介绍历史拉链表历史拉链表是一种数据模型,主要针对数据仓库设计中表存储数据的方式而定义的。**它记录一个事物从开始到当前状态的所有变化的信息。**拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据的一种常见方式。也就是说,对于表中的任何数据,不进行真正的删除,只记录操作和有效日期。流程其中,tmp0表有两个分区,表…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

使用UDW创建历史拉链表。

介绍

  • 历史拉链表

    • 历史拉链表是一种数据模型,主要针对数据仓库设计中表存储数据的方式而定义的。**它记录一个事物从开始到当前状态的所有变化的信息。**拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据的一种常见方式。
    • 也就是说,对于表中的任何数据,不进行真正的删除,只记录操作和有效日期。
  • 流程

    • 55253131334
    • 其中,tmp0表有两个分区,表示历史数据和当前数据,使用tmp1tmp0和事实表进行更新和交换。

操作

创建表

  • 首先需要创建delta表,事实表,以及两个tmp表

  • -- 事实表
    create table public.member_fatdt0
    (
    member_id varchar(64),         -- 会员ID
    phoneno varchar(20),           -- 电话号码
    dw_beg_date date,              -- 生效日期
    dw_end_date date,              -- 失效日期
    dtype char(1),                 -- 类型(历史数据,当前数据)
    dw_status char(1),             -- 数据操作类型(I,D,U)
    dw_ins_date date               -- 数据仓库插入日期
    )with(appendonly=true,compresslevel=5)   -- 压缩级别
    distributed by (member_id)
    PARTITION BY RANGE (dw_end_date)
    (
    PARTITION p20111201 START (date '2011-12-01') INCLUSIVE,
    PARTITION p20111202 START (date '2011-12-02') INCLUSIVE,
    PARTITION p20111203 START (date '2011-12-03') INCLUSIVE,
    PARTITION p20111204 START (date '2011-12-04') INCLUSIVE,
    PARTITION p20111205 START (date '2011-12-05') INCLUSIVE,
    PARTITION p20111206 START (date '2011-12-06') INCLUSIVE,
    PARTITION p20111207 START (date '2011-12-07') INCLUSIVE,
    PARTITION p20111231 START (date '2011-12-31') INCLUSIVE
    END (date '3001-01-01') EXCLUSIVE
    );
    -- 增量表
    create table public.member_delta
    (
    member_id varchar(64),
    phoneno varchar(20),
    action char(1),                      -- 数据操作类型(I,D,U)
    dw_ins_date date                     -- 类型(新增,删除,更新)
    )with(appendonly=true,compresslevel=5)   -- 压缩级别
    distributed by (member_id)
    -- 临时表
    create table public.member_tmp0
    (
    member_id varchar(64),
    phoneno varchar(20),
    dw_beg_date date,
    dw_end_date date,
    dtype char(1),
    dw_status char(1),
    dw_ins_date date
    )with(appendonly=true,compresslevel=5)   -- 压缩级别
    distributed by (member_id)
    PARTITION BY LIST (dtype)
    (
    PARTITION PHIS VALUES ('H'),      -- 表示历史信息
    PARTITION PCUR VALUES ('C'),      -- 表示当前信息
    DEFAULT PARTITION other
    );
    

    – 临时表1
    create table public.member_tmp1
    (
    member_id varchar(64),
    phoneno varchar(20),
    dw_beg_date date,
    dw_end_date date,
    dtype char(1),
    dw_status char(1),
    dw_ins_date date
    )with(appendonly=true,compresslevel=5) – 压缩级别
    distributed by (member_id)

    
    
  • 55252699017

插入数据

  • 插入delta表

    • Insert into member_delta values('mem006','1310000006','I','2011-12-03');
      Insert into member_delta values('mem002','1310000002','D','2011-12-03');
      Insert into member_delta values('mem003','1310000003','U','2011-12-03');
      
    
    - ![55252708610](http://wx4.sinaimg.cn/mw690/0060lm7Tly1g124h8xtgtj30al02w0sk.jpg)
    
  • 插入事实表

    • Insert into member_fatdt0 values('mem001','1310000001','2011-12-01','3000-12-31','C','I','2011-12-02');
      Insert into member_fatdt0 values('mem002','1310000002','2011-12-01','3000-12-31','C','I','2011-12-02');
      Insert into member_fatdt0 values('mem003','1310000003','2011-12-01','3000-12-31','C','I','2011-12-02');
      Insert into member_fatdt0 values('mem004','1310000004','2011-12-01','3000-12-31','C','I','2011-12-02');
      Insert into member_fatdt0 values('mem004','1310000004','2011-12-01','3000-12-31','C','I','2011-12-02');
      Insert into member_fatdt0 values('mem005','1310000005','2011-12-01','3000-12-31','C','I','2011-12-02');
      
    • 55252707255

数据刷新

  1. 将member_fatdt0表与member_delta左外连接,相关联的历史数据插入到member_tmp0历史分区,反之插入到member_tmp0的当前分区

    • 这里主要处理update和delete操作,若能进行左连接,说明数据有更新,插入历史分区

    • truncate table public.member_tmp0;
      -- 清理临时表
      insert into public.member_tmp0
      (
      member_id,
      phoneno,
      dw_beg_date,
      dw_end_date,
      dtype,
      dw_status,
      dw_ins_date
      )
      select a.member_id,a.phoneno,a.dw_beg_date,
      case when b.member_id is null then a.dw_end_date
      else date '2011-12-02'
      end as dw_end_date,
      case when b.member_id is null then 'C'
      else 'H'
      end as dtype,
      case when b.member_id is null then a.dw_status
      else b.action
      end as dw_status,
      date '2011-12-03'
      from public.member_fatdt0 a
      left join public.member_delta b 
      on a.member_id = b.member_id
      and b.action in('D','U')
      where a.dw_beg_date <= cast('2011-12-02' as date) -1
      and a.dw_end_date > cast('2011-12-02' as date)-1;
      
    • 55252723553

  2. 将member_delta当前数据(更新,插入的新数据)插入到member_tmp0当前分区,end时间为无穷。

    • insert into public.member_tmp0
      (
      member_id,
      phoneno,
      dw_beg_date,
      dw_end_date,
      dtype,
      dw_status,
      dw_ins_date
      )
      select member_id,phoneno,
      cast('2011-12-02' as date),
      cast('3000-12-31' as date),
      'C',
      action,
      cast('2011-12-03' as date)
      from public.member_delta
      where action in ('I','U');
      
    • 55252858777

  3. 将member_tmp0历史数据与member_fatdt0相应分区交换(通过member_tmp1表进行交换)。

    • alter table  member_tmp1 drop constraint member_tmp0_1_prt_phis_check;
      truncate table public.member_tmp1;
      alter table public.member_tmp0 exchange partition for ('H') with table public.member_tmp1;
      alter table public.member_fatdt0 exchange partition for('2011-12-02') with table public.member_tmp1;
      
      • 55252886260
  4. 将member_tmp0当前数据与member_fatdt0相应分区交换(通过member_tmp1表进行交换)。

    • alter table  member_tmp1 drop constraint member_fatdt0_1_prt_p20111202_check
      alter table  member_tmp1 drop constraint member_tmp0_1_prt_pcur_check
      alter table public.member_tmp0 exchange partition for('C') with table public.member_tmp1;
      alter table public.member_fatdt0 exchange partition for('3000-12-31') with table public.member_tmp1;
      
    • 55252945525

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/181068.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • 数据库是什么?数据库详细笔记!带你走进数据库~你想知道的这里都有!

    数据库是什么?数据库详细笔记!带你走进数据库~你想知道的这里都有!

  • App测试面试题_软件测试算法面试题汇总

    App测试面试题_软件测试算法面试题汇总1.Web端测试和App端测试有何不同(常见)系统结构方面Web项目,b/s架构,基于浏览器的;Web测试只要更新了服务器端,客户端就会同步会更新;App项目,c/s结构的,必须要有客户端;App修改了服务端,则客户端用户所有核心版本都需要进行回归测试一遍;兼容方面Web项目:a.浏览器(火狐、谷歌、IE等)b.操作系统(Windows7、Windows10、Linux等)App项目:a.设备系统:iOS(ipad、iphone)、Android(三星、华为、联想等)、

  • c语言字符串去重_putchar输出字符串

    c语言字符串去重_putchar输出字符串输入一串字符,然后去重。如:输入aaabbbbd;输出应为abd;#include<iostream>#include<string>usingnamespacestd;intmain(){intn;cin>>n;//n表示你要处理多少组stringa;//定义字符串for(inti=0;i<n;…

    2022年10月31日
  • 装水的容器大全_盛水的容器什么最好

    装水的容器大全_盛水的容器什么最好原题链接给你 n 个非负整数 a1,a2,…,an,每个数代表坐标中的一个点 (i, ai) 。在坐标内画 n 条垂直线,垂直线 i 的两个端点分别为 (i, ai) 和 (i, 0) 。找出其中的两条线,使得它们与 x 轴共同构成的容器可以容纳最多的水。说明:你不能倾斜容器。示例 1:输入:[1,8,6,2,5,4,8,3,7]输出:49解释:图中垂直线代表输入数组 [1,8,6,2,5,4,8,3,7]。在此情况下,容器能够容纳水(表示为蓝色部分)的最大值为 49。示例 2:输入:he

  • elasticsearch更新数据效率_elasticsearch update_by_query

    elasticsearch更新数据效率_elasticsearch update_by_query    es批量update远比,批量get,或者单次query到文档,批量修改后,再批量index,这样效率会高非常多(有实验测试高达1000倍!)。

  • SpringBatch概述

    SpringBatch概述1、SpringBatch简介1.1、简介根据Spring官网描述,SpringBatch是一个轻量级的、完善的批处理应用框架,旨在支持企业系统建立健壮、高效的批处理应用。然而SpringBatch不是一个调度框架,它只关注于任务的处理,如日志监控、事务、并发问题等,但是它可以与其它调度框架一起联合使用,完成相应的调度任务,如Quartz、Tivoli、Control-M等。Sprin…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号