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

[PabloCSD]

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

[Alberto_Umana]

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

[PabloCSD]

Hello there @Alberto_Umana ,

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

nc

!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

[PabloCSD]

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.].

[PabloCSD · Accepted solution]

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()
)