Mapping Oracle RDBMS Tables to Hive Tables for Secure Store

To create an Oracle Database external table for querying the Oracle NoSQL Database vehicleTable when a secure store is accessed via a password file, execute a command like the following from the sqlplus prompt,

CREATE TABLE IF NOT EXISTS vehicleTablePasswd
    (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;

And when the store is accessed using an Oracle wallet execute,

CREATE TABLE IF NOT EXISTS vehicleTableWallet
    (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;

To create an Oracle Database external table for querying the Oracle NoSQL Database rmvTable, when a secure store is accessed via a password file, execute the command,

CREATE TABLE IF NOT EXISTS rmvTablePasswd
    (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; 

And when the store is accessed using an Oracle wallet execute,

CREATE TABLE IF NOT EXISTS rmvTableWallet
    (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;

To create an Oracle Database external table for querying the Oracle NoSQL Database exampleJsonTable, when a secure store is accessed via a password file, execute the command,

CREATE TABLE IF NOT EXISTS exampleJsonTablePasswd
    (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;

And when the store is accessed using an Oracle wallet execute,

CREATE TABLE IF NOT EXISTS exampleJsonTableWallet
    (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;