hive字段包括字符 sql hive的字段类型

Hive内外表操作

建表语法

create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , … )
[partitioned by (分区字段名 分区字段类型)] # 分区表固定格式
[clustered by (分桶字段名) into 桶个数 buckets] # 分桶表固定格式 注意: 可以排序[sorted by (排序字段名 asc|desc)]
[row format delimited fields terminated by ‘字段分隔符’] # 自定义字段分隔符固定格式
[stored as textfile] # 默认即可
[location ‘hdfs://域名:8020/user/hive/warehouse/库名.db/表名’] # 默认即可
; # 注意: 最后一定加分号结尾

注意: 关键字顺序是从上到下从左到右,否则报错

default默认库存储路径: /user/hive/warehouse
自定义库在HDFS的默认存储路径: /user/hive/warehouse/库名.db
自定义表在HDFS的默认存储路径: /user/hive/warehouse/库名.db/表名
业务数据文件在HDFS的默认存储路径: /user/hive/warehouse/库名.db/表名/数据文件

数据类型

基本数据类型: 整数: int 小数: float double 字符串: string varchar(长度) 日期: date timestamp

表分类

Hive中可以创建的表有好几种类型， 分别是：
内部表:又叫管理表或者托管表
分区表(分区分桶表)
分桶表
外部表:又叫非管理表或者非托管表
分区表(分区分桶表)
分桶表

内部表和外部表区别?
内部表: 未被external关键字修饰的即是内部表， 即普通表。 内部表又称管理表,还可以叫托管表
删除内部表:直接删除元数据（metadata）和存储数据本身
内部表可以用as select 语句复制表
内部表可以通过truncate删除表中数据
外部表: 被external关键字修饰的即是外部表， 即关联表。 还可以叫非管理表或非托管表
删除外部表:仅仅是删除元数据（metadata），不会删除存储数据本身
外部表不可以使用as select 语句复制表
外部表不可以通过truncate删除表中数据

查看表格式化信息: desc formatted 表名;
– 内部表类型: MANAGED_TABLE
– 外部表类型: EXTERNAL_TABLE

内部表

创建内部表: create table [if not exists] 内部表名(字段名 字段类型 , 字段名 字段类型 , … )[row format delimited fields terminated by ‘字段分隔符’] ;

复制内部表: 方式1: like方式复制表结构 方式2: as 方式复制表结构和数据

删除内部表: drop table 内部表名;
注意: 删除内部表效果是mysql中表相关元数据被删除,同时存储在hdfs中的业务数据本身也被删除

查看表格式化信息: desc formatted 表名; – 内部表类型: MANAGED_TABLE

注意: 还可以使用truncate清空内部表数据 格式: truncate table 内部表名;

外部表

创建外部表: create external table [if not exists] 外部表名(字段名 字段类型 , 字段名 字段类型 , … )[row format delimited fields terminated by ‘字段分隔符’] ;

复制表: 方式1: like方式复制表结构 注意: as方式不可以使用

删除外部表: drop table 外部表名;
注意: 删除外部表效果是mysql中元数据被删除,但是存储在hdfs中的业务数据本身被保留

查看表格式化信息: desc formatted 表名; – 外部表类型: EXTERNAL_TABLE

注意: 外部表不能使用truncate清空数据本身

查看/修改表

查看所有表: show tables;
查看建表语句: show create table 表名;
查看表信息: desc 表名;
查看表结构信息: desc 表名;
查看表格式化信息: desc formatted 表名; 注意: formatted能够展示详细信息

修改表名: alter table 旧表名 rename to 新表名
字段的添加: alter table 表名 add columns (字段名 字段类型);
字段的替换: alter table 表名 replace columns (字段名 字段类型 , …);
字段名和字段类型同时修改: alter table 表名 change 旧字段名 新字段名 新字段类型;
注意: 字符串类型不能直接改数值类型

修改表路径: alter table 表名 set location ‘hdfs中存储路径’; 注意: 建议使用默认路径
location: 建表的时候不写有默认路径/user/hive/warehouse/库名.db/表名,当然建表的时候也可以直接指定路径

修改表属性: alter table 表名 set tblproperties (‘属性名’=‘属性值’); 注意: 经常用于内外部表切换
内外部表类型切换: 外部表属性: ‘EXTERNAL’=‘TRUE’ 内部表属性: ‘EXTERNAL’=‘FALSE’

默认分隔符

创建表的时候,如果不指定分隔符,以后表只能识别默认的分隔符,键盘不好打印,展示形式一般为:\0001,SOH,^A,□

快速映射表

创建表的时候指定分隔符: create [external] table 表名(字段名 字段类型)row format delimited fields terminated by 符号;

加载数据: load data [local] inpath ‘结构化数据文件’ [overwrite] into table 表名;
注意:local代表本地,省略代表从hdfs上加载数据,不省略代表从本地(Linux)中加载数据
overwrite代表覆盖,不省略overwrite代表在原表的基础上追加数据

数据导入和导出

文件数据加载导入

直接上传文件

1.hdfs中window界面上传,前提是服务必须打开
2.Linux中通过put方式,上传Linux中的文件
– linux使用hdfs命令上传文件
– [root@node1 ~]# hdfs dfs -put emp2.txt /user/hive/warehouse/hive02.db/emp2

load加载文件

从hdfs路径把文件移动到表对应存储路径中:
load data inpath ‘HDFS文件路径’ [overwrite] into table 表名;

从Linux本地把文件上传到表对应存储路径中:
load data local inpath ‘Linux文件路径’ [overwrite] into table 表名;

insert插入数据

从其他表查询 ‘追加’ 插入到当前表中:insert into [table] 表名 select语句;
从其他表查询数据 ‘覆盖’ 插入到当前表中:insert overwrite table 表名 select语句;

文件数据导出

直接下载文件

1.hdfs 中window界面直接下载
2.Linux中通过get命令下载hdfs中的文件
[root@node1 binzi]# hdfs dfs -get /user/hive/warehouse/hive02.db/search_log/search_log.txt /binzi

insert导出数据

查询数据导出到hdfs其他路径: insert overwrite directory ‘hfds存储该数据路径’ [指定分隔符] select语句;

查询数据导出到linux本地中: insert overwrite local directory ‘linux存储该数据路径’ [指定分隔符] select语句;

注意: overwrite默认是覆盖重写,所以在指定存储该数据路径的时候尽量指定一个空的目录

注意: 导出数据的时候不指定分隔符采用默认分隔符SOH,0001,?..

导出数据指定分隔符添加: row format delimited fields terminated by ‘分隔符’

hive_shell命令

hive命令执行sql语句: hive -e “sql语句” > 存储该结果数据的文件路径

hive命令执行sql脚本: hive -f sql脚本文件 > 存储该结果数据的文件路径

# 以下命令都是在linux的shell命令行执行
# 3.1使用hive -e sql语句方式导出数据
[root@node1 ~]# hive -e 'select * from hive02.search_log;' > /home/hs1.txt
[root@node1 ~]# cat hs1.txt

# 3.2使用hive -f 脚本文件方式导出数据
[root@node1 ~]# echo 'select * from hive02.search_log;' > /home/export.sql
[root@node1 ~]# hive -f export.sql > /home/hs2.txt
[root@node1 ~]# cat hs2.txt

hive分区表与分桶表

分区表

分区表特点/好处: 需要产生分区目录, 查询的时候使用分区字段筛选数据,避免全表扫描从而提升查询效率

效率上注意: 如果分区表,在查询数据的时候没有使用分区字段去筛选数据,效率不变

分区字段名注意: 分区字段名不能和原有字段名重复,因为分区字段名要作为字段拼接到表后

一级分区

创建分区表: create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , … )partitioned by (分区字段名 分区字段类型)… ;

自动生成分区目录并插入数据: load data [local] inpath ‘文件路径’ into table 分区表名 partition (分区字段名=‘值’);

注意: 如果加local后面文件路径应该是linux本地路径,如果没有加那么就是hdfs文件路径

-- 创建库使用库
create database hive3;
use hive3;
-- 演示分区表
-- 1.一级分区表
-- 建表
create table one_part_order(
    oid string,
    name string,
    price double,
    num int
)partitioned by (year string)
    row format delimited
fields terminated by ' ';
-- 加载数据
-- 先在hdfs的source目录下准备好订单相关数据文件
-- 使用load加载数据到分区表中
load data inpath '/source/order202251.txt' into table one_part_order partition (year=2022);
load data inpath '/source/order2023415.txt' into table one_part_order partition (year='2023');
load data inpath '/source/order202351.txt' into table one_part_order partition (year='2023');
load data inpath '/source/order202352.txt' into table one_part_order partition (year='2023');
-- 验证数据
select * from one_part_order limit 20;

/*分区表特点
去hdfs验证分区表的本质就是分目录存储各个小文件
通过查询发现分区字段最终效果作为一个字段拼接到表最后
*/
-- 分区表的好处:避免全表扫描,提升查询效率
select * from one_part_order where year='2022';
-- 注意: 如果查询的时候条件不是分区字段,效率不会改变
select * from one_part_order where price=20;

