1/33

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Text Conventions
• Syntax Conventions

Changes in This Release for Oracle Big Data Connectors User's Guide

• Changes in Oracle Big Data Connectors Release 4 (4.0)

Part I Setup

1 Getting Started with Oracle Big Data Connectors

• 1.1 About Oracle Big Data Connectors
• 1.2 Big Data Concepts and Technologies
  • 1.2.1 What is MapReduce?
  • 1.2.2 What is Apache Hadoop?
• 1.3 Downloading the Oracle Big Data Connectors Software
• 1.4 Oracle SQL Connector for Hadoop Distributed File System Setup
  • 1.4.1 Software Requirements
  • 1.4.2 Installing and Configuring a Hadoop Client on the Oracle Database System
  • 1.4.3 Installing Oracle SQL Connector for HDFS
  • 1.4.4 Granting User Privileges in Oracle Database
  • 1.4.5 Setting Up User Accounts on the Oracle Database System
  • 1.4.6 Using Oracle SQL Connector for HDFS on a Secure Hadoop Cluster
• 1.5 Oracle Loader for Hadoop Setup
  • 1.5.1 Software Requirements
  • 1.5.2 Installing Oracle Loader for Hadoop
  • 1.5.3 Providing Support for Offline Database Mode
  • 1.5.4 Using Oracle Loader for Hadoop on a Secure Hadoop Cluster
• 1.6 Oracle XQuery for Hadoop Setup
  • 1.6.1 Software Requirements
  • 1.6.2 Installing Oracle XQuery for Hadoop
  • 1.6.3 Troubleshooting the File Paths
  • 1.6.4 Configuring Oozie for the Oracle XQuery for Hadoop Action
• 1.7 Oracle R Advanced Analytics for Hadoop Setup
  • 1.7.1 Installing the Software on Hadoop
    • 1.7.1.1 Software Requirements for a Third-Party Hadoop Cluster
    • 1.7.1.2 Installing Sqoop on a Third-Party Hadoop Cluster
    • 1.7.1.3 Installing Hive on a Third-Party Hadoop Cluster
    • 1.7.1.4 Installing R on a Third-Party Hadoop Cluster
    • 1.7.1.5 Installing the ORCH Package on a Third-Party Hadoop Cluster
  • 1.7.2 Installing Additional R Packages
  • 1.7.3 Providing Remote Client Access to R Users
    • 1.7.3.1 Software Requirements for Remote Client Access
    • 1.7.3.2 Configuring the Server as a Hadoop Client
    • 1.7.3.3 Installing Sqoop on a Hadoop Client
    • 1.7.3.4 Installing R on a Hadoop Client
    • 1.7.3.5 Installing the ORCH Package on a Hadoop Client
    • 1.7.3.6 Installing the Oracle R Enterprise Client Packages (Optional)

Part II Oracle Database Connectors

2 Oracle SQL Connector for Hadoop Distributed File System

• 2.1 About Oracle SQL Connector for HDFS
• 2.2 Getting Started With Oracle SQL Connector for HDFS
• 2.3 Configuring Your System for Oracle SQL Connector for HDFS
• 2.4 Using Oracle SQL Connector for HDFS with Oracle Big Data Appliance and Oracle Exadata
• 2.5 Using the ExternalTable Command-Line Tool
  • 2.5.1 About ExternalTable
  • 2.5.2 ExternalTable Command-Line Tool Syntax
• 2.6 Creating External Tables
  • 2.6.1 Creating External Tables with the ExternalTable Tool
  • 2.6.2 Creating External Tables from Data Pump Format Files
    • 2.6.2.1 Required Properties
    • 2.6.2.2 Optional Properties
    • 2.6.2.3 Defining Properties in XML Files for Data Pump Format Files
    • 2.6.2.4 Example
  • 2.6.3 Creating External Tables from Hive Tables
    • 2.6.3.1 Hive Table Requirements
    • 2.6.3.2 Data Type Mappings
    • 2.6.3.3 Required Properties
    • 2.6.3.4 Optional Properties
    • 2.6.3.5 Defining Properties in XML Files for Hive Tables
    • 2.6.3.6 Example
  • 2.6.4 Creating External Tables from Partitioned Hive Tables
    • 2.6.4.1 Database Objects that Support Access to Partitioned Hive Tables
    • 2.6.4.2 Querying the Metadata Table
    • 2.6.4.3 Creating UNION ALL Views for Querying
    • 2.6.4.4 Error Messages
    • 2.6.4.5 Dropping Dangling Objects
  • 2.6.5 Creating External Tables from Delimited Text Files
    • 2.6.5.1 Data Type Mappings
    • 2.6.5.2 Required Properties
    • 2.6.5.3 Optional Properties
    • 2.6.5.4 Defining Properties in XML Files for Delimited Text Files
    • 2.6.5.5 Example
  • 2.6.6 Creating External Tables in SQL
