CODEWITHSUNDEEP
sqlapache-drill
Stefan Stoychev
Stefan Stoychev

Reputation: 5022

Apache Drill error selecting flatten kvgen value

Im having a json file with the following structure:

[{
    "metric" : "movie.Guns",
    "tags" : {
        "channel" : "143"
    }
    "dps" : {
        "1448942400" : 2.5174216027874565,
        "1448943000" : 4.378333333333333,
        "1448943600" : 4.04,
        "1448944200" : 4.805
    }
 }
....
]

And im trying to extract some data from it using Apache Drill. Basically i need to have 4 columns: metric, channel, timestamp and value.

With the example above the result should be:

metric,     channel, timestamp,  value
movie.Guns, 143    , 1448942400, 2.5174216027874565
movie.Guns, 143    , 1448943000, 4.378333333333333
movie.Guns, 143    , 1448943600, 4.04
movie.Guns, 143    , 1448944200, 4.805

And i've wrote the following sql to achieve this:

select a.metric, a.channel, a.dps.key, a.dps.value from
  (select flat.channel, flat.metric, flat.dps dps from 
    (select t.tags.channel as channel, t.metric, FLATTEN(KVGEN(t.dps)) as  dps from dfs.`/Users/Home/Downloads/tmsdb.json` t)

The problem is a.dps.value column for some reason. If this column is not returned its working normal. As long as i bring it im getting error message ARSE ERROR: Encountered ". value" at line 2, column 45...

If i select only a.dps im getting value like {"key":"1448942400","value":2.5174216027874565} and if a.dps.key is selected it returns correct values.

Not sure where the issues is.

Upvotes: 1

Views: 370

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5022

And one minute later after the post i've found the answer :)

To return correct result the value column should be selected like this:

select a.metric, a.channel, a.dps.key, a.dps.`value` ...

Upvotes: 0

Related Questions