[KYLIN-3996] postgresql can not to be used to construct the flat hive table in NO.1 step in building cube via datasourcedefaultSdk

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: v2.6.1
• Fix Version/s: v3.1.3
• Component/s: Driver - JDBC
• Labels: datasource postgresql sdk
• Environment: kylin3.0.0+hadoop2.7.4+hive2.1.1+hbase1.2.6
• Flags: Important
• Assignee: jimmyz
• Reporter: haijiandu
• Created: 08/May/19 02:40
• Updated: 03/Nov/21 06:14
• Resolved: 03/Nov/21 06:14

Description

postgresql can not to be used to construct the flat hive table in NO.1 step in building cube via datasourcedefaultSdk. whether in kylin.properties or in postgresql.xml，corresponding configureations have been finished as required by official specification.There  alwarys occurs a problem such as "ERROR manager.SqlManager: Error executing statement: org.postgresql.util.PSQLException:
ERROR: syntax error at or near ".", 

when kylin is linked to postgresql databese, it seems that the errors happen in sql brought by kylin. the sql has  back quote,as it is "`" ,that causes query failure brought by table name and columns defined by back quote(`).

as required, in kylin.properties ,its configuration has been as followings

kylin.source.default=16
kylin.source.jdbc.connection-url=jdbc:postgresql://192.168.75.123:5432/dvdrental
kylin.source.jdbc.driver=org.postgresql.Driver
kylin.source.jdbc.dialect=postgresql
kylin.source.jdbc.adaptor=org.apache.kylin.sdk.datasource.adaptor.DefaultAdaptor

kylin.source.jdbc.user=postgres
kylin.source.jdbc.pass=1234
kylin.source.jdbc.sqoop-home=/duhj/sqoop-1.4.7.bin__hadoop-2.6.0
kylin.source.jdbc.filed-delimiter=|
kylin.source.jdbc.sqoop-mapper-num=4

what is more, in ~/apache-kylin-3.0/conf/datasource/postgresql.xml，corresponding conf is as followings:

-<DATASOURCE_DEF DIALECT="postgresql" ID="postgresql" NAME="kylin">

<PROPERTY NAME="sql.default-converted-enabled" VALUE="true"/>

<PROPERTY NAME="sql.allow-no-offset" VALUE="true"/>

<PROPERTY NAME="sql.allow-fetch-no-rows" VALUE="true"/>

<PROPERTY NAME="sql.allow-no-orderby-with-fetch" VALUE="true"/>

<PROPERTY NAME="sql.keyword-default-escape" VALUE="true"/>

<PROPERTY NAME="sql.keyword-default-uppercase" VALUE="true"/>

<PROPERTY NAME="schema.database.black-list-pattern" VALUE="publi.*, edw"/>

<PROPERTY NAME="sql.case-sensitive" VALUE="true"/>

<PROPERTY NAME="metadata.enable-cache" VALUE="true"/>

<PROPERTY NAME="sql.enable-quote-all-identifiers" VALUE="false"/>

is there a method to solve it?

 

Issue Links

• is fixed by: KYLIN-4046 Refine JDBC Source(source.default=8)
• is fixed by: KYLIN-3832 Kylin pushdown to support postgresql