Complex select with condition

Apache Druid Q&A Analytics
#1

Hello! Can I execute complex select query with condition after “from” word? For more understanding my question show example:
I have select:
SELECT
__time as “time”,
name as “service”,
count(sla) as " "
FROM SYTESTER_DRUID
WHERE name IN (select “name” from “SYTESTER_DRUID”)
GROUP BY __time, name

But I want to make condition depends on value from another table (it’s not important how I get this value by using sub_query or some way else) like this:
SELECT
__time as “time”,
name as “service”,
count(sla) as " "
FROM SYTESTER_DRUID
WHERE name IN (if some_value = 0 then (select “name” from “SYTESTER_DRUID”) else “exmple_string” end)
GROUP BY __time, name

Can I do this in druid sql?

#2

Hi @nik,

Can you share an example of your desired output?

I’ve been playing with this a bit. My initial guess is that you’ll be better off using native queries.

I put this simple query together using the Wikipedia data from the Quickstart tutorial:

SELECT cityName AS "City Name", comment AS "Comment", countryName AS "Country Name"
FROM wikipedia
WHERE cityName IN ('Buenos Aires', 'Paris', 'New York')

I then used Explain SQL query in the web console to translate the SQL query to a native query:

{
  "queryType": "scan",
  "dataSource": {
    "type": "table",
    "name": "wikipedia"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "virtualColumns": [],
  "resultFormat": "compactedList",
  "batchSize": 20480,
  "limit": 101,
  "order": "none",
  "filter": {
    "type": "in",
    "dimension": "cityName",
    "values": [
      "New York",
      "Paris",
      "Buenos Aires"
    ]
  },
  "columns": [
    "cityName",
    "comment",
    "countryName"
  ],
  "legacy": false,
  "context": {
    "sqlOuterLimit": 101,
    "sqlQueryId": "9daafb5d-4bb5-4526-ae6f-3cb1509bada5"
  },
  "descending": false,
  "granularity": {
    "type": "all"
  }
}

You can see that the WHERE clause was turned into a filter. From there you might get to your desired result through a combination of logical expression filters.

Best,

Mark

#3

Yes, you can do this, selecting WHERE col IN (some other query), eg, see here. If you follow the link there, you’ll see that the subquery is generally executed and full results are loaded into the heap before proceeding with the query (by putting the values into a filter list, as Mark described). So, if your subselect results are really large, you might run into memory pressure in the heap.

#4

@Ben_Krug and @Mark_Herrera thanks so much! Your advices helped me.

#5

Excellent, glad to hear it, thanks!