Sentry Snuba Clickhoue 数据源分析

Clickhouse 库表介绍

Sentry 和数据分析相关的表，只有四张，如下：

• errors_local : 存储所有的 issue 数据
• transactions_local ：存储所有的 trace 、span 数据，以及自定义的 measurements
• sessions_raw_local ：存储 session 的原始数据信息，崩溃率的原始数据
• sessions_hourly_local ：存储 session 的小时粒度的统计聚合信息

分析案例

需求1：以下 SQL 用于实时的分析【启动开屏广告 P95 耗时】

SELECT

    extract(`release`, '[0-9]+\.[0-9]+\.[0-9]') AS `version`,

    (quantile(0.95)(if(has(measurements.key, 'tap_app_start_cold_time'), arrayElement(measurements.value, indexOf(measurements.key, 'tap_app_start_cold_time')), NULL) as fps)) as avg_fps

FROM 

    transactions_local

WHERE

    `start_ts` BETWEEN timestamp_sub(now(), INTERVAL 28 DAY) and now()

    AND fps > 0

    AND `project_id` IN (148)

    AND transaction_op = 'tap.biz.page.load'

    AND version NOT IN ('2.53.1', '2.54.0')

    AND `environment` IN ('rnd','release')

GROUP BY version

需求2：按天持久化【自定义埋点数据】。

针对这个进阶需求，可以采用 Clickhouse 的物化视图功能（CREATE VIEW | ClickHouse Docs  ）对源表进行聚合处理成物化表。然后查询物化图即可。

首先创建一张表，用于存储物化视图的结果

CREATE TABLE overview_daily.`transactions_daily_local`

(

    `daytime` DateTime('Asia/Shanghai'),

    `project_id` UInt64,

    `major_release` String,

    `os` String,

    `area` String,

    `app_start_cold_time_duration_p95` AggregateFunction (quantilesIf (0.5, 0.95), Float64, UInt8),

    `app_start_cold_duration_p95` AggregateFunction (quantilesIf (0.5, 0.95), Float64, UInt8),

    `first_page_duration_p95` AggregateFunction (quantilesIf (0.5, 0.95), Float64, UInt8),

    `gamedetail_page_duration_p95` AggregateFunction (quantilesIf (0.5, 0.95), Float64, UInt8),

    `ui_show_duration_p95` AggregateFunction (quantilesIf (0.5, 0.95), Float64, UInt8)

)

ENGINE = AggregatingMergeTree()

PARTITION BY toMonday(daytime)

ORDER BY (project_id,

 major_release,

 daytime)

SETTINGS index_granularity = 8192;

物化视图创建：

#---- app_start_cold_time_duration_p95 的物化视图

CREATE MATERIALIZED VIEW overview_daily.transactions_daily_mv_app_start_cold_time_duration_p95 to overview_daily.transactions_daily_local AS

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

(quantilesIfState(0.5,0.95)(if(has(measurements.key,'tap_app_start_cold_time'),

arrayElement(measurements.value,

indexOf(measurements.key,

'tap_app_start_cold_time')),

0) as temp_duration,(temp_duration !=4294967295))) as app_start_cold_time_duration_p95

FROM

`default`.transactions_local

WHERE

