标签：name 19 分区 Hive -- 视图 DDL table

数据定义语言（Data Definition Language，DDL）是 SQL 语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括 database(schema)、table、view、index 等。核心语法由 CREATE、ALTER 与 DROP 三个所组成。DDL 并不涉及表内部数据的操作。

在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。

Hive SQL（HQL）与 SQL 的语法大同小异，基本上是相通的，学过 SQL 的使用者可以无痛使用 Hive SQL。只不过在学习 HQL 语法的时候，特别要注意 Hive 自己特有的语法知识点，比如 partition 相关的 DDL 操作。

基于 Hive 的设计、使用特点，HQL 中 create 语法（尤其 create table）将是学习掌握 DDL 语法的重中之重。可以说建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于 SQL 分析数据。通俗点说，没有表，表没有数据，你分析什么呢？

1. Hive DDL 基础

简要说明：

（1）TEMPORARY

临时表，该表只在当前会话可见，会话结束，表会被删除。

（2）EXTERNAL

外部表，与之相对应的是内部表（管理表）。管理表意味着 Hive 会完全接管该表，包括元数据和 HDFS 中的数据。而外部表则意味着 Hive 只接管元数据，而不完全接管 HDFS 中的数据。

（3）data_type

Hive 中的字段类型可分为基本数据类型和复杂数据类型。

（4）PARTITIONED BY

创建分区表

（5）CLUSTERED BY ... SORTED BY...INTO ... BUCKETS

创建分桶表

（6）ROW FORMAT

指定 SERDE（Serializer and Deserializer），Hive 使用 SERDE 序列化和反序列化每行数据。

（7）STORED AS

指定文件格式，常用的文件格式有 textfile（默认）、sequence file、orc file、parquet file 等。

（8）LOCATION

指定表所对应的HDFS路径，若不指定路径，其默认值为 ${hive.metastore.warehouse.dir}/db_name.db/table_name

（9）TBLPROPERTIES

用于配置表的一些 KV 键值对参数。

1.1 数据类型

Hive 中的数据类型指的是 Hive 表中的列字段类型。Hive 数据类型整体分为两个类别：原生数据类型（Primitive Data Type）和复杂数据类型（Complex Data Type）。

• 原生数据类型：数值类型、时间类型、字符串类型、杂项数据类型；
• 复杂数据类型：array 数组、map 映射、struct 结构、union 联合体。

关于 Hive 的数据类型，需要注意：

• 英文字母大小写不敏感；
• 除 SQL 数据类型外，还支持 Java 数据类型，如：string；
• int 和 string 是使用最多的，大多数函数都支持；
• 复杂数据类型的使用通常需要和分隔符指定语法配合使用。
• 如果定义的数据类型和文件不一致，Hive 会尝试隐式转换，但是不保证成功。

a. 原生数据类型

b. 数据类型转换

与 SQL 类似，HQL 支持隐式和显式类型转换。

【隐式转换】原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。 下表描述了类型之间允许的隐式转换：

• 任何整数类型都可以隐式地转换为一个范围更广的类型，如 tinyint 可以转换成 int，int 可以转换成 bigint。
• 所有整数类型、float 和 string 类型都可以隐式地转换成 double。
• tinyint、smallint、int 都可以转换为 float。
• boolean 类型不可以转换为任何其它的类型。

【显式类型】显式类型转换使用 CAST 函数。例如 CAST('100' as INT) 会将字符串 '100' 转换为整数值 '100'。 如果强制转换失败，例如 CAST('Allen' as INT)，该函数返回 NULL。

1.2 读写文件机制

a. SerDe 概述

SerDe 是 "Serializer and Deserializer" 的简称，Hive 使用 SERDE 序列化和反序列化每行数据。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。

Hive 使用 SerDe（和 FileFormat）读取和写入行数据。需要注意的是，key 部分在读取时会被忽略，而在写入时 key 始终是常数。基本上行对象存储在 value 中。

通过 desc formatted TABLE_NAME 查看该表的结构化数据，默认如下：

b. 读写文件流程

• Hive 读取文件机制：首先调用 InputFormat（默认 TextInputFormat），返回一条一条 kv 键值对记录（默认是一行对应一条记录）。然后调用 SerDe（默认 LazySimpleSerDe）的 Deserializer，将一条记录中的 value 根据分隔符切分为各个字段。
• Hive 写文件机制：将 Row 写入文件时，首先调用 SerDe（默认 LazySimpleSerDe）的 Serializer 将对象转换成字节序列，然后调用 OutputFormat 将数据写入 HDFS 文件中。

c. SerDe 语法

