Migrate the Hue Database

When you change Hue databases, you can migrate the existing data to your new database. If the data is dispensable, there is no need to migrate.

The Hue database stores things like user accounts, SQL queries, and Oozie workflows, and you may have accounts, queries, and workflows worth saving. See How to Populate the Hue Database.

Migrating your existing database currently requires some work-arounds (in parentheses):
  • Stop the Hue service.
  • Dump database (and delete "useradmin.userprofile" objects from .json file).
  • Connect to new database.
  • Synchronize database (and drop foreign key to clean tables).
  • Load database (and add foreign key).
  • Start Hue service.

Dump Database

  1. In the Hue Web UI, click the home icon Hue Home icon to see what documents you are migrating.
  2. In Cloudera Manager, stop the Hue service: go to Hue and select Actions > Stop.
  3. Select Actions > Dump Database and click Dump Database. The file is written to /tmp/hue_database_dump.json on the host of the Hue server.
  4. Log on to the host of the Hue server in a command-line terminal. You can find the hostname on the Dump Database window and at Hue > Hosts.
  5. 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"
  }
},

Connect New Database

In Cloudera Manager, connect Hue to the new database. See Hue Custom Databases for help on installing and configuring a custom database.
  1. Go to Hue > Configuration.
  2. Filter by category, Database.
  3. Set the appropriate database parameters : 
    Hue Database Type: MySQL or PostgreSQL or Oracle
Hue Database Hostname: <fqdn of host with database server>
Hue Database Port: 3306 or 5432 or 1521
Hue Database Username: <hue database username>
Hue Database Password: <hue database password>
Hue Database Name: <hue database name or SID>
  4. Click Save Changes.
  5. Oracle users only should add support for a multithreaded environment:
    1. Filter by Category, Hue-service and Scope, Advanced.
    2. Add support for a multithreaded environment by setting Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini: 
      [desktop]
[[database]]
options={"threaded":True}
    3. Click Save Changes.

Synchronize and Load

  1. Synchronize: select Actions > Synchronize Database and click Synchronize Database.
  2. Log on to the host of the database server in a command-line terminal and clean tables:
    • MySQL and PostgreSQL users remove a foreign key from auth.permission and clean django_content_type.
    • Oracle users delete content from all tables.
  3. Load: select Actions > Load Database and click Load Database.
  4. Return to the host of the database server:
  5. Start: select Actions > Start and click Start.
  6. In the Hue Web UI, click the home icon Hue Home icon to ensure that all documents were migrated.

MariaDB / MySQL

  1. Synchronize Database in Cloudera Manager.
  2. Log on to MySQL: 
    mysql -u root -p
Enter password: <root password>
  3. Drop the foreign key constraint from the hue.auth_permission table:
    • Execute the following statement to find the content_type_id_refs_id_<value> in the CONSTRAINT clause of the CREATE TABLE statement for the hue.auth_permission table: 
      SHOW CREATE TABLE hue.auth_permission;
      This SHOW CREATE TABLE statement produces output similar to the following: 
      |  auth_permission | CREATE TABLE 'auth_permission' (
   'id' int(11) NOT NULL AUTO-INCREMENT,
   'name' varchar(50) NOT NULL,
   'content_type_id' int(11) NOT NULL,
   'CODENAME' VARCHAR(100) NOT NULL,
   PRIMARY KEY ('id'),
   UNIQUE KEY 'content_type_id' ('content_type_id', 'codename'),
   KEY 'auth_permission_37ef4eb4' ('content_type_id'),
   CONSTRAINT 'content_type_id_refs_id_d043b34a' FOREIGN KEY ('content_type_id')
REFERENCES 'django_content_type' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=229 DEFAULT CHARSET=utf8 |
    • Then execute the following statement to drop the foreign key constraint: 
      ALTER TABLE hue.auth_permission DROP FOREIGN KEY
content_type_id_refs_id_<value>;

      For example, if you used the above output from the SHOW CREATE TABLE statement, you would use the following ALTER TABLE statement:

      ALTER TABLE hue.auth_permission DROP FOREIGN KEY
content_type_id_refs_id_d043b34a;
  4. Delete the contents of django_content_type: 
    DELETE FROM hue.django_content_type;


  5. Load Database in Cloudera Manager.
  6. Add the foreign key, content_type_id, to auth_permission: 
    ALTER TABLE hue.auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);


  7. Start Hue in Cloudera Manager.

PostgreSQL

  1. Synchronize Database in Cloudera Manager.
  2. Log on to PostgreSQL: 
    psql -h localhost -U hue -d hue
Password for user hue:
  3. Drop the foreign key constraint from auth_permission: 
    \d auth_permission;
ALTER TABLE auth_permission DROP CONSTRAINT content_type_id_refs_id_<id value>;
  4. Delete the contents of django_content_type: 
    TRUNCATE django_content_type CASCADE;
  5. Load Database in Cloudera Manager.
  6. Add the foreign key, content_type_id, to auth_permission: 
    ALTER TABLE auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED;
  7. Start Hue in Cloudera Manager.

Oracle

Oracle users should delete all content from the Oracle tables after synchronizing and before loading:
  1. Synchronize Database in Cloudera Manager.
  2. Log on to Oracle: 
    su - oracle
sqlplus / as sysdba
  3. Grant a quota to the tablespace where tables are created (the default is SYSTEM). For example: 
    ALTER USER hue quota 100m on system;
  4. Log on as the hue: 
    sqlplus hue/<hue password>
  5. Create a spool script that creates a delete script to clean the content of all tables. 
    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
  6. Run both scripts: 
    ## 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
  7. Load Database in Cloudera Manager.
  8. Start Hue in Cloudera Manager.