[关闭]
@aitanjupt 2015-12-17T08:52:27.000000Z 字数 6559 阅读 6040

使用Hive或Impala执行SQL语句,对存储在Elasticsearch中的数据操作

Hive Impala Elasticsearch Hadoop SQL


摘要:

使用Elasticsearch-SQL可以对存储在Elasticsearch中的数据执行简单的SQL查询操作,然而并不支持多表join等联接查询。
Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的SQL查询功能,可以将SQL语句转换为MapReduce任务进行运行。
因此如果Hive可以从Elasticsearch中取数据,并结合Hive的SQL查询功能,便能做到较为复杂的SQL查询操作。

我们的目标是:

  1. 支持Elasticsearch多表联接查询;
  2. 结合Elasticsearch搜索引擎提高SQL查询效率。

基础环境

CentOS-6.5
hive:hive-0.13.1+cdh5.3.6+397
impala:impala-2.1.5+cdh5.3.6+0
spark:spark-1.2.0+cdh5.3.6+379
elasticsearch:elasticsearch-1.7.1
elasticsearch-hadoop-hive:elasticsearch-hadoop-hive-2.1.1

Elasticsearch简介

Elasticsearch是一个基于Apache Lucene(TM)的开源搜索引擎。它基于RESTful web接口,提供实时、稳定、可靠的分布式服务。

Elasticsearch的数据类型

ES使用JSON作为数据交互格式,所以简单来说,JSON支持的数据类型,ES都支持。

  1. String: string
  2. Whole number: byte, short, integer, long
  3. Floating point: float, double
  4. Boolean: boolean
  5. Date: date

除了以上Core Types(核心数据类型),还有:Array Type、Object Type、Root Object Type、Nested Type、IP Type、Geo-point Type、Geo Shap Type、Attachment Type等。

Solr的数据类型

相应的Solr的数据类型有:

BCDIntField BCDLongField BCDStrField BinaryField BoolField
二进制整形字段 二进制长整形字段 二进制字符型字段 二进制数据 包含true或者false,值的第一个字符如果是1、t、T均表示true,其他为false
ByteField DateField DoubleField ExternalFileField FloatField
一个字节数据 一个精确到毫秒的时间值
IntField Location LongField Point RandomSortField
方位搜索用到,存经纬度值 N维点搜索,在蓝图或者CAD图中使用 不包含值,当查询时按这种类型的字段排序时会产生随机排序。使用该特性时需要是一个动态字段
ShortField StrField TextField UUIDField
UTF-8编码的字符串或Unicode 文本类型,一般包含多个单词或汉字 唯一识别码,单传入的值是“NEW”时,Solr将为其创建1个UUID值

数据存储在Elasticsearch上,使用Hive执行SQL语句

要打通Elasticsearch与Hive,需要Elasticsearch for Apache Hadoop下的elasticsearch-hadoop-hive。
Elasticsearch for Apache Hadoop提供了三类解决方案:Elasticsearch on YARN、repository-hdfs、elasticsearch-hadoop proper。
详情查看:Elasticsearch for Apache Hadoop

创建Hive外部表

先有Hive再有Elasticsearch

在Elasticsearch中并无表,甚至并无Elasticsearch服务时,可以通过执行以下语句在Hive中创建一个新的外部表。

  1. #在Hive中创建一个新的外部表
  2. hive> CREATE EXTERNAL TABLE hive_es_angel(
  3. id BIGINT,
  4. name STRING)
  5. STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
  6. TBLPROPERTIES('es.nodes'='192.168.181.190','es.resource' = 'indexname/typename');

这里指定了ES的地址与索引名和类型名(index-type)。通过执行此语句,便可以在Hive查看到结构正确的表。

注意
elasticsearch-hadoop will always convert Hive column names to lower-case,就是说建立起的Hive表中所有字段均变为小写。

在执行此语句后,并不会在ElasticSearch中建立指定的索引与类型,只有当后续在Hive中执行SQL插入语句时,才会在ElasticSearch中建立指定的索引与类型,并插入相关数据。
执行此语句并不会抛出错误,这与HBase和Hive建立关联时的情况不一样,创建HBase外部表时需要查看HBase中信息。 更多细节可以查看《使用Hive或Impala执行SQL语句,对存储在HBase中的数据操作》一文。

先有Elasticsearch再有Hive

在ElasticSearch中已有索引和类型,甚至在其中存储有数据时,我们可以在Hive中直接对ElasticSearch中数据进行操作。
即是在Hive中执行以上创建ES外部表的命令,即可以在Hue中看到ElasticSearch中的数据。
以下是几种典型的创建Hive外部表命令:

  1. CREATE EXTERNAL TABLE hive_es_decimal(
  2. price DECIMAL(12,2) )
  3. STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
  4. TBLPROPERTIES('es.nodes'='192.168.181.160','es.resource' = 'indexname/typename');

默认情况下,是以列名称与ES映射的,注意:elasticsearch-hadoop will always convert Hive column names to lower-case,此时ES列名也必须小写,否则映射不上。
经验证,以上语句有问题。如Hive定义为Decimal,而ES只能Double,会出现org.apache.hadoop.hive.serde2.io.DoubleWritable cannot be cast to org.apache.hadoop.hive.serde2.io.HiveDecimalWritable错误。

  1. CREATE EXTERNAL TABLE hive_es_map(
  2. price DOUBLE,
  3. itemid BIGINT,
  4. myname STRING
  5. )
  6. STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
  7. TBLPROPERTIES('es.nodes'='192.168.181.160','es.resource' = 'indexname/typename','es.mapping.names' = 'itemid:itemId, myname:myName, devicenumber:deviceNumber');

