hiveQL 外部表

来源：互联网发布：知乎正常感情浓度编辑：程序博客网时间：2024/04/26 22:47

1、创建外部表

hive> create external table ext_t (name string)

> location '/user/grid/ext_t';
OK
Time taken: 5.494 seconds
使用external 关键字后，hive知道数据并不是由自己管理的，因此不会吧数据移动到自己的目录，即：在表定义时，甚至不会去检查这个外部位置是否存在

2、load data
可以有两种方式：
方式一：load data inpath '/user/grid/aa' into table ext_t;

方式二：直接拷贝数据到目录
[grid@h1 ~]$ hadoop dfs -cat /user/grid/aa
0136835990999992013010100004-24050-046267FM-12+010099999V0209999C000019999999N0200001N1+02801+02531102021ADDAY121061AY221061MD1410001+9999MW1021REMSYN088AAXX 01004 83599 31/98 /0000 10280 20253 40202 54000 70222 222// 20501 80260 333 10290;
0166835990999992013010112004-24050-046267FM-12+010099999V0202501N000512200019N0200001N1+03001+02741101381ADDAY101061GF103991031011002501021021MD1210101+9999MW1031REMSYN100AAXX 01124 83599 31398 32501 10300 20274 40138 52010 7030/ 83122 222// 02250 20501 80280 333 20300;
0171835990999992013010118004-24050-046267FM-12+010099999V0209999C000010120019N0040001N1+03201+02951101691ADDAY111061AY201061GF106991061041012501031021MD1610101+9999MW1031UA1M050059999REMSYN084AAXX 01184 83599 31696 60000 10320 20295 40169 56010 70310 86432 222// 20501 80300;
0136835990999992013010200004-24050-046267FM-12+010099999V0200601N001019999999N0200001N1+02501+02361102171ADDAY151061AY221061MD1110101+9999MW1021REMSYN088AAXX 02004 83599 31/98 /0602 10250 20236 40217 51010 70252 222// 20502 80240 333 10280;
0185835990999992013010212004-24050-046267FM-12+010099999V0203301N001010120019N0040001N1+02501+02361101821ADDAY161061GF108991999051012501071999MD1410001+9999MW1031REMSYN100AAXX 02124 83599 31696 83302 10250 20236 40182 54000 7036/ 8857/ 222// 02230 20502 80240 333 20250;EQDQ01+000082SCOTLC
0190835990999992013010218004-24050-046267FM-12+010099999V0200401N001510006019N0020001N1+02301+02161102281ADDAY161061AY251061GF108991999061000751071999MD1110101+9999MW1211UA1M050159999REMSYN084AAXX 02184 83599 41195 80403 10230 20216 40228 51010 72165 8867/ 222// 20503 80220;EQDQ01+000082SCOTLC
0136835990999992013010300004-24050-046267FM-12+010099999V0203301N001019999999N0040001N1+02101+01961102481ADDAY161061AY251061MD1110101+9999MW1201REMSYN088AAXX 03004 83599 31/96 /3302 10210 20196 40248 51010 72065 222// 20503 80200 333 10230;
0190835990999992013010318004-24050-046267FM-12+010099999V0203501N001510006019N0100001N1+02401+02121102631ADDAY161061AY261061GF108991999061000751071999MD1610101+9999MW1031UA1M050109999REMSYN084AAXX 03184 83599 31197 83503 10240 20212 40263 56010 70366 8867/ 222// 20502 80220;EQDQ01+000082SCOTLC

