elasticsearch 的mapping对应ob应该怎样建表

OceanBase 技术问题
#1

{
“mappings”: {
“aaaaa”: {
“properties”: {
“Area”: {
“type”: “long”
},
“City”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“CityCode”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“DeptId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“IsDelete”: {
“type”: “long”
},
“KeyWord”: {
“analyzer”: “ik_max_word”,
“fielddata”: true,
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“OperationTime”: {
“format”: “yyyy-MM-dd HH:mm:ss”,
“type”: “date”
},
“OrganId”: {
“type”: “keyword”
},
“userId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
},
“bbbbb”: {
“properties”: {
“DeptId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“IsDelete”: {
“type”: “long”
},
“KeyWord”: {
“analyzer”: “ik_max_word”,
“fielddata”: true,
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“OperationTime”: {
“format”: “yyyy-MM-dd HH:mm:ss”,
“type”: “date”
},
“OrganId”: {
“type”: “keyword”
},
“SearchTime”: {
“format”: “yyyy-MM-dd HH:mm:ss”,
“type”: “date”
},
“SearchType”: {
“type”: “long”
},
“UserId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
},
“ddddd”: {
“properties”: {
“Area”: {
“type”: “long”
},
“City”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“CityCode”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“DeptId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“IsDelete”: {
“type”: “long”
},
“query”: {
“properties”: {
“bool”: {
“properties”: {
“adjust_pure_negative”: {
“type”: “boolean”
},
“boost”: {
“type”: “float”
},
“disable_coord”: {
“type”: “boolean”
},
“must”: {
“properties”: {
“match”: {
“properties”: {
“IsDelete”: {
“properties”: {
“boost”: {
“type”: “float”
},
“fuzzy_transpositions”: {
“type”: “boolean”
},
“lenient”: {
“type”: “boolean”
},
“max_expansions”: {
“type”: “long”
},
“operator”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“prefix_length”: {
“type”: “long”
},
“query”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“zero_terms_query”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
},
“UserId”: {
“properties”: {
“keyword”: {
“properties”: {
“boost”: {
“type”: “float”
},
“fuzzy_transpositions”: {
“type”: “boolean”
},
“lenient”: {
“type”: “boolean”
},
“max_expansions”: {
“type”: “long”
},
“operator”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“prefix_length”: {
“type”: “long”
},
“query”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
},
“zero_terms_query”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
}
}
}
}
}
}
}
}
}
}
},
“searchTime”: {
“type”: “long”
},
“size”: {
“type”: “long”
},
“sort”: {
“properties”: {
“SearchTime”: {
“properties”: {
“order”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
}
}
},
“userId”: {
“fields”: {
“keyword”: {
“ignore_above”: 256,
“type”: “keyword”
}
},
“type”: “text”
}
}
}
}
}

es的索引mapping情况是这样的，请问对应的ob我应该怎么建表呢，相同字段是要去重吗，请老师指点

1 个赞
#3

举例说明类似这样不过复杂场景还需要根据业务需要分析：

基本数据类型映射

Elasticsearch 类型 OceanBase 类型 说明 示例
keyword VARCHAR(n) 或 CHAR(n) 精确匹配字符串，不分析 VARCHAR(255)
text TEXT 或 VARCHAR(65535) 全文搜索文本，会被分析 TEXT
long BIGINT 64位整数 BIGINT
integer INT 32位整数 INT
short SMALLINT 16位整数 SMALLINT
byte TINYINT 8位整数 TINYINT
double DOUBLE 双精度浮点数 DOUBLE
float FLOAT 单精度浮点数 FLOAT
boolean BOOLEAN 或 TINYINT(1) 布尔值 BOOLEAN
date DATETIME 或 TIMESTAMP 日期时间 DATETIME 或 TIMESTAMP(6)
ip VARCHAR(45) IPv4/IPv6 地址 VARCHAR(45)
geo_point VARCHAR(255) 或 JSON 地理坐标点 JSON 或 VARCHAR(255)
geo_shape JSON 或 TEXT 地理形状 JSON
object JSON 嵌套对象 JSON
nested 子表或 JSON 嵌套数组对象 建议使用子表
array JSON 或 子表 数组类型 JSON 或 子表

特殊类型处理

Elasticsearch 类型 OceanBase 处理方式 说明
binary VARBINARY(n) 或 BLOB 二进制数据
range JSON 范围类型，存储为 JSON
completion TEXT 自动完成建议
token_count INT 词条计数

二、Mapping 结构分析

Elasticsearch Mapping 示例

{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "title": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "price": {
        "type": "double"
      },
      "created_at": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      },
      "tags": {
        "type": "keyword"
      },
      "description": {
        "type": "text"
      },
      "metadata": {
        "type": "object",
        "properties": {
          "author": {
            "type": "keyword"
          },
          "category": {
            "type": "keyword"
          }
        }
      },
      "comments": {
        "type": "nested",
        "properties": {
          "user": {
            "type": "keyword"
          },
          "content": {
            "type": "text"
          },
          "created_at": {
            "type": "date"
          }
        }
      },
      "location": {
        "type": "geo_point"
      }
    }
  }
}

建表 SQL

CREATE TABLE products (
    id VARCHAR(255) PRIMARY KEY COMMENT '产品ID',
    title TEXT COMMENT '产品标题',
    title_keyword VARCHAR(255) COMMENT '产品标题（精确匹配）',
    price DOUBLE COMMENT '价格',
    created_at DATETIME COMMENT '创建时间',
    tags JSON COMMENT '标签数组',
    description TEXT COMMENT '描述',
    metadata JSON COMMENT '元数据对象',
    comments JSON COMMENT '评论数组（nested类型）',
    location JSON COMMENT '地理位置（geo_point）',
    INDEX idx_title_keyword (title_keyword),
    INDEX idx_price (price),
    INDEX idx_created_at (created_at)
) COMMENT='产品表';
#4

一张表的还好解决，就是几张表合并的，在ob上不知道怎么建表结构，
如果ob都建成一张表的话，重复的字段怎么搞，直接去重吗？
如果ob建成三张表的话，那oms要怎么同步？

#5

文档里说明支持es7以上的版本，
es7的index对应的是表维度，es5的index对应的是库维度，type才是对应表维度

上面的例子是es5.5.16 的mapping，里面包含了三个type（对应三个表），这在es7里面是不支持的
所以上面的例子本来就有问题