在hive创建ES表
CREATE TABLE `features.app_sale_clue_status_ability_result_es`(
uid int COMMENT '学规ID',
status int COMMENT '线索状态',
score string COMMENT '标准能力',
dt string COMMENT '日期'
)
COMMENT '表名'
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES (
'es.index.auto.create'='false',
'es.index.read.missing.as.empty'='true',
'es.mapping.names'='uid:uid,status:status,score:score,dt:dt',
'es.net.http.auth.pass'='ES密码',
'es.net.http.auth.user'='ES账号',
'es.nodes'='ES地址',
'es.nodes.wan.only'='true',
'es.nodes.discovery' = 'false',
'es.port'='9200',
'es.read.metadata'='true',
'es.resource'='clue_strategies_lp_ability/lp_ability'
)
--查询索引：
GET /clue_strategies_lp_ability/lp_ability/_mapping?pretty
--删除索引
DELETE /clue_strategies_lp_ability/
-----------------------LP能力查询
POST /clue_strategies_lp_ability/lp_ability/_search?pretty
{
"query": { "match_all": {} },
"from": 0,
"size": 5600
}
-------查5条
GET /clue_strategies_lp_ability/lp_ability/_search?pretty
{
"query": {
"match_all": {}
}
}
---LP建索引
PUT /clue_strategies_lp_ability/
{
"mappings": {
"lp_ability": {
"properties": {
"uid": {
"type": "integer"
},
"status": {
"type": "integer"
},
"score":{
"type":"keyword"
},
"dt": {
"type": "keyword"
}
}
}
}
}
---------------------遇到问题
字段类型不同：int ES报错：版本问题 改为：integer
decimal(10,5) 与 string ES有数据，hive查询会报错 解决变为hive 变为string ES变为keyword
decimal(10,5) 与 double 也不行
支持建唯一索引
没数据报错：加上 'es.index.read.missing.as.empty'='true'
----------------ES时间范围查询
POST /clue_strategies_clue_saturation/clue_saturation/_search?pretty
{
"size": 5600,
"query": {
"range": {
"dt": {
"lt": "2019-07-26",
"gte": "2019-07-25"
}
}
}}
----------------排序查询
POST /clue_strategies_clue_saturation/clue_saturation/_search?pretty
{
"query": { "match_all": {} },
"sort": { "status": { "order": "desc" } }
}
-----------带条件查询
POST /clue_strategies_clue_saturation/clue_saturation/_search?pretty
{
"query": { "match": { "status": 8} }
}
----
# 查询指定列
POST /clue_strategies_clue_saturation/clue_saturation/_search?pretty
{
"query": { "match_all": {} },
"_source": ["status", "saturation_cnt"]
}
-------------------------------赋值
POST /features/app_sale_clue_status_saturation_result_es/
{
"sale_id":1,
"clue_status":1,
"saturation_result ":10,
"dt":"2019-07-23"
}