Configuring the Hue Server to Store Data in the Oracle database

You can connect Hue to your Oracle database while installing Cloudera Runtime (and Hue).

Connect Hue Service to Oracle

If you want to connect Hue service to Oracle with an existing CDH installation, then connect and restart Hue without saving the data in your current database. Alternatively, you can migrate the old data into Oracle.

New Cloudera Runtime Installation

See Step 3: Install Cloudera Manager Server to install Cloudera Manager (and its Installation Wizard), which you will use here to install Cloudera Runtime and the Oracle client.

Install Hue in CDP 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 libclntsh.so.<ver>.1 libclntsh.so
    ln -s libocci.so.<ver>.1 libocci.so
    # For example:
    ln -s libclntsh.so.12.1 libclntsh.so
    ln -s libocci.so.12.1 libocci.so
    ln -s libclntsh.so.12.1
    ln -s libocci.so.12.1 libocci.so.11.1

    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
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

Apply temporary workaround for Oracle 12c 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.
    echo $ORACLE_HOME $LD_LIBRARY_PATH
    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 CDP installation, stop here to run the first 5 steps of the Cloudera Manager Installation Wizard. Do not go past Cluster Installation.
  6. Navigate to Hue's python environment, $HUE_HOME/build/env/lib/<python version>/site-packages.
    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 to Oracle

Continuing with Cloudera Manager Installation Wizard …

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


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

Existing CDH Installation

If you are using Oracle database with Hue and are upgrading to CDP 7.x from CDH 5 or CDH 6, then do the following:

Dectivate the Oracle Client Parcel

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

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 libclntsh.so.<ver>.1 libclntsh.so
    ln -s libocci.so.<ver>.1 libocci.so

    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
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

Connect Hue to Oracle

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

  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 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}
  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/<your 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.