How to pass multiple statements into Spark SQL HiveContext

Question

For example I have few Hive HQL statements which I want to pass into Spark SQL:

set parquet.compression=SNAPPY;
create table MY_TABLE stored as parquet as select * from ANOTHER_TABLE;
select * from MY_TABLE limit 5;

Following doesn't work:

hiveContext.sql("set parquet.compression=SNAPPY; create table MY_TABLE stored as parquet as select * from ANOTHER_TABLE; select * from MY_TABLE limit 5;")

How to pass the statements into Spark SQL?

Just like you would do in Java (or any other language that uses a database driver): execute *each* statement in order. That's what the Hive CLI does, actually, parsing its input into individual HQL statements -- plus doing some variable substitution and stuff like that. — Samson Scharfrichter, Apr 29 '16 at 22:57
@SamsonScharfrichter Thank you. I think this would be acceptable answer. — Andrey Dmitriev, May 01 '16 at 10:43

kuldeep singh · Answer 1 · 2022-07-01T06:47:15.747

6

I worked on a scenario where i needed to read a sql file and run all the; separated queries present in that file.

One simple way to do it is like this:

val hsc = new org.apache.spark.sql.hive.HiveContext(sc)
val sql_file = "/hdfs/path/to/file.sql"
val f = sc.wholeTextFiles(s"$sql_file")
val queries = f.take(1)(0)._2
Predef.refArrayOps(queries.split(';')).map(query => hsc.sql(query))

edited Jul 01 '22 at 06:47

answered Nov 14 '17 at 13:01

kuldeep singh

71
1
3

Hello @kuldeep singh, please format your code, here a link to help you with that: https://stackoverflow.com/help/formatting – Alejandro Montilla Nov 14 '17 at 13:20

score 4 · Accepted Answer · answered May 01 '16 at 10:45

4

Thank you to @SamsonScharfrichter for the answer.

This will work:

hiveContext.sql("set spark.sql.parquet.compression.codec=SNAPPY")
hiveContext.sql("create table MY_TABLE stored as parquet as select * from ANOTHER_TABLE")
val rs = hiveContext.sql("select * from MY_TABLE limit 5")

Please note that in this particular case instead of parquet.compression key we need to use spark.sql.parquet.compression.codec

answered May 01 '16 at 10:45

Andrey Dmitriev

528
2
9
27

Is it a bug from Spark SQL parser? Because this is possible with Hive already. – Thomas Decaux Oct 13 '17 at 12:25

score 0 · Answer 3 · answered Feb 16 '22 at 05:28

This function helped me get around Spark limitation:

def execute_query(spark, sql_script):
    sql_arr = sql_script.split(";")
    #print(sql_arr)
    for sql in sql_arr:
        if len(sql.strip()) > 0:
            print(sql)
            #spark.sql(sql.strip())
            print(spark.sql(sql))

How to pass multiple statements into Spark SQL HiveContext

3 Answers3

Linked

Related