将hive的元数据存到Oracle里各位高手总结-大数据学习-About云-梭伦科技

pig2 发表于 2013-12-23 12:58:04

将hive的元数据存到Oracle里各位高手总结

本帖最后由 pig2 于 2013-12-23 13:07 编辑

高手一：
1)create oracle user
sql> create user hive identified by hive identified by hive

default tablespace hive;

sql> grant dba to hive;

2)edit hive/conf/hive-default.xml<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>








<property>
<name>mapred.reduce.tasks</name>
<value>-1</value>
<description>The default number of reduce tasks per job. Typically set
to a prime close to the number of available hosts. Ignored when
mapred.job.tracker is "local". Hadoop set this to 1 by default, whereas hive uses -1 as its default value.
By setting this property to -1, Hive will automatically figure out what should be the number of reducers.
</description>
</property>

<property>
<name>hive.exec.reducers.bytes.per.reducer</name>
<value>1000000000</value>
<description>size per reducer.The default is 1G, i.e if the input size is 10G, it will use 10 reducers.</description>
</property>

<property>
<name>hive.exec.reducers.max</name>
<value>999</value>
<description>max number of reducers will be used. If the one
specified in the configuration parameter mapred.reduce.tasks is
negative, hive will use this one as the max number of reducers when
automatically determine number of reducers.</description>
</property>

<property>
<name>hive.exec.scratchdir</name>
<value>/tmp/hive-${user.name}</value>
<description>Scratch space for Hive jobs</description>
</property>

<property>
<name>hive.test.mode</name>
<value>false</value>
<description>whether hive is running in test mode. If yes, it turns on sampling and prefixes the output tablename</description>
</property>

<property>
<name>hive.test.mode.prefix</name>
<value>test_</value>
<description>if hive is running in test mode, prefixes the output table by this string</description>
</property>








<property>
<name>hive.test.mode.samplefreq</name>
<value>32</value>
<description>if hive is running in test mode and table is not bucketed, sampling frequency</description>
</property>

<property>
<name>hive.test.mode.nosamplelist</name>
<value></value>
<description>if hive is running in test mode, dont sample the above comma seperated list of tables</description>
</property>

<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>controls whether to connect to remove metastore server or open a new metastore server in Hive Client JVM</description>
</property>

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:oracle:thin:@192.168.1.101:1521/ORCL</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>oracle.jdbc.driver.OracleDriver</value> 【就是第三步下载的ojdbc.jar】
<description>Driver class name for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.PersistenceManagerFactoryClass</name>
<value>org.datanucleus.jdo.JDOPersistenceManagerFactory</value>
<description>class implementing the jdo persistence</description>
</property>

<property>
<name>javax.jdo.option.DetachAllOnCommit</name>
<value>true</value>
<description>detaches all objects from session so that they can be used after transaction is committed</description>
</property>

<property>
<name>javax.jdo.option.NonTransactionalRead</name>
<value>true</value>
<description>reads outside of transactions</description>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value> [用户]
<description>username to use against metastore database</description>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value> [密码]
<description>password to use against metastore database</description>
</property>

<property>
<name>datanucleus.validateTables</name>
<value>false</value>
<description>validates existing schema against code. turn this on if you want to verify existing schema </description>
</property>

<property>
<name>datanucleus.validateColumns</name>
<value>false</value>
<description>validates existing schema against code. turn this on if you want to verify existing schema </description>
</property>

<property>
<name>datanucleus.validateConstraints</name>
<value>false</value>
<description>validates existing schema against code. turn this on if you want to verify existing schema </description>
</property>

<property>
<name>datanucleus.storeManagerType</name>
<value>rdbms</value>
<description>metadata store type</description>
</property>

<property>
<name>datanucleus.autoCreateSchema</name>
<value>true</value>
<description>creates necessary schema on a startup if one doesn't exist. set this to false, after creating it once</description>
</property>

<property>
<name>datanucleus.autoStartMechanismMode</name>
<value>checked</value>
<description>throw exception if metadata tables are incorrect</description>
</property>

<property>
<name>datancucleus.transactionIsolation</name>
<value>read-committed</value>
<description></description>
</property>

<property>
<name>datanuclues.cache.level2</name>
<value>true</value>
<description>use a level 2 cache. turn this off if metadata is changed independently of hive metastore server</description>
</property>

