0
358
Question Pushyanthkumar Mukkala · Aug 4, 2023

Azure Databricks JDBC connection to Intersystems

#InterSystems IRIS

We encountered difficulties while attempting to establish a JDBC connection to Intersystems using AZURE Databricks, resulting in an inability to retrieve data. The JDBC version utilized was intersystems-jdbc-3.3.1.jar. If anyone has successfully employed Databricks for establishing a connection, we would appreciate information regarding the libraries you used

Error Message:
org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 0.0 failed 4 times, most recent failure: Lost task 0.3 in stage 0.0 (TID 3) (10.140.70.71 executor driver): java.sql.SQLException: [SQLCODE: <-25>:<Input encountered after end of query>]

Product version: IRIS 2022.3
Discussion (3)0
Log in or sign up to continue

Comments

David Hockenbroch · Aug 4, 2023

What was the query you were trying to run? This looks like an issue with the way the query was written.

0
Pushyanthkumar Mukkala  Aug 7, 2023 to David Hockenbroch

Hi David

We followed the instructions in the Spark JDBC connection guide. We think the problem might have something to do with a library, but we're not sure

Below is the query:

cacheDF = spark.read \
    .format('jdbc') \
    .option('url', connectString) \
    .option("query", "select AdjudicatedSvcNum from CLAIMS.AdjudicatedSvcs") \
    .option('user', user) \
    .option('password', password) \
    .option('driver','com.intersystems.jdbc.IRISDriver') \
    .load()
cacheDF.show()

0
Pushyanthkumar Mukkala · Aug 4, 2023

We followed the instructions in the Spark JDBC connection guide. We think the problem might have something to do with a library, but we're not sure

Below is the query:


cacheDF = spark.read \
    .format('jdbc') \
    .option('url', connectString) \
    .option("query", "select AdjudicatedSvcNum from CLAIMS.AdjudicatedSvcs") \
    .option('user', user) \
    .option('password', password) \
    .option('driver','com.intersystems.jdbc.IRISDriver') \
    .load()
cacheDF.show()

0
Vishwas Gupta  Sep 27, 2024 to sween

Hello Ron

I am using dbtable only 

final_result = (spark.read.format("jdbc")\

    .option("url", jdbcUrl)
    .option("driver", "com.intersystems.jdbc.IRISDriver")
   .option("dbtable", f"({sql}) as temp;")
   .option("user", user)
    .option("password", password)
   .option("sslConnection","true")
   .load())
This works fine until I add one specific column from the same table, when I add that column I get following error 
< Input (;) encountered after end of query

using - intersystems-jdbc-3.8.0.jar
Kindly help

0
Karthik P  Jul 27 to Karthik P

I tried below code, which is providing incorrect data. For String datatype columns, it is providing column name as values for rows and for Integer datatype columns, it is providing 0 as values for rows.
Could someone assist with this issue? or How to read data from InterSystems Cache DB to Databricks using JDBC?
 

df = spark.read \
    .format("jdbc") \
    .option("url", f"jdbc:Cache://{server_ip}:{port}/{namespace}") \
    .option("driver", "com.intersys.jdbc.CacheDriver") \
    .option("dbtable", "(SELECT * FROM Sample.Company) AS t;") \
    .option("user", username) \
    .option("password", password) \
    .option("fetchsize", "1000") \
    .option("pushDownPredicate", "false") \
    .option("pushDownAggregate", "false") \
    .option("pushDownLimit", "false") \
    .load()

df.show()


Below is the output, I received:
   

0