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

NYPD Complaint Data

标签分隔值（TSV）文件是常见的文件格式，可能在文件的第一行包含字段标题。ClickHouse 可以摄取 TSV，并且可以在不摄取文件的情况下查询 TSV。本指南涵盖了这两种情况。如果您需要查询或摄取 CSV 文件，使用相同的技术，只需在格式参数中将 TSV 替换为 CSV。

在本指南中，您将：

  • 调查：查询 TSV 文件的结构和内容。
  • 确定目标 ClickHouse 架构：选择适当的数据类型，并将现有数据映射到这些类型。
  • 创建一个 ClickHouse 表。
  • 预处理并流式传输 数据到 ClickHouse。
  • 对 ClickHouse 运行一些查询。

本指南使用的数据集来自纽约市开放数据团队，包含有关"报告给纽约市警察局（NYPD）的所有有效重罪、轻罪和违规行为的犯罪数据"的信息。在撰写时，数据文件大小为 166MB，但此文件定期更新。

来源：data.cityofnewyork.us 使用条款：https://www1.nyc.gov/home/terms-of-use.page

前提条件​

关于本指南中描述的命令的说明​

本指南中有两种类型的命令：

  • 部分命令查询 TSV 文件，这些命令在命令提示符下运行。
  • 其余命令查询 ClickHouse，这些命令在 clickhouse-client 或 Play UI 中运行。
备注

本指南中的示例假设您已将 TSV 文件保存到 ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv，请根据需要调整命令。

熟悉 TSV 文件​

在开始使用 ClickHouse 数据库之前，请熟悉数据。

查看源 TSV 文件中的字段​

这是一个查询 TSV 文件的命令示例，但暂时不要运行它。

示例响应

提示

通常情况下，上述命令会告诉您输入数据中的哪些字段是数字，哪些是字符串，哪些是元组。但并不总是如此。因为 ClickHouse 通常用于包含数十亿条记录的数据集，所以为了避免解析数十亿行数据以推断架构，默认情况下只检查 100 行以 推断架构。下面的响应可能与您看到的内容不匹配，因为数据集每年更新几次。查看数据字典，您可以看到 CMPLNT_NUM 被指定为文本，而不是数字。通过使用设置 SETTINGS input_format_max_rows_to_read_for_schema_inference=2000 来覆盖默认的 100 行推断，您可以更好地了解内容。

注意：从版本 22.5 开始，推断架构的默认行数为 25,000 行，因此仅在您使用较旧版本或需要超过 25,000 行进行采样时才更改该设置。

在命令提示符下运行此命令。您将使用 clickhouse-local 查询您下载的 TSV 文件中的数据。

结果：

此时，您应检查 TSV 文件中的列是否与 数据集网页 中“此数据集的列”部分中指定的名称和类型相匹配。数据类型不够具体，所有数字字段都设置为 Nullable(Float64)，而其他所有字段都为 Nullable(String)。当您创建一个 ClickHouse 表以存储数据时，可以指定更合适和高效的类型。

确定适当的架构​

为了确定字段应使用哪些类型，需要了解数据的外观。例如，字段 JURISDICTION_CODE 是数字：它应该是 UInt8，还是 Enum，或者 Float64 合适？

结果：

查询响应显示 JURISDICTION_CODE 很适合 UInt8。

同样，查看一些 String 字段，看看它们是否适合 DateTime 或 LowCardinality(String) 字段。

例如，字段 PARKS_NM 被描述为“发生地点 NYC 公园、游乐场或绿地的名称（如适用，州立公园不包括在内）”。纽约市的公园名称可以很好地作为 LowCardinality(String) 的候选：

结果：

查看一些公园的名称：

结果：

在撰写时使用的数据集在 PARK_NM 列中只有数百个不同的公园和游乐场。根据 LowCardinality 的建议，保持在 LowCardinality(String) 字段中不超过 10,000 个不同字符串，这个数量是比较小的。

DateTime 字段​

根据 数据集网页 中的"此数据集的列"部分，有报告事件开始和结束的日期和时间字段。查看 CMPLNT_FR_DT 和 CMPLT_TO_DT 的最小值和最大值，可以了解这些字段是否始终填充：

结果：

结果：

结果：

结果：

制定计划​

基于上述调查：

  • JURISDICTION_CODE 应该转换为 UInt8。
  • PARKS_NM 应该转换为 LowCardinality(String)
  • CMPLNT_FR_DT 和 CMPLNT_FR_TM 始终填充（可能默认时间为 00:00:00）
  • CMPLNT_TO_DT 和 CMPLNT_TO_TM 可能为空
  • 日期和时间在源数据中存储在单独字段中
  • 日期格式为 mm/dd/yyyy
  • 时间格式为 hh:mm:ss
  • 可将日期和时间串联成 DateTime 类型
  • 一些日期在 1970 年 1 月 1 日之前，这意味着我们需要 64 位 DateTime
