[ https://issues.apache.org/jira/browse/SPARK-26388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16780365#comment-16780365 ]
nivedita singh edited comment on SPARK-26388 at 3/1/19 3:12 AM: ---------------------------------------------------------------- I even got the same error on using spark 2.3.2 and hive 1.2.1.I saw hive 1.2.1 supporting alter table replace column. Hive BEELINE : 0: jdbc:hive2://10.18.226.234:10000/default> create table mytable2(a int, b string,c float); No rows affected (0.299 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> desc mytable2; +-------------+----------+++------------ |col_name|data_type|comment| +-------------+----------+++------------ |a|int| | |b|string| | |c|float| | +-------------+----------+++------------ 3 rows selected (0.114 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> alter table mytable2 replace columns(a int,b string,d int); No rows affected (0.11 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> desc mytable2; +-------------+----------+++------------ |col_name|data_type|comment| +-------------+----------+++------------ |a|int| | |b|string| | |d|int| | +-------------+----------+++------------ 3 rows selected (0.077 seconds) Spark-SQL : spark-sql> desc mytable2; a int NULL b string NULL d int NULL Time taken: 2.434 seconds, Fetched 3 row(s) spark-sql> alter table mytable2 replace columns(a int,b string,c string); Error in query: Operation not allowed: alter table replace columns(line 1, pos 0) == SQL == alter table mytable2 replace columns(a int,b string,c string) ^^^ [~hyukjin.kwon] Can you please confirm on should spark support alter table replace columns ? i will be working on it. was (Author: nivedeeta): I even got the same error on using spark 2.3.2 and hive 1.2.1.I saw hive 1.2.1 supporting alter table replace column, so I think it should be supported in Spark too. Hive BEELINE : 0: jdbc:hive2://10.18.226.234:10000/default> create table mytable2(a int, b string,c float); No rows affected (0.299 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> desc mytable2; +------------+-----------++------------+ |col_name|data_type|comment| +------------+-----------++------------+ |a|int| | |b|string| | |c|float| | +------------+-----------++------------+ 3 rows selected (0.114 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> alter table mytable2 replace columns(a int,b string,d int); No rows affected (0.11 seconds) 0: jdbc:hive2://10.18.226.234:10000/default> desc mytable2; +------------+-----------++------------+ |col_name|data_type|comment| +------------+-----------++------------+ |a|int| | |b|string| | |d|int| | +------------+-----------++------------+ 3 rows selected (0.077 seconds) Spark-SQL : spark-sql> desc mytable2; a int NULL b string NULL d int NULL Time taken: 2.434 seconds, Fetched 3 row(s) spark-sql> alter table mytable2 replace columns(a int,b string,c string); Error in query: Operation not allowed: alter table replace columns(line 1, pos 0) == SQL == alter table mytable2 replace columns(a int,b string,c string) ^^^ [~hyukjin.kwon] Can you please confirm on should spark support alter table replace columns ? I will be working on it. > No support for "alter table .. replace columns" to drop columns > --------------------------------------------------------------- > > Key: SPARK-26388 > URL: https://issues.apache.org/jira/browse/SPARK-26388 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 2.2.1, 2.3.1, 2.3.2 > Reporter: nirav patel > Priority: Major > > Looks like hive {{replace columns}} is not working with spark 2.2.1 and 2.3.1 > > create table myschema.mytable(a int, b int, c int) > alter table myschema.mytable replace columns (a int,b int,d int) > > *Expected Behavior* > it should drop column c and add column d. > alter table... replace columns.. should work just as it works in hive. > It replaces existing columns with new ones. It delete if column is not > mentioned. > > here's the snippet of hive cli: > hive> desc mytable; > OK > a int > b int > c int > Time taken: 0.05 seconds, Fetched: 3 row(s) > hive> alter table mytable replace columns(a int, b int, d int); > OK > Time taken: 0.078 seconds > hive> desc mytable; > OK > a int > b int > d int > Time taken: 0.03 seconds, Fetched: 3 row(s) > > *Actual Result* > Exception in thread "main" > org.apache.spark.sql.catalyst.parser.ParseException: Operation not allowed: > alter table replace columns > {{ADD COLUMNS}} works which seemed to previously reported and fixed as well: > https://issues.apache.org/jira/browse/SPARK-18893 > > Replace columns should be supported as well. afaik, that's the only way to > delete hive columns. > > > It supposed to work according to this docs: > > [https://docs.databricks.com/spark/latest/spark-sql/language-manual/alter-table-or-view.html#replace-columns] > > [https://spark.apache.org/docs/2.2.0/sql-programming-guide.html#supported-hive-features] > > but it's throwing error for me on 2 different versions. > > -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org