How to execute .sql file in spark using python

Question

from pyspark import SparkConf, SparkContext
from pyspark.sql import SQLContext

conf = SparkConf().setAppName("Test").set("spark.driver.memory", "1g")
sc = SparkContext(conf = conf)

sqlContext = SQLContext(sc)

results = sqlContext.sql("/home/ubuntu/workload/queryXX.sql")

When I execute this command using: python test.py it gives me an error.

y4j.protocol.Py4JJavaError: An error occurred while calling o20.sql. : java.lang.RuntimeException: [1.1] failure: ``with'' expected but `/' found

/home/ubuntu/workload/queryXX.sql

at scala.sys.package$.error(package.scala:27)

I am very new to Spark and I need help here to move forward.

zero323 · Accepted Answer · 2015-10-06 07:04:59Z

12

SqlContext.sql expects a valid SQL query not a path to the file. Try this:

with open("/home/ubuntu/workload/queryXX.sql") as fr:
   query = fr.read()
results = sqlContext.sql(query)

answered Oct 6, 2015 at 7:04

zero323

331k108 gold badges982 silver badges958 bronze badges

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

Comments

Jiacai Liu · Accepted Answer · 2016-07-20 07:15:06Z

Run spark-sql --help will give you

CLI options:
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

So you can execute your sql script like this:

spark-sql -f <your-script>.sql

WoodChopper · Accepted Answer · 2015-10-06 05:17:29Z

0

I'm not sure will it answer your question. But if you intend to run query on existing table you can use,

spark-sql -i <Filename_with abs path/.sql>

One more thing, if you have pyspark script you can use spark-submit details in here.

answered Oct 6, 2015 at 5:17

WoodChopper

4,4056 gold badges34 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to execute .sql file in spark using python

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related