You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Talat Uyarer <tu...@paloaltonetworks.com> on 2020/07/15 01:59:59 UTC
Calcite and Enum Type
Hi,
I am using Beam SQL which uses calcite. IN Beam we have logical types which
we can use for Enumeration. But looks like they did not implement
enumeration support for calcite. I want to give that support. But I could
not find the right way to implement it. In my Enumeration is a
HashMap<String,Integer>.
My question is: Does calcite support Enum types like mysql ? [1] if not
How can store metadata information such as (enum strings, beam type name
etc) for columns ?
Thanks
[1] https://dev.mysql.com/doc/refman/8.0/en/enum.html
Re: Calcite and Enum Type
Posted by Talat Uyarer <tu...@paloaltonetworks.com>.
Hi Rui,
Yes On the Beam side I am using Enum logical type. I thought I can use Int
sql type however i faced following issues.
Let assume my enum is [(0,Apple),(1,Orange),(2,Pears),(3,Banana)].
- If I map my Enum type to any calcite type. there can be other columns
which have the same sqltype. How can I know which column is my enum type
when I get back the result of the query. For instance If we store Enum as
Int. There could be other regular int columns.
- Also I can not enforce values if I map some other types. Let's continue
to use the same example as previous item. If I use INT How can I
enforce that the column's value should be always smaller than 4.
- If i have two different enum type How can i understand which column
belongs to which enum type.
I actually thought to use Row/Struct type but i could not find solution
above issues. Please let me know if there is a solution fo them ?
Thanks
On Wed, Jul 15, 2020 at 4:24 PM Rui Wang <am...@apache.org> wrote:
> Hi Talat,
>
> I am guessing when you say logical type, you mean something like this in
> Beam [1].
>
> My question is why do you need Calcite to support ENUM? If you use logical
> type, you can define a ENUM by yourself and the underlying type can be a
> Map<string, int>. Map is supported by Calcite. So ENUM will be transparent
> for Calcite and you define how to deal with it in your own UDF.
>
>
>
> [1]:
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_beam_blob_bcdb3ee943b92b856c8f8526528f120c903b8c0e_sdks_java_core_src_main_java_org_apache_beam_sdk_schemas_Schema.java-23L539&d=DwIFaQ&c=V9IgWpI5PvzTw83UyHGVSoW3Uc1MFWe5J8PTfkrzVSo&r=BkW1L6EF7ergAVYDXCo-3Vwkpy6qjsWAz7_GD7pAR8g&m=VrmcHBi5Gv1rW6-vJXNro6_CYvjqPmE2sOcViMYjFkE&s=uwI5RKLBbcjlx25YQP0vDyhmVKo11YVqx3G8KeT1tYc&e=
>
> -Rui
>
> On Wed, Jul 15, 2020 at 3:55 PM Talat Uyarer <tuyarer@paloaltonetworks.com
> >
> wrote:
>
> > Hi Julian,
> >
> > Thanks for your answer. I dont know other dbs but Also Postgresql
> support
> > enum too[1]. Do you think supporting logical types makes more sense ?
> When
> > we define a new type that can be stored in a schema field. Is it
> possible ?
> >
> > Thanks
> >
> > [1]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_11_datatype-2Denum.html&d=DwIFaQ&c=V9IgWpI5PvzTw83UyHGVSoW3Uc1MFWe5J8PTfkrzVSo&r=BkW1L6EF7ergAVYDXCo-3Vwkpy6qjsWAz7_GD7pAR8g&m=VrmcHBi5Gv1rW6-vJXNro6_CYvjqPmE2sOcViMYjFkE&s=ln8psgcVNyBgdMdkycqBXLMGx9faR4v1KQhOvkJ5eg4&e=
> >
> > On Wed, Jul 15, 2020 at 3:09 PM Julian Hyde <jh...@apache.org> wrote:
> >
> > > In answer to your question, no, Calcite does not support ENUM. It
> > > looks as if only MySQL does this.
> > >
> > > One idiomatic SQL way to achieve this would be to define a CHECK
> > > constraint that ensures that a column can only have a given set of
> > > values. Then hopefully a storage system would compress repeated values
> > > to a few bits each.
> > >
> > > Feel free to log a JIRA with your requirements. We'll see if anyone
> > > else wants this, and is prepared to implement it.
> > >
> > > Julian
> > >
> > > On Tue, Jul 14, 2020 at 7:00 PM Talat Uyarer
> > > <tu...@paloaltonetworks.com> wrote:
> > > >
> > > > Hi,
> > > > I am using Beam SQL which uses calcite. IN Beam we have logical types
> > > which
> > > > we can use for Enumeration. But looks like they did not implement
> > > > enumeration support for calcite. I want to give that support. But I
> > could
> > > > not find the right way to implement it. In my Enumeration is a
> > > > HashMap<String,Integer>.
> > > >
> > > > My question is: Does calcite support Enum types like mysql ? [1] if
> not
> > > > How can store metadata information such as (enum strings, beam type
> > name
> > > > etc) for columns ?
> > > >
> > > > Thanks
> > > >
> > > > [1]
> > >
> >
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_enum.html&d=DwIBaQ&c=V9IgWpI5PvzTw83UyHGVSoW3Uc1MFWe5J8PTfkrzVSo&r=BkW1L6EF7ergAVYDXCo-3Vwkpy6qjsWAz7_GD7pAR8g&m=C6H60jgOEboCiMMPoCkttuBvRctAdfSX_bb4fdHteg8&s=CsvfHu0s3sruXHp-cUyo68Yjsne0Bz414FI3_1M6Zas&e=
> > >
> >
>
Re: Calcite and Enum Type
Posted by Rui Wang <am...@apache.org>.
Hi Talat,
I am guessing when you say logical type, you mean something like this in
Beam [1].
My question is why do you need Calcite to support ENUM? If you use logical
type, you can define a ENUM by yourself and the underlying type can be a
Map<string, int>. Map is supported by Calcite. So ENUM will be transparent
for Calcite and you define how to deal with it in your own UDF.
[1]:
https://github.com/apache/beam/blob/bcdb3ee943b92b856c8f8526528f120c903b8c0e/sdks/java/core/src/main/java/org/apache/beam/sdk/schemas/Schema.java#L539
-Rui
On Wed, Jul 15, 2020 at 3:55 PM Talat Uyarer <tu...@paloaltonetworks.com>
wrote:
> Hi Julian,
>
> Thanks for your answer. I dont know other dbs but Also Postgresql support
> enum too[1]. Do you think supporting logical types makes more sense ? When
> we define a new type that can be stored in a schema field. Is it possible ?
>
> Thanks
>
> [1] https://www.postgresql.org/docs/11/datatype-enum.html
>
> On Wed, Jul 15, 2020 at 3:09 PM Julian Hyde <jh...@apache.org> wrote:
>
> > In answer to your question, no, Calcite does not support ENUM. It
> > looks as if only MySQL does this.
> >
> > One idiomatic SQL way to achieve this would be to define a CHECK
> > constraint that ensures that a column can only have a given set of
> > values. Then hopefully a storage system would compress repeated values
> > to a few bits each.
> >
> > Feel free to log a JIRA with your requirements. We'll see if anyone
> > else wants this, and is prepared to implement it.
> >
> > Julian
> >
> > On Tue, Jul 14, 2020 at 7:00 PM Talat Uyarer
> > <tu...@paloaltonetworks.com> wrote:
> > >
> > > Hi,
> > > I am using Beam SQL which uses calcite. IN Beam we have logical types
> > which
> > > we can use for Enumeration. But looks like they did not implement
> > > enumeration support for calcite. I want to give that support. But I
> could
> > > not find the right way to implement it. In my Enumeration is a
> > > HashMap<String,Integer>.
> > >
> > > My question is: Does calcite support Enum types like mysql ? [1] if not
> > > How can store metadata information such as (enum strings, beam type
> name
> > > etc) for columns ?
> > >
> > > Thanks
> > >
> > > [1]
> >
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_enum.html&d=DwIBaQ&c=V9IgWpI5PvzTw83UyHGVSoW3Uc1MFWe5J8PTfkrzVSo&r=BkW1L6EF7ergAVYDXCo-3Vwkpy6qjsWAz7_GD7pAR8g&m=C6H60jgOEboCiMMPoCkttuBvRctAdfSX_bb4fdHteg8&s=CsvfHu0s3sruXHp-cUyo68Yjsne0Bz414FI3_1M6Zas&e=
> >
>
Re: Calcite and Enum Type
Posted by Talat Uyarer <tu...@paloaltonetworks.com>.
Hi Julian,
Thanks for your answer. I dont know other dbs but Also Postgresql support
enum too[1]. Do you think supporting logical types makes more sense ? When
we define a new type that can be stored in a schema field. Is it possible ?
Thanks
[1] https://www.postgresql.org/docs/11/datatype-enum.html
On Wed, Jul 15, 2020 at 3:09 PM Julian Hyde <jh...@apache.org> wrote:
> In answer to your question, no, Calcite does not support ENUM. It
> looks as if only MySQL does this.
>
> One idiomatic SQL way to achieve this would be to define a CHECK
> constraint that ensures that a column can only have a given set of
> values. Then hopefully a storage system would compress repeated values
> to a few bits each.
>
> Feel free to log a JIRA with your requirements. We'll see if anyone
> else wants this, and is prepared to implement it.
>
> Julian
>
> On Tue, Jul 14, 2020 at 7:00 PM Talat Uyarer
> <tu...@paloaltonetworks.com> wrote:
> >
> > Hi,
> > I am using Beam SQL which uses calcite. IN Beam we have logical types
> which
> > we can use for Enumeration. But looks like they did not implement
> > enumeration support for calcite. I want to give that support. But I could
> > not find the right way to implement it. In my Enumeration is a
> > HashMap<String,Integer>.
> >
> > My question is: Does calcite support Enum types like mysql ? [1] if not
> > How can store metadata information such as (enum strings, beam type name
> > etc) for columns ?
> >
> > Thanks
> >
> > [1]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_enum.html&d=DwIBaQ&c=V9IgWpI5PvzTw83UyHGVSoW3Uc1MFWe5J8PTfkrzVSo&r=BkW1L6EF7ergAVYDXCo-3Vwkpy6qjsWAz7_GD7pAR8g&m=C6H60jgOEboCiMMPoCkttuBvRctAdfSX_bb4fdHteg8&s=CsvfHu0s3sruXHp-cUyo68Yjsne0Bz414FI3_1M6Zas&e=
>
Re: Calcite and Enum Type
Posted by Julian Hyde <jh...@apache.org>.
In answer to your question, no, Calcite does not support ENUM. It
looks as if only MySQL does this.
One idiomatic SQL way to achieve this would be to define a CHECK
constraint that ensures that a column can only have a given set of
values. Then hopefully a storage system would compress repeated values
to a few bits each.
Feel free to log a JIRA with your requirements. We'll see if anyone
else wants this, and is prepared to implement it.
Julian
On Tue, Jul 14, 2020 at 7:00 PM Talat Uyarer
<tu...@paloaltonetworks.com> wrote:
>
> Hi,
> I am using Beam SQL which uses calcite. IN Beam we have logical types which
> we can use for Enumeration. But looks like they did not implement
> enumeration support for calcite. I want to give that support. But I could
> not find the right way to implement it. In my Enumeration is a
> HashMap<String,Integer>.
>
> My question is: Does calcite support Enum types like mysql ? [1] if not
> How can store metadata information such as (enum strings, beam type name
> etc) for columns ?
>
> Thanks
>
> [1] https://dev.mysql.com/doc/refman/8.0/en/enum.html