0
0

Clickhouse JSON表字段方法详解（处理半结构化/结构化数据方法）

Clickhouse JSON表字段方法详解（处理半结构化/结构化数据方法）

文章目录

官网解释：

https://clickhouse.com/docs/en/guides/developer/working-with-json/json-semi-structured/

https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json

结构化方法

首先，利用官网给出的Github上的一个JSON数据集来演示，展示Clickhouse在处理JSON类型数据时面对的挑战。在下面的例子中，我们不依赖于模式推断来将JSON字段映射到列——相反，我们指定了JSONEachRow格式，并显式地将字段映射到s3函数中的列。

SELECTtype,`actor.display_login`,`repo.name`, created_at
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022-flat.ndjson.gz','JSONEachRow','type String, `actor.avatar_url` String, `actor.display_login` String, '||'`actor.id` Float64, `actor.login` String, `actor.url` String, `repo.id` Float64, '||'`repo.name` String, `repo.url` String, created_at String, `payload.pull_request.updated_at` String, '||'`payload.action` String, `payload.ref` String, `payload.ref_type` String, '||'`payload.pull_request.user.login` String, `payload.pull_request.number` Float64, '||'`payload.pull_request.title` String, `payload.pull_request.state` String, '||'`payload.pull_request.author_association` String, `payload.pull_request.head.ref` String, '||'`payload.pull_request.head.sha` String, `payload.pull_request.base.ref` String, '||'`payload.pull_request.base.sha` String, `payload.size` Float64, `payload.distinct_size` Float64')LIMIT10;

输出结果：

Query id: e288ce1f-c2c6-43c5-8e9c-dcd92a51b674

┌─type──────────────┬─actor.display_login──────┬─repo.name───────────────────────────────────┬─created_at───────────┐
│ PushEvent         │ Lakshmipatil2021         │ revacprogramming/pps-test1-Lakshmipatil2021 │ 2022-01-04T07:00:00Z │
│ MemberEvent       │ KStevenT                 │ KStevenT/HTML_ExternalWorkshop              │ 2022-01-04T07:00:00Z │
│ PushEvent         │ Soumojit28               │ Soumojit28/Oxytocin                         │ 2022-01-04T07:00:00Z │
│ PushEvent         │ github-actions           │ diogoaraujo017/diogoaraujo017               │ 2022-01-04T07:00:00Z │
│ PushEvent         │ Aman-Sonwani             │ Aman-Sonwani/crwn-clothing                  │ 2022-01-04T07:00:00Z │
│ PushEvent         │ huangshanyoumumingwutong │ huangshanyoumumingwutong/picgo              │ 2022-01-04T07:00:00Z │
│ PullRequestEvent  │ rfprod                   │ rfprod/nx-ng-starter                        │ 2022-01-04T07:00:00Z │
│ PushEvent         │ Helikopter-Bojowy        │ Helikopter-Bojowy/Exp-na-helikopterze       │ 2022-01-04T07:00:00Z │
│ IssueCommentEvent │ PRMerger-test-1          │ MicrosoftDocs/CSIDev-Public                 │ 2022-01-04T07:00:00Z │
│ PushEvent         │ github-actions           │ pioug/yield-data                            │ 2022-01-04T07:00:00Z │
└───────────────────┴──────────────────────────┴─────────────────────────────────────────────┴──────────────────────┘

10rowsinset. Elapsed: 48.180 sec.

请注意，此数据集是后面使用的示例的子集。在上述sql中，我们没有将JSON数据作为嵌套对象来处理，而是利用段分隔符（period separator）对得到的JSON对象进行了平铺，JSON中的每个键设置了字段与之对应。虽然嵌套对象可以通过显式映射来处理，但它需要使用新的JSON对象字段或(对于旧的ClickHouse版本)元组、映射和嵌套结构(参见其他方法)进一步复杂化使用。这也引出了后面Clickhouse对嵌套结构的进一步处理方法。

我们可以使用INSERT INTO SELECT语句将结果持久化到本地Merge Tree表中。但是上述这种方法需要映射所有字段，当JSON可能是动态的或未知时，这种方法有明显的局限性，这样的表需要用户知道所有字段并在下面详细地表达定义。

