使用Excel、Python、Hive和Spark处理JSON数据

好久时间没更了，最近继续更新起来！本文咱们来看看如何使用Excel、Python、Hive和Spark SQL来处理json格式的数据！满满干货，细细品尝！

先介绍下咱们使用的数据，就来点简单的吧：

{"name":"wenwen","age":"26","sex":"male"}

就三个字段，分别是姓名、年龄、性别。

1、使用Excel处理JSON字符串

Excel中并没有解析JSON数据的函数，只能通过两三个字符串处理函数来实现类似的功能，直接先上一个简单的版本来获取name：

=MID(A1,FIND("name",A1)+LEN("name"":"""),6)

结果如下：

上面的例子中，我们用到了几个excel函数，下面分别来介绍一下：

1）MID(单元格／目标字符串，起始位置，字符个数)：MID函数用于截取字符串中的一部分，我们需要指定两个参数，一个是截取的起始位置，另一个是要截取的字符的个数。要想获得指定key的起始位置，我们需要下面的函数find

2）find(指定字符串，单元格／目标字符串)：find函数用于获取指定字符串在给定单元格的内容中的位置

3）len(单元格／目标字符串)：len函数用于返回目标字符串或单元格内容中的字符串长度

4）json中有双引号，那么我们如何进行转义呢？并不像一般编程语言中使用\来进行转义，这里我们使用两个连续的双引号来表示一个双引号。

好了，了解了上面的几个Excel函数，我们来看看上面的公式吧。主要讲一下如何获得名字起始位置的过程：

FIND("name",A1)+LEN("name"":""")

1） 首先，FIND("name",A1)获取的是name或者说开头的字母n在A1单元格对应的字符串的位置，但这并不是我们想要的位置，需要向后偏移
2）偏移的长度是字符串name":"的长度，可以看到，对于双引号，用了连续的两个双引号进行了转义。

基础知识梳理完毕，但你可能会问两个问题：

1）名字长度不一定是6啊，能不能自适应获取名字的长度？
2）如果是json中最后一个key该怎么办呢？

先来解决第一个问题，我们来自适应获取名字的长度。这里我们就要借助下一个key的位置了：

=MID(A1,FIND("name",A1)+LEN("name"":"""),FIND(""",""age",A1)-(FIND("name",A1)+LEN("name"":""")))

结果如下：

再看第二个问题，当我们要获取的是最后一个Key的内容也就是性别时，我们需要借助}来自适应得到对应的长度：

=MID(A1,FIND("sex",A1)+LEN("sex"":"""),FIND("""}",A1)-(FIND("sex",A1)+LEN("sex"":""")))

结果如下：

好了，Excel就介绍到这里了！本文最难的部分也结束了，下面的内容就相对简单了，一起来学习／复习下！

2、使用Python处理JSON字符串

JSON数据可以类比于Python里面的字典dict，因此使用时首先需要把JSON字符串转换为dict类型，这里使用json.loads方法：

import json

json_str = '{"name":"wenwen","age":"26","sex":"male"}'

dict_obj = json.loads(json_str)

print(type(dict_obj))
print(dict_obj['name'])
print(dict_obj['sex'])

另一个方法是json.load，与loads方法不同的是，load方法传入的是文件对象，而loads方法传入的是字符串：

f = open("data/json_file.txt",'r')
dict_obj = json.load(f)

print(dict_obj['name'])
print(dict_obj['sex'])

那么，字典如何转换成json串呢，这里使用的是dumps方法：

dict_obj = {"name":"wenwen","age":"26","sex":"male"}
json_str = json.dumps(dict_obj)
print(json_str) # {"name":"wenwen","age":"26","sex":"male"}

同样，如果想直接写入文件，使用dump方法即可：

dict_obj = {"name":"wenwen","age":"26","sex":"male"}
f = open("data/json_file2.txt",'w')
json.dump(dict_obj,f)

3、使用Hive处理JSON字符串

我们先用spark写点数据进去：

import spark.implicits._

val seqData = Seq(
  (1,"{\"name\":\"wenwen\",\"age\":\"26\",\"sex\":\"male\"}")
)
val seq2df = seqData.toDF("id","userinfo")
seq2df.write.saveAsTable("default.userinfo")

用hive处理json字符串使用get_json_object和json_tuple方法。先来看一下get_json_object方法：

select 
  id,
  get_json_object(userinfo,'$.name') as name,
  get_json_object(userinfo,'$.sex') as sex
from 
  default.userinfo;

结果如下：

如果指定的字段不存在，则会返回null：

使用get_json_object方法只能获取其中一个字段的值，那么要想获取多个字段的值，可以使用json_tuple方法:

select json_tuple(userinfo,'name','age') from default.userinfo;

此时返回结果：

再试一次：

select id,json_tuple(userinfo,'name','age','sex') from default.userinfo;

咦，好想报错了：

哈哈，下面才是正确的打开方式：

select id,b.name,b.age,b.sex
from default.userinfo
lateral view json_tuple(userinfo,'name','age','sex') b as name,age,sex;

此时结果正常：

最后再提一句，使用 get_json_object获取时，字段要使用'$.name'，而使用json_tuple时，直接用name即可。

4、使用Spark SQL处理JSON字符串

最后看看spark吧，当然，我们可以直接写sql：

 val df = spark.sql(
      """
        |select id,b.name,b.age,b.sex
        |from
        | default.userinfo
        | lateral view json_tuple(userinfo,'name','age','sex') b as name,age,sex
      """.stripMargin)

如果不写sql，使用spark sql提供的function的话，写法如下：

import spark.implicits._

    val seqData = Seq(
      (1,"{\"name\":\"wenwen\",\"age\":\"26\",\"sex\":\"male\"}")
    )

    val seq2df = seqData
      .toDF("id","userinfo")
      .withColumn("name",json_tuple($"userinfo","name"))
      .withColumn("age",get_json_object($"userinfo","$.age"))
      .select(json_tuple($"userinfo","name","sex").as(Seq("name1","sex")),col("id"),col("userinfo"))

输出如下：

可以看到，和hive的写法基本是一致的。当使用json_tuple方法时，如果只有一列，那么可以直接使用withColumn方法，如果两列及以上时，则可以尝试最后一行的写法，在select中获取。

好了，本文的内容就到这里了，使用四种数分工具处理json数据，你学会了么？

参考文献

1、https://www.jianshu.com/p/9213cdd4adb3
2、https://blog.csdn.net/weixin_37139561/article/details/90415619