database.md 3.4 KB


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 Database

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 the Database

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.

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.

Note that Hue has only been tested with SQLite and MySQL database backends.

Configuring to Store Data in MySQL

To configure Hue to store data in MySQL:

  1. Create a new database in MySQL and grant privileges to a Hue user to manage this database.

    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 necessary database tables:

    /usr/share/hue/build/env/bin/hue syncdb --noinput
    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.