ICP 4 - a190884810/Big-Data-Programming GitHub Wiki
Lesson 4 Hive
Finish Hive query in the class
- Hadoop Dependent Query Based NoSQL Database
- 1.Create Hive Tables and Perform Queries for Use Case based on Petrol Data.
- 2.Create Hive Tables and Perform Queries for Use Case based on Olympics Data.
- 3.Create Hive Tables and Perform Queries for Use Case based on Movielens dataset which has 3 datasets as movies, users and ratings.
- Bonus: List all the movies with its genre where the movie genre is Action or Drama and the average movie rating is in between 4.4 -4.9 and only the male users rate the movie.
- Result
1)In real life what is the total amount of petrol in volume sold by every distributor?
2)Which are the top 10 distributors ID’s for selling petrol and also display the amount of petrol sold in volume by them individually?
SELECT distributer_id,vol_OUTFROM petrol order by vol_OUTdesclimit 10;
3)Find real life 10 distributor name who sold petrol in the least amount.
SELECT distributer_id,vol_OUTFROM petrol order by vol_OUTlimit 10;
4)Try One yourself
List all distributors who have this difference, along with the year and the difference which they have in that year.Hint: (vol_IN-vol_OUT)>500
Using the dataset list the total number of medals won by each country in swimming.
select country,SUM(total) from olympicwhere sport = “Swimming” GROUP BY country;
2)Display real life number of medals India won year wise.
select year,SUM(total) from olympicwhere country = “India” GROUP BY year
3)Find the total number of medals each country won display the name along with total medals.
select country,SUM(total) from olympicGROUP BY country;
4)Find the real life number of gold medals each country won.
select country,SUM(gold) from olympicGROUP BY country
5)Try One yourself
Which country got medals for Shooting, year wise classification?
1.Create 3 tables called movies, ratings and users. Load the data into tables.
2.For movies table:–List all movies with genre of movie is “Action” and “Drama”
3.For Ratings table:–List movie ids of all movies with rating equal to 5.
4.Find top 11 average rated "Action" movies with descending order of rating.–( Hint: Need to perform join operation on Movies and Ratings table)
Bonus:List all the movies with its genre where the movie genre is Action or Drama and the average movie rating is in between 4.4 -4.9 and only the male users rate the movie.
