1/11

Contents

Title and Copyright Information

Preface

• Audience
• Related Documents
• Conventions
• Backus-Naur Form Syntax
• Changes in Oracle Big Data SQL 3.0.1
• Changes in Oracle Big Data SQL 3.0

1 Introducing Oracle Big Data SQL

• 1.1 What Is Oracle Big Data SQL?
  • 1.1.1 About Oracle External Tables
  • 1.1.2 About Predicate Push Down
  • 1.1.3 About the Access Drivers for Oracle Big Data SQL
  • 1.1.4 About Smart Scan Technology
  • 1.1.5 About Storage Indexes

2 Installing Oracle Big Data SQL

• 2.1 Oracle Big Data SQL Compatibility Matrix
• 2.2 Installing On Oracle Big Data Appliance and the Oracle Exadata Database Machine
  • 2.2.1 Performing the Installation
  • 2.2.2 Running the Post-Installation Script for Oracle Big Data SQL
    • 2.2.2.1 Running the bds-exa-install Script
    • 2.2.2.2 bds-exa-install Syntax
    • 2.2.2.3 Troubleshooting Running bds-exa-install Script
  • 2.2.3 About Data Security with Oracle Big Data SQL
  • 2.2.4 Enabling Oracle Big Data SQL Access to a Kerberized Cluster
  • 2.2.5 Starting and Stopping the Big Data SQL Agent
• 2.3 Installing Oracle Big Data SQL on Other Hadoop Systems
  • 2.3.1 Downloading Oracle Big Data SQL
  • 2.3.2 Prerequisites for Installing on an HDP Cluster
  • 2.3.3 Prerequisites for Installing on a CDH Cluster
  • 2.3.4 Installation Overview
  • 2.3.5 Installing on the Hadoop Cluster Management Server
  • 2.3.6 Creating the Database-Side Installation Bundle
  • 2.3.7 Installing on the Oracle Database Server
  • 2.3.8 Uninstalling Oracle Big Data SQL
  • 2.3.9 Securing Big Data SQL
    • 2.3.9.1 Big Data SQL Communications and Secure Hadoop Clusters
    • 2.3.9.2 Setting up Oracle Big Data SQL and Oracle Secure External Password Store

3 Using Oracle Big Data SQL for Data Access

• 3.1 Creating an Oracle External Table for Hive Data
  • 3.1.1 Obtaining Information About a Hive Table
  • 3.1.2 Using the CREATE_EXTDDL_FOR_HIVE Function
  • 3.1.3 Developing a CREATE TABLE Statement for ORACLE_HIVE
    • 3.1.3.1 Using the Default ORACLE_HIVE Settings
    • 3.1.3.2 Overriding the Default ORACLE_HIVE Settings
• 3.2 Creating an Oracle External Table for Oracle NoSQL Database
  • 3.2.1 Creating a Hive External Table for Oracle NoSQL Database
  • 3.2.2 Creating the Oracle Database Table for Oracle NoSQL Data
  • 3.2.3 About Column Data Type Mappings
  • 3.2.4 Example of Accessing Data in Oracle NoSQL Database
    • 3.2.4.1 Creating the Oracle NoSQL Database Example Table
    • 3.2.4.2 Creating the Example Hive Table for vehicleTable
    • 3.2.4.3 Creating the Oracle Table for VEHICLES
• 3.3 Creating an Oracle External Table for Apache HBase
  • 3.3.1 Creating a Hive External Table for HBase
  • 3.3.2 Creating the Oracle Database Table for HBase
• 3.4 Creating an Oracle External Table for HDFS Files
  • 3.4.1 Using the Default Access Parameters with ORACLE_HDFS
  • 3.4.2 Overriding the Default ORACLE_HDFS Settings
    • 3.4.2.1 Accessing a Delimited Text File
    • 3.4.2.2 Accessing Avro Container Files
• 3.5 About the SQL CREATE TABLE Statement
  • 3.5.1 Basic Syntax
  • 3.5.2 About the External Table Clause
    • 3.5.2.1 TYPE Clause
    • 3.5.2.2 DEFAULT DIRECTORY Clause
    • 3.5.2.3 LOCATION Clause
    • 3.5.2.4 ORACLE_HDFS LOCATION Clause
    • 3.5.2.5 ORACLE_HIVE LOCATION Clause
    • 3.5.2.6 REJECT LIMIT Clause
    • 3.5.2.7 ACCESS PARAMETERS Clause
• 3.6 About Data Type Conversions
• 3.7 Querying External Tables
  • 3.7.1 Granting User Access
  • 3.7.2 About Error Handling
  • 3.7.3 About the Log Files
