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

PabloCSD
Valued Contributor II

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