Hive Queries on rmvTable: Non-Primitive Data Types
After following the directions presented in the previous sections to create and map a Hive external table to a table in the Oracle NoSQL Database store (either non-secure or secure), the data in the store's table can be queried via the Hive Query Language.
In a fashion similar to the vehicleTable queries presented previously, each sub-section below presents three instances of a given query, one for each of the three possible scenarios: non-secure, secure with password file, or secure with Oracle Wallet. But the query results are shown only once, in edited form.
Type the query from each sub-section below that corresponds to how you have configured your particular environment.
List Each Row in the rmvTable Located in Oracle NoSQL Database
SELECT * FROM rmvTable;
SELECT * FROM rmvTablePasswd;
SELECT * FROM rmvTableWallet;
OK
49027 GOMEZ CHRISTOPHER 509367447 male S57428836
{"cell":"616-351-0185","home":"213-630-2419","work":"617-227-9840"}
{
"number":88072,
"street":"Fifth Avenue",
"unit":6,
"city":"Cambridge",
"state":"OK",
"zip":49027
}
[
{
"type":"auto",
"make":"Ford",
"model":"Taurus",
"class":"AllWheelDrive",
"color":"blue",
"value":20743.234,
"tax":566.29,
"paid":false
},
{
"type":"auto",
"make":"Ford",
"model":"Taurus",
"class":"FrontWheelDrive",
"color":"blue",
"value":20743.559,
"tax":566.29,
"paid":true
}
]
40719 ROSARIO ANNE 448406765 female S04809975
{"cell":"303-804-1660","home":"408-630-2412","work":"415-804-9515"}
{
"number":96581,
"street":"Third Avenue",
"unit":7,
"city":"Springfield",
"state":"RI",
"zip":40719
}
[
{
"type":"truck",
"make":"Chrysler",
"model":"Ram3500",
"class":"RearWheelDrive",
"color":"blue",
"value":31115.26,
"tax":849.44,
"paid":true
},
{
"type":"truck",
"make":"Chrysler",
"model":"Ram1500",
"class":"AllWheelDrive",
"color":"blue",
"value":31114.87,
"tax":849.43,
"paid":false
},
{
"type":"auto",
"make":"Ford",
"model":"Edge",
"class":"RearWheelDrive",
"color":"yellow",
"value":20743.88,
"tax":566.30,
"paid":true
}
]
.......... List Name, Gender, and Address of Each Vehicle Owner in rmvTable
SELECT lastname,firstname,gender,address FROM rmvTable;
SELECT lastname,firstname,gender,address FROM rmvTablePasswd;
SELECT lastname,firstname,gender,address FROM rmvTableWallet;
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 = 80%, reduce = 0%, Cumulative CPU 6.87 sec
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 14.16 sec
..........
Job 0: Map: 6 Reduce: 1 Cumulative CPU: 14.16 sec
HDFS Read: 4760 HDFS Write: 4702 SUCCESS
Total MapReduce CPU Time Spent: 14 seconds 160 msec
OK
SNIDER FRANK male
{
"number":33512,
"street":"Summer Street",
"unit":1,
"city":"Arlington",
"state":"TN","zip":89150
}
MILLER ROCH male
{
"number":25698,
"street":"Mullberry Street",
"unit":6,
"city":"Madison",
"state":"VA",
"zip":5740
}
TATE BENJAMIN male
{
"number":2894,
"street":"View Street",
"unit":-1,
"city":"Clinton",
"state":"KY",
"zip":57466
}
..........List Name and Phone Number of Each Vehicle Owner in rmvTable
SELECT firstname,lastname,phoneinfo["home"] FROM rmvTable;
SELECT firstname,lastname,phoneinfo["cell"] FROM rmvTablePasswd;
SELECT firstname,lastname,phoneinfo["work"] FROM rmvTableWallet;
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 = 100%, reduce = 0%, Cumulative CPU 18.11 sec
..........
Job 0: Map: 6 Cumulative CPU: 18.11 sec
HDFS Read: 4724 HDFS Write: 2141 SUCCESS
Total MapReduce CPU Time Spent: 18 seconds 110 msec
OK
CHRISTOPHER GOMEZ 213-630-2419
ANNE ROSARIO 408-630-2412
MEGAN PHELPS 978-541-5710
MICHAEL BRADLEY 313-351-4580
..........Count Total Number of Rows in rmvTable
SELECT count(vehicleinfo[0].type) FROM rmvTable;
SELECT count(vehicleinfo[0].type) FROM rmvTablePasswd;
SELECT count(vehicleinfo[0].type) FROM rmvTableWallet;
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 50%, reduce = 0%, Cumulative CPU 12.12 sec
Stage-1 map = 100%, reduce = 100%, Cumulative CPU 25.51 sec
..........
Job 0: Map: 6 Reduce: 1 Cumulative CPU: 25.51 sec
HDFS Read: 4760 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 25 seconds 510 msec
OK
79For Each Owner's Primary Vehicle, Find the Minimum Assessed Value
SELECT min(vehicleinfo[0].value) FROM rmvTable;
SELECT min(vehicleinfo[0].value) FROM rmvTablePasswd;
SELECT min(vehicleinfo[0].value) FROM rmvTableWallet;
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 14.3 sec
Stage-1 map = 100%, reduce = 100%, Cumulative CPU 15.38 sec
..........
Job 0: Map: 6 Reduce: 1 Cumulative CPU: 15.38 sec
HDFS Read: 4532 HDFS Write: 16 SUCCESS
Total MapReduce CPU Time Spent: 15 seconds 380 msec
OK
20743.24List All Info For Each Owner's Vehicle (Primary, Secondary, Tertiary)
SELECT vehicleinfo[0] FROM rmvTable;
SELECT vehicleinfo[1] FROM rmvTablePasswd;
SELECT vehicleinfo[2] FROM rmvTableWallet;
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 17%, reduce = 0%, Cumulative CPU 4.59 sec
Stage-1 map = 95%, reduce = 0%, Cumulative CPU 27.33 sec
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 27.89 sec
..........
Job 0: Map: 6 Cumulative CPU: 27.89 sec
HDFS Read: 4760 HDFS Write: 5681 SUCCESS
Total MapReduce CPU Time Spent: 27 seconds 890 msec
OK
{
"type":"suv",
"make":"GM",
"model":"Tahoe",
"class":"4WheelDrive",
"color":"black",
"value":41487.24,
"tax":1132.60,
"paid":true
}
{
"type":"auto",
"make":"Chrysler",
"model":"Imperial",
"class":"AllWheelDrive",
"color":"red",
"value":20743.92,
"tax":566.30,
"paid":true
}
{
"type":"auto",
"make":"Ford",
"model":"Taurus",
"class":"RearWheelDrive",
"color":"blue",
"value":20744.07,
"tax":566.31,
"paid":true
}
..........List Name, Address, Vehicle Info For Owner Surnames Starting With 'H'
SELECT firstname,lastname,address,vehicleinfo[0] FROM rmvTable
WHERE RLIKE "^[H].*";
SELECT firstname,lastname,address,vehicleinfo[0]FROM rmvTablePasswd
WHERE RLIKE "^[H].*";
SELECT firstname,lastname,address,vehicleinfo[0] FROM rmvTableWallet
WHERE RLIKE "^[H].*";
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 33%, reduce = 0%, Cumulative CPU 9.46 sec
Stage-1 map = 83%, reduce = 0%, Cumulative CPU 23.29 sec
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 27.78 sec
..........
Job 0: Map: 6 Cumulative CPU: 27.78 sec
HDFS Read: 4760 HDFS Write: 1143 SUCCESS
Total MapReduce CPU Time Spent: 27 seconds 780 msec
OK
CINDY HODGES
{
"number":56758,
"street":"Vaughan Avenue",
"unit":-1,
"city":"Madison",
"state":"NH",
"zip":79623
}
{
"type":"truck",
"make":"Chrysler",
"model":"Ram1500",
"class":"RearWheelDrive",
"color":"black",
"value":31115.12,
"tax":849.44,
"paid":true
}
JULIA HOLDEN
{
"number":56209,
"street":"Main Street",
"unit":1,"city":"Georgetown",
"state":"CA",
"zip":62154
}
{
"type":"auto",
"make":"Ford",
"model":"Taurus",
"class":"FrontWheelDrive",
"color":"blue",
"value":20743.80,
"tax":566.30,
"paid":true
}
..........List Name, Address, Vehicle Info When Owner's Second Vehicle Is GM
SELECT firstname,lastname,address,vehicleinfo[1] FROM rmvTable
WHERE vehicleinfo[1].make LIKE "%GM%";
SELECT firstname,lastname,address,vehicleinfo[1] FROM rmvTablePasswd
WHERE vehicleinfo[1].make LIKE "%GM%";
SELECT firstname,lastname,address,vehicleinfo[1] FROM rmvTableWallet
WHERE vehicleinfo[1].make LIKE "%GM%";
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 50%, reduce = 0%, Cumulative CPU 9.29 sec
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 18.8 sec
..........
Job 0: Map: 6 Cumulative CPU: 18.8 sec
HDFS Read: 4724 HDFS Write: 2087 SUCCESS
Total MapReduce CPU Time Spent: 18 seconds 800 msec
OK
NANCY STOUT
{
"number":31126,
"street":"Cedar Street",
"unit":8,
"city":"Arlington",
"state":"MO",
"zip":73131
}
{
"type":"suv",
"make":"GM",
"model":"Equinox",
"class":"AllWheelDrive",
"color":"red",
"value":41486.43,
"tax":1132.57,
"paid":true
}
RANDY MCDOWELL
{
"number":18391,
"street":"Lane Avenue",
"unit":8,
"city":"Concord",
"state":"NH",
"zip":42540
}
{
"type":"auto",
"make":"GM",
"model":"Corvette",
"class":"FrontWheelDrive",
"color":"black",
"value":20744.03,
"tax":566.31,
"paid":false
}
..........List Name, Address, Model, Assessed Value & Registration Fee Status (Paid or Not) When Primary Vehicle Is Chrysler
SELECT firstname, lastname, address, vehicleInfo[0].model,
vehicleinfo[0].value, vehicleinfo[0].tax, vehicleinfo[0].paid
FROM rmvTable WHERE vehicleinfo[0]make LIKE "%Chrysler%";
SELECT firstname,lastname,address, vehicleInfo[0].model,
vehicleinfo[0].value, vehicleinfo[0].tax, vehicleinfo[0].paid
FROM rmvTablePasswd WHERE vehicleinfo[0]make LIKE "%Chrysler%";
SELECT firstname,lastname,address, vehicleInfo[0].model,
vehicleinfo[0].value, vehicleinfo[0].tax, vehicleinfo[0].paid
FROM rmvTableWallet WHERE vehicleinfo[0]make LIKE "%Chrysler%";
Launching Job 1 out of 1
..........
Hadoop job information for Stage-1: number of mappers: 6;
number of reducers: 1
Stage-1 map = 43%, reduce = 0%, Cumulative CPU 9.46 sec
Stage-1 map = 100%, reduce = 0%, Cumulative CPU 18.15 sec
..........
Job 0: Map: 6 Cumulative CPU: 18.15 sec
HDFS Read: 4724 HDFS Write: 2164 SUCCESS
Total MapReduce CPU Time Spent: 18 seconds 150 msec
OK
ANNE ROSARIO
{
"number":96581,
"street":"Third Avenue",
"unit":7,
"city":"Springfield",
"state":"RI","zip":40719
}
Ram3500 31115.26 849.44 true
MEGAN PHELPS
{
"number":12713,
"street":"MAC Avenue",
"unit":4,"city":"Salem",
"state":"MS",
"zip":76554
}
Ram1500 31115.30 849.44 true
BRIAN ROWLAND
{
"number":37868,
"street":"First Street",
"unit":3,
"city":"Salem",
"state":"GA",
"zip":98106
}
Imperial 20744.15 566.31 true
..........