This is the documentation for Cloudera Manager 4.8.5.
Documentation for other versions is available at Cloudera Documentation.

Using an External Database for Hue

By default, Cloudera Manager uses SQLite for Hue's database. If necessary, you can configure Cloudera Manager to use an external database such as MySQL or PostgreSQL as the database for Hue. The procedure described in this topic illustrates how to migrate the Hue database from the default SQLite installation to another database.

Dump the Hue Database

  1. Using the Cloudera Manager Admin Console, click the Hue service instance for the Hue database you are reconfiguring.
  2. Select Actions > Stop. Confirm you want to stop the service by clicking Stop.
  3. Select Actions > Dump Database. Confirm you want to dump the database by clicking Dump Database.
  4. Open the database dump file (by default /tmp/hue_database_dump.json) and remove all JSON objects with 'useradmin.userprofile' in the 'model' field. (You can verify the location of the Database Dump File by searching for Database Dump File in the Hue Configuration settings.)

Configuring the Hue Server to Store Data in MySQL

  1. Perform the steps in Dump the Hue Database.
  2. Create a new database and grant privileges to a Hue user to manage this database. For example:
    mysql> create database hue;
    Query OK, 1 row affected (0.01 sec)
    mysql> grant all on hue.* to 'hue'@'localhost' identified by 'secretpassword';
    Query OK, 0 rows affected (0.00 sec)
  3. Using the Cloudera Manager Admin Console, click the service instance for the Hue database you are reconfiguring. The Hue service instance page in Cloudera Manager Admin Console appears.
  4. Click Configuration > View and Edit.
  5. In the Category pane, click the instance of Database under Service-Wide.
  6. Specify the settings for Hue Database Type, Hue Database Hostname, Hue Database Port, Hue Database Username, Hue Database Password, and Hue Database Name. For example, for a MySQL database on the local host, you might use the following values:
    Hue Database Type = mysql
    Hue Database Hostname = localhost
    Hue Database Port = 3306
    Hue Database Username = hue
    Hue Database Password = secretpassword
    Hue Database Name = hue
  7. The following steps are for restoring the Hue data to the new database. If you would like Hue to start from a fresh state, you can start your Hue service now.
  8. Click Actions and click Synchronize Database.
  9. Determine the foreign key ID.
    $ mysql -uhue -psecretpassword
    mysql > SHOW CREATE TABLE auth_permission;
  10. (InnoDB only) Drop the foreign key that you retrieved in the previous step.
    mysql > ALTER TABLE auth_permission DROP FOREIGN KEY content_type_id_refs_id_XXXXXX;
  11. Delete the rows in the django_content_type table.
    mysql > DELETE FROM hue.django_content_type;
  12. In Hue service instance page, click Actions, and click Load Database. Confirm you want to load the database by clicking Load Database.
  13. (InnoDB only) Add back the foreign key.
    mysql > ALTER TABLE auth_permission ADD FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
  14. Start the Hue service.

