11 Mapping the Oracle RDBMS Data Model to the Oracle NoSQL Database Table Model
As the examples in this section demonstrate, in order to execute an Oracle Big Data SQL query against data stored in an Oracle NoSQL Database table, a Hive external table must first be created with a schema mapped from the schema of the desired Oracle NoSQL Database table. Once that Hive external table is created, a corresponding Oracle RDBMS external table must then be created with a schema mapped from the schema of the Hive table. This is accomplished by applying the mappings shown in the following table:
Table 11-1 Data Type Mappings: Oracle NoSQL Database - Hive - RDBMS
| Oracle NoSQL Database Type | Hive Type | RDBMS Type |
|---|---|---|
| FieldDef.Type.STRING | STRING | VARCHAR2(N) |
| CHAR | ||
| VARCHAR | ||
| FieldDef.Type.JSON | STRING | VARCHAR2(N) |
| FieldDef.Type.BOOLEAN | BOOLEAN | VARCHAR2(5) |
| FieldDef.Type.BINARY | BINARY | VARCHAR2(N) |
| FieldDef.Type.FIXED_BINARY | BINARY | VARCHAR2(N) |
| TINYINT | ||
| SMALLINT | ||
| FieldDef.Type.INTEGER | INT | NUMBER |
| FieldDef.Type.LONG | BIGINT | NUMBER |
| FieldDef.Type.FLOAT | FLOAT | NUMBER |
| FieldDef.Type.NUMBER | DECIMAL | NUMBER |
| FieldDef.Type.DOUBLE | DOUBLE | NUMBER |
| FieldDef.Type.ENUM | STRING | VARCHAR2(N) |
| FieldDef.Type.TIMESTAMP | java.sql.TIMESTAMP | TIMESTAMP |
| DATE | ||
| FieldDef.Type.ARRAY | ARRAY | VARCHAR2(N) |
| FieldDef.Type.MAP | MAP<STRING, data_type> | VARCHAR2(N) |
| FieldDef.Type.RECORD | STRUCT <col_name : data_type,…> | VARCHAR2(N) |
| UNIONTYPE <data_type, data_type,…> |
It is important to understand that when using Oracle Big Data SQL to query data in an Oracle NoSQL Database table, the schema of the Oracle external table you create is dependent on the schema of the corresponding Hive external table; which, in turn, is dependent on the schema of the Oracle NoSQL Database table you wish to query. Thus, if either type of external table is created using a schema that includes a data type that does not belong to one of the mappings presented in the table above, then an error will occur when any attempt is made to query the table.
Note that for fields in the Oracle external table specified as VARCHAR2(N), the value of N is the maximum number of characters of the variable length STRING that represents the specified field in the corresponding Hive and Oracle NoSQL Database tables. Therefore, you should use the type, structure, and expected length or size of the corresponding Hive and Oracle NoSQL Database fields to determine the appropriate value to specify for N when creating the Oracle external table.