Apache Hive skilled data analyst needed
Budget: $10 – $30 CAD
I am in need of a skilled data analyst with experience in Apache Hive to work on a project involving data analysis.
- Experience with Apache Hive is a requirement. If you have experience with other data analysis tools, that would be a bonus.
- The project will involve working with a dataset to perform various data analysis tasks - such as data aggregation, filtering or transformation.
- The dataset will range in size, so experience in handling large amounts of data would be beneficial.
More details about the project, including specific data analysis tasks and the purpose of the project, will be provided upon hiring.
DATASET
Classified Ads for Cars
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
We can only use apache hive and need the follow information
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.
- Experience with Apache Hive is a requirement. If you have experience with other data analysis tools, that would be a bonus.
- The project will involve working with a dataset to perform various data analysis tasks - such as data aggregation, filtering or transformation.
- The dataset will range in size, so experience in handling large amounts of data would be beneficial.
More details about the project, including specific data analysis tasks and the purpose of the project, will be provided upon hiring.
DATASET
Classified Ads for Cars
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
We can only use apache hive and need the follow information
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.