查看原文
其他

用 Elasticsearch 统计做了几次核酸检测?怎么破?

铭毅天下 铭毅天下Elasticsearch 2024-03-20

1、两个实战场景问题

事出有因,近期的两个问题比较类似:

  • Q1:如何在 Elasticsearch 实现统计做了 5 次(含以上)核酸检测的人员名单及详情?


  • Q2:请教下大家,业务场景要记录每个人的每天的出勤情况,今天出勤标记为1或者当天日期,未出勤不记录,或者为0,有个个人信息索引,那么这个出勤情况改怎么存储,用数组?还是这种场景不适合es?要实现:查询在某段时间至少出勤几次的人,这个字段目前存的是日期数组,然后我们有需要要查询比如1号到15号,至少出现3次 满足条件的人?

这两个问题本质是一类问题,这类问题涉及技术选型、方案选型、实现细节等问题,本篇文章我们一并讨论一下。

2、关于选型

先看 MySQL 怎么搞!以核酸检测为例,设计两个基础表(以下信息已经全部脱敏处理):

  • 表1:用户基础信息表 user_info
  • 表2:核酸检测信息表 nucleic_test_info

通过两个表关联,然后借助 having 条件判定加上时间条件判定过滤就能找到满足条件的数据。实现方式如下:

select s_id, s_id_number, s_name, nu_check_time from user_info, nucleic_test_info
where user_info.s_id = nucleic_test_info.nu_user_id
and nucleic_test_info.nu_user_id in
(select nu_user_id
from nucleic_test_info 
group by nu_user_id 
having count(nu_user_id) >= 5)
and nu_check_time >= "2022-03-01 00:00:00" and nu_check_time <= "2022-03-31 23:59:59";

用户期望的查询结果如下:

这个问题,如果用 Elasticsearch 会转嫁为两个核心问题:

  • 问题 1:选型问题——如上问题的选型 Elasticsearch 是否合适?
  • 问题 2:如果非要选型 Elasticsearch,那么如何实现上述 MySQL 的业务逻辑呢?

我们先先讨论问题 1。

多表关联是 Mysql 的强项,但是 Elasticsearch 就有些捉襟见肘、力不从心。

选型的时候要注意各取所长,将各个技术栈的优势发挥到极致。

MySQL 支持事务ACID 特性且支持多表关联,但太多表关联会有性能问题,《阿里巴巴Java开发手册》有强调“超过三个表禁止 Join”

Elasticsearch 更擅长大规模数据量级别的全文检索,且ELKB 整合优势对于数据分析也方便快捷。之前的文章咱们分析过:探究 | Elasticsearch 与传统数据库界限也推荐再次读一遍。

选型的细节还有很多综合因素,需要结合业务进行讨论,所以,这里我没有展开。

但,这时候同学可能会有疑问?那类似多表关联问题,Elasticsearch 就搞不定了?

不是的!

Elasticsearch 支持的关联方式核心就如下几大类:

  • 宽表方案
  • nested 嵌套文档实现
  • join 父子文档实现
  • 业务层面自己实现

本文是建立在选型 Elasticsearch 作为核酸检测存储方案的基础上,从数据建模、数据写入、数据检索实现三个维度对不同的实现方案进行拆解剖析。

为方便读者自己动手实战,本文会有大篇幅的 DSL,如有不适感,建议先关注文字描述和截图。

3、Elasticsearch 宽表实现

3.1 宽表 Mapping 建模

宽表的方案本质是“冗余存储”,借助“空间换时间”实现高效检索。

所以,该方案写入数据部分会有大量的冗余个人信息存储。

宽表存储


