如何为Druid构建多维json原生查询？

Question

我有多维数据，存储在Druid集群中。例如，电影的数据以及他们从放映的每个国家获得的收入。我正在尝试构建一个查询，返回的答案将是所有电影的表，每个电影的总收入，以及每个国家的收入。

我成功地在Turnilo中完成了-它为我生成了以下Druid查询-

[


[
    {
      "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
    }
  ]
]

但是当我试图将它用作Postman查询的主体时，它不起作用--我得到了

{
    "error": "Unknown exception",
    "errorMessage": "Unexpected token (START_ARRAY), expected VALUE_STRING: need JSON String that contains type id (for subtype of org.apache.druid.query.Query)\n at [Source: (org.eclipse.jetty.server.HttpInputOverHTTP); line: 2, column: 3]",
    "errorClass": "com.fasterxml.jackson.databind.exc.MismatchedInputException",
    "host": null
}

我应该如何构建相应的查询，以使其与Postman一起工作？

Answer 1

我不熟悉Turnilo，但是您是否尝试过使用Druid控制台编写SQL，并使用"Run/...“下面的"Explain SQL query”选项将其转换为Native request菜单？

您的原生查询似乎是执行Top N，而不是列出所有电影，所以我认为SQL可能是这样的：

SELECT movie_id, country_id, SUM(revenue) total_revenue
FROM movies_source
WHERE __time BETWEEN '2021-11-18 00:01:00' AND '2021-11-21 00:01:00'
GROUP BY movie_id, country_id
ORDER BY total_revenue DESC
LIMIT 50

我没有要测试的数据源，但使用具有类似查询结构的样本维基百科数据进行了测试：

SELECT namespace, cityName, sum(sum_added) total
FROM "wikipedia" r 
WHERE cityName IS NOT NULL
 AND __time BETWEEN '2015-09-12 00:00:00' AND '2015-09-15 00:00:00' 
GROUP BY namespace, cityName
ORDER BY total DESC
limit 50

这将导致以下本机查询：

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "table",
    "name": "wikipedia"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "2015-09-12T00:00:00.000Z/2015-09-15T00:00:00.001Z"
    ]
  },
  "virtualColumns": [],
  "filter": {
    "type": "not",
    "field": {
      "type": "selector",
      "dimension": "cityName",
      "value": null,
      "extractionFn": null
    }
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "namespace",
      "outputName": "d0",
      "outputType": "STRING"
    },
    {
      "type": "default",
      "dimension": "cityName",
      "outputName": "d1",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "longSum",
      "name": "a0",
      "fieldName": "sum_added",
      "expression": null
    }
  ],
  "postAggregations": [],
  "having": null,
  "limitSpec": {
    "type": "default",
    "columns": [
      {
        "dimension": "a0",
        "direction": "descending",
        "dimensionOrder": {
          "type": "numeric"
        }
      }
    ],
    "limit": 50
  },
  "context": {
    "populateCache": false,
    "sqlOuterLimit": 101,
    "sqlQueryId": "cd5aabed-5e08-49b7-af63-fe82c125d3ee",
    "useApproximateCountDistinct": false,
    "useApproximateTopN": false,
    "useCache": false
  },
  "descending": false
}