（1）在 Hive 的建表语句中，和 SerDe 相关的语法

其中 ROW FORMAT 是语法关键字，DELIMITED 和 SERDE 二选其一。

如果使用 DELIMITED 表示使用默认的 LazySimpleSerDe 类来处理数据。

若数据文件格式比较特殊可以使用 ROW FORMAT SERDE serde_name 指定其他内置的 SerDe 类来处理数据，甚至支持用户自定义 SerDe 类。

（2）LazySimpleSerDe 分隔符指定

LazySimpleSerDe 是 Hive 默认的序列化类，包含 4 种子语法，分别用于指定字段之间、集合元素之间、map 映射 kv 之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

这里有一点需要注意，建表语法里只提供了 map 中的 key 与 value 的分隔符语法，没有 struct 属性名和值的分割符号，因为 struct 的属性名是元数据。

（3）Hive 默认分隔符

Hive 建表时如果没有 ROW FORMAT 语法。此时字段之间默认的分割符是 '\001'，是一种特殊的字符，使用的是 ASCII 编码的值，键盘是打不出来的。

1.3 数据存储路径

默认存储路径：

Hive 表默认存储路径是由 ${HIVE_HOME}/conf/hive-site.xml 配置文件的 hive.metastore.warehouse.dir 属性指定。默认值：/user/hive/warehouse。

在该路径下，文件将根据所属的库、表，有规律的存储在对应的文件夹下。

指定存储路径：

在 Hive 建表的时候，可以通过 LOCATION 语法来更改数据在 HDFS 上的存储路径，使得建表加载数据更加灵活方便。

对于已经生成好的数据文件，使用 LOCATION 指定路径将会很方便。

1.4 建表语法案例

a. 简单数据类型案例

文件 archer.txt 中记录了某手游射手的相关信息，内容如下所示，其中字段之间分隔符为制表符 \t，要求在 Hive 中建表映射成功该文件。

字段：id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）。

分析：字段都是基本类型，字段的顺序需要注意一下。字段之间的分隔符是制表符，需要使用 ROW FORMAT 语法进行指定。

-- 建库
create database tree;
use tree;

-- 建表
create table t_archer
(
    id           int comment "ID",
    name         string comment "英雄名称",
    hp_max       int comment "最大生命",
    mp_max       int comment "最大法力",
    attack_max   int comment "最高物攻",
    defense_max  int comment "最大物防",
    attack_range string comment "攻击范围",
    role_main    string comment "主要定位",
    role_assist  string comment "次要定位"
) comment "射手信息"
row format delimited
    fields terminated by "\t";

建表成功之后，在 Hive 的默认存储路径下就生成了表对应的文件夹，把 archer.txt 文件上传到对应的表文件夹下：hadoop fs -put archer.txt /user/hive/warehouse/tree.db/t_archer。

b. 复杂数据类型案例

文件 hot_hero_skin_price.txt 中记录了某手游热门英雄的相关皮肤价格信息，内容如下，要求在 Hive 中建表映射成功该文件。

字段：id、name（英雄名称）、win_rate（胜率）、skin_price（皮肤及价格）

分析：前 3 个字段原生数据类型、最后一个字段复杂类型 map。需要指定字段之间分隔符、集合元素之间分隔符、map kv 之间分隔符。

create table t_hot_hero_skin_price
(
    id         int,
    name       string,
    win_rate   int,
    skin_price map<string,int>
) comment "皮肤价格信息"
row format delimited
    fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':';

建表成功后，把 hot_hero_skin_price.txt 文件上传到对应的表文件夹下：hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/tree.db/t_hot_hero_skin_price。

c. 默认分隔符案例

文件 team_ace_player.txt 中记录了某手游主要战队内最受欢迎的王牌选手信息，内容如下，要求在 Hive 中建表映射成功该文件。

字段：id、team_name（战队名称）、ace_player_name（王牌选手名字）

分析：数据都是原生数据类型，且字段之间分隔符是 \001，因此在建表的时候可以省去 ROW FORMAT 语句，因为 Hive 默认的分隔符就是 \001。

create table t_team_ace_player
(
    id              int,
    team_name       string,
    ace_player_name string
);

建表成功后，把 team_ace_player.txt 文件上传到对应的表文件夹下：hadoop fs -put team_ace_player.txt /user/hive/warehouse/tree.db/t_team_ace_player。

d. SERDE 和复杂类型

若现有如下格式的 JSON 文件需要由 Hive 进行分析处理，请考虑如何设计表？

注：以下内容为格式化之后的结果，文件中每行数据为一个完整的 JSON 字符串。

{
    "name": "yudingyan",
    "friends": [
        "linnalian",
        "piaozhixiao"
    ],
    "students": {
        "stu1": 13,
        "stu2": 67
    },
    "address": {
        "street": "jyp",
        "city": "shouer",
        "code": 2
    }
}

我们可以考虑使用专门负责 JSON 文件的 JSON Serde，设计表字段时，表的字段与 JSON 字符串中的一级字段保持一致，对于具有嵌套结构的 JSON 字符串，考虑使用合适复杂数据类型保存其内容。最终设计出的表结构如下：

create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

创建该表，并上传 json 数据文件到 Hive 表指定的路径。注意，需要确保文件中每行数据都是一个完整的 JSON 字符串，JSON SERDE 才能正确的处理。

select friends[0], students, students[‘xiaohaihai’], address.city from teacher;

2. Hive DDL 进阶

2.1 Hive 内/外部表

a. 内部表

内部表（Internal table）也称为被 Hive 拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive 拥有该表的结构和文件。换句话说，Hive 完全管理表（元数据和数据）的生命周期，类似于 RDBMS 中的表。

当删除内部表时，它会删除数据以及表的元数据。

可以使用 DESCRIBE FORMATTED 表名 来获取表的描述信息，从中可以看出表的类型。

b. 外部表

外部表（External table）中的数据不是 Hive 拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用 EXTERNAL 语法关键字。

删除外部表只会删除元数据，而不会删除实际数据。在 Hive 外部仍然可以访问实际数据，而且外部表更为方便的是可以搭配 LOCATION 语法指定数据的路径。

create external table student_ext
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
) 
row format delimited fields terminated by ','
location '/hive-ext';

使用 DESCRIBE FORMATTED student_ext; 来获取表的描述信息，从中可以看出表的类型。

c. 表差异

无论内部表还是外部表，Hive 都在 Hive Metastore 中管理表定义及其分区信息。

• 删除内部表会从 Metastore 中删除表元数据，还会从 HDFS 中删除其所有数据/文件。
• 删除外部表，只会从 Metastore 中删除表的元数据，并保持 HDFS 位置中的实际数据不变。

如何选择内部表、外部表？

• 当需要通过 Hive 完全管理控制表的整个生命周期时，请使用内部表。
• 当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。

Hive 表对应的数据在 HDFS 上的存储位置：

（1）在创建外部表的时候，可以使用 LOCATION 指定存储位置路径，如果不指定会如何？

如果不指定 LOCATION，外部表的默认路径也是位于 /user/hive/warehouse，由默认参数控制。

（2）创建内部表的时候，是否可以使用 LOCATION 指定？

内部表可以使用 LOCATION 指定位置的。

（3）是否意味着 Hive 表的数据在 HDFS 上的位置不是一定要在 /user/hive/warehouse 下？

不一定，Hive 中表数据存储位置，不管内部表还是外部表，默认都是在 /user/hive/warehouse，当然可以在建表的时候通过 LOCATION 关键字指定存储位置在 HDFS 的任意路径。

2.2 Hive 分区表

a. 场景演示

现有 6 份数据文件，分别记录了某手游中 6 种位置的英雄相关信息。现要求通过建立一张表 t_all_hero，把 6 份文件同时映射加载。

create table t_all_hero
(
    id           int,
    name         string,
    hp_max       int,
    mp_max       int,
    attack_max   int,
    defense_max  int,
    attack_range string,
    role_main    string,
    role_assist  string
)
row format delimited fields terminated by "\t";

加载数据文件到 HDFS 指定路径下：hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/tree.db/t_all_hero。

现要求查询 role_main 主要定位是射手并且 hp_max 最大生命大于 6000 的有几个，sql 语句如下：

select count(*) from t_all_hero where role_main="archer" and hp_max>6000; -- 查询耗时25s！

思考一下：WHERE 语句的背后需要进行全表扫描才能过滤出结果，对于 Hive 来说需要扫描每一个文件。如果数据文件个数特别多的话，扫描效率很慢也没必要。本需求中，只需要扫描 archer.txt 文件即可，如何优化可以加快查询，减少全表扫描呢？

指定文件扫描和全表扫描，效率还是存在差异的。

b. 创建分区表

当 Hive 表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive 支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。

Hive 中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录，每个目录就称为该表的一个分区。在查询时通过 WHERE 子句中的表达式选择查询所需要的分区，这样的查询效率会提高很多。

比如把一整年的数据根据月份划分 12 个月（12 个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

分区表建表语法（可以建立指定多个分区字段，建立多级分区表）：

针对英雄数据，重新创建一张分区表 t_all_hero_part，以 role 角色作为分区字段。

