ClickHouse - Materialized view contains duplicates on the grouping key

[StarpTech]

Component(s)

exporter/clickhouse

What happened?

Description

Hello, we experience duplicates in the otel_traces_trace_id_ts_mv when ingesting spans from multiple services. This looks like a race condition or limitation of how the schema was designed. We work around this by querying the otel_traces_trace_id_ts table directly.

Reading the blog post https://clickhouse.com/blog/storing-traces-and-spans-open-telemetry-in-clickhouse from clickhouse the value of the materialized view is very limited. I recommend deleting it to avoid this. I also saw that the README examples don't use the materialized view at all.

Steps to Reproduce

Expected Result

No duplicates are in the otel_traces_trace_id_ts_mv table because the idea is to use this table as a root spans overview.

Actual Result

Duplicates. See image above.

Collector version

0.81.0

Environment information

Environment

OS: (e.g., "Ubuntu 20.04") PopOs 22.04 LTS
Compiler(if manually compiled): (e.g., "go 14.2") 1.2.0

OpenTelemetry Collector configuration

No response

Log output

No response

Additional context

No response

[github-actions]

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[hanjm]

The duplicates data is normal beacuse it is inserted when a batch of spans write to clickhouse. A traceID may exist in more than more batch. so we query with min(start) and max(end)+1 as the time index of traceID.

The mv is high effectly when spans count grow high especially more than billon level. #13442 (comment)

If you query traceID is very fast without this time index mv in small dataset, you can just delete it.

[StarpTech]

@hanjm thanks for clarifying. That means In order to get correct results, I'd GROUP again on the otel_traces_trace_id_ts_mv table?

SELECT
	TraceId,
	min(Timestamp) as Start,
	max(Timestamp) as End
FROM
otel_traces_trace_id_ts_mv
WHERE TraceId !=''
GROUP BY TraceId;

[hanjm]

@StarpTech Yes. as the readme.md example

opentelemetry-collector-contrib/exporter/clickhouseexporter/README.md

Lines 150 to 156 in 3ec9d22

	```clickhouse
	WITH
	'391dae938234560b16bb63f51501cb6f' as trace_id,
	(SELECT min(Start) FROM otel_traces_trace_id_ts WHERE TraceId = trace_id) as start,
	(SELECT max(End) + 1 FROM otel_traces_trace_id_ts WHERE TraceId = trace_id) as end
	SELECT Timestamp as log_time,
	TraceId,

[StarpTech]

@hanjm I mean when making use of the mv.

[hanjm]

@StarpTech No, this mv is just a insert trigger, you can select from the distination table otel_traces_trace_id_ts but can not query otel_traces_trace_id_ts_mv directly. https://clickhouse.com/blog/using-materialized-views-in-clickhouse

[StarpTech]

@hanjm Thank you, that makes sense.

[StarpTech]

I think this is not 100% correct. I know where my confusion came from. In Clickhouse, you can also use mv differently. Look at https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 and query the mv directly because it creates a hidden table.

[hanjm]

The diffence is the to table when create a mv, you can see the best practices from altinity. https://kb.altinity.com/altinity-kb-schema-design/materialized-views/#best-practices