hive 操作

1、Hive操作演示

1.1内部表

1.1.1 创建表并加载数据

第一步启动HDFS、YARN和Hive,启动完毕后创建Hive数据库

hive>create database hive;

hive>show databases;

hive>use hive;

第二步创建内部表

由于Hive使用了类似SQL的语法,所以创建内部表的语句相对SQL只增加了行和字段分隔符。

hive>CREATE

TABLE tab2(DT STRING,WEBSESSION STRING,WORD STRING,S_SEQ INT,C_SEQ

INT,WEBSITE STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES

TERMINATED BY '\n' ;

第三步加载数据

数据文件可以从HDFS或者本地操作系统加载到表中,如果加载HDFS文件使用LOAD DATA INPATH,而加载本地操作系统文件使用LOAD DATA LOCAL INPATH命令。HIVE表保存的默认路径在${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定,当创建表时会在hive.metastore.warehouse.dir指向的目录下以表名创建一个文件夹,在本演示中表默认指向的是/user/hive/warehouse。

数据文件在本地操作系统将复制到表对应的目录中,而数据文件在HDFS中,数据文件将移动到表对应的目录中,原来的路径将不存在该文件。

hive>LOAD DATA LOCAL INPATH '/home/hadoop/upload/sogou/tab2.txt' INTO TABLE tab2;

在/user/hive/warehouse/hive.db/tab2目录下,可以看到tab2.txt数据文件:

1.1.2 查询行数

可以用count关键字查询tab2.txt数据行数,查询时会启动MapReduce进行计算,Map的个数一般和数据分片个数对应,在本查询中有2个Map任务(数据文件有2个Block),1个Reduce任务。

hive>select count(*) from tab2;

1.1.3 包含laopo的数据

可以用like关键字进行模糊查询,Map的个数一般和数据分片个数对应。

hive>select count(*) from tab2 where WEBSITE like '%laopo%';

1.1.4 查询结果排名第1,点击次序排第2,其中URL包含google的数据

hive>select count(*) from tab2 where S_SEQ=1 and C_SEQ=2 and WEBSITE like '%google%';

1.2外部表

1.2.1 创建表关联数据

第一步在HDFS创建外部表存放数据目录

$hadoop fs -mkdir -p /class5/tab1

$hadoop fs -ls /class5

第二步在Hive创建外部表,指定表存放目录

hive>CREATE

EXTERNAL TABLE tab1(DT STRING,WEBSESSION STRING,WORD STRING,S_SEQ

INT,C_SEQ INT,WEBSITE STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY

'\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION

'/class5/tab1';

hive>show tables;

观察一下创建表和外部表的区别,会发现创建外部表多了EXTERNAL关键字以及指定了表对应存放文件夹LOCATION ‘/class5/tab1

【注】在删除表的时候,内部表将删除表的元数据和数据文件;而删除外部表的时候,仅仅删除外部表的元数据,不删除数据文件

第三步加载数据文件到外部表对应的目录中

创建Hive外部表关联数据文件有两种方式,一种是把外部表数据位置直接关联到数据文件所在目录上,这种方式适合数据文件已经在HDFS存在,另外一种方式是创建表时指定外部表数据目录,随后把数据加载到该目录下。以下将以第二种方式进行演示:

$hadoop fs -copyFromLocal /home/hadoop/upload/sogou/tab1.txt  /class5/tab1/

$hadoop fs -ls /class5/tab1

$hadoop fs -tail /class5/tab1/tab1.txt

1.2.2 查询行数

hive>select count(*) from tab1;

1.2.3 显示前10行

hive>select * from tab1 limit 10;

可以看出Hive会根据查询不同任务决定是否生成Job,获取前10条并没有生成Job,而是得到数据后直接进行显示。

1.2.4 查询结果排名第1,点击次序排第2的数据

hive>select count(*) from tab1 where S_SEQ=1 and C_SEQ=2;

1.2.5 查询次数排行榜

按照session号进行归组,并按照查询次数进行排序,最终显示查询次数最多的前10条。

hive>select WEBSESSION,count(WEBSESSION) as cw from tab1 group by WEBSESSION order by cw desc limit 10;

2、交易数据演示

2.1准备数据

2.1.1 上传数据

交易数据存放在该系列配套资源的/class5/saledata目录下,在/home/hadoop/upload创建class5目录用于存放本周测试数据

$cd /home/hadoop/upload

$mkdir class5

创建新文件夹后使用,使用SSH Secure File Transfer工具上传到/home/hadoop/upload/class5目录下,如下图所示:

2.1.2 在Hive创建数据库和表

启动Hadoop集群,进入Hive命令行操作界面,使用如下命令创建三张数据表:

ltbDate定义了日期的分类,将每天分别赋予所属的月份、星期、季度等属性,字段分别为日期、年月、年、月、日、周几、第几周、季度、旬、半月;

ltbStock定义了订单表头,字段分别为订单号、交易位置、交易日期;

ltbStockDetail文件定义了订单明细,该表和tbStock以交易号进行关联,字段分别为订单号、行号、货品、数量、金额:

hive>use hive;

hive>CREATE TABLE tbDate(dateID

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' ;

hive>CREATE TABLE tbStock(ordernumber STRING,locationid string,dateID string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n' ;

hive>CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amount int) ROW FORMAT DELIMITED

FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;

2.1.3 导入数据

从本地操作系统分别加载日期、交易信息和交易详细信息表数据

hive>use hive;

hive>LOAD DATA LOCAL INPATH '/home/hadoop/upload/class5/saledata/tbDate.txt' INTO TABLE tbDate;

hive>LOAD DATA LOCAL INPATH '/home/hadoop/upload/class5/saledata/tbStock.txt' INTO TABLE tbStock;

hive>LOAD DATA LOCAL INPATH '/home/hadoop/upload/class5/saledata/tbStockDetail.txt' INTO TABLE tbStockDetail;

查看HDFS中相关SALEDATA数据库中增加了三个文件夹,分别对应三个表:

2.2计算所有订单每年的总金额

2.2.1 算法分析

要计算所有订单每年的总金额,首先需要获取所有订单的订单号、订单日期和订单金信息,然后把这些信息和日期表进行关联,获取年份信息,最后根据这四个列按年份归组统计获取所有订单每年的总金额。

2.2.2 执行HSQL语句

hive>use hive;

hive>select

c.theyear, sum(b.amount) from tbStock a,tbStockDetail b,tbDate c where

a.ordernumber=b.ordernumber and a.dateid=c.dateid group by c.theyear

order by c.theyear;


2.3计算所有订单每年最大金额订单的销售额

2.3.1 算法分析

该算法分为两步:

1.按照日期和订单号进行归组计算,获取所有订单每天的销售数据;

2.把第一步获取的数据和日期表进行关联获取的年份信息,然后按照年份进行归组,使用Max函数,获取所有订单每年最大金额订单的销售额。

2.3.2 执行HSQL语句

//所有订单每年最大金额订单的销售额

//第一步:

hive>use hive;

hive>select

a.dateid,a.ordernumber,sum(b.amount) as sumofamount from tbStock

a,tbStockDetail b where a.ordernumber=b.ordernumber group by

a.dateid,a.ordernumber;

//第二步:

hive>select

c.theyear,max(d.sumofamount) from tbDate c,(select

a.dateid,a.ordernumber,sum(b.amount) as sumofamount from tbStock

a,tbStockDetail b where a.ordernumber=b.ordernumber group by

a.dateid,a.ordernumber)

d  where c.dateid=d.dateid group by c.theyear sort by c.theyear;

运行过程中创建两个Job,分别为job_1437659442092_0004和job_1437659442092_0005,运行过程如下:

在YARN的资源管理器界面中可以看到如下界面:

其中job_1437659442092_0005运行的具体情况如下:

2.3.3 查看结果

整个计算过程使用了285秒,结果如下:

2.4计算其他金额

2.4.1 所有订单中季度销售额前10位

//所有订单中季度销售额前10位

hive>use hive;

hive>select

c.theyear,c.thequot,sum(b.amount) as sumofamount from tbStock

a,tbStockDetail b,tbDate c where a.ordernumber=b.ordernumber and

a.dateid=c.dateid group by c.theyear,c.thequot order by sumofamount

desc limit 10;

2008    1    5252819

2007    4    4613093

2007    1    4446088

2006    1    3916638

2008    2    3886470

2007    3    3870558

2007    2    3782235

2006    4    3691314

2005    1    3592007

2005    3    3304243

2.4.2 列出销售金额在100000以上的单据

//列出销售金额在100000以上的单据

hive>use hive;

hive>select

a.ordernumber,sum(b.amount) as sumofamount from tbStock a,tbStockDetail b

where a.ordernumber=b.ordernumber group by a.ordernumber having

sumofamount>100000;

2.4.3 所有订单中每年最畅销货品

//所有订单中每年最畅销货品

第一步:

hive>use hive;

hive>select

c.theyear,b.itemid,sum(b.amount) as sumofamount from tbStock

a,tbStockDetail b,tbDate c where a.ordernumber=b.ordernumber and

a.dateid=c.dateid group by c.theyear,b.itemid;

第二步:

hive>select

d.theyear,max(d.sumofamount) as maxofamount from (select

c.theyear,b.itemid,sum(b.amount) as sumofamount from tbStock

a,tbStockDetail b,tbDate c where a.ordernumber=b.ordernumber and

a.dateid=c.dateid

group by c.theyear,b.itemid) d group by d.theyear ;

第三步:

hive>select distinct  e.theyear,e.itemid,f.maxofamount from (select c.theyear,b.itemid,sum(b.amount) as sumofamount from tbStock a,tbStockDetail b,tbDate c where a.ordernumber=b.ordernumber and a.dateid=c.dateid group by c.theyear,b.itemid) e , (select d.theyear,max(d.sumofamount) as maxofamount from (select c.theyear,b.itemid,sum(b.amount) as sumofamount from tbStock a,tbStockDetail b,tbDate c where a.ordernumber=b.ordernumber and a.dateid=c.dateid group by c.theyear,b.itemid) d group by d.theyear) f where e.theyear=f.theyear and e.sumofamount=f.maxofamount order by e.theyear;

2004    JY424420810101   53374

2005    24124118880102   56569

2006    JY425468460101   113684

2007    JY425468460101   70226

2008    E2628204040101   97981

2009    YL327439080102   30029

2010    SQ429425090101  4494

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 200,045评论 5 468
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 84,114评论 2 377
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 147,120评论 0 332
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 53,902评论 1 272
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 62,828评论 5 360
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,132评论 1 277
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,590评论 3 390
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,258评论 0 254
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,408评论 1 294
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,335评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,385评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,068评论 3 315
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,660评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,747评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,967评论 1 255
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,406评论 2 346
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 41,970评论 2 341

推荐阅读更多精彩内容