I’m running a HomeAssistant instance at home.
I’ve configured it to log data into an InfluxDB database,
so I can retrieve historical data for analysis later on. In default mode
HomeAssistant would aggregate historical data for storage reasons.
So now I want to access the InfluxDB database from R to perform custom analyses.
HomeAssistant is still using InfluxDB version 1. To connect to InfluxDB from R,
I thought I can use the influxdbr package. But I got some errors because
this package seems to be outdated.
Here’s what I get, when fetching data:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
library(influxdbr)
library(tidyverse)
influx_con <- influx_connection(
scheme = "http", host = "my_influx_host", port = 8086,
user = "my_influx_user", pass = "my_influx_pass"
)
query <- 'SELECT entity_id, value FROM "autogen"."°C" WHERE time > now() - 5d'
influx_query(influx_con,
db = "my_influx_db",
query = query,
)
|
The error message is:
1
2
3
4
5
6
7
8
9
10
11
|
Error in `purrr::map()`:
ℹ In index: 1.
Caused by error in `purrr::map()`:
ℹ In index: 1.
ℹ With name: results.
Caused by error in `purrr::map()`:
ℹ In index: 1.
Caused by error:
! The `validate` argument of `as_tibble()` was deprecated in tibble 2.0.0 and is now defunct.
ℹ Please use the `.name_repair` argument instead.
Run `rlang::last_trace()` to see where the error occurred.
|
It seems that the influxdbr package is not compatible with the current version
of the tibble package. The latest commit is 5 years old.
So I decided to use the httr package to send HTTP requests directly to the InfluxDB API.
Here’s how to do it:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
library(tidyverse)
library(httr)
library(jsonlite)
query <- 'SELECT entity_id, value FROM "autogen"."°C" WHERE time > now() - 5d'
response <- GET(
url = paste0("my_influx_host", "/query"),
query = list(
db = "my_influx_db",
q = query,
u = "my_influx_user",
p = "my_influx_pass"
),
config = config(ssl_verifypeer = TRUE)
)
if (response$status_code == 200) {
data_raw <- content(response, as = "text", encoding = "UTF-8") %>% fromJSON(flatten = TRUE)
}
|
The data structure looks a little bit complicated:
1
2
3
4
5
6
7
8
9
10
|
## List of 1
## $ results:'data.frame': 1 obs. of 2 variables:
## ..$ statement_id: int 0
## ..$ series :List of 1
## .. ..$ :'data.frame': 1 obs. of 3 variables:
## .. .. ..$ name : chr "°C"
## .. .. ..$ columns:List of 1
## .. .. .. ..$ : chr [1:3] "time" "entity_id" "value"
## .. .. ..$ values :List of 1
## .. .. .. ..$ : chr [1:44176, 1:3] "2025-11-11T13:20:31.171165Z" "2025-11-11T13:20:43.741052Z" "2025-11-11T13:20:43.741159Z" "2025-11-11T13:20:44.552422Z" ...
|
But here starts the magic of tidyverse. The interesting data is stored in
data_raw$results[[1]]$series[[1]]$values[[1]]. We can get it using pluck()
and convert it to a data.frame:
1
2
3
|
data_raw_df <- data_raw %>%
pluck("results", "series", 1, "values", 1) %>%
as.data.frame(stringsAsFactors = FALSE)
|
1
2
|
data_raw_df %>%
head(10)
|
1
2
3
4
5
6
7
8
9
10
11
|
## V1 V2 V3
## 1 2025-11-11T13:20:31.171165Z Temp_5 25.875
## 2 2025-11-11T13:20:43.741052Z Temp_11 30.625
## 3 2025-11-11T13:20:43.741159Z Temp_10 48.75
## 4 2025-11-11T13:20:44.552422Z Temp_8 28.25
## 5 2025-11-11T13:20:44.552522Z Temp_9 28.25
## 6 2025-11-11T13:20:50.225514Z Temp_2 19.8
## 7 2025-11-11T13:21:07.61594Z Temp_15 26.5625
## 8 2025-11-11T13:21:07.616039Z Temp_14 22.9375
## 9 2025-11-11T13:21:10.101543Z Temp_13 26.75
## 10 2025-11-11T13:21:10.911298Z Temp_12 23.5
|
Now we can change the column names (they are stored in
data_raw %>% pluck("results", "series", 1, "columns", 1)) and convert the time column to POSIXct:
1
2
3
4
5
6
7
|
data_raw_df %>%
set_names(data_raw %>% pluck("results", "series", 1, "columns", 1)) %>%
mutate(
time = as.POSIXct(time, format = "%Y-%m-%dT%H:%M:%OSZ", tz = "UTC"),
value = as.numeric(value)
) %>%
str()
|
1
2
3
4
|
## 'data.frame': 44176 obs. of 3 variables:
## $ time : POSIXct, format: "2025-11-11 13:20:31" "2025-11-11 13:20:43" ...
## $ entity_id: chr "Temp_5" "Temp_11" "Temp_10" "Temp_8" ...
## $ value : num 25.9 30.6 48.8 28.2 28.2 ...
|