3 Storing Oracle Data in Hadoop

What is Oracle Shell for Hadoop Loaders?

What’s Demonstrated in These Examples

These examples demonstrate the follow tasks:

• Starting an OHSH session and create the resources you’ll need for Copy to Hadoop.

• Using Copy to Hadoop to copy the data from the selected Oracle Database table to a new Hive table in Hadoop (using the resources that you created).

• Using the load operation to add more data to the Hive table created in the first example.

• Using the create or replace operation to drop the Hive table and replace it with a new one that has a different record set.

• Querying the data in the Hive table and in the Oracle Database table.

• Converting the data into other formats

Starting OHSH, Creating Resources, and Running Copy to Hadoop

1. Start OHSH. (The startup command below assumes that you’ve added the OHSH path to your PATH variable as recommended.)

   $ ohsh
   ohsh>
   

2. Create the following resources.

   • SQL*Plus resource.

     ohsh> create sqlplus resource sql0 connectid=”<database_connection_url>”
     

   • JDBC resource.

     ohsh> create jdbc resource jdbc0 connectid=”<database_connection_url>”
     

   Note:

   For the Hive access shown in this example, only the default hive0 resource is needed. This resource is already configured to connect to the default Hive database. If additional Hive resources were required, you would create them as follows:

   ohsh> create hive resource hive_mydatabase connectionurl=”jdbc:hive2:///<Hive_database_name>”
   

3. Include the Oracle Database table name in the create hive table command below and run the command below. This command uses the Copy to Hadoop directcopy method. Note that directcopy is the default mode and you do not actually need to name it explicitly.

   ohsh> create hive table hive0:<new_Hive_table_name> from oracle table jdbc0:<Oracle_Database_table_name> from oracle table jdbc0:<Oracle_Database_table_name> using directcopy
   

   Your Oracle Table data would now be stored in Hadoop as a Hive table.

Adding More Data to the Hive Table

Use the OHSH load method to add data to an existing Hive table.

Let’s assume that the original Oracle table includes a time field in the format DD-MM-YY and that a number of daily records were added after the Copy to Hadoop operation that created the corresponding Hive table.

Use load to add these new records to the existing Hive table:

ohsh> load hive table hive0:<Hive_table_name> from oracle table jdbc0:<Oracle_Database_table_name> where “(time >= ’01-FEB-18’)”

Using OHSH create or replace

The OHSH create or replace operation does the following:

1. Drops the named Hive table (and the associated Data Pump files) if a table by this name already exists.

   Note:

   Unlike create or replace, A create operation fails and returns an error if the Hive table and the related Data Pump files already exist.

2. Creates a new Hive table using the name provided.

Suppose some records were deleted from the original Oracle Database table and you want to realign the Hive table with the new state of the Oracle Database table. Hive does not support update or delete operations on records, but the create or replace operation in OHSH can achieve the same end result:

ohsh> create or replace hive table hive0:<new_hive_table_name> from oracle table jdbc0:<Oracle_Database_table_name>

Querying the Hive Table

You can invoke a Hive resource in OHSH in order to run HiveQL commands. Likewise, you can invoke an SQL*Plus resource to run SQL commands. For example, these two queries compare the original Oracle Database table with the derivative Hive table:

ohsh> %sql0 select count(*) from <Oracle_Database_table_name>
ohsh> %hive0 select count(*) from <Hive_table_name>

Storing Data in Other Formats, Such as Parquet or ORC

By default, Copy to Hadoop outputs Data Pump files. In a create operation, you can use the “stored as” syntax to change the destination format to Parquet or ORC:

ohsh> %hive0 create table <Hive_table_name_parquet> stored as parquet as select * from <Hive_table_name>

This example creates the Data Pump files, but then immediately copies them to Parquet format. (The original Data Pump files are not deleted.)

Syntax

This topic does not apply to Oracle Big Data SQL Cloud Service.