多级分区

创建分区表: create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , … )partitioned by (一级分区字段名 分区字段类型, 二级分区字段名 分区字段类型 , …) ;

自动生成分区目录并插入数据: load data [local] inpath ‘文件路径’ into table 分区表名 partition (一级分区字段名=‘值’,二级分区字段名=‘值’ , …);

注意: 如果加local后面文件路径应该是linux本地路径,如果没有加那么就是hdfs文件路径

-- 2.多级分区表
-- 创建表
create table multi_part_order(
    oid string,
    name string,
    price float,
    num int
)partitioned by (year string,month string,day string)
    row format delimited
fields terminated by ' ';
-- 加载数据
-- 思考数据文件在哪里?如果想从hdfs加载,怎么操作?上传到hdfs指定位置
load data inpath '/source/order202251.txt' into table multi_part_order partition (year=2022,month=05,day=01);
load data inpath '/source/order202351.txt' into table multi_part_order partition (year=2023,month=05,day=01);
load data inpath '/source/order202352.txt' into table multi_part_order partition (year=2023,month=05,day=02);
load data inpath '/source/order2023415.txt' into table multi_part_order partition (year=2023,month=04,day=15);
-- 验证数据
select * from multi_part_order;

-- 分区表的好处:避免全表扫描,提升查询效率
-- 需求: 统计2023年商品总销售额
select sum(price*num) from multi_part_order where year='2023'; -- 提升效率
-- 需求: 统计2023年5月份商品总销售额
select sum(price*num) from multi_part_order where year='2023'and month='5'; -- 提升效率
-- 需求: 统计2023年5月1日的商品总销售额
select sum(price*num) from multi_part_order where year='2023'and month='5' and day='1'; -- 提升效率

分区操作

添加分区: alter table 分区表名 add partition (分区字段名=‘值’ , …);

删除分区: alter table 分区表名 drop partition (分区字段名=‘值’ , …);

修改分区名: alter table 分区表名 partition (分区字段名=‘旧值’ , …) rename to partition (分区字段名=‘新值’ , …);

查看所有分区: show partitons 分区表名;

同步/修复分区: msck repair table 分区表名;

-- 分区操作
-- 注意: 先确定有一级分区和多级分区表,如果没有先创建再做分区操作
select * from one_part_order limit 20;
select * from multi_part_order limit 20;

-- 添加分区(本质在hdfs上创建分区目录)
alter table one_part_order add partition (year=2024);
alter table multi_part_order add partition (year=2024,month=5,day=1);

-- 修改分区(本质在hdfs上修改分区目录名)
alter table one_part_order partition (year=2024) rename to partition (year=2030);
alter table multi_part_order  partition (year=2024,month=5,day=1) rename to partition (year=2030,month=6,day=10);

-- 查看所有分区
show partitions one_part_order;
show partitions multi_part_order;

-- 删除分区
alter table multi_part_order drop partition (year=2030,month=6,day=10);
alter table multi_part_order drop partition (year=2023,month=5,day=2);
alter table multi_part_order drop partition (year=2023,month=5);
alter table multi_part_order drop partition (year=2023,month=4);
alter table multi_part_order drop partition (year=2022);

-- 如果在hdfs上创建符合分区目录格式的文件夹,可以使用msck repair修复
-- 举例:手动创建一个year=2033目录
msck repair table one_part_order;
msck repair table multi_part_order;
-- 修复后再次查看所有分区
show partitions one_part_order;
show partitions multi_part_order;

分桶表

分桶表特点/好处: 需要产生分桶文件, 查询的时候特定操作上提升效率(过滤,join,分组 以及 抽样)

