Appearance
ClickHouse 怎么给千万行日志的 IP 补归属地?用 Python 导一张字典,按省统计秒出
数据仓库里最常见的尴尬场面:市场部要一份「上周各省访问量和转化率」,你打开日志表,里面只有一个 ip 字段,字符串。ClickHouse 查聚合很快,但它不认识「广东」这两个字——IP 到地名这一步,得你自己补。
在 SQL 里逐行调接口是最容易被想到、也最容易翻车的方案:一天三千万行日志,逐行一次 HTTP,跑一年也跑不完。这篇文章把这件事拆成两步——离线把 IP 补齐成一张字典文件,线上用字典查询,中间没有任何 HTTP。
一、先算一笔账,看看规模有多吓人
以一天三千万行、保留 30 天的访问日志为例:
- 行数:3 千万/天 × 30 天 = 9 亿行;
- 独立 IP:按访客构成不同,一天大概 30 万 ~ 80 万个;
- 去重后要查的次数就是 30 万 ~ 80 万次,跟行数无关——这是第一个关键点,按「独立 IP」而不是「行」去调接口;
- 免费版接口是 60 次/分钟/IP,也就是一秒一次,一天 8.6 万次,80 万个 IP 要跑 9 天多。
所以真正的工程问题不是「怎么调接口」,而是「怎么把调用量砍下来」。两个有效手段:
- 频次过滤。真实流量里长尾 IP 极多、但总占比很小。把「出现次数 ≥ 5 次」的 IP 挑出来先补,往往能把 80 万降到几万,视觉效果几乎没损失;剩下的统一算「未知」。
- 升级额度。要全量补齐就上 VIP:18 万次/分钟,0.05 元/千次起,80 万个 IP 大概 40 元跑完一次全量,后面按天做增量。
二、方案:字典文件 + 字典查询
ClickHouse 支持「字典」(dictionary),可以理解为常驻内存的一张 KV 表,用 dictGet 按 key 取字段值,比 join 快得多。整条链路是:
- 从日志表按 IP 聚合,拿到「去重 + 频次」的列表;
- Python 批量补齐归属地,写出
ip,prov,city,isp的 CSV; - CSV 放进 ClickHouse 的
user_files目录,建一个HASHED布局的字典; - 查询时用
dictGetOrDefault('ip_geo', 'prov', ip, '未知'),想按省、按城市、按运营商聚合都是一句话的事。
两个地方要先说清楚,省得走弯路:
- ClickHouse 的字典有 HTTP 源,但它不适用于按 IP 查询的接口。HTTP 源是「整表拉取 + 全量解析」,也就是它期待对端一次返回整张表(比如私有化部署给你导出的全量数据);而公开接口是「一次请求一个 IP」,语义对不上。所以公开接口场景下,「离线补齐 + 文件字典」才是对的路子。
- 如果你手里本来就有 CIDR 形式的段数据(比如自建的机房段、运营商段表),可以改用
ip_trie布局做最长前缀匹配;但只有「单个 IP 的地名」时,HASHED字典最简单、内存也最省。
三、Python 实现:从日志表到字典文件
脚本只依赖标准库。三件事:从 ClickHouse 拉 IP 列表、限速批量查、写 CSV。
python
# ip_geo_dict.py —— 给 ClickHouse 日志表生成 IP 归属地字典
# 依赖:仅标准库(Python 3.8+)
import csv, json, time, urllib.parse, urllib.request
API = "https://ip9.com.cn/get" # 不传 ip 查当前请求方;?ip= 查指定 IP
CH = "http://127.0.0.1:8123" # ClickHouse HTTP 接口
OUT = "/var/lib/clickhouse/user_files/ip_geo.csv" # 字典文件必须落在 user_files 目录
MIN_HITS = 5 # 出现次数低于这个阈值的长尾 IP 不查,统一记「未知」
RATE = 1.05 # 免费版 60 次/分钟/IP ≈ 每秒 1 次,留点余量;VIP 可以调到 0.001
def fetch_ips():
"""从 ClickHouse 拿「去重 + 频次」的 IP,TSV 逐行读,别一次读进内存。
ORDER BY 频次降序:中途断了,必须先补的高频 IP 已经在文件里了。"""
q = ("SELECT ip, count() AS c FROM access_log GROUP BY ip "
"HAVING c >= {n} ORDER BY c DESC FORMAT TSV").format(n=MIN_HITS)
req = urllib.request.Request(CH + "/?query=" + urllib.parse.quote(q),
headers={"X-ClickHouse-User": "default",
"X-ClickHouse-Key": ""})
with urllib.request.urlopen(req, timeout=120) as r:
for line in r.read().decode("utf-8").splitlines():
ip, c = line.split("\t")
yield ip, int(c)
_cache, _last = {}, 0.0
def geo(ip):
"""查一个 IP 的归属地;失败统一返回 None,调用方按「未知」处理"""
global _last
gap = RATE - (time.time() - _last) # 严格按 RATE 限速,别撞 429
if gap > 0:
time.sleep(gap)
_last = time.time()
req = urllib.request.Request(API + "?ip=" + urllib.parse.quote(ip),
headers={"User-Agent": "ip9-dict-builder/1.0"})
try:
with urllib.request.urlopen(req, timeout=5) as r:
body = json.loads(r.read().decode("utf-8"))
except Exception:
return None # 超时/网络异常:这一轮先记未知,下次增量补
if body.get("ret") != 200: # 非法 IP 是 400,限速是 429
return None
d = body.get("data") or {}
return d if d.get("ip") else None # 出错时 data 是空数组,这里统一挡掉
def main():
hit = miss = 0
with open(OUT, "w", newline="", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["ip", "prov", "city", "isp"])
for ip, times in fetch_ips():
d = _cache.get(ip, 0) or geo(ip)
_cache[ip] = d
if d:
w.writerow([ip, d.get("prov") or d.get("country") or "未知",
d.get("city") or "", d.get("isp") or ""])
hit += 1
else:
w.writerow([ip, "未知", "", ""]) # 未命中也要写,避免日志里的 IP 落空
miss += 1
if (hit + miss) % 2000 == 0:
f.flush() # 边跑边落盘,断了不用从头再来
print("已处理 %d 个(命中 %d,高频优先)" % (hit + miss, hit), flush=True)
print("完成:命中 %d,未命中 %d → %s" % (hit, miss, OUT))
if __name__ == "__main__":
main()跑完你会得到一个几十万行、大概十几 MB 的 CSV。注意两个细节:未命中的 IP 也要写一行「未知」,否则字典里没有这个 key,后面的 dictGetOrDefault 虽然不会报错,但你就分不清「没查过」和「查了没结果」;边跑边 flush,几十万次请求要跑很久,中断了能接着补。
四、ClickHouse 侧:建字典 + 按省统计
sql
-- ① 建字典:文件源 + HASHED 布局(key 是完整 IP,精确匹配,内存里常驻)
CREATE DICTIONARY ip_geo
(
ip String,
prov String DEFAULT '未知',
city String DEFAULT '',
isp String DEFAULT ''
)
PRIMARY KEY ip
SOURCE(FILE(path '/var/lib/clickhouse/user_files/ip_geo.csv' format 'CSVWithNames'))
LAYOUT(HASHED())
LIFETIME(MIN 3600 MAX 7200); -- 每小时到两小时之间重载一次
-- ② 验证:字典加载状态、条目数、占了多少内存
SELECT name, status, element_count, bytes_allocated, last_successful_update_time
FROM system.dictionaries WHERE name = 'ip_geo';
-- ③ 查询:按省统计最近 7 天的访问量
SELECT dictGetOrDefault('ip_geo', 'prov', ip, '未知') AS prov,
count() AS pv,
uniqExact(ip) AS uv
FROM access_log
WHERE ts >= today() - 7
GROUP BY prov
ORDER BY pv DESC
LIMIT 20;
-- ④ 想更细一点:省市 + 运营商三维
SELECT dictGetOrDefault('ip_geo', 'prov', ip, '未知') AS prov,
dictGetOrDefault('ip_geo', 'city', ip, '未知') AS city,
dictGetOrDefault('ip_geo', 'isp', ip, '未知') AS isp,
count() AS pv
FROM access_log
WHERE ts >= today() - 1
GROUP BY prov, city, isp
ORDER BY pv DESC
LIMIT 50;几个实测里容易踩的点:
- 字典 key 的类型必须和表里
ip字段的类型一致。 如果建表时把 IP 存成了IPv6类型,字典的PRIMARY KEY ip也得声明成IPv6,否则dictGet会直接报类型不匹配。 - 内存要有个预期。 80 万条、四个字符串字段的 HASHED 字典,
bytes_allocated通常落在 60~100MB,常驻内存,可以接受;如果 IP 数量到千万级,考虑先只保留高频 IP,或者换SSD_CACHE之类需要磁盘的布局。 LIFETIME别设太短。 它决定字典多久重载一次文件,设成几分钟会让后台不断重读大文件;按天的补数节奏,1~2 小时足够。- 更新是「替换文件 + 等重载」。 Python 脚本先生成
ip_geo.csv.tmp,跑完再mv覆盖,避免 ClickHouse 读到写了一半的文件。
五、落地注意事项
别把接口调用放进查询链路。 这篇文章的方案之所以快,是因为查询路径上一点外部依赖都没有——字典在内存里,dictGet 是纯内存操作。反过来,只要有人在 SQL 里调 HTTP,你的报表稳定性就交给了对端。
长尾 IP 的处理要说明白。 按频次阈值过滤后,「未知」一般占到独立 IP 数的八成以上、请求量的百分之几。报表里要把这一桶显式留出来,别让运营以为「未知」是海外流量。
增量补数比全量重跑划算。 日志是按天增长的,每天新增的独立 IP 通常是老 IP 的小部分。做法是每天只 GROUP BY 当天的 IP、减去字典里已有的,补完追加到 CSV 尾部(ClickHouse 的 FILE 字典需要重载,追加后等下一次 LIFETIME 自动生效;或者干脆按天分文件、按天建字典)。
要区县级精度就得上 VIP。 免费版返回到城市级;area(区县)、ip_type(ISP/企业/IDC)、ip_asn 是 VIP 字段。区县能让你把「同城」做到「同区」,ip_type 则能把机房流量从真实用户里剔出来——做「真实地域分布」报表时这一步很关键,不然 IDC 段会把大城市的数据顶高。
合规别忘了。 IP 归属地属于个人信息范畴,做地域统计时只保留统计需要的字段、约定留存期限,导出 CSV 也要注意权限,别把一份带 IP 的文件长期放在共享目录里。
总结
「日志里只有 IP、报表要地名」这件事,正确解法是把它变成一次离线批处理:按独立 IP 去重、按频次砍掉长尾、批量补齐成 CSV、在 ClickHouse 里做成字典。之后无论按省、按市、按运营商聚合,都是毫秒级的纯内存查询,链路里没有任何外部接口。
接口还是那一个:https://ip9.com.cn/get?ip=xxx(不传参数查当前请求方,IPv4/IPv6 都支持;免费版 60 次/分钟,返回 prov、city、isp、lng、lat 等字段)。需要全量跑、或者要用区县/ip_type/ip_asn 做更细的分层,走 VIP 版更合适,具体额度和计费以官网 https://www.ip9.com.cn 为准。