026.hive-通过hdfs文件复制表

https://www.cnblogs.com/airnew/p/9788122.html

不能直接通过 create table  as   select  创建表，对改变表结构丢失表备注信息（分区表）
create table dwd_cstm_points_record_mi_back__20220311as  
  as select * from dwd_cstm_points_record_mi limit 1 ;

这样分区、 注释会消失，存储格式、分隔符会改变
hive> show create table dwd_cstm_points_record_mi_back__20220311as;
OK
CREATE TABLE `dwd_cstm_points_record_mi_back__20220311as`(
  `record_id` bigint, 
  `instance_id` bigint, 
  `tenant_id` bigint, 
  `member_model_id` bigint, 
  `member_id` bigint, 
  `points_id` bigint, 
  `trade_id` bigint, 
  `points_type` string, 
  `points_code` string, 
  `points` int, 
  `change_type` string, 
  `order_no` string, 
  `trade_type` int, 
  `channel` string, 
  `channel_no` string, 
  `current_points` int, 
  `available_points` int, 
  `effect_time` timestamp, 
  `expire_time` timestamp, 
  `is_expired` int, 
  `remark` string, 
  `status` int, 
  `extension` string, 
  `dr` tinyint, 
  `create_time` timestamp, 
  `create_person` string, 
  `update_time` timestamp, 
  `update_person` string, 
  `sync_type` int, 
  `migrated_time` timestamp, 
  `dm` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://xxxive/warehouse/saas.db/dwd_cstm_points_record_mi_back__20220311as'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 
  'numFiles'='1', 
  'numRows'='1', 
  'rawDataSize'='348', 
  'totalSize'='349', 
  'transient_lastDdlTime'='1646993827')


drop table dwd_cstm_points_record_mi_back__20220311as;

方法1. 通过 like建表  ，插入数据
方法2：通过hdfs文件复制，复制表

方法2：

1.创建表结构
CREATE TABLE dwd_cstm_points_record_mi_back_20220311 like dwd_cstm_points_record_mi;
2.查看表路径
50070端口
http://XXXXXXX:50070/explorer.html#/apps/hive/warehouse/ads_biz_order_period_mi

表结构查看
hive> show create table dwd_cstm_points_record_mi;
OK
CREATE TABLE `dwd_cstm_points_record_mi`(
  `record_id` bigint COMMENT '????????', 
  `instance_id` bigint COMMENT '????????', 
  `tenant_id` bigint COMMENT '????????', 
  `member_model_id` bigint COMMENT '????????', 
  `member_id` bigint COMMENT '????????', 
  `points_id` bigint COMMENT '????????', 
  `trade_id` bigint COMMENT '????????', 
  `points_type` string COMMENT '????', 
  `points_code` string COMMENT '????', 
  `points` int COMMENT '????', 
  `change_type` string COMMENT '????', 
  `order_no` string COMMENT '????', 
  `trade_type` int COMMENT '?????1.???? 2.???? 3.???? 4.???? 5.???? 6.?????', 
  `channel` string COMMENT '????', 
  `channel_no` string COMMENT '????', 
  `current_points` int COMMENT '???????????', 
  `available_points` int COMMENT '????????????????????', 
  `effect_time` timestamp COMMENT '????', 
  `expire_time` timestamp COMMENT '??????????????????????????????????', 
  `is_expired` int COMMENT '????', 
  `remark` string COMMENT '??', 
  `status` int COMMENT '?????1????2????3????', 
  `extension` string COMMENT '????', 
  `dr` tinyint COMMENT '?????0?????1?????', 
  `create_time` timestamp COMMENT '????', 
  `create_person` string COMMENT '???', 
  `update_time` timestamp COMMENT '????', 
  `update_person` string COMMENT '???', 
  `sync_type` int COMMENT '?????0?????1?????', 
  `migrated_time` timestamp COMMENT '????')
PARTITIONED BY ( 
  `dm` int)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\u0001' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://xxxxx/hive/warehouse/xxs.db/dwd_cstm_points_record_mi'
TBLPROPERTIES (
  'transient_lastDdlTime'='1622021164')
3.查看原表分区、查看新表分区
hive下

show partitions dwd_cstm_points_record_mi;
show partitions dwd_cstm_points_record_mi_back_20220311;
4.查看表文件hdfs文件
复制hdfs文件到新的路径 
su hdfs 用户下

hadoop fs -ls  /apps/hive/warehouse/saas.db/dwd_cstm_points_record_mi/
hadoop fs -cp  /apps/hive/warehouse/saas.db/dwd_cstm_points_record_mi/*  /apps/hive/warehouse/saas.db/dwd_cstm_points_record_mi_back_20220311/

查看复制后的新hdfs文件
hadoop fs -ls /apps/hive/warehouse/saas.db/dwd_cstm_points_record_mi_back_20220311/
5.查看新表分区（无分区数据）
show partitions dwd_cstm_points_record_mi_back_20220311;
6.修复分区
hive下

MSCK REPAIR TABLE dwd_cstm_points_record_mi_back_20220311;
7.查看分区数据已经显示
show partitions dwd_cstm_points_record_mi_back_20220311;