question JSON to SQL in Archives of Support Questions (Read Only)

JSON to SQL

hans_feldmann — Wed, 24 Aug 2016 23:57:03 GMT

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

Re: JSON to SQL

mburgess — Thu, 25 Aug 2016 00:32:08 GMT

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.

Re: JSON to SQL

mqureshi — Thu, 25 Aug 2016 00:49:45 GMT

@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

Re: JSON to SQL

boudra_moha — Wed, 16 Aug 2017 04:15:05 GMT

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.