You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 胡继续 <hu...@163.com> on 2018/07/30 05:07:36 UTC

[Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Hello guys


We use kylin in our BI system as a sub-query engine. we met some problems in our use case like bellow.
1. Our visual design tool use source table schema as metadata, and the source table column is lower case or mixed case, and visual tool generate SQL for kylin query.
2. Kylin execute sql and return result
3. we use a parser to parse result and assembly it to entities (like ORM engine did)


but the problem is that kylin now always casting the column to UPPER case, that break our parser and whe can not change that behavior by configure kylin.
like "select 1 as value_ALIAS" we always get the column named "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.


although we can use 'select 1 as "value_ALIAS"' (with double-qouted alias) and it will be better if we can configure the quoting method.


I would like contribute features to make the query engine is more flexible
1. First of all, make the case handling and quoting method is configurable by kylin.properties, that a global configuration.
2. Second make case handling and quoting method available by JDBC connection url parameters or connection properties by DriverManager.getConnection(url-with-parameters, extra-properties)


Are the features above is matter to contribute? Please give me feedback and I will make a contribution.

Re:Re: Re: Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by 胡继续 <hu...@163.com>.
Jira issue created: https://issues.apache.org/jira/browse/KYLIN-3475
and the pr is here https://github.com/apache/kylin/pull/180 







在 2018-07-30 17:03:50，"ShaoFeng Shi" <sh...@apache.org> 写道：
>Awesome! Could you please create a JIRA with this feature, and then make a
>PR? Thanks!
>
>2018-07-30 16:21 GMT+08:00 胡继续 <hu...@163.com>:
>
>> >> Do you know whether Calcite supports that? If Calcite already supports
>> it
>> with some parameter, Kylin can expose that as a configuration.
>>
>> Yes, Calcite support to customize some sql handle feature by connection
>> properties.
>> We can customize it by provide an Properties when use jdbc's
>> DriverManager.getConnection(url, extraProperties).
>>
>>
>> Here is my commit (with UT) to achieve this https://github.com/ian4hu/
>> kylin/commit/88ee638fb4c8b55b089294107ad361ec3fc6dae9
>> For short lookup see QueryConnection.getConnection(project)@
>> https://github.com/apache/kylin/blob/25b300c1f654561e561565ebf35c4e
>> 0e93ae0a04/query/src/main/java/org/apache/kylin/query/
>> QueryConnection.java#L51
>> the key is the info variable.
>>
>>
>>
>>
>>
>>
>>
>>
>> 在 2018-07-30 15:41:50，"ShaoFeng Shi" <sh...@apache.org> 写道：
>> >Do you know whether Calcite supports that? If Calcite already supports it
>> >with some parameter, Kylin can expose that as a configuration.
>> >
>> >BTW, which ORM framework are you running with Kylin? Can it generate SQL
>> >with double quoted? As I know some commercial BI tool like Tableau,
>> >MicroStrategy, they generate very standard SQL: every table and column are
>> >quoted with "".
>> >
>> >2018-07-30 13:47 GMT+08:00 胡继续 <hu...@163.com>:
>> >
>> >> Hi Shaofeng
>> >>
>> >>
>> >> As I mentioned, if our use a ORM framework that not mantainced by
>> >> ourselves, if there is an option for us to adjust the kylin parser
>> >> behavior, it would be better.
>> >>
>> >>
>> >> Truely the behavior is more attached to  Apache Calcite, but Kylin is
>> not
>> >> allow us to customize it.
>> >>
>> >>
>> >> I think we should give the choice for users.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> At 2018-07-30 13:34:14, "ShaoFeng Shi" <sh...@apache.org> wrote:
>> >> >Hi Hu,
>> >> >
>> >> >We welcome community contributions, but I'm not sure whether this
>> behavior
>> >> >is in Kylin or not. I think it is in Apache Calcite, which is the SQL
>> >> >parser of Kylin. (I don't remember Kylin has the logic to change the
>> >> column
>> >> >name)
>> >> >
>> >> >Usually, the column/table name in the database is case-insensitive;
>> Column
>> >> >"Col1" and "COL1" are the same. The application should aware that.
>> >> Besides,
>> >> >as you mentioned, if you use double-quote for the column name, it will
>> >> keep
>> >> >the original case in the result set; This is the recommended way I
>> think.
>> >> >
>> >> >So this sounds like an issue already has the solution. Just my
>> comments;
>> >> >Please share more insights if you have.
>> >> >
>> >> >2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:
>> >> >
>> >> >> Hello guys
>> >> >>
>> >> >>
>> >> >> We use kylin in our BI system as a sub-query engine. we met some
>> >> problems
>> >> >> in our use case like bellow.
>> >> >> 1. Our visual design tool use source table schema as metadata, and
>> the
>> >> >> source table column is lower case or mixed case, and visual tool
>> >> generate
>> >> >> SQL for kylin query.
>> >> >> 2. Kylin execute sql and return result
>> >> >> 3. we use a parser to parse result and assembly it to entities (like
>> ORM
>> >> >> engine did)
>> >> >>
>> >> >>
>> >> >> but the problem is that kylin now always casting the column to UPPER
>> >> case,
>> >> >> that break our parser and whe can not change that behavior by
>> configure
>> >> >> kylin.
>> >> >> like "select 1 as value_ALIAS" we always get the column named
>> >> >> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
>> >> >>
>> >> >>
>> >> >> although we can use 'select 1 as "value_ALIAS"' (with double-qouted
>> >> alias)
>> >> >> and it will be better if we can configure the quoting method.
>> >> >>
>> >> >>
>> >> >> I would like contribute features to make the query engine is more
>> >> flexible
>> >> >> 1. First of all, make the case handling and quoting method is
>> >> configurable
>> >> >> by kylin.properties, that a global configuration.
>> >> >> 2. Second make case handling and quoting method available by JDBC
>> >> >> connection url parameters or connection properties by
>> >> >> DriverManager.getConnection(url-with-parameters, extra-properties)
>> >> >>
>> >> >>
>> >> >> Are the features above is matter to contribute? Please give me
>> feedback
>> >> >> and I will make a contribution.
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >--
>> >> >Best regards,
>> >> >
>> >> >Shaofeng Shi 史少锋
>> >>
>> >
>> >
>> >
>> >--
>> >Best regards,
>> >
>> >Shaofeng Shi 史少锋
>>
>
>
>
>-- 
>Best regards,
>
>Shaofeng Shi 史少锋

Re: Re: Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by ShaoFeng Shi <sh...@apache.org>.
Awesome! Could you please create a JIRA with this feature, and then make a
PR? Thanks!

2018-07-30 16:21 GMT+08:00 胡继续 <hu...@163.com>:

> >> Do you know whether Calcite supports that? If Calcite already supports
> it
> with some parameter, Kylin can expose that as a configuration.
>
> Yes, Calcite support to customize some sql handle feature by connection
> properties.
> We can customize it by provide an Properties when use jdbc's
> DriverManager.getConnection(url, extraProperties).
>
>
> Here is my commit (with UT) to achieve this https://github.com/ian4hu/
> kylin/commit/88ee638fb4c8b55b089294107ad361ec3fc6dae9
> For short lookup see QueryConnection.getConnection(project)@
> https://github.com/apache/kylin/blob/25b300c1f654561e561565ebf35c4e
> 0e93ae0a04/query/src/main/java/org/apache/kylin/query/
> QueryConnection.java#L51
> the key is the info variable.
>
>
>
>
>
>
>
>
> 在 2018-07-30 15:41:50，"ShaoFeng Shi" <sh...@apache.org> 写道：
> >Do you know whether Calcite supports that? If Calcite already supports it
> >with some parameter, Kylin can expose that as a configuration.
> >
> >BTW, which ORM framework are you running with Kylin? Can it generate SQL
> >with double quoted? As I know some commercial BI tool like Tableau,
> >MicroStrategy, they generate very standard SQL: every table and column are
> >quoted with "".
> >
> >2018-07-30 13:47 GMT+08:00 胡继续 <hu...@163.com>:
> >
> >> Hi Shaofeng
> >>
> >>
> >> As I mentioned, if our use a ORM framework that not mantainced by
> >> ourselves, if there is an option for us to adjust the kylin parser
> >> behavior, it would be better.
> >>
> >>
> >> Truely the behavior is more attached to  Apache Calcite, but Kylin is
> not
> >> allow us to customize it.
> >>
> >>
> >> I think we should give the choice for users.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> At 2018-07-30 13:34:14, "ShaoFeng Shi" <sh...@apache.org> wrote:
> >> >Hi Hu,
> >> >
> >> >We welcome community contributions, but I'm not sure whether this
> behavior
> >> >is in Kylin or not. I think it is in Apache Calcite, which is the SQL
> >> >parser of Kylin. (I don't remember Kylin has the logic to change the
> >> column
> >> >name)
> >> >
> >> >Usually, the column/table name in the database is case-insensitive;
> Column
> >> >"Col1" and "COL1" are the same. The application should aware that.
> >> Besides,
> >> >as you mentioned, if you use double-quote for the column name, it will
> >> keep
> >> >the original case in the result set; This is the recommended way I
> think.
> >> >
> >> >So this sounds like an issue already has the solution. Just my
> comments;
> >> >Please share more insights if you have.
> >> >
> >> >2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:
> >> >
> >> >> Hello guys
> >> >>
> >> >>
> >> >> We use kylin in our BI system as a sub-query engine. we met some
> >> problems
> >> >> in our use case like bellow.
> >> >> 1. Our visual design tool use source table schema as metadata, and
> the
> >> >> source table column is lower case or mixed case, and visual tool
> >> generate
> >> >> SQL for kylin query.
> >> >> 2. Kylin execute sql and return result
> >> >> 3. we use a parser to parse result and assembly it to entities (like
> ORM
> >> >> engine did)
> >> >>
> >> >>
> >> >> but the problem is that kylin now always casting the column to UPPER
> >> case,
> >> >> that break our parser and whe can not change that behavior by
> configure
> >> >> kylin.
> >> >> like "select 1 as value_ALIAS" we always get the column named
> >> >> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
> >> >>
> >> >>
> >> >> although we can use 'select 1 as "value_ALIAS"' (with double-qouted
> >> alias)
> >> >> and it will be better if we can configure the quoting method.
> >> >>
> >> >>
> >> >> I would like contribute features to make the query engine is more
> >> flexible
> >> >> 1. First of all, make the case handling and quoting method is
> >> configurable
> >> >> by kylin.properties, that a global configuration.
> >> >> 2. Second make case handling and quoting method available by JDBC
> >> >> connection url parameters or connection properties by
> >> >> DriverManager.getConnection(url-with-parameters, extra-properties)
> >> >>
> >> >>
> >> >> Are the features above is matter to contribute? Please give me
> feedback
> >> >> and I will make a contribution.
> >> >
> >> >
> >> >
> >> >
> >> >--
> >> >Best regards,
> >> >
> >> >Shaofeng Shi 史少锋
> >>
> >
> >
> >
> >--
> >Best regards,
> >
> >Shaofeng Shi 史少锋
>



-- 
Best regards,

Shaofeng Shi 史少锋

Re:Re: Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by 胡继续 <hu...@163.com>.
>> Do you know whether Calcite supports that? If Calcite already supports it
with some parameter, Kylin can expose that as a configuration.

Yes, Calcite support to customize some sql handle feature by connection properties.
We can customize it by provide an Properties when use jdbc's DriverManager.getConnection(url, extraProperties).


Here is my commit (with UT) to achieve this https://github.com/ian4hu/kylin/commit/88ee638fb4c8b55b089294107ad361ec3fc6dae9
For short lookup see QueryConnection.getConnection(project)@ https://github.com/apache/kylin/blob/25b300c1f654561e561565ebf35c4e0e93ae0a04/query/src/main/java/org/apache/kylin/query/QueryConnection.java#L51
the key is the info variable.








在 2018-07-30 15:41:50，"ShaoFeng Shi" <sh...@apache.org> 写道：
>Do you know whether Calcite supports that? If Calcite already supports it
>with some parameter, Kylin can expose that as a configuration.
>
>BTW, which ORM framework are you running with Kylin? Can it generate SQL
>with double quoted? As I know some commercial BI tool like Tableau,
>MicroStrategy, they generate very standard SQL: every table and column are
>quoted with "".
>
>2018-07-30 13:47 GMT+08:00 胡继续 <hu...@163.com>:
>
>> Hi Shaofeng
>>
>>
>> As I mentioned, if our use a ORM framework that not mantainced by
>> ourselves, if there is an option for us to adjust the kylin parser
>> behavior, it would be better.
>>
>>
>> Truely the behavior is more attached to  Apache Calcite, but Kylin is not
>> allow us to customize it.
>>
>>
>> I think we should give the choice for users.
>>
>>
>>
>>
>>
>>
>>
>>
>> At 2018-07-30 13:34:14, "ShaoFeng Shi" <sh...@apache.org> wrote:
>> >Hi Hu,
>> >
>> >We welcome community contributions, but I'm not sure whether this behavior
>> >is in Kylin or not. I think it is in Apache Calcite, which is the SQL
>> >parser of Kylin. (I don't remember Kylin has the logic to change the
>> column
>> >name)
>> >
>> >Usually, the column/table name in the database is case-insensitive; Column
>> >"Col1" and "COL1" are the same. The application should aware that.
>> Besides,
>> >as you mentioned, if you use double-quote for the column name, it will
>> keep
>> >the original case in the result set; This is the recommended way I think.
>> >
>> >So this sounds like an issue already has the solution. Just my comments;
>> >Please share more insights if you have.
>> >
>> >2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:
>> >
>> >> Hello guys
>> >>
>> >>
>> >> We use kylin in our BI system as a sub-query engine. we met some
>> problems
>> >> in our use case like bellow.
>> >> 1. Our visual design tool use source table schema as metadata, and the
>> >> source table column is lower case or mixed case, and visual tool
>> generate
>> >> SQL for kylin query.
>> >> 2. Kylin execute sql and return result
>> >> 3. we use a parser to parse result and assembly it to entities (like ORM
>> >> engine did)
>> >>
>> >>
>> >> but the problem is that kylin now always casting the column to UPPER
>> case,
>> >> that break our parser and whe can not change that behavior by configure
>> >> kylin.
>> >> like "select 1 as value_ALIAS" we always get the column named
>> >> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
>> >>
>> >>
>> >> although we can use 'select 1 as "value_ALIAS"' (with double-qouted
>> alias)
>> >> and it will be better if we can configure the quoting method.
>> >>
>> >>
>> >> I would like contribute features to make the query engine is more
>> flexible
>> >> 1. First of all, make the case handling and quoting method is
>> configurable
>> >> by kylin.properties, that a global configuration.
>> >> 2. Second make case handling and quoting method available by JDBC
>> >> connection url parameters or connection properties by
>> >> DriverManager.getConnection(url-with-parameters, extra-properties)
>> >>
>> >>
>> >> Are the features above is matter to contribute? Please give me feedback
>> >> and I will make a contribution.
>> >
>> >
>> >
>> >
>> >--
>> >Best regards,
>> >
>> >Shaofeng Shi 史少锋
>>
>
>
>
>-- 
>Best regards,
>
>Shaofeng Shi 史少锋

Re: Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by ShaoFeng Shi <sh...@apache.org>.
Do you know whether Calcite supports that? If Calcite already supports it
with some parameter, Kylin can expose that as a configuration.

BTW, which ORM framework are you running with Kylin? Can it generate SQL
with double quoted? As I know some commercial BI tool like Tableau,
MicroStrategy, they generate very standard SQL: every table and column are
quoted with "".

2018-07-30 13:47 GMT+08:00 胡继续 <hu...@163.com>:

> Hi Shaofeng
>
>
> As I mentioned, if our use a ORM framework that not mantainced by
> ourselves, if there is an option for us to adjust the kylin parser
> behavior, it would be better.
>
>
> Truely the behavior is more attached to  Apache Calcite, but Kylin is not
> allow us to customize it.
>
>
> I think we should give the choice for users.
>
>
>
>
>
>
>
>
> At 2018-07-30 13:34:14, "ShaoFeng Shi" <sh...@apache.org> wrote:
> >Hi Hu,
> >
> >We welcome community contributions, but I'm not sure whether this behavior
> >is in Kylin or not. I think it is in Apache Calcite, which is the SQL
> >parser of Kylin. (I don't remember Kylin has the logic to change the
> column
> >name)
> >
> >Usually, the column/table name in the database is case-insensitive; Column
> >"Col1" and "COL1" are the same. The application should aware that.
> Besides,
> >as you mentioned, if you use double-quote for the column name, it will
> keep
> >the original case in the result set; This is the recommended way I think.
> >
> >So this sounds like an issue already has the solution. Just my comments;
> >Please share more insights if you have.
> >
> >2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:
> >
> >> Hello guys
> >>
> >>
> >> We use kylin in our BI system as a sub-query engine. we met some
> problems
> >> in our use case like bellow.
> >> 1. Our visual design tool use source table schema as metadata, and the
> >> source table column is lower case or mixed case, and visual tool
> generate
> >> SQL for kylin query.
> >> 2. Kylin execute sql and return result
> >> 3. we use a parser to parse result and assembly it to entities (like ORM
> >> engine did)
> >>
> >>
> >> but the problem is that kylin now always casting the column to UPPER
> case,
> >> that break our parser and whe can not change that behavior by configure
> >> kylin.
> >> like "select 1 as value_ALIAS" we always get the column named
> >> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
> >>
> >>
> >> although we can use 'select 1 as "value_ALIAS"' (with double-qouted
> alias)
> >> and it will be better if we can configure the quoting method.
> >>
> >>
> >> I would like contribute features to make the query engine is more
> flexible
> >> 1. First of all, make the case handling and quoting method is
> configurable
> >> by kylin.properties, that a global configuration.
> >> 2. Second make case handling and quoting method available by JDBC
> >> connection url parameters or connection properties by
> >> DriverManager.getConnection(url-with-parameters, extra-properties)
> >>
> >>
> >> Are the features above is matter to contribute? Please give me feedback
> >> and I will make a contribution.
> >
> >
> >
> >
> >--
> >Best regards,
> >
> >Shaofeng Shi 史少锋
>



-- 
Best regards,

Shaofeng Shi 史少锋

Re:Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by 胡继续 <hu...@163.com>.
Hi Shaofeng


As I mentioned, if our use a ORM framework that not mantainced by ourselves, if there is an option for us to adjust the kylin parser behavior, it would be better.


Truely the behavior is more attached to  Apache Calcite, but Kylin is not allow us to customize it.


I think we should give the choice for users.








At 2018-07-30 13:34:14, "ShaoFeng Shi" <sh...@apache.org> wrote:
>Hi Hu,
>
>We welcome community contributions, but I'm not sure whether this behavior
>is in Kylin or not. I think it is in Apache Calcite, which is the SQL
>parser of Kylin. (I don't remember Kylin has the logic to change the column
>name)
>
>Usually, the column/table name in the database is case-insensitive; Column
>"Col1" and "COL1" are the same. The application should aware that. Besides,
>as you mentioned, if you use double-quote for the column name, it will keep
>the original case in the result set; This is the recommended way I think.
>
>So this sounds like an issue already has the solution. Just my comments;
>Please share more insights if you have.
>
>2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:
>
>> Hello guys
>>
>>
>> We use kylin in our BI system as a sub-query engine. we met some problems
>> in our use case like bellow.
>> 1. Our visual design tool use source table schema as metadata, and the
>> source table column is lower case or mixed case, and visual tool generate
>> SQL for kylin query.
>> 2. Kylin execute sql and return result
>> 3. we use a parser to parse result and assembly it to entities (like ORM
>> engine did)
>>
>>
>> but the problem is that kylin now always casting the column to UPPER case,
>> that break our parser and whe can not change that behavior by configure
>> kylin.
>> like "select 1 as value_ALIAS" we always get the column named
>> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
>>
>>
>> although we can use 'select 1 as "value_ALIAS"' (with double-qouted alias)
>> and it will be better if we can configure the quoting method.
>>
>>
>> I would like contribute features to make the query engine is more flexible
>> 1. First of all, make the case handling and quoting method is configurable
>> by kylin.properties, that a global configuration.
>> 2. Second make case handling and quoting method available by JDBC
>> connection url parameters or connection properties by
>> DriverManager.getConnection(url-with-parameters, extra-properties)
>>
>>
>> Are the features above is matter to contribute? Please give me feedback
>> and I will make a contribution.
>
>
>
>
>-- 
>Best regards,
>
>Shaofeng Shi 史少锋

Re: [Feature][Query]Case insensitive and configurable casing handle feature, may I contribute it?

Posted by ShaoFeng Shi <sh...@apache.org>.
Hi Hu,

We welcome community contributions, but I'm not sure whether this behavior
is in Kylin or not. I think it is in Apache Calcite, which is the SQL
parser of Kylin. (I don't remember Kylin has the logic to change the column
name)

Usually, the column/table name in the database is case-insensitive; Column
"Col1" and "COL1" are the same. The application should aware that. Besides,
as you mentioned, if you use double-quote for the column name, it will keep
the original case in the result set; This is the recommended way I think.

So this sounds like an issue already has the solution. Just my comments;
Please share more insights if you have.

2018-07-30 13:07 GMT+08:00 胡继续 <hu...@163.com>:

> Hello guys
>
>
> We use kylin in our BI system as a sub-query engine. we met some problems
> in our use case like bellow.
> 1. Our visual design tool use source table schema as metadata, and the
> source table column is lower case or mixed case, and visual tool generate
> SQL for kylin query.
> 2. Kylin execute sql and return result
> 3. we use a parser to parse result and assembly it to entities (like ORM
> engine did)
>
>
> but the problem is that kylin now always casting the column to UPPER case,
> that break our parser and whe can not change that behavior by configure
> kylin.
> like "select 1 as value_ALIAS" we always get the column named
> "VALUE_ALIAS" instead of "value_ALIAS" from Kylin.
>
>
> although we can use 'select 1 as "value_ALIAS"' (with double-qouted alias)
> and it will be better if we can configure the quoting method.
>
>
> I would like contribute features to make the query engine is more flexible
> 1. First of all, make the case handling and quoting method is configurable
> by kylin.properties, that a global configuration.
> 2. Second make case handling and quoting method available by JDBC
> connection url parameters or connection properties by
> DriverManager.getConnection(url-with-parameters, extra-properties)
>
>
> Are the features above is matter to contribute? Please give me feedback
> and I will make a contribution.




-- 
Best regards,

Shaofeng Shi 史少锋