CUMIPMT and CUMPRINC function

Lab : Hive Queries

Objectives:

To understand and be able to write simple HQL queries.

Due date:

Tuesday, Sep 18 at 8:30 pm (in class exercise).

Tools for the assignment

For this assignment, you will use your Cloudera Live Hive query engine.

What to turn in:

You need to turn in a word document containing the Screenshot of HQL query and the query result for steps 4 – 11 below.

Lab Description

In this lab you are asked to write some HQL queries. The data in this database is already in your Cloudera QuickStart VM that we installed last week.  The database consists of six tables as shown in the db schema – namely categories, customers, departments, order_items, orders and products.

1. Launch Cloudera QuickStart VM. Open firefox browser and Launch Hue UI.
2. Click on button on Hue → select Files → go to /user/hive/warehouse → you should see folders for each of the tables mentioned above.
3. Click on button on Hue → select tables → select categories → Explore metadata related categories table
4. Run the following query that you ran in Impala in the previous lab. Note the amount of time it takes to give you the result first time.

-- Most popular product categories

select c.category_name, count(order_item_quantity) as count

from order_items oi

inner join products p on oi.order_item_product_id = p.product_id

inner join categories c on c.category_id = p.product_category_id

group by c.category_name

order by count desc

limit 10;

5. Run the following query that you ran in Impala in the previous lab. Note the amount of time it takes to give you the result first time.

-- top 10 revenue generating products

select p.product_id, p.product_name, r.revenue

from products p inner join

(select oi.order_item_product_id, sum(cast(oi.order_item_subtotal as float)) as revenue

from order_items oi inner join orders o

on oi.order_item_order_id = o.order_id

where o.order_status <> 'CANCELED'

and o.order_status <> 'SUSPECTED_FRAUD'

group by order_item_product_id) r

on p.product_id = r.order_item_product_id

order by r.revenue desc

limit 10;

6. Create a new HQL query to List all the customers who bought at least one ‘Electronics’ product
7. List first name and last name of all the customers who purchased products that has ‘Under Armour' in product name.
8. List top 10 customers with largest total purchase – list first name, last name and total sales for each top customer   
9. For every customer, find the total number of orders created. Sort the answers in decreasing order of the total number of orders.
10. Find all the customers who have made at least 10 orders
11. Find all the customers who have purchased at least 25 distinct products
12. Submit word document with screenshot of query and query result to Black Board.