大数据技术之Hadoop-Apache Hive使用语法与概念原理

一、数据库操作

1、数据库操作

• 创建数据库

        create database if not exists myhive;

        use  myhive;

• 查看数据库详细信息

        desc  database  myhive;

数据库本质上就是在HDFS之上的文件夹。

默认数据库的存放路径是HDFS的：/user/hive/warehouse内

• 创建数据库并指定hdfs存储位置

        create database myhive2 location '/myhive2';

使用location关键字，可以指定数据库在HDFS的存储路径。

• 删除一个空数据库，如果数据库下面有数据表，那么就会报错

        drop  database  myhive;

• 强制删除数据库，包含数据库下面的表一起删除

        drop  database  myhive2  cascade;

二、数据表操作

2.1、表操作语法和数据类型

2.1.1、创建数据库表语法

创建表的语法还是比较复杂的

 

• EXTERNAL，创建外部表

• PARTITIONED BY， 分区表

• CLUSTERED BY，分桶表

• STORED AS，存储格式

• LOCATION，存储位置

2.1.2、数据类型

分类	类型	描述	字面量示例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组
	TIMESTAMP	时间戳，毫秒值精度	122327493795
	DATE	日期	‘2016-03-29’
		时间频率间隔
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

Hive中支持的数据类型还是比较多的，其中红色的是使用比较多的类型

2.1.3、基础创建表示意

尽管建表语法比较复杂，目前我们暂时未接触到分区、分桶等概念。

所以，创建一个简答的数据库表可以有如下SQL：

CREATE TABLE test(
	id INT,
    name STRING,
    gender STRING
);

删除表

如果要删除表可以使用：

DROP TABLE table_name;

2.2、内部表操作

 表分类

Hive中可以创建的表有好几种类型， 分别是：

• 内部表
• 外部表
• 分区表
• 分桶表

内部表和外部表

快速对比一下内部表和外部表

	创建	存储位置	删除数据	理念
内部表	CREATE TABLE ......	Hive管理，默认/user/hive/warehouse	• 删除 元数据（表信息） • 删除 数据	Hive管理表 持久使用
外部表	CREATE EXTERNAL TABLE ......	随意，LOCATION关键字指定	• 仅删除 元数据（表信息） • 保留  数据	临时链接外部数据用

创建内部表

内部表的创建语法就是标准的：CREATE TABLE table_name......

创建一个基础的表

create database if not exists myhive;
use myhive;
create table if not exists stu(id int,name string);
insert into stu values (1,"zhangsan"), (2, "wangwu");
select * from stu;

查看表的数据存储

在HDFS上，查看表的数据存储文件

数据分隔符

可以看到，数据在HDFS上也是以明文文件存在的。

奇怪的是， 列ID和列NAME，好像没有分隔符，而是挤在一起的。

这是因为，默认的数据分隔符是：”\001”是一种特殊字符，是ASCII值，键盘是打不出来

在某些文本编辑器中是显示为SOH的。

自行指定分隔符

当然，分隔符我们是可以自行指定的。

在创建表的时候可以自己决定：

create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t';

• row format delimited fields terminated by '\t'：表示以\t分隔

其它创建内部表的形式

除了标准的CREATE TABLE table_name的形式创建内部表外

我们还可以通过：

• CREATE TABLE table_name as，基于查询结果建表

create table stu3 as select * from stu2;

• CREATE TABLE table_name like，基于已存在的表结构建表

create table stu4 like stu2;

• 也可以使用DESC FORMATTED table_name，查看表类型和详情

DESC FORMATTED stu2;

删除内部表

我们是内部表删除后，数据本身也不会保留，让我们试一试吧。

DROP TABLE table_name，删除表

drop table stu2;

 2.3、外部表操作

外部表的创建

外部表，创建表被EXTERNAL关键字修饰，从概念是被认为并非Hive拥有的表，只是临时关联数据去使用。

创建外部表也很简单，基于外部表的特性，可以总结出： 外部表 和 数据 是相互独立的， 即： 

• 可以先有表，然后把数据移动到表指定的LOCATION中
• 也可以先有数据，然后创建表通过LOCATION指向数据 

1. 在Linux上创建新文件，test_external.txt，并填入如下内容：

数据列用’\t’分隔

2. 演示先创建外部表，然后移动数据到LOCATION目录

