hive-jsonserde建表映射

最新推荐文章于 2024-05-19 09:20:32 发布

威尼斯的星期天

最新推荐文章于 2024-05-19 09:20:32 发布

阅读量669

点赞数

CC 4.0 BY-SA版权

分类专栏： hive

本文链接：https://blog.youkuaiyun.com/qq_37714755/article/details/114325000

hive 专栏收录该内容

17 篇文章

订阅专栏

本文介绍如何使用JSONSerDe将JSON数据直接转换为Hive表，并提供了一个实际的例子，包括所需的建表语句及配置。通过这种方式，可以轻松地处理复杂的JSON数据并进行分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

jsonserde简介

直接解析json数据转换成hive表，可以配置映射。需要相关jar包支持~

样例数据

{"a":"2021-03-02 21:27:55","e":"activity_on_start","faceImage":"","p":{"an":".ui.VIPCenterActivity","from":"com.imprexion.adplayer","fromActivity":""},"t":1614691675578,"uid":-1,"v":3,"device_id": "1f43f6d39e","app": "vipcenter"}
{"a":"2021-03-02 21:28:55","e":"activity_on_stop","faceImage":"","p":{"an":".ui.VIPCenterActivity","from":"com.imprexion.adplayer","fromActivity":""},"t":1614691675638,"uid":-1,"source_channel":2,"v":3,"device_id": "1f43f6d39e","app": "vipcenter"}

建表语句

drop table if exists andr_log_new;
create external table andr_log_new(
  `e` string COMMENT 'from deserializer', 
  `faceimage` string COMMENT 'from deserializer', 
  `p` string COMMENT 'from deserializer', 
  `t` timestamp COMMENT 'from deserializer', 
  `uid` int COMMENT 'from deserializer', 
  `v` int COMMENT 'from deserializer', 
  `source_channel` int COMMENT 'from deserializer',
  `device_id` string COMMENT 'from deserializer', 
  `app` string COMMENT 'from deserializer'
)
PARTITIONED BY ( 
  `dt` string)
ROW FORMAT SERDE 
    'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
   'ignore.malformed.json'='true',
   'mapping.e'='e',
   'mapping.faceimage'='faceimage',
   'mapping.p'='p',
   'mapping.t'='a',
   'mapping.uid'='uid',
   'mapping.v'='v',
   'mapping.source_channel'='source_channel',
   'mapping.device_id'='device_id',
   'mapping.app'='app'
)
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/user/hive/warehouse/yxsj/andr_log_new';

测试结果

在这里插入图片描述