CODEWITHSUNDEEP
apache-sparkapache-spark-sqlpyspark
Sophie Dinka
Sophie Dinka

Reputation: 83

Duplicate Records move to other temp table in pyspark

I am using Pyspark

My Input Data looks like below.

 COL1|COL2
|TYCO|130003|
|EMC |120989|
|VOLVO|102329|
|BMW|130157|
|FORD|503004|
|TYCO|130003|

I have created DataFrame and querying for duplicates like below.

from pyspark.sql import Row
from pyspark.sql import SparkSession
spark = SparkSession \
     .builder \
     .appName("Test") \
     .getOrCreate()

data = spark.read.csv("filepath")

data.registerTempTable("data")
spark.sql("SELECT count(col2)CNT, col2 from data GROUP BY col2 ").show()

This give correct result but can we get duplicate value in seperate temp table.

output data in Temp1

+----+------+
|   1|120989|
|   1|102329|
|   1|130157|
|   1|503004|
+----+------+

output data in temp2

+----+------+
|   2|130003|
+----+------+

Upvotes: 0

Views: 291

Answers (1)

dassum
dassum

Reputation: 5113

sqlDF = spark.sql("SELECT count(col2)CNT, col2 from data GROUP BY col2  having cnt > 1 ");
sqlDF.createOrReplaceTempView("temp2");

Upvotes: 1

Related Questions