SparkSQL专题3 ~ json函数

绝地灬酷狼 2022-09-02 12:45 301阅读 0赞

## 前言 ##

记 录 本 人 使 用 S p a r k S q l 历 程 。 以 前 主 要 是 用 M Y S Q L 居 多 ， 且 底 层 基 本 依 赖 于 M y b a t i s ， S Q L 编 写 能 力 较 弱 \\color\{red\} 记录本人使用SparkSql历程。以前主要是用MYSQL居多，且底层基本依赖于Mybatis，SQL编写能力较弱 记录本人使用SparkSql历程。以前主要是用MYSQL居多，且底层基本依赖于Mybatis，SQL编写能力较弱  
 最 近 开 始 使 用 S p a r k S Q L 进 行 各 种 各 样 S Q L 的 需 求 开 发 \\color\{red\} 最近开始使用Spark SQL进行各种各样SQL的需求开发 最近开始使用SparkSQL进行各种各样SQL的需求开发  
 本 人 是 S p a r k S Q L 小 白 ， 分 享 S p a r k S Q L 系 列 文 章 内 容 比 较 简 单 ， 高 手 请 绕 道 \\color\{red\}本人是Spark SQL小白，分享SparkSQL系列文章内容比较简单，高手请绕道 本人是SparkSQL小白，分享SparkSQL系列文章内容比较简单，高手请绕道

推荐翻阅 [SparkSQL函数手册][SparkSQL]  
第一次接触SparkSQL时，就是通过SparkSQL函数手册学习，函数手册非常的好，但是知识缺乏限制自己的想象力，没有这个概念就很难去找相应的函数，例如单条数据的某个字段存储的是数组对象，将其展开，由一条变成多条，直接用滑窗函数即可。  
在后续的文章中，会通过一个个的案例，实际的业务需求，将函数慢慢普及

## to\_json() ##

![在这里插入图片描述][abaa188f3e064c9a8b6bf2ac46b22cb8.png]

SELECT to_json(named_struct('a', 1, 'b', 2));
    
    SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
    
    SELECT to_json(array(named_struct('a', 1, 'b', 2)));
    
    SELECT to_json(map('a', named_struct('b', 1)));
    
    SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
    
    SELECT to_json(map('a', 1));
    
    SELECT to_json(array((map('a', 1))));

任 何 数 据 结 构 想 转 化 为 J s o n 字 符 串 的 都 可 以 这 么 做 ， 相 关 转 化 函 数 \\color\{red\} 任何数据结构想转化为 Json字符串的都可以这么做，相关转化函数 任何数据结构想转化为Json字符串的都可以这么做，相关转化函数

## cast () ##

cast(expr AS type) - Casts the value expr to the target data type type

select cast ( '10' as int ) as b

![在这里插入图片描述][5fdf30228e804a9b8505c6865cb92d4f.png]  
 注 意 ： c a s t 只 能 转 化 基 础 的 数 据 类 型 ， 无 法 转 化 特 殊 的 数 据 结 构 ， 不 然 会 报 错 \\color\{red\}注意：cast只能转化基础的数据类型，无法转化特殊的数据结构，不然会报错 注意：cast只能转化基础的数据类型，无法转化特殊的数据结构，不然会报错  
1、 转化数组 或数组对象

select cast (array(1,2,3) as string) as b
    
    or
    
    select cast(array(
    	named_struct('a', 1, 'b', 2) 
    ) as string) as b

![在这里插入图片描述][4de1b04763cb42b79d9e8f7e74628860.png]  
2、转化对象

select cast (named_struct('a', 1, 'b', 2)  as string) as b

报一样的错

## 函数库类型转化 ##

tinyint  
tinyint(expr) - Casts the value expr to the target data type tinyint.  
int  
int(expr) - Casts the value expr to the target data type int.  
decimal  
decimal(expr) - Casts the value expr to the target data type decimal.  
…  
具体参考手册

select string(10) as b

![在这里插入图片描述][dc4d7df171324d3396add2d9b3bb7c85.png]  
 注 意 ： 只 能 转 化 基 础 的 数 据 类 型 ， 无 法 转 化 特 殊 的 数 据 结 构 ， 不 然 会 报 错 \\color\{red\}注意：只能转化基础的数据类型，无法转化特殊的数据结构，不然会报错 注意：只能转化基础的数据类型，无法转化特殊的数据结构，不然会报错

select string(
    	array(1, 3, 4)
    ) as b

![在这里插入图片描述][d87e0834fbe14628bc56a8ea33cb6f1a.png]

## get\_json\_object ##

get\_json\_object(json\_txt, path) - 从path.

-- 创建测试表
    CREATE TABLE db.test (
    `params` STRING COMMENT 'params'
    )
    -- 插入测试数据
    insert overwrite table  db.test 
    select to_json(named_struct('username', 'xiaozheng', 'password', 123456)) as params;
    
    -- 查看数据
    select * from db.test ;
    
    -- 解析json 
    select get_json_object(params, '$.username') as username from db.test;

![在这里插入图片描述][ca4697ba678a4e8ca389e9c816c50ddd.png]  
两层数据结构

