· 5 years ago · Jun 17, 2020, 11:06 AM
1DROP TABLE IF EXISTS bet.match;
2CREATE TABLE IF NOT EXISTS bet.match (
3 match_id Int64 CODEC(ZSTD(10)),
4 sport_id Int8 CODEC(ZSTD(10)),
5 source_id Int8 CODEC(ZSTD(10)),
6 create_date DateTime CODEC(ZSTD(10)),
7 update_date DateTime DEFAULT now() CODEC(ZSTD(10)),
8 start_date DateTime CODEC(ZSTD(10)),
9 match_sid String CODEC(ZSTD(10)),
10 league String CODEC(ZSTD(10)),
11 league_sid String CODEC(ZSTD(10)),
12 homeTeam String CODEC(ZSTD(10)),
13 homeTeam_sid String CODEC(ZSTD(10)),
14 awayTeam String CODEC(ZSTD(10)),
15 awayTeam_sid String CODEC(ZSTD(10)),
16 country String CODEC(ZSTD(10)),
17 country_sid String CODEC(ZSTD(10)),
18 location String CODEC(ZSTD(10)),
19 location_sid String CODEC(ZSTD(10)),
20 stage String CODEC(ZSTD(10)),
21 stage_sid String CODEC(ZSTD(10)),
22 uri String CODEC(ZSTD(10)),
23 outcome Array(UUID) CODEC(ZSTD(10)),
24 odds Array(Float32) CODEC(ZSTD(10))
25)
26ENGINE = ReplacingMergeTree(update_date)
27PARTITION BY ( toYYYYMM(toDateTime(toUInt32(bitShiftRight(match_id, 32)))) )
28ORDER BY (match_id);
29
30
31CREATE MATERIALIZED VIEW IF NOT EXISTS bet.match_updates
32ENGINE = AggregatingMergeTree()
33PARTITION BY (toYYYYMM(update_date))
34ORDER BY (update_date)
35POPULATE
36 AS
37 SELECT
38 match_id,
39 MAX(update_date) as update_date
40 FROM bet.match
41 GROUP BY match_id;
42
43
44CREATE MATERIALIZED VIEW IF NOT EXISTS bet.match_sid_to_id
45ENGINE = ReplacingMergeTree()
46PARTITION BY ( sipHash64(sport_id,source_id,match_sid) % 16 )
47ORDER BY (sport_id,source_id,match_sid)
48POPULATE
49 AS
50 SELECT
51 match_id,
52 sport_id,
53 source_id,
54 match_sid
55 FROM bet.match;