<property>
<name>datanuclues.cache.level2.type</name>
<value>SOFT</value>
<description>SOFT=soft reference based cache, WEAK=weak reference based cache.</description>
</property>

<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>location of default database for the warehouse</description>
</property>

<property>
<name>hive.metastore.connect.retries</name>
<value>5</value>
<description>Number of retries while opening a connection to metastore</description>
</property>

<property>
<name>hive.metastore.rawstore.impl</name>
<value>org.apache.hadoop.hive.metastore.ObjectStore</value>
<description>Name of the class that implements org.apache.hadoop.hive.metastore.rawstore interface. This class is used to store and retrieval of raw metadata objects

such as table, database</description>
</property>

<property>
<name>hive.default.fileformat</name>
<value>TextFile</value>
<description>Default file format for CREATE TABLE statement. Options are TextFile and SequenceFile. Users can explicitly say CREATE TABLE ... STORED AS

<TEXTFILE|SEQUENCEFILE> to override</description>
</property>

<property>
<name>hive.map.aggr</name>
<value>true</value>
<description>Whether to use map-side aggregation in Hive Group By queries</description>
</property>

<property>
<name>hive.groupby.skewindata</name>
<value>false</value>
<description>Whether there is skew in data to optimize group by queries</description>
</property>

<property>
<name>hive.groupby.mapaggr.checkinterval</name>
<value>100000</value>
<description>Number of rows after which size of the grouping keys/aggregation classes is performed</description>
</property>

<property>
<name>hive.mapred.local.mem</name>
<value>0</value>
<description>For local mode, memory of the mappers/reducers</description>
</property>

<property>
<name>hive.map.aggr.hash.percentmemory</name>
<value>0.5</value>
<description>Portion of total memory to be used by map-side grup aggregation hash table</description>
</property>

<property>
<name>hive.map.aggr.hash.min.reduction</name>
<value>0.5</value>
<description>Hash aggregation will be turned off if the ratio between hash
table size and input rows is bigger than this number. Set to 1 to make sure
hash aggregation is never turned off.</description>
</property>

<property>
<name>hive.optimize.cp</name>
<value>true</value>
<description>Whether to enable column pruner</description>
</property>

<property>
<name>hive.optimize.ppd</name>
<value>true</value>
<description>Whether to enable predicate pushdown</description>
</property>

<property>
<name>hive.optimize.pruner</name>
<value>true</value>
<description>Whether to enable the new partition pruner which depends on predicate pushdown. If this is disabled,
the old partition pruner which is based on AST will be enabled.</description>
</property>

<property>
<name>hive.join.emit.interval</name>
<value>1000</value>
<description>How many rows in the right-most join operand Hive should buffer before emitting the join result. </description>
</property>

<property>
<name>hive.mapred.mode</name>
<value>nonstrict</value>
<description>The mode in which the hive operations are being performed. In strict mode, some risky queries are not allowed to run</description>
</property>

<property>
<name>hive.exec.script.maxerrsize</name>
<value>100000</value>
<description>Maximum number of bytes a script is allowed to emit to standard error (per map-reduce task). This prevents runaway scripts from filling logs partitions to

capacity </description>
</property>

<property>
<name>hive.exec.compress.output</name>
<value>false</value>
<description> This controls whether the final outputs of a query (to a local/hdfs file or a hive table) is compressed. The compression codec and other options are

determined from hadoop config variables mapred.output.compress* </description>
</property>

<property>
<name>hive.exec.compress.intermediate</name>
<value>false</value>
<description> This controls whether intermediate files produced by hive between multiple map-reduce jobs are compressed. The compression codec and other options are

determined from hadoop config variables mapred.output.compress* </description>
</property>

<property>
<name>hive.hwi.listen.host</name>
<value>0.0.0.0</value>
<description>This is the host address the Hive Web Interface will listen on</description>
</property>

<property>
<name>hive.hwi.listen.port</name>
<value>9999</value>
<description>This is the port the Hive Web Interface will listen on</description>
</property>

<property>
<name>hive.hwi.war.file</name>
<value>${HIVE_HOME}/lib/hive-hwi.war</value>
<description>This is the WAR file with the jsp content for Hive Web Interface</description>
</property>

<property>
<name>hive.exec.pre.hooks</name>
<value></value>
<description>Pre Execute Hook for Tests</description>
</property>

