视频元数据的大规模存储：从CDN日志到用户行为分析的数据管道

视频元数据的大规模存储从CDN日志到用户行为分析的数据管道一、视频平台的元数据爆炸一个视频100个维度一个视频在B站或YouTube上不只是视频文件本身还附带着爆炸级的元数据内容元数据标题、简介、标签、分类、封面图、字幕质量元数据分辨率(1080P/4K/8K)、码率、编码格式(H.264/AV1)、帧率分发元数据CDN节点、缓存命中率、首屏时间、卡顿率行为元数据播放量、完播率、弹幕密度曲线、点赞/投币/收藏推荐元数据Embedding向量、兴趣标签权重、协同过滤分数以B站为例每天约500万个新视频上传每个视频约200条元数据单日元数据增长就是10亿行。而分析需求是千奇百怪的运营要过去7天投稿量Top10分区、推荐算法要用户最近50个视频的完播率序列、CDN团队要哪些视频的卡顿率5%。二、Lambda架构下的视频元数据管道三、核心表结构与查询实现视频基础信息MySQL表CREATE TABLE videos ( video_id BIGINT PRIMARY KEY, bvid VARCHAR(32) NOT NULL UNIQUE, uploader_id BIGINT NOT NULL, title VARCHAR(256) NOT NULL, description TEXT, duration_sec INT, category_id INT, tags JSON, resolution ENUM(360P,480P,720P,1080P,4K,8K), codec VARCHAR(32), cover_url VARCHAR(512), status ENUM(UPLOADING,TRANSCODING,PUBLISHED,HIDDEN,DELETED), publish_time DATETIME, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_uploader_time (uploader_id, publish_time), INDEX idx_category_time (category_id, publish_time) ) ENGINEInnoDB;ClickHouse播放行为表CREATE TABLE playback_events ON CLUSTER video_cluster ( event_id UUID, video_id UInt64, user_id UInt64, event_time DateTime64(3), event_type LowCardinality(String), -- play/pause/seek/complete position_sec Float32, -- 当前播放位置 watched_sec Float32, -- 本次观看时长 quality LowCardinality(String), device_type LowCardinality(String), session_id String ) ENGINE ReplicatedMergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (video_id, event_time, user_id) TTL event_time INTERVAL 90 DAY;播放行为分析查询示例class VideoAnalyticsService: def __init__(self, clickhouse_client, mysql_pool): self.ch clickhouse_client self.mysql mysql_pool def get_video_performance(self, video_id: int, days: int 7) - dict: 获取视频最近N天的表现数据 try: query SELECT countIf(event_type play) AS total_plays, uniqExact(user_id) AS unique_viewers, avgIf(watched_sec, event_type complete) / duration_sec AS avg_completion_rate, countIf(event_type complete) / countIf(event_type play) AS completion_ratio, -- 弹出率观看10秒就关闭 countIf(event_type play AND watched_sec 10) / countIf(event_type play) AS bounce_rate FROM playback_events WHERE video_id %(vid)s AND event_time now() - INTERVAL %(days)s DAY result self.ch.execute(query, { vid: video_id, days: days }) if not result: return None row result[0] return { total_plays: int(row[0]), unique_viewers: int(row[1]), avg_completion_rate: round(float(row[2] or 0), 3), completion_ratio: round(float(row[3] or 0), 3), bounce_rate: round(float(row[4] or 0), 3) } except Exception as e: raise AnalyticsException(f视频分析查询失败: {video_id}, e) def get_real_time_dashboard(self, uploader_id: int) - dict: 创作者实时数据看板 try: # 最近1小时的数据 query SELECT v.video_id, v.title, countIf(e.event_type play) AS plays_1h, uniqExact(e.user_id) AS viewers_1h, countIf(e.event_type like) AS likes_1h, countIf(e.event_type coin) AS coins_1h, countIf(e.event_type danmaku) AS danmaku_1h FROM playback_events e RIGHT JOIN ( SELECT video_id, title FROM mysql(host:port, db, videos, user, pass) WHERE uploader_id %(uid)s ) v ON e.video_id v.video_id WHERE e.event_time now() - INTERVAL 1 HOUR GROUP BY v.video_id, v.title ORDER BY plays_1h DESC LIMIT 20 return self.ch.execute(query, {uid: uploader_id}) except Exception as e: raise AnalyticsException(f实时看板查询失败: {e})四、视频元数据管道的四个工程坑坑一播放事件的去重。同一用户拖动进度条会产生大量seek事件被误计为多次播放。需要在Flink层做会话窗口聚合——30分钟内的多个事件合并为一次播放会话。坑二CDN日志的延迟到达。CDN节点可能因为网络波动在几小时后才上报日志。ClickHouse的MergeTree引擎容忍这种乱序写入但聚合查询需要加上event_time的时间范围过滤避免统计T1数据时混入延迟的T0数据。坑三封禁视频的元数据清理。视频被版权方投诉下架后其所有元数据播放量、弹幕应该保留还是清除保留用于审计但标记为不可见是对外产品需求而AI训练数据需要清除避免推荐模型继续推荐。二者的处理逻辑相反需要数据管道的分流。坑四大V视频的流量倾斜。一个千万粉丝UP主的新视频在发布后1小时内可能有100万次播放。这会导致ClickHouse的单个分区数据量远超平均值查询时可能触发OOM。需要预聚合强力的物化视图将高并发大V的播放数据提前聚合到分钟级。五、总结视频元数据的存储架构是典型的Lambda架构实践MySQL负责基础信息的强一致性CRUDClickHouse负责海量时序行为数据的实时聚合分析HDFSSpark负责推荐特征的离线计算。三者的数据通过video_id关联各司其职。对于中小型视频平台日播放量1亿MySQLClickHouse的组合足以支撑全部分析需求。当播放量突破十亿级时才需要考虑在ClickHouse层做更细粒度的分片如按video_id哈希分布或引入Flink的流式预聚合。本文属于「行业场景与项目复盘」系列深入分析视频平台元数据的Lambda架构存储与实时分析实践。