after many trials with the Hive Connector Node on my Windows 10 client I don’t get any further.
We had a successful (!) connection to our Cloudera system with the Squirrel SQL client using the following URL:
jdbc:hive2://si-384l.de.bosch.com:10000/esc6;ssl=1;AuthMech=1;KrbServiceName=hive;KrbHostFQDN=si-384l.de.bosch.com;KrbRealm=RB-AA-SI01.BDPS.BOSCH-ORG.COM;mapreduce.job.queuename=root.default
based on the Hive jdbc driver com.cloudera.hive.jdbc41.HS2Driver
which (according the KNIME log file) creates the right URL
Additionally I added to the knime.ini file
-Djava.security.krb5.conf=C:/Users/grr2pl/krb5.ini
We use LSA authentication which is enabled by the Windows registry
After node execution the error message is as follows:
Execute failed: “Could not create connection to database: unable to find valid certification path to requested target”
You are connecting to Hive using SSL, which requires Hiveserver to present a SSL certificate signed by a CA (certificate authority) that the client (=JDBC driver) trusts. The error you are getting basically means, that the client does not trust that CA.
This is often the case for self-signed certificates or for company-internal corporate CAs.
This can be fixed by providing a so-called “truststore” in JKS format to the JDBC driver. The truststore needs to contain the CA certificate and mark it as trusted.
Step 1: Create a JKS truststore file
If you already a truststore file of course you can skip this step.
To create the file you can use a separate program called “Portecle”: http://portecle.sourceforge.net/
After installing and starting Portecle do the following to download the certificate:
Usually you don’t just get one certificate but a whole chain.
For each certificate, click on “PEM Encoding” > Save (save each one in its own .pem file)
Now you have the whole certificate chain in PEM format. To make a new truststore:
File > New Keystore
Select “JKS” and then click OK
For each saved PEM certificate:
** Click Tool > Import Trusted Certificate
** Select the PEM file and then click “Import”
** If asked “Do you want to accept the certificate as trusted?” then click “Yes”. Choose a uniqe alias for each one.
Save the newly created truststore. Choose a simple password. You only need a password here because the JDBC driver will demand that. Conceptually there is nothing worth protecting in the truststore.
Step 2: Configure the JDBC driver to use the truststore
Append the following JDBC parameters in the Hive Connector node (adjust as necessary): SSLTrustStore=C:\path\to\truststore.jks;SSLTrustStorePwd=the_password