# paas后管对接starrocks指南

### 1. 架构
[![数据中心对接starrocks架构图.jpg](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/starrocks.jpg)](https://book.e-buy.com/uploads/images/gallery/2023-05/starrocks.jpg)
所有后管切换starrocks后端开发不需要改任何代码，通过透传到paas-micro-datacenter-admin直接查询数据。后端开发人员只需要编写对应的数据查询sql，配置到数据中心数据库中。
### 2. 功能介绍
#### 2.1 配置表介绍
1. data_search_interface 数据查询接口表（主表）
[![data_search_interface](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/image-1684203086331.png)](https://book.e-buy.com/uploads/images/gallery/2023-05/image-1684203086331.png)
2. data_search_item 数据查询明细子表（明细查询中存在子查询时需要）
[![data_search_item](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/image-1684203146477.png)](https://book.e-buy.com/uploads/images/gallery/2023-05/image-1684203146477.png)
#### 2.2 配置说明
主表配置说明
``` sql
select
  t.ticket_id,
  t.ticket_name,
  t.brand_id,
  t.brand_name
from
  paas_micro_ecoupon.ecoupon_ticket t
  join paas_micro_ecoupon.ecoupon_sale_channel s on t.sale_channel_id = s.sale_channel_id
where
  t.sale_channel_id > @*saleChannelId 
  and (
    t.brand_id = @brandIds_0
    or t.brand_id = @brandIds_1
  )
  and t.ticket_name like @ticketName
```
[![sql配置说明](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/image-1684204266929.png)](https://book.e-buy.com/uploads/images/gallery/2023-05/image-1684204266929.png)
说明：
1. 多表查询直接使用join，要非常清楚使用inner join还是 left join。inner join 是两边数据都存在才会有记录，left join只要左表存在就会有记录。
2. 所有编写的sql中表名都需要加上库名，例如：paas_micro_ecoupon.ebuy_ecoupon_code。
3. sql编写语法与nutz语法一致，唯独就是如果希望条件里面的参数需要必填 需要使用@*。
4. 所有sql中where后面的条件参数值获取流程为：首先判断请求参数content中是否有searchConditions，如果有这个对象，所有参数从这个对象中获取，如果searchConditions这个对象不存在，直接从content中获取。<b>注意：只要请求参数中有searchConditions，所有参数都会从searchConditions中获取，所以参数不能一部分放到searchConditions，一部分直接放到content中</b>。未找到视为参数未传输，如果是必填参数将会报错。
5. 如果条件参数是数组，sql中使用”@参数名_数组索引”，参见上面截图说明。
6. 数据配置中的intf_action为sql查询的唯一标识，建议与原来es查询时的action值保持一致，唯一索引不能重复。
7. 当sql中条件没有传参数值，系统会自动将整个条件替换为true。
8. appId参数，如果sql中有app_id = @appId，appId参数不用显示传值，系统会自动从RpcRequest中获取appId
9. 主表可以用来配置列表查询sql，也可以配置明细查询sql，区别在于后续调用的查询接口不同，同时明细sql会自动加上“limit 1”。
10. sql条件不支持between and，支持in，in的书写格式为 "brandId in (@brandId)"。
11. 配置的sql会自动刷新，测试环境为1分钟。
12. 所有配置的sql请先将sql格式化后再压缩后再存入数据库，<b>格式化后@*中间会产生空格需要手动删除空格</b>，附sql压缩及格式化工具地址：<a href="https://www.toolscat.com/format/sql" target="_blank">https://www.toolscat.com/format/sql</a><br/>
[![](https://book.e-buy.com/uploads/images/gallery/2023-12/scaled-1680-/image-1702976743610.png)](https://book.e-buy.com/uploads/images/gallery/2023-12/image-1702976743610.png)
上面sql请求及返回事例：
``` json
{
  "uuid": "20230516-nzumknyqpmirkvmjodmpgbkvlkhlwl53",
  "appId": "7458433922654696",
  "action": "_dataSearch",
  "timestamp": 1680142763,
  "signType": "sha256",
  "sign": "2234549905748620",
  "content": {
    "pageNumber": 1,
    "pageSize": 20,
    "intfAction": "queryEcouponTicket",
    "saleChannelId": 1,
    "brandIds": [1458,1148]
  },
  "lang": "zh_CN"
}
```
执行的sql
[![执行sql截图](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/image-1684204785284.png)](https://book.e-buy.com/uploads/images/gallery/2023-05/image-1684204785284.png)
子表配置说明：
子表是用来配置明细查询时还有子查询的情况，例如券明细查询，除了显示券明细以外还需要显示券的生命周期列表，这时候就可以将券生命周期列表sql配置到子表中，查询的数据会会同主表查询数据一同返回。
事例：
``` sql
SELECT * FROM paas_micro_ecoupon.ebuy_ecoupon_code WHERE code_id  = @codeId --主表
SELECT * FROM paas_micro_ecoupon.ebuy_ecoupon_code_update WHERE code_id  = @codeId --子表
```
* 上面两条sql中，第一条为主表，@codeId参数会从请求上送的参数中获取值。而第二条sql的codeId参数会从主表查询结果的json中获取值。
* 子表配置是还需要配置一个json_key字段，json_key字段的值会作为key放入返回结果的json中去。
请求参数：
``` json
{
  "uuid": "20230516-ojvfvgcweuelcohkhcfzmfydrytigoru",
  "appId": "2873492170393134",
  "action": "_dataDetail",
  "timestamp": 1684141840,
  "signType": "sha256",
  "sign": "2534751707490756",
  "content": {
    "codeId": "f729c9e95c654b2f87127e98236e28f0",
    "intfAction": "getEcouponCodeDetail"
  },
  "lang": "zh_CN"
}
```
返回结果
``` json
{
  "uuid": "20230516-ojvfvgcweuelcohkhcfzmfydrytigoru",
  "action": "_dataDetail",
  "content": {
    "userInfo": {},
    "updateDate": "2023-05-14 08:00:07",
    "codeSecret": "10032806704005215057",
    "activateDate": "20230514",
    "orderId": "51b54e332fd64fc584008cec8d3144bb",
    "activateInfo": {
      "activateDate": "2023-05-14 08:00:06",
      "activateTime": "2023-05-14 08:00:06"
    },
    "codeSearch": "10092305142760000026",
    "uuid": "20230514-58ae8755c139455892909e98e26278ba",
    "outerOrderId": "SI6QtqLT",
    "codeId": "f729c9e95c654b2f87127e98236e28f0",
    "operationList": [
      {
        "codeId": "f729c9e95c654b2f87127e98236e28f0",
        "orderId": "51b54e332fd64fc584008cec8d3144bb",
        "outerOrderId": "SI6QtqLT",
        "outerOrderItemId": "tA7zoVzl",
        "ticketId": 10000300000053,
        "ticketName": "医疗补贴权益券",
        "brandId": 4049,
        "brandName": "乐乐茶",
        "codeMask": "100***5057",
        "codeSecret": "10032806704005215057",
        "codeSearch": "10092305142760000026",
        "useTimes": 1,
        "verifyTimes": 0,
        "validStart": "2023-05-14 00:00:00",
        "validEnd": "2023-06-18 23:59:59",
        "codeStatus": "00",
        "activateInfo": {
          "activateDate": "2023-05-14 08:00:06",
          "activateTime": "2023-05-14 08:00:06"
        },
        "userInfo": {},
        "tenancyId": 58,
        "tenancyName": "乐乐茶",
        "partnersAppId": "ebuywha102420109",
        "status": "0",
        "createBy": "paas-gateway",
        "createDate": "2023-05-14 08:00:07",
        "updateBy": "paas-gateway",
        "updateDate": "2023-05-14 08:00:07",
        "appId": "ebuye7eb3a0ac617",
        "uuid": "20230514-58ae8755c139455892909e98e26278ba",
        "productId": 3261,
        "activateDate": "20230514",
        "activateTime": "20230514080006"
      }
    ],
    "tenancyName": "乐乐茶",
    "updateBy": "paas-gateway",
    "outerOrderItemId": "tA7zoVzl",
    "appId": "ebuye7eb3a0ac617",
    "createDate": "2023-05-14 08:00:07",
    "brandName": "乐乐茶",
    "productId": 3261,
    "validStart": "2023-05-14 00:00:00",
    "activateTime": "20230514080006",
    "codeStatus": "00",
    "verifyTimes": 0,
    "tenancyId": 58,
    "createBy": "paas-gateway",
    "brandId": 4049,
    "validEnd": "2023-06-18 23:59:59",
    "codeMask": "100***5057",
    "partnersAppId": "ebuywha102420109",
    "ticketId": 10000300000053,
    "ticketName": "医疗补贴权益券",
    "useTimes": 1,
    "status": "0"
  },
  "success": true,
  "errorCode": "",
  "errorMessage": "",
  "timestamp": 1684205810109,
  "signType": "sha256",
  "sign": "8efa67f2f6e466f27a94ee6759ee7e32a022d90d216825647ab58852a67d49ff"
}
```
[![配置jsonkey截图](https://book.e-buy.com/uploads/images/gallery/2023-05/scaled-1680-/image-1684205950611.png)](https://book.e-buy.com/uploads/images/gallery/2023-05/image-1684205950611.png)
#### 2.3 接口说明
1. 数据查询分页接口 https://apimock.e-buychina.com/project/663/interface/api/35867
2. 数据查询明细接口 https://apimock.e-buychina.com/project/663/interface/api/35896
3. 数据查询列表接口 https://apimock.e-buychina.com/project/663/interface/api/35909
3. 前端使用透传地址查询：例如：
* https://dev-paas-app-adminecoupon.e-buy.com/paas/app/adminecoupon/datacenter/dataSearch
* https://dev-paas-app-adminecoupon.e-buy.com/paas/app/adminecoupon/datacenter/dataItem

#### 2.4 starrocks sql编写规范
1. 无论是列表查询还是明细查询，所有sql select 字段中不允许出现 *, 请大家将具体需要显示的字段列出来。
2. 翻页查询：
	* 翻页查询所有的查询条件只能使用主表中的字段作为条件筛选;
    * 翻页查询select语句中前两列，第一列为主表主键；第二列为主表order by的排序列;
    * 除主表以外其他关系表统一使用left join关联；
    * 查询语句中不允许使用 group by。
    * 自动sql优化功能需要在data_search_interface表中optimize_enable字段设置为Y
    [![翻页查询规范图列](https://book.e-buy.com/uploads/images/gallery/2023-06/scaled-1680-/image-1685694949692.png)](https://book.e-buy.com/uploads/images/gallery/2023-06/image-1685694949692.png)
    自动优化后的sql
    [![自动优化的结果](https://book.e-buy.com/uploads/images/gallery/2023-06/scaled-1680-/image-1686122207244.png)](https://book.e-buy.com/uploads/images/gallery/2023-06/image-1686122207244.png)