如何为Druid构建可在Postman调用的多维度JSON原生查询

你当前使用的是Turnilo内部生成的嵌套多批次查询结构，不符合Druid原生HTTP接口的入参规范：Druid的/druid/v2查询接口仅支持两种合法请求体格式，要么是单个JSON查询对象，要么是顶层为平级结构的多查询数组，不接受嵌套数组格式，因此触发JSON结构解析报错。

方案1：单独运行单个查询 #

如果需要逐个执行查询，直接将每个独立的{"queryType": "..."}对象提取出来作为单独的请求体发送即可，例如全量总营收查询的请求体为：

{
  "queryType": "timeseries",
  "dataSource": "movies_source",
  "intervals": "2021-11-18T00:01Z/2021-11-21T00:01Z",
  "granularity": "all",
  "aggregations": [
    {
      "name": "__VALUE__",
      "type": "doubleSum",
      "fieldName": "revenue"
    }
  ]
}

方案2：批量执行所有查询 #

如果需要一次性执行3个查询，将原嵌套数组拆平为顶层平级的查询数组，发送到/druid/v2接口即可，修改后的请求体如下：

[
  {
    "queryType": "timeseries",
    "dataSource": "movies_source",
    "intervals": "2021-11-18T00:01Z/2021-11-21T00:01Z",
    "granularity": "all",
    "aggregations": [
      {
        "name": "__VALUE__",
        "type": "doubleSum",
        "fieldName": "revenue"
      }
    ]
  },
  {
    "queryType": "topN",
    "dataSource": "movies_source",
    "intervals": "2021-11-18T00:01Z/2021-11-21T00:01Z",
    "granularity": "all",
    "dimension": {
      "type": "default",
      "dimension": "movie_id",
      "outputName": "movie_id"
    },
    "aggregations": [
      {
        "name": "revenue",
        "type": "doubleSum",
        "fieldName": "revenue"
      }
    ],
    "metric": "revenue",
    "threshold": 50
  },
  {
    "queryType": "topN",
    "dataSource": "movies_source",
    "intervals": "2021-11-18T00:01Z/2021-11-21T00:01Z",
    "granularity": "all",
    "filter": {
      "type": "selector",
      "dimension": "movie_id",
      "value": "some_movie_id"
    },
    "dimension": {
      "type": "default",
      "dimension": "country",
      "outputName": "country"
    },
    "aggregations": [
      {
        "name": "revenue",
        "type": "doubleSum",
        "fieldName": "revenue"
      }
    ],
    "metric": "revenue",
    "threshold": 5
  }
]

你需要的「所有电影、单部电影总营收、对应各国营收」结果可以通过单个groupBy查询直接获取，无需执行多次查询，性能更高，查询语句如下：

{
  "queryType": "groupBy",
  "dataSource": "movies_source",
  "intervals": "2021-11-18T00:01Z/2021-11-21T00:01Z",
  "granularity": "all",
  "dimensions": [
    {"type": "default", "dimension": "movie_id", "outputName": "movie_id"},
    {"type": "default", "dimension": "country", "outputName": "country"}
  ],
  "aggregations": [
    {"name": "country_revenue", "type": "doubleSum", "fieldName": "revenue"}
  ],
  "postAggregations": [
    {
      "type": "expression",
      "name": "total_movie_revenue",
      "expression": "sum(country_revenue) over (partition by movie_id)"
    }
  ]
}

返回结果每行对应单部电影在单个国家的营收，同时携带该电影的总营收，可直接拼装为你需要的结果表。

• 请求方法选择POST
• 请求头添加Content-Type: application/json
• 请求地址为Druid Broker服务的地址，格式为http://<你的Druid Broker域名/IP>:<端口>/druid/v2/

内容的提问来源于stack exchange，提问作者nirkov