Writing traces based ClickHouse queries for building dashboard panels

Traces Schema

distributed_signoz_index_v3

This is primary table of Traces which is queried to fetch spans and apply aggregation on spans. It has over 30 different columns which helps in faster filtering on most common attributes following OpenTelemetry Trace Semantic conventions.

(
    `ts_bucket_start` UInt64 CODEC(DoubleDelta, LZ4),
    `resource_fingerprint` String CODEC(ZSTD(1)),
    `timestamp` DateTime64(9) CODEC(DoubleDelta, LZ4),
    `trace_id` FixedString(32) CODEC(ZSTD(1)),
    `span_id` String CODEC(ZSTD(1)),
    `trace_state` String CODEC(ZSTD(1)),
    `parent_span_id` String CODEC(ZSTD(1)),
    `flags` UInt32 CODEC(T64, ZSTD(1)),
    `name` LowCardinality(String) CODEC(ZSTD(1)),
    `kind` Int8 CODEC(T64, ZSTD(1)),
    `kind_string` String CODEC(ZSTD(1)),
    `duration_nano` UInt64 CODEC(T64, ZSTD(1)),
    `status_code` Int16 CODEC(T64, ZSTD(1)),
    `status_message` String CODEC(ZSTD(1)),
    `status_code_string` String CODEC(ZSTD(1)),
    `attributes_string` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `attributes_number` Map(LowCardinality(String), Float64) CODEC(ZSTD(1)),
    `attributes_bool` Map(LowCardinality(String), Bool) CODEC(ZSTD(1)),
    `resources_string` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `resource` JSON(max_dynamic_paths = 100) CODEC(ZSTD(1)),
    `events` Array(String) CODEC(ZSTD(2)),
    `links` String CODEC(ZSTD(1)),
    `response_status_code` LowCardinality(String) CODEC(ZSTD(1)),
    `external_http_url` LowCardinality(String) CODEC(ZSTD(1)),
    `http_url` LowCardinality(String) CODEC(ZSTD(1)),
    `external_http_method` LowCardinality(String) CODEC(ZSTD(1)),
    `http_method` LowCardinality(String) CODEC(ZSTD(1)),
    `http_host` LowCardinality(String) CODEC(ZSTD(1)),
    `db_name` LowCardinality(String) CODEC(ZSTD(1)),
    `db_operation` LowCardinality(String) CODEC(ZSTD(1)),
    `has_error` Bool CODEC(T64, ZSTD(1)),
    `is_remote` LowCardinality(String) CODEC(ZSTD(1)),
    `resource_string_service$$name` LowCardinality(String) DEFAULT resources_string['service.name'] CODEC(ZSTD(1)),
    `attribute_string_http$$route` LowCardinality(String) DEFAULT attributes_string['http.route'] CODEC(ZSTD(1)),
    `attribute_string_messaging$$system` LowCardinality(String) DEFAULT attributes_string['messaging.system'] CODEC(ZSTD(1)),
    `attribute_string_messaging$$operation` LowCardinality(String) DEFAULT attributes_string['messaging.operation'] CODEC(ZSTD(1)),
    `attribute_string_db$$system` LowCardinality(String) DEFAULT attributes_string['db.system'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$system` LowCardinality(String) DEFAULT attributes_string['rpc.system'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$service` LowCardinality(String) DEFAULT attributes_string['rpc.service'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$method` LowCardinality(String) DEFAULT attributes_string['rpc.method'] CODEC(ZSTD(1)),
    `attribute_string_peer$$service` LowCardinality(String) DEFAULT attributes_string['peer.service'] CODEC(ZSTD(1))
)
ORDER BY (ts_bucket_start, resource_fingerprint, has_error, name, timestamp)

For faster filtering using resource attributes we have another table i.e distributed_traces_v3_resource

(
    `labels` String CODEC(ZSTD(5)),
    `fingerprint` String CODEC(ZSTD(1)),
    `seen_at_ts_bucket_start` Int64 CODEC(Delta(8), ZSTD(1))
)
📝 Note

Any query that we write will be written for the distributed_signoz_index_v3 table. If any resource filter is present then we will use the distributed_traces_v3_resource for better performance.

Columns in the distributed_signoz_index_v3 table

NAMEDESCRIPTION
ts_bucket_startStart timestamp of the bucket
resource_fingerprintFingerprint of the resource, generated by combining all the resource attributes
timestampTime when the span generated at the source
trace_idTrace ID. W3C Trace Context
span_idSpan ID
trace_stateTrace state of the span
parent_span_idParent Span ID
flagsFlags of the span
nameName of the span
kindKind of the span. OpenTelemetry Span Kind
kind_stringString representation of the kind of the span
duration_nanoDuration of the span in nanoseconds
status_codeStatus code of the span. OpenTelemetry Status Code
status_messageStatus message of the span
status_code_stringString representation of the status code of the span
attributes_stringMap of all string tags/attributes of the span
attributes_numberMap of all number tags/attributes of the span
attributes_boolMap of all bool tags/attributes of the span
resources_string[Deprecated] Map of all resource tags/attributes of the span
resourceAll the resource attributes are stored in this JSON column
eventsEvents of the span. It is an array of stringified json of span events
linksLinks of the span. It is a stringified json of span links
response_status_codeResponse status code of the span. Derived from http.status_code, rpc.grpc.status_code and rpc.jsonrpc.error_code attribute of a span
external_http_urlExternal HTTP url of the span
http_urlHTTP url of the span
external_http_methodExternal HTTP method of the span
http_methodHTTP method of the span
http_hostHTTP host of the span
db_nameDatabase name of the span. Derived from db.name attribute of a span
db_operationDatabase operation of the span. Derived from db.operation attribute of a span
has_errorWhether the span has error or not
is_remoteWhether the span is remote or not
resource_string_service$$nameName of the service. Derived from resources_string['service.name'] attribute of a span
attribute_string_http$$routeHTTP route of the span. Derived from attributes_string['http.route'] attribute of a span
attribute_string_messaging$$systemMessaging system of the span. Derived from attributes_string['messaging.system'] attribute of a span
attribute_string_messaging$$operationMessaging operation of the span. Derived from attributes_string['messaging.operation'] attribute of a span
attribute_string_db$$systemDatabase system of the span. Derived from attributes_string['db.system'] attribute of a span
attribute_string_rpc$$systemRPC system of the span. Derived from attributes_string['rpc.system'] attribute of a span
attribute_string_rpc$$serviceRPC service of the span. Derived from attributes_string['rpc.service'] attribute of a span
attribute_string_rpc$$methodRPC method of the span. Derived from attributes_string['rpc.method'] attribute of a span
attribute_string_peer$$servicePeer service of the span. Derived from attributes_string['peer.service'] attribute of a span

distributed_signoz_error_index_v2

This table stores error events derived from spans

(
    `timestamp` DateTime64(9) CODEC(DoubleDelta, LZ4),
    `errorID` FixedString(32) CODEC(ZSTD(1)),
    `groupID` FixedString(32) CODEC(ZSTD(1)),
    `traceID` FixedString(32) CODEC(ZSTD(1)),
    `spanID` String CODEC(ZSTD(1)),
    `serviceName` LowCardinality(String) CODEC(ZSTD(1)),
    `exceptionType` LowCardinality(String) CODEC(ZSTD(1)),
    `exceptionMessage` String CODEC(ZSTD(1)),
    `exceptionStacktrace` String CODEC(ZSTD(1)),
    `exceptionEscaped` Bool CODEC(T64, ZSTD(1)),
    `resourceTagsMap` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    INDEX idx_error_id errorID TYPE bloom_filter GRANULARITY 4,
    INDEX idx_resourceTagsMapKeys mapKeys(resourceTagsMap) TYPE bloom_filter(0.01) GRANULARITY 64,
    INDEX idx_resourceTagsMapValues mapValues(resourceTagsMap) TYPE bloom_filter(0.01) GRANULARITY 64
)

Columns in the distributed_signoz_error_index_v2 table

NAMEDESCRIPTION
timestampTime when the span generated at the source
errorIDError ID. W3C Trace Context
groupIDGroup ID of the error
traceIDTrace ID. W3C Trace Context
spanIDSpan ID
serviceNameName of the service. Derived from service.name attribute of a span
exceptionTypeException type of the error. Derived from exception.type attribute of a span
exceptionMessageException message of the error. Derived from exception.message attribute of a span
exceptionStacktraceException stacktrace of the error. Derived from exception.stacktrace attribute of a span
exceptionEscapedWhether the exception is escaped or not. Derived from exception.escaped attribute of a span
resourceTagsMapMap of all resource tags/attributes of the span

distributed_top_level_operations

This table stores top operations and service name.

(
    `name` LowCardinality(String) CODEC(ZSTD(1)),
    `serviceName` LowCardinality(String) CODEC(ZSTD(1))
)

Columns in the distributed_top_level_operations table

NAMEDESCRIPTION
nameName of the span
serviceNameName of the service

distributed_span_attributes_keys

This table stores all the attributes keys of the span.

(
    `tagKey` LowCardinality(String) CODEC(ZSTD(1)),
    `tagType` Enum8('tag' = 1, 'resource' = 2) CODEC(ZSTD(1)),
    `dataType` Enum8('string' = 1, 'bool' = 2, 'float64' = 3) CODEC(ZSTD(1)),
    `isColumn` Bool CODEC(ZSTD(1))
)

Columns in the distributed_span_attributes_keys table

NAMEDESCRIPTION
tagKeyName of the attribute
tagTypeType of the attribute. It can be tag or resource
dataTypeData type of the attribute. It can be string, bool or float64
isColumnWhether the attribute is a column or not

distributed_span_attributes

This table stores all the attributes of the span.

(
    `timestamp` DateTime CODEC(DoubleDelta, ZSTD(1)),
    `tagKey` LowCardinality(String) CODEC(ZSTD(1)),
    `tagType` Enum8('tag' = 1, 'resource' = 2) CODEC(ZSTD(1)),
    `dataType` Enum8('string' = 1, 'bool' = 2, 'float64' = 3) CODEC(ZSTD(1)),
    `stringTagValue` String CODEC(ZSTD(1)),
    `float64TagValue` Nullable(Float64) CODEC(ZSTD(1)),
    `isColumn` Bool CODEC(ZSTD(1))
)

Columns in the distributed_span_attributes table

NAMEDESCRIPTION
timestampTime when the span generated at the source
tagKeyName of the attribute
tagTypeType of the attribute. It can be tag or resource
dataTypeData type of the attribute. It can be string, bool or float64
stringTagValueString value of the attribute
float64TagValueFloat64 value of the attribute
isColumnWhether the attribute is a column or not

Timestamp Bucketing for distributed_signoz_index_v3

In the new schema for traces i.e distributed_signoz_index_v3, we have a new column ts_bucket_start which is used to store the start timestamp of the bucket. This is used for faster filtering on timestamp attribute.

How to use this column in the queries? If your timestamp query is timestamp BETWEEN $start_datetime AND $end_datetime then you will have to add ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp

We have added -1800, as bucketing is done in 30 minute intervals.

Using Resource Attributes for distributed_signoz_index_v3

Resource attributes represent metadata about the source of your traces (like service name, host, environment, etc.). Using resource attributes in your queries will significantly improve query performance because they allow ClickHouse to filter data more efficiently using the dedicated distributed_traces_v3_resource table.

You can read more about it here.

💡 Tip

Always include resource attribute filters in your queries when possible. This is one of the most effective ways to optimize trace query performance.

How to Filter Using Resource Attributes

When filtering by resource attributes, you need to use a Common Table Expression (CTE) to first identify the relevant resource fingerprints, then use those fingerprints in your main query.

Step 1: Create the Resource Filter CTE

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'myservice') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)

Step 2: Use the Filter in Your Main Query

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'myservice') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT count(*) 
FROM signoz_traces.distributed_signoz_index_v3 
WHERE resource_fingerprint GLOBAL IN __resource_filter
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
    AND timestamp >= {{.start_datetime}} AND timestamp <= {{.end_datetime}}

Using Resource Attributes in SELECT and GROUP BY

To include resource attributes in your SELECT clause or GROUP BY statements, use the format: resource.<keyname>::String

Example: resource.service.name::String

Selected (Indexed) Attributes for distributed_signoz_index_v3

When an attribute field is converted to a selected (indexed) field, two new dedicated columns are automatically created for better query performance.

How Selected Attributes Work

For example, if you have an attribute named http.method in attributes_string, the following columns will be created:

`attribute_string_http$$method` LowCardinality(String) DEFAULT attributes_string['http.method'] CODEC(ZSTD(1)),
`attribute_string_http$$method_exists` Bool MATERIALIZED if(mapContains(attributes_string, 'http.method') != 0, true, false) CODEC(ZSTD(1)),
Info

Selected attributes can be referenced directly by their column name (e.g., attribute_string_http$$method) instead of using map syntax (e.g., attributes_string['http.method']).

Query Syntax for Attributes

When writing queries for the traces table, you need to reference attributes using the correct format based on their data type and whether they are selected fields.

Standard Attribute Reference Format

For non-selected attributes, use the format: <type>_<dataType>[<keyname>]

Where:

  • type = attributes
  • dataType = string, number, or bool
  • keyname = the actual attribute name

Examples:

  • String attribute: attributes_string['http.status_code']
  • Number attribute: attributes_number['response_time']
  • Boolean attribute: attributes_bool['is_error']

Selected Attribute Reference Format

For selected (indexed) attributes, use the direct column name: attribute_<dataType>_<keyname_with_dollars>

Examples:

  • Selected string attribute: attribute_string_http$$route
  • Selected number attribute: attribute_number_response$$time
  • Selected boolean attribute: attribute_bool_is$$error
📝 Note

Note that in selected attribute column names, dots (.) in the original attribute name are replaced with double dollar signs ($$).

Writing Clickhouse Queries for Dashboard Panels

Timeseries

This panel is used when you want to view your aggregated data in a timeseries.

Examples

Plotting a chart on 100ms interval

Plot a chart of 1 minute showing count of spans in 100ms interval of service frontend with duration > 50ms

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT fromUnixTimestamp64Milli(intDiv( toUnixTimestamp64Milli ( timestamp ), 100) * 100) AS interval, 
toFloat64(count()) AS count 
FROM (
 SELECT timestamp 
 FROM signoz_traces.distributed_signoz_index_v3
 WHERE resource_string_service$$name='frontend' 
 AND duration_nano>=50*exp10(6) 
 AND timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}}
 AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp)
 GROUP BY interval ORDER BY interval ASC;

Value

For the value type panel, the overall query will be similar to timeseries, just that you will have to get the absolute value at the end. You can reduce your end result to either average, latest, sum, min, or max.

Examples

Average duration of spans where method = 'POST' , service.name = 'sample-service'
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'frontend') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    avg(value) as value, 
    any(ts) as ts FROM (
        SELECT 
            toStartOfInterval((timestamp), INTERVAL 1 MINUTE) AS ts, 
            toFloat64(count()) AS value 
        FROM 
            signoz_traces.distributed_signoz_index_v3
        WHERE 
            timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
            ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
            http_method = 'POST' AND 
            resource_fingerprint GLOBAL IN __resource_filter
        GROUP BY ts 
        ORDER BY ts ASC
    )
Count of spans per minute with resource filtering for service.name = 'frontend' and environment = 'production'
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'frontend') 
    AND (simpleJSONExtractString(labels, 'environment') = 'production')
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts 
ORDER BY ts ASC;
Average duration of spans where http.method = 'GET' and service.name = 'api-service' using resource filtering
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'api-service') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    avg(value) as value, 
    any(ts) as ts FROM (
        SELECT 
            toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts, 
            toFloat64(avg(duration_nano)) AS value 
        FROM 
            signoz_traces.distributed_signoz_index_v3
        WHERE 
            resource_fingerprint GLOBAL IN __resource_filter AND
            timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
            ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
            http_method = 'GET'
        GROUP BY ts 
        ORDER BY ts ASC
    )

Table

This is used when you want to view the timeseries data in a tabular format.

The query is similar to timeseries query but instead of using time interval we use just use now() as ts in select.

Examples

GroupBy a tag/attribute in distributed tracing data
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT now() as ts, 
    attributes_string['example_string_attribute'] AS attribute_name, 
    toFloat64(avg(duration_nano)) AS value 
FROM signoz_traces.distributed_signoz_index_v3  
WHERE timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}}
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY (attribute_name, ts) order by (attribute_name, ts) ASC;
Count of spans by service name with resource filtering
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    now() as ts,
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    `service.name` IS NOT NULL
GROUP BY `service.name`, ts 
ORDER BY value DESC;
Average duration by HTTP method for specific service using resource filtering
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'api-gateway') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    now() as ts,
    http_method,
    toFloat64(avg(duration_nano)) AS avg_duration_nano 
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    http_method IS NOT NULL AND http_method != ''
GROUP BY http_method, ts 
ORDER BY avg_duration_nano DESC;

Real Life Use Cases Examples

Number of spans generated by each service

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3  
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND 
    `service.name` IS NOT NULL AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY `service.name`, ts
ORDER BY ts ASC;

Number of error spans generated by each service

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3  
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND 
    has_error = true AND
    `service.name` IS NOT NULL AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY `service.name`, ts 
ORDER BY ts ASC;

Last updated: June 6, 2024

Edit on GitHub

Was this page helpful?