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，外部分区表真的很重要，刷分区也很重要。

标签：

相关文章