# 宽表创建索引
PUT nucleic_testing_infos
{
  "mappings": {
    "properties": {
      "s_id_number": {
        "type": "keyword"
      },
      "s_phone": {
        "type": "keyword"
      },
      "s_name": {
        "type": "keyword"
      },
      "s_wx_id": {
        "type": "keyword"
      },
      "s_address": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "nu_check_time": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      },
      "nu_check_addr": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

如上所示:字段中规中矩。

3.2 数据写入

# 宽表导入数据集
PUT nucleic_testing_infos/_bulk
{"index":{"_id":1}}
{"s_id_number":"910105197612304XXX","s_phone":"13655551111","s_name":"王小一","s_address":"京州市丰山区35号","s_wx_id":"wang_01","nu_check_time":"2022-03-01 17:06:10","nu_check_addr":"京州市丰山区核酸检测001号站"}
{"index":{"_id":2}}
{"s_id_number":"910105197612304XXX","s_phone":"13655551111","s_name":"王小一","s_address":"京州市丰山区35号","s_wx_id":"wang_01","nu_check_time":"2022-03-02 17:10:23","nu_check_addr":"京州市丰山区核酸检测002号站"}
{"index":{"_id":3}}
{"s_id_number":"910105197612304XXX","s_phone":"13655551111","s_name":"王小一","s_address":"京州市丰山区35号","s_wx_id":"wang_01","nu_check_time":"2022-03-05 10:10:23","nu_check_addr":"京州市丰山区核酸检测002号站"}
{"index":{"_id":4}}
{"s_id_number":"910105197612305XXX","s_phone":"13655552222","s_name":"张小二","s_address":"京州市海定区002号","s_wx_id":"zhang_02","nu_check_time":"2022-03-02 17:10:33","nu_check_addr":"京州市丰山区核酸检测002号站"}
{"index":{"_id":5}}
{"s_id_number":"910105197612305XXX","s_phone":"13655552222","s_name":"张小二","s_address":"京州市海定区002号","s_wx_id":"zhang_02","nu_check_time":"2022-03-28 17:15:28","nu_check_addr":"京州市丰山区核酸检测002号站"}
{"index":{"_id":6}}
{"s_id_number":"910105197612303XXX","s_phone":"13655553333","s_name":"刘三","s_address":"京州市海定区003号","s_wx_id":"liu_03","nu_check_time":"2022-03-02 17:15:01","nu_check_addr":"京州市海定区核酸检测站003号"}

如上所示,为了保证检索的遍历,个人信息会有大量的“冗余”。

3.3 检索实现

宽表具体的实现

POST nucleic_testing_infos/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "nu_check_time": {
              "gte": "2022-03-01 00:00:00",
              "lte": "2022-03-31 23:59:59"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "terms_aggs": {
      "terms": {
        "field": "s_id_number",
        "size": 10,
        "min_doc_count": 3
      },
      "aggs": {
        "top_hits_aggs": {
          "top_hits": {
            "_source": {
              "includes": [
                "s_id_number",
                "s_phone",
                "s_name",
                "s_address",
                "s_wx_id",
                "nu_check_time",
                "nu_check_addr"
              ]
            },
            "size": 10
          }
        }
      }
    }
  }
}
  • 检索部分实现了 MySQL where 条件子句的功能;
  • 借助于基于身份证号的 terms 分桶聚合实现;
  • 参数:min_doc_count 实现了类似 MySQL having 条件的功能;
  • top_hits 聚合的目的是获取聚合后的详情信息。

4、Elasticsearch 宽表数组方案

既然上面的方案涉及到冗余存储,会有大量的空间浪费。

那自然有同学会想到:“我用数组存储核酸检测时间,地点我不考虑了,不就可以节约存储了”。

行,没问题,你说的都对。

但是实现起来,你看看下面的检索就知道——这也太太太复杂了吧?!

宽表数组形态

4.1 宽表数组方案

DELETE nucleic_testing_infos_array
PUT nucleic_testing_infos_array
{
  "mappings": {
    "properties": {
      "s_id_number": {
        "type": "keyword"
      },
      "s_phone": {
        "type": "keyword"
      },
      "s_name": {
        "type": "keyword"
      },
      "s_wx_id": {
        "type": "keyword"
      },
      "s_address": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "nu_check_time": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      }
    }
  }
}

4.2 宽表数组写入

