博客 / 工程

使用 ClickHouse 可视化数据 - 第 3 部分 - Metabase

Dale McDiarmid

2022 年 10 月 19 日 - 9 分钟阅读

这篇博文是系列文章的一部分

在这篇文章中，我们将继续我们关于在 Clickhouse_[1]_[2] 中可视化数据的系列文章，探讨 Metabase 作为一种面向不太懂技术的业务用户的流行工具。

对于那些不太习惯编写 SQL 的人来说，Metabase 提供了一种更简单的体验。可以通过简单地拖放字段来构建查询 - 这一功能很可能吸引业务用户。专注于快速数据探索，其可视化和可定制性的广度不如我们之前演示过的工具，但优点是界面和工作流程更简洁明了。

下载 Metabase jar 包提供了最简单的入门体验。这需要您在系统上安装 Java 运行时环境 (JRE)。确保您下载 ClickHouse 驱动程序（jar 文件），并将其放在“/plugins”子目录中。然后启动 Metabase 非常简单

java -jar metabase.jar

该应用程序可能需要几分钟才能启动。控制台应将您重定向到 https://127.0.0.1:3000，一旦 Metabase 准备就绪。

在设置期间选择 ClickHouse 作为您的数据库，并为数据源分配一个名称。请注意 HTTP 端口的使用。

Metabase 提供了一个探索功能，可用于快速了解特定数据集的概况。概况统计信息包括字段基数和值随时间的变化。下面我们探索来自早期文章的外汇数据集，以获得主要基础列和报价列的概况。

Metabase 用户创建的分析可以基于传统的 SQL 查询或“问题”。问题是通过引导式查询构建器构建的，用户可以选择数据库、表、过滤器和汇总字段。汇总也可以分为组 - 有效地使用 GROUP BY。这种直观的方法允许用户快速构建简单的可视化。

下面我们使用问题功能来查看来自我们的外汇数据集的 EUR 货币对 USD、AUD、CAD、CHF、GPB 和 NZD 的平均价格。请注意，我们假设买入价代表货币对的平均价格。

这种“问题”方法特别适合大多数简单的可视化要求。对于更复杂的可视化，Metabase 提供了 SQL 编辑器。用户只需确保以适合可视化的结构返回结果 - 类似于 Grafana 的方法。由于没有客户端转换逻辑，所有操作都推迟到查询本身。凭借广泛的分析功能，这非常适合 ClickHouse，并且通常即使对于最大的数据集也受到鼓励。

下面我们演示如何使用此 SQL 编辑器来显示世界上最冷的国家，根据我们的 NOAA 数据集，叠加在区域地图上。


SELECT
    code,
    min(tempMin) / 10 AS min_temp
FROM blogs.noaa
WHERE date > '1970-01-01'
GROUP BY substring(station_id, 1, 2) AS code
LIMIT 1000
 ✎

除了区域地图，Metabase 还包括一个网格地图。虽然目前非常简单，但它可用于绘制点，其中颜色强度由指标决定。我们使用此功能来绘制美国的天气事件，其中阵风速度大于 100 公里/小时或约 30 米/秒（我们的阵风速度单位为十分之一米/秒），并且在一周内涉及大量降雨，即平均每天降雨量超过 2 厘米。我们将此限制在 7 月到 10 月期间，这大致描述了飓风的条件和一年中的时间。最后，我们根据风速绘制每年最高的 2 个事件，并滤除高海拔（>500 米）的事件 - 因为恶劣天气往往是山峰上常见的现象！请注意，我们使用了函数 geohashEncode 来捕获广泛区域内的重大事件，而不是潜在的单个异常站点，以及使用 dictGet 函数将我们的分析限制在美国。

正如预期的那样，我们捕获了影响美国东海岸的飓风 - 包括 2005 年的卡特里娜飓风。


SELECT
    week,
    toYear(week) AS year,
    lat, lon,
    avg_precipitation,
    max_wind_speed * 10
FROM
(
    SELECT
        geoHash,
        week,
        geohashDecode(geoHash) AS lonlat,
        lonlat.1 AS lon,
        lonlat.2 AS lat,
        max(maxWindSpeed) AS max_wind_speed,
        avg(precipitation)/10 AS avg_precipitation
    FROM blogs.noaa
    WHERE (dictGet(blogs.country_polygons, 'name', location) IN ('United States of America')) AND (elevation < 500) AND toMonth(date) BETWEEN 6 AND 10
    GROUP BY
        geohashEncode(location.1, location.2, 4) AS geoHash,
        toStartOfWeek(date) AS week
    HAVING max_wind_speed > 300  AND avg_precipitation > 20
    ORDER BY
        max_wind_speed DESC
)
ORDER BY
    year ASC,
    max_wind_speed DESC
LIMIT 2 BY year
 ✎

瀑布可视化传达的信息与我们早期文章中显示的 Grafana 蜡烛图类似 - 这是外汇数据集的常用技术。下面我们重用我们早期的外汇文章中的查询来绘制 GBP/EUR 货币对的变化 - 使用每日收盘价上的窗口函数。如图所示，Metabase 还保留了仪表板的概念，用于整理多个可视化。


