数据仓库Hive编程——HiveQL的数据定义（三）：创建表

分类目录：商业智能《数据仓库Hive编程》总目录

相关文章：
HiveQL的数据定义（一）：Hive中的数据库
HiveQL的数据定义（二）：修改数据库
HiveQL的数据定义（三）：创建表
HiveQL的数据定义（四）：分区表和管理表
HiveQL的数据定义（五）：删除表
HiveQL的数据定义（六）：修改表

CREATE TABLE语句遵从SQL语法惯例，但是Hive的这个语句中具有显著的功能扩展，使其可以具有更广泛的灵活性。例如，可以定义表的数据文件存储在什么位置、使用什么样的存储格式，等等。前面的文章中我们已经讨论了很多种存储格式，同时在后续的文章中我们将会再次探讨一下更加高级的格式。本文中，我们会讨论其他一些在CREATE TABLE语句中可以使用到的选项：

CREATE TABLE IF NOT EXISTS mydb.employees (
　name　　　　　　　STRING COMMENT 'Employee name',
　salary　　　　　　FLOAT COMMENT 'Employee salary',
　subordinates　　　ARRAY<STRING> COMMENT 'Names of subordinates',
　deductions　　　　MAP<STRING, FLOAT>
　　　　　　　　　　 COMMENT 'Keys are deductions names, values are percentages',
　address　　　　　　STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
COMMENT 'Home address')
COMMENT 'Description of the table'
TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00', ...)
LOCATION '/user/hive/warehouse/mydb.db/employees';

首先，我们可以注意到，如果用户当前所处的数据库并非是目标数据库，那么用户是可以在表名前增加一个数据库名来进行指定的，也就是例子中的mydb。

如果用户增加上可选项IF NOT EXITS ，那么若表已经存在了，Hive就会忽略掉后面的执行语句，而且不会有任何提示。在那些第一次执行时需要创建表的脚本中，这么写是非常有用的。

然而，这个语句还有一个用户需要注意的问题。如果用户所指定的表的模式和已经存在的这个表的模式不同的话，Hive不会为此做出提示。如果用户的意图是使这个表具有重新指定的那个新的模式的话，那么就需要先删除这个表，也就是丢弃之前的数据，然后再重建这张表。用户可以考虑使用一个或多个ALTER TABLE语句来修改已经存在的表的结构。有更详细的信息。也就是说，如果用户使用了IF NOT EXISTS，而且这个已经存在的表和CREATE TABLE语句后指定的模式是不同的。Hive会忽略掉这个差异。

用户可以在字段类型后为每个字段增加一个注释。和数据库一样，用户也可以为这个表本身添加一个注释，还可以自定义一个或多个表属性。大多数情况下，TBLPROPERTIES的主要作用是按键值对的格式为表增加额外的文档说明。但是，当我们检查Hive和像DymamoDB这样的数据库间的集成时，我们可以发现TBLPROPERTIES还可用作表示关于数据库连接的必要的元数据信息。

Hive会自动增加两个表属性：一个是last_modified_by，其保存着最后修改这个表的用户的用户名；另一个是last_modified_time，其保存着最后一次修改的新纪元时间秒。

Hive v0.10.0版本中有一个功能增强计划，也就是增加一个SHOW TBLPROPERTIES table_name命令，用于列举出某个表的TBLPROPERTIES属性信息。

最后，可以看到我们可以根据情况为表中的数据指定一个存储路径（和元数据截然不同的是，元数据总是会保存这个路径）。在这个例子中，我们使用的Hive将会使用的默认的路径/user/hive/warehouse/mydb.db/employees。其中，/user/hive/warehouse是默认的“数据仓库”路径地址，mydb.db是数据库目录，employees是表目录。

默认情况下，Hive总是将创建的表的目录放置在这个表所属的数据库目录之后。不过，default数据库是个例外，其在/user/hive/warehouse下并没有对应一个数据库目录。因此default数据库中的表目录会直接位于/user/hive/warehouse目录之后。为了避免潜在产生混淆的可能性，如果用户不想使用默认的表路径，那么最好是使用外部表。

用户还可以拷贝一张已经存在的表的表模式，而无需拷贝数据：

CREATE TABLE IF NOT EXISTS mydb.employees2
LIKE mydb.employees;

这个版本还可以接受可选的LOCATION语句，但是注意其他的属性，包括模式，都是不可能重新定义的，这些信息直接从原始表获得。

SHOW TABLES命令可以列举出所有的表。如果不增加其他参数，那么只会显示当前工作数据库下的表。假设我们已经创建了一些其他表，table1和table2，而且我们的工作数据库是mydb：

hive> USE mydb;

hive> SHOW TABLES;
employees
table1
table2

即使我们不在那个数据库下，我们还是可以列举指定数据库下的表的：

hive> USE default;

hive> SHOW TABLES IN mydb;
employees
table1
table2

如果我们有很多的表，那么我们可以使用正则表达式来过滤出所需要的表名。

hive> USE mydb;
hive> SHOW TABLES 'empl.*';
employees

Hive并非支持所有的正则表达式功能。如果用户了解正则表达式的话，最好事先测试下备选的正则表达式是否真正奏效！单引号中的正则表达式表示的是选择所有以empl开头并以其他任意字符结尾的表名。

