1.hive加载数据
1.1、load方式加载数据
两种:1、load data local inpath "/home/hadoop/student.txt" into table student;
2、load data inpath "/appstudent/student.txt" into table student;
3、hadoop fs -put /home/hadoop/student.txt /user/hive/warehouse/myhive.db/student
1.2、insert方法加载数据
五种:1、单个insert插入
insert into table (id, name , sex, age ,department) values (1,2,3,4,5),(1,2,3,4,5);
2、insert .... select ....
insert into table (id, name, sex, age, department) select id,name, sex,age, department from student;
3、多重插入
from student
insert into table ptn_student partition (department = 'CS') select * where department = 'CS'
insert into table ptn_student partition (department = 'IS') select * where department = 'IS'
4、分区插入
分两种:
1、静态分区插入
先添加分区:alter table ptn-student add partition (city="bj");
加载数据;load data local inpath "studnet.txt" into table pnt_student parition(city="bj");
2、动态分区
两个条件:
1、开启动态分区的开关
set hive.exec.dynamic.partition=true;
2、默认的动态分区的模式为严格模式
在严格模式下,进行动态分区插入要求至少要有一列静态分区,并且静态分区必须在前面
set hive.exec.dynamic.partition.mode = nonstrict;
动态分区插入的语句:
insert into table ptn_student partition(city) select id,name,sex,city from student;
注意:动态分区的字段必须是select的最后一个字段
2.insert方式导出数据
分两种:1、导出到本地;
insert overwrite local direcotry "/home/hadoop/appstudent" select * from student;
2、导出到HDFS:
insert overwrite direcotry "/appstudent" select * from student;
insert overwrite direcotry "hdfs://hadoop02:9000/appstudent" select * from student;
3、hive表的数据进行备份:
export : 导出备份
import : 载入备份