跳到主要内容
跳到主要内容
编辑此页

设计您的模式

虽然 模式推断 可以用于为 JSON 数据建立初始模式，并就地查询 JSON 数据文件（例如，在 S3 中），但用户应力求为其数据建立优化的版本化模式。我们将在下面讨论用于建模 JSON 结构的选项。

尽可能提取​

如果可能，我们鼓励用户将他们经常查询的 JSON 键提取到模式的根目录的列中。除了简化查询语法外，这还允许用户在需要时在 ORDER BY 子句中使用这些列，或指定二级索引。

考虑在指南 JSON 模式推断 中探讨的 arXiv 数据集

{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}

假设我们希望将 versions.created 的第一个值作为主要排序键 - 最好在名称 published_date 下。这应该在插入之前提取，或者在使用 ClickHouse 物化视图 或 物化列 的插入时提取。

物化列代表了在查询时提取数据的最简单方法，如果提取逻辑可以捕获为简单的 SQL 表达式，则首选物化列。例如，published_date 可以作为物化列添加到 arXiv 模式中，并定义为排序键，如下所示

CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `journal-ref` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String)),
    `published_date` DateTime DEFAULT parseDateTimeBestEffort(versions[1].1)
)
ENGINE = MergeTree
ORDER BY published_date
嵌套的列表达式

以上要求我们使用符号 versions[1].1 访问元组，通过位置而不是首选的 versions.created_at[1] 语法来引用 created 列。

在加载数据时，将提取该列

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
0 rows in set. Elapsed: 39.827 sec. Processed 2.52 million rows, 1.39 GB (63.17 thousand rows/s., 34.83 MB/s.)

SELECT published_date
FROM arxiv_2
LIMIT 2
┌──────published_date─┐
│ 2007-03-31 02:26:18 │
│ 2007-03-31 03:16:14 │
└─────────────────────┘

2 rows in set. Elapsed: 0.001 sec.
物化列行为

物化列的值始终在插入时计算，并且不能在 INSERT 查询中指定。默认情况下，物化列不会在 SELECT * 中返回。这是为了保留 SELECT * 的结果始终可以使用 INSERT 重新插入到表中的不变量。可以通过设置 asterisk_include_materialized_columns=1 来禁用此行为。

对于更复杂的过滤和转换任务，我们建议使用物化视图。

静态与动态 JSON​

在定义 JSON 模式的主要任务是确定每个键的值的适当类型。我们建议用户在 JSON 层次结构中的每个键上递归应用以下规则，以确定每个键的适当类型。

  1. 原始类型 - 如果键的值是原始类型，无论它是否是子对象的一部分或在根目录上，请确保您根据常规模式设计最佳实践和类型优化规则选择其类型。原始数组（例如，下面的 phone_numbers）可以建模为 Array(<type>)，例如 Array(String)。
  2. 静态与动态 - 如果键的值是复杂对象，即对象或对象数组，请确定它是否会发生更改。很少有新键的对象，其中新键的添加可以通过 ALTER TABLE ADD COLUMN 的模式更改来预测和处理，可以被认为是静态的。这包括仅在某些 JSON 文档上提供键的子集的对象。频繁添加新键和/或不可预测的对象应被认为是动态的。要确定值是静态还是动态，请参阅相关部分 处理静态对象 和 处理动态对象。

重要提示：上述规则应递归应用。如果键的值被确定为动态的，则不需要进一步评估，并且可以遵循 处理动态对象 中的指南。如果对象是静态的，请继续评估子键，直到键值是原始的或遇到动态键为止。

为了说明这些规则，我们使用以下 JSON 示例来表示一个人

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "[email protected]",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics",
    "labels": {
      "type": "database systems",
      "founded": "2021"
    }
  },
  "dob": "2007-03-31",
  "tags": {
    "hobby": "Databases",
    "holidays": [
      {
        "year": 2024,
        "location": "Azores, Portugal"
      }
    ],
    "car": {
      "model": "Tesla",
      "year": 2023
    }
  }
}

