[ Hive ] DDL : 完整建表语法.Show语法

1 Hive DDL建表基础

1.1 完整建表语法树

1.2 Hive数据类型

1. Hive数据类型分为:原生数据类型（primitive data type）和复杂数据类型（complex data type）
2. 英文字母大小写不敏感
3. 复杂数据类型的使用通常需要和分隔符指定语法配合使用
4. 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功,不成功的话默认转为null值

原生数据类型:

复杂数据类型:

1.3 数据类型转换

原生类型从窄类型到宽类型的转换称为隐式转换[如下图]

显式类型转换使用CAST函数

1.4 读写文件机制

SerDe:Serializer.Deserializer的简称,用于序列化和反序列化,将对象和字节码互相转换

读写流程:

• Hive读取文件:

  调用InputFormat(默认TextInputFormat),返回一条一条kv–>调用SerDe(默认LazySimpleSerDe)的Deserializer,将一条记录的(忽略key)value根据分隔符切分为各个字段

• Hive写文件:

  调用SerDe的Serializer将对象(kv,key始终为常数)转换成字节序列–>调用OutputFormat将数据写入HDFS

1.5 SerDe相关语法

说明:

DELIMITED和SERDE表示使用不同的SERDE类,前者使用默认的LazySimpleSerDe类只能使用单字符分隔符来处理一般格式的数据文件,后者使用其他指定的SerDe类处理特殊格式的文件

1.6 分隔符

指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号(单字符)

默认分隔符:

1)如果建表时,没有使用row format 语法,字段之间默认分隔符是’\001’(键盘打不出来)

2)在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入’\001’ ，显示^A

3)在一些文本编辑器中将以SOH的形式显示

4)使用该方式在数据文件的字段间加上默认分隔符,解决文件映射不上表(没有使用row format)的问题

5)启发:字段以\001分隔建表,可以省去建表语句中很多关键字,因此采集,清洗数据时优先考虑使用\001分隔符

1.7 总结

• hive建表及映射流程

  1)hive中建表,注意一下字段顺序,使用row format语法指定字段间分隔符

  2)建表成功之后,Hive默认存储路径下就生成了表对应的文件夹

  3)将数据文件上传到对应的表文件夹下

  4)执行查询操作可以看出数据已映射成功

2 Hive DDL建表高阶

2.1 内外部表

	内部表(Internal table)/托管表(Managed table)	外部表(External table)
关键字	不使用,默认创建	external
Hive管理范围(生命周期)	表的结构和文件(元数据和数据)	表结构(元数据)
删除表结果	删除元数据和HDFS上文件数据	只删除元数据
操作	支持ARCHIVE,UNARCHIVE,TRUNCATE,MERGE,CONCATENATE	不支持
事务	支持	不支持
缓存	支持结果缓存	不支持

总结:

1. 通过Hive完全管理控制表的整个生命周期,使用内部表
2. 文件已存在或位于远程位置时,使用外部表,配合location关键字使用
3. location关键字和是内/外部表没有太大关联

2.2 分区表

2.2.1 建表语法

CREATE TABLE table_name (column1 data_type, column2 data_type) 
PARTITIONED BY (partition1 data_type, partition2 data_type,….);

1. 分区的目的:优化,提高查询效率,由原来的扫描全表数据改为分区下文件

2. 分区的好处:

   1)不同分区对应着不同的文件夹,同一分区的数据存储在同一个文件夹下

   2)查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件即可，避免全表数据扫描。

   3)这种指定分区查询的方式叫做分区裁剪。

3. 注意:

   1)分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上

2.2.2 静态分区数据加载

语法:

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

1)静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的

2)前提是该文件的数据符合该分区的过滤条件,本质上就是将文件分区存储,没有将数据过滤

3)映射的数据文件中不存在分区字段

2.2.3 动态分区数据加载

语法:

insert into table 分区表 partition(分区字段) select 任意字段,分区值字段 from 另一张表;

1)启用动态分区前,需要设置两个参数:

​ set hive.exec.dynamic.partition=true;

​ set hive.exec.dynamic.partition.mode=nonstrict;

2)hdfs上的映射数据文件中也不存在该分区字段

2.2.4 多重分区表

