Using an External Database for Hue Using Cloudera Manager

Minimum Required Role: Full Administrator

The Hue server requires an SQL database to store small amounts of data such as user account information, job submissions, and Hive queries. Hue supports a lightweight embedded (SQLite) database and several types of external databases. This page explains how to configure Hue with a selection of Supported Databases. To configure Hue with any of the supported external databases, the high-level steps are:
  1. Stop Hue service.
  2. Backup default SQLite database (if applicable).
  3. Install database software and dependencies.
  4. Create and configure database and load data.
  5. Start Hue service.
See the tasks on this page for details. If you do not need to migrate a SQLite database, you can skip the steps on dumping the database and editing the JSON objects.

Continue reading:

Configuring the Hue Server to Store Data in MariaDB

For information about installing and configuring a MariaDB database , see MariaDB Database.

  1. In the Cloudera Manager Admin Console, go to the Hue service status page.
  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. Note the host to which the dump was written under Step in the Dump Database Command window. You can also find it by selecting Commands > Recent Commands > Dump Database.
  5. Open a terminal window for the host and go to the dump file in /tmp/hue_database_dump.json.
  6. Remove all JSON objects with useradmin.userprofile in the model field, for example: 
    {
"pk": 14,
"model": "useradmin.userprofile",
"fields":
{ "creation_method": "EXTERNAL", "user": 14, "home_directory": "/user/tuser2" }
},
  7. Set strict mode in /etc/my.cnf and restart MySQL: 
    [mysqld]
sql_mode=STRICT_ALL_TABLES
  8. 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)
  9. In the Cloudera Manager Admin Console, click the Hue service.
  10. Click the Configuration tab.
  11. Select Scope > All.
  12. Select Category > Database.
  13. 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 = host
    • Hue Database Port = 3306
    • Hue Database Username = hue
    • Hue Database Password = secretpassword
    • Hue Database Name = hue
  14. Optionally restore the Hue data to the new database:
    1. Select Actions > Synchronize Database.
    2. Determine the foreign key ID. 
      $ mysql -uhue -psecretpassword
mysql > SHOW CREATE TABLE auth_permission;
    3. (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;
    4. Delete the rows in the django_content_type table. 
      mysql > DELETE FROM hue.django_content_type;
    5. In Hue service instance page, click Actions > Load Database. Confirm you want to load the database by clicking Load Database.
    6. (InnoDB only) Add back the foreign key. 
      mysql > ALTER TABLE auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);
  15. Start the Hue service.

Configuring the Hue Server to Store Data in MySQL

For information about installing and configuring a MySQL database , see MySQL Database.

  1. In the Cloudera Manager Admin Console, go to the Hue service status page.
  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. Note the host to which the dump was written under Step in the Dump Database Command window. You can also find it by selecting Commands > Recent Commands > Dump Database.
  5. Open a terminal window for the host and go to the dump file in /tmp/hue_database_dump.json.
  6. Remove all JSON objects with useradmin.userprofile in the model field, for example: 
    {
"pk": 14,
"model": "useradmin.userprofile",
"fields":
{ "creation_method": "EXTERNAL", "user": 14, "home_directory": "/user/tuser2" }
},
  7. Set strict mode in /etc/my.cnf and restart MySQL: 
    [mysqld]
sql_mode=STRICT_ALL_TABLES
  8. 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)
  9. In the Cloudera Manager Admin Console, click the Hue service.
  10. Click the Configuration tab.
  11. Select Scope > All.
  12. Select Category > Database.
  13. 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 = host
    • Hue Database Port = 3306
    • Hue Database Username = hue
    • Hue Database Password = secretpassword
    • Hue Database Name = hue
  14. Optionally restore the Hue data to the new database:
    1. Select Actions > Synchronize Database.
    2. Determine the foreign key ID. 
      $ mysql -uhue -psecretpassword
mysql > SHOW CREATE TABLE auth_permission;
    3. (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;
    4. Delete the rows in the django_content_type table. 
      mysql > DELETE FROM hue.django_content_type;
    5. In Hue service instance page, click Actions > Load Database. Confirm you want to load the database by clicking Load Database.
    6. (InnoDB only) Add back the foreign key. 
      mysql > ALTER TABLE auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);
  15. Start the Hue service.

Configuring the Hue Server to Store Data in PostgreSQL

