This message was deleted Apache Druid #general

Join Slack

This message was deleted.

# general

Slackbot

09/28/2023, 7:44 AM

This message was deleted.

Abhishek Agarwal

09/28/2023, 9:38 AM

How much cardinality do these columns have?

Akansha Juyal -BM

09/28/2023, 3:41 PM

Not exactly sure of the number but it is url data for clickstream data

Sergio Ferragut

09/28/2023, 5:07 PM

What is the regex? Will it be consistent at query time? If so perhaps you can apply a transformation that extracts a relevant portion at ingestion time so that you can apply a direct equality filter on the calculated column at query time.

Akansha Juyal -BM

09/29/2023, 4:51 AM

So this is a contains filter basically and the url values are pretty long here. Also extracting relevant words here can be a bit of a task

John Kowtko

09/30/2023, 4:24 PM

A couple additional thoughts -- • Is there a simpler false-positive filter (e.g. like with trailing wildcard to match URL prefix, URL length, etc) that you can apply first with an AND condition, that will filter out most of the rows so the RegEx won't have to be applied to many rows? I believe Druid will short-circuit AND clauses in the filter for native queries. • Have you tried "like" instead of RegEx?

Akansha Juyal -BM

10/02/2023, 7:42 PM

Copy code

{
  "type": "search",
  "dimension": "id",
  "query": {
    "type": "insensitive_contains",
    "value": "test"
  },
  "extractionFn": {
    "type": "lookup",
    "lookup": "master",
    "retainMissingValue": true
  }
}

Akansha Juyal -BM

10/02/2023, 7:42 PM

This is how we are doing it currently

John Kowtko

10/03/2023, 5:09 PM

Is (case sensitive) "contains" significantly faster than "insensitive_contains"? Just thinking -- if it is, then if it's possible to store the index as all lowercase the search algorithm could potentially be made to be faster ... If not, then the only other thing I could think of to look at is if there is a trailing wildcard that could be used as a rough filter first before using the contains filter.

Akansha Juyal -BM

10/04/2023, 9:33 AM

Copy code

{
  "filter": {
    "type": "and",
    "fields": [
      {
        "type": "search",
        "dimension": "name",
        "query": {
          "type": "insensitive_contains",
          "value": "contains 1"
        }
      },
      {
        "type": "not",
        "field": {
          "type": "in",
          "dimension": "name",
          "values": [
            "not equal 1"
          ]
        }
      },
      {
        "type": "or",
        "fields": [
          {
            "type": "bound",
            "dimension": "name",
            "lower": "1",
            "upper": "5"
          },
          {
            "type": "bound",
            "dimension": "name",
            "lower": "6",
            "upper": "10",
            "ordering": "numeric"
          }
        ]
      }
    ]
  }
}

Akansha Juyal -BM

10/04/2023, 9:37 AM

So in the above filter, the insensitive_contains subquery is written before IN and the BOUND queries. Will it improve my performance if I reorder the filters inside the AND clause? Or the execution of the subqueries independent of each other here?

John Kowtko

10/04/2023, 1:28 PM

Hi AJ, I have tried reording filters in an AND group, from what I observed it would "short-circuit", which means if the first AND filter on the list evaluates to false, then it won't bother to evaluate the other filters on the list for that record that it is evaluating. So try putting the fastest processing filter first and see if that helps. In fact here even the OR condition would probably be better as the second AND filter, and put insensitive_contains last. Eager to hear if this helps ...

2 Views

Open in Slack

Previous Next