<property>
<name>hive.merge.mapfiles</name>
<value>true</value>
<description>Merge small files at the end of a map-only job</description>
</property>

<property>
<name>hive.merge.mapredfiles</name>
<value>false</value>
<description>Merge small files at the end of any job(map only or map-reduce)</description>
</property>

<property>
<name>hive.heartbeat.interval</name>
<value>1000</value>
<description>Send a heartbeat after this interval - used by mapjoin and filter operators</description>
</property>

<property>
<name>hive.merge.size.per.task</name>
<value>256000000</value>
<description>Size of merged files at the end of the job</description>
</property>

<property>
<name>hive.script.auto.progress</name>
<value>false</value>
<description>Whether Hive Tranform/Map/Reduce Clause should automatically send progress information to TaskTracker to avoid the task getting killed because of

inactivity. Hive sends progress information when the script is outputting to stderr. This option removes the need of periodically producing stderr messages, but users

should be cautious because this may prevent infinite loops in the scripts to be killed by TaskTracker. </description>
</property>

</configuration>
3)download oracle jdbc：------- ojdbc-14.jar

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

4) test it ：hive> # hive
Hive history file=/tmp/root/hive_job_log_root_201001162330_322775424.txt
hive> show tables;
OK
Time taken: 6.008 seconds
hive> create table test(id bigint);
OK
Time taken: 0.495 seconds
hive>

5）login into oracle,check if autocreate some tables;

$ sqlplus hive/hive@192.168.1.101:1521/spgjmega

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 16 23:57:37 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user;
USER is "HIVE"
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
NUCLEUS_TABLES TABLE
A TABLE
DBS TABLE
SEQUENCE_TABLE TABLE
SERDES TABLE
TBLS TABLE
SDS TABLE
PARTITION_KEYS TABLE
COLUMNS TABLE
BUCKETING_COLS TABLE
SD_PARAMS TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SORT_COLS TABLE
SERDE_PARAMS TABLE
TABLE_PARAMS TABLE

14 rows selected.

SQL> set line 1000
SQL> r
1* select * from TBLS

TBL_ID CREATE_TIME DB_ID LAST_ACCESS_TIME OWNER RETENTION SD_ID TBL_NAME
---------- ----------- ---------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1 1263655926 1 0 root 0 1 test

SQL>
高手二：

hadoop, hive, sqoop installed
ojdbc6.jar was in directory /usr/lib/sqoop/lib (ojdbc6.jar is for JDK 1.6)

1. Install oracle 11g for 32bit on a windows 32bit vm (windows server 2003)
2. Configure local listener(本地监听服务) inoracle Net Manager, add a listener
3. Load Oracle table to Hive table via Sqoop
In Oracle SQL Plus, check all tables owned by current user SQL>select unique tname from col;
We choose table "DEPT" to migrate.

In cluster node

$ hive
$ show tables;
$ quit;

$ sqoop import --connect jdbc:oracle:thin:@IPAddressOfNodeInstallOracle:1521:orcl --username SCOTT --password 123456 --table DEPT -m 1 --hive-import --hive-table "HIVETEST"
NOTICE: IPAddressOfNodeInstallOracle is for windows run oracle(ex: 172.16.3.4); username and password are configured in oracle;tablename and username should be upper case
$ show tables;
$ select * from hivetest;

祭淰♂ 发表于 2013-12-23 14:28:22

大哥，能给下配置步骤吗，我的为什么连接不上oracle呢

pig2 发表于 2013-12-23 15:01:08

祭淰♂ 发表于 2013-12-23 14:28
大哥，能给下配置步骤吗，我的为什么连接不上oracle呢

你可以对照这个检查一下：
$ sqoop import --connect jdbc:oracle:thin:@IPAddressOfNodeInstallOracle:1521:orcl --username SCOTT --password 123456 --table DEPT -m 1 --hive-import --hive-table "HIVETEST"
连接不上的原因：
（1）你安装配置不当
（2）url不正确

aqi915 发表于 2015-10-9 10:37:14

