You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Kapil Ghodawat <ka...@gmail.com> on 2016/12/14 12:48:57 UTC
Column Ordinal Support in GROUP BY
Hi Folks,
I am using Apache Calcite to add SQL support for my custom data source. I
receive SQLs from external SQL clients like Tableau and I have written an
ODBC client for connecting to my data source, which basically delegates
these SQLs (generated by Tableau) to my Java program where I use Calcite to
execute them.
I am stuck at a place where I receive SQL which has GROUP BY clause that
uses column ordinals instead of columns names and when I try to validate
(parsing works fine) such SQLs through Calcite it fails.
E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY 1, 2
(The SQL says GROUP BY on prod_name and store_name fields which are column
ordinal 1 and 2 respectively in the SELECT clause)
I tried delving into the code of calcite and per my understanding I believe
that usage of column ordinals is supported in ORDER BY clause but not in
the GROUP BY.
Primarily, I want to know Is my understanding about no support of column
ordinals in GROUP BY correct? Or is there something I am missing?
Secondarily, if anyone knows about ODBC, is there a way I can tell/force
the Tableau to send me column names instead of column ordinals in the SQL?
--
Best Regards,
Kapil Ghodawat
contact: +91 94254 86638
Re: Column Ordinal Support in GROUP BY
Posted by Kapil Ghodawat <ka...@gmail.com>.
Thanks Julian for writing this. I haven't checked yet if power BI is
sending such SQLs but when you say Oracle also does not support this and
it's not standard SQL it gave me a big relief.
On Thu, Dec 15, 2016 at 10:17 PM, Julian Hyde <jh...@gmail.com>
wrote:
> Do you know for sure that Power BI generates this kind of SQL? I suspect
> it doesn't, because it's not standard SQL. And by the way, Oracle doesn't
> support it.
>
> I have no idea why I'm Tableau is generating this flavor of SQL.
>
> Calcite's tool integration is in pretty good shape, and that's because
> we've stayed close to the standard.
>
> There's no question that an extension like this would be nice, especially
> for end users writing SQL by hand (and contributions are most welcome!),
> but don't conflate it with tool integration.
>
> Julian
>
> > On Dec 15, 2016, at 06:21, Gian Merlino <gi...@imply.io> wrote:
> >
> > That, I'm not sure how to do. There might be a similar config there for
> > Power BI. Ultimately it would be nice to have support for this in
> Calcite,
> > since it seems to be a pretty common extension to SQL (and makes many
> > queries easier to write by hand) but I'm not a Calcite expert enough to
> > know how hard that would be…
> >
> > Gian
> >
> > On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat <kapilghodawat@gmail.com
> >
> > wrote:
> >
> >> Thanks a lot Gian. I think this would make Tableau work.
> >>
> >> But I am worried about connectivity from other SQL clients as i am
> planning
> >> in near future connectivity from Microsoft Power BI
> >>
> >>> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino <gi...@imply.io> wrote:
> >>>
> >>> With Tableau you can use a TDC file to tweak the SQL generation. If you
> >> set
> >>> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then
> >> Tableau
> >>> will avoid using aliases and ordinals for GROUP BY. See
> >>> http://kb.tableau.com/articles/knowledgebase/
> >> customizing-odbc-connections
> >>> for more info.
> >>>
> >>> The relevant Calcite issue is
> >>> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you
> care
> >>> about is getting Tableau to work then the TDC approach should do it.
> >>>
> >>> Gian
> >>>
> >>> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <
> kapilghodawat@gmail.com
> >>>
> >>> wrote:
> >>>
> >>>> Hi Folks,
> >>>>
> >>>> I am using Apache Calcite to add SQL support for my custom data
> >> source. I
> >>>> receive SQLs from external SQL clients like Tableau and I have written
> >> an
> >>>> ODBC client for connecting to my data source, which basically
> delegates
> >>>> these SQLs (generated by Tableau) to my Java program where I use
> >> Calcite
> >>> to
> >>>> execute them.
> >>>>
> >>>> I am stuck at a place where I receive SQL which has GROUP BY clause
> >> that
> >>>> uses column ordinals instead of columns names and when I try to
> >> validate
> >>>> (parsing works fine) such SQLs through Calcite it fails.
> >>>>
> >>>> E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP
> BY
> >>> 1,
> >>>> 2
> >>>>
> >>>> (The SQL says GROUP BY on prod_name and store_name fields which are
> >>> column
> >>>> ordinal 1 and 2 respectively in the SELECT clause)
> >>>>
> >>>>
> >>>> I tried delving into the code of calcite and per my understanding I
> >>> believe
> >>>> that usage of column ordinals is supported in ORDER BY clause but not
> >> in
> >>>> the GROUP BY.
> >>>>
> >>>> Primarily, I want to know Is my understanding about no support of
> >> column
> >>>> ordinals in GROUP BY correct? Or is there something I am missing?
> >>>>
> >>>> Secondarily, if anyone knows about ODBC, is there a way I can
> >> tell/force
> >>>> the Tableau to send me column names instead of column ordinals in the
> >>> SQL?
> >>>>
> >>>>
> >>>> --
> >>>> Best Regards,
> >>>> Kapil Ghodawat
> >>>> contact: +91 94254 86638
> >>>>
> >>>
> >>
> >>
> >>
> >> --
> >> Best Regards,
> >> Kapil Ghodawat
> >> contact: +91 94254 86638
> >>
>
--
Best Regards,
Kapil Ghodawat
contact: +91 94254 86638
Re: Column Ordinal Support in GROUP BY
Posted by Julian Hyde <jh...@gmail.com>.
Do you know for sure that Power BI generates this kind of SQL? I suspect it doesn't, because it's not standard SQL. And by the way, Oracle doesn't support it.
I have no idea why I'm Tableau is generating this flavor of SQL.
Calcite's tool integration is in pretty good shape, and that's because we've stayed close to the standard.
There's no question that an extension like this would be nice, especially for end users writing SQL by hand (and contributions are most welcome!), but don't conflate it with tool integration.
Julian
> On Dec 15, 2016, at 06:21, Gian Merlino <gi...@imply.io> wrote:
>
> That, I'm not sure how to do. There might be a similar config there for
> Power BI. Ultimately it would be nice to have support for this in Calcite,
> since it seems to be a pretty common extension to SQL (and makes many
> queries easier to write by hand) but I'm not a Calcite expert enough to
> know how hard that would be…
>
> Gian
>
> On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat <ka...@gmail.com>
> wrote:
>
>> Thanks a lot Gian. I think this would make Tableau work.
>>
>> But I am worried about connectivity from other SQL clients as i am planning
>> in near future connectivity from Microsoft Power BI
>>
>>> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino <gi...@imply.io> wrote:
>>>
>>> With Tableau you can use a TDC file to tweak the SQL generation. If you
>> set
>>> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then
>> Tableau
>>> will avoid using aliases and ordinals for GROUP BY. See
>>> http://kb.tableau.com/articles/knowledgebase/
>> customizing-odbc-connections
>>> for more info.
>>>
>>> The relevant Calcite issue is
>>> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care
>>> about is getting Tableau to work then the TDC approach should do it.
>>>
>>> Gian
>>>
>>> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <kapilghodawat@gmail.com
>>>
>>> wrote:
>>>
>>>> Hi Folks,
>>>>
>>>> I am using Apache Calcite to add SQL support for my custom data
>> source. I
>>>> receive SQLs from external SQL clients like Tableau and I have written
>> an
>>>> ODBC client for connecting to my data source, which basically delegates
>>>> these SQLs (generated by Tableau) to my Java program where I use
>> Calcite
>>> to
>>>> execute them.
>>>>
>>>> I am stuck at a place where I receive SQL which has GROUP BY clause
>> that
>>>> uses column ordinals instead of columns names and when I try to
>> validate
>>>> (parsing works fine) such SQLs through Calcite it fails.
>>>>
>>>> E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY
>>> 1,
>>>> 2
>>>>
>>>> (The SQL says GROUP BY on prod_name and store_name fields which are
>>> column
>>>> ordinal 1 and 2 respectively in the SELECT clause)
>>>>
>>>>
>>>> I tried delving into the code of calcite and per my understanding I
>>> believe
>>>> that usage of column ordinals is supported in ORDER BY clause but not
>> in
>>>> the GROUP BY.
>>>>
>>>> Primarily, I want to know Is my understanding about no support of
>> column
>>>> ordinals in GROUP BY correct? Or is there something I am missing?
>>>>
>>>> Secondarily, if anyone knows about ODBC, is there a way I can
>> tell/force
>>>> the Tableau to send me column names instead of column ordinals in the
>>> SQL?
>>>>
>>>>
>>>> --
>>>> Best Regards,
>>>> Kapil Ghodawat
>>>> contact: +91 94254 86638
>>>>
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Kapil Ghodawat
>> contact: +91 94254 86638
>>
Re: Column Ordinal Support in GROUP BY
Posted by Gian Merlino <gi...@imply.io>.
That, I'm not sure how to do. There might be a similar config there for
Power BI. Ultimately it would be nice to have support for this in Calcite,
since it seems to be a pretty common extension to SQL (and makes many
queries easier to write by hand) but I'm not a Calcite expert enough to
know how hard that would be…
Gian
On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat <ka...@gmail.com>
wrote:
> Thanks a lot Gian. I think this would make Tableau work.
>
> But I am worried about connectivity from other SQL clients as i am planning
> in near future connectivity from Microsoft Power BI
>
> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino <gi...@imply.io> wrote:
>
> > With Tableau you can use a TDC file to tweak the SQL generation. If you
> set
> > CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then
> Tableau
> > will avoid using aliases and ordinals for GROUP BY. See
> > http://kb.tableau.com/articles/knowledgebase/
> customizing-odbc-connections
> > for more info.
> >
> > The relevant Calcite issue is
> > https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care
> > about is getting Tableau to work then the TDC approach should do it.
> >
> > Gian
> >
> > On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <kapilghodawat@gmail.com
> >
> > wrote:
> >
> > > Hi Folks,
> > >
> > > I am using Apache Calcite to add SQL support for my custom data
> source. I
> > > receive SQLs from external SQL clients like Tableau and I have written
> an
> > > ODBC client for connecting to my data source, which basically delegates
> > > these SQLs (generated by Tableau) to my Java program where I use
> Calcite
> > to
> > > execute them.
> > >
> > > I am stuck at a place where I receive SQL which has GROUP BY clause
> that
> > > uses column ordinals instead of columns names and when I try to
> validate
> > > (parsing works fine) such SQLs through Calcite it fails.
> > >
> > > E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY
> > 1,
> > > 2
> > >
> > > (The SQL says GROUP BY on prod_name and store_name fields which are
> > column
> > > ordinal 1 and 2 respectively in the SELECT clause)
> > >
> > >
> > > I tried delving into the code of calcite and per my understanding I
> > believe
> > > that usage of column ordinals is supported in ORDER BY clause but not
> in
> > > the GROUP BY.
> > >
> > > Primarily, I want to know Is my understanding about no support of
> column
> > > ordinals in GROUP BY correct? Or is there something I am missing?
> > >
> > > Secondarily, if anyone knows about ODBC, is there a way I can
> tell/force
> > > the Tableau to send me column names instead of column ordinals in the
> > SQL?
> > >
> > >
> > > --
> > > Best Regards,
> > > Kapil Ghodawat
> > > contact: +91 94254 86638
> > >
> >
>
>
>
> --
> Best Regards,
> Kapil Ghodawat
> contact: +91 94254 86638
>
Re: Column Ordinal Support in GROUP BY
Posted by Kapil Ghodawat <ka...@gmail.com>.
Thanks a lot Gian. I think this would make Tableau work.
But I am worried about connectivity from other SQL clients as i am planning
in near future connectivity from Microsoft Power BI
On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino <gi...@imply.io> wrote:
> With Tableau you can use a TDC file to tweak the SQL generation. If you set
> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then Tableau
> will avoid using aliases and ordinals for GROUP BY. See
> http://kb.tableau.com/articles/knowledgebase/customizing-odbc-connections
> for more info.
>
> The relevant Calcite issue is
> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care
> about is getting Tableau to work then the TDC approach should do it.
>
> Gian
>
> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <ka...@gmail.com>
> wrote:
>
> > Hi Folks,
> >
> > I am using Apache Calcite to add SQL support for my custom data source. I
> > receive SQLs from external SQL clients like Tableau and I have written an
> > ODBC client for connecting to my data source, which basically delegates
> > these SQLs (generated by Tableau) to my Java program where I use Calcite
> to
> > execute them.
> >
> > I am stuck at a place where I receive SQL which has GROUP BY clause that
> > uses column ordinals instead of columns names and when I try to validate
> > (parsing works fine) such SQLs through Calcite it fails.
> >
> > E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY
> 1,
> > 2
> >
> > (The SQL says GROUP BY on prod_name and store_name fields which are
> column
> > ordinal 1 and 2 respectively in the SELECT clause)
> >
> >
> > I tried delving into the code of calcite and per my understanding I
> believe
> > that usage of column ordinals is supported in ORDER BY clause but not in
> > the GROUP BY.
> >
> > Primarily, I want to know Is my understanding about no support of column
> > ordinals in GROUP BY correct? Or is there something I am missing?
> >
> > Secondarily, if anyone knows about ODBC, is there a way I can tell/force
> > the Tableau to send me column names instead of column ordinals in the
> SQL?
> >
> >
> > --
> > Best Regards,
> > Kapil Ghodawat
> > contact: +91 94254 86638
> >
>
--
Best Regards,
Kapil Ghodawat
contact: +91 94254 86638
Re: Column Ordinal Support in GROUP BY
Posted by Gian Merlino <gi...@imply.io>.
With Tableau you can use a TDC file to tweak the SQL generation. If you set
CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then Tableau
will avoid using aliases and ordinals for GROUP BY. See
http://kb.tableau.com/articles/knowledgebase/customizing-odbc-connections
for more info.
The relevant Calcite issue is
https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care
about is getting Tableau to work then the TDC approach should do it.
Gian
On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <ka...@gmail.com>
wrote:
> Hi Folks,
>
> I am using Apache Calcite to add SQL support for my custom data source. I
> receive SQLs from external SQL clients like Tableau and I have written an
> ODBC client for connecting to my data source, which basically delegates
> these SQLs (generated by Tableau) to my Java program where I use Calcite to
> execute them.
>
> I am stuck at a place where I receive SQL which has GROUP BY clause that
> uses column ordinals instead of columns names and when I try to validate
> (parsing works fine) such SQLs through Calcite it fails.
>
> E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY 1,
> 2
>
> (The SQL says GROUP BY on prod_name and store_name fields which are column
> ordinal 1 and 2 respectively in the SELECT clause)
>
>
> I tried delving into the code of calcite and per my understanding I believe
> that usage of column ordinals is supported in ORDER BY clause but not in
> the GROUP BY.
>
> Primarily, I want to know Is my understanding about no support of column
> ordinals in GROUP BY correct? Or is there something I am missing?
>
> Secondarily, if anyone knows about ODBC, is there a way I can tell/force
> the Tableau to send me column names instead of column ordinals in the SQL?
>
>
> --
> Best Regards,
> Kapil Ghodawat
> contact: +91 94254 86638
>