• 2.7 Publishing the HDFS Data Paths
  • 2.7.1 ExternalTable Syntax for Publish
  • 2.7.2 ExternalTable Example for Publish
• 2.8 Exploring External Tables and Location Files
  • 2.8.1 ExternalTable Syntax for Describe
  • 2.8.2 ExternalTable Example for Describe
• 2.9 Dropping Database Objects Created by Oracle SQL Connector for HDFS
  • 2.9.1 ExternalTable Syntax for Drop
  • 2.9.2 ExternalTable Example for Drop
• 2.10 More About External Tables Generated by the ExternalTable Tool
  • 2.10.1 About Configurable Column Mappings
    • 2.10.1.1 Default Column Mappings
    • 2.10.1.2 All Column Overrides
    • 2.10.1.3 One Column Overrides
    • 2.10.1.4 Mapping Override Examples
  • 2.10.2 What Are Location Files?
  • 2.10.3 Enabling Parallel Processing
    • 2.10.3.1 Setting Up the Degree of Parallelism
  • 2.10.4 Location File Management
  • 2.10.5 Location File Names
• 2.11 Configuring Oracle SQL Connector for HDFS
  • 2.11.1 Creating a Configuration File
  • 2.11.2 Oracle SQL Connector for HDFS Configuration Property Reference
• 2.12 Performance Tips for Querying Data in HDFS

3 Oracle Loader for Hadoop

• 3.1 What Is Oracle Loader for Hadoop?
• 3.2 About the Modes of Operation
  • 3.2.1 Online Database Mode
  • 3.2.2 Offline Database Mode
• 3.3 Getting Started With Oracle Loader for Hadoop
• 3.4 Creating the Target Table
  • 3.4.1 Supported Data Types for Target Tables
  • 3.4.2 Supported Partitioning Strategies for Target Tables
  • 3.4.3 Compression
• 3.5 Creating a Job Configuration File
• 3.6 About the Target Table Metadata
  • 3.6.1 Providing the Connection Details for Online Database Mode
  • 3.6.2 Generating the Target Table Metadata for Offline Database Mode
    • 3.6.2.1 OraLoaderMetadata Utility
• 3.7 About Input Formats
  • 3.7.1 Delimited Text Input Format
    • 3.7.1.1 About DelimitedTextInputFormat
    • 3.7.1.2 Required Configuration Properties
    • 3.7.1.3 Optional Configuration Properties
  • 3.7.2 Complex Text Input Formats
    • 3.7.2.1 About RegexInputFormat
    • 3.7.2.2 Required Configuration Properties
    • 3.7.2.3 Optional Configuration Properties
  • 3.7.3 Hive Table Input Format
    • 3.7.3.1 About HiveToAvroInputFormat
    • 3.7.3.2 Required Configuration Properties
    • 3.7.3.3 Optional Configuration Properties
  • 3.7.4 Avro Input Format
    • 3.7.4.1 Configuration Properties
  • 3.7.5 Oracle NoSQL Database Input Format
    • 3.7.5.1 About KVAvroInputFormat
    • 3.7.5.2 Required Configuration Properties
  • 3.7.6 Custom Input Formats
    • 3.7.6.1 About Implementing a Custom Input Format
    • 3.7.6.2 About Error Handling
    • 3.7.6.3 Supporting Data Sampling
    • 3.7.6.4 InputFormat Source Code Example
• 3.8 Mapping Input Fields to Target Table Columns
  • 3.8.1 Automatic Mapping
  • 3.8.2 Manual Mapping
  • 3.8.3 Converting a Loader Map File
• 3.9 About Output Formats
  • 3.9.1 JDBC Output Format
    • 3.9.1.1 About JDBCOutputFormat
    • 3.9.1.2 Configuration Properties
  • 3.9.2 Oracle OCI Direct Path Output Format
    • 3.9.2.1 About OCIOutputFormat
    • 3.9.2.2 Configuration Properties
  • 3.9.3 Delimited Text Output Format
    • 3.9.3.1 About DelimitedTextOutputFormat
    • 3.9.3.2 Configuration Properties
  • 3.9.4 Oracle Data Pump Output Format
    • 3.9.4.1 About DataPumpOutputFormat
• 3.10 Running a Loader Job
  • 3.10.1 Specifying Hive Input Format JAR Files
  • 3.10.2 Specifying Oracle NoSQL Database Input Format JAR Files
  • 3.10.3 Job Reporting
• 3.11 Handling Rejected Records
  • 3.11.1 Logging Rejected Records in Bad Files
  • 3.11.2 Setting a Job Reject Limit
