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. 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
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 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. > > 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
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 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 > 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. > > > > 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
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. 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 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 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 > > 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. > > > > > > 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
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 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. 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 > > 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 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 > > > 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. > > > > > > > > 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= > > > > > >
