Как лучше всего хранить данные датчиков в Clickhouse?

У нас есть набор устройств, и все они имеют датчики. Все устройства имеют общий набор датчиков, но некоторые устройства имеют дополнительные датчики. Каждый датчик имеет разный уровень дискретизации, и некоторые датчики могут изменяться иногда очень быстро, а иногда не могут изменяться в течение некоторого времени. Например, у нас есть DeviceA и поток пакетов в форме (NULL означает, что значение не меняется):

Timestamp, Temp, Latitude, Longitude, Speed...
111, 20, 54.111, 23.111, 10
112, 20, NULL, NULL, 13
113, 20, NULL, 23.112, 15

И DeviceB:

Timestamp, Temp, Latitude, Longitude, Speed..., AdditionalSensor
111, 24, 54.111, 23.121, 10 ... 1
112, 23, 55.111, 23.121, 13 ... 2
113, 23, 55.111, 23.122, 15 ... 1

Через некоторое время к какому-либо устройству могут быть добавлены новые датчики. Каждый датчик может быть любого числового типа (Int32, UInt8, Float32).

После этого данные будут использоваться для расчета: dau, mau, удержания, кластеризации координат GPS и так далее.

Мы могли бы просто создать какую-нибудь таблицу:

CREATE TABLE Sensors
(
        Date Date,
        Id FixedString(16),
        DeviceNumber FixedString(16),
        TimeUtc DateTime,
        DeviceTime DateTime, 
        Version Int32, 
        Mileage Int32, 
        Longitude Float64, 
        Latitude Float64, 
        AccelX Float64, 
        AccelY Float64, 
        AccelZ Float64
        ...
) ENGINE = MergeTree(Date, (DeviceNumber, TimeUtc), 8192);

Но здесь две проблемы: нет поддержки другого набора датчиков, и иногда у нас есть нулевые значения для некоторых значений датчиков в случае отсутствия изменений, и было бы здорово увидеть последнее ненулевое значение перед меткой времени.

Первую проблему мы смогли решить, создав таблицу с полями: SensorName, Timestamp, Date, Value. Но как выбрать правильный тип? Следует ли использовать разные таблицы для разных типов? Наверное, нам нужно использовать графитовый движок, к сожалению, у меня нет опыта в этом. Так что любая помощь действительно приветствуется. Было бы здорово иметь возможность сохранять только измененные значения любого датчика.

Обновить

Я нашел способ справиться с нулевыми значениями. Мы могли бы использовать функцию anyLast для запроса последнего полученного значения для столбца:

SELECT anyLast(Lights) FROM test where TimeUtc <= toDateTime('2017-11-07 11:13:59');

К сожалению, мы не можем заполнить все отсутствующие значения, используя какие-то перекрывающиеся оконные функции (они не поддерживаются в clickhouse). Таким образом, в случае поля, допускающего значение NULL, агрегатная функция будет использовать только ненулевые значения, а в случае поля, не допускающего значения NULL, будут использоваться все значения, включая нулевые значения, и оба способа неверны. Обходной путь - заполнить нулевые значения перед вставкой, используя select с anyLast значениями для всех нулевых значений в строке.


iot clickhouse
person Hodza    schedule 06.11.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете использовать Clickhouse как базу данных временных рядов.

Определение вашей таблицы не позволяет вам иметь динамические показатели. Вот почему вы пытаетесь иметь дело со значениями NULL.

Вы можете использовать эту таблицу для значений датчиков:

CREATE TABLE ts1(
    entity String,
    ts UInt64, -- timestamp, milliseconds from January 1 1970
    s Array(String), -- names of the sensors
    v Array(Float32), -- sensor values
    d Date MATERIALIZED toDate(round(ts/1000)), -- auto generate date from ts column
    dt DateTime MATERIALIZED toDateTime(round(ts/1000)) -- auto generate date time from ts column
) ENGINE = MergeTree(d, entity, 8192)

Здесь мы загружаем значения датчиков устройства A:

INSERT INTO ts1(entity, ts, s, v) 
VALUES ('deviceA', 1509232010254, ['temp','lat','long','speed'], [24, 54.111, 23.121, 11])

Запрашиваем данные об устройстве Временные данные:

SELECT 
    entity, 
    dt, 
    ts, 
    v[indexOf(s, 'temp')] AS temp
FROM ts1 
WHERE entity = 'deviceA'

┌─entity─┬──────────────────dt─┬────────────ts─┬─temp─┐
│ deviceA│ 2017-10-28 23:06:50 │ 1509232010254 │  24  │
└────────┴─────────────────────┴───────────────┴──────┘

Просмотрите этот полный ответ, чтобы получить подробное описание.

person Ramazan Polat    schedule 22.11.2017