For information about installing and configuring an external PostgreSQL database , see External PostgreSQL Database.

  1. In the Cloudera Manager Admin Console, go to the Hue service status page.
  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. Note the host to which the dump was written under Step in the Dump Database Command window. You can also find it by selecting Commands > Recent Commands > Dump Database.
  5. Open a terminal window for the host and go to the dump file in /tmp/hue_database_dump.json.
  6. Remove all JSON objects with useradmin.userprofile in the model field, for example: 
    {
"pk": 14,
"model": "useradmin.userprofile",
"fields":
{ "creation_method": "EXTERNAL", "user": 14, "home_directory": "/user/tuser2" }
},
  7. Install the PostgreSQL server.

    RHEL

    $ sudo yum install postgresql-server

    SLES

    $ sudo zypper install postgresql-server

    Ubuntu or Debian

    $ sudo apt-get install postgresql
  8. Initialize the data directories. 
    $ service postgresql initdb
  9. 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
  10. Start the PostgreSQL server. 
    $ su - postgres
# /usr/bin/postgres -D /var/lib/pgsql/data > logfile 2>&1 &
# exit
  11. 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
  12. 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
  13. Restart the PostgreSQL server. 
    $ sudo service postgresql restart
  14. Verify connectivity. 
    su - postgres
# psql –h localhost –U hue –d hue 
Password for user hue: secretpassword
hue=> \q
exit
  15. Configure the PostgreSQL server to start at boot.

    RHEL

    $ 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

    SLES

    $ sudo chkconfig --add postgresql

    Ubuntu or Debian

    $ sudo chkconfig postgresql on
  16. Configure the Hue database:
    1. In the Cloudera Manager Admin Console, click the HUE service.
    2. Click the Configuration tab.
    3. Select Scope > Hue Server.
    4. Select Category > Advanced.
    5. Set Hue Server Advanced Configuration Snippet (Safety Valve) for hue_safety_valve_server.ini with the following: 
      [desktop]
[[database]]
engine=postgresql_psycopg2
name=hue
host=localhost
port=5432
user=hue
password=secretpassword
    6. Click Save Changes.
  17. Optionally restore the Hue data to the new database:
    1. Select Actions > Synchronize Database.
    2. Determine the foreign key ID. 
      bash# su – postgres
$ psql –h localhost –U hue –d hue
postgres=# \d auth_permission;
    3. Drop the foreign key that you retrieved in the previous step. 
      postgres=# ALTER TABLE auth_permission DROP CONSTRAINT content_type_id_refs_id_XXXXXX;
    4. Delete the rows in the django_content_type table. 
      postgres=# TRUNCATE django_content_type CASCADE;
    5. In Hue service instance page, Actions > Load Database. Confirm you want to load the database by clicking Load Database.
    6. 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;
  18. Start the Hue service.

Configuring the Hue Server to Store Data in Oracle (Parcel Installation)

Use the following instructions to configure the Hue Server with an Oracle database if you are working on a parcel-based deployment. If you are using packages, see Configuring the Hue Server to Store Data in Oracle (Package Installation).

For information about installing and configuring an Oracle database , see Oracle Database.
  1. Install the required packages.

    RHEL

    $ sudo yum install gcc python-devel python-pip python-setuptools libaio

    SLES:

    Python devel packages are not included in SLES. Add the SLES Software Development Kit (SDK) as a repository and then install: 
    $ zypper install gcc libaio python-pip python-setuptools python-devel

    Ubuntu or Debian

    $ sudo apt-get install gcc python-dev python-pip python-setuptools libaio1
  2. Download and add the Oracle Client parcel to the Cloudera Manager remote parcel repository URL list and download, distribute, and activate the parcel.
  3. For CDH versions lower than 5.3, install the Python Oracle library: 
    $ HUE_HOME/build/env/bin/pip install cx_Oracle
  4. For CDH versions lower than 5.3, upgrade django south: 
    $ HUE_HOME/build/env/bin/pip install south --upgrade
  5. In the Cloudera Manager Admin Console, go to the Hue service status page.
  6. Select Actions > Stop. Confirm you want to stop the service by clicking Stop.
  7. Select Actions > Dump Database. Confirm you want to dump the database by clicking Dump Database.
  8. Click the Configuration tab.
  9. Select Scope > All.
  10. Select Category > Advanced.
  11. Set the Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini property. Add the following options (and modify accordingly for your setup): 
    [desktop]
[[database]]
host=localhost
port=1521
engine=oracle
user=hue
password=secretpassword
name=<SID of the Oracle database, for example, 'XE'>
    For CDH 5.1 and higher you can use an Oracle service name. To use the Oracle service name instead of the SID, use the following configuration instead: 
    port=0