[grid@h1 ~]$ hadoop dfs -mv /user/grid/aa /user/grid/ext_t
hive> select * from ext_t;
OK
0136835990999992013010100004-24050-046267FM-12+010099999V0209999C000019999999N0200001N1+02801+02531102021ADDAY121061AY221061MD1410001+9999MW1021REMSYN088AAXX 01004 83599 31/98 /0000 10280 20253 40202 54000 70222 222// 20501 80260 333 10290;
0166835990999992013010112004-24050-046267FM-12+010099999V0202501N000512200019N0200001N1+03001+02741101381ADDAY101061GF103991031011002501021021MD1210101+9999MW1031REMSYN100AAXX 01124 83599 31398 32501 10300 20274 40138 52010 7030/ 83122 222// 02250 20501 80280 333 20300;
0171835990999992013010118004-24050-046267FM-12+010099999V0209999C000010120019N0040001N1+03201+02951101691ADDAY111061AY201061GF106991061041012501031021MD1610101+9999MW1031UA1M050059999REMSYN084AAXX 01184 83599 31696 60000 10320 20295 40169 56010 70310 86432 222// 20501 80300;
0136835990999992013010200004-24050-046267FM-12+010099999V0200601N001019999999N0200001N1+02501+02361102171ADDAY151061AY221061MD1110101+9999MW1021REMSYN088AAXX 02004 83599 31/98 /0602 10250 20236 40217 51010 70252 222// 20502 80240 333 10280;
0185835990999992013010212004-24050-046267FM-12+010099999V0203301N001010120019N0040001N1+02501+02361101821ADDAY161061GF108991999051012501071999MD1410001+9999MW1031REMSYN100AAXX 02124 83599 31696 83302 10250 20236 40182 54000 7036/ 8857/ 222// 02230 20502 80240 333 20250;EQDQ01+000082SCOTLC
0190835990999992013010218004-24050-046267FM-12+010099999V0200401N001510006019N0020001N1+02301+02161102281ADDAY161061AY251061GF108991999061000751071999MD1110101+9999MW1211UA1M050159999REMSYN084AAXX 02184 83599 41195 80403 10230 20216 40228 51010 72165 8867/ 222// 20503 80220;EQDQ01+000082SCOTLC
0136835990999992013010300004-24050-046267FM-12+010099999V0203301N001019999999N0040001N1+02101+01961102481ADDAY161061AY251061MD1110101+9999MW1201REMSYN088AAXX 03004 83599 31/96 /3302 10210 20196 40248 51010 72065 222// 20503 80200 333 10230;
0190835990999992013010318004-24050-046267FM-12+010099999V0203501N001510006019N0100001N1+02401+02121102631ADDAY161061AY261061GF108991999061000751071999MD1610101+9999MW1031UA1M050109999REMSYN084AAXX 03184 83599 31197 83503 10240 20212 40263 56010 70366 8867/ 222// 20502 80220;EQDQ01+000082SCOTLC

Time taken: 0.38 seconds, Fetched: 8 row(s)

3、drop 外部表时，hive不会碰数据
hive> drop table ext_t;
OK
[grid@h1 ~]$ hadoop dfs -ls /user/grid/ext_t
Found 1 items
-rw-r--r-- 2 grid supergroup 2158 2013-08-29 04:47 /user/grid/ext_t/aa

4、再次创建这个外部表数据依旧存在
hive> create external table ext_t (name string)
> location '/user/grid/ext_t';
OK
Time taken: 0.067 seconds
hive> select * from ext_t;
OK
0136835990999992013010100004-24050-046267FM-12+010099999V0209999C000019999999N0200001N1+02801+02531102021ADDAY121061AY221061MD1410001+9999MW1021REMSYN088AAXX 01004 83599 31/98 /0000 10280 20253 40202 54000 70222 222// 20501 80260 333 10290;
0166835990999992013010112004-24050-046267FM-12+010099999V0202501N000512200019N0200001N1+03001+02741101381ADDAY101061GF103991031011002501021021MD1210101+9999MW1031REMSYN100AAXX 01124 83599 31398 32501 10300 20274 40138 52010 7030/ 83122 222// 02250 20501 80280 333 20300;
0171835990999992013010118004-24050-046267FM-12+010099999V0209999C000010120019N0040001N1+03201+02951101691ADDAY111061AY201061GF106991061041012501031021MD1610101+9999MW1031UA1M050059999REMSYN084AAXX 01184 83599 31696 60000 10320 20295 40169 56010 70310 86432 222// 20501 80300;
0136835990999992013010200004-24050-046267FM-12+010099999V0200601N001019999999N0200001N1+02501+02361102171ADDAY151061AY221061MD1110101+9999MW1021REMSYN088AAXX 02004 83599 31/98 /0602 10250 20236 40217 51010 70252 222// 20502 80240 333 10280;
0185835990999992013010212004-24050-046267FM-12+010099999V0203301N001010120019N0040001N1+02501+02361101821ADDAY161061GF108991999051012501071999MD1410001+9999MW1031REMSYN100AAXX 02124 83599 31696 83302 10250 20236 40182 54000 7036/ 8857/ 222// 02230 20502 80240 333 20250;EQDQ01+000082SCOTLC
0190835990999992013010218004-24050-046267FM-12+010099999V0200401N001510006019N0020001N1+02301+02161102281ADDAY161061AY251061GF108991999061000751071999MD1110101+9999MW1211UA1M050159999REMSYN084AAXX 02184 83599 41195 80403 10230 20216 40228 51010 72165 8867/ 222// 20503 80220;EQDQ01+000082SCOTLC
0136835990999992013010300004-24050-046267FM-12+010099999V0203301N001019999999N0040001N1+02101+01961102481ADDAY161061AY251061MD1110101+9999MW1201REMSYN088AAXX 03004 83599 31/96 /3302 10210 20196 40248 51010 72065 222// 20503 80200 333 10230;
0190835990999992013010318004-24050-046267FM-12+010099999V0203501N001510006019N0100001N1+02401+02121102631ADDAY161061AY261061GF108991999061000751071999MD1610101+9999MW1031UA1M050109999REMSYN084AAXX 03184 83599 31197 83503 10240 20212 40263 56010 70366 8867/ 222// 20502 80220;EQDQ01+000082SCOTLC
Time taken: 0.216 seconds, Fetched: 8 row(s)