Apache Hive 使用语法与概念原理

一、数据库操作

1.1.数据库操作语法

基于语法描述说明

CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION] 'path';
SELECT expr, ... FROM tbl ORDER BY col_name [ASC | DESC]
(A | B | C)

如上语法，在语法描述中出现：

[]，表示可选，如上[LOCATION]表示可写、可不写
|，表示或，如上ASC | DESC，表示二选一
...，表示序列，即未完结，如上SELECT expr, ... 表示在SELECT后可以跟多个expr（查询表达式），以逗号隔开
()，表示必填，如上(A | B | C)表示此处必填，填入内容在A、B、C中三选一

1.2.数据库操作

创建数据库

CREATE DATABASE IF NOT EXISTS myhive;
USE myhive

查看数据库详细信息

DESC DATABASE myhive;

输入如下：

数据库本质上就是在 HDFS 之上的文件夹。默认数据库的存放路径是 HDFS 的： /user/hive/warehouse 内

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

create database myhive2 location '/myhive2/myhive2.db';

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

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

drop database myhive;

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

drop database myhive2 cascade;

1.3.数据库和HDFS的关系

Hive 的库在 HDFS 上就是一个以 .db 结尾的目录，默认存储在： /user/hive/warehouse 内，可以通过 LOCATION 关键字在创建的时候指定存储目录

二、数据表操作

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

2.1.1.创建表的语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_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.1.2.数据类型如下:

Hive中常见数据类型较多，下面红色标注的十常见的：

分类	类型	描述	字面量示例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1 字节的有符号整数 -128~127	1Y
	INT	4 个字节的带符号整数	1S
	BIGINT	8 字节带符号整数	1L
	FLOAT	4 字节单精度浮点数
	DOUBLE	8 字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组
	TIMESTAMP	时间戳，毫秒值精度	122327495796
	DATE	日期	2019-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)

2.1.3.基础创建案例

建表语法比较复杂，暂时未接触到分区、分桶等概念。所以，创建一个简答的数据库表可以有如下 SQL ：

# 创建表
CREATE TABLE test (
    id INT,
    name STRING,
    gender STRING
);

删除表可以使用：

# 删除表
DROP TABLE table_name;

2.1.4.表分类

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

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

不同类型的表有各自的用途。

2.2.内部表操作

2.2.1.内部表

语法：

CREATE TABLE table_name ......

说明：未被 external 关键字修饰的即是内部表，即普通表。内部表又称管理表 , 内部表数据存储的位置由 hive.metastore.warehouse.dir 参数决定（默认： /user/hive/warehouse ），删除内部表会直接删除元数据（ metadata ）及存储数据，因此内部表不适合和其他工具共享数据。

2.2.2.创建内部表

创建一个基础表

# 创建库
CREATE DATABASE IF NOT EXISTS myhive

# 选择库
USE myhive;

# 创建内部表
CREATE TABLE IF NOT EXISTS student(id int, name string);

# 插入数据
INSERT INTO student values (1,'吴迪'),(2,'顾严')

# 查看数据
SELECT * FROM student;

查看表的数据存储

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

2.2.3.数据分隔符

数据在 HDFS 上也是以明文文件存在的。

是不是很奇怪列 ID 和列 NAME ，好像没有分隔符一样挤在一起了。这是因为，默认的数据分隔符是：” \001” 是一种特殊字符，是 ASCII 值，键盘是打不出来，在某些文本编辑器中是显示为 SOH 的。

1SOH吴迪

ASCII表如下：

2.2.4.自行指定分隔符

分隔符我们是可以自行指定的。在创建表的时候可以指定：

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 分隔

案例：

-- 创建表，设置数据分隔符为\t
CREATE TABLE IF NOT EXISTS student2(
    id int,
    name string
)row format delimited fields terminated by '\t'

-- 插入数据
INSERT INTO student2 values (1,'李元霸'),(2,'裴元庆');

-- 查询数据
SELECT * FROM student2;

查看数据，发现分隔符已经改变使用制表符\t

2.2.5.其它创建内部表的形式

除了标准的 CREATE TABLE table_name 的形式创建内部表外我们还可以通过：

CREATE TABLE table_name as ，基于查询结果建表(之前表格的数据也会被复制)

create table student3 as select * from student2;

CREATE TABLE table_name like ，基于已存在的表结构建表（只有表结构，没有数据）

create table student4 like student2;

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

