Connect Hue to Oracle with Client Parcel

To connect to an Oracle database, Hue needs Oracle client libraries (Basic and SDK). These are available from Oracle as packages (zip files) or from Cloudera as a parcel (for CDH parcel deployments).

This page covers connecting with the Oracle client parcel.

Continue reading:

Install and Configure Oracle Server
Create Hue Database
Create Oracle Client Parcel Repository
Connect Hue Service to Oracle

Install and Configure Oracle Server

Refer to the Oracle documentation for help on how to install an Oracle database.

Set Environment Variables

Set all necessary Oracle environment variables. For example:

## Example Environment Variables
VERSION=12.1.0.2
ORACLE_HOSTNAME=<your hostname> 
ORACLE_BASE=/ora01/app/oracle/product/base
ORACLE_HOME=${ORACLE_BASE}/${VERSION} 
ORACLE_SID=orcl
ORAOWNER_BIN=/home/oracle/bin
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}

Ensure that your shell .profile resembles:

## Example from /home/oracle/.bash_profile
TMP=/tmp
ORACLE_HOSTNAME=<your hostname> 
ORACLE_BASE=/ora01/app/oracle/product/base
ORACLE_HOME=/ora01/app/oracle/product/base/12.1.0.2
ORACLE_SID=orcl
ORAOWNER_BIN=/home/oracle/bin
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
PATH=${ORACLE_HOME}/bin:${ORAOWNER_BIN}:${PATH}
CLASSPATH=${ORACLE_HOME}/jlib:${ORACLE_HOME}/rdbms/jlib;
export ORACLE_HOSTNAME ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH CLASSPATH TMP

Configure Character Set

Log on as the oracle user:
```
su - oracle
```
Start the listener control (as user oracle):
```
$ORACLE_HOME/bin/lsnrctl start
```
Log on to SQL*Plus:
```
sqlplus / as sysdba
```

Ensure character set is AL32UTF8 and national character set is UTF8:

SELECT * FROM v$nls_parameters where parameter like '%CHARACTERSET';

To update, quit the shell and run these commands in a SQL*Plus script:

vi alter_charset.ddl

## Save in alter_charset.ddl (script takes 2-3 minutes)
CONNECT / as sysdba
SHUTDOWN immediate
STARTUP mount
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN immediate
STARTUP

sqlplus /nolog < alter_charset.ddl

Create Hue Database

Create the hue schema, set quotas, and grant select permissions (do not grant all):

vi create_hue_database.ddl

## Save in create_hue_database.ddl
## Change huepassword to something more secure
CONNECT / as sysdba
ALTER session set "_ORACLE_SCRIPT"=true;

DROP user hue cascade;
CREATE user hue identified by huepassword;
ALTER user hue quota 1000m on users;
ALTER user hue quota 100m on system;
GRANT create sequence to hue;
GRANT create session to hue;
GRANT create table to hue;
GRANT create view to hue;
GRANT create procedure to hue;
GRANT create trigger to hue;
GRANT execute on sys.dbms_crypto to hue;
GRANT execute on sys.dbms_lob to hue;

sqlplus /nolog < create_hue_database.ddl

Verify that you can connect to hue:
```
sqlplus hue/<your hue password>
```

Clean all hue user tables. Create a script to spool delete statements into a new file, delete_from_tables.ddl:

vi spool_statements.ddl

## Save in spool_statements.ddl (which generates delete_from_tables.ddl)
spool delete_from_tables.ddl
set pagesize 100;
SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
commit;
spool off
quit

## Create delete_from_tables.ddl
sqlplus hue/<your hue password> < spool_statements.ddl

## Run delete_from_tables.ddl
sqlplus hue/<your hue password> < delete_from_tables.ddl

Create Oracle Client Parcel Repository

Cloudera provides the Oracle Instant Client for Hue (11.2 only) as a parcel for CDH parcel deployments.

Download and Stage Oracle Instant Client Parcel

Point a browser to https://www.cloudera.com/downloads/oracle_instant_client_hue.html.
Select your OS and click Get It Now!
Check the box to accept Cloudera's Standard Licence Agreement and click Submit.
Download the parcel: ORACLE_INSTANT_CLIENT-11.2-1.oracleinstantclient1.0.0.p0.130-<your linux distro>.parcel.
Download the manifest for the mirrored repository.

Upload the parcel and manifest to the host with Cloudera Manager server, for example:

scp ORACLE_INSTANT_CLIENT-11.2-1* manifest.json root@<Cloudera Manager server hostname>:.

Install Asynchronous I/O Library

Log on to the host of Cloudera Manager server.

Install the Asynchronous I/O library, libaio/libaio1:

## CentOS/RHEL (yum), SLES (zypper), Ubuntu/Debian (apt-get)
sudo yum install -y libaio
#sudo zypper install -y libaio
#sudo apt-get install -y libaio1

Create Mirrored Parcel Repository

When manually adding parcels it is best to use mirrored repository as it preserves the metadata that enforces relation constraints.

Create a temporary repository , for example:

mkdir -pm 755 /var/www/html/cdh511
mv ~/ORACLE_INSTANT_CLIENT-11.2-1* ~/manifest.json /var/www/html/cdh511

Start a web server with any available port, for example:

cd /var/www/html/cdh511/
python -m SimpleHTTPServer 8900

Test the repository in a browser:
```
http://<server hostname>:8900/
```

[Optional]

In fact, the Oracle parcel does not have any constraints, but using a repository allows you to more easily connect to an Oracle database during a new CDH installation if necessary. It is also a best practice and not more work.

