使用hive访问elasticsearch的数据
1.配置
将elasticsearch-hadoop-2.1.1.jar拷贝到hive/lib
hive -hiveconf hive.aux.jars.path=/usr/local/hive-1.2.1/lib/elasticsearch-hadoop-2.1.1.jar
或者配置:
hive-site.xml
<property>
<name>hive.aux.jars.path</name>
<value>file:///usr/local/hive-1.2.1/lib/elasticsearch-hadoop-2.1.1.jar</value>
<description>A comma separated list (with no spaces) of the jar files</description>
</property>
2.创建外表
CREATE EXTERNAL TABLE achi_ex
( vid
string, dtime
timestamp, platid
bigint)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED BY ‘org.elasticsearch.hadoop.hive.EsStorageHandler’
TBLPROPERTIES(‘es.resource’ = ‘db_1/achi’,
‘es.index.auto.create’ = ‘true’);
*hive 中的timestamp和ES中的date相互映射
3.导入数据
insert overwrite table achi_ex select * from achi;
4.测试
select * from achi_ex limit 10;
错误:
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.IntWritable
hive表中field类型为int时,映射到es中变成long,所以会报此错误。将hive表中int改为bigint即可。
CREATE EXTERNAL TABLE achi_ex
( vid
string, dtime
timestamp, platid
bigint)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED BY ‘org.elasticsearch.hadoop.hive.EsStorageHandler’
TBLPROPERTIES(‘es.resource’ = ‘db_1/achi’,
‘es.mapping.names’ = ‘vid:vID,dtime:dTime,platid:PlatID
‘);