I am trying to alter several columns on HIVE external table from double to decimal. I have dropped, recreated the table, and ran msck repair statement. However, I am unable to select the table neither from Hive nor Impala as it returns these error:
Hive: ERROR processing query/statement. Error Code: 0, SQL state: File 'hdfs://ns-bigdata/user/warehouse/fact/TEST_FACT/key=2458773/000000_0' has an incompatible Parquet schema for column 'testing.fact_table.tot_amt'. Column type: DECIMAL(28,7), Parquet schema:
optional double tot_amt [i:29 d:1 r:0]
Impala: ERROR processing query/statement. Error Code: 0, SQL state: File 'hdfs://ns-bigdata/user/warehouse/fact/TEST_FACT/key=2458773/000000_0' has an incompatible Parquet schema for column 'testing.fact_table.tot_amt'. Column type: DECIMAL(28,7), Parquet schema:
optional double tot_amt [i:29 d:1 r:0]
Is it possible to change the datatype from double to decimal? Also what's the difference between droping+recreating the table and altering the table?
can you pls use alter table like below to convert from double to decimal. Please make sure your decimal column can hold all double data. it works on both impala and hive.
alter table table_name change col col decimal(5,4); -- notice col name mentioned twice
Alter table - useful if you want to add a new column in the end of a table and not wipe out all data. Easier, faster but with limited capability.
Drop, create table - useful when you want to restructure columns, file format, all columns to partitioned tables.