跳至主要内容

设计您的架构

虽然 架构推断 可以用来为 JSON 数据建立初始架构，并在 S3 等地方查询 JSON 数据文件，但用户应该力求为其数据建立一个经过优化的版本化架构。 我们将在下面讨论用于建模 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 访问元组，通过位置引用 created 列，而不是首选的语法 versions.created_at[1]。

加载数据时，将提取该列

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 定义架构的主要任务是确定每个键值的适当类型。 我们建议用户对 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 人员示例，省略动态对象

{
  "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键。考虑以下添加了nickname键的修改后的JSON有效负载。

{
  "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作为结构化对象处理的极端相反面。这种灵活性带来了巨大的成本，主要体现在查询语法复杂度增加以及性能下降。

如前所述，对于最初的人员对象，我们无法确保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时，使用String键来存储JSON键名。因此，映射总是Map(String, T)，其中T取决于数据。

原始值​

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

考虑我们之前的人员JSON，其中company.labels对象被确定为动态的。重要的是，我们只期望向该对象添加类型为String的键值对。因此，我们可以将其声明为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.

查询请求对象中的这些字段需要使用映射语法，例如。

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.

在这种情况下，映射的应用通常很少见，这表明应该对数据进行重新建模，以便动态键名没有子对象。例如，上面的内容可以重新建模如下，从而可以使用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"
    }
  ]
}