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

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

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

在这篇文章中，我们将继续探讨Clickhouse 中数据可视化系列，探索 Superset 作为商业用户常用工具。

Apache Superset 是一个 Apache 开源可视化工具，于 2017 年在 AirBnB 的一项倡议下推出。它专注于基于 SQL 的数据存储，用户需要能够编写查询，但可以通过类似 IDE 的编辑器获得帮助。保留了仪表板的概念，数据分析师和更偏技术性的 BI 用户可以使用丰富的 BI 体验和各种可视化类型，包括树状图和箱线图。

对于 Superset 的新用户，docker-compose 代表了最简单的入门体验。在运行任何 docker-compose 命令之前，请确保通过将“clickhouse-connect”添加到 requirements 文件中来安装官方 ClickHouse 驱动程序，如下所示。我们还添加了一个 map box 密钥以启用地理可视化。

git clone https://github.com/apache/superset.git
cd superset

echo "clickhouse-connect" >> ./docker/requirements-local.txt
echo"MAPBOX_API_KEY=<INSERT>" >> docker/.env-non-dev

docker-compose -f docker-compose-non-dev.yml pull
docker-compose -f docker-compose-non-dev.yml up

启动后，通过https://127.0.0.1:8088访问 Superset，并使用凭据 admin/admin 登录。

添加数据库连接非常简单。下面我们将连接到play.clickhouse.com，使用用户浏览器（无密码）利用我们的数据集。请注意 HTTP 端口的使用。读者应该能够重现所有示例。

Superset 要求用户创建虚拟“数据集”。它们可以提供到特定表的连接，或捕获查询的结果。下面我们将创建一个到 play.clickhouse.com 中的 hackernews 表的连接，使其可用于图表创建。请注意，ClickHouse 的 Schema 只是一个数据库。

重新创建我们上一篇文章中简单的折线图可视化，显示 ClickHouse Hacker News 帖子随时间的变化非常简单——只需要有限的 SQL 知识即可添加 OR 条件。

使用我们上一篇文章中的外汇数据集，Superset 能够使用树状图可视化最常交易的货币对。在这里，我们将数据限制在 2020 年代，并假设读者已使用前面的示例在 Superset 中创建了数据集。请注意，我们使用 1/avg(ask-bid) 作为货币对市场活动的估计。这计算了价差的倒数（请参阅我们之前的文章中的定义），其中较低的值表示更大的流动性和交易量。

我们可以对 GBP/EUR 进行类似的分析，但将其可视化为日历，以查看 2016 年期间价格发生最大变化的那些日期——毫不奇怪，是英国脱欧公投的那天。这要求我们使用 SQL IDE，其中查询的结果可以保存为数据集。此强大功能使我们能够捕获更复杂的逻辑。此特定查询来自我们之前的文章，并且需要窗口函数来计算每日变化。

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') AND (datetime > '2016-01-01 00:00:00.000') AND (datetime < '2017-01-01 00:00:00.000')
    GROUP BY
        base,
        quote,
        toStartOfDay(datetime) AS day
    ORDER BY
        base ASC,
        quote ASC,
        day ASC
)
ORDER BY
    base ASC,
    quote ASC,
    day ASC
 ✎

虽然我们没有在我们的博客系列中探讨英国房价数据集，但它通常用于我们文档中的示例。通过使用英国（ISO 3166-2）的区域代码列表，我们可以识别出过去 20 年中房屋中位数价格涨幅最大的地区。此处的查询使用了一些不错的聚合函数，将我们的代码和价格连接起来，但在我们的公共游乐环境中仍然可以在不到 0.2 秒的时间内运行。英国在过去 25 年中经历了区域代码的变化，因此一些房屋在当前代码中没有有效值——但是，这影响不到百分之一，对我们的分析没有意义。

SELECT
   code,
   (anyIf(med_2020, med_2020 > 0) - anyIf(med_2000, med_2000 > 0)) / anyIf(med_2000, med_2000 > 0) AS percent_change
FROM
(
   SELECT
       code,
       medianIf(price, year = 2000) AS med_2000,
       medianIf(price, year = 2020) AS med_2020
   FROM
   (
       SELECT
           date,
           price,
           locality,
           town,
           district,
           county,
           code
       FROM uk_price_paid
       LEFT JOIN blogs.uk_codes AS codes ON (uk_price_paid.county = codes.name) OR (uk_price_paid.district = codes.name) OR (uk_price_paid.town = codes.name) OR (uk_price_paid.locality = codes.name) OR (replaceAll(uk_price_paid.district, 'CITY OF ', '') = codes.name)
   )
   WHERE (code != '') AND ((toYear(date) = 2000) OR (toYear(date) = 2020))
   GROUP BY
       code,
       toYear(date) AS year
   ORDER BY code ASC
)
GROUP BY code
ORDER BY percent_change DESC
 ✎

