Mapping Hive Tables to Oracle NoSQL Database Tables In a Non-Secure Store
Assuming you have deployed a non-secure Oracle NoSQL Database store in the manner described in the Deploying a Non-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 map a Hive external table to the vehicleTable
described in the CountTableRows Support Programs appendix, where line breaks are inserted for readability:
CREATE EXTERNAL TABLE IF NOT EXISTS vehicleTable
(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");
Similarly, to map a Hive external table to the rmvTable
described in the Creating and Populating the rmvTable appendix:
CREATE EXTERNAL TABLE IF NOT EXISTS rmvTable
(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");
Finally, to map a Hive external table to the exampleJsonTable
described in Creating and Populating the exampleJsonTable appendix:
CREATE EXTERNAL TABLE IF NOT EXISTS exampleJsonTable
(id INT, 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");