create table t_all_hero_part
(
    id           int,
    name         string,
    hp_max       int,
    mp_max       int,
    attack_max   int,
    defense_max  int,
    attack_range string,
    role_main    string,
    role_assist  string
) partitioned by (role string)
    row format delimited fields terminated by "\t";

注意：分区字段不能是表字段声明中出现的字段！因为分区字段最终也会以虚拟字段的形式显示在表结构上。

c. 数据加载·静态分区

所谓「静态分区」指的是分区的属性值是由用户在加载数据的时候手动指定的。

-- local 参数用于指定待加载的数据是位于本地文件系统还是HDFS文件系统
load data [local] inpath 'filepath' into table tablename partition(分区字段='分区值' ...);

静态加载数据操作如下，文件都位于 Hive 所在机器本地文件系统上。

load data local inpath '/home/hive-test/hero/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/home/hive-test/hero/assassin.txt' into table t_all_hero_part partition(role='cike');
load data local inpath '/home/hive-test/hero/mage.txt' into table t_all_hero_part partition(role='fashi');
load data local inpath '/home/hive-test/hero/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data local inpath '/home/hive-test/hero/tank.txt' into table t_all_hero_part partition(role='tanke');
load data local inpath '/home/hive-test/hero/warrior.txt' into table t_all_hero_part partition(role='zhanshi');

查看表数据：

外表上看起来分区表好像没多大变化，只不过多了一个分区字段。实际上分区表在底层管理数据的方式发生了改变。这里直接去。HDFS 查看区别。

查询分区表数据时，可以将分区字段看作表的伪列，可像使用其他字段一样使用分区字段。

d. 数据加载·动态分区

往 Hive 分区表中插入加载数据时，如果需要创建的分区很多，则需要复制粘贴修改很多 sql 去执行，效率低。因为 Hive 是批处理系统，所以 Hive 提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。

所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的，核心语法就是 insert + select。

启用 Hive 动态分区，需要在 Hive 会话中设置两个参数：

1. set hive.exec.dynamic.partition=true;
2. set hive.exec.dynamic.partition.mode=nonstrict;

第 1 个参数设置为 true 表示开启动态分区功能。第 2 个参数指定动态分区的模式，分为 nonstick 非严格模式和 strict 严格模式。strict 严格模式要求至少有一个分区为静态分区，nonstrict 模式下允许所有分区都是动态的。

关于严格模式、非严格模式，演示如下：

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt
-- 在这里，country 分区将由 SELECT 子句（即 pvs.cnt）的最后一列动态创建，而 dt 分区是手动指定写死的。
-- 如果是 nonstrict 模式，dt 分区也可以动态创建。

【补充】其他相关参数

-- 1. 一条insert语句可同时创建的最大的分区个数，默认为1000。
set hive.exec.max.dynamic.partitions=1000
-- 2. 单个Mapper或者Reducer可同时创建的最大的分区个数，默认为100。
set hive.exec.max.dynamic.partitions.pernode=100
-- 3. 一条insert语句可以创建的最大的文件个数，默认100000。
hive.exec.max.created.files=100000
-- 4. 当查询结果为空时且进行动态分区时，是否抛出异常，默认false。
hive.error.on.empty.partition=false

【案例】

（1）创建一张新的分区表 t_all_hero_part_dynamic

create table t_all_hero_part_dynamic
(
    id           int,
    name         string,
    hp_max       int,
    mp_max       int,
    attack_max   int,
    defense_max  int,
    attack_range string,
    role_main    string,
    role_assist  string
) partitioned by (role string)
    row format delimited fields terminated by "\t";

（2）执行动态分区插入

-- tmp.role_main 对应着 DDL 中的 partitioned by (role string)
insert into table t_all_hero_part_dynamic partition(role) 
select tmp.*, tmp.role_main from t_all_hero tmp;

动态分区插入时，分区值是根据查询返回字段位置自动推断的。

e. 多重分区表

通过建表语句中关于分区的相关语法可以发现，Hive 支持多个分区字段：PARTITIONED BY (partition1 data_type, partition2 data_type, …)。

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从 HDFS 的角度来看就是文件夹下继续划分子文件夹。

e.g. 把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是 3 分区表。

-- 单分区表，按省份分区
create table t_user_province (id int, name string,age int) partitioned by (province string);

-- 双分区表，按省份和市分区
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);

-- 三分区表，按省份、市、县分区
create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string, county string);

多分区表的数据插入和查询使用：

load data local inpath '文件路径' into table t_user_province partition(province='shanghai');
load data local inpath '文件路径' into table t_user_province_city_county partition(province='zhejiang',city='hangzhou',county='xiaoshan');

