Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

How to export data from InfluxDB #16

Closed
agross opened this issue Nov 28, 2019 · 9 comments
Closed

How to export data from InfluxDB #16

agross opened this issue Nov 28, 2019 · 9 comments

Comments

@agross
Copy link

agross commented Nov 28, 2019

Hi,

thank you for creating Kotori, it is really useful.

I have a setup that uses weewx to acquire weather data, saves it to InfluxDB and displays graphs via Grafana.

So far everything works as expected:

[wetter]
enable      = true
type        = application
realm       = wetter
mqtt_topics = wetter/#
application = kotori.daq.application.mqttkit:mqttkit_application

# How often to log metrics
metrics_logger_interval = 60

My question is about data export which currently yields Connection reset by peer regardless of the requested URL.

[wetter.influx-data-export]
enable          = true

type            = application
application     = kotori.io.protocol.forwarder:boot

realm           = wetter
source          = http:/api/{realm:wetter}/{network:.*}/{gateway:.*}/{node:.*}/{slot:(data|event)}.{suffix} [GET]
target          = influxdb:/{database}?measurement={measurement}
transform       = kotori.daq.intercom.strategies:WanBusStrategy.topology_to_storage,
                  kotori.io.protocol.influx:QueryTransformer.transform
[kotori.io.protocol.forwarder       ] INFO    : Starting ProtocolForwarderService(wetter.influx-data-export-forwarder)
[kotori.io.protocol.forwarder       ] INFO    : Forwarding payloads from http:/api/{realm:wetter}/{network:.*}/{gateway:.*}/{node:.*}/{slot:(data|event)}.{suffix} [GET] to influxdb:/{database}?measurement={measurement}
[kotori.io.protocol.http            ] INFO    : Initializing HttpChannelContainer
[kotori.io.protocol.http            ] INFO    : Connecting to Metadata storage database
[kotori.io.protocol.http            ] INFO    : Starting HTTP service on localhost:24642
[kotori.io.protocol.http.LocalSite  ] INFO    : Starting factory <kotori.io.protocol.http.LocalSite instance at 0x7fcb3495b1e0>
[kotori.io.protocol.http            ] INFO    : Registering endpoint at path '/api/{realm:wetter}/{network:.*}/{gateway:.*}/{node:.*}/{slot:(data|event)}.{suffix}' for methods [u'GET']
[kotori.io.protocol.target          ] INFO    : Starting ForwarderTargetService(wetter-wetter.influx-data-export) for serving address influxdb:/{database}?measurement={measurement} []

$ curl http://localhost:24642/api/wetter/de/ogd/oben_sensors/data.csv
curl: (56) Recv failure: Connection reset by peer

The InfluxDB database is called wetter_de. Grafana runs queries like these: SELECT mean(windSpeed_kph) FROM wetter_de.autogen.ogd_oben_sensors WHERE time >= now() - 5m GROUP BY time(500ms) successfully.

I don't know how I need to construct the data export URL to get data from InfluxDB. As far as I understand from the docs http://localhost:24642/api/wetter/de/ogd/oben_sensors/data.csv is transformed as follows:

  • wetter/de is translated to the wetter_de database,
  • ogd/oben_sensors is translated to the ogd_oben_sensors measurement

Unfortunately, Kotori does not log my HTTP requests and their transformations. InfluxDB also does not log any failing queries. Can you help me find what goes wrong?

I'm also not sure why I need to specify the realm twice in the export ini:

realm           = wetter
source          = http:/api/{realm:wetter}/...
@agross
Copy link
Author

agross commented Nov 28, 2019

FYI, this is the weewx MQTT config:

    [[MQTT]]
        server_url = mqtt://mqtt:1883/
        topic = wetter/de/ogd/oben
        unit_system = METRIC
        binding = loop

@amotl
Copy link
Member

amotl commented Nov 28, 2019 via email

@agross
Copy link
Author

agross commented Nov 28, 2019

Thanks for the reply.

I can confirm that the port is not blocked.

$ netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:24642         0.0.0.0:*               LISTEN      1/python
tcp        0      0 127.0.0.11:38419        0.0.0.0:*               LISTEN      -
udp        0      0 127.0.0.11:42484        0.0.0.0:*                           -

$ curl -4v http://localhost:24642/api/wetter/de/ogd/oben_sensors/data.csv
*   Trying 127.0.0.1:24642...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 24642 (#0)
> GET /api/wetter/de/ogd/oben/data.csv HTTP/1.1
> Host: localhost:24642
> User-Agent: curl/7.66.0
> Accept: */*
>
* Recv failure: Connection reset by peer
* Closing connection 0
curl: (56) Recv failure: Connection reset by peer

@amotl
Copy link
Member

amotl commented Nov 28, 2019 via email

@amotl
Copy link
Member

amotl commented Nov 28, 2019 via email

@agross
Copy link
Author

agross commented Nov 29, 2019

When I set up two real weather stations yesterday I found this in the Kotori log. It refers to the realm, network, etc., probably the same information that is used to construct the export URL.

2019-11-29T02:01:15+0000 [kotori.daq.services.mig            ] ERROR   : Grafana provisioning failed for storage={"node": "unten", "slot": "loop", "realm": "wetter", "network": "de", "database": "wetter_de", "measurement_events": "ogd_unten_events", "label": "ogd_unten", "measurement": "ogd_unten_sensors", "gateway": "ogd"}, message={u'windSpeed10_kph': 11.265408,...

Both of my weather stations post their respective data to the /wetter/de/ogd/{unten,oben} topic.

@agross
Copy link
Author

agross commented Nov 29, 2019

🤦‍♂

Inside my Kotori docker container:

root@80232691a26f:/# netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:24642         0.0.0.0:*               LISTEN      1/python
...

Note the 127.0.0.1:24642 part.

I mapped that port on the docker host and my requests above were made from outside of the container. Given that from the container's perspective the request does not come from localhost (rather from the docker host, which is != localhost) it had no choice other than to reset the connection.

Please accept my apologies for disturbing your vacation!

@agross agross closed this as completed Nov 29, 2019
@amotl
Copy link
Member

amotl commented Nov 29, 2019

Dear Alexander,

so everything is fine now? I was about to answer on the issue referenced above outlining an eventual problem with Kotori not being able to talk to Grafana and then regarding your dockerized setup.

As I see you've closed the issue already, I am believing that everything is all right now? Please feel free to create another issue as you need.

Regarding dockerization, I will just go ahead and do so.

With kind regards,
Andreas.

@agross
Copy link
Author

agross commented Nov 29, 2019

Hi Andreas, thanks for the follow-up, it's fine and I was able to export data using curl. I can share my dockerfiles if you're interested. It was quite challenging to find a compatible base image. (I'm not a Python dev myself.)

@amotl amotl mentioned this issue Nov 29, 2019
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@agross @amotl