You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@druid.apache.org by Jason Chen <ja...@shopify.com.INVALID> on 2021/06/24 20:27:05 UTC

A question about a potential bug in Druid Joins

Hello, Druid community,

Ben Krug from Imply points me to this mail list for my question about Druid Joins. We have a following Druid Join query that may trigger a bug in Druid:
> quote_type
> WITH DIM AS (
>   SELECT api_client_id, title
>   FROM inline_dimension_api_clients_1 AS API_CLIENTS
> ),
> FACTS AS (
>   SELECT api_client_id, COUNT(*) as api_client_count
>   FROM inline_data AS ORDERS
>   WHERE ORDERS.__time >= TIMESTAMP '2021-06-10 00:00:00' AND ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id = 25248974
>   GROUP BY 1
> )
> SELECT DIM.title, FACTS.api_client_id, FACTS.api_client_count
> FROM FACTS
> LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id

So the “api_client_id” field is `long` type in both “inline_data” and “inline_dimension_api_clients_1” datasources. However, when doing a join, the makeLongProcessor method will be called, and throw an “UnsupportedOperationException" because "index.keyType()" is string in MapIndex.

Then I found Gian Merlino has a PR to fix the issue. I have validated that this fix works for our case in my local Druid cluster. The fix is not included in Druid v0.21.1.

I have the following questions:

1. Why the index key type is `string` rather than `long` for my subquery? Is it implicitly transformed to `string` type for performance benefit?
2. When will you publish a new Druid release? Will the fix be part of the next release?


Thank you
Jason Chen



Jason (Jianbin) Chen
Senior Data Developer
p: +1 2066608351 | e: jason.chen@shopify.com
a: 234 Laurier Ave W Ottawa, ON K1N 5X8

Re: A question about a potential bug in Druid Joins

Posted by Ben Krug <be...@imply.io>.
To add details for any devs who might be able to answer this, the query
given does not throw an error if the GROUP BY is taken out.  The grouped by
field, api_client_id, is a long, but it seems as if grouping by it somehow
gets it cast to string?  Jason was asking why this would be, and where in
the code.

On Thu, Jun 24, 2021 at 1:27 PM Jason Chen <ja...@shopify.com.invalid>
wrote:

> Hello, Druid community,
>
> Ben Krug from Imply points me to this mail list for my question about
> Druid Joins. We have a following Druid Join query that may trigger a bug in
> Druid:
> > quote_type
> > WITH DIM AS (
> >   SELECT api_client_id, title
> >   FROM inline_dimension_api_clients_1 AS API_CLIENTS
> > ),
> > FACTS AS (
> >   SELECT api_client_id, COUNT(*) as api_client_count
> >   FROM inline_data AS ORDERS
> >   WHERE ORDERS.__time >= TIMESTAMP '2021-06-10 00:00:00' AND
> ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id =
> 25248974
> >   GROUP BY 1
> > )
> > SELECT DIM.title, FACTS.api_client_id, FACTS.api_client_count
> > FROM FACTS
> > LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id
>
> So the “api_client_id” field is `long` type in both
> “inline_data” and “inline_dimension_api_clients_1” datasources. However,
> when doing a join, the makeLongProcessor method will be called, and
> throw an “UnsupportedOperationException" because "index.keyType()" is
> string in MapIndex.
>
> Then I found Gian Merlino has a PR to fix the issue. I have validated that
> this fix works for our case in my local Druid cluster. The fix is not
> included in Druid v0.21.1.
>
> I have the following questions:
>
> 1. Why the index key type is `string` rather than `long` for my subquery?
> Is it implicitly transformed to `string` type for performance benefit?
> 2. When will you publish a new Druid release? Will the fix be part of the
> next release?
>
>
> Thank you
> Jason Chen
>
>
>
> Jason (Jianbin) Chen
> Senior Data Developer
> p: +1 2066608351 | e: jason.chen@shopify.com
> a: 234 Laurier Ave W Ottawa, ON K1N 5X8
>

Re: A question about a potential bug in Druid Joins

Posted by Jason Chen <ja...@shopify.com.INVALID>.
Hey Gian,

Thank you very much for your reply.

> "but I wonder if it might still be better-performing to do the + 0. Long typed keys might be faster than string typed keys."

When we are building our models, we didn’t consider the types of columns from the performance aspect. We just used the original types from the Kafka schema (the datasource is ingested from Kafka). Usually for dimension columns, we can store the fields as `string`, `long`, `double`, `float`, or `arrays of strings`. For metrics, we will store the columns as `float` or `long`. In a query, we will do aggregation or filtering to dimensions. Are you referring to different performance of different types when doing group by or filtering, e.g. group by / filtering on `long` typed keys performs better than `string` typed keys?

Apart from group by / filtering, we will also do Druid Joins on various types of keys. We have long-to-long matching, string-to-long matching, and long-to-string matching. Will the performance be differentiated when joining with different types on the left and right side? Will the join perform better if both sides are long or string?

We also tried to `CAST` the `long` key in the left-hand-side to `varchar` in query time (in a post-aggregation expression, before doing join) to avoid triggering the bug, and it works. Compared with the `api_client_id + 0` solution, will the `CAST` solution perform better or worse?


Here is the query with `CAST`?
> quote_type
> > quote_type
> > WITH DIM AS (
> >   SELECT api_client_id, title
> >   FROM api_clients_inline_dimension_20210619 AS API_CLIENTS
> > ),
> > FACTS AS (
> >   SELECT CAST(api_client_id as VARCHAR) as "c_api_client_id", COUNT(*) as api_client_count
> >   FROM inline_data AS ORDERS
> >   WHERE ORDERS.__time >= TIMESTAMP '2021-06-10 00:00:00' AND ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id = 25248974
> >   GROUP BY 1
> > )
> > SELECT DIM.title, FACTS.c_api_client_id, FACTS.api_client_count
> > FROM FACTS
> > LEFT JOIN DIM ON FACTS.c_api_client_id = DIM.api_client_id


The native query for `api_client_id + 0`: https://gist.github.com/drinkbeer/b9010a4e4c95ba470e25fe9ec38a4a9d
The native query for `CAST`: https://gist.github.com/drinkbeer/13c13e934937fd6d1e6427677f08fcd2


Regards,
Jianbin Chen
On Jun 24, 2021, 8:01 PM -0400, dev@druid.apache.org, wrote:
>
> but I wonder if it might still be
> better-performing to do the + 0. Long typed keys might be faster than
> string typed keys.

Re: A question about a potential bug in Druid Joins

Posted by Gian Merlino <gi...@apache.org>.
Thanks for writing back and confirming that the suggested workaround is
effective.

As far as comments on the future: when we do a release with #10942, your
original query will work ok too, but I wonder if it might still be
better-performing to do the + 0. Long typed keys might be faster than
string typed keys.

And later on, when we make the changes to carry type information through to
the Scan query toolchest, the original query will work *and* will switch to
using long typed keys automatically.

On Thu, Jun 24, 2021 at 4:35 PM Jason Chen <ja...@shopify.com.invalid>
wrote:

