- Question 3 10 Marks In This Question We Use The Same File Hightemp Txt As In Question 1 Of The Examination Paper A Fi 1 (107.86 KiB) Viewed 37 times
hightemp.txt
- Question 3 10 Marks In This Question We Use The Same File Hightemp Txt As In Question 1 Of The Examination Paper A Fi 2 (11.95 KiB) Viewed 37 times
Question 3 (10 marks) In this
question we use the same file hightemp.txt as in
Question 1 of the examination paper. A file hightemp.txt contains information about the highest temperatures recorded every day in a number of cities all over the world. The file higtemp.txt is a text file where information about the highest temperature recorded on a given day, in a given city is stored in a single row. Data items like date, temperature and city name are separated with a single blank. A file hightemp.txt has been loaded to HDFS to a location /bigdata/hightemp. We must not replicate data already loaded to HDFS. (1) Write HQL statements that prepare a file hightemp.txt to be accessed through SELECT statements. Next, write SELECT
statement that lists the total number of cities where the temperature measurements were recorded. (2 marks) (2) Write SELECT
statement that lists the names of cities, temperature measurements and dates sorted in the ascending order of the temperature measurements per each city. Additionally, list a rank of each temperature measurement in each city. (2 marks) (3) Write SELECT
statement that lists an average temperature per year and per year and city. (2 marks) (4) Write SELECT
statement that lists an average temperature per year and city, per city, and an average temperature from all measurements. (2 marks) (5) Write SELECT
statement that lists five month moving average of temperate measurements per each city. (2 marks)
01-JAN-1991 25 Sydney 01-JAN-1991 30 Brisbane 32 Singapore. 01-JAN-1991 02-JAN-1991 02-JAN-1991 02-JAN-1991 25 Sydney 31 Brisbane 35 Singapore 05-JUN-2022 15 Sydney 05-JUN-2022 20 Brisbane 05-JUN-2022 25 Singapore