From issues-return-18933-archive-asf-public=cust-asf.ponee.io@kylin.apache.org Thu Aug 2 05:39:07 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 698BC180634 for ; Thu, 2 Aug 2018 05:39:06 +0200 (CEST) Received: (qmail 46752 invoked by uid 500); 2 Aug 2018 03:39:05 -0000 Mailing-List: contact issues-help@kylin.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@kylin.apache.org Delivered-To: mailing list issues@kylin.apache.org Received: (qmail 46743 invoked by uid 99); 2 Aug 2018 03:39:05 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Aug 2018 03:39:05 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id E6BC8180A85 for ; Thu, 2 Aug 2018 03:39:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -109.501 X-Spam-Level: X-Spam-Status: No, score=-109.501 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, KAM_ASCII_DIVIDERS=0.8, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id hIvK82XXdTEn for ; Thu, 2 Aug 2018 03:39:03 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id B0FE85F30C for ; Thu, 2 Aug 2018 03:39:02 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 8395EE2636 for ; Thu, 2 Aug 2018 03:39:01 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id C056627768 for ; Thu, 2 Aug 2018 03:39:00 +0000 (UTC) Date: Thu, 2 Aug 2018 03:39:00 +0000 (UTC) From: "ASF GitHub Bot (JIRA)" To: issues@kylin.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (KYLIN-3475) Make calcite case handling and quoting method more configurable. MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/KYLIN-3475?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1656= 6309#comment-16566309 ]=20 ASF GitHub Bot commented on KYLIN-3475: --------------------------------------- shaofengshi closed pull request #180: KYLIN-3475 Make calcite connection is= more configurable URL: https://github.com/apache/kylin/pull/180 =20 =20 =20 This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/core-common/src/main/java/org/apache/kylin/common/KylinConfigB= ase.java b/core-common/src/main/java/org/apache/kylin/common/KylinConfigBas= e.java index b2331e1627..4683ce05fe 100644 --- a/core-common/src/main/java/org/apache/kylin/common/KylinConfigBase.jav= a +++ b/core-common/src/main/java/org/apache/kylin/common/KylinConfigBase.jav= a @@ -1236,6 +1236,16 @@ public boolean isConvertCreateTableToWith() { return Boolean.valueOf(getOptional("kylin.query.convert-create-tab= le-to-with", "false")); } =20 + /** + * Extras calcite properties to config Calcite connection + */ + public Properties getCalciteExtrasProperties() { + Properties properties =3D new Properties(); + Map map =3D getPropertiesByPrefix("kylin.query.cal= cite.extras-props."); + properties.putAll(map); + return properties; + } + /** * Rule is usually singleton as static field, the configuration of thi= s property is like: * RuleClassName1#FieldName1,RuleClassName2#FieldName2,... diff --git a/core-common/src/main/resources/kylin-defaults.properties b/cor= e-common/src/main/resources/kylin-defaults.properties index c01d3a8236..23c0730afa 100644 --- a/core-common/src/main/resources/kylin-defaults.properties +++ b/core-common/src/main/resources/kylin-defaults.properties @@ -225,6 +225,18 @@ kylin.query.max-scan-bytes=3D0 =20 kylin.query.cache-enabled=3Dtrue =20 +# Controls extras properties for Calcite jdbc driver +# all extras properties should undder prefix "kylin.query.calcite.extras-p= rops." +# case sensitive, default: true, to enable case insensitive set it to fals= e +# @see org.apache.calcite.config.CalciteConnectionProperty.CASE_SENSITIVE +kylin.query.calcite.extras-props.caseSensitive=3Dtrue +# how to handle unquoted identity, defualt: TO_UPPER, available options: U= NCHANGED, TO_UPPER, TO_LOWER +# @see org.apache.calcite.config.CalciteConnectionProperty.UNQUOTED_CASING +kylin.query.calcite.extras-props.unquotedCasing=3DTO_UPPER +# quoting method, default: DOUBLE_QUOTE, available options: DOUBLE_QUOTE, = BACK_TICK, BRACKET +# @see org.apache.calcite.config.CalciteConnectionProperty.QUOTING +kylin.query.calcite.extras-props.quoting=3DDOUBLE_QUOTE + # TABLE ACL kylin.query.security.table-acl-enabled=3Dtrue =20 diff --git a/core-common/src/test/java/org/apache/kylin/common/KylinConfigT= est.java b/core-common/src/test/java/org/apache/kylin/common/KylinConfigTes= t.java index 811c574c3a..c9939e7fae 100644 --- a/core-common/src/test/java/org/apache/kylin/common/KylinConfigTest.jav= a +++ b/core-common/src/test/java/org/apache/kylin/common/KylinConfigTest.jav= a @@ -146,4 +146,21 @@ public void testUnexpectedBlackInPro() { String s =3D override.get("test2"); assertEquals("test2", s); } + + @Test + public void testCalciteExtrasProperties() { + KylinConfig conf =3D KylinConfig.getInstanceFromEnv(); + Properties extras =3D conf.getCalciteExtrasProperties(); + assertEquals("true", extras.getProperty("caseSensitive")); + assertEquals("TO_UPPER", extras.getProperty("unquotedCasing")); + assertEquals("DOUBLE_QUOTE", extras.getProperty("quoting")); + + conf.setProperty("kylin.query.calcite.extras-props.caseSensitive",= "false"); + conf.setProperty("kylin.query.calcite.extras-props.unquotedCasing"= , "UNCHANGED"); + conf.setProperty("kylin.query.calcite.extras-props.quoting", "BRAC= KET"); + extras =3D conf.getCalciteExtrasProperties(); + assertEquals("false", extras.getProperty("caseSensitive")); + assertEquals("UNCHANGED", extras.getProperty("unquotedCasing")); + assertEquals("BRACKET", extras.getProperty("quoting")); + } } diff --git a/query/src/main/java/org/apache/kylin/query/QueryConnection.jav= a b/query/src/main/java/org/apache/kylin/query/QueryConnection.java index ed9065447a..b2db178c55 100644 --- a/query/src/main/java/org/apache/kylin/query/QueryConnection.java +++ b/query/src/main/java/org/apache/kylin/query/QueryConnection.java @@ -46,6 +46,7 @@ public static Connection getConnection(String project) th= rows SQLException { } File olapTmp =3D OLAPSchemaFactory.createTempOLAPJson(project, Kyl= inConfig.getInstanceFromEnv()); Properties info =3D new Properties(); + info.putAll(KylinConfig.getInstanceFromEnv().getCalciteExtrasPrope= rties()); info.put("model", olapTmp.getAbsolutePath()); info.put("typeSystem", "org.apache.kylin.query.calcite.KylinRelDat= aTypeSystem"); return DriverManager.getConnection("jdbc:calcite:", info); diff --git a/query/src/test/java/org/apache/kylin/query/QueryConnectionTest= .java b/query/src/test/java/org/apache/kylin/query/QueryConnectionTest.java new file mode 100644 index 0000000000..e0bdf223c8 --- /dev/null +++ b/query/src/test/java/org/apache/kylin/query/QueryConnectionTest.java @@ -0,0 +1,77 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied= . + * See the License for the specific language governing permissions and + * limitations under the License. +*/ + +package org.apache.kylin.query; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Map; + +import org.apache.calcite.avatica.InternalProperty; +import org.apache.calcite.avatica.util.Casing; +import org.apache.calcite.avatica.util.Quoting; +import org.apache.calcite.jdbc.CalciteConnection; +import org.apache.commons.lang3.reflect.FieldUtils; +import org.apache.kylin.common.HotLoadKylinPropertiesTestCase; +import org.apache.kylin.common.KylinConfig; +import org.junit.Test; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertTrue; + +public class QueryConnectionTest extends HotLoadKylinPropertiesTestCase { + + private static final String SQL_WITH_MIXED_CASE =3D "select 1 as value= _ALIAS"; + + @Test + public void testGetConnection() throws SQLException, IllegalAccessExce= ption { + Connection connection =3D QueryConnection.getConnection("default")= ; + assertNotNull(connection); + Map properties =3D dirtyReadProperties(c= onnection); + assertEquals(true, properties.get(InternalProperty.CASE_SENSITIVE)= ); + assertEquals(Casing.TO_UPPER, properties.get(InternalProperty.UNQU= OTED_CASING)); + assertEquals(Quoting.DOUBLE_QUOTE, properties.get(InternalProperty= .QUOTING)); + + ResultSet resultSet =3D connection.prepareStatement(SQL_WITH_MIXED= _CASE).executeQuery(); + String columnName =3D resultSet.getMetaData().getColumnName(1); + assertEquals("VALUE_ALIAS", columnName); + + // override connection properties + KylinConfig conf =3D KylinConfig.getInstanceFromEnv(); + conf.setProperty("kylin.query.calcite.extras-props.caseSensitive",= "false"); + conf.setProperty("kylin.query.calcite.extras-props.unquotedCasing"= , "UNCHANGED"); + conf.setProperty("kylin.query.calcite.extras-props.quoting", "BRAC= KET"); + Connection conn2 =3D QueryConnection.getConnection("default"); + Map props =3D dirtyReadProperties(conn2)= ; + assertEquals(false, props.get(InternalProperty.CASE_SENSITIVE)); + assertEquals(Casing.UNCHANGED, props.get(InternalProperty.UNQUOTED= _CASING)); + assertEquals(Quoting.BRACKET, props.get(InternalProperty.QUOTING))= ; + + ResultSet resultSet1 =3D conn2.prepareStatement(SQL_WITH_MIXED_CAS= E).executeQuery(); + assertEquals("value_ALIAS", resultSet1.getMetaData().getColumnName= (1)); + } + + @SuppressWarnings("unchecked") + private static Map dirtyReadProperties(Conne= ction connection) throws IllegalAccessException { + assertTrue(connection instanceof CalciteConnection); + return (Map) FieldUtils.readField(connec= tion, "properties"); + + } +} =20 ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. =20 For queries about this service, please contact Infrastructure at: users@infra.apache.org > Make calcite case handling and quoting method more configurable. > ---------------------------------------------------------------- > > Key: KYLIN-3475 > URL: https://issues.apache.org/jira/browse/KYLIN-3475 > Project: Kylin > Issue Type: Improvement > Components: Query Engine > Reporter: Ian Hu > Assignee: Ian Hu > Priority: Major > Fix For: v2.5.0 > > > >> Do you know whether Calcite supports that? If Calcite already supports= it with some parameter, Kylin can expose that as a configuration. Yes, Cal= cite 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 achiev= e this [https://github.com/ian4hu/kylin/commit/88ee638fb4c8b55b089294107ad3= 61ec3fc6dae9]=C2=A0=C2=A0For short lookup see QueryConnection.getConnection= (project)@ [https://github.com/apache/kylin/blob/25b300c1f654561e561565ebf3= 5c4e0e93ae0a04/query/src/main/java/org/apache/kylin/query/QueryConnection.j= ava#L51]=C2=A0=C2=A0the key is the info variable. > =C2=A0 > Background (from mailing list [http://mail-archives.apache.org/mod_mbox/k= ylin-dev//201807.mbox/%3C4247fe2a.634d.164e995a707.Coremail.hu2008yinxiang%= 40163.com%3E]=C2=A0): > We use kylin in our BI system as a sub-query engine. we met some problems= in our use case like bellow. > # Our visual design tool use source table schema as metadata, and the so= urce table column is lower case or mixed case, and visual tool generate SQL= for kylin query. > # Kylin execute sql and return result > # 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 k= ylin. like "select 1 as value_ALIAS" we always get the column named "VALUE_= ALIAS" instead of "value_ALIAS" from Kylin. > =C2=A0 > 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. > =C2=A0 > I would like contribute features to make the query engine is more flexibl= e > # First of all, make the case handling and quoting method is configurabl= e by kylin.properties, that a global configuration. > # Second make case handling and quoting method available by JDBC connect= ion url parameters or connection properties by DriverManager.getConnection(= url-with-parameters, extra-properties) > =C2=A0 -- This message was sent by Atlassian JIRA (v7.6.3#76005)