本文通过案例为您介绍如何使用实时计算完成直播数字化运营的视频直播解决方案。

直播数字化运营业务

本文主要关注的是直播数据化运营业务。为您介绍使用实时计算完成对当前直播间运营情况实时的监控,包括热门视频、用户走势等等的。

解决方案

  • 业务目标
    • 全站观看直播总人数以及走势
    • 房间直播总人数以及走势
    • 热门直播房间及主播Top10,分类目主播Top10
  • 数据格式

    参考上面的数据格式,使用APP打点日志作为原始数据进行统计。

    客户端APP向服务器打点日志格式如下:

    字段 含义
    ip 用户端ip
    agent 端类型
    roomid 房间号
    userid 用户id
    abytes 音频码率
    afcnt 音频帧数
    adrop 音频丢帧数量
    afts 音频时间戳
    alat 音频帧端到端延迟
    vbytes 视频码率
    vfcnt 视频帧数
    vdrop 视频丢帧数量
    vfts 视频时间戳
    vlat 视频帧端到端延迟
    ublock 上行卡顿次数
    dblock 下行卡顿次数
    timestamp 打点时间戳
    region 地区

    日志服务内部是半结构化存储,上述数据将展现出来格式如下:

    {
    	"ip": "ip",
    	"agent": "agent",
    	"roomid": "123456789",
    	"userid": "123456789",
    	"abytes": "123456",
    	"afcnt": "34",
    	"adrop": "3",
    	"afts": "1515922566",
    	"alat": "123",
    	"vbytes": "123456",
    	"vfcnt": "34",
    	"vdrop": "4",
    	"vfts": "1515922566",
    	"vlat": "123",
    	"ublock": "1",
    	"dblock": "2",
            "timestamp": "1515922566",
    	"region": "hangzhou"
    }
    
  • SQL编写
    • 全站总人数及走势

      使用每分钟一个窗口统计全站观看人数走势,走势最近的1分钟计算结果就是当前总人数。

      CREATE VIEW view_app_total_visit_1min AS
      SELECT
      	CAST(TUMBLE_START(app_ts, INTERVAL '1' MINUTE) as VARCHAR) as app_ts,
      	COUNT(DISTINCT userid) as app_total_user_cnt
      FROM
      	view_app_heartbeat_stream
      GROUP BY
      	TUMBLE(app_ts, INTERVAL '1' MINUTE);
      
    • 房间直播总人数以及走势

      同样,使用每分钟一个窗口统计房间内观看人数走势,走势最近的1分钟计算结果就是当前房间人数。

      CREATE VIEW view_app_room_visit_1min AS
      SELECT
      	CAST(TUMBLE_START(app_ts, INTERVAL '1' MINUTE) as VARCHAR) as app_ts,
      	roomid as room_id,
      	COUNT(DISTINCT userid) as app_room_user_cnt
      FROM
      	view_app_heartbeat_stream
      GROUP BY
      	TUMBLE(app_ts, INTERVAL '1' MINUTE), roomid;
      
    • 热门直播房间排名

      统计热门直播实际上为首页推荐用户使用,把热门的房间放在首页推广能够获取更多流量和点击。

      CREATE VIEW view_app_room_visit_top10 AS
      SELECT
        app_ts,
        room_id,
        app_room_user_cnt,
        rangking
      FROM
      (
      	SELECT 
      		app_ts,
      		room_id,
      		app_room_user_cnt,
      		ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY app_room_user_cnt desc) AS ranking
      	FROM
      		view_app_room_visit_1min
      ) WHERE ranking <= 10;
      
    • 分类目统计热门直播房间排行

      为了最大程度活跃用户社群,覆盖更多场景直播频道,最终实现平台方盈利,通常同一个视频直播网站会开设大量的不同频道,以迎合各类不同人群的观看需求。例如,淘宝直播就会有美妆、男装、汽车、健身等多个不同类目。

      类目和房间关系表是在RDS存储的映射表,里面字段如下:

      CREATE TABLE dim_category_room ( 
      	id	BIGINT,
      	category_id BIGINT,
      	category_name VARCHAR,
      	room_id	BIGINT
        	PRIMARY KEY (room_id), 
        	PERIOD FOR SYSTEM_TIME --定义为维表 
       ) WITH ( 
           type= 'rds', 
           url = 'xxxx',--您的数据库url 
           tableName = 'xxx',--您的表名 
           userName = 'xxx',--您的用户名 
           password = 'xxx'--您的密码 
       );
      

      针对不同的房间,关联类目表,并分类统计各自的排名情况,如下:

      CREATE VIEW view_app_room_visit_1min AS
      SELECT
      	CAST(TUMBLE_START(app_ts, INTERVAL '1' MINUTE) as VARCHAR) as app_ts,
      	roomid as room_id,
      	COUNT(DISTINCT userid) as app_room_user_cnt
      FROM
      	view_app_heartbeat_stream
      GROUP BY
      	TUMBLE(app_ts, INTERVAL '1' MINUTE), roomid;
      
      --关联类目表
      CREATE VIEW view_app_category_visit_1min AS
      SELECT 
      	r.app_ts,
      	r.room_id,
      	d.category_id,
      	d.category_name,
      	r.app_room_user_cnt
      FROM
      	view_app_room_visit_1min r
      JOIN
      	dim_category_room d
      ON
      	r.room_id = d.room_id;
      	
      
      --计算不同类目下房间号排名
      CREATE VIEW view_app_category_visit_top10 AS
      SELECT
        app_ts,
        category_id,
        category_name,
        app_room_user_cnt,
        rangking
      FROM
      (
      	SELECT 
      		app_ts,
      		room_id,
      		category_id,
      		category_name,
      		app_room_user_cnt,
      		ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY app_room_user_cnt desc) AS ranking
      	FROM
      		view_app_category_visit_1min
      ) WHERE ranking <= 10;
      

Demo示例以及源代码

根据上文介绍的视频直播解决方案之直播数字化运营,阿里云团队您创建了一个包含完整链路的Demo示例,如下。

  • 选择csv文件上传至DataHub作为源表。
  • RDS作为维表。
  • RDS作为结果表。

Demo代码完整,您可参考示例代码,注册上下游数据,制定自己的直播数字化运营的解决方案。点击附件进行下载。