13 Example: SQL Queries On Oracle NoSQL Database Tables

After creating the Oracle Database external tables described in the previous sections, you can then execute SQL SELECT queries to retrieve the data stored in the Oracle NoSQL Database store. To execute the example queries presented in the following sections, connect to the database via Oracle sqlplus and set the linesize to aid readability; for example,

sqlplus NOSQL_EXAMPLE_USER/welcome1@<oracledbhost>:1521/ORCLPDB1

set linesize 250;

Before executing the examples presented in the following sections, you can verify that each Hive table you created is now accessible from the system's Oracle Database. To display information about those tables, execute the following commands from the SQL prompt:

col cluster_id format A20;
col database_name format A15;
col owner format A10;
col table_name format A20;
col partitioned format A15;

SELECT cluster_id, database_name, owner, table_name,
    partitioned FROM all_hive_tables;

After verifying that the Hive tables you created are accessible, you can then query the all_hive_columns view to verify the data mappings you specified.

col table_name format A20;
col column_name format A20;
col hive_column_type format A20;
col oracle_column_type format A20;

SELECT table_name, column_name, hive_column_type,
    oracle_column_type FROM all_hive_columns;