Getting _id field in elasticsearch to map to a field in HIVE


I am working on a project to integrate hive and elasticsearch and for one
of our use case we are loading data from ELASTISCSEARCH --> HIVE.

During this process I want to store the _id field which is in elasticsearch
document in hive. I am able to get the fields which are part of _source
like messages, @timestamp etc but I am not able to get the _id associated
with that particular document.

The following is the sample table I am trying to create

create external table eshivetable (id string,eventdate timestamp, host
string, username string, message string) STORED BY
TBLPROPERTIES('es.resource' = 'logstash-*/syslog', 'es.mapping.names' =

So when I select id it returns a null value...

Can some one help me with this please.

You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit
For more options, visit

Have the same issue, does anyone know the solution?

Found the solution:

create external table some_table (id string)
stored by 'org.elasticsearch.hadoop.hive.EsStorageHandler'
tblproperties (
    'es.resource' = '<index>/<type>',
    'es.nodes' = '<es_host>',
    '' = 'true',
    'es.mapping.names' = 'id:_metadata._id'

What about: