Hive内、外部表

什么是内部表？

内部表(Internal table)也称为被Hive拥有和管理的托管表(Managed table)。
默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。
当您删除内部表时，它会删除数据以及表的元数据。
可以使用DESCRIBE FORMATTED tablename，来获取表的元数据描述信息，从中可以看出表的类型。

什么是外部表？

外部表(External table)中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。
要创建一个外部表，需要使用EXTERNAL语法关键字。
删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。
实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全。

内、外部表差异

无论内部表还是外部表，Hive都在Hive Metastore中管理表定义、字段类型等元数据信息。
删除内部表时，除了会从Metastgre中删除表元数据，还会从HDFS中删除其所有数据文件。
删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

如何选择内、外部表

Stored关键字的作用

详细讲解请参考后续内容[文件存储格式（TEXTFILE、ORC 、PARQUET)]

Location关键字的作用

Hive分区表

概念

当Hive表对应的数据量大、文件个数多时，为了避免查询时全表扫描数据，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。
比如把一整年的数据根据月份划分12个月(12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

创建

-- 分区表语法
create table partition_table(
    column1 data_type comment '注释',
    column2 date_type comment '注释',
...)
partitioned by (partition1 data_type,partition2 data_type,...);

注意:分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。
示例：

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

再创建一个t_all_hero表，不带分区，以作比对，上传文件：

[root@node1 hivedata]# hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/db_test.db/t_all_hero

分区表数据加载–静态分区

load data [local] inpath 'filepath' into table tablename partition(分区字段='分区值'...);

示例：

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

本质

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

非分区表：t_all_hero

分区表：t_all_hero_part

分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。
不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。
查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件即可，避免全表数据扫描。
这种指定分区查询的方式叫做分区裁剪。

-- 非分区表  全表扫描过滤查询
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;

多重分区表

分区表数据加载–动态分区

-- 是否开启动态分区功能
set hive.exec.dynamic.partition=true;
-- 指定动态分区模式，分为nonstrict非严格模式和strict严格模式
-- strict严格模式要求至少有一个分区为静态分区，以防用户意外覆盖所有分区；
-- nonstrict模式下，允许所有分区都是动态的
set hive.exec.dynamic.partition.mode=nonstrict;

示例：

-- 是否开启动态分区功能
set hive.exec.dynamic.partition=true;
-- 指定动态分区模式，分为nonstrict非严格模式和strict严格模式
-- strict严格模式要求至少有一个分区为静态分区
set hive.exec.dynamic.partition.mode=nonstrict;
-- 创建一张新的分区表
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";
-- 执行动态分区插入
insert into table t_all_hero_part_dynamic partition(role)   -- 分区值并未手动写死
select t.*,t.role_main from t_all_hero t;	-- 分区字段放到查询最末尾

SQL查看：

HDFS查看：

分区表的注意事项

Hive分桶表

概念

分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。
分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。
在分桶时，要指定根据哪个字段将数据分为几桶(几个部分)。

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


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

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

语法

-- 分桶表建表语句
create table [external] table [db_name.]table_name
[(col_name data_type,...)]
clustered by (col_name) sorted by (col_name asc/desc) into N buckets;

创建、数据加载

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

-- step2：把数据源加载到普通Hive表中
drop table if exists t_usa_covid19;
create table db_test.t_usa_covid19(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int
)row format delimited fields terminated by ",";

-- step3：将源数据上传到HDFS，t_usa_covid19表对应的路径下
[root@node1 hivedata]# hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/db_test.db/t_usa_covid19

-- step4：创建分桶表，根据state分为5桶，每个桶内根据cases倒序排序
create table db_test.t_usa_covid19_bucket(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int
)clustered by (state) sorted by (cases desc) into 5 buckets;

-- step5：使用inset+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

HDFS查看：

到HDFS上查看t_usa_covid19_bucket底层数据结构可以发现，数据被分为了5个部分。
并且从结果可以发现，分桶字段一样的数据就─定被分到同一个桶中。

使用好处

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

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

根据join的字段对表进行分桶操作（比如下图中id是join的字段)


分桶表数据进行高效抽样

Hive事务表

Hive事务背景

流式传输数据

尺寸变化缓慢

数据重述

局限性

实现原理

Hive的文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改，只能是采取另外的手段来完成。
用HDFS文件作为原始数据（基础数据），用delta保存事务操作的记录增量数据;
正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹。每次执行一次事务操作都会有这样的一个delta增量文件夹;
当访问Hive数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据。
INSERT语句会直接创建delta目录；
DELETE目录的前缀是delete_delta；
UPDATE语句采用了split-update特性，即先删除、后插入；