CREATEtable github_flat
(type                                      String,`actor.avatar_url`                        String,`actor.display_login`                     String,`actor.id`                                Float64,`actor.login`                             String,`actor.url`                               String,`repo.id`                                 Float64,`repo.name`                               String,`repo.url`                                String,
   created_at                                String,`payload.pull_request.updated_at`         String,`payload.action`                          String,`payload.ref`                             String,`payload.ref_type`                        String,`payload.pull_request.user.login`         String,`payload.pull_request.number`             Float64,`payload.pull_request.title`              String,`payload.pull_request.state`              String,`payload.pull_request.author_association` String,`payload.pull_request.head.ref`           String,`payload.pull_request.head.sha`           String,`payload.pull_request.base.ref`           String,`payload.pull_request.base.sha`           String,`payload.size`                            Float64,`payload.distinct_size`                   Float64
)ENGINE= MergeTree ORDERBY(type,`repo.name`, created_at);INSERTINTO github_flat SELECT*FROM s3 ('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022-flat.ndjson.gz','JSONEachRow');SELECTcount()from github_flat;

输出结果：

Query id: 0dee266f-8ebc-4dd0-95e2-2e390893f00b

┌───count─┐
│ 1000000 │
└─────────┘

1rowsinset. Elapsed: 0.003 sec.

此外，如果JSON中添加了新的属性，则需要更新表，即通过ALTER table进行更新。自然，这也引申出ClickHouse的半结构化功能。

半结构化方法

在上述完全基于结构化的方法中，对于JSON类型的嵌套数据，CK需要定义明确的表结构，将JSON字段与之一一对应。这种方式不利于JSON数据的动态扩展。因此，CK提供了半结构化的方法来处理。

概述

为了解决半结构化数据的挑战，ClickHouse提供了一个JSON对象类型。此特性仅在22.3.1以上版本中可用。它代表了将来处理任意JSON的首选机制。后面描述的替代方法(部分依赖于施加严格的模式)仍然有效，因为将JSON字段提取到专用列中允许利用主/排序键对其进行优化

JSON Object类型在处理复杂的嵌套结构时很有优势，这些结构可能会发生变化。类型在插入期间自动从结构中推断列，并将这些列合并到现有的表模式中。通过将JSON键及其值存储为列和动态子列，ClickHouse可以利用用于结构化数据的相同优化，从而提供类似的性能。还为用户提供了用于列选择的直观路径语法。此外，表可以包含具有灵活模式的JSON对象列和具有预定义类型的更严格的常规列。

值得注意的是，JSON类型主要在语法上增强了插入和查询时的JSON处理，也就是说，它仍然为列使用本地现有的ClickHouse类型，JSON对象使用Tuple类型表示。因此，以前手动模式处理是自动处理的，查询要简单得多。

CK的模式推断

ClickHouse可以自动确定几乎所有支持的输入格式的输入数据结构。当ClickHouse需要以特定的数据格式读取数据且结构未知时，就使用模式推断。

注意，ClickHouse(22.4.1+)的最新版本将推断JSONEachRow的模式。这种推断也适用于具有嵌套结构的JSON对象。这些将被推断为JSON对象字段。例如，执行一个DESCRIBE会显示检测到的文件模式，并推断文件中数据字段的模式，如下所示：

DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz','JSONEachRow') SETTINGS input_format_max_rows_to_read_for_schema_inference=100;

输出结果：

┌─name───────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type       │ Nullable(String)         │              │                    │         │                  │                │
│ actor      │ Object(Nullable('json')) │              │                    │         │                  │                │
│ repo       │ Object(Nullable('json')) │              │                    │         │                  │                │
│ created_at │ Nullable(String)         │              │                    │         │                  │                │
│ payload    │ Object('json')           │              │                    │         │                  │                │
└────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

5rowsinset. Elapsed: 1.203 sec.

github-2022.ndjson.gz文件中某行数据示例如下所示：

