4 Copying Oracle Tables to Hadoop

For network connections, supported Oracle Database levels, and other requirements, see the general requirements for installing Oracle Big Data SQL on your platform: Installing Oracle Big Data SQL.

You must have read and write access to a database directory in Oracle Database. Only Oracle Database users with the CREATE ANY DIRECTORY system privilege can create directories.

This example creates a database directory named EXPORTDIR that points to the /exportdir directory on the Oracle Database server (Oracle Exadata Database Machine or other):

SQL> CREATE DIRECTORY exportdir AS '/exportdir';

The following is the basic syntax of the CREATE TABLE statement for Data Pump format files:

CREATE TABLE table_name
   ORGANIZATION EXTERNAL (
     TYPE oracle_datapump
     DEFAULT DIRECTORY database_directory
     LOCATION ('filename1.dmp','filename2.dmp'...)
     ) PARALLEL n
   AS SELECT * FROM tablename;

DEFAULT DIRECTORY

Identifies the database directory that you created for this purpose. See "Identifying the Target Directory".

LOCATION

Lists the names of the Data Pump files to be created. The number of names should match the degree of parallelism (DOP) specified by the PARALLEL clause. Otherwise, the DOP drops to the number of files.

The number of files and the degree of parallelism affect the performance of Oracle Database when generating the Data Pump format files. They do not affect querying performance in Hive.

PARALLEL

Sets the degree of parallelism (DOP). Use the maximum number that your Oracle DBA permits you to use. By default the DOP is 1, which is serial processing. Larger numbers enable parallel processing.

AS SELECT

Use the full SQL SELECT syntax for this clause. It is not restricted. The tablename identifies the Oracle table to be copied to HDFS.

The Oracle Big Data SQL installation installs Hadoop client files on the Oracle Datatabase server (Oracle Exadata Database Machine or other). The Hadoop client installation enables you to use Hadoop commands to copy the Data Pump files to HDFS. You must have write privileges on the HDFS directory.

To copy the dmp files into HDFS, use the hadoop fs -put command. This example copies the files into the HDFS customers directory owned by the oracle user:

$ hadoop fs -put customers*.dmp /user/oracle/customers

These SQL statements create a local database directory named EXPDIR and grant access to the SH user:

SQL> CREATE DIRECTORY expdir AS '/expdir';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY expdir TO SH;
Grant succeeded.

The following examples show how to create the Data Pump files and check their contents.

Copy to Hadoop supports only the syntax shown in the examples. Data pump files created with the Export utility or Oracle Data Pump are not compatible.

You can check the content of the output data files before copying them to Hadoop. The previous CREATE TABLE statement created an external table named EXPORT_AMERICAS, which you can describe and query the same as any other table.

The DESCRIBE statement shows the selection of columns and the modified names:

SQL> DESCRIBE export_americas;
 Name                      Null?    Type
 ------------------------- -------- -----------------
 FIRST_NAME                NOT NULL VARCHAR2(20)
 LAST_NAME                 NOT NULL VARCHAR2(40)
 GENDER                    NOT NULL CHAR(1)
 BIRTH                     NOT NULL NUMBER(4)
 EMAIL                              VARCHAR2(50)
 POSTAL_CODE               NOT NULL VARCHAR2(10)
 COUNTRY                   NOT NULL VARCHAR2(40)

A SELECT statement like the following shows a sample of the data:

SELECT first_name, last_name, gender, birth, country 
   FROM export_americas 
   WHERE birth > 1985 
   ORDER BY last_name 
   FETCH FIRST 5 ROWS ONLY;

FIRST_NAME      LAST_NAME            GENDER      BIRTH COUNTRY
--------------- -------------------- ------ ---------- ------------------------
Opal            Aaron                M            1990 United States of America
KaKit           Abeles               M            1986 United States of America
Mitchel         Alambarati           M            1987 Canada
Jade            Anderson             M            1986 United States of America
Roderica        Austin               M            1986 United States of America

The following commands list the files in the local expdir directory, create a Hadoop subdirectory named customers, and copy the files to it. The user is connected to the Hadoop cluster (Oracle Big Data Appliance or other) as the oracle user.

$ cd /expdir
$ ls americas*.dmp
americas1.dmp  americas2.dmp
$ hadoop fs -mkdir customers
$ hadoop fs -put *.dmp customers
$ hadoop fs -ls customers
Found 2 items
-rw-r--r--   1 oracle oracle     798720 2014-10-13 17:04 customers/americas1.dmp
-rw-r--r--   1 oracle oracle     954368 2014-10-13 17:04 customers/americas2.dmp

This HiveQL statement creates an external table using the Copy to Hadoop SerDes. The LOCATION clause identifies the full path to the Hadoop directory containing the Data Pump files:

CREATE EXTERNAL TABLE customers
   ROW FORMAT SERDE 'oracle.hadoop.hive.datapump.DPSerDe'
   STORED AS
      INPUTFORMAT  'oracle.hadoop.hive.datapump.DPInputFormat'
      OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION '/user/oracle/customers';

The DESCRIBE command shows the columns of the CUSTOMERS external table.

hive> DESCRIBE customers;
OK
first_name               varchar(20)              from deserializer   
last_name                varchar(40)              from deserializer   
gender                   char(1)                  from deserializer   
birth                    int                      from deserializer   
email                    varchar(50)              from deserializer   
postal_code              varchar(10)              from deserializer   
country                  varchar(40)              from deserializer   

The following HiveQL SELECT statement shows the same data as the SQL SELECT statement from Oracle Database shown in "Verifying the Contents of the Data Files". The two queries access copies of the same Data Pump files.

SELECT first_name, last_name, gender, birth, country 
  FROM customers 
  WHERE birth > 1985 
  ORDER BY last_name LIMIT 5;

Total MapReduce jobs = 1
Launching Job 1 out of 1
     .
     .
     .
OK
Opal      Aaron       M     1990     United States of America
KaKit     Abeles      M     1986     United States of America
Mitchel   Alambarati  M     1987     Canada
Jade      Anderson    M     1986     United States of America
Roderica  Austin      M     1986     United States of America