Hive SQL实战-优快云博客

最近学习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;

我这执行没成功，不太清除什么原因