hive 使用oralce 存储元数据_hive元数据用oralce

作者：Cpp五条 | 2024-03-01 21:07:26

踩

hive元数据用oralce

hive 使用oracle 作为元数据存储的时候，创建表会遇到此错误：

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

解决办法：

用解压缩工具打开${HIVE_HOME}/lib中的hive-metastore-0.13.0.jar，发现名为package.jdo的文件，打开该文件并找到下面的内容。

<code class="prettyprint" style="max-height: 600px; display: block; background-color: rgb(247, 247, 247);"></code><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; font-size: 11pt; white-space: normal;"><</span><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"><field name="viewOriginalText" default-fetch-group="false"></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;">        <column name="VIEW_ORIGINAL_TEXT" jdbc-type="<span style="color: rgb(255, 0, 0);">LONGVARCHAR</span>"/></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"></field></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"><field name="viewExpandedText" default-fetch-group="false"></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;">        <column name="VIEW_EXPANDED_TEXT" jdbc-type="<span style="color: rgb(255, 0, 0);">LONGVARCHAR</span>"/></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"></field></span></span></div><div style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; white-space: normal;"></div>

可以发现列VIEW_ORIGINAL_TEXT和VIEW_EXPANDED_TEXT的类型都为LONGVARCHAR，对应于Oracle中的LONG，这样就与Oracle表只能存在一列类型为LONG的列的要求相矛盾，所以就出现错误了。

按照Hive官网的建议将该两列的jdbc-type的值改为CLOB，修改后的内容如下所示。

<code class="prettyprint" style="max-height: 600px; display: block; background-color: rgb(247, 247, 247);"></code><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; font-size: 11pt; white-space: normal;"><</span><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"><field name="viewOriginalText" default-fetch-group="false"></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;">        <column name="VIEW_ORIGINAL_TEXT" jdbc-type="</span></span><span style="font-family: 微软雅黑, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; font-size: 15px; line-height: 22px; white-space: normal;"><span style="color: rgb(255, 0, 0);">CLOB</span></span><span style="font-size: 15px; white-space: normal; font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; line-height: 1.5;">"/></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"></field></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"><field name="viewExpandedText" default-fetch-group="false"></span></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;">        <column name="VIEW_EXPANDED_TEXT" jdbc-type="</span></span><span style="font-family: 微软雅黑, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; font-size: 15px; line-height: 22px; white-space: normal;"><span style="color: rgb(255, 0, 0);">CLOB</span></span><span style="font-size: 15px; white-space: normal; font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; line-height: 1.5;">"/></span></div><div><span style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;"><span style="font-size: 15px; white-space: normal;"></field></span></span></div><div style="font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', SimSun, 'Segoe UI', Tahoma, Helvetica, sans-serif, 'Microsoft YaHei', Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif; white-space: normal;"></div>

修改以后，重启进入hive 创建表即可。

如果进入hive创建表仍旧错误，可查看oracle下的元数据库是否建表完毕，如下图：

如果未创建完毕，可查看hive的log文件，具体执行的SQL脚本位于 hive文件夹下scripts/metastore/upgrade/oracle/hive-schema-1.2.0.oracle.sql（1.2.0为对应的hive版本）

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/176907