Hive中怎样创建内部数据表和外部数据表？

数据表是存储数据的基本单位，Hive数据表主要分为内部表(又叫托管表)和外部表，以内部表和外部表为基础可以创建分区表或分桶表，即内/外部分区表或内/外部分桶表。接下来，针对内部表和外部表进行详细讲解。

默认情况下，内部表和外部表的数据都存储在Hive配置文件中参数hive.metastore.warehouse.dir指定的路径。它们的区别在于删除内部表时，内部表的元数据和数据会一同删除;而删除外部表时，只删除外部表的元数据，不会删除数据。外部表相对来说更加安全，数据组织更加灵活并且方便共享源数据文件。

CREATE TABLE IF NOT EXISTS
hive_database.managed_table(
staff_id INT COMMENT "This is staffid",
staff_name STRING COMMENT "This is staffname",
salary FLOAT COMMENT "This is staff salary",
hobby ARRAY CONMENT "This is staff hobby",
deduct(hps MAP COMMENT "This is staff deduction",
address STRUCT COMMENT "This is staff address"
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY
MAP KEYS TERMINMTED BY  ':'
LINES TERMINATED BY '\n'
STORED AS textfile
TBLPROPERTIES("comment"="This is a managed table");

上述命令中，指定ROW FORMAT DELIMITED子句使用Hive内置的Serde，自定义字段(FIELDS)分隔符为“，”;自定义集合元素(COLLECTION ITEMS)的分隔符为“_”;自定义MAP(MAP KEYS)的键值对分隔符为“：”;自定义行(LINES)分隔符为\n。

(2)创建外部表external_table的命令如下。

CREATE TABLE IF NOT EXISTS
hive_database.managed_table(
staff_id INT COMMENT "This is staffid",
staff_name STRING COMMENT "This is staffname",
salary FLOAT COMMENT "This is staff salary",
hobby ARRAY CONMENT "This is staff hobby",
deduct(hps MAP COMMENT "This is staff deduction",
address STRUCT COMMENT "This is staff address"
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY
MAP KEYS TERMINMTED BY  ':'
LINES TERMINATED BY '\n'
STORED AS textfile
TBLPROPERTIES("comment"="This is a managed table");

上述命令中，通过在CREATETABLE句式中指定EXTERNAL子句创建外部表。创建外部表时通常配合LOCATION子句指定数据的存储位置，便于数据的维护与管理。