hive的基本语法汇总（hql）

2019/2/20 星期三

hive的基本语法汇总（hql）
——————————————————————————————————————————————
Hive学习3：Hive三种建表语句详解 https://blog.csdn.net/qq_36743482/article/details/78383964
Hive建表方式共有三种：
1、直接建表法
例如：create table table_name(col_name data_type);
2、查询建表法
例如：通过AS 查询语句完成建表：将子查询的结果存在新表里，有数据，一般用于中间表
3、like建表法
会创建结构完全相同的表，但是没有数据。常用语中间表
//详细解释见链接

Hive文件格式（表STORE AS 的四种类型）：https://blog.csdn.net/hereiskxm/article/details/42171325
hive文件存储格式包括以下几类：
1、TEXTFILE
2、SEQUENCEFILE //序列文件
3、RCFILE
4、ORCFILE(0.11以后出现)
小结
其中TEXTFILE为默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；
SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入SequenceFile,RCFile,ORCFile表中。
//详细解释见链接
小结
1、textfile默认格式，数据不做压缩，磁盘开销大，数据解析开销大。
可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，
从而无法对数据进行并行操作。
2、SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。
SequenceFile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。
3、RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。
4、ORCFILE()
总结:
相比TEXTFILE和SEQUENCEFILE，RCFILE由于列式存储方式，数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取，因此，整体来看，RCFILE相比其余两种格式具有较明显的优势。

set hive.cli.print.header=true; //hive客户端打印头部

CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING) //分区
ROW FORMAT DELIMITED //这句话的意思是，文件里的一行就是我们这个表中的一条记录
FIELDS TERMINATED BY '\001' //我们用'tab'作为分隔符
STORED AS SEQUENCEFILE; //存储为序列文件

