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

此博文是系列文章的一部分

• 使用 ClickHouse 可视化数据 - 第 1 部分 - Grafana
• 使用 ClickHouse 可视化数据 - 第 2 部分 - Superset

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

Metabase 为那些不太习惯编写 SQL 的人提供了一个更简单的体验。只需拖放字段即可构建查询——此功能很可能吸引业务用户。由于专注于快速数据探索，Metabase 的可视化和可定制程度不及我们之前演示过的其他工具，但它拥有更简洁、更简单的界面和工作流程。

下载 Metabase jar 可以获得最简单的入门体验。这要求您的系统上安装了 Java 运行时环境 (JRE)。确保您下载了 ClickHouse 驱动程序（jar 文件），将其放置在“/plugins”子目录中。然后启动 Metabase 就很简单了

java -jar metabase.jar

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

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

Metabase 提供了一个探索功能，可用于快速了解特定数据集。概述统计信息，如字段基数和随时间变化的值。下面我们探索了来自 之前文章 的 forex 数据集，以概述主要基数和报价列。

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

下面我们演示如何使用此 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 月期间，这大致描述了飓风的条件和时间。最后，我们根据风速绘制每年的前两大事件，并过滤掉海拔高度较高的事件（> 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 烛台 类似——这是 forex 数据集的流行技术。下面我们重用 我们之前 forex 文章 中的查询，以使用 窗口函数 在每日收盘价上绘制 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
    )
)
 ✎

对于那些好奇最佳位置的人来说，图钉地图允许我们快速解释坐标。这要求我们将最严格的条件移动到 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 生态系统中都是一等公民，我们希望继续改进这些工具。

如果您热衷于最新技术并对开源充满热情，我们目前正在招聘我们的集成团队，很乐意收到您的来信。