You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "nivedita singh (JIRA)" <ji...@apache.org> on 2019/03/01 03:13:00 UTC
[jira] [Comment Edited] (SPARK-26388) No support for "alter table
.. replace columns" to drop columns
[ 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-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org