效率上注意: 如果分桶表,在查询数据的时候没有使用分桶字段去筛选数据,效率不变

分桶字段名注意: 分桶字段名必须是原有字段名, 因为分桶需要根据对应字段值取余数把余数相同的数据放到同一个分桶文件中

重要参数

– 默认开启,hive2.x版本已经被移除
set hive.enforce.bucketing; – 查看未定义因为已经被移除
set hive.enforce.bucketing=true; – 修改

– 查看reduce数量
– 参数优先级: set方式 > hive文档 > hadoop文档
set mapreduce.job.reduces; – 查看默认-1,代表自动根据桶数量匹配reduce数量
set mapreduce.job.reduces=3; – 设置参数

基础分桶表

创建基础分桶表:
create [external] table [if not exists] 表名(
字段名 字段类型
)
clustered by (分桶字段名)
into 桶数量 buckets ;

-- 1.创建基础分桶表,要求分3个桶
create table course_base (
    cid int,
    cname string,
    sname string
)
clustered by(cid) into 3 buckets
row format delimited fields terminated by '\t';

-- 2.load方式加载数据
-- 前提: 已经上传course.txt文件到hdfs的/source目录下
load data inpath '/source/course.txt' into table course_base;

-- 3.查询数据,观察结果
select * from course_base;

分桶表排序

创建基础分桶表,然后桶内排序:
create [external] table [if not exists] 表名(
字段名 字段类型
)
clustered by (分桶字段名)
sorted by(排序字段名 asc|desc) # 注意:asc升序(默认) desc降序
into 桶数量 buckets ;

-- 1.创建基础分桶表,要求分3个桶,桶内根据cid降序
create table course_sort (
    cid int,
    cname string,
    sname string
)
clustered by(cid) sorted by (cid desc) into 3 buckets
row format delimited fields terminated by '\t';

-- 2.加载数据
-- 还是使用/source/course.txt数据文件
load data inpath '/source/course.txt' into table course_sort;

-- 3.查询数据,观察结果
select * from course_sort;

分桶原理

分桶原理:
如果是数值类型分桶字段: 直接使用数值对桶数量取模
如果是字符串类型分桶字段: 底层会使用hash算法计算出一个数字然后再对桶数量取模

Hash: Hash是一种数据加密算法，其原理不去详细讨论，我们只需要知道其主要特征：同样的值被Hash加密后的结果是一致的

分区表和分桶表的区别

分区表
创建表的时候使用关键字: partition by (分区字段名 分区字段类型)
分区字段名注意事项: 是一个新的字段,需要指定类型,且不能和其他字段重名
分区表好处: 使用分区字段作为条件的时候,底层直接找到对应的分区目录,能够避免全表扫描,提升查询效率
分区表最直接的效果: 在hfds表目录下,分成多个分区目录(year=xxxx,month=xx,day=xx)
不建议直接上传文件在hdfs表根路径下: 分区表直接不能识别对应文件中数据,因为分区表会找分区目录下的数据文件
使用load方式加载hdfs中文件: 本质是移动文件到对应分区目录下
不进行MR流程

分桶表
创建表的时候使用关键字: clustered by (分桶字段名) into 桶数量 buckets
分桶字段名注意事项: 是指定一个已存在的字段,不需要指定类型
分桶表好处: 使用分桶字段做抽样等特定操作的时候,也能提升性能效率
分桶表最直接的效果: 在hdfs表目录或者分区目录下,分成多个分桶文件(000000_0,000001_0,000002_0…)
不建议直接上传文件在hdfs表根路径下: 分桶表可以识别对应文件中数据,但是并没有分桶效果,也是不建议的
使用load方式加载hdfs中文件: 本质是复制数据到各个分桶文件中
进行MR任务

复杂类型

hive的SerDe机制

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。本次我们主要学习DELIMITED关键字相关知识点

如果使用delimited: 表示底层默认使用的Serde类:LazySimpleSerDe类来处理数据。

如果使用serde:表示指定其他的Serde类来处理数据,支持用户自定义SerDe类。

Hive默认的序列化类: LazySimpleSerDe
包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。
在建表的时候可以根据数据的类型特点灵活搭配使用。
COLLECTION ITEMS TERMINATED BY ‘分隔符’ : 指定集合类型（array）/结构类型(struct)元素的分隔符
MAP KEYS TERMINATED BY ‘分隔符’ : 表示映射类型(map)键值对之间用的分隔

