redash_refresh.R

# cf. https://gist.github.com/arikfr/e3e434d8cfd7f331d499ccf351abbff9

library(httr)

get_fresh_query_result <-
  function(redash_url,
           query_id,
           user_api_key,
           params) {
    poll_job <- function(redash_url, job_id) {
      jobs_url <- sprintf("%s/api/jobs/%s", redash_url, job_id)
      # TODO: add timeout
      while (job_status != 3 && job_status != 4) {
        jobs_resp <- GET(jobs_url, add_headers(Authorization = auth_value))
        content(jobs_resp)
        job_status <- content(jobs_resp)$job$status
        result_id <- content(jobs_resp)$job$query_result_id
        Sys.sleep(1)
      }
      if (job_status == 4) {
        return(NULL)
      }
      
      return(result_id)
    }
    
    # refresh
    refresh_url <-
      sprintf("%s/api/queries/%s/refresh", redash_url, query_id)
    auth_value <- sprintf("Key %s", user_api_key)
    refresh_resp <-
      POST(refresh_url, query = params, add_headers(Authorization = auth_value))
    if (refresh_resp$status_code != 200) {
      stop("Refresh failed.")
    }
    str(content(refresh_resp))
    content(refresh_resp)
    
    # getting query_result_id
    job_id <- content(refresh_resp)$job$id
    query_result_id <- poll_job(redash_url, job_id)
    if (is.null(query_result_id)) {
      stop("Query execution failed.")
    }
    
    # getting results
    results_url <-
      sprintf("%s/api/queries/%s/results/%s.csv",
              redash_url,
              query_id,
              query_result_id)
    results_resp <-
      GET(results_url, add_headers(Authorization = auth_value))
    if (results_resp$status_code != 200) {
      stop("Failed getting results.")
    }
    
    content(results_resp)
    return(results_resp)
  }

redash_url <- "https://..."
query_id <- "1234"
params <- list(p_param = "1243")
# Need to use a *user API key* here (and not a query API key).
user_api_key <- "..."

results_resp <-
  get_fresh_query_result(redash_url, query_id, user_api_key, params)

results_csv <- content(results_resp)

head(results_csv)
View(results_csv)