select * from t_user_province_city_county where province='zhejiang' and city='hangzhou';

f. 分区表的使用

分区的概念提供了一种将 Hive 表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。

查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件即可，避免全表数据扫描。这种指定分区查询的方式叫做「分区裁剪」。

分区表的使用重点在于：

1. 建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；
2. 查询的时候尽量先使用 WHERE 进行分区过滤，查询指定分区的数据，避免全表扫描。

e.g. 查询英雄主要定位是射手并且最大生命大于 6000 的个数。使用分区表查询和使用非分区表进行查询：

-- 非分区表: 全表扫描过滤查询
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
-- 分区表: 先基于分区过滤 再查询
select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;

注意事项：

1. 分区表不是建表的必要语法规则，是一种优化手段表，可选；
2. 分区字段不能是表中已有的字段，不能重复；
3. 分区字段是虚拟字段，其数据并不存储在底层的文件中；
4. 分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）；
5. Hive 支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度。

2.3 Hive 分桶表

a. 概述

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分，「分区」针对的是数据的存储路径，「分桶」针对的是数据文件。

分桶表的基本原理是，首先为每行数据计算一个指定字段的数据的 hash 值，然后模以一个指定的分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。

针对同一个字段既分区又分桶无意义～ 而且也不可能，因为分区字段是个虚拟列。

分桶规则如下（桶编号相同的数据会被分到同一个桶当中）：

Bucket number = hash_function(bucketing_column) % num_buckets

hash_function 取决于分桶字段 bucketing_column 的类型：

• 如果是 int 类型，hash_function(int) == int;
• 如果是其他类型，比如 bigint、string 或复杂数据类型，hash_function 比较棘手，将是从该类型派生的某个数字，比如 hashcode 值。

语法：

b. 案例

现有美国 2021-1-28 的各个县 county 的新冠疫情累计案例信息，包括确诊病例和死亡病例，数据格式如下所示：

count_date（统计日期），county（县），state（州），fips（县编码code），cases（累计确诊病例），deaths（累计死亡病例）

（1a）根据 state 州把数据分为 5 桶

CREATE TABLE t_usa_covid19_bucket
(
    count_date string,
    county     string,
    state      string,
    fips       int,
    cases      int,
    deaths     int
)
CLUSTERED BY (state) INTO 5 BUCKETS;

（1b）在创建分桶表时，还可以指定分桶内的数据排序规则

-- 根据 state 分为 5 桶 每个桶内根据 cases 确诊病例数倒序排序
CREATE TABLE t_usa_covid19_bucket_sort
(
    count_date string,
    county     string,
    state      string,
    fips       int,
    cases      int,
    deaths     int
)
CLUSTERED BY (state) sorted by (cases desc) INTO 5 BUCKETS;

（2）分桶表的数据加载

-- 1. 开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

-- 2. 把源数据加载到普通Hive表中
CREATE TABLE t_usa_covid19_tmp
(
    count_date string,
    county     string,
    state      string,
    fips       int,
    cases      int,
    deaths     int
)
row format delimited fields terminated by ",";

-- 3. 将源数据上传到 HDFS，t_usa_covid19_tmp 表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/tree.db/t_usa_covid19_tmp

-- 4. 使用 insert+select 语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19_tmp;

【补充】Hive3.x 的 load 做了增强，load 数据可以直接跑 MapReduce；老版的 load 只能移动文件，也就是说加载分桶表需要先将数据 load 到一张表里，再通过查询的方式导入到分桶表里面。

（3）到 HDFS 上查看 t_usa_covid19_bucket 底层数据结构可以发现，数据被分为了 5 个部分。并且从结果可以发现，只要 hash_function(bucketing_column) 一样的，就一定被分到同一个桶中。

c. 优点

和非分桶表相比，分桶表的使用好处有以下几点：

（1）基于分桶字段查询时，减少全表扫描

-- 基于分桶字段 state 查询来自于 New York 州的数据
-- 不再需要进行全表扫描过滤，而是根据分桶的规则 hash_function(New York) mod 5 计算出分桶编号
-- 查询指定分桶里面的数据就可以找出结果。此时是分桶扫描而不是全表扫描
select * from t_usa_covid19_bucket where state="New York";

（2）JOIN 时可以提高 MR 程序效率，减少笛卡尔积数量

对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了分桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。

（3）分桶表数据进行抽样

当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

3. Hive View

3.1 视图

Hive 中的视图（View）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。

创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，并且视图不能存储数据、操作数据，只能查询。

概况起来就是：视图是用来简化操作的，它其实是一张虚表，在视图中不缓冲记录，也没有提高查询性能。

