【HIVE SQL】HIVE分区表和非分区的建立、删除、覆盖写入等操作

一、非分区表

1. 建表和数据写入

-- 表的建立 无备注

create table  table db_name.table_name2 as  

select * from table db_name.table_name1

 

--整张表的覆盖写入 非分区表

INSERT OVERWRITE TABLE db_name.table_name1

select * from db_name.table_name2

（会保留表的字段备注）

 

2.表的删除和数据清空

--  删除整张表

drop table if exists db_name.table_name;

 

--  清空整张表数据，保留表结构

truncate table db_name.table_name;

--  清空整张表数据，保留表结构 第二种方式

insert overwrite table db_name.table_name

 select * from db_name.table_name where 1=0;

 

--  按条件删除 非分区表数据

insert overwrite table db_name.table_name

select * from db_name.table_name where id>'180203a15f';

 

 

二、分区表

1. 建表和数据写入

-- 空分区表的建立

USE db_name;

CREATE TABLE table_name (

     businesstype         STRING COMMENT '类型',

     businessid         STRING COMMENT '业务id'

  )

 COMMENT 'xxx数据'

 PARTITIONED BY(d STRING COMMENT '更新日期')

 STORED AS ORC;

 

--整张表的覆盖写入 分区表

INSERT OVERWRITE TABLE db_name.table_name2 PARTITION(partiton_name)

select  column1,column2，partiton_name  from db_name.table_name1

where xxxx 

eg：

INSERT OVERWRITE TABLE db_name.table_name2 PARTITION(d)

SELECT businesstype, businessid, d

from db_name.table_name1

 

2. 表的删除和数据清空

--  删除整张表

drop table if exists db_name.table_name;

 

--  清空整张表数据，保留表结构

truncate table db_name.table_name;

--  清空整张表数据，保留表结构 第二种方式

insert overwrite table db_name.table_name

 select * from db_name.table_name where 1=0;

 

--  删除某个分区

alter table db_name.table_name drop partition(partiton_name=‘value’))

eg：alter table db_name.table_name drop partition (stat_year_month>='2018-01');

 

--  按条件删除 分区表某分区的数据

insert overwrite table db_name.table_name  PARTITION(partiton_name)

select column1,column2 from db_name.table_name

where id>'180203a15f';

(相当于对该分区的数据进行筛选后重新覆盖写入当前分区)

（这里也可以将数据复制到其他表再筛选导入）

注：如果这里写select*from便会报错，分区ds虽然显示但它不属于表分区后可插的数据

重新把对应的partition信息写一遍，通过WHERE 来限定需要留下的信息，没有留下的信息就被删除了。

eg:

INSERT OVERWRITE TABLE table db_name.table_name PARTITION(d='2020-05-15')

SELECT businesstype, businessid   # 这里不需要再select分区字段d了

FROM table db_name.table_name

WHERE businesstype='blog'

and d= '2020-05-15' ;

 

INSERT OVERWRITE TABLE table db_name.table_name PARTITION(d)

SELECT businesstype, businessid   # 这里不需要再select分区字段d了

FROM table db_name.table_name

WHERE businesstype='blog'

 

3. 其他

-- 获取最新的分区名字

SELECT MAX(d) as latest_d FROM db_name.table_name

WHERE d IS NOT NULL

-- 获取最新的分区的数据

SELECT

* 

FROM

(SELECT MAX(d) as latest_d FROM db_name.table_name

WHERE d IS NOT NULL) a

JOIN

db_name.table_name  b

ON a.latest_d=b.d

WHERE b.d IS NOT NULL;

 

 

三、参考资料

hive分区表的分区操作

https://blog.csdn.net/afafawfaf/article/details/80249974

 

hive删除数据、删除分区、删除库表

https://blog.csdn.net/zimou5581/article/details/82383906

https://blog.csdn.net/a_drjiaoda/article/details/94433005

https://blog.csdn.net/a308601801/article/details/93970153

https://blog.csdn.net/Carl_wang3333333/article/details/100556735

 

hive获取最新分区

https://blog.csdn.net/zhaohansk/article/details/77861469