最近学习Hive的基本使用,下面就记录一下我学习Hive的一些基本语句
数据可以到这里下载:链接: https://pan.baidu.com/s/1RAoicaE7uygtUpdwE-ACtg 提取码: ysrm
数据格式:
tbDate.txt
2003-1-1,200301,2003,1,1,3,1,1,1,1
2003-1-2,200301,2003,1,2,4,1,1,1,1
2003-1-3,200301,2003,1,3,5,1,1,1,1
2003-1-4,200301,2003,1,4,6,1,1,1,1
2003-1-5,200301,2003,1,5,7,1,1,1,1
2003-1-6,200301,2003,1,6,1,2,1,1,1
2003-1-7,200301,2003,1,7,2,2,1,1,1
2003-1-8,200301,2003,1,8,3,2,1,1,1
字段含义:
日期,年月,年,月,日,上半年,周几,第几周,第几季度,上半
tbStock.txt
BYSL00000893,ZHAO,2007-8-23
BYSL00000897,ZHAO,2007-8-24
BYSL00000898,ZHAO,2007-8-25
BYSL00000899,ZHAO,2007-8-26
BYSL00000900,ZHAO,2007-8-26
BYSL00000901,ZHAO,2007-8-27
BYSL00000902,ZHAO,2007-8-27
BYSL00000904,ZHAO,2007-8-28
字段含义:
订单号, 交易位置 ,交易日期
tbStockDetail.txt
BYSL00000893,0,FS527258160501,-1,268,-268
BYSL00000893,1,FS527258169701,1,268,268
BYSL00000893,2,FS527230163001,1,198,198
BYSL00000893,3,24627209125406,1,298,298
BYSL00000893,4,K9527220210202,1,120,120
BYSL00000893,5,01527291670102,1,268,268
BYSL00000893,6,QY527271800242,1,158,158
字段含义:
订单号,行号,订单产品,有效性,数量,金额
创建Hive的表:
创建表tbDate
create table tbDate(dataID string,theyearmonth string,theyear string,themonth string,thedate string,theweek string,theweeks string,thequot string,thetenday string,thehalfmonth string) row format delimited fields terminated by ',' lines terminated by '\n';
创建表tbSock
CREATE TABLE tbStock(ordernumber STRING,locatitionid string,dataID string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
创建表tbStockDetail
CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amout int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
导入数据:
本次导入是从本地直接导入
load data local inpath '/home/hadoop/hive/tbDate.txt' into table tbDate; //导入tbDate.txt的数据到hive数据仓库的tbdate表中
load data local inpath 'home/hadoop/hive/tbStock.txt' into table tbStock; //导入tbStock.txt的数据到hive数据仓库的tbstock表中
load data local inpath 'home/hadoop/hive/tbStockDetail.txt' into table tbStockDetail; //导入tbStockDetail.txt的数据到hive数据仓库的tbStockdetail表中
操作数据:
//查询销售额前十名的季度
select c.theyear,c.thequot,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdate c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,c.thequot order by sumofamount desc limit 10;
//查询订单总额大于2000的订单和金额
select a.ordernumber,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b where a.ordernumber=b.ordernumber group by a.ordernumber having sumofamount >2000;
//每年订单中销售最畅销的商品
select distinct e.theyear,e.itemid,f.maxofmount from (select c.theyear,b.itemid,sum(b.amout) as sumofmount from tbstock a,tbstockdetail b,tbdate c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,b.itemid) e,(select d.theyear, max(d.sumofamount) as maxofmount from (select c.theyear,b.itemid,sum (b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdate c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,b.itemid) d group by d.theyear) f where e.theyear=f.theyear and e.sumofmount=f.maxofmount order by e.theyear;
我这执行没成功,不太清除什么原因