How to manage the Hue database with the shell

Last update on March 9 2016

First, backup the database. By default this is this SqlLite file:

cp /var/lib/hue/desktop.db ~/

Then if using CM, export this variable in order to point to the correct database:

HUE_CONF_DIR=/var/run/cloudera-scm-agent/process/-hue-HUE_SERVER-id

echo $HUE_CONF_DIR

export HUE_CONF_DIR

Where is the most recent ID in that process directory for hue-HUE_SERVER.

A quick way to get the correct directory is to use this script:

export HUE_CONF_DIR="/var/run/cloudera-scm-agent/process/\`ls -alrt /var/run/cloudera-scm-agent/process | grep HUE | tail -1 | awk '{print $9}'\`"

Then go in the Database. From the Hue root (/use/lib/hue by default):

root@hue:hue# build/env/bin/hue dbshell

Note:

You might hit some permissions error about the logs:

build/env/bin/hue dbshell

Traceback (most recent call last):

File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/build/env/bin/hue", line 9, in 

load_entry_point('desktop==3.6.0', 'console_scripts', 'hue')()

File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/manage_entry.py", line 41, in entry

from desktop import settings, appmanager

File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/settings.py", line 55, in 

desktop.log.basic_logging(os.environ[ENV_HUE_PROCESS_NAME])

File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/log/__init__.py", line 146, in basic_logging

logging.config.fileConfig(log_conf)

File "/usr/lib/python2.7/logging/config.py", line 78, in fileConfig

handlers = _install_handlers(cp, formatters)

File "/usr/lib/python2.7/logging/config.py", line 156, in _install_handlers

h = klass(*args)

File "/usr/lib/python2.7/logging/handlers.py", line 118, in __init__

BaseRotatingHandler.__init__(self, filename, mode, encoding, delay)

File "/usr/lib/python2.7/logging/handlers.py", line 65, in __init__

logging.FileHandler.__init__(self, filename, mode, encoding, delay)

File "/usr/lib/python2.7/logging/__init__.py", line 897, in __init__

StreamHandler.__init__(self, self._open())

File "/usr/lib/python2.7/logging/__init__.py", line 916, in _open

stream = open(self.baseFilename, self.mode)

IOError: [Errno 13] Permission denied: '/tmp/logs/dbshell.log'

A “workaround” is to run the command as root:

sudo HUE_CONF_DIR=/var/run/cloudera-scm-agent/process/9679-hue-HUE_SERVER /opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/build/env/bin/hue dbshell

And you can start typing SQL queries:

sqlite> .tables

auth_group oozie_dataset

auth_group_permissions oozie_decision

auth_permission oozie_decisionend

auth_user oozie_distcp

auth_user_groups oozie_email

auth_user_user_permissions oozie_end

beeswax_metainstall oozie_fork

beeswax_queryhistory oozie_fs

beeswax_savedquery oozie_generic

beeswax_session oozie_history

desktop_document oozie_hive

desktop_document_tags oozie_java

desktop_documentpermission oozie_job

desktop_documentpermission_groups oozie_join

desktop_documentpermission_users oozie_kill

desktop_documenttag oozie_link

desktop_settings oozie_mapreduce

desktop_userpreferences oozie_node

django_admin_log oozie_pig

django_content_type oozie_shell

django_openid_auth_association oozie_sqoop

django_openid_auth_nonce oozie_ssh

django_openid_auth_useropenid oozie_start

django_session oozie_streaming

django_site oozie_subworkflow

jobsub_checkforsetup oozie_workflow

jobsub_jobdesign pig_document

jobsub_jobhistory pig_pigscript

jobsub_oozieaction search_collection

jobsub_ooziedesign search_facet

jobsub_ooziejavaaction search_result

jobsub_ooziemapreduceaction search_sorting

jobsub_ooziestreamingaction south_migrationhistory

oozie_bundle useradmin_grouppermission

oozie_bundledcoordinator useradmin_huepermission

oozie_coordinator useradmin_ldapgroup

oozie_datainput useradmin_userprofile

oozie_dataoutput

Or migrating the database manually:

build/env/bin/hue syncdb

build/env/bin/hue migrate

If you want to switch to another database (we recommend MySql), this guide details the migration process.

The database settings in Hue are located in the hue.ini.

Note, you also query the database by pointing the DB Query App to the Hue database.

In developer mode (runserver command), you can also access the /admin page for using the Django Admin.

Examples

Transfer Oozie workflows belonging to the user Bob to Joe.

until Hue 3.8

# First move the objects

from oozie.models import Job

from django.contrib.auth.models import User

u1 = User.objects.get(username='bob')

u2 = User.objects.get(username='joe')

Job.objects.filter(owner=u1)

> []

Job.objects.filter(owner=u1).update(owner=u2)

> 1

Job.objects.filter(owner=u1)

> []

Job.objects.filter(owner=u2)

> []

wfs = Job.objects.filter(owner=u2)

For Hue 3.9+

# First move the objects

from desktop.models import Document2

from django.contrib.auth.models import User

u1 = User.objects.get(username='bob')

u2 = User.objects.get(username='joe')

Document2.objects.filter(owner=u1, type='oozie-workflow2')

> []

Document2.objects.filter(owner=u1, type='oozie-workflow2').update(owner=u2)

> 1

Document2.objects.filter(owner=u1, type='oozie-workflow2')

> []

Document2.objects.filter(owner=u2, type='oozie-workflow2')

> []

wfs = Document2.objects.filter(owner=u2, type='oozie-workflow2')

For both

# The list of ALL the workflows (will also list the already known ones) of the second user

\# Then move the documents

from desktop.models import Document

Document.objects.filter(object_id__in=wfs)

> []

Document.objects.filter(object_id__in=wfs)

> []

Document.objects.filter(object_id__in=wfs).update(owner=u2)

> []

Note: it will change again in Hue 3.10 and be easier.