transaction_op = 'tap.biz.page.load'

    AND `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    and arrayElement(measurements.value,

indexOf(measurements.key,

'tap_app_start_cold_time')) > 0

GROUP BY

major_release,

daytime,

project_id;

#---- app_start_cold_duration_p95 的物化视图

CREATE MATERIALIZED VIEW overview_daily.transactions_daily_mv_app_start_cold_duration_p95 to overview_daily.transactions_daily_local AS

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

(quantilesIfState(0.5,0.95)(if(has(measurements.key,'app_start_cold'),

                                   arrayElement(measurements.value,indexOf(measurements.key,'app_start_cold')),0) 

                                as temp_duration,(temp_duration !=4294967295))) as app_start_cold_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    and arrayElement(measurements.value,

indexOf(measurements.key,

'app_start_cold')) > 0

GROUP BY

major_release,

daytime,

project_id

#------ ui_show_duration_p95 的物化视图

CREATE MATERIALIZED VIEW overview_daily.transactions_daily_mv_ui_show_duration_p95 to overview_daily.transactions_daily_local AS

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

(quantilesIfState(0.5,0.95)(if(has(measurements.key,'tap_loading_more_ui_show_total'),

                                   arrayElement(measurements.value,indexOf(measurements.key,'tap_loading_more_ui_show_total')),0) 

                                as temp_duration,(temp_duration !=4294967295))) as ui_show_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release','rnd')

    AND transaction_op = 'tap.loading.more'

    and arrayElement(measurements.value,

indexOf(measurements.key,

'tap_loading_more_ui_show_total')) > 0

GROUP BY

major_release,

daytime,

project_id

#------- gamedetail_page_duration_p95 的物化视图

CREATE MATERIALIZED VIEW overview_daily.transactions_daily_mv_gamedetail_page_duration_p95 to overview_daily.transactions_daily_local AS

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

    (quantilesIfState(0.5,0.95)(toFloat64(duration),( duration!= 4294967295)))  AS gamedetail_page_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    AND transaction_op = 'ui.load'

    AND `transaction_name` in ('GameDetailNewPager','GameDetailPager','GameDetail.GameDetailV2ViewController','TapTap.TapGameDetailViewController', 'TapTap.TapGameDetailInfoViewController', 'TapGameDetailViewController')

GROUP BY

major_release,

daytime,

project_id

#----------- first_page_duration_p95 的物化视图

CREATE MATERIALIZED VIEW overview_daily.transactions_daily_mv_first_page_duration_p95 to overview_daily.transactions_daily_local AS

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

    (quantilesIfState(0.5,0.95)(toFloat64(duration),( duration!= 4294967295)))  AS first_page_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    AND transaction_op = 'ui.load'

    AND `transaction_name` in ('AdvPageActivity', 'MainAct', 'HomePageActivity', 'TapHomeFeedListViewController', 'TapTap.TapHomeFeedListViewController', 'TapTap.TapHomeFeedsListViewController', 'HomeModule.ForYouViewController')

GROUP BY

major_release,

daytime,

project_id

物化视图创建后，只会对新增的数据生效，如果要处理历史数据，需要手动插入。如：

#从历史结果表里插入

INSERT INTO

  overview_daily.transactions_daily_local (

    daytime,

    major_release,

    os,

    area,

    app_start_cold_duration_p95,

    first_page_duration_p95,

    gamedetail_page_duration_p95,

    ui_show_duration_p95

  )

select

  daytime,

  major_release,

  os,

  area,

  quantilesIfState (0.5, 0.95) (

    app_start_cold_duration_p95,

    (app_start_cold_duration_p95 != 4294967295)

  ) as app_start_cold_duration_p95,

  quantilesIfState (0.5, 0.95) (

    first_page_duration_p95,(first_page_duration_p95 != 4294967295)

  ) as first_page_duration_p95,

  quantilesIfState (0.5, 0.95) (

    gamedetail_page_duration_p95,

    (gamedetail_page_duration_p95 != 4294967295)

  ) as gamedetail_page_duration_p95,

  quantilesIfState (0.5, 0.95) (

    ui_show_duration_p95,

    (ui_show_duration_p95 != 4294967295)

  ) as ui_show_duration_p95

FROM

  apm.sentry_overview_daily

GROUP BY daytime , major_release , os , area 

#---- app_start_cold_time_duration_p95

INSERT INTO

  overview_daily.transactions_daily_local (

    daytime,

      project_id,

    os,

    area,

      major_release,

    app_start_cold_time_duration_p95

  )

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,

'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

(quantilesIfState(0.5,0.95)(if(has(measurements.key,

'tap_app_start_cold_time'),

arrayElement(measurements.value,

indexOf(measurements.key,

'tap_app_start_cold_time')),

0) as fps,(fps !=4294967295))) as app_start_cold_time_duration_p95

FROM

`default`.transactions_local

WHERE

arrayElement(measurements.value,

indexOf(measurements.key,

'tap_app_start_cold_time')) > 0

AND transaction_op = 'tap.biz.page.load'

AND major_release NOT IN ('2.53.1', '2.54.0')

    AND `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

GROUP BY

major_release,

daytime,

project_id;

#---- ui_show_duration_p95

INSERT INTO

  overview_daily.transactions_daily_local (

    daytime,

      project_id,

    os,

    area,

      major_release,

    ui_show_duration_p95

  )

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

(quantilesIfState(0.5,0.95)(if(has(measurements.key,'tap_loading_more_ui_show_total'),

                                   arrayElement(measurements.value,indexOf(measurements.key,'tap_loading_more_ui_show_total')),0) 

                                as temp_duration,(temp_duration !=4294967295))) as ui_show_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release','rnd')

    AND transaction_op = 'tap.loading.more'

    and arrayElement(measurements.value,

indexOf(measurements.key,

'tap_loading_more_ui_show_total')) > 0

    and daytime ='2023-06-07 00:00:00'