DESC FORMATTED student2;

2.2.6.删除内部表

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

DROP TABLE table_name ，# 删除表

案例，删除student2

DROP TABLE student2

可以看到, student2 文件夹已经不存在了，数据被删除了

2.3.外部表操作

2.3.1.外部表

外部表语法：

CREATE EXTERNAL TABLE table_name ......LOCATION......

说明：

被 external 关键字修饰的即是外部表，即关联表。
外部表是指表数据可以在任何位置，通过 LOCATION 关键字指定。数据存储的不同也代表了这个表在理念是并不是。
Hive 内部管理的，而是可以随意临时链接到外部数据上的。

所以，在删除外部表的时候，仅仅是删除元数据（表的信息），不会删除数据本身。

2.3.2.外部表的创建

外部表，创建表被 EXTERNAL 关键字修饰，从概念是被认为并非 Hive 拥有的表，只是临时关联数据去使用。创建外部表也很简单，基于外部表的特性，可以总结出：外部表和数据是相互独立的，即：

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

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

1       theima
2       itcast
3       hadoop

注意：数据列使用制表符“\t”进行分割

2.3.3.1.先创建表，在移动数据

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

首先检查：确认不存在 /tmp/test_ext1 目录

hadoop fs -ls /tmp

创建外部表：

-- 创建外部表
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 ，空结果，无数据

上传数据： hdfs dfs -put test_external.txt /tmp/test_ext1/

select * from test_ext1 ，即可看到数据结果：

2.3.3.1.先存在数据，在创建外部表

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

先在HDFS中创建目录/tmp/test_ext2

hdfs dfs -mkdir /tmp/test_ext2

上传创建的文件

hdfs dfs -put test_external.txt /tmp/test_ext2/

创建外部表，指定数据存储位置是 /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;

2.3.3.删除外部表

语法：

drop table test_ext1;

可以发现，在 Hive 中通过 show table ，表不存在了，但是在 HDFS 中，数据文件依旧保留

2.3.4.内外部表转换

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

查看表类型： desc formatted student;

转换

内部表转外部表

ALTER TABLE student SET tblproperties('EXTERNAL'='TRUE');

外部表转内部表

ALTER TABLE student SET tblproperties('EXTERNAL'='FALSE');

通过 stu set tblproperties 来修改属性，需要注意： ('EXTERNAL'='FALSE') 或 ('EXTERNAL'='TRUE') 为固定写法，区分大小写！！！

2.3.5.内部表和外部表的区别

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

2.4.数据加载和导出

2.4.1.数据加载

2.4.1.1.数据加载-LOAD语法

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

2.4.1.2.数据加载-LOAD语法案例

创建表：

CREATE TABLE test_load(
    dt string comment '时间(时分秒)',
    user_id string comment '用户ID',
    word string comment '搜索词',
    url string comment '用户访问地址'
)comment '搜索引擎日志表' row format delimited fields terminated by '\t';

准备数据：search_log.txt，注意分隔符是\t，有时候复制上去会出错，如果有问题自己调整一下

00:03:01        1233215666      百度    http://www.baidu.com
00:52:04        2233216666      谷歌搜索        http://www.google.cn
01:16:00        3233217666      必应    http://www.bing.cn
05:36:00        3233218666      hadoop  http://www.hadoop.com
17:39:00        2233219666      python  http://www.python.org.com
22:26:00        1233211666      java    http://www.java.com
23:42:00        6233212666      processon       https://www.processon.com

导入数据

-- 将/home/hadoop/search_log.txt导入到test_load表中,默认是追加模式
load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load
SELECT * FROM myhive.test_load

-- overwrite是覆盖模式，覆盖之前的数据
load data local inpath '/home/hadoop/search_log.txt' overwrite into table myhive.test_load

注意，基于 HDFS 进行 load 加载数据，源数据文件会消失（本质是被移动到表所在的目录中）

2.4.1.3.数据加载 - INSERT SELECT 语法

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

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

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

-- 创建test_load2
CREATE TABLE test_load2(
    dt string comment '时间(时分秒)',
    user_id string comment '用户ID',
    word string comment '搜索词',
    url string comment '用户访问地址'
)comment '搜索引擎日志表' row format delimited fields terminated by '\t';

-- 查询数据
SELECT * FROM test_load2;

