数据表是存储数据的基本单位，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<STRING> CONMENT “This is staff hobby”, deduct(hps MAP<STRING, FLOAT> COMMENT “This is staff deduction”, address STRUCT<street:STRING,city:STRING> 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<STRING> CONMENT “This is staff hobby”, deduct(hps MAP<STRING, FLOAT> COMMENT “This is staff deduction”, address STRUCT<street:STRING,city:STRING> 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子句指定数据的存储位置，便于数据的维护与管理。
免责声明：文章内容来自互联网，本站仅作为分享，不对其真实性负责，如有侵权等情况，请与本站联系删除。
转载请注明出处：Hive中怎样创建内部数据表和外部数据表？ https://www.dachanpin.com/a/cyfx/10962.html
