从 mysql 用户的角度使用 Elasticsearch

mervyn
mervyn
mervyn
68
文章
0
评论
2018年10月18日14:06:17数据库 Elasticsearch从 mysql 用户的角度使用 Elasticsearch已关闭评论1413

目录

本文主要讲述初次接触ES时，如何以使用MySQL的方式来应用 Elasticsearch 从而达到快速入门的目的。文章源自编程技术分享-https://mervyn.life/bf3d584f.html

以下例子均基于 Elasticsearch 6.0
例： order_test 表结构文章源自编程技术分享-https://mervyn.life/bf3d584f.html

字段名 类型 注释
id integer 主键ID
uid integer 用户UID
email keyword 邮箱
goods_id integer 商品ID
status short 订单状态
amount integer 商品总额
created_at date 创建时间
updated_at date 更新时间

create table

curl -X PUT 'http://10.10.18.113:9200/order_test' -H 'Content-Type: application/json' -d '
{
    "mappings": {
        "doc": {
            "properties" : {
                "id": {"type": "integer" },
                "uid": {"type": "integer" },
                "email": {"type": "keyword" },
                "goods_id": {"type": "integer" },
                "status": {"type": "short" },
                "amount": {"type": "double" },
                "created_at": {"type" : "date", "format":"yyyy-MM-dd HH:mm:ss" },
                "updated_at": {"type" : "date", "format":"yyyy-MM-dd HH:mm:ss" }
            }
        }
    }
}'

desc table

curl -XGET "http://10.10.18.113:9200/order_test/_mapping?pretty"

alter table add column

curl -XPOST "http://10.10.18.113:9200/order_test/doc/_mapping?pretty"  -H 'Content-Type: application/json' -d '{
    "doc": {
        "properties": {
            "qty":{
                "type":"integer"
            }
        }
    }
}'

注意 ES 不允许修改字段的类型。原因是一个字段的类型修改以后，那么该字段的所有数据都需要重新索引。
修改字段可以参考 《 http://www.cnblogs.com/Creator/p/3722408.html 》文章源自编程技术分享-https://mervyn.life/bf3d584f.html

delete table

curl -XDELETE  'http://10.10.18.113:9200/order_test'

insert

指定 doc_id

curl -XPUT 'http://10.10.18.113:9200/order_test/doc/3?pretty' -H 'Content-Type: application/json' -d '
{
    "id": 3,
    "uid": 10000,
    "email": "test@163.com",
    "goods_id": 111,
    "status": 10,
    "amount": 30.6,
    "created_at": "2018-10-18 13:55:32",
    "updated_at": "2018-10-18 13:55:32"
}'

返回结果：文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

{
    "_index" : "order_test",
    "_type" : "doc",
    "_id" : "3",
    "_version" : 1,
    "result" : "created",
    "_shards" : {
        "total" : 2,
        "successful" : 1,
        "failed" : 0
    },
    "_seq_no" : 0,
    "_primary_term" : 1
}

不指定 doc_id

当插入数据不指定doc_id 时则会自动生成一个ID文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XPOST 'http://10.10.18.113:9200/order_test/doc/?pretty' -H 'Content-Type: application/json' -d '
{
    "id": 3,
    "uid": 10000,
    "email": "test@163.com",
    "goods_id": 111,
    "status": 10,
    "amount": 30.6,
    "created_at": "2018-10-18 13:55:32",
    "updated_at": "2018-10-18 13:55:32"
}'

返回结果：文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

{
    "_index" : "order_test",
    "_type" : "doc",
    "_id" : "Kx_2hWYBURZFRkUGtvZ2",
    "_version" : 1,
    "result" : "created",
    "_shards" : {
        "total" : 2,
        "successful" : 1,
        "failed" : 0
    },
    "_seq_no" : 0,
    "_primary_term" : 1
}

批量 insert


curl -X POST "http://10.10.18.113:9200/_bulk?pretty" -H 'Content-Type: application/json' -d'
{ "index" : { "_index" : "order_test", "_type" : "doc", "_id" : "1" } }
{"id": 1, "uid": 10000, "email": "test@163.com", "goods_id": 123, "status": 10, "amount": 30.6, "created_at": "2018-10-18 13:55:32", "updated_at": "2018-10-18 13:55:32"}
{ "index" : { "_index" : "order_test", "_type" : "doc", "_id" : "2" } }
{"id": 2, "uid": 10000, "email": "test@163.com", "goods_id": 234, "status": 10, "amount": 30.6, "created_at": "2018-10-18 13:55:32", "updated_at": "2018-10-18 13:55:32"}
{ "index" : { "_index" : "order_test", "_type" : "doc", "_id" : "3" } }
{ "id": 3, "uid": 10001, "email": "test1@163.com", "goods_id": 234, "status": 10, "amount": 30.6, "created_at": "2018-10-18 13:55:32", "updated_at": "2018-10-18 13:55:32"}
{ "index" : { "_index" : "order_test", "_type" : "doc", "_id" : "4" } }
{"id": 4, "uid": 10001, "email": "test1@163.com", "goods_id": 234, "status": 10, "amount": 30.6, "created_at": "2018-10-18 13:55:32", "updated_at": "2018-10-18 13:55:32"}
'

select

找到具体的某个 doc文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/Kx_2hWYBURZFRkUGtvZ2?pretty'

*select from order_test.doc limit 10**文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty' -H 'Content-Type: application/json' -d '
{
    "size": 10,
    "query": {
        "match_all": {}
    }
}'

