跳到主要内容
跳到主要内容

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

目标​

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

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

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

MCC 204 中按无线电类型划分的基站仪表板

获取数据集​

该数据集来自 OpenCelliD - 世界上最大的开放基站数据库。

截至 2021 年，它包含关于全球超过 4000 万个基站（GSM、LTE、UMTS 等）的记录，并附有地理坐标和元数据（国家代码、网络等）。

OpenCelliD 项目根据创作共用署名-相同方式共享 4.0 国际许可协议进行许可，我们根据同样的许可条款重新分发该数据集的快照。最新版本的数据集可以在登录后下载。

加载示例数据​

ClickHouse Cloud 提供了一键上传此数据集到 S3 的功能。登录您的 ClickHouse Cloud 组织，或在 ClickHouse.cloud 创建一个免费试用账户。

选择您的服务，然后点击 数据源 -> 预定义示例数据。

Cloud service Actions menu

从 Sample data 选项卡中选择 Cell Towers 数据集，然后 Load data：

加载基站数据集

检查 cell_towers 表的模式​

SQL 控制台

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

连接到 SQL 控制台

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

连接到 SQL 控制台

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

SQL 控制台

这是 DESCRIBE 的输出。在本指南后面的部分将对字段类型的选择进行描述。

运行一些示例查询​

  1. 按类型分类的基站数量：
  1. 按 移动国家代码（MCC） 分类的基站：

根据上述查询和 MCC 列表，拥有最多基站的国家是：美国、德国和俄罗斯。

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

用例：整合地理数据​

使用 pointInPolygon 函数。

  1. 创建一个将存储多边形的表：
  1. 这是莫斯科的粗略形状（不包括“新莫斯科”）：
  1. 检查莫斯科有多少基站：

模式回顾​

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

列描述
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，请直接在下面的 "Launch Apache Superset in Docker" 中打开。

在 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：

ClickHouse Cloud 服务连接按钮

选择 HTTPS，详细信息会在示例 curl 命令中提供。

ClickHouse Cloud HTTPS 连接详细信息

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

在 Superset 中，可以通过选择数据库类型，然后提供连接详细信息来添加数据库。打开 Superset，寻找 +，它带有 Data 菜单，然后选择 Connect database 选项。

添加数据库

从列表中选择 ClickHouse Connect：

选择 ClickHouse Connect 作为数据库类型

备注

如果 ClickHouse Connect 不是您的选项之一，您需要安装它。命令是 pip install clickhouse-connect，更多信息 请参见此处。

添加您的连接详情:​

提示

确保在连接到 ClickHouse Cloud 或其他强制使用 SSL 的 ClickHouse 系统时，设置 SSL 为开启。

将 ClickHouse 添加为 Superset 数据源

将 cell_towers 表添加为 Superset 数据集​

在 Superset 中，数据集 映射到数据库中的一张表。点击添加一个数据集，选择您的 ClickHouse 服务，包含您的表的数据库（default），然后选择 cell_towers 表：

将 cell_towers 表添加为数据集

创建一些 图表​

当您选择在 Superset 中添加图表时，必须指定数据集（cell_towers）和图表类型。由于 OpenCelliD 数据集提供了基站的经度和纬度坐标，因此我们将创建一个 地图 图表。deck.gl 散点图 类型适合此数据集，因为它适用于地图上密集的数据点。

在 Superset 中创建地图

指定用于地图的查询​

deck.gl 散点图需要经度和纬度，同时也可以对查询施加一个或多个过滤器。在这个例子中应用了两个过滤器，一个是针对带有 UMTS 无线电的基站，另一个是分配给荷兰的移动国家代码。

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

指定经度和纬度字段

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

针对 MCC 204 进行过滤

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

过滤器为 radio = UMTS

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

MCC 204 中 UMTS 无线电的图表

点击 UPDATE CHART 来渲染可视化。

将图表添加到 仪表板​

此屏幕截图显示了带有 LTE、UMTS 和 GSM 无线电的基站位置。所有图表都是以相同的方式创建，并添加到仪表板中。

MCC 204 中按无线电类型划分的基站仪表板

提示

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

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

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