/
add_clickhouse.cr
72 lines (67 loc) · 1.82 KB
/
add_clickhouse.cr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
class AddClickhouseTable < LuckyCli::Task
summary "Add the clickhouse table"
name "kind.clickhouse"
def initialize
@client = Clickhouse.new(host: ENV["CLICKHOUSE_HOST"]?.try(&.strip), port: 8123)
end
def call
create_database
add_events
add_sessions
end
def create_database
sql = <<-SQL
CREATE DATABASE IF NOT EXISTS kindmetrics
SQL
res = @client.execute(sql)
end
def add_events
buf = <<-SQL
CREATE TABLE IF NOT EXISTS kindmetrics.events (
`id` UInt64,
`user_id` String,
`name` String,
`domain` String,
`url` String,
`referrer_source` Nullable(String),
`path` String,
`device` Nullable(String),
`operative_system` Nullable(String),
`referrer_domain` Nullable(String),
`referrer` Nullable(String),
`browser_name` Nullable(String),
`country` Nullable(String),
`domain_id` UInt64,
`session_id` UInt64,
`created_at` DateTime
)
ENGINE = MergeTree PARTITION BY toYYYYMM(created_at) ORDER BY (user_id, created_at)
SQL
create = @client.execute buf
end
def add_sessions
buf = <<-SQL
CREATE TABLE IF NOT EXISTS kindmetrics.sessions (
`id` UInt64,
`mark` UInt8,
`user_id` String,
`domain` String,
`url` String,
`referrer_source` Nullable(String),
`path` String,
`device` Nullable(String),
`operative_system` Nullable(String),
`referrer_domain` Nullable(String),
`referrer` Nullable(String),
`browser_name` Nullable(String),
`country` Nullable(String),
`domain_id` UInt64,
`is_bounce` UInt32,
`length` Nullable(UInt64),
`created_at` DateTime
)
ENGINE = MergeTree PARTITION BY toYYYYMM(created_at) ORDER BY (user_id, created_at)
SQL
create = @client.execute buf
end
end