bds-copy-tbs-to-hdfs.sh syntax is as follows:

bds-copy-tbs-to-hdfs.sh
bds-copy-tbs-to-hdfs.sh --install
bds-copy-tbs-to-hdfs.sh --tablespace=<tablespace name> [-pdb=<pluggable database name>]
bds-copy-tbs-to-hdfs.sh --list=<tablespace name> [--pdb=<pluggable database name>]
bds-copy-tbs-to-hdfs.sh --show=<tablespace name> [--pdb=<pluggable database name>]

Usage

Use bds-copy-tbs-to-hdfs.sh to move a tablespace to HDFS as follows.

1. Log on as the oracle Linux user and cd to the bds-database-install directory where the database bundle was extracted. Find bds-copy-tbs-to-hdfs.sh in this directory.

2. Check that FUSE-DFS is installed.

   $ ./bds-copy-tbs-to-hdfs.sh
   

3. Install the FUSE-DFS service (if it was not found in the previous check). This command will also start the FUSE-DFS the service.

   $ ./bds-copy-tbs-to-hdfs.sh --install
   

   If this script does not find the mount point, it launches a secondary script. Run this script as root when prompted. It will set up the HDFS mount. You can run the secondary script in a separate session and then return to this session if you prefer.

   For RAC Databases: Install FUSE_DFS on All Nodes:

   On a RAC database, the script will prompt you that you must install FUSE-DFS on the other nodes of the database.

4. List the eligible tablespaces in a selected PDB or all PDBs. You can skip this step if you already know the tablespace name and location.

   $ ./bds-copy-tbs-to-hdfs.sh --list=mytablesapce --pdb=pdb1
   

5. Select a tablespace from the list and then, as oracle, run bds-copy-tbs-to-hdfs.sh again, but this time pass in the --tablespace parameter (and the --pdb parameter if specified). The script moves the tablespace to the HDFS file system.

   $ ./bds-copy-tbs-to-hdfs.sh --tablespace=mytablespace --pdb=pdb1
   

   This command automatically makes the tablespace eligible for Smart Scan in HDFS. It does this in SQL by adding the “hdfs:” prefix to the datafile name in the tablespace definition. The rename changes the pointer in the database control file. It does not change the physical file name.

   For RAC Databases: Replicate Symlinks to Mountpoints on All Nodes:

   On the database node where you are running bds-copy-tbs-to-hdfs.sh , it creates a symlink in $ORACLE_HOME/dbs/hdfs:<cluster name> for each datafile, pointing to the corresponding FUSE-DFS mountpoint. But the script only does this on the local node. You must manually set up the same symlinks on each node of the database.

   For example, if the database consists of three nodes and you run bds-copy-tbs-to-hdfs.sh on Node1, then for each datafile in the copy operation, the script will create a symlink to the mountpoint on Node. In this example there is only one datafile:

   $ ls -l $ORACLE_HOME/dbs/hdfs:<cluster>/<path to datafile>/<datafile name> 
   $ORACLE_HOME/dbs/hdfs:<cluster>/<path to datafile>/<datafile name> -> /mnt/fuse-<cluster>-hdfs/user/oracle/<path to datafile>/<datafile name>
   

   On Node2 and Node3, you can see that the mountpoint exists (if FUSE-DFS was installed):

   $ ls -l /mnt/fuse-<cluster>-hdfs/user/oracle/<path to datafile>/<datafile name>
   $ /mnt/fuse-<cluster>-hdfs/user/oracle/<path to datafile>/<datafile name>
   

   However, bds-copy-tbs-to-hdfs.sh does not create the symlinks to the mountpoint on these other nodes and you must add them manually:

   $ ln -s /mnt/fuse-<cluster>-hdfs/user/oracle/<path to datafile>/<datafile name> $ORACLE_HOME/dbs/hdfs:<cluster>/<path to datafile>/<datafile name>
   

The tablespace should be back online and ready for access when you have completed this procedure.