Hive_3. DDL -- 数据库 & 内部表 & 外部表

Hive 数据定义语言

Hive Data Definition Language (DDL) 是 Hive SQL 语句的的一个子集，它通过 creating, deleting, or altering  模式对象(数据库，表，分区，视图，Buckets)来描述Hive的数据结构。大部分的 DDL 关键词为CREATE, DROP 或者 ALTER.， Hive DDL 的语法类似于 SQL，所以对有数据库基础的人员看起来会异常亲切。

Hive 数据库

Hive 中的数据库用来描述相同目的的表集合或者在相同目录下的一组文件。如果为指定数据库，则会使用默认数据库。创建一个新数据库时，Hive 会在 /user/hive/warehouse 路径下为每一张表创建一个目录，可以通过 hive.metastore.warehouse.dir 参数进行配置。

例如：数据库myhivebook 坐落于/user/hive/datawarehouse/myhivebook.db。但是默认数据库不存在目录。以下是 Hive 数据库的核心 DDL：

创建数据库(不检查数据库是否已经存在) ：

jdbc:hive2://> CREATE DATABASE myhivebook;

在创建数据库的时候检查是否已经存在 ：

jdbc:hive2://> CREATE DATABASE IF NOT EXISTS myhivebook;

创建数据库的时候提供：位置，注释和元数据信息：

jdbc:hive2://> CREATE DATABASE IF NOT EXISTS myhivebook
. . . . . . .> COMMENT 'hive database demo'
. . . . . . .> LOCATION '/hdfs/directory'
. . . . . . .> WITH DBPROPERTIES ('creator'='dayongd','date'='2015-01-01');  --数据库键值对属性信息

使用通配符来显示和描述数据库 ：

jdbc:hive2://> SHOW DATABASES;
+----------------+
| database_name  |
+----------------+
| default        |
+----------------+
1 row selected (1.7 seconds)

jdbc:hive2://> SHOW DATABASES LIKE 'my.*';
jdbc:hive2://> DESCRIBE DATABASE default;
+-------+----------------------+-----------------------------+
|db_name|      comment         |               location      |
+-------+----------------------+-----------------------------+
|default|Default Hive database |hdfs://localhost:8020/user/hive/warehouse|
+-------+----------------------+-----------------------------+
1 row selected (1.352 seconds)

使用特定数据库：

jdbc:hive2://> USE myhivebook;

删除(空的)数据库：

jdbc:hive2://> DROP DATABASE IF EXISTS myhivebook;

IF EXISTS子句是可选的，如果加了这个子句，就可以避免因数据库myhivebook不存在而抛出警告信息

Note

需要注意的是，Hive 以目录的形式来存储数据库和表。如果你希望删除父目录，你需要首先删除子目录。

默认情况下，非空的数据库是不允许删除的。除非指定 CASCADE 。CASCADE 会在删除数据库之前自动删除数据库中的表。

使用 CASCADE 来删除数据库：级联删除数据库(当数据库还有表时，级联删除表后在删除数据库),默认是restrict
默认情况下，Hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，然后再删除数据库；要么在删除命令的最后面加上关键字CASCADE，这样可以使Hive自行先删除数据库中的表

jdbc:hive2://> DROP DATABASE IF EXISTS myhivebook CASCADE;

从 Hive 0.13.0 开始，你可以通过 ALTER DATABASE 语句来更改表的属性和Roles -- 键值对属性。数据库元数据信息如数据库名和数据库所在的目录位置不能被修改：

jdbc:hive2://> ALTER DATABASE myhivebook 
. . . . . . .> SET DBPROPERTIES ('edited-by' = 'Dayong');

jdbc:hive2://> ALTER DATABASE myhivebook
               SET OWNER user dayongd;

Note：SHOW 和 DESCRIBE

