« back

Querying Analytics-Store Locally

Setup

MySQL Configuration

Create a ~/.my.cnf file with the following contents:

[client]
user=<username copied from stat2:/etc/mysql/conf.d/analytics-research-client.cnf>
password=<password copied from stat2:/etc/mysql/conf.d/analytics-research-client.cnf>

SSH Tunnel

In a Terminal window, use the following command to create an SSH tunnel (assuming this SSH configuration):

ssh -N stat3 -L 3307:analytics-store.eqiad.wmnet:3306

Do not close that window/connection until you’re done.

Connecting in R

Note that we set a default for the connection chunk option in the setup chunk, so that it is not necessary to specify on each individual chunk.

library(RMySQL)
con <- dbConnect(MySQL(), host = "127.0.0.1", group = "client", dbname = "log", port = 3307)
knitr::opts_chunk$set(connection = "con")

Example

Refer to this documentation for details on the SQL engine and chunk options in RMarkdown.

start_date <- as.character(Sys.Date() - 1 - 7, format = "%Y%m%d")
end_date <- as.character(Sys.Date() - 1, format = "%Y%m%d")

SELECT
  DATE(LEFT(timestamp, 8)) AS `date`,
  CASE WHEN (
         INSTR(userAgent, 'Android') > 0
         OR INSTR(userAgent, 'Mobile') > 0
         OR INSTR(userAgent, 'iOS') > 0
         OR INSTR(userAgent, 'Phone') > 0
       ) THEN 'Mobile'
       ELSE 'Desktop' END AS device,
  CASE WHEN INSTR(event_destination, 'itunes.apple.com') > 0 THEN 'iOS App link'
       WHEN INSTR(event_destination, 'play.google.com') > 0 THEN 'Android app link'
       ELSE 'List of apps' END AS clicked,
  COUNT(*) AS clicks
FROM WikipediaPortal_15890769
WHERE
  LEFT(timestamp, 8) >= ?start_date AND LEFT(timestamp, 8) <= ?end_date
  AND event_cohort = 'baseline'
  AND event_event_type = 'clickthrough'
  AND event_section_used = 'other projects'
  AND (
    INSTR(event_destination, 'itunes.apple.com') > 0
    OR INSTR(event_destination, 'play.google.com') > 0
    OR event_destination = 'https://en.wikipedia.org/wiki/List_of_Wikipedia_mobile_applications'
  )
GROUP BY `date`, device, clicked;

For this chunk, we set output.var = "events" to to assign the results of the SQL query to an R data frame. Note that when the results of a SQL query are assigned to a data frame no records are printed within the document.

library(tidyverse)
events %>%
  group_by(device, clicked) %>%
  summarize(clicks = sum(clicks)) %>%
  spread(clicked, clicks)

dbDisconnect(con)

[1] TRUE