Hive复制表结构

1、text格式表

create table if not exists 
dws.dws_zwfw_business_index like   ods.ods_business_index
row format delimited fields terminated by '|' NULL DEFINED AS '' 
stored as textfile;

2、orc格式表

create table if not exists 
dws.dws_business_stage like  ods.ods_business_stage
row format delimited fields terminated by '|' NULL DEFINED AS '' 
stored as orc tblproperties ("orc.compress"="NONE");

3、CTAT

create table if not exists 
dwd.dwd_business_index
row format delimited fields terminated by '|' NULL DEFINED AS '' 
stored as textfile 
as select * from dws.dws_business_index where 1=2;

4、like 复制表结构

create table if not exists src_hpxzspj_bak.spj_mbfqydwdjzs like src_hpxzspj.spj_mbfqydwdjzs row format delimited fields terminated by '|' NULL DEFINED AS '' stored as textfile;

5、Hive表分区相关操作

#添加分区
alter table src_test0506.stg_sdsjzx_gx_ods_hsjc_2 add if not exists partition(batch_no='20220411');

#删除分区数据
truncate table src_test0506.stg_sdsjzx_gx_ods_hsjc_2 partition(batch_no='20220411');

#删除分区
alter table src_test0506.stg_sdsjzx_gx_ods_hsjc_2 drop partition(batch_no='20220411');

#统计全表的所有分区的信息
ANALYZE TABLE Table1 COMPUTE STATISTICS;

#只统计文件数和文件大小，不扫描文件行数，执行较快
ANALYZE TABLE Table1 COMPUTE STATISTICS NOSCAN;

#统计执行分区的信息
ANALYZE TABLE Table1 PARTITION(ds='2008-04-09', hr=11) COMPUTE STATISTICS;

#拷贝完，修复分区
msck repair table table_new;

6、Hive字符串null处理方法

insert overwrite table dws.dws_business_index_bak
select 
case 
when bjbh='null' then ''
when bjbh is null then ''
else bjbh
end as bjbh
,case 
when sxbm='null' then ''
when sxbm is null then ''
else sxbm
end as sxbm
,case 
when sxmc='null' then ''
when sxmc is null then ''
else sxmc
end as sxmc
,case 
when bjlx='null' then ''
when bjlx is null then ''
else bjlx
end as bjlx
,case 
when sqr='null' then ''
when sqr is null then ''
else sqr
end as sqr
,case 
when sqzjlx='null' then ''
when sqzjlx is null then ''
else sqzjlx
end as sqzjlx
,case 
when sqzjhm='null' then ''
when sqzjhm is null then ''
else sqzjhm
end as sqzjhm
,case 
when lxr='null' then ''
when lxr is null then ''
else lxr
end as lxr
,case 
when lxrzjlx='null' then ''
when lxrzjlx is null then ''
else lxrzjlx
end as lxrzjlx
,case 
when lxrzjhm='null' then ''
when lxrzjhm is null then ''
else lxrzjhm
end as lxrzjhm
,case 
when lxdh='null' then ''
when lxdh is null then ''
else lxdh
end as lxdh
,case 
when lxyzbm='null' then ''
when lxyzbm is null then ''
else lxyzbm
end as lxyzbm
,case 
when lxtxdz='null' then ''
when lxtxdz is null then ''
else lxtxdz
end as lxtxdz
,case 
when fddbr='null' then ''
when fddbr is null then ''
else fddbr
end as fddbr
,case 
when sqly='null' then ''
when sqly is null then ''
else sqly
end as sqly
,case 
when sqsj='null' then ''
when sqsj is null then ''
else sqsj
end as sqsj
,case 
when xmbh='null' then ''
when xmbh is null then ''
else xmbh
end as xmbh
,case 
when bjzt='null' then ''
when bjzt is null then ''
else bjzt
end as bjzt
,case 
when id='null' then ''
when id is null then ''
else id
end as id
,case 
when oid='null' then ''
when oid is null then ''
else oid
end as oid
,case 
when qhdm='null' then ''
when qhdm is null then ''
else qhdm
end as qhdm
,case 
when jgdm='null' then ''
when jgdm is null then ''
else jgdm
end as jgdm
,case 
when cjsj='null' then ''
when cjsj is null then ''
else cjsj
end as cjsj
,case 
when gxsj='null' then ''
when gxsj is null then ''
else gxsj
end as gxsj
,case 
when flag='null' then ''
when flag is null then ''
else flag
end as flag
,case 
when `exchange`='null' then ''
when `exchange` is null then ''
else `exchange`
end as `exchange`
,case 
when data_up_uuid='null' then ''
when data_up_uuid is null then ''
else data_up_uuid
end as data_up_uuid
,case 
when data_up_time='null' then ''
when data_up_time is null then ''
else data_up_time
end as data_up_time
,data_up_status
,case 
when pbsnum='null' then ''
when pbsnum is null then ''
else pbsnum
end as pbsnum
,case 
when sckqzsj='null' then ''
when sckqzsj is null then ''
else sckqzsj
end as sckqzsj

from ods.ods_business_index;

7、Hive保留近3天历史分区

-- 保留最近3天的分区
alter table `dw`.`dw_hb_tour_consumption_scale` drop IF EXISTS partition(dt <= '${deldate}');

-- drop IF EXISTS partition(pdate < '20221219' );