大哥好:
   我的环境是hive 1.2.1 在linux上，32位系统与环境，oracle 11g 在window 64位系统，32位环境（oracle,java等）。原选集成mysql是成功的。

   hive元数据oracle集成，要建表时报如下错误，自己解决了下，到hive-metastore-1.2.1.jar下的 package.jdo里把 LONGVARCHAR 改为 clob但还是报如下错误，看了hive.log里的日志，我修改的没有成功，执行还是认到了两个long。
   <field name="viewOriginalText" default-fetch-group="false">
   <column name="VIEW_ORIGINAL_TEXT" jdbc-type="CLOB"/>
   </field>
   <field name="viewExpandedText" default-fetch-group="false">
   <column name="VIEW_EXPANDED_TEXT" jdbc-type="CLOB"/>
   </field>

建表过程
hive> CREATE TABLE pokes1(foo INT, bar STRING);
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : ORA-01754: a table may contain only one column of type LONG

java.sql.SQLSyntaxErrorException: ORA-01754: a table may contain only one column of type LONG

   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.executeForRows(T4CStatement.java:1036)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
   at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
   at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
   at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
   at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
   at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:760)
   at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatementList(AbstractTable.java:711)
   at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:425)
   at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:488)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3380)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:3190)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2841)
   at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:122)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:1605)
   at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:954)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:679)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.getPropertiesForGenerator(RDBMSStoreManager.java:2045)
   at org.datanucleus.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:1365)
   at org.datanucleus.ExecutionContextImpl.newObjectId(ExecutionContextImpl.java:3827)
   at org.datanucleus.state.JDOStateManager.setIdentity(JDOStateManager.java:2571)
   at org.datanucleus.state.JDOStateManager.initialiseForPersistentNew(JDOStateManager.java:513)
   at org.datanucleus.state.ObjectProviderFactoryImpl.newForPersistentNew(ObjectProviderFactoryImpl.java:232)
   at org.datanucleus.ExecutionContextImpl.newObjectProviderForPersistentNew(ExecutionContextImpl.java:1414)
   at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2218)
   at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:2065)
   at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1913)
   at org.datanucleus.ExecutionContextThreadedImpl.persistObject(ExecutionContextThreadedImpl.java:217)
   at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:727)
   at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
   at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:814)
   at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
   at com.sun.proxy.$Proxy0.createTable(Unknown Source)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1416)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1449)
   at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
   at com.sun.proxy.$Proxy3.create_table_with_environment_context(Unknown Source)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9200)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9184)
   at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
   at java.security.AccessController.doPrivileged(Native Method)
   at javax.security.auth.Subject.doAs(Subject.java:415)
   at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
   at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:745)

   at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)
   at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:732)
   at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
   at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:814)
   at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
   at com.sun.proxy.$Proxy0.createTable(Unknown Source)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1416)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1449)
   at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
   at com.sun.proxy.$Proxy3.create_table_with_environment_context(Unknown Source)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9200)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9184)
   at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
   at java.security.AccessController.doPrivileged(Native Method)
   at javax.security.auth.Subject.doAs(Subject.java:415)
   at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
   at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:745)
NestedThrowablesStackTrace:
java.sql.SQLSyntaxErrorException: ORA-01754: a table may contain only one column of type LONG

   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.executeForRows(T4CStatement.java:1036)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
   at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
   at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
   at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
   at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
   at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:760)
   at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatementList(AbstractTable.java:711)
   at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:425)
   at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:488)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3380)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:3190)
   at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2841)
   at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:122)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:1605)
   at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:954)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:679)
   at org.datanucleus.store.rdbms.RDBMSStoreManager.getPropertiesForGenerator(RDBMSStoreManager.java:2045)
   at org.datanucleus.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:1365)
   at org.datanucleus.ExecutionContextImpl.newObjectId(ExecutionContextImpl.java:3827)
   at org.datanucleus.state.JDOStateManager.setIdentity(JDOStateManager.java:2571)
   at org.datanucleus.state.JDOStateManager.initialiseForPersistentNew(JDOStateManager.java:513)
   at org.datanucleus.state.ObjectProviderFactoryImpl.newForPersistentNew(ObjectProviderFactoryImpl.java:232)
   at org.datanucleus.ExecutionContextImpl.newObjectProviderForPersistentNew(ExecutionContextImpl.java:1414)
   at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2218)
   at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:2065)
   at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1913)
   at org.datanucleus.ExecutionContextThreadedImpl.persistObject(ExecutionContextThreadedImpl.java:217)
   at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:727)
   at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
   at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:814)
   at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
   at com.sun.proxy.$Proxy0.createTable(Unknown Source)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1416)
   at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1449)
   at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
   at com.sun.proxy.$Proxy3.create_table_with_environment_context(Unknown Source)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9200)
   at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9184)
   at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
   at java.security.AccessController.doPrivileged(Native Method)
   at javax.security.auth.Subject.doAs(Subject.java:415)
   at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
   at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
   at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:745)
)