复杂类型

复杂类型建表格式:

…
 [row format delimited] # hive的serde机制
 [fields terminated by ‘字段分隔符’] # 自定义字段分隔符固定格式
 [collection ITEMS terminated by ‘集合分隔符’] # 自定义array同类型集合和struct不同类型集合
 [map KEYS terminated by ‘键值对分隔符’] # 自定义map映射kv类型
[lines terminated by ‘\n’] # # 默认即可
 …;

hive复杂类型: array struct map

array类型: 又叫数组类型,存储同类型的单数据的集合
建表指定类型: array<数据类型>
取值: 字段名[索引] 注意: 索引从0开始
获取长度: size(字段名)
判断是否包含某个数据: array_contains(字段名,某数据)

struct类型: 又叫结构类型,可以存储不同类型单数据的集合
建表指定类型: struct<子字段名1:数据类型1, 子字段名2:数据类型2 , …>
取值: 字段名.子字段名n

map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)
建表指定类型: map<key类型,value类型>
取值: 字段名[key]
获取长度: size(字段名)
获取所有key: map_keys()
获取所有value: map_values()

array

-- 演示使用简单类型映射数据
-- 创建表
create table test_array1(
    name string,
    location string
)row format delimited
fields terminated by '\t';
-- 加载数据
load data inpath '/source/data_for_array_type.txt' into table test_array1;
-- 验证数据
select * from test_array1;


-- 演示使用array类型映射数据
-- 创建表
create table test_array2(
    name string,
    location array<string>
)row format delimited
fields terminated by '\t'
collection items terminated by ',';
-- 加载数据
load data inpath '/source/data_for_array_type.txt' into table test_array2;
-- 验证数据
select * from test_array2;

-- 需求: 查询zhangsan的地址有几个?
select size(location) from test_array2 where name = 'zhangsan';
-- 需求: 查询zhangsan的第二个地址?
select location[1] from test_array2 where name = 'zhangsan';
-- 需求: 查询zhangsan是否在tianjin住过?
select array_contains(location,'tianjin') from test_array2 where name = 'zhangsan';

struct

-- 演示使用简单类型映射数据
-- 创建表
create table test_struct1(
    id int,
    info string
)row format delimited
fields terminated by '#';
-- 加载数据(前提hdfs必须有对应文件)
load data inpath '/source/data_for_struct_type.txt' into table test_struct1;
-- 验证数据
select * from test_struct1;

-- 演示struct类型映射数据
-- 创建表
create table test_struct2(
    id int,
    info struct<name:string,age:int>
)row format delimited
fields terminated by '#'
collection items terminated by ':';
-- 加载数据(前提hdfs必须有对应文件)
load data inpath '/source/data_for_struct_type.txt' into table test_struct2;
-- 验证数据
select * from test_struct2;

-- 需求: 获取所有的姓名
select info.name from test_struct2;
-- 需求: 获取所有的年龄
select info.age from test_struct2;

map

-- 演示简单类型映射数据
-- 创建表
create table test_map1(
    id int,
    name string,
    info string,
    age int
)row format delimited
fields terminated by ',';
-- 加载数据(前提hdfs有对应数据文件)
load data inpath '/source/data_for_map_type.txt' into table test_map1;
-- 验证数据
select * from test_map1;

-- 演示map类型的应用
-- 创建表
create table test_map2(
    id int,
    name string,
    info map<string,string>,
    age int
)row format delimited
fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';
-- 加载数据(前提hdfs有对应数据文件)
load data inpath '/source/data_for_map_type.txt' into table test_map2;
-- 验证数据
select * from test_map2;

-- 需求: 查看所有人的father,mother信息
select name,info['father'] as father ,info['mother'] as mother from test_map2;
-- 需求: 查看所有人的家庭相关角色
select name,map_keys(info) from test_map2;
-- 需求: 查看所有人的家庭相关姓名
select name,map_values(info) from test_map2;
-- 需求: 查看所有人的家庭相关人员个数
select name,size(info) as cnt from test_map2;
-- 需求: 查看马大云是否包含brother角色
select name,array_contains(map_keys(info),'brother') from test_map2 where name = '马大云';