使用基站数据集的地理数据

目标

在本指南中，您将学习如何：

• 在 ClickHouse 中加载 OpenCelliD 数据
• 将 Apache Superset 连接到 ClickHouse
• 基于数据集中的数据构建仪表板

以下是本指南中创建的仪表板的预览：

获取数据集

该数据集来自 OpenCelliD - 世界上最大的信号塔开放数据库。

截至 2021 年，它包含了全球超过 4000 万条关于信号塔（GSM、LTE、UMTS 等）的记录，以及它们的地理坐标和元数据（国家代码、网络等）。

OpenCelliD 项目采用创意共享署名-相同方式共享 4.0 国际许可证， chúng tôi 在同一许可证条款下重新分发该数据集的快照。注册后可以下载最新版本的数据集。

ClickHouse Cloud

加载示例数据

ClickHouse Cloud 提供了一个简单的按钮来从 S3 上传此数据集。登录到您的 ClickHouse Cloud 组织，或者在 ClickHouse.cloud 创建一个免费试用账户。

选择你的服务，然后点击 Data souces -> Predefined sample data。

从 示例数据 选项卡中选择 信号塔 数据集，并 加载数据：

检查 cell_towers 表的模式

SQL 控制台

如果您需要 SQL 客户端连接，您的 ClickHouse Cloud 服务具有一个相关的基于网络的 SQL 控制台；请展开下面的 连接到 SQL 控制台 以获取详细信息。

连接到 SQL 控制台

从您的 ClickHouse Cloud 服务列表中，点击一个服务。

这将重定向您到 SQL 控制台。

这是 DESCRIBE 的输出。稍后在本指南中将描述字段类型的选择。

自管理

1. 创建一个表：

2. 从公共 S3 存储桶导入数据集（686 MB）：

运行一些示例查询

1. 按类型分类的信号塔数量：

2. 按 手机国家代码 (MCC) 分类的信号塔：

根据上述查询和 MCC 列表，信号塔数量最多的国家是：美国、德国和俄罗斯。

您可能希望在 ClickHouse 中创建一个 字典 来解码这些值。

用例：结合地理数据

使用 pointInPolygon 函数。

1. 创建一个表来存储多边形：

ClickHouse Cloud

自管理

2. 这是莫斯科（不包括“新莫斯科”）的大致形状：

3. 检查莫斯科内有多少信号塔：

模式评审

在 Superset 中构建可视化之前，查看您将要使用的列。该数据集主要提供全球移动信号塔的位置信息（经度和纬度）以及无线电类型。列的描述可以在 社区论坛 中找到。本次构建的可视化所使用的列描述如下：

以下是从 OpenCelliD 论坛获取的列描述：

列	描述
radio	技术代：CDMA、GSM、UMTS、5G NR
mcc	手机国家代码：204 是荷兰
lon	经度：与纬度一起，近似信号塔位置
lat	纬度：与经度一起，近似信号塔位置

mcc

要查找您的 MCC，请查看 移动网络代码，并在 手机国家代码 列中使用三位数字。

该表的模式被设计为在磁盘上紧凑存储并提高查询速度。

• radio 数据以 Enum8 (UInt8) 格式存储，而不是字符串。
• mcc 或手机国家代码以 UInt16 格式存储，因为我们知道其范围是 1 - 999。
• lon 和 lat 为 Float64。

本指南中没有其他字段在查询或可视化中使用，但如果您对此感兴趣，可以在上面链接的论坛中找到它们的描述。

使用 Apache Superset 构建可视化

Superset 可以轻松地通过 Docker 运行。如果您已经在运行 Superset，只需使用 pip install clickhouse-connect 将其连接到 ClickHouse。如果您需要安装 Superset，请直接打开 在 Docker 中启动 Apache Superset。

在 Docker 中启动 Apache Superset

Superset 提供了 使用 Docker Compose 本地安装 Superset 的说明。在从 GitHub 上检出 Apache Superset 仓库后，您可以运行最新的开发代码或特定标签。我们推荐使用 2.0.0 版本，因为它是最新的未标记为 pre-release 的版本。

在运行 docker compose 之前，有几个任务需要完成：

1. 添加官方 ClickHouse Connect 驱动程序
2. 获取一个 Mapbox API 密钥并将其添加为环境变量（可选）
3. 指定要运行的 Superset 版本

提示

以下命令需从 GitHub 仓库的顶层目录 superset 中运行。

官方 ClickHouse Connect 驱动程序

要使 ClickHouse Connect 驱动程序在 Superset 部署中可用，请将其添加到本地需求文件中：

Mapbox

这是可选的，您可以在 Superset 中绘制位置数据，而无需 Mapbox API 密钥，但您会看到一条消息，提示您应添加密钥，并且地图的背景图像将缺失（您只会看到数据点，而没有地图背景）。如果您想使用它，Mapbox 提供免费层。

一些示例可视化指南要求您创建使用位置（例如经度和纬度）数据的可视化。Superset 包含对 Mapbox 地图的支持。要使用 Mapbox 可视化，您需要一个 Mapbox API 密钥。注册 Mapbox 免费层，并生成一个 API 密钥。

将 API 密钥提供给 Superset：

部署 Superset 版本 2.0.0

要部署 2.0.0 版本，请运行：

要使用 OpenCelliD 数据集构建 Superset 仪表板，您应：

• 将您的 ClickHouse 服务添加为 Superset 数据库
• 将表 cell_towers 添加为 Superset 数据集
• 创建一些 图表
• 将图表添加到 仪表板

将您的 ClickHouse 服务添加为 Superset 数据库

要通过 HTTP(S) 连接到 ClickHouse，您需要以下信息：

• HOST 和 PORT：通常情况下，使用 TLS 时端口为 8443，不使用 TLS 时端口为 8123。

• 数据库名称：默认情况下，有一个名为 default 的数据库，请使用您要连接的数据库名称。

• 用户名和密码：默认情况下，用户名为 default。请使用适合您用例的用户名。

您的 ClickHouse Cloud 服务的详细信息可以在 ClickHouse Cloud 控制台中找到。选择您要连接的服务并点击 Connect：

选择 HTTPS，并且详细信息可以在一个示例 curl 命令中找到。

如果您使用自管理的 ClickHouse，连接详细信息由您的 ClickHouse 管理员设置。

在 Superset 中，可以通过选择数据库类型并提供连接详细信息来添加数据库。打开 Superset，寻找 +，它有一个包含 数据 和 连接数据库 选项的菜单。

从列表中选择 ClickHouse Connect：

备注

如果 ClickHouse Connect 不是您的选项之一，那么您需要安装它。命令为 pip install clickhouse-connect，更多信息 可在此处查找。

添加您的连接详细信息：

提示

连接到 ClickHouse Cloud 或其他强制使用 SSL 的 ClickHouse 系统时，请确保启用 SSL。

将表 cell_towers 添加为 Superset 数据集

在 Superset 中，数据集 映射到数据库中的一个表。单击添加数据集并选择您的 ClickHouse 服务，包含您表的数据库 (default)，以及选择 cell_towers 表：

创建一些 图表

在 Superset 中选择添加图表时，您必须指定数据集 (cell_towers) 和图表类型。由于 OpenCelliD 数据集提供了信号塔的经纬度坐标，因此我们将创建一个 地图 图表。deck.gl 散点图 类型适合此数据集，因为它可以很好地处理地图上的密集数据点。

指定地图使用的查询

deck.gl 散点图需要经度和纬度，并且可以对查询应用一个或多个过滤器。在此示例中，应用了两个过滤器：一个用于具有 UMTS 无线电的信号塔，另一个用于分配给荷兰的手机国家代码。

字段 lon 和 lat 包含经度和纬度：

添加过滤器 mcc = 204 （或替换为其他 mcc 值）：

添加过滤器 radio = 'UMTS' （或替换为其他 radio 值，您可以在 DESCRIBE TABLE cell_towers 的输出中查看选项）：

这是针对 radio = 'UMTS' 和 mcc = 204 的图表的完整配置：

单击 更新图表 以呈现可视化效果。

将图表添加到 仪表板

此截图显示了具有 LTE、UMTS 和 GSM 无线电的信号塔位置。所有图表都是以相同方式创建的，并且它们被添加到一个仪表板中。

提示

数据也可在 Playground 中进行交互式查询。

这个 示例 将为您自动填充用户名甚至查询。

虽然您不能在 Playground 中创建表，但您可以运行所有查询，甚至使用 Superset（调整主机名和端口号）。