-- 将test_load中的数据导入到test_load2中（这里有个小bugDBeaver多次导入相同数据会报错，但是数据可以导入进去）
INSERT INTO TABLE test_load2 SELECT * FROM test_load;

-- overwrite是覆盖模式导入
INSERT OVERWRITE TABLE test_load2 SELECT * FROM test_load;

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

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

⑴.数据在本地：推荐 load data local 加载

⑵.数据在 HDFS

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

⑶.数据已经在表中：只可以 INSERT SELECT

2.4.2.数据导出

2.4.2.1.hive 表数据导出 - insert overwrite 方式

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

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

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

-- 将test_load表中数据导出到/home/hadoop/export1目录下
insert overwrite local directory '/home/hadoop/export1' select * from test_load;

查看如下：发现分隔符是默认的显示效果并不好

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

-- 将test_load表中数据导出到/home/hadoop/export2目录下,指定分隔符为\t
insert overwrite local directory '/home/hadoop/export2'
row format delimited fields terminated by '\t'
select * from test_load

如下字段直接有分割了：

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

-- 将查询的结果导出到 HDFS 上 ( 不带 local 关键字 )
insert overwrite directory '/tmp/export'
row format delimited fields terminated by '\t'
select * from test_load

2.4.2.2.hive 表数据导出 - hive shell 方式

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

bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export2/export2.txt
# 执行指定SQL到处数据
bin/hive -f export.sql > /home/hadoop/export4/export4.txt

案例1：导出test_load数据到/home/hadoop/export2/export2.txt

bin/hive -e 'select * from myhive.test_load;' > /home/hadoop/export2/export2.txt

如下：

案例2：编写 export.sql文件，SQL需要说明是那个库下的那个表，内容如下：

SELECT * FROM myhive.test_load;

然后执行导出命令。就会将上面SQL中查询出来的结果保存，输出

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

执行如下：

2.5.分区表

2.5.1.什么是分区表？

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

下图，是一个按月份分区的表，每一个分区，是一个文件夹

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

2.5.2.分区表的语法

基本语法：

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

创建分区表语法：

create table score(s_id string,c_id string,s_score int) 
partitioned by(month string)
row format delimited fields terminated by '\t'

创建一个表有多个分区：

create table score2(s_id string,c_id string,s_score int) 
partitioned by(year string,month string,day string)
row format delimited fields terminated by '\t';

加载数据到分区表中：

load data local inpath '/export/server/hivedatas/score.txt' into table score partition(month='202306')

加载数据到一个多分区表中：

load data local inpath '/export/server/hivedatas/score.txt' into table score2 partition(year='2023',month='06',day='01')

2.5.3.分区表的使用

查看分区：

show partitions score;

添加一个分区

alter table score add partition(month='202305');

同时添加多个分区

alter table score add partition(month='202304') partition(month='202303');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

删除分区

alter table score drop partition(month='202305');

2.5.4.分区表练习

需求描述：现在有一个文件 score.txt 文件，存放在集群的这个目录下 /scoredatas/month=202006 这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建 hive 对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除。

方式一：

⑴.准备数据文件

# 创建目录
hadoop fs -mkdir -p /scoredatas/month=202306
# 准备文件score.txt上传到指定目录
hadoop fs -put score.txt /scoredatas/month=202306/

⑵.创建外部分区表。并指定文件数据存放目录

create external table score5(s_id string,c_id string,s_score int)
partitioned by(month string)
row format delimited fields terminated by '\t'
location '/scoredatas';

⑶.进行表的修复换言之就是建立我们表与我们数据文件之间的一个关系映射

msck repair table score5;

注意：修复成功后即可看到数据已经全部加载到表当中去了

方式二：

上传数据之后手动添加分区即可

hadoop fs -mkdir -p /scoredatas/month=202305
hadoop fs -put score.txt /scoredatas/month=202305/

修改表，进行手动添加方式

alter table score5 add partition(month='202305')

然后查看，会发现有两个分区：

2.6.分桶表

2.6.1.分桶表

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式，但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

2.6.2.创建分桶表

开启分桶的自动优化（自动匹配 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';

2.6.3.分桶表数据加载

桶表的数据加载，由于桶表的数据加载通过 load data 无法执行，只能通过 insert select。所以，比较好的方式是

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

# 创建普通表
create table course_common (c_id string,c_name string,t_id string)
row format delimited fields terminated by '\t';

# 给普通表中导入数据
load data local inpath '/home/hadoop/score.txt' into table course_common;

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

