--- title: "clickhouseでログからどの地域からのアクセスが多いか分かるヒートマップを作ってみた | grasys blog" url: "https://blog.grasys.io/post/hk/create-heatmap-with-clickhouse/" description: "こんにちは、エンジニアのHKです。 今回はclickhouseを使って、grafanaでどの地域からのアクセスが多いかが視覚的にわかりやすく表示されるヒートマップを作成してみました。 clickhouseとは 詳しい説明は公式のドキュメントに書いてありますが、一言で言うと、「大規模なデータの分析を高速で行える列指向デ…" --- # clickhouseでログからどの地域からのアクセスが多いか分かるヒートマップを作ってみた - ![](/_astro/noicon.CTHOhNiB_1HMs6A.webp)[HK](/authors/hk/) - 公開日：2025年8月13日 - カテゴリー：[Tech](/categories/tech/) - タグ：[#clickhouse](/tags/clickhouse/)[#Grafana](/tags/grafana/) ![clickhouseでログからどの地域からのアクセスが多いか分かるヒートマップを作ってみた](/_astro/hero.CcybaYxC_1bHWir.webp) こんにちは、エンジニアのHKです。 今回はclickhouseを使って、grafanaでどの地域からのアクセスが多いかが視覚的にわかりやすく表示されるヒートマップを作成してみました。 ## clickhouseとは 詳しい説明は[公式のドキュメント](https://clickhouse.com/docs/jp/intro)に書いてありますが、一言で言うと、「大規模なデータの分析を高速で行える列指向データベース」です。 今回はそのclickhouseの機能の一つであるIPアドレス検索機能を使って、大規模なアクセスログのデータからどに地域からのアクセスが多いかがわかるヒートマップを作れるのでそれを試してみました。 ## インストール [公式チュートリアル](https://clickhouse.com/docs/jp/install/quick-install-curl)があるのでその手順に従ってインストールします。 Plain textcontent\_copy ``` # インストール curl https://clickhouse.com/ | sh # サーバー起動 ./clickhouse server # (サーバー起動中に別ウィンドウで)clientを起動 ./clickhouse client ``` 毎回サーバーを起動してから別ウィンドウでclientを開くのも面倒なので、ユニットファイルを作ってサービス化します。 Plain textcontent\_copy ``` [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.target ``` Plain textcontent\_copy ``` systemctl daemon-reload systemctl start clickhouse-server ``` これで最初の準備は完了です。 ## 辞書作成 次に、IPアドレスから位置情報を取得するのに必要な辞書を作成します。 clickhouseでは大量のデータを処理する時に[辞書](https://clickhouse.com/docs/jp/sql-reference/dictionaries)を使うことで、処理を手助けして高速化が実現できます。 辞書作成の手順は基本的に[公式の記事](https://clickhouse.com/blog/geolocating-ips-in-clickhouse-and-grafana)を参考にしています。 まず、外部のGeoIPデータベースファイル(URLのデータに直接クエリをかけます)をもとに辞書作成用のテーブルを用意します。 Plain textcontent\_copy ``` 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'); ``` Plain textcontent\_copy ``` :) 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形式に変換して辞書を作るのに適した形にします。 Plain textcontent\_copy ``` :) 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_url ``` geoipテーブルに入れたデータをもとに、IPアドレス範囲(CIDR)をキーとしてIPアドレス→位置情報を取得する辞書を作成します。 Plain textcontent\_copy ``` :) 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](https://clickhouse.com/docs/sql-reference/dictionaries#ip_trie) という辞書タイプを採用することでCIDR範囲からIPアドレス→位置情報の取得までが迅速にできる辞書が作成できます。 この辞書さえあれば、dictGet()関数を使ってIPアドレス→位置情報の取得ができます。 ## 分析するアクセスログを準備 今回のヒートマップ用の分析に使うテーブルを準備します。 Plain textcontent\_copy ``` :) 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内にテーブルを作成します。 Plain textcontent\_copy ``` # du -sh /etc/clickhouse/datasets/access_log.csv 2.8G /etc/clickhouse/datasets/access_log.csv ``` Plain textcontent\_copy ``` :) 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 = ','; ``` ちゃんとテーブルにデータが入ったか確認します。 Plain textcontent\_copy ``` :) 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のデータソースがあるのでそれを使います。 ![データソース作成(grafana)の画面（1）](/_astro/01-fa4693d0a263e8e1c71c48e0575e03c5-1024x471-2e9e433e.DtTVHyMH_Zna4vu.webp) インストール後、clickhouseを選んでdatasourceを作成します。 ![データソース作成(grafana)の画面（2）](/_astro/02-2fbd519860fa6b7246982dbefa69e7ae-1024x343-a0284883.JkSzxMz-_Zz1VVw.webp) ## grafana上でヒートマップ表示 grafanaでヒートマップを表示してみましょう。 grafanaのダッシュボードでヒートマップを表示する時には、座標はジオハッシュ(geohash)という形式に変換する必要があるので、geohashEncode関数を利用した以下のクエリでデータを変換します。 Plain textcontent\_copy ``` 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 hash ``` visualizationをGeomapにして、日付をログに対応した日時に設定することで、ヒートマップが表示されました。 データがアメリカのものなのでやはりアメリカからのアクセスが多い結果になりました。 ![grafana上でヒートマップ表示の画面（1）](/_astro/03-a1a470699d2c5738128f8aef65ee2e9c-1-1024x337-93909ce6.BlNbVVL__1uOJrk.webp) 数千万行のデータでも高速でヒートマップの表示まで確認できたので、clickhouseの強みである大規模なデータの高速分析を体感することができました。 マップを詳細にして地域を絞ることは可能ですが、マップの縮尺を変更するときは、Radius (半径)、Blur (ぼかし)などのパラメーターもセットで調整しないと表示が見辛くなるので注意です。 ![grafana上でヒートマップ表示の画面（2）](/_astro/04-ebede765da17278927821f7139906604-51f91e01.BGcVxYjW_Z16IQvy.webp) ![grafana上でヒートマップ表示の画面（3）](/_astro/05-d8f987ffcecb3ac9b6a7a73f91358cae-1024x317-bc70ecab.uKIENsX2_Pg2Al.webp) 試しに日本からのアクセスに絞ってみましたが、アメリカのデータなので日本からのアクセスは少なめですね。 今回はclickhouseのインストールから、アクセスログを分析してgrafanaヒートマップに表示するまでをやってみました。大規模なデータがあって分析を高速でする方法を探している方はぜひ試してみてください。 それでは、また。 ## この記事を書いた人 [![](/_astro/noicon.CTHOhNiB_Z24aAgM.webp)](/authors/hk/) ### [HK](/authors/hk/) HKのプロフィールと執筆記事をご覧いただけます。 [プロフィールと記事一覧](/authors/hk/)