{"type": "PushEvent", "actor": {"avatar_url": "https://avatars.githubusercontent.com/u/93110249?", "display_login": "Lakshmipatil2021", "id": 93110249, "login": "Lakshmipatil2021", "url": "https://api.github.com/users/Lakshmipatil2021"}, "repo": {"id": 429298592, "name": "revacprogramming/pps-test1-Lakshmipatil2021", "url": "https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021"}, "created_at": "2022-01-04T07:00:00Z", "payload": {"pull_request": {"updated_at": "", "user": {"login": ""}, "number": 0, "title": "", "state": "", "author_association": "", "head": {"ref": "", "sha": ""}, "base": {"ref": "", "sha": ""}}, "action": "", "ref": "refs/heads/main", "ref_type": "", "size": 1, "distinct_size": 1}}

可以看出，CK利用

DESCRIBE

函数对文本中的数据进行了模式推断。从文本中的实例数据可以发现，这种推断是正确的。

注：如果上面的sql执行失败，可以从以下几种原因分析：

  1. 报错DB::Exception: The signature of table function s3 could be the following:...检查Clickhouse server version。需要满足22.4.1+，才能对JSONEachRow的模式进行推断
  2. 报错DB::Exception: Cannot extract table structure from JSONEachRow format file. Error: Code: 652. DB::Exception: Cannot determine typeforcolumn payload byfirst100rowsofdata, most likely this columncontains only Nulls or empty Arrays/Maps. You can specify the typefor this columnusing setting schema_inference_hints.(ONLY_NULLS_WHILE_READING_SCHEMA)payload类型推断出了问题，需要利用schema_inference_hints手动设置类型，在上述sql后添加手动设置payload类型如下：DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz','JSONEachRow') SETTINGS input_format_max_rows_to_read_for_schema_inference=100, schema_inference_hints='payload Object(\'json\')';

注意设置

input_format_max_rows_to_read_for_schema_inference

，这决定了用于推断模式的行数。在这种情况下，可以在默认的100行内推断模式，如果前100行包含空值的列，则需要设置更高的值。这种模式推断简化了SELECT语句。尝试执行以下命令，看看actor列和repo列是如何作为JSON返回的。

SELECTtype, actor, repo FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz','JSONEachRow')LIMIT2;

模式推断和JSON对象类型的引入使我们能够优雅地处理嵌套数据，并避免冗长的定义（像文初结构化处理那样，需要把JSON中每个字段都列出来）。但是，我们需要将整个行视为根上的动态属性的JSON对象。ClickHouse的22.4版引入了JSONAsObject格式来帮助实现这一点。

JSON对象类型

使用与上面相同的数据集，我们通过JSONAsObject格式显式声明每行是一个单独的对象。这个单一对象被映射到object (JSON)类型的字段中。下面的sql显示地定义了一个JSON类型的event字段。注意，如果我们没有在s3函数中显式地指定event作为字段名，则将使用一个字段json:

SELECT*FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz','JSONAsObject','event JSON')LIMIT1;

event{“type”:“PushEvent”,“actor.avatar_url”:“https://avatars.githubusercontent.com/u/93110249?”,“actor.display_login”:“Lakshmipatil2021”,“actor.id”:93110249,“actor.login”:“Lakshmipatil2021”,“actor.url”:“https://api.github.com/users/Lakshmipatil2021”,“repo.id”:429298592,“repo.name”:“revacprogramming/pps-test1-Lakshmipatil2021”,“repo.url”:“https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021”,“created_at”:“2022-01-04T07:00:00Z”,“payload.pull_request.updated_at”:“”,“payload.pull_request.user.login”:“”,“payload.pull_request.number”:0,“payload.pull_request.title”:“”,“payload.pull_request.state”:“”,“payload.pull_request.author_association”:“”,“payload.pull_request.head.ref”:“”,“payload.pull_request.head.sha”:“”,“payload.pull_request.base.ref”:“”,“payload.pull_request.base.sha”:“”,“payload.action”:“”,“payload.ref”:“refs/heads/main”,“payload.ref_type”:“”,“payload.size”:1,“payload.distinct_size”:1}
为了有效地查询这些数据，我们目前需要将其存储到一个MergeTree中，因此，我们创建一个表并使用insert INTO SELECT插入行。

首先，在插入行之前创建表：

注意，使用allow_experimental_object_type作为JSON对象类型仍然是一个实验特性。

SET allow_experimental_object_type=1;CREATEtable github_json(event JSON)ENGINE= MergeTree ORDERBY tuple()INSERTINTO github_json SELECT*FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', JSONAsObject,'event JSON');

确认表模式和行数为1 Million。

SELECTcount()FROM github_json;

Query id: 2fc6e7a6-428a-4605-b3f2-ce55e4d6f7b0

┌─count()─┐
│ 1000000 │
└─────────┘

1rowsinset. Elapsed: 0.003 sec.DESCRIBETABLE github_json

Query id: 97766169-fe9a-468e-b636-3e78de16254f

┌─name──┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ event │ Object('json') │              │                    │         │                  │                │
└───────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

1rowsinset. Elapsed: 0.002 sec.

虽然上面确认了每一行都被视为JSON对象，但它没有提供关于JSON中的字段是如何映射列的信息。为此，我们可以利用设置descripbe_extend_object_types。

DESCRIBETABLE github_json
SETTINGS describe_extend_object_types =1

Query id: 6105c51e-0351-4bda-9dda-137eebff01d8

┌─name──┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ event │ Tuple(actor Tuple(avatar_url String, display_login String, id Int32, login String, url String), created_at String, payload Tuple(action String, distinct_size Int32, pull_request Tuple(author_association String, base Tuple(ref String, sha String), head Tuple(ref String, sha String), number Int32, state String, title String, updated_at String,user Tuple(login String)), ref String, ref_type String, size Int16), repo Tuple(id Int32, name String, url String),type String) │              │                    │         │                  │                │
└───────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

1rowsinset. Elapsed: 0.002 sec.

这个映射中最有趣的组件是对嵌套JSON的处理。注意下面的JSON结构是如何映射到

repo Tuple(id Int32, name String, url String)

:

"repo":{"id":429298592,"name":"revacprogramming/pps-test1-Lakshmipatil2021","url":"https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021"}

可以看出，对于实际的一个JSON类型嵌套数据来说，CK是用Tuple的嵌套来处理的。利用上文提到的模式推断，CK将JSON中的每个键和类型组合成Tuple中的一个元素进行存储。

接下来，我们利用这些动态创建的列来执行查询。

查询动态的子列

对于上表的JSON类型的event字段进行查询方式，这种JSON类型CK采用Tuple嵌套来存储的：

SELECT event.type, event.repo, event.actor FROM github_json LIMIT1;

输出结果：

SELECT
    event.type,
    event.repo,
    event.actor
FROM github_json
LIMIT1

Query id: 10d8b242-22ae-4f9d-a4b0-8f9966552820

┌─event.type─┬─event.repo───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─event.actor──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ PushEvent  │ (429298592,'revacprogramming/pps-test1-Lakshmipatil2021','https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021') │ ('https://avatars.githubusercontent.com/u/93110249?','Lakshmipatil2021',93110249,'Lakshmipatil2021','https://api.github.com/users/Lakshmipatil2021') │
└────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1rowsinset. Elapsed: 0.036 sec.

要返回原始结构，我们需要JSONEachRow格式和参数output_format_json_named_tuples_as_objects：

SELECT event.type, event.repo, event.actor FROM github_json LIMIT1 
FORMAT JSONEachRow SETTINGS output_format_json_named_tuples_as_objects=1;

输出结果：

SELECT
    event.type,
    event.repo,
    event.actor
FROM github_json
LIMIT1
FORMAT JSONEachRow
SETTINGS output_format_json_named_tuples_as_objects =1

Query id: 06092a0f-7db3-4cbc-ad60-daa002365aba

{"event.type":"PushEvent","event.repo":{"id":429298592,"name":"revacprogramming\/pps-test1-Lakshmipatil2021","url":"https:\/\/api.github.com\/repos\/revacprogramming\/pps-test1-Lakshmipatil2021"},"event.actor":{"avatar_url":"https:\/\/avatars.githubusercontent.com\/u\/93110249?","display_login":"Lakshmipatil2021","id":93110249,"login":"Lakshmipatil2021","url":"https:\/\/api.github.com\/users\/Lakshmipatil2021"}}

1rowsinset. Elapsed: 0.063 sec.

上面的简化示例说明了使用JSON Object类型的机制。同时，用户可以使用与CK中其他类型相同的过滤器和聚合功能来查询这些基于json的列，比如下例所示：

查询：按starts数计算Top 5的repositories

SELECT event.repo.name,count()AS stars FROM github_json WHERE event.type='WatchEvent'GROUPBY event.repo.name ORDERBY stars DESCLIMIT5;

输出结果：

SELECT
    event.repo.name,count()AS stars
FROM github_json
WHERE event.type='WatchEvent'GROUPBY event.repo.name
ORDERBY stars DESCLIMIT5

Query id: 3724b07a-24a3-440c-80fd-056bd5b398e2

┌─event.repo.name─────────────┬─stars─┐
│ dwmkerr/hacker-laws         │   283 │
│ tkellogg/dura               │   200 │
│ aplus-framework/app         │   157 │
│ seemoo-lab/opendrop         │   111 │
│ heroku-python/flask-sockets │    92 │
└─────────────────────────────┴───────┘

5rowsinset. Elapsed: 0.045 sec. Processed 1.00 million rows,53.40 MB (22.35 million rows/s.,1.19 GB/s.)

更复杂的查询：显示随时间变化的最多stars数的repositories列表。

我们调整查询，因为它涵盖了一个较短的时间段(3天)。另外，请注意需要解析事件。created_at字段，使用parsedatetimebestefort函数，因为它已被推断为字符串。

SELECT
   repo AS name,
   groupArrayInsertAt(toUInt32(c), toUInt64(dateDiff('hour', toDate('2022-01-01'),hour)))ASdataFROM(SELECT
       lower(event.repo.name)AS repo,
       toStartOfHour(parseDateTimeBestEffort(event.created_at))AShour,count()AS c
   FROM github_json
   WHERE(event.type='WatchEvent')AND(toYear(parseDateTimeBestEffort(event.created_at))>=2022)AND(repo IN(SELECT lower(event.repo.name)AS repo
       FROM github_json
       WHERE(event.type='WatchEvent')AND(toYear(parseDateTimeBestEffort(event.created_at))>=2022)GROUPBY event.repo.name
       ORDERBYcount()DESCLIMIT10))GROUPBY
       repo,hour)GROUPBY repo
ORDERBY repo ASC;

添加主键

上面的例子是不现实的，因为它没有主键或排序键，也就是说，它使用

tuple()

。这就否定了ClickHouse中索引功能的好处。要添加一个主键，并且仍然利用JSON对象的功能，我们建议为JSON使用一个专用的子键。 为克服无法将 JSON 子列用作为主键这种限制，我们建议用户对可能发生变化的行的半结构化部分使用 JSON 类型，但为那些可以声明可靠结构和类型的部分明确指定列。这需要使用 JSONEachRow格式而不是JSONAsObject插入数据。例如，考虑下面的JSON和相应的表定义和插入语句。

SET allow_experimental_object_type=1;DROPTABLEIFEXISTS github_json;CREATEtable github_json
(
   event_type Enum('CommitCommentEvent'=1,'CreateEvent'=2,'DeleteEvent'=3,'ForkEvent'=4,'GollumEvent'=5,'IssueCommentEvent'=6,'IssuesEvent'=7,'MemberEvent'=8,'PublicEvent'=9,'PullRequestEvent'=10,'PullRequestReviewCommentEvent'=11,'PushEvent'=12,'ReleaseEvent'=13,'SponsorshipEvent'=14,'WatchEvent'=15,'GistEvent'=16,'FollowEvent'=17,'DownloadEvent'=18,'PullRequestReviewEvent'=19,'ForkApplyEvent'=20,'Event'=21,'TeamAddEvent'=22),
    repo_name LowCardinality(String),
    event      JSON
)ENGINE= MergeTree ORDERBY(event_type, repo_name);

插入数据时需要使用JSONEachRow格式。注意事件子字段现在是如何保存动态JSON的，而根键是显式定义的。

INSERTINTO github_json FORMAT JSONEachRow
{"event":{"type":"PushEvent","actor":{"avatar_url":"https://avatars.githubusercontent.com/u/41898282?","display_login":"github-actions","gravatar_id":"","id":41898282,"login":"github-actions[bot]","url":"https://api.github.com/users/github-actions[bot]"},"repo":{"id":410071248,"name":"pioug/yield-data","url":"https://api.github.com/repos/pioug/yield-data"}},"event_type":"PushEvent","repo_name":"pioug/yield-data"}

这种方式将JSON数据中的某几个字段单独列出来作为排序字段，同时在插入时将这几个列的数据从原始数据中单独再分离出来。可以看出，这种方式需要重新构造JSON，这是最不方便的。理想情况下，我们需要一种更灵活的方法，允许我们随时修改希望提取为根键的字段，而不需要更改原始数据。

将我们的行作为字符串插入到EPHEMERAL列message_raw中，我们可以使用根字段的DEFAULT表达式提取感兴趣的特定字段。String EPHEMERAL列也被映射到提供通常灵活性的JSON对象列消息。这个EPHEMERAL列将不会被持久化，并将在INSERT时被丢弃。结果，我们的主键字段被复制了，即它们出现在文档的根，以及消息JSON中。

DROPTABLEIFEXISTS github_json;SET allow_experimental_object_type =1;CREATEtable github_json
(
   event_type LowCardinality(String)DEFAULT JSONExtractString(message_raw,'type'),// 从JSON数据中抽取出type字段
   repo_name LowCardinality(String)DEFAULT JSONExtractString(message_raw,'repo.name'),// 从JSON数据中抽取出repo.name字段
   message JSON DEFAULT message_raw,// 保留原始JSON数据
   message_raw String EPHEMERAL
)ENGINE= MergeTree ORDERBY(event_type, repo_name);

因此插入需要一个修改过的结构——注意JSON是如何在message_raw中被解析为字符串的。

INSERTINTO github_json (message_raw) FORMAT JSONEachRow {"message_raw": "{\"type\":\"PushEvent\", 
\"created_at\": \"2022-01-04 07:00:00\", \"actor\":{\"avatar_url\":\"https://avatars.githubusercontent.com/u/41898282?\",
\"display_login\":\"github-actions\",\"gravatar_id\":\"\",\"id\":41898282,\"login\":\"github-actions[bot]\",
\"url\":\"https://api.github.com/users/github-actions[bot]\"},\"repo\":{\"id\":410071248,\"name\":\"pioug/yield-data\",
\"url\":\"https://api.github.com/repos/pioug/yield-data\"}}"}

此时，这里message_raw对应的值是一个字符串了，而不是之前event的JSON格式数据

局限和最佳实践

JSON对象中的动态列与快速预定义类型一样。灵活的模式是一个非常强大的特性，语法开销很小，非常适合处理诸如日志之类的数据——其中键经常通过动态属性(如Kubernetes中的容器标签)添加。

解析JSON和模式推断在插入时确实会产生成本。因此，我们建议将列计数保持在10k以下。

在如何使用动态列方面也有一些限制。如前所述，它们不能用作主键或排序键。此外，它们不能配置为使用特定的编解码器。为了获得最佳性能，我们建议将JSON对象类型用于JSON的特定子键，并显式声明根键。这允许它们配置为特定的编解码器或用于排序/主键。如添加主键中所示，这需要使用 JSONEachRow格式，而不是使用JSONAsObject格式将整行作为JSON插入。

处理数据变更

添加列

处理半结构化数据需要ClickHouse在添加新列或改变其类型时适应新列。下面我们将探讨其中一些行为。

考虑下面这个简单的例子:

{
  "type": "PushEvent","actor": {
    "id": 93110249
  },"repo": {
    "id": 429298592,"name": "revacprogramming/pps-test1-Lakshmipatil2021","url": "https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021"
  }
}

创建一个表来接收这些数据并执行插入操作非常简单。

SET allow_experimental_object_type=1;CREATEtable github_tmp (event JSON)ENGINE= MergeTree ORDERBY tuple();INSERTINTO github_tmp FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":93110249},"repo":{"id":429298592,"name":"revacprogramming/pps-test1-Lakshmipatil2021","url":"https://api.github.com/repos/revacprogramming/pps-test1-Lakshmipatil2021"}}

检查类型，可以看到创建的列:

SET describe_extend_object_types=1;DESCRIBE github_tmp;

Tuple(actor Tuple(id Int32), repo Tuple(id Int32, name String, url String),type String)

假设现在插入下面的对象。这将为actor对象添加额外的字段:

{
    "type": "PushEvent","actor": {
      "avatar_url": "https://avatars.githubusercontent.com/u/81258380?","display_login": "Helikopter-Bojowy","gravatar_id": "","id": 81258380,"login": "Helikopter-Bojowy","url": "https://api.github.com/users/Helikopter-Bojowy"
    },"repo": {
      "id": 352069365,"name": "Helikopter-Bojowy/Exp-na-helikopterze","url": "https://api.github.com/repos/Helikopter-Bojowy/Exp-na-helikopterze"
    }
}

INSERTINTO github_tmp FORMAT JSONAsObject
{"type":"PushEvent","actor":{"avatar_url":"https://avatars.githubusercontent.com/u/81258380?","display_login":"Helikopter-Bojowy","gravatar_id":"","id":81258380,"login":"Helikopter-Bojowy","url":"https://api.github.com/users/Helikopter-Bojowy"},"repo":{"id":352069365,"name":"Helikopter-Bojowy/Exp-na-helikopterze","url":"https://api.github.com/repos/Helikopter-Bojowy/Exp-na-helikopterze"}}

如果我们检查schema，我们可以看到列已经被自动推断和添加:

SET describe_extend_object_types=1;DESCRIBE github_tmp;

Tuple(actor Tuple(avatar_url String, display_login String, gravatar_id String, 
id Int32, login String, url String), repo Tuple(id Int32, name String, url String),type String)

修改列

尽管尽了最大努力，JSON的类型经常不一致。虽然有些数据存储(如Kafka)可以在JSON上强制执行模式，但通常不会强制执行。因此，ClickHouse可以以多种类型接收相同的字段。这通常需要统一类型。考虑下面的例子:

{"type":"PushEvent","actor":{"id":10}}

这里的actor.Id为整数。如果插入到表中，它将被映射到一个Int8，如下所示:

SET allow_experimental_object_type=1;CREATEtable github_types ( event JSON )ENGINE= MergeTree ORDERBY tuple();INSERTINTO github_types FORMAT JSONAsObject {"type":"PushEvent","actor":{"id":10}}

SET describe_extend_object_types=1;DESCRIBE github_types;

Tuple(actor Tuple(id Int8),type String)

假设插入一个更大的整数：

INSERTINTO github_types FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":93110249}}

可以看到，CK进行类型推断，id字段现在表示为Int32。

SET describe_extend_object_types=1;DESCRIBE github_types;

Tuple(actor Tuple(id Int32),type String)

若将id改为String类型：

INSERTINTO github_types FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":"81258380"}}

SET describe_extend_object_types=1;DESCRIBE github_types;

Tuple(actor Tuple(id String),type String)

如下所示，ClickHouse现在将列actor.Id标识为字符串。

这种强制适用于大多数具有变量表示的类型，例如Int, Float。如果有必要，ClickHouse将统一为更高的位类型，允许表示所有当前值。如果需要，转换为String表示最不精确的定义。

警告:如果你依赖于特定类型的函数，例如数字的sum，类型的改变会破坏查询。我们建议您尽可能确保您的数据是一致的，并依赖此功能作为备份与最佳实践。

注意，并不是所有类型都可以统一。在插入任何前面的数据后，尝试以下操作将导致错误:

INSERTINTO github_types FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":["92258380"]}}

相反的情况也会失败，例如，如果第一行id是一个数组(字符串)，后续的行只是一个字符串。同样，对象(表示为元组)不能与标量类型(如String)统一。然而，这些内容是可以被强制的。例如，考虑下面的actor.id首先是数组(Int8)，然后是数组(String)。

DROPTABLE github_types;SET allow_experimental_object_type=1;CREATEtable github_types ( event JSON )ENGINE= MergeTree ORDERBY tuple();INSERTINTO github_types FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":[10]}}

SET describe_extend_object_types=1;DESCRIBE github_types;

Tuple(actor Tuple(id Array(Int8)),type String)INSERTINTO github_types FORMAT JSONAsObject
{"type":"PushEvent","actor":{"id":["92258380"]}}

SET describe_extend_object_types=1;DESCRIBE github_types;

Tuple(actor Tuple(id Array(String)),type String)
标签： json clickhouse github
本文转载自: https://blog.csdn.net/Urbanears/article/details/128938666
版权归原作者 JermeryBesian 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Clickhouse JSON表字段方法详解（处理半结构化/结构化数据方法）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航