Critical Description
As part of Hive configuration for Oracle I ran the schema creation script for Oracle. Here is what I observed when ran the script:
% sqlplus hive/hive@xe
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 10 18:47:11 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> @scripts/metastore/upgrade/oracle/hive-schema-0.10.0.oracle.sql;
.....
ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT SKEWED_STRING_LIST_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED
*
ERROR at line 1:
ORA-00904: "STRING_LIST_ID": invalid identifier
.....
ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT SKEWED_STRING_LIST_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED
*
ERROR at line 1:
ORA-00904: "STRING_LIST_ID": invalid identifier
Table created.
Table altered.
Table altered.
CREATE TABLE SKEWED_COL_VALUE_LOCATION_MAPPING
*
ERROR at line 1:
ORA-00972: identifier is too long
Table created.
Table created.
ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT SKEWED_COL_VALUE_LOCATION_MAPPING_PK PRIMARY KEY (SD_ID,STRING_LIST_ID_KID)
*
ERROR at line 1:
ORA-00972: identifier is too long
ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT SKEWED_COL_VALUE_LOCATION_MAPPING_FK1 FOREIGN KEY (STRING_LIST_ID_KID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED
*
ERROR at line 1:
ORA-00972: identifier is too long
ALTER TABLE SKEWED_COL_VALUE_LOCATION_MAPPING ADD CONSTRAINT SKEWED_COL_VALUE_LOCATION_MAPPING_FK2 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) INITIALLY DEFERRED
*
ERROR at line 1:
ORA-00972: identifier is too long
Table created.
Table altered.
ALTER TABLE SKEWED_VALUES ADD CONSTRAINT SKEWED_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID_EID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) INITIALLY DEFERRED
*
ERROR at line 1:
ORA-00904: "STRING_LIST_ID": invalid identifier
Basically there are two issues here with the Oracle sql script:
(1) Table "SKEWED_STRING_LIST" is created with the column "SD_ID". Later the script tries to reference "STRING_LIST_ID" column in "SKEWED_STRING_LIST" which is obviously not there. Comparing the sql with that for other flavors it seems it should be "STRING_LIST_ID".
(2) Table name "SKEWED_COL_VALUE_LOCATION_MAPPING" is too long for Oracle which limits identifier names to 30 characters. Also impacted are identifiers "SKEWED_COL_VALUE_LOCATION_MAPPING_PK" and "SKEWED_COL_VALUE_LOCATION_MAPPING_FK1".