Hive教程（05）- Hive命令汇总

01 引言

在前面的教程，已经初步了解了Hive的数据类型，有兴趣的同学可以参阅：

• 《Hive教程（01）- 初识Hive》
• 《Hive教程（02）- Hive安装》
• 《Hive教程（03）- Hive数据模型》
• 《Hive教程（04）- Hive数据类型》

通过前面的学习，我们知道了Hive是什么？怎么安装？数据存放到哪个目录？能存哪种类型？

接下来，我们需要知道的是该怎么存？也就是hive的操作命令。

02 命令汇总 2.1 基础命令 2.1.1 启动

启动命令：

hive

2.1.2 退出

--退出hive quit; --exit会影响之前的使用，所以需要下一句kill掉hadoop的进程 exit; hadoop job -kill jobid

2.1.3 数据导入导出 2.1.3.1 数据导入

Hive没有行级别的插入、删除、更新的操作，那么往表里面装数据的唯一的途径就是使用一种“大量”的数据装载操作，或者仅仅将文件写入到正确的目录下面：

这里使用overwrite关键字：

load data local inpath '${env:HOME}/目录' overwrite(可选) into table table_name
    partition (分区)；

注意：如果没有使用overwrite，则会再拷贝一份数据，不会覆盖原来的数据。

2.1.3.2 数据导出

hadoop fs -cp source_path target_path
insert……directory……
e.g insert overwrite local directory '/tmp/目录' -- 这里指定的路径也可以是全URL路径

2.1.4 HSql文件执行 2.1.4.1 控制台执行

hive -f sql_path; e.g hive -f /path/to/file/xxxx.hql; 

2.1.4.2 hive shell 执行

source sql_path; e.g source /path/to/file/test.sql; 

2.1.4.3 一次使用命令

hive -e "SQL语句"；
e.g.  $ hive -e "select * from mytable limit 3"; 

2.1.5 配合nohup使用

nohup hive -f insert.sql >log.log & 

2.2 DDL命令 2.2.1 数据库 2.2.1.1 创建数据库

-- 新建数据库 create database database_name; -- 新建数据库 database_name; creat database if not exists --只是复制了表结构，并不会复制内容 create table test3 like test2; --复制表结构的同时，把内容也复制过来了，需要执行mapreduce create table test2 as select name,addr from test1; --修改数据库的路径 create database database_name location '路径'; 

2.2.1.2 删除数据库

--删除空的数据库 drop database if exists database_name; --先删除数据库中的表再删除数据库 drop database if exists database_name cascade; --删除表t1 drop table t1 CASCADE(可选，忽略错误); --删除数据库的时候，不允许删除有数据的数据库，如果数据库里面有数据则会报错。如果要忽略这些内容，则在后面增加CASCADE关键字，则忽略报错，删除数据库。 t1;--如果存在表t1，删除表t1  drop table if exists CASCADE 

2.2.1.3 修改数据库

alter table table_name rename to another_name; --修改表名 

2.2.1.4 查看数据库

-- 查看数据库 show databases; -- 查看数据库 show databases like 'h.*'; --查看该数据库中的所有表 show tables; --支持模糊查询 show tables ‘*t*’; --查看指定数据库中的所有表 SHOW TABLES IN DbName; --查看表有哪些分区  show partitions t1; --查看表的结构及表的路径 describe formatted(可选) tab_name; --查看数据库的描述及路径 describe database database_name; 

2.2.1.5 使用数据库

--使用哪个数据库 use default; --移动hdfs中数据到t1表中 load data inpath '/root/inner_table.dat' into table t1; --上传本地数据到hdfs中 load data local inpath '/root/inner_table.dat' into table t1; --查询当前linux文件夹下的文件 !ls; --查询当前hdfs文件系统下  '/'目录下的文件 dfs -ls /; --显示地展示当前使用的数据库 set hive.cli.print.current.db=true; --Hive显示列头 set hive.cli.print.header=true; 

2.2.2 表 2.2.2.1 新建表

基础命令：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later) ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later) [AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables) 

创建表：

hive> CREATE TABLE pokes (foo INT, bar STRING); Creates a table called pokes with two columns, the first being an integer and the other a string

创建一个新表，结构与其他一样：

hive> create table new_table like records; 

创建分区表：

hive> create table logs(ts bigint,line string) partitioned by (dt String,country String); 

2.2.2.2 更新表

更新表的名称：

