V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
zdking08135
V2EX  ›  程序员

请教一个系统设计题

  •  
  •   zdking08135 ·
    zd08135 · 12 天前 · 2546 次点击
    一个系统,客户端会上报统计点,上报本身不去重,报一次记一次:
    uid ,地域(城市省份国家),打点时间(时间戳)。

    这里定位服务保证地域唯一。

    要求实现如下能力:

    - 统计指定地域(最小城市维度,省份,国家也可以)与时间范围(天维度)内的单 uid 单日平均打点数
    (结果是按天输出,每天一个指定地域平均数)。

    - 查询支持实时 & 支持输入。

    - 时间范围,最长 1 年。

    - 地域支持多地域 or 查询,但是多地域出现的 uid 需要合并统计。(比如,用户同一天在 A,B 两地上报,如果查询条件为 A or B ,那么这个用户的打点数要合并计算)

    指标:用户量 10 亿,单 uid 单日打点数 10~50 之间,城市范围覆盖到全球。
    第 1 条附言  ·  12 天前
    这里细化一点,假设城市、省份、国家就用 id 表示(城市不需要模糊查询),并且范围包括全世界。

    希望给出更多的细节,表怎么组织,数据怎么组织,怎么做查询。
    22 条回复    2024-04-04 13:31:34 +08:00
    wei2629
        1
    wei2629  
       12 天前
    找个时序数据库
    lsk569937453
        2
    lsk569937453  
       12 天前   ❤️ 2
    ## 假设
    - 存储每天 10 亿*50(次)*100byte=5TB(存储量太大,上 hbase 吧)
    - 打点接口最多每秒访问次数:10 亿次
    - 打点接口最多每秒占用的网络带宽:10 亿*100byte=100GB(万兆网卡可能不够用了)
    - 单中心情况下网络延迟:地球上两点间最长距离为 20000 公里/光速(299792458m/s)=0.066s ,即单次请求的网络延时为 0.066*2=1.3(s)

    ## 系统设计
    - 打点接口收到数据直接异步写入 kafka 集群,假设接口单次处理时间为 0.5ms,则单机 QPS 为 2000,处理 10 亿条数据需要的机器数量为:10 亿/2000=5w 台。
    - 同时我们开线程从 kafka 集群读取数据,格式化后写入 HBase 集群。

    ### 数据库设计


    Hbase 的 rowkey 设计为:地域+时间戳+uuid
    - 统计指定地域:直接地域+时间范围全部查出来即可
    - 地域支持多地域 or 查询:根据查询条件查询出来,将所有的数据写入到 kafka ,然后由 storm/spark/flink 做实时的统计,然后将结果写入到数据库中。
    llsquaer
        3
    llsquaer  
       12 天前   ❤️ 1
    给你们老板说下,别来不来就 10 亿,先 10 万的开始
    thedinosaurmail
        4
    thedinosaurmail  
       12 天前
    clickhouse , 10 亿还好 ,按天分区就行
    zdking08135
        5
    zdking08135  
    OP
       12 天前
    @lsk569937453
    感谢老哥,这里不是每秒 10 亿次,保证一天能抗住 10 亿 * 50 次上报就行了,大约是 60w 的 qps ,这个不是重点。
    重点是怎么支持查询。

    --------------------

    "根据查询条件查询出来,将所有的数据写入到 kafka ,然后由 storm/spark/flink 做实时的统计,然后将结果写入到数据库中。"

    这里,如果想查比如上海+苏州范围,两地一共 2kw 用户,10 亿条记录
    需要把上海和苏州的用户记录数据全部读出来,再写 Kakfa 做统计?
    zdking08135
        6
    zdking08135  
    OP
       12 天前
    @llsquaer 系统设计题啦,不是实际业务,实际肯定会取舍。
    yjhatfdu2
        7
    yjhatfdu2  
       12 天前
    这个量,clickhouse 集群,做好表的设计,选好排序键、字段编码和压缩,按天分区,这个量写入和查询问题应该都不是很大。顺便,兄弟你这是在做天网么
    zdking08135
        8
    zdking08135  
    OP
       12 天前
    @yjhatfdu2
    @thedinosaurmail

    用 clickhouse 的话,具体一点呢?表怎么设计?有哪些字段?
    查询要怎么写?效率如何,可以实时吗?
    thedinosaurmail
        9
    thedinosaurmail  
       12 天前
    直接写 clickhouse 就行 ,不需要怎么设计设计
    uid ,country ,province ,create_at

    主要是要判断好按什么排序就行
    thedinosaurmail
        10
    thedinosaurmail  
       12 天前
    在使用 ClickHouse 进行表的设计时,针对您的需求,我们需要考虑如何优化存储和查询效率,尤其是面对大规模数据和复杂查询(如跨地域合并统计)。以下是一个基于您需求的示例表结构,包括了用户 ID 、打点时间、地域信息和打点数。

    首先,考虑到数据量和查询需求,建议使用 MergeTree 系列引擎,它适用于大数据量的存储和分析,支持高效的数据插入和实时查询。

    表结构设计
    sql
    Copy code
    CREATE TABLE user_events
    (
    `event_date` Date,
    `user_id` UInt64,
    `city_id` UInt32,
    `country_id` UInt32,
    `event_count` UInt32,
    `event_datetime` DateTime
    )
    ENGINE = MergeTree()
    PARTITION BY toYYYYMM(event_date)
    ORDER BY (event_date, country_id, city_id, user_id)
    SAMPLE BY user_id
    SETTINGS index_granularity = 8192;
    字段解释:
    event_date: 打点发生的日期,用于分区和快速过滤。
    user_id: 用户的唯一标识符。
    city_id: 城市的唯一标识符,需要有一个额外的映射表来解释每个城市 ID 对应的实际城市。
    country_id: 国家的唯一标识符,同样需要一个映射表来详细说明。
    event_count: 该用户在该日的打点数,考虑到您的业务场景,可能需要在数据插入前进行聚合计算。
    event_datetime: 打点的具体时间点,支持精确到秒的时间戳,可用于进一步的时间序分析。
    注意事项:
    分区策略:根据 event_date 进行分区,可以有效地管理数据的存储和查询,尤其是对历史数据的分析。
    排序键:通过(event_date, country_id, city_id, user_id)进行排序,优化查询性能,特别是当进行地域和时间范围的查询时。
    采样:通过 SAMPLE BY user_id 支持对数据进行采样查询,适用于需要估算或快速分析的场景。
    索引粒度:index_granularity 设置为 8192 ,这是一个平衡查询速度和存储效率的配置。根据实际数据量和查询模式,这个值可能需要调整。
    多地域查询设计思路:
    对于跨地域的统计分析,可以在查询时通过 GROUP BY 语句实现。例如,如果需要合并计算用户在同一天内不同城市(或国家)的打点数,可以通过将 user_id 和 event_date 作为聚合的关键字,然后对 event_count 求和。
    dlmy
        11
    dlmy  
       12 天前   ❤️ 1
    Log -> Kafka -> Flink ↓
    --> ODS -> DWD -> DWM -> DWS -> ADS ↓
    --> ClickHouse -> API ↓
    --> Visualization Panel

    看得懂这个,你就知道怎么做了
    yjhatfdu2
        12
    yjhatfdu2  
       12 天前   ❤️ 1
    clickhouse 造一天数据试试看,单机 64 核 epyc 256G ram
    建表,目前试下来效率最高的表结构
    create table test4
    (
    time datetime CODEC (DoubleDelta, LZ4),
    country UInt8 CODEC (DoubleDelta, LZ4),
    province UInt8 CODEC (DoubleDelta, LZ4),
    city UInt16 CODEC (DoubleDelta, LZ4),
    uid UInt32
    ) engine = MergeTree() partition by toYYYYMMDD(time)
    order by (time, country, province, city) settings index_granularity = 65536;

    先造 10 亿数据,分布在一天内
    insert into test4
    select dateAdd(second, number / 1000000000, toDateTime('2024-04-02'))
    , rand32() % 200
    , rand32(1) % 250
    , rand32(2) % 100
    , number + 1
    from numbers(1000000000);
    -- 然后扩增到 32 倍
    insert into test4 select * from test4;
    insert into test4 select * from test4;
    insert into test4 select * from test4;
    insert into test4 select * from test4;
    insert into test4 select * from test4;

    SELECT count(*)
    FROM test4

    Query id: a4a01197-a22b-4a0d-9747-26555229ff58

    ┌─────count()─┐
    │ 32000000000 │
    └─────────────┘

    1 row in set. Elapsed: 0.004 sec.
    一共 320 亿
    等后台 merge 完才 14.28 GiB 磁盘占用
    楼主要的查询
    WITH r AS
    (
    SELECT count() AS c
    FROM test4
    WHERE country = 100
    GROUP BY uid
    )
    SELECT avg(c)
    FROM r

    Query id: c634e7a7-13fa-4d40-9f30-e6e43105ffe9

    ┌─avg(c)─┐
    │ 32 │
    └────────┘

    1 row in set. Elapsed: 0.168 sec. Processed 160.30 million rows, 801.18 MB (954.12 million rows/s., 4.77 GB/s.)
    0.168 秒完成

    这样看起来,一年的数据单机也问题不大
    注意,不同的建表语句尤其是 CODEC 非常影响存储空间和性能
    siaronwang
        13
    siaronwang  
       12 天前
    apache drios
    MoYi123
        14
    MoYi123  
       12 天前
    只要想办法把 Euler Tour Tree 存数据库里就行了.
    hefish
        15
    hefish  
       12 天前
    现在就要准备毕业设计啦。。。这么早啊。。。
    1018ji
        16
    1018ji  
       12 天前
    有时间范围只能现算,又不能预聚合,ck doris 之类试试吧
    wu00
        17
    wu00  
       12 天前
    牛批,学习一下
    zdking08135
        18
    zdking08135  
    OP
       11 天前
    @yjhatfdu2

    NB 了,感谢,看来要多研究一下这个软件。
    话说,可以顺便尝试复杂查询?

    比如(city = 100 or city = 101) and date < '2024-04-02' and date > '2024-03-31'
    yjhatfdu2
        19
    yjhatfdu2  
       11 天前
    @zdking08135 当然可以,不过按照这个编码形式,肯定要指定 country
    zzmark06
        20
    zzmark06  
       11 天前 via Android
    就这么点数据,想这么多,又这又那的
    这点量都摸不到 doris/ck 单机瓶颈

    拿 ck 来说,上面给出 ck 表结构的兄弟,@yjhatfdu2 表排序键有问题,排序优先遵循业务必选条件,再根据基数由低到高。建议调整排序顺序为 country,province,city,time 编码方式里,时间去掉 doubledelta ,追求压缩率平衡不用 lz4 ,改用 zstd(1)差不多就这样了。
    你这第一个就是高基数,压缩比会很低,速度上不来

    对列存来说,整分区 count 都是 O(1)消耗的元数据查询,看不出性能

    至于表分区键选用按日还是按月,需要考虑业务平常查询到底按什么的多些。经常跨度大的就改为按月,反之按日。若是业务有按国家为租户的习惯,那分区把国家带上再按月也合理。
    若是还有一些大范围时间内区域统计需求,上 projection 来预计算
    zzmark06
        21
    zzmark06  
       11 天前 via Android
    按题目数据量级,大概算下来,一年大概 18 万亿行,磁盘空间应该在 1t 到 2t 之间,写入带宽都喂不满一个单点 ck 配几块机械盘
    不过列存嘛,整体结构参考上面 @dlmy 兄弟的描述
    xueling
        22
    xueling  
       10 天前
    兄弟,用我的开源软件,不能帮你实现所有需求,但是可以帮你实现很多需求,可以实现统计 1 天内、小时级、分钟级的 uv,和各地区的 uv ,支持高并发查询结果。至于地区模糊查询和超过 1 天以上日期的查询可以借助 clickhouse 或离线统计来实现。为什么不建议全部使用 ck ,因为 ck 每次查询都是全量计算,并发查询效率比较低。我的开源项目: https://github.com/xl-xueling/xl-lighthouse.git ,有问题找我~
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2679 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 11:49 · PVG 19:49 · LAX 04:49 · JFK 07:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.