【用户画像】功能实现值写入ClickHouse人群包、预估和更新分群人数，NoSQL数据库介绍

一 写入ClickHouse人群包

1 组合查询Bitmap表SQL代码实现

第一步插入数据工作已经完成，现进行第二步，需要根据筛选条件进行计算，具体工作如下图：

（1）SQL语句分析

select
bitmapToArray(
bitmapAnd( 
  bitmapAnd(
    (select groupBitmapMergeState(us) from user_tag_value_string where tag_code='TG_BASE_PERSONA_GENDER' and tag_value='男' and dt='2022-10-16')
      ,
    (select groupBitmapMergeState(us) from user_tag_value_string where tag_code='TG_BASE_PERSONA_AGEGROUP' and tag_value in ('90后','80后','70后') and dt='2022-10-16')
  ),
  (select groupBitmapMergeState(us) from user_tag_value_long where tag_code='TG_TG_BEHAVIOR_ORDER_LAST30d_CT' and tag_value>=1 and dt='2022-10-16')
)
    )
1
2
3
4
5
6
7
8
9
10
11

在页面端，创建分群中想要选择条件，点击保存就能将大概人数估计出来，需要将以上SQL拼接出来，传递进去，然后执行。

其中子查询中的变量为

• 表名
  • 需要知道标签的值类型：去找标签编码，然后去查询标签的定义，最后得到标签的值类型 TagCondition.tag_code。
• 标签编码 TagCondition.tag_code。
• 标签值
  • 值 TagCondition.tag_values。
  • 是否存在单引号 TagCondition.tag_code。
  • 是否有多值，( , , , ) TagCondition.tag_values.size。
• 操作符（=，<）
  • 把英文转为操作符，eq ==》 =，it ==》 < TagCondition.operator。
• 业务日期
  • 业务日期从页面提取 没有现成的，需要从外部传进来。

完整SQL查询的拼接为bitmapAnd( bitmapAnd( bitmapAnd(sql1,sql2),sql3),sql4)

（2）实现思路

将所有的标签定义全部查找出来，然后放入到堆内存（变量）map中，如果是List则需要根据tag_code依次去List中进行遍历，时间复杂度为O(N)。map中的K存储tag_code，V存储tag_info。

（3）实现过程

controller层

增加方法

/**
 * 生成分群列表
 * @param userGroup
 * @return
 */
@PostMapping("/user-group")
public String genUserGroup(@RequestBody UserGroup userGroup){
    userGroupService.genUserGroup(userGroup);
    return "success";
}
1
2
3
4
5
6
7
8
9
10

service层

在接口中增加方法

public interface UserGroupService  extends IService<UserGroup> {

    public void genUserGroup(UserGroup userGroup);
}
1
2
3
4

在实现类中编写

@Service
@Slf4j
@DS("mysql")
public class UserGroupServiceImpl extends ServiceImpl<UserGroupMapper, UserGroup> implements UserGroupService {

    // 以获取全部tagInfo标签的map
    @Autowired
    TagInfoService tagInfoService;

    @Override
    public void genUserGroup(UserGroup userGroup){

        // 1 写入mysql人群的基本定义
        // （1）补充condition_json_str空白字段
        List<TagCondition> tagConditions = userGroup.getTagConditions();
        // 转成json串
        String conditionJson = JSON.toJSONString(tagConditions);
        // 再放回数据库中
        userGroup.setConditionJsonStr(conditionJson);
        // （2）补充condition_commen空白字段，将json串转成一个json说明
        userGroup.setConditionComment(userGroup.conditionJsonToComment());
        // （3）补充create_time空白字段
        userGroup.setCreateTime(new Date());
        // （4）user_group_num和update_time先不处理
        // 调用父类方法
        super.saveOrUpdate(userGroup);
        // 2 写入ClickHouse人群包
        // 全部条件都存储在列表中，一个条件对应list中的一个
        // 所以想生成一个子查询，依据就是集合中的一个condition
        // 获取全部tagInfo标签的map，在TaskInfoServiceImpl中实现
        // K为TagCode，V为TagInfo
        // （1）组合查询Bitmap表完整SQL
        Map<String, TagInfo> tagInfoMapWithCode = tagInfoService.getTagInfoMapWithCode();
        String bitAndSQL = getBitAndSQL(userGroup.getTagConditions(),tagInfoMapWithCode,userGroup.getBusiDate());
        System.out.println(bitAndSQL);

        // 3 人群包（包含所有uid）以应对高QPS访问
        // redis（bitmap/set）
    }

    // 获取完整bitAndSQL语句
    public String getBitAndSQL(List<TagCondition> tagConditionList,Map<String,TagInfo> tagInfoMap,String busiDate){
        String sql="";
        for (TagCondition tagCondition : tagConditionList) {
            String conditionSQL = getConditionSQL(tagCondition,tagInfoMap,busiDate);
            if(sql.length() == 0){
                sql = conditionSQL;
            }else{
                sql = " bitmapAnd(" + sql + "," + conditionSQL + ")";
            }
        }
        return "select " + sql;
    }

