hive操作elasticsearch

一、es添加数据

curl -XPUT 'masterhadoop:9600/customer/user/00002' -d '{"name":"lisi","sex":"M","phone":"13379434343","age":20}';

二、进入到hive cli命令控制台，添加依赖的jar

add jar file:////home/elasticsearch-hadoop-5.3.1/dist/elasticsearch-hadoop-hive-5.3.1.jar;

三、创建es数据对应的hive关联表

1）es无searchguard插件的

CREATE EXTERNAL TABLE es_customer_user(

 id STRING,

 name STRING,

 phone STRING,

 sex STRING,

 age BIGINT

)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES(

 'es.nodes'='masterhadoop:9600',

 'es.index.auto.create'='true',

 'es.resource'='customer/user',

 'es.read.metadata' = 'true',

 'es.mapping.names'='id:_metadata._id,name:name,phone:phone,sex:sex,age:age'

);

2）es集成searchgurad插件的情况：

CREATE EXTERNAL TABLE es_customer_user_2(

 id STRING,

 name STRING,

 phone STRING,

 sex STRING,

 age BIGINT

)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES(

 'es.nodes'='masterhadoop:9200',

 'es.net.ssl' = 'true',

 'es.net.ssl.keystore.location' = 'file:////usr/local/test/elasticsearch-5.3.0/config/CN=node-1-keystore.jks',

 'es.net.ssl.keystore.pass' = 'changeit',

 'es.net.ssl.keystore.type' = 'JKS',

 'es.net.ssl.truststore.location' = 'file:////usr/local/test/elasticsearch-5.3.0/config/truststore.jks',

 'es.net.ssl.truststore.pass' = 'changeit',

 'es.net.http.auth.user' = 'admin',
 'es.net.http.auth.pass' = 'admin',

 'es.index.auto.create'='false',

 'es.resource'='customer/user',

 'es.read.metadata' = 'true',

 'es.mapping.names'='id:_metadata._id,name:name,phone:phone,sex:sex,age:age'

);

三、验证

1) select * from es_customer_user;

2) select * from es_customer_user_2;

三、备注

如果在同一个hive cli上下文，映射二种不同环境的es（es和es+searchguard），其中会有一个查询会报错的，貌似是由于有些变量是共用的，所以最好开二个cli进行操作，当然实际情况中肯定是同样的es环境。