Spark 2.1 Alter table - Alternatives / Work Arounds

[#1]

We need run Create table and alter table statements to hive from within a spark streaming application. The spark version is 2.1.X and it is HDP 2.6.2.

spark.sqlContext.sql("CREATE ...")
spark.sqlContext.sql("ALTER ...")

The create statement works but alter fails with Spark error "Operation not allowed". We are stuck and not able to proceed further. Is there any way by which we can run Alter table commands to hive with HDP 2.6.2 from within a spark application. From the SparkSession we cannot get hold of HiveContext hence that approach is also not possible. Any help in this matter is much appreciated.

[#2]

Unfortunately, it's Spark 2.1.X behavior. You need to use Hive. BTW, which `ALTER TABLE` do you need? In HDP 2.6.3, Spark 2.2 supports `ALTER TABLE ADD COLUMNS` via the following two issues.

- https://issues.apache.org/jira/browse/SPARK-19261

- https://issues.apache.org/jira/browse/SPARK-21929

[#3]

we have requirement where we have to stream DDL statements from kafka and apply them on hive table, can we use spark streaming with hive JDBC to perform same, because spark 2.1.x does not support "ALTER TABLE".

[#4]

@PremKumar Karunakaran

In spark you will not be able to modify the data. It's has immutable data which cannot be altered or modified. If you need to perform some modification in the DDL again that's not supported in spark, atleast as of now. You have to do it either through hive CLI but definitely not through spark. Hope it helps!!