[Hadoop大数据]——Hive数据的导入导出

Hive作为大数据环境下的数据仓库工具，支持基于hadoop以sql的方式执行mapreduce的任务，非常适合对大量的数据进行全量的查询分析。

本文主要讲述下hive载cli中如何导入导出数据：

导入数据

第一种方式，直接从本地文件系统导入数据

我的本机有一个test1.txt文件，这个文件中有三列数据，并且每列都是以'\t'为分隔

[root@localhost conf]# cat /usr/tmp/test1.txt
1   a1  b1
2   a2  b2
3   a3  b3
4   a4  b

创建数据表：

>create table test1(a string,b string,c string)
>row format delimited
>fields terminated by '\t'
>stored as textfile;

导入数据：

load data local inpath '/usr/tmp/test1.txt' overwrite into table test1;

其中local inpath，表明路径为本机路径
overwrite表示加载的数据会覆盖原来的内容

第二种，从hdfs文件中导入数据

首先上传数据到hdfs中

hadoop fs -put /usr/tmp/test1.txt /test1.txt

在hive中查看test1.txt文件

hive> dfs -cat /test1.txt;
1   a1  b1
2   a2  b2
3   a3  b3
4   a4  b4

创建数据表，与前面一样。导入数据的命令有些差异:

load data inpath '/test1.txt' overwrite into table test2;

第三种，基于查询insert into导入

首先定义数据表，这里直接创建带有分区的表

hive> create table test3(a string,b string,c string) partitioned by (d string) row format delimited fields terminated by '\t' stored as textfile;
OK
Time taken: 0.109 seconds
hive> describe test3;
OK
a                       string                                      
b                       string                                      
c                       string                                      
d                       string                                      
         
# Partition Information      
# col_name              data_type               comment             
         
d                       string                                      
Time taken: 0.071 seconds, Fetched: 9 row(s)

通过查询直接导入数据到固定的分区表中：

hive> insert into table test3 partition(d='aaaaaa') select * from test2;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160823212718_9cfdbea4-42fa-4267-ac46-9ac2c357f944
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-23 21:27:21,621 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local1550375778_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 directory hdfs://localhost:8020/user/hive/warehouse/test.db/test3/d=aaaaaa/.hive-staging_hive_2016-08-23_21-27-18_739_4058721562930266873-1/-ext-10000
Loading data to table test.test3 partition (d=aaaaaa)
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 248 HDFS Write: 175 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 3.647 seconds

通过查询观察结果

hive> select * from test3;
OK
1   a1  b1  aaaaaa
2   a2  b2  aaaaaa
3   a3  b3  aaaaaa
4   a4  b4  aaaaaa
Time taken: 0.264 seconds, Fetched: 4 row(s)

PS:也可以直接通过动态分区插入数据:

insert into table test4 partition(c) select * from test2;

分区会以文件夹命名的方式存储：

hive> dfs -ls /user/hive/warehouse/test.db/test4/;
Found 4 items
drwxr-xr-x   - root supergroup          0 2016-08-23 21:33 /user/hive/warehouse/test.db/test4/c=b1
drwxr-xr-x   - root supergroup          0 2016-08-23 21:33 /user/hive/warehouse/test.db/test4/c=b2
drwxr-xr-x   - root supergroup          0 2016-08-23 21:33 /user/hive/warehouse/test.db/test4/c=b3
drwxr-xr-x   - root supergroup          0 2016-08-23 21:33 /user/hive/warehouse/test.db/test4/c=b4

第四种，直接基于查询创建数据表

直接通过查询创建数据表:

hive> create table test5 as select * from test4;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160823213944_03672168-bc56-43d7-aefb-cac03a6558bf
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-23 21:39:46,030 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local855333165_0003
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 directory hdfs://localhost:8020/user/hive/warehouse/test.db/.hive-staging_hive_2016-08-23_21-39-44_259_5484795730585321098-1/-ext-10002
Moving data to directory hdfs://localhost:8020/user/hive/warehouse/test.db/test5
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 600 HDFS Write: 466 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 2.184 seconds

查看结果

hive> select * from test5;
OK
1   a1  b1
2   a2  b2
3   a3  b3
4   a4  b4
Time taken: 0.147 seconds, Fetched: 4 row(s)

导出数据

导出到本地文件

执行导出本地文件命令:

hive> insert overwrite local directory '/usr/tmp/export' select * from test1;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160823221655_05b05863-6273-4bdd-aad2-e80d4982425d
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-23 22:16:57,028 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local8632460_0005
Moving data to local directory /usr/tmp/export
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 794 HDFS Write: 498 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 1.569 seconds
hive> 

在本地文件查看内容:

[root@localhost export]# ll
total 4
-rw-r--r--. 1 root root 32 Aug 23 22:16 000000_0
[root@localhost export]# cat 000000_0 
1
a1
b1
2
a2
b2
3
a3
b3
4
a4
b4
[root@localhost export]# pwd
/usr/tmp/export
[root@localhost export]# 

导出到hdfs

hive> insert overwrite directory '/usr/tmp/test' select * from test1;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160823214217_e8c71bb9-a147-4518-8353-81f9adc54183
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-23 21:42:19,257 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_local628523792_0004
Stage-3 is selected by condition resolver.
Stage-2 is filtered out by condition resolver.
Stage-4 is filtered out by condition resolver.
Moving data to directory hdfs://localhost:8020/usr/tmp/test/.hive-staging_hive_2016-08-23_21-42-17_778_6818164305996247644-1/-ext-10000
Moving data to directory /usr/tmp/test
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 730 HDFS Write: 498 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 1.594 seconds

导出成功，查看导出的hdfs文件

hive> dfs -cat /usr/tmp/test;
cat: `/usr/tmp/test': Is a directory
Command failed with exit code = 1
Query returned non-zero code: 1, cause: null
hive> dfs -ls /usr/tmp/test;
Found 1 items
-rwxr-xr-x   3 root supergroup         32 2016-08-23 21:42 /usr/tmp/test/000000_0


hive> dfs -cat /usr/tmp/test/000000_0;
1
a1
b1
2
a2
b2
3
a3
b3
4
a4
b4
hive> 

导出到另一个表

样例可以参考前面数据导入的部分:

insert into table test3 select * from test1;