Combining data using Kapacitor and storing in a table

Hi Folks,

A part of my project wants me to stream data from one table and match the obtained value with another table to get the corresponding message. Then join the data and store it in a third table.

I am using InfluxDB as my database. In InfluxDB I have a table called ErrorMessage and another table called ProcessData. In the ProcessData table, I have a field called ErrorCode which has a corresponding integer value under the field Value. The ErrorMessage table has all the possible error codes and the respective error message. Now I want to stream Value from the ProcessData table and find the corresponding error message from the ErrorMessage table. Then I want to store the result in another table called ErrorLog.

This is how the ProcessData table looks:

=====================================================
|timestamp          |Field               |Value     |
=====================================================
|123456789          |ErrorCode           |2         |
|123456788          |ErrorCode           |4         |
|123456787          |ErrorCode           |6         |
|123456786          |ErrorCode           |8         |
=====================================================

The ErrorMessage looks like this:

=============================================
|timestamp          |ErrorCode   |Message   |
=============================================
|123456789          |1           |Msg_1     |
|123456788          |2           |Msg_2     |
|123456787          |3           |Msg_3     |
|123456786          |4           |Msg_4     |
|123456786          |5           |Msg_5     |
|123456786          |6           |Msg_6     |
|123456786          |7           |Msg_7     |
|123456786          |8           |Msg_8     |
|123456786          |9           |Msg_9     |
|123456786          |10          |Msg_10    |
=============================================

The ErrorLog table should look like this:

=============================================
|timestamp          |Value       |Message   |
=============================================
|123456789          |2           |Msg_2     |
|123456788          |4           |Msg_4     |
|123456787          |6           |Msg_6     |
|123456786          |8           |Msg_8     |
=============================================

How am I supposed to achieve this using tick-script?

Thanks in advance.

Cheers, SD

1 Like

You appear to be trying to use InfluxDB as a Relational Database Management
System (RDBMS), which it isn’t.

InfluxDB is a Time Series Database (TSDB) which does not support joins or
relations between tables.

You may find the following documentation helpful in explaining the differences:
https://docs.influxdata.com/influxdb/v1.8/concepts/crosswalk/

Regards,

Antony.

1 Like

Hi @Pooh,

Thanks for the reply. I went through the document. It reads:

However InfluxQL is not SQL and lacks support for more advanced operations like UNION , JOIN and HAVING that SQL power-users are accustomed to. This functionality is available with Flux.

So is it not possible to achieve this JOIN or UNION functionality using Kapacitor. I saw they have this functionality. I want to know how exactly should I do it, if possible!

Cheers,
SD

Hi,

What if table creation is not possible for some reason and I just want to display the data. I want to reference one query to another query. Here is what I mean.

Query A shows the Error Code and Query B shows the Error text. You will notice that in Query B the value “12832” has been written manually. I want to reference it from the result of Query A. How can I do it?

Cheers,
SD

1 Like