轻松入门大数据第10章入门到进阶：如何快速掌握Hive核心函数 10.1 Hive函数 10.1.7 动手实操场景题之处理json数据

谁践踏了优雅 2024-03-16 11:09 8阅读 0赞

### 轻松入门大数据 ###

#### 文章目录 ####

*   *  轻松入门大数据
     *   *  第10章 入门到进阶：如何快速掌握Hive核心函数
         *   *  10.1 Hive函数
             *   *  10.1.7 动手实操场景题之处理json数据

![在这里插入图片描述][a3ba3bda4a56412082b470cd935fc5fb.png_pic_center]

#### 第10章 入门到进阶：如何快速掌握Hive核心函数 ####

##### 10.1 Hive函数 #####

> 1.  复杂数据类型
> 2.  Hive内置函数
> 3.  Hive UDF函数
> 4.  窗口分析函数
> 5.  SQL场景题

###### 10.1.7 动手实操场景题之处理json数据 ######

查看函数描述：

`desc function extended json_tuple;`

![在这里插入图片描述][20b1fd41969d41d299bbb5ecf90872d9.png_pic_center]

OK，试试，先来份数据：

![在这里插入图片描述][6780a6e82b2c4d2ca1af69627d4798d5.png_pic_center]

![在这里插入图片描述][644274451a994b42a479e29e31b1fd3b.png_pic_center]

这份数据很大，笔者这里就不贴出来了。

这是一份电影评分数据，电影编号、评分、时间、评分人

创建表并加载数据：

create table rating_json(json  string);
    
    load data local inpath '/home/hadoop/data/rating.json' into table rating_json;

![在这里插入图片描述][e12483330a6543b1a0635db2edc31a2c.png_pic_center]

OK，查一下，就不全查了，limit 5 条

![在这里插入图片描述][65491bde0ee14ef7b52e25e33ddc1a7d.png_pic_center]

没问题。

使用`json_tuple()` 函数进行处理：

select json_tuple(json,'movie','rate','time','userid') from rating_json limit 5;

![在这里插入图片描述][907a159e964b470883e84cbf2ec19e9f.png_pic_center]

改下列名：

select json_tuple(json,'movie','rate','time','userid') as(movie,rate,`time`,userid) from rating_json limit 5;

![在这里插入图片描述][d4bd76f2d12f469ab1c2508255877c9f.png_pic_center]

> time 是个关键字，不加\`\`会报错

对这个数据进行操作：

select
    userid,movie,rate,`time`,
    year(from_unixtime(cast(`time` as bigint))) as year
    from
    (select json_tuple(json,'movie','rate','time','userid') as(movie,rate,`time`,userid) from rating_json)tmp limit 10;

![在这里插入图片描述][6f2568d9cff24d97a57e9228c0f7adfa.png_pic_center]

select
    userid,movie,rate,`time`,
    year(from_unixtime(cast(`time` as bigint))) as year,
    month(from_unixtime(cast(`time` as bigint))) as month,
    day(from_unixtime(cast(`time` as bigint))) as day,
    hour(from_unixtime(cast(`time` as bigint))) as hour,
    minute(from_unixtime(cast(`time` as bigint))) as minute,
    from_unixtime(cast(`time` as bigint)) as ts
    from
    (select json_tuple(json,'movie','rate','time','userid') as(movie,rate,`time`,userid) from rating_json)tmp limit 10;

![在这里插入图片描述][b96dc88d1f9548bba7c32ee9f6273d85.png_pic_center]

create table t_rate as 
    select
    userid,movie,rate,`time`,
    year(from_unixtime(cast(`time` as bigint))) as year,
    month(from_unixtime(cast(`time` as bigint))) as month,
    day(from_unixtime(cast(`time` as bigint))) as day,
    hour(from_unixtime(cast(`time` as bigint))) as hour,
    minute(from_unixtime(cast(`time` as bigint))) as minute,
    from_unixtime(cast(`time` as bigint)) as ts
    from
    (select json_tuple(json,'movie','rate','time','userid') as(movie,rate,`time`,userid) from rating_json) tmp;

把这个加工写到一个表里，方便后续的处理【数据清洗】

![在这里插入图片描述][b25203351a5a49c784ad80930b2f7e40.png_pic_center]

简单查一下：

select * from t_rate limit 5;

![在这里插入图片描述][6bc8ed7d1372402587aa8b70d3052859.png_pic_center]

没问题。

当然官网还有很多对json 进行处理的函数使用说明：

[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF\#LanguageManualUDF-get\_json\_object][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-get_json_object]

![在这里插入图片描述][76faf99596a24fdfa041bd34dff0263d.png_pic_center]

[a3ba3bda4a56412082b470cd935fc5fb.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/efad54cea405415f8c3a1dd3e67cc5d0.png
[20b1fd41969d41d299bbb5ecf90872d9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c80e400da115455db509380fdb51d010.png
[6780a6e82b2c4d2ca1af69627d4798d5.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/31a3b3a06bdf42ea8e0d3463167e3565.png
[644274451a994b42a479e29e31b1fd3b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a1901b56837d499186eb8705e41c66c5.png
[e12483330a6543b1a0635db2edc31a2c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/19eda4b03b834f5e99f504e876cc9ae2.png
[65491bde0ee14ef7b52e25e33ddc1a7d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/804a62a18b56435b8e91295cfb8ee168.png
[907a159e964b470883e84cbf2ec19e9f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/54f0244042d94ee8beabb0207348eba6.png
[d4bd76f2d12f469ab1c2508255877c9f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e7684bfc6f4047babb5da3e63cfb9b87.png
[6f2568d9cff24d97a57e9228c0f7adfa.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/1b6cbe288b1d4a66bed19a26b9d51944.png
[b96dc88d1f9548bba7c32ee9f6273d85.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/684cf92323844acb8b5b8f30023bd3bf.png
[b25203351a5a49c784ad80930b2f7e40.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c48d4c3c079a4245ad8d8cbbd720db99.png
[6bc8ed7d1372402587aa8b70d3052859.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/3808fa2001324404b67a741aac4ede3f.png
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-get_json_object]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-get_json_object
[76faf99596a24fdfa041bd34dff0263d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c5e2fd18ddd1435a8b309c79012a4375.png