Mapping Oracle RDBMS Tables to Hive Tables for Non-Secure Store

To create and map an Oracle Database external table to the Hive external table initially mapped to the Oracle NoSQL Database vehicleTable, execute commands like the following from the sqlplus prompt:

CREATE TABLE IF NOT EXISTS vehicleTable
    (type VARCHAR2(10), make VARCHAR2(12), model VARCHAR2(20), 
        class VARCHAR2(40), color VARCHAR2(20), price NUMBER(8,2), 
        count NUMBER, dealerid NUMBER, delivered TIMESTAMP)
    ORGANIZATION EXTERNAL (TYPE ORACLE_HIVE 
        DEFAULT DIRECTORY DEFAULT_DIR 
        ACCESS PARAMETERS (com.oracle.bigdata.log.qc=query.log))
    REJECT LIMIT UNLIMITED;

Similarly, to map an Oracle Database external table to the Hive external table initially mapped to the Oracle NoSQL Database rmvTable, execute the command,

CREATE TABLE IF NOT EXISTS rmvTable
    (zipcode VARCHAR2(7), lastname VARCHAR2(20), firstname VARCHAR2(20), 
        ssn NUMBER, gender VARCHAR2(6), license VARCHAR2(9),
        phoneinfo VARCHAR2(67), address VARCHAR2(100),
        vehicleinfo VARCHAR2(1000))
    ORGANIZATION EXTERNAL (TYPE ORACLE_HIVE 
        DEFAULT DIRECTORY DEFAULT_DIR 
        ACCESS PARAMETERS (com.oracle.bigdata.log.qc=query.log))
    REJECT LIMIT UNLIMITED;

Finally, to map an Oracle Database external table to the Hive external table initially mapped to the Oracle NoSQL Database exampleJsonTable, execute the command,

CREATE TABLE IF NOT EXISTS exampleJsonTable
    (id INT, jsonfield VARCHAR2(2000))
    ORGANIZATION EXTERNAL (TYPE ORACLE_HIVE 
        DEFAULT DIRECTORY DEFAULT_DIR 
        ACCESS PARAMETERS (com.oracle.bigdata.log.qc=query.log))
    REJECT LIMIT UNLIMITED;

Note that if you want the name that you specify for the Oracle Database external table to be different than the name of the Hive external table to which it is mapped, then you must use the com.oracle.bigdata.tablename property to specify the name of the Hive external table in the command's ACCESS PARAMETERS; otherwise the name of the Oracle external table will default to the name of the Hive table. For example,

CREATE TABLE IF NOT EXISTS oracleVehicleTable
    (type VARCHAR2(10), make VARCHAR2(12), model VARCHAR2(20), 
        class VARCHAR2(40), color VARCHAR2(20), price NUMBER(8,2), 
        count NUMBER, dealerid NUMBER, delivered TIMESTAMP)
    ORGANIZATION EXTERNAL (TYPE ORACLE_HIVE 
        DEFAULT DIRECTORY DEFAULT_DIR 
        ACCESS PARAMETERS (com.oracle.bigdata.log.qc=query.log
        com.oracle.bigdata.tablename=vehicleTable))
    REJECT LIMIT UNLIMITED;

The Oracle Big Data SQL 4 User's Guide provides information on the various ACCESS PARAMETERS that can be specified for the ORACLE_HIVE access driver.