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、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;

5、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;

1、非分区表

1） 全同步

create table table_new as select * from table_old;

2） 部分同步(同步name,age,sex三个字段的数据)

create table table_new as 
select
	name,
	age,
	sex
from table_old

2、分区表

1）复制表结构

create table table_new like table_old;

2 ）复制数据：

a. 第一种

insert overwrite table table_new partition(dt) select * from table_old;

b. 第二种（数据量大，推荐）

dfs -cp /user/bigdata.db/table_old/* /user/bigdata/db/bigdata_new/;
-- 拷贝完，修复分区
msck repair table table_new;

1、复制表或者迁移数据库

CREATE TABLE new_table AS SELECT * FROM old_table;（非分区）

create table t_copy like t_part;(分区表)

2、复制数据

查看老表的hdfs文件位置 （show create table xxx;） old_file_path

查看新表的hdfs文件位置 new_file_path;

hdfs复制：hadoop fs -cp old_file_path new_file_path；

3、修复元数据

MSCK REPAIR TABLE new_table;修复新表的分区元数据。