bgp.tools

import.bash

curl -s -H "user-agent: " https://bgp.tools/table.jsonl \
 | jq '{cidr: .CIDR, asn: .ASN, hits: .Hits}' \
 | clickhouse-client --user admin --password "${CH_PWD}" --database bgp --query="INSERT INTO routes FORMAT JSONEachRow"
 
 curl -s -H "user-agent: " https://bgp.tools/asns.csv \
 | csvformat -U 1 -D ',' \
 | python3 -c 'import csv,json,sys; [print(json.dumps({**row, "asn": int(row["asn"].replace("AS", ""))})) for row in csv.DictReader(sys.stdin) if (lambda asn: asn >= 0)(int(row["asn"].replace("AS", "")) if row["asn"].replace("AS", "").isdigit() else -1)]' \
 | clickhouse-client --user admin --password "${CH_PWD}" --database bgp --query="INSERT INTO asns FORMAT JSONEachRow"

clickhouse-client --user admin --password "${CH_PWD}" --database bgp --query="OPTIMIZE TABLE asns FINAL"

schema.sql

CREATE TABLE bgp.routes
(
    `cidr` String CODEC(ZSTD(6)),
    `asn` UInt32 CODEC(ZSTD(6)),
    `hits` UInt32 CODEC(ZSTD(6)),
    `observed_at` DateTime DEFAULT now() CODEC(DoubleDelta, ZSTD(6)),
    `ip_version` UInt8 MATERIALIZED if(position(cidr, ':') > 0, 6, 4),
    `cidr_hash` UInt64 MATERIALIZED cityHash64(cidr),
    `cidr_prefix_length` UInt8 MATERIALIZED toUInt8(substring(cidr, position(cidr, '/') + 1)),
    INDEX idx_asn asn TYPE minmax GRANULARITY 1,
    INDEX idx_cidr_hash cidr_hash TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = ReplacingMergeTree(observed_at)
ORDER BY (cidr, asn)
SETTINGS index_granularity = 4096

CREATE TABLE bgp.asns
(
    `asn` UInt32 CODEC(ZSTD(6)),
    `name` String CODEC(ZSTD(6)),
    `class` String CODEC(ZSTD(6)),
    `cc` FixedString(2) CODEC(ZSTD(6)),
    `observed_at` DateTime DEFAULT now() CODEC(DoubleDelta, ZSTD(6))
)
ENGINE = ReplacingMergeTree(observed_at)
ORDER BY asn
SETTINGS index_granularity = 8192