n0dKe2p6

1CREATE TEMPORARY FUNCTION zipper AS 'com.pinterest.hadoop.hive.Zipper';
2
3create external table if not exists pixie.pranavjindal_board_basics (
4  board_index bigint,
5  numPins bigint,
6  number_boards bigint,
7  lang_name STRING,
8  lang_ratio FLOAT
9)
10
11row format delimited fields terminated by ',' lines terminated by '\n'
12 location 's3n://pinlogs/pixie/pranavjindal_board_basics/';
13
14insert overwrite table pixie.pranavjindal_board_basics
15
16SELECT
17  boardid,
18  crossboarddata.pinsCount as numPins,
19  CASE 
20  WHEN language.index = 0 THEN "en"
21  WHEN language.index = 1 THEN "fr"
22  WHEN language.index = 2 THEN "ja"
23  WHEN language.index = 5 THEN "es"
24  WHEN language.index = 6 THEN "pt"
25  WHEN language.index = 8 THEN "de" 
26  ELSE "ne"  
27  END AS lang_name,      
28  language.value / crossboarddata.pinsCount as lang_ratio
29  FROM search.meta_board_joins
30LATERAL
31   VIEW
32EXPLODE (zipper("value",crossboarddata.linkLanguageVec.values, "index", crossboarddata.linkLanguageVec.indexes)) lt AS language 
33  WHERE epoch in ("2016-10-25-00-00")
34    AND crossboarddata.numPinsLastNinetyDays > 0
35    AND boarddata.isprivate = false
36    AND boarddata.ishiddenfromfeeds = false
37    AND boarddata.inactive = false
38    AND boarddata.isprotected = false
39    AND boarddata.numcollaborators <= 5
40    AND user.isnotactive = false
41    AND user.ishiddenfrompublic = false
42    AND user.unsafe = false
43    AND crossboarddata.pinscount >= 10
44    AND crossboarddata.pinscount < 2000
45    AND crossboarddata.unsafepinscount = 0
46    AND crossboarddata.spampinscount < 5
47    AND 10*crossboarddata.spampinscount < 2*crossboarddata.pinscount
48  AND language.index IN (0, 1, 2, 5, 6, 8) -- english (0), french (1), japanese (2), spanish (5), portuguese (6), german (8)