0
62
0
0
首页/专栏/ 技术分享/ 查看内容

ClickHouse 官方文档：处理 JSON 数据 （1/2）

 admin   发表于  2025-1-13 17:29
专栏 技术分享


概述

ClickHouse 提供了多种处理 JSON 的方法，每种方法都有其优缺点和适用场景。在本指南中，我们将介绍如何加载 JSON 并优化架构设计。本指南包括以下内容：

  • 加载 JSON：在 ClickHouse 中使用简单架构加载和查询 JSON（尤其是 NDJSON）。

  • JSON 架构推断：通过 JSON 架构推断查询 JSON 并生成表结构。

  • 设计 JSON 架构：设计并优化 JSON 架构的步骤。

  • 导出 JSON：JSON 的导出方法。

  • 处理其他 JSON 格式：处理非 NDJSON 格式 JSON 的技巧。

  • 其他 JSON 建模方法：高级 JSON 建模方法（不推荐使用）。

重要：全新 JSON 类型已进入 Beta 阶段  

本指南涵盖现有的 JSON 处理技术。值得注意的是，ClickHouse 已推出一种新的 JSON 类型，目前处于 Beta 阶段。详情请参见这里。【https://clickhouse.com/docs/en/sql-reference/data-types/newjson】


加载 JSON 数据  

本节假设 JSON 数据采用 NDJSON（换行分隔的 JSON，Newline delimited JSON）格式，在 ClickHouse 中称为 JSONEachRow。这种格式因其简洁性和高效的空间利用率而成为加载 JSON 的首选，但 ClickHouse 同样支持其他格式的输入和输出。

以下示例展示了一行来自 Python PyPI 数据集的 JSON 数据：

{  "date": "2022-11-15",  "country_code": "ES",  "project": "clickhouse-connect",  "type": "bdist_wheel",  "installer": "pip",  "python_minor": "3.9",  "system": "Linux",  "version": "0.3.0"}

要将该 JSON 对象加载到 ClickHouse 中，需要先定义表架构。以下是一个简单的架构示例，其中 JSON 的键被映射为表的列名：

CREATE TABLE pypi (  `date` Date,  `country_code` String,  `project` String,  `type` String,  `installer` String,  `python_minor` String,  `system` String,  `version` String)ENGINE = MergeTreeORDER BY (project, date)

排序键  

我们使用 ORDER BY 子句定义了一个排序键。有关排序键的详细信息以及如何选择，请参考相关文档。【https://clickhouse.com/docs/en/data-modeling/schema-design#choosing-an-ordering-key】

ClickHouse 支持以多种格式加载 JSON 数据，并能根据文件扩展名和内容自动推断其类型。以下示例中，我们通过 S3 函数读取了与上述表对应的 JSON 文件：

SELECT *FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')LIMIT 1┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘
1 row in set. Elapsed: 1.232 sec.

注意，这里无需显式指定文件格式。我们通过通配模式读取桶中的所有 *.json.gz 文件，ClickHouse 会根据文件扩展名和内容自动识别格式为 JSONEachRow（ndjson）。如果格式无法自动识别，可以通过参数函数手动指定。

SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)

压缩文件 

上述文件均已压缩，ClickHouse 能够自动检测并处理这些压缩文件。


要加载这些文件中的数据行，可以使用 INSERT INTO SELECT：

INSERT INTO pypi SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')Ok.
0 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)
SELECT * FROM pypi LIMIT 2
┌───────date─┬─country_code─┬─project────────────┐│ 2022-05-26 │ CN           │ clickhouse-connect ││ 2022-05-26 │ CN           │ clickhouse-connect │└────────────┴──────────────┴────────────────────┘
2 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 908.03 KB (1.63 million rows/s., 180.38 MB/s.)

数据行也可以通过 FORMAT 子句直接加载，例如：

INSERT INTO pypiFORMAT JSONEachRow{"date":"2022-11-15","country_code":"CN","project":"clickhouse-connect","type":"bdist_wheel","installer":"bandersnatch","python_minor":"","system":"","version":"0.2.8"}

