hive-4 hive数据的导入和导出

Hive数据导入

直接向表中插入(Insert)数据

不推荐使用：每个insert语句产生一个MR任务，大量的insert语句会产生大量的小文件

hive> create table score3 like score;
hive> insert into table score3
 partition(month ='2200601') values ('001','002','100');

通过load加载数据

filepath:

• 相等路径: project/data1
• 绝对路径:/user/hive/project/data1
• URI:hdfs://namenode:9000/user/hive/project/data1

overwrite

• 指定overwrite，新数据会覆盖旧数据
• 不指定overwrite，新数据会追加到旧数据后

#语法
 hive> load data [local] inpath 'dataPath' 
 [overwrite]into table student [partition (partcol1=val1,…)]; 

 # 示例
 # 从本地上传文件，上传操作成功后，本地文件仍然存在。类似拷贝
 hive> load data local inpath '/hivedatas/score.csv' 
 overwrite into table score3 
 partition(month='201806');
 # 从HDFS上传文件，上传后HDFS,指定的HDFS路径上的文件从该路径小时。类似剪切
 hive> load data inpath '/hivedatas/score.csv' 
 overwrite into table 
 score3 partition(month='201806');
 

通过查询加载数据

hive> create table score5 like score;
hive> insert overwrite table score5 
partition(month = '202006') 
select s_id,c_id,s_score from score;

查询语句中创建表并加载数据

CREATE TABLE new_key_value_store
   ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
   STORED AS RCFile
   AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

创建表时指定Location

# 从HDFS中查询数据并加载到新建的表中
hive> create external table score7 (s_id string,c_id string,s_score int) 
row format delimited fields terminated by '\t' 
location '/myscore7';

export导出与import导入hive表数据(内部表操作)

hive> create table teacher2 like teacher;
hive> export table teacher to  '/teacher';
hive> import table teacher2 from '/teacher';

Hive数据导出

insert导出

• 将查询到的结果导出到本地

insert overwrite local directory '/hivedatas/stu' select * from stu;

• 将查询的结果格式化导出到本地

insert overwrite local directory '/hivedatas/stu2' 
row format delimited fields terminated by ',' select * from stu;

• 将查询的结果导出到HDFS上

insert overwrite directory '/hivedatas/stu' 
row format delimited fields terminated by  ','  select * from stu;

hive shell命令导出

• hive -e ‘sql语句’ > file

hive -e 'select * from hivetest.stu;' > /hivedatas/student1.txt

• hive -f sql文件 > file

hive -f ./hive.sql > /hivedatas/student1.txt
# hive1.0后支持参数传递
# 更多参数传递搜索hiveconf、hiveevar、define

export导出到HDFS上

export table  hivetest.stu to '/hivedatas/stuexport';