Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Re: metastore set up with Oracle backend?


Copy link to this message
-
Re: metastore set up with Oracle backend?
Thanks for the pointer John! - I got it working by editing
metastore/src/model/package.jdo to make the following change:

       <field name="viewOriginalText">
-        <column name="VIEW_ORIGINAL_TEXT" jdbc-type="LONGVARCHAR"/>
+        <column name="VIEW_ORIGINAL_TEXT" jdbc-type="CLOB"/>
       </field>
       <field name="viewExpandedText">
-        <column name="VIEW_EXPANDED_TEXT" jdbc-type="LONGVARCHAR"/>
+        <column name="VIEW_EXPANDED_TEXT" jdbc-type="CLOB"/>
       </field>

I then rebuilt hive and used the resulting build and it worked - is
there a way to achieve the same without changing
metastore/src/model/package.jdo and rebuilding?

Thanks,
Pradeep

John Sichi wrote:
> Hmm, this is because MTable now has two LONGVARCHAR fields (viewOriginalText and viewExpandedText).  Can you find a way to get the Oracle driver or JDO to map these to CLOB instead of LONG VARCHAR?  Oracle allows multiple CLOB fields on the same table, but only one LONG VARCHAR (legacy crud).
>
> JVS
>
> ________________________________________
> From: Pradeep Kamath [[EMAIL PROTECTED]]
> Sent: Friday, June 04, 2010 9:22 AM
> To: [EMAIL PROTECTED]
> Subject: Re: metastore set up with Oracle backend?
>
> Hi,
>   I tried using the oracle thin jdbc driver with the settings below in hive-site.xml but got the exception pasted below[1]- anybody hit this? (I am using Oracle 11g and hive trunk as of a month back).
>
> Thanks,
> Pradeep
>
> [1]
> -bash-3.1$ bin/hive
> Hive history file=/tmp/pradeepk/hive_job_log_pradeepk_201006032327_643566363.txt
> hive> show tables;
> FAILED: Error in metadata: MetaException(message:Got exception: 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.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
>         at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
>         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
>         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
>         at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
>         at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
>         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
>         at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
>         at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
>
>
>
> Carl Steinbach wrote:
> Hi Pradeep,
>
> Your config values will need to look something like this:
>
> javax.jdo.option.ConnectionURL = jdbc:oracle:thin:@'host':'port':'db-name'
> javax.jdo.option.ConnectionDriverName = oracle.jdbc.driver.OracleDriver
> javax.jdo.option.ConnectionUserName</hadoop/ConnectionUserName> = your-user-name
> javax.jdo.option.ConnectionPassword</hadoop/ConnectionPassword> = your-password
>
> Obviously you need to replace 'host', 'port' and 'db-name' with appropriate values. You also need to get a copy of the Oracle thin JDBC driver (or some other JDBC driver that supports Oracle) and make sure that it is available in Hive's classpath (e.g. by referencing it in HIVE_AUX_JARS_PATH). If this does not work out of the box I recommend consulting the documentation that comes with your JDBC driver.
>
> Thanks.
>
> Carl
>
> On Tue, May 25, 2010 at 11:12 AM, Pradeep Kamath <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
> Hi Sonal,
>  I want to set up hive metastore with Oracle as backend and wanted the config values for that.
>
> Thanks,
> Pradee
>
> -----Original Message-----
> From: Sonal Goyal [mailto:[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>]