Example for materialized view from raw data store

flat_parsed.sql

CREATE MATERIALIZED VIEW example.parsed
            (
             `event_uuid` String,
             `datetime` DateTime,
             `a` Int32,
             `b` Int32,
             `c` Int32,
             `d` Float64,
             `e` Float64,
             `f` Float64,
             `g` DateTime64,
             `h` DateTime64,
             `i` DateTime64,
             `k` String,
             `l` String,
             `m` String
             )
            ENGINE = MergeTree
                PARTITION BY client_version
                ORDER BY client_timestamp
                SETTINGS index_granularity = 8192
AS
SELECT event_uuid,
       max(_datetime)                                                  AS datetime,
       toInt32OrZero(max(_a))                                          AS a,
       toInt32OrZero(max(_b))                                          AS b,
       toInt32OrZero(max(_c))                                          AS c,
       toFloat64OrZero(max(_d))                                        AS d,
       toFloat64OrZero(max(_e))                                        AS e,
       toFloat64OrZero(max(_f))                                        AS f,
       toDateTime64OrNull(max(_g))                                     as g,
       toDateTime64OrNull(max(_h))                                     as h,
       toDateTime64OrNull(max(_i))                                     as i,
       max(_k)                                                         as k,
       max(_l)                                                         as l,
       max(_m)                                                         as m
FROM (
      SELECT event_uuid,
             datetime as _datetime,
             if(key = 'a', string_value, '')                           AS _a,
             if(key = 'b', string_value, '')                           AS _b,
             if(key = 'c', string_value, '')                           AS _c,
             if(key = 'd', string_value, '')                           AS _d,
             if(key = 'e', string_value, '')                           AS _e,
             if(key = 'f', string_value, '')                           AS _f,
             if(key = 'g', string_value, '')                           AS _g,
             if(key = 'h', string_value, '')                           AS _h,
             if(key = 'i', string_value, '')                           AS _i,
             if(key = 'k', string_value, '')                           AS _k,
             if(key = 'l', string_value, '')                           AS _l,
             if(key = 'm', string_value, '')                           AS _m
      FROM holistic.gamedev_raw_data
      WHERE datetime >= '2022-01-15' AS t
)
GROUP BY event_uuid;

json_parsed.sql

CREATE MATERIALIZED VIEW example.parsed
            (
             `event_uuid` String,
             `datetime` DateTime,
             `a` Int32,
             `b` Int32,
             `c` Int32,
             `d` Float64,
             `e` Float64,
             `f` Float64,
             `g` DateTime64,
             `h` DateTime64,
             `i` DateTime64,
             `k` String,
             `l` String,
             `m` String
             )
            ENGINE = MergeTree
                PARTITION BY client_version
                ORDER BY client_timestamp
                SETTINGS index_granularity = 8192
AS
SELECT datetime as _datetime,
     if(JSONHas(json_value, 'a'), toInt32OrZero(JSONExtractString(json_value, 'a')), 0) as a,
     if(JSONHas(json_value, 'b'), toInt32OrZero(JSONExtractString(json_value, 'b')), 0) as b,
     if(JSONHas(json_value, 'c'), toInt32OrZero(JSONExtractString(json_value, 'c')), 0) as c,
     if(JSONHas(json_value, 'd'), toFloat64OrZero(JSONExtractString(json_value, 'd')), 0) as d,
     if(JSONHas(json_value, 'e'), toFloat64OrZero(JSONExtractString(json_value, 'e')), 0) as e,
     if(JSONHas(json_value, 'f'), toFloat64OrZero(JSONExtractString(json_value, 'f')), 0) as f,
     if(JSONHas(json_value, 'g'), toDateTime64OrNull(JSONExtractString(json_value, 'g')), null) as g,
     if(JSONHas(json_value, 'h'), toDateTime64OrNull(JSONExtractString(json_value, 'h')), null) as h,
     if(JSONHas(json_value, 'i'), toDateTime64OrNull(JSONExtractString(json_value, 'i')), null) as i,
     if(JSONHas(json_value, 'k'), JSONExtractString(json_value, 'k'), '') as k,
     if(JSONHas(json_value, 'l'), JSONExtractString(json_value, 'l'), '') as l,
     if(JSONHas(json_value, 'm'), JSONExtractString(json_value, 'm'), '') as m
FROM holistic.gamedev_raw_data
WHERE datetime >= '2022-01-15' AS t

prod_raw_data.sql

create table example.raw_data
(
    datetime      DateTime,
    json_value    String,
)
    engine = MergeTree PARTITION BY toYYYYMM(datetime)
        ORDER BY datetime
        SETTINGS index_granularity = 8192;

raw_data.sql

// практически не требует изменений

create table example.raw_data
(
    datetime      DateTime,
    event_uuid    String           default '',
    key           String           default '',
    string_value  String           default '',
    json_value    String,
)
    engine = MergeTree PARTITION BY toYYYYMM(datetime)
        ORDER BY datetime
        SETTINGS index_granularity = 8192;