--- title: How to manage the Hue database with the shell author: admin type: post date: 2014-02-03T17:59:00+00:00 url: /how-to-manage-the-hue-database-with-the-shell/ tumblr_gethue_permalink: - http://gethue.tumblr.com/post/75496233379/how-to-manage-the-hue-database-with-the-shell tumblr_gethue_id: - 75496233379 sf_thumbnail_type: - none sf_thumbnail_link_type: - link_to_post sf_detail_type: - none sf_page_title: - 1 sf_page_title_style: - standard sf_no_breadcrumbs: - 1 sf_page_title_bg: - none sf_page_title_text_style: - light sf_background_image_size: - cover sf_social_sharing: - 1 sf_sidebar_config: - left-sidebar sf_left_sidebar: - Sidebar-2 sf_right_sidebar: - Sidebar-1 sf_caption_position: - caption-right slide_template: - default categories: - Administration - Development --- _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][1] details the migration process. The database settings in Hue are located in the [hue.ini][2]. 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. [1]: http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH5/latest/CDH5-Installation-Guide/cdh5ig_hue_database.html [2]: https://github.com/cloudera/hue/blob/branch-3.5/desktop/conf.dist/hue.ini#L185