hive读取es里面的数据建表时，时间格式不能转换问题

用hive读取es里面的数据，建表。时间类型的数据不能接受的问题

问题描述：spark读取指定索引/类型的数据，其中有自定义格式的日期数据，读取该日期时报异常，日期定义格式："estime" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},

 1 CREATE EXTERNAL TABLE esjson.app_phone_device(
 2   id string,
 3   into_code string,
 4   order_no string,
 5   cust_code string,
 6   estime date,
 7   ttimestamp string,
 8   vcard string,
 9   vphone string,
10   appType string,
11   appNo string,
12   appVersion string,
13   custCode string,
14   pid string,
15   modelType string,
16   operatSystem string,
17   systemVersion string
18 )
19 STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
20 TBLPROPERTIES(
21   'es.nodes' = '172.18.100.187:9200',
22   'es.index.auto.create' = 'false',
23   'es.index.read.missing.as.empty' = 'true',
24   'es.resource' = 'app_phone_device/app_phone_device',
25   'es.read.metadata' = 'true',
26   'es.mapping.names'= '\
27 id:_metadata._id,\
28 into_code:applyNum,\
29 order_no:orderNo,\
30 cust_code:customerNum,\
31 estime:cast(esTime as date),\
32 ttimestamp:timestamp,\
33 vcard:vcard,\
34 vphone:vphone,\
35 appType:header.appType,\
36 appNo:header.appNo,\
37 appVersion:header.appVersion,\
38 custCode:body.custCode,\
39 pid:body.pid,\
40 modelType:body.modelType,\
41 operatSystem:body.operatSystem,\
42 systemVersion:body.systemVersion');

hive建表想用date类型接收，把es的date类型用

cast(esTime as date) 转换一下，结果查询hive表的estime数据列为null

找了大半天也没找到怎么解决，转换思路，用String类型接受estime。增加一项配置es.mapping.date.rich=false

公司用的CDH  直接在hue里增加配置项

CDH永久更改词参数

 hvie的配置

 

 

 

看了好资料 spark读取es时间类型的字段也会遇到该问题

在命令行提交时设置spark.es.mapping.date.rich为false生效，可以不解析为date，直接返回string。

或者

val sparkConf = new SparkConf().setAppName("esspark").setMaster("local[2]")
    sparkConf.set("es.nodes", "10.3.162.202")
    sparkConf.set("es.port", "9200")
    sparkConf.set("es.mapping.date.rich", "false")

 

如果是hadoop读写es

//如果是api操作可以set这个值
import org.apache.hadoop.conf.Configuration;

Configuration conf = new Configuration();

conf.set("es.mapping.date.rich", "false");

 

 

引用 

https://www.elastic.co/guide/en/elasticsearch/hadoop/master/configuration.html

在官方找到这个配置项

Whether to create a rich Date like object for Date fields in Elasticsearch or returned them as primitives (String or long). By default this is true. The actual object type is based on the library used; noteable exception being Map/Reduce which provides no built-in Date object and as such LongWritable and Text are returned regardless of this setting.

posted @ 2018-02-27 11:04  阳光下的me  阅读(3087)  评论(0)  编辑  收藏  举报