3 Using Oracle Big Data SQL for Data Access

Verify that the following files reside in the examples/hadoop/table directory:

create_vehicle_table.kvs
CountTableRows.java
LoadVehicleTable.java

This example runs on a Hadoop cluster node named some1node07 and uses a KVStore named SOME1KV.

To create and populate the sample table in Oracle NoSQL Database:

1. Open a connection to an Oracle NoSQL Database node on your Hadoop cluster.

2. Create a table named vehicleTable. The following example uses the load command to run the commands in create_vehicle_table.kvs:

   $ cd NOSQL_HOME
   $ java -jar lib/kvcli.jar -host some1node07 -port 5000 \
     load -file examples/hadoop/table/create_vehicle_table.kvs
   

3. Compile LoadVehicleTable.java:

   $ javac -cp examples:lib/kvclient.jar examples/hadoop/table/LoadVehicleTable.java
   

4. Execute the LoadVehicleTable class to populate the table:

   $ java -cp examples:lib/kvclient.jar hadoop.table.LoadVehicleTable -host some1node07 -port 5000 -store SOME1KV
   {"type":"auto","make":"Chrysler","model":"PTCruiser","class":"4WheelDrive","colo
   r":"white","price":20743.240234375,"count":30}
   {"type":"suv","make":"Ford","model":"Escape","class":"FrontWheelDrive","color":"
        .
        .
        .
   10 new records added
   

The vehicleTable table contains the following fields:

The following example creates a Hive table named VEHICLES that accesses vehicleTable in the SOME1KV KVStore. In this example, the system is configured with a Hadoop cluster in the first six servers (some1node01 to some1node06) and an Oracle NoSQL Database cluster in the next three servers (some1node07 to some1node09).

CREATE EXTERNAL TABLE IF NOT EXISTS vehicles 
   (type STRING, 
    make STRING, 
    model STRING, 
    class STRING, 
    color STRING, 
    price DOUBLE, 
    count INT) 
COMMENT 'Accesses data in vehicleTable in the SOME1KV KVStore' 
STORED BY 'oracle.kv.hadoop.hive.table.TableStorageHandler' 
TBLPROPERTIES
  ("oracle.kv.kvstore" = "SOME1KV", 
   "oracle.kv.hosts" = "some1node07.example.com:5000,some1node08.example.com:5000",
   "oracle.kv.hadoop.hosts" = "some1node01.example.com,some1node02.example.com,some1node03.example.com,some1node04.example.com,some1node05.example.com,some1node06.example.com", 
   "oracle.kv.tableName" = "vehicleTable");

The DESCRIBE command lists the columns in the VEHICLES table:

hive> DESCRIBE vehicles;
OK
type                    string                  from deserializer
make                    string                  from deserializer
model                   string                  from deserializer
class                   string                  from deserializer
color                   string                  from deserializer
price                   double                  from deserializer
count                   int                     from deserializer

A query against the Hive VEHICLES table returns data from the Oracle NoSQL vehicleTable table:

hive> SELECT make, model, class
      FROM vehicletable
      WHERE type='truck' AND color='red'
      ORDER BY make, model;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
     .
     .
     .
Chrysler       Ram1500         RearWheelDrive
Chrysler       Ram2500         FrontWheelDrive
Ford           F150            FrontWheelDrive
Ford           F250            RearWheelDrive
Ford           F250            AllWheelDrive
Ford           F350            RearWheelDrive
GM             Sierra          AllWheelDrive
GM             Silverado1500   RearWheelDrive
GM             Silverado1500   AllWheelDrive

After you create the Hive table, the metadata is available in the Oracle Database static data dictionary views. The following SQL SELECT statement returns information about the Hive table created in the previous topic:

SQL> SELECT table_name, column_name, hive_column_type 
     FROM all_hive_columns 
     WHERE table_name='vehicles';
TABLE_NAME      COLUMN_NAME  HIVE_COLUMN_TYPE
--------------- ------------ ----------------
vehicles        type         string
vehicles        make         string
vehicles        model        string
vehicles        class        string
vehicles        color        string
vehicles        price        double
vehicles        count        int

The next SQL CREATE TABLE statement generates an external table named VEHICLES over the Hive VEHICLES table, using the ORACLE_HIVE access driver. The name of the table in Oracle Database must be identical to the name of the table in Hive. However, both Oracle NoSQL Database and Oracle Database are case insensitive.

CREATE TABLE vehicles
  (type  VARCHAR2(10), make  VARCHAR2(12), model VARCHAR2(20), 
   class VARCHAR2(40), color VARCHAR2(20), price NUMBER(8,2), 
   count NUMBER) 
  ORGANIZATION EXTERNAL 
    (TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR 
       ACCESS PARAMETERS 
         (com.oracle.bigdata.debug=true com.oracle.bigdata.log.opt=normal)) 
    REJECT LIMIT UNLIMITED;

This SQL SELECT statement retrieves all rows for red trucks from vehicleTable in Oracle NoSQL Database:

SQL> SELECT make, model, class 
     FROM vehicles
     WHERE type='truck' AND color='red'
     ORDER BY make, model;
MAKE         MODEL                CLASS
------------ -------------------- ---------------------
Chrysler     Ram1500              RearWheelDrive
Chrysler     Ram2500              FrontWheelDrive
Ford         F150                 FrontWheelDrive
Ford         F250                 AllWheelDrive
Ford         F250                 RearWheelDrive
Ford         F350                 RearWheelDrive
GM           Sierra               AllWheelDrive
GM           Silverado1500        RearWheelDrive
GM           Silverado1500        4WheelDrive
GM           Silverado1500        AllWheelDrive

The following example is equivalent to the one shown in "Overriding the Default ORACLE_HIVE Settings". The external table access a delimited text file stored in HDFS.

CREATE TABLE order (cust_num VARCHAR2(10), 
                    order_num VARCHAR2(20), 
                    order_date DATE,
                    item_cnt NUMBER,
                    description VARCHAR2(100),
                    order_total NUMBER(8,2)) 
   ORGANIZATION EXTERNAL 
   (
     TYPE oracle_hdfs
     DEFAULT DIRECTORY DEFAULT_DIR  
           ACCESS PARAMETERS 
                 (
        com.oracle.bigdata.colmap: {"col":"item_cnt", "field":"order_line_item_count"}
        com.oracle.bigdata.overflow: {"action":"TRUNCATE", "col":"DESCRIPTION"}
        com.oracle.bigdata.erroropt: [{"action":"replace", \
                                         "value":"INVALID NUM", \
                                         "col":["CUST_NUM","ORDER_NUM"]} , \
                                         {"action":"reject", "col":"ORDER_TOTAL}]
     )
 LOCATION ('hdfs:/usr/cust/summary/*'));

The parameters make the following changes in the way that the ORACLE_HDFS access driver locates the data and handles error conditions:

• com.oracle.bigdata.colmap: Handles differences in column names. ORDER_LINE_ITEM_COUNT in the HDFS files matches the ITEM_CNT column in the external table.

• com.oracle.bigdata.overflow: Truncates string data. Values longer than 100 characters for the DESCRIPTION column are truncated.

• com.oracle.bigdata.erroropt: Replaces bad data. Errors in the data for CUST_NUM or ORDER_NUM set the value to INVALID_NUM.

The TYPE clause identifies the access driver. The type of access driver determines how the other parts of the external table definition are interpreted.

Specify one of the following values for Oracle Big Data SQL:

• ORACLE_HDFS: Accesses files in an HDFS directory.

• ORACLE_HIVE: Accesses a Hive table.

The DEFAULT DIRECTORY clause identifies an Oracle Database directory object. The directory object identifies an operating system directory with files that the external table reads and writes.

ORACLE_HDFS and ORACLE_HIVE use the default directory solely to write log files on the Oracle Database system.

The LOCATION clause identifies the data source.

The LOCATION clause for ORACLE_HDFS contains a comma-separated list of file locations. The files must reside in the HDFS file system on the default cluster.

A location can be any of the following:

• A fully qualified HDFS name, such as /user/hive/warehouse/hive_seed/hive_types. ORACLE_HDFS uses all files in the directory.

• A fully qualified HDFS file name, such as /user/hive/warehouse/hive_seed/hive_types/hive_types.csv

• A URL for an HDFS file or a set of files, such as hdfs:/user/hive/warehouse/hive_seed/hive_types/*. Just a directory name is invalid.

The file names can contain any pattern-matching character described in Table 3-2.

Do not specify the LOCATION clause for ORACLE_HIVE; it raises an error. The data is stored in Hive, and the access parameters and the metadata store provide the necessary information.

Limits the number of conversion errors permitted during a query of the external table before Oracle Database stops the query and returns an error.

Any processing error that causes a row to be rejected counts against the limit. The reject limit applies individually to each parallel query (PQ) process. It is not the total of all rejected rows for all PQ processes.

The ACCESS PARAMETERS clause provides information that the access driver needs to load the data correctly into the external table. See "CREATE TABLE ACCESS PARAMETERS Clause".

The bigdata.properties file in the common directory contains property-value pairs that define the Java class paths and native library paths required for accessing data in HDFS.

These properties must be set:

• bigdata.cluster.default

• java.classpath.hadoop

• java.classpath.hive

• java.classpath.oracle

The following list describes all properties permitted in bigdata.properties.

bigdata.properties

Xmx2048m,-verbose=jni

Example 3-1 shows a sample bigdata.properties file.

The bigdata-log4j.properties file in the common directory defines the logging behavior of queries against external tables in the Java code. Any log4j properties are allowed in this file.

Example 3-2 shows a sample bigdata-log4j.properties file with the relevant log4j properties.