You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "Shaoxiong Zhan (Jira)" <ji...@apache.org> on 2021/07/23 07:46:00 UTC
[jira] [Comment Edited] (KYLIN-3996) postgresql can not to be used
to construct the flat hive table in NO.1 step in building cube via
datasourcedefaultSdk
[ https://issues.apache.org/jira/browse/KYLIN-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17371852#comment-17371852 ]
Shaoxiong Zhan edited comment on KYLIN-3996 at 7/23/21, 7:45 AM:
-----------------------------------------------------------------
Most of the quoted scenarios related to jdbc source had been processed in JIRA https://issues.apache.org/jira/browse/KYLIN-4046 and https://issues.apache.org/jira/browse/KYLIN-3832.
Try to reproduce the problem on the code of the master branch, and found that the problem has been fixed.
The reproduction steps are as follows:
1. Import the postgresql table
2. Create a model, select the table FT as the fact table, and set the date type column of the table as the partition column
3. Create a cube and build, the first step is to build successfully
My configuration is as below:
kylin.source.default=16
kylin.source.hive.quote-enabled=true
kylin.source.jdbc.adaptor=org.apache.kylin.sdk.datasource.adaptor.DefaultAdaptor
kylin.source.jdbc.connection-url=jdbc:postgresql://10.1.2.131:5432/public
kylin.source.jdbc.driver=org.postgresql.Driver
kylin.source.jdbc.dialect=postgresql
kylin.source.jdbc.user=postgres
kylin.source.jdbc.pass=pass
kylin.source.jdbc.sqoop-home=/usr/hdp/current/sqoop-client/
kylin.source.jdbc.filed-delimiter=|
kylin.source.jdbc.sqoop-mapper-num=4
was (Author: zhanshaoxiong):
Most of the quoted scenarios related to jdbc source had been processed in JIRA https://issues.apache.org/jira/browse/KYLIN-4046, but it is found that there are some problems in org.apache.kylin.metadata.model.PartitionDesc.IPartitionConditionBuilder#buildDateRangeCondition. The use of hard code quotes requires similar processing. We need to choose the corresponding quote according to the data source.
such as:
{code:java}
public static class YearMonthDayPartitionConditionBuilder implements IPartitionConditionBuilder {
@Override
public String buildDateRangeCondition(PartitionDesc partDesc, ISegment seg, SegmentRange segRange, Function<TblColRef, String> func) {
long startInclusive = (Long) segRange.start.v;
long endExclusive = (Long) segRange.end.v;
TblColRef partitionColumn = partDesc.getPartitionDateColumnRef();
Preconditions.checkNotNull(partitionColumn);
partitionColumn.setQuotedFunc(func);
String tableAlias = partitionColumn.getTableAlias();
String concatField = String.format(Locale.ROOT, "CONCAT(%s.YEAR,'-',%s.MONTH,'-',%s.DAY)", tableAlias,
tableAlias, tableAlias);
StringBuilder builder = new StringBuilder();
if (startInclusive > 0) {
builder.append(concatField + " >= '" + DateFormat.formatToDateStr(startInclusive) + "' ");
builder.append("AND ");
}
builder.append(concatField + " < '" + DateFormat.formatToDateStr(endExclusive) + "'");
return builder.toString();
}
}
{code}
> postgresql can not to be used to construct the flat hive table in NO.1 step in building cube via datasourcedefaultSdk
> ---------------------------------------------------------------------------------------------------------------------
>
> Key: KYLIN-3996
> URL: https://issues.apache.org/jira/browse/KYLIN-3996
> Project: Kylin
> Issue Type: Bug
> Components: Driver - JDBC
> Affects Versions: v2.6.1
> Environment: kylin3.0.0+hadoop2.7.4+hive2.1.1+hbase1.2.6
> Reporter: haijiandu
> Assignee: Shaoxiong Zhan
> Priority: Critical
> Labels: datasource, postgresql, sdk
> Fix For: v3.1.3
>
> Attachments: kylin_log_error_1.jpg, kylin_log_error_2.jpg, kylin_log_error_3.jpg, kylin_log_error_4.jpg, kylin_log_error_5.jpg, kylin_properties_duhj.jpg, kylin_sql.jpg, kylin_sqoopToflatHiveTable_Error.jpg, postgresql_xml.jpg, server_config_kylin_1.jpg, server_config_kylin_1.jpg, server_config_kylin_2.jpg
>
>
> 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?
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)