Hive---DDL_hive ddl

1.hive数据类型

hive的数据类型分为两种：
1.原生数据类型：数值类型、时间类型、字符串类型、杂项数据类型。
2.复杂数据类型：array数组、map映射、struct结构、union联合体。

1.1 原生数据类型

1.2 复杂数据类型

1.3 数据类型转换

HQL支持隐式和显示转换，原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
下表描述了类型之间允许的隐式转换：

2.Hive读写机制

2.1 SerDe

SerDe是Serializer、DeSerializer的简称，目的是用于序列化和反序列化。序列化是将对象转化为字节码的过程，序列化是将字节码转化为对象的过程。

2.2 hive读取文件机制

首先调用InputFileFormat(在hdfs中默认为TextInputFormat按行读取)，返回一条条kv键值对记录，然后调用SerDe的Deserializer将字节序列转换为对象，然后将一条记录中的value根据分隔符切分为各个字段。

2.3 hive写文件机制

将行写入文件时，首先调用serde的Serializer将对象转换为字节序列，然后调用OutputFileFormat将数据写入HDFS文件。

2.4 SerDe语法

LazySimpleSerDe是Hive默认的序列化类，包含四种子语法，分别用于指定字段之间、集合元素之间、Map映射kv之间、换行之间的分隔符符号。如

row format delimited fileds terminated by ',';
1

字段之间默认的分隔符是’\001’,是一种特殊的字符，使用的是ascii编码的值，键盘无法打出。

3.Hive存储路径

Hive表默认存储路径默认值为/user/hive/warehouse

3.1 指定存储路径

在hive建表的时候，可以通过location语法来更改数据在HDFS上的路径，使得建表加载数据更加灵活。

4.Hive建表高阶

4.1 hive内部表、外部表

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

外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。而且外部表更为方便的是可以搭配location语法指定数据的路径。
外部表创建：使用external进行修饰

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

1
2
3
4
5
6
7
8
9
10

4.2 分区表

现有一张表t_all_hero，同时映射加载了六份数据文件(分别记录了王者荣耀中六种位置的英雄的相关信息)。
如果不对表进行分区，那么在执行查询的时候，会对全表进行检索才能得出相应的结果，如果数据文件很多的话，则会大大降低查询效率，此时可以通过对表进行分区，避免查询时候全表扫描数据，极大的提升了查询效率。此外，hive支持根据用户指定的字段进行分区。

分区建表语法：

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

4.2.1 静态分区

静态分区：指的是分区的字段值是由用户在加载数据的时候手动指定的。
语法如下：

load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);
eg:
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');
1
2
3
4

4.2.2 动态分区

向分区表中加载数据时，如果需要创建的分区很多，则会复制修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，即根据查询参数的位置去推断分区的名称，从而建立分区
动态分区：分区的字段值是根据查询结果自动推断出来的。
启动hive分区之前，需要在hive会话中设置两个参数：

//开启动态分区功能
set hive.exec.dynamic.partition=true;
//指定动态分区的模式，nonstrict为非严格模式和strict严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
1
2
3
4

严格模式要求至少有一个分区为静态分区

案例：

//创建一张新的分区表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";
//执行动态分区插入,tmp.*表示插入的总数据，tmp.role_main表示分区值，即查询参数的位置
insert into table t_all_hero_part_dynamic partition(role) select tmp.*,tmp.role_main from t_all_hero tmp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意：tmp.*和tmp.role_main位置不可颠倒

4.2.3 分区表的注意事项

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

4.2.4 多重分区

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。

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

load data local inpath '文件路径' into table t_user_province_city
partition(province='zhejiang',city='hangzhou');
1
2
3
4
5
6
7
8

4.3 分桶表

分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。
在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分）。默认规则是：Bucket number = hash_function(bucketing_column) mod num_buckets。
可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column的类型：
如果是int类型，hash_function(int) == int;
如果是其他类型，比如bigint,string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值。

4.3.1 分桶表的语法

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

其中CLUSTERED BY (col_name)表示根据哪个字段进行分；INTO N BUCKETS表示分为几桶（也就是几个部分)。需要注意的是，分桶的字段必须是表中已经存在的字段。

4.3.2 分桶表的数据加载

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

//把源数据加载到普通hive表中
CREATE TABLE itcast.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";
//将源数据上传到HDFS，t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itcast.db/t_usa_covid19

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

分桶表将数据分为了五个部分：

4.3.3 分桶表的好处

1、基于分桶字段查询时，减少全表扫描
2、JOIN时可以提高MR程序效率，减少笛卡尔积数量
3、分桶表数据进行抽样

4.4 事务表

hive中通过事务解决以下问题：
1.流式传输数据
使用如Apache Flume或Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据，但是Hive只能每隔15分钟到一个小时添加一次分区。频繁添加分区会很快导致表中大量的分区。因此通常使用这些工具将数据流式传输到现有分区中，但是这会使读者感到脏读（也就是说，他们将在开始查询后看到写入的数据），并将许多小文件留在目录中，这将给NameNode带来压力。通过事务功能，同时允许读者获得一致的数据视图并避免过多的文件。
2.尺寸变化缓慢(例如：表结构变化)
在典型的星型模式数据仓库中，维度表随时间缓慢变化。例如，零售商将开设新商店，需要将其添加到商店表中，或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致插入单个记录或更新 记录（取决于所选策略）。
3.数据重述
有时发现收集的数据不正确，需要更正。从Hive 0.14开始，可以通过INSERT，UPDATE和 DELETE支持这些用例 。

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

4.4.1 操作事务表

--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 (id, name, age)
values (1,"allen",18);

update trans_student
set age = 20
where id = 1;

delete from trans_student where id =1;

//查询事务表
select *
from trans_student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

4.5 Hive View

Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。
创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，并且视图不能存储数据，操作数据，只能查询。

4.5.1 操作视图

--hive中有一张真实的基础表t_usa_covid19
select *
from itcast.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;--hive v2.2.0之后支持

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

--能否插入数据到视图中呢？
--不行 报错  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
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

4.5.2 视图的优点

1.提高数据的安全性，只将特定列的数据展示给用户
2.降低查询的复杂度，优化查询语句

4.6 物化视图

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

4.6.1 物化视图和视图的区别

视图是虚拟的，逻辑存在的，只有定义没有存储数据。
物化视图是真实的，物理存在的，里面存储着预计算的数据。
视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

4.6.2 语法

--物化视图的创建语法
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 ...;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.6.3 基于物化视图的查询重写

物化视图创建后即可用于相关查询的加速，用户提交查询query，若该query经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速。
是否重写查询使用物化视图可以通过全局参数控制，默认为true：

SET hive.materializedview.rewriting=true;
1

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

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;
1