eW3MH5Wb

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