• 3.8 About Oracle Big Data SQL on the Database Server (Oracle Exadata Machine or Other)
  • 3.8.1 About the Common Directory
  • 3.8.2 Common Configuration Properties
    • 3.8.2.1 bigdata.properties
    • 3.8.2.2 bigdata-log4j.properties
  • 3.8.3 About the Cluster Directory
  • 3.8.4 About Permissions

4 Copying Oracle Tables to Hadoop

• 4.1 What Is Copy to Hadoop?
• 4.2 Getting Started Using Copy to Hadoop
• 4.3 Installing Copy to Hadoop
  • 4.3.1 Prerequisites for Copy to Hadoop
  • 4.3.2 Installing Copy to Hadoop in an Oracle Big Data Appliance/Oracle Exadata Database Machine Environment
    • 4.3.2.1 Installing Copy to Hadoop on Oracle Big Data Appliance
    • 4.3.2.2 Installing Copy to Hadoop on Oracle Exadata Database Machine
  • 4.3.3 Installing Copy to Hadoop on Other Systems
    • 4.3.3.1 Installing Copy to Hadoop on a Hadoop Cluster (Other than an Oracle Big Data Database Appliance Cluster)
    • 4.3.3.2 Installing Copy to Hadoop on an Oracle Database Server (Other Than Oracle Exadata Database Machine)
• 4.4 Generating the Data Pump Files
  • 4.4.1 About Data Pump Format Files
  • 4.4.2 Identifying the Target Directory
  • 4.4.3 About the CREATE TABLE Syntax
  • 4.4.4 Copying the Files to HDFS
• 4.5 Creating a Hive Table
  • 4.5.1 About Hive External Tables
  • 4.5.2 About Column Mappings
  • 4.5.3 About Data Type Conversions
• 4.6 Example Using the Sample Schemas
  • 4.6.1 About the Sample Data
  • 4.6.2 Creating the EXPDIR Database Directory
  • 4.6.3 Creating Data Pump Format Files for Customer Data
    • 4.6.3.1 CREATE TABLE Example With a Simple SELECT Statement
    • 4.6.3.2 CREATE TABLE Example With a More Complex SQL SELECT Statement
  • 4.6.4 Verifying the Contents of the Data Files
  • 4.6.5 Copying the Files into Hadoop
  • 4.6.6 Creating a Hive External Table
  • 4.6.7 Querying the Data in Hive
• 4.7 Using Oracle Shell for Hadoop Loaders With Copy to Hadoop
  • 4.7.1 Introduction to Oracle Shell for Hadoop Loaders
  • 4.7.2 Oracle Shell for Hadoop Loaders Setup

5 Oracle Big Data SQL Reference

• 5.1 DBMS_HADOOP PL/SQL Package
  • 5.1.1 CREATE_EXTDDL_FOR_HIVE
    • 5.1.1.1 Example
• 5.2 CREATE TABLE ACCESS PARAMETERS Clause
  • 5.2.1 Syntax Rules for Specifying Properties
  • 5.2.2 ORACLE_HDFS Access Parameters
    • 5.2.2.1 Default Parameter Settings for ORACLE_HDFS
    • 5.2.2.2 Optional Parameter Settings for ORACLE_HDFS
  • 5.2.3 ORACLE_HIVE Access Parameters
    • 5.2.3.1 Default Parameter Settings for ORACLE_HIVE
    • 5.2.3.2 Optional Parameter Values for ORACLE_HIVE
  • 5.2.4 com.oracle.bigdata.buffersize
  • 5.2.5 com.oracle.bigdata.colmap
  • 5.2.6 com.oracle.bigdata.datamode
  • 5.2.7 com.oracle.bigdata.erroropt
  • 5.2.8 com.oracle.bigdata.fields
  • 5.2.9 com.oracle.bigdata.fileformat
  • 5.2.10 com.oracle.bigdata.log.exec
  • 5.2.11 com.oracle.bigdata.log.qc
  • 5.2.12 com.oracle.bigdata.overflow
  • 5.2.13 com.oracle.bigdata.rowformat
  • 5.2.14 com.oracle.bigdata.tablename
• 5.3 Static Data Dictionary Views for Hive
  • 5.3.1 ALL_HIVE_DATABASES
  • 5.3.2 ALL_HIVE_TABLES
  • 5.3.3 ALL_HIVE_COLUMNS
  • 5.3.4 DBA_HIVE_DATABASES
  • 5.3.5 DBA_HIVE_TABLES
  • 5.3.6 DBA_HIVE_COLUMNS
  • 5.3.7 USER_HIVE_DATABASES
  • 5.3.8 USER_HIVE_TABLES
  • 5.3.9 USER_HIVE_COLUMNS

Appendices

A Licensing Information

• A.1 Oracle Big Data SQL Licensing
  • A.1.1 ANTLR 4.5.2
  • A.1.2 JLine 2.13
  • A.1.3 Apache Commons Exec 1.3
  • A.1.4 Apache License

Index