其他建模 JSON 的方法

以下是 ClickHouse 中建模 JSON 的替代方法。出于完整性考虑，此处对其进行了记录，但在大多数用例中通常不建议或不适用。

使用嵌套

可以使用嵌套类型对很少发生更改的静态对象进行建模，从而提供 Tuple 和 Array(Tuple) 的替代方案。我们通常建议避免将此类型用于 JSON，因为其行为通常令人困惑。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 控制嵌套的行为。

flatten_nested=1

值为 1（默认值）不支持任意级别的嵌套。使用此值，最容易将嵌套数据结构视为多个相同长度的数组列。字段 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 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 表示为字符串的灵活性与更结构化方法的性能之间取得平衡。架构是灵活的，因为任何新字段都可能被添加到根节点。但是，这需要更复杂的查询语法，并且与嵌套结构不兼容。

例如，请考虑以下表格

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')]。我们仍然需要 JSON 解析方法来处理 request 列 - 在这种情况下，为 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.

其他建模 JSON 的方法

使用嵌套​

flatten_nested​

flatten_nested=1​

flatten_nested=0​

示例​

使用成对数组​

使用嵌套

flatten_nested

flatten_nested=1

flatten_nested=0

示例

使用成对数组