【语法】

select * from tree.t_usa_covid19;

-- 1、创建视图
create view v_usa_covid19 as select count_date, county,state,deaths from t_usa_covid19 limit 5;

-- 也可以从已有的视图中创建视图
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;

-- 2、显示当前已有的视图 
show tables;
show views;

-- 3、视图的查询使用
select *
from v_usa_covid19;

-- 能否插入数据到视图中呢？
-- 不行！throw SemanticException:A view cannot be used as target table for LOAD or INSERT
insert into v_usa_covid19 select count_date,county,state,deaths from t_usa_covid19;

-- 4、查看视图定义
show create table v_usa_covid19;

-- 5、删除视图
drop view v_usa_covid19_from_view;

-- 6、更改视图属性
alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');

-- 7、更改视图定义
alter view v_usa_covid19 as  select county,deaths from t_usa_covid19 limit 2;

【优点】

（1）将真实表中特定的列数据提供给用户，保护数据隐私。通过视图来限制数据访问可以用来保护信息不被随意查询。

（2）降低查询的复杂度，优化查询语句。使用视图优化嵌套查询，把嵌套子查询变成一个视图，基于视图查询。

3.2 物化视图

a. 概念

在传统的数据库领域基本已经都实现了物化视图，属于数据库的高级功能。物化视图（Materialized View）是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。

使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间。

但是在 SQL On Hadoop 领域里支持这个特性的还不多，比较令人期待。Hive3.0 开始尝试引入物化视图，并提供对于物化视图的查询自动重写（基于 Apache Calcite 实现）。值得注意的是，3.0 中提供了物化视图存储选择机制，可以本地存储在 Hive，同时可以通过用户自定义 Storage Handlers 存储在其他系统（如 Druid）。

Hive 引入物化视图的目的就是为了优化数据查询访问的效率，相当于从数据预处理的角度优化数据访问。Hive 从 3.0 丢弃了 index 索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

物化视图 / 视图 · 区别：

• 视图是虚拟的，逻辑存在的，只有定义没有存储数据。
• 物化视图是真实的，物理存在的，里面存储着预计算的数据。
• 不同于视图，物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，Hive 把物化视图当成一张“表”，将数据缓存。而视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写 SQL 去访问实际的数据表。
• 视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

b. 语法

（1）语法说明

• 物化视图创建后，SELECT 查询执行数据自动落地，“自动”也即在 Query 的执行期间，任何用户对该物化视图是不可见的，执行完毕之后物化视图可用。

• 默认情况下，创建好的物化视图可被用于查询优化器 Optimizer 查询重写，在物化视图创建期间可以通过 DISABLE REWRITE 参数设置禁止使用。

• SerDe 和 Storage Format 是非强制参数，可以用户配置，默认可用 hive.materializedview.serde、 hive.materializedview.fileformat。

• 物化视图可以使用 Custom Storage Handlers 存储在外部系统（如 Druid）：

  CREATE MATERIALIZED VIEW druid_wiki_mv
  STORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'
  AS
  SELECT __time, page, user, c_added, c_removed FROM src;
  

（2）目前支持物化视图的 drop 和 show 操作，后续会增加其他操作：

-- Drops a materialized view
DROP MATERIALIZED VIEW [db_name.]materialized_view_name;
-- Shows materialized views (with optional filters)
SHOW MATERIALIZED VIEWS [IN database_name];
-- Shows information about a specific materialized view
DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;

（3）当数据源变更（新数据插入 inserted、数据修改 modified），物化视图也需要更新以保持数据一致性，目前需要用户主动触发 rebuild：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

c. 查询重写

物化视图创建后即可用于相关查询的加速，用户提交查询 Query，若该 Query 经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速。

• 是否重写查询使用物化视图可以通过全局参数控制（默认 true）： SET hive.materializedview.rewriting=true;
• 用户可选择性的失能物化视图的重写：ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

-- 1、新建一张事务表 student_trans
CREATE TABLE student_trans (
      sno int,
      sname string,
      sdept string)
clustered by (sno) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

-- 2、导入数据到 student_trans 中
insert overwrite table student_trans
select sno,sname,sdept
from student;

select * from student_trans;

-- 3、对 student_trans 建立聚合物化视图
CREATE MATERIALIZED VIEW student_trans_agg
AS
SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

-- 注意！这里当执行 CREATE MATERIALIZED VIEW，会启动一个 MR 对物化视图进行构建。
-- 通过下述命令可以发现当下的数据库中有了一个物化视图
show tables;
show materialized views;

