Hive Table Partition Metadata Issue

user_13508511 0
Viewed 129

we have a Hive Internal partitioned table which is existing from long time. recently we discovered that we need to change a column's name and datatype. also, we need to remove 3 duplicate records from the table. so we performed this step.

alter table tablename CHANGE line_nbr line_id INT; (it was string before)

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
               column1,
               columnx
               from table1
               where id NOT IN ('1','2','3')
               and order_dt in (CAST('2020-02-18' AS DATE),CAST('2020-02-19' AS DATE),CAST('2020-10-30' AS DATE));"

Tried to run the above steps to change the column name and type and also remove dupes. during this, we encountered an error saying there are few bad partitions in the table. but when checked, the dupes were not removed, but the column name and type were changed.

Now, we removed the bad partitions from the table, and then tried to run only the insert overwrite step to remove the dupes. It was successful, and removed the dupes. But if i try to query the table now, for some of the dates, it gives me an error

-- presto error: Error opening Hive split gs:xxxx/cmny_cd=xxx/order_dt=2021-09-20/000000_0 (offset=0, length=24370774): Malformed ORC file. Cannot read SQL type 'integer' from ORC stream '._col6' of type STRING with attributes {} [gs:xxxx/cmny_cd=xxx/order_dt=2021-09-20/000000_0]

when i check the show create table, the name and dataype are changed, but when i try the below

describe formatted tablename partition (cmny_cd='xxx',order_dt='2021-09-20');

few partitions, it shows changed name and type, but few, it shows the old name and string datatype. Im guessing this could have happened, because of the failure when we first attempted to change the dataype and remove dupes.

Im confused on what should i do in order to repair the table and query the table without any issues. Can someone please help on this? It would be greatly appreciated.

we ran msck repair table tablename already on the table, but it did not solve the issue

hiveql google-cloud-platform hive
1 Answers

Overwrite all partitions using Hive, like you did when removed duplicates. Some partitions remain not overwritten and causing error in Presto because the type inside ORC file and in Hive metadata is different. So, just try to overwrite partitions which you did not previously overwritten or overwrite all of them:

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
        column1,
        columnx
        ...
  from table1
  ---you can limit partitions in the WHERE
user_27003440
Related