How to execute multi line sql in spark sql

Question

How can I execute lengthy, multiline Hive Queries in Spark SQL? Like query below:

val sqlContext = new HiveContext (sc)
val result = sqlContext.sql ("
 select ...
 from ...
");

Answer 1

Use """ instead, so for example

val results = sqlContext.sql ("""
     select ....
     from ....
""");

or, if you want to format code, use:

val results = sqlContext.sql ("""
     |select ....
     |from ....
""".stripMargin);

Answer 2

You can use triple-quotes at the start/end of the SQL code or a backslash at the end of each line.

val results = sqlContext.sql ("""
    create table enta.scd_fullfilled_entitlement as
    select *
    from my_table
    """);

results = sqlContext.sql (" \
    create table enta.scd_fullfilled_entitlement as \
    select * \
    from my_table \
    ")

Answer 3

val query = """(SELECT
      a.AcctBranchName,
    c.CustomerNum,
    c.SourceCustomerId,
    a.SourceAccountId,
    a.AccountNum,
    c.FullName,
    c.LastName,
    c.BirthDate,
    a.Balance,
    case when [RollOverStatus] = 'Y' then 'Yes' Else 'No' end as RollOverStatus
    FROM
    v_Account AS a left join v_Customer AS c
      ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate
    WHERE
    a.Category = 'Deposit' AND
    c.Businessdate= '2018-11-28'  AND
    isnull(a.Classification,'N/A') IN ('Contractual Account','Non-Term Deposit','Term Deposit')
    AND IsActive = 'Yes' ) tmp """