You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Xu Chi <xu...@126.com> on 2021/06/09 03:37:33 UTC
[HELP] Calcite interpreting SQL to Druid DSL Question
Hi team,
I’m trying to integrate Druid with Calcite by using calcite-druid adapter.
After following the tutorial, we could connect Calcite with Druid and fetching metadata back.
But when trying to execute query with below SQL, the DSL generated by Calcite is translating the __time where clause by using the combination of full time period intervals + filters to specify the specified time range. But this kind of interpreting results in a bad query performance.
Is there any way that we could achieve that translating the __time where clause directly into interval field as shown below in the “Desired DSL for interval field” section?
Thank you.
SQL:
SELECT * FROM xxx WHERE __time > '2021-06-06T00:00:15+08:00' AND __time < '2021-06-07T00:00:15+08:00' LIMIT 10
DSL Generated by Calcite:
{
"queryType": "scan",
"dataSource": "xxx",
"intervals": [
"1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"
],
"filter": {
"type": "and",
"fields": [
{
"type": "expression",
"expression": "(\"__time\" > timestamp_parse('2021-06-06T00:00:15\\u002B08:00','','Asia/Shanghai'))"
},
{
"type": "expression",
"expression": "(\"__time\" < timestamp_parse('2021-06-07T00:00:15\\u002B08:00','','Asia/Shanghai'))"
}
]
},
"columns": [
"__time",
...
],
"resultFormat": "compactedList",
"limit": 10
}
Desired DSL for the interval field:
"intervals": [ "2021-06-07T00:00:15+08:00/2021-06-08T00:00:15+08:00" ]
Regards,
Chi
Re: [HELP] Calcite interpreting SQL to Druid DSL Question
Posted by Xu Chi <xu...@126.com>.
Hi Ben,
Sorry for the late reply. The Druid is not owned by our team and I’m trying to figure out whether it’s resulting from some wrong configuration on the Druid side.
Regarding the tutorial, I’m able to run the tutorial and the problem happened when I’m trying to use code querying from some real Druid. There’s no error in the tutorial, sorry about the confusion.
Will come back to this thread if we have any new findings. Thanks.
Regards,
Chi
发自我的iPhone
> 在 2021年6月9日，下午11:31，Ben Krug <be...@imply.io> 写道：
>
> Hi Xu -
>
> I think it should work the way you expect. It would help to know the steps
> that you followed to set it up.
> When you say a tutorial, which one? Do you mean this -
> https://calcite.apache.org/docs/druid_adapter.html ?
> What steps did you take to set it up and run the query?
>
> Thank you,
>
> Ben
>
>> On Tue, Jun 8, 2021 at 10:26 PM Xu Chi <xu...@126.com> wrote:
>>
>> Hi Ben,
>>
>> Thanks for your prompt response.
>> I checked the data schema in Druid and found below definition which
>> specify the timestamp column.
>> If I query with “__time”, it will be the situation which mentioned in
>> previous email. And I tried to query with the column name “at”, it shows
>> there’s no such column which I think it has been filled into the “__time”
>> column.
>> Did I missed any steps when setting up the model or define Calcite’s
>> DruidSchema which resulting this issue?
>> Please advise. Thanks.
>>
>> {
>> ...
>> "timestampSpec": {
>> "column": "at",
>> "format": "posix"
>> }
>> ...
>> }
>>
>> Regards,
>> Chi
>>>
>>>> On Jun 9, 2021, at 12:01 PM, Ben Krug <be...@imply.io> wrote:
>>>
>>> I'm coming from the druid side, not a calcite person, but I wanted to
>> ask.
>>> What tutorial do you mean? Did you specify a model, and did it specify
>>> timestampColumn? (If so, was it __time?)
>>>
>>> I notice that in the druid console, if you use your SQL, it translate
>>> correctly, as in your desired DSL, so I imagine it's something in the
>>> configuration, maybe the model configuration?
>>>
>>>> On Tue, Jun 8, 2021 at 8:37 PM Xu Chi <xu...@126.com> wrote:
>>>>
>>>> Hi team,
>>>>
>>>> I’m trying to integrate Druid with Calcite by using calcite-druid
>> adapter.
>>>> After following the tutorial, we could connect Calcite with Druid and
>>>> fetching metadata back.
>>>> But when trying to execute query with below SQL, the DSL generated by
>>>> Calcite is translating the __time where clause by using the combination
>> of
>>>> full time period intervals + filters to specify the specified time
>> range.
>>>> But this kind of interpreting results in a bad query performance.
>>>>
>>>> Is there any way that we could achieve that translating the __time where
>>>> clause directly into interval field as shown below in the “Desired DSL
>> for
>>>> interval field” section?
>>>>
>>>> Thank you.
>>>>
>>>> SQL:
>>>> SELECT * FROM xxx WHERE __time > '2021-06-06T00:00:15+08:00' AND __time
>> <
>>>> '2021-06-07T00:00:15+08:00' LIMIT 10
>>>>
>>>> DSL Generated by Calcite:
>>>> {
>>>> "queryType": "scan",
>>>> "dataSource": "xxx",
>>>> "intervals": [
>>>> "1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"
>>>> ],
>>>> "filter": {
>>>> "type": "and",
>>>> "fields": [
>>>> {
>>>> "type": "expression",
>>>> "expression": "(\"__time\" >
>>>> timestamp_parse('2021-06-06T00:00:15\\u002B08:00','','Asia/Shanghai'))"
>>>> },
>>>> {
>>>> "type": "expression",
>>>> "expression": "(\"__time\" <
>>>> timestamp_parse('2021-06-07T00:00:15\\u002B08:00','','Asia/Shanghai'))"
>>>> }
>>>> ]
>>>> },
>>>> "columns": [
>>>> "__time",
>>>> ...
>>>> ],
>>>> "resultFormat": "compactedList",
>>>> "limit": 10
>>>> }
>>>>
>>>> Desired DSL for the interval field:
>>>> "intervals": [ "2021-06-07T00:00:15+08:00/2021-06-08T00:00:15+08:00" ]
>>>>
>>>>
>>>> Regards,
>>>> Chi
>>
>>
>
Re: [HELP] Calcite interpreting SQL to Druid DSL Question
Posted by Ben Krug <be...@imply.io>.
Hi Xu -
I think it should work the way you expect. It would help to know the steps
that you followed to set it up.
When you say a tutorial, which one? Do you mean this -
https://calcite.apache.org/docs/druid_adapter.html ?
What steps did you take to set it up and run the query?
Thank you,
Ben
On Tue, Jun 8, 2021 at 10:26 PM Xu Chi <xu...@126.com> wrote:
> Hi Ben,
>
> Thanks for your prompt response.
> I checked the data schema in Druid and found below definition which
> specify the timestamp column.
> If I query with “__time”, it will be the situation which mentioned in
> previous email. And I tried to query with the column name “at”, it shows
> there’s no such column which I think it has been filled into the “__time”
> column.
> Did I missed any steps when setting up the model or define Calcite’s
> DruidSchema which resulting this issue?
> Please advise. Thanks.
>
> {
> ...
> "timestampSpec": {
> "column": "at",
> "format": "posix"
> }
> ...
> }
>
> Regards,
> Chi
> >
> > On Jun 9, 2021, at 12:01 PM, Ben Krug <be...@imply.io> wrote:
> >
> > I'm coming from the druid side, not a calcite person, but I wanted to
> ask.
> > What tutorial do you mean? Did you specify a model, and did it specify
> > timestampColumn? (If so, was it __time?)
> >
> > I notice that in the druid console, if you use your SQL, it translate
> > correctly, as in your desired DSL, so I imagine it's something in the
> > configuration, maybe the model configuration?
> >
> >> On Tue, Jun 8, 2021 at 8:37 PM Xu Chi <xu...@126.com> wrote:
> >>
> >> Hi team,
> >>
> >> I’m trying to integrate Druid with Calcite by using calcite-druid
> adapter.
> >> After following the tutorial, we could connect Calcite with Druid and
> >> fetching metadata back.
> >> But when trying to execute query with below SQL, the DSL generated by
> >> Calcite is translating the __time where clause by using the combination
> of
> >> full time period intervals + filters to specify the specified time
> range.
> >> But this kind of interpreting results in a bad query performance.
> >>
> >> Is there any way that we could achieve that translating the __time where
> >> clause directly into interval field as shown below in the “Desired DSL
> for
> >> interval field” section?
> >>
> >> Thank you.
> >>
> >> SQL:
> >> SELECT * FROM xxx WHERE __time > '2021-06-06T00:00:15+08:00' AND __time
> <
> >> '2021-06-07T00:00:15+08:00' LIMIT 10
> >>
> >> DSL Generated by Calcite:
> >> {
> >> "queryType": "scan",
> >> "dataSource": "xxx",
> >> "intervals": [
> >> "1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"
> >> ],
> >> "filter": {
> >> "type": "and",
> >> "fields": [
> >> {
> >> "type": "expression",
> >> "expression": "(\"__time\" >
> >> timestamp_parse('2021-06-06T00:00:15\\u002B08:00','','Asia/Shanghai'))"
> >> },
> >> {
> >> "type": "expression",
> >> "expression": "(\"__time\" <
> >> timestamp_parse('2021-06-07T00:00:15\\u002B08:00','','Asia/Shanghai'))"
> >> }
> >> ]
> >> },
> >> "columns": [
> >> "__time",
> >> ...
> >> ],
> >> "resultFormat": "compactedList",
> >> "limit": 10
> >> }
> >>
> >> Desired DSL for the interval field:
> >> "intervals": [ "2021-06-07T00:00:15+08:00/2021-06-08T00:00:15+08:00" ]
> >>
> >>
> >> Regards,
> >> Chi
>
>
Re: [HELP] Calcite interpreting SQL to Druid DSL Question
Posted by Xu Chi <xu...@126.com>.
Hi Ben,
Thanks for your prompt response.
I checked the data schema in Druid and found below definition which specify the timestamp column.
If I query with “__time”, it will be the situation which mentioned in previous email. And I tried to query with the column name “at”, it shows there’s no such column which I think it has been filled into the “__time” column.
Did I missed any steps when setting up the model or define Calcite’s DruidSchema which resulting this issue?
Please advise. Thanks.
{
...
"timestampSpec": {
"column": "at",
"format": "posix"
}
...
}
Regards,
Chi
>
> On Jun 9, 2021, at 12:01 PM, Ben Krug <be...@imply.io> wrote:
>
> I'm coming from the druid side, not a calcite person, but I wanted to ask.
> What tutorial do you mean? Did you specify a model, and did it specify
> timestampColumn? (If so, was it __time?)
>
> I notice that in the druid console, if you use your SQL, it translate
> correctly, as in your desired DSL, so I imagine it's something in the
> configuration, maybe the model configuration?
>
>> On Tue, Jun 8, 2021 at 8:37 PM Xu Chi <xu...@126.com> wrote:
>>
>> Hi team,
>>
>> I’m trying to integrate Druid with Calcite by using calcite-druid adapter.
>> After following the tutorial, we could connect Calcite with Druid and
>> fetching metadata back.
>> But when trying to execute query with below SQL, the DSL generated by
>> Calcite is translating the __time where clause by using the combination of
>> full time period intervals + filters to specify the specified time range.
>> But this kind of interpreting results in a bad query performance.
>>
>> Is there any way that we could achieve that translating the __time where
>> clause directly into interval field as shown below in the “Desired DSL for
>> interval field” section?
>>
>> Thank you.
>>
>> SQL:
>> SELECT * FROM xxx WHERE __time > '2021-06-06T00:00:15+08:00' AND __time <
>> '2021-06-07T00:00:15+08:00' LIMIT 10
>>
>> DSL Generated by Calcite:
>> {
>> "queryType": "scan",
>> "dataSource": "xxx",
>> "intervals": [
>> "1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"
>> ],
>> "filter": {
>> "type": "and",
>> "fields": [
>> {
>> "type": "expression",
>> "expression": "(\"__time\" >
>> timestamp_parse('2021-06-06T00:00:15\\u002B08:00','','Asia/Shanghai'))"
>> },
>> {
>> "type": "expression",
>> "expression": "(\"__time\" <
>> timestamp_parse('2021-06-07T00:00:15\\u002B08:00','','Asia/Shanghai'))"
>> }
>> ]
>> },
>> "columns": [
>> "__time",
>> ...
>> ],
>> "resultFormat": "compactedList",
>> "limit": 10
>> }
>>
>> Desired DSL for the interval field:
>> "intervals": [ "2021-06-07T00:00:15+08:00/2021-06-08T00:00:15+08:00" ]
>>
>>
>> Regards,
>> Chi
Re: [HELP] Calcite interpreting SQL to Druid DSL Question
Posted by Ben Krug <be...@imply.io>.
I'm coming from the druid side, not a calcite person, but I wanted to ask.
What tutorial do you mean? Did you specify a model, and did it specify
timestampColumn? (If so, was it __time?)
I notice that in the druid console, if you use your SQL, it translate
correctly, as in your desired DSL, so I imagine it's something in the
configuration, maybe the model configuration?
On Tue, Jun 8, 2021 at 8:37 PM Xu Chi <xu...@126.com> wrote:
> Hi team,
>
> I’m trying to integrate Druid with Calcite by using calcite-druid adapter.
> After following the tutorial, we could connect Calcite with Druid and
> fetching metadata back.
> But when trying to execute query with below SQL, the DSL generated by
> Calcite is translating the __time where clause by using the combination of
> full time period intervals + filters to specify the specified time range.
> But this kind of interpreting results in a bad query performance.
>
> Is there any way that we could achieve that translating the __time where
> clause directly into interval field as shown below in the “Desired DSL for
> interval field” section?
>
> Thank you.
>
> SQL:
> SELECT * FROM xxx WHERE __time > '2021-06-06T00:00:15+08:00' AND __time <
> '2021-06-07T00:00:15+08:00' LIMIT 10
>
> DSL Generated by Calcite:
> {
> "queryType": "scan",
> "dataSource": "xxx",
> "intervals": [
> "1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"
> ],
> "filter": {
> "type": "and",
> "fields": [
> {
> "type": "expression",
> "expression": "(\"__time\" >
> timestamp_parse('2021-06-06T00:00:15\\u002B08:00','','Asia/Shanghai'))"
> },
> {
> "type": "expression",
> "expression": "(\"__time\" <
> timestamp_parse('2021-06-07T00:00:15\\u002B08:00','','Asia/Shanghai'))"
> }
> ]
> },
> "columns": [
> "__time",
> ...
> ],
> "resultFormat": "compactedList",
> "limit": 10
> }
>
> Desired DSL for the interval field:
> "intervals": [ "2021-06-07T00:00:15+08:00/2021-06-08T00:00:15+08:00" ]
>
>
> Regards,
> Chi