Last active
October 14, 2022 05:57
-
-
Save ruslux/80fd8a35fab3a6037cdd2c302dd70f0c to your computer and use it in GitHub Desktop.
Example for materialized view from raw data store
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create table example.raw_data | |
| ( | |
| datetime DateTime, | |
| json_value String, | |
| ) | |
| engine = MergeTree PARTITION BY toYYYYMM(datetime) | |
| ORDER BY datetime | |
| SETTINGS index_granularity = 8192; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // практически не требует изменений | |
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment