使用基站数据集的地理数据
目标
在本指南中,您将学习如何:
- 在 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 客户端连接,您的 ClickHouse Cloud 服务具有一个相关的基于网络的 SQL 控制台;请展开下面的 连接到 SQL 控制台 以获取详细信息。
连接到 SQL 控制台
从您的 ClickHouse Cloud 服务列表中,点击一个服务。

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

这是 DESCRIBE
的输出。稍后在本指南中将描述字段类型的选择。
- 创建一个表:
- 从公共 S3 存储桶导入数据集(686 MB):
运行一些示例查询
- 按类型分类的信号塔数量:
- 按 手机国家代码 (MCC) 分类的信号塔:
根据上述查询和 MCC 列表,信号塔数量最多的国家是:美国、德国和俄罗斯。
您可能希望在 ClickHouse 中创建一个 字典 来解码这些值。
用例:结合地理数据
使用 pointInPolygon
函数。
- 创建一个表来存储多边形:
- ClickHouse Cloud
- 自管理
- 这是莫斯科(不包括“新莫斯科”)的大致形状:
- 检查莫斯科内有多少信号塔:
模式评审
在 Superset 中构建可视化之前,查看您将要使用的列。该数据集主要提供全球移动信号塔的位置信息(经度和纬度)以及无线电类型。列的描述可以在 社区论坛 中找到。本次构建的可视化所使用的列描述如下:
以下是从 OpenCelliD 论坛获取的列描述:
列 | 描述 |
---|---|
radio | 技术代:CDMA、GSM、UMTS、5G NR |
mcc | 手机国家代码:204 是荷兰 |
lon | 经度:与纬度一起,近似信号塔位置 |
lat | 纬度:与经度一起,近似信号塔位置 |
要查找您的 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
之前,有几个任务需要完成:
- 添加官方 ClickHouse Connect 驱动程序
- 获取一个 Mapbox API 密钥并将其添加为环境变量(可选)
- 指定要运行的 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(调整主机名和端口号)。