1. 多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。

2. 从HDFS的角度来看就是文件夹下继续划分子文件夹

3. 建表的时候有明显的递进逻辑关系,查询的时候呢?where过滤时and左右过滤条件的关系明显吗,两个分区字段需要考虑先后吗,例如partitioned by (province string,city string)?

   过滤时不需要考虑递进关系,将低级目录的分区字段写在前也无妨:

   select * from table_name where province=‘省’ and city=‘市’;(正确)

   select * from table_name where city=‘市’ and province=‘省’ ;(正确)

2.3 分桶表

建表语法:

CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;

1. 对分桶字段(选用表中字段)使用哈希方法后对桶数取模得到桶编号,将数据按桶编号分到对应的桶中,hdfs上表现为将数据拆分为多个桶文件

2. 分桶表是一种用于优化查询而设计的表类型

3. 分桶表的数据加载只能使用insert+select

4. 同样的,分桶表的使用好处有:

   1)基于分桶字段查询时,减少全表扫描

   2)join时减少笛卡尔积数量[下图说明]

   3)分桶表的抽样更随机,更科学

2.4 事务表

2.4.1 局限性

1. 尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的。
2. 仅支持ORC文件格式（STORED AS ORC）。
3. 默认情况下事务配置为关闭。需要配置参数开启使用。
4. 表必须是分桶表（Bucketed）才可以使用事务功能。
5. 表参数transactional必须为true；
6. 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表。

2.4.2 创建事务表

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实例上运行多少个压缩程序工作线程。

--创建Hive事务表
--这里强制分桶,即如果不手动创建分桶,会自动分为一桶
create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

2.4.3 原理

1. 对事务表的Insert插入操作能够成功的原因在于，底层是直接把数据写在一个新的文件中的。
2. 同理,update操作就是筛选出符合条件的记录,对数据做删除标记(生成删除的标记文件)后,insert新的修改后的记录
3. 而delete操作就是同update的前半部分操作一样,生成删除标记文件,从而是select无法获得被标记删除的数据

2.5 视图

1. Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。
2. 通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。
3. 创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，
4. 视图不能存储数据，操作数据，只能查询。
5. 视图是用来降低查询的复杂度,屏蔽数据的，没有提高查询性能
6. 创建语法:create view 视图名 as select…

2.6 物化视图

1. 用于预先计算并保存表连接或聚集等耗时较多的操作的结果,以提高查询性能
2. 物化视图是真实的，物理存在的，里面存储着预计算的数据。
3. 不同于视图，物化视图能够缓存数据，相当于一张表,而视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写SQL去访问实际的数据表。
4. 查询重写:用户提交查询query，若该query经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速

语法树

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
    [DISABLE REWRITE]
    [COMMENT materialized_view_comment]
    [PARTITIONED ON (col_name, ...)]
    [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
    [
    [ROW FORMAT row_format]
    [STORED AS file_format]
    | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;

用户可选择性的失能物化视图的重写：

SET hive.materializedview.rewriting=true; --是否重写查询使用物化视图
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

3 Hive DDL 其他语法

3.1 数据库DDL

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

--显示元数据信息
DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;

--删库跑路
--默认restrict(删空库)
--cascade(删完跑路)
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

--更改元数据
--更改数据库属性
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
--更改数据库所有者
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
--更改数据库位置
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

3.2 表DDL

--显示元数据信息
describe formatted|extended  [db_name.]table_name;

--删除,purge表示不经过回收站
DROP TABLE [IF EXISTS] table_name [PURGE];

--删除所有行,经过hdfs回收站
TRUNCATE [TABLE] table_name;

--更改元数据
--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;
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、添加/替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

3.3 分区DDL

--1、增加分区
--一次添加一个分区
ALTER TABLE table_name ADD PARTITION (dt='20170101') location
    '/user/hadoop/warehouse/table_name/dt=20170101'; 
--一次添加多个分区
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';
                       
--2、重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

--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、修复分区,将文件上传到hdfs的表目录的自建分区目录下,再修复,才能有分区元数据
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

--5、修改分区
--更改分区文件存储格式
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";

4 Hive Show 显示语法

--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;
show tblproperties student;

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