Mapping Hive Tables to Oracle NoSQL Database Tables In a Secure Store

Assuming you have deployed a secure Oracle NoSQL Database store in the manner described in the Deploying a Secure Store appendix, login to one of the nodes of the Big Data SQL system that can be used as a Hive client. Then, from the Hive command line interface, execute the following command to use a password file to map a Hive external table to the vehicleTable in the secure Oracle NoSQL Database:

CREATE EXTERNAL TABLE IF NOT EXISTS vehicleTablePasswd
    (type STRING, make STRING, model STRING, class STRING, color STRING, 
        price DOUBLE, count INT, dealerid DECIMAL, delivered TIMESTAMP) 
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "vehicleTable",
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user", 
        "oracle.kv.auth.pwdfile.file" = "/tmp/example-user.passwd");

And to use an Oracle Wallet to map a Hive external table to that same vehicleTable in the secure Oracle NoSQL Database, execute the Hive command:

CREATE EXTERNAL TABLE IF NOT EXISTS vehicleTableWallet
    (type STRING, make STRING, model STRING, class STRING, color STRING, 
        price DOUBLE, count INT, dealerid DECIMAL, delivered TIMESTAMP) 
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "vehicleTable",
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user", 
        "oracle.kv.auth.wallet.dir" = "/tmp/example-user-wallet.dir");

Similarly, to use a password file to map a Hive external table to the rmvTable described in Creating and Populating the rmvTable appendix, execute the command:

CREATE EXTERNAL TABLE IF NOT EXISTS rmvTablePasswd
    (zipcode STRING, lastname STRING, firstname STRING, ssn BIGINT, 
        gender STRING, license BINARY, phoneinfo MAP<STRING, STRING>,
        address STRUCT<number:INT street:STRING, 
        unit:INT, city:STRING, zip:INT>,
        vehicleinfo ARRAY<STRUCT<type:STRING, make:STRING,
            model:STRING, class:STRING, color:STRING,
            value:FLOAT, tax:DOUBLE, paid:BOOLEAN>>)
    COMMENT 'Hive mapped to NoSQL table: rmvTable'
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "rmvTable",
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user", 
        "oracle.kv.auth.pwdfile.file" = "/tmp/example-user.passwd");

And to use an Oracle Wallet to map a Hive external table to that same rmvTable in the secure Oracle NoSQL Database, execute the command:

CREATE EXTERNAL TABLE IF NOT EXISTS rmvTableWallet
    (zipcode STRING, lastname STRING, firstname STRING, ssn BIGINT, 
        gender STRING, license BINARY, phoneinfo MAP<STRING, STRING>,
        address STRUCT<number:INT street:STRING, 
            unit:INT, city:STRING, zip:INT>,
        vehicleinfo ARRAY<STRUCT<type:STRING, make:STRING,
            model:STRING, class:STRING, color:STRING,
            value:FLOAT, tax:DOUBLE, paid:BOOLEAN>>)
    COMMENT 'Hive mapped to NoSQL table: rmvTable'
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "rmvTable",
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user", 
        "oracle.kv.auth.wallet.dir" = "/tmp/example-user-wallet.dir");

Finally, to use a password file to map a Hive external table to the exampleJsonTable described in the Creating and Populating the exampleJsonTable appendix, execute the command:

CREATE EXTERNAL TABLE IF NOT EXISTS exampleTablePasswd
    (id INTEGER, jsonfield STRING)
    COMMENT 'Hive mapped to NoSQL table: exampleJsonTable'
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "exampleJsonTable", 
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user",
        "oracle.kv.auth.pwdfile.file" = "/tmp/example-user.passwd");

And to use an Oracle Wallet to map a Hive external table to that same exampleJsonTable in the secure Oracle NoSQL Database, execute the command:

CREATE EXTERNAL TABLE IF NOT EXISTS exampleJsonTableWallet
    (int INTEGER, jsonfield STRING)
    COMMENT 'Hive mapped to NoSQL table: exampleJsonTable'
    STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler'
    TBLPROPERTIES (
        "oracle.kv.kvstore" = "example-store", 
        "oracle.kv.hosts"= "kv-host-1:5000,kv-host-2:5000,kv-host-3:5000", 
        "oracle.kv.tableName" = "exampleJsonTable", 
        "oracle.kv.hadoop.hosts" = "dn-host-1,dn-host-2,dn-host-3",
        "oracle.kv.security" = "/tmp/hive-nosql.login",
        "oracle.kv.ssl.truststore" = "/tmp/client.trust",
        "oracle.kv.auth.username" = "example-user",
        "oracle.kv.auth.wallet.dir" = "/tmp/example-user-wallet.dir");

Note that although the tables in the secure Oracle NoSQL Database store are named vehicleTable, rmvTable, and exampleJsonTable, the names of the tables created in Hive are not required to match the names of the corresponding Oracle NoSQL Database tables. This allows you to create different Hive tables mapped to the same Oracle NoSQL Database table; which allows you to query the Oracle NoSQL Database table using different security mechanisms.