hive 之拉链表构建

1、什么是拉链表
    做过数据分析建模的人都知道，拉链表是一个保留历史业务场景，能随时回溯某个时点业务场景的数据表。
    举例来说，一个账务核算系统里面会根据不同的需要建立一些用于特定核算目的的账户，每个账户每天都有可能发生余额变化，但每天并不会所有的账户都会发生余额的改变，我们在进行数据分析时，又往往需要对特定的历史时点数据进行分析，因此需要保留特定历史时点的业务场景。

    对于这种情况而言，解决问题的办法有如下三种方式：

• 第一种方式：每天保留截止到当期时点的最新数据，并针对当前的最新数据进行分析，产生分析报表，对分析结果每天保存。

           这种方法的好处是账户表数据量保留最小，只保留截止到当前日期的全量数据，分析报表数据实时产生。
          这种方法的不足之处在于，当数据分析需求发生改变时，无法对历史业务场景进行回溯。  

• 第二种方式：每天保留一份账户数据的全量快照，对于一个账户数不多的财务系统而言，每天保留一份账户的全量快照是没有问题的，但是对于一个有着很大的账户数目的账务核算系统而言（比如账户数达到数百万），每天保留一份账户的全量快照则会使得账户表的数据量巨大，即不利于存储，也会因为数据量过大而造成无法快速检索需要的数据。当然：你可以在 hive 中按日创建分区表，可以解决查询速度问题，但是数据量却无法减少，即使i采用 orc 表对数据进行压缩，依然存在数据量巨大的问题。

        这种方法的好处在于实现简单，也最大限度的保留了业务数据的历史场景。
        这种方法的不足之处在于每天一份全量数据，造成数据量巨大，同时：当数据量达到一定程度后，其数据量将使得数据查询变得不可能。

• 第三种方式：针对账户表构建拉链表，这种方式即保留了业务数据的历史场景，同时又最大限度的减少了数据量，使数据访问的执行效率大大提升。
  

2、典型的拉链表数据结构及检索方式。
     一个典型的拉链表一般会命名为历史表，同时还有一个当前最新的变动情况表，两个表的数据表结构基本是一样的，举例如下：
     我们给某个商业银行做一个数据仓库，需要对商业银行的账户表建立拉链表，则我们需要创建如下两张数据表。

• 账户信息历史情况表（account_info_history),表结构如下：

点击(此处)折叠或打开

create table account_info_history (
    account_id                 string,
    account_name            string,
    account_cur integer,
    account_balance decimal(18,20)
    start_update_date date,
    last_update_date date
);

• 账户信息变动情况表（account_trans_info)，表结构如下：
  

点击(此处)折叠或打开

create table account_trans_info (
    account_id                 string,
    account_name            string,
    account_cur integer,
    account_balance decimal(18,20)
    start_update_date date,
    last_update_date date
);

      在第一次装入数据时，我们需要获得一份最初的全量数据，装入到“账户信息历史情况表” 进行数据初始化，装入数据时，start_update_date 的值为当时装入数据的日期，last_update_date 为 ‘2999-12-31’ 。
     开始更新数据时，需要经过如下步骤：
      step 1：首先将 account_trans_info 表中的所有记录删除，然后将当期更新部分的数据装入到 account_trans_info 表，装入时 start_update_date 的值为当时装入数据的日期，last_update_date 为 ‘2999-12-31’ 。
      step 2：将出现在 account_trans_info     表中的账号，从 account_info_history 中找出来，找出的条件是  account_info_history.account_id = account_trans_info.account_id and last_update_date = '2999-12-31' 的记录，将该部分数据记录的 last_update_date 的值修改成当前的日期。
      step 3：将 account_trans_info 表中的记录追加到 account_info_history 表中。

     完成上述操作后，我们的数据表中记录将呈现出如下的状态：

account_id |   account_name         |  account_cur  | account_balance |  start_update_date |  last_update_date
---------------+---------------------------+-----------------+---------------------+------------------------+-----------------------
10000001 |  张山                         |   RMB             |          100000.00 |   2021-08-10        | 2021-08-15
10000002 |  李四                         |   RMB             |          800000.00 |   2021-08-31        | 2999-12-31    |
10000001 |  张山                         |   RMB             |          150000.00 |   2021-08-15        |  2999-12-31    |

     当我们需要检索 2021年 8 月 14 日的所有账户的信息时，我们只需要实行如下语句即可实现：

select * from account_info_history where '2021-08-14' between start_update_date and last_update_date ;

     即可获得截止于 2021年8月14日的时点，所有的账户中的情况。

3、在 hive 中创建拉链表遇到的技术难题
     由于 hive 中不支持 update 、delete 等操作，或者说，执行 update、delete 等操作效率极其低下。因此，像传统的操作拉链表的方式，即：先对当前表中有表中有变动情况的数据的最后修改日期字段进行 update 操作，然后再将当期发生变化的数据追加到账户历史情况表中的操作变得不可能。
     不过，在 hive 中有一个很好的操作手法： insert overwrite , 可以将 account_info_history 先全部清除，然后再进行完整的插入操作。

4、在 hive 创建拉链表需要避免的坑
     在 hive 中创建拉链表时，不必要也不能创建分区表。

5、在 hive 中创建一个典型的拉链表的案例。
     以前例子中的两个表作为基础，还是延续上述的两张表，不过建表语句需要进行修改，建表语句如下：

• 账户信息历史情况表（account_info_history),表结构如下：

点击(此处)折叠或打开

create table account_info_history (
   account_id                 string,
   account_name            string,
   account_cur integer,
   account_balance decimal(18,20)
   start_update_date date,
   last_update_date date
) row format delimited fields terminated '\u001' lines terminated '\n' stored orc location 'dws/account_info_history' ;

• 账户信息变动情况表（account_trans_info)，表结构如下：
  

create table account_trans_info (
     account_id                 string,
     account_name            string,
     account_cur integer,
     account_balance decimal(18,20)
     start_update_date date,
     last_update_date date
) row format delimited fields terminated '\u001' lines terminated '\n' stored orc location 'dws/account_trans_info' ;

     我们可进行如下的操作；
     step 1: 删除 account_trans_info  表中的所有记录，然后插入变更后的数据。

truncate table account_trans_info ;
load data local inpath ‘/tmp/account_trans_info.txt ’ overwrite into table account_trans_info ;

     step 2: 更新 account_info_history 表数据。

INSERT OVERWRITE TABLE dws.account_info_history
    SELECT * FROM
    (
        SELECT A.account_id,
           A.account_name,
           A.account_cur,
           A.account_balance,
           A.start_update_date,
           CASE
               WHEN A.last_update = '2999-12-31' AND B.account_id IS NOT NULL THEN '2021-08-15'
               ELSE A.last_update_date
               END AS last_update_date
           FROM dws.account_info_history AS A
               LEFT JOIN dws.account_trans_info AS B
               ON A.account_id = B.account_id
       UNION
          SELECT C.account_id,
                 C.account_name,
                 C.account_cur,
                 C.account_balance,
                 '2021-08-15' AS start_update_date,
                 '2999-12-31' AS last_update_date
             FROM dws.account_trans_info AS C
        ) AS T