ClickHouse Schema

qryn

Database Schema

CREATE TABLE time_series (
    date Date,
    fingerprint UInt64,
    labels String,
    name String
)
ENGINE = ReplacingMergeTree
    PARTITION BY date
    ORDER BY fingerprint;

CREATE TABLE samples (
    fingerprint UInt64,
    timestamp_ms Int64,
    value Float64,
    string String
)
ENGINE = MergeTree
    PARTITION BY toDate(timestamp_ms / 1000)
    ORDER BY (fingerprint, timestamp_ms);

Database Schema for cluster

CREATE TABLE distributed_time_series (
    date Date,
    fingerprint UInt64,
    labels String,
    name String
) ENGINE = Distributed('cloki','cloki','time_series',fingerprint);


CREATE TABLE distributed_samples (
    fingerprint UInt64,
    timestamp_ms Int64,
    value Float64,
    string String
) ENGINE = Distributed('cloki','cloki','samples',fingerprint);

Raw Queries

CREATE

DATABASE

CREATE DATABASE IF NOT EXISTS loki

TABLES

CREATE TABLE IF NOT EXISTS loki.time_series (date Date,fingerprint UInt64,labels String, name String) ENGINE = ReplacingMergeTree PARTITION BY date ORDER BY fingerprint;
CREATE TABLE IF NOT EXISTS loki.samples (fingerprint UInt64,timestamp_ms Int64,value Float64,string String) ENGINE = MergeTree PARTITION BY toRelativeHourNum(toDateTime(timestamp_ms / 1000)) ORDER BY (fingerprint, timestamp_ms);

SELECT

FINGERPRINTS

SELECT DISTINCT fingerprint, labels FROM loki.time_series

SAMPLES

SELECT fingerprint, timestamp_ms, string
	FROM loki.samples
	WHERE fingerprint IN (7975981685167825999) AND timestamp_ms >= 1514730532900 
	AND timestamp_ms <= 1514730532902
	ORDER BY fingerprint, timestamp_ms

SELECT fingerprint, timestamp_ms, value
	FROM loki.samples
	ANY INNER JOIN 7975981685167825999 USING fingerprint
	WHERE timestamp_ms >= 1514730532900 AND timestamp_ms <= 1514730532902
	ORDER BY fingerprint, timestamp_ms

INSERT

FINGERPRINTS

INSERT INTO loki.time_series (date, fingerprint, labels, name) VALUES (?, ?, ?, ?)

SAMPLES

INSERT INTO loki.samples (fingerprint, timestamp_ms, value, string) VALUES (?, ?, ?, ?)

QUERIES

Logs by Fingerprint

FINGERPRINT QUERY SELECT DISTINCT fingerprint FROM time_series FINAL PREWHERE (visitParamExtractString(labels, 'host') = 'nyc01')
FOUND FINGERPRINTS:  [
  '127413607',  '782691015',
]
SEARCH QUERY SELECT fingerprint, timestamp_ms, string FROM samples WHERE fingerprint IN (127413607,782691015) AND timestamp_ms BETWEEN 1606162560000 AND 1606162861000 ORDER BY fingerprint, timestamp_ms

Matrix Timeseries by Fingerprint/Tag

 SELECT tag, groupArray((timestamp_ms, toString(value))) AS groupArr FROM ( SELECT timestamp_ms, visitParamExtractString(labels, 'cpu') as tag, round(value,2) as value FROM loki.samples RIGHT JOIN loki.time_series ON samples.fingerprint = time_series.fingerprint WHERE timestamp_ms BETWEEN 1606142042000 AND 1606142343000 AND (visitParamExtractString(labels, 'cpu') != '') ORDER BY fingerprint, timestamp_ms) GROUP BY tag ;