Ingest and Visualization for OpenTelemetry Metrics

1
CREATE TABLE IF NOT EXISTS metrics_sum
2
(
3
    ProjectId         UInt32,
4
    ServiceName       LowCardinality(String),
5
    MetricName        String,
6
    MetricDescription String,
7
    MetricUnit        String,
8
    Attributes        Map(LowCardinality(String), String),
9
    Timestamp         DateTime64(9) CODEC (Delta, ZSTD),
10
    RetentionDays     UInt8 DEFAULT 30,
11
    -- sum
12
    Value             Float64
13
    -- other columns omitted for brevity
14
) ENGINE = MergeTree()
15
      TTL toDateTime(Timestamp) + toIntervalDay(RetentionDays)
16
      PARTITION BY toStartOfDay(Timestamp)
17
      ORDER BY (ProjectId, ServiceName, MetricName, toUnixTimestamp64Nano(Timestamp));
18
19
CREATE TABLE IF NOT EXISTS metrics_histogram
20
(
21
    ProjectId         UInt32,
22
    ServiceName       LowCardinality(String),
23
    MetricName        String,
24
    MetricDescription String,
25
    MetricUnit        String,
26
    Attributes        Map(LowCardinality(String), String),
27
    Timestamp         DateTime64(9) CODEC (Delta, ZSTD),
28
    RetentionDays     UInt8 DEFAULT 30,
29
    -- common
30
    -- histogram
31
    Count             UInt64 CODEC (Delta, ZSTD),
32
    Sum               Float64,
33
    BucketCounts Array (UInt64),
34
    ExplicitBounds Array (Float64),
35
    Min               Float64,
36
    Max               Float64
37
    -- other columns omitted for brevity
38
) ENGINE = MergeTree()
39
      TTL toDateTime(Timestamp) + toIntervalDay(RetentionDays)
40
      PARTITION BY toStartOfDay(Timestamp)
41
      ORDER BY (ProjectId, ServiceName, MetricName, toUnixTimestamp64Nano(Timestamp));
42
43
44
CREATE TABLE IF NOT EXISTS metrics_summary
45
(
46
    ProjectId         UInt32,
47
    ServiceName       LowCardinality(String),
48
    MetricName        String,
49
    MetricDescription String,
50
    MetricUnit        String,
51
    Attributes        Map(LowCardinality(String), String),
52
    Timestamp         DateTime64(9) CODEC (Delta, ZSTD),
53
    RetentionDays     UInt8 DEFAULT 30,
54
    -- common
55
    Flags             UInt32,
56
    -- summary
57
    Count             Float64,
58
    Sum               Float64
59
    -- other columns omitted for brevity
60
) ENGINE = MergeTree()
61
      TTL toDateTime(Timestamp) + toIntervalDay(RetentionDays)
62
      PARTITION BY toStartOfDay(Timestamp)
63
      ORDER BY (ProjectId, ServiceName, MetricName, toUnixTimestamp64Nano(Timestamp));
64
65
-- the destination table which contains the aggregate across metrics formats
66
CREATE TABLE IF NOT EXISTS default.metrics
67
(
68
    ProjectId         UInt32,
69
    ServiceName       String,
70
    MetricName        String,
71
    MetricType        Enum8('Empty' = 0, 'Gauge' = 1, 'Sum' = 2, 'Histogram' = 3, 'ExponentialHistogram' = 4, 'Summary' = 5),
72
    Attributes        Map(LowCardinality(String), String),
73
    Timestamp         DateTime CODEC (Delta(4), ZSTD(1)),
74
    -- meta
75
    MetricDescription SimpleAggregateFunction(anyLast, String),
76
    MetricUnit        SimpleAggregateFunction(anyLast, String),
77
    RetentionDays     SimpleAggregateFunction(max, UInt8) DEFAULT 30,
78
    -- histogram
79
    Min               SimpleAggregateFunction(min, Float64),
80
    Max               SimpleAggregateFunction(max, Float64),
81
    BucketCounts      SimpleAggregateFunction(groupArrayArray, Array(UInt64)),
82
    ExplicitBounds    SimpleAggregateFunction(groupArrayArray, Array(Float64)),
83
    -- common
84
    Count             SimpleAggregateFunction(sum, UInt64),
85
    Sum               SimpleAggregateFunction(sum, Float64)
86
    -- other columns omitted for brevity
87
) ENGINE = AggregatingMergeTree()
88
    PARTITION BY toStartOfDay(Timestamp)
