1. Integrations Guide
  2. Integration with Apache Hive
  3. Example: Hive Queries On Oracle NoSQL Database Tables
  4. Primitive Data Types - The vehicleTable Example
  5. Hive Queries on vehicleTable: Primitive Data Types

Hive Queries on vehicleTable: Primitive Data Types

After following the directions presented in the previous sections to create and map a Hive external table to the vehicleTable in the Oracle NoSQL Database store (either non-secure or secure), that table can be queried via the Hive Query Language.

In the previous sections, three scenarios were presented for mapping a Hive table to a table in a given Oracle NoSQL Database store: a non-secure store, a secure store in which the client store's its password in a password file, and a secure store in which the client store's its password in an Oracle Wallet. As a result, each of the following sections present three forms of a given query, one for each scenario; specifically,

  • A query on the table named vehicleTable in the non-secure scenario
  • A query on vehicleTablePasswd in the secure (with password file) scenario
  • A query on vehicleTableWallet in the secure (with wallet) scenario

The only difference between a given query from scenario to scenario is in the name of the table to query.

Because the Hive table created for each separate scenario is mapped to the same underlying Oracle NoSQL Database table, the output of each form of a given query for each scenario will be the same. Thus, although each section presents three instances of a given query, the query result is shown only once, and is edited for clarity.

At the Hive CLI command prompt, type the query from each section below that corresponds to how you have configured your particular environment; non-secure or secure store and, if secure, whether you are using a password file or an Oracle Wallet to store the client's password.

Note:

In some cases Hive will execute a MapReduce job to satisfy the query, whereas in other cases, the query is satisfied by simply consulting the Hive data dictionary and so MapReduce is not employed.

List Each Row in the Oracle NoSQL Database vehicleTable

SELECT * FROM vehicleTable;
SELECT * FROM vehicleTablePasswd;
SELECT * FROM vehicleTableWallet;

OK
auto  Ford Focus  4WheelDrive white  20743.94 15 3 2020-10-09
auto  GM   Impala 4WheelDrive black  29834.91 24 7 2019-12-11
auto  GM   Impala 4WheelDrive yellow 21753.53 27 8 2017-03-31
truck Ford F250   4WheelDrive blue   31115.76 14 9 2018-02-01
..........

Count the Rows in vehicleTable

SELECT count(type) FROM vehicleTable;
SELECT count(type) FROM vehicleTablePasswd;
SELECT count(type) FROM vehicleTableWallet;

OK
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 0%,  reduce = 0%
Stage-1 map = 7%,  reduce = 0%, Cumulative CPU 2.26 sec
Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 6.7 sec
Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 6.87 sec
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 14.16 sec
Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 15.24 sec
..........
Job 0: Map: 6  Reduce: 1   Cumulative CPU: 15.24 sec   
HDFS Read: 4532 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 15 seconds 240 msec
OK
79
Time taken: 89.359 seconds, Fetched: 1 row(s)

Find the Lowest Price On Any Vehicle in vehicleTable

SELECT min(price) FROM vehicleTable;
SELECT min(price) FROM vehicleTablePasswd;
SELECT min(price) FROM vehicleTableWallet;

OK
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 0%,  reduce = 0%
Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 6.7 sec
Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 6.7 sec
Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 6.87 sec
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 12.16 sec
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 14.16 sec
..........
Job 0: Map: 6  Reduce: 1   Cumulative CPU: 15.24 sec   
HDFS Read: 4532 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 15 seconds 240 msec
OK
20743.94
Time taken: 89.615 seconds, Fetched: 1 row(s)

List All GM Vehicles in vehicleTable

SELECT * FROM vehicleTable WHERE make LIKE "%GM";
SELECT * FROM vehicleTablePasswd WHERE make LIKE "%GM";
SELECT * FROM vehicleTableWallet WHERE make LIKE "%GM";

OK
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 0
Stage-1 map = 0%,  reduce = 0%
Stage-1 map = 9%,  reduce = 0%, Cumulative CPU 2.43 sec
Stage-1 map = 26%,  reduce = 0%, Cumulative CPU 4.81 sec
Stage-1 map = 79%,  reduce = 0%, Cumulative CPU 13.09 sec
Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 16.06 sec
..........
Job 0: Map: 6  Cumulative CPU: 15.24 sec   
HDFS Read: 4532 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 15 seconds 240 msec
OK

suv   GM Equinox  4WheelDrive   white  20743.94 3 1 2019-03-01
truck GM Sierra   4WheelDrive   black  29834.91 8 3 2020-05-15
auto  GM Corvette 4WheelDrive   yellow 21753.53 7 5 2017-10-23
auto  GM Impala   AllWheelDrive blue   31115.76 4 9 2018-05-04
..........
Time taken: 89.615 seconds, Fetched: 1 row(s)

List All GM Vehicles in vehicleTable that are Red or Blue

SELECT * FROM vehicleTable WHERE color IN ('red','blue') AND make='GM';
SELECT * FROM vehicleTablePasswd WHERE color IN ('red','blue') AND make='GM';
SELECT * FROM vehicleTableWallet WHERE color IN ('red','blue') AND make='GM';

OK
auto  GM Tahoe   AllWheelDrive  red  20743.67 28 3 2019-02-04
auto  GM Sierra  RearWheelDrive blue 20744.10 63 7 2018-08-04
suv   GM Tahoe   RearWheelDrive red  41486.74 27 5 2020-07-08
truck GM Equinox 4WheelDrive    red  31115.17 31 9 2017-06-04
truck GM Blazer  AllWheelDrive  red  31114.83 69 2 2019-04-06
truck GM Sierra  4WheelDrive    blue 31115.32 85 7 2019-02-11
..........

Query a Range of Delivery Times and Order the Results

SELECT delivered FROM vehicleTable WHERE delivered 
    BETWEEN '2020-06-06 06:53:41.448643' AND '2019-09-05 15:40:22.057282' 
    ORDER BY delivered;
SELECT delivered FROM vehicleTablePasswd WHERE delivered 
    BETWEEN '2020-06-06 06:53:41.448643' AND '2019-09-05 15:40:22.057282' 
    ORDER BY delivered;
SELECT delivered FROM vehicleTableWallet WHERE delivered 
    BETWEEN '2020-06-06 06:53:41.448643' AND '2019-09-05 15:40:22.057282' 
    ORDER BY delivered;

OK
2019-07-25 09:19:24.743
2019-10-24 00:13:29.702
2019-11-04 05:31:34.358
..........
2020-05-25 06:15:16.809
2020-07-05 12:16:03.658
2020-09-05 15:40:22.057