Hive内部表、外部表、分区表以及外部分区表创建以及导入数据实例讲解

时间:2021-05-24 06:39:26

源数据格式:

[dd_edw@BJHC-Client-144113 zhang_dd_edw]$ more data.txt
25502#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

1. 内部表的创建以及数据的导入(重点:无论是内部表还是外部表,数据最终都会被移动到所使用的location下面,如果没有指定,就默认移动到hive-site.xml中配置的location) 
创建表的语句:

create table 10k_cold_data_inner_table( size string comment '冷数据大小', path string comment '冷数据路径' ) row format delimited fields terminated by '#';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

数据的导入:

hive> load data local inpath '/home/dd_edw/zhang_dd_edw/data.txt' into table 10k_cold_data_inner_table  ;
Loading data to table bdm.10k_cold_data_inner_table
Table bdm.10k_cold_data_inner_table stats: [numFiles=1, totalSize=474] OK Time taken: 0.725 seconds
  • 1
  • 2
  • 3
  • 4
  • 5

数据的查询:

hive> select *
    > from 10k_cold_data_inner_table;
OK
25502   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
Time taken: 0.27 seconds, Fetched: 6 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

2、外部表的创建以及数据的导入 
外部表的创建:

create external table 10k_cold_data_external_table( size string comment '冷数据大小', path string comment '冷数据路径' ) row format delimited fields terminated by '#';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

导入数据:

hive> load data local inpath '/home/dd_edw/zhang_dd_edw/data.txt' into table 10k_cold_data_external_table  ;
Loading data to table bdm.10k_cold_data_external_table
Table bdm.10k_cold_data_external_table stats: [numFiles=1, totalSize=474] OK Time taken: 0.622 seconds
  • 1
  • 2
  • 3
  • 4
  • 5

数据查看:

hive> select *
    > from 10k_cold_data_external_table;
OK
25502   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
Time taken: 0.265 seconds, Fetched: 6 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

在Hive当中,对于外部表来说,我们在删除表数据的时候,删除的仅仅是表的元数据信息,但是却不能删除掉表中的真实数据。 
接下来我们测试一下:

hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
hive> drop table 10k_cold_data_external_table;
OK
Time taken: 0.152 seconds
hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

当然,对于外部表来说,我们还有很多玩法,如下: 
源数据路径:

[dd_edw@BJHC-Client-144113 zhang_dd_edw]$ hadoop fs -cat hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
25502#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

创建外部表:直接去关联这个HDFS路径

create external table 10k_cold_data_external_table_2( size string comment '冷数据大小', path string comment '冷数据路径' ) row format delimited fields terminated by '#' location 'hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

查看数据:

hive> select *
    > from 10k_cold_data_external_table_2;
OK
25502   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
Time taken: 0.085 seconds, Fetched: 6 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

当然,我们在这里面再次删除表的元数据信息,发现在HDFS中的真实数据还是存在:

hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
hive> drop table 10k_cold_data_external_table_2;
OK
Time taken: 0.068 seconds
hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

接下来我们在换一种玩法:(在这里我们要弄懂一件事情:什么时候回移动数据,什么时候不会移动数据==>如果建表的时候指定了location,则不会移动,如果不指定,则向默认的HDFS路径下面移动数据),对于下面的建表方式就会产生移动数据。 
第一:我们先查看源数据的路径:

hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table ;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt
hive> dfs -cat hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table/data.txt;
25502#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605#hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

第二:我们创建一个外部表

hive> create external table  10k_cold_data_external_table_2(    size string comment '冷数据大小',    path string comment '冷数据路径')
    > row format delimited
    > fields terminated by '#';
OK
Time taken: 0.054 seconds
  • 1
  • 2
  • 3
  • 4
  • 5

第三:我们向这个表当中导入数据

hive> load data inpath 'hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table' into table 10k_cold_data_external_table_2;
Loading data to table bdm.10k_cold_data_external_table_2
Table bdm.10k_cold_data_external_table_2 stats: [numFiles=1, totalSize=474] OK Time taken: 0.297 seconds
  • 1
  • 2
  • 3
  • 4
  • 5

第四:查看数据

hive> select *
    > from 10k_cold_data_external_table_2;
OK
25502   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15
25499   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16
25519   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17
25538   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18
25555   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19
25605   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21
Time taken: 0.125 seconds, Fetched: 6 row(s)
hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table;
hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table_2 ;
Found 1 items
-rwxr-xr-x   3 dd_edw dd_edw        474 2018-01-08 13:04 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_table_2/data.txt
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

很明显,数据已经被移动过来了。 
总结一下:无论内部表还是外部表,只要load data,数据都会被移动到表的location下面,(之前书中介绍的我感觉指的是一般人在建表的时候没人指定location,删除内部表的时候,表所关联的文件夹和数据都会别删除)。

3、外部分区表的建立以及导入数据 
这里是重点。 
首先我们先创建一个外部表:

create external table 10k_cold_data_external_partitions( size string comment '冷数据大小', path string comment '冷数据路径' ) partitioned by(dt string) row format delimited fields terminated by '#';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

当我们在这个目录下面创建dt=2018-01-08这个目录之后,我们去查看一下表的分区信息:

hive> dfs -ls hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_partitions ;
Found 1 items
drwxr-xr-x   - dd_edw dd_edw          0 2018-01-08 15:16 hdfs://ns2/user/dd_edw/warehouse/bdm/10k_cold_data_external_partitions/dt=2018-01-08
hive> show partitions 10k_cold_data_external_partitions;
OK
Time taken: 0.048 seconds
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

呵呵,没有,你知道这是为什么吗?这是因为虽然在HDFS当中数据已经存在了,但是在Hive的元数据仓库中并没有,所以我们查不到数据。 
接下来我们添加相应的分区:

hive> alter table 10k_cold_data_external_partitions add partition(dt='2018-01-08');
OK
Time taken: 0.078 seconds
hive> show partitions 10k_cold_data_external_partitions;
OK
dt=2018-01-08
Time taken: 0.064 seconds, Fetched: 1 row(s)
hive> select *
    > from 10k_cold_data_external_partitions;
OK
25502   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-15        2018-01-08
25499   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-16        2018-01-08
25519   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-17        2018-01-08
25538   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-18        2018-01-08
25555   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-19        2018-01-08
25605   hdfs://ns1/user/dd_edw/adm.db/adm_dealer_order_list_di_big/dt=2015-07-21        2018-01-08
Time taken: 0.08 seconds, Fetched: 6 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

但是问题来了,如果有100个分区呢?难道也需要手动加100个分区吗? 
这里有一个简单的命令操作:msck repair table table_name 即可。 
关于更多细节:大家可以参考这个:http://blog.csdn.net/opensure/article/details/51323220 
OK,外部分区表真的很重要,刷分区也很重要。