Issues Connecting to Infor Data Lake Using Spark JDBC in Azure Synapse Spark Notebook

Question

Issues Connecting to Infor Data Lake Using Spark JDBC in Azure Synapse Spark Notebook

Sriram M 40

Hi Team,

I'm encountering difficulties while attempting to connect to Infor Data Lake using Spark JDBC within Azure Synapse. Despite configuring the necessary JDBC driver and ionapi file path, I consistently encounter the following error:

"Py4JJavaError: An error occurred while calling o3997.jdbc.

: java.lang.RuntimeException: JDBC Error 100

Unable to connect. The Infor Compass JDBC Driver.ionapi file is not found. The file must be located in the folder containing the Compass JDBC driver JAR file."

Setup Details:

I've initialized my Spark session in Azure Synapse with the required JDBC driver and specified the ionapi file path using spark.jars.

The ionapi file is stored in Azure Data Lake Storage (ADLS), and I have ensured the path is correctly specified in the JDBC connection properties.

Code Snippet:

%%configure -f

{

"conf": {  

    "spark.jars": "abfss://container@datalake_name.dfs.core.windows.net/infor-compass-jdbc-2023.10.jar",  

}

}

storage_account_name = "**"

container_name = "**"

jdbc_jar_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/path_to_jdbc_driver/infor-compass-jdbc-2023.10.jar"

ionapi_file_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/path_to_ionapi_file/Infor Compass JDBC Driver.ionapi"

spark = SparkSession.builder \

.appName("InforDataLakeConnection") \

.config("spark.jars", jdbc_jar_path) \

.getOrCreate()

jdbc_url = "jdbc:infordatalake://your_infordatalake_endpoint"

jdbc_properties = {

"user": "**",

"password": "**",

"driver": "com.infor.idl.jdbc.Driver", 

"ionapi": ionapi_file_path

}

table_name = "table_name"

df = spark.read.jdbc(url=jdbc_url, table=table_name, properties=jdbc_properties)

df.show()

Error Analysis:

I have verified that ionapi_file_path correctly points to the location of the ionapi file in ADLS.

The storage account key used in spark.conf.set matches the correct ADLS storage account.

Request for Assistance:

Could you please provide guidance on how to correctly configure the ionapi file path or any additional settings necessary to successfully connect to Infor Data Lake using Spark JDBC in Azure Synapse?

Harishga 6,000 Reputation points Microsoft External Staff

2024-07-05T06:46:35.4166667+00:00
Hi @Sriram M
Welcome to Microsoft Q&A platform and thanks for posting your question here.

You’re having trouble connecting to Infor Data Lake using Spark JDBC in Azure Synapse. The error message indicates that the ionapi file is not found.

You have already set up your Spark session with the JDBC driver and specified the ionapi file path, which is stored in Azure Data Lake Storage (ADLS). Despite this, the connection is failing.

To resolve this issue, first, ensure that the ionapi file path is correct and that the file permissions allow access from your Spark job. The error message suggests that the ionapi file needs to be in the same folder as the JDBC driver JAR file on the local system where the Spark job runs.

Next, download the ionapi file from ADLS to the local file system of the Spark cluster nodes. Finally, modify your Spark configuration to use the local path of the ionapi file.

Here’s an example of how you do this:

from pyspark.sql import SparkSession import os # Download ionapi file to local path local_ionapi_path = "/tmp/Infor Compass JDBC Driver.ionapi" dbutils.fs.cp(ionapi_file_path, "file://" + local_ionapi_path) # Initialize Spark session with local ionapi file path spark = SparkSession.builder \ .appName("InforDataLakeConnection") \ .config("spark.jars", jdbc_jar_path) \ .config("spark.executor.extraClassPath", "/path/to/jdbc/driver") \ .getOrCreate() jdbc_url = "jdbc:infordatalake://your_infordatalake_endpoint" jdbc_properties = { "user": "**", "password": "**", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path } table_name = "table_name" df = spark.read.jdbc(url=jdbc_url, table=table_name, properties=jdbc_properties) df.show()

Lastly, check the Spark logs for more details on why the ionapi file isn’t being found. By following these steps, you should be able to resolve the connection issue

I hope this information helps you. Let me know if you have any further questions or concerns.
Sriram M 40 Reputation points

2024-07-05T08:45:53.77+00:00

Thanks for your response.

The above code which you have provided is supported for Databricks environment. As we are trying to implement the same in synapse spark environment we are still getting the same error.

Could you please suggest the steps/code which can be used in the Synapse Spark notebook.