以上示例假设使用 JSONEachRow 格式。ClickHouse 还支持其他常见的 JSON 格式，其加载方法请参考相关示例。【https://clickhouse.com/docs/en/integrations/data-formats/json/other-formats】

上述内容展示了加载 JSON 数据的基础方法。对于更复杂的 JSON 数据结构（如嵌套结构），请参考“设计 JSON 架构”指南。【https://clickhouse.com/docs/en/integrations/data-formats/json/schema】


JSON 架构自动推断  

ClickHouse 支持自动推断 JSON 数据的结构。这一功能允许直接查询 JSON 数据，例如使用 clickhouse-local 查询磁盘数据，或查询存储在 S3 存储桶中的数据。此外，还可以在数据加载到 ClickHouse 之前自动生成表架构。


适用场景


  • 结构一致：用于类型推断的数据需包含所有目标列。如果推断完成后数据增加了额外的列，这些列可能无法被查询。

  • 类型一致：特定列的类型需要相互兼容。

注意事项 

如果 JSON 数据具有动态结构，例如频繁新增键但未能及时更新架构（如日志中的 Kubernetes 标签），建议参考“设计 JSON 架构”指南。


类型检测



在之前的示例中，我们使用了 NDJSON 格式的 Python PyPI 数据集的简单版本。本节将探索一个更复杂的数据集——arXiv 数据集。该数据集包含约 250 万篇学术论文，以 NDJSON 格式分发，每一行代表一篇已发表的学术论文。以下为其中一行示例：

{  "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",      ""    ]  ]}

该数据集需要更复杂的架构设计。以下将概述定义此架构的过程，并介绍如 Tuple 和 Array 等复杂类型。

数据集存储于公共 S3 存储桶，路径为 s3://datasets-documentation/arxiv/arxiv.json.gz。

如示例所示，该数据集包含嵌套的 JSON 对象。尽管用户通常需要设计并版本化架构，但通过类型推断功能，可直接从数据中推断出类型。此功能允许自动生成架构的 DDL，避免手动创建架构，加速开发流程。

自动格式检测

除了自动推断架构，JSON 架构推断还会根据文件扩展名和内容检测数据格式。上述文件会被系统自动识别为 NDJSON 格式。


通过结合 s3 函数与 DESCRIBE 命令，可查看推断出的数据类型。

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')SETTINGS describe_compact_output = 1
┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐│ id             │ Nullable(String)                                                        ││ submitter      │ Nullable(String)                                                        ││ authors        │ Nullable(String)                                                        ││ title          │ Nullable(String)                                                        ││ comments       │ Nullable(String)                                                        ││ journal-ref    │ Nullable(String)                                                        ││ doi            │ Nullable(String)                                                        ││ report-no      │ Nullable(String)                                                        ││ categories     │ Nullable(String)                                                        ││ license        │ Nullable(String)                                                        ││ abstract       │ Nullable(String)                                                        ││ versions       │ Array(Tuple(created Nullable(String),version Nullable(String)))         ││ update_date    │ Nullable(Date)                                                          ││ authors_parsed │ Array(Array(Nullable(String)))                                          │└────────────────┴─────────────────────────────────────────────────────────────────────────┘

避免空值

推断结果显示，许多列被标记为 Nullable 类型。我们不建议在非必要情况下使用 Nullable 类型。您可以通过 schema_inference_make_columns_nullable 参数控制 Nullable 类型的应用行为。


系统会自动将大多数列检测为 String 类型，而 update_date 列则正确识别为 Date 类型。versions 列被定义为 Array(Tuple(created String, version String))，用于存储对象列表；authors_parsed 列则为 Array(Array(String))，表示嵌套数组。

控制类型检测  

日期和时间的自动检测可通过参数 input_format_try_infer_dates 和 input_format_try_infer_datetimes 分别进行控制（两者默认启用）。对象被推断为 Tuple 的行为由参数 input_format_json_try_infer_named_tuples_from_objects 控制。其他与 JSON 架构推断相关的设置，例如数字类型的自动检测，可参考相关文档。



通过架构推断查询 JSON 数据

  