> Hey Gian,
>
> Thank you very much for your quick reply. The explanation is pretty clear.
> I can confirm that the query works with `api_client_id + 1`:
>
> > quote_type
> > WITH DIM AS (
> >   SELECT (api_client_id + 0) AS api_client_id, title
> >   FROM sales_model_api_clients_dimension AS API_CLIENTS
> >   WHERE API_CLIENTS.shop_id = 25248974
> > ),
> > FACTS AS (
> >   SELECT api_client_id, COUNT(*) as api_client_count
> >   FROM sales_facts AS ORDERS
> >   WHERE ORDERS.__time >= TIMESTAMP '2021-06-17 00:00:00' AND
> ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id =
> 25248974
> >   GROUP BY 1
> > )
> >
> > SELECT DIM.api_client_id, DIM.title, FACTS.api_client_count
> > FROM FACTS
> > LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id
>
>
>
> The right hand side scan subquery with a virtual column that contains
> the “outputType”:
>
>
>       "right":{
>          "type":"query",
>          "query":{
>             "queryType":"scan",
>             "dataSource":{
>                "type":"table",
>                "name":"api_clients_inline_dimension_20210619"
>             },
>             "intervals":{
>                "type":"intervals",
>                "intervals":[
>
>  "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
>                ]
>             },
>             "virtualColumns":[
>                {
>                   "type":"expression",
>                   "name":"v0",
>                   "expression":"(\"api_client_id\" + 0)",
>                   "outputType":"LONG"
>                }
>             ],
>             "resultFormat":"compactedList",
>             "batchSize":20480,
>             "order":"none",
>             "filter":null,
>             "columns":[
>                "title",
>                "v0"
>             ],
>             "legacy":false,
>             "context":{
>                "sqlOuterLimit":100,
>                "sqlQueryId":"a878dfe1-94d4-4b7f-b0b7-d36290e763da"
>             },
>             "descending":false,
>             "granularity":{
>                "type":"all"
>             }
>          }
>       },
>       "rightPrefix":"j0.",
>       "condition":"(\"d0\" == \"j0.v0\")",
>       "joinType":"LEFT"
>    },
>
>
> For comparison, the original right-hand-side subquery which has columns
> without the types:
>
>       "right":{
>          "type":"query",
>          "query":{
>             "queryType":"scan",
>             "dataSource":{
>                "type":"table",
>                "name":"api_clients_inline_dimension_20210619"
>             },
>             "intervals":{
>                "type":"intervals",
>                "intervals":[
>
>  "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
>                ]
>             },
>             "virtualColumns":[
>
>             ],
>             "resultFormat":"compactedList",
>             "batchSize":20480,
>             "order":"none",
>             "filter":null,
>             "columns":[
>                "api_client_id",
>                "title"
>             ],
>             "legacy":false,
>             "context":{
>                "sqlOuterLimit":100,
>                "sqlQueryId":"ea2c369e-c553-424f-b2ec-13d282ec315c"
>             },
>             "descending":false,
>             "granularity":{
>                "type":"all"
>             }
>          }
>       },
>
>
>
> > quote_type
> >
>
> Regards,
> Jianbin Chen
> On Jun 24, 2021, 5:08 PM -0400, dev@druid.apache.org, wrote:
> >
> > the DIM CTE is getting run as a Scan subquery, and the type information
> is
>

Re: A question about a potential bug in Druid Joins

Posted by Jason Chen <ja...@shopify.com.INVALID>.
Hey Gian,

Thank you very much for your quick reply. The explanation is pretty clear. I can confirm that the query works with `api_client_id + 1`:

> quote_type
> WITH DIM AS (
>   SELECT (api_client_id + 0) AS api_client_id, title
>   FROM sales_model_api_clients_dimension AS API_CLIENTS
>   WHERE API_CLIENTS.shop_id = 25248974
> ),
> FACTS AS (
>   SELECT api_client_id, COUNT(*) as api_client_count
>   FROM sales_facts AS ORDERS
>   WHERE ORDERS.__time >= TIMESTAMP '2021-06-17 00:00:00' AND ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id = 25248974
>   GROUP BY 1
> )
>
> SELECT DIM.api_client_id, DIM.title, FACTS.api_client_count
> FROM FACTS
> LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id



The right hand side scan subquery with a virtual column that contains the “outputType”:


      "right":{
         "type":"query",
         "query":{
            "queryType":"scan",
            "dataSource":{
               "type":"table",
               "name":"api_clients_inline_dimension_20210619"
            },
            "intervals":{
               "type":"intervals",
               "intervals":[
                  "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
               ]
            },
            "virtualColumns":[
               {
                  "type":"expression",
                  "name":"v0",
                  "expression":"(\"api_client_id\" + 0)",
                  "outputType":"LONG"
               }
            ],
            "resultFormat":"compactedList",
            "batchSize":20480,
            "order":"none",
            "filter":null,
            "columns":[
               "title",
               "v0"
            ],
            "legacy":false,
            "context":{
               "sqlOuterLimit":100,
               "sqlQueryId":"a878dfe1-94d4-4b7f-b0b7-d36290e763da"
            },
            "descending":false,
            "granularity":{
               "type":"all"
            }
         }
      },
      "rightPrefix":"j0.",
      "condition":"(\"d0\" == \"j0.v0\")",
      "joinType":"LEFT"
   },


For comparison, the original right-hand-side subquery which has columns without the types:

      "right":{
         "type":"query",
         "query":{
            "queryType":"scan",
            "dataSource":{
               "type":"table",
               "name":"api_clients_inline_dimension_20210619"
            },
            "intervals":{
               "type":"intervals",
               "intervals":[
                  "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
               ]
            },
            "virtualColumns":[

            ],
            "resultFormat":"compactedList",
            "batchSize":20480,
            "order":"none",
            "filter":null,
            "columns":[
               "api_client_id",
               "title"
            ],
            "legacy":false,
            "context":{
               "sqlOuterLimit":100,
               "sqlQueryId":"ea2c369e-c553-424f-b2ec-13d282ec315c"
            },
            "descending":false,
            "granularity":{
               "type":"all"
            }
         }
      },



