ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION is_spider as 'org.wikimedia.analytics.refinery.hive.IsSpiderUDF';
CREATE TEMPORARY FUNCTION ua_parse as 'org.wikimedia.analytics.refinery.hive.GetUAPropertiesUDF';
CREATE TEMPORARY FUNCTION host_parse as 'org.wikimedia.analytics.refinery.hive.GetHostPropertiesUDF';
USE wmf_raw;
WITH wiki_map AS (
  SELECT DISTINCT
    dbname AS wiki_id,
    host_parse(hostname) AS normalized_hostname,
    namespace_localized_name AS localized_namespace,
    namespace_canonical_name AS canonical_namespace
  FROM mediawiki_project_namespace_map
  WHERE snapshot = '2017-04' AND namespace_canonical_name RLIKE '^[A-Za-z\\s]+$'
),
cirrus_searches AS (
  SELECT
    wikiid AS wiki_id,
    source,
    requests[size(requests)-1].querytype AS query_type,
    REGEXP_EXTRACT(requests[size(requests)-1].query, '^([^\\:]+)\\:{1}[^\\:]+.*', 1) AS localized_namespace,
    COUNT(1) AS search_count
  FROM CirrusSearchRequestSet
  WHERE
    year = 2017 AND month = 5 AND day = 23
    AND requests[size(requests)-1].query RLIKE  '^([^\\:]+)\\:{1}[^\\:]+.*'
    -- Filter out bots (not all, but still many):
    AND NOT (
      ua_parser(useragent)['device_family'] = 'Spider'
      OR is_spider(useragent)
      OR ip = '127.0.0.1'
      OR useragent RLIKE 'https?://'
      OR INSTR(useragent, 'www.') > 0
      OR INSTR(useragent, 'github') > 0
      OR LOWER(useragent) RLIKE '([a-z0-9._%-]+@[a-z0-9.-]+\\.(com|us|net|org|edu|gov|io|ly|co|uk))'
      OR (
        ua_parser(useragent)['browser_family'] = 'Other'
        AND ua_parser(useragent)['device_family'] = 'Other'
        AND ua_parser(useragent)['os_family'] = 'Other'
      )
    )
  GROUP BY
    wikiid, source,
    requests[size(requests)-1].querytype,
    REGEXP_EXTRACT(requests[size(requests)-1].query, '^([^\\:]+)\\:{1}[^\\:]+.*', 1)
)
SELECT
  normalized_hostname.project_class AS project,
  normalized_hostname.project AS language,
  source, query_type, canonical_namespace AS namespace_searched,
  search_count
FROM cirrus_searches
INNER JOIN wiki_map
  ON cirrus_searches.wiki_id = wiki_map.wiki_id
  AND cirrus_searches.localized_namespace = wiki_map.localized_namespace;

ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION is_spider as 'org.wikimedia.analytics.refinery.hive.IsSpiderUDF';
CREATE TEMPORARY FUNCTION ua_parse as 'org.wikimedia.analytics.refinery.hive.GetUAPropertiesUDF';
CREATE TEMPORARY FUNCTION host_parse as 'org.wikimedia.analytics.refinery.hive.GetHostPropertiesUDF';
WITH searches AS (
  SELECT DISTINCT
    id, wikiid AS wiki_id,
    STR_TO_MAP(payload['queryString'], '&', '=') AS query_map
  FROM wmf_raw.cirrussearchrequestset
  WHERE
    year = 2017 AND month = 5 AND day = 31 AND hour = 23
    AND source = 'web'
    AND NOT (
      useragent IS NULL
      OR useragent = ''
      OR ua_parse(useragent)['device_family'] = 'Spider'
      OR is_spider(useragent)
      OR ip = '127.0.0.1'
      OR useragent RLIKE 'https?://'
      OR INSTR(useragent, 'www.') > 0
      OR INSTR(useragent, 'github') > 0
      OR LOWER(useragent) RLIKE '([a-z0-9._%-]+@[a-z0-9.-]+\\.(com|us|net|org|edu|gov|io|ly|co|uk))'
      OR (
        ua_parse(useragent)['browser_family'] = 'Other'
        AND ua_parse(useragent)['device_family'] = 'Other'
        AND ua_parse(useragent)['os_family'] = 'Other'
      )
    )
    AND INSTR(payload['queryString'], 'profile=advanced') > 0
    AND INSTR(payload['queryString'], 'fulltext=1') > 0
  LIMIT 100
),
exploded_searches AS (
  SELECT
    s.id, s.wiki_id,
    exp.key AS query_key,
    exp.val AS query_value
  FROM searches s
  LATERAL VIEW EXPLODE(s.query_map) exp AS key, val
),
wiki_map AS (
  SELECT DISTINCT
    dbname AS wiki,
    host_parse(hostname) AS normalized_hostname,
    CONCAT('ns', namespace) AS ns,
    namespace_canonical_name AS canonical_namespace
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2017-04' AND namespace_canonical_name RLIKE '^[A-Za-z\\s]+$'
)
SELECT
  project, language,
  id AS search_id,
  namespace AS namespace_searched