备注

还有许多更改需要对类型进行，所有这些都可以通过遵循相同的调查步骤来确定。查看字段中的不同字符串数量、数字的最小值和最大值，并做出决策。后面指南中给出的表架构包含许多低基数字符串和无符号整数字段，以及很少的浮点数字类型。

串联日期和时间字段​

要将日期和时间字段 CMPLNT_FR_DT 和 CMPLNT_FR_TM 串联成一个可以转换为 DateTime 的单一 String，选择两个字段并用串联运算符连接：CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM。CMPLNT_TO_DT 和 CMPLNT_TO_TM 字段类似处理。

结果：

将日期和时间字符串转换为 DateTime64 类型​

在本指南早期，我们发现 TSV 文件中的日期在 1970 年 1 月 1 日之前，这意味着我们需要 64 位 DateTime 类型的日期。此外，日期还需要从 MM/DD/YYYY 转换为 YYYY/MM/DD 格式。上述两项都可以通过 parseDateTime64BestEffort() 完成。

上面第 2 行和第 3 行包含了上一步的串联，而第 4 行和第 5 行则将字符串解析为 DateTime64。当投诉结束时间未必存在时，使用 parseDateTime64BestEffortOrNull。

结果：

备注

上面显示为 1925 的日期来自数据中的错误。在原始数据中有几个记录，其日期在 1019 年至 1022 年之间，应该被记录为 2019 年至 2022 年。它们被存储为 1925 年 1 月 1 日，因为这是 64 位 DateTime 可存储的最早日期。

创建表​

上述对列数据类型的决策在下面的表架构中反映。我们还需要决定表用于 ORDER BY 和 PRIMARY KEY 的列。必须至少指定 ORDER BY 或 PRIMARY KEY 之一。以下是决定包含哪些列的 ORDER BY 的一些指导，并在本文档末尾的 下一步 部分中提供更多信息。

Order By 和 Primary Key 子句​

  • ORDER BY 元组应包含在查询过滤器中使用的字段
  • 为了最大化磁盘压缩，ORDER BY 元组应按升序基数排序
  • 如果存在，PRIMARY KEY 元组必须是 ORDER BY 元组的一个子集
  • 如果只指定 ORDER BY，则将使用相同的元组作为 PRIMARY KEY
  • 如果指定了 PRIMARY KEY 元组，则将使用 PRIMARY KEY 元组创建主键索引，否则使用 ORDER BY 元组
  • PRIMARY KEY 索引保存在主内存中

查看数据集和通过查询可能回答的问题，我们可能决定查看随着时间推移在纽约市五个区报告的犯罪类型。这些字段可能包括在 ORDER BY 中：

列描述（来自数据字典）
OFNS_DESC对应关键代码的罪犯描述
RPT_DT向警方报告事件的日期
BORO_NM事件发生区的名称

查询 TSV 文件以获取这三个候选列的基数：

结果：

按照基数排序，ORDER BY 变为：

备注

下表将使用更易于阅读的列名，上面的名称将映射到

将数据类型变化与 ORDER BY 元组结合起来，得到以下表结构：

找到表的主键​

ClickHouse system 数据库，尤其是 system.table 中包含您刚刚创建的表的所有信息。此查询显示 ORDER BY（排序键）和 PRIMARY KEY：

响应

预处理和导入数据​

我们将使用 clickhouse-local 工具进行数据预处理，并使用 clickhouse-client 进行上传。

使用的 clickhouse-local 参数​

提示

table='input' 在下面的 clickhouse-local 参数中出现。 clickhouse-local 获取提供的输入（cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv）并将输入插入表中。默认情况下，表名为 table。在本指南中，表名设置为 input 以使数据流更清晰。最后一参数是选择来自表的查询（FROM input），然后将其传送给 clickhouse-client 来填充表 NYPD_Complaint。

验证数据​

备注

数据集每年变化一次或多次，您的数量可能与本文件中的内容不匹配。

查询：

结果：

ClickHouse 中数据集的大小仅为原始 TSV 文件的 12%，将原始 TSV 文件的大小与表的大小进行比较：

查询：

结果：

运行一些查询​

查询 1. 按月比较投诉数量​

查询：

结果：

查询 2. 按区比较投诉总数​

查询：

结果：

下一步​

ClickHouse 中稀疏主索引的实用介绍 讨论了 ClickHouse 索引与传统关系数据库的不同之处，ClickHouse 如何构建和使用稀疏主索引，以及索引的最佳实践。