Hive-内部表&外部表&分区表&扩展命令

先看下如何创建数据表

create [external] table if not exists 表名

(列名数据类型 [comment 本列注释],...)

[comment 表注释]

[partitioned by (列名数据类型 [comment 本列注释],...)]

[clustered by(列名,列名,...)]

[sorted by (列名 [asc|desc],...)] info num_buckets buckets]

[row format row_format][stored as file_format]

[location hdfs_path]

[tblproperties (property_name=property_value,...)]

[as select_statement]

说明：

①external表示创建外部表；hive在创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变

②partitioned by表示创建分区表

③clustered by创建分桶表

④sorted by 不常用

⑤row format delimited [fields terminated by char] [collection items terminated by char] [map keys terminated by char] [line terminated by char]

⑥stored as 指定文件存储类型(sequencefile二进制文件、textfile文本文件、rcfile列式存储格式)

⑦location 指定表在hdfs上的存储位置

⑧like 允许用户复制现有的表结构，但是不复制数据

⑨as 后跟查询语句，根据查询结果创建表

查看数据表结构：

desc formated 表名；  //可以查看分区字段(partition)信息
desc 表名；

external：表示创建外部表，仅记录数据所在路径，不对数据做改变

不写external：表示创建内部表，会将数据移动到数据仓库指向的路径。

内部表&外部表区别：

• 内部表也叫管理表，数据由Hive自身管理，外部表数据由HDFS管理
• 内部表数据存储的位置是hive.metastore.warehouse.dir（数据仓库地址默认：/user/hive/warehouse）
• 外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）
• 删除内部表会直接删除元数据（metadata）及存储数据
• 删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除
• 对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）

使用场合：

1、希望做数据备份并且不经常改变的数据，存放在外部表可以减少失误操作。

2、数据清洗转换后的中间结果，可以存放在内部表，因为Hive对内部表支持的功能比较全面，方便管理。

3、处理完成的数据由于需要共享，可以存储在外部表，这样能够防止失误操作，增加数据的安全性。

4、在生产中一般创建外部表来存储数据。

修改表

修改表名

alter table A_A rename TO AA;

向表中添加列

ALTER TABLE A_A ADD COLUMNS (
    　　　　　　　　　　　　　　　a_name   STRING  COMMENT  ' App  name ' ,
　　　　　　　　　　　　　　　　　a_id  BIGINT  COMMENT  ' The  current  session  id');

修改列名

ALTER TABLE 表名 change 原列名 新列名 新类型;

(慎用)删表：

drop table if exists table_name;

(慎用)清空表：

truncate table 表名;

 扩展：

在Hive中查看函数功能（比如substr）

desc function extended substr;

 查看所有Hive函数

show functions;

杀死进程 -比如有一个进程为 ：11245 RunJar

kill -9 11245;

---

分区表

分区表：在一定程度上可以理解为分成文件夹。

Hive中有分区表的概念，我们可以看到分区具有重要性能优势，分区表可以将数据以一种符合逻辑的方式进行组织，比如分层存储。
2、查询分区表中的数据时，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。
3、换句话说，就是用户不允许扫描所有的分区。
4、进行这个显示的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。如果没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
5、分区是hive存放数据的一种方式。将列值作为目录来存放数据，就是一个分区。这样查询时使用分区列进行过滤，只需根据列值直接扫描对应目录下的数据，不扫描其他不关心的分区，快速定位，提高查询效率.

分区表分位静态分区和动态分区

静态分区：手动指定-->编译时期。

动态分区：通过输入数据来进行判断-->SQL语句。

一般按时间来分区：天，小时，分钟。

静态分区：

create table test
(name string,age int)
partitioned by (country string)
row format delimited fields terminated by '	'
lines terminated by '
'
stored as textfile;

向分区中插入数据：

insert into table test partition(country="china")
values("zhangsan",1);
insert into table test partition(country="usa")
values("james",34);
insert into table test partition(country="usa")
values("tom",2);

查询分区表的数据：

select * from test where country="china";

删除分区：

alter table test drop partition(country="china");

加载数据指定分区：

load data local inpath '/root/Desktop/student.txt' into table test partition(name='zs',age=21);

动态分区：

动态分区的相关配置属性:

set hive.exec.dynamic.partition=true;
(可通过这个语句查看：set hive.exec.dynamic.partition;)
set hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;(如果自动分区数
大于这个参数，将会报错)
SET hive.exec.max.dynamic.partitions.pernode=100000;

显示分区数:

show partitions order_part;

查询分区表中的数据:

select * from user_trade limit 6;  //这样会报错，因为没有加分区条件。

查看所有配置

set

 严格模式：set hive.mapred.mode=strict;

set hive.mapred.mode=strict;
select * from user_trade limit 6;
select * from user_trade where dt='2017-01-12';

严格模式限制3种查询：

我们应该

1、对分区表查询，用where过滤字段用分区字段。

2、禁止用笛卡尔积join查询，join查询语句，不带on条件或者where条件。

3、order by后面用limit。

扩展：hive分桶

1、分桶是对列值取hash值的方式将数据放在不同的文件存储。

2、hive中的每一个表、分区都可以进行分桶。

3、由列的hash值除以桶的个数来决定将每条数据具体划分在哪个桶中。

应用场景：抽样、map-join

分桶我用的比较少，具体内容可自行百度。

参考文献：

https://blog.csdn.net/qq_39783601/article/details/104934245，

https://blog.csdn.net/MrBack/article/details/82379995，

开课吧