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

谢谢大家，本文完！

这篇好文章是转载于：学新通技术网