Retrieving nested array with Hive


#1

I am struggling to retrieve the information under portIdentifiers as shown below - all I get is null. The information under specification pose no problem.

Any hint as to what I've overseen is welcome.

{
  "serviceId": "service_0993",
  "specification": {
    "lineSpecification": {
      "loopConnectionType": "ABC",
      "numberOfLines": 1,
      "downstreamBandwidth": 60000,
      "upstreamBandwidth": 60000,
    },  
    "portIdentifiers": [
      {
        "accessLineId": "ABC-123",
        "node": "no-def456-fttx01",
        "rack": 2,
        "shelf": 3,
        "module": 4,
        "port": 32
      }
    ]
  "currentState": "ENABLED",
}

The CREATE TABLE statement :

CREATE EXTERNAL TABLE elastic.access_line (
serviceId STRING,
currentState STRING,

loopConnectionType STRING,
numberOfLines BIGINT,
downstreamBandwidth BIGINT,
upstreamBandwidth BIGINT,

node STRING,
rack BIGINT,
shelf BIGINT,
module BIGINT,
port BIGINT

)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.nodes'='elastic-server.net', 'es.port'='9200', 'es.resource'='access_line/access_line',

'es.mapping.names'='serviceId:serviceId,currentState:currentState,

loopConnectionType:specification.lineSpecification.loopConnectionType,
numberOfLines:specification.lineSpecification.numberOfLines,
downstreamBandwidth:specification.lineSpecification.downstreamBandwidth,
upstreamBandwidth:specification.lineSpecification.upstreamBandwidth,

node:specification.portIdentifiers[0].node,
rack:specification.portIdentifiers[0].rack,
shelf:specification.portIdentifiers[0].shelf,
module:specification.portIdentifiers[0].module,
port:specification.portIdentifiers[0].port',

'es.query' = '?q=*')

I also tried with the following STRUCT - without success:

portIdentifier STRUCT<
node: STRING,
rack: BIGINT,
shelf: BIGINT,
module: BIGINT,
port: BIGINT,
luNumber: STRING,
unitNumber: BIGINT,
nniName: STRING,
innerTag: BIGINT,
rid: STRING,
ridProvider: STRING>

(system) #2