Hive-0.13.0链接Oracle数据库时候出现ORA--01754解决方案

When installing WSO2 BAM version 2.3.0 on an Oracle Stats DB backend database you could run into an exception from the OracleDB e.g. ORA-01754.

The default hive installation which is embedded in WSO2 BAM is not Oracle ready and uses table definitions not compatible with Oracle DB.

Once you have configered the statsDB within WSO2 BAM and starting the BAM you’ll see errors along the lines of “ORA-01754: a table met contain only one column of type LONG”

This is thrown when Apache Hive starts to create its table in the statistics database.

Fortunately there is a fix for this problem. You’ll need to change the package.jdo file to not use the LONGVARCHAR column types anymore.

A good substitutecan be the CLOB datatype.

Please follow these steps to replace the datatypes for two columns in the Hive jar and reapplying the file to the WSO2 BAM installation:

This has been tailored towards a unix environment as that is the preferred platform for WSO2 BAM.

Nonetheless you can apply the same steps in a Windows environment.

Just substitute the commands (cp -> copy, mkdir -> md) to their Windows equivalents.

[1] Copy the hive jar file to a temp location

cp [BAM_HOME]/repository/components/plugins/hive_0.8.1.wso2v7.jar /tmp

[2] Then create a working folder and go there

mkdir /tmp/work
cd /tmp/work

 

[3] Unpack the hive jar.

jar -xvf ../hive_0.8.1.wso2v7.jar

[4] Create another workfolder for extracting yet another jar…. And go there

mkdir /tmp/metawork
cd /tmp/metawork

[5] Unpack the metastore jar

jar -xvf ../work/hive-metastore-0.8.1-wso2v7.jar

[6] Now comes the magic. Replace the LONGVARCHAR references in the package.jdo with CLOB. This will fix the problem!
 
[7] On unix we can use sed for that, otherwise use any editor you like and manually change the column datatypes.

sed -i -e ‘s/LONGVARCHAR/CLOB/g’ package.jdo

[8] Now repackage the hive-metastore jar again so that the updated package.jdo is incorporated…..

jar cfm hive-metastore-0.8.1-wso2v7.jar META-INF/MANIFEST.MF *

[9] Copy the new metastore jar over to the work folder and repackage the work folder to construct a new hive jar.

cp hive-metastore-0.8.1-wso2v7.jar ../work
cd ../work
jar cfm hive_0.8.1-wso2v7.jar META-INF/MANIFEST.MF *

[10] and copy the jar back to the WSO2 BAM components/plugins folder.
 
[11] if the repository/components/patches/patch0000 folder exists copy the hive jar into that folder too as this file is used after startup…

cp hive_0.8.1-wso2v7.jar [BAM_HOME]/repository/components/plugins

or

cp hive_0.8.1-wso2v7.jar [BAM_HOME]/repository/components/patches/patch0000

Now restart the BAM and you’ll see the error no more.