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

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

使用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外部表命令：

  • 在Hive指定decimal数据类型 
  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没有达成。

posted @ 2015-11-03 22:01  王安琪  阅读(2888)  评论(0)  编辑  收藏  举报