Connect Hue to Oracle with Client Package

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 Oracle client packages.

Install and Configure Oracle Server

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

Set Environment Variables

  1. Set all necessary Oracle environment variables. For example:
    ## Example Environment Variables
    ORACLE_HOSTNAME=<your hostname> 
  2. Ensure that your shell .profile resembles:
    ## Example from /home/oracle/.bash_profile
    ORACLE_HOSTNAME=<your hostname> 

Configure Character Set

  1. Log on as the oracle user:
    su - oracle
  2. Start the listener control (as user oracle):
    $ORACLE_HOME/bin/lsnrctl start
  3. Log on to SQL*Plus:
    sqlplus / as sysdba
  4. 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
    SHUTDOWN immediate
    sqlplus /nolog < alter_charset.ddl

Create Hue Database

  1. 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
  2. Verify that you can connect to hue:
    sqlplus hue/<your hue password>
  3. 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;
    spool off
    ## 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

Install Oracle Client Package

Cloudera Manager requires the Oracle instant client libraries to be in /usr/share/oracle/instantclient/lib/. The following commands arrange the files as such.

Install Asynchronous I/O Library

  1. Log on to the host of Cloudera Manager server.
  2. 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

Install Hue with Oracle database 12c and higher

  1. Download the zip files for the Instant Client Package, both Basic and SDK (with headers).

  2. Switch to the host with the downloaded files and upload zip to the Hue server host:
    scp instantclient-*.zip root@<hue server hostname>:.
  3. Arrange the client libraries to mirror the tree structure in the image as shown in the following example:
    # Create nested directories: /usr/share/oracle/instantclient/lib/
    mkdir -pm 755 /usr/share/oracle/instantclient/lib
    # Unzip. The files expand into /usr/share/oracle/instantclient/instantclient_<ver>/
    unzip '*.zip' -d /usr/share/oracle/instantclient/
    # Move lib files from instantclient_<ver> to /usr/share/oracle/instantclient/lib/
    mv /usr/share/oracle/instantclient/`ls -l /usr/share/oracle/instantclient/ | grep instantclient_ | awk '{print $9}'`/lib* /usr/share/oracle/instantclient/lib/
    # Move rest of the files to /usr/share/oracle/instantclient/
    mv /usr/share/oracle/instantclient/`ls -l /usr/share/oracle/instantclient/ | grep instantclient_ | awk '{print $9}'`/* /usr/share/oracle/instantclient/
    # Create symbolic links. Remember to edit version numbers as necessary
    cd /usr/share/oracle/instantclient/lib
    ln -s<ver>.1
    ln -s<ver>.1
    # For example:
    ln -s
    ln -s
    ln -s
    ln -s

    where <ver> is the version of the Instant Client Package. Replace <ver> with the actual version of the Instant Client Package.

  4. Set the path for $ORACLE_HOME and $LD_LIBRARY_PATH as shown in the following example:
    export ORACLE_HOME=/usr/share/oracle/instantclient

Apply Temporary Workaround for Oracle 12 Client

Update the cx_Oracle package in your native Python environment and copy it to Hue's Python environment. The default cx_Oracle version that is shipped with Cloudera Manager is 5.2.1.

  1. Install gcc and Python development tools:
    ## CentOS/RHEL (yum), SLES (zypper), Ubuntu/Debian (apt-get)
    yum install -y python-setuptools python-devel gcc
    #zypper install -y python-setuptools python-devel gcc
    #apt-get install -y python-setuptools python-dev gcc
  2. Install pip:
    easy_install pip
  3. Install cx_Oracle. Ensure that ORACLE_HOME and $LB_LIBRARY_PATH are properly set so that pip knows which version to install.
    pip install cx_Oracle==5.3
  4. Get the version of the new cx_Oracle package:
    • CentOS/RHEL and SLES:
      ls /usr/lib64/python2.7/site-packages/cx_Oracle*
    • Ubuntu/Debian:
      ls /usr/local/lib/python2.7/dist-packages/cx_Oracle*
  5. If this is a New CDH Installation, stop here to run the first 5 or 6 steps of the Cloudera Manager Installation Wizard (packages=5, parcels=6). Do not go past Cluster Installation.
  6. Navigate to Hue's python environment, $HUE_HOME/build/env/lib/<python version>/site-packages.
    • CDH Parcel installation:
      cd /opt/cloudera/parcels/`ls -l /opt/cloudera/parcels | grep CDH | tail -1 | awk '{print $9}'`/lib/hue/build/env/lib/python2.7/site-packages
    • CDH package installation:
      cd /usr/lib/hue/build/env/lib/python2.7/site-packages
  7. Move the existing cx_Oracle file:
    mv cx_Oracle-5.2.1-py2.7-linux-x86_64.egg cxfoo
  8. Copy the new cx_Oracle module to Hue's python environment. The version can change:
    • CentOS/RHEL and SLES:
      cp -a /usr/lib64/python2.7/site-packages/cx_Oracle-5.3-py2.7.egg-info .
    • Ubuntu/Debian
      cp -a /usr/local/lib/python2.7/dist-packages/cx_Oracle-5.3.egg-info .

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.

  1. 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
  2. Stop at the end of Cluster Installation to copy the latest cx_Oracle package into Hue's Python environment.

  3. 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.
      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.

  4. Continue with the installation and click Finish to complete.
  5. 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:
    4. Click Save Changes.
  6. Restart the Hue service: select Actions > Restart and click Restart.
  7. Log on to Hue by clicking Hue Web UI.

Existing CDH Installation

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.

  1. [migration only] Stop Hue Service
    1. In Cloudera Manager, navigate to Cluster > Hue.
    2. Select Actions > Stop.
  2. [migration only] Dump Current Database
    1. Select Actions > Dump Database.
    2. Click Dump Database. The file is written to /tmp/hue_database_dump.json on the host of the Hue server.
    3. Log on to the host of the Hue server in a command-line terminal.
    4. 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"
  3. Connect to New Database
    1. Configure Database connections: Go to Hue > Configuration, filter by Database, set 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 Hue-service, set Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini, and click Save Changes:
  4. [migration only] Synchronize New Database
    1. Select Actions > Synchronize Database
    2. Click Synchronize Database.
  5. [migration only] Load Data from Old Database
    sqlplus hue/<hue_password> < delete_from_tables.ddl
  6. 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.