*select from order_test.doc where uid = 10000 and goods_id = 123**文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty' -H 'Content-Type: application/json' -d '
{
    "size": 10,
    "query": {
        "bool": {
            "must":[
                {"term":{ "uid":10000}},
                {"term":{ "goods_id":123}}
            ]
        }
    }
}'

*select from order_test.doc where uid = 10000 and goods_id in (123, 234)**文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty' -H 'Content-Type: application/json' -d '
{
    "size": 10,
    "query": {
        "bool": {
            "must":[
                {"term":{ "uid":10000}},
                {"terms":{ "goods_id":[123, 234]}}
            ]
        }
    }
}'

*select from order_test.doc where email like 'test1%'**文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty' -H 'Content-Type: application/json' -d '
{
    "size": 10,
    "query": {
        "bool": {
            "must":[
                {"wildcard":{"email":"test1*"}}
            ]
        }
    }
}'

原生 SQL 查询

目前 Elasticsearch 的支持 SQL 命令只有以下几个：文章源自编程技术分享-https://mervyn.life/bf3d584f.html

命令 类型
DESC table 用来描述索引的字段属性
SHOW COLUMNS 功能同上，只是别名
SHOW FUNCTIONS 列出支持的函数列表，支持通配符？过滤
SHOW TABLES 返回索引列表
SELECT .. FROM table_name WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT .. 用来执行查询的命令 
curl -XPOST 'http://10.10.18.113:9200/_xpack/sql?format=txt' -H 'Content-Type: application/json' -d '
{
    "query": "SELECT * FROM order_test where id = 3"
}'

SQL 转成原生的 ES 查询

curl -XPOST 'http://10.10.18.113:9200/_xpack/sql?format=txt' -H 'Content-Type: application/json' -d '
{
    "query": "show columns in order_test"
}'
curl -XPOST 'http://10.10.18.113:9200/_xpack/sql/translate?pretty' -H 'Content-Type: application/json' -d '
{
    "query": "SELECT uid, amount FROM order_test ORDER BY uid DESC",
    "fetch_size": 10
}'

返回结果：文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

{
    "size" : 10,
    "_source" : false,
    "stored_fields" : "_none_",
    "docvalue_fields" : [
        "uid",
        "amount"
    ],
    "sort" : [
        {
            "uid" : {
                "order" : "desc"
            }
        }
    ]
}

Group By

时间维度分组统计文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty'  -H 'Content-Type: application/json' -d '
{
    "size":0,
    "aggs":{
        "by_month":{
            "date_histogram":{
                "field":"created_at",
                "format":"yyyy-MM-dd HH:mm:ss",
                "interval":"month",
                "extended_bounds":{
                    "min":"2018-10-01 00:00:00",
                    "max":"2018-12-31 00:00:00"
                }
            },
            "aggs":{
                "amount":{
                    "sum":{
                        "field":"amount"
                    }
                }
            }
        }
    }
}
'

返回结果：文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 5,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "by_month" : {
      "buckets" : [
        {
          "key_as_string" : "2018-10-01 00:00:00",
          "key" : 1538352000000,
          "doc_count" : 5,
          "amount" : {
            "value" : 153.0
          }
        },
        {
          "key_as_string" : "2018-11-01 00:00:00",
          "key" : 1541030400000,
          "doc_count" : 0,
          "amount" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2018-12-01 00:00:00",
          "key" : 1543622400000,
          "doc_count" : 0,
          "amount" : {
            "value" : 0.0
          }
        }
      ]
    }
  }
}

商品维度分组统计文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

curl -XGET 'http://10.10.18.113:9200/order_test/doc/_search?pretty'  -H 'Content-Type: application/json' -d '
{
    "size":0,
    "aggs":{
        "by_goods":{
            "terms":{
                "field":"goods_id",
                "size":10,
                "order" : { "_count" : "asc" }
            },
            "aggs":{
                "amount":{
                    "sum":{
                        "field":"amount"
                    }
                }
            }
        }
    }
}
'

返回结果：文章源自编程技术分享-https://mervyn.life/bf3d584f.html 

{
    "took" : 1,
    "timed_out" : false,
    "_shards" : {
        "total" : 5,
        "successful" : 5,
        "skipped" : 0,
        "failed" : 0
    },
    "hits" : {
        "total" : 5,
        "max_score" : 0.0,
        "hits" : [ ]
    },
    "aggregations" : {
        "by_goods" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
            {
                "key" : 111,
                "doc_count" : 1,
                "amount" : {
                    "value" : 30.6
                }
            },
            {
                "key" : 123,
                "doc_count" : 1,
                "amount" : {
                    "value" : 30.6
                }
            },
            {
                "key" : 234,
                "doc_count" : 3,
                "amount" : {
                    "value" : 91.80000000000001
                }
            }
        ]
    }
  }
}
文章源自编程技术分享-https://mervyn.life/bf3d584f.html
weinxin
我的微信公众号
微信扫一扫
mervyn
Elasticsearch-PHP 遇到的坑 PHP

Elasticsearch-PHP 遇到的坑

大数据量分页查询报错 问题详情 在用elasticsearch-php分页查询时，分页几次后报错，错误内容如下： { "error":{ "root_cause"...
09月11日142Elasticsearch PHPElasticsearch-PHP 遇到的坑已关闭评论
Elasticsearch高级应用-聚合 数据库

Elasticsearch高级应用-聚合

聚合的分类 度量聚合 在一组文档中队某个数字型字段进行计算得出指标值 分组聚合 创建多个分组，每个分组关联一个关键字和相关文档标准 管道聚合 这一类的聚合的数据源是其他聚合的输出，然后进行相关指标的计...
02月27日137Elasticsearch评论