HiveQL快速使用

hive命令包括Hive cli 和 hiveQL命令

Hive cli

cli 就是命令行界面，可以通过cli创建表，检查模式和查询表。

代码语言：javascript复制

hive --help --service cli

显示cli所提供的命令的选项列表

hive -e

代码语言：javascript复制

hive -e 'select * from default.student';

hive -e 可以直接执行sql命令

hive -s

代码语言：javascript复制

hive -S -e 'show tables' > a.txt

hive -s 可以将数据存储到指定的目录

hive -f

代码语言：javascript复制

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命令

代码语言：javascript复制

! cat    !pwd

使用 -- 表示注释

代码语言：javascript复制

-- this is a command

执行 select * from table;后可能不会显示表的头，可以通过hiveconf配置

代码语言：javascript复制

set hive.cli.print.header = true

hive dfs

在hive中执行hadoop命令，只要去掉hadoop，只以dfs开头就行

代码语言：javascript复制

dfs -ls

代码语言：javascript复制

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数据库一致

代码语言：javascript复制

create database shopdb;

hiveQL对表的创建具有很显著的扩展，可以定义表的存储位置，以及用什么格式存储。

代码语言：javascript复制

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;

最终创建数据格式如下：

row format delimited fields terminated by ',' 列分割符

collection items terminated by '_';数组分割符

代码语言：javascript复制

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 ':';

最终创建数据格式如下：

代码语言：javascript复制

create table t_person(
id int,
name string,
address struct<city:string,street:string>  )
row format delimited
fields terminated by ','
collection items terminated by '_';

默认表存储在/user/hive/warehourse下

通常创建的都是内部表（管理表）但是管理表不方便和其他工作共享数据。

外部表

主要解决其他工具创建的数据也想使用hive进行处理数据，可以创建外部表指向这部分数据。

主要在创建时加 external 关键词

查看，修改与删除表与mysql基本一致.

分区表

分区表的使用时在创建表的时候创建好分区表，然后将信息添加进去。每一个分区表会行成一个文件夹。

代码语言：javascript复制

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');

代码语言：javascript复制

alter table test1.order_created_partition
  add partition (event_month='2018-01');

桶表

启用桶表

代码语言：javascript复制

vim ~/.hiverc
添加：set hive.enforce.bucketing = true;

创建桶表

代码语言：javascript复制

create table btable1 (id int) clustered by(id) into 4 buckets;

创建中间表

代码语言：javascript复制

create table btest2(id int);
load data local inpath 'btest2' into table btest2;

桶表中插入数据

代码语言：javascript复制

insert into table btest1 select * from btest2;

修改桶数量

代码语言：javascript复制

alter table btest3 clustered by(name,age) sorted by(age) into 10 buckets;

抽样查询

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

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，属于优化操作.

代码语言：javascript复制

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必须满足如下要求 字段名字一样 字段类型一样 字段个数一样 子表不能有别名 如果需要从合并之后的表中查询数据，那么合并的表必须要有别名

代码语言：javascript复制

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;

抽样查询

对于数据量大的问题，有时用户需要使用一个具有代表性的查询语句，而不是全部结果，可以通过分桶抽样实现。

代码语言：javascript复制

select * from table_name tablesample(bucket X out of Y on field);

X表示从哪个桶中开始抽取，Y表示相隔多少个桶再次抽取。

数据块抽样

代码语言：javascript复制

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阶段是随机分布的，案例如下

代码语言：javascript复制

select * from table_name where col=xxx distribute by rand() sort by rand() limit num; 

2）使用order 关键词 案例如下：

代码语言：javascript复制

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），以下是示例：

代码语言：javascript复制

class SimpleUDFExample extends UDF {  
    
  public Text evaluate(Text input) {  
    return new Text("Hello "   input.toString());  
  }  
}  

hive运行

代码语言：javascript复制

%> 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;  

hive mapreduce 数据库 hadoop sql

0 人点赞