    // 2.1 对应2 获取单个condition，生成子查询
    public String getConditionSQL(TagCondition tagCondition,Map<String,TagInfo> tagInfoMap,String busiDate){
        // (1) 获取K，根据K找V
        TagInfo tagInfo = tagInfoMap.get(tagCondition.getTagCode());
        String tagValueType = tagInfo.getTagValueType();
        // (2) 获取表名
        String tableName = null;
        if(tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_STRING)){
            tableName = "user_tag_value_string";
        } else if(tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_LONG)){
            tableName = "user_tag_value_long";
        } else if(tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_DECIMAL)){
            tableName = "user_tag_value_decimal";
        } else if(tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_DATE)){
            tableName = "user_tag_value_date";
        }
        // （3）获取值类型
        String tagValueSQL = getTagValueSQL(tagCondition.getTagValues(), tagValueType);
        // （4）将操作符转换
        String operatorSQL = getConditionOperator(tagCondition.getOperator());
        // （5）拼接SQL
        String conditionSQL = "(select groupBitmapMergeState(us) from " + tableName +
                " where tag_code='" + tagCondition.getTagCode() +
                "' and tag_value " + operatorSQL + " " + tagValueSQL + " " +
                "and dt = '" + busiDate + "')";

        return conditionSQL;
    }

    // 2.2 对应2.1 中（3）
    // 获取SQL中的值部分
    public String getTagValueSQL(List<String> valueList,String tagValueType){
        // 判断是否要单引，是否为多值
        // ('90后','80后','70后')
        boolean needQuote = needStringQuote(tagValueType);
        String tagValueSQL = null;
        if(needQuote){
            tagValueSQL =  " '" + StringUtils.join(valueList,"','") + "' ";
        }else{
            tagValueSQL =  StringUtils.join(valueList,",");
        }
        if(valueList.size() > 1){
            tagValueSQL = "(" + tagValueSQL + ")";
        }
        return tagValueSQL;
    }

    // 2.3 对应2.2
    // 判断是否需要加单引
    public boolean needStringQuote(String tagValueType){
        if(tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_LONG) || tagValueType.equals(ConstCodes.TAG_VALUE_TYPE_DECIMAL)){
            return false;
        } else {
            return true;
        }
    }

    // 对应2.1中（4） 把中文的操作代码转换为判断符号
    private  String getConditionOperator(String operator){
        switch (operator){
            case "eq":
                return "=";
            case "lte":
                return "<=";
            case "gte":
                return ">=";
            case "lt":
                return "<";
            case "gt":
                return ">";
            case "neq":
                return "<>";
            case "in":
                return "in";
            case "nin":
                return "not in";
        }
        throw  new RuntimeException("操作符不正确");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

Taginfo实现类

使用下面方法得到service中的map

public Map<String,TagInfo> getTagInfoMapWithCode(){
    // 获取全部list
    List<TagInfo> tagInfoList =  super.list();
    Map<String,TagInfo> tagInfoMap=new HashMap<>();
    // 用循环将list转成map
    for (TagInfo tagInfo : tagInfoList) {
        tagInfoMap.put(tagInfo.getTagCode(),tagInfo);
    }
    return tagInfoMap;
}
1
2
3
4
5
6
7
8
9
10

mapper层

@DS("mysql")
@Mapper
public interface UserGroupMapper extends BaseMapper<UserGroup> {
}
1
2
3
4

2 人群包代码实现

目的是查出此分群包中包含什么人，人群包的唯一标识是分群ID，所以人群包建表需要内容如下：

• 包含字段
  • 分群id
  • 分群uid集合，可以直接存储为bitmap
• 引擎：AggregatingMergeTree
• 分区：使用分群id进行分区，这样一个分群一个分区，目的是当某个分群的集合发生了变化，造成分区的重建时，不会影响其他的分群
• 排序：分群id

建表语句如下：

CREATE TABLE  user_group
(
    `user_group_id` String,
    `us` AggregateFunction(groupBitmap, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY user_group_id
ORDER BY user_group_id;
1
2
3
4
5
6
7
8

（1）配置文件

spring.datasource.dynamic.datasource.clickhouse.url=jdbc:clickhouse://hadoop101:8123/user_profile1009
spring.datasource.dynamic.datasource.clickhouse.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
1
2

（2）UserGroupMapper

@Insert("${sql}")
@DS("clickhouse")
public void insertSQL(String insertBitSQL);

@Select("select bitmapCardinality(us) ct from user_group where user_group_id=#{userGroupId}")
@DS("clickhouse")
public Long userGroupCount(@Param("userGroupId") String userGroupId);
1
2
3
4
5
6
7

（3）UserGroupServiceImpl实现类

添加方法

// 从已有人群包中，查询人群个数 对应2 （4） a）
public Long getUserGroupCount(String userGroupId){
    Long userGroupCount = super.baseMapper.userGroupCount(userGroupId);
    return userGroupCount;
}

// 获得插入的SQL语句 对应2 （3）
public  String getInsertBitSQL(String userGroupId,String bitAndSQL){
    String insertSQL="insert into user_group  select '"+userGroupId+"' ,"+bitAndSQL+" as  us";
    return insertSQL;
}
1
2
3
4
5
6
7
8
9
10
11

进行调用

//（3）insert into  + 查SQL
String insertBitSQL = getInsertBitSQL(userGroup.getId().toString(), bitAndSQL);

// 执行语句
// 父类已经装配好了mapper 直接使用即可
super.baseMapper.insertSQL(insertBitSQL);

// （4）更新人群包的人数
// a）从已有人群包中，查询人群个数
Long userGroupCount = getUserGroupCount(userGroup.getId().toString());

// b）更新MySQL分群基本信息中的人数，使用mabatis-plus方法
userGroup.setUserGroupNum(userGroupCount);
super.saveOrUpdate(userGroup);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

二 预估分群人数

预估分群人群思路如下图：

三 更新分群人数

更新分群人数思路如下图：

四 NoSQL数据库

1 NoSQL数据库简介

（1）概述

NoSQL(NoSQL = Not Only SQL )，意即 ”不仅仅是SQL“，泛指非关系型的数据库。

NoSQL 不拘泥于关系型数据库的设计范式，放弃了通用的技术标准，为某一领域特定场景而设计，从而使性能、容量或者扩展性都打到了一定程度的突破。

• 不遵循SQL标准。

  是一个巨大的牺牲，学习成本，人力成本很高，所有的语法都需要学习。

• 不支持ACID（事务）。

• 远超于SQL的性能。

（2）NoSQL适用场景

性能快，容量大，扩展性高。

（3）NoSQL不适用场景

需要事务支持、基于sql的结构化查询存储，处理复杂的关系，需要即席查询、用不着sql的和用了sql也不行的情况，请考虑用NoSql。

2 NoSQL家族

（1）Memcache（内存数据库）

很早出现的NoSql数据库。

数据都在内存中，一般不持久化。

持久化：即备份写磁盘。

支持简单的key-value模式，支持类型单一。

KV模式中的K一定是String，类型单一指的是V，Memcache只有String，即一个String对应一个String，一个K只能存储一个值。

不过通过一些转换操作，一个K也可以存储多个值，如将集合变为字符串（JSON）。

一般是作为缓存数据库辅助持久化的数据库。

（2）Redis（内存数据库）

几乎覆盖了Memcached的绝大部分功能。

数据都在内存中，支持持久化，主要用作备份恢复。

MySQL和Hbase都支持写磁盘且使用内存，那么Redis和这两者的区别是什么？

当内存和磁盘中的数据一致时，分不清谁是内存数据库谁是磁盘数据库，当内存和磁盘中的数据不一致时，磁盘数据库以磁盘中的数据为主，这种数据库称为标准的持久化数据库，而内存数据库则以内存中的数据为主，即使是内存数据少，磁盘数据多的情况，如刚刚执行删除操作，删除操作还没有刷到磁盘，磁盘多是作为备份恢复的作用，只有当内存不能使用时，如宕机，才会使用磁盘。

内存数据库中内存的数据是最准确的，任何数据的访问请求都不会第一时间去查磁盘或者写磁盘，会进行周期型的备份。

如果在内存中找不到相应的数据，MySQL和Hbase会查磁盘，而Redis不会去查磁盘，直接返回异常信息。

除了支持简单的key-value模式，还支持多种数据结构的存储，比如 list、set、hash、zset等。

一个K可以存储多个值。

一般是作为缓存数据库辅助持久化的数据库。

一般都是将数据存放到MySQL中，使用Redis作为缓存，其中新浪微博将所有的数据全部存放到Redis中，读写性能大大提高，代价就是成本较高。

（3）Mongodb（文档数据库）

对大数据领域来说，此数据库大多作为一个数据源。

存储的数据结构一般为K-JSON结构，存储结构相对简单，三范式的数据就相对复杂了，表会非常多，Mongodb中的表很少去和其他表进行关联，数据量非常大，不强调关系，可能存储用户与客户的聊天记录、社交中的问答，评论等。

速度介于MySQL和Redis之间。

高性能、开源、模式自由(schema free)的文档型数据库。

数据都在内存中， 如果内存不足，把不常用的数据保存到硬盘。

虽然是key-value模式，但是对value（尤其是json）提供了丰富的查询功能。

支持二进制数据及大型对象。

可以根据数据的特点替代RDBMS ，成为独立的数据库。或者配合RDBMS，存储特定的数据。

（4）Hbase（列式数据库）

HBase是Hadoop项目中的数据库。它用于需要对大量的数据进行随机、实时的读写操作的场景中。

HBase的目标就是处理数据量非常庞大的表，可以用普通的计算机处理超过10****亿行数据，还可处理有数百万**列元素的数据表。

（5）Cassandra（列式数据库）

Apache Cassandra是一款免费的开源NoSQL数据库，其设计目的在于管理由大量商用服务器构建起来的庞大集群上的海量数据集(数据量通常达到PB级别)。在众多显著特性当中，Cassandra最为卓越的长处是对写入及读取操作进行规模调整，而且其不强调主集群的设计思路能够以相对直观的方式简化各集群的创建与扩展流程。

3 DB-Engines数据库排名

国外数据库排行榜