FROM (
  SELECT
    es.id AS id, es.wiki_id AS wiki,
    wm.normalized_hostname.project_class AS project,
    wm.normalized_hostname.project AS language,
    wm.canonical_namespace AS namespace
  FROM exploded_searches es
  LEFT JOIN wiki_map wm
    ON es.wiki_id = wm.wiki
    AND es.query_key = wm.ns
) ns
WHERE namespace IS NOT NULL;

project	language	search_id	namespace_searched
wikipedia	en	qphgphgx9aducocvfql7kl09	MediaWiki
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	Portal
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	Project
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	Anexo
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	Gadget definition
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	Gadget
wikipedia	es	elek4r79vho4h4hn8pke3pmgz	File
wikipedia	fr	c7b6bxjm7ggbd17478yrzly90	Help
wikipedia	es	be6eyaoy1n4slvg3qvrzbso23	Project
wikipedia	es	be6eyaoy1n4slvg3qvrzbso23	Help
wikipedia	es	be6eyaoy1n4slvg3qvrzbso23	Template
wikimedia	commons	alb6fbdmyrloy04ht282scu70	Category
wikimedia	commons	alb6fbdmyrloy04ht282scu70	File

library(tidyverse)

namespaces <- read_tsv("~/Desktop/namespace-searches-edited.tsv")
# ^ not attached in the comment

data("ISO_639_2", package = "ISOcodes")

aggregates <- namespaces %>%
  arrange(project, language, search_id, namespace_searched) %>%
  group_by(project, language, search_id) %>%
  mutate(namespaces = paste0(namespace_searched, collapse = ", ")) %>%
  group_by(project, language, namespaces) %>%
  count %>%
  mutate(proportion = round(n/sum(n), 4)) %>%
  left_join(select(ISO_639_2, c(Alpha_2, Name)),
            by = c("language" = "Alpha_2")) %>%
  select(project, lang = language, language = Name, namespaces, searches = n, proportion) %>%
  arrange(project, lang, desc(searches))

write_csv(aggregates, "~/Desktop/namespace-searches-counts.csv")

FROM wmf_raw.mediawiki_project_namespace_map
WHERE snapshot = '2017-04' AND namespace_canonical_name RLIKE '^[A-Za-z\\s]+$'

export HADOOP_HEAPSIZE=1024
hive -S \
  -d year='2017' \
  -d month='8' \
  -d day='1' \
  -f ~/namespaces.hql \
  2> /dev/null | grep -v parquet.hadoop | grep -v WARN: \
  > ~/namespace-searches.tsv

library(tidyverse)

namespaces <- read_tsv("~/namespace-searches.tsv") # not attached

data("ISO_639_2", package = "ISOcodes")

aggregates <- namespaces %>%
  arrange(project, language, search_id, namespace_searched) %>%
  group_by(project, language, search_id) %>%
  mutate(namespaces = paste0(namespace_searched, collapse = ", ")) %>%
  group_by(project, language, namespaces) %>%
  count %>%
  left_join(select(ISO_639_2, c(Alpha_2, Name)),
            by = c("language" = "Alpha_2")) %>%
  select(project, lang = language, language = Name, namespaces, searches = n) %>%
  arrange(project, lang, desc(searches))

write_csv(aggregates, "~/namespace-searches-counts.csv") # attached