若在Hive中执行INSERT OVERWRITE重写同一个表的数据时，有可能会造成数据丢失。
如 INSERT OVERWRITE TABLE table_name SELECT * FROM table_name
一、新建一张分区表
create table test_chj_cols (id string, name string, age string) partitioned by (ds string) stored as textfile;
二、插入一条记录
insert into test_chj_cols partition (ds='20181224') values ('1','chj','18');
三、确认表数据及结构
1> select * from test_chj_cols; 2OK 3test_chj_cols.id test_chj_cols.name test_chj_cols.age test_chj_cols.ds 41 chj 18 20181224 5 6 7> desc formatted test_chj_cols partition (ds='20181224'); 8OK 9col_name data_type comment 10# col_name data_type comment 11 12id string 13name string 14age string 15 16# Partition Information 17# col_name data_type comment 18 19ds string 20 21# Detailed Partition Information 22Partition Value: [20181224] 23Database: hduser05db 24Table: test_chj_cols 25CreateTime: Mon Dec 24 19:35:28 CST 2018 26LastAccessTime: UNKNOWN 27Protect Mode: None 28Location: hdfs://bdphdp02/user/hive/warehouse/hduser05/hduser05db.db/test_chj_cols/ds=20181224 29Partition Parameters: 30 COLUMN_STATS_ACCURATE true 31 numFiles 1 32 numRows 1 33 rawDataSize 8 34 totalSize 17 35 transient_lastDdlTime 1545651329 36 37# Storage Information 38SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 39InputFormat: org.apache.hadoop.mapred.TextInputFormat 40OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 41Compressed: No 42Num Buckets: -1 43Bucket Columns: [] 44Sort Columns: [] 45Storage Desc Params: 46 serialization.format 1 47Time taken: 0.099 seconds, Fetched: 37 row(s)
四、在表中间新增字段
1alter table test_chj_cols replace columns (id string, name string, money string, age string); 2 3 4> desc formatted test_chj_cols; 5OK 6col_name data_type comment 7# col_name data_type comment 8 9id string 10name string 11money string 12age string 13 14# Partition Information 15# col_name data_type comment 16 17ds string 18 19# Detailed Table Information 20Database: hduser05db 21Owner: hadoop 22CreateTime: Mon Dec 24 19:34:46 CST 2018 23LastAccessTime: UNKNOWN 24Protect Mode: None 25Retention: 0 26Location: hdfs://bdphdp02/user/hive/warehouse/hduser05/hduser05db.db/test_chj_cols 27Table Type: MANAGED_TABLE 28Table Parameters: 29 last_modified_by hadoop 30 last_modified_time 1545651722 31 transient_lastDdlTime 1545651722 32 33# Storage Information 34SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 35InputFormat: org.apache.hadoop.mapred.TextInputFormat 36OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 37Compressed: No 38Num Buckets: -1 39Bucket Columns: [] 40Sort Columns: [] 41Storage Desc Params: 42 serialization.format 1 43Time taken: 0.051 seconds, Fetched: 36 row(s)
五、重写数据
insert overwrite table test_chj_cols partition (ds='20181224') select id,name,age,name from
test_chj_cols;
六、age字段数据丢失
1> select * from test_chj_cols; 2OK 3test_chj_cols.id test_chj_cols.name test_chj_cols.age test_chj_cols.money test_chj_cols.ds 41 chj NULL NULL 20181224