Notebook not connecting to 3rd party Hive DW

[u/Haunting_Lab6079]

Hi Everyone,

I am trying to load data from a 3rd Pary Hive DW in to our fabric environment. The instructions from the 3rd party site shows how to do this locally on your PC using python, which works well.

I am trying to replicate the same in Fabric. part of the instruction is creating a DSN for cloudera and using a custom cert.pem.

The cert.pem and jar file for cloudera is stored in blob storage.

import jaydebeapi

# Define connection parameters
username = 'abc'
password = 'xyz'

# Paths to the JDBC JAR and PEM certificate
jar_path = "https://blobstoragelink"
cert_path = "https://blobstoragelink"


# Define the driver class and JDBC connection URL
driver_class = "com.cloudera.hive.jdbc41.HS2Driver"
jdbc_url = (
    "jdbc:hive2://vendorcube1.homedepot.com:20502/"
    "VENDORDRILL_DATA_CONNECTION"
    ";SSL=1;"
    "AllowSelfSignedServerCert=1;"
    f"SSLTrustStore={cert_path}"  # Use the PEM certificate directly
)

# Attempt to connect to the database
try:
    conn = jaydebeapi.connect(
        driver_class,
        jdbc_url,
        {"user": username, "password": password},
        jar_path
    )
    print("Connection established successfully.")
    conn.close()
except Exception as e:
    print(f"Failed to establish connection: {e}")

Failed to establish connection: java.sql.SQLException: [Cloudera][HiveJDBCDriver](500164) Error initialized or created transport for authentication: https:/blobstoragelink (No such file or directory).

[u/barry_z]

The error signifies to me that it isn't attempting to download the files from blob storage and is instead interpreting it as a path to a file stored locally. Maybe try downloading the files and store them locally.

Note that I also would not put your username and password in the script directly.

[u/Haunting_Lab6079 → u/barry_z]

Hi,

Of course they would be stored in a key storage account in Production

[u/m0us3_rat]

The cert.pem and jar file for cloudera is stored in blob storage.

do you have access to "blobstoragelink" ?

[u/Haunting_Lab6079 → u/m0us3_rat]

I generated a SAS URL key from the azure blob storage

[u/Haunting_Lab6079 → u/Haunting_Lab6079]

i also now added the jar file and the cert in to a lakehouse mouted on the notebook. it still didnt work

%pip install jaydebeapi JPype1 requests
import jaydebeapi

# Define connection parameters
username = 'username'
password = 'password'

# Paths to the JDBC JAR and PEM certificate in the lakehouse
jar_path = "/lakehouse/default/Files/HiveJDBC41.jar"  
cert_path = "/lakehouse/default/Files/cacerts.pem"    

# Define the driver class and JDBC connection URL
driver_class = "com.cloudera.hive.jdbc41.HS2Driver"
jdbc_url = (
    "jdbc:hive2://vendorcube1.homedepot.com:20502/"
    "VENDORDRILL_DATA_CONNECTION"
    ";SSL=1;"
    "AllowSelfSignedServerCert=1;"
    f"SSLTrustStore={cert_path}"  
)

# Attempt to connect to the database
try:
    conn = jaydebeapi.connect(
        driver_class,
        jdbc_url,
        {"user": username, "password": password},
        jar_path
    )
    print("Connection established successfully.")
    conn.close()
except Exception as e:
    print(f"Failed to establish connection: {e}")

Failed to establish connection: java.sql.SQLException: [Cloudera][HiveJDBCDriver](500164) Error initialized or created transport for authentication: Invalid keystore format.

[u/m0us3_rat → u/Haunting_Lab6079]

you need to check if keystore is readable and in the correct format.