You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 高铭潮 <ca...@icloud.com.INVALID> on 2019/06/10 09:02:53 UTC
Build cube by JDBC
Hi, all
When I build the cube by Oracle JDBC. There is something error. Like this error message:
java.io.IOException: OS command error exit with return code: 1, error message: Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hadoop/hadoop-3.1.2/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hbase/hbase-2.0.5/lib/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-06-10 15:57:06,965 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
2019-06-10 15:57:06,996 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
2019-06-10 15:57:07,088 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
2019-06-10 15:57:07,106 INFO manager.SqlManager: Using default fetchSize of 1000
2019-06-10 15:57:07,106 INFO tool.CodeGenTool: Beginning code generation
2019-06-10 15:57:07,587 INFO manager.SqlManager: Executing SQL statement: SELECT `WFPROCESSINST`.`PROCESSINSTID` as `WFPROCESSINST_PROCESSINSTID` ,`WFPROCESSINST`.`PROCESSINSTNAME` as `WFPROCESSINST_PROCESSINSTNAME` ,`WFPROCESSINST`.`CREATOR` as `WFPROCESSINST_CREATOR` ,`WFPROCESSINST`.`OWNER` as `WFPROCESSINST_OWNER` ,`WFPROCESSINST`.`RELATEDATA` as `WFPROCESSINST_RELATEDATA` ,`WFPROCESSINST`.`STARTTIME` as `WFPROCESSINST_STARTTIME` ,`WFPROCESSINST`.`ENDTIME` as `WFPROCESSINST_ENDTIME` ,`WFPROCESSINST`.`FINALTIME` as `WFPROCESSINST_FINALTIME` ,`WFPROCESSINST`.`REMINDTIME` as `WFPROCESSINST_REMINDTIME` ,`WFPROCESSINST`.`CURRENTSTATE` as `WFPROCESSINST_CURRENTSTATE` ,`WFPROCESSINST`.`PARENTACTID` as `WFPROCESSINST_PARENTACTID` FROM `SIE_EMS`.`WFPROCESSINST` as `WFPROCESSINST` INNER JOIN `SIE_EMS`.`SMBP_PROCESSINSTBIZRELA` as `SMBP_PROCESSINSTBIZRELA` ON `WFPROCESSINST`.`PROCESSINSTID` = `SMBP_PROCESSINSTBIZRELA`.`PROCESSINSTID` INNER JOIN `SIE_EMS`.`WFWORKITEM` as `WFWORKITEM` ON `WFPROCESSINST`.`PROCESSINSTID` = `WFWORKITEM`.`PROCESSINSTID` WHERE 1=1 AND (`WFPROCESSINST`.CREATETIME >= '2016-01-01 00:00:00' AND `WFPROCESSINST`.CREATETIME < '2019-06-10 00:00:00') AND (1 = 0)
2019-06-10 15:57:07,623 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:253)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:336)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
2019-06-10 15:57:07,625 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1677)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
The command is:
/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.driver.OracleDriver --username sie_ems --password "emstest" --query "SELECT \`WFPROCESSINST\`.\`PROCESSINSTID\` as \`WFPROCESSINST_PROCESSINSTID\` ,\`WFPROCESSINST\`.\`PROCESSINSTNAME\` as \`WFPROCESSINST_PROCESSINSTNAME\` ,\`WFPROCESSINST\`.\`CREATOR\` as \`WFPROCESSINST_CREATOR\` ,\`WFPROCESSINST\`.\`OWNER\` as \`WFPROCESSINST_OWNER\` ,\`WFPROCESSINST\`.\`RELATEDATA\` as \`WFPROCESSINST_RELATEDATA\` ,\`WFPROCESSINST\`.\`STARTTIME\` as \`WFPROCESSINST_STARTTIME\` ,\`WFPROCESSINST\`.\`ENDTIME\` as \`WFPROCESSINST_ENDTIME\` ,\`WFPROCESSINST\`.\`FINALTIME\` as \`WFPROCESSINST_FINALTIME\` ,\`WFPROCESSINST\`.\`REMINDTIME\` as \`WFPROCESSINST_REMINDTIME\` ,\`WFPROCESSINST\`.\`CURRENTSTATE\` as \`WFPROCESSINST_CURRENTSTATE\` ,\`WFPROCESSINST\`.\`PARENTACTID\` as \`WFPROCESSINST_PARENTACTID\` FROM \`SIE_EMS\`.\`WFPROCESSINST\` as \`WFPROCESSINST\` INNER JOIN \`SIE_EMS\`.\`SMBP_PROCESSINSTBIZRELA\` as \`SMBP_PROCESSINSTBIZRELA\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`SMBP_PROCESSINSTBIZRELA\`.\`PROCESSINSTID\` INNER JOIN \`SIE_EMS\`.\`WFWORKITEM\` as \`WFWORKITEM\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`WFWORKITEM\`.\`PROCESSINSTID\` WHERE 1=1 AND (\`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00') AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-e6a7a9af-d6e4-6eac-646f-3174308bdee4/kylin_intermediate_ems_data_cube_1a4aa387_a2e6_9a2c_7d56_66d21c06451b --split-by \`WFPROCESSINST\`.\`CREATETIME\` --boundary-query "SELECT min(\`WFPROCESSINST\`.\`CREATETIME\`), max(\`WFPROCESSINST\`.\`CREATETIME\`) FROM SIE_EMS.WFPROCESSINST as WFPROCESSINST WHERE \`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00'" --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '|' --num-mappers 4
at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
at org.apache.kylin.source.jdbc.CmdStep.sqoopFlatHiveTable(CmdStep.java:50)
at org.apache.kylin.source.jdbc.CmdStep.doWork(CmdStep.java:61)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
But I take a test by MYSQL. It was successed. The sqoop can connect the oracle db.
Please tell me something about this error.
Re: Build cube by JDBC
Posted by 张 佑铖 <ed...@hotmail.com>.
Hello,
Actually the package kap-datasource-adaptor-oracle-11g-1.0.0-SNAPSHOT.jar you download from Kyligence is adapt to Enterprise version, not adapt to Kylin now.
Return to your first question:
ORA-00911 may caused by the backtick `
You may try your sql without backtick.
Thanks,
Youcheng
在 2019/6/14 下午7:04，“高铭潮”<ca...@icloud.com.INVALID> 写入:
Hi,
Thanks for your reply. Today I got the oracle jdbc plug-in from kyligence. The kap-datasource-adaptor-oracle-11g-1.0.0-SNAPSHOT.jar. When I use this jar to load data from oracle. Throws another exception.
Following is my configuration:
kylin.source.default=16
kylin.source.jdbc.connection-url=jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2
kylin.source.jdbc.driver=oracle.jdbc.OracleDriver
kylin.source.jdbc.dialect=oracle11g
kylin.source.jdbc.adaptor=io.kyligence.kap.sdk.datasource.adaptor.Oracle11gAdaptor
#kylin.source.jdbc.adaptor=org.apache.kylin.sdk.datasource.adaptor.DefaultAdaptor
kylin.source.jdbc.user=sie_ems
kylin.source.jdbc.pass=emstest
kylin.source.jdbc.sqoop-home=/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0
kylin.source.jdbc.filed-delimiter=|
kylin.source.jdbc.sqoop-mapper-num=4
The exception is:
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:178)
... 22 more
The command is:
/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.OracleDriver --username sie_ems --password emstest --query "SELECT T_ORDERS.ID T_ORDERS_ID, T_ORDERS.ORDER_CODE T_ORDERS_ORDER_CODE, T_ORDERS.CUSTOMER_NAME T_ORDERS_CUSTOMER_NAME, T_ORDERS.ORDER_STATUS T_ORDERS_ORDER_STATUS, T_ORDERS.O_AREA T_ORDERS_O_AREA, T_ORDERS.ORDER_ADDRESS T_ORDERS_ORDER_ADDRESS, T_ORDERS.ORDER_PRICE T_ORDERS_ORDER_PRICE
FROM SIE_EMS.T_ORDERS T_ORDERS
LEFT JOIN SIE_EMS.T_ORDER_PRODUCT_LIST T_ORDER_PRODUCT_LIST ON T_ORDERS.ID = T_ORDER_PRODUCT_LIST.ORDER_ID
WHERE 1 = 1 AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9/kylin_intermediate_ems_test_cube_066e42c0_9f91_19e4_3c22_a5fd217ac636 --split-by T_ORDERS.ID --boundary-query "SELECT MIN(T_ORDERS.ID), MAX(T_ORDERS.ID)
FROM \"sie_ems\".T_ORDERS T_ORDERS" --null-string '' --fields-terminated-by '|' --num-mappers 4
at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
at org.apache.kylin.source.jdbc.sqoop.SqoopCmdStep.sqoopFlatHiveTable(SqoopCmdStep.java:59)
at org.apache.kylin.source.jdbc.sqoop.SqoopCmdStep.doWork(SqoopCmdStep.java:70)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2019-06-14 18:01:29,131 INFO [Scheduler 1852146613 Job 7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9-87] execution.ExecutableManager:471 : job id:7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9-00 from RUNNING to ERROR
This exception is caused by boundary-query "SELECT MIN(T_ORDERS.ID), MAX(T_ORDERS.ID) FROM \"sie_ems\".T_ORDERS T_ORDERS”!
When I Try to used \“SIE_EMS\” then this sql run successful
So I looking at the source code from kylin-source-jdbc. In class org.apache.kylin.source.jdbc.extensible.JdbcHiveInputBase I see this :
splitDatabase = splitColRef.getColumnDesc().getTable().getDatabase().toLowerCase(Locale.ROOT);
I think that’s the problem. In Oracle database only the lowercase letters need to be enclosed in double quotation marks. But my schema was uppercase
> 在 2019年6月14日，18:07，Xiaoxiang Yu <xi...@kyligence.io> 写道：
>
> Hi,
> Sorry for my late reply. I have reproduced that error, using oracle 11g as data source.
> First, I met the error " java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符". This can be fixed by adding a configuration in kylin.properties, that is " kylin.source.hive.quote-enabled=false".
> After restart Kylin process, I met another error " java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束", and I found this exception is caused by "AS" in from clause. I will create a JIRA and fixed it later, you may wait for next release.
>
> Following is my configuration:
> kylin.source.default=8
> kylin.source.jdbc.connection-url=jdbc:oracle:thin:@hdp30-qa:49161/XE
> kylin.source.jdbc.driver=oracle.jdbc.driver.OracleDriver
> kylin.source.jdbc.dialect=oracle
> kylin.source.jdbc.user=system
> kylin.source.jdbc.pass=oracle
> kylin.source.jdbc.sqoop-home=/opt/cloudera/parcels/CDH/lib/sqoop
> kylin.source.jdbc.filed-delimiter=|
> kylin.source.hive.quote-enabled=false
>
> If you have any suggestion or find any mistake, please let me know, thank you very much.
>
> ----------------
> Best wishes,
> Xiaoxiang Yu
>
>
> 在 2019/6/10 17:03，“高铭潮”<ca...@icloud.com.INVALID> 写入:
>
> Hi, all
>
> When I build the cube by Oracle JDBC. There is something error. Like this error message:
>
> java.io.IOException: OS command error exit with return code: 1, error message: Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail.
> Please set $HCAT_HOME to the root of your HCatalog installation.
> Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail.
> Please set $ACCUMULO_HOME to the root of your Accumulo installation.
> Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../zookeeper does not exist! Accumulo imports will fail.
> Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hadoop/hadoop-3.1.2/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hbase/hbase-2.0.5/lib/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-06-10 15:57:06,965 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
> 2019-06-10 15:57:06,996 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
> 2019-06-10 15:57:07,088 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
> 2019-06-10 15:57:07,106 INFO manager.SqlManager: Using default fetchSize of 1000
> 2019-06-10 15:57:07,106 INFO tool.CodeGenTool: Beginning code generation
> 2019-06-10 15:57:07,587 INFO manager.SqlManager: Executing SQL statement: SELECT `WFPROCESSINST`.`PROCESSINSTID` as `WFPROCESSINST_PROCESSINSTID` ,`WFPROCESSINST`.`PROCESSINSTNAME` as `WFPROCESSINST_PROCESSINSTNAME` ,`WFPROCESSINST`.`CREATOR` as `WFPROCESSINST_CREATOR` ,`WFPROCESSINST`.`OWNER` as `WFPROCESSINST_OWNER` ,`WFPROCESSINST`.`RELATEDATA` as `WFPROCESSINST_RELATEDATA` ,`WFPROCESSINST`.`STARTTIME` as `WFPROCESSINST_STARTTIME` ,`WFPROCESSINST`.`ENDTIME` as `WFPROCESSINST_ENDTIME` ,`WFPROCESSINST`.`FINALTIME` as `WFPROCESSINST_FINALTIME` ,`WFPROCESSINST`.`REMINDTIME` as `WFPROCESSINST_REMINDTIME` ,`WFPROCESSINST`.`CURRENTSTATE` as `WFPROCESSINST_CURRENTSTATE` ,`WFPROCESSINST`.`PARENTACTID` as `WFPROCESSINST_PARENTACTID` FROM `SIE_EMS`.`WFPROCESSINST` as `WFPROCESSINST` INNER JOIN `SIE_EMS`.`SMBP_PROCESSINSTBIZRELA` as `SMBP_PROCESSINSTBIZRELA` ON `WFPROCESSINST`.`PROCESSINSTID` = `SMBP_PROCESSINSTBIZRELA`.`PROCESSINSTID` INNER JOIN `SIE_EMS`.`WFWORKITEM` as `WFWORKITEM` ON `WFPROCESSINST`.`PROCESSINSTID` = `WFWORKITEM`.`PROCESSINSTID` WHERE 1=1 AND (`WFPROCESSINST`.CREATETIME >= '2016-01-01 00:00:00' AND `WFPROCESSINST`.CREATETIME < '2019-06-10 00:00:00') AND (1 = 0)
> 2019-06-10 15:57:07,623 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
>
> java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
>
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
> at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
> at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
> at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:253)
> at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:336)
> at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
> 2019-06-10 15:57:07,625 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1677)
> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
>
> The command is:
> /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.driver.OracleDriver --username sie_ems --password "emstest" --query "SELECT \`WFPROCESSINST\`.\`PROCESSINSTID\` as \`WFPROCESSINST_PROCESSINSTID\` ,\`WFPROCESSINST\`.\`PROCESSINSTNAME\` as \`WFPROCESSINST_PROCESSINSTNAME\` ,\`WFPROCESSINST\`.\`CREATOR\` as \`WFPROCESSINST_CREATOR\` ,\`WFPROCESSINST\`.\`OWNER\` as \`WFPROCESSINST_OWNER\` ,\`WFPROCESSINST\`.\`RELATEDATA\` as \`WFPROCESSINST_RELATEDATA\` ,\`WFPROCESSINST\`.\`STARTTIME\` as \`WFPROCESSINST_STARTTIME\` ,\`WFPROCESSINST\`.\`ENDTIME\` as \`WFPROCESSINST_ENDTIME\` ,\`WFPROCESSINST\`.\`FINALTIME\` as \`WFPROCESSINST_FINALTIME\` ,\`WFPROCESSINST\`.\`REMINDTIME\` as \`WFPROCESSINST_REMINDTIME\` ,\`WFPROCESSINST\`.\`CURRENTSTATE\` as \`WFPROCESSINST_CURRENTSTATE\` ,\`WFPROCESSINST\`.\`PARENTACTID\` as \`WFPROCESSINST_PARENTACTID\` FROM \`SIE_EMS\`.\`WFPROCESSINST\` as \`WFPROCESSINST\` INNER JOIN \`SIE_EMS\`.\`SMBP_PROCESSINSTBIZRELA\` as \`SMBP_PROCESSINSTBIZRELA\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`SMBP_PROCESSINSTBIZRELA\`.\`PROCESSINSTID\` INNER JOIN \`SIE_EMS\`.\`WFWORKITEM\` as \`WFWORKITEM\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`WFWORKITEM\`.\`PROCESSINSTID\` WHERE 1=1 AND (\`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00') AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-e6a7a9af-d6e4-6eac-646f-3174308bdee4/kylin_intermediate_ems_data_cube_1a4aa387_a2e6_9a2c_7d56_66d21c06451b --split-by \`WFPROCESSINST\`.\`CREATETIME\` --boundary-query "SELECT min(\`WFPROCESSINST\`.\`CREATETIME\`), max(\`WFPROCESSINST\`.\`CREATETIME\`) FROM SIE_EMS.WFPROCESSINST as WFPROCESSINST WHERE \`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00'" --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '|' --num-mappers 4
> at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
> at org.apache.kylin.source.jdbc.CmdStep.sqoopFlatHiveTable(CmdStep.java:50)
> at org.apache.kylin.source.jdbc.CmdStep.doWork(CmdStep.java:61)
> at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
> at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
> at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
> at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
>
> But I take a test by MYSQL. It was successed. The sqoop can connect the oracle db.
>
> Please tell me something about this error.
>
Re: Build cube by JDBC
Posted by 高铭潮 <ca...@icloud.com.INVALID>.
Hi,
Thanks for your reply. Today I got the oracle jdbc plug-in from kyligence. The kap-datasource-adaptor-oracle-11g-1.0.0-SNAPSHOT.jar. When I use this jar to load data from oracle. Throws another exception.
Following is my configuration:
kylin.source.default=16
kylin.source.jdbc.connection-url=jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2
kylin.source.jdbc.driver=oracle.jdbc.OracleDriver
kylin.source.jdbc.dialect=oracle11g
kylin.source.jdbc.adaptor=io.kyligence.kap.sdk.datasource.adaptor.Oracle11gAdaptor
#kylin.source.jdbc.adaptor=org.apache.kylin.sdk.datasource.adaptor.DefaultAdaptor
kylin.source.jdbc.user=sie_ems
kylin.source.jdbc.pass=emstest
kylin.source.jdbc.sqoop-home=/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0
kylin.source.jdbc.filed-delimiter=|
kylin.source.jdbc.sqoop-mapper-num=4
The exception is:
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:178)
... 22 more
The command is:
/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.OracleDriver --username sie_ems --password emstest --query "SELECT T_ORDERS.ID T_ORDERS_ID, T_ORDERS.ORDER_CODE T_ORDERS_ORDER_CODE, T_ORDERS.CUSTOMER_NAME T_ORDERS_CUSTOMER_NAME, T_ORDERS.ORDER_STATUS T_ORDERS_ORDER_STATUS, T_ORDERS.O_AREA T_ORDERS_O_AREA, T_ORDERS.ORDER_ADDRESS T_ORDERS_ORDER_ADDRESS, T_ORDERS.ORDER_PRICE T_ORDERS_ORDER_PRICE
FROM SIE_EMS.T_ORDERS T_ORDERS
LEFT JOIN SIE_EMS.T_ORDER_PRODUCT_LIST T_ORDER_PRODUCT_LIST ON T_ORDERS.ID = T_ORDER_PRODUCT_LIST.ORDER_ID
WHERE 1 = 1 AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9/kylin_intermediate_ems_test_cube_066e42c0_9f91_19e4_3c22_a5fd217ac636 --split-by T_ORDERS.ID --boundary-query "SELECT MIN(T_ORDERS.ID), MAX(T_ORDERS.ID)
FROM \"sie_ems\".T_ORDERS T_ORDERS" --null-string '' --fields-terminated-by '|' --num-mappers 4
at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
at org.apache.kylin.source.jdbc.sqoop.SqoopCmdStep.sqoopFlatHiveTable(SqoopCmdStep.java:59)
at org.apache.kylin.source.jdbc.sqoop.SqoopCmdStep.doWork(SqoopCmdStep.java:70)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2019-06-14 18:01:29,131 INFO [Scheduler 1852146613 Job 7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9-87] execution.ExecutableManager:471 : job id:7517bf62-4f2a-8fcf-8bd1-74d9c0bf2fd9-00 from RUNNING to ERROR
This exception is caused by boundary-query "SELECT MIN(T_ORDERS.ID), MAX(T_ORDERS.ID) FROM \"sie_ems\".T_ORDERS T_ORDERS”!
When I Try to used \“SIE_EMS\” then this sql run successful
So I looking at the source code from kylin-source-jdbc. In class org.apache.kylin.source.jdbc.extensible.JdbcHiveInputBase I see this :
splitDatabase = splitColRef.getColumnDesc().getTable().getDatabase().toLowerCase(Locale.ROOT);
I think that’s the problem. In Oracle database only the lowercase letters need to be enclosed in double quotation marks. But my schema was uppercase
> 在 2019年6月14日，18:07，Xiaoxiang Yu <xi...@kyligence.io> 写道：
>
> Hi,
> Sorry for my late reply. I have reproduced that error, using oracle 11g as data source.
> First, I met the error " java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符". This can be fixed by adding a configuration in kylin.properties, that is " kylin.source.hive.quote-enabled=false".
> After restart Kylin process, I met another error " java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束", and I found this exception is caused by "AS" in from clause. I will create a JIRA and fixed it later, you may wait for next release.
>
> Following is my configuration:
> kylin.source.default=8
> kylin.source.jdbc.connection-url=jdbc:oracle:thin:@hdp30-qa:49161/XE
> kylin.source.jdbc.driver=oracle.jdbc.driver.OracleDriver
> kylin.source.jdbc.dialect=oracle
> kylin.source.jdbc.user=system
> kylin.source.jdbc.pass=oracle
> kylin.source.jdbc.sqoop-home=/opt/cloudera/parcels/CDH/lib/sqoop
> kylin.source.jdbc.filed-delimiter=|
> kylin.source.hive.quote-enabled=false
>
> If you have any suggestion or find any mistake, please let me know, thank you very much.
>
> ----------------
> Best wishes,
> Xiaoxiang Yu
>
>
> 在 2019/6/10 17:03，“高铭潮”<ca...@icloud.com.INVALID> 写入:
>
> Hi, all
>
> When I build the cube by Oracle JDBC. There is something error. Like this error message:
>
> java.io.IOException: OS command error exit with return code: 1, error message: Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail.
> Please set $HCAT_HOME to the root of your HCatalog installation.
> Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail.
> Please set $ACCUMULO_HOME to the root of your Accumulo installation.
> Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../zookeeper does not exist! Accumulo imports will fail.
> Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hadoop/hadoop-3.1.2/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hbase/hbase-2.0.5/lib/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-06-10 15:57:06,965 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
> 2019-06-10 15:57:06,996 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
> 2019-06-10 15:57:07,088 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
> 2019-06-10 15:57:07,106 INFO manager.SqlManager: Using default fetchSize of 1000
> 2019-06-10 15:57:07,106 INFO tool.CodeGenTool: Beginning code generation
> 2019-06-10 15:57:07,587 INFO manager.SqlManager: Executing SQL statement: SELECT `WFPROCESSINST`.`PROCESSINSTID` as `WFPROCESSINST_PROCESSINSTID` ,`WFPROCESSINST`.`PROCESSINSTNAME` as `WFPROCESSINST_PROCESSINSTNAME` ,`WFPROCESSINST`.`CREATOR` as `WFPROCESSINST_CREATOR` ,`WFPROCESSINST`.`OWNER` as `WFPROCESSINST_OWNER` ,`WFPROCESSINST`.`RELATEDATA` as `WFPROCESSINST_RELATEDATA` ,`WFPROCESSINST`.`STARTTIME` as `WFPROCESSINST_STARTTIME` ,`WFPROCESSINST`.`ENDTIME` as `WFPROCESSINST_ENDTIME` ,`WFPROCESSINST`.`FINALTIME` as `WFPROCESSINST_FINALTIME` ,`WFPROCESSINST`.`REMINDTIME` as `WFPROCESSINST_REMINDTIME` ,`WFPROCESSINST`.`CURRENTSTATE` as `WFPROCESSINST_CURRENTSTATE` ,`WFPROCESSINST`.`PARENTACTID` as `WFPROCESSINST_PARENTACTID` FROM `SIE_EMS`.`WFPROCESSINST` as `WFPROCESSINST` INNER JOIN `SIE_EMS`.`SMBP_PROCESSINSTBIZRELA` as `SMBP_PROCESSINSTBIZRELA` ON `WFPROCESSINST`.`PROCESSINSTID` = `SMBP_PROCESSINSTBIZRELA`.`PROCESSINSTID` INNER JOIN `SIE_EMS`.`WFWORKITEM` as `WFWORKITEM` ON `WFPROCESSINST`.`PROCESSINSTID` = `WFWORKITEM`.`PROCESSINSTID` WHERE 1=1 AND (`WFPROCESSINST`.CREATETIME >= '2016-01-01 00:00:00' AND `WFPROCESSINST`.CREATETIME < '2019-06-10 00:00:00') AND (1 = 0)
> 2019-06-10 15:57:07,623 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
>
> java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
>
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
> at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
> at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
> at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:253)
> at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:336)
> at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
> 2019-06-10 15:57:07,625 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1677)
> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
>
> The command is:
> /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.driver.OracleDriver --username sie_ems --password "emstest" --query "SELECT \`WFPROCESSINST\`.\`PROCESSINSTID\` as \`WFPROCESSINST_PROCESSINSTID\` ,\`WFPROCESSINST\`.\`PROCESSINSTNAME\` as \`WFPROCESSINST_PROCESSINSTNAME\` ,\`WFPROCESSINST\`.\`CREATOR\` as \`WFPROCESSINST_CREATOR\` ,\`WFPROCESSINST\`.\`OWNER\` as \`WFPROCESSINST_OWNER\` ,\`WFPROCESSINST\`.\`RELATEDATA\` as \`WFPROCESSINST_RELATEDATA\` ,\`WFPROCESSINST\`.\`STARTTIME\` as \`WFPROCESSINST_STARTTIME\` ,\`WFPROCESSINST\`.\`ENDTIME\` as \`WFPROCESSINST_ENDTIME\` ,\`WFPROCESSINST\`.\`FINALTIME\` as \`WFPROCESSINST_FINALTIME\` ,\`WFPROCESSINST\`.\`REMINDTIME\` as \`WFPROCESSINST_REMINDTIME\` ,\`WFPROCESSINST\`.\`CURRENTSTATE\` as \`WFPROCESSINST_CURRENTSTATE\` ,\`WFPROCESSINST\`.\`PARENTACTID\` as \`WFPROCESSINST_PARENTACTID\` FROM \`SIE_EMS\`.\`WFPROCESSINST\` as \`WFPROCESSINST\` INNER JOIN \`SIE_EMS\`.\`SMBP_PROCESSINSTBIZRELA\` as \`SMBP_PROCESSINSTBIZRELA\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`SMBP_PROCESSINSTBIZRELA\`.\`PROCESSINSTID\` INNER JOIN \`SIE_EMS\`.\`WFWORKITEM\` as \`WFWORKITEM\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`WFWORKITEM\`.\`PROCESSINSTID\` WHERE 1=1 AND (\`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00') AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-e6a7a9af-d6e4-6eac-646f-3174308bdee4/kylin_intermediate_ems_data_cube_1a4aa387_a2e6_9a2c_7d56_66d21c06451b --split-by \`WFPROCESSINST\`.\`CREATETIME\` --boundary-query "SELECT min(\`WFPROCESSINST\`.\`CREATETIME\`), max(\`WFPROCESSINST\`.\`CREATETIME\`) FROM SIE_EMS.WFPROCESSINST as WFPROCESSINST WHERE \`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00'" --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '|' --num-mappers 4
> at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
> at org.apache.kylin.source.jdbc.CmdStep.sqoopFlatHiveTable(CmdStep.java:50)
> at org.apache.kylin.source.jdbc.CmdStep.doWork(CmdStep.java:61)
> at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
> at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
> at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
> at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
>
> But I take a test by MYSQL. It was successed. The sqoop can connect the oracle db.
>
> Please tell me something about this error.
>
Re: Build cube by JDBC
Posted by Xiaoxiang Yu <xi...@kyligence.io>.
Hi,
Sorry for my late reply. I have reproduced that error, using oracle 11g as data source.
First, I met the error " java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符". This can be fixed by adding a configuration in kylin.properties, that is " kylin.source.hive.quote-enabled=false".
After restart Kylin process, I met another error " java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束", and I found this exception is caused by "AS" in from clause. I will create a JIRA and fixed it later, you may wait for next release.
Following is my configuration:
kylin.source.default=8
kylin.source.jdbc.connection-url=jdbc:oracle:thin:@hdp30-qa:49161/XE
kylin.source.jdbc.driver=oracle.jdbc.driver.OracleDriver
kylin.source.jdbc.dialect=oracle
kylin.source.jdbc.user=system
kylin.source.jdbc.pass=oracle
kylin.source.jdbc.sqoop-home=/opt/cloudera/parcels/CDH/lib/sqoop
kylin.source.jdbc.filed-delimiter=|
kylin.source.hive.quote-enabled=false
If you have any suggestion or find any mistake, please let me know, thank you very much.
----------------
Best wishes,
Xiaoxiang Yu
在 2019/6/10 17:03，“高铭潮”<ca...@icloud.com.INVALID> 写入:
Hi, all
When I build the cube by Oracle JDBC. There is something error. Like this error message:
java.io.IOException: OS command error exit with return code: 1, error message: Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hadoop/hadoop-3.1.2/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/Users/gmc/Technology/hbase/hbase-2.0.5/lib/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-06-10 15:57:06,965 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
2019-06-10 15:57:06,996 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
2019-06-10 15:57:07,088 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
2019-06-10 15:57:07,106 INFO manager.SqlManager: Using default fetchSize of 1000
2019-06-10 15:57:07,106 INFO tool.CodeGenTool: Beginning code generation
2019-06-10 15:57:07,587 INFO manager.SqlManager: Executing SQL statement: SELECT `WFPROCESSINST`.`PROCESSINSTID` as `WFPROCESSINST_PROCESSINSTID` ,`WFPROCESSINST`.`PROCESSINSTNAME` as `WFPROCESSINST_PROCESSINSTNAME` ,`WFPROCESSINST`.`CREATOR` as `WFPROCESSINST_CREATOR` ,`WFPROCESSINST`.`OWNER` as `WFPROCESSINST_OWNER` ,`WFPROCESSINST`.`RELATEDATA` as `WFPROCESSINST_RELATEDATA` ,`WFPROCESSINST`.`STARTTIME` as `WFPROCESSINST_STARTTIME` ,`WFPROCESSINST`.`ENDTIME` as `WFPROCESSINST_ENDTIME` ,`WFPROCESSINST`.`FINALTIME` as `WFPROCESSINST_FINALTIME` ,`WFPROCESSINST`.`REMINDTIME` as `WFPROCESSINST_REMINDTIME` ,`WFPROCESSINST`.`CURRENTSTATE` as `WFPROCESSINST_CURRENTSTATE` ,`WFPROCESSINST`.`PARENTACTID` as `WFPROCESSINST_PARENTACTID` FROM `SIE_EMS`.`WFPROCESSINST` as `WFPROCESSINST` INNER JOIN `SIE_EMS`.`SMBP_PROCESSINSTBIZRELA` as `SMBP_PROCESSINSTBIZRELA` ON `WFPROCESSINST`.`PROCESSINSTID` = `SMBP_PROCESSINSTBIZRELA`.`PROCESSINSTID` INNER JOIN `SIE_EMS`.`WFWORKITEM` as `WFWORKITEM` ON `WFPROCESSINST`.`PROCESSINSTID` = `WFWORKITEM`.`PROCESSINSTID` WHERE 1=1 AND (`WFPROCESSINST`.CREATETIME >= '2016-01-01 00:00:00' AND `WFPROCESSINST`.CREATETIME < '2019-06-10 00:00:00') AND (1 = 0)
2019-06-10 15:57:07,623 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:253)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:336)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
2019-06-10 15:57:07,625 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1677)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
The command is:
/Users/gmc/Technology/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true -Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@192.168.175.182:1521:emsxinyi2" --driver oracle.jdbc.driver.OracleDriver --username sie_ems --password "emstest" --query "SELECT \`WFPROCESSINST\`.\`PROCESSINSTID\` as \`WFPROCESSINST_PROCESSINSTID\` ,\`WFPROCESSINST\`.\`PROCESSINSTNAME\` as \`WFPROCESSINST_PROCESSINSTNAME\` ,\`WFPROCESSINST\`.\`CREATOR\` as \`WFPROCESSINST_CREATOR\` ,\`WFPROCESSINST\`.\`OWNER\` as \`WFPROCESSINST_OWNER\` ,\`WFPROCESSINST\`.\`RELATEDATA\` as \`WFPROCESSINST_RELATEDATA\` ,\`WFPROCESSINST\`.\`STARTTIME\` as \`WFPROCESSINST_STARTTIME\` ,\`WFPROCESSINST\`.\`ENDTIME\` as \`WFPROCESSINST_ENDTIME\` ,\`WFPROCESSINST\`.\`FINALTIME\` as \`WFPROCESSINST_FINALTIME\` ,\`WFPROCESSINST\`.\`REMINDTIME\` as \`WFPROCESSINST_REMINDTIME\` ,\`WFPROCESSINST\`.\`CURRENTSTATE\` as \`WFPROCESSINST_CURRENTSTATE\` ,\`WFPROCESSINST\`.\`PARENTACTID\` as \`WFPROCESSINST_PARENTACTID\` FROM \`SIE_EMS\`.\`WFPROCESSINST\` as \`WFPROCESSINST\` INNER JOIN \`SIE_EMS\`.\`SMBP_PROCESSINSTBIZRELA\` as \`SMBP_PROCESSINSTBIZRELA\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`SMBP_PROCESSINSTBIZRELA\`.\`PROCESSINSTID\` INNER JOIN \`SIE_EMS\`.\`WFWORKITEM\` as \`WFWORKITEM\` ON \`WFPROCESSINST\`.\`PROCESSINSTID\` = \`WFWORKITEM\`.\`PROCESSINSTID\` WHERE 1=1 AND (\`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00') AND \$CONDITIONS" --target-dir hdfs://localhost:9000/kylin/kylin_metadata/kylin-e6a7a9af-d6e4-6eac-646f-3174308bdee4/kylin_intermediate_ems_data_cube_1a4aa387_a2e6_9a2c_7d56_66d21c06451b --split-by \`WFPROCESSINST\`.\`CREATETIME\` --boundary-query "SELECT min(\`WFPROCESSINST\`.\`CREATETIME\`), max(\`WFPROCESSINST\`.\`CREATETIME\`) FROM SIE_EMS.WFPROCESSINST as WFPROCESSINST WHERE \`WFPROCESSINST\`.CREATETIME >= '2016-01-01 00:00:00' AND \`WFPROCESSINST\`.CREATETIME < '2019-06-10 00:00:00'" --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '|' --num-mappers 4
at org.apache.kylin.common.util.CliCommandExecutor.execute(CliCommandExecutor.java:96)
at org.apache.kylin.source.jdbc.CmdStep.sqoopFlatHiveTable(CmdStep.java:50)
at org.apache.kylin.source.jdbc.CmdStep.doWork(CmdStep.java:61)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.execution.DefaultChainedExecutable.doWork(DefaultChainedExecutable.java:71)
at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:167)
at org.apache.kylin.job.impl.threadpool.DefaultScheduler$JobRunner.run(DefaultScheduler.java:114)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
But I take a test by MYSQL. It was successed. The sqoop can connect the oracle db.
Please tell me something about this error.