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

build cube on step 3,ORA-00904: "B0"."CATALOG_NAME": 标识符⽆无效

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • v2.6.2
    • None
    • Environment , Metadata
    • None
    • operating system:Centos7(four real computer machines)
      Hadoop : 3.1.1(four nodes)
      Hive : 3.1.0
      HBase : 2.0.5(four nodes)
      Kylin : apache-kylin-2.6.2-bin-hadoop3(one node)

    Description

      My hive's metastore on oracle 11g,hive jdbc config is :

      hive jdbc config

      <property>
      <name>hive.metastore.db.type</name>
      <value>ORACLE</value>
      <description>
      Expects one of [derby, oracle, mysql, mssql, postgres].
      Type of database used by the metastore. Information schema & JDBCStorageHandler depend on it.
      </description>
      </property>
      <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:oracle:thin:@192.168.0.2:1521/xxx</value>
      <description>JDBC connect string for a JDBC metastore</description>
      </property>
      <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>oracle.jdbc.OracleDriver</value>
      <description>Driver class name for a JDBC metastore</description>
      </property>
      <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>bus_info</value>
      <description>username to use against metastore database</description>
      </property>
      <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>here is my passowrd</value>
      <description>password to use against metastore database</description>
      </property>
      <property>
      <name>hive.metastore.warehouse.dir</name>
      <value>/user/hive/warehouse</value>
      <description>location of default database for the warehouse</description>
      </property>

      when building cube on setp 3 , throws a exception is below:

      exception

      java.lang.RuntimeException: java.io.IOException: MetaException(message:Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.OWNER,A0.OWNER_TYPE,A0.RETENTION,A0.REWRITE_ENABLED,A0.TBL_NAME,A0.TBL_TYPE,A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0."NAME" = ? AND B0."CATALOG_NAME" = ?)
      at org.apache.kylin.source.hive.HiveMRInput$HiveTableInputFormat.configureJob(HiveMRInput.java:83)
      at org.apache.kylin.engine.mr.steps.FactDistinctColumnsJob.setupMapper(FactDistinctColumnsJob.java:126)
      at org.apache.kylin.engine.mr.steps.FactDistinctColumnsJob.run(FactDistinctColumnsJob.java:104)
      at org.apache.kylin.engine.mr.common.MapReduceExecutable.doWork(MapReduceExecutable.java:131)
      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.DistributedScheduler$JobRunner.run(DistributedScheduler.java:110)
      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)
      Caused by: java.io.IOException: MetaException(message:Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.OWNER,A0.OWNER_TYPE,A0.RETENTION,A0.REWRITE_ENABLED,A0.TBL_NAME,A0.TBL_TYPE,A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0."NAME" = ? AND B0."CATALOG_NAME" = ?)
      at org.apache.hive.hcatalog.mapreduce.HCatInputFormat.setInput(HCatInputFormat.java:97)
      at org.apache.hive.hcatalog.mapreduce.HCatInputFormat.setInput(HCatInputFormat.java:51)
      at org.apache.kylin.source.hive.HiveMRInput$HiveTableInputFormat.configureJob(HiveMRInput.java:80)
      ... 10 more
      Caused by: MetaException(message:Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.OWNER,A0.OWNER_TYPE,A0.RETENTION,A0.REWRITE_ENABLED,A0.TBL_NAME,A0.TBL_TYPE,A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0."NAME" = ? AND B0."CATALOG_NAME" = ?)
      at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:208)
      at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:108)
      at com.sun.proxy.$Proxy70.get_table_req(Unknown Source)
      at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTable(HiveMetaStoreClient.java:1578)
      at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTable(HiveMetaStoreClient.java:1570)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:208)
      at com.sun.proxy.$Proxy71.getTable(Unknown Source)
      at org.apache.hive.hcatalog.common.HCatUtil.getTable(HCatUtil.java:191)
      at org.apache.hive.hcatalog.mapreduce.InitializeInput.getInputJobInfo(InitializeInput.java:105)
      at org.apache.hive.hcatalog.mapreduce.InitializeInput.setInput(InitializeInput.java:88)
      at org.apache.hive.hcatalog.mapreduce.HCatInputFormat.setInput(HCatInputFormat.java:95)
      ... 12 more
      Caused by: javax.jdo.JDOException: Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.OWNER,A0.OWNER_TYPE,A0.RETENTION,A0.REWRITE_ENABLED,A0.TBL_NAME,A0.TBL_TYPE,A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0."NAME" = ? AND B0."CATALOG_NAME" = ?
      NestedThrowables:
      java.sql.SQLSyntaxErrorException: ORA-00904: "B0"."CATALOG_NAME": 标识符无效

      at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:677)
      at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:391)
      at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:255)
      at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:1863)
      at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:1899)
      at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:1507)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
      at com.sun.proxy.$Proxy68.getTable(Unknown Source)
      at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_core(HiveMetaStore.java:2946)
      at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getTableInternal(HiveMetaStore.java:2907)
      at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_req(HiveMetaStore.java:2892)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147)
      ... 26 more
      Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "B0"."CATALOG_NAME": 标识符无效

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:375)
      at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:552)
      at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:617)
      at org.datanucleus.store.query.Query.executeQuery(Query.java:1855)
      at org.datanucleus.store.query.Query.executeWithArray(Query.java:1744)
      at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:368)
      ... 44 more

      I lookup The Hive's MetaTable('DBS' and 'TBLS' ) on Oracle,can not found the column name called 'CATALOG_NAME' in Table 'DBS' . Then I lookup the Hive's init sql files,also not found any files include 'CATALOG_NAME' .

      The Table 'DBS' struct is below:

      Table DBS

      DB_ID NUMBER NOT NULL,
      "DESC" VARCHAR2(4000) NULL,
      DB_LOCATION_URI VARCHAR2(4000) NOT NULL,
      "NAME" VARCHAR2(128) NULL,
      OWNER_NAME VARCHAR2(128) NULL,
      OWNER_TYPE VARCHAR2(10) NULL,
      CTLG_NAME VARCHAR2(256)

      The Table 'TBLS' struct is below:

      Attachments

        1. errlog.PNG
          185 kB
          fapzlx@163.com
        2. step_errors.PNG
          85 kB
          fapzlx@163.com

        Activity

          People

            Unassigned Unassigned
            fapzlx fapzlx@163.com
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: