Skip to main content
Edu7478
Silver ⭐️
October 24, 2024
Solved

Error when do an alter table change column on an acid table

Hello,

We have done this before but now we are trying to make a change on a table column and we are getting the follow error:

Error while compiling statement: FAILED: Execution Error, return code 40013 from org.apache.hadoop.hive.ql.ddl.DDLTask. Unable to alter table. Cannot change stats state for a transactional table pro_con.dmcrp001_th_importe_consolidado_contrato without providing the transactional write state for verification (new write ID 327, valid write IDs null; current state null; new state {}

The statement that we are executing is:

alter table `pro_con`.`dmcrp001_th_importe_consolidado_contrato` change column `fecha_movimiento` `fecha_proceso_dia` timestamp comment 'Fecha de referencia de los datos.';

 

Mounika_Reddy
Gold ⭐️
October 25, 2024

Hi @Edu7478 
Help us in sharing the below details
Exact CDP and CM version .
During the time of issue appears provide us the HS2 and HMS logs from all nodes 
Is table is partitioned table? Share us the DDL for the same
DESCRIBE FORMATTED pro_con.dmcrp001_th_importe_consolidado_contrato;
SHOW CREATE TABLE pro_con.dmcrp001_th_importe_consolidado_contrato;
Try with following  setting at session level   set hive.stats.autogather=false; and rerun the query .If it fails let us know
For more info refer the below KB article
https://community.cloudera.com/t5/Customer/Alter-Table-Fails-With-quot-Unable-to-alter-table-Cannot/ta-p/380703

Thank You.

3 replies

DianaTorres
Community Manager
Community Manager
October 24, 2024

@Shmoo @cravani @mszurap Hi! Do you have any insights here? Thanks!

Translate
Regards, Diana Torres, Senior Community Moderator
Mounika_Reddy
Gold ⭐️
Gold ⭐️
October 25, 2024

Hi @Edu7478 
Help us in sharing the below details
Exact CDP and CM version .
During the time of issue appears provide us the HS2 and HMS logs from all nodes 
Is table is partitioned table? Share us the DDL for the same
DESCRIBE FORMATTED pro_con.dmcrp001_th_importe_consolidado_contrato;
SHOW CREATE TABLE pro_con.dmcrp001_th_importe_consolidado_contrato;
Try with following  setting at session level   set hive.stats.autogather=false; and rerun the query .If it fails let us know
For more info refer the below KB article
https://community.cloudera.com/t5/Customer/Alter-Table-Fails-With-quot-Unable-to-alter-table-Cannot/ta-p/380703

Thank You.

Translate
ggangadharan
Gold ⭐️
Gold ⭐️
October 25, 2024

Error looks similar to HIVE-27778. 

Can you try the workaround given in the kb

Translate