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

建模 JSON 的其他方法

以下是在 ClickHouse 中建模 JSON 的替代方案。这些文档完整记录，但通常不推荐或不适用于大多数用例。

使用 Nested​

Nested 类型可用于建模很少更改的静态对象，提供 Tuple 和 Array(Tuple) 的替代方案。我们通常建议避免将此类型用于 JSON，因为它的行为通常令人困惑。Nested 的主要优点是子列可以用于排序键。

下面，我们提供一个使用 Nested 类型建模静态对象的示例。考虑以下 JSON 格式的简单日志条目

{
  "timestamp": 897819077,
  "clientip": "45.212.12.0",
  "request": {
    "method": "GET",
    "path": "/french/images/hm_nav_bar.gif",
    "version": "HTTP/1.0"
  },
  "status": 200,
  "size": 3305
}
``

We can declare the `request` key as `Nested`. Similar to `Tuple`, we are required to specify the sub columns.

```sql
-- default
SET flatten_nested=1
CREATE table http
(
   timestamp Int32,
   clientip     IPv4,
   request Nested(method LowCardinality(String), path String, version LowCardinality(String)),
   status       UInt16,
   size         UInt32,
) ENGINE = MergeTree() ORDER BY (status, timestamp);

flatten_nested​

设置 flatten_nested 控制 nested 的行为。

flatten_nested=1​

值 1（默认值）不支持任意级别的嵌套。使用此值，最容易将嵌套数据结构视为相同长度的多个 Array 列。字段 method、path 和 version 实际上都是单独的 Array(Type) 列，但有一个关键约束：method、path 和 version 字段的长度必须相同。 如果我们使用 SHOW CREATE TABLE，这将得到说明

SHOW CREATE TABLE http

CREATE TABLE http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request.method` Array(LowCardinality(String)),
    `request.path` Array(String),
    `request.version` Array(LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

下面，我们插入到此表中

SET input_format_import_nested_json = 1;
INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}

这里需要注意几个要点

  • 我们需要使用设置 input_format_import_nested_json 将 JSON 作为嵌套结构插入。否则，我们需要展平 JSON，即

    INSERT INTO http FORMAT JSONEachRow
    {"timestamp":897819077,"clientip":"45.212.12.0","request":{"method":["GET"],"path":["/french/images/hm_nav_bar.gif"],"version":["HTTP/1.0"]},"status":200,"size":3305}

  • 嵌套字段 method、path 和 version 需要作为 JSON 数组传递，即

    {
      "@timestamp": 897819077,
      "clientip": "45.212.12.0",
      "request": {
        "method": [
          "GET"
        ],
        "path": [
          "/french/images/hm_nav_bar.gif"
        ],
        "version": [
          "HTTP/1.0"
        ]
      },
      "status": 200,
      "size": 3305
    }

可以使用点符号查询列

SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');

┌─clientip────┬─status─┬─size─┬─request.method─┐
│ 45.212.12.0 │    200 │ 3305 │ ['GET']        │
└─────────────┴────────┴──────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.

请注意，子列使用 Array 意味着可以利用完整的 Array 函数，包括 ARRAY JOIN 子句 - 如果您的列有多个值，则非常有用。

flatten_nested=0​

这允许任意级别的嵌套，并意味着嵌套列保持为 Tuple 的单个数组 - 实际上它们变得与 Array(Tuple) 相同。

这代表了使用 JSON 和 Nested 的首选方式，通常也是最简单的方式。正如我们在下面展示的，它只需要所有对象都是列表。

下面，我们重新创建我们的表并重新插入一行

CREATE TABLE http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

SHOW CREATE TABLE http

-- note Nested type is preserved.
CREATE TABLE default.http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}

这里需要注意几个要点

  • 插入不需要 input_format_import_nested_json。

  • Nested 类型保留在 SHOW CREATE TABLE 中。此列下面实际上是 Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))

  • 因此，我们需要将 request 作为数组插入，即

    {
      "timestamp": 897819077,
      "clientip": "45.212.12.0",
      "request": [
        {
          "method": "GET",
          "path": "/french/images/hm_nav_bar.gif",
          "version": "HTTP/1.0"
        }
      ],
      "status": 200,
      "size": 3305
    }