-- 4、对原始表 student_trans 查询
-- 由于会命中物化视图，重写 Query 查询物化视图，查询速度会加快（没有启动 MR，只是普通的 table scan）
SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

-- 5、查询执行计划可以发现：查询被自动重写为 TableScan alias: tree.student_trans_agg
-- 转换成了对物化视图的查询 提高了查询效率
explain SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

4. Hive DDL 其他语法

4.1 Database

Hive 中 DATABASE 的概念和 RDBMS 中类似，我们称之为数据库。在 Hive 中， DATABASE 和 SCHEMA 是可互换的，使用 DATABASE 或 SCHEMA 都可以。

Create Database

默认的数据库叫做 default，存储数据位置位于 /user/hive/warehouse 下。用户自己创建的数据库存储位置是 /user/hive/warehouse/database_name.db 下。

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• COMMENT：数据库的注释说明语句
• LOCATION：指定数据库在 HDFS 存储位置，默认 ${hive.metastore.warehouse.dir}/dbname.db（使用 Location 指定路径的时候，最好是一个新创建的空文件夹）
• WITH DBPROPERTIES：用于指定一些数据库的属性配置

测试用例：

create database if not exists tree1101
comment "this is my first db"
with dbproperties ('createdBy'='ljq');

Show Database

SHOW DATABASES [LIKE '...'];

LIKE 通配表达式：* 表示任意个任意字符，| 表示或的关系。

Describe Database

Hive 中的 DESCRIBE DATABASE 语句用于显示 Hive 中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息。

DESCRIBE DATABASE [EXTENDED] db_name;

• EXTENDED：用于显示更多信息
• 可以将关键字 DESCRIBE 简写成 DESC 使用

Use Database

Hive 中的 USE DATABASE 语句用于选择特定的数据库，切换当前会话使用哪一个数据库进行操作。

USE database_name;

Drop Database

Hive 中的 DROP DATABASE 语句用于删除（删除）数据库。

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

默认行为是 RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用 CASCADE。

Alter Database

Hive 中的 ALTER DATABASE 语句用于更改与 Hive 中的数据库关联的元数据，其中能够修改的信息如下。

-- 更改数据库属性
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

-- 更改数据库所有者
ALTER DATABASE database_name SET OWNER [USER|ROLE] user_or_role;

-- 更改数据库位置
ALTER DATABASE database_name SET LOCATION hdfs_path;

需要注意的是：修改数据库 LOCATION，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

4.2 Table

Hive 中针对表的 DDL 操作可以说是 DDL 中的核心操作，包括建表、修改表、删除表、描述表元数据信息。其中以建表语句为核心中的核心。可以说表的定义是否成功直接影响着数据能够成功映射，进而影响是否可以顺利的使用 Hive 开展数据分析。

由于 Hive 建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建。

Show Table

SHOW TABLES [IN database_name] LIKE ['...'];

LIKE 通配表达式说明：* 表示任意个任意字符，| 表示或的关系。

Describe Table

Hive 中的 DESCRIBE table 语句用于显示 Hive 中表的元数据信息。

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name;

• 指定 EXTENDED 关键字，则它将以 Thrift 序列化形式显示表的所有元数据。
• 指定 FORMATTED 关键字，则它将以表格格式显示元数据。

Drop Table

DROP TABLE 删除该表的元数据和数据。如果已配置垃圾桶（且未指定 PURGE），则该表对应的数据实际上将移动到 .Trash/Current 目录，而元数据完全丢失。

删除 EXTERNAL 表时，该表中的数据不会从文件系统中删除，只删除元数据。

如果指定了 PURGE，则表数据不会进入 .Trash/Current 目录，跳过垃圾桶直接被删除。因此如果 DROP 失败，则无法挽回该表数据。

DROP TABLE [IF EXISTS] table_name [PURGE];    -- (Note: PURGE available in Hive 0.14.0 and later)

Truncate Table

从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果 HDFS 启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。

TRUNCATE [TABLE] table_name;

TRUNCATE 只能清空内部表（管理表），不能删除外部表中数据。

Alter Table

-- 1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;

-- 2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );

-- 3、更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");

-- 4、更改 SerDe 属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

-- 5、移除 SerDe 属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

-- 6、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在 Hive 之外进行。
ALTER TABLE table_name  SET FILEFORMAT file_format;

-- 7、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

-- 8、更改列名称/类型/位置/注释
-- a. 增加列: 增加新的列，新增列的位置位于末尾（但在分区列之前）
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
-- b. 更新列: 修改指定列的列名、数据类型、注释信息以及在表中的位置
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
-- c. 替换列: 用新的列集替换表中原有的全部列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