89
    ORDER BY (ProjectId, ServiceName, MetricName, MetricType, toUnixTimestamp(Timestamp))
90
    TTL toDateTime(Timestamp) + toIntervalDay(RetentionDays);
91
92
CREATE MATERIALIZED VIEW IF NOT EXISTS metrics_sum_mv TO metrics AS
93
SELECT ProjectId,
94
       ServiceName,
95
       MetricName,
96
       MetricType,
97
       Attributes,
98
       toDateTime(toStartOfSecond(Timestamp)) as Timestamp,
99
       -- meta
100
       anyLastSimpleState(MetricDescription)  as MetricDescription,
101
       anyLastSimpleState(MetricUnit)         as MetricUnit,
102
       minSimpleState(StartTimestamp)         as StartTimestamp,
103
       maxSimpleState(RetentionDays)          as RetentionDays,
104
       -- sum
105
       sumSimpleState(1)                      as Count,
106
       sumSimpleState(Value)                  as Sum
107
-- other columns omitted for brevity
108
FROM metrics_sum
109
GROUP BY all;
110
111
CREATE MATERIALIZED VIEW IF NOT EXISTS metrics_histogram_mv TO metrics AS
112
SELECT ProjectId,
113
       ServiceName,
114
       MetricName,
115
       'Histogram'                                as MetricType,
116
       Attributes,
117
       toDateTime(toStartOfSecond(Timestamp))     as Timestamp,
118
       -- meta
119
       anyLastSimpleState(MetricDescription)      as MetricDescription,
120
       anyLastSimpleState(MetricUnit)             as MetricUnit,
121
       minSimpleState(StartTimestamp)             as StartTimestamp,
122
       maxSimpleState(RetentionDays)              as RetentionDays,
123
       -- histogram
124
       minSimpleState(Min)                        as Min,
125
       minSimpleState(Max)                        as Max,
126
       groupArrayArraySimpleState(BucketCounts)   as BucketCounts,
127
       groupArrayArraySimpleState(ExplicitBounds) as ExplicitBounds,
128
       sumSimpleState(Count)                      as Count,
129
       sumSimpleState(Sum)                        as Sum
130
-- other columns omitted for brevity
131
FROM metrics_histogram
132
GROUP BY all;
133
134
CREATE MATERIALIZED VIEW IF NOT EXISTS metrics_summary_mv TO metrics AS
135
SELECT ProjectId,
136
       ServiceName,
137
       MetricName,
138
       'Summary'                              as MetricType,
139
       Attributes,
140
       toDateTime(toStartOfSecond(Timestamp)) as Timestamp,
141
       -- meta
142
       anyLastSimpleState(MetricDescription)  as MetricDescription,
143
       anyLastSimpleState(MetricUnit)         as MetricUnit,
144
       minSimpleState(StartTimestamp)         as StartTimestamp,
145
       maxSimpleState(RetentionDays)          as RetentionDays,
146
       -- summary
147
       sumSimpleState(Count)                  as Count,
148
       sumSimpleState(Sum)                    as Sum
149
-- other columns omitted for brevity
150
FROM metrics_summary
151
GROUP BY all;

1
SELECT Timestamp,
2
       toFloat64(Sum / Count) as value
3
FROM metrics
4
WHERE ProjectId = ?
5
  AND Timestamp <= ?
6
  AND Timestamp >= ?
7
  AND toString(MetricName) = ?
8
  AND Attributes[?] = ?