通过架构推断，我们可以直接查询 JSON 数据。以下示例展示了如何利用自动检测的日期和数组字段，统计每年的主要作者。

SELECT    toYear(update_date) AS year,    authors,    count() AS cFROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')GROUP BY    year,    authorsORDER BY    year ASC,    c DESCLIMIT 1 BY year
┌─year─┬─authors────────────────────────────────────┬───c─┐│ 2007 │ The BABAR Collaboration, B. Aubert, et al  │  98 ││ 2008 │ The OPAL collaboration, G. Abbiendi, et al │  59 ││ 2009 │ Ashoke Sen                                 │  77 ││ 2010 │ The BABAR Collaboration, B. Aubert, et al  │ 117 ││ 2011 │ Amelia Carolina Sparavigna                 │  21 ││ 2012 │ ZEUS Collaboration                         │ 140 ││ 2013 │ CMS Collaboration                          │ 125 ││ 2014 │ CMS Collaboration                          │  87 ││ 2015 │ ATLAS Collaboration                        │ 118 ││ 2016 │ ATLAS Collaboration                        │ 126 ││ 2017 │ CMS Collaboration                          │ 122 ││ 2018 │ CMS Collaboration                          │ 138 ││ 2019 │ CMS Collaboration                          │ 113 ││ 2020 │ CMS Collaboration                          │  94 ││ 2021 │ CMS Collaboration                          │  69 ││ 2022 │ CMS Collaboration                          │  62 ││ 2023 │ ATLAS Collaboration                        │ 128 ││ 2024 │ ATLAS Collaboration                        │ 120 │└──────┴────────────────────────────────────────────┴─────┘
18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)

架构推断使我们无需预先定义架构即可查询 JSON 文件，从而加快临时数据分析的过程。


创建表 

 

通过架构推断，可以自动生成表的架构。以下 CREATE AS EMPTY 命令将推断表的 DDL 并创建表，但不会加载任何数据：

CREATE TABLE arxivENGINE = MergeTreeORDER BY update_date EMPTYAS SELECT *FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')SETTINGS schema_inference_make_columns_nullable = 0

要确认生成的表架构，可以使用 SHOW CREATE TABLE 命令：

SHOW CREATE TABLE 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)))ENGINE = MergeTreeORDER BY update_dateSETTINGS index_granularity = 8192

以上架构正确匹配数据。架构推断是通过对数据进行采样并逐行读取实现的。列值根据文件格式提取，递归解析器与启发式算法共同确定每个值的类型。在架构推断过程中，读取的最大行数和字节数分别由参数 input_format_max_rows_to_read_for_schema_inference（默认值为 25000）和 input_format_max_bytes_to_read_for_schema_inference（默认值为 32MB）控制。如果推断结果不准确，用户可以参考相关文档提供必要的提示。

从片段创建表  

在上述示例中，我们使用了 S3 文件来创建表架构。用户也可以从单行 JSON 数据片段生成表架构。以下为使用 format 函数实现的示例：

CREATE TABLE arxivENGINE = MergeTreeORDER BY update_date EMPTYAS SELECT *FROM format(JSONEachRow, '{"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","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","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",""]]}') SETTINGS schema_inference_make_columns_nullable = 0
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv(    `id` String,    
              
              

                  





路过




雷人




握手




鲜花




鸡蛋







              

                            

                          

收藏
邀请


            

上一篇：ClickHouse 官方文档：处理 JSON 数据 （2/2）下一篇：使用 ClickHouse 实现 Medallion 架构

          
        
      
      
版权声明：本文为 clickhouse 社区用户原创文章，遵循 CC BY-NC-SA 4.0 版权协议，转载请附上原文出处链接和本声明。

      

        

          
评论

          

            

              

              
              

              

                

                    

                 




                    
                    
                    
                  

                    

                      
                    

                    

                      
                    

                  

                  
		



                  

                                

              

            

          

          

            

              

                
    
                                

              

            

          

        

      

    
    

     相关阅读
          相关分类
        版本发布
        客户案例
        技术分享
        meetup活动回顾
        视频分享