经验记录-上亿数据量操作与最长匹配

一．用户需求：

  统计一批号码21年在指定业务的结算费用、条数、用户数。

二．提数思路：

1. 循环2021年1到12月表，在源表按指定条件抽取1到11月的号码、费用、月份字段，存入TMP表PHONE_NO, FEE, YEAR_MONTH；备注：当前为12月还未进行结算，故数据仅统计到11月，11月数据有部分在12月表中，故需要循环12个月。
2. 由于一个号码在一个月中有多条数据，所以对TMP表数据按月按号码分组汇总，并统计条数cnt，得到TMP0表。备注：TMP约8亿，TMP0约5亿数据量。
3. 用户提供的国家编码入库，生成COUNTRY表，去空格去重复数据。
4. TMP0数据根据号码，与COUNTRY表进行匹配（无匹配国家的数据直接插入），TMP0增加国家名称字段生成TMP1表。这里的难点在于，需要按最长匹配原则，即号码字段从第3位起，取一定长度的字符串与国家编码进行匹配。举例：国家编码有16、1633两行，TMP0表存在号码“001633123456”（号码结构为：00+编码+号码，编码和号码长度不定），则该号码只匹配1633对应的国家，不再匹配16。
5. TMP1数据按月汇总费用、条数、用户数，生成RES表。明细数据保留一周，应对用户对明细相关数据的需求。
6. 比对RES和TMP，确认数据处理过程无误。
7. RES表数据导出为文件。

三．编写脚本和存在问题：

提数思路转换为Shell脚本，有以下几部分：
  (1) drop_tab：各表若存在，则删除。脚本重复执行的准备。
  (2) create_tab：表若不存在，则创建。脚本重复执行的准备。注意TMP1分区。

  (3) get_phone：从源表抽取数据到TMP表，11个月数据均汇总到该表。
  遇到问题：TMP最开始是建的月表，因为考虑到一个月数据量约7-8kw，11个月数据如果放在一个表，怕后续匹配时候跑不动。给前辈看了，说8亿数据是可以处理的没有必要分月，这里是日常使用ORACLE习惯了分月处理数据，还不适应大数据量的操作。

  (4) get_group：对TMP数据按月、按号码分组汇总生成TMP0。
  遇到问题：最开始的想法中是没有这个步骤的，直接用TMP表数据匹配国家，但是忽略了一个月一个号码有多条数据的情况，并且国家表未做去空格和去重复处理，导致产生笛卡尔积，一直跑不出来，后来才发现国家数据有重复，才做了处理。
  另外，一开始是对金额字段直接做单位转换了，但是表字段为int类型，导致小于1元的小数统计全部为0，因此将RES金额改为double类型，并把单位转换放到了最后的汇总阶段，也可以减少数据误差。不过最终总结的时候，还是觉得在COUNTRY表数据唯一时，这一步是可有可无的。

  (5) ins_country：TMP0与COUNTRY表关联，匹配号码对应国家名称，插入到TMP1。
  遇到问题：“最长匹配”是该需求的难点，想了很久没有想到一次性匹配的解决办法，所以采取的解决方案是循环匹配。
  首先，对COUNTRY编码位数CODE_LEN做统计，比如这里是1-6位，就从6到1进行循环匹配；由于最长匹配后就不能再重复匹配其他国家编码，所以这里需要增加判断不存在TMP1的才进行INSERT。而TMP1，按CODE_LEN进行分区，考虑到可能该步骤可能单独重新执行，所以对分区加了DROP PARTITION和INSERT OVERWRITE，对分区进行初始化（这里是慎重起见，删分区和重写都写进去了，实际二选一就行）。
  这里用到分区的原因，一是需要将CODE_LEN作为参数，因为Hive是不支持SUBSTR(A.SEND_DN,3,LENGTH(B.INTER_LONG_CODE)=B.INTER_LONG_CODE这种写法的；二是考虑到重复处理某分区时，可以进行分区初始化操作，因为无法执行DELETE嘛。

  (6) ins_no_country：未匹配到国家的数据，直接插入TMP1。
  虽然该部分数据为0，但是我认为应该考虑到这一步。

  (7) ins_res：将TMP1数据按月、国家汇总统计。
  遇到问题：SUM(IN_SETT_FEE)/100000和SUM(IN_SETT_FEE/100000)是有误差的，所以导致和TMP表汇总对不上，小数处理应该考虑到误差情况。

  (8) main部分：按逻辑调用以上部分；导出RES结果到文件。

四．总结

1. “最长匹配”的实现方案之一：将位数作为循环值，从最大倒序循环匹配。该长度值无法用SUBSTR(A.SEND_DN,3,LENGTH(B.INTER_LONG_CODE)=B.INTER_LONG_CODE实现，因此对LENGTH(B.INTER_LONG_CODE)的值先进行统计，直接作为循环值。当然，除了采用分区，也可以采用在B表增加CODE_LEN实现；但是如果B表为子查询：(SELECT *, LENGTH(INTER_LONG_CODE) AS CODE_LEN FROM COUNTRY_LONG_CODE) B这种写法，还是和上面写法一样报错。
2. 涉及较多小数处理时，尽量“先汇总再四舍五入”，避免“先四舍五入再汇总”导致差异增大。
3. 编写前一定要先把处理逻辑理清楚，不能一边想一边写，容易写着写着就逻辑混乱。
4. 脚本编写需要考虑到脚本重复执行的情况。建表删表都进行IF EXISTS判断，避免报错。OVERWRITE使用时需要注意下，避免对某表多次重写，导致数据被覆盖。
5. Hive处理的数据量级是非常大的，可以慢慢适应，同时还需要学习语句优化提升效率。