# 通过insert overwrite给桶表中加载数据
insert overwrite table course SELECT * FROM course_common cluster by(c_id);

2.6.4.为什么插入数据不能使用 load data

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

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

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

但是上面问题就在于：如何将数据分成三份，划分的规则是什么？

数据的三份划分基于分桶列的值进行 hash 取模来决定，由于 load data 不会触发 MapReduce ，也就是没有计算过程（无法执行 Hash 算法），只是简单的移动数据而已，所以无法用于分桶表数据插入。

2.6.5.Hash 取模

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

同样的值被 Hash 加密后的结果是一致的

比如字符串“ hadoop” 被 Hash 后的结果是 123412（仅作为示意），那么无论计算多少次，字符串“ hadoop” 的结果都会是 123412。
比如字符串“ hdfs” 被 Hash 后的结果是 132233 （仅作为示意），那么无论计算多少次，字符串“ bigdata” 的结果都会是 132233 。

基于如上特征，在辅以有 3 个分桶文件的基础上，将 Hash 的结果基于 3 取模（除以 3 取余数）那么，可以得到如下结果：

无论什么数据，得到的取模结果均是： 0 、 1 、 2 其中一个
同样的数据得到的结果一致，如 hadoop hash 取模结果是 1 ，无论计算多少次，字符串 hadoop 的取模结果都是 1

所以，必须使用 insert select 的语法，因为会触发 MapReduce ，进行 hash 取模计算。

2.6.6.分桶表的性能提升

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

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

⑴.基于分桶列，过滤单个值

基于分桶列过滤单个值(hadoop)可以提高性能的原因如下：

减少需要扫描的数据量：分桶将数据按照分桶列的哈希值进行分组存储，因此当你基于分桶列过滤单个值时，Hive可以根据哈希值快速确定该数值所在的分桶，然后只扫描该分桶的数据，而不需要扫描整个表的数据。这样就大大减少了需要处理的数据量，从而提高了查询效率。
减少shuffle操作：当执行基于分桶列的过滤时，Hive可以利用哈希值对数据进行划分，可以避免不必要的数据洗牌（shuffle）操作，减少了数据的移动和网络传输，从而提高了查询性能。

⑵.基于分桶列，进行双表 JOIN

在Hive中，通过基于分桶列进行双表JOIN可以提高性能的原因如下：

局部性原理：基于分桶列进行JOIN时，Hive可以将具有相同分桶列值的记录聚集到同一个reduce任务中处理。这样可以最大程度地减少数据的移动和网络传输，提高了数据处理的效率。因为JOIN的两个表都是按照相同的分桶列进行分桶的，所以在JOIN时可以直接匹配到同一个分桶上的数据，减少了不必要的数据传输和处理。
减少shuffle操作：由于数据已经按照分桶列进行了分组存储，JOIN时可以避免不必要的shuffle操作，节省了数据洗牌和网络传输的开销。
并行处理：基于分桶列进行JOIN可以实现并行处理，每个reduce任务都独立地处理一个或多个分桶，提高了查询的并行度和整体的处理速度。

在Hive中，通过基于分桶列进行双表JOIN可以提高性能的原因如下：

局部性原理：基于分桶列进行JOIN时，Hive可以将具有相同分桶列值的记录聚集到同一个reduce任务中处理。这样可以最大程度地减少数据的移动和网络传输，提高了数据处理的效率。因为JOIN的两个表都是按照相同的分桶列进行分桶的，所以在JOIN时可以直接匹配到同一个分桶上的数据，减少了不必要的数据传输和处理。
减少shuffle操作：由于数据已经按照分桶列进行了分组存储，JOIN时可以避免不必要的shuffle操作，节省了数据洗牌和网络传输的开销。
并行处理：基于分桶列进行JOIN可以实现并行处理，每个reduce任务都独立地处理一个或多个分桶，提高了查询的并行度和整体的处理速度。

⑶.基于分桶列， group by 分组

在Hive中，基于分桶列进行GROUP BY分组可以提高性能的原因如下：

