Adding a tag to aggregated data in influxdb

user_233507 0
Viewed 1171

I'm having trouble understanding how to add tags to data series as I do SELECT INTO queries. I have an Influxdb of the NYTimes COVID dataset where I've used the cases and deaths fields as fields and the state and county information as tags.

I can aggregate data from neighboring counties in a query like this:

SELECT sum("cases") AS "cases" FROM "ny_covid"."autogen"."value" WHERE ("state"='Pennsylvania') AND ("county"='Philadelphia' OR "county"='Delaware') GROUP BY time(1d) FILL(null)

This works perfectly. But I want to save this aggregated data into a new database for doing other queries. Which I can do like this:

SELECT sum("cases") AS "cases" INTO "new_covid"."autogen"."value" FROM "ny_covid"."autogen"."value" WHERE ("state"='Pennsylvania') AND ("county"='Philadelphia' OR "county"='Delaware') GROUP BY time(1d) FILL(null)

My question is, how do I add a tag like location=Philly to the data I've just inserted into the new_covid database? Because, I'd like to do a few other location level aggregations and it seems like the tag is the way to keep these values distinct.

SELECT sum("cases") AS "cases" INTO "new_covid"."autogen"."value" FROM "ny_covid"."autogen"."value" WHERE ("state"='Pennsylvania') AND ("county"='Dauphin' OR "county"='Lancaster') GROUP BY time(1d) FILL(null)

All of the searching I've done has just been about using the tags in queries or preserving them when copying across databases. But I haven't been able to find anything about attaching tags in SELECT INTO type statements.

influxql influxdb
1 Answers

You can easily get the required functionality by using TICKscript language which is complimentary part of InfluxDB within the TICK stack. However that requires to use Kapacitor and Chronograf in addition to InfluxDB itself.

The Kapacitor will performs tasks written on TICKscript language to manipulate the data. The Chronograf provides a Web UI to configure InfluxDB and Kapacitor.

Find the TICKscript task example for InfluxQL queries from your question:

var b1 = batch
    |query('''
    SELECT sum("cases") AS "cases" 
    FROM "ny_covid"."autogen"."value" 
    WHERE ("state"='Pennsylvania') AND ("county"='Philadelphia' OR "county"='Delaware') )
    ''')
        .offset(1d)
        .period(1d)
        .cron('@midnight')
        .groupBy(time(1d))
    // Write the transformed data to InfluxDB
    |influxDBOut()
        .database('new_covid')
        .retentionPolicy('autogen')
        .measurement('value')
        .tag('location', 'Philly')

var b2 = batch
    |query('''
    SELECT sum("cases") AS "cases" 
    FROM "ny_covid"."autogen"."value" 
    WHERE ("state"='Pennsylvania') AND ("county"='Dauphin' OR "county"='Lancaster')
    ''')
        .offset(1d)
        .period(1d)
        .cron('@midnight')
        .groupBy(time(1d))
    // Write the transformed data to InfluxDB
    |influxDBOut()
        .database('new_covid')
        .retentionPolicy('autogen')
        .measurement('value')
        .tag('location', 'Dauphy')

Also that is not one way to get such result. As well you could examine the stream approach instead of batch used above.

All you need to run this code is create new task for kapacitor:

Chronograf-> Alerting tab -> Manage tasks -> "+Write TICKscript" button in the TICKscripts section.

To get the full TICKstack environment you may use offitial sandbox on dockers

user_85005340
Related