json数据上传到hive表中,给出noviablealtexception/praseexception

myzjeezk  于 2021-06-02  发布在  Hadoop
关注(0)|答案(1)|浏览(375)

我的json在下面。我必须将这个json加载到hive中,并且必须查询一些细节。

{
  "id": "1234",
  "pdid": "abcd",
  "summary": {
    "tripStartTimestamp": 1485263310528,
    "tripEndTimestamp": 0,
    "status": 10,
    "totalGPSDistanceMetres": 0,
    "avgGPSSpeed": 0,
    "maxGPSSpeed": 0,
    "avgInstMileage": 0,
    "totalHaltTimeSeconds": 0,
    "totalIdlingTimeSeconds": 0,
    "totalRunningTimeMins": 0,
    "startLocation": {
      "latitude": 13.022425,
      "longitude": 77.760587,
      "speed": 70,
      "ts": 1485263310528,
      "direction": 0
    },
    "endLocation": null,
    "driverBehaviorSummary": [
      {
        "driver": null,
        "noOfRapidAcceleration": 0,
        "noOfRapidDeceleration": 0,
        "noOfOverSpeed": 0,
        "noOfHarshBreak": 0
      }
    ]
  },
  "latLongs": [
    {
      "latitude": 13.022425,
      "longitude": 77.760587,
      "speed": 70,
      "ts": 1485263310528,
      "direction": 0
    }
  ],
  "halts": [],
  "idlings": []
}

我在下面为配置单元编写了create table语句。我计算了json模式,并用它来创建下面的结构。

CREATE TABLE TABLE_ABC_Test1(
  id string ,
  pdid string ,
  summary object<struct<
  tripStartTimestamp:int,
  tripEndTimestamp:int,
  status:int,
  totalGPSDistanceMetres:int,
  avgGPSSpeed:int,
  maxGPSSpeed:int,
  avgInstMileage:int,
  totalHaltTimeSeconds:int,
  totalIdlingTimeSeconds:int,
  totalRunningTimeMins:int,
  startLocation object<struct<
  latitude:int,
  longitude:int,
  speed:int,
  ts:int,
  direction:int>>
  endLocation:string,
  driverBehaviorSummary array<struct<object<struct<
  driver:string,
  noOfRapidAcceleration:int,
  noOfRapidDeceleration:int,
  noOfOverSpeed:int,
  noOfHarshBreak:int
  >>>>
  >>
  latLongs<array<struct<object<
  latitude:int,
  longitude:int,
  speed:int,
  ts:int,
  direction:int
  >>>>
  halts<array<struct<>>>
  idlings<array<struct<>>>
   )
ROW FORMAT SERDE 
'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE;

但错误是:

NoViableAltException(26@[])
        .......some hive stack trace
FAILED: ParseException line 4:10 cannot recognize input near 'object' '<' 'struct' in column type
idv4meu8

idv4meu81#

Hive没有 object 数据类型,你也失踪来了 : 一些现场作业。
这个 create table 语法是,

CREATE TABLE TABLE_ABC_Test1(
  id string ,
  pdid string ,
  summary struct<
  tripStartTimestamp:int,
  tripEndTimestamp:int,
  status:int,
  totalGPSDistanceMetres:int,
  avgGPSSpeed:int,
  maxGPSSpeed:int,
  avgInstMileage:int,
  totalHaltTimeSeconds:int,
  totalIdlingTimeSeconds:int,
  totalRunningTimeMins:int,
  startLocation:struct<
  latitude:int,
  longitude:int,
  speed:int,
  ts:int,
  direction:int>,
  endLocation:string,
  driverBehaviorSummary:array<struct<
  driver:string,
  noOfRapidAcceleration:int,
  noOfRapidDeceleration:int,
  noOfOverSpeed:int,
  noOfHarshBreak:int
  >>
  >,
  latLongs array<struct<
  latitude:int,
  longitude:int,
  speed:int,
  ts:int,
  direction:int
  >>,
  halts array<string>,
  idlings array<string>
   )
ROW FORMAT SERDE 
'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE;

相关问题