--- title: "Database" date: 2019-03-13T18:28:09-07:00 draft: false weight: 4 --- Hue requires a SQL database to store small amounts of data, including user account information as well as history of job submissions and Hive queries. By default, Hue is configured to use the embedded database SQLite for this purpose, and should require no configuration or management by the administrator. However, MySQL is the recommended database to use. This section contains instructions for configuring Hue to access MySQL and other databases. ### Inspecting The default SQLite database used by Hue is located in: `/usr/share/hue/desktop/desktop.db`. You can inspect this database from the command line using the `sqlite3` program or typing `/usr/share/hue/build/env/bin/hue dbshell'. For example: sqlite3 /usr/share/hue/desktop/desktop.db SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select username from auth_user; admin test sample sqlite> It is strongly recommended that you avoid making any modifications to the database directly using SQLite, though this trick can be useful for management or troubleshooting. ### Backing up If you use the default SQLite database, then copy the `desktop.db` file to another node for backup. It is recommended that you back it up on a regular schedule, and also that you back it up before any upgrade to a new version of Hue. ### Clean up When the database has too many entries in certain tables, it will cause performance issue. Now Hue config check will help superuser to find this issue. Login as superuser and go to “Hue Administration”, this sample screenshot will be displayed in the quick start wizard when the tables have too many entries. Run following clean up command: cd /opt/cloudera/parcels/CDH/lib/hue # Hue home directory ./build/env/bin/hue desktop_document_cleanup ### Configuring to Access Another Database Although SQLite is the default database type, some advanced users may prefer to have Hue access an alternate database type. Note that if you elect to configure Hue to use an external database, upgrades may require more manual steps in the future. The following instructions are for MySQL, though you can also configure Hue to work with other common databases such as PostgreSQL and Oracle.
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)
2. Shut down Hue if it is running.
3. To migrate your existing data to MySQL, use the following command to dump the
existing database data to a text file. Note that using the ".json" extension
is required.
/usr/share/hue/build/env/bin/hue dumpdata > $some-temporary-file.json
4. Open the `hue.ini` file in a text editor. Directly below the
`[[database]]` line, add the following options (and modify accordingly for
your MySQL setup):
host=localhost
port=3306
engine=mysql
user=hue
password=secretpassword
name=hue
5. As the Hue user, configure Hue to load the existing data and create the database tables:
/usr/share/hue/build/env/bin/hue migrate
mysql -uhue -psecretpassword -e "DELETE FROM hue.django_content_type;"
/usr/share/hue/build/env/bin/hue loaddata $temporary-file-containing-dumped-data.json
Your system is now configured and you can start the Hue server as normal.
### Migrating
Note: Hue Custom Databases includes database-specific pages on how to migrate from an old to a new database. This page summarizes across supported database types.
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.
Dump Database
Note: Refresh the page to ensure that the Hue service is stopped: Service Stopped icon.
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.
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.
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
Set the appropriate database parameters :
Hue Database Type: MySQL or PostgreSQL or Oracle
Hue Database Hostname: