这篇博文是系列文章的一部分
本篇博文继续“将数据导入 ClickHouse”系列。在之前的博文中，我们展示了加载 Hacker News 数据集的基础知识。除了向入门用户演示模式推断的强大功能外，我们还强调了定义模式以实现高效查询执行的必要性。在深入研究更复杂的主题（如模式优化和主键选择）之前，我们利用 Hacker News 数据集以 JSON 格式分发这一事实，进行一次小的绕行，探索 ClickHouse 最近添加的 JSON 功能。
JSON 文件格式因其人类可读、自描述的格式而成为编码数据集（如结构化日志）的通用格式。ClickHouse 已成为分析 JSON 编码的日志和事件的越来越流行的数据存储。JSON 数据类型通过允许从数据中自动推断和创建列来增强现有的 JSON 功能，即除了将列声明为“JSON”之外，没有其他要求。这使得 ClickHouse 能够处理动态模式，在动态模式中，新的列会不断添加和删除。在本博文中，我们将通过 Hacker News 数据集演示这些功能。
虽然以下示例是使用 ClickHouse Cloud 执行的，但所有命令也与在具有互联网连接的普通笔记本电脑上运行的自管理 ClickHouse 集群兼容。我们的客户端托管在具有 16 个核心和 32GB RAM 的 c5ad.4xlarge 上。
截至 22.8 版本，JSON 数据类型在 ClickHouse 中仍处于实验阶段。因此，此功能尚未准备好用于生产环境。我们鼓励用户进行实验并提供反馈，以便我们尽快使其达到生产就绪状态。
下载
我们的示例都通过 url 函数访问文件，不需要用户显式下载数据集。但是，对于更好奇的读者，可以从此处下载数据集的 JSON 版本。这个压缩文件大小为 4.9GB，包含 2800 万行，应该需要 5-10 分钟才能下载完成。
读取 JSON
我们的 Hacker News 数据实际上是 ndjson，每行一个 JSON 行。这等同于 ClickHouse 的 JSONEachRow 格式。使用 DESCRIBE 命令，我们可以在采样行之前检查模式推断分配的类型。
DESCRIBE TABLE url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', 'JSONEachRow'); ┌─name────────┬─type─────────────────────| │ id │ Nullable(Float64) │ │ deleted │ Nullable(Float64) │ │ type │ Nullable(String) │ │ by │ Nullable(String) │ │ time │ Nullable(String) │ │ text │ Nullable(String) │ │ dead │ Nullable(Float64) │ │ parent │ Nullable(Float64) │ │ poll │ Nullable(Float64) │ │ kids │ Array(Nullable(Float64)) │ │ url │ Nullable(String) │ │ score │ Nullable(Float64) │ │ title │ Nullable(String) │ │ parts │ Array(Nullable(Float64)) │ │ descendants │ Nullable(Float64) │ └─────────────┴──────────────────────────|
SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', JSONEachRow) LIMIT 1 FORMAT Vertical; Row 1: ────── id: 11132929 deleted: 0 type: story by: brakmic time: 2016-02-19 11:40:55 text: dead: 1 parent: 0 poll: 0 kids: [11133170] url: https://www.washingtonpost.com/news/morning-mix/wp/2016/02/18/s-f-tech-bro-writes-open-letter-to-mayor-i-shouldnt-have-to-see-the-pain-struggle-and-despair-of-homeless-people/ score: 3 title: SF ‘tech bro’ writes to mayor: ‘I shouldn’t have to see the despair of homeless’ parts: [] descendants: 0
本系列之前博文的读者会注意到，这与我们的 CSV 示例没有显著不同 - 每列都由模式推断分配类型。虽然我们将数据作为 JSON 读取，但我们并没有将其视为 JSON 对象。为了实现这一点，我们引入了 JSONAsObject 格式。
DESCRIBE TABLE url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', 'JSONAsObject', 'post JSON'); ┌─name──┬─type───────────| │ post │ Object('json') │ └───────┴────────────────┴
SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', 'JSONAsObject', 'post JSON') LIMIT 1 FORMAT JSONEachRow; {"post":{"by":"brakmic","dead":1,"deleted":0,"descendants":0,"id":11132929,"kids":[11133170],"parent":0,"poll":0,"score":3,"text":"","time":"2016-02-19 11:40:55","title":"SF ‘tech bro’ writes to mayor: ‘I shouldn’t have to see the despair of homeless’","type":"story", "url":"https:\/\/www.washingtonpost.com\/news\/morning-mix\/wp\/2016\/02\/18\/s-f-tech-bro-writes-open-letter-to-mayor-i-shouldnt-have-to-see-the-pain-struggle-and-despair-of-homeless-people\/"}} 1 row in set. Elapsed: 3.456 sec.
这需要我们为 JSON 指定一个目标字段：在本例中为 post。请注意，我们还可以通过重用 JSONEachRow 格式来请求以 JSON 格式返回输出。关键的观察结果是，post 列被分配了新的类型 Object('json')。
JSON 作为对象
上面，我们介绍了 Object('json') 类型来表示 JSON 行。假设我们将 Hacker News 行映射到此类型，我们的模式定义将变得非常简单。这就是此类型的真正强大之处。我们的数据现在被视为半结构化数据，任何子列都将自动创建，并且它们的类型将从数据中推断出来！
DROP TABLE IF EXISTS hackernews; SET allow_experimental_object_type = 1; CREATE TABLE hackernews ( `post` JSON ) ENGINE = MergeTree ORDER BY tuple();
这里有几个重要的观察结果。我们使用 JSON 类型作为 Object('JSON') 的缩写 - 未来，我们可能会添加 JSON 之外的其他 Object 表示形式。其次，由于此类型是实验性的，因此我们需要设置 allow_experimental_object_type = 1 才能使用此类型。最后，请注意，我们无法将 post 的任何子列用作主键，因此我们将 ORDER BY as tuple() 定义为，即没有主键。我们稍后会解决此限制。
创建此表后，插入操作变得非常简单。
INSERT INTO hackernews SELECT post FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', 'JSONAsObject', 'post JSON') 0 rows in set. Elapsed: 214.846 sec. Processed 28.74 million rows, 13.42 GB (133.76 thousand rows/s., 62.46 MB/s.)
选择单行可以让我们深入了解 JSON 在 ClickHouse 中的表示方式。
SELECT * FROM hackernews LIMIT 1 FORMAT Vertical Row 1: ────── post: ('brakmic',1,0,0,11132929,[11133170],0,[],0,3,'','2016-02-19 11:40:55','SF ‘tech bro’ writes to mayor: ‘I shouldn’t have to see the despair of homeless’','story','https://www.washingtonpost.com/news/morning-mix/wp/2016/02/18/s-f-tech-bro-writes-open-letter-to-mayor-i-shouldnt-have-to-see-the-pain-struggle-and-despair-of-homeless-people/')
使用 DESCRIBE 命令并设置 describe_extend_object_types=1 确认了使用元组作为内部表示形式
DESCRIBE TABLE hackernews FORMAT Vertical SETTINGS describe_extend_object_types = 1 Query id: bcbf8c48-82eb-4916-869c-842a32445711 Row 1: ────── name: post type: Tuple(by String, dead Int8, deleted Int8, descendants Int16, id Int32, kids Array(Int32), parent Int32, parts Array(Int32), poll Int32, score Int16, text String, time String, title String, type String, url String)
除了该类型通过在底层使用元组、数组和嵌套列来提供 JSON 的清晰表示之外，它还确保在数据中出现新列时会自动创建这些新列并推断出类型。截至 22.8 版本，类型推断适用于数值和字符串：前者根据观察到的数据分配可能的最小位表示形式。
查询 JSON
查询每行中的列需要我们使用点表示法来指示 JSON 中的字段路径。下面我们重复本系列第一篇博文中的一个查询，该查询用于识别关于 ClickHouse 的帖子数量。由于我们的 time 列已映射为 String，因此我们被迫在查询时使用 parseDateTimeBestEffort 函数进行解析 - 强制线性扫描。为了简洁起见，我们还将结果限制为最近 2 年。
SELECT toYYYYMM(parseDateTimeBestEffort(post.time)) AS monthYear, bar(count(), 0, 120, 20) FROM hackernews WHERE (post.type IN ('story', 'comment')) AND ((post.title ILIKE '%ClickHouse%') OR (post.text ILIKE '%ClickHouse%')) AND (monthYear > '201908') GROUP BY monthYear ORDER BY monthYear ASC ┌─monthYear─┬─bar(count(), 0, 120, 20)─┐ │ 201909 │ █▋ │ │ 201910 │ █ │ │ 201911 │ ███ │ │ 201912 │ █▎ │ │ 202001 │ ███████████▋ │ │ 202002 │ ██████▌ │ │ 202003 │ ███████████▋ │ │ 202004 │ ███████▎ │ │ 202005 │ ██████▏ │ │ 202006 │ ██████▏ │ │ 202007 │ ███████▋ │ │ 202008 │ ███▋ │ │ 202009 │ ████ │ │ 202010 │ ████▌ │ │ 202011 │ █████▏ │ │ 202012 │ ███▋ │ │ 202101 │ ███▏ │ │ 202102 │ █████████ │ │ 202103 │ █████████████▋ │ │ 202104 │ ███▏ │ │ 202105 │ ████████████▋ │ │ 202106 │ ███ │ │ 202107 │ █████▏ │ │ 202108 │ ████▎ │ │ 202109 │ ██████████████████▎ │ │ 202110 │ ▏ │ └───────────┴──────────────────────────┘ 26 rows in set. Elapsed: 2.626 sec. Processed 28.74 million rows, 11.47 GB (10.94 million rows/s., 4.37 GB/s.)
克服限制
如上所述，我们目前无法将 JSON 子列用作主键，因为 ClickHouse 在表创建时不知道潜在的动态列。出于类似的原因，我们无法为子列指定编解码器。为了克服此限制，我们建议用户对行中易于更改的半结构化部分使用 JSON 类型，但为那些可以声明可靠结构和类型的列显式指定列。例如，在下面，我们显式定义了 time 和 type 列，但将其他列留给 post 列捕获。反过来，这使我们能够将它们定义为主键的组成部分。
SET allow_experimental_object_type = 1; DROP TABLE IF EXISTS hackernews; CREATE TABLE hackernews ( `post` JSON, `type` String, `time` DateTime ) ENGINE = MergeTree ORDER BY (time, type);
要插入数据，我们需要使用 JSONAsString 格式，该格式将每一行解释为单个 JSON 字符串。这允许使用 JSONExtract* 函数来提取特定字段。未来版本的 ClickHouse 将支持 JSONAsObject 的这些函数。为了清晰起见，我们包含了 url 函数的可选 post String 组件。如果排除，ClickHouse 将推断出一个 json 列。
INSERT INTO hackernews SELECT post::JSON, JSONExtractString(post, 'type') AS type, toDateTime(JSONExtractString(post, 'time')) AS time FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz', 'JSONAsString', 'post String') 0 rows in set. Elapsed: 335.605 sec. Processed 28.74 million rows, 16.24 GB (85.63 thousand rows/s., 48.38 MB/s.)
重复之前的查询，并调整我们的字段名称，证明了单独管理我们的结构化字段并在主键中使用它们带来的性能优势（注意速度提升！）。
SELECT toYYYYMM(time) AS monthYear, bar(count(), 0, 120, 20) FROM hackernews WHERE (type IN ('story', 'comment')) AND ((post.title ILIKE '%ClickHouse%') OR (post.text ILIKE '%ClickHouse%')) AND (monthYear > '201908') GROUP BY monthYear ORDER BY monthYear ASC // omitted for brevity 26 rows in set. Elapsed: 0.742 sec. Processed 7.93 million rows, 3.09 GB (10.69 million rows/s., 4.17 GB/s.)
一些注意事项
ClickHouse 只能根据它已看到的数据来推断类型。对于数值类型，我们的目标是通过分配可能的最低位表示形式来最大限度地减少资源利用率（磁盘和内存）。但是，如果出现需要扩展的新值，则类型将透明地更改。例如，假设我们收到数据的更新，并且添加了一个新列“likes”来表示添加到 Hacker News 的等效功能。
如果我们插入一条包含 2 个赞的评论，我们的新列最初将表示为 Int8。
INSERT INTO hackernews(post, type, time) FORMAT JSONEachRow {"post":{"by":"zX41ZdbW","id":20684796,"text":"ClickHouse is happy to use multiple cores if the query is heavy enough.","likes":2},"type":"comment","time":"2022-08-12 11:48:08"};
DESCRIBE TABLE hackernews FORMAT Vertical SETTINGS describe_extend_object_types = 1 Query id: c1a80073-b5c3-48c1-aa77-7cef5f3eb2d1 Row 1: ────── name: post type: Tuple(by String, dead Int8, deleted Int8, descendants Int16, id Int32, kids Array(Int32), likes Int8, parent Int32, parts Array(Int32), poll Int32, score Int16, text String, time String, title String, type String, url String) default_type: default_expression: comment: codec_expression: ttl_expression:
但是，假设第二条评论收到数千个赞。
INSERT INTO hackernews(post, type, time) FORMAT JSONEachRow {"post":{"by":"dalem","id":1111111,"text":"ClickHouse is just the best OLAP DB ever!!!!!.","likes":2000000},"type":"comment","time":"2022-08-13 11:48:08"};
DESCRIBE TABLE hackernews FORMAT Vertical SETTINGS describe_extend_object_types = 1 Query id: 3056120f-1e7c-4494-b43a-510518c93380 Row 1: ────── name: post type: Tuple(by String, dead Int8, deleted Int8, descendants Int16, id Int32, kids Array(Int32), likes Int32, parent Int32, parts Array(Int32), poll Int32, score Int16, text String, time String, title String, type String, url String) default_type: default_expression: comment: codec_expression: ttl_expression:
我们的列已透明地映射到 UInt32。
此功能很少影响查询。但是，如果该值作为字符串发送（例如，由于单个“脏”文档），它将透明地将列转换为 String 类型。这可能会破坏依赖于数值的查询。要么认真清理您的数据，要么在 JSON 外部显式声明列以强制类型正确性。
总结
在本博文中，我们探讨了读取 JSON 数据集和 JSON 对象类型。我们演示了这种类型的灵活性及其适应不断变化的数据的能力，以及一些限制以及如何克服这些限制。未来的博文将更充分地利用此功能（一旦它不再是实验性的），以解决诸如使用 ClickHouse 作为日志存储之类的问题。
对于希望了解有关 ClickHouse 的 JSON 功能的更多信息的用户，我们已更新文档以描述处理半结构化数据的替代方法。