engine=oracle
user=hue
password=secretpassword
name=oracle.example.com:1521/orcl.example.com

    The directive port=0 allows Hue to use a service name. The name string is the connect string, including hostname, port, and service name.

    To add support for a multithreaded environment, set the threaded option to true under the [desktop]>[[database]] section.

    options={"threaded":true}
  12. Grant required permissions to the hue user in Oracle: 
    GRANT CREATE <sequence> TO <user>; 
GRANT CREATE <session> TO <user>; 
GRANT CREATE <table> TO <user>; 
GRANT CREATE <view> TO <user>; 
GRANT CREATE <procedure> TO <user>; 
GRANT CREATE <trigger> TO <user>; 
GRANT EXECUTE ON sys.dbms_crypto TO <user>; 
GRANT EXECUTE ON SYS.DBMS_LOB TO <user>;
  13. Go to the Hue Server instance in Cloudera Manager and select Actions > Synchronize Database.
  14. Ensure you are connected to Oracle as the hue user, then run the following command to delete all data from Oracle tables: 
    > set pagesize 100;
> SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
  15. Run the statements generated in the preceding step.
  16. Commit your changes. 
    commit;
  17. Load the data that you dumped. Go to the Hue Server instance and 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.
  18. Start the Hue service.

Configuring the Hue Server to Store Data in Oracle (Package Installation)

If you have a parcel-based environment, see Configuring the Hue Server to Store Data in Oracle (Parcel Installation).

  1. 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.
  2. Unzip the Oracle client zip files.
  3. Set environment variables to reference the libraries. 
    $ export ORACLE_HOME=oracle_download_directory 
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
  4. Create a symbolic link for the shared object: 
    $ cd $ORACLE_HOME 
$ ln -sf libclntsh.so.11.1 libclntsh.so
  5. Install the required packages.

    RHEL

    $ sudo yum install gcc python-devel python-pip python-setuptools libaio

    SLES:

    Python devel packages are not included in SLES. Add the SLES Software Development Kit (SDK) as a repository and then install: 
    $ zypper install gcc libaio python-pip python-setuptools python-devel

    Ubuntu or Debian

    $ sudo apt-get install gcc python-dev python-pip python-setuptools libaio1
  6. For CDH versions lower than 5.3, install the Python Oracle library: 
    $ HUE_HOME/build/env/bin/pip install cx_Oracle
  7. For CDH versions lower than 5.3, upgrade django south: 
    $ HUE_HOME/build/env/bin/pip install south --upgrade
  8. In the Cloudera Manager Admin Console, go to the Hue service status page.
  9. Select Actions > Stop. Confirm you want to stop the service by clicking Stop.
  10. Select Actions > Dump Database. Confirm you want to dump the database by clicking Dump Database.
  11. Click the Configuration tab.
  12. Select Scope > All.
  13. Select Category > Advanced.
  14. Set the Hue Service Environment Advanced Configuration Snippet (Safety Valve) property to 
    ORACLE_HOME=oracle_download_directory
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:oracle_download_directory
  15. Set the Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini property. Add the following options (and modify accordingly for your setup): 
    [desktop]
[[database]]
host=localhost
port=1521
engine=oracle
user=hue
password=secretpassword
name=<SID of the Oracle database, for example, 'XE'>
    For CDH 5.1 and higher you can use an Oracle service name. To use the Oracle service name instead of the SID, use the following configuration instead: 
    port=0
engine=oracle
user=hue
password=secretpassword
name=oracle.example.com:1521/orcl.example.com

    The directive port=0 allows Hue to use a service name. The name string is the connect string, including hostname, port, and service name.

    To add support for a multithreaded environment, set the threaded option to true under the [desktop]>[[database]] section.

    options={"threaded":true}
  16. Grant required permissions to the hue user in Oracle: 
    GRANT CREATE <sequence> TO <user>; 
GRANT CREATE <session> TO <user>; 
GRANT CREATE <table> TO <user>; 
GRANT CREATE <view> TO <user>; 
GRANT CREATE <procedure> TO <user>; 
GRANT CREATE <trigger> TO <user>; 
GRANT EXECUTE ON sys.dbms_crypto TO <user>; 
GRANT EXECUTE ON SYS.DBMS_LOB TO <user>;
  17. Go to the Hue Server instance in Cloudera Manager and select Actions > Synchronize Database.
  18. Ensure you are connected to Oracle as the hue user, then run the following command to delete all data from Oracle tables: 
    > set pagesize 100;
> SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
  19. Run the statements generated in the preceding step.
  20. Commit your changes. 
    commit;
  21. Load the data that you dumped. Go to the Hue Server instance and 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.
  22. Start the Hue service.