topic How to connect via JDBC to SAP-HANA in a Databricks Notebook? in Data Engineering

How to connect via JDBC to SAP-HANA in a Databricks Notebook?

PabloCSD — Mon, 06 Jan 2025 21:07:29 GMT

I have a set of connection credentials for SAP-HANA, how can I retrieve data from that location using JDBC?

I have already installed in my cluster the ngdbc.jar (for the driver), but this simple Query has already taken more than 5 minutes and I don't know if it is doing something or not. The HOST was already added to the network routing tables for reaching the HOST.

EDIT: It failed by timeout.

Py4JJavaError: An error occurred while calling o578.load. : com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Cannot connect to jdbc:sap://[REDACTED]:30013/ [Cannot connect to host [REDACTED]:30013 [Connection timed out], -813.].

The Script:

# datos de input query = "SELECT COUNT(*) FROM MY_TABLE" # SQL connection string jdbcHostname = dbutils.secrets.get(scope="my_kv", key="host") jdbcPort = 30013 jdbcDatabase = "MY_DATABASE" jdbcUrl = f"jdbc:sap://{jdbcHostname}:{jdbcPort}/?databaseName={jdbcDatabase}" jdbcUsername = dbutils.secrets.get(scope="my_kv", key="user") jdbcPassword = dbutils.secrets.get(scope="my_kv", key="password") attempts = 0 max_attempts = 3 while attempts < max_attempts: try: df_input_data = ( spark.read.format("jdbc") .option("driver", "com.sap.db.jdbc.Driver") .option("url", jdbcUrl) .option("user", jdbcUsername) .option("password", jdbcPassword) .option("query", query) .option("timeout", "5") .option("fetchsize", "1000") .load() ) display(df_input_data) break except Exception as e: attempts += 1 if attempts == max_attempts: raise e

Re: How to connect via JDBC to SAP-HANA in a Databricks Notebook?

Alberto_Umana — Mon, 06 Jan 2025 22:03:09 GMT

Hi @PabloCSD,

Can you run this command?

nc -zv <hostname> <port>

Also do a telnet just to confirm the connectivity.

Adding this articule:

https://www.databricks.com/blog/fastest-way-access-sap-hana-data-databricks-using-sap-sparkjdbc

Re: How to connect via JDBC to SAP-HANA in a Databricks Notebook?

PabloCSD — Tue, 07 Jan 2025 14:44:11 GMT

Hello there @Alberto_Umana ,

After the architecture team asociated the subnet, when I run the command:

!nc -zv 172.XY.ZKL.MN 30013 [REDACTED]: inverse host lookup failed: Unknown host (UNKNOWN) [[REDACTED]] 30013 (?) open

ping

!ping -c 4 172.XY.ZKL.MN PING [REDACTED] ([REDACTED]) 56(84) bytes of data. 64 bytes from [REDACTED]: icmp_seq=1 ttl=57 time=138 ms 64 bytes from [REDACTED]: icmp_seq=2 ttl=57 time=130 ms 64 bytes from [REDACTED]: icmp_seq=3 ttl=57 time=127 ms 64 bytes from [REDACTED]: icmp_seq=4 ttl=57 time=127 ms --- [REDACTED] ping statistics --- 4 packets transmitted, 4 received, 0% packet loss, time 3000ms rtt min/avg/max/mdev = 126.922/130.489/137.854/4.449 ms

telnet:

telnet 172.XY.ZKL.MN 30013 Trying [REDACTED]... Connected to [REDACTED]. Escape character is '^]'. Connection closed by foreign host.

Even though the connection is ok, the query is not working by authentication:

df_my_table = (spark.read .format("jdbc") .option("driver","com.sap.db.jdbc.Driver") .option("url", f'jdbc:sap://{jdbcHostname}:30013/?autocommit=false') .option("dbtable", "MY_TABLE") .option("user", jdbcUsername) ## should be using databricks secrets instead of putting credentials in code .option("password", jdbcPassword) ## should be using databricks secrets instead of putting credentials in code .load() )

Py4JJavaError: An error occurred while calling o1040.load. : com.sap.db.jdbc.exceptions.jdbc40.SQLInvalidAuthorizationSpecException: [10]: authentication failed at com.sap.db.jdbc.exceptions.jdbc40.SQLInvalidAuthorizationSpecException.createException(SQLInvalidAuthorizationSp

I was following the url that you provided me

Re: How to connect via JDBC to SAP-HANA in a Databricks Notebook?

PabloCSD — Tue, 07 Jan 2025 14:59:37 GMT

Added the database parameter:

df_sap_hana_table = (spark.read .format("jdbc") .option("driver", "com.sap.db.jdbc.Driver") .option("url", f'jdbc:sap://{jdbcHostname}:{jdbcPort}/?databaseName={jdbcDatabase}') .option("query", "SELECT 1 FROM DUMMY") .option("user", jdbcUsername) .option("password", jdbcPassword) .load() )

Still getting the timeout error:

Py4JJavaError: An error occurred while calling o1100.load. : com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Cannot connect to jdbc:sap://[REDACTED]:30013/ [Cannot connect to host 172.XY.ZKL.MN:30041 [Connection timed out], -813.].

Re: How to connect via JDBC to SAP-HANA in a Databricks Notebook?

PabloCSD — Tue, 07 Jan 2025 16:52:57 GMT

It worked changing the port to: 30041, the port for the next tenant (reference: https://community.sap.com/t5/technology-q-a/hana-connectivity-and-ports/qaq-p/12193927 ).

jdbcQuery = '(SELECT * FROM DUMMY)' df_sap_hana_dummy_table = (spark.read .format("jdbc") .option("driver", "com.sap.db.jdbc.Driver") .option("url", f"jdbc:sap://{jdbcHostname}:30041/?databaseName={jdbcDatabase}") .option("dbtable", jdbcQuery) .option("user", jdbcUsername) .option("password", jdbcPassword) .load() )