Apache Hive Table

文本文件

-CSV:以逗号分隔的文本文件
-TSV:以制表符分隔的文本文件
这两种文件格式Hive都支持，但是有个缺点就是：用户要对文本文件中那些不需要作为分隔符处理的逗号或者制表符格外小心。

创建表：
CREATE TABLE 语句遵循SQL语法惯例，但比较灵活，可定义表得数据文件存储在什么位置，使用什么存储格式

示例：
CREATE TABLE IF NOT EXISTS table1(
userId  string  COMMENT '用户ID',
name string COMMENT '用户姓名',
createtime string COMMENT '创建时间'
 )
COMMENT '用户表'
TBLPROPERTIES ('creator'='data', 'created_at'='2019-05-19 20:00:00', ...)
LOCATION '/user/hive/warehouse/data/table1';

1.如果用户当前数据库不是要建表的目标数据库，可以在表明前增加库名来进行指定table1

2.增加 IF NOT EXISTS，若表存在，Hive会忽略后面的建表语句，而且不会有提示，所以第一次建表 IF NOT EXISTS 会有用

3.但需要注意是的，如果已存在的表和这个表模式不一样，Hive不会做出提示，如果要用新表模式需删除原表，然后重新建表。或者修改原表。

4.一般情况下，TBLPROPERTIES的主要作用是按键值对格式为表增加额外的说明文档

SHOW TBLPROPERTIES table_name命令，用于列出表的TBLPROPERTIES属性信息

5.Hive建表时会自动增加两个属性：last_modified_by(保存最后修改这个表的用户的用户名)

last_modified_time(保存最后一次修改的时间)

6.LOCATION指定一个存储路径，这个例子我们使用了默认路径，Hive默认将创建的表的目录放置在该表所属数据库后

但defult库是个例外，他在/user/hive/warehouse下没有对应库目录，所以defult库中的表目录直接位于/user/hive/warehouse目录后（明确指定例外）

拷贝表：

CREATE TABLE INNOT EXISTS table2 LIKE TABLE1

列举表：

SHOW TABLES;
table1
table2
table3

列举库：

hive> show databases;
OK
data
default
Time taken: 0.016 seconds, Fetched: 2 row(s)

删除表：

hive>DROP TABLE IF EXISTS table1;

删除规则：
对于内部表，表的元数据信息和表内的数据都会被删除。

对于外部表，表的元数据信息会被删除，但是表中的数据不会被删除。

HIve表操作

管理表

 --我们目前所创建的表都是管理表，也叫内部表

--Hive会控制管理表数据的生命周期，Hive默认会将表数据存储在/user/hive/warehouse子目录下

 --删除一个管理表时，Hive也会删除这个表中数据

 --管理表不方便和其他工具共享数据

例如：

我们有一份由Pig或其他工具创建并且主要由这一工具使用的数据，同时我们还想使用Hive执行查询，可以创建一个外部表指向这份数据，并不需要对其具有所有权限

外部表：

--文件位于分布式文件系统的/data/test

CREATE EXTERNAL TABLE IF NOT EXISTS app (
hour string,
name string,
pv string,
uv string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/test';

对以上语句分析:

1.关键字EXTERNAL告诉Hive这个表是外部表，LOCATION告诉Hive数据位于哪个路径下

2.因为是外部表，所以Hive并非认为其完全拥有这份数据，删除该表时并不会删除这份数据，只会删除描述表的元数据信息

管理表VS外部表

–可以用 DESCRIBE EXTENDED tablename语句的输出中查看到表是否是管理表或外部表

–对于管理表，可看到如下信息

tableType:MANAGED_TABLE)

–对于外部表，可看到如下信息

tableType:EXTERNAL_TABLE)

注：如果语句省略 EXTERNAL 关键字而源表是外部表的话，那么生成的新表也是外部表

    如果语句省略 EXTERNAL 关键字而源表是管理表的话，那么生成的新表也是管理表

    如果语句有EXTERNAL关键字而源表是管理表的话，那么生成的新表是外部表

分区管理表

--管理表和外部表都可以加分区

CREATE TABLE IF NOT EXISTS tmp.table1(
userId  string  COMMENT '用户ID',
name string COMMENT '用户姓名',
createtime string COMMENT '创建时间'
)PARTITIONED BY (country string,state string );

–分区表改变了Hive对数据存储的组织方式。

如果我们在tmp库下创建这个表,那么对于这个表只会有一个table1目录与之对应：

/user/hive/warehouse/tmp/table1

但是，Hive在表目录下将会建好可以反映分区结构的子目录

/table1/country=CA/state=AB
/table1/country=CA/state=BC
…
/table1/country=US/state=AL
/table1/country=US/state=AK
…
这些都是实际的目录名称，州目录下将会包含有零个文件或者多个文件，这些文件中存放着那些州的用户信息。

