SparkSQL专题3 ~ json函数

前言

$记录本人使用SparkSql历程。以前主要是用MYSQL居多，且底层基本依赖于Mybatis，SQL编写能力较弱$
$最近开始使用SparkSQL进行各种各样SQL的需求开发$
$本人是SparkSQL小白，分享SparkSQL系列文章内容比较简单，高手请绕道$

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

to_json()

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))));

$任何数据结构想转化为Json字符串的都可以这么做，相关转化函数$

cast ()

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

select cast ( '10' as int ) as b

$注意：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

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

$注意：只能转化基础的数据类型，无法转化特殊的数据结构，不然会报错$

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

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;

两层数据结构

-- 创建测试表
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;

get_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 

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 

$总结$

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; 

更多复杂的项目结构看案例

总结

$知道的越多，不知道的越多，希望对你有帮助！$