i have a time series data of past 15 years in a cassandra table. The data points are separated by 1 minute equal intervals (hence each day have 1440 data points, and the first data point on each day starts at "yyyy-mm-dd 00:00:00" and the last point is "yyyy-mm-dd 23:59:00"), therefore the timestamp is divided into two separate columns as "date" (partition key) and "time" (primary key). And the table is then pipe lined to elasticsearch.
But while creating kibana index pattern for the data, in Time Filter field name drop down, I could able to select only the "date", but not along with "time" field, so I selected only "date" field and created index pattern.
Now can this be treated as time series data set in kibana.
How to use both "date" and "time" as we use @timestamp to plot on timelion
Hey @reddihareesh, Kibana doesn't support having separate "date" and "time" fields. Can you reindex your data to combine the date and time into a single "date" field? The date datatype actually stored dates and times with millisecond accuracy.
Thank you. And where do you mean to re-index, at elasticsearch or table in my cassandra data base.
(Actually in cassandra there are separate datatypes for both [date and time])(https://docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/refDataTypes.html#refDataTypes__date)
I have created my cassandra table with the following create-query, but when check the mappings in elasticsearch i found that datatype of the time field is changed to text (and some other fields got attached) as you can see. So is re-indexing is the only solution?
CREATE TABLE srldc.test_dupes_2year (
date date,
time time,
alpur_ap_line_mw double,
alpur_ka_line_mw double,
PRIMARY KEY (date, time)
)
[http://localhost:9200/test_du*/_mapping](http://)
{
"test_dupes_2year": {
"mappings": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"alpur_ap_line_mw": {
"type": "float"
},
"alpur_ka_line_mw": {
"type": "float"
},
"date": {
"type": "date"
},
"time": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}Hey @reddihareesh, Kibana only reads data from Elasticsearch, so you'll want to switch to a date and time field in Elasticserach. You don't have to change your Cassandra configuration.
Thank you @Brandon_Kobel. I have reindexed my data by combining the date and time fields to form a field called datetime whose details are..
"datetime" : {
"type" : "date",
"format" : "date_hour_minute_second"
}
GET my_reindex_data
{
"my_reindex_data" : {
"aliases" : { },
"mappings" : {
"properties" : {
"date" : {
"type" : "date"
},
"datetime" : {
"type" : "date",
"format" : "date_hour_minute_second"
},
"time_stamp" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}
GET my_reindex_data/_search
"time_stamp" : "51960000000000",
"date" : "2016-02-03",
"datetime" : "2016-02-03T14:26:00"
But on Discovery (in all), when I click on (+) or (-) on top 5 values of datetime field, it gives following error (but if I write filters in this way datetime>= "2016-01-22T10:00:00" and datetime <= "2016-01-22T10:29:00" it works) . Am I doing anything wrong?
Error loading data
Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"srldc_test_2years_data","node":"dyeCfRm-TMi9a7IUx9fbKQ","reason":{"type":"query_shard_exception","reason":"failed to create query: {\n \"bool\" : {\n \"must\" : [\n {\n \"match_phrase\" : {\n \"datetime\" : {\n \"query\" : \"2016-01-22T10:29:00.000Z\",\n \"slop\" : 0,\n \"zero_terms_query\" : \"NONE\",\n \"boost\" : 1.0\n }\n }\n },\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : null,\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n ],\n \"filter\" : [\n {\n \"bool\" : {\n \"filter\" : [\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : \"2016-01-22T10:00:00\",\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"time_zone\" : \"UTC\",\n \"boost\" : 1.0\n }\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : null,\n \"to\" : \"2016-01-22T10:29:00\",\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"time_zone\" : \"UTC\",\n \"boost\" : 1.0\n }\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n}","index_uuid":"kCVD5sELRVKXcWbhYPGEQw","index":"srldc_test_2years_data","caused_by":{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]","caused_by":{"type":"illegal_argument_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]","caused_by":{"type":"date_time_parse_exception","reason":"Text '2016-01-22T10:29:00.000Z' could not be parsed, unparsed text found at index 19"}}}}}],"caused_by":{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]","caused_by":{"type":"illegal_argument_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]","caused_by":{"type":"date_time_parse_exception","reason":"Text '2016-01-22T10:29:00.000Z' could not be parsed, unparsed text found at index 19"}}}},"status":400}This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.