This message was deleted.

In MSQ it would be an INSERT/SELECT statement such as:

insert into ds2
 select floor(__time to HOUR) as __time,
        emailid, 
        count(distinct tmp_hash) as num_hashes
   from ds1
  group by 1, 2
partitioned by HOUR

The ingestion shouldn't care about the segment granularity of ds1, it is only source data. Let me know if that doesn't do what you want.

i am on druid .22 Should the datasource already exist?

Object 'ds2' not found

What you have is the correct result. If you query ds2 with approx count ds hll you will get the result you want. Creating a hll sketch in the ingestion will replace the column with the sketch

With INSERT INTO the datasource does not need to exist already. Druid 22 doesn't have MSQ ... to my knowledge MSQ was added in Druid 24. So you may need to upgrade, then add the "druid-multi-stage-query" extension to enable it. One easy way to tell that you have MSQ enabled is to look at the SQL engine options in the Query tab (see screenshot) ...

Hi Vijay, pls find the query here

{
  "queryType": "topN",
  "dataSource": {
    "type": "join",
    "left": "myDataSource",
    "right": {
      "type": "query",
      "query": {
        "queryType": "topN",
        "dataSource": "myDataSource",
        "dimension": "emailid",
        "threshold": 50000,
        "granularity": "all",
        "filter": {},
        "aggregations": [
          {
            "type": "HLLSketchBuild",
            "name": "dev_hash_chg_freq",
            "fieldName": "dev_hash",
            "lgK": 12,
            "tgtHllType": "HLL_4",
            "round": true
          }
        ],
        "metric": {
          "type": "numeric",
          "metric": "dev_hash_chg_freq"
        },
        "intervals": []
      }
    },
    "rightPrefix": "b.",
    "condition": "emailid == \"b.emailid\"",
    "joinType": "INNER"
  },
  "virtualColumns": [],
  "dimension": {
    "type": "default",
    "dimension": "host",
    "outputName": "host",
    "outputType": "STRING"
  },
  "metric": {
    "type": "numeric",
    "metric": "totalcount"
  },
  "intervals": {
    "type": "intervals",
    "intervals": []
  },
  "filter": {},
  "granularity": "all",
  "aggregations": [
    {
      "type": "filtered",
      "aggregator": {
        "type": "count",
        "name": "totalcount"
      },
      "filter": {},
      "name": "totalcount"
    }
  ],
  "context": {},
  "descending": false,
  "threshold": 10
}

Please get me the sql query

Sql version

select count(*), a.host from myDatasource a inner join (
 select emailid, APPROX_COUNT_DISTINCT_DS_HLL(dev_hash, 12, 'HLL_4') from myDatasource
 where "__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   group by emailid
   order by 2 desc
   limit 50000
    ) b
    on a.emailid=b.emailid
  where   
	"__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   GROUP BY a.host

I assume the query is

select count(*), a.host,a.emailid from myDatasource a inner join (
 select emailid, APPROX_COUNT_DISTINCT_DS_HLL(dev_hash, 12, 'HLL_4') from myDatasource
 where "__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   group by emailid
   order by 2 desc
   limit 50000
    ) b
    on a.emailid=b.emailid
  where   
	"__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   GROUP BY a.host

not really since the use of the emailid is only for joining as it represents a unique user. after that i need the host count irrespective of the emailid.

ok...got it

8s on a dataset of 21M records with 500k unique emailid

one thing is that your native query does not have any time filter.

oh i removed that for the sake of a smaller query/simplicity here. i can post the complete query if you need that

what is the requirement....I see that you are doing a hll in the right query but not using it in the final output. This is wasteful.

actually i am working with native query only. i just converted this into sql and missed that condition. pasting it again.

select count(*), a.host from myDatasource a inner join (
 select emailid, APPROX_COUNT_DISTINCT_DS_HLL(dev_hash, 12, 'HLL_4') dev_hash_count from myDatasource
 where "__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   group by emailid
   order by 2 desc
   limit 50000
    ) b
    on a.emailid=b.emailid
  where   
	"__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' AND b.dev_hash_count > 20
   GROUP BY a.host

try

select a.host,sum(total) from (select count(*) total, host,emailid from myDatasource group by emailid,host) a inner join (
 select emailid, APPROX_COUNT_DISTINCT_DS_HLL(dev_hash, 12, 'HLL_4') dev_hash_count from myDatasource from myDatasource
 where "__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
   group by emailid
   order by 2 desc
   limit 50000
    ) b
    on a.emailid=b.emailid
  where   
  "__time" BETWEEN TIMESTAMP '2023-03-06 07:10:00' AND TIMESTAMP '2023-03-06 11:11:00' 
  AND b.dev_hash_count > 20
   GROUP BY a.host

I have re-written to aggregtae the left table first before the join

Thanks Vijay. I will try that and let you know.