应用这些规则

  • 根键 name、username、email、website 可以表示为 String 类型。列 phone_numbers 是 Array(String) 类型的原始数组，dob 和 id 类型分别为 Date 和 UInt32。
  • 新键不会添加到 address 对象（仅新的地址对象），因此可以将其视为静态。如果我们递归，则所有子列都可以被认为是原始类型（和 String 类型），除了 geo。这也是一个静态结构，具有两个 Float32 列：lat 和 lon。
  • tags 列是动态的。我们假设可以将任意新标签添加到此对象，标签可以是任何类型和结构。
  • company 对象是静态的，并且始终最多包含指定的 3 个键。子键 name 和 catchPhrase 的类型为 String。键 labels 是动态的。我们假设可以将任意新标签添加到此对象。值将始终是字符串类型的键值对。

处理静态对象​

我们建议使用命名元组（即 Tuple）来处理静态对象。对象数组可以使用元组数组（即 Array(Tuple)）来保存。在元组本身中，应使用相同的规则定义列及其各自的类型。这可能会导致嵌套元组来表示嵌套对象，如下所示。

为了说明这一点，我们使用较早的 JSON person 示例，省略了动态对象

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "[email protected]",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}

此表的模式如下所示

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY username

请注意 company 列是如何定义为 Tuple(catchPhrase String, name String) 的。address 字段使用 Array(Tuple)，其中嵌套的 Tuple 用于表示 geo 列。

JSON 可以以其当前结构插入到此表中

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

在上面的示例中，我们的数据最少，但如下所示，我们可以通过句点分隔的名称查询元组字段。

SELECT
    address.street,
    company.name
FROM people

┌─address.street────┬─company.name─┐
│ ['Victor Plains'] │ ClickHouse   │
└───────────────────┴──────────────┘

请注意 address.street 列是如何作为 Array 返回的。要按位置查询数组内的特定对象，应在列名称后指定数组偏移量。例如，要访问第一个地址的街道

SELECT address.street[1] AS street
FROM people

┌─street────────┐
│ Victor Plains │
└───────────────┘

1 row in set. Elapsed: 0.001 sec.

元组的主要缺点是子列不能在排序键中使用。因此，以下操作将失败

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY company.name

Code: 47. DB::Exception: Missing columns: 'company.name' while processing query: 'company.name', required columns: 'company.name' 'company.name'. (UNKNOWN_IDENTIFIER)
排序键中的元组

虽然元组列不能在排序键中使用，但可以使用整个元组。虽然可能，但这很少有意义。

处理默认值​

即使 JSON 对象是结构化的，它们也通常是稀疏的，仅提供已知键的子集。幸运的是，Tuple 类型不需要 JSON 有效负载中的所有列。如果未提供，将使用默认值。

考虑我们较早的 people 表和以下稀疏 JSON，缺少键 suite、geo、phone_numbers 和 catchPhrase。

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "[email protected]",
  "address": [
    {
      "street": "Victor Plains",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771"
    }
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse"
  },
  "dob": "2007-03-31"
}

我们可以在下面看到此行可以成功插入

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","city":"Wisokyburgh","zipcode":"90566-7771"}],"website":"clickhouse.com","company":{"name":"ClickHouse"},"dob":"2007-03-31"}

Ok.

1 row in set. Elapsed: 0.002 sec.

查询此单行，我们可以看到对于省略的列（包括子对象），使用了默认值

SELECT *
FROM people
FORMAT PrettyJSONEachRow

{
    "id": "1",
    "name": "Clicky McCliickHouse",
    "username": "Clicky",
    "email": "[email protected]",
    "address": [
        {
            "city": "Wisokyburgh",
            "geo": {
                "lat": 0,
                "lng": 0
            },
            "street": "Victor Plains",
            "suite": "",
            "zipcode": "90566-7771"
        }
    ],
    "phone_numbers": [],
    "website": "clickhouse.com",
    "company": {
        "catchPhrase": "",
        "name": "ClickHouse"
    },
    "dob": "2007-03-31"
}

