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

NYPD 投诉数据

制表符分隔值 (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 被描述为“如果适用，发生地点的纽约市公园、游乐场或绿地的名称（不包括州立公园）”。纽约市的公园名称可能非常适合使用 LowCardinality(String)：

结果：

查看一些公园名称：

结果：

在撰写本文时，使用的数据集中 PARK_NM 列中只有几百个独特的公园和游乐场。根据 LowCardinality 的建议，这个数量应该很小，保持在 10,000 个独特字符串以下，以优化 LowCardinality(String) 字段。

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.tables）中包含有关您刚创建的表的所有信息。以下查询显示了 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，以使数据流更清晰。 clickhouse-local 的最后一个参数是一个查询，该查询从表中选择（FROM input），然后将其传递给 clickhouse-client 以填充表 NYPD_Complaint。

验证数据​

备注

数据集每年会更改一次或多次，您的计数可能与本文中的计数不匹配。

查询：

结果：

ClickHouse 中的数据集大小仅为原始 TSV 文件的 12%，通过下面的查询比较原始 TSV 文件大小与表的大小：

查询：

结果：

执行一些查询​

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

查询：

结果：

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

查询：

结果：

下一步​

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