jTf1H1zw

1CREATE EXTERNAL TABLE IF NOT EXISTS e_wiki (
2Year int, Month int, Article string, Views int, Rank int)
3ROW FORMAT DELIMITED
4FIELDS TERMINATED BY ','
5STORED AS TEXTFILE
6location '/user/cloudera/wiki'; 
7
8CREATE TABLE IF NOT EXISTS t_wiki (
9Year int, Month int, Article string, Views int, Rank int)
10ROW FORMAT DELIMITED
11FIELDS TERMINATED BY ','
12STORED AS ORC; 
13
14INSERT OVERWRITE TABLE t_wiki SELECT year, month, article, views, rank FROM e_wiki;
15
16
17CREATE EXTERNAL TABLE IF NOT EXISTS e_wiki (
18Project string, Access string, Year int, Month int, Day int, Article string, Views int, Rank int)
19ROW FORMAT SERDE
20    'org.apache.hadoop.hive.serde2.OpenCSVSerde'
21WITH SERDEPROPERTIES (
22    'separatorChar' = ',',
23    'quoteChar' = '"')
24STORED AS INPUTFORMAT
25    'org.apache.hadoop.mapred.TextInputFormat'
26OUTPUTFORMAT
27    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
28LOCATION
29    '/user/cloudera/wiki'
30TBLPROPERTIES (
31    'serialization.null.format' = '',
32    'skip.header.line.count' = '1')
33
34select  DISTINCT article, views from (select DISTINCT article, views from t_wiki3 sort by views desc limit 2)
35result sort by views limit 1;
36
37SELECT article, COUNT(DISTINCT month) No_of_distinct_articles
38from t_wiki
39group by article,month
40
41
42select article, avg (views)
43from t_wiki
44group by article
45having sum (total_sales) > 10000