Expert in Big Data Analysis with Apache Hive Needed
Budget: $10 – $30 CAD
DATASET
Classified Ads for Cars
Note: Instead of downloading from Kaggle, download it from this URL [new window]. The
data was scraped from several websites in the Czech Republic and Germany over a period
of more than a year.
You can directly download the dataset to your local using the following command:
wget https://bit.ly/ClassifiedCars -O cars.zip
After downloading, you can extract the dataset using the following command:
unzip cars.zip && rm cars.zip
TASKS
Use the Car Dataset and Linux Terminal and write your code using Apache Hive HQL:
1. Write a Hive query to create a table called used_cars from the data by observing the
structure of the dataset.
2. Write Hive queries to see how many missing values you have in each attribute. In the
Text section of your notebook, write how many missing values in each column we
have. Especially, mention those columns with more than 50% missing values.
3. Write a Hive query to create a new table called clean_used_cars from used_cars with
the following conditions:
○ Do not include the columns with more than 50% missing values
○ The manufacture year between 2000 and 2017 including 2000 and 2017
○ Both maker and model exist in the row
○ The price range is from 3000 to 2,000,000 (3000 ≤ price ≤ 2,000,000)
4.
5. Write Hive to find how many records remained clean_used_cars.
6. Write a Hive query to find the make and model for the cars with the top 10 highest
average prices.
7. Write a Hive query to find the make and model for the cars with the top 10 lowest
average prices.
8. Write a Hive query to recommend the top five make and models for Economic
segment customers
(Top five manufacturers in the 3000 to 20,000 price range; 3000 ≤ price < 20,000) -
based on the top average price.
9. Write a Hive query to recommend the top five make and models for Intermediate
segment customers
(Top five manufacturers in the 20,000 to 300,000 price range; 20,000 ≤ price <
300,000) - based on the top average price.
10. Write a Hive query to recommend the top five make and models for the Luxury
segment customers
(Top five manufacturers in the 300,000 to 2,000,000 price range; 300,000 ≤ price <
2,000,000) - based on the top average price.
Classified Ads for Cars
Note: Instead of downloading from Kaggle, download it from this URL [new window]. The
data was scraped from several websites in the Czech Republic and Germany over a period
of more than a year.
You can directly download the dataset to your local using the following command:
wget https://bit.ly/ClassifiedCars -O cars.zip
After downloading, you can extract the dataset using the following command:
unzip cars.zip && rm cars.zip
TASKS
Use the Car Dataset and Linux Terminal and write your code using Apache Hive HQL:
1. Write a Hive query to create a table called used_cars from the data by observing the
structure of the dataset.
2. Write Hive queries to see how many missing values you have in each attribute. In the
Text section of your notebook, write how many missing values in each column we
have. Especially, mention those columns with more than 50% missing values.
3. Write a Hive query to create a new table called clean_used_cars from used_cars with
the following conditions:
○ Do not include the columns with more than 50% missing values
○ The manufacture year between 2000 and 2017 including 2000 and 2017
○ Both maker and model exist in the row
○ The price range is from 3000 to 2,000,000 (3000 ≤ price ≤ 2,000,000)
4.
5. Write Hive to find how many records remained clean_used_cars.
6. Write a Hive query to find the make and model for the cars with the top 10 highest
average prices.
7. Write a Hive query to find the make and model for the cars with the top 10 lowest
average prices.
8. Write a Hive query to recommend the top five make and models for Economic
segment customers
(Top five manufacturers in the 3000 to 20,000 price range; 3000 ≤ price < 20,000) -
based on the top average price.
9. Write a Hive query to recommend the top five make and models for Intermediate
segment customers
(Top five manufacturers in the 20,000 to 300,000 price range; 20,000 ≤ price <
300,000) - based on the top average price.
10. Write a Hive query to recommend the top five make and models for the Luxury
segment customers
(Top five manufacturers in the 300,000 to 2,000,000 price range; 300,000 ≤ price <
2,000,000) - based on the top average price.