Hive 中 SHOW 和 DESCRIBE 关键词用来表示Hive 中大部分对象的定义：表，分区..
SHOW 语句支持广泛的 Hive 对象，例如表，表分区，表 DDL，索引，分区，字段，函数，锁，roles，配置，事务和 compactions - 压缩/紧凑。
DESCRIBE 语句支持很少的 HIve对象，例如数据库，表，视图，字段，分区。然而，DESCRIBE 语句可以跟  EXTENDED 或者 FORMATTED 关键词一起使用来提供更加详细的信息。

Hive 表: 内部表和外部表

Hive 中表的概念类似于关系数据库中对表的定义。每个表都关联在 HDFS 路径配置${HIVE_HOME}/conf/hive-site.xml下。默认情况下是 HDFS 中的/user/hive/warehouse 。例如 Hive 在 HDFS 中创建了一张employee 表，它的路径是/user/hive/warehouse/employee。所有表中的路径都存储在这里路径下。Hive 表也被称为内部表或者管理表。

如果数据已经存在于 HDFS 中，可以创建一张外部表来表示数据。它被称为EXTERNAL  ，因为外部表在 LOCATION  属性中指定 只需要指定数据存储路径即可，而不需要保存实际的数据。当删除内部表时，表中的数据都会被删除。但是当删除外部表是，只会删除表的元数据信息，不会影响实际数据的位置以及内容。外部表的创建主要是用来规避删除表时遇到的错误。
1. 你可以通过以下的 DDL 来查看内部表和外部表的实例：

查看文件路径以及内部表 employee  数据：

bash-4.1$ vi /home/hadoop/employee.txt
Michael|Montreal,Toronto|Male,30|DB:80|Product:Developer^DLead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead

创建内部表并且加载数据 ：

jdbc:hive2://> CREATE TABLE IF NOT EXISTS employee_internal
. . . . . . .> (
. . . . . . .>  name string,
. . . . . . .>  work_place ARRAY<string>,
. . . . . . .>  sex_age STRUCT<sex:string,age:int>,
. . . . . . .>  skills_score MAP<string,int>,
. . . . . . .>  depart_title MAP<STRING,ARRAY<STRING>>
. . . . . . .> )
. . . . . . .> COMMENT 'This is an internal table'
. . . . . . .> ROW FORMAT DELIMITED
. . . . . . .> FIELDS TERMINATED BY '|'
. . . . . . .> COLLECTION ITEMS TERMINATED BY ','
. . . . . . .> MAP KEYS TERMINATED BY ':'
. . . . . . .> STORED AS TEXTFILE;
No rows affected (0.149 seconds)

jdbc:hive2://> LOAD DATA LOCAL INPATH '/home/hadoop/employee.txt'
. . . . . . .> OVERWRITE INTO TABLE employee_internal;

创建外部表并且加载数据 ：

jdbc:hive2://> CREATE EXTERNAL TABLE employee_external
. . . . . . .> (
. . . . . . .>  name string,
. . . . . . .>  work_place ARRAY<string>,
. . . . . . .>  sex_age STRUCT<sex:string,age:int>,
. . . . . . .>  skills_score MAP<string,int>,
. . . . . . .>  depart_title MAP<STRING,ARRAY<STRING>>
. . . . . . .> )
. . . . . . .> COMMENT 'This is an external table'
. . . . . . .> ROW FORMAT DELIMITED
. . . . . . .> FIELDS TERMINATED BY '|'
. . . . . . .> COLLECTION ITEMS TERMINATED BY ','
. . . . . . .> MAP KEYS TERMINATED BY ':'
. . . . . . .> STORED AS TEXTFILE
. . . . . . .> LOCATION '/user/dayongd/employee';
No rows affected (1.332 seconds)

jdbc:hive2://> LOAD DATA LOCAL INPATH '/home/hadoop/employee.txt'. . . . . . .> OVERWRITEINTO TABLE employee_external;

Note ：CREATE TABLE

Hive 的表没有像数据库那样的限制。

