目次
こんにちは、エンジニアのHKです。
今回はclickhouseを使って、grafanaでどの地域からのアクセスが多いかが視覚的にわかりやすく表示されるヒートマップを作成してみました。
clickhouseとは
詳しい説明は公式のドキュメントに書いてありますが、一言で言うと、「大規模なデータの分析を高速で行える列指向データベース」です。
今回はそのclickhouseの機能の一つであるIPアドレス検索機能を使って、大規模なアクセスログのデータからどに地域からのアクセスが多いかがわかるヒートマップを作れるのでそれを試してみました。
インストール
公式チュートリアルがあるのでその手順に従ってインストールします。
# インストール
curl https://clickhouse.com/ | sh
# サーバー起動
./clickhouse server
# (サーバー起動中に別ウィンドウで)clientを起動
./clickhouse client
毎回サーバーを起動してから別ウィンドウでclientを開くのも面倒なので、ユニットファイルを作ってサービス化します。
[Unit]
Description=ClickHouse Server
After=network.target
[Service]
Type=simple
User=root
ExecStart=/etc/clickhouse/clickhouse server
Restart=on-failure
LimitNOFILE=1048576
[Install]
WantedBy=multi-user.targetsystemctl daemon-reload
systemctl start clickhouse-serverこれで最初の準備は完了です。
辞書作成
次に、IPアドレスから位置情報を取得するのに必要な辞書を作成します。
clickhouseでは大量のデータを処理する時に辞書を使うことで、処理を手助けして高速化が実現できます。
辞書作成の手順は基本的に公式の記事を参考にしています。
まず、外部のGeoIPデータベースファイル(URLのデータに直接クエリをかけます)をもとに辞書作成用のテーブルを用意します。
create table geoip_url(
ip_range_start IPv4,
ip_range_end IPv4,
country_code Nullable(String),
state1 Nullable(String),
state2 Nullable(String),
city Nullable(String),
postcode Nullable(String),
latitude Float64,
longitude Float64,
timezone Nullable(String)
) engine=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV');:) SELECT name, type
FROM system.columns
WHERE table = 'geoip_url';
SELECT
name,
type
FROM system.columns
WHERE `table` = 'geoip_url'
Query id: 6f598911-86b5-4f83-bdbe-72d85388d358
┌─name───────────┬─type─────────────┐
1. │ ip_range_start │ IPv4 │
2. │ ip_range_end │ IPv4 │
3. │ country_code │ Nullable(String) │
4. │ state1 │ Nullable(String) │
5. │ state2 │ Nullable(String) │
6. │ city │ Nullable(String) │
7. │ postcode │ Nullable(String) │
8. │ latitude │ Float64 │
9. │ longitude │ Float64 │
10. │ timezone │ Nullable(String) │
└────────────────┴──────────────────┘
10 rows in set. Elapsed: 0.002 sec.テーブルに入れたデータの要素を見てみると、IPアドレスの範囲を示すip_range_startとip_range_endがあるので、これをCIDR形式に変換して辞書を作るのに適した形にします。
:) create table geoip (
cidr String,
latitude Float64,
longitude Float64,
country_code String
)
engine = MergeTree()
order by cidr;
insert into
geoip
with
bitXor(ip_range_start, ip_range_end) as xor,
if(xor != 0, ceil(log2(xor)), 0) as unmatched,
32 - unmatched as cidr_suffix,
toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
latitude,
longitude,
country_code
from
geoip_urlgeoipテーブルに入れたデータをもとに、IPアドレス範囲(CIDR)をキーとしてIPアドレス→位置情報を取得する辞書を作成します。
:) create dictionary ip_trie (
cidr String,
latitude Float64,
longitude Float64,
country_code String
)
primary key cidr
source(clickhouse(table ‘geoip’))
layout(ip_trie)
lifetime(3600);ip_trie という辞書タイプを採用することでCIDR範囲からIPアドレス→位置情報の取得までが迅速にできる辞書が作成できます。
この辞書さえあれば、dictGet()関数を使ってIPアドレス→位置情報の取得ができます。
分析するアクセスログを準備
今回のヒートマップ用の分析に使うテーブルを準備します。
:) CREATE TABLE access_logs (
ip IPv4,
event_date Date,
event_time DateTime,
zone Float32,
cik UInt32,
accession String,
extension String,
code UInt16,
size UInt32,
idx UInt8,
norefer UInt8,
noagent UInt8,
find Float32,
crawler UInt8
) ENGINE = MergeTree()
ORDER BY (event_date, ip, event_time)
SETTINGS index_granularity = 8192;次に、このテーブルに入れて分析するデータなのですが、アクセス元IP付きの大きいアクセスログのデータというのはなかなかネット上に無かったので、今回は米国証券取引委員会（SEC）が提供するEDGAR Log File Data Setsをお借りしました。
アクセスログのcsvデータをサーバーに入れて、それをもとにclickhouse内にテーブルを作成します。
# du -sh /etc/clickhouse/datasets/access_log.csv
2.8G /etc/clickhouse/datasets/access_log.csv:) INSERT INTO access_logs
SELECT
toIPv4(raw_ip),
toDate(raw_date),
toDateTime(raw_date || ' ' || raw_time),
toFloat32(raw_zone),
toUInt32(toFloat32(raw_cik)),
raw_accession,
raw_extension,
toUInt16(toFloat32(raw_code)),
toUInt32(toFloat32(raw_size)),
toUInt8(toFloat32(raw_idx)),
toUInt8(toFloat32(raw_norefer)),
toUInt8(toFloat32(raw_noagent)),
toFloat32(raw_find),
toUInt8(toFloat32(raw_crawler))
FROM file('access_log.csv', 'CSV', 'raw_ip String, raw_date String, raw_time String, raw_zone String, raw_cik String, raw_accession String, raw_extension String, raw_code String, raw_size String, raw_idx String, raw_norefer String, raw_noagent String, raw_find String, raw_crawler String')
SETTINGS
format_csv_delimiter = ',';ちゃんとテーブルにデータが入ったか確認します。
:) SELECT count() FROM access_logs;
SELECT count()
FROM access_logs
Query id: 36611db8-8170-4375-8475-91883f94a9dc
┌──count()─┐
1. │ 26014327 │ -- 26.01 million
└──────────┘
1 row in set. Elapsed: 0.003 sec.
約2600万行、ちゃんとデータは入っていそうですね。
アクセスログが入ったデータベースと位置情報の取得に必要な辞書は用意することはできたので、次はgrafanaでデータを表示します。
データソース作成(grafana)
ヒートマップをgrafanaで表示するためのデータソースを準備します(grafanaはあらかじめインストールしてある前提で進めます)。
データソースは公式にサポートされているclickhouseのデータソースがあるのでそれを使います。
インストール後、clickhouseを選んでdatasourceを作成します。
grafana上でヒートマップ表示
grafanaでヒートマップを表示してみましょう。
grafanaのダッシュボードでヒートマップを表示する時には、座標はジオハッシュ(geohash)という形式に変換する必要があるので、geohashEncode関数を利用した以下のクエリでデータを変換します。
with coords as (
select
dictGet(
'ip_trie',
('latitude', 'longitude'),
tuple(ip)
) as coords,
coords.1 as latitude,
coords.2 as longitude,
geohashEncode(longitude,latitude,4) as hash
from
access_logs
where
longitude != 0
and latitude != 0
)
select
hash,
count() as heat,
round(log10(heat),2) as adj_heat
from
coords
group by
hash
ORDER BY hashvisualizationをGeomapにして、日付をログに対応した日時に設定することで、ヒートマップが表示されました。
データがアメリカのものなのでやはりアメリカからのアクセスが多い結果になりました。
数千万行のデータでも高速でヒートマップの表示まで確認できたので、clickhouseの強みである大規模なデータの高速分析を体感することができました。
マップを詳細にして地域を絞ることは可能ですが、マップの縮尺を変更するときは、Radius (半径)、Blur (ぼかし)などのパラメーターもセットで調整しないと表示が見辛くなるので注意です。
試しに日本からのアクセスに絞ってみましたが、アメリカのデータなので日本からのアクセスは少なめですね。
今回はclickhouseのインストールから、アクセスログを分析してgrafanaヒートマップに表示するまでをやってみました。大規模なデータがあって分析を高速でする方法を探している方はぜひ試してみてください。
それでは、また。
