JSON to SQL

[#1]

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"
  }
}

[#2 · Accepted solution]

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.

[#3]

@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

[#4]

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.