[jira] [Created] (DRILL-4887) KVGEN throws a Heterogenous Type error while applying on a JSON when querying a JDBC Storage Plugin

[Nataraj Gnanavadivel (JIRA)]

Nataraj Gnanavadivel created DRILL-4887:
-------------------------------------------

             Summary: KVGEN throws a Heterogenous Type error while applying on 
a JSON when querying a JDBC Storage Plugin
                 Key: DRILL-4887
                 URL: https://issues.apache.org/jira/browse/DRILL-4887
             Project: Apache Drill
          Issue Type: Bug
          Components: Storage - JDBC
    Affects Versions: 1.8.0
            Reporter: Nataraj Gnanavadivel


We are using Apache Drill 1.8.0 to query mysql DB via JDBC storage plugin.
One of the columns is of type BLOB and it contains a JSON document.

We intended to access that JSON properties as follows:

select
name,
flatten(kvgen(jsonContent)) as json
from
(select 
regular_col1 as name, 
convert_from(blob_column_having_json, 'JSON') as jsonContent 
from 
mysqlstorageplugin.someschema.sometable)

we are seeing an error similar to this:

org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: 
DrillRuntimeException: Mappify/kvgen does not support heterogeneous value 
types. All values in the input map must be of the same type. The field [ppu] 
has a differing type [minor_type: FLOAT8 mode: OPTIONAL ]. Fragment 0:0 [Error 
Id: 8b572aba-f1ad-4aa4-93ed-b3dbee502e8d on 
kaiburr-bigdata-engine-01.c.erudite-gate-118302.internal:31010]

The sample JSON that is stored as BLOB in mysql is as follows:
{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "sales": 35
}

Any workarounds or the right approach to access JSON that is stored as BLOB in 
any RDBMS columns and query them at the field/property level and applying 
aggregation logics on them is what we are looking for.

please help us giving the right directions.

Thanks a lot!



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)