Home > Workload Solutions > Oracle > Guides > Design Guide — Oracle Big Data SQL on Dell EMC PowerFlex > Creating Oracle external tables pointing to Oracle NoSQL kvstore
Hadoop provides access drivers that can connect to the Oracle NoSQL kvstore using Oracle Big Data SQL:
Hive> CREATE EXTERNAL TABLE IF NOT EXISTS regions
(R_regionkey int,
R_name string,
R_comment string)
STORED BY 'oracle. Kv. hadoop. hive. table. TableStorageHandler
TBLPROPERTIES ("oracle. Kv. kvstore" = "kvstore",
"oracle. Kv. hosts" = "oracle-nosql. localdomain:5001”,
"oracle. Kv. tableName" = "region”,
"oracle. Kv. hadoop. hosts" =” hadoop-namenode. localdomain, hadoop-datanode1.localdomain, datanode2.localdomain”);
Hive> CREATE EXTERNAL TABLE IF NOT EXISTS nation
(n_nationkey int,
N_name string,
N_regionkey string,
N_comment string)
STORED BY 'oracle. Kv. hadoop. hive. table. TableStorageHandler
TBLPROPERTIES ("oracle. Kv. kvstore" = "kvstore",
"oracle. Kv. hosts" = "oracle-nosql. localdomain:5001”,
"oracle. Kv. tableName" = "nation”,
"oracle. Kv. hadoop. hosts" =” hadoop-namenode. localdomain, hadoop- datanode1.localdomain, datanode2.localdomain”);
SQL>create table region (r_regionkey number(10,0), r_name varchar2(30), r_comment varchar(125))
ORGANIZATION EXTERNAL
(TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS
(com. oracle.bigdata. tablename=default.region)
)
REJECT LIMIT UNLIMITED;
SQL>create table nation(n_nationkey number(10,0), n_name varchar2(20),n_regionkey varchar2(30),n_comment varchar2(200))
ORGANIZATION EXTERNAL
(TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS
(com.oracle.bigdata.tablename=default.nation)
)
REJECT LIMIT UNLIMITED;