ES常用的操作语句

ES 常用语句

在原来索引上添加字段

GET /2022_customer/_mapping
PUT /2022_customer/_mapping
{
  "properties":{
    "amt_avg" : {
          "type" : "keyword"
        }
  }
}
PUT /2022_customer/_mapping
{
  "properties":{
    "start_time"  : {
          "type" : "keyword"
        }
  }
}
PUT /2022_customer/_mapping
{
  "properties":{
    "is_new_car"  : {
          "type" : "keyword"
        }
  }
}
 
PUT /2022_customer/_mapping
{
  "properties":{
    "pt_date"  : {
          "type" : "keyword"
        }
  }
}
 

多条件查询

#041
GET /2022_customer/_search
{

  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "product_tx_code": "E"
          }
        },
        {
          "match": {
            "career_type_name": "公路工程"
          }
        }
        
      ]
    }
  }

es创建索引

 PUT / 2022_customer
{
 	"settings": {
 		"number_of_shards": 6,
 		"number_of_replicas": 1,
 		"refresh_interval": "1s",
 		"translog": {
 			"sync_interval": "60s",
 			"durability": "async",
 			"flush_threshold_size": "1g"
 		}
 	},
 	"mappings": {
 		"properties": {
 			"cert_code": {
 				"type": "keyword"
 			},
 			"cert_type": {
 				"type": "keyword"
 			},
 			"is_new_car": {
 				"type": "keyword"
 			},
 			"key": {
 				"type": "keyword"
 			},
 			"name": {
 				"type": "keyword"
 			},
 			"pt_day": {
 				"type": "keyword"
 			},
 			"rtn_code": {
 				"type": "keyword"
 			},
 			"rtn_value": {
 				"type": "keyword"
 			},
 			"start_time": {
 				"type": "keyword"
 			},
 			"vin": {
 				"type": "keyword"
 			}
 		}
 	}
 }

es分组聚合查询

GET /2022_customer/_search
{
  "aggs": {
    "B": {
      "terms": {
        "field": "product_tx_code"
      },
      "aggs": {
        "A": {
          "terms": {
            "field": "career_type_name"
          }
        }
      }
    }
  }
}

建es的hive外表

set role admin;
add jar hdfs:///user/PT11AIUAS/jars/commons-httpclient-3.1.jar;
add jar hdfs:///user/PT11AIUAS/jars/elasticsearch-hadoop-7.5.1.jar;
drop table if exists public_data_platform.2022_customer;
create external table if not EXISTS public_data_platform.2022_customer(
key_1               string
,cust_name                  string
,cert_code                  string
,cert_type                  string
,role_type                  string
,premium_amount             string
,get_premium_amount         string
,final_payed_amount         string
,policy_premium             string
,cust_expected_lose_ratio   string
,insured_amount             string
,cust_annual_amount         string
,inception_date             string
,qsh                        string
,lastupd_dttm               string
)STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES(
'es.nodes'='29.23.89.104:9200,29.23.89.106:9200,29.23.35.222:9200',
'es.nodes.wan.only'='true',
'es.mapping.id'='key_1',
'es.resource'='1013_t_crms_d1_cust_view',
'es.index.auto.create'='false',
'es.mapping.names'='key_1:key,cust_name:cust_name,cert_code:cert_code,cert_type:cert_type,role_type:role_type,premium_amount:premium_amount,get_premium_amount:get_premium_amount,final_payed_amount:final_payed_amount,policy_premium:policy_premium,cust_expected_lose_ratio:cust_expected_lose_ratio,insured_amount:insured_amount,cust_annual_amount:cust_annual_amount,inception_date:inception_date,qsh:qsh,lastupd_dttm:lastupd_dttm',
'es.index.translog.durability' = 'async',
'es.index.translog.sync_interval' = '120s',
'es.index.translog.flush_threshold_size' = '1024mb',
'es.index.translog.flush_threshold_period' = '120m',
'es.index.refresh_interval'='120s',
'es.write.operation' = 'upsert',
'es.http.timeout' = '1800s',
'es.batch.write.retry.count' = '-1',
'es.batch.size.bytes' = '20mb',
'es.batch.write.retry.wait' = '900s',
'es.batch.size.entries' = '5000',
'es.batch.write.refresh'='false'
);

