Reads data from OpenTSDB into an R data frame.

read_opentsdb.r

# Loads data from OpenTSDB.
#
#   `server.url`: The URL of the OpenTSDB server (e.g. 'http://tsdb.example.com')
#   `metrics`: List of strings specifying the metrics to pull (e.g.
#       c("avg:web.cpu.user{host=*}", "sum:web.requests{type=login}")
#   )
#   `start.dt`: How far to go back in the time series, in any format OpenTSDB
#       (e.g. "4h-ago")
#   `end.dt`: Where to end the results (defaults to now)
#   `datify`: Whether to convert timestamps to POSIXlt objects (defaults to TRUE)
# 
# Returns a data frame like
#
#           metric  timestamp  value host     type
#   1 web.cpu.user 1367769600   31.1 web01    <NA>
#   2 web.cpu.user 1367769603   14.2 web03    <NA>
#   3 web.requests 1367769610 219.46 <NA>    login
#   4 web.cpu.user 1367769610   34.8 web01    <NA>
#
read.opentsdb <- function(server.url, metrics, start.dt, end.dt=NA, datify=T) {
    library(reshape2)

    metric.serial <- paste(metrics, collapse="&m=");
    query.url <- paste(server.url,
        "/q?ascii&m=", metric.serial,
        "&start=", start.dt,
        sep="")
    if (! is.na(end.dt)) {query.url <- paste(query.url, "&end=", end.dt, sep="")}

    # Need to give col.names to make sure that read.delim2 doesn't get confused about
    # rows with more tags that occur later in the file
    raw.data <- read.delim2(query.url, sep=" ", fill=T, header=F,
        col.names=c("metric", "timestamp", "value", paste("tag", 1:10, sep=""))
    )
    raw.data$timestamp <- as.numeric(raw.data$timestamp)
    raw.data$value <- as.numeric(raw.data$value)

    # Reformat the tags into per-tag columns
    m <- melt(raw.data, id.vars=1:3, value.name="tag")
    m <- na.omit(m)
    m <- subset(m, tag != "")
    m$tag.name <- sub("=.*", "", m$tag)
    m$tag.value <- sub(".*=", "", m$tag)
    m <- (dcast(metric + timestamp + value ~ tag.name, data=m, value.var="tag.value"))

    if (datify) { m$timestamp = as.POSIXlt(m$timestamp, origin="1970-01-01") }
    return(m)
}