hive学习笔记

http://hive.apache.org/
Create Table

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

创建表时可以通过确定分割符方式用load读取数据
例：

CREATE TABLE test(
id int,
name string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

数据样例：

1,小明1,101-book-movie,beijing:shangxuetang-shanghai:pudong
2,小明2,101-book-movie,beijing:shangxuetang-shanghai:pudong
3,小明3,1o1-book-movie,beijing:shangxuetang-shanghai:pudong
4,小明4,101-book-movie,beijing:shangxuetang-shanghai:pudong
5,小明5,1o1-movie,beijing:shangxuetang-shanghai:pudong
6,小明6,101-book-movie,beijing:shangxuetang-shanghai:pudong
7,小明7,101-book,beijing:shangxuetang-shanghai:pudong
8,小明8,101-book,beijing:shangxuetang-shanghai:pudong
9,小明9,101-book-movie,beijing:shangxuetang-shanghai:pudong

查询表中的数据：

• 数组元素：select likes[0] from test where name="小明1";数组元素名字后面跟数组的序号
• Map元素：select address['keyValue'] from test where name="小明1";Map类型名字后面跟key的值
• struct元素（样例中没有）：用.取值select yourStruct.name from test;通过struct名.属性名的方式取值

优化hive显示：
在hive-site.xml中添加以下参数，就可以实现显示当前数据库，以及查询表的头信息配置。

<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

上传文件的方式：

• 通过load方式上传本地文件：load data local inpath 'localPath' into table tableName;
• 通过load方式上传HDFS文件：load data inpath 'hdfsPath' into table tableName;
  两种方式的区别在于：上传本地文件时上传本地文件的副本，而上传hdfs上的文件是将文件剪切到/user/hive/warehouse/表名目录下。
• 通过put方式上传本地文件：由于load方式本身是使用put方法上传，所以可以直接上传与所建表分隔符相同的文件到hdfs的相应目录下，hadoop fs -put 'localPath' /user/hive/warehouse/表名
• insert方式效率太低，一般不用，语法与sql相同：insert into table 表名 values(value1, value2);

创建表时不仅可以通过确定分隔符使用load方式读取批量数据 也可通过正则表达式方式
例：

CREATE TABLE logtbl 
(    
host STRING,
identity STRING,
t_user STRING,
time STRING,  
request STRING,
referer STRING,
agent STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
STORED AS TEXTFILE;

数据样例：

192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -

外部表：
外部表与内部表的区别在于外部表在删除后只会删除表信息，不会删除源数据，可以理解为仅删除了mysql表中的meta store的索引信息，而HDFS中的数据并没有删除。如果在同一路径下再次建表，可以查询到数据。
创建外部表仅比内部表在create后面多一个external。

外部表与内部表的转换：
查询表的类型：desc formatted stu;可以在Table Type中看到是什么表。
修改内部表student为外部表：alter table stu set tblproperties('EXTERNAL'='TRUE');此处的EXTERNAL必须为大写，如果是小写的话，仅给表增加了一条属性信息，并没有改变Table Type。

分区表：

• 分区表的创建：

create table tableName(id int, name string)
partitioned by (month string)
row format delimited
fields terminated by '\t';

• 加载数据到分区表：

load data local inpath 'filePath' into table tableName partition(month='01');

分区表创建后在MySQL中的metastore数据库的partitions中查到分区信息。
在查询时可以将分区信息当做普通参数来查询，类似于select * from tableName where month='01';

• 增加分区使用alter table tableName add partition(month='02'); 创建多个分区使用空格隔开。
• 删除分区使用alter table tableName drop partition(month='02');删除多个分区使用逗号隔开。两种操作的分隔符不一样，比较扯。
• 修复分区信息的方法：
  1. 使用msck repair table tableName;用于修复metastore中没有分区信息但是HDFS有数据的情况。
  2. 手动添加分区alter table tableName add partition(month='01');

导出查询结果：
将查询结果格式化导出到本地：

insert overwrite local directory 'localPath' 
row format delimited fields terminated by '\t'
select * from stu;

导出到HDFS的话没有local即可 。

常用函数：

• count()
• max()
• min()
• sum()
• avg()

在hive里使用函数：

package com.hadoop.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class TuoMin extends UDF{
	public Text evaluate(final Text s) {
		if(s == null) {
			return null;
		}
		String str = s.toString().substring(0, 3) + "***";
		return new Text(str);
	}
}

打包后上传到集群，hive里执行

add jar /root/tuomin.jar;
CREATE TEMPORARY FUNCTION tm AS 'com.hadoop.hive.Tuomin';  //tm为hive内调用的函数名，后跟包名+类名

使用举例：

select tm(name) from ps4;

动态分区：先将数据导入test1，然后创建带分区的test2，将test1的数据导入到test2

CREATE TABLE test2(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int,sex string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';


from test1
insert into test2 partition(age,sex)
select id,name,likes,address,age,sex distribute by age,sex;

分桶操作：

create table psn3(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';


create table psnbucket(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited
fields terminated by ',';


1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88

from psn3
insert into table psnbucket
select id,name,age;

虚拟表：用于解决UDTF查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

select count(distinct(col1)),count(distinct(col2))from test
lateral view explode(likes) test as col1
lateral view explode(address) test as col2,col3;

创建索引：

create index t1_index on table test1(name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild
in table t1_index_table;

添加索引内容：

alter index t1_index on test1 rebuild;

hive运行脚本方式：

进入hive，执行查询，并退出

#hive -e ‘select * from test’

进入hive，执行查询，并退出，结果输出到文件aaa

#hive -e ‘select * from test’ > aaa

进入hive，执行查询，并退出，结果输出到文件aaa，整个过程静默执行

#hive -S -e ‘select * from test’ > aaa

将语句写进文件，然后执行

#hive -f file

执行文件里的命令

>source test

hive排序

• order by 对于查询结果做全排序，只允许有一个reduce处理
  （当数据量较大时，应慎用。严格模式下，必须结合limit来使用）
• sort by-对于单个reduce的数据进行排序
• Distribute By-分区排序，经常和SortBy结合使用（Distribute By相当于mr中的partition，与sort by结合使用相当于对每个reduce分别排序，硬在执行前先设置reduce数量set mapreduce.job.reduces=4;）

insert overwrite local directory 'filePath' select * from stu distribute by className sort by id desc;

• ClusterBy-相当于Sort By+Distribute By
  （Cluster By不能通过asc、desc的方式指定排序规则；
  可通过distribute by column sort by column ascldesc的方式）
  （排序默认为升序，如果要降序应在最后加入desc）