https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
g
After some measures in our production dataset, we saw the groupBy query result is sent back is approximatively 8-9s where the topN (without currency conversion) takes less than 2s. So any hints about the possibility to use lookups with topN queries would be helpful 🙂
Well, it is possible. We tried using a virtual column and use lookup expression.
Copy code
{
      "type": "expression",
      "name": "convertedSaleValue",
      "outputType": "DOUBLE",
      "expression": "localCurrencySalesValue * lookup(CONCAT(currency_short_desc, '_USD'),'currencies_rate_fromto')"
    },
The compute is done, but the result are not so accurate. We have a huge factor difference between topN & groupBy. Does anyone know when is this virtual column created ? (before/after/during) aggregation ? It seems like the currency rate used is not the proper one (since for 1 client, there could be multiple currencies used)
s
TopN will use an approximate algorithm where each historical will limit its results to 
max(1000, threshold)
top results prior to merging it in the broker to calculate the TopN globally. This means that the result is an approximation. GroupBy will do the full calculation. Another thought on your use case, if the lookup is used to query events that span multiple days, won't the exchange rates all be different by day? Perhaps this would require upstream data enhancement to add the USD conversion with the specific date based on the time of the transaction?
g
Well, I thought about that but it didn't explain that big differences. Actually, that was due to my mistake in the calculation 😅 Now we have something similar (at least something that could be explained by approximation). For the other point, no, our lookup is refreshed once a year, it does not reflect public rates. But we thought about adding most common currencies in the upstream data, which would really simplify queries. We could even reuse group by (not nested one) to get accurate results if needed with less impacts on query time.
👍 1
g
out of curiosity, do you have the full query you ran? wondering if we can suggest some other restructuring after seeing all the logic
g
Yes, sure. Here is the initial request :
Copy code
{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "groupBy",
      "dataSource": {
        "type": "table",
        "name": "sale"
      },
      "intervals": {
        "type": "intervals",
        "intervals": [
          "2022-01-01T00:00:00.000Z/2022-10-10T23:59:59.999Z"
        ]
      },
      "virtualColumns": [
        {
          "type": "expression",
          "name": "ts",
          "outputType": "LONG",
          "expression": "timestamp(transaction_time)"
        }
      ],
      "filter": {
        "type": "and",
        "fields": [
          {
            "type": "selector",
            "dimension": "brand_code",
            "value": "53",
            "extractionFn": null
          },
          {
            "type": "not",
            "field": {
              "type": "selector",
              "dimension": "operation_flag",
              "value": "D",
              "extractionFn": null
            }
          },
          {
            "type": "not",
            "field": {
              "type": "selector",
              "dimension": "operation_flag",
              "value": "true",
              "extractionFn": null
            }
          },
          {
            "type": "not",
            "field": {
              "type": "selector",
              "dimension": "unknown_flg",
              "value": "Y",
              "extractionFn": null
            }
          },
          {
            "type": "not",
            "field": {
              "type": "selector",
              "dimension": "transaction_time",
              "value": null,
              "extractionFn": null
            }
          }
        ]
      },
      "granularity": {
        "type": "all"
      },
      "dimensions": [
        {
          "type": "default",
          "dimension": "client_id",
          "outputName": "clientId",
          "outputType": "STRING"
        },
        {
          "type": "extraction",
          "dimension": "currency_short_desc",
          "outputName": "currency_rate",
          "outputType": "DOUBLE",
          "extractionFn": {
            "type": "cascade",
            "extractionFns": [
              {
                "type": "stringFormat",
                "format": "%s_USD"
              },
              {
                "type": "registeredLookup",
                "lookup": "currencies_rate_fromto",
                "retainMissingValue": true
              }
            ]
          }
        }
      ],
      "aggregations": [
        {
          "type": "doubleSum",
          "name": "localCurrencySalesValue",
          "fieldName": "amt_discounted",
          "expression": null
        },
        {
          "type": "longSum",
          "name": "quantity",
          "fieldName": "qty",
          "expression": null
        },
        {
          "type": "longMax",
          "name": "lastPurchaseDate",
          "fieldName": "ts",
          "expression": null
        },
        {
          "type": "stringLast",
          "name": "firstName",
          "fieldName": "client_name_first_local",
          "expression": null
        },
        {
          "type": "stringLast",
          "name": "lastName",
          "fieldName": "client_name_last_local",
          "expression": null
        }
      ],
      "postAggregations": [
        {
          "type": "arithmetic",
          "name": "convertedSalesValue",
          "fn": "*",
          "fields": [
            {
              "type": "fieldAccess",
              "name": "localCurrencySalesValue",
              "fieldName": "localCurrencySalesValue"
            },
            {
              "type": "fieldAccess",
              "name": "currency_rate",
              "fieldName": "currency_rate"
            }
          ]
        }
      ]
    }
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "2022-01-01T00:00:00.000Z/2022-10-10T23:59:59.999Z"
    ]
  },
  "virtualColumns": [],
  "filter": null,
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "clientId",
      "outputName": "clientId",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "doubleSum",
      "name": "salesValue",
      "fieldName": "convertedSalesValue",
      "expression": null
    },
    {
      "type": "longSum",
      "name": "quantity",
      "fieldName": "quantity",
      "expression": null
    },
    {
      "type": "longMax",
      "name": "lastPurchaseDate",
      "fieldName": "lastPurchaseDate",
      "expression": null
    },
    {
      "type": "stringLast",
      "name": "firstName",
      "fieldName": "firstName",
      "expression": null
    },
    {
      "type": "stringLast",
      "name": "lastName",
      "fieldName": "lastName",
      "expression": null
    }
  ],
  "having": null,
  "limitSpec": {
    "type": "default",
    "limit": 10,
    "columns": [
      {
        "dimension": "salesValue",
        "direction": "descending",
        "dimensionOrder": "numeric"
      }
    ]
  },
  "context": {
    "groupByStrategy": "v2"
  },
  "descending": false
}
and here is the topN equivalent one :
Copy code
{
  "queryType": "topN",
  "metric": "salesValue",
  "threshold": 10,
  "dataSource": {
    "type": "table",
    "name": "sale"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "2022-01-01T00:00:00.000Z/2022-10-10T23:59:59.999Z"
    ]
  },
  "virtualColumns": [
    {
      "type": "expression",
      "name": "convertedSaleValue",
      "outputType": "DOUBLE",
      "expression": "amt_discounted * lookup(CONCAT(currency_short_desc, '_USD'),'currencies_rate_fromto')"
    },
    {
      "type": "expression",
      "name": "ts",
      "outputType": "LONG",
      "expression": "timestamp(transaction_time)"
    }
  ],
  "filter": {
    "type": "and",
    "fields": [
      {
        "type": "selector",
        "dimension": "brand_code",
        "value": "53",
        "extractionFn": null
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "operation_flag",
          "value": "D",
          "extractionFn": null
        }
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "operation_flag",
          "value": "true",
          "extractionFn": null
        }
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "unknown_flg",
          "value": "Y",
          "extractionFn": null
        }
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "transaction_time",
          "value": null,
          "extractionFn": null
        }
      }
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimension": "client_id",
  "aggregations": [
    {
      "type": "stringLast",
      "name": "firstName",
      "fieldName": "client_name_first_local",
      "expression": null
    },
    {
      "type": "stringLast",
      "name": "lastName",
      "fieldName": "client_name_last_local",
      "expression": null
    },
    {
      "type": "doubleSum",
      "name": "salesValue",
      "fieldName": "convertedSaleValue",
      "expression": null
    },
    {
      "type": "longSum",
      "name": "quantity",
      "fieldName": "qty",
      "expression": null
    },
    {
      "type": "longMax",
      "name": "lastPurchaseDate",
      "fieldName": "ts",
      "expression": null
    }
  ]
}
Don't hesitate if you need some other inputs.
g
oh man, the topN version is likely going to be super slow since 
lookup
is a string function, and it's being fed into arithmetic. that means we need to do a string parse on every row!
g
That's what I thought until I run it against our prod dataset. Group by : ~10s TopN : ~1s 🤷‍♂️ It may be "druid slow" but still better for our users 😄
g
applying 
timestamp(transaction_time)
on each row is also going to be pretty rough
possibly because you have a lot of 
client_id
?
g
Yes Don't have the exact in head, but yes
g
that's generally when topN has an advantage, if there are a lot of distinct values of the dimension (
client_id
) in this case
out of curiosity -- can you try this one? I think it's equivalent to your topN version, but replaces the 
lookup
with a join on a subquery, which might be more efficient since it avoids doing string-to-number conversion for each row. It does add a subquery, though, so I'm not 100% sure which way it'll go efficiency-wise 🙂
the 
TIMESTAMP_TO_MILLIS(TIME_PARSE(transaction_time))
is the equivalent of the expr 
timestamp(transaction_time)
you have. this is probably taking a bunch of time too, so it'd be better to store that time as a number instead of a string. i.e. do the transformation on ingest instead of during query
g
well, I fixed some errors thrown back in the druid query ui, but now I get a 503 error 😕 Damn, my cluster just went down, I forgot about the time 😅
g
the time?
g
My QA cluster is destroyed during nights. So I run the query in production, annnnd
Copy code
Error: Unknown exception
Cannot build plan for query: WITH currencies AS ( SELECT REPLACE(k, '_USD', '') AS currency, CAST(v AS DOUBLE) AS rate FROM lookup.currencies_rate_fromto WHERE k LIKE '%_USD' ) SELECT client_id AS clientId, SUM(amt_discounted * currencies.rate) AS convertedSalesValue, SUM(qty) AS quantity, MAX(TIMESTAMP_TO_MILLIS(TIME_PARSE(transaction_time))) AS lastPurchaseDate, LATEST(client_name_first_local, 1024) AS firstName, LATEST(client_name_last_local, 1024) AS lastName FROM sale LEFT JOIN currencies ON sale.currency_short_desc = currencies.currency WHERE __time >= TIMESTAMP '2022-01-01 00:00:00' AND __time <= TIMESTAMP '2022-10-10 23:59:59' AND brand_code = '53' AND operation_flag NOT IN ('D', 'true') AND unknown_flg <> 'Y' AND transaction_time IS NOT NULL GROUP BY client_id ORDER BY SUM(convertedSalesValue) DESC LIMIT 10
org.apache.druid.java.util.common.ISE
I guessed your function "LATEST_VALUE" was "LATEST" and just fixed some minor typos. I'll go in logs to see if I get more insights
Copy code
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=DRUID, sort=[6 DESC].
Missing conversion is LogicalSort[convention: NONE -> DRUID]
There is 1 empty subset: rel#9046781:Subset#9.DRUID.[6 DESC], the relevant part of the original plan is as follows
9046779:LogicalSort(sort0=[$6], dir0=[DESC], fetch=[10])
  9046777:LogicalAggregate(subset=[rel#9046778:Subset#8.NONE.[]], group=[{0}], convertedSalesValue=[SUM($1)], quantity=[SUM($2)], lastPurchaseDate=[MAX($3)], firstName=[LATEST($4, $5)], lastName=[LATEST($6, $5)], agg#5=[SUM($7)])
    9046775:LogicalProject(subset=[rel#9046776:Subset#7.NONE.[]], clientId=[$3], $f1=[*($1, $12)], qty=[$8], $f3=[TIMESTAMP_TO_MILLIS(TIME_PARSE($9))], client_name_first_local=[$4], $f5=[1024], client_name_last_local=[$5], $f7=[SUM(*($1, $12))])
      9046773:LogicalFilter(subset=[rel#9046774:Subset#6.NONE.[]], condition=[AND(>=($0, 2022-01-01 00:00:00), <=($0, 2022-10-10 23:59:59), =($2, '53'), <>($7, 'D'), <>($7, 'true'), <>($10, 'Y'), IS NOT NULL($9))])
        9046771:LogicalJoin(subset=[rel#9046772:Subset#5.NONE.[]], condition=[=($6, $11)], joinType=[left])
          9046764:LogicalProject(subset=[rel#9046765:Subset#1.NONE.[]], __time=[$0], amt_discounted=[$4], brand_code=[$11], client_id=[$19], client_name_first_local=[$20], client_name_last_local=[$21], currency_short_desc=[$42], operation_flag=[$69], qty=[$76], transaction_time=[$124], unknown_flg=[$129])
            9046710:LogicalTableScan(subset=[rel#9046763:Subset#0.NONE.[]], table=[[druid, enriched_sale]])
          9046769:LogicalProject(subset=[rel#9046770:Subset#4.NONE.[]], currency=[REPLACE($0, '_USD', '')], rate=[CAST($1):DOUBLE])
            9046767:LogicalFilter(subset=[rel#9046768:Subset#3.NONE.[]], condition=[LIKE($0, '%_USD')])
              9046711:LogicalTableScan(subset=[rel#9046766:Subset#2.NONE.[]], table=[[lookup, currencies_rate_fromto]])
btw, I never noticed that kind of logs... is it related to sql queries only ? (because we only run native json queries here)
g
oops, i meant 
ANY_VALUE
not 
LATEST_VALUE
as to the error, it means the SQL planner can't figure out how to make a native query -- sometimes due to some unsupported feature. wonder what the reason is…
g
I'm in 0.22.1, maybe that is the reason ?
g
hmm, could be… I'm not sure, really
does this part work by itself?
Copy code
SELECT
    REPLACE(k, '_USD', '') AS currency,
    CAST(v AS DOUBLE) AS rate
  FROM lookup.currencies_rate_fromto
  WHERE k LIKE '%_USD'
g
yep
g
oh shoot
hmm
try replacing 
ORDER BY SUM(convertedSalesValue) DESC
with 
ORDER BY SUM(amt_discounted * currencies.rate) DESC
i got that part wrong
g
it's working
from some executions, it's faster than groupBy, but slower than topN
g
hmm, okay, well, that's good to know
g
can't win everytime ^^ Anyway, thanks for the time. I'll also take a look tomorrow at this transaction_time and its type
2 Views
Open in Slack
PreviousNext
Powered by