I have db
and i have csv
how to join table?
What does “join” mean in this context?
join “IP” from data csv with tags “IP” in influxdb
Not sure about the context, but it sounds like you are thinking of join in terms of relational tables as in RDBMS. The approach of querying influx or tseries DBs is slightly different where the meaurements(tables) are not related in that sense that joins would apply, probably it is possible to construct the query using logical operators to achieve similar effects. The data in the csv file would however need to be in the influxdb as well.
soo how to data csv inner join to influxdb?
This is the case:
We have 2 measurement, sitescope and reference (we want to use this table to reference each ip belong to which service/location)
sitescope format: timestamp, ip as (tag), hostname as (tag), monitor_type as (tag), metric_name as (tag), and metric_value as (field)
Example data: 1526461228, 10.10.x.x, DMxxxx, CPU, utilization, 5
reference: timestamp, ip as (tag), service as (tag), location as (tag), and ref_status as (field)
Example: 1526461228, 10.10.x.x, Petshop, DC1, 1
How to combine / join the table so we can query metric_value based on service/location?
Ok, I see what you mean, thanks for the clarification.
As noted earlier seems no way around this if the measurements are separate, and there are actually no logical operator that allow mimicking a join of sorts.
See this issue: Frequently asked questions | InfluxDB OSS 1.4 Documentation
code :
// Get errors stream data
var tes1 = stream
|from()
.measurement(‘sitescope.ip’)
.groupBy(‘ip’)
// Get views stream data
var tes2 = stream
|from()
.measurement(‘reference.ip’)
.groupBy(‘ip’)
// Join errors and views
tes1
|join(tes2)
.as(‘tes1’, ‘tes2’)
.fill(0.0)
.streamName(‘error_rate’)
|where(lambda: “tes1.ip” == “tes2.ip”)
|eval(lambda: “tes1”, lambda: “tes2”)
.as(‘tes1’, ‘tes2’)
|influxDBOut()
.database(‘telegraf’)
.retentionPolicy(‘autogen’)
.measurement(‘sitescope’)
Thank you very much for your feedback.
Actually we can solve the problem by running script like this with Kapacitor and it’s actually working fine:
stream
|from()
.database(‘telegraf’)
.measurement(‘sitescope’)
.groupBy(‘ip’)
|where(lambda: “ip” == ‘10.10.11.31’ OR
“ip” == ‘10.10.11.32’ OR
“ip” == ‘10.10.12.31’ OR
“ip” == ‘10.10.12.32’)
|eval(lambda: ‘Petshop’)
.as(‘service’)
.tags(‘service’)
.keep()
|delete()
.field(‘service’)
|influxDBOut()
.database(‘telegraf’)
.retentionPolicy(‘autogen’)
.measurement(‘sitescope’)
But the problems are, spesific and hard-coded script like that will be difficult to maintain for long-term in production stage.
Our idea is to make reference table so we only need to update the table instead of the script, so it will be easier to maintain.
What do you think? Is there any other solution?
It seems all potential solutions at the moment can only be found “external” to the database itself due to the known limitation.
The solution you have at the moment seems to produce your desired results atleast with a solution found at another part of the larger stack. I would have also suggested mangling the data with other tools e.g pandas and then storing the data back into influxdb with the desired structure. Again not optimal since that is another script and tool you introduce.
Hi, Alvianno!
I’m new to IndfluxDB, and as fas as i can see - you integrated your sitescope to indluxdb.
Could you please tell me, how did you did it? I’m looking for the best way to collect data from sitescope to influxdb, but i had never used RDBMS to store sitescope data.
Thank you for your help!
Take a look at Kapacitor SideloadNode. It allows to load fields and tags values from a file selected based on a tag value.
Another option is Telegraf Enum processor plugin :