-- 创建测试表
    CREATE TABLE db.test (
    `params` STRING COMMENT 'params'
    )
    -- 插入测试数据
    insert overwrite table  db.test 
    select to_json(named_struct('username', 'xiaozheng', 'password', named_struct('a', 'b', 'c', 'd'))) as params;
    
    -- 查看数据
    select * from db.test ;
    
    -- 解析json 
    select get_json_object(params, '$.password.a') as username from db.test;

![在这里插入图片描述][4a0ebea4a68b472daff7e79adfad91aa.png]  
![在这里插入图片描述][6217ed43d3cb4afd8bde7be0c595cbcf.png]

## get\_tuple() ##

关于get\_json\_object和json\_tuple区别推荐文章：[get\_json\_object 和 json\_tuple][get_json_object _ json_tuple]

insert overwrite table  hive_temp_vipvpe.tmp_test_all_20210804; 
    select to_json(named_struct('username', 'xiaozheng', 'password', 123456)) as params;
    
    select json_tuple(params, 'username', 'password') from  hive_temp_vipvpe.tmp_test_all_20210804

![在这里插入图片描述][2ecc58117c3d43a0a332e289a87fa78e.png]

insert overwrite table  db.test
    select to_json(named_struct('username', 'xiaozheng', 'password', named_struct('a', 1, 'b',2))) as params;
    
    select * from db.test; 
    
    -- xiaozheng {
        "a":1,"b":2}
    select json_tuple(params, 'username', 'password') from  db.test 
    -- xiaozheng NULL
    select json_tuple(params, 'username', 'password.a') from  db.test

![在这里插入图片描述][b766b4bf68674192b0ab673b68489c0a.png]  
![在这里插入图片描述][6f9fd3faa36746a4acecb564f5fecec6.png]  
 总 结 \\color\{red\}总结 总结

1.  对同一个json数据想要查看多个值，只能多写几个get\_json\_object，比较麻烦，所以get\_tuple就派上了用场。
2.  对于复杂的嵌套的json，就操作不了。只能操作第一层JSON，会将第二层json复杂数据结构当做字符串

## from\_json() ##

字符串转json

案例1：json字符串数组转 数组对象

-- 插入测试数据
    insert overwrite table  db.test 
    select to_json(
    	array(1, 2, 4)
    ) as params;
    
    SELECT * FROM  db.test; 
    
    select from_json(params, 'ARRAY<BIGINT>') from  db.test;

案例2

-- 插入测试数据
    insert overwrite table  db.test 
    select to_json(
    	array(
    		named_struct('a',1, 'b',2),
    		named_struct('a',11, 'b',22),
    		named_struct('a',111, 'b',222)
    	)
    ) as params;
    
    SELECT * FROM  db.test; 
    
    select from_json(params, 'ARRAY<STRUCT<a:BIGINT, b:BIGINT>>') from  db.test;

![在这里插入图片描述][9e886338153343ddb8a2983bb7168ef3.png]  
更多复杂的项目结构看案例

## 总结 ##

知 道 的 越 多 ， 不 知 道 的 越 多 ， 希 望 对 你 有 帮 助 ！ \\color\{red\}知道的越多，不知道的越多，希望对你有帮助！ 知道的越多，不知道的越多，希望对你有帮助！

[SparkSQL]: https://spark.apache.org/docs/latest/api/sql/index.html
[abaa188f3e064c9a8b6bf2ac46b22cb8.png]: /images/20220829/a138fc31d8c84f24a76ecff328ea8c6b.png
[5fdf30228e804a9b8505c6865cb92d4f.png]: /images/20220829/83679acb424743bf832aab6f0a3ef018.png
[4de1b04763cb42b79d9e8f7e74628860.png]: /images/20220829/b64c0e19167c4791b94b0aad14421717.png
[dc4d7df171324d3396add2d9b3bb7c85.png]: /images/20220829/ee7d2e6bf24c4358b9c83353dc5fb121.png
[d87e0834fbe14628bc56a8ea33cb6f1a.png]: /images/20220829/39d0560a51204a068be8b433601aa727.png
[ca4697ba678a4e8ca389e9c816c50ddd.png]: /images/20220829/5f259febe02741b0aff60ace3f1c1397.png
[4a0ebea4a68b472daff7e79adfad91aa.png]: /images/20220829/ac7526ff7dbd4949ae519b3ef736a4db.png
[6217ed43d3cb4afd8bde7be0c595cbcf.png]: /images/20220829/7fbb12762d244685ac1f04e433610e09.png
[get_json_object _ json_tuple]: https://blog.csdn.net/weixin_38750084/article/details/93498986
[2ecc58117c3d43a0a332e289a87fa78e.png]: /images/20220829/59f6a01fb7a84aa893c5a24635350a0f.png
[b766b4bf68674192b0ab673b68489c0a.png]: /images/20220829/4b93e3ef2ea34be699fd8e93790ef848.png
[6f9fd3faa36746a4acecb564f5fecec6.png]: /images/20220829/76ed60f246d34bb9b78753c20426d863.png
[9e886338153343ddb8a2983bb7168ef3.png]: /images/20220829/b8d037e3fe2940caa6c5637801ac5f01.png