GROUP BY

major_release,

daytime,

project_id

#------- gamedetail_page_duration_p95、

INSERT INTO

  overview_daily.transactions_daily_local (

    daytime,

      project_id,

    os,

    area,

      major_release,

    gamedetail_page_duration_p95

  )

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

    (quantilesIfState(0.5,0.95)(toFloat64(duration),( duration!= 4294967295)))  AS gamedetail_page_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    AND transaction_op = 'ui.load'

    AND `transaction_name` in ('GameDetailNewPager','GameDetailPager','GameDetail.GameDetailV2ViewController','TapTap.TapGameDetailViewController', 'TapTap.TapGameDetailInfoViewController', 'TapGameDetailViewController')

    and daytime ='2023-06-07 00:00:00'

GROUP BY

major_release,

daytime,

project_id

#----------- first_page_duration_p95

INSERT INTO

  overview_daily.transactions_daily_local (

    daytime,

      project_id,

    os,

    area,

      major_release,

    first_page_duration_p95

  )

SELECT

toStartOfDay(start_ts, 'Asia/Shanghai') AS daytime,

project_id,

    multiIf( project_id in [148,175],'android', project_id in[140,92,177],'ios','N/A')  AS os,

    multiIf( project_id in [148,92,177],'cn', project_id in[175,140],'intl','N/A')  AS area,

extract(`release`,'[0-9]+\.[0-9]+\.[0-9]') AS major_release,

    (quantilesIfState(0.5,0.95)(toFloat64(duration),( duration!= 4294967295)))  AS first_page_duration_p95

FROM

`default`.transactions_local

WHERE

     `project_id` IN (148, 175, 92, 177, 140)

    AND `environment` IN ('release')

    AND transaction_op = 'ui.load'

    AND `transaction_name` in ('AdvPageActivity', 'MainAct', 'HomePageActivity', 'TapHomeFeedListViewController', 'TapTap.TapHomeFeedListViewController', 'TapTap.TapHomeFeedsListViewController', 'HomeModule.ForYouViewController')

    and daytime ='2023-06-07 00:00:00'

GROUP BY

major_release,

daytime,

project_id

后续分析直接查询物化视图即可

select project_id FROM  overview_daily.transactions_daily_mv_local

注意：AggregateFunction 类型标注的字段，需要使用 quantilesIfMerge 函数查询，比如

SELECT

  daytime AS time,

   major_release,

  quantilesIfMerge(0.95)(app_start_cold_time_duration_p95)[1] AS `duration_p95`

FROM

  overview_daily.transactions_daily_local

WHERE

  daytime >= '2023-05-07 00:00:00'

  AND os = 'android'

  AND area = 'cn'

GROUP BY

  `time`,

  major_release

HAVING duration_p95>=0

需求3：按天持久化 SESSION 、SESSION_CRASHED 数据

 依然采用 Clickhouse 的物化视图功能（CREATE VIEW | ClickHouse Docs  ）对源表进行聚合处理成物化表。然后查询物化图即可。

首先创建一张表，用于存储物化视图的结果，注意：目标表的表引擎需要使用 SummingMergeTree ，用于实时的聚合

CREATE TABLE overview_daily.`sessions_daily_local`

(

    `daytime` DateTime('Asia/Shanghai'),

    `major_release` String,

    `os` String,

    `area` String,

    `total_session_crashed` Nullable(Float64), 

    `total_session` Nullable(Float64)

)

ENGINE = SummingMergeTree()

PARTITION BY toMonday(daytime)

ORDER BY (os,

 major_release,

 area,

 daytime)

SETTINGS index_granularity = 8192;

然后创建物化视图

CREATE MATERIALIZED VIEW overview_daily.sessions_daily_mv_local to overview_daily.sessions_daily_local AS

SELECT

  toStartOfDay (`started`, 'Asia/Shanghai') AS `daytime`,

  extract(`release`, '[0-9]+\.[0-9]+\.[0-9]+') AS `major_release`,

  multiIf (

    `project_id` in (148, 175),

    'android',

    `project_id` in (92, 177, 140),

    'ios',

    'N/A'

  ) AS os,

  multiIf (

    `project_id` in (148, 92, 177),

    'cn',

    `project_id` in (175, 140),

    'intl',

    'N/A'

  ) AS area,

  plus (

    countIfMerge (sessions_crashed),

    sumIfMerge (sessions_crashed_preaggr)

  ) AS total_session_crashed,

  plus (

    countIfMerge (sessions),

    sumIfMerge (sessions_preaggr)

  ) AS total_session

