Querying our InfluxDB databases interactively

influxdb

#1

Starting 2019, we are offering direct access to our InfluxDB databases to query them interactively.
In the following sections, we want to show appropriate examples about querying InfluxDB.

We ask for your understanding that we will permit access to a limited amount of people who might be interested for research and testing purposes and who we are already familiar with. Thus, this is not a complete public offering yet.

If you feel eligible for InfluxDB access, feel free to get back to us to request an account.


Getting started with Flux
Upgrade to InfluxDB 1.7.2 and Grafana 5.4.2
#2

Authentication howto

Before querying data, you should login to the system. As we usually recommend HTTPie for talking to HTTP servers, these are the canonical examples for doing so:

Authenticate with swarm.hiveeyes.org

http --session=hiveeyes https://swarm.hiveeyes.org/influx/ --auth <username>:<password>

Authenticate with weather.hiveeyes.org

http --session=hiveeyes https://weather.hiveeyes.org/influx/ --auth <username>:<password>

Get access credentials

Please get back to us for receiving your personal access credentials by starting to Introduce yourself - Hiveeyes or by sending a respective email to hello@hiveeyes.org.


#3

Submit InfluxQL query expression

Request

http --session=hiveeyes https://swarm.hiveeyes.org/influx/query \
    db==hiveeyes_open_hive_clemens \
    q=='SELECT * FROM default_2_sensors LIMIT 3'

Response headers

HTTP/1.1 200 OK
Connection: keep-alive
Content-Encoding: gzip
Content-Type: application/json
Date: Tue, 08 Jan 2019 18:15:43 GMT
Request-Id: 696151bb-1371-11e9-9b27-00163e08ddd3
Server: nginx/1.10.3
Strict-Transport-Security: max-age=15768000
Transfer-Encoding: chunked
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.7.2
X-Request-Id: 696151bb-1371-11e9-9b27-00163e08ddd3

Response body

{
    "results": [
        {
            "series": [
                {
                    "columns": [
                        "time",
                        "Aussen-Feuchtigkeit",
                        "Aussen-Temperatur",
                        "Gewicht",
                        "Spannung"
                    ],
                    "name": "default_2_sensors",
                    "values": [
                        [
                            "1999-12-31T23:12:41Z",
                            31.6,
                            21.5,
                            0.5,
                            4.53
                        ],
                        [
                            "2016-08-14T19:02:06Z",
                            56.1,
                            19.6,
                            58.697,
                            4.13
                        ],
                        [
                            "2016-08-14T19:22:06Z",
                            58.3,
                            19.4,
                            58.663,
                            4.13
                        ]
                    ]
                }
            ],
            "statement_id": 0
        }
    ]
}

#4

Submit Flux query expression

Request

# Define Flux expression
expression='
from(bucket:"hiveeyes_open_hive_clemens")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "default_2_sensors")
  |> last()
'

# Submit to InfluxDB
echo $expression | \
    http --session=hiveeyes \
    https://swarm.hiveeyes.org/influx/api/v2/query Content-Type:application/vnd.flux

Response headers

HTTP/1.1 200 OK
Connection: keep-alive
Content-Encoding: gzip
Content-Type: text/csv; charset=utf-8
Date: Tue, 08 Jan 2019 18:33:25 GMT
Request-Id: e2dedf54-1373-11e9-9cb8-00163e08ddd3
Server: nginx/1.10.3
Strict-Transport-Security: max-age=15768000
Transfer-Encoding: chunked
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.7.2
X-Request-Id: e2dedf54-1373-11e9-9cb8-00163e08ddd3

Response body

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string
#group,false,false,true,true,false,false,true,true
#default,_result,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement
,,0,2019-01-08T17:33:25.99352161Z,2019-01-08T18:33:25.99352161Z,2019-01-08T18:26:40Z,99.9,Aussen-Feuchtigkeit,default_2_sensors
,,1,2019-01-08T17:33:25.99352161Z,2019-01-08T18:33:25.99352161Z,2019-01-08T18:26:40Z,5.8,Aussen-Temperatur,default_2_sensors
,,2,2019-01-08T17:33:25.99352161Z,2019-01-08T18:33:25.99352161Z,2019-01-08T18:26:40Z,8.259,Gewicht,default_2_sensors
,,3,2019-01-08T17:33:25.99352161Z,2019-01-08T18:33:25.99352161Z,2019-01-08T18:26:40Z,3.52,Spannung,default_2_sensors

#5

Important note for querying large buckets with Flux

Please be aware that we host different databases with largely varying cardinality numbers. While beekeeping data is usually low-frequency and low-traffic, thus usually not leading to high cardinality numbers, the weather- and environmental data collection endeavors are operating with databases having way higher cardinality numbers.

When using InfluxQL, the measurement/table name is encoded into the "FROM <measurement>" part of the SQL query expression, thus obligatory.

However, when using the Flux language, the measurement name can be omitted because you are allowed to query whole buckets across all measurements. As this might stall on large databases/buckets due to the amount of data to be churned through, you might think about limiting a Flux query to specific measurements.

Examples

DWD CDC

Our observations are that a query like

from(bucket:"dwd_cdc")
  |> range(start: -1h)
  |> count()

runs into a timeout, even when increasing the timeout value like "--timeout=300".

On the other hand, a query like

from(bucket:"dwd_cdc")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "dwd_cdc_temp_2m_c"
  )
  |> count()

works perfectly within a response time of about a second or lower.


Full commandline example

# Define Flux expression
expression='
from(bucket:"dwd_cdc")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "dwd_cdc_temp_2m_c")
  |> last()
'

# Submit to InfluxDB
echo $expression | \
    http --session=hiveeyes \
    https://weather.hiveeyes.org/influx/api/v2/query Content-Type:application/vnd.flux

#6

luftdaten.info

While it actually does work for the dwd_cdc bucket, it does not work for the luftdaten_info bucket. Even a query like

from(bucket:"luftdaten_info")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "earth_43_sensors"
  )
  |> count()

runs into the timeout of 5 minutes.

However, an InfluxQL query like

SELECT count(*) FROM earth_43_sensors WHERE time > now()-1h;

works perfectly and returns its results in ~1.5 seconds.

I’m feeling a bit silly now.