Support Questions

Find answers, ask questions, and share your expertise

JSON to SQL

avatar
Rising Star

I have a JSON array that I'm attempting to input to ConvertJSONToSQL, however I'm getting the following error:

"None of the fields in the JSON map to the columns defined"

I would like the process to be dynamic rather than use the AttributesToJSON process. Is that possible? My Table does have the EndTime, H1526728411, etc, columns. My JSON looks like:

{
  "Table":{
    "TableName":"HH_CELL_VQI",
    "EndTime":"2016-07-12T05:30:00-06:00",
    "H1526728411":"0",
    "H1526728412":"0",
    "H1526728413":"0",
    "H1526728414":"0",
    "H1526728415":"0",
    "H1526728416":"0",
    "H1526728417":"0",
    "H1526728418":"0",
    "H1526728419":"0",
    "H1526728420":"0"
  }
}
1 ACCEPTED SOLUTION

avatar
Master Guru

The JSON object has to be "flat", meaning all your column fields have to be at the top level of the JSON object. Yours are nested inside a "Table" field. You can use EvaluateJsonPath with a path of $.Table to get the flat JSON object.

View solution in original post

3 REPLIES 3

avatar
Master Guru

The JSON object has to be "flat", meaning all your column fields have to be at the top level of the JSON object. Yours are nested inside a "Table" field. You can use EvaluateJsonPath with a path of $.Table to get the flat JSON object.

avatar
Super Guru

@Hans Feldmann

One way I parsed my json was to convert it to Avro. So basically, after getting rid of special characters from json using "replaceText" processor, I sent it to "inferAvroSchema". Then used convertJsonToAvro using the inferred schema and then wrote that to HDFS where I had a table and read it in Hive.

Another way is to use Json hive Serde. That's actually much easier. Check this out.

https://github.com/rcongiu/Hive-JSON-Serde

avatar
New Contributor

Why don't you use this tool: JSON to SQL, it will automatically flatten the JSON data for you, so there's no extra work or coding required.