HiveQL快速使用
hive命令包括Hive cli 和 hiveQL命令
Hive cli
cli 就是命令行界面，可以通过cli创建表，检查模式和查询表。
hive --help --service cli
显示cli所提供的命令的选项列表
hive -e
hive -e 'select * from default.student';
hive -e 可以直接执行sql命令
hive -s
hive -S -e 'show tables' > a.txt
hive -s 可以将数据存储到指定的目录
hive -f
vi hfile.sql
select * from default.student ;
hive -f hfile.sql
hive -f 可以执行sql脚本
--define可以定义用户变量
--hivevar可以定义用户遍历
--hiveconf使用key-value得到hive-site.xml配值的变量
hive shell
hive中使用！可以支持shell命令
! cat !pwd
使用 -- 表示注释
-- this is a command
执行 select * from table;后可能不会显示表的头，可以通过hiveconf配置
set hive.cli.print.header = true
hive dfs
在hive中执行hadoop命令，只要去掉hadoop，只以dfs开头就行
dfs -ls
dfs -put /data /user/pcap/data
同时要注意hive中struct,map,array数据类型
所有的数据类型都是Java接口的实现，所有所有的具体行为细节和实现与对应的java是一致的。
BINARY和关系型数据库VARBINARY数据类型相似，但是和BLOB数据类型不同，因为BINARY的列是存储在记录中的，而BLOB不是，BLOB是一个可以存储二进制文件的容器。
集合的数据类型，STRUCT {FIELD1 string，FIELD2} 那么第一个字段可以通过元素来引用。
集合类型主要包括：array，map，struct等，hive的特性支持集合类型，这特性是关系型数据库所不支持的，利用好集合类型可以有效提升SQL的查询速率。
hiveQL
hiveQL对数据库的创建与修改与mysql数据库一致
create database shopdb;
hiveQL对表的创建具有很显著的扩展，可以定义表的存储位置，以及用什么格式存储。
create table t_person(
id int,
name string,
likes array<string> )
row format delimited
fields terminated by ','
collection items terminated by '_';
load data local inpath 'Documents/hive/t_person.txt' into table t_person;
最终创建数据格式如下：
1,王力宏,唱歌钢琴二胡作曲演戏导演书法
row format delimited
fields terminated by ',' 列分割符
collection items terminated by '_';数组分割符
create table t_person(
id int,
name string,
tedia map<string,string> )
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':';
最终创建数据格式如下：
1,王力宏,性别:男_形象:非常健康
create table t_person(
id int,
name string,
address struct<city:string,street:string> )
row format delimited
fields terminated by ','
collection items terminated by '_';
1,王力宏,台湾省_台北市
默认表存储在/user/hive/warehourse下
通常创建的都是内部表（管理表）但是管理表不方便和其他工作共享数据。
外部表
主要解决其他工具创建的数据也想使用hive进行处理数据，可以创建外部表指向这部分数据。
主要在创建时加 external 关键词
查看，修改与删除表与mysql基本一致.
分区表
分区表的使用时在创建表的时候创建好分区表，然后将信息添加进去。每一个分区表会行成一个文件夹。
CREATE TABLE order_created_partition (
order_number string,
event_time string
)
PARTITIONED BY (event_month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
-- 建表语句，指定分区字段为event_month，这个字段是伪列
-- 会在数据load到表的这个分区时，在hdfs上创建名为event_month=2017-12的子目录
LOAD DATA LOCAL INPATH "/tmp/order_created.txt"
OVERWRITE INTO TABLE order_created_partition
PARTITION (event_month='2017-12');
alter table test1.order_created_partition
add partition (event_month='2018-01');
桶表
启用桶表
vim ~/.hiverc
添加：set hive.enforce.bucketing = true;
创建桶表
create table btable1 (id int) clustered by(id) into 4 buckets;
创建中间表
create table btest2(id int);
load data local inpath 'btest2' into table btest2;
桶表中插入数据
insert into table btest1 select * from btest2;
修改桶数量
alter table btest3 clustered by(name,age) sorted by(age) into 10 buckets;
抽样查询
select * from table_name tablesample(bucket X out of Y on field);
X表示从哪个桶中开始抽取，Y表示相隔多少个桶再次抽取。
Y必须为分桶数量的倍数或者因子，比如分桶数为6，Y为6，则表示只从桶中抽取1个bucket的数据；若Y为3，则表示从桶中抽取6/3（2）个bucket的数据
order by
select col1,other...
from table
where conditio
order by col1,col2 [asc|desc]
order by后面可以有多列进行排序，默认按字典排序 order by为全局排序 order by需要reduce操作，且只有一个reduce，与配置无关。数据量很大时，慎用。
从表中读取数据，执行where条件，以col1，col2列的值做成组合key，其他列值作为value，然后在把数据传到同一个reduce中，根据需要的排序方式进行。
groupby
select col1 [,col2] ,count(1),sel_expr（聚合操作）from table
where condition -->Map端执行
group by col1 [,col2] -->Reduce端执行
[having] -->Reduce端执行
select后面非聚合列，必须出现在group by中 select后面除了普通列就是一些聚合操作 group by后面也可以跟表达式，比如substr(col)
特性 使用了reduce操作，受限于reduce数量，设置reduce参数mapred.reduce.tasks 输出文件个数与reduce数相同，文件大小与reduce处理的数据量有关。
问题 网络负载过重 数据倾斜，优化参数hive.groupby.skewindata为true，会启动一个优化程序，避免数据倾斜。
执行流程
从表中读取数据，执行where条件，以col1列分组，把col列的内容作为key，其他列值作为value，上传到reduce，在reduce端执行聚合操作和having过滤。
join
两个表m,n之间按照on条件连接，m中的一条记录和n中的一条记录组成一条新记录。
join等值连接（内连接），只有某个值在m和n中同时存在时。
left outer join左外连接，左边表中的值无论是否在b中存在时，都输出；右边表中的值，只有在左边表中存在时才输出。
right outer join和left outer join相反。
left semi join类似exists。即查找a表中的数据，是否在b表中存在，找出存在的数据。
mapjoin：在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作.
select m.col as col1, m.col2 as col2, n.col3 as col3 from
(select col1,col2 from，test where ... (map端执行)
)m （左表）
[left outer |right outer | left semi] join
n （右表）
on m.col=n.col
where condition (reduced端执行)
set hive.optimize.skewjoin=true;
mapjoin(map side join) 在map端把小表加载到内存中，然后读取大表，和内存中的小表完成连接操作。其中使用了分布式缓存技术。
优点 不消耗集群的reduce资源（reduce相对紧缺）。 减少了reduce操作，加快程序执行。 降低网络负载。
缺点 占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次。 生成较多的小文件。
union all
union all必须满足如下要求 字段名字一样 字段类型一样 字段个数一样 子表不能有别名 如果需要从合并之后的表中查询数据，那么合并的表必须要有别名
select * from (
select * from m
union all
select * from n
)temp;
如果两张表的字段名不一样，要将一个表修改别名同另一个表的字段名一样。
select * from (
select col1,col2 from m
union all
select col1,col3 as col2 from n
)temp;
抽样查询
对于数据量大的问题，有时用户需要使用一个具有代表性的查询语句，而不是全部结果，可以通过分桶抽样实现。
select * from table_name tablesample(bucket X out of Y on field);
X表示从哪个桶中开始抽取，Y表示相隔多少个桶再次抽取。
数据块抽样
create table xxx_new as select * from xxx tablesample(10 percent)
根据hive表数据的大小按比例抽取数据，并保存到新的hive表中。如：抽取原hive表中10%的数据
（注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决）
也可以 tablesample(n M) 指定抽样数据的大小，单位为M。
3）tablesample(n rows) 指定抽样数据的行数，其中n代表每个map任务均取n行数据，map数量可通过hive表的简单查询语句确认（关键词：number of mappers: x)
随机抽样
使用rand()函数进行随机抽样，limit关键字限制抽样返回的数据，其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的，案例如下
select * from table_name where col=xxx distribute by rand() sort by rand() limit num;
2）使用order 关键词
案例如下：
select * from table_name where col=xxx order by rand() limit num;
经测试对比，千万级数据中进行随机抽样 order by方式耗时更长，大约多30秒左右。
hiveQL的视图与索引的创建与mysql基本一致。
hive函数
有类似mysql函数，count(),sin(),exp()，sum()等
UDF
编写Apache Hive用户自定义函数（UDF）有两个不同的接口，一个非常简单，另一个复杂
简单API： org.apache.hadoop.hive.ql.exec.UDF
Text,IntWritable,LongWriable,DoubleWritable等等
复杂API： org.apache.hadoop.hive.ql.udf.generic.GenericUDF
操作内嵌数据结构，如Map，List和Set
用简单UDF API来构建一个UDF只涉及到编写一个类继承实现一个方法（evaluate），以下是示例：
class SimpleUDFExample extends UDF {
public Text evaluate(Text input) {
return new Text("Hello " + input.toString());
}
}
hive运行
%> hive
hive> ADD JAR target/hive-extensions-1.0-SNAPSHOT-jar-with-dependencies.jar;
hive> CREATE TEMPORARY FUNCTION helloworld as 'com.matthewrathbone.example.SimpleUDFExample';
hive> select helloworld(name) from people limit 1000;