我们也可以使用DESCRIBE EXTENDED mydb.employees命令来查看这个表的详细表结构信息。如果我们当前所处的工作数据库就是mydb的话，那么我们可以不加mydb.这个前缀。下面显示的内容我们进行过格式调整，使之查看起来更加容易，而且我们还省略掉了一些无关紧要的细节，因为我们只关注我们感兴趣的信息：

hive> DESCRIBE EXTENDED mydb.employees;
name　　　string　　Employee name
salary　　float　　 Employee salary
subordinates　　　　array<string>　　Names of subordinates
deductions　　　　　map<string,float> Keys are deductions names, values are percentages
address struct<street:string,city:string,state:string,zip:int> Home address

Detailed Table Information　　　　Table(tableName:employees, dbName:mydb, owner:me,
...
location:hdfs://master-server/user/hive/warehouse/mydb.db/employees,
parameters:{creator=me, created_at='2012-01-02 10:00:00',
　　　　　　last_modified_user=me, last_modified_time=1337544510,
　　　　　　comment:Description of the table, ...}, ...)

使用FORMATTED关键字替代EXTENDED关键字的话，可以提供更加可读的和冗长的输出信息,而且在实际中，使用FORMATTED要更多些，因为其输出内容详细而且可读性强）

上面输出信息的第一段是和没有使用关键字EXTENDED或者FROMATTED时输出的结果一样的。如果用户只想查看某一个列的信息，那么只要在表名后增加这个字段的名称即可。这种情况下，使用EXTENDED关键字也不会增加更多的输出信息：

hive> DESCRIBE mydb.employees.salary;
salary float Employee salary

回到之前的那个包含详细扩展信息的输出。我们需要特别注意以location:开头的那行描述信息。这个是Hive在HDFS中的存储表中数据的完整的URL目录路径。

同时，我们说过last_modified_by和last_modified_time两个表属性是会自动创建的。如果没有定义任何的用户自定义表属性的话，那么它们也不会显示在表的详细信息。

管理表

我们目前所创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会控制着数据的生命周期。正如我们所看见的，Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。

但是，管理表不方便和其他工作共享数据。例如，假设我们有一份由Pig或者其他工具创建并且主要由这一工具使用的数据，同时我们还想使用Hive在这份数据上执行一些查询，可是并没有给予Hive对数据的所有权，我们可以创建一个外部表指向这份数据，而并不需要对其具有所有权。

外部表

假设我们正在分析来自股票市场的数据。我们会定期地从像Infochimps这样的数据源接入关于NASDAQ和NYSE的数据，然后使用很多工具来分析这份数据。我们后面将要使用的模式和这源数据都是匹配的。我们假设这些数据文件位于分布式文件系统的/data/stocks目录下。下面的语句将创建一个外部表，其可以读取所有位于/data/stocks目录下的以逗号分隔的数据：

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
　exchange　　　　　STRING,
　symbol　　　　　　STRING,
　ymd　　　　　　　  STRING,
　price_open　　　　FLOAT,
　price_high　　　　FLOAT,
　price_low　　　　 FLOAT,
　price_close　　　 FLOAT,
　volume　　　　　　INT,
　price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

关键字EXTENAL告诉Hive这个表是外部的，而后面的LOCATION…子句则用于告诉Hive数据位于哪个路径下。
因为表是外部的，所以Hive并非认为其完全拥有这份数据。因此，删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

管理表和外部表有一些小小的区别，那就是，有些HiveQL语法结构并不适用于外部表。后面的文章在我们遇到这些问题的时候，我们再来进行讲述。

然而，我们需要清楚的重要的一点是管理表和外部表之间的差异要比刚开始所看到的小得多。即使对于管理表，用户也是可以知道数据是位于哪个路径下的，因此用户也是可以使用其他工具来修改甚至删除管理表所在的路径目录下的数据的。可能从严格意义上来说，Hive是管理着这些目录和文件，但是其并非具有对它们的完全控制权限在《数据类型和文件格式（四）：读时模式》中，我们说过，Hive实际上对于所存储的文件的完整性以及数据内容是否和表模式相一致并没有支配能力，甚至管理表都没有给用户提供这些管理能力。

尽管如此，好的软件设计的一般原则是表达意图。如果数据会被多个工具共享，那么可以创建一个外部表，来明确对数据的所有权。

用户可以在DESCRIBE EXTENDED tablename语句的输出中查看到表是否是管理表或外部表。在末尾的详细表信息输出中，对于管理表，用户可以看到如下信息：

... tableType:MANAGED_TABLE)

对于外部表，用户可以查看到如下信息：

... tableType:EXTERNAL_TABLE)

对于管理表，用户还可以对一张存在的表进行表结构复制（而不会复制数据）：

CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employees
LOCATION '/path/to/data';

这里，如果语句中省略掉EXTERNAL关键字而且源表是外部表的话，那么生成的新表也将是外部表。如果语句中省略掉EXTERNAL关键字而且源表是管理表的话，那么生成的新表也将是管理表。但是，如果语句中包含有EXTERNAL关键字而且源表是管理表的话，那么生成的新表将是外部表。即使在这种场景下，LOCATION子句同样是可选的。