10分钟快速熟悉hive操作命令

1 hive是什么

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。HBase是Hadoop的数据库，一个分布式、可扩展、大数据的存储 。

Hive帮助熟悉SQL的人运行MapReduce任务。因为它是JDBC兼容的，同时，它也能够和现存的SQL工具整合在一起。运行Hive查询会花费很长时间，因为它会默认遍历表中所有的数据。虽然有这样的缺点，一次遍历的数据量可以通过Hive的分区机制来控制。分区允许在数据集上运行过滤查询，这些数据集存储在不同的文件夹内，查询的时候只遍历指定文件夹(分区)中的数据。这种机制可以用来，例如，只处理在某一个时间范围内的文件，只要这些文件名中包括了时间格式。

2 操作数据库

1、创建数据库

-- 创建数据库语法：
CREATE DATABASE IF NOT EXISTS database_name;
--例如：
CREATE DATABASE IF NOT EXISTS userdb;

2、删除数据库

-- 删除数据库语法：
DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name；
-- 例如：
DROP DATABASE IF EXISTS userdb;

3、进入数据库

-- 进入数据库 USE database_name;

show databases; # 查看某个数据库 show tables; # 展示所有表 desc 表名; # 显示表结构 show partitions 表名; # 显示表名分区 show create table_name; # 显示创建表的结构

3 操作表属性

1、显示数据库下面所有表

SHOW TABLES;

2、删除表

DROP TABLE [IF EXISTS] table_name;

3、显示建表结果

SHOW CREATE TABLE table_name;

4、显示表分区

SHOW PARTITION table_name;

5、修改表属性

-- 内部表转外部表
ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘TRUE’);
-- 外部表转内部表
ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘FALSE’);

6、表重命名

ALTER TABLE table_name RENAME TO new_table_name;

7、新增列

ALTER TABLE table_name ADD COLUMNS (cloumn_name string);

8、删除列

ALTER TABLE name DROP [COLUMN] column_name;

9、修改列

ALTER TABLE name CHANGE column_name new_name new_type;

10、删除分区

ALTER TABLE table_name DROP IF EXISTS PARTITION(day=); – 这里分区是day

11、增加分区

-- 分区字段包含:p_hour,p_city,p_loctype
ALTER TABLE table_name IF NOT EXISTS \
ADD PARTITION (p_hour=‘2017113003’, p_city=‘573’, p_loctype=‘MHA’);

12、修改分区、LOCATION

-- 修改location
ALTER TABLE table_name SET LOCATION “new_location_name”;
-- partition修改location路径
ALTER TABLE table_name PARTITION (dt=‘2008-08-08’) SET LOCATION “new_location_name”;
-- 修改分区名
ALTER TABLE table_name PARTITION (dt=‘2008-08-08’) RENAME TO PARTITION (dt=‘20080808’);

4 创建表

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

举例：

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT ‘Employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

5 LOAD DATA语句

语法如下：

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 …)]
-- 说明：
LOCAL是标识符指定本地路径。它是可选的。
OVERWRITE 是可选的，覆盖表中的数据
PARTITION 这是可选的

参数描述：

• LOCAL是标识符，指本地路径，它是可选的
• 命令中带LOCAL，表示从本地文件系统中加载数据，可以是相对路径，绝对路径，目录，URL(file:///user/hive/project/data)
• 命令中不带LOCAL，表示从HDFS加载文件，可以是完整的URL方式或者使用fa.default.name定义的值
• FILEPATH 文件路径，可以是：
• 相对路径，如：project/data/file.txt
• 绝对路径，如：/user/hive/project/data/file.txt
• 完整的URL，如：hdfs://namenode:9000/user/hive/project/data/file.txt
• 目录，如：/user/project/data；表示加载目录下的所有文件
• OVERWRITE：
• 命令中带OVERWRITE，加载数据之前会先清空目标表或分区中的内容
• 命令中不带OVERWRITE，向目标表后者分区追加内容

例如：

• 本地数据加载到表

load data local inpath ‘/opt/test/emp.txt’ \
into table emp_part partition (year=‘2016’, month=‘3’);

6 hive内置运算符

1、算术运算符

2、逻辑运算符

3、复杂运算符

七、hive视图与索引

八、实例

1、把本地文件放在新建表中

-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ LOAD DATA LOCAL INPATH ‘xxx/input.txt’ \
OVERWRITE INTO TABLE table_name;

2、把hdfs文件放在新建表中

-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ \
LOAD DATA INPATH ‘/emp.txt’ INTO TABLE table_name partition (year=‘2016’, month=‘6’);

3、把查询结果放在一个新建表中

描述：

create table 表名 as sql

举例：

create table meiying_rate as \
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b \
on a.pkg_name=b.packageid \
where b.day=20151101 and a.rank<=100000 \
order by a.rank;

4、把查询结果放在本地文件中

描述：

insert overwrite local directory ‘路径’ row format delimited fields terminated by ‘|’ sql

举例：

-- 第一种方式
insert overwrite local directory ‘${dir_in}rate_${DATE}/’ \
row format delimited fields terminated by ‘|’ 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank；
-- 第二种方式
hive>
hive -e ‘select * from testdb2.emp’ » ./emp_export.txt

5、把查询结果放在HDFS文件中

insert overwrite directory ‘${dir_in}rate_${DATE}/’ \
row format delimited fields terminated by ‘|’ 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank；

6、差集使用左右连接

select from 表a right(left) outer join 表b on 条件 where

create table diff_in_bj_idc_mangguo as SELECT BB.uid FROM (SELECT dw_user_lastlogin.uid FROM report_dw_mdp.dw_user_lastlogin where dw_user_lastlogin.day=20151028 and dw_user_lastlogin.app_id =‘X0LuNCmE6t6VuvA1b3EyW4’) AA RIGHT OUTER JOIN (SELECT user_bj_idc_20151028.uid FROM yuankai.user_bj_idc_20151028) BB ON (AA.uid= BB.uid) WHERE AA.uid IS NULL;

7、其他

去重：distinct

分组：group by

排序：order by （若有where放在其后）

8、shell 运行hive

hive>hive -e “insert overwrite local directory ‘${dir_in}rate_${DATE}/’ row format delimited fields terminated by ‘|’ select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank”

7 Hive数据类型

Hive所有数据类型分为两种：

• 基本类型
• 复杂类型

1、基本类型

2、复杂类型

8 存储格式

Hive会为每个创建的数据库在HDFS上创建一个目录，该数据库的表会以子目录形式存储，表中的数据会以表目录下的文件形式存储。对于default数据库，默认的缺省数据库没有自己的目录，default数据库下的表默认存放在/user/hive/warehouse目录下。

1、textfile

textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。

2、SequenceFile

SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。

SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。

3、RCFile

一种行列存储相结合的存储方式。

4、ORCFile

数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。

5、Parquet

Parquet也是一种列式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。