Adding a new column to a spark dataframe [duplicate]

Question

I want to add a column to a spark dataframe which has been registered as a table. This column needs to have an auto incrementing long.

df = spark.sql(query)
df.createOrReplaceTempView("user_stories")
df = spark.sql("ALTER TABLE user_stories ADD COLUMN rank int AUTO_INCREMENT")
df.show(5)

This throws the following error,

Py4JJavaError: An error occurred while calling o72.sql.
: org.apache.spark.sql.catalyst.parser.ParseException: 
no viable alternative at input 'ALTER TABLE user_stories ADD COLUMN'(line 1, pos 29)

== SQL ==
ALTER TABLE user_stories ADD COLUMN rank int AUTO_INCREMENT
-----------------------------^^^

What am I missing here?

How's this question a duplicate, I need an auto increment value in the new column, I don't see that addressed in the question you've cited. — Melissa Stewart
– Melissa Stewart, Commented Jul 25, 2018 at 19:16
There is no auto increment in spark. What are you trying to do? — pault
– pault, Commented Jul 25, 2018 at 19:25

Gaurang Shah · Accepted Answer · 2018-07-25 19:33:50Z

1

if you want to add new incremental column to DF, you could do in following ways.

df.show()
+-------+
|   name|
+-------+
|gaurnag|
+-------+   
from pyspark.sql.functions import monotonically_increasing_id
new_df = df.withColumn("id", monotonically_increasing_id())
new_df.show()
+-------+---+
|   name| id|
+-------+---+
|gaurnag|  0|
+-------+---+

edited Jul 25, 2018 at 19:33

answered Jul 25, 2018 at 19:18

Gaurang Shah

13.1k15 gold badges96 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding a new column to a spark dataframe [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related