Example: Hive Queries On Oracle NoSQL Database Tables

8 Example: Hive Queries On Oracle NoSQL Database Tables

This section presents examples of how Hive can be configured to query data stored in different tables located in an Oracle NoSQL Database store, either non-secure or secure. The Primitive Data Types - The vehicleTable Example contains only primitive Oracle NoSQL Database data types, and is a good place to start when investigating basic Hive queries. The Non-Primitive Data Types - The rmvTable Example contains a mix of primitive and non-primitive data types, and demonstrates how to query more complex data. Finally, the NoSQL JSON Data Type - The exampleJsonTable Example focuses on how to query JSON documents that have been written to an Oracle NoSQL Database.

Before running any of the queries described here, you must take the following initial steps to setup your system for Hive integration with Oracle NoSQL Database:

Satisfy the necessary prerequisites, see Prerequisites.
Follow the directions presented in the Deploying a Non-Secure Store appendix or Deploying a Secure Store appendix to deploy either a non-secure or a secure Oracle NoSQL Database store.
Follow the instructions presented in the CountTableRows Support Programs appendix to create and populate a table named vehicleTable in the store that you deployed.
Follow the instructions presented in the Creating and Populating the rmvTable appendix to create and populate a table named rmvTable in the store that you deployed.
Follow the instructions presented in the Creating and Populating the exampleJsonTable appendix to create and populate a table named exampleJsonTable in the store that you deployed.
Follow the instructions presented in the Configuring the Hive Client Environment appendix to configure the Hive client environment so that it has access to the Oracle NoSQL Database libraries needed to query data stored in the Oracle NoSQL Database store you deployed.
If the store you deployed is configured for secure access, then follow the steps provided in the Hive and Oracle NoSQL Database Security appendix to configure Hive with the environment and artifacts necessary to interact with a secure store.

Once these initial steps are performed, the sections that follow present Hive commands for creating and mapping Hive external tables to the tables you created in the Oracle NoSQL Database, and then demonstrate how to use Hive to query the data stored in those tables.

Note the following general points about the Hive commands that are presented:

The contents of the Hive commands presented below are displayed on separate lines for readability. In practice, because the Hive command interpreter may have trouble handling multi-line commands, it is generally best to enter a single, continuous command with no line breaks.
When executing the command to create a Hive external table, the oracle.kv.tableName property is used to indicate to Hive the name of the table in the Oracle NoSQL Database store that will be queried; where the name specified for the Hive table is not required to be the same as name of the corresponding Oracle NoSQL Database table. We used a combination of both in the examples. In the cases where the names are different, we used a name that was descriptive of the scenario.
If the Oracle NoSQL Database store is configured with multiple administrative hosts, then any subset of the names of those hosts can be included in the value of the oracle.kv.hosts property specified in the command; as long as at least one valid administrative host and port is included.
With respect to the property named oracle.kv.hadoop.hosts:
- That property is currently optional for all systems except the Big Data SQL system.
- The property will have no effect if specified on a system that does not require it.
- When the property is specified on a Big Data SQL system or any other system for which the property is required, the property's value must contain the names of all of the data nodes making up the Hadoop cluster. See Big Data SQL User's Guide.

It's important to understand the different scenarios in which each Hive command is executed and how a given command differs in each scenario. This is because the command used to create a Hive external table mapped to an Oracle NoSQL Database table requires different parameters, where the parameters specified depend on which of the following conditions are met:

The Oracle NoSQL Database store is non-secure.
The Oracle NoSQL Database store is secure and your Hive client's password is:
- Stored in a password file.
- Stored in an Oracle Wallet.

To understand the difference between the non-secure scenario and the secure scenarios, it will help to compare the command used to map a Hive external table to a table in a non-secure store with the commands used to map two separate Hive tables to a single table in a secure store.

The respective commands in each scenario of a given example will apply the same Hive data model mapping, specified in Table 7-1, to create three different Hive external tables. Each table will have the same structure, schema, and attributes.

The only difference between the table created in the non-secure scenario, and the two tables created in the secure scenario, is the value specified for the Hive table name (for example, vehicleTable, vehicleTablePasswd, and vehicleTableWallet), and whether or not security artifacts needed for communication with a secure store are required to create the desired Hive table.

Specifically, when creating and mapping a Hive external table to a table in a secure Oracle NoSQL Database store, the TBLPROPERTIES directive of the Hive CREATE EXTERNAL TABLE command requires that you specify the following additional security-related properties:

oracle.kv.security
oracle.kv.auth.username
oracle.kv.auth.pwdfile.file or oracle.kv.auth.wallet.dir

Each of the properties listed above corresponds to one of the artifacts Oracle NoSQL Database requires for Hive to securely communicate with the store identified by the remaining properties specified in the Hive TBLPROPERTIES directive.

For details on the nature of each of the additional security related properties, refer to the Model For Building & Packaging Secure Clients appendix.

Other than the differences just described, with respect to the Hive commands presented in the following sections, the non-secure scenario and the secure scenario are the same in all other aspects.