Hive中数据导入或导出
程序员文章站
2022-05-09 11:14:50
...
1.将数据导入hive
首先创建hive表,SQL如下:
CREATE TABLE test(
name string,
age int,
address string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
1.1 从本地导入
linux 中有hive_test.txt 文件,数据格式如下:
zhangsan1,12,aaa1
zhangsan2,13,aaa2
zhangsan3,14,aaa3
zhangsan4,15,aaa4
zhangsan5,16,aaa5
导入命令如下:
load data local inpath '/data/hive_test.txt' into table test;
效果如下图所示:
1.2 从hdfs上导入数据
首先上传本地文件到hdfs中
hdfs dfs -mkdir /recommend/hive
hadoop fs -put hive_test.txt /recommend/hive
效果如图所示:
导入命令如下:
load data inpath '/recommend/hive/hive_test.txt' into table test;
tips:注意此方式会把hdfs上的文件直接移动到 "hdfs://dcnode1:8020/user/hive/warehouse/xxx.db/test/"目录下
1.3 load data 时使用 overwrite 方式
本地文件导入命令如下:
load data local inpath '/data/hive_test.txt' overwrite into table test;
hdfs文件导入命令如下:
load data inpath '/recommend/hive/hive_test.txt' overwrite into table test;
1.4 子查询方式
create table test_2 as select * from test;
1.5 insert into,和上面类似
insert into table test_2 select * from test;
1.6 location
适合创建外部表关联hdfs上的数据。文件在hdfs上路径:/recommend/hive/hive_test.txt
1.6.1 创建外部表时,直接指定location:
CREATE EXTERNAL TABLE `test`(
`name` string,
`age` int,
`address` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/recommend/hive/';
1.6.2 先创建表,然后修改location:
CREATE EXTERNAL TABLE `test`(
`name` string,
`age` int,
`address` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
alter table test set location '/recommend/hive/';
2. 将数据从hive里导出
2.1 保存到本地
insert overwrite local directory '/data/hive_test_copy' select * from test;
在linux上查看导出文件:
可以看出没有分隔符,显然不是我们想要的效果。改进如下:
insert overwrite local directory '/data/hive_test_copy' row format delimited fields terminated by ',' select * from test;
在linux上查看导出文件,完全按照我们设置的格式导出,Wow!!:
2.2 保存到HDFS上
insert overwrite directory '/recommend/hive' row format delimited fields terminated by ',' select * from test;
上一篇: 快把眼镜带上