How to load sql file in spark using python

Question

My pySpark version is 2.4 and python version is 2.7. I have multiple line sql file which needs to run in spark. Instead of running line by line, is it possible to keep the sql file in python (which initialize spark) and execute it using spark-submit ? I am trying to write a generic script in python so that we just need to replace sql file from hdfs folder later. Below is my code snippet.

import sys
from pyspark.sql import SparkSession
import pyspark.sql.functions as F

args = str(sys.argv[1]).split(',')

fd = args[0]
ld = args[1]
sd = args[2]

#Below line does not work
df = open("test.sql")
query = df.read().format(fd,ld,sd)

#Initiating SparkSession.
spark = SparkSession.builder.appName("PC").enableHiveSupport().getOrCreate()


#Below line works fine
df_s=spark.sql("""select * from test_tbl where batch_date='2021-08-01'""")

#Execute the sql (Does not work now)
df_s1=spark.sql(query)

spark-submit throws following error for the above code.

Exception in thread "main" org.apache.spark.SparkException: Application application_1643050700073_7491 finished with failed status at org.apache.spark.deploy.yarn.Client.run(Client.scala:1158) at org.apache.spark.deploy.yarn.YarnClusterApplication.start(Client.scala:1606) at org.apache.spark.deploy.SparkSubmit.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:847) at org.apache.spark.deploy.SparkSubmit.doRunMain$1(SparkSubmit.scala:161) at org.apache.spark.deploy.SparkSubmit.submit(SparkSubmit.scala:184) at org.apache.spark.deploy.SparkSubmit.doSubmit(SparkSubmit.scala:86) at org.apache.spark.deploy.SparkSubmit$$anon$2.doSubmit(SparkSubmit.scala:922) at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:931) at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala) 22/02/10 01:24:52 INFO util.ShutdownHookManager: Shutdown hook called

I am relatively new in pyspark. Can anyone please guide me what I am missing here ?

Answer 1

You cant run pyspark on your local directory. If you want to do an sql statement on a File in HDFS, you have to put your file from HDFS, first on your local directory.

Referred to spark 2.4.0 Spark Documentation, you can simply use the pyspark API.

from os.path import expanduser, join, abspath   
from pyspark.sql import SparkSession
from pyspark.sql import Row

spark.sql("YOUR QUERY").show()

or query files directly with:

df = spark.sql("SELECT * FROM parquet.`examples/src/main/resources/users.parquet`")