Hive SQL DDL建表高阶语法

标签：建表 string 分区 Hive 分区表 role SQL table

Hive 内部表、外部表

什么是内部表

 

• 内部表（Internal table）也称为被Hive拥有和管理的托管表（Managed table）。

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

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

• 可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息，从中可以看出表的类型。

 

什么是外部表

• 外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。

• 要创建一个外部表，需要使用EXTERNAL语法关键字。

• 删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。

• 实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全。

 

• 可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息，从中可以看出表的类型。

 

内、外部表差异

 

• 无论内部表还是外部表，Hive都在Hive Metastore中管理表定义、字段类型等元数据信息。

• 删除内部表时，除了会从Metastore中删除表元数据，还会从HDFS中删除其所有数据文件。

• 删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

• 针对创建好的内部表student、外部表student_ext,分别上传结构化数据文件students.txt到对应的表路径下；

• 确定数据被Hive解析加载成功。

 

• 执行drop table tablename 命令，分别在Hive中和HDFS中查看效果。

• Hive中的表信息全被删除，不管是内部表还是外部表。

• 而HDFS上，外部表对应的数据文件原封不动，内部表对应的数据文件连同文件夹一起被删除。

 

 

 

 

 

 

 

Location关键字的作用

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

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

• 创建内部表的时候，是否可以使用location指定？

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

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

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

 

Hive Partitioned Tables 分区表

 

分区表是hive支持根据指定的字段对表进行分区，分区的字段不限制

 

-- 指定语法
PARTITIONED BY()

 

创建

• 分区表语法

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

 

 

-- 例子
create table t_tall_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 
)
partition by(role string)   --对表让role进行分区
row format delimited  -- 行分隔符 默认没有
fields terminated by '\t'

 

 

 

 

 

 

 

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

静态分区就是分区的属性值是由用户在加载数据的时候手动指定的

load data[load] inpath 'filepath' into tablename partition（分区字段='分区值'.....）;

Local参数用于指定待加载的数据是位于本地文件系统还是HDFS文件系统

 

 

 

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进行查看

在底层已经把大文件根据role=？进行分类，如果下次进行查找直接后面跟上role=？进行查找，而不是把所有的数据进行走一遍，大大优化进程速度。其实本质上分区就是一种优化的方式 (单文件细分小文件)

 

• 分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。

• 不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下

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

• 这种指定分区查询的方式叫做分区裁剪

 

使用

• 分区表重点使用在

  • 建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；

  • 查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描

同理前者走的是一个大循环，后者是根据需要进行寻找

 

多重分区表

• 通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：

PARTITIONED BY (partition1 data_type, partition2 data_type,….)。

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

• 从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。

-- 后面的partitioned by 不能随便写，其中是有对应关系的
​
--单分区表，按省份分区
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';
​

 

 

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

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

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

-- 是否开启动态分区功能
set hive.exec.dynamic.partition=true;
-- 指定动态分区模式，分为nonstick非严格模式和strict严格模式。•#strict严格模式要求至少有一个分区为静态分区。
set hive.exec.dynamic.partition.mode=nonstrict;

 

 

-- 例子
--创建一张新的分区表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";
​
-- 执行动态分区插入
insert into table t_all_hero_part_dynamic partition(role) select tmp*,tmp.role_main from t_all_hero

 

注意事项

一、分区表不是建表的必要语法规则，是一种优化手段表，可选；

二、分区字段不能是表中已有的字段，不能重复；

三、分区字段是虚拟字段，其数据并不存储在底层的文件中；

四、分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）

五、Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

标签：建表,string,分区,Hive,分区表,role,SQL,table
From： https://www.cnblogs.com/catch-autumn/p/16811101.html