1 row in set. Elapsed: 0.001 sec.
区分空值和 Null 值

如果用户需要区分值为空值和未提供值，则可以使用 Nullable 类型。除非绝对必要，否则应避免使用此类型，因为它会对这些列的存储和查询性能产生负面影响。

处理新列​

虽然当 JSON 键是静态的时，结构化方法最简单，但如果可以计划对模式的更改，即提前知道新键，并且可以相应地修改模式，则仍然可以使用此方法。

请注意，默认情况下，ClickHouse 将忽略有效负载中提供但模式中不存在的 JSON 键。考虑以下修改后的 JSON 有效负载，其中添加了 nickname 键

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "nickname": "Clicky",
  "username": "Clicky",
  "email": "[email protected]",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}

此 JSON 可以成功插入，但 nickname 键将被忽略

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

Ok.

1 row in set. Elapsed: 0.002 sec.

可以使用 ALTER TABLE ADD COLUMN 命令将列添加到模式中。可以通过 DEFAULT 子句指定默认值，如果在后续插入期间未指定默认值，则将使用该默认值。对于此值不存在的行（因为它们是在创建之前插入的），也将返回此默认值。如果未指定 DEFAULT 值，则将使用该类型的默认值。

例如

-- insert initial row (nickname will be ignored)
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

-- add column
ALTER TABLE people
    (ADD COLUMN `nickname` String DEFAULT 'no_nickname')

-- insert new row (same data different id)
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

-- select 2 rows
SELECT id, nickname FROM people

┌─id─┬─nickname────┐
│  2 │ Clicky      │
│  1 │ no_nickname │
└────┴─────────────┘

2 rows in set. Elapsed: 0.001 sec.

处理动态对象​

有两种推荐的方法来处理动态对象

可以应用以下规则来确定最合适的方法。

  1. 如果对象是高度动态的，没有可预测的结构并且包含任意嵌套对象，则用户应使用 String 类型。可以使用 JSON 函数在查询时提取值，如下所示。
  2. 如果对象用于存储任意键，并且大多数键都是一种类型，请考虑使用 Map 类型。理想情况下，唯一键的数量不应超过数百个。对于具有子对象的对象，也可以考虑使用 Map 类型，前提是后者在其类型中具有一致性。通常，我们建议 Map 类型用于标签，例如日志数据中的 Kubernetes pod 标签。

应用对象级别方法

不同的技术可以应用于同一模式中的不同对象。某些对象最好使用 String 解决，而另一些对象最好使用 Map 解决。请注意，一旦使用 String 类型，就不需要再做出模式决策。相反，可以将子对象嵌套在 Map 键中，如下所示 - 包括表示 JSON 的 String。

使用 String​

对于那些具有动态 JSON 的用户来说，使用上述结构化方法处理数据通常是不可行的，动态 JSON 要么会发生更改，要么其模式不为人所知。为了绝对的灵活性，用户可以简单地将 JSON 存储为 String，然后再使用函数根据需要提取字段。这代表了将 JSON 作为结构化对象处理的极端对立面。这种灵活性会带来成本，并具有明显的缺点 - 主要是查询语法复杂性增加以及性能下降。

如前所述，对于原始的 person 对象，我们无法确保 tags 列的结构。我们插入原始行（我们还包括 company.labels，我们现在忽略它），将 Tags 列声明为 String

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

Ok.
1 row in set. Elapsed: 0.002 sec.

我们可以选择 tags 列，并看到 JSON 已作为字符串插入

SELECT tags
FROM people

┌─tags───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}} │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

JSONExtract 函数可用于从此 JSON 检索值。考虑下面的简单示例

SELECT JSONExtractString(tags, 'holidays') as holidays FROM people

