HIVE 内部表与外部表的区别
# HIVE储存数据位置
[hadoop@test1 hive]$ hadoop fs -ls /user/hive/warehouse/testhive.db/info/
Found 1 items
-rw-r--r-- 3 hadoop supergroup ... /user/hive/warehouse/testhive.db/info/000000_0
# 内部表-创建: create table ......
# 外部表-创建: create external table ......
# 内部表-存储位置: Hive管理，默认/user/hive/warehouse
# 外部表-存储位置: 随意，location关键字指定
# 内部表-删除数据: 删除元数据（表信息）, 删除数据
# 外部表-存储位置: 删除元数据（表信息），保留数据
# 内部表-理念: Hive管理表持久使用，删除时会全部一起删除
# 外部表-理念: 临时链接外部数据用，删除时会删除元数据与表结构，但是不会删除实际数据
# 总的来说，内部表适合在 Hive 中进行数据管理和查询操作，而外部表适合在 Hive 中进行数据分析和集成外部数据。外部表通常用于引入和处理外部数据源，而内部表通常用于存储经过处理的数据。
HIVE 内/外表的命令区别
# 内部表命令
create database testHive;
use testHive;
create table testHive.info(id int,name string);
insert into testHive.info values (1,'周杰伦'),(2,'林俊杰');
select * from testHive.info;
# 外部表命令
# 先创建外部表，然后移动数据到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'; # 创建表
select * from test_ext1; # 空结果，无数据
hadoop fs -put test_external.txt /tmp/test_ext1/ # 上传数据：
select * from test_ext1 # 即可看到数据结果
# 演示先存在数据，后创建外部表
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;
HIVE 数据导入
# 使用local，数据不在HDFS，需使用file://协议指定路径。 不使用local，数据在HDFS，可以使用HDFS://协议指定路径
# 使用OVERWRITE进行覆盖数据 不使用OVERWRITE则不覆盖数据
load data local inpath '本地文件路径' overwrite into table 表名
# <表名>是要导入数据的目标表名。如果使用OVERWRITE参数，将会覆盖目标表中的数据。
# 示例命令：
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;
# 将查出的书 追加INTO 或者覆盖OVERWRITE 到指定的表里面
insert into table tbl1 select * from tbl2;
insert overwrite table tbl1 select * from tbl2;
数据在本地:
推荐 load data local加载
数据在HDFS:
如果不保留原始文件:推荐使用LOAD方式直接加载
如果保留原始文件:推荐使用外部表先关联数据，然后通过INSERT SELECT 外部表的形式加载数据
数据已经在表中:
只可以insert select
# 推荐选择加载数据到内部表内
HIVE 数据导出
# 保存
# 将查询的结果导出到本地 - 使用默认列分隔符
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 分区表
-- 创建分区 多分区的时候使用逗号隔开partitioned by (year string, month string)
create table testhive.score(id string, cid string, score int) partitioned by (month string)
row format delimited fields terminated by '\t';
-- 加载数据 partition(month='202005')：将这段加载的数据全部标志成month='202005'
-- 多分区的时候使用逗号隔开 partition(year='2023',month='202005');
load data local inpath '/home/hadoop/score.txt' overwrite into table testHive.score partition(month='202005');
周杰轮,语文,99,202005
周杰轮,数学,66,202005
周杰轮,英语,88,202005
林均街,语文,66,202005
林均街,数学,77,202005
...
HIVE 分桶表
# 开启分桶自动优化 自动匹配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';
-- 加载数据 只能走insert select 需要创建临时表导入数据后 查询数据后导入
-- 1：创建临时表
create table testhive.lst(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
-- 2: 加载数据到临时表
load data local inpath '/home/hadoop/course.txt' into table testhive.lst;
-- 3: insert select 向分桶表里面加载数据 cluster不需要带ed
insert overwrite table testHive.course select * from testHive.lst cluster by (c_id);
# 查看分桶数量
[hadoop@test1 ~]$ hadoop fs -ls /user/hive/warehouse/testhive.db/course
Found 3 items
-rw-r--r-- 3 hadoop supergroup /user/hive/warehouse/testhive.db/course/000000_0
-rw-r--r-- 3 hadoop supergroup /user/hive/warehouse/testhive.db/course/000001_0
-rw-r--r-- 3 hadoop supergroup /user/hive/warehouse/testhive.db/course/000002_0
# 分桶时为什么不能直接使用load数据加载
需要insert select触发MapReduce进行hash取模计算，来基于分桶列的值，确定哪一条数据进入到哪一个桶文件中
（哈希加密后除分桶数量取余分配），同样key (分桶列的值)的数据，会在同一个桶中。
# 好处：
单值过滤
join
group by
HIVE 基本命令
# T为自定义数据表
# 表重命名
alter table T1 rename to T2;
# 修改表属性值
alter table T set tblproperties table_properties;
# 查看表类型：
desc formatted T;
# 内部表转外部表
alter table T set tblproperties('EXTERNAL'='TRUE');
# 外部表转内部表
alter table T set tblproperties('EXTERNAL'='FALSE');
# 添加分区 新分区是空的没数据，需要手动添加或上传数据文件
alter table T add partition(month='201101');
# 修改分区值
alter table T partition(month='202005') rename to partition (month='201105');
# 删除分区
alter table T drop partition(month='201105');
# 添加列
alter table T add columns (v1 int, v2 string);
# 修改列名 当前名称 新列名 数据类型
alter table T change column_name new_column_name column_type;
alter table T change v1 v1New int;
# 删除表
drop table T;
# 清空表 只可以清空内部表
truncate table T;
HIVE Array类型
# 建表语句
create table testhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ','; # 数组以','相隔开
-- 导入本地数据
load data local inpath '/home/hadoop/data_for_array_type.txt' into table testHive.test_array;
-- 查询所有数据
select * from testhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from testhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from testhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from testhive.test_array where array_contains(work_locations,'tianjin');
HIVE Map类型
# 建表语句 k-v分隔符 ':' 字段分隔符 '#'
create table testhive.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' into table testhive.test_map;
# 查询全部
select * from testhive.test_map;
# 查询所有列 表 ['key 名'] 自定义列名
select id, name, members['father'] father, members['mother'] mother, age from testhive.test_map;
id,name,father,mother,age
1,林杰均,林大明,小甜甜,28
2,周杰伦,马小云,黄大奕,22
# 查询全部map的key，使用map_keys函数，结果是array类型
select id, name, map_keys(members) as relation from testhive.test_map;
1,林杰均,"[""father"",""mother"",""brother""]"
2,周杰伦,"[""father"",""mother"",""brother""]"
# 查询全部map的value，使用map_values函数，结果是array类型
select id, name, map_values(members) as relation from testhive.test_map;
1,林杰均,"[""林大明"",""小甜甜"",""小甜""]"
2,周杰伦,"[""马小云"",""黄大奕"",""小天""]"
# 查询map类型的KV对数量
select id,name,size(members) num from testhive.test_map;
# 查询map的key中有brother的数据 精确匹配
select * from testhive.test_map where array_contains(map_keys(members), 'brother');
select * from testhive.test_map where array_contains(map_values(members), '马小云');
# 模糊查询
select * from testhive.test_map where map_values(members) like '%马%';
HIVE Struct类型
# 建表语句 字段分隔符'#'
create table testhive.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 testHive.test_struct;
select * from test_struct;
# 直接使用列名.字段名 即可从struct中取出子列查询
select id,info.name from test_struct;
HIVE Sampling采样（数据抽样）
# 随机分桶抽样
select ... from tbl tablesample(bucket x out of y on(colname | rand()))
y表示将表数据随机划分成y份（y个桶）
x表示从y里面随机抽取x份数据作为取样
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行
# 示例：
# 按username使用哈希分成10个桶，每个桶抽取1个拼接到一起
SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);
# 按照随机字段进行分分成10个桶，每个桶抽取1个拼接到一起
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());
# 数据块抽样
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
# 示例
select * from orders tablesample ( 100 rows )
HIVE 虚拟列
# 虚拟列是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 testhive.course;
HIVE 常用内置函数
# 使用 show functions 查看当下可用的所有函数；
# 通过 describe function extended funcname 来查看函数的使用方式。
--取整函数: 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();
-- 集合函数：
size(array/map)：返回数组或map的元素个数。
array(value1, value2, ...)：创建一个数组。
map(key1, value1, key2, value2, ...)：创建一个map。
-- 数组函数：
array_contains(array, value)：检查数组中是否包含指定的元素。
array_length(array)：返回数组的长度。
array_sort(array)：对数组进行排序。
array_max(array)：返回数组中的最大值。
array_min(array)：返回数组中的最小值。
array_distinct(array)：返回数组中的唯一值。
array_join(array, delimiter)：将数组元素连接为一个字符串。
-- map函数：
map_keys(map)：返回map中的所有键。
map_values(map)：返回map中的所有值。
map_keys_sorted(map)：返回按键排序的map。
map_values_sorted(map)：返回按值排序的map。
map_contains_key(map, key)：检查map中是否包含指定的键。
-- 集合操作函数：
union(array1, array2)：返回两个数组的并集。
intersect(array1, array2)：返回两个数组的交集。
difference(array1, array2)：返回两个数组的差集。
-------- 类型转换函数 -------
to string:
cast(value as string)：将值转换为字符串类型。
string(value)：将值转换为字符串类型。
concat_ws(separator, value1, value2, ...)：将多个值按指定分隔符连接为一个字符串。
to numeric:
cast(value as int)：将值转换为整数类型。
cast(value as bigint)：将值转换为长整数类型。
cast(value as float)：将值转换为浮点数类型。
cast(value as double)：将值转换为双精度浮点数类型。
to boolean:
cast(value as boolean)：将值转换为布尔类型。
to date/time:
cast(value as date)：将值转换为日期类型。
cast(value as timestamp)：将值转换为时间戳类型。
to collection:
array(value1, value2, ...)：将多个值转换为数组类型。
map(key1, value1, key2, value2, ...)：将多个键值对转换为map类型。
to complex types:
struct(value1, value2, ...)：将多个值转换为结构类型。
-------- 日期函数 -------
current_date(): 返回当前日期，格式为yyyy-MM-dd。
current_timestamp(): 返回当前时间戳，格式为yyyy-MM-dd HH:mm:ss.SSS。
year(date): 返回给定日期的年份。
month(date): 返回给定日期的月份（1-12）。
day(date): 返回给定日期的天数（1-31）。
hour(timestamp): 返回给定时间戳的小时数（0-23）。
minute(timestamp): 返回给定时间戳的分钟数（0-59）。
second(timestamp): 返回给定时间戳的秒数（0-59）。
date_format(date, pattern): 使用指定的模式格式化日期。常见的模式包括"yyyy-MM-dd"（年-月-日），"MM/dd/yyyy"（月/日/年）等。
from_unixtime(unixtime, pattern): 将Unix时间戳转换为指定格式的日期。
unix_timestamp(): 返回当前时间的Unix时间戳。
datediff(end_date, start_date): 返回两个日期之间的天数差。
add_months(start_date, num_months): 返回在给定日期上增加指定月数后的日期。
trunc(date, format): 根据指定的格式截断日期。常见的格式包括"YYYY"（年），"MM"（月），"DD"（日）等。
date_add(start_date, num_days): 返回在给定日期上增加指定天数后的日期。
date_sub(start_date, num_days): 返回在给定日期上减去指定天数后的日期。
------ 条件函数 -------
if(condition, value_if_true, value_if_false): 如果条件为真，则返回value_if_true；否则返回value_if_false。
case expr when value1 then result1 when value2 then result2 ... else default_result end: 根据表达式的值匹配多个可能的结果，并返回匹配的结果。如果没有匹配项，则返回默认结果。
coalesce(value1, value2, ...): 返回第一个非空值。如果所有值都为空，则返回NULL。
isnull(value): 如果值为空，则返回true；否则返回false。
isnotnull(value): 如果值不为空，则返回true；否则返回false。
nullif(expr1, expr2): 如果expr1等于expr2，则返回NULL；否则返回expr1。
------ 字符串函数 -------
length(string): 返回字符串的长度。
lower(string)/upper(string): 将字符串转换为小写/大写。
trim(string): 去除字符串两端的空格。
substring(string, start, length)/substr(string, start, length): 返回从指定位置开始的子字符串。
concat(string1, string2, ...): 连接多个字符串。
replace(string, search, replace): 将字符串中的指定子字符串替换为新字符串。
split(string, delimiter): 将字符串按指定分隔符拆分为数组。
regexp_extract(string, pattern, index): 从字符串中提取符合正则表达式的指定位置的子字符串。
locate(substr, string)/instr(string, substr): 返回子字符串在字符串中第一次出现的位置。
lpad/rpad(string, length, pad): 在字符串的左侧/右侧填充指定字符，使其达到指定长度。
------ 脱敏函数 --------
mask(string, n): 将字符串的前n个字符替换为*号。
hash(string): 对字符串进行哈希处理，生成不可逆的摘要。
sha1(string)/md5(string): 对字符串进行SHA1/MD5散列处理，生成不可逆的摘要。
encrypt(string, key): 使用指定密钥对字符串进行加密。
decrypt(string, key): 使用指定密钥对字符串进行解密。
scramble(string): 对字符串进行乱序处理。
shuffle(string): 对字符串进行随机打乱处理。
redact(string, pattern): 将字符串中符合指定正则表达式的部分替换为指定字符。
--------- 其他函数 ---------
int hash(a1[, a2...]): 返回参数的hash数字。这个函数可以用于快速生成输入参数的哈希值，用于数据的散列分布和分区操作。
string current_user(): 返回当前登录用户的用户名。这个函数可以用于获取当前会话中正在操作的用户，用于权限控制和审计。
string current_database(): 返回当前选择的数据库的名称。这个函数可以用于获取当前会话中正在使用的数据库，用于操作数据库对象。
string version(): 返回当前 Hive 的版本号。这个函数可以用于获取 Hive 的版本信息，用于判断功能支持和升级决策。
string md5(string/binary): 返回给定参数的 MD5 值。这个函数可以用于对字符串或二进制数据进行 MD5 哈希计算，用于数据完整性校验或加密存储。