hive> ALTER TABLE source RENAME TO target; 

添加新一列：

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); 

2.2.2.3 删除表

删除表：

hive> DROP TABLE records; 

删除表中数据，但要保持表的结构定义：

hive> dfs -rmr /user/hive/warehouse/records; 

2.2.2.4 查询表

注意：select *不执行mapreduce，只进行一个本地的查询；而select某个字段 生成一个job，执行mapreduce。

基础查询：

hive> select * from employees; OK
tony 1338 ["a1","a2","a3"] {"k1":1.0,"k2":2.0,"k3":3.0}    {"street":"s1","city":"s2","state":"s3","zip":4}
mark 5453 ["a4","a5","a6"] {"k4":4.0,"k5":5.0,"k6":6.0}    {"street":"s4","city":"s5","state":"s6","zip":6}
ivy 323 ["a7","a8","a9"] {"k7":7.0,"k8":8.0,"k9":9.0}    {"street":"s7","city":"s8","state":"s9","zip":9} Time taken: 10.204 seconds, Fetched: 3 row(s) 

查树组：

hive> select subordinates[1] from employees; Total MapReduce CPU Time Spent: 2 seconds 740 msec
OK
a2
a5
a8

查map：

hive> select deductions["k2"] from employees; OK 2.0 NULL NULL Time taken: 75.812 seconds, Fetched: 3 row(s) 

查结构体：

hive> select address.city from employees; Total MapReduce CPU Time Spent: 2 seconds 200 msec
OK
s2
s5
s8 Time taken: 75.311 seconds, Fetched: 3 row(s) 

加载分区表数据：

hive> load data local inpath '/home/hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB'); 

展示表中有多少分区：

hive> show partitions logs; 

展示所有表：

hive> SHOW TABLES; lists all the tables hive> SHOW TABLES '.*s'; 

显示表的结构信息 ：

hive> DESCRIBE invites; 

显示所有函数：

hive> show functions; 

查看函数用法：

hive> describe function substr; 

查看数组、map、结构：

hive> select col1[0],col2['b'],col3.c from complex; 

内连接：

hive> SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id); 

查看hive为某个查询使用多少个MapReduce作业：

hive> Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id); 

外连接：

hive> SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id); hive> SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id); hive> SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id); 

in查询： Hive不支持，但可以使用：

LEFT SEMI JOIN hive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id); 

Map连接： Hive可以把较小的表放入每个Mapper的内存来执行连接操作

hive> SELECT /*+ MAPJOIN(things) */ sales.*, things.* FROM sales JOIN things ON (sales.id = things.id); 

INSERT OVERWRITE TABLE …SELECT： 新表预先存在

hive> FROM records2 > INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year > INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year > INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year; 

CREATE TABLE … AS SELECT：新表表预先不存在

hive>CREATE TABLE target AS SELECT col1,col2 FROM source; 

创建视图：

hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999; 

查看视图详细信息：

hive> DESCRIBE EXTENDED valid_records; 

03 函数

hive里面以内置了很多的函数，查询的时候可以直接调用，同时也可以自定义函数，具体可以参考：

• 内置函数：https://www.hadoopdoc.com/hive/hive-built-in-function
• 自定义函数：https://www.hadoopdoc.com/hive/hive-udf-intro

举例：求绝对值

hive> select abs(-3.9) from iteblog; 3.9 hive> select abs(10.9) from iteblog; 10.9 

04 join原理与机制

Hive中 的Join可分为:

• Common Join（Reduce阶段完成join）
• Map Join（Map阶段完成join）

4.1 Common Join

Hive解析器会默认执行Common Join，即在Reduce阶段完成join。整个过程包含Map、Shuffle、Reduce阶段。

• Map阶段：读取源表的数据，Map 输出时候以 Join on条件中的列为key，如果 Join 有多个关联键，则以这些关联键的组合作为 key；Map输出的value 为 join 之后所关心的（select 或者 where 中需要用到的）列，同时在 value中还会包含表的Tag信息，用于标明此 value 对应哪个表；
• Shuffle阶段：根据 key 的值进行 hash，并将 key/value 按照 hash 值推送至不同的reduce 中，这样确保两个表中相同的key 位于同一个reduce 中；
• Reduce阶段：根据 key 的值完成 join 操作，期间通过 Tag 来识别不同表中的数据。

以下面的HQL为例，图解其过程：

SELECT a.id,a.dept,b.age FROM a join b ON (a.id = b.id); 

