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

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

目标

在本指南中,您将学习如何:

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

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

获取数据集

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

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

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

加载示例数据

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. 按类型分类的信号塔数量:
  1. 手机国家代码 (MCC) 分类的信号塔:

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

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

用例:结合地理数据

使用 pointInPolygon 函数。

  1. 创建一个表来存储多边形:
  1. 这是莫斯科(不包括“新莫斯科”)的大致形状:
  1. 检查莫斯科内有多少信号塔:

模式评审

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

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

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

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

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

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

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

使用 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 无线电的信号塔,另一个用于分配给荷兰的手机国家代码。

字段 lonlat 包含经度和纬度:

添加过滤器 mcc = 204 (或替换为其他 mcc 值):

添加过滤器 radio = 'UMTS' (或替换为其他 radio 值,您可以在 DESCRIBE TABLE cell_towers 的输出中查看选项):

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

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

将图表添加到 仪表板

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

提示

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

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

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