┌─holidays──────────────────────────────────────┐
│ [{"year":2024,"location":"Azores, Portugal"}] │
└───────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

请注意，这些函数既需要引用 String 列 tags，也需要在 JSON 中提取的路径。嵌套路径需要嵌套函数，例如 JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')，它提取列 tags.car.year。可以通过函数 JSON_QUERY 和 JSON_VALUE 来简化嵌套路径的提取。

考虑 arxiv 数据集的极端情况，其中我们将整个正文视为 String。

CREATE TABLE arxiv (
  body String
)
ENGINE = MergeTree ORDER BY ()

要插入到此模式中，我们需要使用 JSONAsString 格式

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', 'JSONAsString')

0 rows in set. Elapsed: 25.186 sec. Processed 2.52 million rows, 1.38 GB (99.89 thousand rows/s., 54.79 MB/s.)

假设我们希望按年份计算发布的论文数量。将查询与模式的结构化版本与仅使用字符串的版本进行对比

-- using structured schema
SELECT
    toYear(parseDateTimeBestEffort(versions.created[1])) AS published_year,
    count() AS c
FROM arxiv_v2
GROUP BY published_year
ORDER BY c ASC
LIMIT 10

┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘

10 rows in set. Elapsed: 0.264 sec. Processed 2.31 million rows, 153.57 MB (8.75 million rows/s., 582.58 MB/s.)

-- using unstructured String

SELECT
    toYear(parseDateTimeBestEffort(JSON_VALUE(body, '$.versions[0].created'))) AS published_year,
    count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10

┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘

10 rows in set. Elapsed: 1.281 sec. Processed 2.49 million rows, 4.22 GB (1.94 million rows/s., 3.29 GB/s.)
Peak memory usage: 205.98 MiB.

请注意此处 XPath 表达式的用法，以按方法过滤 JSON，即 JSON_VALUE(body, '$.versions[0].created')。

字符串函数明显比使用索引的显式类型转换慢（> 10 倍）。上述查询始终需要全表扫描和处理每一行。虽然这些查询在这样的小型数据集上仍然很快，但在较大型数据集上性能会下降。

这种方法的灵活性以明显的性能和语法成本为代价，它应该仅用于模式中高度动态的对象。

简单 JSON 函数​

上面的示例使用 JSON* 函数系列。这些函数利用基于 simdjson 的完整 JSON 解析器，该解析器在解析中非常严格，并且将区分嵌套在不同级别的相同字段。这些函数能够处理语法正确但不格式良好的 JSON，例如键之间的双空格。

一组更快且更严格的函数可用。这些 simpleJSON* 函数提供可能更优越的性能，主要是通过对 JSON 的结构和格式做出严格的假设。具体来说

  • 字段名称必须是常量

  • 字段名称的编码一致，例如 simpleJSONHas('{"abc":"def"}', 'abc') = 1，但 visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0

  • 字段名称在所有嵌套结构中都是唯一的。不区分嵌套级别，并且匹配是不加区分的。如果存在多个匹配字段，则使用第一个出现的字段。

  • 字符串文字之外没有特殊字符。这包括空格。以下内容无效，并且不会解析。

    {"@timestamp": 893964617, "clientip": "40.135.0.0", "request": {"method": "GET",
    "path": "/images/hm_bg.jpg", "version": "HTTP/1.0"}, "status": 200, "size": 24736}

    而以下内容将正确解析

    {"@timestamp":893964617,"clientip":"40.135.0.0","request":{"method":"GET",
    "path":"/images/hm_bg.jpg","version":"HTTP/1.0"},"status":200,"size":24736}

在某些情况下，当性能至关重要且您的 JSON 满足上述要求时，这些函数可能是合适的。下面显示了早期查询的示例，已重写为使用 simpleJSON* 函数

SELECT
    toYear(parseDateTimeBestEffort(simpleJSONExtractString(simpleJSONExtractRaw(body, 'versions'), 'created'))) AS published_year,
    count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10

┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘

10 rows in set. Elapsed: 0.964 sec. Processed 2.48 million rows, 4.21 GB (2.58 million rows/s., 4.36 GB/s.)
Peak memory usage: 211.49 MiB.

上面使用 simpleJSONExtractString 提取 created 键，利用了我们只想将第一个值用于发布日期的事实。在这种情况下，simpleJSON* 函数的局限性对于性能的提升是可以接受的。

使用 Map​

如果对象用于存储主要为一种类型的任意键，请考虑使用 Map 类型。理想情况下，唯一键的数量不应超过数百个。我们建议 Map 类型用于标签，例如日志数据中的 Kubernetes pod 标签。虽然 Map 是一种表示嵌套结构的简单方法，但它有一些明显的局限性

  • 字段必须都是相同的类型。
  • 访问子列需要特殊的 map 语法，因为字段不作为列存在；整个对象都是一个列。
  • 访问子列会加载整个 Map 值，即所有同级及其各自的值。对于较大的 map，这可能会导致明显的性能损失。
字符串键

当将对象建模为 Map 时，使用 String 键来存储 JSON 键名称。因此，map 将始终是 Map(String, T)，其中 T 取决于数据。

原始值​

Map 最简单的应用是当对象包含相同原始类型的作为值时。在大多数情况下，这涉及对值 T 使用 String 类型。

考虑我们较早的 person JSON，其中 company.labels 对象被确定为动态的。重要的是，我们只希望将字符串类型的键值对添加到此对象。因此，我们可以将其声明为 Map(String, String)

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String, labels Map(String,String)),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username

我们可以插入我们原始的完整 JSON 对象

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

Ok.

1 row in set. Elapsed: 0.002 sec.

在请求对象中查询这些字段需要 map 语法，例如

SELECT company.labels FROM people

┌─company.labels───────────────────────────────┐
│ {'type':'database systems','founded':'2021'} │
└──────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

SELECT company.labels['type'] AS type FROM people

┌─type─────────────┐
│ database systems │
└──────────────────┘

1 row in set. Elapsed: 0.001 sec.

一组完整的 Map 函数可用于此时查询，此处描述。如果您的数据类型不一致，则存在执行必要类型强制转换的函数。

对象值​

对于具有子对象的对象，也可以考虑使用 Map 类型，前提是后者在其类型中具有一致性。

假设我们 persons 对象的 tags 键需要一致的结构，其中每个 tag 的子对象都具有 name 和 time 列。此类 JSON 文档的简化示例可能如下所示

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "[email protected]",
  "tags": {
    "hobby": {
      "name": "Diving",
      "time": "2024-07-11 14:18:01"
    },
    "car": {
      "name": "Tesla",
      "time": "2024-07-11 15:18:23"
    }
  }
}

这可以使用 Map(String, Tuple(name String, time DateTime)) 进行建模，如下所示

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `tags` Map(String, Tuple(name String, time DateTime))
)
ENGINE = MergeTree
ORDER BY username

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","tags":{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"},"car":{"name":"Tesla","time":"2024-07-11 15:18:23"}}}

Ok.

1 row in set. Elapsed: 0.002 sec.

SELECT tags['hobby'] AS hobby
FROM people
FORMAT JSONEachRow

{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"}}

1 row in set. Elapsed: 0.001 sec.

在这种情况下，map 的应用通常很少见，并且表明应该对数据进行重新建模，以便动态键名称没有子对象。例如，可以将上述内容重新建模为如下所示，从而允许使用 Array(Tuple(key String, name String, time DateTime))。

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "[email protected]",
  "tags": [
    {
      "key": "hobby",
      "name": "Diving",
      "time": "2024-07-11 14:18:01"
    },
    {
      "key": "car",
      "name": "Tesla",
      "time": "2024-07-11 15:18:23"
    }
  ]
}