At SigNoz we store our data on ClickHouse. In this documentation, we will go through the schema of the logs table and see how we can write clickhouse queries to create different dashboard panels from Logs Data.
Logs Schema V2
If we check the schema of the logs table in clickhouse this is what it looks like. The table was created with respect to the OpenTelemetry Logs Data Model
(
`timestamp` UInt64 CODEC(DoubleDelta, LZ4),
`observed_timestamp` UInt64 CODEC(DoubleDelta, LZ4),
`id` String CODEC(ZSTD(1)),
`trace_id` String CODEC(ZSTD(1)),
`span_id` String CODEC(ZSTD(1)),
`trace_flags` UInt32,
`severity_text` LowCardinality(String) CODEC(ZSTD(1)),
`severity_number` UInt8,
`body` String CODEC(ZSTD(2)),
`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)),
`scope_name` String CODEC(ZSTD(1)),
`scope_version` String CODEC(ZSTD(1)),
`scope_string` Map(LowCardinality(String), String) CODEC(ZSTD(1))
)
For faster filtering using resource attributes we have another table i.e logs_v2_resource
(
`labels` String CODEC(ZSTD(5)),
`fingerprint` String CODEC(ZSTD(1)),
`seen_at_ts_bucket_start` Int64 CODEC(Delta(8), ZSTD(1))
)
The distributed tables which references the above tables are distributed_logs_v2 and distributed_logs_v2_resource respectively.
Any query that we write will be written for the distributed_logs_v2 table. If any resource filter is present then we will use the distributed_logs_v2_resource for better performance.
Columns in the Logs Table
| NAME | DESCRIPTION |
|---|---|
| timestamp | Time when the log line was generated at the source. The default value is the time at which it is received and it can be changed using the time parser. |
| observed_timestamp | Time when the log line is observed at the collection system. It is automatically added by the collector. |
| id | It is a ksuid, it helps us in paginating and sorting log lines. It is automatically added by the collector. |
| trace_id | Trace ID of the log line. W3C Trace Context. It can be filled using trace parser. |
| span_id | Span ID for the log line or set of log lines that are part of a particular processing span. It can be filled using trace parser. |
| trace_flags | Trace Flag of the log line. W3C Trace Context. It can be filled using trace parser. |
| severity_text | It is the log level. eg:- info . It can be filled using severity parser. |
| severity_number | Numerical value of the severity_text. It can be filled using severity parser. |
| body | The body/message of the log record. |
| resources_string | [Deprecated]All the resource attributes are stored in this map. |
| resource | All the resource attributes are stored in this JSON column. |
| attributes_string | All the attributes with string data type are stored in this map. |
| attributes_number | All the attributes with number data type are stored in this map. |
| attributes_bool | All the attributes with boolean data type are stored in this map. |
| scope_name | Instrumentation scope name. |
| scope_version | Instrumentation scope version. |
| scope_string | Instrumentation scope attributes |
The attributes and resources can be added and transformed using different processors and operators. You can read more about them here
Timestamp Bucketing
In the new schema for logs i.e distributed_logs_v2, 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_timestamp_nano AND $start_timestamp_nano 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 Better Performance
Resource attributes represent metadata about the source of your logs (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_logs_v2_resource table.
You can read more about it here.
Always include resource attribute filters in your queries when possible. This is one of the most effective ways to optimize log 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_logs.distributed_logs_v2_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_logs.distributed_logs_v2_resource
WHERE (simpleJSONExtractString(labels, 'service.name') = 'myservice')
AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT count(*)
FROM signoz_logs.distributed_logs_v2
WHERE resource_fingerprint GLOBAL IN __resource_filter
AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
AND timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano
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
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 method in attributes_string, the following columns will be created:
`attribute_string_method` String MATERIALIZED attributes_string['method'] CODEC(ZSTD(1)),
`attribute_string_method_exists` Bool MATERIALIZED if(mapContains(attributes_string, 'method') != 0, true, false) CODEC(ZSTD(1)),
Query Syntax for Attributes
When writing queries for the logs 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=attributesdataType=string,number, orboolkeyname= the actual attribute name
Examples:
- String attribute:
attributes_string['status'] - 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>
Examples:
- Selected string attribute:
attribute_string_status - Selected number attribute:
attribute_number_response$$time - Selected boolean attribute:
attribute_bool_is_error
In the above example, if status is an selected field , then it can be referenced as attribute_string_status
We will use two variables i.e $start_timestamp_nano and $end_timestamp_nano while writing our queries to specify the time range.
Timeseries
This panel is used when you want to view your aggregated data in a timeseries.
Examples
Count of log lines per minute
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_resource
WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts
ORDER BY ts ASC;
Count of log lines per minute group by container name attribute
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_resource
WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
attributes_string['container_name'] as container_name,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
mapContains(attributes_string, 'container_name')
GROUP BY container_name, ts
ORDER BY ts ASC;
Count of log lines per minute where severity_text = 'INFO'
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_resource
WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='INFO' AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts
ORDER BY ts ASC;
Count of log lines per minute where severity_text = 'INFO' , method = 'GET' , service.name = 'demo'. Here method is an attribute while service.name is a resource attribute.
WITH __resource_filter AS (
SELECT fingerprint FROM signoz_logs.distributed_logs_v2_resource
WHERE (simpleJSONExtractString(labels, 'service.name') = 'demo')
AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='INFO' AND
attributes_string['method'] = 'GET' AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts
ORDER BY ts ASC;
Count of log lines per minute where severity_text = 'INFO' , method = 'GET' , service.name = 'demo'. Here method is an attribute while service.name is a resource attribute and method is selected field.
WITH __resource_filter AS (
SELECT fingerprint FROM signoz_logs.distributed_logs_v2_resource
WHERE (simpleJSONExtractString(labels, 'service.name') = 'demo')
AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='INFO' AND
attribute_string_method = 'GET' AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts
ORDER BY ts 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 count of log lines where severity_text = 'INFO' , method = 'GET' , service.name = 'demo'. Here method is an attribute while service.name is a resource attribute.
WITH __resource_filter AS (
SELECT fingerprint FROM signoz_logs.distributed_logs_v2_resource
WHERE (simpleJSONExtractString(labels, 'service.name') = 'demo')
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(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='INFO' AND
attributes_string['method'] = 'GET' AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY ts
ORDER BY ts ASC
)
attributes_string['method'] will change to attribute_string_method if method is a selected field and resources_string['service.name'] will change to resource_string_service$$name if service.name is a selected field.
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
Count of log lines where severity_text = 'INFO' , method = 'GET' group by service.name. Here method is an attribute while service.name is a resource attribute.
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_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_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='INFO' AND
attributes_string['method'] = 'GET' AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY `service.name`, ts
ORDER BY ts ASC;
attributes_string['method'] will change to attribute_string_method if method is a selected field and resources_string['service.name'] will change to resource_string_service$$name if service.name is a selected field.
Real Life Use Cases Example
Number of log lines generated by each kubernetes cluster
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_resource
WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
resource.k8s.cluster.name::String as k8s_cluster_name,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
k8s_cluster_name IS NOT NULL AND
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY k8s_cluster_name, ts
ORDER BY ts ASC;
Number of error logs generated by each service
WITH __resource_filter AS (
SELECT fingerprint
FROM signoz_logs.distributed_logs_v2_resource
WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS ts,
resource.`service.name`::String as `service.name`,
toFloat64(count()) AS value
FROM
signoz_logs.distributed_logs_v2
WHERE
resource_fingerprint GLOBAL IN __resource_filter AND
(timestamp >= $start_timestamp_nano AND timestamp <= $end_timestamp_nano) AND
severity_text='ERROR' 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;
Panel Time preference
Using the Panel Time Preference present on the right you can select a custom time range for your panel. When you open the dashboard the specific panel will render for the time specified for that panel.
