數(shù)據(jù)導(dǎo)入
向表中裝載數(shù)據(jù)(Load)
語法
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table table_name [partition (partcol1=val1,…)];
- load data:表示加載數(shù)據(jù)
- local:表示從本地加載數(shù)據(jù)到hive表（復(fù)制）；否則從HDFS加載數(shù)據(jù)到hive表（移動）
- inpath:表示加載數(shù)據(jù)的路徑
- overwrite into:表示覆蓋表中已有數(shù)據(jù)箩祥，否則表示追加
- into table:表示加載到哪張表
- student:表示具體的表
- partition:表示上傳到指定分區(qū)
案例
- 準(zhǔn)備數(shù)據(jù)
新建student1.txt和student2.txt两芳，輸入以下內(nèi)容
student1.txt
1 zhao 18
2 jun 19
student2.txt
3 feng 17
4 xiang 16
5 bin 15
將student2.txt 上傳到hdfs
hadoop fs -put /opt/module/datas/student2.txt /
- 開始操作
-- 創(chuàng)建student表
create table if not exists student(
id int,
name string,
age int
)
partitioned by(year string)
row format delimited fields terminated by '\t';
-- 加載本地文件到hive
load data local inpath '/opt/module/datas/student.txt'into table student partition(year='2017-2018');
-- 加載hdfs上的文件
load data inpath '/student.txt' into table student partition(year='2017-2018');
-- 覆蓋上傳
load data local inpath '/opt/module/datas/student.txt' overwrite into table student partition(year='2017-2018');
通過查詢語句向表中插入數(shù)據(jù)(Insert)
還是上面那張表
-- 基本插入數(shù)據(jù)
insert into table student partition(year='2017-2018') values(11, 'zzz',10);
-- 根據(jù)單張表的查詢結(jié)果插入數(shù)據(jù)
insert into table student partition(year='2018-2019') select id,name,age from student where year='2017-2018';
-- 根據(jù)多張表的查詢結(jié)果插入數(shù)據(jù)
insert into table student partition(year='2019-2020')
select id,name,age from student where year='2017-2018'
union
select id,name,age from student where year='2018-2019';
from student
insert into table student partition(year='2020-2021')
select id,name,age where year in ('2017-2018','2018-2019','2019-2020');
查詢語句中創(chuàng)建表并加載數(shù)據(jù)（As Select）
根據(jù)查詢結(jié)果創(chuàng)建表（查詢的結(jié)果會添加到新創(chuàng)建的表中）
create table if not exists student1 as select id,name,age from student where year in ('2017-2018','2018-2019','2019-2020','2020-2021');
創(chuàng)建表的時候通過Location指定加載數(shù)據(jù)路徑
create table if not exists student2(
id int, name string, age int
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student2';
dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student2/;
select * from student2;
Import數(shù)據(jù)到指定hive表中
先使用export導(dǎo)出后，再將數(shù)據(jù)導(dǎo)入
數(shù)據(jù)導(dǎo)出
insert導(dǎo)出
-- 將查詢結(jié)果導(dǎo)出到本地
insert overwrite local directory '/opt/module/datas/output/student1' select * from student;
-- 將查詢結(jié)果格式化導(dǎo)出到本地
insert overwrite local directory '/opt/module/datas/output/student2' row format delimited fields terminated by '\t' select * from student;
-- 將查詢結(jié)果格式化導(dǎo)出到HDFS
insert overwrite directory '/output/student1' row format delimited fields terminated by '\t' select * from student;
hadoop命令導(dǎo)出到本地
dfs -get /user/hive/warehouse/student1/000000_0 /opt/module/datas/output/student.txt
hive shell命令導(dǎo)出
基本語法：（hive -f/-e 執(zhí)行語句或者腳本 > file）
hive -e 'select * from default.student' > student1.txt
export導(dǎo)出到hdfs上
export table student to '/output/student3/';
sqoop導(dǎo)出
這個后面會寫文章詳細(xì)講述
清除表中數(shù)據(jù)（Truncate）
Truncate只能刪除管理表迈着，不能刪除外部表中數(shù)據(jù)
truncate table student;
