Passing a list loaded from text file to sql query in Spark SQL

Question

I have read accounts from a text file which use , as separator:

val csv = spark.read.text("src/main/resources/in/insight/account_issues.txt")

//implicits
import spark.sqlContext.implicits._

val string_account = csv.map(_.getString(0)).collect.toList.toString()
//print(string_account)

val query = s"""(SELECT
               |    ACCOUNT_NUMBER,
               |    CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
               |        ELSE NULL
               |    END as 'CreditAmount',
               |    CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
               |        ELSE  NULL
               |    END as 'DebitAmount',
               |    STMT.BOOKING_DATE,
               |    STMT.VALUE_DATE,
               |    CRF_TYPE
               |FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT
               |    LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER
               |
               |WHERE STMT.MIS_DATE='$BusinessDate'
               | AND STMT.ACCOUNT_NUMBER IN ($string_account) ) tmp """.stripMargin

val responseWithSelectedColumns = spark
  .read
  .format("jdbc")
  .option("url", url)
  .option("driver", driver)
  .option("dbtable", query)
  .load()

I cannot get the works instead getting error:

: 'List' is not a recognized built-in function name

What is wrong from my code?

Shaido · Accepted Answer · 2019-02-11 07:45:27Z

4

When you create string_account you use toString() on a List. This will give you a string List(...), for example:

scala> List(1,2,3).toString()
res0: String = List(1, 2, 3)

What you want to use instead is mkString(","):

scala> List(1,2,3).mkString(",")
res1: String = "1,2,3"

In this case that would be:

val string_account = csv.map(_.getString(0)).collect.toList.mkString(",")

Note: You can add the parentheses easily to string_account instead of having them in the SQL query if wanted with mkString("(", ",", ")").

edited Feb 11, 2019 at 7:45

answered Feb 11, 2019 at 7:19

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

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

4 Comments

tree em Over a year ago

do you mean I can change to this val string_account = csv.map(_.getString(0)).collect.toList.mkString("(", ",", ")")

Shaido Over a year ago

@kn3l: I made the answer a bit clearer. If you don't change the SQL query, val string_account = csv.map(_.getString(0)).collect.toList.mkString(",") is correct.

tree em Over a year ago

my case ACCOUNT_NUMBER is string data type like this ACCOUNT_NUMBER in ('14496084' ,'14038388' ,'12602537' ,'14497935' ,'11913927')

Shaido Over a year ago

@kn3l: I see, then you need to add ' around each element. This can be done with mkString by adjusting it a bit: mkString("'", "', '", "'"). (The ( and ) is already in the sql query so no need to add here).

Collectives™ on Stack Overflow

Passing a list loaded from text file to sql query in Spark SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related