4.2 Map Join

MapJoin ：通常用于一个很小的表和一个大表进行 join 的场景。

具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，默认值为 25M。满足条件的话Hive在执行时候会自动转化为MapJoin，或使用hint提示/*+ mapjoin(table) */执行 MapJoin。

解析上图中的流程：

1. 首先Task A在客户端本地执行，负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache中。
2. 接下来的Task B任务是一个没有Reduce的MapReduce，启动MapTasks扫描大表a；
3. 在Map阶段，根据a的每一条记录去和DistributeCache中 b 表对应的HashTable关联，并直接输出结果，因为没有Reduce，所以有多少个Map Task就有多少个结果文件。

Map Join的相关参数：

• hive.auto.convert.join：该参数表示是否自动把任务转为 map join。默认该配置为 true。当一个表的大小小于 25 MB（配置参数：hive.mapjoin.smalltable.filesize）时，表之间的 join 会被自动转成 map join。
• hive.auto.convert.join.noconditionaltask：是否将多个 map join 合并为一个。默认为 true。多个 mapjoin 转换成 1 个时，所有小表的文件大小总和的最大值由hive.auto.convert.join.noconditionaltask.size 配置项控制。
• hive.mapjoin.localtask.max.memory.usage：将小表转成 hashtable 的本地任务的最大内存使用率,默认0.9。
• hive.mapjoin.followby.gby.localtask.max.memory.usage：如果 mapjoin 后面紧跟着一个 group by 任务，这种情况下本地任务的最大内存使用率，默认是0.55。
• hive.mapjoin.check.memory.rows：localtask 每处理完多少行，就执行内存检查。默认为 100000。

注意：Map JOIN 不适合FULL/RIGHT OUTER JOIN。

4.3 bucket map join 4.3.1 bucket map join概念

bucket map join：在Hive中，当表非常大，而且所有需要关联的表都是分桶表，并且关联字段都是分桶字段，那么我们就可以使用bucket map join来关联表。

原理：多个表使用bucket map join来关联的时候，关联操作只会在mapper端进行。换一种方式来理解就是，mapper处理A表的分桶1的时候，它只会从B表的分桶 1 取数据，即分桶之间做关联。

如果一个表的分桶数是 2 ，那么其他表的分桶数必须是 2 或者是 2 的倍数（2，4，6等等），因此如果满足了前面这些条件，那么表的 join 操作就只会在 mapper 端进行，否则，表的 join 就是一个普通的 inner join。

bucket map join 适用于以下场景：

• 所有的表都非常大
• 关联字段使用的是表的分桶字段
• 一个表的分桶数是另一个表的分桶数的倍数
• 所有的表都是不排序的

缺点：

• 使用bucket map join主要的缺点就是必须要根据SQL代码的特点来对表做分桶，也就是说，SQL中的关联字段和表的分桶字段要一致才行，只要关联字段和分桶字段不一致，我们就没办法使用bucket map join了。

4.3.2 bucket map join例子

首先，表的分桶字段与关联字段要保持一致。另外，在插入数据的时候要对表做分桶，其中有一种方式就是在插入数据之前把hive.enforce.bucketing参数设置为true:

set hive.enforce.bucketing=true 

比如：

create table b1( col0 string, col1 string, col2 string, col3 string, col4 string, col5 string, col6 string ) clustered by (col0) into 32 buckets; create table b2( col0 string, col1 string, col2 string, col3 string, col4 string, col5 string, col6 string ) clustered by (col0) into 8 buckets; set hive.enforce.bucketing = true; insert OVERWRITE table b1 select * limit 10000; insert OVERWRITE table b2 select * limit 10000; 

在做关联的时候把下面参数设置为true:

set hive.optimize.bucketmapjoin=true; 

关联代码：

select /*+ MAPJOIN(b2) */ b1.* from b1,b2 where b1.col0=b2.col0; 

4.4 Join的几种类型

Hive 中 join 有 4 种类型。分别是：

• 内关联（inner join 或者简写成 join）
• 左关联（left outer join 或者简写成 left join）
• 右关联（right outer join 或者简写成 right join）
• 全关联（full outer join 或者简写成 full join）

05 文末

本文是结合以下文献整理的博客：

• https://blog.csdn.net/u013084266/article/details/80221918
• https://www.cnblogs.com/zzjhn/p/3855572.html

谢谢大家，本文完！