纽约市警察局投诉数据

制表符分隔值或 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

先决条件

通过访问 NYPD 投诉数据当前（当年至今）页面，点击“导出”按钮，并选择“用于 Excel 的 TSV”来下载数据集。
安装 ClickHouse 服务器和客户端。
启动 ClickHouse 服务器，并使用 clickhouse-client 连接

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

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

一些命令用于查询 TSV 文件，这些命令在命令提示符下运行。
其余命令用于查询 ClickHouse，这些命令在 clickhouse-client 或 Play UI 中运行。

注意

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

熟悉 TSV 文件

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

查看源 TSV 文件中的字段

这是一个查询 TSV 文件的命令示例，但请勿立即运行它。

clickhouse-local --query \
"describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')"

示例响应

CMPLNT_NUM                  Nullable(Float64)
ADDR_PCT_CD                 Nullable(Float64)
BORO_NM                     Nullable(String)
CMPLNT_FR_DT                Nullable(String)
CMPLNT_FR_TM                Nullable(String)

提示

大多数情况下，上述命令会让您知道输入数据中的哪些字段是数字，哪些是字符串，哪些是元组。但并非总是如此。由于 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 文件中的数据。

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')"

结果

CMPLNT_NUM        Nullable(String)
ADDR_PCT_CD       Nullable(Float64)
BORO_NM           Nullable(String)
CMPLNT_FR_DT      Nullable(String)
CMPLNT_FR_TM      Nullable(String)
CMPLNT_TO_DT      Nullable(String)
CMPLNT_TO_TM      Nullable(String)
CRM_ATPT_CPTD_CD  Nullable(String)
HADEVELOPT        Nullable(String)
HOUSING_PSA       Nullable(Float64)
JURISDICTION_CODE Nullable(Float64)
JURIS_DESC        Nullable(String)
KY_CD             Nullable(Float64)
LAW_CAT_CD        Nullable(String)
LOC_OF_OCCUR_DESC Nullable(String)
OFNS_DESC         Nullable(String)
PARKS_NM          Nullable(String)
PATROL_BORO       Nullable(String)
PD_CD             Nullable(Float64)
PD_DESC           Nullable(String)
PREM_TYP_DESC     Nullable(String)
RPT_DT            Nullable(String)
STATION_NAME      Nullable(String)
SUSP_AGE_GROUP    Nullable(String)
SUSP_RACE         Nullable(String)
SUSP_SEX          Nullable(String)
TRANSIT_DISTRICT  Nullable(Float64)
VIC_AGE_GROUP     Nullable(String)
VIC_RACE          Nullable(String)
VIC_SEX           Nullable(String)
X_COORD_CD        Nullable(Float64)
Y_COORD_CD        Nullable(Float64)
Latitude          Nullable(Float64)
Longitude         Nullable(Float64)
Lat_Lon           Tuple(Nullable(Float64), Nullable(Float64))
New Georeferenced Column Nullable(String)

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

确定合适的架构

为了弄清楚应该为字段使用什么类型，有必要了解数据的样子。例如，字段 JURISDICTION_CODE 是一个数字：它应该是 UInt8 还是 Enum，或者 Float64 是否合适？

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select JURISDICTION_CODE, count() FROM
 file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
 GROUP BY JURISDICTION_CODE
 ORDER BY JURISDICTION_CODE
 FORMAT PrettyCompact"

结果

┌─JURISDICTION_CODE─┬─count()─┐
│                 0 │  188875 │
│                 1 │    4799 │
│                 2 │   13833 │
│                 3 │     656 │
│                 4 │      51 │
│                 6 │       5 │
│                 7 │       2 │
│                 9 │      13 │
│                11 │      14 │
│                12 │       5 │
│                13 │       2 │
│                14 │      70 │
│                15 │      20 │
│                72 │     159 │
│                87 │       9 │
│                88 │      75 │
│                97 │     405 │
└───────────────────┴─────────┘

查询响应显示 JURISDICTION_CODE 非常适合 UInt8。

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

例如，字段 PARKS_NM 被描述为“如果适用，则为纽约市公园、游乐场或绿地的名称（不包括州立公园）”。纽约市公园的名称可能是 LowCardinality(String) 的一个很好的候选者。

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select count(distinct PARKS_NM) FROM
 file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
 FORMAT PrettyCompact"

结果

┌─uniqExact(PARKS_NM)─┐
│                 319 │
└─────────────────────┘

查看一些公园名称

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select distinct PARKS_NM FROM
 file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
 LIMIT 10
 FORMAT PrettyCompact"

结果