数据本地化处理：基于分桶列进行GROUP BY时，Hive可以将具有相同分桶列值的记录聚集到同一个reduce任务中处理。这样可以减少数据的移动和网络传输，提高了数据处理的效率。因为GROUP BY的字段和分桶字段是相关的，所以在进行GROUP BY时可以直接在同一个reduce任务上对同一个分桶上的数据进行聚合处理，减少了不必要的数据传输和处理。
减少shuffle操作：由于数据已经按照分桶列进行了分组存储，进行GROUP BY时可以避免不必要的shuffle操作，节省了数据洗牌和网络传输的开销。
并行处理：基于分桶列进行GROUP BY可以实现并行处理，每个reduce任务都独立地处理一个或多个分桶，提高了查询的并行度和整体的处理速度。

2.7.修改表

表重命名

alter table old_table_name rename to new_table_name;

修改表属性

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

其他属性的修改参考：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;

注意: 只可以清空内部表

2.8.复杂类型操作

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

array：数组类型
map：映射类型
struct：结构类型

2.8.1.Array类型

如下数据文件，有 2 个列， locations 列包含多个城市，说明 :name 与 locations 之间制表符分隔， locations 中元素之间逗号分隔

name | locations
-------------------------------------------------
Augus | beijing,shanghai,tianjin,hangzhou
tom | shenzhen,wuhan,tianjing,beijin

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

创建array语法：

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）元素的分隔符是逗号

基于 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
-- 查询location数组中的第一个元素
SELECT name,work_locations[0] location FROM myhive.test_array;
-- 查询location数组中元素的个数
SELECT name,size(work_locations) location FROM myhive.test_array;
-- 查询location数组中包含beijing的信息
SELECT * FROM myhive.test_array where ARRAY_CONTAINS(work_locations, 'beijing')

2.8.2.Map类型

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

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

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

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

说明：

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 设置字段分隔符是逗号“，”
COLLECTION ITEMS TERMINATED BY '#' 设置多个kv键值对之间的分隔符为“#”
MAP KEYS TERMINATED BY ':'; 设置key和value之间的分隔符是冒号

基于 map 定义的结构，一条数据示例

导入数据：

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 FROM myhive.test_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') ;

2.8.3.struct类型

struct 类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称，有如下数据文件，说明：字段之间 # 分割， struct 之间冒号分割

1#李易峰:11
2#林均杰:16
3#刘德滑:21
4#张学油:26
5#王清波:23

建表SQL语法：

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 myhive.test_struct;
-- 直接使用列名，子列名即可从struct中取出子列查询
SELECT id,info.name,info.age FROM myhive.test_struct;

2.8.4.array、map、struct类型总结

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

如：ALTER TABLEtable_nameSET TBLPROPERTIES ('comment' = new_comment);修改表注释

posted @ 2019-10-29 11:40 酒剑仙* 阅读(1324) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Augus

Apache Hive 使用语法与概念原理

一、数据库操作

1.1.数据库操作语法

1.2.数据库操作

1.3.数据库和HDFS的关系

二、数据表操作

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

2.1.1.创建表的语法：

2.1.2.数据类型如下:

2.1.3.基础创建案例

2.1.4.表分类

2.2.内部表操作

2.2.1.内部表

2.2.2.创建内部表

2.2.3.数据分隔符

2.2.4.自行指定分隔符

2.2.5.其它创建内部表的形式

2.2.6.删除内部表

2.3.外部表操作

2.3.1.外部表

2.3.2.外部表的创建

2.3.3.1.先创建表，在移动数据

2.3.3.1.先存在数据，在创建外部表

2.3.3.删除外部表

2.3.4.内外部表转换

2.3.5.内部表和外部表的区别

2.4.数据加载和导出

2.4.1.数据加载

2.4.1.1.数据加载-LOAD语法

2.4.1.2.数据加载-LOAD语法案例

2.4.1.3.数据加载 - INSERT SELECT 语法

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

2.4.2.数据导出

2.4.2.1.hive 表数据导出 - insert overwrite 方式

2.4.2.2.hive 表数据导出 - hive shell 方式

2.5.分区表

2.5.1.什么是分区表？

2.5.2.分区表的语法

2.5.3.分区表的使用

2.5.4.分区表练习

方式一：

方式二：

2.6.分桶表

2.6.1.分桶表

2.6.2.创建分桶表

2.6.3.分桶表数据加载

2.6.4.为什么插入数据不能使用 load data

2.6.5.Hash 取模

2.6.6.分桶表的性能提升

2.7.修改表

2.8.复杂类型操作

2.8.1.Array类型

2.8.2.Map类型

2.8.3.struct类型

2.8.4.array、map、struct类型总结

公告