SELECT
    base,
    quote,
    day,
    close,
    close - any(close) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change
FROM
(
    SELECT
        base,
        quote,
        day,
        argMax(ask, datetime) AS close
    FROM blogs.forex
    WHERE (quote = 'GBP') AND (base = 'EUR')
    GROUP BY
        base,
        quote,
        toStartOfDay(datetime) AS day
    ORDER BY
        base ASC,
        quote ASC,
        day ASC
)
ORDER BY day ASC
 ✎

与 Grafana 和 Superset 类似，Metabase 支持组合图表，以允许绘制多个轴。例如，我们使用贯穿我们文档的英国房价数据集。我们绘制了自 1995 年以来每月的平均房价和销售额，区分了租赁权和永久产权交易。


SELECT
    month,
    countIf(duration = 'leasehold') AS `Leasehold Sold`,
    countIf(duration = 'freehold') AS `Freehold Sold`,
    avgIf(price, duration = 'freehold') AS `Average Freehold Price`,
    avgIf(price, duration = 'leasehold') AS `Average Leasehold Price`
FROM uk_price_paid
GROUP BY toStartOfMonth(date) AS month
ORDER BY month ASC
 ✎

回到我们的 NOAA 数据集，我们好奇哪里可能是基于气候条件的完美度假目的地。我们初步的标准 - 良好的温度范围、少量降雨、非过高海拔以及没有极端天气事件，可能有点牵强，但这使我们能够演示漏斗可视化以及 arrayJoin 和 If 组合器函数的用法，用于将列名转换为值。我们计算了地理哈希区域和一年中月份的条件，因此不仅仅考虑单个站点记录。


SELECT
    values.1 AS labels,
    values.2 AS count
FROM
(
    SELECT arrayJoin([('not_too_cold', countIf(min_temp > 0)), ('not_too_cold_or_cold', countIf((min_temp > 0) AND (max_temp < 40))), ('ideal_temp', countIf((max_temp < 40) AND (min_temp > 0) AND (avg_temp > 10))), ('ideal_temp_min_rain', countIf((max_temp < 40) AND (min_temp > 0) AND (avg_temp > 10) AND (sum_precipitation < 100))), ('ideal_temp_min_rain_not_high', countIf((max_temp < 40) AND (min_temp > 0) AND (avg_temp > 10) AND (sum_precipitation < 100) AND (avg_elevation < 1000)))]) AS values
    FROM
    (
        SELECT
            geoHash,
            month,
            avg(percentDailySun) AS avg_daily_sun,
            geohashDecode(geoHash) AS lonlat,
            lonlat.1 AS lat,
            lonlat.2 AS lon,
            avg(tempAvg) / 10 AS avg_temp,
            max(tempMax) / 10 AS max_temp,
            min(tempMin) / 10 AS min_temp,
            sum(precipitation) AS sum_precipitation,
            avg(elevation) AS avg_elevation
        FROM blogs.noaa
        WHERE date > '1970-01-01'
        GROUP BY
            geohashEncode(location.1, location.2, 4) AS geoHash,
            toMonth(date) AS month
    )
)
 ✎

对于那些对最佳位置感到好奇的人，Pin 地图允许我们快速解释坐标。这要求我们将最严格的条件移至 HAVING 子句。


SELECT
    geoHash,
    month,
    avg(percentDailySun) AS avg_daily_sun,
    geohashDecode(geoHash) AS lonlat,
    lonlat.1 AS lon,
    lonlat.2 AS lat,
    avg(tempAvg) / 10 AS avg_temp,
    max(tempMax) / 10 AS max_temp,
    min(tempMin) / 10 AS min_temp,
    sum(precipitation) AS sum_precipitation,
    avg(elevation) AS avg_elevation
FROM blogs.noaa
WHERE date > '1970-01-01'
GROUP BY
    geohashEncode(location.1, location.2, 4) AS geoHash,
    toMonth(date) AS month
HAVING (max_temp < 40) AND (min_temp > 0) AND (avg_temp > 10) AND (sum_precipitation < 100) AND (avg_elevation < 1000)
 ✎

我们将留给读者探索这些地点，并报告它们是否确实是理想的度假目的地。

在这篇文章中，我们演示了 Metabase 如何通过提供一种简单而有效的方式，通过问题功能为 BI 构建可视化，从而为不熟悉 SQL 的用户提供出色的入门体验。这与本博客系列中的其他工具形成对比，这些工具满足其他需求。Grafana 在时间序列数据及其在地理分析等用例中不断发展的灵活性方面具有明显的优势，这要求用户熟悉 SQL。与此同时，Superset 针对数据分析师和 BI 用例进行了优化，它具有出色的 SQL 编辑器以及将任何查询保存为可重用的“数据集”的能力，从而形成了一种高效的可视化构建方式。您的选择通常取决于您的数据和个人偏好，但它们都是 ClickHouse 生态系统中的一流成员，我们希望继续改进。

如果您对最新技术充满热情，并且对开源充满激情，我们目前正在为我们的集成团队招聘，并期待收到您的来信。

分享这篇文章