┌─PARKS_NM───────────────────┐
│ (null)                     │
│ ASSER LEVY PARK            │
│ JAMES J WALKER PARK        │
│ BELT PARKWAY/SHORE PARKWAY │
│ PROSPECT PARK              │
│ MONTEFIORE SQUARE          │
│ SUTTON PLACE PARK          │
│ JOYCE KILMER PARK          │
│ ALLEY ATHLETIC PLAYGROUND  │
│ ASTORIA PARK               │
└────────────────────────────┘

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

DateTime 字段

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

CMPLNT_FR_DT
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_FR_DT), max(CMPLNT_FR_DT) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"

结果

┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐
│ 01/01/1973        │ 12/31/2021        │
└───────────────────┴───────────────────┘

CMPLNT_TO_DT
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_TO_DT), max(CMPLNT_TO_DT) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"

结果

┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐
│                   │ 12/31/2021        │
└───────────────────┴───────────────────┘

CMPLNT_FR_TM
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_FR_TM), max(CMPLNT_FR_TM) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"

结果

┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐
│ 00:00:00          │ 23:59:00          │
└───────────────────┴───────────────────┘

CMPLNT_TO_TM
clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select min(CMPLNT_TO_TM), max(CMPLNT_TO_TM) FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
FORMAT PrettyCompact"

结果

┌─min(CMPLNT_TO_TM)─┬─max(CMPLNT_TO_TM)─┐
│ (null)            │ 23:59:00          │
└───────────────────┴───────────────────┘

制定计划

根据上述调查

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 字段的处理方式类似。

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM AS complaint_begin FROM
file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
LIMIT 10
FORMAT PrettyCompact"

结果

┌─complaint_begin─────┐
│ 07/29/2010 00:01:00 │
│ 12/01/2011 12:00:00 │
│ 04/01/2017 15:00:00 │
│ 03/26/2018 17:20:00 │
│ 01/01/2019 00:00:00 │
│ 06/14/2019 00:00:00 │
│ 11/29/2021 20:00:00 │
│ 12/04/2021 00:35:00 │
│ 12/05/2021 12:50:00 │
│ 12/07/2021 20:30:00 │
└─────────────────────┘

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