Note: The major issue we are facing currently is, it is unable to read the ionapi file in the spark code even though we have placed the file in same location where the jar file is placed in ADLS Gen2. If there is any workaround using python code, please suggest the steps/code for the same.

Thanks in advance!
Harishga 6,000 Reputation points Microsoft External Staff

2024-07-08T09:35:49.93+00:00
Hi @Sriram M
To resolve the issue in Azure Synapse Spark notebook, you can follow these steps:

To download the ionapi file to your local system using Azure Synapse, use the mssparkutils library to get the file from ADLS Gen2.

Here's an example:

from mssparkutils import fs ionapi_file_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/path_to_ionapi_file/Infor Compass JDBC Driver.ionapi" local_ionapi_path = "/tmp/Infor Compass JDBC Driver.ionapi" fs.download_file(ionapi_file_path, local_ionapi_path)

Update your Spark settings to use the local ionapi file path.

spark = SparkSession.builder \ .appName("InforDataLakeConnection") \ .config("spark.jars", jdbc_jar_path) \ .config("spark.executor.extraClassPath", "/path/to/jdbc/driver") \ .getOrCreate() jdbc_properties = { "user": "**", "password": "**", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path }

Check the ionapi file permissions to make sure the Spark job can access it. Following these steps will help you connect to Infor Data Lake using Spark JDBC in Azure Synapse.

I hope this information helps you. Let me know if you have any further questions or concerns.
Vijayakumar Konduru 0 Reputation points

2024-07-08T17:42:56.02+00:00
Hi,

Thanks for your response.

Tried all approaches.

jdbc_jar_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/infor-compass-jdbc-2023.10.jar" # Path in your storage account

ionapi_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/Infor Compass JDBC Driver.ionapi" # Path in your storage account

key='xxxxx'

from notebookutils import mssparkutils

#from mssparkutils import fs

ionapi_file_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/Infor Compass JDBC Driver.ionapi"

local_ionapi_path = "/tmp/Infor Compass JDBC Driver.ionapi"

mssparkutils.fs.cp(ionapi_file_path, local_ionapi_path)

//download_file method not available in fs package

from pyspark.sql import SparkSession

spark = SparkSession.builder \

.appName("InforDataLakeConnection") \

.config("spark.jars", jdbc_jar_path) \

.config("spark.executor.extraClassPath", "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/") \

.getOrCreate()

