在Hive中执行shell命令和hdfs命令
1. 执行shell命令
用户可以执行简单的bash shell命令，只要在命令前加上！并且以分号结尾。Hive CLI不能使用需要用户进行输入的交互式命令，而且不支持shell的“管道”功能和文件名的自动补全功能。
hive> ! /bin/echo "hello world!";
"hello world!"
hive> !pwd;
/home/tyhawk
2. 执行hdfs命令
需要把hadoop关键字去掉，加上dfs，然后以分号结尾。（这种使用hadoop命令的方式实际上比与其等价的在bash shell中执行的hadoop fs命令更加高效）
hive> dfs -ls /;
Found 6 items
drwxr-xr-x - tyhawk supergroup 0 2019-03-21 10:59 /ducl
drwxr-xr-x - tyhawk supergroup 0 2019-03-19 17:31 /out2
-rw-r--r-- 3 tyhawk supergroup 36 2019-03-18 20:45 /test
drwx------ - tyhawk supergroup 0 2019-03-20 17:32 /tmp
drwxr-xr-x - tyhawk supergroup 0 2019-03-21 10:24 /user
-rw-r--r-- 2 tyhawk supergroup 70 2019-03-19 16:28 /word.txt
DDL操作
1. Hive中的数据库操作
1.1创建数据库（最简单的写法和mysql相同）
create database {database_name};
在这里，IF NOT EXISTS是一个可选子句
create database if not exists {databasename}的行为，先判断表是否存在，
如果存在，语句就相当于执行insert into select；
如果不存在，则相当于create table … select。
当数据表存在的时候，使用insert into select将select的结果插入到数据表中，
create database if not exists {databasename};
1.2查看数据库
show databases;
如果数据库非常多，可以使用正则表达式
show databases like 'd.*'
1.3查看数据库信息
describe database {database_name};
1.4删除数据库
drop database xxx
删除db xxx
drop database if exists xxx
若存在db xxx，则删除之，否则忽略
两者的区别是在 xxx 不存在的情况下，第一个 sql 会返回错误；第二个返回值是正确的，只是会有一个 warning
drop database if exists {database_name};
1.5删除含有表的数据库
drop database {database_name} cascade;
2. 创建表
简要来说，建表一般会有如下类型：内部表、外部表、分区表、分桶表
- 内部表和外部表
未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；
- 区别：
1）内部表的生命周期以及数据都由Hive自身管理，就是内部表的表结构和表中的数据都是由hive进行管理的。如果删除了内部表，那么内部表中的数据也会别删除。外部表只有表结构是hive进行管理的，数据由HDFS管理，如果删除hive中的外部表，那么表结构会删除，但是不会删除表中的数据。
2）删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；
3）对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）
4）内部表的数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），但是内部表和外部表都可以自己指定存储位置；
- 分区和分桶表
区别:
分区的目的就是提高查询效率，查询分区数据的方式就是指定分区名，指定分区名之后就不再全表扫描，直接从指定分区（如sex=man的分区）中查询，从hdfs的角度看就是从相应的文件系统中（如sex=man文件夹下）去查找特定的数据
分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值得hash值进行区分，如要安装name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件。
2.1 建表示例
【简易用法】
hive> create table student(
> id int,
> name string,
> age int)
> row format delimited //默认的hive的语句
> fields terminated by '\t' //字段与字段间用制表符分隔
> stored as textfile; //文件类型
【复杂示例】
CREATE TABLE page_view //表名
(viewTime INT, //列名和定义
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User') //字段注释
COMMENT 'This is the page view table' //数据库注释
PARTITIONED BY(datetime STRING, country STRING) //分区，通过指定类别分区
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 20 BUCKETS //将userid排列，分为20个桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' //字段与字段之间的分隔符
COLLECTION ITEMS TERMINATED BY '\t' //一个字段各个字节的分隔符
MAP KEYS TERMINATED BY '\t' //key value分隔符
STORED AS TEXTFILE; //指定文件类型为textfile
2.2 内部表
hive> create table student(
> id int,
> name string,
> age int)
> row format delimited //默认的hive的语句
> fields terminated by '\t' //字段与字段间用制表符分隔
> stored as textfile; //文件类型
2.3 外部表
hive> create external table student2(
> id int,
> name string,
> age int)
> row format delimited
> fields terminated by '\t'
> stored as textfile
> location '/tyhawk/student2'; //自己设定一个数据库存放的路径
OK
Time taken: 0.671 seconds
存放路径必须是hdfs上一个没有的路径，如果不设定就存在默认路径/user/hive/warehouse下
2.4 分区表
关于静态分区和动态分区的区别，可以参考这里（简单来说，录入数据时，静态分区需要指定分区，动态分区会自动截取字段存放分区）：https://www.cnblogs.com/yongjian/archive/2017/03/29/6640951.html
创建静态分区表
hive> create table student3(
> id int,
> name string,
> age int)
> partitioned by(sex string) //通过性别分区
> row format delimited
> fields terminated by '\t'
> stored as textfile;
OK
【数据示例】
student1.txt(female)
1001 shiny 23
1002 cendy 22
1003 angel 23
1009 ella 21
1012 eva 24
student2.txt(male)
1005 bob 24
1006 mark 23
1007 leo 22
1011 JACK 23
1014 JAMES 24
插入数据
load data local inpath '/home/tyhawk/student1.txt'
into table student3 partition (sex='female');
load data local inpath '/home/tyhawk/student1.txt'
into table student3 partition (sex='male');
查询表分区
hive> show partitions student3;
OK
sex=male
Time taken: 0.191 seconds, Fetched: 1 row(s)
2.5 创建分桶表
分桶是相对分区表更为细颗粒度的划分存储。比如将name列细分为多个桶来进行存储。
可以借鉴：https://blog.csdn.net/jenrey/article/details/80588493
create table student4(
id INT,
name string,
age int)
clustered by(id) sorted bg(id desc) into 3 buckets
row format delimited fields terminated by '\t';
3. 修改表
3.1 重命名表
ALTER TABLE student4 RENAME TO student_buck;
3.2 增加列
ALTER TABLE student ADD COLUMNS(address STRING);
3.3 改变列
ALTER TABLE student CHANGE COLUMN id number INT;
3.4 替换列
ALTER TABLE student REPLACE COLUMNS(id INT,name STRING,age int);
3.5 增加分区
ALTER TABLE student3 ADD
PARTITION(sex='weizhi') //指定分区
LOCATION '/user/hive/warehouse/tyhawk.db/student3/sex=weizhi'; //指定存储位置
注意：只有在存在分区列的表上执行增加分区的操作，才会成功。
3.6 删除分区
ALTER TABLE student3 DROP PARTITION(sex='weizhi');
4. 删除表
DROP TABLE [IF EXISTS] {table_name};
DML操作
源数据
student1.txt
1001 shiny 23
1002 cendy 22
1003 angel 23
1009 ella 21
1012 eva 24
student2.txt
1005 bob 24
1006 mark 23
1007 leo 22
1011 JACK 23
1014 JAMES 24
1.加载数据
1.1 put
hadoop fs -put /home/tyhawk/student1.txt /ducl/student2 //从本地放到hdfs
1.2 load
普通存放数据
load data local inpath '/home/tyhawk/student2.txt' into table student;
存放数据到指定分区
load data inpath '/student1.txt' into table student3 partition(sex='female');
通过查询语句向表中插入数据
insert into table student_buck select * from student;
单个查询语句向表中插入数据
create table student5 as select * from student2;
2. 导出数据
2.1 导出数据到本地
INSERT OVERWRITE LOCAL DIRECTORY '/home/tyhawk/students.txt'
SELECT * FROM student3;
INSERT OVERWRITE LOCAL DIRECTORY '/home/tyhawk/students.txt' row format delimited fields terminated by '\t'
SELECT * FROM student3;
2.2 导出数据到HDFS
INSERT OVERWRITE DIRECTORY '/students'
SELECT * FROM student3;
3. 查询数据
3.1 按学生姓名汇总学生年龄
SELECT name,SUM(age) FROM student_buck GROUP BY name;
3.2 获取班级年龄最大的三个学生
SELECT * FROM student3 ORDER BY age DESC LIMIT 3;
ORDER BY： 会对输入做全局排序，因此只有一个 reducer，只有一个 reduce task 的结果，比如文件名是 000000_0，会导致当输入规模较大时，需要较长的计算时间。
3.3查询学生信息，按照id降序排序
set mapreduce.job.reduces=2; //设置reduce的个数为2
SELECT * FROM student3 SORT BY id DESC;
SORT BY： 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 SORT BY 进行排序，并且设置 mapreduce.job.reduces > 1，则 SORT BY 只保证每个 reducer 的输出有序，不保证全局有序。
3.4 分桶和排序的组合操作，对 id 进行分桶，对 age， id 进行降序排序（先对 age 进行降序排序，age 相同的情况下对 id 进行降序排序）
>set mapreduce.job.reduces; ##查看reduce的个数
>INSERT OVERWRITE LOCAL DIRECTORY '/home/tyhawk/students_buck'
SELECT * FROM student3 DISTRIBUTE BY id SORT BY age DESC,id DESC;
DISTRIBUTE BY(字段)：根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。
3.5 分桶和排序的组合操作，等于DISTRIBUTE BY + SORT BY。对 id 进行分桶，对 id 进行升序排
INSERT OVERWRITE LOCAL DIRECTORY '/home/lan/data/students_buck'
SELECT * FROM student3 CLUSTER BY id ;
CLUSTER BY(字段)： 除了具有 DISTRIBUTE BY 的功能外，还会对该字段进行排序。
补充
1. 动态分区表
1.1 准备数据
data.txt
5 男 16 北京 13754554587 2015-03-24
6 女 17 北京 13872374170 2017-02-20
2 女 16 北京 17938844884 2015-05-26
23 男 15 北京 13700000033 2016-07-25
4 男 17 北京 15257575773 2017-08-11
3 女 15 北京 15885888889 2018-05-03
10 男 16 北京 14098593989 2018-04-06
15 女 14 北京 14938983000 2019-06-24
1.2 创建表
create table stu_messages(
stu_id int,
sex string,
age int,
address string,
tel_num string,
ts date
)
row format delimited
fields terminated by '\t';
1.3 加载数据
load data local inpath '/home/tyhawk/stu_messages' into table stu_messages;
2. 动态分区
2.1 包含动态分区的字段的表
创建表
create table if not exists stu_mess(
stu_id int,
sex string,
age int,
address string,
tel_num string,
ts date,
y int,
m int
)
row format delimited
fields terminated by "\t"
stored as textfile;
加载数据
insert into table stu_mess select stu_id,sex,age,address,tel_num,ts,year(ts)as y,month(ts)as m from stu_messages;
2.2 关闭严格分区模式
分区模式，默认nostrict
set hive.exec.dynamic.partition.mode=nonstrict
开启动态分区,默认true
set hive.exec.dynamic.partition=true
最大动态分区数,默认1000
set hive.exec.max.dynamic.partitions=1000
动态分区模式时是严格模式，也就是至少有一个静态分区。
2.3 创建带有分区的表
创建表
create table if not exists stu_mess_part(
stu_id int,
sex string,
age int,
address string,
tel_num string,
ts date
)
partitioned by (y int,m int)
row format delimited
fields terminated by "\t"
stored as textfile;
加载数据
insert overwrite table stu_mess_part partition(y,m) select * from stu_mess;
2. 桶表
2.1 为什么存在桶表？
分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成
合理的分区，尤其是需要确定合适大小的分区划分方式，（不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况）。分桶是将数据集分解为更容易管理的若干部分的另一种技术。
2.2 什么是桶表？
对于每一个表（table）或者分区，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。
Hive也是针对某一列进行桶的组织。
2.3 怎么分桶？
Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
2.4 有什么优点？
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询
时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用连接。比如 JOIN 操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。
(2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
2.5 实例
data.txt
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA
2.5.1第一个分桶
创建表
create table student(sno int,
sname string,
sex string,
sage int,
sdept string)
clustered by(sno) into 3 buckets
row format delimited
fields terminated by ',';
设置分桶（强制分桶）
set hive.enforce.bucketing = true;
加载数据
load data local inpath '/home/hadoop/hivedata/students.txt' overwrite into table student;
我们看到虽然设置了强制分桶，但实际STUDENT表下面只有一个STUDENTS一个文件。分桶也就是分区，分区数量等于文件数，所以上面方法并没有分桶。
2.5.2 第二个分桶表
创建表
create table stu_buck(sno int,sname string,sex string,sage int,sdept string)
clustered by(sno)
sorted by(sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';
设置分桶，设置分区
set hive.enforce.bucketing = true; //设置变量,设置分桶为true
set mapreduce.job.reduces=4; //设置reduce数量是分桶的数量个数
开始往创建的分桶表插入数据(插入数据需要是已分桶, 且排序的)
insert into table stu_buck
select sno,sname,sex,sage,sdept from student distribute by(sno) sort by(sno asc);
#可以使用distribute by(sno) sort by(sno asc) 或是排序和分桶的字段相同的时候使用Cluster by(字段)
#注意使用cluster by 就等同于分桶+排序(sort)
2.6重点
- CLUSTERED BY来指定划分桶所用列和划分桶的个数。Hive对key的hash值除bucket个数取余数，保证数据均匀随机分布在所有bucket里。
- SORTED BY对桶中的一个或多个列另外排序
2.7 总结
其实桶的概念就是MapReduce的分区的概念，两者完全相同。物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同。
而分区表的概念，则是新的概念。分区代表了数据的仓库，也就是文件夹目录。每个文件夹下面可以放不同的数据文件。
通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。
桶则是按照数据内容的某个值进行分桶，把一个大文件散列称为一个个小文件。这些小文件可以单独排序。如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候，就不必要扫描整个表，
只需要匹配相同分桶的数据即可。效率当然大大提升。
同样，对数据抽样的时候，也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。
3 分区又分桶
3.1创建表
create table if not exists stu_mess_part_buck(
stu_id int,
sex string,
age int,
address string,
tel_num string,
ts date
)
partitioned by (y int,m int)
clustered by(stu_id) into 4 buckets
row format delimited
fields terminated by "\t"
stored as textfile;
3.2 加载数据
insert into table stu_mess_part_buck partition(y,m)
select * from stu_mess;
4. 分桶的查询
4.1 基于桶抽样
select * from stu_mess_part_buck tablesample(bucket 1 out of 3 on sex);
注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUTOF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例
例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，
当y=128时，抽取(64/128=)1/2个bucket的数据。
x表示从哪个bucket开始抽取。
例如，table总bucket数为32，tablesample(bucket 3 out of 16)，
表示总共抽取（32/16=）2个bucket的数据，
分别为第3个bucket和第（3+16=）19个bucket的数据。
4.2 基于百分比抽样
hive另外一种按照抽样百分比进行抽样的方式，该种方式基于行数，按照输入路径下的数据块的百分比进行抽样。这种抽样的最小单元是一个hdfs数据块，如果表的数据大小小于普通块大小128M,将返回所有行。
基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优种子信息：
<property>
<name>hive.sample.seednumber</name>
<value>0</value>
</property>
5. 数据分桶存在的一些缺陷
如果通过数据文件LOAD 到分桶表中，会存在额外的MR负担。
实际生产中分桶策略使用频率较低，更常见的还是使用数据分区
扫一扫
927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
