Uploaded image for project: 'Kylin'
  1. Kylin
  2. KYLIN-4245

SqlConverter returns wrong syntax SQL when SqlParser fails for JDBC source

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: v2.6.4
    • Fix Version/s: None
    • Component/s: Others
    • Labels:
      None

      Description

      Last year ( 28 Nov 2018) a change have been made to SqlConverter, that replace backtick to double qoute when converting sql. sqlWriter is responsible to format it back to backtick after parsing.

      However, if something failed in during parsing or formating, the SQL (backtick replaced with double qoute) will be returned instead. This will cause failure in following Sqoop execution.

      Information:

      SQL of Cube:

      SELECT
      `COM_BASIC_INFO`.`COM_NAME` as `COM_BASIC_INFO_COM_NAME`
      ,`COM_BASIC_INFO`.`COM_ENAME` as `COM_BASIC_INFO_COM_ENAME`
      ,`COM_BASIC_INFO`.`SNAME` as `COM_BASIC_INFO_SNAME`
      ,`COM_BASIC_INFO`.`FIN_REPO_TYPE` as `COM_BASIC_INFO_FIN_REPO_TYPE`
       FROM `COM_DS`.`COM_BASIC_INFO` as `COM_BASIC_INFO`
      WHERE 1=1
      

      SQL Submitted to Sqoop (Exactly the one after replace backtick with double qoute):

      SELECT "COM_BASIC_INFO"."COM_NAME" as "COM_BASIC_INFO_COM_NAME" ,"COM_BASIC_INFO"."COM_ENAME" as "COM_BASIC_INFO_COM_ENAME" ,"COM_BASIC_INFO"."SNAME" as "COM_BASIC_INFO_SNAME" ,"COM_BASIC_INFO"."FIN_REPO_TYPE" as "COM_BASIC_INFO_FIN_REPO_TYPE"  FROM "COM_DS"."COM_BASIC_INFO" as "COM_BASIC_INFO" WHERE 1=1 AND  (1 = 0)
      

      Error Logs:

      2019-11-08 15:17:49,609 ERROR [http-bio-7070-exec-29] conv.SqlConverter:60 : Failed to default convert sql, will use the input: SELECT `COM_BASIC_INFO`.`COM_NAME` as `COM_BASIC_INFO_COM_NAME` ,`COM_BASIC_INFO`.`COM_ENAME` as `COM_BASIC_INFO_COM_ENAME` ,`COM_BASIC_INFO`.`SNAME` as `COM_BASIC_INFO_SNAME` ,`COM_BASIC_INFO`.`FIN_REPO_TYPE` as `COM_BASIC_INFO_FIN_REPO_TYPE`  FROM `COM_DS`.`COM_BASIC_INFO` as `COM_BASIC_INFO` WHERE 1=1
      java.lang.IllegalStateException: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'dbuser'@'db.ip.addr.1' for table 'tablename'
      	at org.apache.kylin.sdk.datasource.adaptor.DefaultAdaptor.fixIdentifierCaseSensitve(DefaultAdaptor.java:277)
      	at org.apache.kylin.sdk.datasource.framework.conv.DefaultConfiguer.fixIdentifierCaseSensitve(DefaultConfiguer.java:131)
      	at org.apache.kylin.sdk.datasource.framework.conv.ConvSqlWriter.identifier(ConvSqlWriter.java:145)
      	at org.apache.calcite.sql.SqlIdentifier.unparse(SqlIdentifier.java:298)
      	at org.apache.calcite.sql.SqlAsOperator.unparse(SqlAsOperator.java:76)
      	at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:333)
      	at org.apache.calcite.sql.dialect.MysqlSqlDialect.unparseCall(MysqlSqlDialect.java:155)
      	at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:103)
      	at org.apache.calcite.sql.SqlNodeList.commaList(SqlNodeList.java:121)
      	at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:349)
      	at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:338)
      	at org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:152)
      	at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:237)
      	at org.apache.calcite.sql.pretty.SqlPrettyWriter.format(SqlPrettyWriter.java:806)
      	at org.apache.kylin.sdk.datasource.framework.conv.SqlConverter.convertSql(SqlConverter.java:58)
      	at org.apache.kylin.sdk.datasource.framework.JdbcConnector.convertSql(JdbcConnector.java:84)
      	at org.apache.kylin.source.jdbc.extensible.JdbcHiveInputBase$JDBCBaseBatchCubingInputSide.createSqoopToFlatHiveStep(JdbcHiveInputBase.java:79)
      	at org.apache.kylin.source.jdbc.JdbcHiveInputBase$JdbcBaseBatchCubingInputSide.addStepPhase1_DoCreateFlatTable(JdbcHiveInputBase.java:65)
      	at org.apache.kylin.source.hive.HiveInputBase$BaseBatchCubingInputSide.addStepPhase1_CreateFlatTable(HiveInputBase.java:83)
      	at org.apache.kylin.engine.mr.BatchCubingJobBuilder2.build(BatchCubingJobBuilder2.java:59)
      	at org.apache.kylin.engine.mr.MRBatchCubingEngine2.createBatchCubingJob(MRBatchCubingEngine2.java:42)
      	at org.apache.kylin.engine.EngineFactory.createBatchCubingJob(EngineFactory.java:60)
      	at org.apache.kylin.rest.service.JobService.submitJobInternal(JobService.java:234)
      	at org.apache.kylin.rest.service.JobService.submitJob(JobService.java:202)
      	at org.apache.kylin.rest.controller.CubeController.buildInternal(CubeController.java:395)
      	at org.apache.kylin.rest.controller.CubeController.rebuild(CubeController.java:355)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              hust.zcheng Cheng Zhong
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: