-
Notifications
You must be signed in to change notification settings - Fork 29
Expand file tree
/
Copy path002_libp2p_join_leave_local_peer_id.up.sql
More file actions
70 lines (66 loc) · 6.46 KB
/
Copy path002_libp2p_join_leave_local_peer_id.up.sql
File metadata and controls
70 lines (66 loc) · 6.46 KB
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
DROP TABLE IF EXISTS default.libp2p_join ON CLUSTER '{cluster}' SYNC;
DROP TABLE IF EXISTS default.libp2p_join_local ON CLUSTER '{cluster}' SYNC;
DROP TABLE IF EXISTS default.libp2p_leave ON CLUSTER '{cluster}' SYNC;
DROP TABLE IF EXISTS default.libp2p_leave_local ON CLUSTER '{cluster}' SYNC;
CREATE TABLE IF NOT EXISTS default.libp2p_join_local ON CLUSTER '{cluster}'
(
`updated_date_time` DateTime COMMENT 'Timestamp when the record was last updated' CODEC(DoubleDelta, ZSTD(1)),
`event_date_time` DateTime64(3) COMMENT 'Timestamp of the event' CODEC(DoubleDelta, ZSTD(1)),
`topic_layer` LowCardinality(String) COMMENT 'Layer of the topic',
`topic_fork_digest_value` LowCardinality(String) COMMENT 'Fork digest value of the topic',
`topic_name` LowCardinality(String) COMMENT 'Name of the topic',
`topic_encoding` LowCardinality(String) COMMENT 'Encoding of the topic',
`local_peer_id_unique_key` Int64 COMMENT 'Unique key derived from the libp2p peer.ID of the local host that joined the topic',
`meta_client_name` LowCardinality(String) COMMENT 'Name of the client that generated the event',
`meta_client_version` LowCardinality(String) COMMENT 'Version of the client that generated the event',
`meta_client_implementation` LowCardinality(String) COMMENT 'Implementation of the client that generated the event',
`meta_client_os` LowCardinality(String) COMMENT 'Operating system of the client that generated the event',
`meta_client_ip` Nullable(IPv6) COMMENT 'IP address of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_city` LowCardinality(String) COMMENT 'City of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_country` LowCardinality(String) COMMENT 'Country of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_country_code` LowCardinality(String) COMMENT 'Country code of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_continent_code` LowCardinality(String) COMMENT 'Continent code of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_longitude` Nullable(Float64) COMMENT 'Longitude of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_latitude` Nullable(Float64) COMMENT 'Latitude of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_autonomous_system_number` Nullable(UInt32) COMMENT 'Autonomous system number of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_autonomous_system_organization` Nullable(String) COMMENT 'Autonomous system organization of the client that generated the event' CODEC(ZSTD(1)),
`meta_network_name` LowCardinality(String) COMMENT 'Ethereum network name'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/{table}', '{replica}', updated_date_time)
PARTITION BY (meta_network_name, toYYYYMM(event_date_time))
ORDER BY (meta_network_name, event_date_time, meta_client_name, local_peer_id_unique_key, topic_fork_digest_value, topic_name)
COMMENT 'Contains the details of the JOIN events from the libp2p client.';
CREATE TABLE IF NOT EXISTS default.libp2p_leave_local ON CLUSTER '{cluster}'
(
`updated_date_time` DateTime COMMENT 'Timestamp when the record was last updated' CODEC(DoubleDelta, ZSTD(1)),
`event_date_time` DateTime64(3) COMMENT 'Timestamp of the event' CODEC(DoubleDelta, ZSTD(1)),
`topic_layer` LowCardinality(String) COMMENT 'Layer of the topic',
`topic_fork_digest_value` LowCardinality(String) COMMENT 'Fork digest value of the topic',
`topic_name` LowCardinality(String) COMMENT 'Name of the topic',
`topic_encoding` LowCardinality(String) COMMENT 'Encoding of the topic',
`local_peer_id_unique_key` Int64 COMMENT 'Unique key derived from the libp2p peer.ID of the local host that left the topic',
`meta_client_name` LowCardinality(String) COMMENT 'Name of the client that generated the event',
`meta_client_version` LowCardinality(String) COMMENT 'Version of the client that generated the event',
`meta_client_implementation` LowCardinality(String) COMMENT 'Implementation of the client that generated the event',
`meta_client_os` LowCardinality(String) COMMENT 'Operating system of the client that generated the event',
`meta_client_ip` Nullable(IPv6) COMMENT 'IP address of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_city` LowCardinality(String) COMMENT 'City of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_country` LowCardinality(String) COMMENT 'Country of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_country_code` LowCardinality(String) COMMENT 'Country code of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_continent_code` LowCardinality(String) COMMENT 'Continent code of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_longitude` Nullable(Float64) COMMENT 'Longitude of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_latitude` Nullable(Float64) COMMENT 'Latitude of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_autonomous_system_number` Nullable(UInt32) COMMENT 'Autonomous system number of the client that generated the event' CODEC(ZSTD(1)),
`meta_client_geo_autonomous_system_organization` Nullable(String) COMMENT 'Autonomous system organization of the client that generated the event' CODEC(ZSTD(1)),
`meta_network_name` LowCardinality(String) COMMENT 'Ethereum network name'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/{table}', '{replica}', updated_date_time)
PARTITION BY (meta_network_name, toYYYYMM(event_date_time))
ORDER BY (meta_network_name, event_date_time, meta_client_name, local_peer_id_unique_key, topic_fork_digest_value, topic_name)
COMMENT 'Contains the details of the LEAVE events from the libp2p client.';
CREATE TABLE IF NOT EXISTS default.libp2p_join ON CLUSTER '{cluster}'
AS default.libp2p_join_local
ENGINE = Distributed('{cluster}', 'default', 'libp2p_join_local', cityHash64(event_date_time, meta_network_name, meta_client_name, local_peer_id_unique_key, topic_fork_digest_value, topic_name));
CREATE TABLE IF NOT EXISTS default.libp2p_leave ON CLUSTER '{cluster}'
AS default.libp2p_leave_local
ENGINE = Distributed('{cluster}', 'default', 'libp2p_leave_local', cityHash64(event_date_time, meta_network_name, meta_client_name, local_peer_id_unique_key, topic_fork_digest_value, topic_name));