//sequencefile
create table tab_ip_seq(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as sequencefile;

//external 外部表
CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,ip STRING,country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/external/user';

//从本地导入数据到hive 的表中（实质就是将文件上传到hdfs 中hive 管理目录下）
load data local inpath '/home/hadoop/ip.txt' into table tab_ext;

//从hdfs 上导入数据到hive 表中（实质就是将文件从原始目录移动到hive 管理的目录下）
load data inpath 'hdfs://ns1/aa/bb/data.log' into table tab_user;

//使用select 语句来批量插入数据
insert overwrite table tab_ip_seq select * from tab_ext;

//create & load
create table tab_ip(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as textfile;

// CTAS 根据select 语句建表结构
CREATE TABLE tab_ip_ctas
AS
SELECT id new_id, name new_name, ip new_ip,country new_country FROM tab_ip_ext
SORT BY new_id; //排序方式按照new_id

//CLUSTER <--相对高级一点，你可以放在有精力的时候才去学习> //此部分为分桶表的意思
create table tab_ip_cluster(id int,name string,ip string,country string)
clustered by(id) into 3 buckets; //由（id）聚集成3个桶

load data local inpath '/home/hadoop/ip.txt' overwrite into table tab_ip_cluster;
set hive.enforce.bucketing=true; //hive执行分桶
insert into table tab_ip_cluster select * from tab_ip;

select * from tab_ip_cluster tablesample(bucket 2 out of 3 on id);

//PARTITION 分区表
create table tab_ip_part(id int,name string,ip string,country string)
partitioned by (year string)
row format delimited
fields terminated by ',';

load data local inpath '/home/hadoop/data.log' overwrite into table tab_ip_part
partition(year='1990');

load data local inpath '/home/hadoop/data2.log' overwrite into table tab_ip_part
partition(year='2000');

上面的意思是把/home/hadoop/data2.log数据插入到tab_ip_part下2000的这个分区中

select from tab_ip_part;
select from tab_ip_part where part_flag='part2';
select count(*) from tab_ip_part where part_flag='part2';

更改命令
alter table tab_ip change id id_alter string;
ALTER TABLE tab_cts ADD PARTITION (partCol = 'dt') location '/external/hive/dt';

show partitions tab_ip_part; //查看分区情况

HIVE-分桶表的详解和创建实例 https://www.cnblogs.com/kouryoushine/p/7809299.html
Hive的分区表和分桶表的区别 https://blog.csdn.net/jenrey/article/details/80588493
Hive 基础之：分区、桶、Sort Merge Bucket Join https://blog.csdn.net/wisgood/article/details/17186107
hive的分区分桶为高级功能，可以深入学习了解 （参考xinghuan资料）

//insert from select 通过select 语句批量插入数据到别的表
create table tab_ip_like like tab_ip;
insert overwrite table tab_ip_like select * from tab_ip;

//write to hdfs 将结果写入到hdfs 的文件中
insert overwrite local directory '/home/hadoop/hivetemp/test.txt' select from tab_ip_part where part_flag='part1';
insert overwrite directory '/hiveout.txt' select from tab_ip_part where part_flag='part1';

//cli shell 通过shell 执行hive 的hql 语句
hive -S -e 'select country,count() from tab_ext' > /home/hadoop/hivetemp/e.txt
select from tab_ext sort by id desc limit 5;
select a.ip,b.book from tab_ext a join tab_ip_book b on(a.name=b.name);

hive复杂格式array,map,struct使用 ： https://blog.csdn.net/u010670689/article/details/72885944
//array 数组
create table tab_array(a array<int>,b array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

select a[0] from tab_array;
select * from tab_array where array_contains(b,'word');
insert into table tab_array select array(0),array(name,ip) from tab_ext t;

Hive中数组的使用 https://blog.csdn.net/zhao897426182/article/details/78347960

//map //映射
create table tab_map(name string,info map<string,string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',' //收集物品终止,
map keys terminated by ':'; //映射键终止

load data local inpath '/home/hadoop/hivetemp/tab_map.txt' overwrite into table tab_map;
insert into table tab_map select name,map('name',name,'ip',ip) from tab_ext;

//struct
create table tab_struct(name string,info struct<age:int,tel:string,addr:string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','

load data local inpath '/home/hadoop/hivetemp/tab_st.txt' overwrite into table tab_struct;
insert into table tab_struct select name,named_struct('age',id,'tel',name,'addr',country) from tab_ext;

//UDF 函数 这一步运维人员可以忽略，有兴趣可以学习
select if(id=1,first,no-first),name from tab_ext;
hive>add jar /home/hadoop/myudf.jar;
hive>CREATE TEMPORARY FUNCTION fanyi AS 'cn.itcast.hive.Fanyi';
select id,name,ip,fanyi(country) from tab_ip_ext;

Hive的UDF是什么 https://blog.csdn.net/yqlakers/article/details/70211522
首先什么是UDF，UDF的全称为user-defined function，用户定义函数，为什么有它的存在呢？有的时候 你要写的查询无法轻松地使用Hive提供的内置函数来表示，通过写UDF，Hive就可以方便地插入用户写的处理代码并在查询中使用它们，相当于在HQL（Hive SQL）中自定义一些函数。首先UDF必须用java语言编写，Hive本身就是用java写的。所以想学好hadoop这个分布式框架的相关技术，熟练使用java就是基本功了！

作者：YQlakers
来源：CSDN
原文：https://blog.csdn.net/yqlakers/article/details/70211522
版权声明：本文为博主原创文章，转载请附上博文链接！

hql查询进阶 //hive的查询语法
hive中order by,sort by, distribute by, cluster by作用以及用法 ：https://blog.csdn.net/jthink_/article/details/38903775
MapReduce 脚本
连接（join）
内连接（inner join）
外连接（outer join）
半连接（semi join）
Map 连接（map join）
子查询（sub query）
视图（view）
通过Hive 提供的order by 子句可以让最终的输出结果整体有序。但是因为Hive是基于Hadoop 之上的，要生成这种整体有序的结果，就必须强迫Hadoop 只利用一个Reduce 来完成处理。这种方式的副作用就是回降低效率。

如果你不需要最终结果整体有序，你就可以使用sort by 子句来进行排序。这种排序操作只保证每个Reduce 的输出是有序的。如果你希望某些特定行被同一个Reduce 处理，则你可以使用distribute（分发） 子句来完成。比如：
表student（classNo，stuNo，score）数据如下：
C01 N0101 82
C01 N0102 59
C02 N0201 81
C01 N0103 65
C03 N0302 92
C02 N0202 82
C02 N0203 79
C03 N0301 56
C03 N0306 72
我们希望按照成绩由低到高输出每个班级的成绩信息。执行以下语句：
Select classNo,stuNo,score from student distribute byclassNo sort by score;
输出结果为:
C02 N0203 79
C02 N0201 81
C02 N0202 82
C03 N0301 56
C03 N0306 72
C03 N0302 92
C01 N0102 59
C01 N0103 65
C01 N0101 82
我们可以看到每一个班级里所有的学生成绩是有序的。因为同一个classNo 的记录会被分发到一个单独的reduce 处理，而同时sort by 保证了每一个reduce的输出是有序的。
注意：
为了测试上例中的distribute by 的效果，你应该首先设置足够多的reduce。比如上例中有3 个不同的classNo，则我们需要设置reduce 个数至少为3 或更多。如果设置的reduce 个数少于3，将会导致多个不同的classNo 被分发到同
一个reduce，从而不能产生你所期望的输出。设置命令如下：
set mapred.reduce.tasks = 3;

MapReduce 脚本
如果我们需要在查询语句中调用外部脚本，比如Python，则我们可以使用
transform（转变），map，reduce 等子句。
比如，我们希望过滤掉所有不及格的学生记录，只输出及格学生的成绩信息。
新建一个Python 脚本文件score_pass.py，内容如下：
#! /usr/bin/env python
import sys
for line in sys.stdin:
(classNo,stuNo,score)= line.strip().split('\t')
ifint(score) >= 60:
print"%s\t%s\t%s" %(classNo,stuNo,score)

执行以下语句
add file /home/user/score_pass.py;
select transform(classNo,stuNo,score) using'score_pass.py' as classNo,stuNo,score from student;
输出结果为：
C01 N0101 82
C02 N0201 81
C01 N0103 65
C03 N0302 92
C02 N0202 82
C02 N0203 79
C03 N0306 72
注意：
1) 以上Python 脚本中，分隔符只能是制表符(\t)。同样输出的分隔符也必须为制表符。这个是有hive 自身决定的，不能更改，不要尝试使用其他分隔符，否则会报错。同时需要调用strip 函数，以去除掉行尾的换行符。（或者直接使用不带参数的line.split()代替。
2) 使用脚本前，先使用add file 语句注册脚本文件，以便hive 将其分发到Hadoop 集群。
3) Transfom 传递数据到Python 脚本，as 语句指定输出的列。

连接（join）
直接编程使用Hadoop 的MapReduce 是一件比较费时的事情。Hive 则大大简化了这个操作。
内连接（inner join）
和SQL 的内连相似。执行以下语句查询每个学生的编号和教师名：
Select a.stuNo,b.teacherName from student a join teacherb on a.classNo = b.classNo;
输出结果如下：
N0203 Sun
N0202 Sun
N0201 Sun
N0306 Wang
N0301 Wang
N0302 Wang
N0103 Zhang
N0102 Zhang
N0101 Zhang

注意：
数据文件内容请参照上一篇文章。
不要使用select xx from aa,bb where aa.f=bb.f 这样的语法，hive 不支持这种写法。
如果需要查看hive 的执行计划，你可以在语句前加上explain，比如：
explain Select a.stuNo,b.teacherName from student a jointeacher b on a.classNo = b.classNo;

外连接（outer join）
和传统SQL 类似，Hive 提供了left outer join，right outer join，full out join。

半连接（semi join）
Hive 不提供in 子查询。此时你可以用leftsemi join 实现同样的功能。执行以下语句：
Select * from teacher left semi join student onstudent.classNo = teacher.classNo;
输出结果如下：
C02 Sun
C03 Wang
C01 Zhang
可以看出，C04 Dong 没有出现在查询结果中，因为C04 在表student 中不存在。
注意：
右表（student）中的字段只能出现在on 子句中，不能出现在其他地方，比如不能出现在select 子句中。

Map 连接（map join）
当一个表非常小，足以直接装载到内存中去时，可以使用map 连接以提高效率，
比如：
Select /+mapjoin(teacher) / a.stuNo,b.teacherNamefrom student a join teacher b on a.classNo = b.classNo;
以上红色标记部分采用了C 的注释风格。
当连接时用到不等值判断时，也比较适合Map 连接。具体原因需要深入了解Hive和MapReduce 的工作原理。

子查询（sub query）
运行以下语句将返回所有班级平均分的最高记录。
Select max(avgScore) as maScore from
(Select classNo,avg(score) as avgScore from student group byclassNo) a;
输出结果：
80.66666666666667
以上语句中红色部分为一个子查询，且别名为a。返回的子查询结果和一个表类似，可以被继续查询。

视图（view）
和传统数据库中的视图类似，Hive 的视图只是一个定义，视图数据并不会存储到文件系统中。同样，视图是只读的。
运行以下两个命令：
Create view avg_score as
Select classNo,avg(score) as avgScore from student groupby classNo;
Select max(avgScore) as maScore From avg_score;
可以看到输出结果和上例中的结果是一样的。

hive 数据类型
---基本类型
---复合类型