分区字段一旦创建好，表现得就和普通字段一样，除非优化查询性能，否则不需要关心是否是分区字段

如果要查某个国家的用户，那仅仅需要扫描那个国家对应的目录就可以

--查看表中存在的所有分区

hive> SHOW PARTITIONS table1;
country=CA/state=AB    
country=CA/state=BC
···
country=US/state=AL
country=US/state=AK
...
查看某个特定分区
hive> SHOW PARTITIONS table1 PARTITION（country='US'）;
country=US/state=AL
country=US/state=AK

外部分区表

--创建外部分区表

CREATE EXTERNAL TABLE IF NOT EXISTS app (
hour string,
name string,
pv string,
uv string) 
PARTITIONED BY (timetype string,clct_day string ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

--增加指定分区的值

ALTER TABLE app ADD PARTITION (timetype=hour, clct_day='2019-05-19')
LOCATION '/data/test/table1/hour/'2019-05-19' ';

自定义表的存储格式

--Hive的默认存储格式是文本文件格式，
也可以用STORED AS TEXTFILE 指定，同时在创建表的时候指定各种分隔符
--使用TEXTFILE意味着每一行被认为是一个单独的记录
--也可以保存为其他Hive支持的文件格式，包括SEQUENCEFILE和RCFILE,这两种文件格式都是使用二进制编码和压缩来优化磁盘空间使用以及I/O带宽性能的
CREATE TABLE IF NOT EXISTS data.table1(
userId  string  COMMENT '用户ID',
name string COMMENT '用户姓名',
createtime string COMMENT '创建时间') 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

修改表：
大多数表属性可以通过ALTER TABLE语句来进行修改，这种操作会修改元数据，但不会修改数据本身。

⊙表重命名

ALTER TABLE  app RENAME TO user;
⊙增加、修改和删除表分区

 --ALTER TABLE tablename ADD PARTITION ... 语句用于为表（通常是外部表）增加一个新的分区
例如: ALTER TABLE  app ADD IF NOT EXISTS
PARTITION (timetype=hour, clct_day='2019-05-16' ) LOCATION '/data/test/app/hour/'2019-05-19' '
PARTITION (timetype=hour, clct_day='2019-05-19' ) LOCATION '/data/test/app/hour/'2019-05-19' '
PARTITION (timetype=hour, clct_day='2019-05-19' ) LOCATION '/data/test/app/hour/'2019-05-19' ';
--移动位置来修改某个分区的路径
ALTER TABLE  app  PARTITION (timetype=hour, clct_day='2019-05-19' )
SET LOCATION '/home/data/app/hour/'2019-05-19' ';
这个命令不会将数据从旧的路线转移走，也不会删除旧的数据。
--删除分区
ALTER TABLE  app DROP IF EXISTS PARTITION (timetype=hour, clct_day='2019-05-19' );
注：对于管理表，即使是使用ALTER TABLE...ADD PARTITION 语句增加的分区，分区内的数据也是会同时和元数据信息一起被删除的
对于外部表，分区内数据不会被删除
⊙修改列信息

--对某个字段进行重命名，并修改其位置、类型或者注释

ALTER TABLE  appCHANGE COLUMN hour time_h INT
COMMENT 'THE hours part of the timestamp'
AFTER uv;

注：即使字段名或者字段类型没有改变，也需要完全指定旧的字段名，并给出新的字段名及新的字段类型

此例子我们将字段转移到uv字段之后，如果要转移到第一个位置，只需要用FIRST关键字替代AFTER other_column子句即可

和通常一样，这个命令只会修改元数据信息，如过移动字段，那么数据也应和新的模式匹配

增加列

--我们可以在分区字段前增加新字段到已有字段之后

ALTER TABLE  app ADD COLUMNS(
appversion  STRING COMMENT 'Application version',
nettype STRING COMMENT 'logining application with nettype');
⊙删除或者替换列

--移除之前所有字段并重新指定了新字段
ALTER TABLE  app REPLACE COLUMNS(
time int,
name string,
message string);
解析：这个语句实际上重命名了之前的hour字段并且从原表移除了字段pv,uv，
增加了message字段，因为是ALTER语句，所以只有表的元数据信息改变了
⊙修改表属性

--可以增加附加的表属性或者修改已经存在的表属性，但是无法删除属性

ALTER TABLE  app SET TBLPROPERTIES('notes'='this column is always NULL');
⊙修改存储属性

--有几个ALTER TABLE 语句用于修改存储格式和SerDe属性

ALTER TABLE  app
PARTITION (timetype string,clct_day string )
SET FILEFORMAT SEQUENCEFILE;
解析：以上语句是将一个分区的存储格式改成了SEQUENCEFILE，

如果是分区表，需要使用PARTITION子句