Hive to Elasticsearch to Kibana: No Fields in the Available field column

Question

I am following the below steps:

Step 1:
create table tutorials_tbl(submission_date date, tutorial_id INT,tutorial_title STRING,tutorial_author STRING) ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe';

Step 2:
INSERT INTO tutorials_tbl (submission_date, tutorial_title, tutorial_author) VALUES ('2016-03-19 18:00:00', "Mark Smith", "John Paul");

Step 3:
CREATE EXTERNAL TABLE tutorials_tbl_es(submission_date date,tutorial_id INT,tutorial_title STRING,tutorial_author STRING)STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES('es.resource'='tutor/tutors','es.nodes'='saturn:9200');

Step 4:
INSERT INTO tutorials_tbl_es SELECT * FROM tutorials_tbl LIMIT 1;

Now I selected the index in Kibana>Settings. I have configured _timestamp in the advanced settings so i only got that in the Time-field name even though I have submission_date column in the data.

Query 1: Why I am not getting submission_date in the Time-field name?

Query 2: When I selected _timestamp and clicked 'Create', I did not get anything under Available fields in the Discover tab? Why is that so?

Answer 1

Please load data into tutorials_tbl and try these steps as follows.

Step 1: create "tutor" dynamic template with settings and mappings.

{ 
  "order": 0,
  "template": "tutor-*",
  "settings": {
  "index": {
    "number_of_shards": "4",
    "number_of_replicas": "1",
    "refresh_interval": "30s"
    }
  },
"mappings": {

"tutors": {
"dynamic": "true",
"_all": {
"enabled": true
},
"_timestamp": {
"enabled": true,
"format": "yyyy-MM-dd HH:mm:ss"
},
"dynamic_templates": [
{
"disable_string_index": {

"mapping": {
"index": "not_analyzed",
"type": "string"
},
"match_mapping_type": "string",
"match": "*"
}

}
],
"date_detection": false,
"properties": {
"submission_date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss"
},
"tutorial_id": {
"index": "not_analyzed",
"type": "integer"
},
"tutorial_title": {
"index": "not_analyzed",
"type": "string"
},
"tutorial_author": {
"index": "not_analyzed",
"type": "string"
}
}
}
}
}

Step 2: create ES index "tutor" based on tutor-* template ( from Step 1).

I usually use elasticsearch head "Index" tab / "Any request" to create it.

Step 3 : create ES HIVE table with timestamp mapping

CREATE EXTERNAL TABLE tutorials_tbl_es(submission_date STRING ,tutorial_id INT,tutorial_title STRING,tutorial_author STRING)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'   TBLPROPERTIES('es.resource'='tutor/tutors','es.nodes'='saturn:9200','es.mapping.timestamp'='submission_date');

Step 4: insert data from tutorials_tbl to tutorials_tbl_es

INSERT INTO tutorials_tbl_es SELECT * FROM tutorials_tbl LIMIT 1;