实现原理之delta文件夹命名格式

delta_minWID_maxWID_stmtID，即delta前缀、写事务的ID范围、以及语句ID;删除时前缀是delete_delta，里面包含了要删除的文件；
Hive会为写事务（INSERT、DELETE等)创建一个写事务ID (Write ID)，该ID在表范围内唯一；
语句ID (Statement ID )则是当一个事务中有多条写入语句时使用的，用作唯一标识。


每个事务的delta文件夹下，都有两个文件：

1、_orc_acid_version的内容是2，即当前ACID版本号是2。和版本1的主要区别是UPDATE语句采用了split-update特性，即先删除、后插入。这个文件不是ORC文件，可以下载下来直接查看。


2、bucket_00000文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以ORC格式存储，底层二级制，需要使用ORC TOOLS查看。


operation：0表示插入，1表示更新，2表示删除。由于使用了split-update，UPDATE是不会出现的，所以delta文件中的operation是0 , delete_delta文件中的operation是2。

originalTransaction、currentTransaction：该条记录的原始写事务ID，当前的写事务ID。

rowld：一个自增的唯一ID，在写事务和分桶的组合中唯一。

row：具体数据。对于DELETE语句，则为null，对于INSERT就是插入的数据，对于UPDATE就是更新后的数据。

合并器（Compactor）

随着表的修改操作，创建了越来越多的delta增量文件，就需要合并以保持足够的性能。

合并器Compactor是一套在Hive Metastore内运行，支持ACID系统的后台进程。所有合并都是在后台完成的，不会阻止数据的并发读、写。合并后，系统将等待所有旧文件的读操作完成后，删除旧文件。

合并操作分为两种，minor compaction (小合并)、major compaction (大合并）：
小合并会将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta文件;
大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为delta文件相应于基础文件占比，10%。

事务表创建使用

-- Hive中事务表的创建使用
-- 1、开启事务配置（可以使用set设置当前session生效，也可以配置在hive-site.xml中）
set hive.support.concurrency = true;    -- Hive是否支持并发
set hive.enforce.bucketing = true;  -- 从Hive2.0开始不再需要，是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict;   -- 动态分区模式，非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;  -- 事务器，指定默认管理类
set hive.compactor.initiator.on = true; -- 是否在metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1;  -- 在此metastore实例上运行多少个压缩程序工作线程

-- 2、创建Hive事务表
create table trans_student(
    id int,
    name string,
    age int
)clustered by (id) into 2 buckets	-- 此处创建分桶，也可以不需要
stored as orc
tblproperties ('transactional'='true');

-- 3、针对事务表进行insert、update、delete操作
insert into trans_student values (1,'Aiw',20);
update trans_student set age = 22 where id = 1;
delete from trans_student where id =1;

select * from trans_student;

HDFS查看：

可以看到不管是update还是delete，hive底层并未直接修改数据，而是对文件重新写了一份数据，之前文件做标记删除。

Hive视图

Hive中的视图( view )是一种虚拟表，只保存定义，不实际存储数据。
通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。
创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败。
视图是用来简化操作的，不缓冲记录，也没有提高查询性能。

视图创建使用：

-- hive视图相关语法
-- 1.1、创建视图（指定查询字段）
create view v_usa_covid as
    select count_date,county,state,deaths from t_usa_covid19 limit 5;

-- 1.2、创建视图（指定全部字段）
create view v_usa_covid_all as
    select * from t_usa_covid19 limit 5;

-- 2、显示当前已有的视图
show tables;    -- 会显示table+view
show views;     -- 只显示view

-- 3、视图使用
select * from v_usa_covid;

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

-- 5、查看视图字段信息
desc v_usa_covid;

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

-- 6、更改视图属性
alter view v_usa_covid set tblproperties ('comment'='这是个视图');
desc formatted v_student_tmp;	-- 查看

-- 7、更改视图定义
alter view v_usa_covid as select count_date,county,state from t_usa_covid19;

使用好处

将真实表中特定的列数据提供给用户，保护数据隐式
降低查询的复杂度，优化查询语句

Hive物化视图（Hive 3.0新特性）

概念与语法

物化视图(Materialized View )是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间。
Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写机制（基于Apache Calcite实现)。
Hive的物化视图还提供了物化视图存储选择机制，可以本地存储在Hive，也可以通过用户自定义storage handlers存储在其他系统(如Druid)。
Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问。
Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

物化视图、视图区别

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

语法

(1)物化视图创建后，select查询执行数据自动落地，“自动”也即在query的执行期间，任何用户对该物化视图是不可见的,执行完毕之后物化视图可用;

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


(3)默认SerDe和storage format为hive.materializedview.serde、hive.materializedview.fileformat;

(4)物化视图支持将数据存储在外部系统(如druid)，如下述语法所示:


(5)目前支持物化视图的drop和show操作，后续会增加其他操作


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

查询重写加速机制

物化视图创建后即可用于相关查询的加速，即︰用户提交查询query，若该query经过重写后可以命中已经存在的物化视图，则直接通过物化视图查询数据返回结果，以实现查询加速。
是否重写查询使用物化视图可以通过全局参数控制，默认为true: hive.materializedview.rewriting=true;
用户可选择性的控制指定的物化视图查询重写机制，语法如下∶


示例：

-- 1、开启事务配置（可以使用set设置当前session生效，也可以配置在hive-site.xml中）
set hive.support.concurrency = true;    -- Hive是否支持并发
set hive.enforce.bucketing = true;  -- 从Hive2.0开始不再需要，是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict;   -- 动态分区模式，非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;  -- 事务器，指定默认管理类
set hive.compactor.initiator.on = true; -- 是否在metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1;  -- 在此metastore实例上运行多少个压缩程序工作线程

-- 2、准备基表（只有分桶表才能使用事务）
create table student_materialized(
    sno int,
    sname string,
    sdept string
) clustered by (sno) into 2 buckets stored as orc tblproperties ('transactional'='true');

-- 3、数据导入（插入覆盖）
insert overwrite table student_materialized
select num,name,dept from student;

select * from student_materialized;

-- 4、创建物化视图（基表必须是事务表，不然会执行报错）
create materialized view v_student_materialized as
    select sdept,count(*) as cnt from student_materialized group by sdept;

-- 5、查看物化视图
show materialized views;

-- 6、对基表进行查询，由于会命中物化视图，查询速度会加快（没有启动MR，只是普通的table扫描）
select sdept,count(*) as cnt from student_materialized group by sdept;

-- 7、查询执行计划可以发现：查询被自动重写为TableScan alias: db_test.v_student_materialized，转换成了对物化视图查询，提高了查询效率
explain select sdept,count(*) as cnt from student_materialized group by sdept;

-- 8、禁用物化视图自动重写
alter materialized view v_student_materialized disable rewrite;

-- 9、删除物化视图
drop materialized view v_student_materialized;

-- 10、手动更新物化视图
alter materialized view v_student_materialized rebuild;

HDFS查看（可以看到物化视图是真实存在的表）：

数据库DDL操作

整体概述

在Hive中，DATABASE的概念和RDBMS中类似，我们称之为数据库，DATABASE和SCHEMA是可以互换的，都可以使用。
默认的数据库叫做default，存储数据位置位于/user/hive/warehouse下。
用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下。
创建数据库

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

COMMENT∶数据库的注释说明语句
LOCATION∶指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db
WITH DBPROPERTIES∶用于指定一些数据库的属性配置。

显示数据库元信息

(DESCRIBE|DESC) (DATABASE|SCHEMA) [EXTENDED] db_name;

显示Hive中数据库的名称，注释（如果已设置)及其在文件系统上的位置等信息。
EXTENDED关键字用于显示更多信息。可以将关键字describe简写成desc使用。

切换数据库

use database_name;

切换当前会话使用哪一个数据库进行操作

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

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

修改数据库元信息

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

-- 更改数据库所有者
ALTER (DATABASE|SCHEMA) database_name SET OWNER USER user_name;

-- 更改数据库位置
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

表DDL操作

Hive中针对表的DDL操作可以说是DDL中的核心操作，包括建表、修改表、删除表、描述表元数据信息。
可以说表的定义是否成功直接影响着数据能够成功映射，进而影响是否可以顺利的使用Hive开展数据分析。
由于Hive建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建。

显示表元信息

(DESCRIBE|DESC) [EXTENDED | FORMATTED] table_name;

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

删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

如果已配置垃圾桶且未指定PURGE，则该表对应的数据实际上将移动到HDFS垃圾桶，而元数据完全丢失。删除外部（EXTERNAL）表时，该表中的数据不会从文件系统中删除，只删除元数据。
如果指定了PURGE，则表数据跳过HDFS垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。

清空表

TRUNCATE [TABLE] table_name;

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

修改表

