ClickHouse （十五）解析json数据_clickhouse json_高并发的博客

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

憨厚的圣诞树 · 如何通过MySQL命令行连接到PolarDB ...· 10 月前 ·

细心的乒乓球 · vue拖拽@drop不生效解决方式_haos ...· 1 年前 ·

微醺的大象 · 1. ...· 1 年前 ·

活泼的打火机 · 为什么安卓应用卸载的时候不会全部删除相关文件 ...· 1 年前 ·

使用字符串行式保存在ClickHouse的json数据，需要我们解析提取相关字段，将json行转多列。
如提取json数据的各个字段的值，以方便查询。

'[{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}]' AS new, 'S123' AS num SELECT ┌─new────────────────────────────────────────────────────────────────────────────────────────┬─num──┐ │ [{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}] │ S123 │ └────────────────────────────────────────────────────────────────────────────────────────────┴──────┘

ClickHouse提供了JSON函数，方便我们操作json数据：

visitParamExtractBool（json，name） → 提取json中的name字段，返回UInt8，0或1。
visitParamExtractInt（json，name） →提取json中的name字段，返回Int型的值。
visitParamExtractFloat （json，name）→ 提取json中的name字段，返回Float型的值。
visitParamExtractString （json，name）→提取json中的name字段，返回String型的值。
visitParamExtractRaw （json，name）→ 提取json中的name字段，返回字段的值，包含空格符。

SELECT 
    visitParamExtractBool('{"name":true}', 'name') AS bool, 
    visitParamExtractInt('{"name":123}', 'name') AS int, 
    visitParamExtractFloat('{"name":0.1}', 'name') AS float, 
    visitParamExtractString('{"name":"你好"}', 'name') AS str, 
    visitParamExtractRaw('{"name":"你好"}', 'name') AS raw
┌─bool─┬─int─┬─float─┬─str──┬─raw────┐
│    1 │ 123 │   0.1 │ 你好 │ "你好" │
└──────┴─────┴───────┴──────┴────────┘
测试，解析json数组
 
使用JSONExtractArrayRaw()函数，将字符串转化为json数组： 
SELECT 
    visitParamExtractString(json, 'name') AS name, 
    visitParamExtractInt(json, 'tall') AS tall, 
    visitParamExtractString(json, 'model') AS model, 
        '[{"name":"天台","tall":100,"model":"M779011"},      {"name":"楼顶","tall":90,"model":"M669011"},      {"name":"秀儿","tall":80,"model":"M559011"}]' AS new, 
        'S123' AS num
    SELECT 
        JSONExtractArrayRaw(new) AS arr, 
        arrayJoin(arr) AS json
┌─name─┬─tall─┬─model───┬─num──┐
│ 天台 │  100 │ M779011 │ S123 │
│ 楼顶 │   90 │ M669011 │ S123 │
│ 秀儿 │   80 │ M559011 │ S123 │
└──────┴──────┴─────────┴──────┘
还可以使用字符截取： 
    '[{"name":"天台","tall":100,"model":"M779011"},    
     {"name":"楼顶","tall":90,"model":"M669011"},  
     {"name":"秀儿","tall":80,"model":"M559011"}]' AS new, 
    replaceAll(replaceAll(new, '[', ''), ']', '') AS out, 
    concat(arrayJoin(splitByString('},', out)), '}') AS json, 
    'S123' AS num
SELECT 
    visitParamExtractString(json, 'name') AS name, 
    visitParamExtractInt(json, 'tall') AS tall, 
    visitParamExtractString(json, 'model') AS model, 
┌─name─┬─tall─┬─model───┬─num──┐
│ 天台 │  100 │ M779011 │ S123 │
│ 楼顶 │   90 │ M669011 │ S123 │
│ 秀儿 │   80 │ M559011 │ S123 │
└──────┴──────┴─────────┴──────┘
                                    基础数据准备
    '[{"name":"xiaoming","age":18,"sex":"male"},{"name":"xiaohong","age":20,"model":"female"}]' AS people
SELECT 
    people
visitParamExtractBool（json，name） → 提取json中的name字段，返回UInt8，0或1。
visitParamExtractInt（json，name） →提取json中的name字段，返
                                    文章目录1 本文简介2 单表查询优化1 Prewhere 替代 where2 数据采样3 指定select字段查询与分区裁剪4 orderby结合 where、limit5 避免构建虚拟列6 uniqCombined 替代 distinct7 使用物化视图8 其他注意事项(1)查询熔断(2)关闭虚拟内存(3)配置 join_use_nulls(4)批量写入时先排序(5)关注 CPU3 多表查询优化1 准备表和数据2 用 IN 代替 JOIN（强烈建议）3 大小表 JOIN4 注意谓词下推(版本差异)5 分布
                                    ClickHouse是一个开源的列式数据库管理系统，专门为在线分析处理（OLAP）和数据仓库设计。它支持高性能查询和数据压缩，能够快速处理海量数据。以下是ClickHouse的一些特点：高性能：ClickHouse是一个高度优化的列式数据库，它可以在秒级别内查询和分析数十亿行数据。灵活的数据模型：ClickHouse支持动态和静态列，以及多级分区和排序。它还支持任意维度的数组和嵌套结构数据类型。实时数据传输：ClickHouse可以与Kafka等实时消息队列集成，以支持流数据处理。
                                    clickhouse 22版本中新添加了一种字段类型: json， 存储JavaScript Object Notation (JSON) documents 在单个字段中
JSON字段类型目前还是一个实验特性，如果启用，需要设置：`allow_experimental_object_type = 1`
本文讲解json字段类型如何使用，将基于json相关的函数如何进行行列转化、字段提取及解析json数组。
建表插数据
create table t_json_demo(id UInt8, prov String) ENGINE=TinyLog;
insert into t_json_demo values(1, 'jiangsu'),
(1, 'jiangsu'),
(2, 'anhui'),
(2, 'anihu'),
(3, 'beijing');
以JSON格式查询
select id,c
                                    json数据在ClickHouse中就和string字段一样，但可以使用JSON*函数检查并抽取json键值。还可以使用抽取函数作为索引提升查询性能，但最好考虑将它们移动到单独的列中，只留下动态内容存储在JSON列中。参考：https://altinity.com/blog/clickhouse-json-data-type-version-22-6；
                                    JSON解析案例
[clickhouse加载文件类型](https://clickhouse.tech/docs/en/interfaces/formats/#formats)   
[json解析字符串函数](https://clickhouse.tech/docs/en/sql-reference/functions/json-functions/)
drop table if exists tb_ods_log;
create table tb_ods_log(line String)en
    "date": "2021-01-02",
    "total_time": "72;34;0",
    "pkg": "com.android.launcher;com.android.settings;com.google.android.set...
JSON函数
在Yandex.Metrica中，用户使用JSON作为访问参数。为了处理这些JSON，实现了一些函数。（尽管在大多数情况下，JSON是预先进行额外处理的，并将结果值放在单独的列中。）所有的这些函数都进行了尽可能的假设。以使函数能够尽快的完成工作。
我们对JSON格式做了如下假设：
字段名称（函数的参数）必须使常量。
	字段名称必须使用规范的编码。例如：visitParamHas('{"abc":"def"}', 'abc') = 1，但是 visi.