这篇文章是系列的一部分
这篇文章继续了“将数据导入 ClickHouse”系列。在上一篇文章中，我们展示了加载 Hacker News 数据集的基本方法。除了向入门用户演示模式推断功能，我们还强调了为高效查询执行定义模式的必要性。在深入探讨模式优化和主键选择等更复杂主题之前，我们利用 Hacker News 数据集作为 JSON 格式发布这一事实，稍作绕道，探索 ClickHouse 最近添加的 JSON 功能。
JSON 文件格式已成为编码数据集（如结构化日志）的通用格式，因为它具有易于人类阅读、自描述的格式。ClickHouse 作为分析 JSON 编码的日志和事件的越来越受欢迎的数据存储。JSON 数据类型通过允许从数据自动推断和创建列来增强现有的 JSON 功能，即除了将列声明为“JSON”之外，没有其他要求。这允许 ClickHouse 处理动态模式，其中新列会不断添加和删除。在这篇文章中，我们将通过 Hacker News 数据集来演示这些功能。
虽然下面的示例是使用 ClickHouse Cloud 执行的，但所有命令也都与在拥有互联网访问权限的普通笔记本电脑上运行的自管理 ClickHouse 集群兼容。我们的客户端托管在具有 16 个内核和 32 GB 内存的 c5ad.4xlarge 上。
截至 22.8 版本，JSON 数据类型在 ClickHouse 中仍然是实验性的。因此，此功能尚未准备好用于生产环境。鼓励用户在过渡到生产就绪状态期间进行试验并提供反馈。
下载
我们的所有示例都通过 url 函数访问文件，无需用户显式下载数据集。但是，对于更感兴趣的读者，可以从此处下载数据集的 JSON 版本。压缩后的文件大小为 4.9 GB，包含 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 定义为 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.)
选择一行，我们可以了解 ClickHouse 中 JSON 的表示方式。
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 列被映射为字符串，因此我们被迫使用 parseDateTimeBestEffort 函数在查询时解析它 - 强制进行线性扫描。为简洁起见，我们还将结果限制在最近两年。
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。
此功能很少影响查询。但是，如果值以字符串形式发送（例如，由于单个“脏”文档），它将透明地将列转换为字符串类型。这可能会破坏依赖于数值的查询。请仔细清理您的数据或在 JSON 外部显式声明列以强制执行类型正确性。
摘要
在这篇文章中，我们探讨了读取 JSON 数据集和 JSON 对象类型。我们展示了这种类型的灵活性及其适应变化数据的的能力，以及一些限制和如何克服这些限制。一旦不再是实验性的，未来的帖子将更充分地利用此功能来解决诸如将 ClickHouse 用作日志存储等问题。
对于希望了解 ClickHouse JSON 功能的更多信息的使用者，我们已更新了我们的 文档 以描述处理半结构化数据的替代方法。
