AGvdLNXJ

1DROP TABLE access_log;
2CREATE EXTERNAL TABLE access_log (
3    ip STRING,
4    date STRING,
5    url STRING,
6    status STRING,
7    referer STRING,
8    user_agent STRING
9)
10PARTITIONED BY (day string)
11ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
12WITH SERDEPROPERTIES (
13    "input.regex" = "([\\d\\.:]+) - - \\[(\\S+) [^\"]+\\] \"\\w+ ([^\"]+) HTTP/[\\d\\.]+\" (\\d+) \\d+ \"([^\"]+)\" \"(.*?)\""
14)
15STORED AS TEXTFILE;
16
17ALTER TABLE access_log ADD PARTITION(day='${DATE}')
18LOCATION '/user/bigdatashad/logs/${DATE}';
19
20DROP TABLE parsed_log;
21
22CREATE TABLE IF NOT EXISTS parsed_log (
23    ip STRING,
24    date TIMESTAMP,
25    status SMALLINT,
26    url STRING,
27    profile STRING,
28    referer STRING
29)
30PARTITIONED BY (day STRING, hour STRING)
31STORED AS RCFILE;
32
33INSERT OVERWRITE TABLE parsed_log
34PARTITION(day='${DATE}', hour)
35
36SELECT
37    ip,
38    from_unixtime(unix_timestamp(date ,'dd/MMM/yyyy:HH:mm:ss')),
39    CAST(status AS smallint),
40    url,
41    regexp_extract(url, '/(id\\d+)$', 1),
42    referer,
43    regexp_extract(date, '(\\d+/.+/\\d+):(\\d+):(\\d+):(\\d+)', 2) as hour
44FROM access_log
45WHERE day='${DATE}' AND status='200';
46
47
48SELECT country, COUNT(CASE WHEN url LIKE '%like%' THEN 1 END), COUNT(DISTINCT ip),
49COUNT(CASE WHEN url LIKE '%like%' THEN 1 END) / COUNT(DISTINCT ip) FROM
50(SELECT TRANSFORM (ip, url) USING 'path.py' AS country, ip, url FROM parsed_log WHERE day='${DATE}' AND status='200')
51TABLE
52GROUP BY country ORDER BY country;