Schema for the Example Table Named rmvTable

To demonstrate how a Hive query can be applied to an Oracle NoSQL Database table consisting of a mix of primitive and non-primitive data types, a table named rmvTable having the schema shown in the table below must be created in the Oracle NoSQL Database store deployed for this example. The data types specified in the schema shown below are defined by the Oracle NoSQL Database table API (see FieldDef.Type) .

Table 9-1 Schema for rmvTable

Field Name		Field Type
zipcode		FieldDef.Type.STRING
lastname		FieldDef.Type.STRING
first name		FieldDef.Type.STRING
ssn		FieldDef.Type.STRING
gender		FieldDef.Type.ENUM
license		FieldDef.Type.FIXED_BINARY(9)
phoneinfo		FieldDef.Type.MAP(STRING)
address		FieldDef.Type.RECORD
address Record Schema	number	FieldDef.Type.INTEGER
	street	FieldDef.Type.STRING
	unit	FieldDef.Type.INTEGER
	city	FieldDef.Type.STRING
	state	FieldDef.Type.STRING
	zip	FieldDef.Type.INTEGER
vehicleinfo		FieldDef.Type.ARRAY (FieldDef.Type.RECORD)
vehicleinfo Element Record Schema	type	FieldDef.Type.STRING
	make	FieldDef.Type.STRING
	model	FieldDef.Type.STRING
	class	FieldDef.Type.STRING
	color	FieldDef.Type.STRING
	value	FieldDef.Type.STRING
	tax	FieldDef.Type.STRING
	paid	FieldDef.Type.STRING

Table 9-2 Primary Key Field Names

Primary Key Field Names
zipcode	lastname	first name	ssn

Table 9-3 Shard Key Field Names

Shard Key Field Names
zipcode

Upon examining this schema, one can see that the example rmvTable consists of rows of data the Registry of Motor Vehicles might maintain about vehicle owners who have registered a primary vehicle and (optionally) a second and maybe a third vehicle. In addition to personal information about each owner - such as name, address, gender, phone number(s), etc. - each row of data also contains an array in which each element of the array is a record whose contents consists of information about each vehicle the owner registers.

For example, in addition to vehicle attributes such as the make, model, color, etc., the record will also contain the vehicle's assessed value, registration fee (the tax), and whether or not the owner has paid the fee. Although the table schema presented above may seem a bit contrived, it is intended to demonstrate a broad spectrum of data types from the Oracle NoSQL Database table API.

To create a table with the above schema, the preferred approach is to employ the table Data Definition Language (DDL) (see Table Data Definition Language Overview), rather than entering individual commands in the store's admin CLI. To accomplish this, you can follow the instructions presented in the following sections to compile and execute the LoadRmvTable program, which will populate the desired table after using the DDL to create it.