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

在这篇文章中，我们将继续我们关于 在 Clickhouse 中可视化数据 系列，探讨 Superset 作为业务用户的流行工具。

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

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

Apache Superset 是一个 Apache 开源可视化工具，于 2017 年作为 AirBnB 的一项倡议而推出。专注于基于 SQL 的数据存储，用户需要能够舒适地编写查询，但在类似 IDE 的编辑器的帮助下。Superset 保留了仪表板的概念，为数据分析师和更专业的 BI 用户提供了丰富的 BI 体验和各种可视化类型，包括树形图和箱线图。

Superset 的主要优势之一是其可扩展性，允许开发人员创建自定义可视化和插件，以满足特定的组织需求。此外，Superset 支持各种数据源，包括传统的关系数据库、NoSQL 数据库和云原生数据仓库，使其成为具有多样化数据生态系统的组织的通用选择。该平台还强调安全性和治理，提供诸如基于角色的访问控制和数据源管理等功能，以确保数据完整性并符合组织策略。

docker-compose 代表了 Superset 新用户的 最简单的入门体验。

首先克隆 Superset 仓库

1git clone --depth=1  https://github.com/apache/superset.git
2cd superset

在运行任何 docker-compose 命令之前，请确保官方 ClickHouse 驱动程序将被安装，方法是将 “clickhouse-connect” 添加到 requirements 文件中，如下所示。我们还添加了一个 map box 密钥以启用地理可视化。

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

接下来，让我们启动 Superset

1export TAG=3.1.1
2docker compose -f docker-compose-image-tag.yml up

最后，我们将创建我们的 Superset 管理员并初始化 Superset 的数据库

1SUPERSET_ID=$(docker ps -aqf "name=superset_app")

1docker exec -it ${SUPERSET_ID} superset fab create-admin \
2  --username admin --firstname Superset --lastname Admin \
3  --email [email protected] --password admin
4docker exec -it ${SUPERSET_ID}  superset db upgrade
5docker exec -it ${SUPERSET_ID}  superset init

您可以通过 https://:8088 访问 Superset，并使用凭据 admin/admin 登录。

登录后，我们将创建一个数据库连接。下面我们连接到 sql.clickhouse.com 以使用用户资源管理器（无密码）来利用我们的数据集。请注意 HTTP 端口的使用。

使用以下凭据

• 主机 - sql-clickhouse.clickhouse.com
• 端口 - 443
• 用户 - demo

Superset 要求您创建虚拟 “数据集”。这些数据集可以提供与特定表的连接，也可以捕获查询结果。下面我们创建一个与 sql.clickhouse.com 中的 hackernews 表的连接，使其可用于图表创建。

请注意，ClickHouse 的 Schema 是一个数据库。

配置数据集后，我们可以基于它创建图表。

下面显示了重新创建我们 之前的帖子 中的简单折线图可视化，显示了 ClickHouse Hacker News 帖子随时间的变化

使用我们 之前的帖子 中的外汇数据集，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 年中 median 房价百分比变化最大的地区。这里的查询使用了一些很好的聚合函数，连接了我们的代码和价格，但在我们的公共演示环境中仍然在不到 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 的前 2 位数字代表 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 个 2-term 短语（shingles）。

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 个数据点上添加点差的移动平均值。

对您的图表感到满意后，您可以保存它，并可以选择将其添加到现有仪表板或创建新仪表板，如下面的屏幕截图所示。

然后，您可以直接转到仪表板，或通过顶部导航栏上的 Dashboards 链接找到它。下面是我们使用几个图表创建的 Hacker News 仪表板的示例

要发布仪表板以便其他人可以查看它，请单击“草稿”按钮，该按钮将变为“已发布”

在这篇文章中，我们探讨了使用 Superset 的一些独特可视化功能及其对技术和业务用户都非常有用的能力。在下一篇文章中，我们将演示 Metabase 如何针对更简单的体验进行快速数据调查。

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