Hive基本概念及操作

1.三种部署方式 ：

1）内嵌模式

元数据服务和HIve服务运行在一个JVM中，同时使用内嵌的Derby数据库作为元数据存储，只能支持同时最多一个用户打开Hive会话。
 

2）本地模式

Hive服务和元数据服务运行在一个JVM中，采用外置的MySQL作为元数据存储。支持多用户同时访问Hive

 

3）远程模式

元数据服务和Hive服务运行在不同的JVM中，这样做的好处是可以将数据层置于防火墙之后，客户端则不需 要数据库验证。

2.Hive命令的使用

安装并配置好Hive环境变量后，在任意路径下，输入hive进入到hive命令行界面

如果需要执行完hive命令后立即退出，可以用hive -e方式

如果不希望看到日志信息，可以加上-S： hive -S -e

如果一次希望执行多条查询语句，可以将查询语句保存在后缀为hql的文件中，使用hive -f 来执行

使用hive查看hdfs上的目录，可以在hive界面中执行：dfs -ls /

使用--来注释语句：select * from test --count the testtable

查看帮助：hive --help --service cli

3.基本数据类型

数据类型	长度	示例
TINYINT	1字节，有符号整数	11
SMALLINT	2字节，有符号整数	11
INT	4字节，有符号整数	11
BIGINT	8字节，有符号整数	11
FLOAT	4字节，单精度浮点数	11.0
DOUBLE	8字节，双精度浮点数	11.0
BOOLEAN	true/false	TRUE
STRING	字符序列	'hello'

此外还支持三种集合数据类型：STRUCT，MAP，ARRAY

数据类型	说明	示例
STRUCT	结构体，可以通过属性名访问，如某列的数据类型为 struct(first string,last string)，第一个元素可以通过列名.first 来访问	struct('tom','mike')
MAP	map是一组键值对元组的集合，可以通过键访问，如列名['first']	map('first','tom','second','mike')
ARRAY	数组是具有相同类型变量的集合，可以通过下标访问，如列名[1]	array('tom','mike')

   

4.存储格式

1）TEXTFILE

2）SEQUENCEFILE

3）RCFILE

4）自定义

在建表时使用STORED AS指定文件存储的格式

TEXTFILE：文本格式，默认格式，数据不压缩，磁盘开销大，数据解析开销大。

SEQUENCEFILE：hadoop中的一种二进制格式，使用方便，可分割，可压缩，按行切分

RCFILE：行列存储相结合，首先按行分块，其次块上的数据按列存储，有利于压缩和快速存取。

具备相当于行存储的数据加载速度和负载适应能力，读优化可以在扫描表时避免不必要的列读取，

使用列维度的压缩可以有效提升存储空间利用率。但在数据加载时性能损失较大。

自定义格式：可以通过实现InputFormat和OutputFormat来自定义

5.数据格式

Hive分隔符

分隔符	描述
\n	换行符，默认行分隔符
^A(Ctrl + A)	文本中以八进制编码\001表示，列分隔符
^B(Ctrl + B)	文本中以八进制\002表示，作为分隔ARRAY，STRUCT或者MAP键值对分隔
^C(Ctrl + C)	文本中以八进制\003表示，用于MAP的键值对分隔

   

也可以指定其他分隔符