• 3.12 Balancing Loads When Loading Data into Partitioned Tables
  • 3.12.1 Using the Sampling Feature
  • 3.12.2 Tuning Load Balancing
  • 3.12.3 Tuning Sampling Behavior
  • 3.12.4 When Does Oracle Loader for Hadoop Use the Sampler's Partitioning Scheme?
  • 3.12.5 Resolving Memory Issues
  • 3.12.6 What Happens When a Sampling Feature Property Has an Invalid Value?
• 3.13 Optimizing Communications Between Oracle Engineered Systems
• 3.14 Oracle Loader for Hadoop Configuration Property Reference
• 3.15 Third-Party Licenses for Bundled Software
  • 3.15.1 Apache Licensed Code
  • 3.15.2 Apache Avro 1.7.3
  • 3.15.3 Apache Commons Mathematics Library 2.2
  • 3.15.4 Apache Hadoop 0.20.0
  • 3.15.5 Jackson JSON 1.8.8

Part III Oracle XQuery for Hadoop

4 Using Oracle XQuery for Hadoop

• 4.1 What Is Oracle XQuery for Hadoop?
• 4.2 Getting Started With Oracle XQuery for Hadoop
  • 4.2.1 Basic Steps
  • 4.2.2 Example: Hello World!
• 4.3 About the Oracle XQuery for Hadoop Functions
  • 4.3.1 About the Adapters
  • 4.3.2 About Other Modules for Use With Oracle XQuery for Hadoop
• 4.4 Creating an XQuery Transformation
  • 4.4.1 XQuery Transformation Requirements
  • 4.4.2 About XQuery Language Support
  • 4.4.3 Accessing Data in the Hadoop Distributed Cache
  • 4.4.4 Calling Custom Java Functions from XQuery
  • 4.4.5 Accessing User-Defined XQuery Library Modules and XML Schemas
  • 4.4.6 XQuery Transformation Examples
• 4.5 Running Queries
  • 4.5.1 Oracle XQuery for Hadoop Options
  • 4.5.2 Generic Options
  • 4.5.3 About Running Queries Locally
• 4.6 Running Queries from Apache Oozie
  • 4.6.1 Getting Started Using the Oracle XQuery for Hadoop Oozie Action
  • 4.6.2 Supported XML Elements
  • 4.6.3 Example: Hello World
• 4.7 Oracle XQuery for Hadoop Configuration Properties
• 4.8 Third-Party Licenses for Bundled Software
  • 4.8.1 Apache Licensed Code
  • 4.8.2 ANTLR 3.2
  • 4.8.3 Apache Ant 1.7.1
  • 4.8.4 Apache Xerces 2.9.1
  • 4.8.5 Apache XMLBeans 2.3, 2.5
  • 4.8.6 Jackson 1.8.8
  • 4.8.7 Woodstox XML Parser 4.2.0

5 Oracle XQuery for Hadoop Reference

• Avro File Adapter
  • Built-in Functions for Reading Avro Files
    • avro:collection-avroxml
    • avro:get
  • Custom Functions for Reading Avro Container Files
  • Custom Functions for Writing Avro Files
  • Examples of Avro File Adapter Functions
  • About Converting Values Between Avro and XML
    • Reading Avro as XML
    • Writing XML as Avro
• JSON File Adapter
  • Built-in Functions for Reading JSON
    • json:collection-jsonxml
    • json:parse-as-xml
    • json:get
  • Custom Functions for Reading JSON Files
  • Examples of JSON Functions
  • JSON File Adapter Configuration Properties
  • About Converting JSON Data Formats to XML
    • About Converting JSON Objects to XML
    • About Converting JSON Arrays to XML
    • About Converting Other JSON Types
• Oracle Database Adapter
  • Custom Functions for Writing to Oracle Database
  • Examples of Oracle Database Adapter Functions
  • Oracle Loader for Hadoop Configuration Properties and Corresponding %oracle-property Annotations
• Oracle NoSQL Database Adapter
  • Prerequisites for Using the Oracle NoSQL Database Adapter
  • Built-in Functions for Reading from and Writing to Oracle NoSQL Database
    • kv:collection-text
    • kv:collection-avroxml
    • kv:collection-xml
    • kv:collection-binxml
    • kv:put-text
    • kv:put-xml
    • kv:put-binxml
    • kv:get-text
    • kv:get-avroxml
    • kv:get-xml
    • kv:get-binxml
    • kv:key-range
    • kv:key-range
  • Custom Functions for Reading Values from Oracle NoSQL Database
  • Custom Functions for Retrieving Single Values from Oracle NoSQL Database
  • Custom Functions for Writing to Oracle NoSQL Database
  • Examples of Oracle NoSQL Database Adapter Functions
  • Oracle NoSQL Database Adapter Configuration Properties
