ADD JAR /usr/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar;
USE ebernhardson;
CREATE EXTERNAL TABLE `TestSearchSatisfaction2` (
  `json_string` string
)
PARTITIONED BY (
  year int,
  month int,
  day int,
  hour int
)
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.SequenceFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 
  '/wmf/data/raw/eventlogging/eventlogging_TestSearchSatisfaction2';

ALTER TABLE TestSearchSatisfaction2
ADD PARTITION (year=2016,month=9,day=8,hour=20)
LOCATION '/wmf/data/raw/eventlogging/eventlogging_TestSearchSatisfaction2/hourly/2016/09/08/20';

ADD JAR /mnt/hdfs/wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION deconstruct as 'org.wikimedia.analytics.refinery.hive.DeconstructSearchQueryUDF';
SELECT query, deconstruct(query)
  FROM ( SELECT get_json_object(json_string, '$.event.query') as query
           FROM TestSearchSatisfaction2
          WHERE year=2016 
            AND get_json_object(json_string, '$.event.action') = 'searchResultPage'
            AND get_json_object(json_string, '$.event.source') = 'fulltext'
          LIMIT 10
       ) x;

SELECT *
FROM log.TestSearchSatisfaction2_15700292
WHERE LEFT(timestamp, 10) = '2016091112'
  AND event_source = 'fulltext'
  AND event_query IS NOT NULL
  AND event_searchToken = 'ds93k5gct8e30rlnsuufee87e';

SELECT
  id, identity,
  requests[size(requests)-1].query AS query,
  requests[size(requests)-1].querytype AS query_type
FROM wmf_raw.CirrusSearchRequestSet
WHERE
  year = '2016'
  AND month = '9'
  AND day = '11'
  AND id = 'ds93k5gct8e30rlnsuufee87e';