发现hive-metastore-1.2.1.jar下的 package.jdo里还有个，也只有一个这个类型，
<field name="message">
   <column name="MESSAGE" jdbc-type="LONGVARCHAR"/>
   </field>
里面还有下面这样的类型，不懂有没有影响
<field name="longLowValue">
   <column name="LONG_LOW_VALUE"jdbc-type="BIGINT" allows-null="true"/>
   </field>
   <field name="longHighValue">
   <column name="LONG_HIGH_VALUE" jdbc-type="BIGINT" allows-null="true"/>
   </field>
   <field name="doubleLowValue">
   <column name="DOUBLE_LOW_VALUE"jdbc-type="DOUBLE" allows-null="true"/>
   </field>
   <field name="doubleHighValue">
   <column name="DOUBLE_HIGH_VALUE" jdbc-type="DOUBLE" allows-null="true"/>
   </field>

hive-site.xml 配置文件如下
<configuration>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://ktbigdata1:9000/hive</value>
<description>location of default database for thewarehouse</description>
</property>
<property>
<name>hive.exec.scratchdir</name>
<value>hdfs://ktbigdata1:9000/hive/scratchdir</value>
<description>Scratch space for Hive jobs</description>
</property>
<property>
<name>hive.querylog.location</name>
<value>/home/hadoop/apache-hive-1.2.1-bin/logs</value>
<description>
Location of Hive run time structured log file
</description>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name> #oracle1
<value>jdbc:oracle:thin:@192.168.3.107:1521/ORCL</value>
<description>JDBC connect string for a JDBCmetastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>#oracle2
<value>oracle.jdbc.OracleDriver</value>
<description>Driver class name for aJDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name> #oracle3 oracle中的用户名
<value>username</value>
<description>username to use against metastoredatabase</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name> #oracle4 oracle中的密码
<value>password</value>
<description>password to use against metastoredatabase</description>
</property>
<property>
<name>hive.zookeeper.quorum</name>
<value>192.168.100.141,192.168.100.142,192.168.100.143</value>
<description>
List of ZooKeeper servers to talk to. This is needed for:
1. Read/write locks - when hive.lock.manager is set to
org.apache.hadoop.hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager,
2. When HiveServer2 supports service discovery via Zookeeper.
3. For delegation token storage if zookeeper store is used, if
hive.cluster.delegation.token.store.zookeeper.connectString is not set
</description>
</property>
## hive.aux.jars.path 可以不用
<property>
<name>hive.aux.jars.path</name>
<value>file:///home/hadoop/apache-hive-1.2.1-bin/lib/hive-hbase-handler*.jar,
file:///home/hadoop/apache-hive-1.2.1-bin/lib/protobuf-java-*.jar,
file:///home/hadoop/apache-hive-1.2.1-bin/lib/hbase-client*.jar,
file:///home/hadoop/apache-hive-1.2.1-bin/lib/hbase-common-*hadoop2.jar,
file:///home/hadoop/apache-hive-1.2.1-bin/lib/zookeeper-*.jar,
file:///home/hadoop/apache-hive-1.2.1-bin/lib/guava-*.jar
file:///home/hadoop/apache-hive-1.2.1-bin/lib/mongo-hadoop-hive-1.4-rc0.jar</value>
</property>
#客户必须增加下面代码
<property>
<name>hive.metastore.uris</name>
<value>thrift://ktbigdata1:9083</value>
</property>
</configuration>

可以帮助排查下么，非常感谢！

bianxq86 发表于 2016-8-10 18:41:04

aqi915 发表于 2015-10-9 10:37
大哥好:
我的环境是hive 1.2.1 在linux上，32位系统与环境，oracle 11g 在window 64位系统，32位环 ...

今天碰到和你一样的问题，请问你解决了没。。

页: [1]

About云-梭伦科技's Archiver

将hive的元数据存到Oracle里各位高手总结