create table student (
	name STRING
	age  INT
	course ARRAY<STRING>
	body  MAP<STRING,FLOAT>
	address  STRUCT<STREET:STRING,CITY:STRING,STATE:STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' //指定列分隔符

  COLLECTION ITEMS TERMINATED BY '\002' //指定集合元素分隔符

  MAP KEYS TERMINATED BY '\003' //指定MAP的键值对分隔符

  LINES TERMINATED BY '\n' //指定行分隔符，目前仅支持'\n'

6.Hive数据库操作

创建数据库：CREATE DATABASE test;

查看数据库：SHOW DATABASES;

更改某个数据库存储位置，需要在建表时指定：CREATE DATABASE test LOCATION '/user/temp'

查看已存在的数据库：DESCRIBE DATABASE test;

切换数据库：USE test;

删除数据库：DROP DATABASE IF EXISTS test CASCADE;

IF EXISTS是为了避免库不存在出现的警告信息，CASCADE表示删除库时，连同表一起删除

查看表：SHOW TABLES; 或者 SHOW TABLES IN test;

创建表：CREATE TABLE IF NOT EXISTS test.student (

name STRING COMMENT 'student name',

age INT COMMENT 'student age',

course ARRAY<STRING>,

body MAP<STRING,FLOAT>,

address STRUCT<STREET:STRING,CITY:STRING,STATE:STRING>

)

COMMENT 'the info of student'

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE

LOCATION '/user/hive/warehouse/test.db/student'

查看表字段：DESC student;

查看表级别的注释：

DESC EXTENDED student; 或者 DESC FORMATTED student;

删除表：DROP TABLE IF EXISTS test;

表重命名：ALTER TABLE test RENAME TO test2;

增加分区：ALTER TABLE test ADD PARTITION(x = x1,y = y2) LOCATION '/user/test/x1/y1'

修改分区：ALTER TABLE test ADD PARTITION(x = x1,y = y2) SET LOCATION '/user/test/x1/y1'

删除分区：ALTER TABLE test ADD DROP PARTITION (x = x1,y = y2)

修改列： ALTER TABLE test CHANGE COLUMN id uid INT COMMENT 'the unique id' AFTER name;

(将字段id修改为uid，并且放在name字段之后)

增加列：ALTER TABLE test ADD COLUMNS (new_col INT,new_col2 INT);

删除或替换列：ALTER TABLE test REPLACCE COLUMNS (new_col INT,new_col2 STRING)

（删除了test表中所有字段并且重新定义了字段）

7.管理表和外部表区别

1） 在导入数据到外部表，数据并没有移动到自己的数据仓库目录下，也就是说外部表中的数据并不是由它自己来管理的！而表则不一样；

     2）在删除表的时候，Hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的！

那么，应该如何选择使用哪种表呢？在大多数情况没有太多的区别，因此选择只是个人喜好的问题。但是作为一个经验，如果所有处理都需要由Hive完成，那么你应该创建表，否则使用外部表！

8.数据加载，导出操作

1）装载数据：

LOAD DATA

LOAD DATA INPATH '/user/hadoop/o' INTO TABLE test;

如果需要覆盖已有记录需加上OVERWRITE

LOAD DATA INPATH ‘/user/hadoop/o’ OVERWRITE INTO TABLE test;

如果是分区表，需要指定分区

LOAD DATA INPATH '/user/hadoop/o' OVERWRITE INTO TABLE test PARTITION (part="a");

如果要从本地加载到数据库表中，需要加上local关键字

LOAD DATA LOCAL INPATH ‘/root/hadoop/o’ INTO TABLE test;

   2)通过查询语句插入数据

INSERT OVERWRITE TABLE test SELECT * FROM source;

分区表要指定分区

INSERT OVERWRITE TABLE test PARTITION （part = 'a'）SELECT id,name FROM source;

优化：一次查询多次输出

FROM source

INSERT OVERWRITE TABLE test PARTITION （part = 'a'）SELECT id,name WHERE id >= 0 AND id <= 100;

INSERT OVERWRITE TABLE test PARTITION （part = 'b'）SELECT id,name WHERE id >= 100 AND id <= 200;

INSERT OVERWRITE TABLE test PARTITION （part = 'a'）SELECT id,name WHERE id >= 200 AND id <= 300;

3）利用动态分区向表中插入数据

基于查询参数自动推断出需要创建的分区,Hive会根据最后一个查询字段作为动态分区的依据

使用之前要开启动态分区：set hive.exec.dynamic.partition = true;

INSERT OVERWRITE TABLE test PARTITION(time) SELECT id,modify_time FROM source;

set hive.exec.dynamic.partition.mode = nostrict;

设置为nostrict表示允许所有分区都是动态的，Hive默认不允许所有分区都是动态的，并且静态分区必须在动 态分区之前

其他参数

hive.exec.max.dynamic.partitions.pernode //每个Mapper和Reducer可以创建的最大分区数

hive.exec.max.dynamic.partitions //一条动态分区创建语句能够创建的最大分区数

hive.exec.max.created.files //一个Mapreduce作业能够创建的最大分区数

4）通过CTAS加载数据

CTAS：CREATE TABLE ...AS SELECT的缩写，创建表并加载数据

eg: CREATE TABLE test AS SELECT id,name FROM source;

5）数据导出

通过SELECT查询需要的数据格式，再用INSERT将数据导出到HDFS或者本地

INSERT OVERWRITE DIRECTORY '/user/hadoop/o' SELECT * FROM test;

INSERT OVERWRITE LOCAL DIRECTORY '/user/hadoop/o' SELECT * FROM test;

9. HQL数据查询

1）SELET...FROM语句

SELECT col1,col2 FROM table;

SELECT t.col1,t.col2 FROM table t;

SELECT l.name,r.course FROM (SELECT id,name FROM left) l JOIN (SLECT id,course FROM right) r ON l.id = r.id;

正则表达式指定查询的列

SELECT 'user.*' FROM test LIMIT 100;

在SELECT中使用CASE...WHEN...THEN

SELECT id,name,sex CASE WHEN sex = 'M' THEN '男' WHEN sex = 'F' THEN '女' ELSE '无效' END FROM student;

2)WHERE 语句

SELECT * FROM student WHERE age=18 AND sex='F';

3)GROUP BY 和HAVING语句

SELECT COUNT(*) FROM student GROUP BY age;

SELECT AVG(age) FROM student GROUP BY classId;

**如果查询的字段没有出现在GROUP BY字句的后面，则必须使用聚合函数**

错误语句：SELECT name,AVG(age) FROM student GROUP BY classId;

抛出异常：Expression not in GROUP BY key name

