其他建模 JSON 的方法
以下是 ClickHouse 中建模 JSON 的替代方案。这些方法是为了完整性而记录的,并且在 JSON 类型开发之前适用,因此在大多数用例中通常不推荐使用。
在相同的模式中,可以对不同的对象应用不同的技术。例如,某些对象可以使用 String
类型解决,而其他对象则可以使用 Map
类型。请注意,一旦使用了 String
类型,则不需要做进一步的模式决定。相反,可以在 Map
键中嵌套子对象 - 包括一个表示 JSON 的 String
- 正如我们下面所示:
使用 String
如果对象高度动态,结构不可预测并包含任意嵌套对象,用户应使用 String
类型。可以在查询时使用 JSON 函数提取值,如下所示。
对于拥有动态 JSON 的用户,使用上述结构化方法处理数据通常不可行,这些 JSON 要么有可能会变化,要么其模式不易理解。为了获得绝对的灵活性,用户可以简单地将 JSON 作为 String
存储,然后使用函数根据需要提取字段。这代表了将 JSON 处理为结构化对象的极端对立面。这种灵活性带来了显著的缺陷 - 主要是查询语法复杂性的增加以及性能下降。
如前所述,对于 原始人员对象,我们无法确保 tags
列的结构。我们插入原始行(包括 company.labels
,我们暂时忽略),将 Tags
列声明为 String
:
我们可以选择 tags
列,并看到 JSON 已作为字符串插入:
JSONExtract
函数可用于检索此 JSON 中的值。考虑下面这个简单的例子:
请注意,这些函数需要同时引用 String
列 tags
和提取 JSON 的路径。嵌套路径要求函数嵌套,例如 JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')
,它提取列 tags.car.year
。通过函数 JSON_QUERY
和 JSON_VALUE
,可以简化嵌套路径的提取。
考虑极端情况下的 arxiv
数据集,我们将整个正文视为 String
。
要插入此模式,我们需要使用 JSONAsString
格式:
假设我们希望按年份统计发布的论文数量。对比使用字符串的以下查询与模式的 结构化版本:
请注意,这里使用 XPath 表达式来根据方法过滤 JSON,即 JSON_VALUE(body, '$.versions[0].created')
。
字符串函数明显比显式类型转换带索引慢 (> 10x)。上述查询始终需要完全扫描整个表并处理每一行。尽管在小数据集上这样的查询仍然会很快,但在更大型的数据集中,性能会降低。
这种方法的灵活性伴随着明显的性能和语法成本,仅应对高度动态的对象使用。
简单 JSON 函数
上述示例使用了 JSON* 函数系列。它们利用了基于 simdjson 的完整 JSON 解析器,该解析器在解析时很严格,并将处于不同级别的相同字段加以区分。这些函数能够处理语法正确但格式不佳的 JSON,例如键之间的双空格字符。
还有一组更快且更严格的函数。这些 simpleJSON*
函数通过对 JSON 的结构和格式做出严格假设,提供潜在的优越性能。具体要求如下:
- 字段名必须为常量
- 字段名称编码一致,例如
simpleJSONHas('{"abc":"def"}', 'abc') = 1
,而visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
- 字段名称在所有嵌套结构中是唯一的。对嵌套级别不做区分,匹配是无差别的。如果存在多个匹配字段,则使用第一个匹配的字段。
- 除了字符串文字外,不允许有特殊字符。这包括空格。以下是无效的,并且无法解析。
而下面的将正确解析:
上述查询使用 simpleJSONExtractString
提取 created
键,利用我们只需获取已发布日期的第一个值。在这种情况下,simpleJSON*
函数的限制对性能提升是可接受的。
使用 Map
如果对象用于存储任意键,且大多数为同一类型,则考虑使用 Map
类型。理想情况下,唯一键的数量不应超过几百个。Map
类型也可以用于具有子对象的对象,前提是后者在其类型上具有一致性。一般来说,我们建议将 Map
类型用于标签和标签,例如日志数据中的 Kubernetes pod 标签。
虽然 Map
提供了一种简单的方式来表示嵌套结构,但他们有一些显著的限制:
- 所有字段必须是同一类型。
- 访问子列需要特殊的 Map 语法,因为这些字段不存在为列。整个对象 是 一列。
- 访问子列会加载整个
Map
值,即所有兄弟和它们各自的值。对于较大的 Map,这可能会导致显著的性能惩罚。
在将对象建模为 Map
时,使用 String
键来存储 JSON 键名称。因此 Map 将始终是 Map(String, T)
,其中 T
取决于数据。
原始值
Map
的最简单应用是当对象包含相同的原始类型作为值时。在大多数情况下,这涉及将 String
类型用于值 T
。
考虑我们之前的 人员 JSON,其中 company.labels
对象被确定为动态。重要的是,我们只期望将类型为 String 的键值对添加到这个对象中。因此,我们可以将其声明为 Map(String, String)
:
我们可以插入我们原始的完整 JSON 对象:
在请求对象中查询这些字段需要使用 Map 语法,例如:
此时可以使用完整的 Map
函数查询,详细描述见 这里。如果您的数据没有一致的类型,则存在执行 必要的类型强制转换 的函数。
对象值
Map
类型也可以考虑用于具有子对象的对象,只要后者在其类型上保持一致。
假设我们 persons
对象的 tags
键需要一致的结构,其中每个 tag
的子对象具有 name
和 time
列。这样 JSON 文档的简化示例如下:
这可以建模为 Map(String, Tuple(name String, time DateTime))
,如下所示:
在这种情况下使用 Map 通常是罕见的,建议对数据进行重建,使动态键名不再有子对象。例如,上述内容可以重建为允许使用 Array(Tuple(key String, name String, time DateTime))
。
使用 Nested
Nested 类型 可用于建模静态对象,这些对象很少发生变化,提供了一种替代 Tuple
和 Array(Tuple)
的方案。我们一般建议避免将此类型用于 JSON,因为它的行为通常令人困惑。Nested
的主要优点是可以在排序键中使用子列。
下面,我们提供一个使用 Nested 类型建模静态对象的示例。考虑以下简单的 JSON 日志条目:
我们可以将 request
键声明为 Nested
。类似于 Tuple
,我们需要指定子列。
flatten_nested
设置 flatten_nested
控制嵌套的行为。
flatten_nested=1
值为 1
(默认值)不支持任意层级的嵌套。使用此值时,可以将嵌套数据结构视为多个 Array 列,且长度相同。字段 method
、path
和 version
实际上都是独立的 Array(Type)
列,并有一个关键约束:method
、path
和 version
字段的长度必须相同。 如果使用 SHOW CREATE TABLE
,将会得到如下示例:
下面,我们插入到此表中:
这里有几点重要事项:
- 我们需要使用
input_format_import_nested_json
设置将 JSON 作为嵌套结构插入。没有此设置,我们需要将 JSON 扁平化,即。
- 嵌套字段
method
、path
和version
需要作为 JSON 数组传递,即。
可以使用点表示法查询列:
请注意,对子列使用的 Array
意味着可以充分利用全套 Array 函数,包括 ARRAY JOIN
子句 - 如果您的列具有多个值,这将非常有用。
flatten_nested=0
这允许任意层级的嵌套,并意味着嵌套列保持为单个 Tuple
的数组 - 实际上它们变成了 Array(Tuple)
的相同形式。
这是使用 Nested
的首选方式,通常也是最简单的方式。如我们下面所示,它只要求所有对象都是一个列表。
下面,我们重新创建表并重新插入一行:
这里有几点重要事项:
- 不需要
input_format_import_nested_json
来插入。 SHOW CREATE TABLE
中保留了Nested
类型。该列的底层实质上是Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))
- 因此,我们需要将
request
作为数组插入,即。
可以再次使用点表示法查询列:
示例
上述数据的大型示例可在 s3 的公共桶中找到:s3://datasets-documentation/http/
。
考虑到 JSON 的约束和输入格式,我们使用以下查询插入此示例数据集。在这里,我们设置 flatten_nested=0
。
以下语句插入了 1000 万行,因此此过程可能需要几分钟来执行。如有需要,请应用 LIMIT
:
查询此数据需要我们将请求字段作为数组访问。下面,我们总结了固定时间段内的错误和 HTTP 方法。
使用成对数组
成对数组提供了一种在将 JSON 表示为字符串的灵活性和更结构化方法的性能之间的平衡。模式是灵活的,因为可以潜在地向根添加任何新字段。然而,这需要显著更复杂的查询语法,并且与嵌套结构不兼容。
例如,考虑以下表:
要插入此表,我们需要将 JSON 构建为键值对的列表。以下查询说明了如何使用 JSONExtractKeysAndValues
来实现这一点:
注意请求列仍然作为字符串表示的嵌套结构。我们可以向根添加任何新键。我们还可以在 JSON 本身中有任意差异。要插入我们的本地表,请执行以下操作:
查询此结构需要使用 indexOf
函数识别所需键的索引(该索引应与值的顺序一致)。这可以用于访问值数组列,即 values[indexOf(keys, 'status')]
。我们仍然需要一个 JSON 解析方法用于请求列 - 在这种情况下为 simpleJSONExtractString
。