FAA 停飞日美国天空被清空:ClickHouse 一条 SQL 把 4250 万条航迹画成动画
原文信息:本文译自《The day the sky emptied: rendering the FAA outage with ClickHouse FORMAT PNG》,作者 Alexey Milovidov,发布于 2026 年 11 月 18 日。原文链接:https://clickhouse.com/blog/faa-outage-clickhouse-format-png (纯文本)。原文未注明许可信息。本文为翻译,译者在文末补充了译者注。
译者导语:2026 年 11 月 13 日,美国联邦航空局(FAA)一次故障让全美航班停飞,整片天空在几个小时里被清空。ClickHouse 创始人 Alexey Milovidov 把当天 4250 万条 ADS-B 航迹数据变成了一部 23 秒的动画——没有绘图库,没有图像处理脚本,地图、时钟、时间轴全是一条 SQL 查询吐出来的像素。下面是这篇技术解析的翻译。
停飞那天,天空是怎么被清空的
2026 年 11 月 13 日星期五,FAA 一次故障让美国全境的空中交通停摆。这篇文章用 ADS-B 数据(来源:adsb.exposed)还原了美国本土上空的这一整天,每 5 分钟一帧。
这部动画本身就是由 ClickHouse 渲染出来的。没有绘图库,也没有图像处理脚本:一条 SQL 查询每像素返回一行,内置的 PNG 输出格式就把这些行变成了一张动态 PNG。地图、标题、时钟、底部的时间轴,全是同一结果集里的行。就连字母都是 SQL 画出来的:一个 3×5 的点阵字体,存在一个 map 字面量里。
同一部动画还有动态 PNG、GIF 和 MP4 三个版本,见原文链接。
动画里,每架飞机是一道短拖影,也就是它 5 分钟内飞过的距离。颜色沿用 adsb.exposed 的默认配色:
- 红色随高度升高而褪去;
- 绿色在头 5000 英尺(约 1524 米)内逐渐增强,所以机场附近的爬升和下降呈现黄色和橙色;
- 蓝色代表地速,所以高空巡航的飞机是蓝色的。
(原文配图:14:00 与 20:00 两个 10 分钟窗口的对比图,见原文链接)
上午和往常的周五没什么两样。航线网络自东向西逐渐填满,13:00–14:00(美东时间)之间,画面里每小时同时在空中的飞机接近 15000 架。15:40 左右,位置点数量开始下降,整个下午一路下滑。高空的拖影先变稀,机场一个接一个暗下去。到 20:00,只剩下零星的低空航班和画面边缘的几架过境飞机。
数字:每小时的飞行量
下表是画面范围内每小时的统计(飞机数按 ICAO 地址去重,最后一列是 18000 英尺以上,即 FL180 以上的飞机数):
| 小时(美东时间) | 位置点数 | 飞机数 | 18000 英尺以上 |
|---|---|---|---|
| 12:00 | 4,038,963 | 14,787 | 5,850 |
| 13:00 | 4,151,849 | 14,885 | 6,046 |
| 14:00 | 4,217,014 | 14,957 | 5,995 |
| 15:00 | 3,991,427 | 13,958 | 5,376 |
| 16:00 | 2,556,066 | 10,080 | 3,196 |
| 17:00 | 1,465,549 | 6,304 | 1,658 |
| 18:00 | 949,405 | 4,084 | 907 |
| 19:00 | 543,053 | 2,592 | 632 |
| 20:00 | 418,486 | 2,131 | 559 |
| 21:00 | 566,155 | 2,641 | 1,059 |
| 22:00 | 819,072 | 3,132 | 1,731 |
| 23:00 | 796,677 | 3,128 | 1,929 |
20:00 时,巡航高度的飞机数不到午后早些时候的十分之一。做个对比:前一夜最安静的时段(03:00)还有 818 架。21:00 之后,高空航班开始恢复。
数据从哪来:一张 3800 亿行的航迹表
adsb.exposed 把 adsb.lol、airplanes.live 和 ADSB Exchange 的每一条位置报告,都存在一张 ClickHouse 表 planes_mercator 里。这张表有 3800 亿行,每行有时间、坐标、高度、速度和飞机。坐标还以 Web 墨卡托投影(Web Mercator projection)预存为两个 UInt32 列:
mercator_x UInt32 MATERIALIZED 4294967295 * ((lon + 180) / 360),
mercator_y UInt32 MATERIALIZED 4294967295 * ((1 / 2) - ((log(tan(((lat + 90) / 360) * pi())) / 2) / pi())),
整个世界是一个 2³² × 2³² 的正方形。表按 mortonEncode(mercator_x, mercator_y) 排序,也就是 Z 序曲线(Z-order curve)。地图瓦片,或者地图上的任何矩形,都是 mercator_x 和 mercator_y 的一个取值范围,主键让这种范围读取很便宜。坐标变像素只需一次位移:mercator_x >> 19 把世界切成 2¹³ = 8192 个像素,对应网站上的缩放级别 5。这个比例下,西经 128° 到 64°、北纬 22° 到 51° 的矩形是 1456 × 840 像素。
先聚合:每帧、每像素算一次
对每一帧、每一个像素,服务器统计位置点数,并算高度和速度的平均值(pixels.sql):
WITH
toUInt32(4294967295 * ((-128 + 180) / 360)) AS x0,
toUInt32(4294967295 * ((-64 + 180) / 360)) AS x1,
toUInt32(4294967295 * (1 / 2 - log(tan((51 + 90) / 360 * pi())) / 2 / pi())) AS y0,
toUInt32(4294967295 * (1 / 2 - log(tan((22 + 90) / 360 * pi())) / 2 / pi())) AS y1,
toDateTime('2026-11-13 05:00:00', 'UTC') AS t0,
t0 + {h:UInt32} * 3600 AS hour_begin
SELECT
toUInt16(intDiv(toUnixTimestamp(time) - toUnixTimestamp(t0), 300)) AS frame,
toUInt16(bitShiftRight(mercator_x - x0, 19)) AS px,
toUInt16(bitShiftRight(mercator_y - y0, 19)) AS py,
toUInt32(count()) AS c,
toFloat32(avg(altitude)) AS alt,
toFloat32(avg(ground_speed)) AS speed
FROM planes_mercator
WHERE time >= hour_begin AND time < hour_begin + 3600
AND mercator_x >= x0 AND mercator_x < x1
AND mercator_y >= y0 AND mercator_y < y1
GROUP BY frame, px, py;
一天按美东时间算:美东午夜是 UTC 05:00。这条查询读了 4250 万个位置点,返回 900 万行——288 帧里每个至少出现过一架飞机的像素各一行。平均每帧约 31000 个亮像素,总共 120 万个像素里只占一小部分。
adsb.exposed 的公开端点限制结果约 100 万行。所以查询把小时作为参数 {h:UInt32},跑 24 次。clickhouse-local 一条语句就能办到:url 表函数会把 URL 里的 {0..23} 展开成 24 个 URL,这里把通配写进了查询参数本身:
URL="https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com/?user=website&default_format=RowBinary¶m_h={0..23}&query=$(python3 -c 'import sys, urllib.parse; print(urllib.parse.quote(sys.stdin.read(), safe=""))' < pixels.sql)"
clickhouse local --query "
SELECT * FROM url('$URL', RowBinary, 'frame UInt16, px UInt16, py UInt16, c UInt32, alt Float32, speed Float32')
INTO OUTFILE 'pixels.native' TRUNCATE FORMAT Native
SETTINGS max_threads = 6"
注意 default_format=RowBinary。url 函数告诉客户端用什么格式解析,但不告诉服务器用什么格式发送。website 用户默认是 TSV,把 TSV 当 RowBinary 解析会报一个让人摸不着头脑的 "Cannot read all data" 错误。
结果是一个 160 MB 的预聚合像素文件,往后的每一步都在本地跑。
FORMAT PNG:查询结果直接就是一张图
ClickHouse 有一种输出格式,能把查询结果渲染成图片。颜色模式由列名决定:r、g、b 是 RGB;r、g、b、a 是 RGBA;v 是灰度。如果有整数 x 和 y 列,每行就落在画布对应位置上;否则按扫描线顺序依次填像素。如果还有一列 t,结果就变成动态 PNG,每个 t 值一帧。一帧持续到下一个 t 值出现,output_format_image_time_multiplier_seconds 和 output_format_image_time_divisor_seconds 这两个设置决定 t 的单位。这里是 2/25 秒,所以每 5 分钟一帧播 80 毫秒,整天 23 秒播完。
两行落在同一个像素上时,后到的胜出(画家算法,painter's algorithm)。所以画面按图层搭建,用 ORDER BY layer 排序:
- 残影(ghost)。全天流量按像素汇总后取对数尺度,每帧都以深灰色画一遍。天空清空后,国家的轮廓还看得见。这 405000 个像素用 ARRAY JOIN range(frames) 复制进 288 帧。
- 飞机。当前帧的飞机,按高度和速度着色。
- 时间轴。每帧的位置点数画成地图下方的柱状图,当前帧高亮,过去的比未来的颜色浅。
- 文字。每个字符串先拆成字符,每个字符拆成字形的 15 个比特,每个亮起的比特再变成 scale × scale 的像素方块。四个 ARRAY JOIN 一个接一个,产生笛卡尔积。时钟就是 America/New_York 和 UTC 两个时区的 formatDateTime。
完整查询(render.sql):
-- Renders the animation from the per-frame, per-pixel aggregates in `pixels.native`.
-- Every row of the result is one pixel of one frame: (t, x, y, r, g, b).
-- Rows are painted in the order they arrive, so the layers are sorted: background first, text last.
WITH
1456 AS width, -- map width in pixels
840 AS map_height, -- the timeline goes below the map
288 AS frames, -- one frame per 5 minutes
pixels AS
(
SELECT * FROM file('pixels.native')
WHERE px < width AND py < map_height AND frame < frames
),
-- The faint trace of the whole day, repeated in every frame, so that the outline of the US
-- is still there when the sky is empty.
ghost AS
(
SELECT px, py, toUInt8(pow(log1p(total) / log1p(max(total) OVER ()), 1.5) * 55) AS v
FROM (SELECT px, py, sum(c) AS total FROM pixels GROUP BY px, py)
QUALIFY v >= 4
),
-- The number of positions in every frame, for the timeline.
activity AS
(
SELECT frame, sum(c) / max(sum(c)) OVER () AS share
FROM pixels
GROUP BY frame
),
-- A 3x5 bitmap font: five rows of three bits each.
map(
'0', '111101101101111', '1', '010110010010111', '2', '111001111100111', '3', '111001111001111',
'4', '101101111001001', '5', '111100111001111', '6', '111100111101111', '7', '111001001001001',
'8', '111101111101111', '9', '111101111001111', ':', '000010000010000', '.', '000000000000010',
'-', '000000111000000', ' ', '000000000000000',
'A', '010101111101101', 'B', '110101110101110', 'C', '111100100100111', 'D', '110101101101110',
'E', '111100110100111', 'F', '111100110100100', 'G', '111100101101111', 'I', '111010010010111',
'N', '110101101101101', 'O', '010101101101010', 'P', '111101111100100', 'R', '110101110101101',
'S', '111100111001111', 'T', '111010010010010', 'U', '101101101101111', 'V', '101101101101010',
'X', '101101010101101', 'Y', '101101010010010') AS font,
-- All the text, one row per string: the frame, the position of the top-left corner, the scale, the color.
texts AS
(
SELECT number AS frame, 20 AS x0, 16 AS y0, 4 AS scale,
'FAA OUTAGE - FRIDAY NOV 13 2026' AS text, 255 AS r, 255 AS g, 255 AS b
FROM numbers(frames)
UNION ALL
SELECT number, 20, 44, 4,
formatDateTime(toDateTime('2026-11-13 05:00:00', 'UTC') + number * 300, '%H:%i ET', 'America/New_York')
|| ' ' || formatDateTime(toDateTime('2026-11-13 05:00:00', 'UTC') + number * 300, '%H:%i UTC', 'UTC'),
255, 210, 110
FROM numbers(frames)
UNION ALL
SELECT number, width - 20 - 12 * 8, map_height - 22, 2, 'ADSB.EXPOSED', 150, 150, 150
FROM numbers(frames)
UNION ALL
-- The hour labels under the timeline: 00:00, 03:00, ..., 24:00.
SELECT frame, toInt32(30 + (width - 60) * hour / 24 - 2.5 * 8), map_height + 72, 2,
leftPad(toString(hour), 2, '0') || ':00', 150, 150, 150
FROM (SELECT number * 3 AS hour FROM numbers(9)) ARRAY JOIN range(frames) AS frame
),
text_pixels AS
(
SELECT frame,
x0 + ((char_num - 1) * 4 + bit % 3) * scale + dx AS x, -- glyphs are 3 pixels wide, plus 1 of spacing
y0 + intDiv(bit, 3) * scale + dy AS y,
r, g, b
FROM texts
ARRAY JOIN arrayEnumerate(splitByString('', text)) AS char_num
ARRAY JOIN range(15) AS bit
ARRAY JOIN range(scale) AS dx
ARRAY JOIN range(scale) AS dy
WHERE substring(font[substring(text, char_num, 1)], bit + 1, 1) = '1'
),
-- The timeline: a bar for every frame, highlighted for the current one.
timeline AS
(
SELECT
current AS frame,
toInt32(floor(30 + (width - 60) * bar / frames)) AS xa,
toInt32(floor(30 + (width - 60) * (bar + 1) / frames)) - 1 AS xb,
toInt32(48 * share) AS h,
multiIf(bar = current, (255, 200, 80), bar < current, (110, 110, 110), (55, 55, 55)) AS color
FROM (SELECT frame AS bar, share FROM activity) ARRAY JOIN range(frames) AS current
)
SELECT t, toInt32(x) AS x, toInt32(y) AS y, toUInt8(round(r)) AS r, toUInt8(round(g)) AS g, toUInt8(round(b)) AS b
FROM
(
-- Layer 0: the ghost of the whole day.
SELECT 0 AS layer, frame AS t, px AS x, py AS y, v AS r, v AS g, v AS b
FROM ghost ARRAY JOIN range(frames) AS frame
UNION ALL
-- Layer 1: the aircraft, colored with the same formula as on adsb.exposed: red fades with the altitude,
-- green grows over the first 5000 feet (so the climbs and descents near airports are yellow), blue is the speed.
SELECT 1, frame, px, py,
(1 + transparency) / 2 * (1 - least(alt, 50000) / 50000) * 255,
transparency * greatest(0, least(alt, 5000)) / 5000 * 255,
greatest(0, least(speed, 700)) / 700 * 255
FROM (SELECT *, pow(least(c / 30, 1), 1 / 5) AS transparency FROM pixels)
UNION ALL
-- Layer 2: the timeline.
SELECT 2, frame, xa + dx, map_height + 66 - dy, color.1, color.2, color.3
FROM timeline
ARRAY JOIN range(xb - xa + 1) AS dx
ARRAY JOIN range(h + 1) AS dy
UNION ALL
-- Layer 3: the text.
SELECT 3, frame, x, y, r, g, b
FROM text_pixels
)
ORDER BY layer
INTO OUTFILE 'faa-outage-2026-11-13.png' TRUNCATE
FORMAT PNG
SETTINGS
output_format_image_width = 1456,
output_format_image_height = 930,
-- One unit of `t` is 2/25 of a second: 80 ms per frame, the whole day in 23 seconds.
output_format_image_time_multiplier_seconds = 2,
output_format_image_time_divisor_seconds = 25;
这条查询产出约 1.3 亿个像素行:残影 1.17 亿,飞机 900 万,剩下的是时间轴和文字。单机 31 秒写出全部 288 帧。
几个注意点
- 像素值会被钳制,浮点数会被缩放。整数通道钳制到 [0, 255],Float 通道钳制到 [0, 1] 再乘以 255。这里的颜色计算得出的是 [0, 255] 区间内的浮点数,所以外层 SELECT 用 toUInt8(round(...)) 转换。否则每个亮像素都会变成纯白色。
- 各帧相互独立。每帧都从黑色画布开始,这就是为什么残影必须在每帧里重复画,不能只画一次。作为回报,t 可以按任意顺序到达。一帧里的行只需在帧内按图层排好序。
- 内存与流式。默认情况下,这种格式会把所有帧都留在内存里,直到查询结束:288 × 1456 × 930 × 3 字节,这里约 1.2 GB。设置 output_format_image_streaming_animation = 1 后,每当 t 推进就写出一帧,内存里只留一帧。这要求 ORDER BY t, layer。另外此时文件头里的帧数只是个上限:浏览器能播这种文件,但有些解码器会抱怨。
FORMAT PNG 写的是真彩色帧,288 帧一共 150 MB。画面大半是黑色,所以从几帧采样建一个 256 色调色板,损失很小。转调色板后动态 PNG 只有 25 MB。GIF 和 MP4 由同一文件经 ffmpeg 转出:
PALETTE="split[a][b];[a]palettegen=max_colors=256:stats_mode=full[p];[b][p]paletteuse=dither=none"
ffmpeg -i faa-outage-2026-11-13.png -vf "$PALETTE" -loop 0 faa-outage-2026-11-13.gif
ffmpeg -i faa-outage-2026-11-13.png -c:v libx264 -preset slow -crf 18 -pix_fmt yuv420p \
-profile:v high -level 4.1 -movflags +faststart faa-outage-2026-11-13.mp4
MP4 用 H.264 High profile、yuv420p,能在 QuickTime、Windows 播放器、VLC、浏览器和聊天软件里播放。它是三者中最大的,因为成千上万条一像素宽的细拖影很难被视频压缩压下去。
不用写代码也能出对比图
adsb.exposed 的公开 website 用户是只读的,所以改不了图片尺寸。不过默认画布是 1024 × 1024,做静态图够了。下面这个查询把两个面板上下叠放,保持地图的宽高比,直接在服务器上跑,PNG 从 HTTP 响应里返回(before-after.sql):
WITH
toUInt32(4294967295 * ((-128 + 180) / 360)) AS x0,
toUInt32(4294967295 * ((-64 + 180) / 360)) AS x1,
toUInt32(4294967295 * (1 / 2 - log(tan((51 + 90) / 360 * pi())) / 2 / pi())) AS y0,
toUInt32(4294967295 * (1 / 2 - log(tan((22 + 90) / 360 * pi())) / 2 / pi())) AS y1,
-- Two panels of 1024 x 512, one above the other; keep the aspect ratio of the map.
512 / (y1 - y0) AS scale,
(1024 - (x1 - x0) * scale) / 2 AS left_margin,
time >= '2026-11-13 21:00:00' AS after, -- 14:00 ET goes to the top panel, 20:00 ET to the bottom one
toInt32(left_margin + (mercator_x - x0) * scale) AS x,
toInt32((mercator_y - y0) * scale) + after * 512 AS y,
pow(least(count() / 20, 1), 1 / 5) AS transparency
SELECT x, y,
toUInt8((1 + transparency) / 2 * (1 - least(avg(altitude), 50000) / 50000) * 255) AS r,
toUInt8(transparency * greatest(0, least(avg(altitude), 5000)) / 5000 * 255) AS g,
toUInt8(greatest(0, least(avg(ground_speed), 700)) / 700 * 255) AS b
FROM planes_mercator
WHERE ((time >= '2026-11-13 19:00:00' AND time < '2026-11-13 19:10:00')
OR (time >= '2026-11-14 01:00:00' AND time < '2026-11-14 01:10:00'))
AND mercator_x >= x0 AND mercator_x < x1
AND mercator_y >= y0 AND mercator_y < y1
GROUP BY x, y
FORMAT PNG;
curl 'https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com/?user=website' --data-binary @before-after.sql > before-after.png
10 秒跑完,ClickHouse 之外一行代码都不用跑。
全部材料都在原文链接的目录里:make.sh 跑这三步。需要 clickhouse(任何带 FORMAT PNG 的较新版本;这里用的是 26.10)、ffmpeg,以及带 Pillow 的 Python(最后一步转调色板用)。
数据来源:© adsb.lol(ODbL v1.0)、© airplanes.live、© adsbexchange.com。
译者注
- SQL 也能当画笔,但这招有适用边界:FORMAT PNG 适合把聚合结果直接变成图——监控大盘、数据故事、一次性可视化都很顺手。但交互式探索、复杂排版还是交给专业绘图工具;像素级拼图层写起来并不比 Python 脚本省事,胜在“数据不出库”。
- 预聚合是关键一跳:4250 万个位置点先聚合成 900 万行像素,再本地渲染——这个分层值得学。直接拿原始明细行去画图,数据量和内存都会失控;先在库里把“每像素每帧”算好,下游只处理结果,量级立刻下来。
- 坐标预处理决定查询能不能跑得动:墨卡托坐标预存为 UInt32、再按 Morton 编码(Z 序曲线)排序,矩形范围查询才能走主键快速过滤。如果坐标是现算的、表是按时间排序的,同样一个地图框选查询要慢得多——空间数据的表设计,先想好最常用的查询形状。
- 落到华为云场景要换一套坐标:FORMAT PNG 是 ClickHouse 的特有能力,GaussDB(DWS)、DLI 这类云上数仓没有等价的输出格式,别直接照搬语法。但“库内聚合、结果出图”的思路可以借鉴:把聚合结果导出后接可视化流水线,日报、监控大盘同样能做到查询即出图。
本文译自 ClickHouse 官方博客《The day the sky emptied: rendering the FAA outage with ClickHouse FORMAT PNG》,作者 Alexey Milovidov,发布于 2026 年 11 月 18 日。原文链接:https://clickhouse.com/blog/faa-outage-clickhouse-format-png
相关阅读:
- Spark 4.3 发布:有状态实时流 P99 延迟低于 150 毫秒,小数据迭代也更快:https://bbs.huaweicloud.cn/blogs/c33b90df190c49e89522cf073b09bab9
- SQL 共现统计慢在哪?Tessera 用稀疏乘法加 AMX 做连接聚合,比 DuckDB 快最高 1802 倍:https://bbs.huaweicloud.cn/blogs/d76945b1150e48c184016490b1be3d33
- 点赞
- 收藏
- 关注作者
评论(0)