Spark sql how to execute sql command in a loop for every record in input DataFrame

Question

I have a DataFrame with following schema

     %> input.printSchema
        root
        |-- _c0: string (nullable = true)
        |-- id: string (nullable = true)

I have another DataFrame on which I need to execute sql command

        val testtable = testDf.registerTempTable("mytable")

        %>testDf.printSchema
            root
            |-- _1: integer (nullable = true)

        sqlContext.sql(s"SELECT * from mytable WHERE _1=$id").show()

$id should be from the input DataFrame and the sql command should execute for all input table ids

You probably want to do an inner join between both tables using different keys... — Alberto Bonsanto
– Alberto Bonsanto, Commented May 17, 2016 at 20:44

Daniel de Paula · Accepted Answer · 2016-05-17 23:52:13Z

3

Assuming you can work with a single new DataFrame containing all the rows present in testDf that matches the values present in the id column of input, you can do an inner join operation, as stated by Alberto:

val result = input.join(testDf, input("id") == testDf("_1"))
result.show()

Now, if you want a new, different DataFrame for each distinct value present in testDf, the problem is considerably harder. If this is the case, I would suggest you to make sure the data in your lookup table can be collected as a local list, so you could loop through its values and create a new DataFrame for each one as you already thought (this is not recommended):

val localArray: Array[Int] = input.map { case Row(_, id: Integer) => id }.collect
val result: Array[DataFrame] = localArray.map {
  i => testDf.where(testDf("_1") === i)
}

Anyway, unless the lookup table is very small, I suggest that you adapt your logic to work with the single joined DataFrame of my first example.

edited May 17, 2016 at 23:52

answered May 17, 2016 at 22:40

Daniel de Paula

18k9 gold badges75 silver badges73 bronze badges

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

1 Comment

G G Over a year ago

That's correct ,I want new different DataFrame for each distinct value in testDf. As I have to do a join with reference DataFrame to get the final output.

Collectives™ on Stack Overflow

Spark sql how to execute sql command in a loop for every record in input DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related