HiveQL命令行的使用

实验步骤

注意：由于数据库对大小写不敏感，因此大写和小写都可以使用。

1. HiveQL：数据操作

（1）进入HiveQL，命令如下：

hive

image

（2）Hive创建数据库：创建一个名为userdb的数据库

命令：

create database userdb;

image

随时可以通过show命令来查看Hive中所包含的数据库：

命令：

show databases;

image

Hive会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录的子目录形式存储。

（3）Hive创建表

首先需要使用use命令使用数据库userdb：

use userdb;

image

再根据相应要求在userdb数据库中创建表（仅仅是创建表结构，表中并没有内容）：

create table if not exists employee ( id int, name String, salary String, designation String)

comment 'employee1 details'

row format delimited

fields terminated BY '\t'

lines terminated BY '\n'

stored as textfile;

</pre>

image

（4）Hive加载数据：向建的表employee中加载数据

load data local inpath '/opt/sample.txt'

overwrite into table employee;

</pre>

image

（5）查看数据是否加载成功

select * from employee;

image

（5）Hive修改表

①. 重命名表，将表名由employee修改为emp

alter table employee rename to emp;

image

查看名称是否正确修改：

show tables;

image

②. 删除表emp

drop table emp;

image

查看是否删除成功：

show tables;

image

（6）删除数据库

①. 若删除的数据库不为空则会报错。如下图所示：

drop database if exists userdb1;

image

②. 若删除的数据库为空则可以成功删除，如下图所示：

drop database if exists userdb;

image

此时，再查看数据库则会看到userdb1没有被删除，而userdb已被删除。

show databases;

image

（7）使用数据库userdb1，接下来所有操作均在此数据库中执行。

use userdb1;

image

（8）Hive分区

我们已经有一张分区表，名为employee1，下面为这张表添加分区。

①. 添加分区

alter table employee1

add partition (year='2012') location '/opt/empdata/2012/part2012.txt'

partition (year='2013') location '/opt/empdata/2013/part2013.txt';

</pre>

image

②. 查看分区：

show partitions employee1;

image

③. 写入数据：

load data local inpath '/opt/empdata/2013/file3'

into table employee1 partition(year='2013');

load data local inpath '/opt/empdata/2012/file2'

into table employee1 partition(year='2012');

</pre>

image

（9）导出数据

①. 将表employee1中的数据导出到HDFS

insert overwrite directory '/home/Test/hdfs'

row format delimited

fields terminated by '\t'

select * from employee1;

</pre>

image

查看（注意：在集群中查看，需要先退出hiveQL，输入quit;命令来退出数据库到root用户下执行查询命令）

hadoop fs -cat /home/Test/hdfs/000000_0

image

②. 将表employee1中的数据导出到本地文件系统（注意：在HiveQL中操作，需要先进入hiveQL，输入命令hive。每次重新进入hiveQL都要使用“use userdb1;”命令来指定需要操作的数据库。）

insert overwrite local directory '/home/Test/sample'

row format delimited

fields terminated by '\t'

select * from employee1;

</pre>

image

查看（注意：在集群中查看，需要先退出hiveQL，输入quit;命令来退出数据库到root用户下执行查询命令）

cat /home/Test/sample/000000_0

image

③. 将表employee1中的数据导出到已经存在的表employee2（注意：在HiveQL中操作，需要先进入hiveQL，输入命令hive。每次重新进入hiveQL都要使用“use userdb1;”命令来指定需要操作的数据库。）

insert into table employee2

select * from employee1;

</pre>

image

查看数据是否导入到表employee2中：

select * from employee2;

image

④. 给分区重命名，并且查看重命名是否成功：

alter table employee1 partition (year='2013')

rename to partition (year='2014');

</pre>

image

show partitions employee1;

image

⑤. 删除分区，并且查看分区是否删除成功：

alter table employee1 drop

partition (year='2014');

</pre>

image

show partitions employee1;

[图片上传中...(image-78082e-1509677357636-21)]

（10）外部表的基本操作

①. 创建外部表，表名为exter_emp：

create external table exter_emp( id int, name String, age int, tel String)

row format delimited

fields terminated by '\t'

lines terminated by '\n'

stored as textfile

location '/home/Test/External';

</pre>

[图片上传中...(image-c08f8d-1509677357636-20)]

②. 向外部表中加载数据：

load data local inpath '/opt/empdata/file4' into table exter_emp;

image

③. 查看表中是否成功导入数据：

select * from exter_emp;

image

④. 删除外部表exter_emp：

drop table exter_emp;

image

⑤. 查看文件本体依然存在：删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的。

dfs -ls /home/Test/External;