• 首先检查：hadoop fs -ls /tmp，确认不存在/tmp/test_ext1目录
• 创建外部表：create external table test_ext1(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext1’;
• 可以看到，目录/tmp/test_ext1被创建
• select * from test_ext1，空结果，无数据
• 上传数据： hadoop fs -put test_external.txt /tmp/test_ext1/
• select * from test_ext1，即可看到数据结果

3. 演示先存在数据，后创建外部表

• hadoop fs -mkdir /tmp/test_ext2
• hadoop fs -put test_external.txt /tmp/test_ext2/
• create external table test_ext2(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext2’;
• select * from test_ext2;

删除外部表

drop table test_ext1;
drop table test_ext2;

可以发现，在Hive中通过show table，表不存在了

但是在HDFS中，数据文件依旧保留

内外部表转换

Hive可以很简单的通过SQL语句转换内外部表。

查看表类型：desc formatted stu;

 转换

内部表转外部表

alter table stu set tblproperties('EXTERNAL'='TRUE');

外部表转内部表

alter table stu set tblproperties('EXTERNAL'='FALSE');

通过stu set tblproperties来修改属性

要注意：('EXTERNAL'='FALSE') 或 ('EXTERNAL'='TRUE')为固定写法，区分大小写！！！

2.4、数据加载和导出

数据加载 - LOAD语法

我们使用 LOAD 语法，从外部将数据加载到Hive内，语法如下：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

建表：

CREATE TABLE myhive.test_load(
  dt string comment '时间（时分秒）', 
  user_id string comment '用户ID', 
  word string comment '搜索词',
  url string comment '用户访问网址'
) comment '搜索引擎日志表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

数据：

 

load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load;
load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load;

 除了load加载外部数据外，我们也可以通过SQL语句，从其它表中加载数据。

语法：

INSERT [OVERWRITE | INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

 将SELECT查询语句的结果插入到其它表中，被SELECT查询的表可以是内部表或外部表。

示例：

INSERT INTO TABLE tbl1 SELECT * FROM tbl2;
INSERT OVERWRITE TABLE tbl1 SELECT * FROM tbl2;

 数据加载 - 两种语法的选择

对于数据加载，我们学习了：LOAD和INSERT SELECT的方式，那么如何选择它们使用呢？

数据在本地

        推荐 load data local加载

数据在HDFS

        如果不保留原始文件：    推荐使用LOAD方式直接加载
        如果保留原始文件：    推荐使用外部表先关联数据，然后通过INSERT SELECT 外部表的形式加载数据

数据已经在表中

        只可以INSERT SELECT

hive表数据导出 - insert overwrite 方式

将hive表中的数据导出到其他任意目录，例如linux本地磁盘，例如hdfs，例如mysql等等

语法：insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement;

将查询的结果导出到本地 - 使用默认列分隔符

insert overwrite local directory '/home/hadoop/export1' select * from test_load ;

将查询的结果导出到本地 - 指定列分隔符

insert overwrite local directory '/home/hadoop/export2' row format delimited fields terminated by '\t' select * from test_load;

将查询的结果导出到HDFS上(不带local关键字)

insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from test_load;

 hive表数据导出 - hive shell

基本语法：（hive -f/-e 执行语句或者脚本 > file）

bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

bin/hive -f export.sql > /home/hadoop/export4/export4.txt

为什么需要数据加载

 为什么需要数据加载

两种方式均可以，但推荐选择加载数据到内部表内

外部表就像企业的外聘顾问一样，并非正式成员，在设计上只是临时工，一般用于中转数据或临时使用。

外部表存储位置不固定，权限管控不统一，容易出现数据丢失问题。

复杂类型

Hive支持的数据类型很多，除了基本的：int、string、varchar、timestamp等

还有一些复杂的数据类型：

• array

数组类型

• map

映射类型

• struct

结构类型

array类型

如下数据文件，有2个列，locations列包含多个城市: 

说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

 可以使用array数组类型，存储locations的数据

建表语句：

create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

row format delimited fields terminated by '\t' 表示列分隔符是\t

COLLECTION ITEMS TERMINATED BY ',' 表示集合（array）元素的分隔符是逗号

 导入数据

load data local inpath '/home/hadoop/data_for_array_type.txt' overwrite into table myhive.test_array;

常用array类型查询：

-- 查询所有数据
select * from myhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from myhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from myhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from myhive.test_array where array_contains(work_locations,'tianjin'); 

 map类型

map类型其实就是简单的指代：Key-Value型数据格式。 有如下数据文件，其中members字段是key-value型数据

字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

id,name,members,age
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

建表语句： 

create table myhive.test_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#' 
MAP KEYS TERMINATED BY ':';

MAP KEYS TERMINATED BY ':' 表示key-value之间用:分隔

导入数据

load data local inpath '/home/hadoop/data_for_map_type.txt' overwrite into table myhive.test_map;

常用查询

# 查询全部
select * from myhive.test_map;
# 查询father、mother这两个map的key
select id, name, members['father'] father, members['mother'] mother, age from myhive.test_map;
# 查询全部map的key，使用map_keys函数，结果是array类型
select id, name, map_keys(members) as relation from myhive.test_map;
# 查询全部map的value，使用map_values函数，结果是array类型
select id, name, map_values(members) as relation from myhive.test_map;
# 查询map类型的KV对数量
select id,name,size(members) num from myhive.test_map;
# 查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), 'brother');

struct类型

struct类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称

有如下数据文件，说明：字段之间#分割，struct之间冒号分割

1#周杰轮:11
2#林均杰:16
3#刘德滑:21
4#张学油:26
5#蔡依临:23

建表语句

create table myhive.test_struct(
id string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

 导入数据

load data local inpath '/home/hadoop/data_for_struct_type.txt' into table myhive.test_struct;

常用查询 

select * from hive_struct;
# 直接使用列名.子列名 即可从struct中取出子列查询
select ip, info.name from hive_struct;

2.5、分区表

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了
同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了。

 同时Hive也支持多个字段作为分区，多分区带有层级关系，如图

 基本语法：

create table tablename(...) partitioned by (分区列 列类型, ......) 
row format delimited fields terminated by '';

 

分区表的使用

分区表练习

 需求描述：现在有一个文件score.txt（课程资料有提供）文件，存放在集群的这个目录下/scoredatas/month=202006

这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。

需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除。

2.6、分桶表

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式

但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

 分桶表创建

开启分桶的自动优化（自动匹配reduce task数量和桶数量一致）

set hive.enforce.bucketing=true;

创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

 分桶表数据加载

桶表的数据加载，由于桶表的数据加载通过load  data无法执行，只能通过insert  select.

所以，比较好的方式是

        1. 创建一个临时表（外部表或内部表均可），通过load data加载数据进入表

        2. 然后通过insert select 从临时表向桶表插入数据

 为什么不可以用load data，必须用insert select插入数据

如果没有分桶设置，插入（加载）数据只是简单的将数据放入到：

• 表的数据存储文件夹中（没有分区）
• 表指定分区的文件夹中（带有分区）

• 为什么不可以用load data，必须用insert select插入数据

• 一旦有了分桶设置，比如分桶数量为3，那么，表内文件或分区内数据文件的数量就限定为3
  当数据插入的时候，需要一分为3，进入三个桶文件内。

  

 为什么不可以用load data，必须用insert select插入数据

一旦有了分桶设置，比如分桶数量为3，那么，表内文件或分区内数据文件的数量就限定为3

当数据插入的时候，需要一分为3，进入三个桶文件内。

2.7、修改表

修改表操作

表重命名

alter  table  old_table_name  rename  to  new_table_name;
如：alter table score4 rename to score5;

修改表属性值

ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties:
  : (property_name = property_value, property_name = property_value, ... )
如：ALTER TABLE table_name SET TBLPROPERTIES("EXTERNAL"="TRUE");  修改内外部表属性
如：ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment); 修改表注释
其余属性可参见：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-listTableProperties

添加分区

ALTER TABLE tablename  ADD PARTITION (month='201101');

新分区是空的没数据，需要手动添加或上传数据文件

修改分区值

ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105');

删除分区

ALTER TABLE tablename DROP PARTITION (month='201105');

 修改表操作

添加列

ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string);

修改列名

ALTER TABLE test_change CHANGE v1 v1new INT;

删除表

DROP TABLE tablename;

清空表

TRUNCATE TABLE tablename;
ps:只可以清空内部表

2.8、复杂类型操作

  复杂类型

Hive支持的数据类型很多，除了基本的：int、string、varchar、timestamp等

还有一些复杂的数据类型：

• array

数组类型

• map

映射类型

• struct

结构类型

array类型

如下数据文件，有2个列，locations列包含多个城市: 

说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

可以使用array数组类型，存储locations的数据

建表语句：

create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

row format delimited fields terminated by '\t' 表示列分隔符是\t
COLLECTION ITEMS TERMINATED BY ',' 表示集合（array）元素的分隔符是逗号

 建表语句：

create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

 基于COLLECTION ITEMS TERMINATED BY ',' 设定的array类型的一条数据示意

 

导入数据

load data local inpath '/home/hadoop/data_for_array_type.txt' overwrite into table myhive.test_array;

 常用array类型查询：

-- 查询所有数据
select * from myhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from myhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from myhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from myhive.test_array where array_contains(work_locations,'tianjin'); 

map类型

map类型其实就是简单的指代：Key-Value型数据格式。 有如下数据文件，其中members字段是key-value型数据
字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

id,name,members,age
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

 建表语句：

create table myhive.test_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#' 
MAP KEYS TERMINATED BY ':';

MAP KEYS TERMINATED BY ':' 表示key-value之间用:分隔

建表语句：

create table myhive.test_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#' 
MAP KEYS TERMINATED BY ':';

 导入数据

load data local inpath '/home/hadoop/data_for_map_type.txt' overwrite into table myhive.test_map;

 常用查询

# 查询全部
select * from myhive.test_map;
# 查询father、mother这两个map的key
select id, name, members['father'] father, members['mother'] mother, age from myhive.test_map;
# 查询全部map的key，使用map_keys函数，结果是array类型
select id, name, map_keys(members) as relation from myhive.test_map;
# 查询全部map的value，使用map_values函数，结果是array类型
select id, name, map_values(members) as relation from myhive.test_map;
# 查询map类型的KV对数量
select id,name,size(members) num from myhive.test_map;
# 查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), 'brother');

 struct类型

struct类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称

有如下数据文件，说明：字段之间#分割，struct之间冒号分割

1#周杰轮:11
2#林均杰:16
3#刘德滑:21
4#张学油:26
5#蔡依临:23

 建表语句

create table myhive.test_struct(
id string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

建表语句

create table myhive.test_struct(
id string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

 

导入数据

load data local inpath '/home/hadoop/data_for_struct_type.txt' into table myhive.test_struct;

常用查询

select * from hive_struct;
# 直接使用列名.子列名 即可从struct中取出子列查询
select ip, info.name from hive_struct;

 array、map、struct总结

 

三、数据查询

基本语法

查询语句的基本语法

SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BYcol_list]
[HAVING where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

整体上和普通SQL差不多，部分有区别，如：CLUSTER BY、DISTRIBUTE BY、SORT BY等

这些我们会放入到高阶原理章节进行讲解。

准备数据：订单表

CREATE DATABASE itheima;
USE itheima;
CREATE TABLE itheima.orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id',
    userId bigint COMMENT '用户id',
    orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
    goodsMoney double COMMENT '商品金额',
    deliverMoney double COMMENT '运费',
    totalMoney double COMMENT '订单金额（包括运费）',
    realTotalMoney double COMMENT '实际订单金额（折扣后金额）',
    payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
    isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',
    userName string COMMENT '收件人姓名',
    userAddress string COMMENT '收件人地址',
    userPhone string COMMENT '收件人电话',
    createTime timestamp COMMENT '下单时间',
    payTime timestamp COMMENT '支付时间',
    totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/hadoop/itheima_orders.txt' INTO TABLE itheima.orders;

准备数据：用户表

CREATE TABLE itheima.users (
    userId int,
    loginName string,
    loginSecret int,
    loginPwd string,
    userSex tinyint,
    userName string,
    trueName string,
    brithday date,
    userPhoto string,
    userQQ string,
    userPhone string,
    userScore int,
    userTotalScore int,
    userFrom tinyint,
    userMoney double,
    lockMoney double,
    createTime timestamp,
    payPwd string,
    rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
 

LOAD DATA LOCAL INPATH '/home/hadoop/itheima_users.txt' INTO TABLE itheima.users;

 基本查询

查询所有
SELECT * FROM itheima.orders;

查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM itheima.orders;

查询数据量
SELECT COUNT(*) FROM itheima.orders;

过滤广东省订单
SELECT * FROM itheima.orders WHERE useraddress LIKE '%广东%';

找出广东省单笔营业额最大的订单
SELECT * FROM itheima.orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;

分组、聚合

统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;

在已付款订单中，统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay = 1 GROUP BY userid;


统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;


统计每个用户的平均订单消费额，过滤大于10000的数据

SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money > 10000;

 JOIN

JOIN订单表和用户表，找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o JOIN itheima.users u ON o.userid = u.userid;

左外关联，订单表和用户表，找出用户名

SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o LEFT JOIN itheima.users u ON o.userid = u.userid;

 正则表达式

RLIKE

Hive中提供RLIKE关键字，可以供用户使用正则和数据进行匹配。

我们以上一节中使用的订单表为例，来简单使用一下RLIKE正则匹配。

 

查找广东省的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '.*广东.*';
查找用户地址是：xx省 xx市 xx区的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '..省 ..市 ..区';
查找用户姓为张、王、邓
SELECT * FROM itheima.orders WHERE username RLIKE '[张王邓]\\S+';
查找手机号符合：188****0*** 规则
SELECT * FROM itheima.orders WHERE userphone  RLIKEE '188\\S{4}0\\S{3}';

UNION联合

UNION 用于将多个 SELECT 语句的结果组合成单个结果集。

每个 select 语句返回的列的数量和名称必须相同。否则，将引发架构错误。

准备数据进行测试

CREATE TABLE itheima.course(
c_id string, 
c_name string, 
t_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/course.txt' INTO TABLE itheima.course; 

 联合两个查询结果集

SELECT * FROM course WHERE t_id = '周杰轮'
    UNION 
SELECT * FROM course WHERE t_id = '王力鸿'

UNION默认有去重功能：

直接联合两个同样的查询结果

SELECT * FROM course
UNION 
SELECT * FROM course

如果不需要去重效果

SELECT * FROM course
    UNION ALL 
SELECT * FROM course

 UNION写在FROM中

SELECT t_id, COUNT(*) FROM 
(
	SELECT t_id FROM itheima.course WHERE t_id = '周杰轮'
		UNION ALL
	SELECT t_id FROM itheima.course WHERE t_id = '王力鸿'
) AS u GROUP BY t_id;

 用于INSERT SELECT中

CREATE TABLE itheima.course2 LIKE itheima.course;
INSERT OVERWRITE TABLE itheima.course2
    SELECT * FROM itheima.course
	UNION ALL
    SELECT * FROM itheima.course;

为什么需要抽样表数据

对表进行随机抽样是非常有必要的。

大数据体系下，在真正的企业环境中，很容易出现很大的表，比如体积达到TB级别。

对这种表一个简单的SELECT * 都会非常的慢，哪怕LIMIT 10想要看10条数据，也会走MapReduce流程

这个时间等待是不合适的。

Hive提供的快速抽样的语法，可以快速从大表中随机抽取一些数据供用户查看。

 TABLESAMPLE函数

语法1，基于随机分桶抽样：

SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

y表示将表数据随机划分成y份（y个桶）
x表示从y里面随机抽取x份数据作为取样
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行

 示例：

SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

注意：

使用 colname 作为随机依据，则其它条件不变下，每次抽样结果一致

使用 rand() 作为随机依据，每次抽样结果都不同

语法2，基于数据块抽样

SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));

num ROWS 表示抽样num条数据
num PERCENT 表示抽样num百分百比例的数据
num(K|M|G) 表示抽取num大小的数据，单位可以是K、M、G表示KB、MB、GB

 注意：

使用这种语法抽样，条件不变的话，每一次抽样的结果都一致
即无法做到随机，只是按照数据顺序从前向后取。

Virtual Columns虚拟列

虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。

Hive目前可用3个虚拟列：

• INPUT__FILE__NAME，显示数据行所在的具体文件
• BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
• ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量

  • 此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

 示例：

SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM itheima.course;

虚拟列的作用

使用虚拟列，可以让我们更精准的查看到具体每一条数据在存储上的详细参数细节

虚拟列不仅仅可以用于SELECT，在WHERE、GROUP BY等均可使用

如：

SELECT *, BLOCK__OFFSET__INSIDE__FILE FROM course WHERE BLOCK__OFFSET__INSIDE__FILE > 50;
SELECT INPUT__FILE__NAME, COUNT(*) FROM itheima.orders_bucket GROUP BY INPUT__FILE__NAME;

 如上SQL，统计分桶表每个桶的数据行数

四、函数

分类标准

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）

查看函数列表 

Hive内建了不少函数

使用show functions查看当下可用的所有函数；
通过describe function extended funcname来查看函数的使用方式。

Mathematical Functions 数学函数 - 部分

----Mathematical Functions 数学函数-------------
--取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);
--求数字的绝对值
select abs(-3);
--得到pi值（小数点后15位精度）
select pi();

 Collection Functions集合函数 - 全部

 Type Conversion Functions类型转换函数 - 全部

Date Functions日期函数 - 部分

 Conditional Functions条件函数 - 全部

 String Functions字符串函数 - 部分

 Data Masking Functions数据脱敏函数 - 部分

 Misc. Functions其它函数 - 部分