hive中创建hive-json格式的表及查询
2023-09-14 08:57:20 时间
在hive中对于json的数据格式,可以使用get_json_object或json_tuple先解析然后查询。
也可以直接在hive中创建json格式的表结构,这样就可以直接查询,实战如下(hive-2.3.0版本):
1. 准备数据源
将以下内容保存为test.txt
{"student":{"name":"king","age":11,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"wang","age":12,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"test","age":13,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"test2","age":14,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"test3","age":15,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"test4","age":16,"sex":"M"},"class":{"book":"语文","level":2,"score":80},"teacher":{"name":"t1","class":"语文"}}
2. 创建hive表
注意serde格式大小写不能写错: org.apache.hive.hcatalog.data.JsonSerDe
create external table if not exists dw_stg.student( student map<string,string> comment "学生信息", class map<string,string> comment "课程信息", teacher map<string,string> comment "授课老师信息" ) comment "学生课程信息" row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' stored as textfile;
3. 上传数据
将test.txt上传到刚才创建的student目录
hdfs dfs -put test.txt /user/hive/warehouse/dw_stg.db/student/
4. 使用hql查询
查询所有信息记录:
查询字段student信息
查询字段class信息
查询学生姓名为test4的所有记录
取json串中某个值可以使用 student['name'] ,如下:
select student['name'] as stuName, class['book'] as cls_book, class['score'] as cls_score, teacher['name'] as tech_name from student where student['name'] = 'test4';
总体看起来,比使用get_json_object或json_tuple解析方便多了。
相关文章
- Golang JSON、MD5、XML
- js判断字符串是否json格式
- Newtonsoft.Json 序列化和反序列化 以及时间格式
- Json概述以及python对json的相关操作(转)
- 在JavaScript中使用json.js:使得js数组转为JSON编码
- python中精确输出JSON浮点数的方法
- 在JavaScript中使用json.js:使得js数组转为JSON编码
- .NET Core采用的全新配置系统[6]: 深入了解三种针对文件(JSON、XML与INI)的配置源
- 如何用CL_CLB_PARSE_JSON解析json字符串到动态生成的ABAP内表结构里
- 推荐一个谷歌浏览器插件json单独窗口格式化插件JSON-handle
- Flutter高级第2篇:JSON的序列化和反序列化、创建模型类转换Json数据
- Android kotlin 进阶之用Retrofit+OkHttp+协程+LiveData搭建MVVM来实现网络请求(网络数据JSON解析)显示在RecyclerView
- JSON说明及使用,javascript,java如何解析及转化json
- Android JSON解析json数据
- (39)C#中使用JSON库
- jQuery ajax读取本地json文件_jQuery请求本地JSON文件,在谷歌浏览器运行时报跨域错误_Vscode使用Live Server
- Jackson转换json大写_关于jackson转化json的原理_jackson序列化和反序列化Json