-- 1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
-- 2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES(property_name = property_value,...);
-- 更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment'= "new comment for student table");
-- 3、更改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'=',');
-- 移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES(property_name,...);
-- 4、更改表的文件存储格式该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进
ALTER TABLE table_name SET FILEFORMAT file_format;
-- 5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";
-- 6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
-- 更改列名称
ALTER TABLE test_change CHANGE a a1 INT;
-- 更改列类型，并放到b列后面
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
-- 更改列位置，放到最前面
ALTER TABLE test_change CHANGE c c1 INT FIRST;
-- 更改列注释
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
-- 7、添加/替换列
-- 使用ADD COLUMNS，可以将新列添加到现有列的末尾但在分区列之前。
-- REPLACE COLUMNS将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS(col_name data_type [comment '..'],...);

分区DDL操作

增加分区

-- 1、增加分区（一次添加一个分区）
ALTER TABLE table_name ADD PARTITION(dt='20170101') location '/user/hadooplwarehouse/table_nameldt=20170101';
-- 一次添加多个分区
ALTER TABLE table_name ADD PARTITION(dt='2008-08-08', country='us') location '/path/tolus.
part080808'
PARTITION(dt='2008-08-09', country='us') location 'path/tolus/part080809';

ADD PARTITION会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询时将不会返回结果。
因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区之后导入分区数据。

重命名分区

-- 2、重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
ALTER TABLE table_name PARTITION(dt='2008-08-O9') RENAME TO PARTITION(dt='20080809');

删除分区

-- 3、删除分区
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; -- 直接删除数据不进垃圾桶

删除表的分区。这将删除该分区的数据和元数据。

修改分区

-- 4、修改分区
-- 更改分区文件存储格式
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、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

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

MSCK是metastore check的缩写，表示元数据检查操作，可用于元数据的修复。

MSCK默认行为ADD PARTITIONS，使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到metastore。

DROP PARTITIONS选项将从已经从HDFS中删除的metastore中删除分区信息。

SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS。

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

示例：

-- 分区修复（add partitions），实际用途：假设分区比较多，将数据按照如下方式写到HDFS上面，执行修复命令，比动态/静态加载分区要快多了
-- 1、创建分区表
create table t_all_hero_part_msck(
    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、在linux上，使用HDFS命令创建分区文件
[root@node1 ~]# hadoop fs -mkdir -p /user/hive/warehouse/db_test.db/t_all_hero_part_msck/role=sheshou
[root@node1 ~]# hadoop fs -mkdir -p /user/hive/warehouse/db_test.db/t_all_hero_part_msck/role=tanke

-- 3、将数据文件上传到对应的分区文件夹下
[root@node1 hivedata]# hadoop fs -put archer.txt /user/hive/warehouse/db_test.db/t_all_hero_part_msck/role=sheshou
[root@node1 hivedata]# hadoop fs -put tank.txt /user/hive/warehouse/db_test.db/t_all_hero_part_msck/role=tanke

-- 4、查询表，发现没有数据
select * from t_all_hero_part_msck;

-- 5、使用msck命令进行修复（add partitions可以不写，默认就是增加分区）
msck repair table t_all_hero_part_msck add partitions;

-- 6、再次查询表，发现有数据
select * from t_all_hero_part_msck;

-- 分区修复（drop partitions）
-- 1、直接使用HDFS命令删除分区表的某一个分区文件夹
[root@node1 ~]# hadoop fs -rm -r /user/hive/warehouse/db_test.db/t_all_hero_part_msck/role=sheshou

-- 2、查询发现还有分区信息，因为元数据信息没有删除
show partitions t_all_hero_part_msck;

-- 3、使用msck命令进行修复
msck repair table t_all_hero_part_msck drop partitions;

-- 4、再次查询发现分区信息被删除
show partitions t_all_hero_part_msck;

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;

-- 7、显示表的属性信息
show tblproperties table_name;

-- 8、显示表、视图的创建语句
show create table([db_name.]table_name|view_name);

-- 9、显示表中的所有列，包括分区列。
show columns (from|in) table_name [(from|in) db_name];

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

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

-- 查看当前数据库
select current_database();

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/qq_45917176/article/details/143082051

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

《数据资产管理白皮书》下载地址：https://www.dtstack.com/resources/1073/?src=bbs

《行业指标体系白皮书》下载地址：https://www.dtstack.com/resources/1057/?src=bbs
《数据治理行业实践白皮书》下载地址：https://www.dtstack.com/resources/1001/?src=bbs
《数栈V6.0产品白皮书》下载地址：https://www.dtstack.com/resources/1004/?src=bbs

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs

同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack