Spark中显示视图记录总数的结果不正确

发布于 2025-01-11 04:29:50 字数 1776 浏览 3 评论 0原文

要求： 有 3 个数据帧 df1、df2 和 df4 包含一些记录，我们分别从名为“aaa”、“bbb”和“ccc”的数据帧创建了一个临时视图。还有另一个数据帧 df3 包含列 table_name ，该列具有 3 个值“aaa”、“bbb”和“ccc”。我们创建了 df3 的临时视图作为“表”。我们必须创建另一个包含 2 列的数据框，第一列包含临时视图名称，第二列包含相应视图的许多记录。

我尝试过以下代码：

import org.apache.spark.sql.SparkSession
object SampleDemo1 {
  val spark=SparkSession.builder.master("local").appName("SampleDemo1").getOrCreate()
  val sc=spark.sparkContext
  import spark.implicits._
  sc.setLogLevel("ERROR")

  def main(args: Array[String]): Unit = {
    val l1=List((1,"ABC",50),(2,"PQR",70))
    val l2=List((3,"MNO",75),(4,"XYZ",100))
    val l4=List((3,"MNO",75),(4,"XYZ",100),(4,"XYZ",100))
    val df1=l1.toDF("id","name","age")
    val df2=l2.toDF("id","name","age")
    val df4=l4.toDF("id","name","age")
    df1.createOrReplaceTempView("aaa")
    df2.createOrReplaceTempView("bbb")
    df4.createOrReplaceTempView("ccc")

    val l3=List(("aaa"),("bbb"),("ccc"))
    val df3=l3.toDF("table_name")
    df3.createOrReplaceTempView("tables")
    //spark.sql("select * from tables").show
    val dfresult=spark.sql("""select table_name,(select count(1) from (select table_name from tables)) as number_of_records from tables""")
    dfresult.show
  }
}

输出：

<前><代码>+----------+-----------------+ |表名|记录数| +----------+-----------------+ |啊啊| 3| | bbb| 3| | ccc| 3| +----------+-----------------+

预期输出：

<前><代码>+----------+-----------------+ |表名|记录数| +----------+-----------------+ |啊啊| 2| | bbb| 2| | ccc| 3| +----------+-----------------+

问题：代码中的问题是，由于 df1 和 df2 总共包含 2 条记录，但解决方案显示记录数为 3。而 df4 包含 3 条记录，并且显示正确。

请指导我以正确的方式解决问题？

原文

Requirement:
There are 3 data frames df1,df2, and df4 containing some records and we have created a temp view out of the dataframes named "aaa","bbb" and "ccc" respectively.
There is another dataframe df3 containing a column table_name having 3 values "aaa","bbb" and "ccc".We have created a temp view of df3 as "tables".
We have to create another dataframe containing 2 columns, 1st containing temp view names and the second containing a number of records of the corresponding view.

I have tried the following code:

import org.apache.spark.sql.SparkSession
object SampleDemo1 {
  val spark=SparkSession.builder.master("local").appName("SampleDemo1").getOrCreate()
  val sc=spark.sparkContext
  import spark.implicits._
  sc.setLogLevel("ERROR")

  def main(args: Array[String]): Unit = {
    val l1=List((1,"ABC",50),(2,"PQR",70))
    val l2=List((3,"MNO",75),(4,"XYZ",100))
    val l4=List((3,"MNO",75),(4,"XYZ",100),(4,"XYZ",100))
    val df1=l1.toDF("id","name","age")
    val df2=l2.toDF("id","name","age")
    val df4=l4.toDF("id","name","age")
    df1.createOrReplaceTempView("aaa")
    df2.createOrReplaceTempView("bbb")
    df4.createOrReplaceTempView("ccc")

    val l3=List(("aaa"),("bbb"),("ccc"))
    val df3=l3.toDF("table_name")
    df3.createOrReplaceTempView("tables")
    //spark.sql("select * from tables").show
    val dfresult=spark.sql("""select table_name,(select count(1) from (select table_name from tables)) as number_of_records from tables""")
    dfresult.show
  }
}

output:

+----------+-----------------+
|table_name|number_of_records|
+----------+-----------------+
|       aaa|                3|
|       bbb|                3|
|       ccc|                3|
+----------+-----------------+

Expected Output:

+----------+-----------------+
|table_name|number_of_records|
+----------+-----------------+
|       aaa|                2|
|       bbb|                2|
|       ccc|                3|
+----------+-----------------+

Issue:
The issue in the code is, as the df1 and df2 contain a total of 2 records but the solution is showing as a number of records as 3. While the df4 contains the 3 records and it is showing correctly.

Could anyone please guide me in solving the issue in the correct manner?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╭ゆ眷念 2025-01-18 04:29:50

您的需求是统计每个视图中的记录数，建议使用union all。

    val dfresult=spark.sql("""
        select 'aaa' as table_name,count(*) as number_of_records  from aaa
        union all
        select 'bbb' as table_name,count(*) as number_of_records  from bbb
        union all
        select 'ccc' as table_name,count(*) as number_of_records  from ccc
    """)

Your requirement is to count the number of records in each view, it is recommended to use union all.

    val dfresult=spark.sql("""
        select 'aaa' as table_name,count(*) as number_of_records  from aaa
        union all
        select 'bbb' as table_name,count(*) as number_of_records  from bbb
        union all
        select 'ccc' as table_name,count(*) as number_of_records  from ccc
    """)

回复收藏 0 原文

~没有更多了~