如果在LOCATION 路径属性中不存在文件夹，Hive 会自动创建那个文件夹。如果在 LOCATION 路径属性下已经有另一个文件夹了，Hive 在创建表的时候不会报错，但是在查询的时候会报错。
对于临时表，会在 Hive 回话的最后被自动删除。你可以通过CREATE TEMPORARY TABLE 语句来完成该操作。它是在 Hive 0.14.0 版本才开始支持的 ：(https://issues.apache.org/jira/browse/HIVE-7090) 
对于 属性，默认情况下是  格式，当然也支持其他格式，例如说SEQUENCEFILE, RCFILE, ORC, AVRO(since Hive 0.14.0) 和 PARQUET (since Hive 0.13.0)。需要注意的是, Hive 在0.9.1 版本中就可以集成 Avro package，并且可以通过调用 org.apache.hadoop.hive.serde2.avro.AvroSerDe  来将文件存储为 Avro 格式。但是在 Hive 0.14.0 版本中才全面支持 Avro, Hive 可以自动识别 Avro 的 schema，并且自动创建 Hive 表的 schema。
详细内容请参考：http://blog.csdn.net/mike_h/article/details/50098295

2. Create the table as select (CTAS):

jdbc:hive2://> CREATE TABLE ctas_employee . . . . . . .> AS SELECT * FROM employee_external;No rows affected (1.562 seconds)

注意 ：CTAS

CTAS 拷贝数据的时候也会拷贝数据定义。CTAS 会自动创建表，这意味着只有当所有查询结果都插入以后，其他用户才能看到这种表。CTAS 有以下限制：

• 创建的表不能是分区表
• 创建的表不能是外部表
• 不能是一系列的桶表

一个 CTAS 语句之后触发一个 Map Job 来填充数据； 甚至 SELECT * 语句自己都不会触发任何 MapReduce Job。

CTAS with Common Table Expression (CTE) 可以通过以下方式使用:

jdbc:hive2://> CREATE TABLE cte_employee AS
. . . . . . .> WITH r1 AS 
. . . . . . .> (SELECT name FROM r2 
. . . . . . .> WHERE name = 'Michael'),
. . . . . . .> r2 AS 
. . . . . . .> (SELECT name FROM employee 
. . . . . . .> WHERE sex_age.sex= 'Male'),
. . . . . . .> r3 AS 
. . . . . . .> (SELECT name FROM employee 
. . . . . . .> WHERE sex_age.sex= 'Female')
. . . . . . .> SELECT * FROM r1 UNION ALL select * FROM r3;
No rows affected (61.852 seconds)

jdbc:hive2://> SELECT * FROM cte_employee;
+----------------------------+
| cte_employee.name          |
+----------------------------+
| Michael                    |
| Shelley                    |
| Lucy                       |
+----------------------------+
3 rows selected (0.091 seconds)

Note :CTE

从 Hive 0.13.0 版本开始便支持 CTE，它使用WITH 语句来存储 SELECT 查询的临时结果，之后再通过 SELECT 或者 INSERT 关键词来操作这个结果集。 CET 只能定义在单个语句的执行范围。一个或多个 CETs 可以通过Hive 关键字来嵌套和链接方式。例如 SELECT, INSERT, CREATE TABLE AS SELECT, 或者 CREATE VIEW AS SELECT 语句

有两种方式可以创建一张空表：

1. 使用 CTAS ： 

2. 使用LIKE 语法： 更快一些

jdbc:hive2://> CREATE TABLE empty_ctas_employee AS 
. . . . . . .> SELECT * FROM employee_internal WHERE 1=2;
No rows affected (213.356 seconds) 

jdbc:hive2://> SELECT COUNT(*) AS row_cnt 
. . . . . . .> FROM empty_ctas_employee;
+----------+
| row_cnt  |
+----------+
| 0        |
+----------+
1 row selected (51.228 seconds)

jdbc:hive2://> CREATE TABLE empty_like_employee
. . . . . . .> LIKE employee_internal;
No rows affected (0.115 seconds)

jdbc:hive2://> SELECT COUNT(*) AS row_cnt 
. . . . . . .> FROM empty_like_employee;
+----------+
| row_cnt  |
+----------+
| 0        |
+----------+
1 row selected (41.628 seconds)

Note

如上所示，LIKE  方法会更加快一些。因为它不需要触发 MapReduce Job，只是单纯的元数据复制

3. 更改表的属性：
如果配置了 Trash的话，执行删除表命令的时候，元数据和数据会全部被移到 Trash 中 ：

jdbc:hive2://> DROP TABLE IF EXISTS empty_ctas_employee;
No rows affected (0.283 seconds)

jdbc:hive2://> DROP TABLE IF EXISTS empty_like_employee;
No rows affected (0.202 seconds)

Truncate 命令是针对内部表进行操作，会移除一张表中的所有行 ：

jdbc:hive2://> SELECT * FROM cte_employee;
+--------------------+
| cte_employee.name  |
+--------------------+
| Michael            |
| Shelley            |
| Lucy               |
+--------------------+
3 rows selected (0.158 seconds)

jdbc:hive2://> TRUNCATE TABLE cte_employee;
No rows affected (0.093 seconds)

--Table is empty after truncate
jdbc:hive2://> SELECT * FROM cte_employee;
+--------------------+
| cte_employee.name  |
+--------------------+
+--------------------+
No rows selected (0.059 seconds)

更改表名操作语句如下： 

jdbc:hive2://> !table
+-----------+------------------+-----------+---------------------------+
|TABLE_SCHEM|     TABLE_NAME   | TABLE_TYPE| REMARKS                   |
+-----------+------------------+-----------+---------------------------+
|default    | employee         | TABLE     | NULL                      |
|default    | employee_internal| TABLE     | This is an internal table |
|default    | employee_external| TABLE     | This is an external table |
|default    | ctas_employee    | TABLE     | NULL                      |
|default    | cte_employee     | TABLE     | NULL                      |
+-----------+------------------+-----------+---------------------------+

jdbc:hive2://> ALTER TABLE cte_employee RENAME TO c_employee;
No rows affected (0.237 seconds)

更改表的属性： 

jdbc:hive2://> ALTER TABLE c_employee 
. . . . . . .> SET TBLPROPERTIES ('comment'='New name, comments');
No rows affected (0.239 seconds)

jdbc:hive2://> !table
+-----------+------------------+-----------+---------------------------+
|TABLE_SCHEM|     TABLE_NAME   | TABLE_TYPE| REMARKS                   |
+-----------+------------------+-----------+---------------------------+
|default    | employee         | TABLE     | NULL                      |
|default    | employee_internal| TABLE     | This is an internal table |
|default    | employee_external| TABLE     | This is an external table |
|default    | ctas_employee    | TABLE     | NULL                      |
|default    | c_employee       | TABLE     | New name, comments        |
+-----------+------------------+-----------+---------------------------+

• 通过 SERDEPROPERTIES 来更改表的分隔符： 

  jdbc:hive2://> ALTER TABLE employee_internal SET . . . . . . .> SERDEPROPERTIES ('field.delim' = '$');No rows affected (0.148 seconds)

• 更改表的文件格式： 

  jdbc:hive2://> ALTER TABLE c_employee SET FILEFORMAT RCFILE; No rows affected (0.235 seconds) 

更改表的路径，必须提供 HDFS 的完整的 URL：

jdbc:hive2://> ALTER TABLE c_employee 
. . . . . . .> SET LOCATION
. . . . . . .> 'hdfs://localhost:8020/user/dayongd/employee'; 
No rows affected (0.169 seconds)

更改(enable/disable)表的防护等级为 NO_DROP，用来避免表被删除。或者使用 OFFLINE ，来避免一张表中的数据被查询。
该方法常用在用户私密信息管理的时候，对应用户密码以及私密信息的表/列 ，我们应该加上一定的权限，使得普通用户无法访问这些数据。

jdbc:hive2://> ALTER TABLE c_employee ENABLE NO_DROP; 
jdbc:hive2://> ALTER TABLE c_employee DISABLE NO_DROP; 
jdbc:hive2://> ALTER TABLE c_employee ENABLE OFFLINE;
jdbc:hive2://> ALTER TABLE c_employee DISABLE OFFLINE;

更改表的 concatenation 来允许将小文件整合成大文件： 

--转换成支持的ORC文件格式 
jdbc:hive2://> ALTER TABLE c_employee SET FILEFORMAT ORC;
No rows affected (0.160 seconds)
--Concatenate files
jdbc:hive2://> ALTER TABLE c_employee CONCATENATE;
No rows affected (0.165 seconds)

--转换成常规的文件格式
jdbc:hive2://> ALTER TABLE c_employee SET FILEFORMAT TEXTFILE;
No rows affected (0.143 seconds)

Note
CONCATENATE： 在 Hive 发布的0.8.0 版本中，RCFile 可以通过CONCATENATE 命令来快速整合 block 级别小的 RCFile。在 Hive 0.14版本 又发布了 ORC 格式，它支持通过CONCATENATE  命令来快速整合stripe 级别小的ORC文件。 暂时还不支持整合其他格式。在处理 RCFile的时候，整合是基于 block 级别的，ORC 文件是基于 stripe 级别的。这样可以避免解压缩和解码数据时产生的负载。当执行 CONCATENATE  的时候会触发 MapReduce。

4. 更改列的属性：
更改字段的数据类型： 

--在更改之前检查字段类型 
jdbc:hive2://> DESC employee_internal;
+----------------+-----------------------------+----------+
|    col_name    |          data_type          | comment  |
+----------------+-----------------------------+----------+
| employee_name  | string                      |          |
| work_place     | array<string>               |          |
| sex_age        | struct<sex:string,age:int>  |          |
| skills_score   | map<string,int>             |          |
| depart_title   | map<string,array<string>>   |          |
+----------------+-----------------------------+----------+
5 rows selected (0.119 seconds)

--更改字段类型和排序
jdbc:hive2://> ALTER TABLE employee_internal 
. . . . . . .> CHANGE name employee_name string AFTER sex_age;
No rows affected (0.23 seconds) 

--检查更改 
jdbc:hive2://> DESC employee_internal; 
+----------------+-----------------------------+----------+
|    col_name    |          data_type          | comment  |
+----------------+-----------------------------+----------+
| work_place     | array<string>               |          |
| sex_age        | struct<sex:string,age:int>  |          |
| employee_name  | string                      |          |
| skills_score   | map<string,int>             |          |
| depart_title   | map<string,array<string>>   |          |
+----------------+-----------------------------+----------+
5 rows selected (0.214 seconds)

更改字段的类型和排序：

jdbc:hive2://> ALTER TABLE employee_internal 
. . . . . . .> CHANGE employee_name name string FIRST;
No rows affected (0.238 seconds)
--检查更改
jdbc:hive2://> DESC employee_internal; 
+---------------+-----------------------------+----------+
|   col_name    |          data_type          | comment  |
+---------------+-----------------------------+----------+
| name          | string                      |          |
| work_place    | array<string>               |          |
| sex_age       | struct<sex:string,age:int>  |          |
| skills_score  | map<string,int>             |          |
| depart_title  | map<string,array<string>>   |          |
+---------------+-----------------------------+----------+
5 rows selected (0.119 seconds)

增加 / 替换列 ： 

--增加新列到表中 
jdbc:hive2://> ALTER TABLE c_employee ADD COLUMNS (work string);
No rows affected (0.184 seconds)

--检查新增的列 
jdbc:hive2://> DESC c_employee;      
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| name      | string     |          |
| work      | string     |          |
+-----------+------------+----------+
2 rows selected (0.115 seconds)

--替换所有的列 
jdbc:hive2://> ALTER TABLE c_employee 
. . . . . . .> REPLACE COLUMNS (name string);
No rows affected (0.132 seconds)

--检查替换的所有列 
jdbc:hive2://> DESC c_employee;      
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| name      | string     |          |
+-----------+------------+----------+
1 row selected (0.129 seconds)

Note：

ALTER 命令之后更改 Hive 的元数据信息，并不会影响数据。所有用户需要手动确保实际数据与元数据的匹配