However, if you have an existing CDH installation, you can simply copy the parcel (in this case) and add a corresponding SHA-1 file to /opt/cloudera/parcel-repo.

You must have CDH installed because the directory, parcel-repo, is created during step 6 of a CDH parcel installation.

sha1sum ORACLE_INSTANT_CLIENT-11.2-1.oracleinstantclient1.0.0.p0.130-<your linux distro>.parcel | awk '{ print $1 }' > ORACLE_INSTANT_CLIENT-11.2-1.oracleinstantclient1.0.0.p0.130-<your linux distro>.parcel.sha1 
mv ORACLE_INSTANT_CLIENT* /opt/cloudera/parcel-repo/

Connect Hue Service to Oracle

You can connect Hue to your Oracle database while installing CDH (and Hue) or with an existing installation. With existing CDH installations, you can connect and restart Hue, without saving the data in your current database, or you can migrate the old data into Oracle.

New CDH Installation

See Cloudera Installation Guide to install Cloudera Manager (and its Installation Wizard), which you will use here to install CDH and the Oracle client.

Install CDH and Oracle Parcel

Open the Cloudera Manager Admin Console and run the Cloudera Manager Installation Wizard to install CDH (and Hue). The URL for Cloudera Manager is: http://<cm server hostname>:7180
Stop at Select Repository to add the Oracle client parcel repository (Cluster Installation, step 1):
1. Choose Method Use Parcels and click More Options.
2. Click and add the URL for your Oracle Remote Parcel Repository:
3. Click Save Changes.
4. Select the newly added radio button by ORACLE_INSTANT_CLIENT and click Continue.
  
  The Oracle parcel is downloaded, distributed, and activated at Cluster Installation, step 6 (Installing Selected Parcels).

Connect Hue to Oracle

Continuing with Cloudera Manager Installation Wizard …

Stop at Database Setup to set connection properties (Cluster Setup, step 3).
1. Select Use Custom Database.
2. Under Hue, set the connection properties to the Oracle database.
  Note: Copy and store the password for the Hue embedded database (just in case).
```
Database Hostname (and port): <fqdn of host with Oracle server>:1521
Database Type (or engine): Oracle
Database SID (or name): orcl
Database Username: hue
Database Password: <hue database password>
```
3. Click Test Connection and click Continue when successful.
Continue with the installation and click Finish to complete.
Add support for a multi-threaded environment:
1. Go to Clusters > Hue > Configuration.
2. Filter by Category, Hue-service and Scope, Advanced.
3. Add support for a multi-threaded environment by setting Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini:
```
[desktop]
[[database]]
options={"threaded":true}
```
4. Click Save Changes.
Restart the Hue service: select Actions > Restart and click Restart.
Log on to Hue by clicking Hue Web UI.

Existing CDH Installation

Activate Oracle Client Parcel

Log on to Cloudera Manager.
Go to the Parcels page by clicking Hosts > Parcels (or clicking the parcels icon ).
Click the Configuration > Check for New Parcels.
Find ORACLE_INSTANT_CLIENT and click Download, Distribute, and Activate.

Connect Hue to Oracle

If you are not migrating the current (or old) database, simply connect to your new Oracle database and restart Hue. See Connect Hue to an External Database.

[migration only] Stop Hue Service
1. In Cloudera Manager, navigate to Cluster > Hue.
2. Select Actions > Stop.
  Note: If necessary, refresh the page to ensure the Hue service is stopped: .

[migration only] Dump Current Database

Select Actions > Dump Database.
Click Dump Database. The file is written to /tmp/hue_database_dump.json on the host of the Hue server.
Log on to the host of the Hue server in a command-line terminal.

Edit /tmp/hue_database_dump.json by removing all objects with useradmin.userprofile in the model field. For example:

# Count number of objects
grep -c useradmin.userprofile /tmp/hue_database_dump.json

vi /tmp/hue_database_dump.json

{
  "pk": 1,
  "model": "useradmin.userprofile",
  "fields": {
    "last_activity": "2016-10-03T10:06:13",
    "creation_method": "HUE",
    "first_login": false,
    "user": 1,
    "home_directory": "/user/admin"
  }
},
{
  "pk": 2,
  "model": "useradmin.userprofile",
  "fields": {
    "last_activity": "2016-10-03T10:27:10",
    "creation_method": "HUE",
    "first_login": false,
    "user": 2,
    "home_directory": "/user/alice"
  }
},

Connect to New Database
1. Configure Database connections:
  - Go to Hue > Configuration and filter by category, Database.
  - Set database properties and click Save Changes:
```
Hue Database Type (or engine): Oracle
Hue Database Hostname: <fqdn of host with Oracle server>
Hue Database Port: 1521
Hue Database Username: hue
Hue Database Password: <hue database password>
Hue Database Name (or SID): orcl
```
2. Add support for a multi-threaded environment:
  - Filter by Category, Hue-service and Scope, Advanced.
  - Set Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini and click Save Changes:
```
[desktop]
[[database]]
options={"threaded":true}
```
[migration only] Synchronize New Database
1. Select Actions > Synchronize Database
2. Click Synchronize Database.
[migration only] Load Data from Old Database
Important: All user tables in the Hue database must be empty. You cleaned them at step 3 of Create Hue Database. Ensure they are still clean.
```
sqlplus hue/<your hue password> < delete_from_tables.ddl
```
Re/Start Hue service
1. Navigate to Cluster > Hue.
2. Select Actions > Start, and click Start.
3. Click Hue Web UI to log on to Hue with a custom Oracle database.

Categories: Databases | Hue | Oracle | All Categories

Connect to PostgreSQL

Connect to Oracle (Package)