ES-Hive integration is not working on secured elastic cluster
The details are below
Elasticsearch 7.8.0 (basic license), Hive 1.2.1000.2.6.5.0-292
TLS/SSL secured cluster has been configured and tested successfully with curl command and java JEST client on RHEL
curl --cacert /path/to/http.crt -u estester:estester_pwd -XGET 'https://ip:port/_cat/nodes?v'
curl --cacert /path/to/http.crt -u estester:estester_pwd -XGET 'https://ip:port/_cat/indices/books?pretty'
elasticsearch.yml
# Security configuration
xpack.security.enabled: true
# TLS/SSL encryption inter-node communication
xpack.security.transport.ssl.enabled: true
xpack.security.transport.ssl.verification_mode: certificate
xpack.security.transport.ssl.keystore.path: certs/elastic-certificates.p12
xpack.security.transport.ssl.truststore.path: certs/elastic-certificates.p12
# TLS/SSL encryption http client communication
xpack.security.http.ssl.enabled: true
xpack.security.http.ssl.keystore.path: "http.p12"
xpack.security.http.ssl.truststore.path: "http.p12"
xpack.security.http.ssl.client_authentication: optional
xpack.security.transport.ssl.supported_protocols: [ "TLSv1.2", "TLSv1.1", "TLSv1"]
Add jars
hive> ADD JAR hdfs://xxxxx/tmp/elasticsearch-hadoop-7.8.0.jar;
hive> ADD JAR .../commons-httpclient-3.0.1.jar;
Create table
hive> create external table if not exists db_name.tbl_name (
id string,
title string,
year string)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES(
"es.nodes"="xx.xxx.xx.xxx",
"es.port"="xxxx",
"es.resource.read"="books/book",
"es.read.operation"="index",
"es.index.read.missing.as.empty"="true",
"es.nodes.discovery"="true",
"es.read.metadata"="true",
"es.mapping.names"="id:_metadata._id, title:title, year:year",
"es.net.ssl"="true",
"es.net.ssl.cert.allow.self.signed"="true",
"es.net.ssl.keystore.type"="PKCS12",
"es.net.ssl.keystore.location"="hdfs:///xxxxx_domain/tmp/http.p12",
"es.net.ssl.truststore.location"="hdfs:///xxxxx_domain/tmp/http.p12",
"es.net.http.auth.user"="estester",
"es.net.http.auth.pass"="estester_pwd");
Table created successfully, select failed with the details below
hive> select * from db_name.tbl_name;
OK
Failed with exception java.io.IOException:org.elasticsearch.hadoop.EsHadoopIllegalArgumentException: Cannot detect ES version- this typically happens if network/elasticsearch cluster is not accessible or when targetting a WAN/Cloud instance without proper setting in 'es.nodes.wan.only'
Note: When setting and configuring elastic cluster, I generated the following certificate files:
elastic-certificates.p12, elastic-stack-ca.p12, http.p12, http.crt
These files reside also in hdfs:///xxxxx/tmp/
A few questions please:
Q1: which of the above mentioned certificate file should be used for es.net.ssl.keystore.location and es.net.ssl.truststore.location ?
Q2: what would be the correct value for es.net.ssl.keystore.type ?
Q3: is referencing directory like hdfs:///xxxxx_domain/tmp correct for es.net.ssl.keystore.location and es.net.ssl.truststore.location ?
Am I missing something in table's properties or elsewhere ?
Thanks in advance