Hive column data type change

Question

There is an existing hive table data types as below,

col1 String,
col2 String,
col3 String,
col4 String

so when querying with select * from table; it throws below error

Bad status for request TFetchResultsReq(fetchType=0, operationHandle=TOperationHandle(hasResultSet=True, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret='\\\x9f\xd0\x97\x88\x98Mg\x82\x85r>\xd9\xf1\xc6\xe1', guid='+M\x99\xb7\xe1\x88C\xea\x9b#\x98\xd59mU\xf8')), orientation=4, maxRows=100): TFetchResultsResp(status=TStatus(errorCode=0, errorMessage='java.io.IOException: java.lang.UnsupportedOperationException: Cannot inspect org.apache.hadoop.io.LongWritable', sqlState=None, infoMessages=['*org.apache.hive.service.cli.HiveSQLException:java.io.IOException: java.lang.UnsupportedOperationException:

So then I checked the parquet file and their data types as below,

col1 String,
col2 int64,
col3 int64,
col4 int64

then I run ALTER TABLE table CHANGE col2 col2 BIGINT; query for all 3 mismatched columns and changed the column data types. then after query for the data, I'm getting the same error,

then again I have created another hive table as table_test with correct data types as parquet then insert a few files using

LOAD DATA INPATH '/tmp/date=20220109/' 
INTO table table_test PARTITION (date=20220109);

Now all queries become successful.

Is there anyway to fix this issue without inserting all data into new table?

This can be related: https://stackoverflow.com/a/63792451/2700344 — leftjoin, Jan 11 '22 at 09:11
@leftjoin i'm not getting exception for `ALTER TABLE table CHANGE col2 col2 BIGINT;` query — Dulanga Heshan, Jan 11 '22 at 09:21
@leftjoin thank you what if create a new table and then `ALTER TABLE new_table SET LOCATION 'old_table_location'` — Dulanga Heshan, Jan 11 '22 at 11:17
You can create new (external) table specifying old location in create table DDL, no need to alter... It should work — leftjoin, Jan 11 '22 at 11:24
If the table is partitioned, you should mount partitions using MSCK REPAIR or ALTER TABLE RECOVER PARTITIONS (if on EMR) See https://stackoverflow.com/a/57033175/2700344 — leftjoin, Jan 11 '22 at 12:18
@leftjoin thank you now from hive its works perfect, but in impala still throws exceptions — Dulanga Heshan, Jan 12 '22 at 08:04

Hive column data type change

0 Answers0