• Sequence File Adapter
  • Built-in Functions for Reading and Writing Sequence Files
    • seq:collection
    • seq:collection-xml
    • seq:collection-binxml
    • seq:put
    • seq:put-xml
    • seq:put-binxml
  • Custom Functions for Reading Sequence Files
  • Custom Functions for Writing Sequence Files
  • Examples of Sequence File Adapter Functions
• Solr Adapter
  • Prerequisites for Using the Solr Adapter
    • Configuration Settings
    • Example Query Using the Solr Adapter
  • Built-in Functions for Loading Data into Solr Servers
    • solr:put
  • Custom Functions for Loading Data into Solr Servers
  • Examples of Solr Adapter Functions
  • Solr Adapter Configuration Properties
• Text File Adapter
  • Built-in Functions for Reading and Writing Text Files
    • text:collection
    • text:collection-xml
    • text:put
    • text:put-xml
    • text:trace
  • Custom Functions for Reading Text Files
  • Custom Functions for Writing Text Files
  • Examples of Text File Adapter Functions
• XML File Adapter
  • Built-in Functions for Reading XML Files
    • xmlf:collection (Single Task)
    • xmlf:collection (Multiple Tasks)
  • Custom Functions for Reading XML Files
  • Examples of XML File Adapter Functions
• Utility Module
  • Duration, Date, and Time Functions
    • ora-fn:date-from-string-with-format
    • ora-fn:date-to-string-with-format
    • ora-fn:dateTime-from-string-with-format
    • ora-fn:dateTime-to-string-with-format
    • ora-fn:time-from-string-with-format
    • ora-fn:time-to-string-with-format
    • Format Argument
    • Locale Argument
  • String Functions
    • ora-fn:pad-left
    • ora-fn:pad-right
    • ora-fn:trim
    • ora-fn:trim-left
    • ora-fn:trim-right
• Hadoop Module
  • Built-in Functions for Using Hadoop
    • oxh:find
    • oxh:increment-counter
    • oxh:println
    • oxh:println-xml
    • oxh:property
• Serialization Annotations

6 Oracle XML Extensions for Hive

• 6.1 What are the XML Extensions for Hive?
• 6.2 Using the Hive Extensions
• 6.3 About the Hive Functions
• 6.4 Creating XML Tables
  • 6.4.1 Hive CREATE TABLE Syntax for XML Tables
  • 6.4.2 CREATE TABLE Configuration Properties
  • 6.4.3 CREATE TABLE Examples
    • 6.4.3.1 Syntax Example
    • 6.4.3.2 Simple Examples
    • 6.4.3.3 OpenStreetMap Examples
• Oracle XML Functions for Hive Reference
  • Data Type Conversions
  • Hive Access to External Files
• Online Documentation of Functions
• xml_exists
• xml_query
• xml_query_as_primitive
• xml_table

Part IV Oracle R Advanced Analytics for Hadoop

7 Using Oracle R Advanced Analytics for Hadoop

• 7.1 About Oracle R Advanced Analytics for Hadoop
  • 7.1.1 Oracle R Advanced Analytics for Hadoop Architecture
  • 7.1.2 Oracle R Advanced Analytics for Hadoop packages and functions
  • 7.1.3 Oracle R Advanced Analytics for Hadoop APIs
  • 7.1.4 Inputs to Oracle R Advanced Analytics for Hadoop
• 7.2 Access to HDFS Files
• 7.3 Access to Apache Hive
  • 7.3.1 ORCH Functions for Hive
  • 7.3.2 ORE Functions for Hive
  • 7.3.3 Generic R Functions Supported in Hive
  • 7.3.4 Support for Hive Data Types
  • 7.3.5 Usage Notes for Hive Access
  • 7.3.6 Example: Loading Hive Tables into Oracle R Advanced Analytics for Hadoop
• 7.4 Access to Oracle Database
  • 7.4.1 Usage Notes for Oracle Database Access
  • 7.4.2 Scenario for Using Oracle R Advanced Analytics for Hadoop with Oracle R Enterprise
• 7.5 Oracle R Advanced Analytics for Hadoop Functions
  • 7.5.1 Native Analytical Functions
  • 7.5.2 Using the Hadoop Distributed File System (HDFS)
  • 7.5.3 Using Apache Hive
  • 7.5.4 Using Aggregate Functions in Hive
  • 7.5.5 Making Database Connections
  • 7.5.6 Copying Data and Working with HDFS Files
  • 7.5.7 Converting to R Data Types
  • 7.5.8 Using MapReduce
  • 7.5.9 Debugging Scripts
• 7.6 Demos of Oracle R Advanced Analytics for Hadoop Functions
• 7.7 Security Notes for Oracle R Advanced Analytics for Hadoop

Index