[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 
1a1b1
2a2b2
3a3b3
4a4b4
[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;
1a1b1
2a2b2
3a3b3
4a4b4
hive> 

导出到另一个表

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

insert into table test3 select * from test1;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Albert陈凯

Spark对比Hadoop MapReduce 的优势

与Hadoop MapReduce相比,Spark的优势如下: ❑ 中间结果:基于MapReduce的计算引擎通常将中间结果输出到磁盘上,以达到存储和容错的目...

31940
来自专栏Albert陈凯

3.5RDD的容错机制

3.5 RDD的容错机制 RDD实现了基于Lineage的容错机制。RDD的转换关系,构成了compute chain,可以把这个compute chain认...

29680
来自专栏北京马哥教育

Spark:一个高效的分布式计算系统

马哥linux运维 | 最专业的linux培训机构 ---- 概述 什么是Spark Spark是UC Berkeley AMP lab所开源的类Hado...

48860
来自专栏大数据学习笔记

Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.3 Hive 快速入门)

第11章 Hive:SQL on Hadoop 11.3 Hive快速入门 11.3.1 HQL介绍 Hive查询语言(Hive QL,可以简称HQL)的语法和...

280100
来自专栏蓝天

启动Hadoop HDFS时的“Incompatible clusterIDs”错误原因分析

Hadoop HDFS时的“Incompatible clusterIDs”错误原因分析.pdf

7310
来自专栏Albert陈凯

Spark系列课程-00xxSpark任务调度疑问,生成有向无环图的这个东西叫什么名字?

下面我们一起来看一下Spark的任务调度 ? Spark任务调度.png 首先最左边的叫做RDD Object就是一个一个的RDD对象 一个一个的RDD对象,...

462140
来自专栏行者悟空

Spark RDD中的持久化

14230
来自专栏智能计算时代

「大数据系列」:Apache Hive 分布式数据仓库项目介绍

Apache Hive™数据仓库软件有助于读取,编写和管理驻留在分布式存储中的大型数据集并使用SQL语法进行查询

60720
来自专栏蓝天

强制DataNode向NameNode上报blocks

正常情况下,什么时候上报blocks,是由NameNode通过回复心跳响应的方式触发的。 一次机房搬迁中,原机房hadoop版本为2.7.2,新机房版本为2....

15420
来自专栏肖力涛的专栏

Spark踩坑记:Spark Streaming+kafka应用及调优

本文首先对spark streaming嵌入kafka的方式进行归纳总结,之后简单阐述Spark streaming+kafka 在舆情项目中的应用,最后将自己...

5.8K30

扫码关注云+社区

领取腾讯云代金券