如果想对分组的结果进行过滤，使用HAVING：

SELECT classId,AVG(age) FROM student WHERE sex='F' GROUP BY classId HAVING AVG(age) > 18;

4 )JOIN语句

只支持等值连接

默认内连接：

eg：表1的ID列为 1 2 3 4，表2的ID列为2 3 5 6

执行内连接操作

SELECT t1.id,t2.id FROM table1 t1 JOIN table2 t2 on t1.id = t2.id;

结果：

2 2

        3 3

LEFT/RIGHT OUTER JOIN左/右外连接(使用左/右连接

意味着通过左/右连接得到的结果集中，会包含左/右表中全部记录，而右/左表中没有符合连接的条件的记录会以 NULL出现)

eg: SELECT t1.id,t2.id FROM table1 t1 LEFT OUTER JOIN table2 ON t1.id = t2.id;

结果：

1 NULL

2 2

3 3

4 NULL

SELECT t1.id,t2.id FROM table1 t1 RIGHT OUTER JOIN table2 ON t1.id = t2.id;

结果：

2 2

3 3

NULL 5

NULL 6

FULL OUTER JOIN(全外连接)

结果集中会包含左表和右表中所有记录

eg:SELECT t1.id,t2.id FROM table1 t1 FULL OUTER JOIN table2 ON t1.id = t2.id;

结果：

1 NULL

2 2

3 3

4 NULL

NULL 5

NULL 6

LEFT-SEMI JOIN（左半连接）

Hive特有的语法用来代替SQL中的IN操作

eg:SELECT t1.id FROM table1 t1 LEFT SEMI JOIN table2 t2 ON t1.id = t2.id;

结果：

2

3

上述语句也可以通过内连接实现，但是左半连接要比内连接高效

SELECT t1.id FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

map-side JOIN

需要在HQL中声明：

SELECT /* + MPJOIN(t1) */ t1.id,t2.id FROM table t1 JOIN table t2 ON t1.id=t2.id;

可以在配置中自动开启这个优化

hive.auto.convert.join = true

hive.mapjoin.smalltable.filesize 来定义表的大小默认25000000字节

多表JOIN

SELECT * FROM table1 t1 JOIN table2 ON t1.id = t2.id JOIN table3 t3 ON t1.id=t3.id

4) ORDER BY 和 SORT BY语句

eg:SELECT * FROM student ORDER BY classId DESC,age ASC;   //全局排序，一个reduce

SELECT * FROM student SORT BY classId DESC,age ASC;  //局部排序 reduce不止一个

5）DISTRIBUTE BY 和SORT BY语句

HIve通过DISTRIBUTE BY控制map的输出在reduce中如何划分，DISTRIBUTE BY的意义相当于partition对于mapreduce的意义。

二次排序

eg：SELECT col1.col2 FROM ss DISTRIBUTE BY col1 SORT BY col1,col2;

6）CLUSTER BY

如果在使用DISTRIBUTE BY和SORT BY时，所涉及的列完全相同，并且升序排列可以使用CLUSTER BY代替

7）分桶和抽样

eg:SELECT * FROM test TABLESAMPLE(BUCKET 3 OUT OF 10 ON id);

BUCKET x OUT OF y ON z //y表示分y个桶。x表示取第几个桶，z表示分桶依据为将z列的hash值除以y的余数，如果不指定列，可以采用随机列抽样的方式：

SELECT * FROM test TABLESAMPLE(BUCKET 3 OUT OF 10 ON RAND());

建表前指定分桶表 还需要将hive.enforce.bucketing设置为true

CREATE TABLE buckettable(id INT) CLUSTER By (id) INTO 4 BUCKETS;

则该表被划分为4个桶，插入数据

INSERT OVERWRITE TABLE buckettable SELECT * FROM source；

8）UNION ALL

SELECT r.id.r.price FROM ( SELECT m.id,m.price FROM monday m  UNION ALL SELECT t.id,t.price FROM tuesday t ) r;

10.Hive函数

1）标准函数

以一行的一列或者多列作为参数，返回一个值

eg:to_date(string timestamp),sqrt(double a)

2) 聚合函数

以0行或者多行的0列或者多列为参数，返回一个值

sum(col),avg(),max(),std()

3)表生成函数

0个或者多个输入，产生多列或者多行输出

explode(Array a) //将array的每个元素生成一行

eg: SELECT EXPLODE (ARRAY("a","b","c")) AS s FROM test;

11.自定义函数

1）UDF：UDFTest.java

2）UDAF:UDAFTest.java

3）UDTF:UDTFTest.java

测试代码在：https://github.com/yxfff/hadoop

HiveUDF目录下

运行步骤：

1）添加jar文件，将项目导出为jar文件

add jar  /home/hadoop/udf.jar;

2) 注册函数：

create temporary function myconcat as 'com.xfyan.hive.UDFTest.java';

有效期为当前hive会话，下次需要重新添加jar文件和注册函数