Configuring the Hue Server to Store Data in PostgreSQL

  1. Perform the steps in Dump the Hue Database.
  2. Install required packages.

    To install on RHEL systems:

    $ sudo yum install postgresql-devel gcc python-devel

    To install on SLES systems:

    $ sudo zypper install postgresql-devel gcc python-devel

    To install on Ubuntu or Debian systems:

    $ sudo apt-get install postgresql-devel gcc python-devel
  3. Install the Python module that provides the connector to PostgreSQL:
    • Parcel install
      $ sudo /opt/cloudera/parcels/CDH/lib/hue/build/env/bin/pip install setuptools
      $ sudo /opt/cloudera/parcels/CDH/lib/hue/build/env/bin/pip install psycopg2
    • Package install
      sudo -u hue /usr/share/hue/build/env/bin/pip install setuptools
      sudo -u hue /usr/share/hue/build/env/bin/pip install psycopg2
  4. Install the PostgreSQL server.

    To install PostgreSQL on a RHEL system:

    $ sudo yum install postgresql-server

    To install PostgreSQL on SLES systems:

    $ sudo zypper install postgresql-server

    To install PostgreSQL on Ubuntu or Debian systems:

    $ sudo apt-get install postgresql
  5. Initialize the data directories.
    $ service postgresql initdb
  6. Configure client authentication.
    1. Edit /var/lib/pgsql/data/pg_hba.conf.
    2. Set the authentication methods for local to trust and for host to password and add the following line at the end.
      host	hue	hue	0.0.0.0/0	md5
  7. Start the PostgreSQL server.
    $ su - postgres
    # /usr/bin/postgres -D /var/lib/pgsql/data > logfile 2>&1 &
  8. Configure PostgreSQL to listen on all network interfaces.
    1. Edit /var/lib/pgsql/data/postgresql.conf and set list_addresses.
      listen_addresses = ‘0.0.0.0’     # Listen on all addresses
  9. Create the hue database and grant privileges to a hue user to manage the database.
    # psql -U postgres
    postgres=# create database hue;
    postgres=# \c hue;
    You are now connected to database 'hue'.
    postgres=# create user hue with password 'secretpassword';
    postgres=# grant all privileges on database hue to hue;
    postgres=# \q
  10. Restart the PostgreSQL server.
    $ sudo service postgresql restart
  11. Verify connectivity.
    psql –h localhost –U hue –d hue 
    Password for user hue: secretpassword
  12. Configure the PostgreSQL server to start at boot. '

    On RHEL systems:

    $ sudo /sbin/chkconfig postgresql on
    $ sudo /sbin/chkconfig --list postgresql
    postgresql          0:off   1:off   2:on    3:on    4:on    5:on    6:off

    On SLES systems:

    $ sudo chkconfig --add postgresql

    On Ubuntu or Debian systems:

    $ sudo chkconfig postgresql on
  13. Using the Cloudera Manager Admin Console, click the service instance for the Hue database you are reconfiguring. The Hue service instance page in Cloudera Manager Admin Console appears.
  14. Select Configuration > View and Edit.
  15. Expand the Service-Wide > Advanced category.
  16. Specify the settings for Hue Server Configuration Safety Valve:
    [desktop]
    [[database]]
    host=localhost
    port=5432
    engine=postgresql_psycopg2
    user=hue
    password=secretpassword
    name=hue
  17. Click Save Changes.
  18. The following steps are for restoring the Hue data to the new database. If you would like Hue to start from a fresh state, you can start your Hue service now.
  19. Click Actions and click Synchronize Database.
  20. Determine the foreign key ID.
    bash# su – postgres
    $ psql –h localhost –U hue –d hue
    postgres=# \d auth_permission;
  21. Drop the foreign key that you retrieved in the previous step.
    postgres=# ALTER TABLE auth_permission DROP CONSTRAINT content_type_id_refs_id_XXXXXX;
  22. Delete the rows in the django_content_type table.
    postgres=# TRUNCATE django_content_type CASCADE;
  23. In Hue service instance page, click Actions, and click Load Database. Confirm you want to load the database by clicking Load Database.
  24. Add back the foreign key you dropped.
    bash# su – postgres
    $ psql –h localhost –U hue –d hue 
    postgres=# ALTER TABLE auth_permission ADD CONSTRAINT content_type_id_refs_id_XXXXXX FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED;
  25. Start the Hue service.

Configuring the Hue Server to Store Data in Oracle

  1. Ensure Python 2.6 or newer is installed on the server Hue is running on.
  2. Download the Oracle libraries at Instant Client for Linux x86-64 Version 11.1.0.7.0, Basic and SDK (with headers) zip files to the same directory.
  3. Unzip the Oracle client zip files.
  4. Set environment variables to reference the libraries.
    $ export ORACLE_HOME=oracle_download_directory 
    $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
  5. Create a symbolic link for the shared object:
    $ cd $ORACLE_HOME 
    $ ln -sf libclntsh.so.11.1 libclntsh.so
  6. Install the Python Oracle library: $ HUE_HOME/build/env/bin/pip install cx_Oracle
  7. Upgrade django south: $ HUE_HOME/build/env/bin/pip install south --upgrade
  8. Generate statements to delete all data from Oracle tables by running this query in the Oracle DB as the Hue schema user:
    > set page size 100
    > SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
      Note: If you are logged into Oracle via sqlplus as the Hue schema user, some of these commands may hang. If that occurs, log out of sqlplus.
  9. Copy the statements from the previous step and run them in the Oracle DB as the Hue schema user.
  10. Stop the Hue service.
  11. Go to the Hue service in Cloudera Manager Admin Console and select Actions > Dump Database.
  12. Select Configuration > View and Edit.
  13. Expand the Service-Wide > Advanced category.
  14. Set the Hue Service Environment Safety Valve property to
    ORACLE_HOME=oracle_download_directory
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:oracle_download_directory
  15. Set the Hue Service Safety Valve for hue_safety_valve.ini property with a [desktop] and [[database]] section similar to the following (modified accordingly for your Oracle setup):
    [desktop]
    [[database]]
    host=localhost
    port=1521
    engine=oracle
    user=hue
    password=secretpassword
    name=<SID of the Oracle database, for example, 'XE’>
    options={“threaded”:true}
  16. Login to the Hue server as root and run the following commands:
    cd /var/run/cloudera-scm-agent/process
    ls -1 | grep hue | sort -n | tail -1 (copy the directory listed from this command, should be similar to “47-hue-server-syncdb”.
    cd <directory above>
    export HUE_CONF_DIR=`pwd`<HUE_HOME>/build/env/bin/hue migrate
  17. Select Actions > Synchronize Database.
  18. Load the data that you dumped in step 10. Select Actions > Load Database. This step is not necessary if you have a fresh Hue install with no data or if you don’t want to save the Hue data.
  19. Start the Hue service.