Define OpenSearch® timestamp data in SQL pipeline#
Warning
As with many beta products, we are currently redesigning the Aiven for Apache Flink® experience, APIs, and CLI calls.
We are also updating all examples in the documentation to reflect these changes. During this process, you may encounter error messages as we introduce new improvements.
Frequently results in Apache Flink® data pipelines include one or more timestamps, either contained in the source events or generated by window aggregations.
When the output of the Apache Flink® data pipeline is an Aiven for OpenSearch® index, you need to convert the Flink timestamps to a format recognizable by OpenSearch®, otherwise they will be interpreted as strings, losing the benefits of time filtering.
OpenSearch® recognises the following as correct date/time formats:
yyyy/MM/dd
for a date fieldHH:mm:ss
for a time fieldyyyy/MM/dd HH:mm:ss
for a timestamp field
Therefore you need to structure the data pipeline output to follow one of the acceptable formats
Define Apache Flink® target tables including timestamps for OpenSearch®#
When the result of the data pipeline contains a timestamp column like the below:
EVENT_TIME TIMESTAMP(3),
HOSTNAME STRING,
CPU DOUBLE
to push the data correctly to an OpenSearch® index, you’ll need to set the target column format as STRING
in the Flink table definition, like:
EVENT_TIME STRING,
HOSTNAME STRING,
CPU DOUBLE
and, assuming the EVENT_TIME
is a timestamp, you’ll need to specify it in the format understood by OpenSearch® using the DATE_FORMAT
function, like:
DATE_FORMAT(EVENT_TIME, 'yyyy/MM/dd HH:mm:ss')
Once the pipeline is running, you can check that the EVENT_TIME
field in OpenSearch® is recognized as a timestamp.