在创建Hive外部表时指定映射关系时,也需要对应好数据类型,否则也会出现类似数据类型转换失败错误。

注意
从ES的date数据类型转换到Hive的date/timestamp数据类型,均出错,出错信息为:java.io.IOException: java.lang.IllegalArgumentException: 2015-06-03T14:54:46+0800

向Elasticsearch写

  1. hive> add jar /opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/lib/hive/lib/elasticsearch-hadoop-hive-2.1.1.jar;
  1. #向ES中导入Hive中表sample_07数据
  2. hive> INSERT OVERWRITE TABLE hive_es_test
  3. SELECT s.id, s.code
  4. FROM sample_07 s;

当Elasticsearch中不存在相应索引时,经过执行以上插入语句,在Elasticsearch中就会创建一个新索引:indexname,新类型:typename(当然是在Hive创建新外部表时指定好的),数据也已经添加入其中。
没有指定列映射类型时,下面是部分默认情况下的Hive与ElasticSearch数据类型映射关系。

Hive ES 默认映射结果
bigint long 无问题
timestamp date 无问题
double double 无问题
date string 有问题
decimal string 有问题

注意
可以看到有问题的映射是两端不能匹配的映射。当存在有问题的映射时,在Hive中执行SELECT * FROM table_decimal时会抛出异常:

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.hive.serde2.io.HiveDecimalWritable

即数据类型从ElasticSearch到Hive数据类型转换失败。可见,在使用Hive对存储在ElasticSearch中的数据进行操作时,要注意使用双方均支持的数据类型。

从Elasticsearch读

我们选择以下几条有代表性的SQL查询语句,来验证使用Hive可以对存储在Elasticsearch中的数据进行正确的查询操作。

where子句

  1. hive> add jar /opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/lib/hive/lib/elasticsearch-hadoop-hive-2.1.1.jar;
  2. hive> select count(*) from hive_es_angel where id>1000;

在执行此语句时,可以看到map-reduce的执行过程:

  1. Hadoop job information for Stage-1: number of mappers: 5; number of reducers: 1
  2. 2015-10-15 14:30:28,181 Stage-1 map = 0%, reduce = 0%
  3. 2015-10-15 14:30:38,623 Stage-1 map = 2%, reduce = 0%, Cumulative CPU 4.07 sec
  4. 2015-10-15 14:30:39,678 Stage-1 map = 4%, reduce = 0%, Cumulative CPU 8.8 sec
  5. 2015-10-15 14:30:41,766 Stage-1 map = 5%, reduce = 0%, Cumulative CPU 9.36 sec

这说明,Hive在执行此语句时,并没有用到Elasticsearch建立的索引,以使查询效率更高,而是将Elasticsearch相应数据全部获取下来。

注意
Hive并没有用到Elasticsearch查询高效的好处。

普通表与外表关联

  1. hive> add jar /opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/lib/hive/lib/elasticsearch-hadoop-hive-2.1.1.jar;
  2. hive> select * from hive_es_angel left join sample_08 on hive_es_angel.name=sample_08.code;

其中sample_08表是Hive普通表,hive_es_angel是数据存储在HBase中的Hive外表。
经验证,通过执行上面语句,可以将Hive普通表与Hive的Elasticsearch外表进行正确的关联。
这样一来,ES的SQL插件elasticsearch-sql所不能完成的join等查询语句,便可以通过Hive很好的执行了。

Double数据类型求和

  1. hive> add jar /opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/lib/hive/lib/elasticsearch-hadoop-hive-2.1.1.jar;
  2. hive> SELECT SUM(field_double) FROM hive_es_double;

由于ElasticSearch并不支持decimal数据类型,故只能在Hive中对小数列创建double数据类型,这时使用Hive也无法进行精确求和,这在需要进行小数精确统计时,是一大问题。
Solr也有此缺陷,可见目前ES、Solr天生不适合进行精确计算。故将数据放在ES中,使用Hive或Impala进行SQL精确统计,并非一个好办法。

数据存储在Elasticsearch上,使用Impala执行SQL语句

hive中建立的普通表,impala可以读取使用。执行join语句时,impala明显比hive快很多。在impala中执行INVALIDATE METADATA 、 REFRESH table_name语句以便 Impala 识别新的或已更改的数据。

但是,对于EXTERNAL TABLE数据存放在ES上的,Hive SQL可以正确执行,Impala SQL执行不正确,出错信息如下所示。

  1. ERROR: AnalysisException: Failed to load metadata for table: default.hive_table
  2. CAUSED BY: TableLoadingException: Unrecognized table type for table: default. es_table

可见使用elasticsearch-hadoop来做Impala计算,是不可行的。相比较下,数据存储在HBase中,使用Impala执行SQL语句却没有问题。具体可以查看《使用Hive或Impala执行SQL语句,对存储在HBase中的数据操作》一文。

综上所述

数据存储在Elasticsearch中,使用Hive进行SQL操作,并不是一个好主意,这是由于ES的数据类型所限导致的ES与Hive的数据类型对应问题,并且Hive并不能用到Elasticsearch建立的索引的高效查询好处。虽然以上方案可以解决使用Hive对Elasticsearch较复杂SQL语句查询,但不方便也不高效,且Impala也不能支持。
故,目标1达成,但目标2没有达成。

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注