Hive HQL知识DDL汇总之数据表操作

Hive HQL知识DDL汇总之数据表操作

1. 数据表操作概述

1. 数据库中database和table可以看作是2级文件管理，如mysql在windows下的数据库文件就是划分2级文件夹目录。
   
   
2. 数据表的操作，可以简单分为建立表格，对表结构修改、删除表。
3. hive的表，元数据存储在单独的数据库中，本文配置为存放在mysql中。表中数据则存放在hdfs中。如下图示例
   

2 创建表

2.1 概述

1. 建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 分区
[CLUSTERED BY (col_name, col_name, ...) 分桶
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format]   row format delimited fields terminated by “分隔符”
[STORED AS file_format] 
[LOCATION hdfs_path]

2. 字段解释

1. CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2. EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）.

• Hive创建内部表时，会将数据移动到数据仓库指向的路径；
• 若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
• 在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
• 实际公司开发，一般都会建立外部表，防止删除表时把数据一起删除掉。建立外部表时，注意一定指定location也就是数据文件位置。

3. COMMENT：为表和列添加注释。注意实际上database、table、字段都可以加comment注释。企业开发时，最好都加上注释，方便数据库后续的维护和使用。
4. PARTITIONED BY创建分区表
5. CLUSTERED BY创建分桶表
6. SORTED BY不常用
7. ROW FORMAT
   DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
   [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
   用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
   SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
8. STORED AS指定存储文件类型
   常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

• 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
• 实际使用的文本格式有text、orc、parquet，其中orc压缩率最高但进行解压缩和压缩时会有一定CPU占用，parquet压缩率不那么高，但比text要好一些，所以综合性一般使用parquet，如果是历史性数据，查询和使用不那么频繁时，为了降低磁盘开销，可以使用orc格式。

9. LOCATION ：指定表在HDFS上的存储位置。
10. LIKE允许用户复制现有的表结构，但是不复制数据。

2.2 内部表（管理表）

2.2.1.理论

1. 默认创建的都是管理表，也称之为内部表。这种表，hive会控制数据的生命周期。
2. 默认情况，这些数据库、数据表的数据会存在配置文件中hive.metastore.warehouse.dir所在的hdfs目录，例如/user/hive/warehouse。这个前文已经截图展示。
3. 当删除一个管理表（内部表时），这个表对应存储在hdfs中的数据也会被删除。管理表存储的数据一般不适合跟其他工具或者表共享数据
   注意，实际生产中，为了保护数据安全，一般都会使用外部表，这样就算不小心把表删除，数据也还会存储在hdfs中

2.2.2. 案例

1. 普通表

• 文本文件

200,kongfuzi
300,taoxingzhi
400,maozedong
500,mengzi
600,makesi
700,engesi

• 建表语句

create table if not exists tb_teacher(
id int comment 'teacher id',
name string comment 'teacher name'
) comment 'teacher table'
row format delimited fields terminated by ','
stored as textfile;

• 将文本文件上传到hdfs上
  
  
• 将hdfs中数据加载到数据表中

load data inpath '/hive_data/hive_manage_example.txt' into table tb_teacher;

2. 将查询结果放入表中

create table if not exists tb_result as select id, name from tb_teacher;

3. 根据已存在表创建表

create table if not exists tb_copy like tb_teacher;

4. 查询表类型

desc formatted tb_teacher;

2.3 外部表

2.3.1. 理论

• 如果表是外部表，则hive认为表并不完全拥有这份数据。删除这个表并不会删除掉这份数据。但描述这份表的元数据会被删除掉。
• 使用场景上，例如每天搜集到的日志信息，使用外部表存放，但统计分析后结果使用内部表存放。
• 真实场景上，为了降低因为数据表删除带来的数据删除，一般都使用外部表。

2.3.2. 案例

1. 数据

<