Iceberg connector : ALTER TABLE <TableName> DROP COLUMN is not working for ORC and Parquet file format

[vedprakash0505]

Hi Team,

Could you please help me on this.

We are using the iceberg connector release 393 and tried the following commands

SET SESSION hive.orc_use_column_names=true;

Set session hive.parquet_use_column_names=true;

alter table iceberg.<schema_name>. drop column <column_name>;

this is throwing us the exceptions:

SQL Error [16777216]: Query failed (#20220826_114607_00215_j2gsm): Failed to commit to table

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [16777216]: Query failed (#20220826_114607_00215_j2gsm): Failed to commit to table test.parquet_column_alert_test4
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:577)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:493)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:894)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3643)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4945)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Query failed (#20220826_114607_00215_j2gsm): Failed to commit to table test.parquet_column_alert_test4
	at io.trino.jdbc.AbstractTrinoResultSet.resultsException(AbstractTrinoResultSet.java:1937)
	at io.trino.jdbc.TrinoResultSet.getColumns(TrinoResultSet.java:285)
	at io.trino.jdbc.TrinoResultSet.create(TrinoResultSet.java:61)
	at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:262)
	at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:240)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
	at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more
Caused by: org.apache.iceberg.exceptions.CommitFailedException: Failed to commit to table test.parquet_column_alert_test4
	at io.trino.plugin.iceberg.catalog.hms.HiveMetastoreTableOperations.commitToExistingTable(HiveMetastoreTableOperations.java:94)
	at io.trino.plugin.iceberg.catalog.AbstractIcebergTableOperations.commit(AbstractIcebergTableOperations.java:150)
	at org.apache.iceberg.SchemaUpdate.commit(SchemaUpdate.java:388)
	at io.trino.plugin.iceberg.IcebergMetadata.dropColumn(IcebergMetadata.java:1368)
	at io.trino.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.dropColumn(ClassLoaderSafeConnectorMetadata.java:416)
	at io.trino.metadata.MetadataManager.dropColumn(MetadataManager.java:720)
	at io.trino.execution.DropColumnTask.execute(DropColumnTask.java:99)
	at io.trino.execution.DropColumnTask.execute(DropColumnTask.java:41)
	at io.trino.execution.DataDefinitionExecution.start(DataDefinitionExecution.java:145)
	at io.trino.execution.SqlQueryManager.createQuery(SqlQueryManager.java:249)
	at io.trino.dispatcher.LocalDispatchQuery.lambda$startExecution$7(LocalDispatchQuery.java:143)
	at io.trino.$gen.Trino_393____20220826_112735_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: io.trino.spi.TrinoException: The following columns have types incompatible with the existing columns in their respective positions :
graduate
	at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.alterTable(ThriftHiveMetastore.java:1001)
	at io.trino.plugin.hive.metastore.thrift.BridgingHiveMetastore.alterTable(BridgingHiveMetastore.java:334)
	at io.trino.plugin.hive.metastore.thrift.BridgingHiveMetastore.replaceTable(BridgingHiveMetastore.java:213)
	at io.trino.plugin.hive.metastore.cache.CachingHiveMetastore.replaceTable(CachingHiveMetastore.java:523)
	at io.trino.plugin.iceberg.catalog.hms.HiveMetastoreTableOperations.commitToExistingTable(HiveMetastoreTableOperations.java:90)
	... 14 more
Caused by: org.apache.hadoop.hive.metastore.api.InvalidOperationException: The following columns have types incompatible with the existing columns in their respective positions :
graduate
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$alter_table_with_environment_context_result$alter_table_with_environment_context_resultStandardScheme.read(ThriftHiveMetastore.java)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$alter_table_with_environment_context_result$alter_table_with_environment_context_resultStandardScheme.read(ThriftHiveMetastore.java)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$alter_table_with_environment_context_result.read(ThriftHiveMetastore.java)
	at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:86)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_alter_table_with_environment_context(ThriftHiveMetastore.java:2286)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.alter_table_with_environment_context(ThriftHiveMetastore.java:2270)
	at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastoreClient.alterTableWithEnvironmentContext(ThriftHiveMetastoreClient.java:251)
	at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.lambda$alterTableWithEnvironmentContext$9(FailureAwareThriftMetastoreClient.java:146)
	at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.runWithHandle(FailureAwareThriftMetastoreClient.java:467)
	at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.alterTableWithEnvironmentContext(FailureAwareThriftMetastoreClient.java:146)
	at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.lambda$alterTable$39(ThriftHiveMetastore.java:992)
	at io.trino.plugin.hive.metastore.thrift.ThriftMetastoreApiStats.lambda$wrap$0(ThriftMetastoreApiStats.java:42)
	at io.trino.plugin.hive.util.RetryDriver.run(RetryDriver.java:130)
	at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.alterTable(ThriftHiveMetastore.java:985)
	... 18 more

we followed this link but did not solve our problem
https://trino.io/docs/current/connector/hive.html#parquet-format-configuration-properties