e.g. CREATE TABLE test_change (a int, b int, c int);
-- First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
-- Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
-- The new table's structure is:  b int, a2 string, c int.
-- Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
-- The new table's structure is:  c1 int, b int, a2 string.
-- Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

When hive.metastore.disallow.incompatible.col.type.changes is set to false, the types of columns in Metastore can be changed from any type to any other type. After such a type change, if the data can be shown correctly with the new type, the data will be displayed. Otherwise, the data will be displayed as NULL.

Create Table As Select（CTAS）

该语法允许用户利用 select 查询语句返回的结果直接建表，表的结构和查询语句的结构保持一致，且保证包含 select 查询语句放回的内容。

-- 这种方式创建的表只能是内部表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

Create Table Like

该语法允许用户复刻一张已经存在的表结构，与上述的 CTAS 语法不同，该语法创建出来的表中不包含数据。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

4.3 Partition

Hive 中针对分区 Partition 的操作主要包括：增加分区、删除分区、重命名分区、修复分区、修改分区。

可以通过 show partitions <表名> 查看表的分区信息。

Add Partition

-- 一次添加一个分区
ALTER TABLE table_name ADD PARTITION (dt='20170101') location '/user/hadoop/warehouse/table_name/dt=20170101'; 

-- 一次添加多个分区（PARTITION 之间不要加 ','）
ALTER TABLE table_name ADD 
PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';  

• 分区值仅在为字符串时才应加引号。
• 位置必须是数据文件所在的目录。
• ADD PARTITION 会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询将不会返回任何结果。

Rename Partition

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');

Delete Partition

-- 删除多个时，分区之间必须有 ','
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
-- 直接删除数据 不进垃圾桶
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE;

Msck Partition

Hive 将每个表的分区列表信息存储在其 Metastore 中。但是，如果将新分区直接添加到 HDFS（例如通过使用 hadoop fs -put 命令）或从 HDFS 中直接删除分区文件夹，则除非用户 ALTER TABLE table_name ADD/DROP PARTITION 在每个新添加的分区上运行命令，否则 Metastore（也就是 Hive）将不会意识到分区信息的这些更改。

对于这种 Hive 的元数据和 HDFS 的分区路径不一致的情况，除了上述命令，Hive 还提供了 MSCK 命令（MSCK 是 MetaStoreChecK 的缩写，表示元数据检查操作，可用于元数据的修复）。

-- 都是根据 HDFS 路径来改元数据
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

• ADD PARTITIONS 会增加 HDFS 路径存在但元数据缺失的分区信息（默认选项）；
• DROP PARTITIONS 会删除 HDFS 路径已经删除但元数据仍然存在的分区信息；
• SYNC PARTITIONS 会同步 HDFS 路径和元数据分区信息，相当于同时执行上述的两个命令。

如果存在大量未跟踪的分区，则可以批量运行 MSCK REPAIR TABLE，以避免 OOME（内存不足错误）。

Alter Partition

-- 更改分区文件存储格式
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
-- 更改分区位置
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";

5. Hive SHOW 语法

SHOW 相关的语句提供了一种查询 Hive Metastore 的方法。可以帮助用户查询相关信息。

-- 1、显示所有数据库 SCHEMAS和DATABASES的用法/功能一样
SHOW databases;
SHOW schemas;

-- 2、显示当前数据库所有表/视图/物化视图/分区/索引
SHOW tables;
SHOW TABLES [IN database_name]; -- 指定某个数据库

-- 3、显示当前数据库下所有视图
SHOW VIEWS;
SHOW VIEWS 'test_*';        -- show all views that start with "test_"
SHOW VIEWS FROM test1;  -- show views from database test1
SHOW VIEWS [IN/FROM database_name];

-- 4、显示当前数据库下所有物化视图
SHOW MATERIALIZED VIEWS [IN|FROM database_name];

-- 5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
SHOW PARTITIONS table_name;

-- 6、显示表/分区的扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name;
SHOW TABLE EXTENDED LIKE student;

-- 7、显示表的属性信息
SHOW TBLPROPERTIES table_name;
describe formatted table_name;

-- 8、显示表、视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
SHOW CREATE TABLE student;

-- 9、显示表中的所有列，包括分区列
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
SHOW COLUMNS IN student;

-- 10、显示当前支持的所有自定义和内置的函数
SHOW functions;

-- 11、describe/desc
-- 查看表信息
desc extended table_name;
-- 查看表信息（格式化美观）
desc formatted table_name;
-- 查看数据库相关信息
describe database database_name;

标签：name,19,分区,Hive,--,视图,DDL,table
From： https://www.cnblogs.com/liujiaqi1101/p/17590758.html