导入数据

set role admin;
add jar hdfs:///user/PT11AIUAS/jars/commons-httpclient-3.1.jar;
add jar hdfs:///user/PT11AIUAS/jars/elasticsearch-hadoop-7.5.1.jar;

insert into public_data_platform.2022_customer
select md5(concat(cust_name,cert_code,cert_type,role_type)) key_1 ,
cust_name,
cert_code,
cert_type,
role_type,
premium_amount,
get_premium_amount,
final_payed_amount,
policy_premium,
cust_expected_lose_ratio,
insured_amount,
cust_annual_amount,
inception_date,
qsh,
lastupd_dttm from 
crms.T_CRMS_D1_CUST_VIEW;

替换

POST /2022_customer/_update_by_query
{
    "query": {
        "match": {
            "prod_class2_name": "九、人身意外保险"
        }
    },
    "script": {
        "inline": "ctx._source['prod_class2_name'] = '人身意外保险'"
    }
}
POST /1013_t_crms_d1_cust_risk_au/_update_by_query
{
  "script":{
    "source": "ctx._source.prod_class2_name='九、人身意外保险'"
  },
  "query": {
    "bool": {
      "filter": {
        "term": {
          "prod_class2_name" : "人身意外保险"
        }
      }
    }
  }
}

长度查询

### 长度查询
GET /2022_customer/_search
{
  "query": {
    "bool": {
      "filter": [{
        "term": {
          "cert_type" : "1"
        }
      },{
        "regexp":{
          "cert_code":{
              "value": ".{15,18}"
            }
          }
        }
      ]
    }
  }
}

范围查询

GET  /2022_customer/_search
{
	"from": 0,
	"query": {
		"bool": {
			"filter": [{
					"range": {
						"quotationDate": {
							"format": "yyyy-MM-dd HH:mm:ss",
							"gte": "2021-01-26 22:29:44",
							"lte": "2021-01-29 22:29:44"
						}
					}
				}
			],
			"must": [{
					"term": {
						"vin": "LFV2A11GX93118956"
					}
				}
			]
		}
	},
	"size": 1
}


GET /2022_customer/_search
Content-Type: application/json

{
  "query": {
    "range": {
      "age": {
        "gte": 26,
        "lte": 35
      }
    }
  }
}

分组汇总

### 分组汇总
GET http://29.21.31.191:9200/2022_customer/_search
Content-Type: application/json

{
  "size": 0,
  "aggs": {
    "sal_sum": {
      "terms": {
        "field": "dept_no",
        "order":{
          "sum_sa" :"asc"
        }
      },
      "aggs": {
        "sum_sa": {
          "sum": {
            "field": "salary"
          }
        }
      }
    }
  }
}

### 多维分组汇总
GET /2022_customer/_search
{
  "size": 0,
  "aggs": {
    "sal_sum": {
      "terms": {
        "script": {
          "inline":"doc['dept_no'].value+' '+doc['age'].value"
        },
        "order":{
          "sum_sa" :"asc"
        }
      },
      "aggs": {
        "sum_sa": {
          "sum": {
            "field": "salary"
          }
        }
      }
    }
  }
}


### 多维分组汇总-方式2
GET /2022_customer/_search
{
     "query": {
          "match_all": {}
     },
     "size": 0,
     "aggregations": {
          "age": {
               "aggregations": {
                    "dept_no": {
                         "aggregations": {
                              "SUM(salary)": {
                                   "sum": {
                                        "field": "salary"
                                   }
                              }
                         },
                         "terms": {
                              "field": "dept_no"
                         }
                    }
               },
               "terms": {
                    "field": "age"
               }
          }
     }
}