Hive 建表

内部表与外部表

内部表（默认）

内部表（MANAGED_TABLE)：表目录按照 Hive 的规范来部署

• 默认情况下，Hive 创建内部表，其中的表数据、元数据和统计数据由内部 Hive 进程管理
• 内部表数据存储在 Hive 仓库的 /user/hive/warehouse/database_name/table_name 目录下（HDFS）
• 内部表使用 location 属性可以覆盖默认位置
• 如果删除一个内部表或分区，则会删除与该表或分区关联的数据和元数据
• 当 Hive 应该管理表的生命周期，或者在生成临时表时，使用内部表

外部表

外部表（EXTERNAL_TABLE)：表目录由建表用户自己指定

• 外部表描述外部文件的元数据/模式
• 外部表可以访问存储在源中的数据
• 如果外部表的结构或分区被更改，可以使用 MSCK REPAIR table table_name 语句刷新元数据信息
• 删除外部表，只会删除元数据信息，数据不会删除
• 一般源数据使用外部表

内部表和外部表转换

• 查看表的描述信息

desc extended t_external; # 查看表的扩展信息
desc formatted t_external; #  格式化表的扩展信息

• 内部表转外部表

ALTER TABLE managed_table_name SET TBLPROPERTIES ('EXTERNAL'='TRUE');

• 外部表转内部表

ALTER TABLE external_table_name SET TBLPROPERTIES ('EXTERNAL'='FALSE');

注：一个 Hive 数据仓库的数据表一定来源于外部系统，所以在生产中一般使用外部表来映射表数据

何时用外部表或内部表

（一般情况下，在企业内部都是使用外部表的。因为会有多人操作数据仓库，可能会产生数据表误删除操作，为了数据安全性，通常会使用外部表。

当个人写测试代码的时候，可以使用内部表，这样当自己使用完成后，可以方便直接删除

）

• 每天采集的ng日志和埋点日志,在存储的时候建议使用外部表，因为日志数据是采集程序实时采集进来的，一旦被误删，恢复起来非常麻烦。而且外部表方便数据的共享

• 抽取过来的业务数据，其实用外部表或者内部表问题都不大，就算被误删，恢复起来也是很快的，如果需要对数据内容和元数据进行紧凑的管理, 那还是建议使用内部表

• 在做统计分析时候用到的中间表，结果表可以使用内部表，因为这些数据不需要共享，使用内部表更为合适。并且很多时候结果分区表我们只需要保留最近3天的数据，用外部表的时候删除分区时无法删除数据

分区表

分区原理

分区表的实质是在表目录中为数据文件创建分区子目录，以便在查询时，MR 程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。（类似 MySQL 表分区）

Hive 处理的是离线数据（历史数据），离线数据一般是按照天或者周为单位的数据。如果每天的数据都放在一个表中，那么处理其中一天的数据的时候会加载所有的数据，这样严重影响性能，此时可以使用分区表。

在 Hive 中数据库是一个目录；Hive 的表也是一个目录；Hive的分区表是表目录的子目录（目录名即分区字段）

分区表目录格式：分区字段=值

• 创建表分区

• 分区字段不能是表中已存在的字段，否则会出现字段冲突

• 外部表也可以创建分区表

• 加载表分区数据

• 分区表数据查询

注：分区字段可用于查看分区表中数据

删除分区

语法结构：

ALTER TABLE table_name DROP [IF EXISTS]
PARTITION partition_spec[, PARTITION
partition_spec, ...];
partition_spec:
: (partition_column =
partition_col_value, partition_column =
partition_col_value, ...)

创建多级分区

注：一般分区也就是 2-3 级

动态分区

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

动态分区相关的调优参数：

#每个maper或reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。
set hive.exec.max.dynamic.partitions.pernode=100 
#一个动态分区语句可以创建的最大动态分区个数，超出报错
set hive.exec.max.dynamic.partitions =1000(默认值) 
#全局可以创建的最大文件个数，超出报错。
set hive.exec.max.created.files =10000(默认) 

CATS 建表

• 通过已存在的表来建表

新建的 t_user_2 表结构定义与源表 t_user 一致，但是没有数据

• 建表的同时插入数据

t_access_user 会根据查询的字段来建表，同时将查询的结果插入新表中

修改表

仅修改Hive元数据，不会触动表中的数据，用户需要确定实际的数据布局符合元数据的定义。

• 修改表名

• 修改分区名

• 添加分区

• 删除分区

• 修改表的文件格式定义

• 修改列名定义

• 增加/替换列

分桶表

分桶原理

分桶就是按照用户创建表时指定的分桶字段进行 hash 散列多个文件（与 MapReduce 中的 HashPartitioner 分区原理相同）

分桶比分区

分桶操作

• 开启分桶

  set hive.enforce.bucketing=true;
  

• 创建分桶表

  create table t_bucket(
  id int,
  name string,
  score int
  )
  clustered by(id) into 3 buckets
  row format delimited fields terminated by ',';
  

• 使用 insert 插入数据

  insert into table t_bucket select id,name,money from t_user;
  

分桶好处

• 方便抽样

  在处理大规模数据集时，在开发和修改查询的阶段，可以使用整个数据集的一部分进行抽样测试查询、修改，可以使得开发更高效

• map-side join

  获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN 操作就可以，可以大大较少 JOIN 的数据量。