Why is dbtable / query required in spark.jdbc

user_15929504 0
Viewed 1310

I am a SPARK noob, and I am unclear why the dbtable OR query is required as a part of the JDBC options.

e.g. Using this with the Presto JDBC driver, the Presto Driver does not like the url, driver, dbtable and query parameters. Other drivers perform similar validations (e.g. CData Driver for Presto)

url = "jdbc:presto:Server=spill.asifkazi.cp.ahana.cloud;Port=443;"
jdbcDriver = "com.facebook.presto.jdbc.PrestoDriver" 
sqlQuery = "select * from customer limit 1"
jdbcOptions = spark.read.format("jdbc")
jdbcOptions.option("url",jdbcUrl)
jdbcOptions.option("user", user)
jdbcOptions.option("password", password)
jdbcOptions.option("query",sqlQuery)
df = jdbcOptions.load()
df.show()

21/05/13 21:50:41 INFO SharedState: Warehouse path is 'file:/Users/asifkazi/Downloads/Projects/pyspark/spark-warehouse'.
Traceback (most recent call last):
  File "/Users/asifkazi/Downloads/Projects/pyspark/test_jdbc.py", line 32, in <module>
    df = jdbcOptions.load()
  File "/usr/local/Cellar/apache-spark/3.1.1/libexec/python/lib/pyspark.zip/pyspark/sql/readwriter.py", line 210, in load
  File "/usr/local/Cellar/apache-spark/3.1.1/libexec/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py", line 1304, in __call__
  File "/usr/local/Cellar/apache-spark/3.1.1/libexec/python/lib/pyspark.zip/pyspark/sql/utils.py", line 111, in deco
  File "/usr/local/Cellar/apache-spark/3.1.1/libexec/python/lib/py4j-0.10.9-src.zip/py4j/protocol.py", line 326, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o35.load.
: java.sql.SQLException: Unrecognized connection property 'driver'
    at com.facebook.presto.jdbc.PrestoDriverUri.validateConnectionProperties(PrestoDriverUri.java:353)
    at com.facebook.presto.jdbc.PrestoDriverUri.<init>(PrestoDriverUri.java:104)
    at com.facebook.presto.jdbc.PrestoDriverUri.<init>(PrestoDriverUri.java:94)
    at com.facebook.presto.jdbc.PrestoDriver.connect(PrestoDriver.java:87)
    at org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49)
    at

Why am I not allowed to simply create a JDBC connection for spark and then run a query independently as I would in JDBC? Is there a way to accomplish the querying without passing the information as a part of the jdbc options?

pyspark apache-spark jdbc
2 Answers

e.g. Using this with the Presto JDBC driver, the Presto Driver does not like the url, driver, dbtable and query parameters. Other drivers perform similar validations (e.g. CData Driver for Presto)

It should accept those options, check this out https://stackoverflow.com/a/56806009/3441510

Why am I not allowed to simply create a JDBC connection for spark and then run a query independently as I would in JDBC? Is there a way to accomplish the querying without passing the information as a part of the jdbc options?

I don't see the point of having JDBC connection without running an actual query in Spark context (immediately). The main purpose of spark.read.jdbc is to establish a connection then load data in parallel with multiple executors, not to "test" the connection

user_34415100

You should be wrapping the query and passing to the database, Hoping that url is fine the below should work, I dont have presto test it out

query = """(select * from customer limit 1) query_wrap"""
url = 'jdbc:presto:Server=spill.asifkazi.cp.ahana.cloud;Port=443'
connectionProperties = {"user": "user" , "password": "paswwrod", "driver": "com.facebook.presto.jdbc.PrestoDriver", "fetchsize": "10000"}
df = spark.read.jdbc(url = url, table = query, properties = connectionProperties)
user_77182730
Related