Spark SQL UNION,EXPECT,INTERSECT之间的区别

最新推荐文章于 2024-04-30 11:11:45 发布

青春程序不迷路

最新推荐文章于 2024-04-30 11:11:45 发布

阅读量2k

点赞数

分类专栏： spark 文章标签： spark python

本文链接：https://blog.csdn.net/weixin_42331985/article/details/124097622

版权

spark 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Spark SQL UNION,EXPECT,INTERSECT之间的区别

from pyspark.sql import SparkSession

if __name__ == '__main__':
    spark = SparkSession.builder.getOrCreate()
    df1 = spark.createDataFrame([[3], [1], [2], [2], [3], [4]], schema=['v1'])
    df2 = spark.createDataFrame([[5], [1], [2], [2]], schema=['v2'])
    # df.printSchema()
    # 设置数据的表名
    df1.createTempView("num1")
    df2.createTempView("num2")
    # 编写sql实现操作
    # EXCEPT 排除
    # num2 在 num1不存在的数据
    spark.sql("select * from num1 EXCEPT  select * from num2").show()
    spark.sql("select * from num1 MINUS   select * from num2").show()
    # num2 在 num1 不存在的数据（不去重）
    spark.sql("select * from num1 EXCEPT  All (select  * from num2)").show()
    spark.sql("select * from num1 MINUS  All (select  * from num2)").show()
    print("111111111111111111111111")
    # 交集 INTERSECT
    spark.sql("(select * from num1) INTERSECT  (select * from num2)").show()
    spark.sql("(select * from num1) INTERSECT DISTINCT  (select * from num2)").show()
    # 不去重
    spark.sql("select * from num1 INTERSECT ALL (select * from num2)").show()
    print("2222222222222222222222222")
    # 联合 UNION
    spark.sql("(select * from num1) UNION  (select * from num2)").show()
    spark.sql("(select * from num1) UNION DISTINCT  (select * from num2)").show()
    # 不去重
    spark.sql("select * from num1 UNION  ALL (select * from num2)").show()