image

2. HiveQL查询操作

（1）创建外部表employees

create external table employees(eid int,name String,salary float,age int,birthday date,

subordinates array<String>,

deductions map<String,float>,

address struct<street:String,city:String,state:String>)

row format delimited

fields terminated by '\t'

collection items terminated by ','

map keys terminated by ':'

location '/home/Test/external-1';

</pre>

image

向外部表employees中加载数据：

load data local inpath '/opt/empdata/file5' into table employees;

image

（2）SELECT...FROM语句的使用

①. 查看employees表中的薪水数据

select name,salary from employees;

image

②. 查看employees表中的下属数据信息

select name,subordinates from employees;

image

③. 查看employees表中的扣除税数据

select name,deductions from employees;

image

④. 引用复合字段查看下级数据

select name,subordinates[0] from employees;

image

⑤. 选择一个deductions元素进行查询

select name,deductions["FedTax"] from employees;

image

（3）使用列值计算

①. 把查询得到的员工姓名转换为大写并计算员工的税后薪资

select upper(name),salary,deductions["FedTax"],

round(salary*(1-deductions["FedTax"])) from employees;

</pre>

image

②. 内置函数查询表employees中有多少员工，以及计算员工平均薪水

select count(*),avg(salary) from employees;

image

③. 将employees表中每行记录中的subordinates字段内容转换成0个或者多个新的记录行。如果某行员工记录subordinates字段内容为空的话，就不会产生记录；如果不为空，那么这个数组的每个元素都将产生一行新记录：

select explode(subordinates) as sub from employees;

image

（4）SELECT...WHERE语句的使用

查看薪资超过30000的员工

select * from employees2 where salary > 30000;

image

（5）GROUP BY 语句的使用

查询获取每个部门的员工人数的查询语句如下：

select dept,count(*) from employees2 group by dept;

image

（6）ORDER BY语句的使用

获取员工的详细信息，并把结果按照部门名称排序：

select id,name, dept from employees2 order by dept;

image

（7）JOIN语句的使用

①. 下面的查询对customers和orders进行连接，找出每个客户下的订单信息。连接的条件是customers表中的id必须与orders表中的customer_id相同。这个JOIN操作实际上就是获取每个下了订单的客户的订单情况。如果某个客户没有下过任何订单，那么该客户的信息将不会返回。

select c.id, c.name, c.age, o.amount

from customers c join orders o

on (c.id = o.customer_id);

image

②. HiveQL LEFT OUTER JOIN（左外连接）返回所有行左表，即使是在右边的表中没有匹配。这意味着，如果ON子句匹配的右表0（零）记录，JOIN还是返回结果行，但在右表中的每一列的值为NULL。这个LEFT OUTER JOIN操作实际上就是获取每个客户的订单情况，不管客户是不是下过订单，该客户的信息都将会返回。通过这个操作，用户可以了解到整个客户的情况，包括下过订单的客户和没有下过订单的客户。

select c.id, c.name, o.amount, o.time

from customers c

left outer join orders o

on (c.id = o.customer_id);

image

③. HiveQL RIGHT OUTER JOIN（右外连接）返回右边表的所有行，即使在左表中没有匹配。如果ON子句的左表匹配0（零）的记录，JOIN结果返回一行，但在左表中的每一列为NULL。这个RIGHT OUTER JOIN操作实际上就是获取所有订单的订单和下单客户的情况，即使某个订单没有下单客户ID，也会返回该订单的情况，有关客户信息项将为NULL.

select c.id, c.name, o.amount, o.time

from customers c

right outer join orders o

on (c.id = o.customer_id);

image

④. HiveQL FULL OUTER JOIN（完全外连接）将会返回两个表的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。这个FULL OUTER JOIN操作实际上就是首先进行LEFT OUTER JOIN，再进行RIGHT OUTER JOIN。也就是说，首先获取所有客户的订单信息，如果没有订单，相应的订单信息项就返回NULL；然后再获取所有订单的客户信息，如果没有客户，那么有关客户信息项将为NULL。

select c.id, c.name, o.amount, o.time

from customers c

full outer join orders o

on (c.id = o.customer_id);

</pre>

image

最后编辑于：2017.12.11 07:40:13

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,214评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,307评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,543评论 0赞 341
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,221评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,224评论 5赞 371
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,007评论 1赞 284
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,313评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,956评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,441评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,925评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,018评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,685评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,234评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,240评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,464评论 1赞 261
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,467评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,762评论 2赞 345

HiveQL命令行的使用

实验步骤

1. HiveQL：数据操作

2. HiveQL查询操作

推荐阅读更多精彩内容