hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] | into table student
[partition (partcol1=val1,…)];
(1)load data:表示加载数据 (2)local:表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表 (3)inpath:表示加载数据的路径 (4)overwrite:表示覆盖表中已有数据,否则表示追加 (5)into table:表示加载到哪张表 (6)student:表示具体的表 (7)partition:表示上传到指定分区
hive (default)> create table student(id string, name string) row
format delimited fields terminated by '\t';
(1)加载本地文件到 hive
hive (default)> load data local inpath
'/opt/module/datas/student.txt' into table default.student;
(2)加载 HDFS 文件到 hive 中 上传文件到 HDFS
hive (default)> dfs -put /opt/module/datas/student.txt
/user/atguigu/hive;
加载 HDFS 上数据
hive (default)> load data inpath
'/user/atguigu/hive/student.txt' into table default.student;
(3)加载数据覆盖表中已有的数据 上传文件到 HDFS
hive (default)> dfs -put /opt/module/datas/student.txt
/user/atguigu/hive;
加载数据覆盖表中已有的数据
hive (default)> load data inpath
'/user/atguigu/hive/student.txt' overwrite into table
default.student;
hive (default)> create table student(id int, name string)
partitioned by (month string) row format delimited fields
terminated by '\t';
hive (default)> insert into table student
partition(month='201709') values(1,'wangwu');
hive (default)> insert overwrite table student
partition(month='201708')
select id, name from student where month='201709';
hive (default)> from student
insert overwrite table student
partition(month='201707')
select id, name where month='201709'
insert overwrite table student
partition(month='201706')
select id, name where month='201709';
根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student3
as select id, name from student;
hive (default)> create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
2. 上传数据到 hdfs 上 hive (default)> dfs -put /opt/module/datas/student.txt
/user/hive/warehouse/student5;
hive (default)> select * from student5;
注意:先用 export 导出后,再将数据导入。
hive (default)> import table student2 partition(month='201709')
from
'/user/hive/warehouse/export/student';
hive (default)> insert overwrite local directory
'/opt/module/datas/export/student'
select * from student;
hive(default)>insert overwrite local directory
'/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
hive (default)> insert overwrite directory
'/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
hive (default)> dfs -get
/user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;
基本语法:(hive -f/-e 执行语句或者脚本 > file)
[atguigu@hadoop102 hive]$ bin/hive -e 'select * from
default.student;' >
/opt/module/datas/export/student4.txt;
(defahiveult)> export table default.student to
'/user/hive/warehouse/export/student';
后续课程专门讲。
注意:Truncate 只能删除管理表,不能删除外部表中数据
hive (default)> truncate table student;