持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第17天，点击查看活动详情

操作

表的增删改

--  修改表
create table modifyTest (
    id int,
    name string
)
row format delimited fields terminated by '\t';

--- 重命名
alter table modifyTest rename to modifyTest1;
--- 查询表结构
desc modifyTest1;
--- 添加列
alter table modifyTest1 add columns (age string, sex int);
-- 更新列
alter table modifyTest1 change column sex ssex string;
-- 删除列
drop table modifyTest1;
--- 清空表数据
truncate table modifyTest1;

数据插入

--- 加载数据
create table modifyTest1 (
    id int,
    name string,
    age int
)
partitioned by (dt string)
row format delimited fields terminated by '\t';

通过insert into 方式添加数据

-- 通过insert into 方式添加数据
insert into table modifyTest1 partition (dt='2022-10-26') values (1,'zhangsan','18');
select * from modifyTest1;

通过查询的方式添加数据

-- 通过查询的方式添加数据
create table modifyTest2 (
    id int,
    name string,
    age int
);
insert into modifyTest2 values (2,"lisi","19"), (3, "王五","20");
insert overwrite table modifyTest1 partition (dt='2022-10-27')
select id, name, age from modifyTest2;

select * from modifyTest1;

load 方式添加数据

--- load 方式添加数据
load data local inpath '/export/data/hivedatas/modifyTest2.txt' overwrite into table modifyTest1 partition (dt='2022-10-28');

插入多表模式

--- 创建第一张表
create table modifyTest_first(
    id int,
    name string,
    age int
)
partitioned by (dt string)
row format delimited fields terminated by '\t';
--- 创建第二张表
create table modifyTest_second(
    id int,
    name string,
    age int
)
partitioned by (dt string)
row format delimited fields terminated by '\t';

---分别给两张表加载数据
from modifyTest1
insert overwrite table modifyTest_first partition (dt='2022-10-24')
select id, name, age
insert overwrite table modifyTest_second partition (dt='2022-10-23')
select id, name, age;

select * from modifyTest_first;
select * from modifyTest_second;

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

-- 查询语句中创建表并加载数据 as select
create table modifyTest3 as select * from modifytest1;

通过location指定加载数据路径

-- 通过location指定加载数据路径
drop table modifytest4;
create external table modifytest4 (
    id int,
    name string,
    age int
)
row format delimited fields terminated by '\t'
location '/modifyTest2';
-- 2）上传数据到hdfs上
-- hadoop fs -mkdir -p /modifyTest2
-- hadoop fs -put modifyTest2.txt  /modifyTest2;
-- 3）查询数据
select * from modifytest4;

数据导出

insert导出

--- insert导出
insert overwrite local directory '/export/data/hivedatas/modifytest4' select * from modifytest4;

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

-- 将查询的结果格式化导出到本地
insert overwrite local directory '/export/data/hivedatas/modifytest1' row format delimited fields terminated by '\t'
select *
from modifytest1;

将查询的结果导出到HDFS上(没有local)

-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/exporthive' row format delimited fields terminated by '\t'
select * from modifytest1;

shell 导出

hive -e "select * from modifytest1;" > /export/data/exporthive/modifytest1.txt

export导出到HDFS上

-- export导出到HDFS上
export table modifytest1 to '/export/exporthive/modifytest1';

hive 修改-导入导出操作

操作

表的增删改

数据插入

数据导出