如何使用 Hive 使用单个 hdfs 路径创建 n 个外部表 [英] How to create n number of external tables with a single hdfs path using Hive
问题描述
是否可以使用 Hive 创建 n 个指向单个 hdfs 路径的外部表.如果是，有什么优点和局限性.
Is it possible to create n number of external tables are pointing to a single hdfs path using Hive. If yes what are the advantages and its limitations.
推荐答案
可以在 HDFS 的同一位置上创建多个表(同时托管和外部).
It is possible to create many tables (both managed and external at the same time) on top of the same location in HDFS.
在相同数据之上创建具有完全相同架构的表根本没有用，但是您可以创建具有不同列数的不同表，例如使用 RegexSerDe 创建具有不同解析列的不同表，因此您可以有不同的这些表中的模式.您可以对 Hive 中的这些表拥有不同的权限.也可以在其他一些表文件夹的子文件夹顶部创建表，在这种情况下，它将包含一个子集数据.最好在单个表中使用分区.
Creating tables with exactly the same schema on top of the same data is not useful at all, but you can create different tables with different number of columns for example or with differently parsed columns using RegexSerDe for example, so you can have different schemas in these tables. And you can have different permissions on these tables in Hive. Also table can be created on top of the sub-folder of some other tables folder, in this case it will contain a sub-set of data. Better use partitions in single table for the same.
缺点是容易混淆，因为你可以使用多个表重写相同的数据，而且你可能会不小心删除它，认为这个数据属于唯一的表，你可以删除数据，因为你不需要那个表了.
And the drawback is that it is confusing because you can rewrite the same data using more than one table and also you may drop it accidentally, thinking this data belongs to the only table and you can drop data because you do not need that table any more.
这是几个测试:
创建带有 INT 列的表:
create table T(id int);
OK
Time taken: 1.033 seconds
检查位置和其他属性:
hive> describe formatted T;
OK
# col_name data_type comment
id int
# Detailed Table Information
Database: my
Owner: myuser
CreateTime: Fri Jan 04 04:45:03 PST 2019
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://myhdp/user/hive/warehouse/my.db/t
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1546605903
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.134 seconds, Fetched: 26 row(s)
sts)
在相同位置的顶部创建第二个表格，但带有 STRING 列:
hive> create table T2(id string) location 'hdfs://myhdp/user/hive/warehouse/my.db/t';
OK
Time taken: 0.029 seconds
插入数据:
hive> insert into table T values(1);
OK
Time taken: 33.266 seconds
检查数据:
hive> select * from T;
OK
1
Time taken: 3.314 seconds, Fetched: 1 row(s)
插入第二个表:
hive> insert into table T2 values( 'A');
OK
Time taken: 23.959 seconds
检查数据:
hive> select * from T2;
OK
1
A
Time taken: 0.073 seconds, Fetched: 2 row(s)
从第一个表格中选择:
hive> select * from T;
OK
1
NULL
Time taken: 0.079 seconds, Fetched: 2 row(s)
String 被选为 NULL，因为该表被定义为具有 INT 列.
String was selected as NULL because this table is defined as having INT column.
现在将 STRING 插入第一个表(INT 列):
insert into table T values( 'A');
OK
Time taken: 84.336 seconds
惊奇，它没有失败！
插入了什么?
hive> select * from T2;
OK
1
A
NULL
Time taken: 0.067 seconds, Fetched: 3 row(s)
插入了NULL，因为在之前的插入字符串被转换为int，这导致NULL
NULL was inserted, because during previous insert string was converted to int and this resulted in NULL
现在让我们尝试删除一张表并从另一张表中进行选择:
hive> drop table T;
OK
Time taken: 4.996 seconds
hive> select * from T2;
OK
Time taken: 6.978 seconds
返回 0 行，因为第一个表是 MANAGED 并且删除表也删除了公共位置.
Returned 0 rows because first table was MANAGED and drop table also removed common location.
结束，
数据被移除了，我们需要没有数据的T2表吗?
data is removed, do We need T2 table without data in it?
drop table T2;
OK
第二个表被删除了，你看，它只是元数据.该表也被管理，drop table 也应该删除带有数据的位置，但在 HDFS 中已经没有什么可以删除的了，只删除了元数据.
Second table is removed, you see, it was metadata only. The table was also managed and drop table should remove the location with data also, but it's already nothing to remove in HDFS, only metadata was removed.
这篇关于如何使用 Hive 使用单个 hdfs 路径创建 n 个外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