可以再次使用点符号查询列

SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');

┌─clientip────┬─status─┬─size─┬─request.method─┐
│ 45.212.12.0 │    200 │ 3305 │ ['GET']        │
└─────────────┴────────┴──────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.

示例​

更大的上述数据示例可在 s3 的公共存储桶中找到：s3://datasets-documentation/http/。

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONEachRow')
LIMIT 1
FORMAT PrettyJSONEachRow

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

1 row in set. Elapsed: 0.312 sec.

考虑到 JSON 的约束和输入格式，我们使用以下查询插入此示例数据集。在这里，我们设置 flatten_nested=0。

以下语句插入 1000 万行，因此可能需要几分钟才能执行。如果需要，应用 LIMIT

INSERT INTO http
SELECT `@timestamp` AS `timestamp`, clientip, [request], status,
size FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz',
'JSONEachRow');

查询此数据需要我们将请求字段作为数组访问。下面，我们总结了固定时间段内的错误和 http 方法。

SELECT status, request.method[1] as method, count() as c
FROM http
WHERE status >= 400
  AND toDateTime(timestamp) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP by method, status
ORDER BY c DESC LIMIT 5;

┌─status─┬─method─┬─────c─┐
│    404 │ GET    │ 11267 │
│    404 │ HEAD   │   276 │
│    500 │ GET    │   160 │
│    500 │ POST   │   115 │
│    400 │ GET    │    81 │
└────────┴────────┴───────┘

5 rows in set. Elapsed: 0.007 sec.

使用成对数组​

成对数组在将 JSON 表示为字符串的灵活性和更结构化方法的性能之间提供了平衡。Schema 很灵活，因为任何新字段都可以潜在地添加到根目录。然而，这需要明显更复杂的查询语法，并且与嵌套结构不兼容。

作为示例，考虑以下表

CREATE TABLE http_with_arrays (
   keys Array(String),
   values Array(String)
)
ENGINE = MergeTree  ORDER BY tuple();

要插入到此表中，我们需要将 JSON 结构化为键和值的列表。以下查询说明了如何使用 JSONExtractKeysAndValues 来实现此目的

SELECT
    arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
    arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')
LIMIT 1
FORMAT Vertical

Row 1:
──────
keys:   ['@timestamp','clientip','request','status','size']
values: ['893964617','40.135.0.0','{"method":"GET","path":"/images/hm_bg.jpg","version":"HTTP/1.0"}','200','24736']

1 row in set. Elapsed: 0.416 sec.

请注意，request 列仍然是表示为字符串的嵌套结构。我们可以将任何新键插入到根目录。我们还可以让 JSON 本身存在任意差异。要插入到我们的本地表中，请执行以下操作

INSERT INTO http_with_arrays
SELECT
    arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
    arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')

0 rows in set. Elapsed: 12.121 sec. Processed 10.00 million rows, 107.30 MB (825.01 thousand rows/s., 8.85 MB/s.)

查询此结构需要使用 indexOf 函数来识别所需键的索引（这应与值的顺序一致）。这可以用于访问 values 数组列，即 values[indexOf(keys, 'status')]。我们仍然需要用于 request 列的 JSON 解析方法 - 在这种情况下，为 simpleJSONExtractString。

SELECT toUInt16(values[indexOf(keys, 'status')])                           as status,
       simpleJSONExtractString(values[indexOf(keys, 'request')], 'method') as method,
       count()                                                             as c
FROM http_with_arrays
WHERE status >= 400
  AND toDateTime(values[indexOf(keys, '@timestamp')]) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP by method, status ORDER BY c DESC LIMIT 5;

┌─status─┬─method─┬─────c─┐
│    404 │ GET    │ 11267 │
│    404 │ HEAD   │   276 │
│    500 │ GET    │   160 │
│    500 │ POST   │   115 │
│    400 │ GET    │    81 │
└────────┴────────┴───────┘

5 rows in set. Elapsed: 0.383 sec. Processed 8.22 million rows, 1.97 GB (21.45 million rows/s., 5.15 GB/s.)
Peak memory usage: 51.35 MiB.