jdbc_properties = {

"user": "", "password": "", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path ```} jdbc_url="jdbc:infordatalake://tenant_id" table_name = "xxxxx" # df = spark.read.jdbc(url=jdbc_url, table=table_name, properties=jdbc_properties) after trying he above code we are facing the same issue in pyspark notebook. the file must be located in the folder containing the Compass JDBC driver JAR file." Is there any otherway we can call python script from synapse notebook level. Please suggest
Harishga 6,000 Reputation points Microsoft External Staff

2024-07-09T10:19:01.5433333+00:00
Hi @Vijayakumar Konduru

The issue you're facing is due to the ionapi file not being in the same directory as the JDBC driver JAR file. Here are a few suggestions to resolve this:

Copy the ionapi file to the same directory as the JDBC driver JAR file: You can use the mssparkutils.fs.cp method to copy the ionapi file to the same directory as the JDBC driver JAR file. For example:

jdbc_jar_dir = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/" ionapi_file_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/Infor Compass JDBC Driver.ionapi" local_ionapi_path = jdbc_jar_dir + "Infor Compass JDBC Driver.ionapi" mssparkutils.fs.cp(ionapi_file_path, local_ionapi_path)

Then, update the jdbc_properties dictionary to point to the new location of the ionapi file:

jdbc_properties = { "user": "", "password": "", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path }

Use a relative path for the ionapi file: You can specify a relative path for the ionapi file in the jdbc_properties dictionary. For example:

jdbc_properties = { "user": "", "password": "", "driver": "com.infor.idl.jdbc.Driver", "ionapi": "./Infor Compass JDBC Driver.ionapi" }

This assumes that the ionapi file is in the same directory as the JDBC driver JAR file.

Use a Python script to download and configure the ionapi file: You can create a Python script that downloads the ionapi file and configures it correctly. Then, you can call this script from your Synapse notebook using the command. For example:

! python download_and_configure_ionapi.py

The download_and_configure_ionapi.py script would contain the necessary code to download the ionapi file and configure it correctly.

Use a Spark configuration option to specify the ionapi file location: You can use the spark.executor.extraClassPath configuration option to specify the location of the ionapi file. For example:

spark = SparkSession.builder \ .appName("InforDataLakeConnection") \ .config("spark.jars", jdbc_jar_path) \ .config("spark.executor.extraClassPath", jdbc_jar_dir) \ .config("spark.executor.extraFiles", ionapi_file_path) \ .getOrCreate()

This will make the ionapi file available to the Spark executors.

I hope one of these suggestions helps you resolve the issue.

1 answer

Your answer

Sriram M 40 Reputation points

2024-07-05T08:45:53.77+00:00

Thanks for your response.

The above code which you have provided is supported for Databricks environment. As we are trying to implement the same in synapse spark environment we are still getting the same error.

Could you please suggest the steps/code which can be used in the Synapse Spark notebook.

Note: The major issue we are facing currently is, it is unable to read the ionapi file in the spark code even though we have placed the file in same location where the jar file is placed in ADLS Gen2. If there is any workaround using python code, please suggest the steps/code for the same.

Thanks in advance!
Harishga 6,000 Reputation points Microsoft External Staff

2024-07-08T09:35:49.93+00:00

Hi @Sriram M
To resolve the issue in Azure Synapse Spark notebook, you can follow these steps:

To download the ionapi file to your local system using Azure Synapse, use the mssparkutils library to get the file from ADLS Gen2.

Here's an example:

from mssparkutils import fs ionapi_file_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/path_to_ionapi_file/Infor Compass JDBC Driver.ionapi" local_ionapi_path = "/tmp/Infor Compass JDBC Driver.ionapi" fs.download_file(ionapi_file_path, local_ionapi_path)

Update your Spark settings to use the local ionapi file path.

spark = SparkSession.builder \ .appName("InforDataLakeConnection") \ .config("spark.jars", jdbc_jar_path) \ .config("spark.executor.extraClassPath", "/path/to/jdbc/driver") \ .getOrCreate() jdbc_properties = { "user": "**", "password": "**", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path }

Check the ionapi file permissions to make sure the Spark job can access it. Following these steps will help you connect to Infor Data Lake using Spark JDBC in Azure Synapse.

I hope this information helps you. Let me know if you have any further questions or concerns.
Vijayakumar Konduru 0 Reputation points

2024-07-08T17:42:56.02+00:00

Hi,

Thanks for your response.

Tried all approaches.

jdbc_jar_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/infor-compass-jdbc-2023.10.jar" # Path in your storage account

ionapi_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/Infor Compass JDBC Driver.ionapi" # Path in your storage account

key='xxxxx'

from notebookutils import mssparkutils

#from mssparkutils import fs

ionapi_file_path = "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/Infor Compass JDBC Driver.ionapi"

local_ionapi_path = "/tmp/Infor Compass JDBC Driver.ionapi"

mssparkutils.fs.cp(ionapi_file_path, local_ionapi_path)

//download_file method not available in fs package

from pyspark.sql import SparkSession

spark = SparkSession.builder \

.appName("InforDataLakeConnection") \

.config("spark.jars", jdbc_jar_path) \

.config("spark.executor.extraClassPath", "abfss://******@storageacoount.dfs.core.windows.net/pipeline_dev/wms/") \

.getOrCreate()

jdbc_properties = {

"user": "", "password": "", "driver": "com.infor.idl.jdbc.Driver", "ionapi": local_ionapi_path ```} jdbc_url="jdbc:infordatalake://tenant_id" table_name = "xxxxx" # df = spark.read.jdbc(url=jdbc_url, table=table_name, properties=jdbc_properties) after trying he above code we are facing the same issue in pyspark notebook. the file must be located in the folder containing the Compass JDBC driver JAR file." Is there any otherway we can call python script from synapse notebook level. Please suggest

Answer 1

Surprisingly, I have your answer and found this thread while looking for a better way to connect to the Infor data lake. If you've found a better way, please let me know as I was hoping to find either a way to use the API directly or to use the JDBC on the data gateway server.

For the JDBC connection where you can't reference the ionapi file, you have to modify the connection string to include the ion api credentials.

Open the .ionapi file in an editor, like Notepad++, and copy out the contents. You need to encode this string for URLs, I recommend adding the MIME Tools plugin to Notepad++, hightlight the string and select Plugins - MIME Tools - URL Encode. Copy this new string.

Back to your jdbc connection string, it begins with jdbc:infordatalake://TENANT_NAME, after the tenant name, add "?ionApiCredentials=" followed by the new string you copied, like this:

?ionApiCredentials=your_copied_url_encoded_ion_api_string

The rest of the parameters in the connection string would then follow the end of that string. I'm just learning to use Spark in Fabric, but I suspect there's a way to store that ion api string as a secure key that can be referenced in that string, but for now the above works.

Share via

Issues Connecting to Infor Data Lake Using Spark JDBC in Azure Synapse Spark Notebook

1 answer

Your answer