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_partitionPARTITION (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 tablewhere 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;

作者：张晓天a
链接：https://www.jianshu.com/p/95fb698e50e9