在本指南的前面，我们发现TSV文件中存在早于1970年1月1日的日期，这意味着我们需要使用64位DateTime类型来存储这些日期。此外，还需要将日期格式从MM/DD/YYYY转换为YYYY/MM/DD格式。这两种操作都可以使用parseDateTime64BestEffort()函数来完成。

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START,
      (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END
select parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin,
       parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end
FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
ORDER BY complaint_begin ASC
LIMIT 25
FORMAT PrettyCompact"

上面第2行和第3行包含了来自上一步的连接操作，第4行和第5行将字符串解析为DateTime64类型。由于投诉结束时间并非始终存在，因此使用了parseDateTime64BestEffortOrNull函数。

结果

┌─────────complaint_begin─┬───────────complaint_end─┐
│ 1925-01-01 10:00:00.000 │ 2021-02-12 09:30:00.000 │
│ 1925-01-01 11:37:00.000 │ 2022-01-16 11:49:00.000 │
│ 1925-01-01 15:00:00.000 │ 2021-12-31 00:00:00.000 │
│ 1925-01-01 15:00:00.000 │ 2022-02-02 22:00:00.000 │
│ 1925-01-01 19:00:00.000 │ 2022-04-14 05:00:00.000 │
│ 1955-09-01 19:55:00.000 │ 2022-08-01 00:45:00.000 │
│ 1972-03-17 11:40:00.000 │ 2022-03-17 11:43:00.000 │
│ 1972-05-23 22:00:00.000 │ 2022-05-24 09:00:00.000 │
│ 1972-05-30 23:37:00.000 │ 2022-05-30 23:50:00.000 │
│ 1972-07-04 02:17:00.000 │                    ᴺᵁᴸᴸ │
│ 1973-01-01 00:00:00.000 │                    ᴺᵁᴸᴸ │
│ 1975-01-01 00:00:00.000 │                    ᴺᵁᴸᴸ │
│ 1976-11-05 00:01:00.000 │ 1988-10-05 23:59:00.000 │
│ 1977-01-01 00:00:00.000 │ 1977-01-01 23:59:00.000 │
│ 1977-12-20 00:01:00.000 │                    ᴺᵁᴸᴸ │
│ 1981-01-01 00:01:00.000 │                    ᴺᵁᴸᴸ │
│ 1981-08-14 00:00:00.000 │ 1987-08-13 23:59:00.000 │
│ 1983-01-07 00:00:00.000 │ 1990-01-06 00:00:00.000 │
│ 1984-01-01 00:01:00.000 │ 1984-12-31 23:59:00.000 │
│ 1985-01-01 12:00:00.000 │ 1987-12-31 15:00:00.000 │
│ 1985-01-11 09:00:00.000 │ 1985-12-31 12:00:00.000 │
│ 1986-03-16 00:05:00.000 │ 2022-03-16 00:45:00.000 │
│ 1987-01-07 00:00:00.000 │ 1987-01-09 00:00:00.000 │
│ 1988-04-03 18:30:00.000 │ 2022-08-03 09:45:00.000 │
│ 1988-07-29 12:00:00.000 │ 1990-07-27 22:00:00.000 │
└─────────────────────────┴─────────────────────────┘

注意

上面显示为1925的日期是数据错误导致的。原始数据中存在一些记录，其日期为1019年至1022年，应该为2019年至2022年。由于64位DateTime类型的最早日期是1925年1月1日，因此这些日期被存储为1925年1月1日。

创建表

上面关于列数据类型的决策反映在下表的模式中。我们还需要确定表使用的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元组，则使用该元组创建主键索引，否则使用ORDER BY元组。
主键索引保存在主内存中。

查看数据集以及可能通过查询数据集来回答的问题，我们可能会决定查看纽约市五个区在一段时间内报告的犯罪类型。然后，这些字段可能会包含在ORDER BY中。

列	描述（来自数据字典）
OFNS_DESC	与关键代码相对应的违法行为描述
RPT_DT	事件报告给警方的日期
BORO_NM	发生事件的区名称

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

clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \
--query \
"select formatReadableQuantity(uniq(OFNS_DESC)) as cardinality_OFNS_DESC,
        formatReadableQuantity(uniq(RPT_DT)) as cardinality_RPT_DT,
        formatReadableQuantity(uniq(BORO_NM)) as cardinality_BORO_NM
  FROM
  file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')
  FORMAT PrettyCompact"

结果

┌─cardinality_OFNS_DESC─┬─cardinality_RPT_DT─┬─cardinality_BORO_NM─┐
│ 60.00                 │ 306.00             │ 6.00                │
└───────────────────────┴────────────────────┴─────────────────────┘

按基数排序后，ORDER BY变为

ORDER BY ( BORO_NM, OFNS_DESC, RPT_DT )

注意

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

ORDER BY ( borough, offense_description, date_reported )

将数据类型更改和ORDER BY元组组合在一起，得到以下表结构。

CREATE TABLE NYPD_Complaint (
    complaint_number     String,
    precinct             UInt8,
    borough              LowCardinality(String),
    complaint_begin      DateTime64(0,'America/New_York'),
    complaint_end        DateTime64(0,'America/New_York'),
    was_crime_completed  String,
    housing_authority    String,
    housing_level_code   UInt32,
    jurisdiction_code    UInt8,
    jurisdiction         LowCardinality(String),
    offense_code         UInt8,
    offense_level        LowCardinality(String),
    location_descriptor  LowCardinality(String),
    offense_description  LowCardinality(String),
    park_name            LowCardinality(String),
    patrol_borough       LowCardinality(String),
    PD_CD                UInt16,
    PD_DESC              String,
    location_type        LowCardinality(String),
    date_reported        Date,
    transit_station      LowCardinality(String),
    suspect_age_group    LowCardinality(String),
    suspect_race         LowCardinality(String),
    suspect_sex          LowCardinality(String),
    transit_district     UInt8,
    victim_age_group     LowCardinality(String),
    victim_race          LowCardinality(String),
    victim_sex           LowCardinality(String),
    NY_x_coordinate      UInt32,
    NY_y_coordinate      UInt32,
    Latitude             Float64,
    Longitude            Float64
) ENGINE = MergeTree
  ORDER BY ( borough, offense_description, date_reported )

查找表的Primary Key

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

SELECT
    partition_key,
    sorting_key,
    primary_key,
    table
FROM system.tables
WHERE table = 'NYPD_Complaint'
FORMAT Vertical

响应

Query id: 6a5b10bf-9333-4090-b36e-c7f08b1d9e01

Row 1:
──────
partition_key:
sorting_key:   borough, offense_description, date_reported
primary_key:   borough, offense_description, date_reported
table:         NYPD_Complaint

1 row in set. Elapsed: 0.001 sec.

预处理和导入数据

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

`clickhouse-local`使用的参数

提示

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

cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv \
  | clickhouse-local --table='input' --input-format='TSVWithNames' \
  --input_format_max_rows_to_read_for_schema_inference=2000 \
  --query "
    WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START,
     (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END
    SELECT
      CMPLNT_NUM                                  AS complaint_number,
      ADDR_PCT_CD                                 AS precinct,
      BORO_NM                                     AS borough,
      parseDateTime64BestEffort(CMPLNT_START)     AS complaint_begin,
      parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end,
      CRM_ATPT_CPTD_CD                            AS was_crime_completed,
      HADEVELOPT                                  AS housing_authority_development,
      HOUSING_PSA                                 AS housing_level_code,
      JURISDICTION_CODE                           AS jurisdiction_code,
      JURIS_DESC                                  AS jurisdiction,
      KY_CD                                       AS offense_code,
      LAW_CAT_CD                                  AS offense_level,
      LOC_OF_OCCUR_DESC                           AS location_descriptor,
      OFNS_DESC                                   AS offense_description,
      PARKS_NM                                    AS park_name,
      PATROL_BORO                                 AS patrol_borough,
      PD_CD,
      PD_DESC,
      PREM_TYP_DESC                               AS location_type,
      toDate(parseDateTimeBestEffort(RPT_DT))     AS date_reported,
      STATION_NAME                                AS transit_station,
      SUSP_AGE_GROUP                              AS suspect_age_group,
      SUSP_RACE                                   AS suspect_race,
      SUSP_SEX                                    AS suspect_sex,
      TRANSIT_DISTRICT                            AS transit_district,
      VIC_AGE_GROUP                               AS victim_age_group,
      VIC_RACE                                    AS victim_race,
      VIC_SEX                                     AS victim_sex,
      X_COORD_CD                                  AS NY_x_coordinate,
      Y_COORD_CD                                  AS NY_y_coordinate,
      Latitude,
      Longitude
    FROM input" \
  | clickhouse-client --query='INSERT INTO NYPD_Complaint FORMAT TSV'

验证数据

注意

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

查询

SELECT count()
FROM NYPD_Complaint

结果

┌─count()─┐
│  208993 │
└─────────┘

1 row in set. Elapsed: 0.001 sec.

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

查询

SELECT formatReadableSize(total_bytes)
FROM system.tables
WHERE name = 'NYPD_Complaint'

结果

┌─formatReadableSize(total_bytes)─┐
│ 8.63 MiB                        │
└─────────────────────────────────┘

运行一些查询

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

查询

SELECT
    dateName('month', date_reported) AS month,
    count() AS complaints,
    bar(complaints, 0, 50000, 80)
FROM NYPD_Complaint
GROUP BY month
ORDER BY complaints DESC

结果

Query id: 7fbd4244-b32a-4acf-b1f3-c3aa198e74d9

┌─month─────┬─complaints─┬─bar(count(), 0, 50000, 80)───────────────────────────────┐
│ March     │      34536 │ ███████████████████████████████████████████████████████▎ │
│ May       │      34250 │ ██████████████████████████████████████████████████████▋  │
│ April     │      32541 │ ████████████████████████████████████████████████████     │
│ January   │      30806 │ █████████████████████████████████████████████████▎       │
│ February  │      28118 │ ████████████████████████████████████████████▊            │
│ November  │       7474 │ ███████████▊                                             │
│ December  │       7223 │ ███████████▌                                             │
│ October   │       7070 │ ███████████▎                                             │
│ September │       6910 │ ███████████                                              │
│ August    │       6801 │ ██████████▊                                              │
│ June      │       6779 │ ██████████▋                                              │
│ July      │       6485 │ ██████████▍                                              │
└───────────┴────────────┴──────────────────────────────────────────────────────────┘

12 rows in set. Elapsed: 0.006 sec. Processed 208.99 thousand rows, 417.99 KB (37.48 million rows/s., 74.96 MB/s.)

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

查询

SELECT
    borough,
    count() AS complaints,
    bar(complaints, 0, 125000, 60)
FROM NYPD_Complaint
GROUP BY borough
ORDER BY complaints DESC

结果

Query id: 8cdcdfd4-908f-4be0-99e3-265722a2ab8d

┌─borough───────┬─complaints─┬─bar(count(), 0, 125000, 60)──┐
│ BROOKLYN      │      57947 │ ███████████████████████████▋ │
│ MANHATTAN     │      53025 │ █████████████████████████▍   │
│ QUEENS        │      44875 │ █████████████████████▌       │
│ BRONX         │      44260 │ █████████████████████▏       │
│ STATEN ISLAND │       8503 │ ████                         │
│ (null)        │        383 │ ▏                            │
└───────────────┴────────────┴──────────────────────────────┘

6 rows in set. Elapsed: 0.008 sec. Processed 208.99 thousand rows, 209.43 KB (27.14 million rows/s., 27.20 MB/s.)

后续步骤

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

先决条件​

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

熟悉 TSV 文件​

查看源 TSV 文件中的字段​

确定合适的架构​

DateTime 字段​

制定计划​

连接日期和时间字段​

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

创建表​

Order By和Primary Key子句​

查找表的Primary Key​

预处理和导入数据​

clickhouse-local使用的参数​

验证数据​

运行一些查询​

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

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

后续步骤​