FROM

  default.sessions_hourly_local

WHERE

   `project_id` IN (148, 175, 92, 177, 140)

  AND `environment` IN ('release')

GROUP BY

  `daytime`,

  `major_release`,

  os,

  area

插入历史记录的数据

INSERT INTO overview_daily.sessions_daily_local

SELECT

  toStartOfDay (`started`, 'Asia/Shanghai') AS `daytime`,

  extract(`release`, '[0-9]+\.[0-9]+\.[0-9]+') AS `major_release`,

  multiIf (

    `project_id` in (148, 175),

    'android',

    `project_id` in (92, 177, 140),

    'ios',

    'N/A'

  ) AS os,

  multiIf (

    `project_id` in (148, 92, 177),

    'cn',

    `project_id` in (175, 140),

    'intl',

    'N/A'

  ) AS area,

  plus (

    countIfMerge (sessions_crashed),

    sumIfMerge (sessions_crashed_preaggr)

  ) AS total_session_crashed,

  plus (

    countIfMerge (sessions),

    sumIfMerge (sessions_preaggr)

  ) AS total_session

FROM

  default.sessions_hourly_local

WHERE

   `project_id` IN (148, 175, 92, 177, 140)

  AND `environment` IN ('release')

  and daytime = '2023-06-06 00:00:00'

GROUP BY

  `daytime`,

  `major_release`,

  os,

  area

需求4：按 5 分钟粒度持久化 CRASH_COUNT 、UNIQUE_USER_COUNT 数据

 依然采用 Clickhouse 的物化视图功能（CREATE VIEW | ClickHouse Docs  ）对源表进行聚合处理成物化表。然后查询物化图即可。

首先创建一张表，用于存储物化视图的结果，注意：目标表的表引擎需要使用 SummingMergeTree ，用于实时的聚合

CREATE TABLE overview_daily.`errors_5min_local`

(

    `mintime` DateTime('Asia/Shanghai'),

    `os` String,

    `area` String,

    `crash_count` Float64,

    `unique_user_count` Float64

)

ENGINE = SummingMergeTree()

PARTITION BY toMonday(mintime)

ORDER BY (os,

 area,

 mintime)

SETTINGS index_granularity = 8192;

创建物化视图

CREATE MATERIALIZED VIEW overview_daily.errors_5min_mv_local to overview_daily.`errors_5min_local` AS

SELECT toStartOfFiveMinute(`timestamp`, 'Asia/Shanghai') as `mintime`,

            multiIf(`project_id` in (148, 175), 'android', `project_id` in (92,177, 140), 'ios','N/A') AS os,

            multiIf(`project_id` in (148, 92,177), 'cn', `project_id` in (175,140), 'intl','N/A') AS area,

            count(*) AS crash_count,

            (ifNull(uniq((nullIf(user, '') AS _snuba_user)), 0) AS unique_user_count)

        FROM default.errors_local 

        WHERE 

             equals(deleted, 0) AND equals((type AS _snuba_type), 'error') 

            and `project_id`  in (148, 175, 92, 177, 140)

            AND equals(ifNull((level AS `_snuba_tags[level]`), ''), 'fatal') 

            AND `environment` in ('release')

        GROUP BY `mintime`,os,area

插入历史记录

#从历史结果表里插入

INSERT INTO

  overview_daily.`errors_5min_local` (

    mintime,

    os,

    area,

    crash_count, 

    unique_user_count

  )

select

  time as mintime,

    os,

    area,

    crash_count, 

    unique_user_count

FROM

  apm.sentry_crash_user_5min 

#------ errors_local 表导入

INSERT INTO overview_daily.errors_5min_local

SELECT toStartOfFiveMinute(`timestamp`, 'Asia/Shanghai') as `mintime`,

            multiIf(`project_id` in (148, 175), 'android', `project_id` in (92,177, 140), 'ios','N/A') AS os,

            multiIf(`project_id` in (148, 92,177), 'cn', `project_id` in (175,140), 'intl','N/A') AS area,

            count(*) AS crash_count,

            (ifNull(uniq((nullIf(user, '') AS _snuba_user)), 0) AS unique_user_count)

        FROM default.errors_local 

        PREWHERE `project_id`  in (148, 175, 92, 177, 140)

        WHERE 

             equals(deleted, 0) AND equals((type AS _snuba_type), 'error') 

            AND equals(ifNull((level AS `_snuba_tags[level]`), ''), 'fatal') 

            AND `environment` in ('release')

            and mintime > '2023-06-09 00:00:00' 

        GROUP BY `mintime`,os,area