Cannot initialize SSL - Expected to find keystore file at

[crawdaddy18 (Chris C)]

Experts,

I am trying to push data from a Hive table over to ElasticSearch. There are numerous examples on the Internet of how to do this, but there are very few on how to do it when security is enabled on ElasticSearch. I have tried numerous combinations of properties for the TBL_PROPERTIES portion of the "CREATE EXTERNAL TABLE" but nothing works.

Here is my current attempt:

create external table sample_07_es (code string, description string, total_emp int, salary int) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES('es.resource' = 'job/jobs','es.nodes'='mapr04.wired.carnoustie','es.index.auto.create' = 'true','es.net.ssl'='true','es.net.ssl.truststore.location'='/opt/mapr/elasticsearch/elasticsearch-6.2.3/etc/elasticsearch/keystores','es.net.http.auth.user'='admin','es.net.http.auth.pass'='admin');

And here is the error message in the Hive logs:

Caused by: org.elasticsearch.hadoop.EsHadoopIllegalStateException: Cannot initialize SSL - Expected to find keystore file at [/opt/mapr/elasticsearch/elasticsearch-6.2.3/etc/elasticsearch/keystores] but was unable to. Make sure that it is available on the classpath, or if not, that you have specified a valid URI.

	at org.elasticsearch.hadoop.rest.commonshttp.SSLSocketFactory.createSSLContext(SSLSocketFactory.java:173)

	at org.elasticsearch.hadoop.rest.commonshttp.SSLSocketFactory.getSSLContext(SSLSocketFactory.java:158)

	at org.elasticsearch.hadoop.rest.commonshttp.SSLSocketFactory.createSocket(SSLSocketFactory.java:127)

	at org.apache.commons.httpclient.HttpConnection.open(HttpConnection.java:706)

	at org.apache.commons.httpclient.HttpMethodDirector.executeWithRetry(HttpMethodDirector.java:386)

	at org.apache.commons.httpclient.HttpMethodDirector.executeMethod(HttpMethodDirector.java:170)

	at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:396)

	at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:324)

	at org.elasticsearch.hadoop.rest.commonshttp.CommonsHttpTransport.execute(CommonsHttpTransport.java:478)

So, the error is around keystores, which are not my strongest area. I don't know what keystore Hive is looking for, but the error is coming from the ElasticSearch method createSSLContext, so my guess was that I needed to pass the ElastiicSearch keystores.

If I knew what keystore it was looking for, I could better troubleshoot the issue. I don't know if the keystore path should be a path on the local machine or a keystore path in the MapR FS.

My next step is to start reading through the code, I "think" this is the code that is being called:

KeyStore truststore = KeyStore.getInstance("jks");
try (InputStream is = Files.newInputStream(keyStorePath)) {
    truststore.load(is, keyStorePass.toCharArray());
}
SSLContextBuilder sslBuilder = SSLContexts.custom()
    .loadTrustMaterial(truststore, null);
final SSLContext sslContext = sslBuilder.build();
RestClientBuilder builder = RestClient.builder(
    new HttpHost("localhost", 9200, "https"))
    .setHttpClientConfigCallback(new HttpClientConfigCallback() {
        @Override
        public HttpAsyncClientBuilder customizeHttpClient(
                HttpAsyncClientBuilder httpClientBuilder) {
            return httpClientBuilder.setSSLContext(sslContext);
        }
    });

Welcome any thoughts or ideas!

[james.baiera]

ES-Hadoop doesn't allow loading a keystore from the local filesystem using that path format. The issue here is that ES-Hadoop anticipates needing to run on remote machines, so a basic path is assumed to be reachable from the classpath of the process that tries connecting. If you want to use a local file, you will need to distinguish it by adding file:/// to the front of it to qualify it as a filesystem URI.

That said, if you are running on Hive, every machine that ES-Hadoop might make a connection from will need a keystore file located at that path. That's usually the Hive server and all the hadoop/spark nodes that run the tougher queries.

[crawdaddy18 (Chris C)]

Thank you @james.baiera

I was able to finally figure it out. Here is what my command eventually looked like (not sure if it can be improved upon, but it works):

create external table sample_07_es (code string, description string, total_emp int, salary int) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES('es.resource' = 'job/jobs','es.nodes'='mapr04.wired.carnoustie','es.index.auto.create' = 'true','es.net.ssl'='true','es.net.ssl.keystore.location'='file:///opt/mapr/elasticsearch/elasticsearch-6.2.3/etc/elasticsearch/keystores/kibana-usr-keystore.jks','es.net.ssl.truststore.location'='file:///opt/mapr/elasticsearch/elasticsearch-6.2.3/etc/elasticsearch/keystores/truststore.jks','es.net.ssl.keystore.pass'='HIDDEN');

The password was found in the .keystore_password file