> quote_type
>

Regards,
Jianbin Chen
On Jun 24, 2021, 5:08 PM -0400, dev@druid.apache.org, wrote:
>
> the DIM CTE is getting run as a Scan subquery, and the type information is

Re: A question about a potential bug in Druid Joins

Posted by Gian Merlino <gi...@apache.org>.
Hey Jason,

I suppose you're talking about this patch:
https://github.com/apache/druid/pull/10942

1) The patch applies to a situation where the left-hand column of a join
condition is long-typed, and the right-hand side is string-typed. I suspect
the DIM CTE is getting run as a Scan subquery, and the type information is
getting lost here:
https://github.com/apache/druid/blob/druid-0.21.1/processing/src/main/java/org/apache/druid/query/scan/ScanQueryQueryToolChest.java#L177-L178.
That means that when we build an index on the subquery, we'll do it using
string keys, since strings are used when the actual type is unknown. The
code also suggests a possible workaround. If you look a few lines up, you
can see that type info is only present for virtual columns. So you could
try doing "SELECT api_client_id + 0" instead of "SELECT api_client_id",
which should cause a virtual column to be generated and thereby preserve
the type info. I haven't tested this, though, so let us know if it works
🙂. And like the comment says: in the future we'd like to be able to fill
in the real type info. [* see note below for details]

2) I'm not sure when the next Druid release will be published. There's
usually one release every few months. The next release from master will
include this patch. That'll likely be the very next release, unless a patch
release is needed for some reason. (Patch releases are usually made from
the prior release branch instead of from master.)

[*] Some details on why we don't fill in type info here today. The
challenge is that the native query toolchests don't currently know the data
types that will actually be encountered. Usually that's fine, because we
only need to know the *result* types, and most native query outputs are
strongly typed. For example, the groupBy toolchest is able to introspect
all of its dimensions and aggregators to generate a fully typed result
signature. This isn't possible for plain Scan queries though. All it has is
the names of the input columns. So, we'd need to carry the type info
through from somewhere else; perhaps from the SQL layer.


On Thu, Jun 24, 2021 at 1:27 PM Jason Chen <ja...@shopify.com.invalid>
wrote:

> Hello, Druid community,
>
> Ben Krug from Imply points me to this mail list for my question about
> Druid Joins. We have a following Druid Join query that may trigger a bug in
> Druid:
> > quote_type
> > WITH DIM AS (
> >   SELECT api_client_id, title
> >   FROM inline_dimension_api_clients_1 AS API_CLIENTS
> > ),
> > FACTS AS (
> >   SELECT api_client_id, COUNT(*) as api_client_count
> >   FROM inline_data AS ORDERS
> >   WHERE ORDERS.__time >= TIMESTAMP '2021-06-10 00:00:00' AND
> ORDERS.__time < TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id =
> 25248974
> >   GROUP BY 1
> > )
> > SELECT DIM.title, FACTS.api_client_id, FACTS.api_client_count
> > FROM FACTS
> > LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id
>
> So the “api_client_id” field is `long` type in both
> “inline_data” and “inline_dimension_api_clients_1” datasources. However,
> when doing a join, the makeLongProcessor method will be called, and
> throw an “UnsupportedOperationException" because "index.keyType()" is
> string in MapIndex.
>
> Then I found Gian Merlino has a PR to fix the issue. I have validated that
> this fix works for our case in my local Druid cluster. The fix is not
> included in Druid v0.21.1.
>
> I have the following questions:
>
> 1. Why the index key type is `string` rather than `long` for my subquery?
> Is it implicitly transformed to `string` type for performance benefit?
> 2. When will you publish a new Druid release? Will the fix be part of the
> next release?
>
>
> Thank you
> Jason Chen
>
>
>
> Jason (Jianbin) Chen
> Senior Data Developer
> p: +1 2066608351 | e: jason.chen@shopify.com
> a: 234 Laurier Ave W Ottawa, ON K1N 5X8
>