Hive表操作

1.表操作语法和数据类型

1.创建数据库表语法

--库创建查看命令
show databases;
--表创建查看命令
show tables;
--查看表的基本结构
desc table_name;
--查看表的详细信息
desc formatted table_name;


create [external] table [if not exists] db_name 
[(col_name data_type [comment col_comment], ...)]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment], ...)]
[clustered by (col_name, col_name, ...)
[sorted by (col_name [asc | desc], ...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[location hdfs_path]

• external , 创建外部表
• partitioned by，分区表
• clustered by , 分桶表
• stored as , 存储格式
• location ， 存储位置

2.数据类型

分类	类型	描述	字面量示例
原始类型	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2字节的有符号整数 -32768~32767	1S
	INT	4字节的有符号整数	1
	BIGINT	8字节的有符号整数	1L
	BOOLEAN	true / false	TRUE
	FLOAT	4字节单精度浮点数	1.0
	DOUBLE	8字节双精度浮点数	1.0
	DECIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,“b”
	CHAR	固定长度字符串	“a”,“b”
	VARCHAR	变长字符串	“a”,“b”
	DATE	日期	2024-01-03
	TIMESTAMP	时间戳。毫秒值精度
	BINARY	字节数组
复杂类型	array	有序的同类型集合	array(1,2)
	map	key-value, key原始类型，value任意类型	map(‘a’,1,‘b’,2)
	struct	字段集合，类型可以不同	struct(‘1’,1,1.0),named_stract(‘col_1’,‘1’,''col_2,‘1’,‘col_3’,‘1.0’)
	union	在有限取值范围内的一个值	creat_union(1,‘a’,63)

2.表分类

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

	创建	存储位置	删除数据	理念
内部表	create table …	/user/hive/warehouse	删除元数据(表信息)，删除数据	Hive管理表持久使用
外部表	create external table …	随意，location关键字指定	仅删除元数据(表信息)	临时链接外部数据用

1.内部表操作

数据分隔符

自行指定

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

2.外部表操作

数据和表相互独立 ，只是链接关系，删除表表不存在，但数据保留。

• 先创建表，在上传数据到目标目录
• 先创上传数据到目标目录，再建表

3.内外部表转换

• ‘EXTERNAL’=‘TRUE’ 或 ‘EXTERNAL’=‘FALSE’ 区分大小写

内转外

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

外转内

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

3.数据加载和导出

1.数据加载

1.语法

1.load语法

-- a. 加载数据  
-- b. 数据是否在本地；使用local数据不在hdfs，需使用file://协议指定路径;
--                 不使用local，数据在hdfs，可以使用hdfs://协议指定路径;
-- c. 数据路径
-- d. 覆盖已存在数据；overwrite覆盖
-- e. 被加载的内部表
--  a        b                c          d                     e
load data [local] inpath 'filepath' [overwrite] into table tablename;

2. insert select 语法

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

insert [overwrite | into] table table_name1 [partition (partcol1=val1, partcol2=val2 ...) [if not exists]] select_statement1 from from_ statement;

2.数据加载

举例

-- 建表
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';

-- 数据
00:00:01	1001	JAVA1	http://www.itheima.cn
01:30:01	1002	JAVA2	http://www.itheima.cn
02:20:01	1003	JAVA3	http://www.itheima.cn
03:30:01	1004	JAVA4	http://www.itheima.cn
04:40:01	1005	JAVA5	http://www.itheima.cn
05:50:01	1006	JAVA6	http://www.itheima.cn
06:40:01	1007	JAVA7	http://www.itheima.cn

1.本地上传

-- 将linux本地 '/opt/module/hive3.1.3/search_log.txt' 加载到 hdfs myhive.test_load
load data local inpath '/opt/module/hive3.1.3/search_log.txt' into table myhive.test_load;

2.hdfs

-- 将linux本地文件上传到hdfs
-- hdfs dfs -put search_log.txt /tmp/
-- 注意：写入后源文件会消失
load data inpath '/tmp/search_log.txt' into table myhive.test_load;

3.数据加载

insert into table tbl1 select * from tbl2;
insert overwrite tabel tbl1 select * from tbl2;

2.hive表数据导出

1.insert overwrite 方式

-- 将hive表中的数据导出到其它任意目录，例如Linux本地磁盘，例如hdfs、mysql等等
-- 语法

insert overwrite [local] directory 'path' select_statement1 from from_statement;

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

insert overwrite local directory '/opt/module/export1' select * from test_load;

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

insert overwrite local directory '/opt/module/export1' 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;

2.hive shell方式

-- 语法：(hive -e/-f 执行语句/脚本 > file)

/opt/module/hive3.1.3/bin/hive -e "select * from myhive.test_load;" > /opt/module/hive3.1.3/export2/export_mess1.txt;

/opt/module/hive3.1.3/bin/hive -f export.sql > /opt/module/hive3.1.3/export2/export_mess2.txt;

4.分区表

将大文件切割分成多个小文件，方便操作。

• 可以选择字段作为表分区
• 分区其实就是 HDFS 上的不同文件夹
• 分区表可以极大的提高特定场景下 Hive 的操作性能

1.语法

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

举例

-- 创建一个多分区表，分区 按年月日 分区
create table myhive.score2(id string, cid string, score int) partitioned by (year string, month string, day string) row format delimited fields terminated by '\t';

-- load data 加载数据到分区表中
load data local inpath '/opt/module/hive3.1.3/temp1/score.txt' into table myhive.score2  partition (year='2024', month='01', day='31');

-- 添加数据到分区表中 与 创建分区表保持一致

5.分桶表

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

不同的是，分区是将表拆分到不同的子文件夹中存储，分桶是将表拆分到固定数量的不同问家中存储。

• 可以选择字段作为分桶字段
• 本质上是数据分开在不同的文件中
• 分区和分桶可以同时使用

1.语法

-- 开启分桶表自动优化(自动匹配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 fileds terminated by '\t';

2.数据加载

1.insert select

只能通过 insert select

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

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

-- 创建临时中转表
create table myhive.course_temp (c_id string, c_name string, t_id string) row format delimited fields terminated by '\t';

-- 中转表中加载数据
load data local inpath '/opt/module/hive3.1.3/temp1/course.txt' into table myhive.course_temp;

-- 中转表通过insert overwrite 给桶表加载数据
insert overwrite table myhive.course select * from myhive.course_temp cluster by (c_id);

2.Hash取模

Hash算法是一种数据加密算法，特征：

同样的值被Hash加密后的结果是一致的（如：“abc” 被hash后是 “123456”），那么计算多少次“abc”都是“123456”。分 3 个桶时，无论什么数据取模结果均是：0、1、2。所以，必须用 insert select 语法，因为会触发MapReduce，进行hash取模计算。

3.分桶表性能提升

分区表性能提升：在指定分区列的前提下，减少被操作的数据量，从而提升性能。

分桶表性能提升：基于分桶列的特定操作，如：过滤、join、分组，均可带来性能提升。

6.修改表

1.表重命名

alter table old_table_name rename to new_table_name;

-- 示例
alter table score1 rename to rename2;

2.修改表属性值

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

3.添加分区

alter table table_name add partition(year="2023");
-- 新分区无数据，需要添加或上传数据文件

4.修改分区

alter table table_name partition(year="2023") rename to partition(year="2024");
-- 修改元数据记录，hdfs实体文件夹不会改名,相当于24年的数据存到了23年的文件夹，数据分区分到了24年的分区，物理存在23年
-- 不建议修改操作

5.删除分区

alter table table_name drop partition(year="2023");

6.添加列

alter table table_name add columns (val1 string, val2 int);

7.修改列名

alter table test_change change val1 val1New int;

8.删除表

drop table table_name;

9.清空表

-- 只能清空内部表
truncate table table_name;

7.复杂类型操作（Array）

Hive支持的数据类型很多，除了基本的：int、string、varchar、timestamp等，还有一些复杂的数据类型

1.array 数组类型.

create table myhive.test_array(name string, work_locations array<string>) row format delimited fields terminated by '\t' collection items terminated by ',';
-- 列分隔符“\t”,array分隔符是“,”

查询 数组名[数字序号]，可以去除指定元素。

size(数组名)，统计数据元素个数。

array_contains(数组名，数据)，可以查看指定数据是否在数组中存在。

2.map 映射类型

map类型其实就是简单的指代：key-value型数据格式。

-- 其中members字段是key-value型数据
-- 字段与字段分隔符  ","
-- map字段之间的分隔符  "#"
-- map内部k-v分隔符  ":"
id,name,members,age
1,zhangsan,father:zhangfei#mother:xiaohuang#brother:zhangsi,28
2,lisi,father:lisan#mother:huahua#brother:lisisi,22
3,wangwu,father:wanggang#mother:liuhua#brother:wangwuwu,29
4,mayun,father:muyiyi#mother:tuhua#brother:mawuyun,29

-- 建表语句
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 ':';

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

类型	array	map	struct
定义	array<类型>	map<key 类型, value 类型>	struct<子列名 类型, 子列名 类型…>
示例	如定义为array数据为：1，2，3，4，5	如定义为：map<string, int>数据为：{‘a’:1,‘b’:2,‘c’:3}	如定义为：struct<c1 string, c2 int, c3 date> 数据为：‘a’,1,‘2024-01-06’
内含元素类型	单值，类型取决于定义	键值对，k-v，k和v类型取决于定义	单值，类型取决于定义
元素个数	动态，不限制	动态，不限制	固定，取决于定义的子列数量
取元素	array[数字序号] 序号从0开始	map[key]取出对应key的value	struct.子列名通过子列名去除子列值
可用函数	size统计元素个数 array_contains判断是否包含指定数据	size统计元素个数 array_contains判断是否包含指定数据 map_keys取出全部key，返回array map_values取出全部values，返回array	暂无

8.数据基本查询

1.基本语法

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差不多，部分有区别，如：clusterby、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 '/opt/module/hive3.1.3/temp1/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 '/opt/module/hive3.1.3/temp1/itheima_users.txt' into table itheima.users;

2.rlike正则匹配

select * from itheima.orders where useraddress rlike '.*广东.*';

select * from itheima.orders where useraddress rlike '..省 ..市 ..区';

select * from itheima.orders where username rlike '[赵钱孙李]\\S+';

select * from itheima.orders where userphone rlike '188\\S{4}0\\S{3}';

字符	匹配	示例
.	任意单个字符	jav.匹配java
[]	[]中任意一个字符	java匹配j[abc]va
-	[]内表示字符范围	java匹配[a-z]av[a-v]
^	在[]内的开头，匹配除[]内的字符之外的任意一个字符	java匹配j[ ^b-f ]va
|	或	x|y匹配x或y
\	将下一字符标记为特殊字符、文本、反向引用或八进制转义符	\( 匹配 （
$	匹配输入字符串结尾的位置，如果设置了regexp对象的multiline属性，$还会与 ‘\n’ 或 '\t’之前的位置匹配	;$匹配位于一行及外围的;号
*	零次或多次匹配前面的字符	zo*匹配zoo或z
+	一次或多次匹配前面的字符	zo+匹配zo或zoo
?	零次或一次匹配前面的字符	zo?匹配z或zo
p{n}	n是非负整数，正好匹配n次	o(2)匹配food中的两个o
p{n,}	n是非负整数，正好匹配n次	o(2)匹配food中的所有o
p{n,m}	m和n是非负整数，其中n<=m，匹配至少n次，至多m次	o{1,3}匹配foood中的三个o
\p{P}	一个标点字符!*#$%&'()"+,-:;<=>?@[\]^_`{|}~	J\p{P}a匹配J?a
\b	匹配一个字边界	va\b匹配java中的va，但不匹配javar中的va
\B	非字边界匹配	va\B匹配javar中的va，但不匹配java中的va
\d	数字字符匹配	1[\d]匹配13
\D	非数字字符匹配	[\D]java匹配Jjava
\w	单词字符	java匹配[\W]ava
\W	非单词字符	$java匹配[\W]
\s	空白字符	Java 2匹配Java\s2
\S	非空白字符	java匹配j[\S]va
\f	匹配换页符	等效于\x0a和\cL
\n	匹配换行符	等效于\x0a和\cJ

3.union联合

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

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

-- 默认去重 （无需去重union all）
select ...
	union [all]
select ...

4.Sampling采样

tablesample函数

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

select ... from tbl tablesample(bucket x out of y on (colname() | rand()))
x: 表示从y里面随机抽取x份数据作为取样
y: 表示将表数据随即划分成y份（y个桶）
colname: 表示随机的依据基于某个列的值
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大小的数据，单位可以是KB、MB、GB

注意：

使用这种语法抽样，条件不变的话，每一次抽样的结果都一致

即无法做到随机，只是按照数据顺序从前向后取

5.虚拟列

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

Hive目前可用3个虚拟列：

• input_file_name：显示数据行所在的具体文件
• block_offset_inside_file：显示数据行所在文件的偏移量
• row_offset_inside_block：显示数据所在hdfs块的偏移量（设置set hive.exec.rowoffset=true才可使用）

虚拟列作用：

• 查看行级别数据详细参数
• 可以用于where、group by 等各类统计计算中
• 可以协助进行错误排查工作

6.函数

分类标准：内置函数、用户定义函数UDF

-- 查看所有可用函数
show functions;

-- 查看函数使用方式
describe function extended 函数名;

1.内建函数

1.数值函数（Mathematical Functions）

-- 取整函数：round 返回double类型的整数值部分（四舍五入）
select round(3.1415926);

-- 指定精度取整函数：round(double a, int b) 返回指定精度b的double类型
select round(3.1415926, 3);

-- 取随机函数：rand每次执行都不一样 返回一个0到1范围内的随机数
select rand();

-- 指定种子取随机数函数：rand(int seed)得到一个稳定的随机数序列
select rand(3);

-- 求数字的绝对值
select abs(-5);

-- 得到pi值
select pi();

2.集合函数（Collection Functions）

-- 返回map类型的元素个数
select size(Map<K,V>) from table_map;

-- 返回array类型的元素个数
select size(Array<T>) from table_array;

-- 返回map内的全部key(得到的是array)
select map_key(Map<K,V>) from table_map;

-- 返回map内的全部value(得到的是array)
select map_values(Map<K, v>) from table_map;

-- 如果array包含指定value，返回true
select * from table_array where array_contains(Array<T>,value);

-- 根据数组元素的自然顺序按升序对输入数组进行排序并返回它
select *, sort_array(Array<T>) from table_name;

3.类型转换函数（Type Conversion Functions）

-- 将给定字符串转换为二进制
select binary("hadoop");

-- 将表达式expr的结果转换为给定类型
select cast('1' as bigint);

4.日期函数（Date Functions）

-- 返回当前时间戳
select current_timstamp()

-- 返回当前日期
select current_date

-- 时间戳转日期
select to_date(string timestamp)

-- 得到给定时间的：年
select year(string date)

-- 得到给定时间的：季度
select quarter(date/timestamp/string)

-- 得到给定时间的：月
select month(string date)

-- 得到给定时间的：日
select day(string date)

-- 得到给定时间的：月份的第几天
select dayofmonth(date)

-- 得到给定时间的：小时
select hour(string date)

-- 得到给定时间的：分钟
select minute(string date)

-- 得到给定时间的：秒
select second(string date)

-- 得到给定时间的：本年第几周
select weekofyear(string date)

-- 返回enddate到startdate之间的天数
select datediff(string enddate, string startdate)

-- 日期相加：
select date_add(date/timestamp/string startdate, tinyint/smallint/int days)

-- 日期相减：
select date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

5.条件函数（Conditional Functions）

-- testCondition为true，返回valueTrue，否则返回valueFalseOrNull
if(boolean testCondition, T valueTrue, T valueFalseOrNull)

-- 如果a为null，则返回true，否则返回false
isnull(a)

-- 如果a不为null，则返回true，否则返回false
isnotnull(a)

-- 如果value为null，则返回default_value，否则value
nvl(T value, default_value)

-- 返回第一个不是null的v，如果所有v都是null，则返回null
coalesce(T v1, T v2, ...)

-- 当a=b时，返回c；[当a=d时，返回e]*；否则返回f
case a when b then c[when d then e]* [else f] end

-- 当a=ture时，返回b；当c=true时，返回d；否则返回e。a可以是表达式，如1+1
case when a then b [when c then d]* [else e] end

-- 如果a=b，则返回null；否则返回a。
nullif(a,b)6

-- 如果boolean——condition结果不为true，则引发异常报错。
assert_true(boolean condition)

6.字符串函数（String Functions）

-- 连接字符串
concat(string|binary A, string|B ...)

-- 同concat，但是可以自己定义字符串之间的分隔符
concat_ws(string SEP, string A, string B ...)

-- 字符串长度
length(string A)

-- 全部转小写 全部转大写
lower(string A)
upper(string a)

-- 返回从A的两端裁剪空格得到的字符串
trim(string A)

-- 按照pat分割字符串str，pat是正则表达式
split(string str, string pat)

7.数据脱敏函数（Data Masking Functions）

-- hash加密（结果是16进制字符串）,非字符串会得到null
mask_hash(string|char|varchar str)

8.其它函数（Misc. Functions）

2.用户自定义函数

1.UDF(User-Defined Functions) - 用户自定义功能函数

2.UDAF(User-Defined Aggregate Functions) - 用户定义聚合函数

3.UDTF(User-Defined Table-Generating) - 用户定义表生成函数