FAA 停飞日美国天空被清空:ClickHouse 一条 SQL 把 4250 万条航迹画成动画

举报
Eveline 发表于 2026/11/19 13:26:48 2026/11/19
【摘要】 2026 年 11 月 13 日 FAA 全国停飞,美国天空在 5 小时内被清空。ClickHouse 创始人用一条 SQL 把当天 4250 万条 ADS-B 航迹聚合成 288 帧动画:不用绘图库、不用图片脚本,地图、时钟、时间轴全是查询结果里的像素行。

原文信息:本文译自《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&param_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 排序:

  1. 残影(ghost)。全天流量按像素汇总后取对数尺度,每帧都以深灰色画一遍。天空清空后,国家的轮廓还看得见。这 405000 个像素用 ARRAY JOIN range(frames) 复制进 288 帧。
  2. 飞机。当前帧的飞机,按高度和速度着色。
  3. 时间轴。每帧的位置点数画成地图下方的柱状图,当前帧高亮,过去的比未来的颜色浅。
  4. 文字。每个字符串先拆成字符,每个字符拆成字形的 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
【版权声明】本文为华为云社区用户翻译文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容, 举报邮箱:cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。