然后，我们可以使用国家地图将其可视化。

Superset 提供了箱线图可视化，可以有效地显示多个组中指标的分布。这使我们能够显示降水量如何根据国家/地区变化，使用我们在上一篇文章中探讨的天气数据集。为此，我们需要一个fips 10-4 代码到国家/地区名称的简单映射。回想一下，天气数据集的站点 ID 的前两位数字表示 fips 国家/地区代码。我们的映射是，然后将其加载到 ClickHouse Complex Key Hashed 字典中，使用我们的表作为源，以便在以下查询中通过dictGet 函数使用。

CREATE TABLE blogs.country_codes (
   `name` LowCardinality(String),
   `code` LowCardinality(String)

) ENGINE = MergeTree ORDER BY code

INSERT INTO blogs.country_codes SELECT Name as name, `FIPS 10-4` as code FROM url('https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv', 'CSVWithNames')

CREATE DICTIONARY blogs.country_iso_codes
(
   `code` String,
   `name` String
)
PRIMARY KEY code
SOURCE(CLICKHOUSE(TABLE 'country_codes' DATABASE 'blogs'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(COMPLEX_KEY_HASHED())
 

下面我们将展示从计算按国家/地区每年平均降水量的查询中创建的数据集。我们将国家/地区限制在其FIPS 代码在欧盟内的国家/地区。这反过来可以用来创建我们的箱线图。

SELECT year,
       avg(`precipitation`) AS `avg_precipitation`,
       dictGet(`blogs`.`country_iso_codes`, 'name', code) as country
FROM `blogs`.`noaa_v2`
WHERE date > '1970-01-01' AND code IN ('AL', 'AN', 'AU', 'BE', 'BO', 'CY', 'DA', 'EI', 'EZ', 'EN', 'FI', 'FR', 'GG', 'GI', 'GK', 'GM',
'GR', 'HR', 'HU', 'IC', 'IM', 'IT', 'JE', 'LG', 'LH', 'LO', 'LS', 'LU', 'MD', 'MK', 'MN', 'MT', 'NL', 'NO', 'PL', 'PO', 'RO', 'SI', 'SM',
'SP', 'SW', 'SZ', 'TU', 'UK', 'UP', 'VT')
GROUP BY toStartOfYear(`date`) AS `year`,
         substring(station_id, 1, 2) as code
HAVING avg_precipitation > 0         
ORDER BY country, year ASC
LIMIT 100000
 ✎

Superset 除了提供常用的数值分析图表外，还可以通过词云进行一些简单的文本分析。专注于我们上一篇文章中的 Hacker News 数据集，我们可以使用标记化和数组函数来识别前 N 个双词短语（分片）。

WITH stop_words AS
   (
       SELECT token
       FROM blogs.stop_words
   )
SELECT
   phrase,
   count() AS c
FROM
(
   SELECT
       arrayJoin(shingles) AS shingle,
       concat(shingle.1, ' ', shingle.2) AS phrase
   FROM
   (
       SELECT
           tokens,
           arrayFilter(t -> (NOT ((t.2) IS NULL)), arrayZip(tokens, arrayPushBack(arrayPopFront(tokens), NULL))) AS shingles
       FROM
       (
           SELECT arrayFilter(t -> ((t NOT IN (stop_words)) AND (length(t) > 2)), alphaTokens(title)) AS tokens
           FROM default.hackernews
           WHERE (type IN ('story', 'comment'))
       )
       WHERE length(tokens) > 0
   )
)
GROUP BY phrase
ORDER BY c DESC
LIMIT 20
 ✎

请注意，我们如何排除包含停用词的短语。这显然是一种识别热门短语的粗略方法，基于统计的技术可能会产生更有趣的结果！

最后，与 Grafana 一样，Superset 也提供了组合图表类型的功能。下面我们探索一个更经典的可视化方法，混合使用折线图和柱状图来显示 GBP/USD 和 USD/EUR 货币对相对于其价差的值。请注意，我们如何使用多个轴并添加过去 10 个数据点的价差移动平均线。

在这篇文章中，我们探讨了如何利用 Superset 独特的数据可视化功能，以及它如何为技术用户和业务用户提供极大的帮助。在下一篇文章中，我们将演示 Metabase 如何为快速数据调查提供更简单的体验。

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