Hive Exercise-I

Mail me for Raw data....

These are the steps for performing exercise :


i) Create Database
------------------
create database retail_store;


ii) Select Database
------------------
use retail_store;

iii) Create table for storing transactional records
-------------------------------------------------
create table txn_records(txnno INT, txndate STRING, custno INT, amount DOUBLE, 
category STRING, product STRING, city STRING, state STRING, spendby STRING)
row format delimited
fields terminated by ','
stored as textfile;

iv) Load the data into the table
-------------------------------
LOAD DATA LOCAL INPATH 'txns' OVERWRITE INTO TABLE txn_records;

v) Describing metadata or schema of the table
---------------------------------------------
describe txn_records;

vi) Counting no of records
-------------------------
select count(*) from txn_records;

vii) Counting total spending by category of products
--------------------------------------------------
select category, sum(amount) from txn_records group by category;

viii) 10 customers
--------------------
select custno, sum(amount) from txn_records group by custno limit 10;