ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION search_classify AS 'org.wikimedia.analytics.refinery.hive.SearchClassifierUDF';

SELECT
    TO_DATE(ts) AS `date`,
    search_classify(uri_path, uri_query) AS api,
    IF(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'generator') IS NOT NULL, 'TRUE', 'FALSE') AS `uses generator`,
    IF(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'list') IS NOT NULL, 'TRUE', 'FALSE') AS `uses list`,
    COUNT(1) AS requests
FROM wmf.webrequest
WHERE
    webrequest_source = 'text'
    AND (
        year = 2016
        OR (year = 2017 AND month = 1 AND day = 1)
        OR (year = 2017 AND month = 1 AND day = 22)
        OR (year = 2017 AND month = 2 AND day = 3)
        OR (year = 2017 AND month = 2 AND day = 5)
        OR (year = 2017 AND month = 2 AND day = 19)
    )
    AND NOT is_pageview
    AND http_status = '200'
    AND ts IS NOT NULL
    AND search_classify(uri_path, uri_query) IN('language', 'cirrus', 'prefix', 'geo', 'open')
GROUP BY
    TO_DATE(ts),
    search_classify(uri_path, uri_query),
    IF(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'generator') IS NOT NULL, 'TRUE', 'FALSE'),
    IF(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'list') IS NOT NULL, 'TRUE', 'FALSE');

library(tidyverse)
api_usage <- read_csv("~/Downloads/query-hive-408.csv")
api_usage %>%
  dplyr::filter(api != "language") %>%
  mutate(date = format(date, "%m/%d"),
         call = case_when(
           .$`uses generator` & !.$`uses list` ~ "that just use generator",
           .$`uses generator` & .$`uses list` ~ "that use both",
           !.$`uses generator` & .$`uses list` ~ "that just use list",
           !.$`uses generator` & !.$`uses list` ~ "that use neither"
         )) %>%
  group_by(date, api, call) %>%
  summarize(requests = sum(requests)) %>%
  ggplot(aes(x = factor(date), y = requests, color = call)) +
  geom_linerange(aes(ymin = 0, ymax = requests),
                 position = position_dodge(width = 1)) +
  geom_text(aes(label = polloi::compress(requests, 1), vjust = "bottom"),
            position = position_dodge(width = 1)) +
  facet_wrap(~ api, nrow = 1, scales = "free_y") +
  scale_color_brewer(palette = "Set1") +
  labs(x = "Date", y = "Requests", color = "API calls",
       title = "Search API usage on certain days in January and February",
       subtitle = "Using the patched UDF (https://gerrit.wikimedia.org/r/#/c/315503/)") +
  theme_minimal(base_family = "Open Sans", base_size = 12) +
  theme(legend.position = "bottom",
        axis.text.y = element_blank(),
        axis.ticks.y = element_blank(),
        axis.line.y = element_blank(),
        panel.grid.major.y = element_blank(),
        panel.grid.minor.y = element_blank(),
        strip.background = element_rect(fill = "gray10"),
        strip.text = element_text(color = "white", size = 12))