This message was deleted Apache Druid #general

Join Slack

This message was deleted.

# general

Slackbot

03/01/2024, 1:00 PM

This message was deleted.

John Kowtko

03/01/2024, 1:42 PM

The easy way for me to test this is to write a SQL query and then run the explain plan:

Copy code

select a.*, b.* 
  from (select channel, count(page) NumPages from wikipedia where flags = 'N' group by channel) a
  join (select channel, sum(added) NumAdded from wikipedia where flags = 'N' group by channel) b
       on a.channel = b.channel

generates the following native query:

Copy code

{
  "queryType": "scan",
  "dataSource": {
    "type": "join",
    "left": {
      "type": "query",
      "query": {
        "queryType": "groupBy",
        "dataSource": {
          "type": "table",
          "name": "wikipedia"
        },
        "intervals": {
          "type": "intervals",
          "intervals": [
            "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
          ]
        },
        "filter": {
          "type": "equals",
          "column": "flags",
          "matchValueType": "STRING",
          "matchValue": "N"
        },
        "granularity": {
          "type": "all"
        },
        "dimensions": [
          {
            "type": "default",
            "dimension": "channel",
            "outputName": "d0",
            "outputType": "STRING"
          }
        ],
        "aggregations": [
          {
            "type": "filtered",
            "aggregator": {
              "type": "count",
              "name": "a0"
            },
            "filter": {
              "type": "not",
              "field": {
                "type": "null",
                "column": "page"
              }
            },
            "name": "a0"
          }
        ],
        "limitSpec": {
          "type": "NoopLimitSpec"
        },
        "context": {
          "enableWindowing": true,
          "maxNumTasks": 3,
          "queryId": "abd92aea-cce6-40f2-a072-3a175402e026",
          "sqlOuterLimit": 1001,
          "sqlQueryId": "abd92aea-cce6-40f2-a072-3a175402e026",
          "taskLockTypeX": "APPEND",
          "useNativeQueryExplain": true
        }
      }
    },
    "right": {
      "type": "query",
      "query": {
        "queryType": "groupBy",
        "dataSource": {
          "type": "table",
          "name": "wikipedia"
        },
        "intervals": {
          "type": "intervals",
          "intervals": [
            "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
          ]
        },
        "filter": {
          "type": "equals",
          "column": "flags",
          "matchValueType": "STRING",
          "matchValue": "N"
        },
        "granularity": {
          "type": "all"
        },
        "dimensions": [
          {
            "type": "default",
            "dimension": "channel",
            "outputName": "d0",
            "outputType": "STRING"
          }
        ],
        "aggregations": [
          {
            "type": "longSum",
            "name": "a0",
            "fieldName": "added"
          }
        ],
        "limitSpec": {
          "type": "NoopLimitSpec"
        },
        "context": {
          "enableWindowing": true,
          "maxNumTasks": 3,
          "queryId": "abd92aea-cce6-40f2-a072-3a175402e026",
          "sqlOuterLimit": 1001,
          "sqlQueryId": "abd92aea-cce6-40f2-a072-3a175402e026",
          "taskLockTypeX": "APPEND",
          "useNativeQueryExplain": true
        }
      }
    },
    "rightPrefix": "j0.",
    "condition": "(\"d0\" == \"j0.d0\")",
    "joinType": "INNER"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "resultFormat": "compactedList",
  "limit": 1001,
  "columns": [
    "a0",
    "d0",
    "j0.a0",
    "j0.d0"
  ],
  "legacy": false,
  "context": {
    "enableWindowing": true,
    "maxNumTasks": 3,
    "queryId": "abd92aea-cce6-40f2-a072-3a175402e026",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "abd92aea-cce6-40f2-a072-3a175402e026",
    "taskLockTypeX": "APPEND",
    "useNativeQueryExplain": true
  },
  "granularity": {
    "type": "all"
  }
}

and both work for me, producing expected output 🙂. Please try this approach and let us know if it still doesn't work. And if it does then you should be able to identify the difference from what you wrote.

Saicharan Poleboina

03/02/2024, 10:46 AM

Thank you!!!

Saicharan Poleboina

03/04/2024, 9:52 AM

Hi @John Kowtko , I tried with below query, but still facing the same issue. If I execute left and right queries, they are giving the common transaction Ids but the whole query is not giving any output. Can you please help me with this. Also, for the same query, If I give left as a table instead of query, it is giving valid output. { "dataSource": { "type": "join", "left": { "type": "query", "query": { "dataSource": { "type": "table", "name": "txn_Dev" }, "queryType": "groupBy", "intervals": [ "2024-02-15T150301.508Z/2025-02-22T150301.508Z" ], "granularity": "all", "virtualColumns": [], "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "tenantId", "value": "apitftrans1209" }, { "type": "selector", "dimension": "triggerState", "value": "COMPLETED" }, { "type": "in", "dimension": "gsiId", "values": [ "1209040733696" ] }, { "type": "in", "dimension": "triggerCuId", "values": [ "1205142740945" ] }, { "type": "selector", "dimension": "txnSlotItemDataName", "value": "phyent187654567" } ] }, "aggregations": [], "dimensions": [ "transactionId" ] } }, "right": { "type": "query", "query": { "dataSource": { "type": "table", "name": "txn_Dev" }, "queryType": "groupBy", "intervals": [ "2024-02-15T150301.508Z/2025-02-22T150301.508Z" ], "granularity": "all", "virtualColumns": [], "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "tenantId", "value": "apitftrans1209" }, { "type": "selector", "dimension": "triggerState", "value": "COMPLETED" }, { "type": "in", "dimension": "gsiId", "values": [ "1209040733696" ] }, { "type": "in", "dimension": "triggerCuId", "values": [ "1205142740945" ] }, { "type": "selector", "dimension": "txnSlotItemDataName", "value": "phyent29876545678" } ] }, "aggregations": [], "dimensions": [ "transactionId" ] } }, "rightPrefix": "r.", "condition": "transactionId == \"r.transactionId\"", "joinType": "INNER" }, "queryType": "scan", "intervals": [ "2024-02-14T111047.748Z/2025-02-21T111047.748Z" ], "granularity": "all", "virtualColumns": [], "columns": [ "transactionId" ] }

John Kowtko

03/05/2024, 1:07 PM

Hi Saicharan, I don't know native query spec well at all ... but looking at this, the things that I could spot that are different from my query: • you don't have aggregate columns defined in either query (I have them in both) • Your dimension columns in each query do not have outputName or outputType defined Maybe try modifying those two things to see if it has an effect? Otherwise, can you try writing this in SQL, and post your SQL statement? Thanks. John

2 Views

Open in Slack

Previous Next