PUT nucleic_testing_infos_array/_bulk
{"index":{"_id":1}}
{"s_id_number":"910105197612304XXX","s_phone":"13655551111","s_name":"王小一","s_address":"京州市丰山区35号","s_wx_id":"wang_01", "nu_check_time": ["2022-03-01T17:06:10Z", "2022-03-02T17:10:23Z", "2022-03-05T10:10:23Z"]}
{"index":{"_id":2}}
{"s_id_number":"910105197612305XXX","s_phone":"13655552222","s_name":"张小二","s_address":"京州市海定区002号","s_wx_id":"zhang_02","nu_check_time": ["2022-03-02T17:10:33Z", "2022-03-28T17:15:28Z"]}
{"index":{"_id":3}}
{"s_id_number":"910105197612303XXX","s_phone":"13655553333","s_name":"刘三","s_address":"京州市海定区003号","s_wx_id":"liu_03","nu_check_time":["2022-03-02T17:15:01Z"]}

4.3 宽表数组检索实现

POST nucleic_testing_infos_array/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": """
            double amount = doc['nu_check_time'].size();
            boolean flag = false;
            int icount =  0;
            
            String start_time = params.start_time;
            String end_time = params.end_time;
         
            ZonedDateTime start_zdt = ZonedDateTime.parse(start_time);
            ZonedDateTime end_zdt = ZonedDateTime.parse(end_time);
            
            long start_litmemills = start_zdt.toInstant().toEpochMilli();
            long end_litmemills = end_zdt.toInstant().toEpochMilli();
            
            for (item in doc['nu_check_time']) 
            { 
              long litmemills = item.toInstant().toEpochMilli();
              if(litmemills <= end_litmemills && litmemills >= start_litmemills)
              {
                icount++; 
              }
            } 
            if (icount >= params.length)
            {
              flag = true;
            }
            return (amount >= params.length && flag);
               """,
            "lang": "painless",
            "params": {
              "length": 3,
              "start_time": "2022-03-01T00:00:00Z",
              "end_time": "2022-03-31T23:59:59Z"
            }
          }
        }
      }
    }
  }
}

建模、写入不必多说。

着重说一下检索部分,检索部分用脚本实现。

  • 第一:统计了数组大小,数组大小必须的大于我们要求的检索值大小,否则没有意义。
  • 第二:统计各个时间字段是否在给定检索要求的时间范围内,如果在,就加1。
  • 第三:比较时间大小,转成了时间戳处理的方案,否则不好处理,仅字符串的比对会有很大的“瑕疵”。

5、Elasticsearch Nested 嵌套实现

5.1 nested 建模

DELETE nucleic_testing_infos_nested
PUT nucleic_testing_infos_nested
{
  "mappings": {
    "properties": {
      "s_id_number": {
        "type": "keyword"
      },
      "s_phone": {
        "type": "keyword"
      },
      "s_name": {
        "type": "keyword"
      },
      "s_wx_id": {
        "type": "keyword"
      },
      "s_address": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "check_time_flatted":{
         "type": "date"
      },
      "check_in": {
        "type": "nested",
        "properties": {
          "nu_check_time": {
            "type": "date",
             "copy_to": "check_time_flatted"
          },
          "nu_check_addr": {
            "type": "text",
            "analyzer": "ik_max_word",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          }
        }
      }
    }
  }
}

这里必须强调的一点是:Nested 中元素的遍历非常“头痛”,“谁碰谁知道”。

所以这里通过“曲线救国“实现,将复杂的 Nested 数组问题借助 copy_to 拉平存储。

这点用过后会发现这个方案的巧妙之处。

思路参考:

https://stackoverflow.com/questions/64447956/how-to-iterate-through-a-nested-array-in-elasticsearch-with-filter-script

Nested 嵌套文档存储

Nested 嵌套文档建模推荐阅读:


Elasticsearch Nested 选型,先看这一篇!


干货 | Elasticsearch Nested类型深入详解


干货 | Elasticsearch Nested 数组大小求解,一网打尽!

5.2 Nested 写入数据

PUT nucleic_testing_infos_nested/_bulk
{"index":{"_id":1}}
{"s_id_number":"910105197612304XXX","s_phone":"13655551111","s_name":"王小一","s_address":"京州市丰山区35号","s_wx_id":"wang_01","check_in":[{"nu_check_time":"2022-03-01T17:06:10Z","nu_check_addr":"京州市丰山区核酸检测001号站"},{"nu_check_time":"2022-03-02T17:10:23Z","nu_check_addr":"京州市丰山区核酸检测002号站"},{"nu_check_time":"2022-03-05T10:10:23Z","nu_check_addr":"京州市丰山区核酸检测002号站"}]}
{"index":{"_id":2}}
{"s_id_number":"910105197612305XXX","s_phone":"13655552222","s_name":"张小二","s_address":"京州市海定区002号","s_wx_id":"zhang_02","check_in":[{"nu_check_time":"2022-03-02T17:10:33Z","nu_check_addr":"京州市丰山区核酸检测002号站"},{"nu_check_time":"2022-03-28T17:15:28Z","nu_check_addr":"京州市丰山区核酸检测002号站"}]}
{"index":{"_id":3}}
{"s_id_number":"910105197612303XXX","s_phone":"13655553333","s_name":"刘三","s_address":"京州市海定区003号","s_wx_id":"liu_03","check_in":[{"nu_check_time":"2022-03-02T17:15:01Z","nu_check_addr":"京州市海定区核酸检测站003号"}]}

5.3 Nested 检索实现

POST nucleic_testing_infos_nested/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "lang": "painless",
              "inline": """
              int icount = 0;
              int totalCount = 3;
              String start_time = '2022-03-01T00:00:00Z';
              String end_time = '2022-03-31T23:59:59Z';
         
              ZonedDateTime start_zdt = ZonedDateTime.parse(start_time);
              ZonedDateTime end_zdt = ZonedDateTime.parse(end_time);
            
              long start_litmemills = start_zdt.toInstant().toEpochMilli();
              long end_litmemills = end_zdt.toInstant().toEpochMilli();
              for (item in doc['check_time_flatted']) 
              { 
                long litmemills = item.toInstant().toEpochMilli();
                if(litmemills <= end_litmemills && litmemills >= start_litmemills)
                {
                  icount++; 
                }
              } 
              if(icount >= totalCount)
              {
                 return true;
              }
              """
            }
          }
        }
      ]
    }
  }
}

检索的时候,基本就是照搬宽表数组的实现方案,不再赘述。

缺点:更新数据是更新的整篇文档,不是子文档独立更新。

而核酸检测的数据本质是:更新核酸检测时间信息,也就是只更新子文档就可以。

6、Join 父子文档实现

6.1 join 父子文档建模

DELETE nucleic_testing_infos_join
PUT nucleic_testing_infos_join
{
  "mappings": {
    "properties": {
      "s_id_number": {
        "type": "keyword"
      },
      "s_phone": {
        "type": "keyword"
      },
      "s_name": {
        "type": "keyword"
      },
      "s_wx_id": {
        "type": "keyword"
      },
      "s_address": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "my_join_field": {
        "type": "join",
        "relations": {
          "user": "nucleic_test"
        }
      },
      "nu_check_time": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      },
      "nu_check_addr": {
        "type": "text",
        "analyzer": "ik_max_word",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      }
    }
  }
}


父子文档建模


Join 类型建模参考:Elasticsearch 6.X 新类型Join深入详解

6.2 Join 父子建模批量导入数据

PUT nucleic_testing_infos_join/_doc/1?refresh
{
  "s_id_number": "910105197612304XXX",
  "s_phone": "13655551111",
  "s_name": "王小一",
  "s_address": "京州市丰山区35号",
  "s_wx_id": "wang_01",
  "my_join_field": {
    "name": "user"
  }
}

PUT nucleic_testing_infos_join/_doc/2?refresh
{
  "s_id_number": "910105197612305XXX",
  "s_phone": "13655552222",
  "s_name": "张小二",
  "s_address": "京州市海定区002号",
  "s_wx_id": "zhang_02",
  "my_join_field": {
    "name": "user"
  }
}

PUT nucleic_testing_infos_join/_doc/3?refresh
{
  "s_id_number": "910105197612303XXX",
  "s_phone": "13655553333",
  "s_name": "刘三",
  "s_address": "京州市海定区003号",
  "s_wx_id": "liu_03",
  "my_join_field": {
    "name": "user"
  }
}

PUT nucleic_testing_infos_join/_doc/4?routing=1
{
  "nu_check_time": "2022-03-01 17:06:10",
  "nu_check_addr": "京州市丰山区核酸检测001号站",
  "my_join_field": {
    "name": "nucleic_test",
    "parent": "1"
  }
}

PUT nucleic_testing_infos_join/_doc/5?routing=1
{
  "nu_check_time": "2022-03-02 17:10:23",
  "nu_check_addr": "京州市丰山区核酸检测002号站",
  "my_join_field": {
    "name": "nucleic_test",
    "parent":  "1"
  }
}

PUT nucleic_testing_infos_join/_doc/6?routing=1
{
  "nu_check_time": "2022-03-05 10:10:23",
  "nu_check_addr": "京州市丰山区核酸检测002号站",
  "my_join_field": {
    "name": "nucleic_test",
    "parent":  "1"
  }
}

PUT nucleic_testing_infos_join/_doc/7?routing=2
{
  "nu_check_time": "2022-03-02 17:10:33",
  "nu_check_addr": "京州市丰山区核酸检测002号站",
  "my_join_field": {
    "name": "nucleic_test",
    "parent":  "2"
  }
}

PUT nucleic_testing_infos_join/_doc/8?routing=2
{
  "nu_check_time": "2022-03-28 17:15:28",
  "nu_check_addr": "京州市丰山区核酸检测002号站",
  "my_join_field": {
    "name": "nucleic_test",
    "parent":  "2"
  }
}


PUT nucleic_testing_infos_join/_doc/9?routing=3
{
  "nu_check_time": "2022-03-02 17:15:01",
  "nu_check_addr": "京州市海定区核酸检测站003号",
  "my_join_field": {
    "name": "nucleic_test",
    "parent": "3"
  }
}

6.3  Join 父子建模检索

POST nucleic_testing_infos_join/_search
{
  "query": {
    "has_child": {
      "type": "nucleic_test",
      "min_children": 3,
      "max_children": 10,
      "query": {
        "range": {
          "nu_check_time": {
            "gte": "2022-03-01 00:00:00",
            "lte": "2022-03-31 23:59:59"
          }
        }
      }
    }
  }
}

父子文档的检索实现相比其他几种方案都要短不少。

实现方面有两个核心参数需要强调:

  • 参数1:min_children, max_children 最小孩子数以及最大孩子数。这是7.X 版本才有的特性。方面统计父文档下子文档数量多少。

  • 参数2:range 区间范围检索,用于过滤子文档的时间是否在检索要求的时间范围内。

7、 小结

除了MySQL 和 Elasticsearch,相关问题必然还会有其他实现方式,本文没有做全量覆盖。而仅就关系型数据库 MySQL 和 大数据全文检索引擎 Elasticsearch 为例展开讨论。

综上四种方案,父子文档相对灵活,应是选型中优先选择的。方案的对比如下:

如果有不同的建模建议,也欢迎留言交流讨论。

推荐

1、重磅 | 死磕 Elasticsearch 方法论认知清单(2021年国庆更新版)2如何从0到1打磨一门 Elasticsearch 线上直播课?(口碑不错)3、如何系统的学习 Elasticsearch ?4、干货 | Elasticsearch多表关联设计指南

更短时间更快习得更多干货!

和全球近 1600+ Elastic 爱好者一起精进!


比同事抢先一步学习进阶干货!
继续滑动看下一个
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存