Hive数据的上传（Load、Insert）和下载

一、Load加载数据

• 从本地加载数据

  LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;
  

• 从HDFS加载数据

  LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
  

• 从HDFS加载数据到分区表并制定分区

  LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS_p partition(country ="CHina");
  

二、Insert插入数据

• 将查询结果插入到表中

  insert into table student_from_insert select num,name from student;
  

• 多重插入，将数据插入到多个表中

  from student
  insert overwrite table student_insert1
  select num
  insert overwrite table student_insert2
  select name;
  

• 动态分区插入

  # 开启动态分区功能
  set hive.exec.dynamic.partition = true;
  # 设置动态分区模式为非严格模式
  set hive.exec.dynamic.partition.mode = nonstrict;
  # 执行插入操作
  insert into table student_partition partition(Sdept)
  select Sno,Sname,Sex,Sage,Sdept from student;
  

三、数据的导出

• 导出查询结果到HDFS指定目录下

  insert overwrite directory '/tmp/hive_export/e1' select * from student;
  

• 导出时指定分隔符和文件存储方式

  insert overwrite directory '/tmp/hive_export/e2' 
  row format delimited fields terminated by ','
  stored as orc
  select * from student;
  

• 导出数据到本地

  insert overwrite local directory '/root/hive_export/e1' select * from student;