Hive 外部表&内部表&临时表

外部表&内部表

1. hive建一个内部表，如果不加location，就会放到默认路径/user/hive

create table test(id int,name string) row format delimited fields terminated by ',';

hdfs上数据导入表test

load data inpath '/test.txt' into table test;

然后观察，hdfs上的/test.txt消失了，去hive默认路径下找，发现文件被转移到了/user/hive/warehouse/hive.db/test/test.txt

如果清空表，表依然存在/user/hive/warehouse/hive.db/test，但test下的数据文件test.txt已经没了，这就造成了数据丢失

truncate table test;

2. hive建一个内部表，指定location

create table test(id int,name string) row format delimited fields terminated by ',' location '/usr';

load data inpath '/test.txt' into table test;

发现文件转移到了/usr/test.txt

清空表，会将location目录下所有文件清空，所以要注意：当指定一个表的location为已存在路径时，要小心路径下其他文件，否则truncate表就是清空目录，drop表就是删除目录。

3. hive建外部表，不指定location

create external table test(id int,name string) row format delimited fields terminated by ',';

表依然是建立在/user/hive/warehouse/hive.db/test

导入数据

load data inpath '/test.txt' into table test;

发现数据移动到了/user/hive/warehouse/hive.db/test/test.txt

清空表，发现无法清空，因为外部表是由hdfs管理的，hive并不能管理

hive (hive)> truncate table test;
FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table test.

删除表，是可以的，但/user/hive/warehouse/hive.db/test/test.txt并不会被删除，

hive (hive)> drop table test;
OK
Time taken: 0.06 seconds

总结：

1. 外部表和内部表都可以通过location来指定表数据的存储路径，如不指定location，就会将表放在/user/Hive/warehouse/

2. 内部表可以对数据进行删除；而外部表只删除元数据，数据文件不会被删除，且外部表是不允许清空的

3. 无论外部表还是内部表，只要进行load操作，inpath路径的文件都会发生转移，移动到表的location路径下，建表时推荐使用location

4. 内部表转外部表：ALTER TABLE tb_name SET TBLPROPERTIES (‘EXTERNAL’=‘TRUE’);

4. 临时表(默认内部表)

临时表只在当前session期间存在， session一退出，表自动删除

建一个临时表

create temporary table test(id int,name string) row format delimited fields terminated by ',' location '/dwd/test';

临时表test建立在/dwd/test下了，导入数据

load data inpath '/test.txt' into table test;

数据移动到了/dwd/test/test.txt，这与前面的外部表内部表是一致的

退出当前session，再进入一个新的session，show tables发现临时表test消失了，但数据还在

再次建立临时表，并将数据导入

create temporary table test(id int,name string) row format delimited fields terminated by ',' location '/dwd/test';
load data inpath '/test.txt' into table test;

hive (hive)> load data inpath '/dwd/test/test.txt' into table test;
Loading data to table hive.test
Table hive.test stats: [numFiles=1, totalSize=18]
OK
Time taken: 0.129 seconds

建一个外部表extest，将临时表test中数据insert进去

create external table extest(id int,name string) row format delimited fields terminated by ',' location '/dwd/extest';
insert into table extest select * from test;

hive (hive)> insert into table extest select * from test;
Query ID = root_20210706144444_0264526b-1578-4874-b987-995a41203dd4
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1625549800777_0001, Tracking URL = http://node2:8088/proxy/application_1625549800777_0001/
Kill Command = /opt/hadoop-2.6.0-cdh5.15.1/bin/hadoop job  -kill job_1625549800777_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2021-07-06 14:44:39,377 Stage-1 map = 0%,  reduce = 0%
2021-07-06 14:44:44,629 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.33 sec
MapReduce Total cumulative CPU time: 1 seconds 330 msec
Ended Job = job_1625549800777_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://192.168.44.12:8020/dwd/extest/.hive-staging_hive_2021-07-06_14-44-23_037_3798864140454164758-1/-ext-10000
Loading data to table hive.extest
Table hive.extest stats: [numFiles=0, numRows=2, totalSize=0, rawDataSize=16]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.33 sec   HDFS Read: 3329 HDFS Write: 85 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 330 msec
OK
test.id	test.name
Time taken: 23.939 seconds

数据导入到了/dwd/extest/000000_0

退出session，临时表test的元数据就删除了，数据还在

再建立临时表test，但导入一份新的数据

create temporary table test(id int,name string) row format delimited fields terminated by ',' location '/dwd/test';
load data inpath '/test.txt' into table test;

hive (hive)> select * from test;
OK
test.id	test.name
1	gaohan
2	hangao
1	gaohan
2	hangao
3	hanhan

临时表中的数据不符合预期，将之前的数据也导入了，而且临时表路径下的数据文件有前后两份

/dwd/test

Permission	Owner	Group	Size	Last Modified	Replication	Block Size	Name
-rwxr-xr-x	root	supergroup	18 B	Tue Jul 06 14:43:52 +0800 2021	1	128 MB	test.txt
-rwxr-xr-x	root	supergroup	27 B	Tue Jul 06 14:47:40 +0800 2021	1	128 MB	test_copy_1.txt

解决方案：临时表使用完毕后，truncate清空临时表目录

truncate table test;

这样下一次建立临时表，就不会影响下一次临时表的数据了，然后就保证insert到正式表的数据是对的。

5. 外部临时表

建一个外部临时表，导入数据

create temporary external table test(id int,name string) row format delimited fields terminated by ',' location '/dwd/test';
load data inpath '/test.txt' into table test;

向上面一样，临时表使用完毕后，执行清空表，同样，外部表是无法清空的

hive (hive)> truncate table test;
FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table test.
hive (hive)> drop table test;
OK
Time taken: 0.036 seconds

尝试删除表，是可以的，但目录下依然存在着文件。这就没意义了，本来清空表就是要清空目录，所以建立临时表时一定不要用外部表。