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

http://www.cnblogs.com/wgp13x/p/4934521.html 内容一样，样式好的版本。

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

标签： Hive Impala Elasticsearch Hadoop SQL Elasticsearch for Apache Hadoop

---

目录

• 使用Hive或Impala执行SQL语句，对存储在Elasticsearch中的数据操作
  • 基础环境
  • Elasticsearch简介
    • Elasticsearch的数据类型
    • Solr的数据类型
  • 数据存储在Elasticsearch上，使用Hive执行SQL语句
    • 创建Hive外部表
      • 先有Hive再有Elasticsearch
      • 先有Elasticsearch再有Hive
    • 向Elasticsearch写
    • 从Elasticsearch读
      • where子句
      • 普通表与外表关联
      • Double数据类型求和
  • 数据存储在Elasticsearch上，使用Impala执行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都支持。

String: string
Whole number: byte, short, integer, long
Floating point: float, double
Boolean: boolean
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中创建一个新的外部表。

#在Hive中创建一个新的外部表
hive> CREATE EXTERNAL TABLE hive_es_angel(
    id      BIGINT,
    name    STRING)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
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数据类型

CREATE EXTERNAL TABLE hive_es_decimal(
    price      DECIMAL(12,2) )
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
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错误。

• 指定列映射关系

CREATE EXTERNAL TABLE hive_es_map(
    price      DOUBLE,
    itemid BIGINT,
    myname STRING
  )
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
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写

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;

#向ES中导入Hive中表sample_07数据
hive> INSERT OVERWRITE TABLE hive_es_test
    SELECT s.id, s.code
                    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子句

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;
hive> select count(*) from hive_es_angel where id>1000;

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

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

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

注意：

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

普通表与外表关联

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;
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数据类型求和

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;
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执行不正确，出错信息如下所示。

ERROR: AnalysisException: Failed to load metadata for table: default.hive_table
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没有达成。

博客园既不支持目录，也不支持表格，真是不舒服。我在考虑要不要换个地方发表。

嫌样式不好的，大家看这篇吧：http://www.cnblogs.com/wgp13x/p/4934521.html内容一样，样式稍好看些。

信息系统集成项目管理人员资质证书还有公司需要的啊？挂靠私聊。