You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "Andrey Molotov (Jira)" <ji...@apache.org> on 2019/10/08 10:46:00 UTC

[jira] [Commented] (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=16946699#comment-16946699 ] 

Andrey Molotov commented on KYLIN-3996:
---------------------------------------

Hello! I have the same issue with Kylin + PostgreSQL. My configuration in kylin.properties and in postgresql.xml are quite the same as [~duhj2], also I've set k{color:#172b4d}ylin.source.{color}default to {color:#172b4d}16. But if I try to build cube I got an error connected with the backtick sign "`", which is invalid in postgresql. It's also worth noting that the backtick appears only around datetime column, which is set up as a required step before starting Build cube job, other column names are framed with accepted double quotes.{color}

{color:#172b4d}Please, help me find a solution.{color}

{color:#172b4d}Here is an extract from log:{color}

{color:#172b4d}java.io.IOException: OS command error exit with return code: 1, error message: Error: Could not find or load main class org.apache.hadoop.hbase.util.GetJavaProperty
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hbase/lib/client-facing-thirdparty/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2019-10-08 11:23:02,350 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
2019-10-08 11:23:02,461 INFO manager.SqlManager: Using default fetchSize of 1000
2019-10-08 11:23:02,461 INFO tool.CodeGenTool: Beginning code generation
2019-10-08 11:23:02,607 INFO manager.SqlManager: Executing SQL statement: SELECT "installations"."install_datetime", "installations"."device_id" AS "INSTALLATIONS_DEVICE_ID"
FROM "app_metrica"."installations" AS "installations"
WHERE 1 = 1 AND ("installations"."install_datetime" >= '2019-01-01 00:00:00' AND "installations"."install_datetime" < '2019-01-04 00:00:00') AND (1 = 0) 
2019-10-08 11:23:02,617 INFO manager.SqlManager: Executing SQL statement: SELECT "installations"."install_datetime", "installations"."device_id" AS "INSTALLATIONS_DEVICE_ID"
FROM "app_metrica"."installations" AS "installations"
WHERE 1 = 1 AND ("installations"."install_datetime" >= '2019-01-01 00:00:00' AND "installations"."install_datetime" < '2019-01-04 00:00:00') AND (1 = 0) 
2019-10-08 11:23:02,641 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop
Note: /tmp/sqoop-hadoop/compile/217aa66a9e2b167022b03fba15d3ceaf/QueryResult.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
2019-10-08 11:23:03,923 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/217aa66a9e2b167022b03fba15d3ceaf/QueryResult.jar
2019-10-08 11:23:03,938 INFO mapreduce.ImportJobBase: Beginning query import.
2019-10-08 11:23:03,939 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address
2019-10-08 11:23:04,022 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
2019-10-08 11:23:04,702 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
2019-10-08 11:23:04,766 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
2019-10-08 11:23:05,011 INFO mapreduce.JobResourceUploader: Disabling Erasure Coding for path: /tmp/hadoop-yarn/staging/hadoop/.staging/job_1570173659742_0011
2019-10-08 11:23:08,115 INFO db.DBInputFormat: Using read commited transaction isolation
2019-10-08 11:23:08,116 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN("installations"."install_datetime"), MAX("installations"."install_datetime")
FROM "app_metrica"."installations" AS "installations" WHERE `INSTALLATIONS`.`INSTALL_DATETIME` >= '2019-01-01 00:00:00' AND `INSTALLATIONS`.`INSTALL_DATETIME` < '2019-01-04 00:00:00'
2019-10-08 11:23:08,123 INFO mapreduce.JobSubmitter: Cleaning up the staging area /tmp/hadoop-yarn/staging/hadoop/.staging/job_1570173659742_0011
2019-10-08 11:23:08,127 ERROR tool.ImportTool: Import failed: java.io.IOException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."{color}

 

 

> 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: Xiaoxiang Yu
>            Priority: Critical
>              Labels: datasource, postgresql, sdk
>         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)