一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据-Toy模板网

这篇具有很好参考价值的文章主要介绍了一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、目标

在Hive的ODS层建外部表，然后加载HDFS中的CSV文件数据

注意：CSV文件中含有未解析的JSON格式的字段数据，并且JSON字段中还有逗号

一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据,Hive,hive,hadoop,json

二、第一次建外部表，直接以'，'分隔行字段，结果JSON数据只显示一部分

（一）建外部表SQL

create  external  table  if not exists ods_track2(
     device_no    string     comment '设备编号',
     create_time  timestamp  comment '创建时间',
     track_data   string     comment '轨迹数据集合（包含多个目标点）'
)
comment '轨迹数据表'
row format delimited fields terminated by ','
stored as  textfile  location '/rtp/track'
tblproperties("skip.header.line.count"="1") ;

（二）查看表数据

一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据,Hive,hive,hadoop,json

（三）表数据问题

JSON数据的字段track_data只显示一部分数据，因为JSON格式数据里面也含有逗号

三、解决问题：第二次建外部表，不直接以逗号分隔行字段，而是用Hive提供的Serde

（一)建外部表SQL

create  external  table  if not exists ods_track(
     device_no    string     comment '设备编号',
     create_time  timestamp  comment '创建时间',
     track_data   string     comment '轨迹数据集合（包含多个目标点）'
)
comment '轨迹数据表'
row format serde  'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties (
"separatorChar" = ",",
"quoteChar" = "\"",
"escapeChar" = "\\"
)
stored as  textfile  location '/rtp/track'
tblproperties("skip.header.line.count"="1") ;

（二）查看表数据

一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据,Hive,hive,hadoop,json

（三）验证一条JSON格式字段track_data的数据是否完整？？？

[{"id":"14","length":5.0,"height":3.0,"posX":63.0,"posY":37.0,"acs":99.0,"angle":83.0,"altitude":99.0,"longitude":40.0,"latitude":33.0,"trust":5.0,"brand":"SU A00001","carType":"4","carColor":10},{"id":"3","length":9.0,"height":1.0,"posX":43.0,"posY":88.0,"acs":52.0,"angle":82.0,"altitude":81.0,"longitude":59.0,"latitude":84.0,"trust":4.0,"brand":"SU A00001","carType":"2","carColor":5},{"id":"13","length":1.0,"height":1.0,"posX":5.0,"posY":33.0,"acs":57.0,"angle":78.0,"altitude":33.0,"longitude":36.0,"latitude":61.0,"trust":5.0,"brand":"SU A00001","carType":"4","carColor":10},{"id":"89","length":3.0,"height":1.0,"posX":80.0,"posY":96.0,"acs":29.0,"angle":90.0,"altitude":91.0,"longitude":43.0,"latitude":40.0,"trust":1.0,"brand":"SU A00001","carType":"4","carColor":2},{"id":"84","length":3.0,"height":1.0,"posX":26.0,"posY":80.0,"acs":21.0,"angle":25.0,"altitude":99.0,"longitude":86.0,"latitude":31.0,"trust":4.0,"brand":"SU A00001","carType":"3","carColor":1},{"id":"97","length":6.0,"height":3.0,"posX":70.0,"posY":75.0,"acs":9.0,"angle":58.0,"altitude":99.0,"longitude":62.0,"latitude":95.0,"trust":1.0,"brand":"SU A00001","carType":"3","carColor":3},{"id":"17","length":9.0,"height":2.0,"posX":99.0,"posY":17.0,"acs":100.0,"angle":79.0,"altitude":5.0,"longitude":61.0,"latitude":99.0,"trust":1.0,"brand":"SU A00001","carType":"3","carColor":10},{"id":"81","length":8.0,"height":1.0,"posX":30.0,"posY":66.0,"acs":92.0,"angle":73.0,"altitude":3.0,"longitude":62.0,"latitude":84.0,"trust":1.0,"brand":"SU A00001","carType":"2","carColor":8}]

验证结果：数据完整

乐于奉献共享，帮助你我他！！！文章来源地址https://www.toymoban.com/news/detail-604794.html

到了这里，关于一百三十三、Hive——Hive外部表加载含有JSON格式字段的CSV文件数据的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！