Hive学习——解析JSON字符串

目录

1.JSON数据

2.创建内部表

3.上传本地文件到hive表的路径下

4.查看文件是否成功上传

5.查看表数据

6.对复杂数据类型取值

---

1.JSON数据

vim /opt/module/data/t1.txt

{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

2.创建内部表

        这里创建内部表，也可以创建外部表，根据业务需要来。

        考虑使用专门负责JSON文件的JSON Serde，设计表字段时，表的字段与JSON字符串中的一级字段保持一致，对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。

create table if not exists teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<street:string,city:string,postal_code:int>
) row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
    location '/user/hive/warehouse/teacher';

3.上传本地文件到hive表的路径下

dfs -put /opt/module/data/teacher.txt /user/hive/warehouse/teacher;

4.查看文件是否成功上传

dfs -cat /user/hive/warehouse/teacher/teacher.txt;

{"name":"dasongsong","friends":["bingbing","lili"],"students":{"xiaohaihai":18,"xiaoyangyang":16},"address":{"street":"hui long guan","city":"beijing","postal_code":10010}}

5.查看表数据

select * from teacher;

6.对复杂数据类型取值

select name,
       friends[0]               as f1,
       friends[1]               as f2,
       students['xiaohaihai']   as s1,
       students['xiaoyangyang'] as s2,
       address.street,
       address.city,
       address.postal_code
from teacher;